一、外鍵
假設現在我們有一張員工資訊表，表的欄位如下：
id # 主鍵
name # 姓名
age # 年齡
dep_name # 部門名稱
dep_desc # 部門描述
單從資料存盤上來看，這個表是沒有問題的，但是從程式開發角度來看的話，這張表目前有如下三個缺陷：
- 表的重點不清晰：到底是員工表還是部門表（其實可以忽略）
- 表中欄位會出現重復存盤，如資料量較大，那么會存盤多個相同部門的名稱和描述（其實也可以忽略）
- 表的擴展性極差，如要修改某個部門名稱資訊，就會導致牽一發而動全身
（這一點不能忽略）
要如何解決上述的三個缺陷呢？
其實我們可以將上述表拆分成兩張表，一張員工表（emp），一張部門表（dep）：
emp表：
id name age
dep表：
id dep_name dep_desc
這樣一來，上述的三個缺陷便全部解決了，但是你應該也發現了一個小問題，那就是這兩張表之間的資料沒有關系了，那又如何解決表與表之間的關系呢？
于是，MySQL給我們提供了外鍵:foreign key這種約束方式來幫我們解決上述問題，
通過建立外鍵欄位來標識表與表之間的資料關系，也可以簡單的理解為該欄位可以讓你去到其他表中查找資料，
下面就讓我們來看看如何使用這種方式給表與表之間添加關系，
二、表與表之間建立關系
表與表之間的關系總共就三種，如下：
一對多多對多一對一
其實我們日常所能接觸到的最常見的關系反而不是這三種，而是 沒有關系 ，
當然，沒有關系 我們是比較容易判斷的，那么對于上面三種關系，我們又該如何判斷呢，這里我推薦一種判斷表的方式，那就是換位思考，接下來就來看看這種方式在三種關系判斷中是如何體現的，
2.1 一對多
以員工和部門表為例：
- 先站在員工表的基礎之上：
- 問：一個員工資訊能否對應多個部門資訊
- 答：不可以
- 再站在部門表的基礎之上：
- 問：一個部門資訊能否對應多個員工資訊
- 答：可以
結論：
一個可以，一個不可以，那么表關系就是
一對多；針對
一對多的表關系，外鍵欄位建在多的一方；表關系沒有
多對一一說，都是一對多；
在上述例子中，員工表是多，部門表是一，
SQL陳述句實作如下：
"""先創建不含外鍵欄位的基本表，之后再添加外鍵欄位"""
"""部門表"""
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(254)
);
"""員工表"""
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
)
2.2 多對多
以書籍表和作者表為例：
- 先站在書籍表的基礎之上：
- 問：一個書籍資訊能否對應多個作者資訊
- 答：可以
- 再站在作者表的基礎之上：
- 問：一個作者資訊能否對應多個書籍資訊
- 答：可以
結論：
兩個都可以，那么表關系就是
多對多；針對
多對多表關系，需要單獨開設第三張表存盤兩張表的關聯資料（第三張表也可以不系結關系）
SQL陳述句實作如下：
"""書籍表"""
create table book(
id int primary key auto_increment,
title varchar(32),
price float(6,2)
);
"""作者表"""
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
"""第三張關系表"""
create table book2author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id)
on update cascade # 級聯更新
on delete cascade, # 級聯洗掉
foreign key(author_id) references author(id)
on update cascade # 級聯更新
on delete cascade # 級聯洗掉
);
2.3 一對一
以作者表和作者詳情表為例：
- 先站在作者表的基礎之上：
- 問：一個作者資訊能否對應多個作者詳情資訊
- 答：不可以
- 再站在作者詳情表的基礎之上：
- 問：一個作者詳情資訊能否對應多個作者資訊
- 答：不可以
結論：
兩個都不可以，那么表關系可能是
一對一或者沒有關系，這個視具體情況應該就能判斷出來；針對
一對一表關系，外鍵欄位建在任何一方都可以，但是推薦建在查詢頻率較高的表中；
SQL陳述句實作如下：
"""作者詳情表"""
create table author_detail(
id int primary key auto_increment,
phone bigint,
address varchar(32)
);
"""作者表"""
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
author_id int unique, # 要加上唯一約束條件，這是與一對多表關系唯一不同的地方
foreign key(author_id) references author_detail(id)
on update cascade # 級聯更新
on delete cascade # 級聯洗掉
);
2.4 外鍵約束
-
在創建表的時候，需要先創建被關聯表（沒有外鍵欄位的表）；
-
在插入新資料的時候，應該先確保被關聯表中有資料；
-
在插入新資料的時候，外鍵欄位只能填寫被關聯表中已經存在的資料；
-
在修改和洗掉被關聯表中的資料的時候，無法直接操作，如果想要資料之間自動修改和洗掉，需要添加額外的配置，即上面所使用到的級聯更新，級聯洗掉：
on update cascade # 級聯更新 on delete cascade # 級聯洗掉
由于外鍵有實質性的諸多約束，當表特別多的時候外鍵的增多反而會增加耦合程度，
因此在實際開發專案中，有時候并不會直接使用外鍵創建表關系，而是通過SQL陳述句層面，建立邏輯意義上的表關系，
eg：操作員工表的SQL陳述句執行完畢之后，立刻跟著執行操作部門的SQL陳述句，
2.5 操作表的SQL陳述句補充
"""修改表名"""
ALTER TABLE 表名 RENAME 新表名;
"""增加欄位"""
ALTER TABLE 表名 ADD 欄位名 資料型別 [約束條件…],...;
ALTER TABLE 表名 ADD 欄位名 資料型別 [約束條件…] FIRST;
ALTER TABLE 表名 ADD 欄位名 資料型別 [約束條件…] AFTER 欄位名;
"""洗掉欄位"""
ALTER TABLE 表名 DROP 欄位名;
"""修改欄位"""
ALTER TABLE 表名 MODIFY 欄位名 資料型別 [約束條件…];
ALTER TABLE 表名 CHANGE 舊欄位名 新欄位名 資料型別 [約束條件…];
modify只能改欄位資料型別和約束條件，不能改欄位名，但是change可以！
三、SQL查詢關鍵字
3.1 資料準備
"""資料準備"""
create table emp(
id int primary key auto_increment,
name varchar(20) not null, # 姓名
sex enum('male','female') not null default 'male', # 性別
age int(3) unsigned not null default 28, # 年齡
hire_date date not null, # 錄用日期
department varchar(50), # 部門名稱
department_comment varchar(100), # 崗位名稱
salary double(15,2), # 薪資
office int, #辦公室（同一部門在一件辦公室）
depart_id int # 部門編號
);
"""插入記錄"""
"""三個部門：教學，銷售，運營"""
insert into emp(name,sex,age,hire_date,department,salary,office,depart_id) values
('jason','male',18,'20170301','teacher',7300.33,401,1), # 以下是教學部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2), # 以下是銷售部門
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('樂樂','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龍','male',28,'20160311','operation',10000.13,403,3), # 以下是運營部門
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬銀','female',18,'20130311','operation',19000,403,3),
('程咬銅','male',18,'20150411','operation',18000,403,3),
('程咬鐵','female',18,'20140512','operation',17000,403,3);
3.2 查詢關鍵字之select與from
-
select 控制的是查詢表里面的哪些欄位；
select * from emp; # 查詢所有欄位 select name,age from emp; # 查詢指定欄位 -
from控制的是查詢哪張表；
3.3 查詢關鍵字之where篩選
下面通過幾道練習題來幫助我們體會where關鍵字的用法及其他補充知識點：
"""1.查詢id大于等于3小于等于6的資料"""
select * from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
"""2.查詢薪資是20000或者18000或者17000的資料"""
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000); # 簡寫，類似于Python中的成員運算
"""3.查詢員工姓名中包含o字母的員工姓名與其薪資"""
select name,salary from emp where name like '%o%';
"""4.查詢員工姓名是由四個字符組成的員工姓名與其薪資"""
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
"""5.查詢id小于3或者大于6的資料"""
select * from emp where id not between 3 and 6; # 取反
"""6.查詢薪資不在20000，18000，17000范圍的資料"""
select * from emp where salary not in (20000,18000,17000);
"""7.查詢崗位描述為空的員工名與崗位名（針對null不能用等號，只能用is）"""
select name,department_comment from emp where department_comment = NULL; # 不報錯，但查詢結果為空！
select name,department_comment from emp where department_comment is NULL;
補充知識點：
模糊查詢：沒有明確的篩選條件
關鍵字：like
關鍵符號：
%：匹配任意個數任意字符_：匹配單個任意字符
示例：
show variables like '%mode%'; # 篩選出含有mode字符的結果
3.4 查詢關鍵字之group by分組
即按照某個指定的條件將單個單個的個體分成 一個個整體，
eg：按照男女將人分組、按照年齡分組、按照膚色分組......
分組之后默認只能獲取到分組的依據，其他資料不能直接獲取，
針對5.6版本需要自己設定sql_mode:
set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
聚合函式：主要就是配合分組一起使用，這里列出五個常用的聚合函式：
max # 最大
min # 最小
sum # 求和
count # 計數
avg # 平均
資料分組所能應用到的場景：每個部門的平均薪資，男女比例等，
下面通過幾道練習題來幫助我們體會group by關鍵字的用法：
"""1.按部門分組"""
select * from emp group by department; # 沒設定sql_mode之前分組后取出的是每個組的第一條資料
select id,name,sex from emp group by department; # 驗證
設定sql_mode為only_full_group_by后，意味著以后但凡分組，只能取到分組的依據，不應該再去取組里面的單個元素的值，那樣的話分組就沒有意義了，因為不分組就是對單個元素資訊的隨意獲取，
set global sql_mode = "strict_trans_tables,only_full_group_by";
# 重新連接客戶端
select * from emp group by department; # 報錯
select id,name,sex from emp group by department; # 報錯
select department from emp group by department; # 獲取部門資訊
強調:只要分了組，就不能夠再“直接”查找到單個資料資訊了，只能獲取到組名
"""2.獲取每個部門的最高工資"""
# 以組為單位統計組內資料>>>>聚合查詢(聚集到一起合成為一個結果)
select department,max(salary) from emp group by department;
在顯示的時候還可以給欄位取別名：
select department as '部門',max(salary) as '最高工資' from emp group by department;
as也可以省略，但是不推薦，因為語意不明確，
"""每個部門的最低工資"""
select department,min(salary) from emp group by department;
"""每個部門的平均工資"""
select department,avg(salary) from emp group by department;
"""每個部門的工資總和"""
select department,sum(salary) from emp group by department;
"""每個部門的人數"""
select department,count(id) from emp group by department; # 統計的時候只要是非空欄位,效果都是一致的
"""演示特殊情況:department_comment"""
select department,count(department_comment) from emp group by department;
補充說明：
-
group_concat：分組之后使用如果真的需要獲取分組以外的資料欄位，可以使用
group_concat()"""每個部門的員工姓名""" select department,group_concat(name) from emp group by department; """每個部門的員工姓名,性別（支持自定義連接符）""" select department,group_concat(name,'|',sex) from emp group by department; -
concat：未分組時使用select concat(name,sex) from emp; select concat(name,'|',sex) from emp;
3.5 查詢關鍵字之having過濾
where與having都是篩選功能，但是有區別：
where是在分組之前對資料進行篩選having是在分組之后對資料進行篩選
"""篩選出員工年齡在30歲以上，且平均薪資在10000以上的部門"""
select department,avg(salary) from emp where age>30 group by department having avg(salary)>10000;
注意：
一條SQL陳述句的結果也可以看成是一張全新的表，
3.6 查詢關鍵字之distinct去重
"""對有重復的展示資料進行去重操作 一定要是重復的資料"""
select distinct department from emp; # 去重單個欄位
select distinct id,age from emp; # 如去重多個欄位必須滿足去重的欄位資料都重復才能去重，否則不去重
3.7 查詢關鍵字之order by排序
select * from emp order by salary asc; # 也可不寫asc，默認升序排
select * from emp order by salary desc; # 降序排
"""先按照age降序排，在年齡相同的情況下再按照薪資升序排"""
select * from emp order by age desc,salary asc;
"""統計各部門年齡在10歲以上的員工平均工資，并且保留平均工資大于1000的部門，然后對平均工資進行降序排序"""
select department,avg(salary) from emp where age>10 group by department having avg(salary)>1000 order by avg(salary) desc;
3.8 查詢關鍵字之limit分頁
"""限制展示條數"""
select * from emp limit 3;
"""查詢工資最高的人的詳細資訊"""
select * from emp order by salary desc limit 1;
"""分頁顯示"""
select * from emp limit 0,5; # 第一個引數表示起始位置，第二個引數表示的是條數，不是索引位置
select * from emp limit 5,5;
3.9 查詢關鍵字之regexp正則
select * from emp where name regexp '^j.*(n|y)$';
3.10 查詢關鍵字之exists存在
exists關鍵字表示是否存在，
在使用exists關鍵字時，內層查詢陳述句不回傳查詢的記錄，而是回傳一個真偽值，True或False，
-
當回傳
True時，外層查詢陳述句將進行查詢； -
當回傳值為
False時，外層查詢陳述句不進行查詢，
select id,name from emp where exists (select id from emp where id > 3); # 有結果
select id,name from emp where exists (select id from emp where id > 200); # 內層查詢無結果，所以外層查詢陳述句不執行
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/430237.html
標籤：其他