文章目錄
-
- mysql進階
- 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.2 表關系(一對多)
- 2.3 表關系(多對多)
- 2.4 表關系(一對一)
- 2.5 資料庫設計案例
-
- **LX:**
- 3，多表查詢
-
- 3.1 内連接配接查詢
- 3.2 外連接配接查詢
- 3.3 子查詢(嵌套查詢)
- 3.4 案例
- 4，事務
-
- 4.1 概述
- 4.2 文法
- 4.3 代碼驗證
- 4.4 事務的四大特征
mysql進階
今日目标
- 掌握限制的使用
- 掌握表關系及建表原則
- 重點掌握多表查詢操作
- 掌握事務操作
1，限制
上面表中可以看到表中資料存在一些問題：
- id 列一般是用标示資料的唯一性的，而上述表中的id為1的有三條資料，并且
馬花疼
-
柳白
-
馬運
-
柳青
針對上述資料問題，我們就可以從資料庫層面在添加資料的時候進行限制，這個就是限制。
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不支援(check)檢查限制。
這樣是不是就沒辦法保證年齡在指定的範圍内了？從資料庫層面不能保證，以後可以在java代碼中進行限制，一樣也可以實作要求。
-
預設限制： 關鍵字是 DEFAULT
儲存資料時，未指定值則采用預設值。
例如：我們在給english列添加該限制，指定預設值是0，這樣在添加資料時沒有指定具體值時就會采用預設給定的0。
-
外鍵限制： 關鍵字是 FOREIGN KEY
外鍵用來讓兩個表的資料之間建立連結，保證資料的一緻性和完整性。
外鍵限制現在可能還不太好了解，後面我們會重點進行講解。
1.3 非空限制
-
概念
非空限制用于保證列中所有資料不能有NULL值
- 文法
- 添加限制
-- 建立表時添加非空限制 CREATE TABLE 表名( 列名 資料類型 NOT NULL, … );-- 建完表後添加非空限制 ALTER TABLE 表名 MODIFY 字段名 資料類型 NOT NULL; - 删除限制
- 添加限制
1.4 唯一限制
-
概念
唯一限制用于保證列中所有資料各不相同
- 文法
- 添加限制
-- 建立表時添加唯一限制 CREATE TABLE 表名( 列名 資料類型 UNIQUE [AUTO_INCREMENT], -- AUTO_INCREMENT: 當不指定值時自動增長 … ); CREATE TABLE 表名( 列名 資料類型, … [CONSTRAINT] [限制名稱] UNIQUE(列名) );-- 建完表後添加唯一限制 ALTER TABLE 表名 MODIFY 字段名 資料類型 UNIQUE; - 删除限制
- 添加限制
1.5 主鍵限制
-
概念
主鍵是一行資料的唯一辨別，要求非空且唯一
一張表隻能有一個主鍵
- 文法
- 添加限制
-- 建立表時添加主鍵限制 CREATE TABLE 表名( 列名 資料類型 PRIMARY KEY [AUTO_INCREMENT], … ); CREATE TABLE 表名( 列名 資料類型, [CONSTRAINT] [限制名稱] PRIMARY KEY(列名) );-- 建完表後添加主鍵限制 ALTER TABLE 表名 ADD PRIMARY KEY(字段名); - 删除限制
- 添加限制
1.6 預設限制
-
概念
儲存資料時，未指定值則采用預設值
- 文法
- 添加限制
-- 建立表時添加預設限制 CREATE TABLE 表名( 列名 資料類型 DEFAULT 預設值, … );-- 建完表後添加預設限制 ALTER TABLE 表名 ALTER 列名 SET 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, -- 員工姓名，非空且唯一 (中間不用and連接配接 空格直接寫就行了)
joindate DATE not null, -- 入職日期，非空
salary DOUBLE(7,2) not null, -- 工資，非空
bonus DOUBLE(7,2) default 0 -- 獎金，如果沒有将近預設為0
);
通過上面語句可以建立帶有限制的
emp
表，限制能不能發揮作用呢。接下來我們一一進行驗證，先添加一條沒有問題的資料
- 驗證主鍵限制，非空且唯一
執行結果如下：
從上面的結果可以看到，字段
id
不能為null。那我們重新添加一條資料，如下：
執行結果如下：
從上面結果可以看到，1這個值重複了。是以主鍵限制是用來限制資料非空且唯一的。那我們再添加一條符合要求的資料
執行結果如下：
- 驗證非空限制
執行結果如下：
從上面結果可以看到，
ename
字段的非空限制生效了。
- 驗證唯一限制
執行結果如下：
從上面結果可以看到，
ename
字段的唯一限制生效了。
- 驗證預設限制
沒有插入bonus列的值
執行完上面語句後查詢表中資料，如下圖可以看到王五這條資料的bonus列就有了預設值0。
注意：預設限制隻有在不給值時才會采用預設值。如果給了null，那值就是null值。
如下：
執行完上面語句後查詢表中資料，如下圖可以看到趙六這條資料的bonus列的值是null。
- 驗證自動增長： auto_increment 當列是數字類型 并且唯一限制 才可以用auto_increment來修飾
重新建立
emp
表，并給id列添加自動增長
drop table if exists emp;
-- 員工表
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 主表名稱(主表列名稱);
- 删除外鍵限制
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_deptID foreign key(dep_id) references dept(id)
);
添加資料
-- 給部門表添加資料 添加2個部門
insert into dept(dep_name,addr) values('研發部','廣州'),('銷售部','深圳');
-- 給員工表添加資料 dpt_id就是所在部門編号
insert into emp(name,age,dep_id) values
('張三',20,1),
('李四',20,1),
('王五',20,1),
('趙六',20,2),
('孫七',22,2),
('周八',18,2);
此時删除
研發部
這條資料，會發現無法删除。
删除外鍵
重新添加外鍵
dept部門表是主表
emp員工表是從表
Tips：
2，資料庫設計
2.1 資料庫設計簡介
- 軟體的研發步驟
02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 - 資料庫設計概念
- 資料庫設計就是根據業務系統的具體需求，結合我們所選用的DBMS，為這個業務系統構造出最優的資料存儲模型。
- 建立資料庫中的表結構以及表與表之間的關聯關系的過程。
- 有哪些表？表裡有哪些字段？表和表之間有什麼關系？
- 資料庫設計的步驟
- 需求分析（資料是什麼? 資料具有哪些屬性? 資料與屬性的特點是什麼）
-
邏輯分析（通過ER圖對資料庫進行邏輯模組化，不需要考慮我們所選用的資料庫管理系統）
如下圖就是ER(Entity/Relation)圖：
02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 - 實體設計（根據資料庫自身的特點把邏輯設計轉換為實體設計）
- 維護設計（1.對新的需求進行建表；2.表優化）
- 表關系
- 一對一
- 如：使用者 和 使用者詳情
- 一對一關系多用于表拆分，将一個實體中經常使用的字段放一張表，不經常使用的字段放另一張表，用于提升查詢性能
- 一對多
- 如：部門 和 員工
- 一個部門對應多個員工，一個員工對應一個部門。如下圖：
02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務
- 多對多
- 如：商品 和 訂單
- 一個商品對應多個訂單，一個訂單包含多個商品。
- 一對一
2.2 表關系(一對多)
- 一對多
- 如：部門 和 員工
- 一個部門對應多個員工，一個員工對應一個部門。
-
實作方式
在多的一方建立外鍵，指向一的一方的主鍵(多方維護)
-
案例
我們還是以
員工表
部門表
經過分析發現，員工表屬于多的一方，而部門表屬于一的一方，此時我們會在員工表中添加一列（dep_id），指向于部門表的主鍵（id）： 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務
其實前面做過了，可以跳過
建表語句如下：
-- 删除表
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 表關系(多對多)
- 多對多
- 如：商品 和 訂單
- 一個商品對應多個訂單，一個訂單包含多個商品
-
實作方式
建立第三張中間表，中間表至少包含兩個外鍵，分别關聯兩方主鍵
轉換為2個一對多，中間表屬于多方
-
案例
我們以
訂單表
商品表
經過分析發現，訂單表和商品表都屬于多的一方，此時需要建立一個中間表，在中間表中添加訂單表的外鍵和商品表的外鍵指向兩張表的主鍵： 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 此外，中間表還可以記錄一些業務相關的字段，比如商品數量 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 建表語句如下： 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務
-- 删除表
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
);
-- 中間表可以加一些業務字段，比如 count
-- 建完表後，添加外鍵
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)
和一對多的差別，就在于外鍵唯一了
-
案例
我們以
使用者表
02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 而在真正使用過程中發現 id、photo、nickname、age、gender 字段比較常用，此時就可以将這張表拆分成兩張表。
02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務
建表語句如下：
-- 一對一 ==== 此處用于表資訊的拆分
drop table if EXISTS tb_user_desc;
drop table if EXISTS tb_user;
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) -- desc名稱不能用 是關鍵字
);
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, -- unique表明是一對一而非一對多
-- 直接表裡關聯外鍵了 注意這樣tb_user_desc表得先建立了
constraint fk_desc_id foreign key(desc_id) references tb_user_desc(id)
);
2.5 資料庫設計案例
根據下圖設計表及表和表之間的關系：
經過分析，我們分為
專輯表
曲目表
短評表
使用者表
4張表。
一個專輯可以有多個曲目，一個曲目隻能屬于某一張專輯，是以專輯表和曲目表的關系是一對多。
一個專輯可以被多個使用者進行評論，一個使用者可以對多個專輯進行評論，是以專輯表和使用者表的關系是 多對多。
一個使用者可以發多個短評，一個短評隻能是某一個人發的，是以使用者表和短評表的關系是 一對多。
(曲目和短評沒有關系)
LX:
分析
音樂專輯表名：Music
| 字段名 | 資料類型 | 說明 |
|---|---|---|
| title | varchar(32) | 專輯名 |
| alias | varchar(32) | 專輯别名 |
| image | varchar(64) | 封面圖檔 |
| style | varchar(8) | 流派（如經典、流行、民謠、電子等） |
| type | varchar(4) | 類型（專輯、單曲等） |
| medium | varchar(4) | 媒體（CD、黑膠、數字等） |
| publish_time | date | 發行時間 |
| publisher | varchar(16) | 出版者 |
| number | tinyint | 唱片數 |
| barcode | bigint | 條形碼 |
| summary | varchar(1024) | 簡介 |
| artist | varchar(16) | 藝術家 |
| id | int | 編号（唯一） |
曲目表名： Song
| 字段名 | 資料類型 | 說明 |
|---|---|---|
| name | varchar(32) | 歌曲名 |
| serial_number | tinyint | 歌曲序号 |
| id | int | 編号（唯一） |
評論表名：Review
| 字段名 | 資料類型 | 說明 |
|---|---|---|
| content | varchar(256) | 評論内容 |
| rating | tinyint | 評分（1~5） |
| review_time | datetime | 評論時間 |
使用者表名：User
| 字段名 | 資料類型 | 說明 |
|---|---|---|
| username | varchar(16) | 使用者名（唯一） |
| image | varchar(64) | 使用者頭像圖檔位址 |
| signature | varchar(64) | 個人簽名，例如（萬般各所是 一切皆圓滿） |
| nickname | varchar(16) | 使用者昵稱 |
| id | int | 使用者編号(主鍵) |
表關系：
曲目表：一個外鍵
短評表：2個外鍵
建立一張專輯使用者中間表：2個外鍵
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);
執行下面的多表查詢語句
結果如下： (直接就是暴力全連接配接的) 也就是：笛卡爾積
笛卡爾積： 有A,B兩個集合，取A,B所有的組合情況
從上面的結果我們看到有一些無效的資料，如
孫悟空
這個員工屬于1号部門，但也同時關聯的2、3、4号部門。是以我們要通過限制員工表中的
dep_id
字段的值和部門表
did
字段的值相等來消除這些無效的資料，
直接連接配接就是笛卡爾集，連接配接查詢就是設計查詢條件，也就是消除笛卡爾集中的無效資料
執行後結果如下：
上面語句就是連接配接查詢，那麼多表查詢都有哪些呢？
- 連接配接查詢
02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 - 内連接配接查詢 ：相當于查詢AB交集資料
- 外連接配接查詢
- 左外連接配接查詢 ：相當于查詢A表所有資料和交集部門資料
- 右外連接配接查詢 ： 相當于查詢B表所有資料和交集部分資料
- 子查詢
3.1 内連接配接查詢
- 文法
-- 隐式内連接配接
SELECT 字段清單 FROM 表1,表2… WHERE 條件;
-- 顯示内連接配接
SELECT 字段清單 FROM 表1 [INNER] JOIN 表2 ON 條件;
内連接配接相當于查詢 A B 交集資料
- 案例
- 隐式内連接配接
執行上述語句結果如下：SELECT * FROM emp, dept WHERE emp.dep_id = dept.did;-- emp和dept交集 -- 無交集的如：小白龍員工，無部門id沒被查出來 -- 4号銷售部 無任何員工，也沒有被查出來兩張表字段沒有重複時可以偷懶，不過好像不建議，萬一表改了 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 -
查詢 emp的 name， gender，dept表的dname
上面的語句所有内容都查出來了，當隻需要部分字段資訊時
– 字段名稱沒有重複，直接寫字段清單即可
但是若兩張表字段名稱有重複，就得指定清楚了 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 SELECT emp. NAME, emp.gender, dept.dname FROM emp, dept WHERE emp.dep_id = dept.did;執行語句結果同上：
上面語句中使用表名指定字段所屬有點麻煩，sql也支援給表指别名，上述語句可以改進為
執行語句結果同上：SELECT t1. NAME, t1.gender, t2.dname FROM emp t1, dept t2 WHERE t1.dep_id = t2.did; - 顯式内連接配接 （無where了）
執行結果如下：select * from emp inner join dept on emp.dep_id = dept.did; -- 上面語句中的inner可以省略，可以書寫為如下語句 select * from emp join dept on emp.dep_id = dept.did;
不建議的偷懶方式 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 - 隐式内連接配接
select * from emp inner join dept on dep_id = did;
select * from emp join dept on dep_id = did;
執行結果同上
3.2 外連接配接查詢
- 文法
-- 左外連接配接 SELECT 字段清單 FROM 表1 LEFT [OUTER] JOIN 表2 ON 條件; -- 右外連接配接 SELECT 字段清單 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 條件;左外連接配接：相當于查詢A表所有資料和交集部分資料
右外連接配接：相當于查詢B表所有資料和交集部分資料
02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 - 案例
-
查詢emp表所有資料和對應的部門資訊（左外連接配接）
執行語句結果如下：
結果顯示查詢到了左表（emp）中所有的資料及兩張表能關聯的資料。 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 (和内連接配接差別：小白龍沒有部門，但是也查出來了，也就是左邊表的所有資訊都得有)
! 注意有join後面必是on 而非 where !
-
查詢dept表所有資料和對應的員工資訊（右外連接配接）
執行語句結果如下：
結果顯示查詢到了右表（dept）中所有的資料及兩張表能關聯的資料。 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 (銷售部雖然沒有員工，但是也查出來了，也即右邊表的所有資料都得展示出來)
要查詢出部門表中所有的資料，也可以通過左外連接配接實作，隻需要将兩個表的位置進行互換：或者 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務
-
以上代碼都省略了outer, 一般也建議省略
然後左外連接配接用得比較多，右外連接配接都可以用左外連接配接實作啊
3.3 子查詢(嵌套查詢)
-
概念
查詢中嵌套查詢，稱嵌套查詢為子查詢。
什麼是查詢中嵌套查詢呢？我們通過一個例子來看：
需求：查詢工資高于豬八戒的員工資訊。
來實作這個需求，我們就可以通過二步實作，第一步：先查詢出來 豬八戒的工資
第二步：查詢工資高于豬八戒的員工資訊
第二步中的3600可以通過第一步的sql查詢出來，是以将3600用第一步的sql語句進行替換
這就是查詢語句中嵌套查詢語句。
- 子查詢根據查詢結果不同，作用不同
- 子查詢語句結果是單行單列，子查詢語句作為條件值，使用 = != > < 等進行條件判斷
- select 字段清單 from 表 where 字段名 = (子查詢);
- 子查詢語句結果是多行單列，子查詢語句作為條件值，使用 in 等關鍵字進行條件判斷
- select 字段清單 from 表 where 字段名 in (子查詢);
- 子查詢語句結果是多行多列，子查詢語句作為虛拟表
- select 字段清單 from (子查詢) where 條件;
- 子查詢語句結果是單行單列，子查詢語句作為條件值，使用 = != > < 等進行條件判斷
- 案例
- 查詢 ‘财務部’ 和 ‘市場部’ 所有的員工資訊
-- 查詢 '财務部' 或者 '市場部' 所有的員工的部門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’ 之後的員工資訊和部門資訊
先查詢一次，查詢結果作為虛拟表 （Oracle好像叫臨時表）
思路：
最終結果:-- 思路 select * from emp where join_date > '2011-11-11'; -- 查詢入職日期是 '2011-11-11' 之後的員工資訊 select * from emp,dept where emp.dep_id = dept.did; -- 先不管入職日期的篩選 -- 第一句的查詢結果才是第二句想要的emp -- 虛拟表喽~ 必須取别名哈 select * from (select * from emp where join_date > '2011-11-11') t1,dept where t1.dep_id = dept.did;-- 查詢入職日期是 '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; 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務
- 查詢 ‘财務部’ 和 ‘市場部’ 所有的員工資訊
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, -- 部門id
dname 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, -- 員工id
ename VARCHAR(50), -- 員工姓名
job_id INT, -- 職務id
mgr 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. 員工編号，員工姓名，工資 資訊在emp 員工表中 2. 職務名稱，職務描述 資訊在 job 職務表中 3. job 職務表 和 emp 員工表 是 一對多的關系 emp.job_id = job.id */ -- 方式一 ：隐式内連接配接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description FROM emp, job WHERE emp.job_id = job.id; -- 方式二 ：顯式内連接配接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description FROM emp INNER JOIN job ON emp.job_id = job.id; - 查詢員工編号，員工姓名，工資，職務名稱，職務描述，部門名稱，部門位置
/* 分析： 1. 員工編号，員工姓名，工資 資訊在emp 員工表中 2. 職務名稱，職務描述 資訊在 job 職務表中 3. job 職務表 和 emp 員工表 是 一對多的關系 emp.job_id = job.id 4. 部門名稱，部門位置 來自于 部門表 dept 5. dept 和 emp 一對多關系 dept.id = emp.dept_id */ -- 方式一 ：隐式内連接配接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description, dept.dname, dept.loc FROM emp, job, dept WHERE emp.job_id = job.id and dept.id = emp.dept_id ; -- 方式二 ：顯式内連接配接 SELECT emp.id, emp.ename, emp.salary, job.jname, job.description, dept.dname, dept.loc FROM emp INNER JOIN job ON emp.job_id = job.id INNER JOIN dept ON dept.id = emp.dept_id -- inner可以省略不寫 - 查詢員工姓名，工資，工資等級
/* 分析： 1. 員工姓名，工資 資訊在emp 員工表中 2. 工資等級 資訊在 salarygrade 工資等級表中 3. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary */ SELECT emp.ename, emp.salary, t2.grade FROM emp, salarygrade t2 WHERE emp.salary >= t2.losalary AND emp.salary <= t2.hisalary -- 寫法二： select emp.ename,emp.salary,salarygrade.grade from emp,salarygrade where emp.salary between salarygrade.losalary and hisalary; - 查詢員工姓名，工資，職務名稱，職務描述，部門名稱，部門位置，工資等級
/* 分析： 1. 員工編号，員工姓名，工資 資訊在emp 員工表中 2. 職務名稱，職務描述 資訊在 job 職務表中 3. job 職務表 和 emp 員工表 是 一對多的關系 emp.job_id = job.id 4. 部門名稱，部門位置 來自于 部門表 dept 5. dept 和 emp 一對多關系 dept.id = emp.dept_id 6. 工資等級 資訊在 salarygrade 工資等級表中 7. emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary */ -- 隐式内連接配接 select emp.ename,emp.salary, job.jname,job.description, dept.dname,dept.loc, sal.grade from emp,job,dept,salarygrade sal where emp.job_id = job.id and emp.dept_id = dept.did and emp.salary between sal.losalary and sal.hisalary; -- 顯式内連接配接 寫一遍 select emp.ename,emp.salary, job.jname,job.description, dept.dname,dept.loc, sal.grade from emp join job on emp.job_id = job.id join dept on emp.dept_id = dept.did join salarygrade sal on emp.salary between sal.losalary and sal.hisalary; -
查詢出部門編号、部門名稱、部門位置、部門人數
部門人數: 在emp表中 按照dept_id 進行分組，然後count(*)統計數量 先查這個 再查部門資訊
/* 分析： 1. 部門編号、部門名稱、部門位置 來自于部門 dept 表 2. 部門人數: 在emp表中 按照dept_id 進行分組，然後count(*)統計數量 3. 使用子查詢，讓部門表和分組後的表進行内連接配接 */ -- 根據部門id分組查詢每一個部門id和員工數 select dept_id, count(*) from emp group by dept_id; SELECT dept.id, dept.dname, dept.loc, t1.count FROM dept, ( SELECT dept_id, count(*) count FROM emp GROUP BY dept_id ) t1 WHERE dept.id = t1.dept_id
/*
分析：
1. 部門編号、部門名稱、部門位置 來自于部門 dept 表
2. 部門人數: 在emp表中 按照dept_id 進行分組，然後count(*)統計數量
3. 使用子查詢，讓部門表和分組後的表進行左外連接配接 (内連接配接感覺有點不符合題幹要求)
*/
select emp.dept_id,count(dept_id) 'num' from emp GROUP BY dept_id;
select * from dept;
-- 就是将上面查詢得到的兩張表進行聯合查詢就行了
select
dept.did,dept.dname,dept.loc,t2.num
from
dept
left join (select emp.dept_id,count(dept_id) 'num' from emp GROUP BY dept_id) t2
on dept.did = t2.dept_id;
-- 用左外連接配接 否則财務部沒人就查不出來了
4，事務
4.1 概述
資料庫的事務（Transaction）是一種機制、一個操作序列，包含了一組資料庫操作指令。
事務把所有的指令作為一個整體一起向系統送出或撤銷操作請求，即這一組資料庫指令要麼同時(全部)成功，要麼同時(全部)失敗。
事務是一個不可分割的工作邏輯單元。
這些概念不好了解，接下來舉例說明，如下圖有一張表
張三和李四賬戶中各有100塊錢，現李四需要轉換500塊錢給張三，具體的轉賬操作為
- 第一步：查詢李四賬戶餘額
- 第二步：從李四賬戶金額 -500
- 第三步：給張三賬戶金額 +500
現在假設在轉賬過程中第二步完成後出現了異常第三步沒有執行，就會造成李四賬戶金額少了500，而張三金額并沒有多500；這樣的系統是有問題的。如果解決呢？使用事務可以解決上述問題
從上圖可以看到在轉賬前開啟事務，如果出現了異常復原事務，三步正常執行就送出事務，這樣就可以完美解決問題。
4.2 文法
- 開啟事務
START TRANSACTION; 或者 BEGIN; - 送出事務
- 復原事務
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); - 不加事務示範問題
整體執行結果肯定會出問題，我們查詢賬戶表中資料，發現李四賬戶少了500。-- 轉賬操作 -- 1. 查詢李四賬戶金額是否大于500 -- 2. 李四賬戶 -500 UPDATE account set money = money - 500 where name = '李四'; 出現異常了... -- 此處不是注釋，在整體執行時會出問題，後面的sql則不執行 -- 3. 張三賬戶 +500 UPDATE account set money = money + 500 where name = '張三'; 02-mysql進階-限制(主鍵,外鍵,非空,唯一,預設)、設計(一對多，多對多)、多表查詢(内連接配接(顯式和隐式)，外連接配接，子查詢(嵌套查詢))、事務 - 添加事務sql如下：
上面sql中的執行成功進選擇執行送出事務，而出現問題則執行復原事務的語句。以後我們肯定不可能這樣操作，而是在java中進行操作，在java中可以抓取異常，沒出現異常送出事務，出現異常復原事務。-- 開啟事務 BEGIN; -- 轉賬操作 -- 1. 查詢李四賬戶金額是否大于500 -- 2. 李四賬戶 -500 UPDATE account set money = money - 500 where name = '李四'; 出現異常了... -- 此處不是注釋，在整體執行時會出問題，後面的sql則不執行 -- 3. 張三賬戶 +500 UPDATE account set money = money + 500 where name = '張三'; -- 送出事務 (未出異常) COMMIT; -- 復原事務 （出現異常了） ROLLBACK;
4.4 事務的四大特征
- 原子性（Atomicity）: 事務是不可分割的最小操作機關，要麼同時成功，要麼同時失敗
- 一緻性（Consistency） :事務完成時，必須使所有的資料都保持一緻狀态
- 隔離性（Isolation） :多個事務之間，操作的可見性 (兩個(使用者)視窗之間的操作是否可見? 隔離性越強，越不可見，性能越低)
- 持久性（Durability） :事務一旦送出或復原，它對資料庫中的資料的改變就是永久的
說明：
mysql中事務是自動送出的。
也就是說我們不添加事務執行sql語句，語句執行完畢會自動的送出事務。
可以通過下面語句查詢預設送出方式：
查詢到的結果是1 則表示自動送出，結果是0表示手動送出。當然也可以通過下面語句修改送出方式
Oracle是手動送出的，寫完一句sql語句後必須自己手動commit