MySQL資料庫（良心資料）

2019 年 10 月 10 日
筆記

一、MySQL資料庫

1、資料庫簡介

1.1、資料庫的概念

資料庫就是用來存儲和管理數據的倉庫。資料庫存儲數據的優點：

l 可存儲大量數據；

l 方便檢索；

l 保持數據的一致性、完整性；

l 安全，可共享；

l 通過組合分析，可產生新數據；

1.2、資料庫的發展歷程

l 沒有資料庫，使用磁碟文件存儲數據；

l 層次結構模型資料庫；

l 網狀結構模型數據；

l 關係結構模型資料庫：使用二維表格來存儲數據；

l 關係-對象模型資料庫；

MySQL就是關係型資料庫。

1.3、常見資料庫

l Oracle：甲骨文；

l MySQL：甲骨文；

l DB2：IBM；

l SQL Server：微軟；

l Sybase：賽爾斯；

1.4、理解資料庫

我們所說的資料庫泛指「關係型資料庫管理系統（RDBMS-Relational database management system）」，即「資料庫伺服器」。

當我們安裝了資料庫伺服器後，就可以在資料庫伺服器中創建資料庫，每個資料庫中還可以包含多張表。

資料庫表就是一個多行多列的表格。在創建表時，需要指定表的列數，以及列名稱，列類型等資訊。而不用指定表格的行數，行數是沒有上限的。下面是tab_student表的結構：

當把表格創建好了之後，就可以向表中添加數據了。向表格添加數據是以行為單位的！下面是s_student表的記錄：

s_id	s_name	s_age	s_sex
s_1001	tom	23	m
s_1002	liyi	32	w
1_1003	jack	44	m

資料庫的理解：

l RDBMS=管理員（manager）+倉庫（database）

l database=N個table

l table：

l 表結構：定義表的列名和列類型

l 表記錄：一行一行的記錄

1.5、應用程式與資料庫

應用程式使用資料庫完成對數據的存儲。

2、安裝MySQL資料庫

2.1、安裝MySQL

參考：MySQL安裝圖解.doc

2.2、MySQL目錄結構

1、MySQL安裝成功後會在兩個目錄中存儲文件：

C:ProgramDataMySQLMySQL Server 5.5data：DBMS資料庫文件（卸載MySQL時不會刪除這個目錄，需要自己手動刪除）；
D:Program FilesMySQLMySQL Server 5.5：DBMS管理程式

2、MySQL重要文件

D:Program FilesMySQLMySQL Server 5.5binmysql.exe：客戶端程式，用來操作伺服器。但必須保證伺服器已開啟才能連接上；
D:Program FilesMySQLMySQL Server 5.5binmysqld.exe：伺服器程式，必須先啟動它，客戶端才能連接上伺服器；
D:Program FilesMySQLMySQL Server 5.5binmy.ini：伺服器配置文件；

3、C:ProgramDataMySQLMySQL Server 5.5dat

該目錄下的每個目錄表示一個資料庫，例如該目錄下有一個mysql目錄，那麼說明你的DBMS中有一個名為mysql的database；
在某個資料庫目錄下回有0–n個擴展名為frm的文件，每個frm文件表示一個table，不要用文本編輯器打開它，它是有DBMS來讀寫的；

4、my.ini，MySQL最為重要的配置文件

配置MySQL的埠，默認為3306，沒有必要時不要修改；
配置字元編碼：

l [client]下配置客戶端編碼：default-character-set=gbk

l [mysqld]下配置伺服器編碼：character-set-server=utf8

配置二進位數據大小上限：

l 在[mysqld]下配置：max_allowed_packet=8M

2.3、伺服器操作

l 開啟伺服器（必須保證mysql為windows服務）：net start mysql

查看進程表中是否存在：mysqld.exe進程（存在）

l 關閉伺服器（必須保證mysql為windows服務）：net stop mysql

查看進程表中是否存在：mysqld.exe進程（不存在）

2.4、客戶端操作

打開運行窗口輸入「cmd」

1、登錄伺服器：mysql -uroot -proot -hlocalhost

l -u：後面跟隨用戶名

l -p：後面跟隨密碼

l -h：後面跟隨資料庫伺服器IP地址，localhost和127.0.0.1表示本機伺服器

2、退出伺服器：exit或quit

3、SQL語言概述

3.1、SQL簡介

1) 什麼是SQL

結構化查詢語言（Structured Query Language）。

2) SQL的作用

客戶端使用SQL來操作伺服器；

啟動mysql.exe，連接伺服器後，就可以使用sql來操作伺服器了；

將來會使用Java程式連接伺服器，然後使用sql來操作伺服器。

3) SQL標準（例如SQL99，即1999年制定的標準）

由國際標準組織（ISO）制定的，對DBMS的統一操作方式（例如相同的語句可以操作：mysql、Oracle等）。

4) SQL方言

某種DBMS不會只支援SQL標準，而且還會有一些自己獨有的語法，被稱為方言。例如limit語句只在MySQL中可以使用。

3.2、SQL語法

1) SQL語句可以在單行或多行書寫，以分號結尾；

2) 可使用空格和縮進來增強語句的可讀性；

3) MySQL不區分大小寫，建議使用大寫

3.3、SQL語句分類（重點）

1) DDL（Data Definition Language）：數據定義語言，用來定義資料庫對象：對資料庫或表的結構操作（增、刪、改）。

2) DML（Data Manipulation Language）：數據操作語言，用來定義資料庫記錄（數據）；

對錶的記錄進行更新（增、刪、改）。

3) DCL（Data Control Language）：數據控制語言，用來定義訪問許可權和安全等級；

對用戶的創建和授權。

4) DQL（Data Query Language）：數據查詢語言，用來查詢記錄（數據）；

主要是對錶記錄的查詢。（重點）

4、SQL操作

4.1、DDL語句

1. 資料庫

l 查看所有資料庫：SHOW DATABASES

l 切換（選擇要操作的）資料庫：USE 資料庫名

l 創建資料庫：CREATE DATABASE [IF NOT EXISTS] mydb1 [CHARSET=utf8]

l 刪除資料庫：DROP DATABASE [IF EXISTS] mydb1

l 修改資料庫編碼：ALTER DATABASE mydb1 CHARACTER SET utf8

2、數據類型（列類型）

l int：整型

l double：浮點型，例如double(5,2)表示最多5位，其中必須有2為小數，即最大值為999.99；

l decimal：浮點型，在表示錢方面使用該類型，因為不會出現精度缺失問題；

l char：固定長度字元串類型；char(255)，數據的長度不足指定長度時，會補足到指定長度，一般用於存儲固定長度的字元串，例如：身份證號，日期時間等；

l varchar：可變長度字元串類型；varchar(65535)，不會自動補足到指定長度，但是會至少佔用1個位元組來記錄字元長度，一般存儲姓名等；

l text(clob)：字元串類型，MySQL獨有類型

類型	長度
tinytext	28-1B(256B)
text	216-1B(64K)
mediumtext	224-1B(16M)
longtext	232-1B(4G)

l blob：位元組類型；

類型	長度
tinyblob	28-1B(256B)
blob	216-1B(64K)
mediumblob	224-1B(16M)
longblob	232-1B(4G)

l data：日期類型，格式為 yyyy-MM-dd；

l time：時間類型，格式為 hh:mm:ss；

l timestamp：時間戳類型；

3、表

l 創建表：

CREATE TABLE [IF NOT EXISTS] 表名(

列名列類型,

……

列名列類型

);

l 查看當前資料庫中所有表名稱：SHOW TABLES;

l 查看指定表的創建語句：SHOW CREATE TABLE 表名;

l 查看錶結構：DESC 表名;

l 刪除表：DROP TABLE 表名;

l 修改表：前綴為 ALTER TABLE 表名

a) 添加列：

ALTER TABLE 表名 ADD(

列名列類型,

列名列類型，

……

);

b) 修改類類型（如果被修改的列已存在數據，那麼新的類型可能會影響到已存在數據）：ALTER TABLE 表名 MODIFY 列名列類型;

c) 修改列名：ALTER TABLE 表名 CHANGE 原列名新列名列類型;

d) 刪除列：ALTER TABLE 表名 DROP 列名;

e) 修改表名稱：ALTER TABLE 原表名 RENAME TO 新表名;

4.2、DML語句

1. 插入數據

l INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);

在表名後給出要插入的列名，其他沒有指定的列等同於插入null值。所以插入記錄總是插入一行；

在VALUES後給出列值，值的順序和個數必須與前面指定的列對應；

在資料庫中所有的字元類型，必須使用單引號，不能使用雙引號，日期類型也要使用單引號

l INSERT INTO 表名 VALUES(列值1,值2);

沒有給出要插入的列，那麼表示插入的所有列；

值的個數必須是該表列的個數；

值的順序，必須與表創建時給出的列的順序相同。

2. 修改數據

l UPDATE 表名 SET 列名1=列值1,列名2=列值2,… [WHERE 條件]

l 條件（條件可選）：

² 條件必須是一個boolean類型的值或表達式：UPDATE t_person SET gender='男', age=age+1 WHERE sid='1';

² 運算符：=，!=，<>，>，<，>=，<=，BETWEEN…AND，IN(…)，IS NULL，NOT，OR，AND

3. 刪除數據

l DELETE FROM 表名 [WHERE 條件];

l TRUNCATE TABLE 表名：TRUNCATE是DDL語句，它是先刪除drop該表，再create該表，而且無法回滾。

4.3、DCL語句

1. 創建用戶

l CREATE USER 用戶名@IP地址 IDENTIFIED BY '密碼';

用戶只能在指定的IP地址上登錄

l CREATE USER 用戶名@'%' IDENTIFIED BY '密碼';

用戶可以在任意IP地址上登錄

2. 給用戶授權

l GRANT 許可權1,……,許可權n ON 資料庫.* TO 用戶名@IP地址;

給用戶分派在指定的資料庫上的指定許可權

例如：GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;

給user1用戶分派在mydb1資料庫上的CREATE、ALTER、DROP、INSERT、UPDATE、DELETE、SELECT許可權

l GRANT ALL ON 資料庫.* TO 用戶名@IP地址;

給用戶分派指定資料庫上的所有許可權

3. 撤銷授權

l REVOKE 許可權1,……,許可權n ON 資料庫.* FROM 用戶名@IP地址;

撤銷指定用戶在指定資料庫上的指定許可權

例如：REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

撤銷user1用戶在mydb1資料庫上的CREATE、ALTER、DROP許可權

4. 查看許可權

l SHOW GRANTS FOR 用戶名@IP地址;

查看指定用戶的許可權

5. 刪除用戶

l DROP USER 用戶名@IP地址;

4.4、DQL語句（重點）

DQL語句只會對資料庫查詢，不會修改數據。

1. 基本查詢

l 欄位（列）控制

² 查詢所有列

SELECT * FROM 表名;

其中「*」表示所有列

l 查詢指定列

SELECT 列1[,列2,……列n] FROM 表名;

l 完全重複的記錄只顯示一次

當查詢結果中的多行記錄完全一樣時，只顯示一行，一般用於查詢某個欄位中一共有幾種類型的值。

SELECT DISTINCT * | 列1[,列2,……,列n] FROM 表名;

例如：SELECT DISTINCT sal FROM emp;

查詢員工表的工資，如果存在相同的工資只顯示一次

l 列運算

u 數量類型的列可以做加、減、乘、除運算

SELECT *,sal*1.5 FROM emp;

字元串做算術運算時，會被當做0來進行運算，在字元串中+號不代表拼接

u 字元串類型可以做連續運算

SELECT CONCAT('$',sal) FROM emp;

u 轉換NULL值

有時需要把NULL轉換成其他值，例如com+1000時，如果com列存在NULL值，那麼NULL+1000還是NULL，而我們這是希望把NULL當做0來運算。

SELECT IFNULL(comm,0)+1000 FROM emp;

IFNULL(comm,0)：如果comm中存在NULL值，那麼當成0來運算；

u 給列起別名

當使用列運算後，查詢出的結果集中的列名稱不好看，這時我們需要給列名起個別名，這樣在結果集中列名就顯示別名了。

SELECT IFNULL(comm,0)+1000 AS 獎金 FROM emp;

其中AS可以省略

2. 條件控制

l 條件查詢

u 與前面介紹的UPDATE和DELETE語句一樣，SELECT語句也可以使用WHERE子句來控制記錄。

ü SELECT empno,ename,sal,comm FROM emp WHERE sal > 1000 AND comm IS NOT NULL;

ü SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;

ü SELECT empno,ename,job FROM emp WHERE job IN('經理','董事長');

l 模糊查詢

u 當你想查詢姓張，並且姓名一共兩個字的員工時，就可以使用模糊查詢

SELECT * FROM emp WHERE ename LIKE '張_';

模糊查詢需要使用運算符：LIKE，其中匹配一個任意字元，注意，只匹配一個字元而不是多個。

上面語句查詢的是姓張，名字由兩個字組成的員工。

u 下劃線「_」可以匹配1個字元，如果要匹配0-n個字元，需要用「%」;

SELECT * FROM emp WHERE ename LIKE 『%剛』;

查詢名字結尾是帶「剛」字的員工。

3. 排序查詢

l 升序

u SELECT * FROM emp ORDER BY sal ASC;

按sal排序，ASC升序，DESC降序，其中ASC是可以省略的

l 降序

u SELECT * FROM emp ORDER BY comm DESC;

按comm降序排序查詢

l 使用多列作為排序條件

u SELECT * FROM emp ORDER BY sal ASC, comm DESC;

使用sal升序排序，如果sal相等，再按照comm降序排序

4. 聚合函數

聚合函數用來做某列的縱向運算

l COUNT()函數

SELECT COUNT(*) FROM emp;

計算emp表中所有列都不為NULL的記錄的行數

SELECT COUNT(comm) FROM emp;

計算emp表中comm列不為NULL的記錄的行數

l MAX()函數

SELECT MAX(sal) FROM emp;

查詢最高工資

l MIN()函數

SELECT MIN(sal) FROM emp;

查詢最低工資

l SUM()函數

SELECT SUM(sal) FROM emp;

查詢所有員工工資的總和

l AVG()函數

SELECT AVG(sal) FROM emp;

查詢平均工資

5. 分組查詢

l 分組查詢是把記錄使用某一列進行分組，然後查詢組資訊。

例如：查看所有部門的記錄數。

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

使用deptno分組，查詢部門編號和每個部門的記錄數

SELECT job,MAX(sal) FROM emp GROUP BY job;

使用job分組，查詢每種工作的最高工資

l 組條件

以部門分組，查詢每組記錄數，條件為記錄數大於3

SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;

6. LIMIT子句（方言）

l LIMIT用來限定查詢結果的起始行，以及總行數。

例如：查詢起始行為第5行，一共查詢3行記錄

SELECT * FROM emp LIMIT 4,3;

其中4表示從第5行開始，其中3表示一共查詢3行。即第5/6/7行記錄。

l 分頁查詢中計算起始頁的公式：（當前頁-1）*每頁記錄數；

例如：每頁顯示10條記錄，查詢第3頁

SELECT * FROM emp LIMIT 20，10;

4.5、DQL語句練習

l 查詢出部門編號為30的所有員工

select * from emp where deptno=30;

l 查詢所有銷售員的姓名、編號和部門編號

select ename,empno,deptno from emp where job='銷售員';

l 查詢獎金高於工資的員工

select ename from emp where comm > sal;

l 查詢獎金高於工資60%的員工

select ename from emp where comm > sal*0.6;

l 查詢部門編號為10中所有經理，和部門編號為20中所有銷售員的詳細資料

select * from emp where (deptno = 10 and job='經理') or (deptno=20 and job='銷售員');

l 查詢部門編號為10中所有經理，部門編號為20中所有銷售員，還有既不是經理又不是銷售員但其工資大於或等於20000的所有員工資料

select * from emp where (deptno = 10 and job='經理') or (deptno=20 and job='銷售員') or (job not in ('經理','銷售員') and sal>20000);

l 查詢無獎金或獎金低於1000的員工

select * from emp where comm is null or comm<1000;

l 查詢名字由三個字組成的員工

select * from emp where ename like '___';

l 查詢2000年入職的員工

select * from emp where hiredate like '2000-%';

l 查詢所有員工詳細資訊，用編號升序排序

select * from emp order by empno asc;

l 查詢所有員工詳細資訊，用工資降序排序，如果工資相同使用入職日期升序排序

select * from emp order by sal desc,hiredate asc;

l 查詢每個部門的平均工資

select deptno,avg(*) 平均工資 from emp group by deptno;

l 查詢每個部門的僱員數量

select deptno,count(*) 數量 from emp group by deptno;

l 查詢每種工作的最高工資、最低工資、人數

select job,max(*) 最高工資,min(*) 最低工資,count(*) 人數 from emp group by job;

4、MySQL編碼問題（了解）

4.1、查看MySQL資料庫編碼

SHOW VARIABLES LIKE 'char%';

4.2、編碼解釋

l character_set_client：MySQL使用該編碼來解讀客戶端發送過來的數據，例如該編碼為utf8，那麼如果客戶端發送過來的數據不是utf8，就會出現亂碼；

l character_set_results：MySQL會把數據轉換成該編碼後，在發送給客戶端，例如該編碼為utf8，那麼如果客戶端不使用utf8來解讀，就會出現亂碼；

4.3、控制台亂碼問題

l 插入或修改時出現亂碼：

這是因為cmd下默認使用GBK，而character_set_client不是GBK的原因，我們只需讓這兩個編碼相同即可；

因為修改cmd的編碼不方便，所以我們去設置character_set_client為GBK即可。

l 查詢出的數據為亂碼：

這是因為character_set_results不是GBK，而cmd默認使用GBK的原因，我們只需讓這兩個編碼相同即可；

因為修改cmd的編碼不方便，所以我們去設置character_set_results為GBK即可。

l 設置變數的語句：

set character_set_client=gbk

set character_set_results=gbk

l 這裡需要注意的是，設置變數只對當前連接有效，當退出窗口後，再次登錄mysql，還需要再次設置變數。為了一勞永逸，可以在my.ini中設置： default-character-set=gbk

l 指定默認編碼

我們在安裝MySQL時已經指定了默認編碼為utf8，所以我們在創建資料庫、創建表時，都無需再次指定編碼。為了一勞永逸，可以在my.ini中設置：character-set-server=utf8

5、MySQL備份與恢復

5.1、資料庫導出SQL腳本（備份資料庫內容）

mysqldump -u用戶名 -p密碼資料庫名>生成的腳本文件路徑

例如：mysqldump -uroot -proot mydb1>D:mydb1.sql

注意，不要分號，不要登錄mysql，直接在cmd下運行，生成的腳本文件中不包含create database語句

5.2、執行SQL腳本（恢複數據）

l 第一種方式

mysql -u用戶名 -p密碼資料庫<腳本文件路徑

先刪除mydb1庫，再重新創建mydb1庫，然後執行語句：

mysql -uroot -proot mydb1<D:mydb1.sql

注意，不要使用分號，不要登錄資料庫，直接在cmd下運行

l 第二種方式

登錄mysql

source SQL腳本路徑

先刪除mydb1庫，再重新創建mydb1庫，切換到mydb1庫，然後執行語句：source c:mydb1.sql

6、MySQL約束

約束是添加在列上的，用來約束列的。

6.1、主鍵約束

主鍵約束的特點有：非空、唯一、被引用

當表的某一列被指定為主鍵後，該列就不能為空，不能有重複值出現。

1. 創建表時指定主鍵的兩種方式：

l 方法一：

CREATE TABLE stu(

sid CHAR(6) PRIMARY KEY,

snam VARCHAR(20),

age INT,

gender VARCHAR(10)

);

l 方法二：

指定sid列為主鍵列，即為sid列添加主鍵約束

CREATE TABLE stu(

sid CHAR(6),

sname VARCHAR(20),

gender VARCHAR(10),

PRIMARY KEY(sid)

);

指定sid列為主鍵列，即為sid列添加主鍵約束

修改表時指定主鍵：ALTER TABLE stu ADD PRIMARY KEY(sid);
刪除主鍵：ALTER TABLE stu DROP PRIMARY KEY;

2. 主鍵自增長

因為主鍵列的特點是：必須唯一，不能為空，所以我們通常會指定主鍵類為整型，然後設置其自動增長，這樣可以保證在插入數據時主鍵列的唯一和非空特性。

l 創建表時指定主鍵自增長

CREATE TABLE stu(

sid INT PRIMARY KEY ATUO_INCREMENT,

sname VARCHAR(20),

age INT,

gender VARCHAR(10)

);

l 修改表時設置主鍵自增長：ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

l 修改表時刪除主鍵自增長：ALTER TABLE stu CHANGE sid sid INT;

l 測試主鍵自增長：

INSERT INTO stu VALUES(NULL,'zhangsan',23,'male');

INSERT INTO stu(sname,age,gender) VALUES('zhangsan',23,'male');

6.2、非空約束

因為某些列不能設置為NULL值，所以可以對列添加非空約束。

例如：

CREATE TABLE stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL,

age INT,

gender VARCHAR(10)

);

對sname列設置非空約束

6.3、唯一約束

車庫某些列不能設置重複的值，所以可以對列添加唯一約束。

例如：

CREATE TABLE stu(

sid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(20) NOT NULL UNIQUE,

age INT,

gender VARCHAR(10)

);

對sname列設置了非空約束

6.4、概念模型

當我們要完成一個軟體系統時，需要把系統中的實體抽取出來，形成概念模型。

例如部門、員工都是系統中的實體。概念模型中的實體最終會成為Java中的類、資料庫中的表。

對象模型就是java中的class，關係（數據）模型就是指資料庫中的表。

在java中的三種關係是：is a、has a、use a

實體之間還存在著關係，關係有三種：

l 一對多：例如每個員工都從屬於一個部門，而一個部門可以有多個員工，其中員工是多方，部門是一方；

l 一對一：列如夫妻關係，只能是一夫一妻；

l 多對多：例如老師與學生的關係，一個老師可以有多個學生，一個學生可以有多個老師。

概念模型在Java中成為實體類（JavaBean）

類就使用成員變數來完成關係，一般都是雙向關聯，在多對一雙向中關聯，即部門中關聯員工，員工中也關聯部門。

Java的一對多

class Employee{//多方關聯一方 … private Department department; } class Deparment{//一方關聯多方 … private List<Employee> empList; }

Java的一對一

class Husband{ … private Wife wife; } class Wife{ … private Husband husband; }

Java多對多

class Student{ … private List<Teacher> teaList; } class Teacher{ … private List<Student> stuList; }

對象模型：可以雙向關聯，而且引用的是對象，而不是一個主鍵；

關係模型：只能多方引用一方，而且引用的只是主鍵，而不是一整行記錄。

6.5、外鍵約束

l 外鍵必須是另一張表（或本表）的主鍵的值，外鍵要引用主鍵；

l 外鍵可以重複；

l 外鍵可以為空；

l 一張表中可以有多個外鍵；

資料庫多對一關係

從表中的外鍵關聯主表的主鍵

創建主表

CREATE TABLE dept( deptno INT PRIMARY KEY AUTO_INCREMENT, dname VARCHAR(50) );

創建從表

CREATE TABLE emp( empno INT PRIMARY KEY AUTO_INCREMENT, ename VARCHAR(50), dno INT, CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno) );

資料庫一對一關係

在表中建立一對一關係比較特殊，需要讓從表的主鍵，即是主鍵又是外鍵。

創建主表

CREATE TABLE hasband( hid INT PRIMARY KEY AUTO_INCREMENT, hname VARCHAR(50) );

創建從表

CREATE TABLE wife( wid INT PRIMARY KEY AUTO_INCREMENT, wname VARCHAR(50), CONSTRAINT fk_wife_hasband FOREIGN KEY(wid) REFERENCES hasband(hid) );

資料庫多對多關係

在表中建立多對多關係需要使用中間表，即需要三張表，在中間表中使用兩個外鍵，分別引用其他兩個表的主鍵。

學生表

CREATE TABLE student( sid INT PRIMARY KEY AUTO_INCREMENT, … );

老師表

CREATE TABLE teacher( tid INT PRIMARY KEY AUTO_INCREMENT, … );

中間表

CREATE TABLE stu_tea( sid INT, tid INT, CONSTRAINT fk_stu_tea_sid FOREIGN KEY(sid) REFERENCES student(sid), CONSTRAINT fk_stu_tea_tid FOREIGN KEY(tid) REFERENCES teacher(tid) );

7、多表查詢

7.1、多表查詢的分類

l 合併結果集

l 連接查詢

l 子查詢

7.2、合併結果集

要合併的表的結果集，列類型和列數相同

UNION：去除重複行

UNION ALL：不去除重複行

例如：

SELECT * FROM ab

UNION ALL

SELECT * FROM cd;

7.3、連接查詢

分類

l 內連接

l 外連接

² 左外連接

² 右外連接

² 全外連接（MySQL不支援）

l 自然連接

內連接

l 直接查詢兩張表會得到笛卡爾集表

l 方言：SELECT * FROM 表1 別名1,表2 別名2 WHERE 別名1.xx=別名2.xx

l 標準：SELECT * FROM 表1 別名1 INNER JOIN 表2 別名2 ON 別名1.xx=別名2.xx

l 自然：SELECT * FROM 表1 別名1 NATURAL JOIN 表2 別名2

l 內連接查詢出的所有記錄都滿足條件

外連接

l 左外：SELECT * FROM 表1 別名1 LEFT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx

² 左外即左表為主，左表記錄無論是否滿足條件都會查詢出來，而右表只有滿足條件才能出來。左表不滿足條件的記錄，右表部分都為NULL

l 左外自然：SELECT * FROM 表1 別名1 NATURAL LEFT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx

l 右外：SELECT * FROM 表1 別名1 RIGHT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx

² 右表記錄無論是否滿足條件都會查詢出來，而左表只有滿足條件才能查詢出來。右表不滿足條件的記錄，其左表部分都為NULL

l 右外自然：SELECT * FROM 表1 別名1 NATURAL RIGHT OUTER JOIN 表2 別名2 ON 別名1.xx=別名2.xx

l 全連接：

在其他資料庫中使用FULL OUTER JOIN實現全連接，但是MySQL不支援，我們可以使用UNION來完成全連接；

在左外查詢語句和右外查詢語句之間加上UNION，得到左外和右外的結果集，可以間接實現全連接。

7.4、子查詢

子查詢即查詢中包含了查詢

出現的位置

l WHERE後作為條件存在

例如：查詢工資最高的員工的詳細資訊

SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

l FROM後作為表存在（多行多列）

查詢30號部門員工的編號和姓名

SELECT e.empno,e.ename FROM (SELECT * FROM emp WHERE deptno=30) e;

條件

l 單行單列：SELECT * FROM 表1 別名1 WHERE 列1 [=、>、<、>=、<=、!=] (SELECT 列 FROM 表2 別名2 WHERE 條件)；

例如：查詢大於平均工資的所有員工資訊

SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

l 多行單列：SELECT * FROM 表1 別名1 WHERE 列1 [IN、ALL、ANY] (SELECT 列 FROM 表2 別名2 WHERE 條件);

例如：查詢大於30部門所有人工資的員工資訊

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30);

l 單行多列：SELECT * FROM 表1 別名1 WHERE (列1，列2) IN (SELECT 列1,列2 FROM 表2 別名2 WHERE 條件);

例如：查詢公司里和某員工在用一個部門並且崗位和工資都相同的員工資訊

SELECT * FROM emp WHERE (job,deptno,sal) IN (SELECT job,deptno,sal FROM emp WHERE ename='張三');

l 多行多列：SELECT * FROM 表1 別名1,(SELECT …) 別名2 WHERE 條件;

二、Oracle資料庫

1、Oracle概述

1.1、資料庫的概念

數據：在資料庫領域看來，數據是存儲的基本單位，包含文本、圖片，影片，音頻等；
資料庫：就是數據倉庫，存儲數據的地方，特指電腦設備中的硬碟，以二進位壓縮文本的形式存放。該文件不能直接操作，必須有各資料庫公司提供的工具方可操作，該文件的格式是每個資料庫公司定義的，不是統一規則。
資料庫對象：在Oracle中，例如：表、視圖、索引、函數、過程、觸發器……
關係型資料庫：簡單來說，以行列結構的形式，將資料庫中的資訊表示出來的對象，即二維表。
常見的關係型資料庫：Oracle&MySQL/DB2(IBM)/SQLServer……

1.2、Oracle資料庫伺服器組成

實例：理解為對象，看不見

資料庫：理解為類，看得見

使用實例（語句）操作資料庫

1.3、Oracle伺服器與orcl資料庫的關係

一個Oracle資料庫伺服器中包含多個資料庫，例如：orcl、orm、oa、bbs、erp等，在oradate目錄下，有多少個文件夾，就有多少個資料庫。我們向資料庫中存儲的所有數據，最終都會存放在對應的庫*.DBF文件中，以二進位壓縮形式存放

1.4、用戶登陸

超級管理員進入：

sqlplus / as sysdba

普通用戶進入：

sqlplus scott/tiger

使用超級管理員為scott用戶解鎖和設置密碼：

解鎖用戶：alter user scott account unlock;

解鎖後使用scott用戶登陸：connect scott/tiger;

設置密碼：alter user scott identified by tiger;

使用普通用戶修改密碼使用password命令

退出工具：exit

1.5、OracleSQL和Oracle的關係

第四代語言：SQL（結構化查詢語言，面向關係的）

第一代：機器語言

第二代：彙編語言

第三代：C/C++/C#/Java/VB……

第四代：SQL

SQL標準的四大分類：

DML（數據操縱語言）：select、insert、update、delete……

DDL（數據定義語言）：create table、alter table、drop table、truncate table……

DCL（數據控制語言）：grant 許可權 to scott、revoke 許可權 from scott……

事務控制語言：commit、rollback、rollback tosavepoint……

1.6、用戶與表空間

系統用戶：sys、system sys要以管理員的角色登陸

sysman 操作企業管理器

scott

登陸：[username/password] [@server] [as sysdba/sysoper]

切換用戶：connect sys/test sysdba;

查看登陸用戶：show user

數據字典：desc dba_users

啟用scott用戶：alter user scott account unlock;

設置密碼：alter user scott identified by tiger;

查看錶空間：dba_tablespaces/user_tablespaces

1.7、卸載Oracle

1．開始－>設置－>控制面板－>管理工具－>服務

停止所有Oracle服務。（運行命令：services.msc）

2．找到：C:appmacproduct11.2.0dbhome_1deinstall

執行：deinstall.bat文件，按照提示操作

3．刪除殘留文件夾

4．運行regedit，分別刪除以下位置的Oracle入口

HKEY_LOCAL_MACHINESOFTWAREORACLE

HKEY_LOCAL_MACHINESYSTEMControlSet001Services

HKEY_LOCAL_MACHINESYSTEMControlSet002Services

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplication

5．刪除環境變數CLASSPATH和PATH中有關Oracle的路徑

6．在C盤搜索Oracle，刪除相關文件

7．重啟電腦

2、Oracle實操

補充

以下程式碼是對emp表進行顯示做設置

col empno for 9999;

col ename for a10;

col job for a10;

col mgr for 9999;

col hiredate for a12;

col sal for 9999;

col comm for 9999;

col deptno for 99;

set pagesize 20;

col tname for a20;

set pagesize 80;

2.1、查詢

查看當前用戶

show user;

查詢scott用戶下所有的表：

select * from tab;

查看錶結構：

desc 表名;

查詢某表的所有數據：

select * from 表名;

或

select 欄位名,欄位名…… from 表名;

*號是通配符，表示該表中的所有欄位，但*號不能和具體的欄位一起使用，在SQL語句中，大小寫不敏感，但建議大寫。

別名

可以使用別名查詢，使用別名後顯示的欄位名為別名，

select empno "編號",ename "姓名」… from emp;

欄位名和別名中間可以使用AS或as，使用as之後，別名可以帶引號也可以不帶引號，如果不帶引號，別名中不能有空格，單引號表示字元串類型或日期類型；

設置表樣式，設置顯示寬度：

a) 字元型、日期型用a表示字元

column 列名 format a12; //a12表示佔12個字元

b) 數值型用9表示數字

column 列名 format 9999; //四個9表示佔4個字元

如果超過了設置的寬度會出現#號

c) 設置一頁顯示的記錄條數

set pagesize 80;

每次登陸為一次會話，exit命令為退出會話，當退出本次會話後，設置的格式都會被清除；

使用/杠，執行最近的一次SQL語句
清屏 host cls;
查詢表中不重複的記錄

select distinct 列名 from 表名;

查詢時對欄位做算術運算

select ename,sal,sal*12 "年薪" from emp; //年薪為別名

查詢時欄位為空

如果結果為null，在SQLplus客戶端中是不顯示的，解決null不顯示的問題，使用NVL()函數，NVL(a,b)：如果a為null，則用b代替；如果a為非null，則顯示a的值，對員工表計算年收入時，可以使用以下語句：

select enamel 「姓名」,sal*12+NVL(comm,0) 「年收入」 from emp;

使用dual啞表或者偽表，可以使用字元串連接符號||，在Oracle中必須使用from

select 『hello』 || 『world』「結果」 from dual;

顯示如下格式資訊：XXX的薪水是XX美元

select ename || 『的薪水是』 || sal || 『美元』 from emp;

使用sysdate顯示系統當前時間，在默認情況下，oracle只顯示日期，而不顯示時間，格式：04-2月-15

select sysdate from dual; 或

select sysdate "日期" from dual;

使用spool命令，保存SQL語句（和輸出結果）到硬碟的指定位置，並創建sql文件

spool e:/oracle-day01.sql;

使用spool off命令，保存SQL語句到硬碟文件e:/oracle-day01.sql。

使用@命令，將硬碟文件，如e:/crm.sql，讀到orcl實例中，並執行文件中的SQL語句

@ e:/crm.sql;

使用「–」符號，設置單行注釋

–select * from emp;

使用/**/符號，設置多行注釋

select * from

emp; */

SQL語句的特點

a) 是SQL92/99的ANSI官方標準，只要按照該標準來寫，在任何的關係型資料庫中都可以直接執行；

b) SQL語句的關鍵字不能簡寫，例如：select、where、from等；

c) 大小寫不敏感，提倡大寫；

d) 能夠對錶數據進行增刪改查操作；

e) 必須以分號結束；

f) 通常稱作語句；

SQLPLUS命令的特點

a) 是oracle自帶的一款工具，在該工具中執行的命令叫做SQLPLUS命令；

b) SQLPLUS工具的命令中關鍵字可以簡寫，例如：col enamel for a10;

c) 大小寫不敏感，提倡大寫；

d) 不能對錶數據進行增刪改查操作，只能完成顯示格式控制，例如：設置寬度、清屏、記錄執行結果等；

e) 可以不用分號結束，也可以用分號結束，建議都使用分號；

f) 通常稱作命令，是SQLPLUS工具中的命令；

2.2、where子句

查詢emp表中20號部門的員工資訊

select * from emp where deptno = 20;

查詢姓名是SMITH的員工，字元串使用』』，內容大小寫敏感

select * from emp where ename = 『SMITH』;

查詢1980年12月17日入職的員工，注意oracle默認日期格式（dd-mm-YY表示2位的年份）

select * from emp where hiredate = 』17-12月-80』;

查詢工資大於1500的員工

select * from emp where sal > 1500;

查詢工資不等於1500的員工【!=或<>】

select * from emp where sal <> 1500;

查詢薪水在1300到1600之間的員工，包括1300和1600

select * from emp where (sal>=1300) and (sal<=1600); 或

select * from emp where sal between 1300 and 1600;

查詢薪水不在1300到1600之間的員工，不包括1300和1600

select * from emp where sal not between 1300 and 1600;

查詢入職時間在「1981-2-20」到「1982-1-23」之間的員工

select * from emp where hiredate between 』20-2月-81』 and 』23-2月-82』; //日期小的在前，數字小的在前

查詢20號或30號部門的員工，例如：根據ID號，選中的員工，批量刪除

select * from emp where (deptno=20) or (deptno=30); 或

select * from emp where deptno in (20,30); //當條件不存在時不出錯

// in中如果是字元串類型的，只能填字元串類型的；如果是數字類型，可以填寫數字類型，也可以填寫能夠轉為數字的類型，如『30』

查詢不是20號或30號部門的員工

select * from emp where deptno not in (20,30);

查詢姓名以大寫字母S開頭的員工，使用%表示0個，1個或多個字元

select * from emp where ename like 『S%』; //不使用%等價於=精確查詢

//精確查詢用=符號，模糊查詢用like

查詢姓名以大寫字母N結束的員工

select * from emp where ename like 『%N』;

查詢姓名第一個字母是T，最後一個字母是R的員工

select * from emp where ename like 『T%R』;

查詢姓名是4個字元的員工，且第二個字元是I，使用_只能表示1個字元，不能表示0個或多個字元

select * from emp where ename like 『_ I _ _』;

插入一條姓名為』T_IM』的員工，薪水1200

insert into emp (empno,ename) values (1111,』T_IM』);

查詢員工姓名中含有『_』的員工，使用轉義符，讓其後的字元回歸本來的意思【like 『%_%』 escape 『』】

select * from emp where ename like '%_%' escape ''; //不一定使用斜杠

插入一個姓名叫』的員工

insert into emp (empno,ename) values (2222,''''); //兩個單引號表示1個單引號

插入一個姓名叫『』的員工

insert into emp (empno,ename) values (3333,'''''');

查詢所有員工資訊，使用%或%%

select * from emp ;

select * from emp where ename like 『%』;

select * from emp where ename like 『%_%』;

查詢傭金為null的員工

select * from emp where comm is null;

//null不能參與等號運算，null能參與number/date/varchar2類型運算

查詢傭金為非null的員工

select * from emp where comm is not null;

查詢無傭金且工資大於1500的員工

select * from emp where (comm is null) and (sal > 1500);

查詢工資是1500或3000或5000的員工

select * from emp where sal in (1500,3000,5000);

查詢職位是「MANAGER」或職位不是「ANALYST」的員工（方式一，使用!=或<>）

select * from emp where (job=』MANAGER』) or (job<>』ANALYST』); //性能優於方式二

查詢職位是「MANAGER」或職位不是「ANALYST」的員工（方式二，使用not）

select * from emp where (job=』MANAGER』) or (not(job=』ANALYST』));

2.3、order by子句

查詢員工資訊（編號，姓名，月薪，年薪），按月薪升序排序，如果月薪相同，按oracle內置的校驗規則排序

select empno,ename,sal,sal*12 from emp order by sal asc; 或

select empno,ename,sal,sal*12 from emp order by sal; //默認是升序排序

查詢員工資訊（編號，姓名，月薪，年薪），按月薪降序排序

select empno,ename,sal,sal*12 from emp order by sal desc;

查詢員工資訊，按入職日期降序排序，使用列名

select empno,ename,hiredate,sal*12 "年薪"

from emp order by hiredate desc;

order by後面可以跟列名、別名、表達式、列號（從1開始，在select子句中的列號）

select empno,ename,hiredate,sal*12 "年薪" from emp order by 「年薪」 desc;

select empno,ename,hiredate,sal*12 "年薪" from emp order by sal*12 desc;

select empno,ename,hiredate,sal*12 "年薪" from emp order by 4 desc;

查詢員工資訊，按傭金升序或降序排列，null值看成最大值

select * from emp order by comm desc;

查詢員工資訊，對有傭金的員工，按傭金降序排列，當order by和where同時出現時，order by在最後

select * from emp where comm is not null order by comm desc;

查詢員工資訊，按工資降序排列，相同工資的員工再按入職時間降序排列

select * from emp order by sal desc,hiredate desc;

查詢20號部門，且工資大於1500，按入職時間降序排列

select * from emp where (deptno=20) and (sal>1500)

order by hiredate desc;

2.4、單行函數

單行函數：只有一個參數輸入，只有一個結果輸出。

多行函數/分組函數：可以有多個參數輸入，只有一個結果輸出。

lower/upper/initcap函數，使用dual啞表

select lower(『HELLO』) from dual; //轉小寫

select upper(『hello』) from dual; //轉大寫

select initcap(『hello』) from dual; //首字母大寫

concat/substr函數

select concat(『hello』,』world』) from dual; //連接字元串，只能有兩個參數，連接多個使用管道符號

select 『hello』 || 『你好』 || 『世界』 from dual; 或

select concat(『hello』,concat(『你好』,』世界』)) from dual;

select substr(『hello你好』,5,3) from dual; //從第5個字元向後截取3個字元，從1開始，表示字元，不論中英文

length/lengthb函數

select length(『hello你好』) from dual; //返回字元串的長度

select lengthb(『hello你好』) from dual; //返回字元串的位元組長度編碼方式為UTF8/GBK，一個中文佔3/2個位元組，一個英文佔一個位元組

instr/lpad/rpad函數，

select instr(『helloworld』,』o』) from dual; //字元o從左向右找第一次出現的位置，從1開始，大小寫敏感，找不到返回0

select lpad(『hello』,10,』#』) from dual; //輸出長度為10，不足10位，向hello左邊補充#，長度小於字元串長度，輸出指定長度字元串（截取後的）

select rpad(『hello』,10,』#』) from dual; //向右邊補充#

trim/replace函數

select trim(『H』 from 『hello』) from dual; //清除字元串兩邊的H，返回清除後的字元串，大小寫敏感

select replace(『hello』,』l』,』L』) from dual; //把字元串中的l替換為L

round/trunc/mod函數作用於數值型

select round(3.1415,3) from dual; // 四捨五入保留3位小數

select trunc(3.1415,3) from dual; // 截取3位小數

select mod() from dual; //取余

round作用於日期型（month）

select round(sysdate,』month』) from dual; //四捨五入到月，15日之前日為1,15日之後，月進1，日為1

round作用於日期型（year）

select round(sysdate,』year』) from dual; //四捨五入到年，6月之前，月為1，日為1,6月之後，年進1，月為1，日為1

測試trunc作用於日期型（month）

select trunc(sysdate,』month』) from dual; //截取到月，日為1

測試trunc作用於日期型（year）

select trunc(sysdate,』year』) from dual; //截取到年，月為1，日為1

顯示昨天，今天，明天的日期，日期類型 +/- 數值 = 日期類型

select sysdate-1 「昨天」,sysdate 「今天」,sysdate+1 「明天」 from dual;

以年和月形式顯示員工近似工齡，日期-日期=數值

select ename 「姓名」,sysdate – hiredate 「入職天數」 from emp;

select ename "姓名",round(sysdate – hiredate) "入職天數" from emp;

select ename "姓名",round(sysdate – hiredate,0) "入職天數" from emp;

使用months_between函數，精確計算到年底還有多少個月

select months_between(』31-12月-17』,sysdate) from dual; //大日期在前

使用months_between函數，以精確月形式顯示員工工齡

select ename 「姓名」,

months_between(sysdate,hiredate) 「入職月數」 from emp;

測試add_months函數，下個月今天是多少號

select add_months(sysdate,1) from dual;

測試add_months函數，上個月今天是多少號

select add_months(sysdate,-1) from dual;

測試next_day函數

a) 從今天開始算，下一個星期三是多少號

select next_day(sysdate,』星期三』) from dual;

b) 從今天開始算，下下個星期三是多少號

select next_day(next_day(sysdate,』星期三』),』星期三』) from dual;

c) 從今天開始算，下一個星期三的下一個星期日是多少號

select next_day(next_day(sysdate,』星期三』),』星期日』) from dual;

測試last_day函數

d) 本月最後一天是多少號

select last_day(sysdate) from dual;

e) 本月倒數第二天是多少號

select last_day(sysdate)-1 from dual;

f) 下一個月最後一天是多少號

select last_day(add_months(sysdate,1)) from dual;

g) 上一個月最後一天是多少號

select last_day(add_months(sysdate,-1)) from dual;

注意：

日期 – 日期 = 天數

日期 +/- 天數 = 日期

2.5、三大類型轉換

隱式轉換：

varchar2 à number，例如：』123』 à 123

varchar2 à date，例如：』25-4月-15』 à 』25-4月-15』

number à varchar2，例如：123 à 『123』

date à varchar2，例如：』25-4月-15』 à 』25-4月-15』

oracle如何隱式轉換：

a) =號兩邊的類型是否相同

b) 如果=號兩邊的類型不同，嘗試的去做轉換

c) 在轉換時，要確保合法合理，否則轉換會失敗，例如：12月不會有32天，一年中不會有13月

查詢1980年12月17日入職的員工（方式一：日期隱式轉換）

select * from emp where hiredate = 』17-12月-80』;

日期格式的元素：

yyyy 表示年份

mm 表示月份，08

month 表示月的全稱，8月

day 表示星期幾

dd 表示日，02

時間格式 HH24:MI:SS 24小時制，12小時制在後面添加 AM/PM

使用to_char（日期，』格」常量」式』）函數將日期轉為字元串，顯示如下格式：2015年04月25日星期六

select to_char(sysdate,'yyyy"年"mm"月"dd"日" day') from dual;

使用to_char(日期，『格式』)函數將日期轉為字元串，顯示格式：2015-04-25今天是星期六 15:15:15

select to_char(sysdate,'yyyy-mm-dd"今天是"day hh24:mi:ss') from dual; 或

select to_char(sysdate,'yyyy-mm-dd"今天是"day hh12:mi:ss pm') from dual;

使用to_char(數值，『格式』)函數將數值轉為字元串，顯示格式：$1,234.00

select to_char(1234,'$9,999.00') from dual;

使用to_char(數值，『格式』)函數將數值轉為字元串，顯示格式：￥1234.00

select to_char(1234,'L9999.00') from dual;

使用to_date(『字元串』，『格式』)函數，查詢1980年12月17日入職的員工（方式二：日期顯示轉換）

select * from emp where

hiredate=to_date('1980年12月17日','yyyy"年"mm"月"dd"日"');

或

select * from emp where hiredate=to_date('1980-12-17','yyyy"-"mm"-"dd');

使用to_number(『字元串』)函數將字元串『123』轉為數字123

select to_number('123')+123 from dual; //顯示轉換，結果為246

select '123'+123 from dual; //隱式轉換，結果為246

select 『123』 || 123 from dual; // ||符號為拼接符，結果為123123

2.6、通用函數和條件判斷

使用NVL(a,b)通用函數，統計員工年收入，NVL()作用於任何類型，即(number/varchar2/date)，通用函數：參數類型可以是number或varchar2或date類型

select ename,sal*12+NVL(comm,0) from emp;

使用NVL2(a,b,c)通用函數，如果a不為NULL，取b值，否則取c值，統計員工年收入

select ename,sal*12+NVL2(comm,comm,0) from emp;

使用NULLIF(a,b)通用函數，在類型一致的情況下，如果a與b相同，返回NULL，否則返回a，比較10和10.0是否相同

select NULLIF(10,10.0) from dual; //結果為null，在SQLPLUS中NULL不顯示

使用SQL99標準通用語法中的case表達式，將職位是分析員的，工資+1000；職位是經理的，工資+800；職位是其它的，工資+400

case 欄位

when 條件 then 表達式1

when 條件 then 表達式2

else 表達式n

end

select ename 「姓名」,job 「職位」,sal 「原工資」,

case job

when 『ANALYST』 then sal+1000

when 『MANAGER』 then sal+800

else sal+400

end 「漲後工資」

from emp;

使用oracle專用語法中的decode()函數，職位是分析員的，工資+1000；職位是經理的，工資+800；職位是其它的，工資+400

decode(欄位,條件1,表達式1,條件2,表達式2,……表達式n)

select ename 「姓名」,job 「職位」,sal 「原工資」,

decode(job,』ANALYST』,sal+1000,』MANAGER』,sal+800,sal+400)

「漲後工資」

from emp;

2.7、多行函數

統計emp表中員工總人數

select count(*) from emp; //*號適用於表欄位較少的情況下，如果欄位多，掃描效率低，建議使用非NULL的欄位，一般用主鍵

統計公司有多少個不重複的部門

select count(distinct deptno) from emp;

統計有傭金的員工人數

select count(comm) from emp; //多行函數不統計NULL值

員工總工資，平均工資，四捨五入，保留小數點後0位

select sum(sal) "總工資",avg(sal) "平均工資" from emp; //未保留指定小數

select sum(sal) "總工資",round(avg(sal),0) "平均工資" from emp;//保留小數

查詢員工表中最高工資，最低工資

select max(sal) "最高工資",min(sal) "最低工資" from emp;

入職最早，入職最晚員工

select min(hiredate) "入職最早",max(hiredate) "入職最晚" from emp;

2.8、分組函數

按部門求出該部門平均工資，且平均工資取整數，採用截斷

select deptno "部門編號",trunc(avg(sal),0) "部門平均工資"

from emp group by deptno;

（繼續）查詢部門平均工資大於2000的部門

select deptno 「部門編號」,trunc(avg(sal),0) 「部門平均工資」

from emp group by deptno having trunc(avg(sal),0) > 2000;

（繼續）按部門平均工資降序排列

select deptno "部門編號",trunc(avg(sal),0) "部門平均工資"

from emp group by deptno

having trunc(avg(sal),0) > 2000

order by 2 desc;

除10號部門外，查詢部門平均工資大於2000元的部門，方式一【having depton <> 10】

select deptno,avg(sal) from emp group by deptno having deptno <> 10;// 先分組後排出，執行效率低

除10號部門外，查詢部門平均工資大於2000元的部門，方式二【where deptno <> 10】

select deptno,avg(sal) from emp where deptno <> 10 group by deptno; //先排除後分組，執行效率高

顯示部門平均工資的最大值

select max(avg(sal)) "結果" from emp group by deptno;

思考：顯示部門平均工資最大的值和該部門的編號
總結：

group by子句的細節：

a) 在select子句中出現的非多行函數的所有列，必須出現在group by子句中；

b) 在group by子句中出現的所有列，可出現可不出現在select子句中；

where和having的區別：

where：

a) 行過濾器；

b) 針對原始的記錄；

c) 跟在from後面；

d) where可省；

e) 先執行；

having：

a) 組過濾器；

b) 針對分組後的記錄；

c) 跟在group by後面；

d) having可省；

e) 後執行；

單引號出現的地方：

a) 字元串，例如：』hello』

b) 日期型，例如：』25-3月-15』

c) to_char/to_date(日期,』yyyy-mm-dd day』)

雙引號出現的地方：

a) 列別名，例如：select ename 「姓名」 from emp;

b) to_char/to_date(日期,』yyyy」年」mm」月」dd」日」』);

2.9、多表查詢

員工表emp和部門表的笛卡爾集（笛卡爾集表=列數之和，行數之積，笛卡爾集表內中有些數據是不符合要求的）

select * from emp,dept; 或

select emp.*,dept.* from emp,dept; 或

select emp.ename,dept.dname from emp,dept;

使用等值連接/內連接（只能使用=號），顯示員工的編號，姓名，部門名

select emp.empno,emp.ename,dept.dname,dept.deptno

from emp,dept where emp.deptno = dept.deptno;

使用非等值連接/內連接查詢（不能使用=號，其他符號可以，例如：>=，<=，<>，between and等），顯示員工的編號，姓名，月薪，工資級別

select e.empno "編號",e.ename "姓名",e.sal "月薪",s.grade "工資級別"

from emp e,salgrade s where e.sal between s.losal and s.hisal;

// 使用表別名簡化

使用外連接，按部門10,20,30,40號，統計各部門員工人數，要求顯示部門號，部門名，人數

a) 左外連接查詢：

select dept.deptno "部門號",dept.dname "部門名",count(emp.empno) "人數"

from dept,emp

where dept.deptno = emp.deptno (+)

group by dept.deptno,dept.dname;

a) 右外連接查詢：

select dept.deptno "部門號",dept.dname "部門名",count(emp.empno) "人數"

from dept,emp

where emp.deptno (+) = dept.deptno

group by dept.deptno,dept.dname;

使用左外連接，按部門10,20,30,40號，統計各部門員工人數，要求顯示部門號，部門名，人數，且按人數降序排列

select dept.deptno "部門號",dept.dname "部門名",count(emp.empno) "人數"

from dept,emp

where dept.deptno = emp.deptno (+)

group by dept.deptno,dept.dname

order by count(emp.empno) desc;

總結：

a) 等值連接/非等值連接/內連接：只會查詢出多張表中，根據某個欄位匹配，符合條件的記錄，不符合條件的記錄是不會存在的；

b) 外連接查詢：既能查詢出符合條件的記錄，也能根據一方強行將另一方查詢出來

自連接查詢（針對單張表查詢）

a) 使用自連接，顯示」SMITH的上級是FORD」這種格式

select users.ename || '的上級是' || boss.ename

from emp users,emp boss

where users.mgr = boss.empno;

b) 基於上述問題，將KING的上級是」」顯示出來

select users.ename || '的上級是' || boss.ename

from emp users,emp boss

where users.mgr = boss.empno (+);

2.10、子查詢

子查詢的作用：查詢條件未知的事物

查詢條件已知問題，例如：查詢工資為800的員工資訊

查詢條件未知的問題，例如：查詢工資為20號部門平均工資的員工資訊

一個條件未知的問題，可以分解為多個條件已知的問題

查詢工資比WARD高的員工資訊

select * from emp

where sal > (

select sal from emp where ename = 'WARD'

);

查詢部門名為『SALES』的員工資訊（使用子查詢）

select * from emp

where deptno = (

select deptno from dept where dname = 'SALES'

);

子查詢細節：

ü 子查詢與父查詢可以針對同一張表；

ü 子查詢和父查詢可以針對不同張表；

ü 子查詢與父查詢在傳統參數時，數量要相同

ü 父查詢的條件要與子查詢的結果，在參數數量、類型、含義上要相同

查詢部門名為『SALES』的員工資訊（多表查詢）

select emp.* from dept,emp

where (dept.deptno=emp.deptno) and (dept.dname='SALES');

查詢每個員工編號，姓名，部門名，工資等級（三表查詢，三張表無外鍵關聯）

select emp.empno "編號",emp.ename "姓名",

dept.dname "部門名",salgrade.grade "工資等級"

from emp,dept,salgrade

where (emp.deptno = dept.deptno)

and (emp.sal between salgrade.losal and salgrade.hisal);

查詢工資最低的員工資訊（單行子查詢，使用=號）

select * from emp where sal = (

select min(sal) from emp

);

單行子查詢：子查詢只返回一個結果，例如800，父查詢用=/<>/>=/<=來比較

多行子查詢：子查詢會返回多個結果，例如20,30，父查詢用in/any/all來比較

查詢部門名為『ACCOUNTING』或『SALES』的員工資訊（多行子查詢，使用in關鍵字）

select * from emp

where deptno in(

select deptno from dept where dname in ('ACCOUNTING','SALES')

);

查詢工資比20號部門【任意any】一個員工工資【低<】的員工資訊（多行子查詢，使用any關鍵字）

select * from emp

where sal <any (

select sal from emp where deptno = 20

); //在oracle看來，<any就等同於<集合中最大的值

查詢工資比30號部門【所有all】員工【低<】的員工資訊（多行子查詢，使用all關鍵字）

select * from emp

where sal <all (

select sal from emp where deptno = 30

);

當多表查詢、子查詢同時能解決問題時，優先選擇多表查詢，但是在某些情況下，只能用子查詢，例如Oracle分頁。

2.11、集合查詢

使用並集運算，查詢20號部門或30號部門的員工資訊

select * from emp where deptno = 20

union

select * from emp where deptno = 30

//union兩個集合中，如果都有相同的，取其一；union all兩個集合中，如果都有相同的，都取

Oracle執行時間操作

set time on 打開時間

set time off 關閉時間

set timing on 顯示當前命令執行的時間

set timing off 關閉執行命令時間的顯示

使用交集運算[intersect]，查詢工資在1000-2000和1500-2500之間的員工資訊（方式一）

select * from emp where sal between 1000 and 2000

intersect

select * from emp where sal between 1500 and 2500;

用where行過濾，查詢工資在1000-2000和1500-2500之間的員工資訊（方式二）

select * from emp

where (sal between 1000 and 2000)

and (sal between 1500 and 2500);

使用差集運算[minus]，查詢工資在1000-2000，但不在1500-2500之間的員工資訊（方式一）

select * from emp where sal between 1000 and 2000

minus

select * from emp where sal between 1500 and 2500;

使用where行過濾，查詢工資在1000-2000，但不在1500-2500之間的員工資訊（方式二）

select * from emp

where (sal between 1000 and 2000) and

(sal not between 1500 and 2500);

集合查詢的細節：

a) 集合操作時，必須確保集合列數是相等；

b) 集合操作時，必須確保集合列類型對應相等

c) A union B union C = C union B union A，如果有重複，去重複

d) 當多個集合操作時，結果的列名由第一個集合列名決定

當多表查詢、子查詢、集合查詢都能完成同樣的任務時，優先選擇：

多表查詢 à 子查詢 à 集合查詢（優先順序高 à 低）

2.12、Oracle分頁

MySQL分頁語法：

select * from 表名 limit 0,2;

查詢users表中前兩條記錄

select * from users limit 0,2; 或

select * from users limit 2;

//0表示第一條記錄的索引號，索引號從0開始

2表示最多選取2個記錄

查詢出users前三條記錄

select * from users limit 0,3; 或

select * from users limit 3;

B . Oracle分頁

a) 在Oracle中使用rownum，是oracle中特有的關鍵字

b) rownum與表在一起，表亡它亡，表在它在

c) rownum在默認情況下，從表中是查不出來的

d) 只有在select子句中，明確寫出rownum才能顯示出來

e) rownum是number類型，且唯一連續

f) rownum最小值是1，最大值與記錄條數相同

g) rownum也能參與關係運算

rownum = 1 有值

rownum < 5 有值

rownum <= 5 有值

rownum > 2 無值

rownum >= 2 無值

rownum <> 2 有值與 rownum < 2 相同

rownum = 2 無值

h) 基於rownum的特性，我們通常rownum只用於<或<=關係運算

顯示emp表中3-8條記錄（方式一：使用集合減運算）

select rownum,emp.* from emp where rownum <= 8

minus

select rownum,emp.* from emp where rownum <= 2;

顯示emp表中3-8條記錄（方式二：使用子查詢，在from子句中使用，重點）

select * from

(select rownum id,emp.* from emp where rownum <= 8)

where id > 2; // id是子查詢中的別名，不可以加」」雙引號

或

select users.* from

(select rownum id,emp.* from emp where rownum <= 8) users

where id > 2; //users是表的別名

顯示emp表中5-9條記錄

select * from

(select rownum id,emp.* from emp where rownum <= 9)

where id > 4;

2.13、操作表

創建用戶表users（id整型/name字元串/birthday日期/sal整型，默認今天）

create table users(

id number(5) primary key, //主鍵

name varchar2(8) not null unique, //非空，唯一

sal number(6,2) not null,

birthday date default sysdate // 默認

);

刪除表，進入回收站

drop table users;

查詢回收站中的對象

show recyclebin;

閃回，即將回收站還原

flashback table 表名 to before drop;

flashback table 表名 to before drop rename to 新表名;

徹底刪除users表

drop table users purge;

清空回收站

purge recyclebin;

為emp表增加image列，alter table 表名 add 列名類型(寬度)

修改ename列的長度為20個字元，alter table 表名 modify 列名類型(寬度)

刪除image 列，alter table 表名 drop column 列名

重名列名ename為username，alter table 表名 rename column 原列名 to 新列名

將emp表重命名emps，rename 原表名 to 新表名

注意：修改表時，不會影響表中原有的數據

三、JDBC基礎

1、什麼是JDBC

JDBC（Java DataBase Connectivity）就是Java資料庫連接，簡單來說，就是用Java語言來操作資料庫。

2、JDBC原理

早期Sun公司想編寫一套可以連接世界上所有資料庫的API，但是當他們剛開始就發現這是一個不可能完成的任務，因為各個廠商的資料庫伺服器差異太大了。後來Sun開始與資料庫廠商們討論，最終得出的結論是，由Sun提供一套訪問資料庫的規範（就是一組介面），並提供連接資料庫的協議標準，然後各個資料庫廠商會遵循Sun的規範提供一套訪問自己公司的資料庫伺服器的API。Sun提供的規範命名為JDBC，而各個廠商提供的，遵循了JDBC規範的，可以訪問自己資料庫的API被稱為驅動。

3、JDBC的實現

3.1、創建資料庫連接

先導入mysql的jdbc驅動jar包：

創建資料庫連接類

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class JDBCDemo { public static void main(String[] args) throws ClassNotFoundException, SQLException { /* * 動態載入驅動 */ Class.forName("com.mysql.jdbc.Driver"); /* * 聲明連接資料庫的配置資訊 */ //連接mysql的url String url = "jdbc:mysql://localhost:3306/test"; //登錄mysql的用戶名 String user = "root"; //登錄mysql的密碼 String password = "root"; /* * 獲得資料庫連接 */ Connection conn = DriverManager.getConnection(url, user, password); } }

3.2、執行SQL語句

/* * 創建聲明 */ Statement stmt = conn.createStatement(); //創建表格 String sql = "create table stu(" + "id int primary key auto_increment," + "name varchar(20) not null," + "age int(2)," + "sex varchar(4)"+ ")"; boolean rel = stmt.execute(sql); System.out.println(rel);
//執行添加 String sql = "insert into stu values(null,'張三',20,'男') "; boolean rel = stmt.execute(sql); System.out.println(rel);
//執行查詢 ResultSet rs = stmt.executeQuery("select * from stu"); while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); int age = rs.getInt("age"); String sex = rs.getString("sex"); }
//關閉資源 rs.close(); stmt.close(); conn.close();

3.3程式碼的規範化

所謂程式碼規範化就是無論是否出現異常，都要關閉ResultSet、Statement，以及Connection。

public class JDBCDemo { private String url = "jdbc:mysql://localhost:3306/test"; private String user = "root"; private String password = "root"; /* * 獲取資料庫連接的方法 */ public Connection getConnection() throws Exception{ Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(url, user, password); } /* * 查詢的方法 */ public void query(){ Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = getConnection(); stmt = conn.createStatement(); String sql = "select * from stu"; rs = stmt.executeQuery(sql); while(rs.next()){ String name = rs.getString("name"); String pwd = rs.getString("pwd"); } } catch (Exception e) { e.printStackTrace(); } finally { try { if(conn!=null) conn.close(); if(stmt!=null) stmt.close(); if(rs!=null) rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } }

4、JDBC主要對象介紹

4.1、DriverManager

我們只需要會用DriverManager的getConnection()方法即可：

1. Class.forName("com.mysql.jdbc.Driver"); 2. String url = "jdbc:mysql://localhost:3306/test"; 3. String user = "root"; 4. String pwd= "root"; 5. Connection conn = DriverManager.getConnection(url, user, pwd);

上面的程式碼可能出現的兩種異常：

ClassNotFoundException：這個異常是在第1句上出現的，異常原因是沒有找到對應的類，出現這個異常有兩種可能：

l 你沒有給出mysql的jar包；

l 你把類名稱打錯了，查看類名是不是com.mysql.jdbc.Driver;

SQLException：這個異常出現在第5句，出現這個異常的原因是三個參數內容有誤，重點看一下URL是否書寫錯誤。

4.2、Connection

l Connection最為重要的方法就是獲取Statement：

Statement stmt = conn.createStatement();

l 在學習ResultSet方法時，還需要學習一下的方法：

Statement stmt = conn.createStatement(int,int);

其中的兩個參數是用來確定創建的Statement能生成什麼樣的結果集。

4.3、Statement

Statement最為重要的方法是：

l int executeUpdate(String sql)：可以執行DDL和DML語句，即可執行insert、update、delete語句和create table、alter table、drop table等語句，返回成功執行的記錄數；

l ResultSet executeQuery(String sql)：執行DQL查詢語句，執行查詢操作會返回ResultSet結果集。

l boolean execute()：可以執行前兩個方法能執行的SQL語句，該方法用來執行增、刪、改、查所有的SQL語句，返回值為boolean類型，表示執行的SQL語句是否有結果。

² 如果使用execute()方法執行的是更新語句，那麼還要調用int getUpdateCount()來獲取insert、update、delete語句所影響的行數；

² 如果使用execute()方法執行的是查詢語句，那麼還要調用ResultSet getResultSet()來獲取select語句的查詢結果。

4.4、ResultSet之滾動結果集

ResultSet表示結果集，它是一個二維的表格。ResultSet內部維護一個行游標（游標），游標位置從1開始。Result提供了一系列的方法來移動游標：

l 移動游標的方法：

² void beforeFirst()：把游標放到第一行的前面，這也是游標默認的位置；

² void afterLast()：把游標放到最後一行的後面；

² boolean first()：把游標放到第一行的位置上，返回值表示調控游標是否成功；

² boolean last()：把游標放到最後一行的位置上；

² boolean previous()：把游標向上挪一行；

² boolean next()：把游標向下挪一行；

² boolean relative(int row)：相對位移，當row為正數時，表示向下移動row行，為負數時表示向上移動row行；

² boolean absolute(int row)：絕對位移，把游標移動到指定的行上；

l 判斷游標的位置方法：

² boolean isBeforeFirst()：當前游標位置是否在第一行前面；

² boolean isAfterLast()：當前游標位置是否在最後一行的後面；

² boolean isFirst()：當前游標位置是否在第一行上；

² boolean isLast()：當前游標位置是否在最後一行上；

² int getRow()：返回當前游標所在位置；

l 獲取當前結果集的總行數：

² 先執行rs.last();把游標移動到最後一行，在執行rs.getRow();獲得當前游標所在行，可以得到結果集一共有多少行；

l 獲取結果集的總列數：

² 先獲取結果集的元數據 ResultSetMetaData rsmd = rs.getMetaData();

² 獲取結果集列數：int len = rsmd.getColumnCount();

² 獲取指定列的列名：String name = rsmd.getColumnName(int colIndex);

/** * 動態獲取結果 */ rs = stmt.executeQuery(sql); //獲得列數 int len = rs.getMetaData().getColumnCount(); while(rs.next()){//遍歷行 //遍歷列 for (int i = 0; i < len; i++) { System.out.print(rs.getString(i)); if(i<len){ System.out.print(","); } } System.out.println("");//換行 }

結果集的特性：

² 是否可滾動

² 是否敏感

² 是否可更新

當使用Connection的createStatement()方法創建Statement對象時，就已經決定了Statement生成的結果集是什麼特性。

使用conn.createStatement()方法生成的結果集不可滾動、不敏感、不可更新，

如果結果集是不可滾動的，那麼只能使用next()方法來移動游標，表示返回下一行。二beforeFirst()、afterLast()、first()、last()、previous()、relative()等方法都不能使用。

如果想要結果集支援滾動，要使用

createStatement(int resultSetType,int resultSetConcurrency)方法來創建對象，其中兩個參數分別表示結果集的類型和並發類型。

resultSetType的可選值：

² ResultSet.TYPE_FORWARD_ONLY：不滾動結果集；（MySQL默認值，但是支援滾動）

² ResultSet.TYPE_SCROLL_INSENSITIVE：滾動結果集，不敏感；

² ResultSet.TYPE_SCROLL_SENSITIVE：滾動結果集，敏感；（沒有資料庫支援）

esultSetConcurrency的可選值：

² CONCUR_READ_ONLY：結果集是只讀的，不能通過修改結果集二反向影響資料庫；

² CONCUR_UPDATABLE：結果集是可更新的，對結果集的更新可以反向影響資料庫；

4.5、ResultSet之獲取列數據

可以通過next()方法使ResultSet的游標向下移動，當游標移動到你需要的行時，就需要來獲取該行的數據了，ResultSet提供了一系列的獲取列數據的方法：

² String getString(int columnIndex)：獲取指定列的String類型數據；

² int getInt(int columnIndex)：獲取指定列的int類型數據；

² double getDouble(int columnIndex)：獲取指定列的double類型數據；

² boolean getBoolean(int columnIndex)：獲取指定的boolean類型數據；

² Object getObject(int columnIndex)：獲取指定列的Object類型的數據

5、PreparedStatement

5.1、什麼是SQL攻擊

在需要用戶輸入的地方，用戶輸入的是SQL語句的片段，最終用戶輸入的SQL片段與我們DAO中寫的SQL語句合成一個完整的SQL語句。例如用戶在登錄時輸入的用戶名和密碼都是為SQL語句的片段。

5.2、演示SQL攻擊

5.3、PreparedStatement用法

PreparedStatement是Statement介面的子介面，

其特點是：

防SQL攻擊；
提高程式碼的可讀性、可維護性；
提高效率；

用法：

聲明SQL模板；
調用Connection的preparedStatement(String sql)方法；
調用pstmt的setXXX()系列方法，為SQL模板中的「?」佔位符賦值；
調用pstmt的executeUpdate()或executeQuery()方法，這些方法都沒有參數；

6、JDBCUtils工具類

6.1、JDBCUtils的作用

連接資料庫的四大參數是：驅動類、URL、用戶名、密碼，這些參數都是與特定資料庫關聯，如果將來想要更改資料庫，那麼就要去修改這四大參數，那麼為了不去修改程式碼，我們需要寫一個JDBCUtils類，讓它從配置文件中讀取配置參數，然後創建連接對象。

6.2、JDBCUtils類的實現

1、創建.properties配置文件

2、編寫配置文件的鍵值

driver=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/test user=root password=root

3、編寫JDBCUtils.java類

public class JDBCUtil { private static Properties prop = null; private static Connection conn = null; static { try { // 讀取配置文件 InputStream is = JDBCUtil.class.getClassLoader() .getResourceAsStream("dbconfig.properties"); prop = new Properties(); prop.load(is); //動態載入驅動 Class.forName(prop.getProperty("driver")); //獲取資料庫連接對象 conn = DriverManager.getConnection(prop.getProperty("user"), prop.getProperty("user"), prop.getProperty("password")); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConnection(){ return conn; } }

7、UserDao

7.1、DAO模型

DAO（Data Access Object）模型就是寫一個類，把訪問資料庫的程式碼封裝起來，DAO在資料庫與業務邏輯（Service）之間。

l 實體域，即操作的對象，例如我們操作的表是user表，那麼就需要先寫一個User類；

l DAO模式需要先提供一個DAO介面；

l 然後再提供一個DAO介面的實現類；

l 再編寫一個DAO工廠，Service通過工廠來獲取DAO實現。

7.2、面向介面編程的程式碼實現

1、UserDAO介面

public interface UserDao { //登錄方法 public boolean login(Object obj); //註冊方法 public boolean reg(Object obj); }

2、創建UserDaoImpl類實現UserDao介面

public class UserDaoImpl implements UserDao { public boolean login(Object obj) { //對資料庫操作的方法 //….. return false; } public boolean reg(Object obj) { //對資料庫操作的方法 //….. return false; } }

3、創建UserDao.properties配置文件

配置資訊：

UserDao=com.jdbc.demo.UserDao

4、創建UserDaoFactory.java類，用來創建UserDao對象

public class UserDaoFactory { private static Properties prop = null; static{ try { //載入配置文件 InputStream is = UserDaoFactory.class.getClassLoader().getResourceAsStream("UserDao.properties"); prop = new Properties(); prop.load(is); } catch (IOException e) { e.printStackTrace(); } } public static UserDao getUserDao(){ try { //通過反射獲取UserDao對象 Class c = Class.forName(prop.getProperty("UserDao")); return (UserDao)c.newInstance(); } catch (Exception e) { e.printStackTrace(); } return null; } }

5、在UserService.java業務邏輯層，通過UserDaoFactory獲取UserDao對象

public class UserService { //通過UserDaoFactory獲取UserDao對象 private UserDao userDao = UserDaoFactory.getUserDao(); //登錄方法 public boolean login(Object obj){ return userDao.login(obj); } //註冊方法 public boolean reg(Object obj){ return userDao.reg(obj); } }

8、時間類型

8.1、時間類型的問題

資料庫類型與Java中類型的對應關係如下：

l DATE –> java.sql.Date

l TIME –> java.sql.Time

l TIMESTAMP –> java.sql.Timestamp

需要注意的是：

l 領域對象（例如實體類User）中的所有屬性不能出現java.sql包下的東西，即不能使用java.sql.Date；

l ResultSet.getDate()方法返回的是java.sql.Date；

l PreparedStatement.setDate(int,Date)方法中第二個參數也是java.sql.Date；

那麼我們就面臨這兩次時間類型轉換的問題，一次是存數據，一次的取數據，要完成以下轉換：

l java.util.Date –> java.sql.Date/Time/Timestamp

l java.sql.Date/Time/Timestamp –> java.util.Date

8.2、時間類型的轉換

因為java.util.Date是java.sql.Date/Time/Timestamp的父類，所以我們可以使用以下方式完成轉換：

l 從資料庫取Date值是可以完成自動類型轉換；

l 往資料庫中存值時，先把java.util.Date轉為毫秒數，通過java.sql.Date(long Date)的構造方法，傳入一個時間戳的方式完成類型轉換；

9、批處理

9.1、Statement批處理

批處理就是一批一批的處理，而不是一個一個的處理。當有10條SQL語句要執行時，一次向伺服器發送一條SQL語句，這麼做效率很差，處理的方案是使用批處理，即一次向伺服器發送多條SQL語句，然後由伺服器一次性處理。

批處理只針對更新（增、刪、改）語句，批處理不包含查詢。

可以多次調用Statement類的addBatch(String sql)方法，把需要執行的所有SQL語句添加到一個「批」中，然後調用Statement類的executeBatch()方法來執行當前「批」中的語句。

l void addBatch(String sql)：添加一條語句到「批」中；

l int[] executeBatch()：執行「批」中所有語句，返回值表示每條語句所影響的行數據；

l void clearBatch()：清空「批」中的所有語句。

9.2、PreparedStatement批處理

PreparedStatement的批處理有所不同，因為每個PreparedStatement對象都綁定一條SQL模板。所以想PreparedStatement中添加的不是SQL語句，而是「?」賦值。

9.3、執行批處理

1、打開MySQL批處理，在連接資料庫的url後面添加參數

jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

2、編寫批處理程式碼

public void add() throws SQLException{ //獲得資料庫連接 Connection conn = JDBCUtil.getConnection(); String sql = "insert into stu(id,name,sex) values(?,?,?)"; PreparedStatement pstm = conn.prepareStatement(sql); //批量添加 for (int i = 1; i <= 10000; i++) { pstm.setInt(1, i); pstm.setString(2, "name_"+i); pstm.setString(3, i%2==0?"男":"女"); //添加批處理，把一組數據添加到集合中 pstm.addBatch(); } //執行批處理 pstm.executeBatch(); }

四、JDBC事務

1、事務概述

1.1、什麼是事務

以銀行轉賬為例，張三轉1000元到李四的賬戶，其完成轉賬過程需要執行兩條SQL語句：

l 給張三的賬戶減去1000元；

l 給李四的賬戶加上1000元。

如果在第一條SQL語句執行成功後，在執行第二條SQL語句之前，程式被中斷了（拋出異常或其他原因），那麼李四的賬戶上沒有加上1000元，而張三卻減去1000元，這樣就造成了很大的安全漏洞。

事務簡單來說，就是在多個操作中，要麼完全成功，要麼完全失敗，不可能出現只成功一半的情況。

1.2、事務的四大特性（ACID）

事務的四大特性是：

l 原子性（Atomicity）：事務中所有操作是不可再分割的原子單位。事務中所有操作要麼全部執行成功，要麼全部執行失敗。

l 一致性（Consistency）：事務執行後，資料庫狀態與其他業務規則保持一致。如轉賬業務，無論事務執行成功與否，參與轉賬的兩個帳號餘額之和應該是不變的。

l 隔離性（Isolation）：隔離性是指在並發操作中，不同事務之間應該隔離開來，使每個並發中的事務不會相互干擾。

l 持久性（Durability）：一旦事務提交成功，事務中所有的數據操作都必須被持久化到資料庫中，即使提交事務後，數據馬上崩潰，在資料庫重啟時，也必須能保證通過某種機制恢複數據。

1.3、MySQL中的事務

在默認情況下，MySQL每執行一條SQL語句，都是一個單獨的事務。如果需要在一個事務中包含多條SQL語句，那麼需要開啟事務和結束事務。

l 開啟事務：start transaction；

l 結束事務：commit或rollback；

在執行SQL語句之前，先執行strat transaction，這就開啟了一個事務（事務的起點），然後可以去執行多條SQL語句，最後要結束事務，commit表示提交，即事務中的多條SQL語句所做出的影響會持久化到資料庫中。或者rollback，表示回滾，即回滾到事務的起點，之前做的所有操作都被撤銷了。

以轉賬為例，演示程式碼如下：

START TRANSACTION; UPDATE account SET balance=balance-1000 WHERE id=1; UPDATE account SET balance=balance+1000 WHERE id=2; ROLLBACK; #回滾結束，事務執行失敗
START TRANSACTION; UPDATE account SET balance=balance-1000 WHERE id=1; UPDATE account SET balance=balance+1000 WHERE id=2; COMMIT; #提交結束，事務執行成功
START TRANSACTION; UPDATE account SET balance=balance-1000 WHERE id=1; UPDATE account SET balance=balance+1000 WHERE id=2; quit; #退出，MySQL會自動回滾事務

2、JDBC事務

在jdbc中處理事務，都是通過Connection完成的, 同一事務中所有的操作，都在使用同一個Connection對象。

Connection的三個方法與事務相關：

l setAutoCommit（boolean）：設置是否為自動提交事務，如果true（默認值就是true）表示自動提交，也就是每條執行的SQL語句都是一個單獨的事務，如果設置false，那麼就相當於開啟了事務了，conn.setAutoCommit(false)表示開啟事務；

l commit()：提交結束事務，conn.commit()表示提交事務；

l rollback()：回滾結束事務，conn.rollback()表示回滾事務；

JDBC處理事務的程式碼格式：

Connection conn = JDBCUtil.getConnection(); try{ conn.setAutoCommit(false); //開啟事務 //執行程式碼 conn.commit();//提交事務 }catch(Exception e){ conn.rollback();//回滾 }

3、事務隔離級別

當多個事務並發執行時才需要考慮並發事務的處理。

3.1、事務的並發讀問題

l 臟讀：讀取到另一個事務未提交數據；

l 不可重複讀：兩次讀取不一致；

l 幻讀（虛讀）：讀到另一個事務已提交數據

3.2、並發事務問題

因為並發事務導致的問題大致有5類，其中兩類是更新問題，三類是讀問題。

l 臟讀（dirty read）：讀到另一個事務的未提交更新數據，即讀取到了臟數據；

l 不可重複讀（unrepeatable read）：對同一記錄的兩次讀取不一致，因為另一事務對該記錄做了修改；

l 幻讀（phantom read）：對同一張表的兩次查詢不一致，因為另一事務插入了一條記錄；

不可重複讀和幻讀的區別：

l 不可重複讀是讀取到了另一個事務的更新；

l 幻讀是讀取到了另一個事務的插入（MySQL中無法測試到幻讀）；

3.3、四大隔離級別

4個等級的事務隔離級別，在相同數據環境下，使用相同的輸入，執行相同的工作，根據不同的隔離級別，可以導致不同的結果。不同事務隔離級別能夠解決的數據並發問題的能力是不同的。

SERIALIZABLE（串列化）

² 不會出現任何並發問題，因為它是對同一數據的訪問是串列的，非並發訪問的；

² 性能最差；

REPEATABLE READ（可重複讀），MySQL默認的隔離級別

² 防止臟讀和不可重複讀，不能處理幻讀問題；

² 性能比SERIALIZABLE好；

READ COMMITTED（讀已提交數據），Oracle默認的隔離級別

² 防止臟讀，不能處理不可重複讀和幻讀問題；

² 性能比REPEATABLE READ；

READ UNCOMMITTED（讀未提交數據）

² 可能出現任何事務並發問題；

² 性能最好；

3.4、MySQL的隔離級別

l MySQL的默認隔離級別為REPEATABLE READ，可以通過下面語句查看：

select @@tx_isolation

l 也可以通過下面語句來設置當前連接的隔離級別：

set transaction isolationlevel [4選1]

3.5、JDBC設置隔離級別

conn.setTransactionIsolation(int level)

參數可選值有：

² Connection.TRANSACTION_READ_UNCOMMITTED;

² Connection.TRANSACTION_READ_COMMITTED;

² Connection.TRANSACTION_REPEATABLE_READ;

² Connection.TRANSACTION_SERIALIZABLE

五、數據連接池

1、數據連接池的概念

用池來管理Connection，這樣就可以重複使用Connection。有了池，所以我們就不用自己來創建Connection，而是通過池來獲取Connection對象。當使用完Connection後，調用Connection的close()方法也不會真的關閉Connection，而是把Connection「歸還」給池。池就可以再利用這個Connection對象了。

數據連接池的參數有：

l 初始大小（10個連接）；

l 最小空閑連接數（3個連接）；

l 增量：即一次創建的最小單位（5個連接）；

l 最大空閑連接數（12個連接）；

l 最大連接數（20個）；

l 最大等待時間（1000毫秒）。

2、C3P0數據連接池

需要導入的jar包：

2.1、使用類實現

//創建連接池對象 ComboPooledDataSource dataSource = new ComboPooledDataSource(); //對池進行四大參數配置 dataSource.setDriverClass("com.mysql.jdbc.Driver"); dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/test"); dataSource.setUser("root"); dataSource.setPassword("root"); //池配置 //配置初始化連接 dataSource.setInitialPoolSize(20); //配置增量 dataSource.setAcquireIncrement(5); //配置最小連接數 dataSource.setMinPoolSize(3); //配置最大連接數 dataSource.setMaxPoolSize(50); //獲取資料庫連接 Connection conn = dataSource.getConnection(); //關閉連接，其實是把連接歸還給池 conn.close();

2.2、使用配置文件實現

使用配置文件實現C3P0的要求：

l 文件名稱必須為c3p0-config.xml

l 文件位置必須在src下

1、默認配置

c3p0-config.xml配置文件

<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <!– 默認配置 –> <default-config> <!– 連接四大參數配置 –> <property name="jdbcUrl">jdbc:mysql://localhost:3306/xm</property> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="user">root</property> <property name="password">root</property> <!– 池參數配置 –> <property name="aoquireInorement">3</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">2</property> <property name="maxPoolSize">10</property> </default-config> </c3p0-config>

直接在java程式碼中創建數據連接池對象

//創建連接池對象 ComboPooledDataSource dataSource = new ComboPooledDataSource(); //獲取資料庫連接 Connection conn = dataSource.getConnection();

2、使用命名配置

c3p0-config.xml配置文件

<?xml version="1.0" encoding="UTF-8"?> <c3p0-config> <!– Oracle配置資訊 –> <named-config name="oracle-config"> <property name="jdbcUrl">jdbc:mysql://localhost:3306/xm</property> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="user">root</property> <property name="password">root</property> <property name="aoquireInorement">3</property> <property name="initialPoolSize">10</property> <property name="minPoolSize">2</property> <property name="maxPoolSize">10</property> </named-config> </c3p0-config>

在java程式碼中創建池對象

//構造器的參數為指定命名配置元素的名稱 ComboPooledDataSource dataSource = new ComboPooledDataSource("oracle-config"); //獲取資料庫連接 Connection conn = dataSource.getConnection();

2.3、優化JDBCUtils工具類

public class JDBCUtil { //創建c3p0數據連接池對象 private static ComboPooledDataSource dataSource = new ComboPooledDataSource(); /** * 使用數據連接池返回一個數據連接對象 * @throws SQLException */ public static Connection getConnection() throws SQLException{ return dataSource.getConnection(); } /** * 返回數據連接池對象 */ public static ComboPooledDataSource getDataSources(){ return dataSource; } }

3、ThreadLocal

ThreadLocal通常用在一個類的成員上，多個執行緒訪問它時，每個執行緒都有自己的副本，互不干擾。在Spring中把Connection放到ThreadLocal中。

3.1、ThreadLocal API

ThreadLocal類只有三個方法：

l void set(T value)：保存值；

l T get()：獲取值；

l void remove()：移除值

3.2、ThreadLocal的內部是Map

ThreadLocal內部其實是個Map來保存數據，雖然在使用ThreadLocal時只給出了值，沒有給出鍵，但是在它內部使用了當前執行緒作為鍵。

程式碼實現：

//實例化ThreadLocal類 ThreadLocal<String> tl = new ThreadLocal<String>(); tl.set("hello");//存值，在ThreadLocal中不能存多個值 String str = tl.get();//取值 tl.remove();//刪除值

ThreadLocal原理：

class ThreadLocal<T> { private Map<Thread,T> map = new HashMap<Thread, T>(); //存值方法 public void set(T value){ //把當前執行緒作為鍵 map.put(Thread.currentThread(), value); } //取值方法 public T get(){ return map.get(Thread.currentThread()); } //刪除值方法 public void remove(){ map.remove(Thread.currentThread()); } }

演示ThreadLocal並發訪問共享資源的問題

public void test(){ //實例化ThreadLocal類 final ThreadLocal<String> tl = new ThreadLocal<String>(); tl.set("hello");//存值，在ThreadLocal中不能存多個值 //內部執行緒類 new Thread(){ public void run() { //在內部類的執行緒中獲取不到主執行緒的ThreadLocal中的值 System.out.println(tl.get()); }; }.start(); }

六、dbUtils工具

1、優化Dao模型層

1.1、數據更新

創建QR類，動態更新數據

public class QR { private Connection conn ; public QR(Connection conn) { this.conn = conn; } public QR() { } /** * 實現增、刪、改的方法 */ public int update(String sql,Object… params){ PreparedStatement ps = null; try{ //使用SQL創建ps對象 ps = conn.prepareStatement(sql); //設置參數 initStatement(ps,params); //執行SQL return ps.executeUpdate(); } catch (Exception e){ e.printStackTrace(); } finally { try { if(ps!=null) ps.close(); if(conn!=null) conn.close(); } catch (Exception e2) { e2.printStackTrace(); } } return -1; } //動態獲取參數的方法 public void initStatement(PreparedStatement ps,Object… params){ try { for (int i = 0; i < params.length; i++) { ps.setObject(i+1, params[i]); } } catch (Exception e) { e.printStackTrace(); } } }

使用QR對象，動態更新數據

//獲得Stu對象 Stu stu = new Stu(100,"tom",20,"男"); QR qr = new QR(JDBCUtil.getConnection()); //創建SQL模板 String sql = "insert into stu values(?,?,?,?)"; //聲明要動態賦值的參數 Object[] params = {stu.getId(),stu.getName(),stu.getAge(),stu.getSex()}; //調用更新方法 int i = qr.update(sql, params); System.out.println(i);

1.2、數據查詢

ResultHandler介面

public interface ResultHandler<T> { public T handler(ResultSet rs) throws SQLException; }

QR.java類

public class QR<T> { private Connection conn ; public QR(Connection conn) { this.conn = conn; } public QR() { } //動態獲取參數的方法 public void initStatement(PreparedStatement ps,Object… params){ try { for (int i = 0; i < params.length; i++) { ps.setObject(i+1, params[i]); } } catch (Exception e) { e.printStackTrace(); } } /** * 動態查詢數據（查詢對象） */ public T query(String sql,ResultHandler rh,Object… params){ PreparedStatement ps = null; ResultSet rs = null; try{ //使用SQL創建ps對象 ps = conn.prepareStatement(sql); //設置參數 initStatement(ps,params); //執行SQL rs = ps.executeQuery(); return (T) rh.handler(rs); } catch (Exception e){ e.printStackTrace(); } finally { try { if(ps!=null) ps.close(); if(conn!=null) conn.close(); } catch (Exception e2) { e2.printStackTrace(); } } return null; } }

使用動態查詢

//獲得Stu對象 Stu stu = new Stu(100,"tom",20,"男"); QR qr = new QR(JDBCUtil.getConnection()); //創建SQL模板 String sql = "select * from stu where id=?"; //聲明要動態賦值的參數 Object[] params = {stu.getId()}; //創建ResultHandler對象 ResultHandler<Stu> rh = new ResultHandler<Stu>() { public Stu handler(ResultSet rs) throws SQLException { if(!rs.next()) return null; Stu s = new Stu(); s.setId(rs.getInt("id")); s.setName(rs.getString("name")); s.setAge(rs.getInt("age")); s.setSex(rs.getString("sex")); return s; } }; //執行查詢 Stu s = (Stu) qr.query(sql, rh, params);

2、dbUtils工具

需要導入的jar包：

2.1、dbUtils工具的使用

dbUtils中對數據的動態操作使用的是QueryRunner對象

//獲得Stu對象 Stu stu = new Stu(100,"tom",20,"男"); //創建QR對象，參數為數據連接池對象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //創建SQL模板 String sql = "select * from stu where id=?"; Object[] params = {stu.getId()}; //執行查詢 Stu s = qr.query(sql, new BeanHandler<Stu>(Stu.class),params); System.out.println(s.getName());

2.2、QueryRunner對象介紹

update()方法

u int update(String sql,Object… params)：可執行增、刪、改語句；

u int update(Connection conn,String sql,Object… params)：需要調用者提供Connection，這說明本方法不再管理Connection了，支援事務；

query()方法

u T query(String sql,ResultSetHandler rsh,Object… params)：可執行查詢,它會先得到ResultSet，然後調用rsh的handle()，把rs轉換成需要的類型；

u T query(Connection conn,String sql,ResultSetHandler rsh,Object… params)：支援事務；

ResultSetHandler介面：

u BeanHandler（單行）：構造器需要一個Class類型的參數，用來把一行結果轉換成指定類型的JavaBean對象；

u BeanListHandler（多行）：構造器也是需要一個Class類型的參數，用來把一行結果集轉換成一個Javabean，那麼多行就是轉換成List對象，一堆Javabean；

u MapHandler（單行）：把一行結果集轉換成Map對象，如

² 一行記錄

sid sname age sex

100 tom 20 男

² 一個Map

{sid:100,sname:tom,age:20,sex:男}

u MapListHandler（多行）：把一行記錄轉換成一個Map，多行就是多個Map，即List<Map>；

u ScalarHandler（單行單列）：通常用於select count(*) from tbname;語句，結果集是單行單列的，返回一個Object；

3、ResultSetHandler介面

3.1、BeanListHandler的使用

//創建QR對象，參數為數據連接池對象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //創建SQL模板 String sql = "select * from stu"; //BeanListHandler多行處理器 List<Stu> list = qr.query(sql, new BeanListHandler<Stu>(Stu.class));

3.2、MapHandler的使用

//獲得Stu對象 Stu stu = new Stu(100,"tom",20,"男"); //創建QR對象，參數為數據連接池對象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //創建SQL模板 String sql = "select * from stu where id=?"; Object[] params = {stu.getId()}; //MapHandler單行處理器，把每一行都轉換成一個Map Map map = qr.query(sql, new MapHandler(),params);

3.3、MapListHandler的使用

//獲得Stu對象 Stu stu = new Stu(100,"tom",20,"男"); //創建QR對象，參數為數據連接池對象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //創建SQL模板 String sql = "select * from stu"; //MapListHandler多行處理器，把每行都轉成一個Map，即List<Map> List<Map<String,Object>> mapList = qr.query(sql, new MapListHandler());

3.4、ScalarHandler的使用

//獲得Stu對象 Stu stu = new Stu(100,"tom",20,"男"); //創建QR對象，參數為數據連接池對象 QueryRunner qr = new QueryRunner(JDBCUtil.getDataSources()); //創建SQL模板 String sql = "select count(*) from stu"; //執行查詢 Object obj = qr.query(sql, new ScalarHandler());

七、Service層的事務處理

如果是在DAO中來處理事務是非常簡單的，事務屬於業務邏輯，我們必須放到Service層中來處理事務，在前面的章節中學習到，JDBC處理事務是用Connection對象來調用事務的相關方法，但是在Service中不能出現Connection，它只能出現在DAO中，這時，我們就會遇到事務處理的問題。

1、優化JDBCUtil工具類

我們把對事務的開啟和關閉放到JDBCUtil中，在Service中調用JDBCUtil的方法來完成事務的處理，但在Service中就不會再出現Connection了。

DAO中的方法不用再讓Service來傳遞Connection了，DAO會主動從JDBCUtil中獲取Connection對象，這樣，JDBCUtil稱為了DAO和Service的中介。

在JDBCUtil類中添加三個方法：

l beginTransaction()：開啟事務的方法，獲取一個Connection，設置它的setAutoCommit(false)，還要保證DAO中使用的連接也是該方法中的Connection對象；

l commitTransaction()：提交事務的方法，獲取beginTransaction提供的Connection，然後調用commit()方法；

l rollbackTransaction()：回滾事務，調用Connection對象的rollback()方法；

在Service中調用這三個方法來完成事務的處理：

public class DemoService { private DemoDao demoDao = new DemoDao(); public void method(){ try { //開啟事務 JDBCUtil.beginTransaction(); demoDao.update(…); demoDao.update(…); //提交事務 JDBCUtil.commitTransaction(); } catch (SQLException e) { //事務回滾 JDBCUtil.rollbackTransaction(); } } }

JDBCUtil.java類的優化

public class JDBCUtil { //數據連接池對象 private static ComboPooledDataSource dataSource = new ComboPooledDataSource();; //事務專用連接 private static Connection conn = null; /** * 通過數據連接池獲取連接 * @throws SQLException */ public static Connection getConnection() throws SQLException{ //如果conn不為空，證明一件調用了beginTransaction方法，事務已經開啟 if(conn != null) return conn; return dataSource.getConnection(); } /** * 獲取數據連接池對象 */ public static DataSource getDataSource(){ return dataSource; } /** * 開啟事務 * 獲取Connection，設置它的setAutoCommit(false) * @throws SQLException */ public static void beginTransaction() throws SQLException{ if(conn!=null){ throw new SQLException("事務已經開啟，不能重複開啟事務！"); } //給conn賦值 conn = getConnection(); //把conn的事務設置為手動提交 conn.setAutoCommit(false); } /** * 提交事務 * @throws SQLException */ public static void commitTransaction() throws SQLException{ if(conn == null){ throw new SQLException("還沒有開啟事務，不能提交！"); } //提交事務 conn.commit(); //把連接歸還，並且賦值為NULL，是為了保證下次獲取，不再是開始事務的連接 conn.close(); conn = null; } /** * 事務回滾 * @throws SQLException */ public static void rollbackTransaction() throws SQLException{ if(conn == null){ throw new SQLException("還沒有開啟事務，不能回滾！"); } conn.rollback(); conn.close(); conn = null; } /** * 釋放連接 * @throws SQLException */ public static void closeConnection(Connection connection) throws SQLException{ //判斷參數連接是否為事務連接，是就不關閉，不是再關閉 //如果conn為NULL，證明沒有開啟事務，那麼就關閉參數的connection if(conn == null){ connection.close(); }else if(conn != connection){ //如果conn不為NULL，但是與connection不是同一個連接，證明參數不是事務專用連接，那麼也關閉connection connection.close(); } } }

創建TxQueryRunner.java類基礎QueryRunner，把資料庫連接封裝起來

public class TxQueryRunner extends QueryRunner { @Override public int[] batch(String sql, Object[][] params) throws SQLException { // 獲取連接 Connection conn = JDBCUtil.getConnection(); // 調用父類方法 int[] result = super.batch(conn, sql, params); // 釋放連接 JDBCUtil.closeConnection(conn); return result; } …… @Override public int update(String sql, Object… params) throws SQLException { // 獲取連接 Connection conn = JDBCUtil.getConnection(); // 調用父類方法 int result = super.update(conn, sql, params); // 釋放連接 JDBCUtil.closeConnection(conn); return result; } …… }

在DAO層中使用TxQueryRunner對象來完成資料庫的操作

public class DemoDao { public void demo(String s1,String s2) throws SQLException { QueryRunner qr = new TxQueryRunner(); //SQL模板 String sql = "update user set ……."; Object[] params = {s1,s2}; //獲取資料庫連接 qr.update(sql, params); } }

2、JDBCUtil處理多執行緒並發問題

當有多個執行緒操作JDBCUtil中的Connection對象時，就會出現並發問題，可以使用ThreadLocal來解決這個問題。

JDBCUtil工具類優化

public class JDBCUtil { //數據連接池對象 private static ComboPooledDataSource dataSource = new ComboPooledDataSource();; //事務專用連接，把事務存入到ThreadLocal中 private static ThreadLocal<Connection> tlConn = new ThreadLocal<Connection>(); /** * 通過數據連接池獲取連接 * @throws SQLException */ public static Connection getConnection() throws SQLException{ //如果conn不為空，證明一件調用了beginTransaction方法，事務已經開啟 Connection conn = tlConn.get(); if(conn != null) return conn; return dataSource.getConnection(); } /** * 獲取數據連接池對象 */ public static DataSource getDataSource(){ return dataSource; } /** * 開啟事務 * 獲取Connection，設置它的setAutoCommit(false) * @throws SQLException */ public static void beginTransaction() throws SQLException{ Connection conn = tlConn.get(); if(conn!=null){ throw new SQLException("事務已經開啟，不能重複開啟事務！"); } //給conn賦值 conn = getConnection(); //把conn的事務設置為手動提交 conn.setAutoCommit(false); //把連接存入到ThreadLocal中 tlConn.set(conn); } /** * 提交事務 * @throws SQLException */ public static void commitTransaction() throws SQLException{ Connection conn = tlConn.get(); if(conn == null){ throw new SQLException("還沒有開啟事務，不能提交！"); } //提交事務 conn.commit(); //把連接歸還，並且賦值為NULL，是為了保證下次獲取，不再是開始事務的連接 conn.close(); tlConn.remove(); } /** * 事務回滾 * @throws SQLException */ public static void rollbackTransaction() throws SQLException{ Connection conn = tlConn.get(); if(conn == null){ throw new SQLException("還沒有開啟事務，不能回滾！"); } conn.rollback(); conn.close(); tlConn.remove(); } /** * 釋放連接 * @throws SQLException */ public static void closeConnection(Connection connection) throws SQLException{ //判斷參數連接是否為事務連接，是就不關閉，不是再關閉 //如果conn為NULL，證明沒有開啟事務，那麼就關閉參數的connection Connection conn = tlConn.get(); if(conn == null){ connection.close(); }else if(conn != connection){ //如果conn不為NULL，但是與connection不是同一個連接，證明參數不是事務專用連接，那麼也關閉connection connection.close(); } } }