第一章 SQL的介紹
1.1、什麼是sql
- SQL：Structure Query Language。（結構化查詢語言）,通過sql運算元據庫(運算元據庫,操作表,運算元據)
- SQL被美國國家標準局（ANSI）確定為關係型資料庫語言的美國標準，後來被國際化標準組織（ISO）採納為關聯式資料庫語言的國際標準
- 各資料庫廠商(MySql,oracle,sql server)都支援ISO的SQL標準。
- 各資料庫廠商在標準的基礎上做了自己的擴充套件。 各個資料庫自己特定的語法
1.2、sql的分類
- Data Definition Language (DDL資料定義語言) 如：運算元據庫，操作表
- Data Manipulation Language(DML資料操縱語言)，如：對錶中的記錄操作增刪改
- Data Query Language(DQL 資料查詢語言)，如：對錶中資料的查詢操作
- Data Control Language(DCL 資料控制語言)，如：對使用者許可權的設定
1.3、MySQL的語法規範和要求
（1）mysql的sql語法不區分大小寫
MySQL的關鍵字和函式名等不區分大小寫，但是對於資料值是否區分大小寫，和字符集與校對規則有關。
ci（大小寫不敏感），cs（大小寫敏感），_bin（二元，即比較是基於字元編碼的值而與language無關，區分大小寫）
（2）命名時：儘量使用26個英文字母大小寫，數字0-9，下劃線，不要使用其他符號user_id
（3）建議不要使用mysql的關鍵字等來作為表名、欄位名等，如果不小心使用，請在SQL語句中使用`（飄號）引起來
（4）資料庫和表名、欄位名等物件名中間不要包含空格
（5）同一個mysql軟體中，資料庫不能同名，同一個庫中，表不能重名，同一個表中，欄位不能重名
（6）標點符號：
- 必須成對
- 必須英文狀態下半形輸入方式
- 字串和日期型別可以使用單引號’’
- 列的別名可以使用雙引號""，給表名取別名不要使用雙引號。取別名時as可以省略
- 如果列的別名沒有包含空格，可以省略雙引號，如果有空格雙引號不能省略。
（7）SQL指令碼中如何加註釋
- 單行註釋：#註釋內容
- 單行註釋：–空格註釋內容 其中–後面的空格必須有
- 多行註釋：/ 註釋內容 /
#以下兩句是一樣的，不區分大小寫
show databases;
SHOW DATABASES;
#建立表格
#create table student info(...); #表名錯誤，因為表名有空格
create table student_info(...);
#其中name使用``飄號，因為name和系統關鍵字或系統函式名等預定義識別符號重名了。
CREATE TABLE t_stu(
id INT,
`name` VARCHAR(20)
);
select id as "編號", `name` as "姓名" from t_stu; #起別名時，as都可以省略
select id as 編號, `name` as 姓名 from t_stu; #如果欄位別名中沒有空格，那麼可以省略""
select id as 編 號, `name` as 姓 名 from t_stu; #錯誤，如果欄位別名中有空格，那麼不能省略""第二章-DDL運算元據庫
2.1、建立資料庫(掌握)
- 語法
create database 資料庫名 [character set 字符集][collate 校對規則] 注: []意思是可選的意思字符集(charset)：是一套符號和編碼。
- 練習
建立一個day01的資料庫（預設字符集)
create database day01;建立一個day01_2的資料庫,指定字符集為gbk(瞭解)
create database day01_2 character set gbk;2.2、檢視所有的資料庫
- 檢視所有的資料庫
語法
show databases; - 檢視資料庫的定義結構【瞭解】
語法
show create database 資料庫名;檢視day01這個資料庫的定義
show create database day01; 2.3、刪除資料庫
語法
drop database 資料庫名;刪除day01_2資料庫
drop database day01_2;2.4、修改資料庫【瞭解】
語法
alter database 資料庫名 character set 字符集;修改day01這個資料庫的字符集(gbk)
alter database day01 character set gbk;注意：
- 是utf8，不是utf-8
- 不是修改資料庫名
2.5、其他操作
切換資料庫, 選定哪一個資料庫
use 資料庫名; //注意: 在建立表之前一定要指定資料庫. use 資料庫名練習: 使用day01
use day01;檢視正在使用的資料庫
select database();第三章-DDL操作表
3.1、建立表
語法
create table 表名( 列名 型別 [約束], 列名 型別 [約束] ... );- 型別
數值型別
整型系列：xxxInt
- int(M)，必須和unsigned zerofill一起使用才有意義
浮點型系列：float,double（或real)
double(M,D)：表示最長為M位，其中小數點後D位
例如：double(5,2)表示的資料範圍[-999.99,999.99]，如果超過這個範圍會報錯。定點型系列：decimal（底層實際上是使用字串進行儲存）
- decimal(M,D)：表示最長為M位，其中小數點後D位
位型別：bit
- 位元組範圍是：1-8，值範圍是：bit(1)~bit(64)，預設bit(1)
用來儲存二進位制數。對於位欄位，直接使用select命令將不會看到結果。可以使用bit()或hex()函式進行讀取。插入bit型別欄位時，使用bit()函式轉為二進位制值再插入，因為二進位制碼是“01”。
日期時間型別
日期時間型別：year, date, datetime, timestamp
注意一下每一種日期時間的表示範圍
timestamp和datetime的區別：
- timestamp範圍比較小
timestamp和時區有關
- show variables like ‘time_zone’;
- set time_zone = ‘+8:00’;
- timestamp受MySQL版本和伺服器的SQLMode影響很大
- 表中的第一個非空的timestamp欄位如果插入和更新為NULL則會自動設定為系統時間
字串型別
MySQL中提供了多種對字元資料的儲存型別，不同的版本可能有所差異。常見的有：
char,varchar，xxtext，binary，varbinary，xxblob，enum，set等等
字串型別char,varchar(M)
- char如果沒有指定寬度，預設為1個字元
- varchar(M)，必須指定寬度
binary和varbinary類似於char和varchar，不同的是它們包含二進位制字串，不支援模糊查詢之類的。
一般在儲存少量字串的時候，我們會選擇char和varchar；而在儲存較大文字時，通常會選擇使用text或blob系列。blob和text值會引起一些效能問題，特別是在執行了大量的刪除操作時，會在資料表中留下很大的“空洞”，為了提高效能，建議定期時候用optimize table功能對這類表進行碎片整理。可以使用合成的(Synthetic)索引來提高大文字欄位的查詢效能，如果需要對大文字欄位進行模糊查詢，MySql提供了字首索引。但是仍然要在不必要的時候避免檢索大型的blob或text值。
enum列舉型別，它的值範圍需要在建立表時通過列舉方式顯式指定，對於1~255個成員的列舉需要1個位元組儲存；對於【 255`65535】個成員需要2個位元組儲存。例如：gender enum(‘男’,‘女’)。如果插入列舉值以外的值，會按第一個值處理。一次只能從列舉值中選擇一個。
set集合型別，可以包含0~64個成員。一次可以從集合中選擇多個成員。如果選擇了1-8個成員的集合，佔1個位元組，依次佔2個，3個。。8個位元組。例如：hoppy set(‘吃飯’,‘睡覺’,‘玩遊戲’,‘旅遊’)，選擇時’吃飯,睡覺’或’睡覺,玩遊戲,旅遊’
示例
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| eid | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(20) | NO | | NULL | |
| tel | char(11) | NO | | NULL | |
| gender | char(1) | YES | | 男 | |
| salary | double | YES | | NULL | |
| commission_pct | double(3,2) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| job_id | int(11) | YES | | NULL | |
| email | varchar(32) | YES | | NULL | |
| mid | int(11) | YES | | NULL | |
| address | varchar(150) | YES | | NULL | |
| native_place | varchar(10) | YES | | NULL | |
| did | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+約束
- 即規則,規矩 限制;
- 作用：保證使用者插入的資料儲存到資料庫中是符合規範的
約束種類:
- not null: 非空 ; eg: username varchar(40) not null username這個列不能有null值
- unique:唯一約束, 後面的資料不能和前面重複; eg: cardNo char(18) unique; cardNo 列裡面不可以有重複資料
primary key；主鍵約束(非空+唯一); 一般用在表的id列上面. 一張表基本上都有id列的, id列作為唯一標識的
- auto_increment: 自動增長,必須是設定了primary key之後,才可以使用auto_increment
- id int primary key auto_increment; id不需要我們自己維護了, 插入資料的時候直接插入null, 自動的增長進行填充進去, 避免重複了.
注意:
- 先設定了primary key 再能設定auto_increment
- 只有當設定了auto_increment 才可以插入null , 否則插入null會報錯
id列:
- 給id設定為int型別, 新增主鍵約束, 自動增長
- 或者給id設定為字串型別,新增主鍵約束, 不能設定自動增長
練習
建立一張學生表(含有id欄位,姓名欄位不能重複,性別欄位不能為空預設值為男. id為主鍵自動增長)
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主鍵自增長
NAME VARCHAR(30) UNIQUE, -- 唯一約束
gender CHAR(1) NOT NULL DEFAULT '男'
);3.2、檢視錶【瞭解】
檢視所有的表
show tables;檢視錶的定義結構
語法
desc 表名；練習: 檢視student表的定義結構
desc student;3.3、修改表【掌握，但是不要記憶】
語法
- 增加一列
alter table 【資料庫名.]表名稱 add 【column】 欄位名 資料型別;
alter table 【資料庫名.]表名稱 add 【column】 欄位名 資料型別 first;
alter table 【資料庫名.]表名稱 add 【column】 欄位名 資料型別 after 另一個欄位;- 修改列的型別約束:alter table 表名 modify 欄位 型別 約束 ；
- 修改列的名稱，型別，約束: alter table 表名 change 舊列 新列 型別 約束;
- 刪除一列: alter table 表名 drop 列名;
- 修改表名 : rename table 舊錶名 to 新表名;
練習
給學生表增加一個grade欄位,型別為varchar(20),不能為空
ALTER TABLE student ADD grade VARCHAR(20) NOT NULL;給學生表的gender欄位改成int型別，不能為空，預設值為1
alter table student modify gender varchar(20);給學生表的grade欄位修改成class欄位
ALTER TABLE student CHANGE grade class VARCHAR(20) NOT NULL;把class欄位刪除
ALTER TABLE student DROP class;把學生表修改成老師表(瞭解)
RENAME TABLE student TO teacher;3.4、刪除表【掌握】
語法
drop table 表名；把teacher表刪除
drop table teacher;第四章-DML操作表記錄-增刪改【重點】
準備工作: 建立一張商品表(商品id,商品名稱,商品價格,商品數量.)
create table product(
pid int primary key auto_increment,
pname varchar(40),
price double,
num int
);4.1、插入記錄
語法
- 方式一: 插入指定列, 如果沒有把這個列進行列出來, 以null進行自動賦值了.
eg: 只想插入pname, price , insert into t_product(pname, price) values(‘mac’,18000);
insert into 表名(列,列..) values(值,值..);注意: 如果沒有插入了列設定了非空約束, 會報錯的
- 方式二: 插入所有的列,如果哪列不想插入值,則需要賦值為null
insert into 表名 values(值,值....);
eg:
insert into product values(null,'蘋果電腦',18000.0,10);
insert into product values(null,'華為5G手機',30000,20);
insert into product values(null,'小米手機',1800,30);
insert into product values(null,'iPhonex',8000,10);
insert into product values(null,'iPhone7',6000,200);
insert into product values(null,'iPhone6s',4000,1000);
insert into product values(null,'iPhone6',3500,100);
insert into product values(null,'iPhone5s',3000,100);
insert into product values(null,'方便麵',4.5,1000);
insert into product values(null,'咖啡',11,200);
insert into product values(null,'礦泉水',3,500);4.2、更新記錄
- 語法
update 表名 set 列 =值, 列 =值 [where 條件]- 練習
將所有商品的價格修改為5000元
update product set price = 5000;將商品名是蘋果電腦的價格修改為18000元
UPDATE product set price = 18000 WHERE pname = '蘋果電腦';將商品名是蘋果電腦的價格修改為17000,數量修改為5
UPDATE product set price = 17000,num = 5 WHERE pname = '蘋果電腦';將商品名是方便麵的商品的價格在原有基礎上增加2元
UPDATE product set price = price+2 WHERE pname = '方便麵';4.3、刪除記錄
- delete
根據條件，一條一條資料進行刪除
語法
delete from 表名 [where 條件] 注意: 刪除資料用delete,不用truncate型別
刪除表中名稱為’蘋果電腦’的記錄
delete from product where pname = '蘋果電腦';刪除價格小於5001的商品記錄
delete from product where price < 5001;刪除表中的所有記錄(要刪除一般不建議使用delete語句，delete語句是一行一行執行，速度過慢)
delete from product;truncate
把表直接DROP掉，然後再建立一個同樣的新表。刪除的資料不能找回。執行速度比DELETE快truncate table 表;工作中刪除資料
- 物理刪除: 真正的刪除了, 資料不在, 使用delete就屬於物理刪除
- 邏輯刪除: 沒有真正的刪除, 資料還在. 搞一個標記, 其實邏輯刪除是更新 eg: state 1 啟用 0禁用
第五章-DQL操作表記錄-查詢【重點】
5.1、基本查詢語法
select 要查詢的欄位名 from 表名 [where 條件] 5.2、簡單查詢
查詢所有行和所有列的記錄
語法
select * form 表查詢商品表裡面的所有的列
select * from product;
查詢某張表特定列的記錄
語法
select 列名,列名,列名... from 表查詢商品名字和價格
select pname, price from product;去重查詢 distinct
語法
SELECT DISTINCT 欄位名 FROM 表名; //要資料一模一樣才能去重去重查詢商品的名字
SELECT DISTINCT pname,price FROM product注意點: 去重針對某列, distinct前面不能先出現列名
別名查詢
語法
select 列名 as 別名 ,列名 from 表 //列別名 as可以不寫 select 別名.* from 表 as 別名 //表別名(多表查詢, 明天會具體講)查詢商品資訊，使用別名
SELECT pid ,pname AS '商品名',price AS '商品價格',num AS '商品庫存' FROM product運算查詢(+,-,*,/,%等)
把商品名，和商品價格+10查詢出來:我們既可以將某個欄位加上一個固定值，又可以對多個欄位進行運算查詢
select pname ,price+10 as 'price' from product;
select name,chinese+math+english as total from student注意
- 運算查詢欄位,欄位之間是可以的
- 字串等型別可以做運算查詢，但結果沒有意義
5.3、條件查詢(很重要)
語法
select ... from 表 where 條件 //取出表中的每條資料，滿足條件的記錄就返回，不滿足條件的記錄不返回運算子
1、比較運算子
大於：>
小於：<
大於等於：>=
小於等於：<=
等於：= 不能用於null判斷
不等於：!= 或 <>
安全等於: <=> 可以用於null值判斷2、邏輯運算子（建議用單詞，可讀性來說）
邏輯與：&& 或 and
邏輯或：|| 或 or
邏輯非：! 或 not
邏輯異或：^ 或 xor3、範圍
區間範圍：between x and y
not between x and y
集合範圍：in (x,x,x)
not in (x,x,x)4、模糊查詢和正則匹配（只針對字串型別，日期型別）
like 'xxx' 模糊查詢是處理字串的時候進行部分匹配
如果想要表示0~n個字元，用%
如果想要表示確定的1個字元，用_
regexp '正則'5、特殊的null值處理
#（1）判斷時
xx is null
xx is not null
xx <=> null
#（2）計算時
ifnull(xx,代替值) 當xx是null時，用代替值計算- 練習
查詢商品價格>3000的商品
select * from product where price > 3000;查詢pid=1的商品
select * from product where pid = 1;查詢pid<>1的商品（！=）
select * from product where pid <> 1;查詢價格在3000到6000之間的商品
select * from product where price between 3000 and 6000;查詢pid在1，5，7，15範圍內的商品
select * from product where id = 1;
select * from product where id = 5;
select * from product where id = 7;
select * from product where id = 15;
select * from product where id in (1,5,7,15);查詢商品名以iPho開頭的商品(iPhone系列)
select * from product where pname like 'iPho%';查詢商品價格大於3000並且數量大於20的商品 (條件 and 條件 and…)
select * from product where price > 3000 and num > 20;查詢id=1或者價格小於3000的商品
select * from product where pid = 1 or price < 3000;5.4、排序查詢
排序是寫在查詢的後面，代表把資料查詢出來之後再排序
環境的準備
# 建立學生表(有sid,學生姓名,學生性別,學生年齡,分數列,其中sid為主鍵自動增長) CREATE TABLE student( sid INT PRIMARY KEY auto_increment, sname VARCHAR(40), sex VARCHAR(10), age INT, score DOUBLE ); INSERT INTO student VALUES(null,'zs','男',18,98.5); INSERT INTO student VALUES(null,'ls','女',18,96.5); INSERT INTO student VALUES(null,'ww','男',15,50.5); INSERT INTO student VALUES(null,'zl','女',20,98.5); INSERT INTO student VALUES(null,'tq','男',18,60.5); INSERT INTO student VALUES(null,'wb','男',38,98.5); INSERT INTO student VALUES(null,'小麗','男',18,100); INSERT INTO student VALUES(null,'小紅','女',28,28); INSERT INTO student VALUES(null,'小強','男',21,95);- 單列排序
語法: 只按某一個欄位進行排序，單列排序
SELECT 欄位名 FROM 表名 [WHERE 條件] ORDER BY 欄位名 [ASC|DESC]; //ASC: 升序，預設值; DESC: 降序案例: 以分數降序查詢所有的學生
SELECT * FROM student ORDER BY score DESC- 組合排序
語法: 同時對多個欄位進行排序，如果第1個欄位相等，則按第2個欄位排序，依次類推
SELECT 欄位名 FROM 表名 WHERE 欄位=值 ORDER BY 欄位名1 [ASC|DESC], 欄位名2 [ASC|DESC];練習: 以分數降序查詢所有的學生, 如果分數一致,再以age降序
SELECT * FROM student ORDER BY score DESC, age DESC5.5、聚合函式
聚合函式用於統計，通常會和分組查詢一起使用，用於統計每組的資料
- 聚合函式列表
語法
SELECT 聚合函式(列名) FROM 表名 [where 條件];案例
-- 求出學生表裡面的最高分數
SELECT MAX(score) FROM student
-- 求出學生表裡面的最低分數
SELECT MIN(score) FROM student
-- 求出學生表裡面的分數的總和(忽略null值)
SELECT SUM(score) FROM student
-- 求出學生表裡面的平均分
SELECT AVG(score) FROM student
-- 求出學生表裡面的平均分（缺考了當成0分處理）
SELECT AVG(IFNULL(score,0)) FROM student
-- 統計學生的總人數 (忽略null)
SELECT COUNT(sid) FROM student
SELECT COUNT(*) FROM student注意: 聚合函式會忽略空值NULL
我們發現對於NULL的記錄不會統計，建議如果統計個數則不要使用有可能為null的列，但如果需要把NULL也統計進去呢？我們可以通過 IFNULL(列名，預設值) 函式來解決這個問題. 如果列不為空，返回這列的值。如果為NULL，則返回預設值。
-- 求出學生表裡面的平均分（缺考了當成0分處理）
SELECT AVG(IFNULL(score,0)) FROM student;5.6、分組查詢
GROUP BY將分組欄位結果中相同內容作為一組，並且返回每組的第一條資料，所以單獨分組沒什麼用處。分組的目的就是為了統計，一般分組會跟聚合函式一起使用
- 分組
語法
SELECT 欄位1,欄位2... FROM 表名 [where 條件] GROUP BY 列 [HAVING 條件];案例
-- 根據性別分組, 統計每一組學生的總人數
SELECT sex '性別',COUNT(sid) '總人數' FROM student GROUP BY sex
-- 根據性別分組，統計每組學生的平均分
SELECT sex '性別',AVG(score) '平均分' FROM student GROUP BY sex
-- 根據性別分組，統計每組學生的總分
SELECT sex '性別',SUM(score) '總分' FROM student GROUP BY sex- 分組後篩選 having
分組後的條件，不能寫在where之後，where關鍵字要寫在group by之前
根據性別分組, 統計每一組學生的總人數> 5的(分組後篩選)
SELECT sex, count(*) FROM student GROUP BY sex HAVING count(sid) > 5根據性別分組，只統計年齡大於等於18的，並且要求組裡的人數大於4
SELECT sex '性別',COUNT(sid) '總人數' FROM student WHERE age >= 18 GROUP BY sex HAVING COUNT(sid) > 4- where和having的區別【面試】
where 子句作用
- 1)對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾資料，即先過濾再分組。
- 2)where後面不可以使用聚合函式
having字句作用
- 1) having 子句的作用是篩選滿足條件的組，即在分組之後過濾資料，即先分組再過濾。
- 2) having後面可以使用聚合函式
5.7、分頁查詢
語法
select ... from .... limit a ,b
案例
-- 分頁查詢 -- limit 關鍵字是使用在查詢的後邊，如果有排序的話則使用在排序的後邊 -- limit的語法: limit offset,length 其中offset表示跳過多少條資料，length表示查詢多少條資料 SELECT * FROM product LIMIT 0,3 -- 查詢product表中的前三條資料(0表示跳過0條，3表示查詢3條) SELECT * FROM product LIMIT 3,3 -- 查詢product表的第四到六條資料(3表示跳過3條，3表示查詢3條) -- 分頁的時候，只會告訴你我需要第幾頁的資料，並且每頁有多少條資料 -- 假如，每頁需要3條資料，我想要第一頁資料: limit 0,3 -- 假如,每頁需要3條資料，我想要第二頁資料: limit 3,3 -- 假如，每頁需要3條資料，我想要第三頁資料: limit 6,3 -- 結論: length = 每頁的資料條數，offset = (當前頁數 - 1)*每頁資料條數 -- limit (當前頁數 - 1)*每頁資料條數, 每頁資料條數5.8、查詢的語法小結
select...from...where...group by...order by...limit select...from...where... select...from...where...order by... select...from...where...limit... select...from...where...order by...imit第六章 資料庫三正規化
好的資料庫設計對資料的儲存效能和後期的程式開發，都會產生重要的影響。建立科學的，規範的資料庫就需要滿足一些規則來優化資料的設計和儲存，這些規則就稱為正規化。
6.1、第一正規化: 確保每列保持原子性
第一正規化是最基本的正規化。如果資料庫表中的所有欄位值都是不可分解的原子值，就說明該資料庫表滿足了第一正規化。
第一正規化的合理遵循需要根據系統的實際需求來定。比如某些資料庫系統中需要用到“地址”這個屬性，本來直接將“地址”屬性設計成一個資料庫表的欄位就行。但是如果系統經常會訪問“地址”屬性中的“城市”部分，那麼就非要將“地址”這個屬性重新拆分為省份、城市、詳細地址等多個部分進行儲存，這樣在對地址中某一部分操作的時候將非常方便。這樣設計才算滿足了資料庫的第一正規化，如下表所示。
如果不遵守第一正規化，查詢出資料還需要進一步處理（查詢不方便）。遵守第一正規化，需要什麼欄位的資料就查詢什麼資料（方便查詢）
6.2、第二正規化: 確保表中的每列都和主鍵相關
第二正規化在第一正規化的基礎之上更進一層。第二正規化需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個資料庫表中，一個表中只能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。
比如要設計一個訂單資訊表，因為訂單中可能會有多種商品，所以要將訂單編號和商品編號作為資料庫表的聯合主鍵，如下表所示
這樣就產生一個問題：這個表中是以訂單編號和商品編號作為聯合主鍵。這樣在該表中商品名稱、單位、商品價格等資訊不與該表的主鍵相關，而僅僅是與商品編號相關。所以在這裡違反了第二正規化的設計原則。
而如果把這個訂單資訊表進行拆分，把商品資訊分離到另一個表中，把訂單專案表也分離到另一個表中，就非常完美了。如下所示
<img src="imgs/tu_13.png" style="zoom: 67%;" />這樣設計，在很大程度上減小了資料庫的冗餘。如果要獲取訂單的商品資訊，使用商品編號到商品資訊表中查詢即可
6.3、第三正規化: 確保每列都和主鍵列直接相關,而不是間接相關
第三正規化需要確保資料表中的每一列資料都和主鍵直接相關，而不能間接相關。
比如在設計一個訂單資料表的時候，可以將客戶編號作為一個外來鍵和訂單表建立相應的關係。而不可以在訂單表中新增關於客戶其它資訊（比如姓名、所屬公司等）的欄位。如下面這兩個表所示的設計就是一個滿足第三正規化的資料庫表。
<img src="imgs/tu_14.png" style="zoom:67%;" />這樣在查詢訂單資訊的時候，就可以使用客戶編號來引用客戶資訊表中的記錄，也不必在訂單資訊表中多次輸入客戶資訊的內容，減小了資料冗餘
第七章 外來鍵約束
7.1、外來鍵約束的概念
在遵循三正規化的前提下，很多時候我們必須要進行拆表，將資料分別存放在多張表中，以減少冗餘資料。但是拆分出來的表與表之間是有著關聯關係的，我們必須得通過一種約束來約定表與表之間的關係，這種約束就是外來鍵約束
7.2、外來鍵約束的作用
外來鍵約束是保證一個或兩個表之間的參照完整性,外來鍵是構建於一個表的兩個欄位或是兩個表的兩個欄位之間的參照關係。
7.3、建立外來鍵約束的語法
在建表時指定外來鍵約束
create table [資料名.]從表名( 欄位名1 資料型別 primary key , 欄位名2 資料型別 , ...., [constraint 外來鍵約束名] foreign key (從表欄位) references 主表名(主表欄位) [on update 外來鍵約束等級][on delete 外來鍵約束等級] #外來鍵只能在所有欄位列表後面單獨指定 #如果要自己命名外來鍵約束名，建議 主表名_從表名_關聯欄位名_fk );在建表後指定外來鍵約束
alter table 從表名稱 add [constraint 外來鍵約束名] foreign key (從表欄位名) references 主表名(主表被參照欄位名) [on update xx][on delete xx];7.4、刪除外來鍵約束的語法
ALTER TABLE 表名稱 DROP FOREIGN KEY 外來鍵約束名; #檢視約束名 SELECT * FROM information_schema.table_constraints WHERE table_name = '表名稱'; #刪除外來鍵約束不會刪除對應的索引，如果需要刪除索引，需要用ALTER TABLE 表名稱 DROP INDEX 索引名; #檢視索引名 show index from 表名稱;7.5、外來鍵約束的要求
- 在從表上建立外來鍵，而且主表要先存在。
- 一個表可以建立多個外來鍵約束
- 通常情況下，從表的外來鍵列一定要指向主表的主鍵列
- 從表的外來鍵列與主表被參照的列名字可以不相同，但是資料型別必須一樣
7.6、外來鍵約束等級
- Cascade方式：在主表上update/delete記錄時，同步update/delete掉從表的匹配記錄
- Set null方式：在主表上update/delete記錄時，將從表上匹配記錄的列設為null，但是要注意子表的外來鍵列不能為not null
- No action方式：如果子表中有匹配的記錄,則不允許對父表對應候選鍵進行update/delete操作
- Restrict方式：同no action, 都是立即檢查外來鍵約束
- Set default方式（在視覺化工具SQLyog中可能顯示空白）：父表有變更時,子表將外來鍵列設定成一個預設的值，但Innodb不能識別
- 如果沒有指定等級，就相當於Restrict方式
7.7、外來鍵約束練習
-- 部門表
create table dept(
id int primary key,
dept_name varchar(50),
dept_location varchar(50)
);
-- 員工表
CREATE TABLE emp(
eid int primary key,
name varchar(50) not null,
sex varchar(10),
dept_id int
);
-- 給員工表表的dept_id新增外來鍵指向部門表的主鍵
alter table emp add foreign key(dept_id) references dept(id)第八章 多表間關係
8.1、一對多關係
- 概念
一對多的關係是指: 主表的一行資料可以同時對應從表的多行資料，反過來就是從表的多行資料指向主表的同一行資料。
- 應用場景
分類表和商品表、班級表和學生表、使用者表和訂單表等等
- 建表原則
將一的一方作為主表，多的一方作為從表，在從表中指定一個欄位作為外來鍵，指向主表的主鍵
建表語句練習
-- 建立分類表 CREATE TABLE category( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(50) ); -- 建立商品表 CREATE TABLE product( pid INT PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(50), price DOUBLE, cid INT ) -- 給商品表新增一個外來鍵 alter table product add foreign key(cid) references category(cid)8.2、多對多關係
- 概念
兩張表都是多的一方，A表的一行資料可以同時對應B表的多行資料，反之B表的一行資料也可以同時對應A表的多行資料
- 應用場景
訂單表和商品表、學生表和課程表等等
- 建表原則
因為兩張表都是多的一方，所以在兩張表中都無法建立外來鍵，所以需要新建立一張中間表，在中間表中定義兩個欄位，這倆欄位分別作為外來鍵指向兩張表各自的主鍵
建表語句練習
-- 建立學生表 CREATE TABLE student( sid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(50) ); -- 建立課程表 CREATE TABLE course( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(20) ); -- 建立中間表 CREATE TABLE s_c_table( sno INT, cno INT ); -- 給sno欄位新增外來鍵指向student表的sid主鍵 ALTER TABLE s_c_table ADD CONSTRAINT fkey01 FOREIGN KEY(sno) REFERENCES student(sid); -- 給cno欄位新增外來鍵指向course表的cid主鍵 ALTER TABLE s_c_table ADD CONSTRAINT fkey03 FOREIGN KEY(cno) REFERENCES course(cid);8.3、一對一關係(瞭解)
- 第一種一對一關係
我們之前學習過一對多關係，在一對多關係中主表的一行資料可以對應從表的多行資料，反之從表的一行資料則只能對應主表的一行資料。這種一行資料對應一行資料的關係，我們可以將其看作一對一關係
- 第二種一對一關係
A表中的一行資料對應B表中的一行資料，反之B表中的一行資料也對應A表中的一行資料，此時我們可以將A表當做主表B表當做從表，或者是將B表當做主表A表當做從表
- 建表原則
在從表中指定一個欄位建立外來鍵並指向主表的主鍵，然後給從表的外來鍵欄位新增唯一約束
第九章 多表關聯查詢
多表關聯查詢是使用一條SQL語句，將關聯的多張表的資料查詢出來
9.1、環境準備
-- 建立一張分類表(類別id,類別名稱.備註:類別id為主鍵並且自動增長)
CREATE TABLE t_category(
cid INT PRIMARY KEY auto_increment,
cname VARCHAR(40)
);
INSERT INTO t_category values(null,'手機數碼');
INSERT INTO t_category values(null,'食物');
INSERT INTO t_category values(null,'鞋靴箱包');
-- 建立一張商品表(商品id,商品名稱,商品價格,商品數量,類別.備註:商品id為主鍵並且自動增長)
CREATE TABLE t_product(
pid INT PRIMARY KEY auto_increment,
pname VARCHAR(40),
price DOUBLE,
num INT,
cno INT
);
insert into t_product values(null,'蘋果電腦',18000,10,1);
insert into t_product values(null,'iPhone8s',5500,100,1);
insert into t_product values(null,'iPhone7',5000,100,1);
insert into t_product values(null,'iPhone6s',4500,1000,1);
insert into t_product values(null,'iPhone6',3800,200,1);
insert into t_product values(null,'iPhone5s',2000,10,1);
insert into t_product values(null,'iPhone4s',18000,1,1);
insert into t_product values(null,'方便麵',4.5,1000,2);
insert into t_product values(null,'咖啡',10,100,2);
insert into t_product values(null,'礦泉水',2.5,100,2);
insert into t_product values(null,'法拉利',3000000,50,null);
-- 給 商品表新增外來鍵
ALTER TABLE t_product ADD FOREIGN KEY(cno) REFERENCES t_category(cid);9.2、交叉查詢【瞭解】
交叉查詢其實就是將多張表的資料沒有條件地連線在一起進行展示
語法
select a.列,a.列,b.列,b.列 from a,b ; select a.*,b.* from a,b ; --或者 select * from a,b;- 練習
使用交叉查詢類別和商品
select * from t_category,t_product;通過查詢結果我們可以看到，交叉查詢其實是一種錯誤的做法，在查詢到的結果集中有大量的錯誤資料，我們稱交叉查詢到的結果集是笛卡爾積
- 笛卡爾積
假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以擴充套件到多個集合的情況。
9.3、內連線查詢
交叉查詢產生這樣的結果並不是我們想要的，那麼怎麼去除錯誤的、不想要的記錄呢，當然是通過條件過濾。通常要查詢的多個表之間都存在關聯關係，那麼就通過關聯關係(主外來鍵關係)去除笛卡爾積。這種通過條件過濾去除笛卡爾積的查詢，我們稱之為連線查詢。連線查詢又可以分為內連線查詢和外連線查詢，我們先學習內連線查詢
- 隱式內連線查詢
隱式內連線查詢裡面是沒有inner join關鍵字
select [欄位,欄位,欄位] from a,b where 連線條件 (b表裡面的外來鍵 = a表裡面的主鍵 ) - 顯式內連線查詢
顯式內連線查詢裡面是有inner join關鍵字
select [欄位,欄位,欄位] from a [inner] join b on 連線條件 [ where 其它條件]- 內連線查詢練習
查詢所有類別下的商品資訊,如果該類別下沒有商品則不展示
-- 1 隱式內連線方式
select *from t_category c, t_product p WHERE c.cid = p.cno;
-- 2 顯示內連線方式
-- 查詢手機數碼這個分類下的所有商品的資訊以及分類資訊
SELECT * FROM t_product tp INNER JOIN t_category tc ON tp.cno = tc.cid WHERE tc.cname = '手機數碼';
SELECT * from t_category c INNER JOIN t_product p ON c.cid = p.cno- 內連線查詢的特點
主表和從表中的資料都是滿足連線條件則能夠查詢出來，不滿足連線條件則不會查詢出來
9.4、外連線查詢
我們發現內連線查詢出來的是滿足連線條件的公共部分， 如果要保證查詢出某張表的全部資料情況下進行連線查詢. 那麼就要使用外連線查詢了. 外連線分為左外連線和右外連線
- 左外連線查詢
概念
以join左邊的表為主表,展示主表的所有資料,根據條件查詢連線右邊表的資料,若滿足條件則展示,若不滿足則以null顯示。可以理解為：在內連線的基礎上保證左邊表的資料全部顯示
語法
select 欄位 from a left [outer] join b on 條件練習
查詢所有類別下的商品資訊，就算該類別下沒有商品也需要將該類別的資訊展示出來
SELECT * FROM t_category c LEFT OUTER JOIN t_product p ON c.cid = p.cno- 右外連線查詢
概念
以join右邊的表為主表,展示右邊表的所有資料,根據條件查詢join左邊表的資料,若滿足則展示,若不滿足則以null顯示。可以理解為：在內連線的基礎上保證右邊表的資料全部顯示
語法
select 欄位 from a right [outer] join b on 條件練習
查詢所有商品所對應的類別資訊
SELECT * FROM t_category c RIGHT OUTER JOIN t_product p ON c.cid = p.cno9.5、union聯合查詢實現全外連線查詢
首先要明確，聯合查詢不是多表連線查詢的一種方式。聯合查詢是將多條查詢語句的查詢結果合併成一個結果並去掉重複資料。
全外連線查詢的意思就是將左表和右表的資料都查詢出來，然後按照連線條件連線
union的語法
查詢語句1 union 查詢語句2 union 查詢語句3 ...練習
# 用左外的A union 右外的B SELECT * FROM t_category c LEFT OUTER JOIN t_product p ON c.cid = p.cno union SELECT * FROM t_category c RIGHT OUTER JOIN t_product p ON c.cid = p.cno9.6、自連線查詢
自連線查詢是一種特殊的多表連線查詢，因為兩個關聯查詢的表是同一張表，通過取別名的方式來虛擬成兩張表，然後進行兩張表的連線查詢
準備工作
-- 員工表 CREATE TABLE emp ( id INT PRIMARY KEY, -- 員工id ename VARCHAR(50), -- 員工姓名 mgr INT , -- 上級領導 joindate DATE, -- 入職日期 salary DECIMAL(7,2) -- 工資 ); -- 新增員工 INSERT INTO emp(id,ename,mgr,joindate,salary) VALUES (1001,'孫悟空',1004,'2000-12-17','8000.00'), (1002,'盧俊義',1006,'2001-02-20','16000.00'), (1003,'林沖',1006,'2001-02-22','12500.00'), (1004,'唐僧',1009,'2001-04-02','29750.00'), (1005,'李逵',1006,'2001-09-28','12500.00'), (1006,'宋江',1009,'2001-05-01','28500.00'), (1007,'劉備',1009,'2001-09-01','24500.00'), (1008,'豬八戒',1004,'2007-04-19','30000.00'), (1009,'羅貫中',NULL,'2001-11-17','50000.00'), (1010,'吳用',1006,'2001-09-08','15000.00'), (1011,'沙僧',1004,'2007-05-23','11000.00'), (1012,'李逵',1006,'2001-12-03','9500.00'), (1013,'小白龍',1004,'2001-12-03','30000.00'), (1014,'關羽',1007,'2002-01-23','13000.00'); #查詢孫悟空的上級 SELECT employee.*,manager.ename mgrname FROM emp employee,emp manager where employee.mgr=manager.id AND employee.ename='孫悟空'- 自連線查詢練習
查詢員工的編號，姓名，薪資和他領導的編號，姓名，薪資
#這些資料全部在員工表中
#把t_employee表，即當做員工表，又當做領導表
#領導表是虛擬的概念，我們可以通過取別名的方式虛擬
SELECT employee.id "員工的編號",emp.ename "員工的姓名" ,emp.salary "員工的薪資",
manager.id "領導的編號" ,manager.ename "領導的姓名",manager.salary "領導的薪資"
FROM emp employee INNER JOIN emp manager
#emp employee：employee.，表示的是員工表的
#emp manager：如果用manager.，表示的是領導表的
ON employee.mgr = manager.id # 員工的mgr指向上級的id
#表的別名不要加""，給列取別名，可以用""，列的別名不使用""也可以，但是要避免包含空格等特殊符號。第十章 子查詢
如果一個查詢語句巢狀在另一個查詢語句裡面，那麼這個查詢語句就稱之為子查詢，根據位置不同，分為：where型，from型，exists型。注意：不管子查詢在哪裡，子查詢必須使用()括起來。
10.1、where型
①子查詢是單值結果(單行單列)，那麼可以對其使用（=，>等比較運算子）
# 查詢價格最高的商品資訊
select * from t_product where price = (select max(price) from t_product)②子查詢是多值結果，那麼可對其使用（【not】in(子查詢結果)，或 >all(子查詢結果)，或>=all(子查詢結果)，<all(子查詢結果)，<=all（子查詢結果)，或 >any(子查詢結果)，或>=any(子查詢結果)，<any(子查詢結果)，<=any（子查詢結果)）
# 查詢價格最高的商品資訊
SELECT * FROM t_product WHERE price >=ALL(SELECT price FROM t_product)
select * from t_product order by price desc limit 0,110.2、from型
子查詢的結果是多行多列的結果，類似於一張表格。
必須給子查詢取別名，即臨時表名，表的別名不要加“”和空格。
-- 思路一: 使用連線查詢
-- 使用外連線，查詢出分類表的所有資料
SELECT tc.cname,COUNT(tp.pid) FROM t_category tc LEFT JOIN t_product tp ON tp.cno = tc.cid GROUP BY tc.cname
-- 思路二: 使用子查詢
-- 第一步:對t_product根據cno進行分組查詢，統計每個分類的商品數量
SELECT cno,COUNT(pid) FROM t_product GROUP BY cno
-- 第二步: 用t_category表去連線第一步查詢出來的結果，進行連線查詢,此時要求查詢出所有的分類
SELECT tc.cname,IFNULL(tn.total,0) '總數量' FROM t_category tc LEFT JOIN (SELECT cno,COUNT(pid) total FROM t_product GROUP BY cno) tn ON tn.cno=tc.cid10.3、exists型
# 查詢那些有商品的分類
SELECT cid,cname FROM t_category tc WHERE EXISTS (SELECT * FROM t_product tp WHERE tp.cno = tc.cid);連結：blog.csdn.net/qq_42076902/article/details/121701974
