Mysql入門【Mysql約束】

Li_CSDN_Li發表於2020-11-20

MySql

學習目標

能夠使用SQL語句進行排序
能夠使用聚合函式
能夠使用SQL語句進行分組查詢
能夠完成資料的備份和恢復
能夠使用SQL語句新增主鍵、外來鍵、唯一、非空約束
能夠說出多表之間的關係及其建表原則

1. DQL查詢語句-條件查詢

目標

能夠掌握條件查詢語法格式

講解

前面我們的查詢都是將所有資料都查詢出來，但是有時候我們只想獲取到滿足條件的資料
語法格式：

SELECT 欄位名... FROM 表名 WHERE 條件;

流程：取出表中的每條資料，滿足條件的記錄就返回，不滿足條件的記錄不返回

準備資料

CREATE TABLE student (
  id int,
  name varchar(20),
  age int,
  sex varchar(5),
  address varchar(100),
  math int,
  english int
);

INSERT INTO student(id,name,age,sex,address,math,english) VALUES (1,'馬雲',55,'男','杭州',66,78),(2,'馬化騰',45,'女','深圳',98,87),(3,'馬景濤',55,'男','香港',56,77),(4,'柳巖',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'劉德華',57,'男','香港',99,99),(7,'馬德',22,'女','香港',99,99),(8,'德瑪西亞',18,'男','南京',56,65);

比較運算子

>大於
<小於
<=小於等於
>=大於等於
=等於
<>、!=不等於

【練習】

查詢math分數大於80分的學生

SELECT * FROM student WHERE math>80;

在這裡插入圖片描述

查詢english分數小於或等於80分的學生

SELECT * FROM student WHERE english<=80;

在這裡插入圖片描述

查詢age等於20歲的學生

SELECT * FROM student WHERE age=20;

在這裡插入圖片描述

查詢age不等於20歲的學生

SELECT * FROM student WHERE age!=20;
SELECT * FROM sstudentWHERE age<>20;

在這裡插入圖片描述

邏輯運算子

and(&&) 多個條件同時滿足
or(||) 多個條件其中一個滿足
not(!) 不滿足

【練習】

查詢age大於35且性別為男的學生(兩個條件同時滿足)
```
SELECT * FROM student WHERE  age>35 AND sex='男';
```

在這裡插入圖片描述

查詢age大於35或性別為男的學生(兩個條件其中一個滿足)
```
SELECT * FROM student WHERE age>35 OR sex='男';
```

在這裡插入圖片描述

查詢id是1或3或5的學生

SELECT * FROM student WHERE id=1 OR id=3 OR id=5;

在這裡插入圖片描述

in關鍵字
【語法格式】

SELECT * FROM 表名 WHERE 欄位 in (值1, 值2, 值3);

in裡面的每個資料都會作為一次條件，只要滿足條件的就會顯示

【練習】

查詢id是1或3或5的學生

SELECT * FROM student WHERE id IN (1,3,5);

在這裡插入圖片描述

查詢id不是1或3或5的學生

SELECT * FROM student WHERE id NOT IN (1,3,5);

在這裡插入圖片描述

範圍

between關鍵字

BETWEEN 值1 AND 值2 -- 表示從值1到值2範圍，包頭又包尾

比如：age BETWEEN 80 AND 100
相當於： age>=80 && age<=100

【練習】

查詢english成績大於等於75，且小於等於90的學生

SELECT * FROM student WHERE english>=75 AND english<=90;
SELECT * FROM sstudentWHERE english BETWEEN 75 AND 90;

在這裡插入圖片描述

2. 模糊查詢like

目標

能夠掌握模糊查詢語法格式

在這裡插入圖片描述

講解

`LIKE`表示模糊查詢

【語法格式】

SELECT * FROM 表名 WHERE 欄位名 LIKE '萬用字元字串';

滿足萬用字元字串規則的資料就會顯示出來所謂的萬用字元字串就是含有萬用字元的字串

MySQL萬用字元有兩個：

%: 表示0個1個n個字元(任意多個字元)
_: 表示1個字元

【練習】

查詢姓馬的學生

SELECT * FROM student WHERE NAME LIKE '馬%';

在這裡插入圖片描述

查詢姓名中包含德字的學生

SELECT * FROM student WHERE NAME LIKE '%德%';

在這裡插入圖片描述

查詢姓馬，且姓名有三個字的學生

SELECT * FROM student WHERE NAME LIKE '馬__';

在這裡插入圖片描述

小結

模糊查詢格式: SELECT 欄位 FROM 表名 WHERE 欄位 LIKE ‘萬用字元字串’;

%:表示零個一個多個字元(任意多個字元)
_:表示一個字元

3. DQL查詢語句-排序

目標

能夠掌握對查詢的資料進行排序

在這裡插入圖片描述

講解

通過ORDER BY子句，可以將查詢出的結果進行排序(排序只是顯示方式，不會影響資料庫中資料的順序)

【語法格式】

SELECT 欄位 FROM 表名 ORDER BY 排序的欄位 [ASC|DESC];

ASC: 升序排序(預設)
DESC: 降序排序

1. 單列排序

單列排序就是使用一個欄位排序

【練習】

查詢所有資料,使用年齡降序排序
```
SELECT * FROM student ORDER BY age DESC;
```

在這裡插入圖片描述

2.組合排序

組合排序就是先按第一個欄位進行排序，如果第一個欄位相同，才按第二個欄位進行排序，依次類推。
上面的例子中，年齡是有相同的。當年齡相同再使用math進行排序

SELECT 欄位名 FROM 表名 WHERE 欄位=值 ORDER BY 欄位名1 [ASC|DESC], 欄位名2 [ASC|DESC];

【練習】

查詢所有資料,在年齡降序排序的基礎上，如果年齡相同再以數學成績降序排序
```
SELECT * FROM student ORDER BY age DESC, math DESC;
```

在這裡插入圖片描述

小結

排序的關鍵字：ORDER BY 欄位名
升序：ASC
降序：DESC

4. DQL查詢語句-聚合函式

目標

能夠掌握五個聚合函式的使用

講解

之前我們做的查詢都是橫向查詢，它們都是根據條件一行一行的進行判斷，而使用聚合函式查詢是縱向查詢，它是對一列的值進行計算，然後返回一個結果值。另外聚合函式會忽略空值。

五個聚合函式：

count：統計指定列記錄數，記錄為NULL的不統計
sum：計算指定列的數值和，如果不是數值型別，那麼計算結果為0
max：計算指定列的最大值
min：計算指定列的最小值
avg：計算指定列的平均值

【語法格式】

聚合函式的使用：寫在 SQL語句SELECT後 欄位名的地方

SELECT 欄位名... FROM 表名;
SELECT 聚合函式(欄位) FROM 表名;

【練習】

查詢學生總數
```
SELECT COUNT(english) FROM student;
```

在這裡插入圖片描述

我們發現對於NULL的記錄不會統計
IFNULL(expr1, expr2)的用法：
假如expr1 不為 NULL，則 IFNULL() 的返回值為 expr1; 否則其返回值為expr2

-- 查詢英語成績，若值為null，使用'未知成績'字串替換。
SELECT IFNULL(english,'未知成績') FROM student;

在這裡插入圖片描述

統計數量常用:

SELECT COUNT(*) FROM student;

在這裡插入圖片描述

查詢年齡大於40的總人數

SELECT COUNT(*) FROM student WHERE age>40;

在這裡插入圖片描述

查詢數學成績總分
```
SELECT SUM(math) FROM student;
```

在這裡插入圖片描述

查詢數學成績最高分
```
SELECT MAX(math) FROM student;
```

在這裡插入圖片描述

查詢數學成績最低分
```
SELECT MIN(math) FROM student;
```

在這裡插入圖片描述

查詢數學成績平均分
```
SELECT AVG(math) FROM student;
```

在這裡插入圖片描述

小結

sum(列名)	求和
count(列名)	統計數量
max(列名)	最大值
min(列名)	最小值
avg(列名)	平均值

5.DQL查詢語句-分組

目標

能夠對查詢後的結果進行分組

在這裡插入圖片描述

講解

group by關鍵字

分組查詢是指使用 GROUP BY語句對查詢資訊進行分組，相同資料作為一組

【語法格式】

SELECT 欄位 FROM 表名 WHERE 條件 GROUP BY 欄位名;

GROUP BY怎麼分組的？
將分組欄位結果中相同內容作為一組

SELECT * FROM student GROUP BY sex;

這句話會將sex相同的資料作為一組
在這裡插入圖片描述

GROUP BY將分組欄位的相同值作為一組，並且返回每組的第一條資料，所以單獨分組沒什麼用處。分組的目的就是為了統計，一般分組會跟聚合函式一起使用。

分組後聚合函式的作用？不是操作所有資料，而是操作一組資料。

SELECT SUM(math), sex FROM student GROUP BY sex;

效果如下：
在這裡插入圖片描述

實際上是將每組的math進行求和,返回每組統計的結果
在這裡插入圖片描述

注意事項：

當我們使用某個欄位分組,在查詢的時候也需要將這個欄位查詢出來,否則看不到資料屬於哪組的

查詢的時候沒有查詢出分組欄位

查詢的時候查詢出分組欄位

【練習】

按性別分組
```
SELECT sex FROM student GROUP BY sex;
```

在這裡插入圖片描述

查詢男女各多少人

-- 1.查詢所有資料,按性別分組。 
-- 2.統計每組人數
SELECT sex, COUNT(*) FROM student GROUP BY sex;

在這裡插入圖片描述

查詢年齡大於25歲的人,按性別分組,統計每組的人數

1.先過濾掉年齡小於25歲的人。
2.再分組。
3.最後統計每組的人數
SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex;

在這裡插入圖片描述

having關鍵字

查詢年齡大於25歲的人,按性別分組,統計每組的人數,並只顯示性別人數大於2的資料
有很多同學可能會將SQL語句寫出這樣:

SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex WHERE COUNT(*) >2;

注意: 並只顯示性別人數>2的資料屬於分組後的條件,對於分組後的條件需要使用having子句

SELECT sex, COUNT(*) FROM student WHERE age > 25 GROUP BY sex HAVING COUNT(*) >2;
只有分組後人數大於2的`男`這組資料顯示出來

在這裡插入圖片描述

having與where的區別

區別	having	where
使用場景	分組後對資料進行過濾	分組前對資料進行過濾
是否可以使用聚合函式	可以	不可以

小結

分組的語法格式？
SELECT 欄位 FROM 表名 WHERE 條件 GROUP BY 欄位 HAVING 條件;
分組的原理？
先將相同資料作為一組,返回每組的第一條資料,單獨分組沒有意義,分組後跟聚合函式操作

6. DQL查詢語句-limit語句

目標

能夠掌握limit語句的使用

在這裡插入圖片描述

講解

【準備資料】

INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES 
(9,'唐僧',25,'男','長安',87,78),
(10,'孫悟空',18,'男','花果山',100,66),
(11,'豬八戒',22,'男','高老莊',58,78),
(12,'沙僧',50,'男','流沙河',77,88),
(13,'白骨精',22,'女','白虎嶺',66,66),
(14,'蜘蛛精',23,'女','盤絲洞',88,88);

LIMIT關鍵字

LIMIT是限制的意思，所以LIMIT的作用就是限制查詢記錄的條數。

【語法格式】

SELECT 欄位 FROM 表名 WHERE 條件 LIMIT offset, length;

offset是指偏移量，可以認為是跳過的記錄數量，不寫則預設為0。
length是指需要顯示的總記錄數

【練習】

查詢學生表中資料，跳過前面2條，顯示6條

我們可以認為跳過前面2條，取6條資料
SELECT * FROM student LIMIT 2,6;

在這裡插入圖片描述

LIMIT的使用場景：分頁

比如我們登入京東，淘寶，返回的商品資訊可能有幾萬條，不是一次全部顯示出來。是一頁顯示固定的條數。假設我們一每頁顯示5條記錄的方式來分頁。

在這裡插入圖片描述

假設我們一每頁顯示5條記錄的方式來分頁，SQL語句如下：

-- 每頁顯示5條
-- 第一頁： LIMIT 0,5;	跳過0條，顯示5條
-- 第二頁： LIMIT 5,5;  跳過5條，顯示5條
-- 第三頁： LIMIT 10,5; 跳過10條，顯示5條
SELECT * FROM student LIMIT 0,5;
SELECT * FROM student LIMIT 5,5;
SELECT * FROM student LIMIT 10,5;

在這裡插入圖片描述

注意：

如果第一個引數是0可以簡寫：
SELECT * FROM student LIMIT 0,5;
SELECT * FROM student LIMIT 5;
LIMIT 10，5;
不夠5條，有多少顯示多少

小結

LIMIT語句的使用格式？

SELECT 欄位 FROM 表名 LIMIT 跳過的條數, 顯示條數;

SELECT 欄位名 FROM 表名 
WHERE 條件 
GROUP BY 分組列名 
HAVING 條件 
ORDER BY 排序列名 
LIMIT 跳過行數, 返回行數;

7. 資料庫備份

目標

能夠使用命令列的方式備份和還原表中的資料

講解

備份的應用場景

在伺服器進行資料傳輸、資料儲存和資料交換，就有可能產生資料故障。比如發生意外停機或儲存介質損壞。這時，如果沒有采取資料備份和資料恢復手段與措施，就會導致資料的丟失，造成的損失是無法彌補與估量的。

在這裡插入圖片描述

命令列方式備份與還原

備份格式：

注意：這個操作不用登入

mysqldump -u使用者名稱 -p密碼 資料庫 > 檔案的路徑

還原格式：

注意：還原的時候需要先登入MySQL,並選中對應的資料庫

SOURCE 匯入檔案的路徑

【練習】：

備份day02資料庫中的資料

mysqldump -uroot -proot day02 >D:\day02.sql

在這裡插入圖片描述

【注意】有些情況會出現備份不成功情況，報錯如下

mysqldump: unknow option '--no-beep'

在這裡插入圖片描述

解決辦法，在mysqldump後面加上：--no-defaults

在這裡插入圖片描述

如果-p後密碼明寫報錯，那麼-p留空。

在這裡插入圖片描述

資料庫中的所有表和資料都會匯出成SQL語句
在這裡插入圖片描述

還原day02資料庫中的資料
1. 刪除day02資料庫中的所有表

在這裡插入圖片描述

備份需要登入MySQL
```
mysql -uroot -proot
```

選中資料庫

use day02;
select database();--【檢視當前選中資料庫】

在這裡插入圖片描述

使用SOURCE命令還原資料
```
source D:\day02.sql
```
【注意】命令後面不要加分號

在這裡插入圖片描述

小結

使用命令列的方式備份和還原表中的資料

在這裡插入圖片描述

8. 資料庫約束的概述

目標

能夠說出資料庫約束的作用

講解

資料庫約束的作用

對錶中的資料進行進一步的限制，保證資料的正確性、有效性和完整性。

約束種類

PRIMARY KEY: 主鍵約束
UNIQUE: 唯一約束
NOT NULL: 非空約束
DEFAULT: 預設值
FOREIGN KEY: 外來鍵約束

小結

資料庫約束的作用？
對錶中的資料進行進一步的限制，保證資料的正確性、有效性和完整性。

9. 主鍵約束

目標

能夠說出主鍵約束的作用
能夠新增和刪除主鍵

講解

主鍵的作用

用來唯一標識一條記錄。

為什麼需要主鍵約束

有些記錄的 name,age,score 欄位的值都一樣時,那麼就沒法區分這些資料,造成資料庫的記錄不唯一,這樣就不方便管理資料。
在這裡插入圖片描述

每張表都應該有一個主鍵，並且每張表主鍵值要唯一。

哪個欄位作為表的主鍵

通常不用業務欄位作為主鍵，單獨給每張表設計一個id的欄位，把id作為主鍵。主鍵是給資料庫和程式使用的，不是給最終的客戶使用的。所以主鍵有沒有含義沒有關係，只要不重複，非空就行。

建立主鍵

主鍵：PRIMARY KEY
主鍵的特點：

主鍵必須包含唯一的值
主鍵列不能包含NULL值

建立主鍵方式：

在建立表的時候給欄位新增主鍵

欄位名 欄位型別 PRIMARY KEY

id int primary key

在已有表中新增主鍵

ALTER TABLE 表名 ADD PRIMARY KEY(欄位名);

alter table student add primary key(id);

【練習】

建立表學生表st5, 包含欄位(id, name, age)將id做為主鍵

CREATE TABLE st5 (
	id INT PRIMARY KEY, -- id是主鍵
	NAME VARCHAR(20),
	age INT
);

在這裡插入圖片描述

新增資料

INSERT INTO st5 (id, NAME) VALUES (1, '唐伯虎');
INSERT INTO st5 (id, NAME) VALUES (2, '周文賓');
INSERT INTO st5 (id, NAME) VALUES (3, '祝枝山');
INSERT INTO st5 (id, NAME) VALUES (4, '文徵明');

插入重複的主鍵值

-- 主鍵是唯一的不能重複：Duplicate entry '1' for key 'PRIMARY'
INSERT INTO st5 (id, NAME) VALUES (1, '文徵明2');

插入NULL的主鍵值

-- 主鍵是不能為空的：Column 'id' cannot be null
INSERT INTO st5 (id, NAME) VALUES (NULL, '文徵明3');

刪除主鍵

【語法格式】

ALTER TABLE 表名 DROP PRIMARY KEY;

【練習】

刪除st5表的主鍵
```
ALTER TABLE st5 DROP PRIMARY KEY;
```

在這裡插入圖片描述

小結

說出主鍵約束的作用？

唯一區分一條記錄
主鍵的特點？

唯一,不能為NULL

新增和刪除主鍵？

欄位名 欄位型別 PRIMARY KEY
ALTER TABLE 表名 ADD PRIMARY KEY(欄位名);

刪除主鍵
```
ALTER TABLE 表名 DROP PRIMARY KEY;
```

10. 主鍵自增

目標

能夠設定主鍵為自動增長

講解

主鍵如果讓我們自己新增很有可能重複,我們通常希望在每次插入新記錄時,資料庫自動生成主鍵欄位的值

【語法格式】

欄位名 欄位型別 PRIMARY KEY AUTO_INCREMENT

AUTO_INCREMENT 表示自動增長【欄位型別必須是整數型別】設定後預設從1開始自增

【練習】

建立學生表st6, 包含欄位(id, name, age)將id做為主鍵並自動增長

CREATE TABLE st6 (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT
);

插入資料

-- 主鍵預設從1開始自動增長
INSERT INTO st6 (NAME, age) VALUES 
('唐僧', 22),
('孫悟空', 26),
('豬八戒', 25),
('沙僧', 20);

在這裡插入圖片描述

【擴充套件】

如果建表時需要指定某個值開始，可以在結尾加上 auto_increment=值，如下：

CREATE TABLE st6 (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT
)AUTO_INCREMENT=100;
#指定自增從100開始

如果希望修改起始值,請使用下列SQL語法
```
ALTER TABLE 表名 AUTO_INCREMENT=起始值;
```

【DELETE和TRUNCATE的區別】

這兩個關鍵字可以用來對錶進行刪除內容。

DELETE 刪除表中的資料，但不重置AUTO_INCREMENT的值。
TRUNCATE 摧毀表，重建表，AUTO_INCREMENT重置為1

小結

設定主鍵為自動增長格式？

欄位名 資料型別 PRIMARY KEY AUTO_INCREMENT

11. 唯一約束

目標

能夠說出唯一約束的作用
能夠新增唯一約束

講解

【作用】

在這張表中這個欄位的值不能重複

【基本格式】

欄位名 欄位型別 UNIQUE

【練習】

建立學生表st7, 包含欄位(id, name),name這一列設定唯一約束,不能出現同名的學生
```
CREATE TABLE st7 (
	id INT,
	NAME VARCHAR(20) UNIQUE
);	
```

新增一些學生

INSERT INTO st7 VALUES (1, '貂蟬');
INSERT INTO st7 VALUES (2, '西施');
INSERT INTO st7 VALUES (3, '王昭君');
INSERT INTO st7 VALUES (4, '楊玉環');

-- 插入相同的名字出現name重複: Duplicate entry '貂蟬' for key 'name'
INSERT INTO st7 VALUES (5, '貂蟬');
-- 出現多個null的時候會怎樣？因為null是沒有值，所以不存在重複的問題
INSERT INTO st3 VALUES (5, NULL);
INSERT INTO st3 VALUES (6, NULL);

小結

說出唯一約束的作用？
讓這個欄位的值不能重複
新增唯一約束格式？
欄位名欄位型別 UNIQUE

12. 非空約束

目標

能夠說出非空約束的作用
能夠新增非空約束

講解

【作用】這個欄位必須設定值,不能是NULL

【語法格式】

欄位名 欄位型別 NOT NULL

【練習】

建立表學生表st8, 包含欄位(id,name,gender)其中name不能為NULL

CREATE TABLE st8 (
	id INT,
	NAME VARCHAR(20) NOT NULL,
	gender CHAR(2)
);

新增一些完整的記錄

INSERT INTO st8 VALUES (1, '郭富城', '男');
INSERT INTO st8 VALUES (2, '黎明', '男');
INSERT INTO st8 VALUES (3, '張學友', '男');
INSERT INTO st8 VALUES (4, '劉德華', '男');

-- 姓名不賦值出現姓名不能為null: Column 'name' cannot be null
INSERT INTO st8 VALUES (5, NULL, '男');

小結

非空約束的格式:

欄位名資料型別 NOT NULL

13. 預設值

目標

能夠說出預設值的作用
能夠給欄位新增預設值

講解

【作用】

往表中新增資料時,如果不指定這個欄位的資料,就使用預設值

【語法格式】

欄位名 欄位型別 DEFAULT 預設值

【練習】

建立一個學生表 st9，包含欄位(id,name,address)，地址預設值是廣州

CREATE TABLE st9 (
	id INT,
	NAME VARCHAR(20),
	address VARCHAR(50) DEFAULT '廣州'
);

新增一條記錄,使用預設地址

INSERT INTO st9 (id, NAME) VALUES (1, '劉德華');

在這裡插入圖片描述

新增一條記錄,不使用預設地址

INSERT INTO st9 VALUES (2, '張學友', '香港');

面試題:

如果一個欄位設定了非空與唯一約束，該欄位與主鍵的區別?

1.一張表只有一個主鍵

2.一張表可以多個欄位新增非空與唯一約束

3.主鍵可以自動增長,自己新增的非空與唯一約束欄位無法自動增長

小結

說出預設值的作用？
不新增這個欄位,就會使用預設值
給欄位新增預設值格式？
欄位名欄位型別 DEFAULT 預設值

14. 外來鍵約束

目標

能夠說出外來鍵約束的作用
能夠建立外來鍵約束

講解

1. 單表的缺點

建立一個員工表包含如下列(id, name, age, dep_name, dep_location),id主鍵並自動增長,新增5條資料

CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_name VARCHAR(30),
	dep_location VARCHAR(30)
);

-- 新增資料
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES 
('張三', 20, '研發部', '廣州'),
('李四', 21, '研發部', '廣州'),
('王五', 20, '研發部', '廣州'),
('老王', 20, '銷售部', '深圳'),
('大王', 22, '銷售部', '深圳'),
('小王', 18, '銷售部', '深圳');

【缺點】表中出現了很多重複的資料(資料冗餘)，如果要修改研發部的地址需要修改3個地方。
在這裡插入圖片描述

【解決方案】將一張表分成2張表(員工表和部門表)
在這裡插入圖片描述

-- 建立部門表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 建立員工表
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
);

-- 新增2個部門
INSERT INTO department (dep_name, dep_location) VALUES ('研發部', '廣州'), ('銷售部', '深圳');

-- 新增員工,dep_id表示員工所在的部門
INSERT INTO employee (NAME, age, dep_id) VALUES 
('張三', 20, 1), 
('李四', 21, 1), 
('王五', 20, 1), 
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

問題：
當我們在employee的dep_id裡面輸入不存在的部門,資料依然可以新增.但是並沒有對應的部門，不能出現這種情況。employee的dep_id中的內容只能是department表中存在的id
在這裡插入圖片描述

需要達到目的:需要約束dep_id只能是department表中已經存在id
解決方式:使用外來鍵約束

2. 什麼是外來鍵約束

一張表中的某個欄位引用另一張表的主鍵
【主表】：約束別人的表,把資料給別人使用功能
【副表/從表】：被別人約束的表,使用別人的資料
在這裡插入圖片描述

3. 建立外來鍵

新建表時增加外來鍵：
```
CONSTRAINT [外來鍵約束名稱] FOREIGN KEY(外來鍵欄位名) REFERENCES 主表名(主鍵欄位名)
```
關鍵字解釋：
CONSTRAINT: 新增約束
FOREIGN KEY(外來鍵欄位名): 將某個欄位作為外來鍵
REFERENCES 主表名(主鍵欄位名) : 外來鍵引用主表的主鍵

已有表增加外來鍵：

ALTER TABLE 從表 ADD [CONSTRAINT] [外來鍵約束名稱] FOREIGN KEY (外來鍵欄位名) REFERENCES 主表(主鍵欄位名);

【練習】

刪除副表/從表 employee，如果存在

建立從表 employee 並新增外來鍵約束

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	-- 新增一個外來鍵
	-- 外來鍵取名按公司要求,一般fk結尾
	CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);

正常新增資料

INSERT INTO employee (NAME, age, dep_id) VALUES
('張三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

部門錯誤的資料新增失敗

INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);

4. 刪除外來鍵

【語法格式】

ALTER TABLE 表名 DROP FOREIGN KEY 外來鍵名稱;

【練習】

刪除employee表的emp_depid_ref_dep_id_fk外來鍵

ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;

在employee表情存在況下新增外來鍵

ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);

小結

建立外來鍵約束格式？

CONSTRAINT [外來鍵約束名稱] FOREIGN KEY(外來鍵欄位名) REFERENCES 主表名(主鍵欄位名)

刪除外來鍵格式？

ALTER TABLE 表名 DROP FROEIGN KEY 外來鍵名稱;

15. 外來鍵的級聯

目標

瞭解外來鍵的級聯操作

講解

要把部門表中的id值2，改成5，能不能直接修改呢？

UPDATE department SET id=5 WHERE id=2;

不能直接修改:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(員工表)中有引用的資料,不能直接修改主表(部門表)主鍵

要刪除部門id等於1的部門, 能不能直接刪除呢？

DELETE FROM department WHERE id = 1;

不能直接刪除:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(員工表)中有引用的資料,不能直接刪除主表(部門表)資料

什麼是級聯操作

【概念】在修改和刪除主表的主鍵時，同時更新或刪除副表的外來鍵值，稱為級聯操作

ON UPDATE CASCADE -- 級聯更新，主鍵發生更新時，外來鍵也會更新
ON DELETE CASCADE -- 級聯刪除，主鍵發生刪除時，外來鍵也會刪除

以上語句放在外來鍵定義後面。

【例如】級聯更新+級聯刪除

CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE

【練習】

刪除employee表

重新建立employee表，新增級聯更新和級聯刪除

CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_id INT,
	-- 新增外來鍵約束,並且新增級聯更新和級聯刪除
	CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
);

再次新增資料到員工表和部門表

INSERT INTO employee (NAME, age, dep_id) VALUES 
('張三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

把部門表中id等於1的部門改成id等於10
```
UPDATE department SET id=10 WHERE id=1;
```

在這裡插入圖片描述

刪除部門號是2的部門
```
DELETE FROM department WHERE id=2;
```

在這裡插入圖片描述

小結

級聯更新：ON UPDATE CASCADE 主鍵修改後,外來鍵也會跟著修改

級聯刪除：ON DELETE CASCADE 主鍵刪除後,外來鍵對應的資料也會刪除

16. 表關係的概念

目標

能夠理解表之間的3種關係

講解

現實生活中，實體與實體之間肯定是有關係的，比如：老公和老婆，部門和員工，老師和學生等。那麼我們在設計表的時候，就應該體現出表與表之間的這種關係！分成三種：

一對一
一對多
多對多

1. 一對多

例如：班級和學生，部門和員工，客戶和訂單，分類和商品
一對多建表原則:
在從表(多方)建立一個欄位作為外來鍵指向主表(一方)的主鍵

在這裡插入圖片描述

2. 多對多

例如：老師和學生，學生和課程，使用者和角色
多對多關係建表原則:
需要建立第三張表，中間表中至少兩個欄位，這兩個欄位分別作為外來鍵指向各自一方的主鍵。
在這裡插入圖片描述

3. 一對一

在實際的開發中應用不多.因為一對一可以建立成一張表。

兩種建表原則：

外來鍵唯一：主表的主鍵和從表的外來鍵（唯一），形成主外來鍵關係，外來鍵唯一UNIQUE
外來鍵是主鍵：主表的主鍵和從表的主鍵，形成主外來鍵關係

在這裡插入圖片描述

17. 一對多關係練習

目標

以下案例是我們JavaWeb課程最後的小專案.我們拿出其中一部分需求,根據需求來設計資料庫表之間的關係

一個旅遊線路分類中有多個旅遊線路
在這裡插入圖片描述

在這裡插入圖片描述

講解

【實現步驟】

建立旅遊線路分類表

-- 建立旅遊線路分類表:tab_category
-- cid旅遊線路分類主鍵，自動增長
-- cname旅遊線路分類名稱非空，唯一，字串100
CREATE TABLE tab_category (
   cid INT PRIMARY KEY AUTO_INCREMENT,
   cname VARCHAR(100) NOT NULL UNIQUE
);

新增旅遊線路分類資料

INSERT INTO tab_category (cname) VALUES ('周邊遊'), ('出境遊'), ('國內遊'), ('港澳遊');

建立旅遊線路表

-- 建立旅遊線路表:tab_route
-- rid旅遊線路主鍵，自動增長
-- rname旅遊線路名稱非空，唯一，字串100
-- price價格
-- rdate 上架時間，日期型別
-- cid 外來鍵，所屬分類
create table tab_route(
   rid int primary key auto_increment,
   rname varchar(100) not null unique,
   price double,
   rdate date,
   cid int,  -- 外來鍵
   foreign key (cid) references tab_category(cid)
);

新增旅遊線路資料

INSERT INTO tab_route VALUES
(NULL, '【廈門+鼓浪嶼+南普陀寺+曾厝垵 高鐵3天 惠貴團】嘗味友鴨面線 住1晚鼓浪嶼', 1499, '2018-01-27', 3),
(NULL, '【浪漫桂林 陽朔西街高鐵3天純玩 高階團】城徽象鼻山 興坪灕江 西山公園', 699, '2018-01-27', 3),
(NULL, '【爆款￥1699秒殺】泰國 曼谷 芭堤雅 金沙島 杜拉拉水上市場 雙飛六天【含送籤費 泰風情 廣州往返 特價團】', 1699, '2018-01-27', 2),
(NULL, '【經典·獅航 ￥2399秒殺】巴厘島雙飛五天 抵玩【廣州往返 特價團】', 2399, '2018-01-27', 2),
(NULL, '香港迪士尼樂園自由行2天【永東跨境巴士廣東至迪士尼去程交通+迪士尼一日門票+香港如心海景酒店暨會議中心標準房1晚住宿】', 799, '2018-01-27', 4);

小結

一對多的關係，建立表的順序：先建主表，再建從表。

18. 多對多關係練習

目標

一個使用者收藏多個線路，一個線路被多個使用者收藏
在這裡插入圖片描述

在這裡插入圖片描述

講解

對於多對多的關係我們需要增加一張中間表來維護他們之間的關係
在這裡插入圖片描述

建立使用者表

-- 建立使用者表tab_user
-- 使用者id主鍵,int型別,自動增長
-- 使用者名稱username,字串,長度30
-- 密碼PASSWORD,字串,長度30
-- 使用者姓名NAME,字串,長度100
-- 性別sex,字串,長度1
CREATE TABLE tab_user (
   uid INT PRIMARY KEY AUTO_INCREMENT, -- 使用者id
   username VARCHAR(100) NOT NULL UNIQUE, -- 使用者名稱
   PASSWORD VARCHAR(30) NOT NULL, -- 密碼
   NAME VARCHAR(100), -- 真實姓名
   sex CHAR(1) -- 性別
);

新增使用者資料

INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '男'),
(NULL, 'cz119', 654321, '小王', '男');

建立收藏表

-- 建立收藏表:tab_favorite
-- 收藏主鍵fid,int型別,主鍵,自動增長
-- 使用者id int,不為null
-- 收藏時間date,不為null
-- 旅遊線路id,int型別,不為null
CREATE TABLE tab_favorite (
   fid INT PRIMARY KEY AUTO_INCREMENT, -- 收藏主鍵
   uid INT NOT NULL, -- 使用者id
   DATE DATE NOT NULL, -- 收藏時間
   rid INT NOT NULL -- 旅遊線路id
);

增加收藏表資料

INSERT INTO tab_favorite VALUES
(NULL, 1, '2018-01-01', 1), -- 老王選擇廈門
(NULL, 1, '2018-01-01', 2), -- 老王選擇桂林
(NULL, 1, '2018-01-01', 3), -- 老王選擇泰國
(NULL, 2, '2018-01-01', 2), -- 小王選擇桂林
(NULL, 2, '2018-01-01', 3), -- 小王選擇泰國
(NULL, 2, '2018-01-01', 5); -- 小王選擇迪士尼

小結

多對多：建立一張中間表，中間表與其它兩張表是多對一的關係

總結

能夠使用SQL語句進行排序
SELECT 欄位 FROM 表名 WHERE 條件 ORDER BY 欄位 [ASC|DESC];
ASC:升序
DESC:降序
能夠使用聚合函式
count: 統計數量
sum:求和
max:獲取最大值
min:獲取最小值
avg:獲取平均值
能夠使用SQL語句進行分組查詢
SELECT 欄位 FROM 表名 WHERE 條件 GROUP BY 欄位;
分組是將相同資料作為一組,返回每組第一條資料(沒有意義), 通常分組後使用聚合函式
能夠完成資料的備份和恢復
命令列的方式
備份:不要登入:mysqldump -uroot -proot 資料庫 > 檔名
還原:要登入,選中資料庫:source 檔名

圖形介面的方式
能夠使用SQL語句新增主鍵、外來鍵、唯一、非空約束
主鍵: 欄位名欄位型別 PRIMARY KEY
外來鍵: CONSTRAINT 外來鍵名 FOREIGN KEY(欄位) REFERENCES 主表(主鍵)
唯一: 欄位名欄位型別 UNIQUE
非空約束: 欄位名欄位型別 NOT NULL
預設約束: 欄位名欄位型別 DEFAULT 預設值
能夠說出多表之間的關係及其建表原則
1對1(1:1)
1對多(1:n)
多對多(m:n)

1對多:多方這張表建立外來鍵引用一方的主鍵(先建立一方主表,後建立多方從表)

多對多:建立中間表,中間表的欄位引用某張表的主鍵(外來鍵)

MySQL 約束
2024-10-28
MySql
MySQL自增約束
2020-11-23
MySql
MySQL 欄位約束
2022-06-06
MySql
mysql資料庫約束
2024-11-20
MySql資料庫
MySQL之完整性約束
2021-05-28
MySql
mysql新增約束語句筆記
2018-07-25
MySql筆記
MySQL——約束(constraint)詳解
2018-03-22
MySqlAI
mysql資料庫匯入外來鍵約束問題
2015-07-07
MySql資料庫
xml--概述，快速入門，約束
2020-10-01
XML
【MySQL】MySQL基礎（SQL語句、約束、資料型別）
2021-09-19
MySql資料型別
MySQL入門--mysql命令
2019-05-27
MySql
MySQL入門--MySQL安全
2019-06-11
MySql
教你mysql如何增加外來鍵約束
2022-01-11
MySql
MySQL 中的約束及相關操作
2021-04-02
MySql
5_MySQL 表的欄位約束
2020-11-19
MySql
MySQL學習筆記——建立與約束
2018-03-21
MySql筆記
MySQL中的 UNIQUE約束和UNIQUE索引
2017-12-08
MySql索引
MySQL禁用恢復外來鍵約束
2010-11-08
MySql
【mysql入門】
2018-01-01
MySql
MySQL入門
2024-04-13
MySql
MySQL入門系列：MySQL概述
2019-03-06
MySql
MySQL學習筆記之約束條件
2015-09-09
MySql筆記
mysql 刪除老是報外來鍵約束
2007-10-26
MySql
《MySQL 基礎篇》八：約束和檢視
2024-09-22
MySql
SQL快速入門 ( MySQL快速入門, MySQL參考, MySQL快速回顧 )
2018-09-16
MySql
入門入門入門 MySQL命名行
2020-12-15
MySql
mysql不能新增外來鍵約束怎麼辦
2021-09-11
MySql
mysql啟動和關閉外來鍵約束
2017-01-06
MySql
MySQL·捉蟲動態·唯一鍵約束失效
2016-05-23
MySql
如何入門 MySQL
2020-05-15
MySql
MySQL 快速入門
2020-04-05
MySql
mySql入門-（二）
2019-05-19
MySql
MySQL入門——排序
2019-07-03
MySql排序
Mysql - 使用入門
2021-04-28
MySql
MYSQL 入門全套
2021-01-31
MySql
Mysql入門【JDBC】
2020-11-22
MySqlJDBC
MYSQL入門全套
2016-08-21
MySql
MySQL快速入門
2016-03-16
MySql

Mysql入門【Mysql約束】

學習目標

1. DQL查詢語句-條件查詢

目標

講解

準備資料

比較運算子

邏輯運算子

範圍

2. 模糊查詢like

目標

講解

LIKE表示模糊查詢

小結

3. DQL查詢語句-排序

目標

講解

小結

4. DQL查詢語句-聚合函式

目標

講解

小結

5.DQL查詢語句-分組

目標

講解

group by關鍵字

having關鍵字

having與where的區別

小結

6. DQL查詢語句-limit語句

目標

講解

小結

7. 資料庫備份

目標

講解

命令列方式備份與還原

小結

8. 資料庫約束的概述

目標

講解

資料庫約束的作用

約束種類

小結

9. 主鍵約束

目標

講解

主鍵的作用

為什麼需要主鍵約束

哪個欄位作為表的主鍵

建立主鍵

刪除主鍵

小結

10. 主鍵自增

目標

講解

【語法格式】

【練習】

【擴充套件】

【DELETE和TRUNCATE的區別】

小結

11. 唯一約束

目標

講解

【作用】

【基本格式】

【練習】

小結

12. 非空約束

目標

講解

【作用】這個欄位必須設定值,不能是NULL

【語法格式】

【練習】

小結

13. 預設值

目標

講解

【作用】

【語法格式】

`LIKE`表示模糊查詢