?博客主頁: https://blog.csdn.net/m0_63815035?type=blog
💗《博客內容》:大數據開發、.NET、Java.測試開發、Python、Android、Go、Node、Android前端小程序等相關領域知識
📢博客專欄: https://blog.csdn.net/m0_63815035/category_11954877.html
📢歡迎點贊 👍 收藏 ?留言 📝
📢本文為學習筆記資料,如有侵權,請聯系我刪除,疏漏之處還請指正🙉
📢大廈之成,非一木之材也;大海之闊,非一流之歸也?
前言:
歡迎來到小羊的《Java教程:JavaWeb —MySQL高級》系列的學習之旅!無論你是初學者還是希望深化對Java編程語言理解的開發者,這系列教程都將為你提供系統化的知識和實用的技能,幫助你從零基礎起步,逐步掌握Java編程的核心概念和技術。希望大家多多支持小羊吧!!讓我們一起開啟這段精彩的編程旅程,從零開始Go Go
目錄
- 前言:
- 本節目標
- 1,約束
- 1.1 概念
- 1.2 分類
- 1.3 非空約束
- 1.4 唯一約束
- 1.5 主鍵約束
- 1.6 默認約束
- 1.7 約束練習
- 1.8 外鍵約束
- 1.8.1 概述
- 1.8.2 語法
- 1.8.3 練習
- 2,數據庫設計
- 2.1 數據庫設計簡介
- 2.3 表關系(多對多)
- 2.4 表關系(一對一)
- 2.5 數據庫設計案例
- 3,多表查詢
- 3.1 內連接查詢
- 3.2 外連接查詢
- 3.3 子查詢
- 3.4 案例
- 4,事務
- 4.1 概述
- 4.2 語法
- 4.4 事務的四大特征
本節目標
- 掌握約束的使用
- 掌握表關系及建表原則
- 重點掌握多表查詢操作
- 掌握事務操作
1,約束
上面表中可以看到表中數據存在一些問題:
- id 列一般是用標示數據的唯一性的,而上述表中的id為1的有三條數據,并且 馬花疼 沒有id進行標示
- 柳白 這條數據的age列的數據是3000,而人也不可能活到3000歲
- 馬運 這條數據的math數學成績是-5,而數學學得再不好也不可能出現負分
- 柳青 這條數據的english列(英文成績)值為null,而成績即使沒考也得是0分
針對上述數據問題,我們就可以從數據庫層面在添加數據的時候進行限制,這個就是約束。
1.1 概念
- 約束是作用于表中列上的規則,用于限制加入表的數據
例如:我們可以給id列加約束,讓其值不能重復,不能為null值。 - 約束的存在保證了數據庫中數據的正確性、有效性和完整性
添加約束可以在添加數據的時候就限制不正確的數據,年齡是3000,數學成績是-5分這樣無效的數據,繼而保障數據的完整性。
1.2 分類
- 非空約束: 關鍵字是 NOT NULL
保證列中所有的數據不能有null值。
例如:id列在添加 馬花疼 這條數據時就不能添加成功。 - 唯一約束:關鍵字是 UNIQUE
保證列中所有數據各不相同。
例如:id列中三條數據的值都是1,這樣的數據在添加時是絕對不允許的。 - 主鍵約束: 關鍵字是 PRIMARY KEY
主鍵是一行數據的唯一標識,要求非空且唯一。一般我們都會給沒張表添加一個主鍵列用來唯一標識數據。
例如:上圖表中id就可以作為主鍵,來標識每條數據。那么這樣就要求數據中id的值不能重復,不能為null值。 - 檢查約束: 關鍵字是 CHECK
保證列中的值滿足某一條件。
例如:我們可以給age列添加一個范圍,最低年齡可以設置為1,最大年齡就可以設置為300,這樣的數據才更合理些。
注意:MySQL不支持檢查約束。
這樣是不是就沒辦法保證年齡在指定的范圍內了?從數據庫層面不能保證,以后可以在java代碼中進行限制,一樣也可以實現要求。
- 默認約束: 關鍵字是 DEFAULT
保存數據時,未指定值則采用默認值。
例如:我們在給english列添加該約束,指定默認值是0,這樣在添加數據時沒有指定具體值時就會采用默認給定的0。 - 外鍵約束: 關鍵字是 FOREIGN KEY
外鍵用來讓兩個表的數據之間建立鏈接,保證數據的一致性和完整性。
外鍵約束現在可能還不太好理解,后面我們會重點進行講解。
1.3 非空約束
- 概念
非空約束用于保證列中所有數據不能有NULL值 - 語法
- 添加約束
-- 創建表時添加非空約束
CREATE TABLE 表名(列名 數據類型 NOT NULL,…
);
-- 建完表后添加非空約束
ALTER TABLE 表名 MODIFY 字段名 數據類型 NOT NULL;
-- 刪除約束ALTER TABLE 表名 MODIFY 字段名 數據類型;
1.4 唯一約束
- 概念
唯一約束用于保證列中所有數據各不相同 - 語法
- 添加約束
-- 創建表時添加唯一約束CREATE TABLE 表名(列名 數據類型 UNIQUE [AUTO_INCREMENT],-- AUTO_INCREMENT: 當不指定值時自動增長…); CREATE TABLE 表名(列名 數據類型,…[CONSTRAINT] [約束名稱] UNIQUE(列名));
-- 建完表后添加唯一約束
ALTER TABLE 表名 MODIFY 字段名 數據類型 UNIQUE;
- 刪除約束
ALTER TABLE 表名 DROP INDEX 字段名;
1.5 主鍵約束
- 概念
主鍵是一行數據的唯一標識,要求非空且唯一
一張表只能有一個主鍵 - 語法
- 添加約束
– 創建表時添加主鍵約束
- 添加約束
CREATE TABLE 表名(列名 數據類型 PRIMARY KEY [AUTO_INCREMENT],…
);
CREATE TABLE 表名(列名 數據類型,[CONSTRAINT] [約束名稱] PRIMARY KEY(列名)
);
-- 建完表后添加主鍵約束ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
- 刪除約束ALTER TABLE 表名 DROP PRIMARY KEY;
1.6 默認約束
- 概念
保存數據時,未指定值則采用默認值 - 語法
- 添加約束
-- 創建表時添加默認約束CREATE TABLE 表名(列名 數據類型 DEFAULT 默認值,…);
-- 建完表后添加默認約束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默認值;
- 刪除約束ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
1.7 約束練習
根據需求,為表添加合適的約束
-- 員工表
CREATE TABLE emp (id INT, -- 員工id,主鍵且自增長ename VARCHAR(50), -- 員工姓名,非空且唯一joindate DATE, -- 入職日期,非空salary DOUBLE(7,2), -- 工資,非空bonus DOUBLE(7,2) -- 獎金,如果沒有將近默認為0
);
上面一定給出了具體的要求,我們可以根據要求創建這張表,并為每一列添加對應的約束。建表語句如下:
DROP TABLE IF EXISTS emp;
-- 員工表
CREATE TABLE emp (id INT PRIMARY KEY, -- 員工id,主鍵且自增長ename VARCHAR(50) NOT NULL UNIQUE, -- 員工姓名,非空并且唯一joindate DATE NOT NULL , -- 入職日期,非空salary DOUBLE(7,2) NOT NULL , -- 工資,非空bonus DOUBLE(7,2) DEFAULT 0 -- 獎金,如果沒有獎金默認為0
);
通過上面語句可以創建帶有約束的 emp 表,約束能不能發揮作用呢。接下來我們一一進行驗證,先添加一條沒有問題的數據
INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'張三','1999-11-11',8800,5000);
- 驗證主鍵約束,非空且唯一
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'張三','1999-11-11',8800,5000);
執行結果如下:
從上面的結果可以看到,字段 id 不能為null。那我們重新添加一條數據,如下:
INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'張三','1999-11-11',8800,5000);
執行結果如下:
從上面結果可以看到,1這個值重復了。所以主鍵約束是用來限制數據非空且唯一的。那我們再添加一條符合要求的數據
INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);
執行結果如下:
- 驗證非空約束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);
執行結果如下:
從上面結果可以看到,ename 字段的非空約束生效了。
- 驗證唯一約束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);
執行結果如下:
從上面結果可以看到,ename 字段的唯一約束生效了。
- 驗證默認約束
INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);
執行完上面語句后查詢表中數據,如下圖可以看到王五這條數據的bonus列就有了默認值0。
注意:默認約束只有在不給值時才會采用默認值。如果給了null,那值就是null值。
如下:
INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'趙六','1999-11-11',8800,null);
執行完上面語句后查詢表中數據,如下圖可以看到趙六這條數據的bonus列的值是null。
- 驗證自動增長: auto_increment 當列是數字類型 并且唯一約束
重新創建 emp 表,并給id列添加自動增長
-- 員工表
CREATE TABLE emp (id INT PRIMARY KEY auto_increment, -- 員工id,主鍵且自增長ename VARCHAR(50) NOT NULL UNIQUE, -- 員工姓名,非空并且唯一joindate DATE NOT NULL , -- 入職日期,非空salary DOUBLE(7,2) NOT NULL , -- 工資,非空bonus DOUBLE(7,2) DEFAULT 0 -- 獎金,如果沒有獎金默認為0
);
接下來給emp添加數據,分別驗證不給id列添加值以及給id列添加null值,id列的值會不會自動增長:
INSERT INTO emp(ename,joindate,salary,bonus) values('趙六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'趙六2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'趙六3','1999-11-11',8800,null);
1.8 外鍵約束
1.8.1 概述
外鍵用來讓兩個表的數據之間建立鏈接,保證數據的一致性和完整性。
如何理解上面的概念呢?如下圖有兩張表,員工表和部門表:
員工表中的dep_id字段是部門表的id字段關聯,也就是說1號學生張三屬于1號部門研發部的員工。現在我要刪除1號部門,就會出現錯誤的數據(員工表中屬于1號部門的數據)。而我們上面說的兩張表的關系只是我們認為它們有關系,此時需要通過外鍵讓這兩張表產生數據庫層面的關系,這樣你要刪除部門表中的1號部門的數據將無法刪除。
1.8.2 語法
- 添加外鍵約束
-- 創建表時添加外鍵約束
CREATE TABLE 表名(列名 數據類型,…[CONSTRAINT] [外鍵名稱] FOREIGN KEY(外鍵列名) REFERENCES 主表(主表列名)
);
-- 建完表后添加外鍵約束
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱);
- 刪除外鍵約束ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;
1.8.3 練習
根據上述語法創建員工表和部門表,并添加上外鍵約束:
-- 刪除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;-- 部門表
CREATE TABLE dept(id int primary key auto_increment,dep_name varchar(20),addr varchar(20)
);
-- 員工表
CREATE TABLE emp(id int primary key auto_increment,name varchar(20),age int,dep_id int,-- 添加外鍵 dep_id,關聯 dept 表的id主鍵CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
);
添加數據
-- 添加 2 個部門
insert into dept(dep_name,addr) values
('研發部','廣州'),('銷售部', '深圳');-- 添加員工,dep_id 表示員工所在的部門
INSERT INTO emp (NAME, age, dep_id) VALUES
('張三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('趙六', 20, 2),
('孫七', 22, 2),
('周八', 18, 2);
此時刪除 研發部 這條數據,會發現無法刪除。
刪除外鍵
alter table emp drop FOREIGN key fk_emp_dept;
重新添加外鍵
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);
2,數據庫設計
2.1 數據庫設計簡介
-
軟件的研發步驟
-
數據庫設計概念
- 數據庫設計就是根據業務系統的具體需求,結合我們所選用的DBMS,為這個業務系統構造出最優的數據存儲模型。
- 建立數據庫中的表結構以及表與表之間的關聯關系的過程。
- 有哪些表?表里有哪些字段?表和表之間有什么關系?
-
數據庫設計的步驟
-
需求分析(數據是什么? 數據具有哪些屬性? 數據與屬性的特點是什么)
-
邏輯分析(通過ER圖對數據庫進行邏輯建模,不需要考慮我們所選用的數據庫管理系統)
如下圖就是ER(Entity/Relation)圖:
-
物理設計(根據數據庫自身的特點把邏輯設計轉換為物理設計)
-
維護設計(1.對新的需求進行建表;2.表優化)
-
-
表關系
-
一對一
- 如:用戶 和 用戶詳情
- 一對一關系多用于表拆分,將一個實體中經常使用的字段放一張表,不經常使用的字段放另一張表,用于提升查詢性能
上圖左邊是用戶的詳細信息,而我們真正在展示用戶信息時最長用的則是上圖右邊紅框所示,所以我們會將詳細信息查分成兩周那個表。
-
一對多
- 如:部門 和 員工
- 一個部門對應多個員工,一個員工對應一個部門。如下圖:
-
多對多
- 如:商品 和 訂單
- 一個商品對應多個訂單,一個訂單包含多個商品。如下圖:
-
2.2 表關系(一對多)
-
一對多
- 如:部門 和 員工
- 一個部門對應多個員工,一個員工對應一個部門。
-
實現方式
在多的一方建立外鍵,指向一的一方的主鍵 -
案例
我們還是以 員工表 和 部門表 舉例:
經過分析發現,員工表屬于多的一方,而部門表屬于一的一方,此時我們會在員工表中添加一列(dep_id),指向于部門表的主鍵(id):
建表語句如下:
-- 刪除表DROP TABLE IF EXISTS tb_emp;DROP TABLE IF EXISTS tb_dept;-- 部門表CREATE TABLE tb_dept(id int primary key auto_increment,dep_name varchar(20),addr varchar(20));-- 員工表 CREATE TABLE tb_emp(id int primary key auto_increment,name varchar(20),age int,dep_id int,-- 添加外鍵 dep_id,關聯 dept 表的id主鍵CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id) );
查看表結構模型圖:
2.3 表關系(多對多)
-
多對多
- 如:商品 和 訂單
- 一個商品對應多個訂單,一個訂單包含多個商品
-
實現方式
建立第三張中間表,中間表至少包含兩個外鍵,分別關聯兩方主鍵 -
案例
我們以 訂單表 和 商品表 舉例:
經過分析發現,訂單表和商品表都屬于多的一方,此時需要創建一個中間表,在中間表中添加訂單表的外鍵和商品表的外鍵指向兩張表的主鍵:
建表語句如下:
```sql```sql
-- 刪除表DROP TABLE IF EXISTS tb_order_goods;DROP TABLE IF EXISTS tb_order;DROP TABLE IF EXISTS tb_goods;-- 訂單表CREATE TABLE tb_order(id int primary key auto_increment,payment double(10,2),payment_type TINYINT,status TINYINT);-- 商品表CREATE TABLE tb_goods(id int primary key auto_increment,title varchar(100),price double(10,2));-- 訂單商品中間表CREATE TABLE tb_order_goods(id int primary key auto_increment,order_id int,goods_id int,count int);-- 建完表后,添加外鍵alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);
查看表結構模型圖:
2.4 表關系(一對一)
-
一對一
- 如:用戶 和 用戶詳情
- 一對一關系多用于表拆分,將一個實體中經常使用的字段放一張表,不經常使用的字段放另一張表,用于提升查詢性能
-
實現方式
在任意一方加入外鍵,關聯另一方主鍵,并且設置外鍵為唯一(UNIQUE) -
案例
我們以 用戶表 舉例:
而在真正使用過程中發現 id、photo、nickname、age、gender 字段比較常用,此時就可以將這張表查分成兩張表。
建表語句如下:
create table tb_user_desc (id int primary key auto_increment,city varchar(20),edu varchar(10),income int,status char(2),des varchar(100)
);create table tb_user (id int primary key auto_increment,photo varchar(100),nickname varchar(50),age int,gender char(1),desc_id int unique,-- 添加外鍵CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
);
查看表結構模型圖:
2.5 數據庫設計案例
根據下圖設計表及表和表之間的關系:
經過分析,我們分為 專輯表 曲目表 短評表 用戶表 4張表。
一個專輯可以有多個曲目,一個曲目只能屬于某一張專輯,所以專輯表和曲目表的關系是一對多。
一個專輯可以被多個用戶進行評論,一個用戶可以對多個專輯進行評論,所以專輯表和用戶表的關系是 多對多。
一個用戶可以發多個短評,一個短評只能是某一個人發的,所以用戶表和短評表的關系是 一對多。
3,多表查詢
多表查詢顧名思義就是從多張表中一次性的查詢出我們想要的數據。我們通過具體的sql給他們演示,先準備環境
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;# 創建部門表CREATE TABLE dept(did INT PRIMARY KEY AUTO_INCREMENT,dname VARCHAR(20));# 創建員工表CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性別salary DOUBLE, -- 工資join_date DATE, -- 入職日期dep_id INT,FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外鍵,關聯部門表(部門表的主鍵));-- 添加部門數據INSERT INTO dept (dNAME) VALUES ('研發部'),('市場部'),('財務部'),('銷售部');-- 添加員工數據INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES('孫悟空','男',7200,'2013-02-24',1),('豬八戒','男',3600,'2010-12-02',2),('唐僧','男',9000,'2008-08-08',2),('白骨精','女',5000,'2015-10-07',3),('蜘蛛精','女',4500,'2011-03-14',1),('小白龍','男',2500,'2011-02-14',null);
執行下面的多表查詢語句
select * from emp , dept; -- 從emp和dept表中查詢所有的字段數據
結果如下:
從上面的結果我們看到有一些無效的數據,如 孫悟空 這個員工屬于1號部門,但也同時關聯的2、3、4號部門。所以我們要通過限制員工表中的 dep_id 字段的值和部門表 did 字段的值相等來消除這些無效的數據,
select * from emp , dept where emp.dep_id = dept.did;
執行后結果如下:
上面語句就是連接查詢,那么多表查詢都有哪些呢?
-
連接查詢
- 內連接查詢 :相當于查詢AB交集數據
- 外連接查詢
- 左外連接查詢 :相當于查詢A表所有數據和交集部門數據
- 右外連接查詢 : 相當于查詢B表所有數據和交集部分數據
-
子查詢
3.1 內連接查詢
- 語法
-- 隱式內連接
SELECT 字段列表 FROM 表1,表2… WHERE 條件;-- 顯示內連接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 條件;
內連接相當于查詢 A B 交集數據
- 案例
- 隱式內連接
SELECT*
FROMemp,dept
WHEREemp.dep_id = dept.did;
執行上述語句結果如下:
- 查詢 emp的 name, gender,dept表的dname
SELECTemp. NAME,emp.gender,dept.dname
FROMemp,dept
WHEREemp.dep_id = dept.did;
執行語句結果如下:
上面語句中使用表名指定字段所屬有點麻煩,sql也支持給表指別名,上述語句可以改進為
SELECTt1. NAME,t1.gender,t2.dname
FROMemp t1,dept t2
WHEREt1.dep_id = t2.did;
- 顯式內連接
select * from emp inner join dept on emp.dep_id = dept.did;-- 上面語句中的inner可以省略,可以書寫為如下語句select * from emp join dept on emp.dep_id = dept.did;
執行結果如下:
3.2 外連接查詢
- 語法
-- 左外連接SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 條件;-- 右外連接SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 條件;
左外連接:相當于查詢A表所有數據和交集部分數據
右外連接:相當于查詢B表所有數據和交集部分數據
- 案例
- 查詢emp表所有數據和對應的部門信息(左外連接)
select * from emp left join dept on emp.dep_id = dept.did;
執行語句結果如下:
結果顯示查詢到了左表(emp)中所有的數據及兩張表能關聯的數據。
- 查詢dept表所有數據和對應的員工信息(右外連接)
select * from emp right join dept on emp.dep_id = dept.did;
執行語句結果如下:
結果顯示查詢到了右表(dept)中所有的數據及兩張表能關聯的數據。
要查詢出部門表中所有的數據,也可以通過左外連接實現,只需要將兩個表的位置進行互換:
select * from dept left join emp on emp.dep_id = dept.did;
3.3 子查詢
- 概念
查詢中嵌套查詢,稱嵌套查詢為子查詢。
什么是查詢中嵌套查詢呢?我們通過一個例子來看:
需求:查詢工資高于豬八戒的員工信息。
來實現這個需求,我們就可以通過二步實現,
第一步:先查詢出來 豬八戒的工資
select salary from emp where name = '豬八戒'
第二步:查詢工資高于豬八戒的員工信息
select * from emp where salary > 3600;
第二步中的3600可以通過第一步的sql查詢出來,所以將3600用第一步的sql語句進行替換
select * from emp where salary > (select salary from emp where name = '豬八戒');
這就是查詢語句中嵌套查詢語句。
- 子查詢根據查詢結果不同,作用不同
- 子查詢語句結果是單行單列,子查詢語句作為條件值,使用 = != > < 等進行條件判斷
- 子查詢語句結果是多行單列,子查詢語句作為條件值,使用 in 等關鍵字進行條件判斷
- 子查詢語句結果是多行多列,子查詢語句作為虛擬表
- 案例
- 查詢 ‘財務部’ 和 ‘市場部’ 所有的員工信息
-- 查詢 '財務部' 或者 '市場部' 所有的員工的部門did
select did from dept where dname = '財務部' or dname = '市場部';select * from emp where dep_id in (select did from dept where dname = '財務部' or dname = '市場部');
- 查詢入職日期是 ‘2011-11-11’ 之后的員工信息和部門信息
-- 查詢入職日期是 '2011-11-11' 之后的員工信息
select * from emp where join_date > '2011-11-11' ;
-- 將上面語句的結果作為虛擬表和dept表進行內連接查詢
select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;
3.4 案例
- 環境準備:
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;-- 部門表
CREATE TABLE dept (did INT PRIMARY KEY PRIMARY KEY, -- 部門iddname VARCHAR(50), -- 部門名稱loc VARCHAR(50) -- 部門所在地
);-- 職務表,職務名稱,職務描述
CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20),description VARCHAR(50)
);-- 員工表
CREATE TABLE emp (id INT PRIMARY KEY, -- 員工idename VARCHAR(50), -- 員工姓名job_id INT, -- 職務idmgr INT , -- 上級領導joindate DATE, -- 入職日期salary DECIMAL(7,2), -- 工資bonus DECIMAL(7,2), -- 獎金dept_id INT, -- 所在部門編號CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工資等級表
CREATE TABLE salarygrade (grade INT PRIMARY KEY, -- 級別losalary INT, -- 最低工資hisalary INT -- 最高工資
);-- 添加4個部門
INSERT INTO dept(did,dname,loc) VALUES
(10,'教研部','北京'),
(20,'學工部','上海'),
(30,'銷售部','廣州'),
(40,'財務部','深圳');-- 添加4個職務
INSERT INTO job (id, jname, description) VALUES
(1, '董事長', '管理整個公司,接單'),
(2, '經理', '管理部門員工'),
(3, '銷售員', '向客人推銷產品'),
(4, '文員', '使用辦公軟件');-- 添加員工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孫悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'盧俊義',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林沖',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'劉備',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'豬八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'羅貫中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吳用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龍',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'關羽',4,1007,'2002-01-23','13000.00',NULL,10);-- 添加5個工資等級
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
- 需求
1. 查詢所有員工信息。查詢員工編號,員工姓名,工資,職務名稱,職務描述/*分析:1. 員工編號,員工姓名,工資 信息在emp 員工表中2. 職務名稱,職務描述 信息在 job 職務表中3. job 職務表 和 emp 員工表 是 一對多的關系 emp.job_id = job.id*/-- 方式一 :隱式內連接SELECTemp.id,emp.ename,emp.salary,job.jname,job.descriptionFROMemp,jobWHEREemp.job_id = job.id;-- 方式二 :顯式內連接SELECTemp.id,emp.ename,emp.salary,job.jname,job.descriptionFROMempINNER JOIN job ON emp.job_id = job.id;
- 查詢員工編號,員工姓名,工資,職務名稱,職務描述,部門名稱,部門位置
/*分析:1. 員工編號,員工姓名,工資 信息在emp 員工表中2. 職務名稱,職務描述 信息在 job 職務表中3. job 職務表 和 emp 員工表 是 一對多的關系 emp.job_id = job.id4. 部門名稱,部門位置 來自于 部門表 dept5. dept 和 emp 一對多關系 dept.id = emp.dept_id*/-- 方式一 :隱式內連接SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.locFROMemp,job,deptWHEREemp.job_id = job.idand dept.id = emp.dept_id;-- 方式二 :顯式內連接SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.locFROMempINNER JOIN job ON emp.job_id = job.idINNER JOIN dept ON dept.id = emp.dept_id
- 查詢員工姓名,工資,工資等級
/*分析:1. 員工姓名,工資 信息在emp 員工表中2. 工資等級 信息在 salarygrade 工資等級表中3. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary*/SELECTemp.ename,emp.salary,t2.*FROMemp,salarygrade t2WHEREemp.salary >= t2.losalaryAND emp.salary <= t2.hisalary
- 查詢員工姓名,工資,職務名稱,職務描述,部門名稱,部門位置,工資等級
/*分析:1. 員工編號,員工姓名,工資 信息在emp 員工表中2. 職務名稱,職務描述 信息在 job 職務表中3. job 職務表 和 emp 員工表 是 一對多的關系 emp.job_id = job.id4. 部門名稱,部門位置 來自于 部門表 dept5. dept 和 emp 一對多關系 dept.id = emp.dept_id6. 工資等級 信息在 salarygrade 工資等級表中7. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary*/SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc,t2.gradeFROMempINNER JOIN job ON emp.job_id = job.idINNER JOIN dept ON dept.id = emp.dept_idINNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;
- 查詢出部門編號、部門名稱、部門位置、部門人數
/*分析:1. 部門編號、部門名稱、部門位置 來自于部門 dept 表2. 部門人數: 在emp表中 按照dept_id 進行分組,然后count(*)統計數量3. 使用子查詢,讓部門表和分組后的表進行內連接*/-- 根據部門id分組查詢每一個部門id和員工數select dept_id, count(*) from emp group by dept_id;SELECTdept.id,dept.dname,dept.loc,t1.countFROMdept,(SELECTdept_id,count(*) countFROMempGROUP BYdept_id) t1WHEREdept.id = t1.dept_id
4,事務
4.1 概述
數據庫的事務(Transaction)是一種機制、一個操作序列,包含了一組數據庫操作命令。
事務把所有的命令作為一個整體一起向系統提交或撤銷操作請求,即這一組數據庫命令要么同時成功,要么同時失敗。
事務是一個不可分割的工作邏輯單元。
這些概念不好理解,接下來舉例說明,如下圖有一張表
張三和李四賬戶中各有100塊錢,現李四需要轉換500塊錢給張三,具體的轉賬操作為
- 第一步:查詢李四賬戶余額
- 第二步:從李四賬戶金額 -500
- 第三步:給張三賬戶金額 +500
現在假設在轉賬過程中第二步完成后出現了異常第三步沒有執行,就會造成李四賬戶金額少了500,而張三金額并沒有多500;這樣的系統是有問題的。如果解決呢?使用事務可以解決上述問題
從上圖可以看到在轉賬前開啟事務,如果出現了異常回滾事務,三步正常執行就提交事務,這樣就可以完美解決問題。
4.2 語法
- 開啟事務
START TRANSACTION;或者 BEGIN;
- 提交事務
commit;
- 回滾事務
rollback;
4.3 代碼驗證
- 環境準備
DROP TABLE IF EXISTS account;-- 創建賬戶表CREATE TABLE account(id int PRIMARY KEY auto_increment,name varchar(10),money double(10,2));-- 添加數據INSERT INTO account(name,money) values('張三',1000),('李四',1000);
- 不加事務演示問題
-- 轉賬操作-- 1. 查詢李四賬戶金額是否大于500-- 2. 李四賬戶 -500UPDATE account set money = money - 500 where name = '李四';出現異常了... -- 此處不是注釋,在整體執行時會出問題,后面的sql則不執行-- 3. 張三賬戶 +500UPDATE account set money = money + 500 where name = '張三';
整體執行結果肯定會出問題,我們查詢賬戶表中數據,發現李四賬戶少了500。
- 添加事務sql如下:
-- 開啟事務BEGIN;-- 轉賬操作-- 1. 查詢李四賬戶金額是否大于500-- 2. 李四賬戶 -500UPDATE account set money = money - 500 where name = '李四';出現異常了... -- 此處不是注釋,在整體執行時會出問題,后面的sql則不執行-- 3. 張三賬戶 +500UPDATE account set money = money + 500 where name = '張三';-- 提交事務COMMIT;-- 回滾事務ROLLBACK;
上面sql中的執行成功進選擇執行提交事務,而出現問題則執行回滾事務的語句。以后我們肯定不可能這樣操作,而是在java中進行操作,在java中可以抓取異常,沒出現異常提交事務,出現異常回滾事務。
4.4 事務的四大特征
- 原子性(Atomicity): 事務是不可分割的最小操作單位,要么同時成功,要么同時失敗
- 一致性(Consistency) :事務完成時,必須使所有的數據都保持一致狀態
- 隔離性(Isolation) :多個事務之間,操作的可見性
- 持久性(Durability) :事務一旦提交或回滾,它對數據庫中的數據的改變就是永久的
說明:
mysql中事務是自動提交的。
也就是說我們不添加事務執行sql語句,語句執行完畢會自動的提交事務。
可以通過下面語句查詢默認提交方式:
SELECT @@autocommit;
查詢到的結果是1 則表示自動提交,結果是0表示手動提交。當然也可以通過下面語句修改提交方式
set @@autocommit = 0;
差不多大概講解完畢了,大家趕快動手練習一下吧。感謝大家觀看,我是程序員小羊!!!
今天這篇文章就到這里了,大廈之成,非一木之材也;大海之闊,非一流之歸也。感謝大家觀看本文