MySQL表的增刪查改(提高篇)

上一篇：MySQL表的增刪查改(基本篇)
接上一篇MySQL表基本的增刪查改，下面看一下提高篇：

一、資料庫約束

1、約束型別
NOT NULL：不為空約束。建立表時，可以指定某列不為空
UNIQUE ：唯一約束。指定某列為唯一的、不重複的
DEFAULT ：預設值約束。指定插入資料時，某列為空，設定預設值
PRIMARY KEY ： 主鍵約束。NOT NULL 和 UNIQUE 的結合。確保某列（或兩個列多個列的結合）有唯一標識，有助於更
容易更快速地找到表中的一個特定的記錄。
FOREIGN KEY ：外來鍵約束。用於關聯其他表的主鍵或唯一鍵，保證一個表中的資料匹配另一個表中的值的參照完整性。
CHECK ：保證列中的值符合指定的條件。對於MySQL資料庫，對CHECK子句進行分析，但是忽略CHECK子
句。

示例語句：

CREATE TABLE users (
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE articles (
	id INT PRIMARY KEY AUTO_INCREMENT,
	//指定id列為主鍵
	對於整數型別的主鍵，常配搭自增長auto_increment來使用。
	插入資料對應欄位不給值時，使用最大值+1
	author_id INT NOT NULL,
	name VARCHAR(20) DEFAULT 'unkown'，
	//指定插入資料時，name列為空，預設值unkown
	title VARCHAR(100) NOT NULL,
	content TEXT NOT NULL,
	published_at DATETIME NOT NULL

外來鍵
定義：上述；
作用：讓MySQL也知道了我們已經知道的表之間的關係，進而產生約束。

如下圖：

DELETE FROM 班級表 WHERE id = 2;
如果直接刪除，導致學生表中的昊天沒有班級資訊了，這就是因為沒有給MySQL增加約束，MySQL不知道這兩張表有直接關係。
為了解決這個情況，就增加了外來鍵。
有了外來鍵之後，在刪除汽車班之後，有以下幾種情況：
1、不允許刪除，除非把昊天刪除（預設）；
2、瀑布下落（幫你把昊天刪除）；
3、把昊天的班級id改成null。
因為MySQL中的外來鍵處理效能不是特別好，所以MySQL中一般不太推薦用外來鍵

二、表的設計

1. 整理需求
2. E-R 圖做輔助
3. 根據需求填寫欄位（欄位型別、約束關係）
4. 資料庫設計的三大正規化（減少資料儲存的冗餘度的）一對一，一對多，多對多。
   a) 表中的每個欄位都是原子的（一個欄位不能即儲存姓名又儲存地址）
   b) 有主鍵，每個欄位應該是和完整的主鍵有關係
   有主鍵，每個欄位應該是和主鍵呈現直接關係，而不是間接關係
5. 生成建表語句

三、新增

建立一張使用者表，設計有name姓名、email郵箱、sex性別、mobile手機號欄位。需要把已有的學生資料復
制進來，可以複製的欄位為name、qq_mail

-- 建立使用者表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment '姓名',
age INT comment '年齡',
email VARCHAR(20) comment '郵箱',
sex varchar(1) comment '性別',
mobile varchar(20) comment '手機號'
);
-- 將學生表中的所有資料複製到使用者表
insert into test_user(name, email) select name, qq_mail from student;

四、複雜查詢

聚合查詢
GROUP BY 子句（可以獨立出現）
HAVING 子句（必須跟在 GROUP BY 後邊）

聚合查詢：（統計每個人發表的文章數量，根據id要分組）
SELECT COUNT(*)FROM articles;
SELECT COUNT(*),author_id FROM articles GROUP BY author_id;
SELECT author_id,COUNT(*)count FROM articles GROUP BY author_id ORDER BY count;
SELECT author_id,COUNT(*)count FROM articles GROUP BY author_id HAVING count > 3 ORDER BY count;

1. 聚合函式（獨立於 GROUP BY 出現）
   a) COUNT
   b) SUM/AVG/MAX/MIN
   示例：

-- 統計班級共有多少同學
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;
-- 統計班級收集的 qq_mail 有多少個，qq_mail 為 NULL 的數
SELECT COUNT(qq_mail) FROM student;
-- 統計數學成績總分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的總分，沒有結果，返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;
-- 統計平均總分
SELECT AVG(chinese + math + english) 平均總分 FROM e
-- 返回英語最高分
SELECT MAX(english) FROM exam_result;
-- 返回 > 70 分以上的數學最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;

2. GROUP BY
   a) 分組憑證
   b) 支援多個分組
   c) SELECT 子句有限制（MySQL 有例外）：聚合函式 OR 分組憑證

select role,max(salary),min(salary),avg(salary) from emp group by role;

3. HAVING
   a) 和 WHERE 對比：WHERE 對聚合前的資料進行過濾；HAVING 對聚合後的資料進行過濾

select role,max(salary),min(salary),avg(salary) from emp group by role having
avg(salary)<1500;

聯表查詢
實際開發中往往資料來自不同的表，所以需要多表聯合查詢。多表查詢是對多張表的資料取笛卡爾積。多張表之間進行聯絡的查詢，不加篩選條件，出現的結果就是一個笛卡兒積。一般都要使用聯絡欄位進行過濾，例如：文章中的作者 id，評論中的作者id，評論中的文章id

內連線（inner join） vs 外連線（outer join）
全外聯（MySQL 不支援 Full Join）
注意：關聯查詢可以對關聯表使用別名

SELECT
	articles.id,author_id,title,content,
	users.id,username
FROM
	articles,users
WHERE author_id = users.id //可加可不加AND articles.id = 1;

內連線

//常用的內聯查詢：
SELECT classes.name, classes.`desc`, student.name FROM classes, student WHERE classes.id = student.classes_id;

SELECT classes.name, student.name FROM classes INNER JOIN student ON classes.id = student.classes_id;

SELECT classes.name,student.name FROM classes JOIN student ON classes.id = student.classes_id;

//內聯查詢 班級名稱、學生姓名、課程名稱、該課程的成績
SELECT 
c.name, s.name, co.name, sc.score 
FROM classes c, student s, course co, score sc 
WHERE c.id = s.classes_id AND s.id = sc.student_id AND co.id = sc.course_id;

外連線
外連線分為左外連線和右外連線。如果聯合查詢，左側的表完全顯示我們就說是左外連線；右側的表完全顯示我們就說是右外連線。

//左外聯：
SELECT classes.name, student.name FROM classes LEFT JOIN student ON classes.id = student.classes_id;

//右外聯：
SELECT classes.name, student.name FROM classes RIGHT JOIN student ON classes.id = student.classes_id;

自連線
自連線是指在同一張表連線自身進行查詢。
因為是同一張表聯表，所以必須起不同的別名以作區分

SELECT * FROM score s1 WHERE course_id = 1;
SELECT * FROM score s2 WHERE course_id = 3;
SELECT s1.* FROM score s1,score s2 WHERE s1.course_id = 1 AND s2.course_id = 3 AND s1.score < s2.score;

子查詢
子查詢是指嵌入在其他sql語句中的select語句，也叫巢狀查詢。
先通過一個 SELECT 查詢，得到一組結果集。利用這個結果集做第二次查詢

1. 把第一次的結果集看作一張表做新的查詢
2. 把第一次的結果集作為過濾條件查詢

//子查詢：
select classes_id from student where name = '不想畢業';
select * from student where classes_id = 1 AND name != '不想畢業';
//合併以上兩句話：
select * from student where classes_id = (SELECT classes_id FROM student WHERE name = '不想畢業') AND name != '不想畢業';

1. [NOT] IN關鍵字

SELECT * FROM course WHERE name IN("語文","英文");
 SELECT * FROM score WHERE course_id IN(4,6);
 SELECT * FROM score WHERE course_id IN(SELECT id FROM course WHERE name IN('語文','英文'));

2. [NOT] EXISTS關鍵字：

 select * 
from score sco
where exists (
    select sco.score from course cou where (name='語文' or name='英文') and cou.id = sco.course_id
);

select * 
from score sco 
where not exists (
    select sco.score 
    from course cou where (name!='語文' and name!='英文') and cou.id = sco.course_id
);

合併查詢
在實際應用中，為了合併多個select的執行結果，可以使用集合操作符 union，union all。使用UNION和UNION ALL時，前後查詢的結果集中，欄位需要一致。
UNION vs UNION ALL
UNION 會進行合併重複項
UNION ALL 不會合並重復項

//union
select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or來實現
select * from course where id<3 or name='英文';
//union all
-- 可以看到結果集中出現重複資料Java
select * from course where id<3
union all
lect * from course where name='英文'

---