1. 過濾條件
1.1 過濾條件之having
[1]概念
HAVING 子句用於對分組後的結果進行過濾。它通常與 GROUP BY 子句一起使用，在 SELECT 語句的聚合函式（如 SUM(), AVG(), COUNT(), MAX(), MIN() 等）之後應用條件。
HAVING 子句與 WHERE 子句類似，但 HAVING 適用於分組後的資料，而 WHERE 適用於原始資料。
[2]應用
分組之後的資料再進行過濾，不能用where，只能用having
案例：員工按部門進行分組，篩選出30歲以上員工的工資，篩選出30歲以上員工平均工資大於10000的部門
先篩選出30歲以上的員工資料，再對資料按部門進行分組，分組後用聚合函式avg求平均值
select post,avg(salary) from emp where age>30 group by post;
在以上分組後的基礎上再篩選出平均工資大於10000的部門
方法一：分組之後的資料用where再篩選（報錯）
select post,avg(salary) from emp where age>30 group by post where avg(salary)>10000;
方法二：分組之後的資料用having再篩選
select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;
1.2 過濾條件之distinct
去重，使用SELECT語句查詢資料時，distinct可以從查詢結果中刪除重複的行，並僅返回唯一的記錄。
語法：select distinct 列1,列2... from 表名
主鍵不重複，對主鍵去重無意義。
select distinct id,age from emp;
對post去重
select distinct post from emp;
1.3 過濾條件之order by
[1]概念
語法：select */列名 from 表名 order by 列名 asc/desc;
asc：升序（asc/desc不寫時預設也為升序）
desc：降序
[2]程式碼應用
(1)查詢所有資料，按工資的升序排列
select * from emp order by salary;
(2)查詢所有資料，按工資的降序排列
select * from emp order by salary desc;
(3)先按部門編號降序排，同一個部門編號內的多條記錄按年齡升序排
select * from emp order by office desc,age asc;
(4)混合排序，與where、group by、having配合使用
員工按部門進行分組，篩選出30歲以上員工的工資，篩選出30歲以上員工平均工資大於500的部門，對篩選出的列（部門、平均工資）按升序排列
select post,avg(salary) from emp where age>30 group by post having avg(salary)>500 order by avg(salary) asc;
1.4 過濾條件之limit
[1] 概念
limit子句用於限制查詢結果的數量
使用方法一：
limit offset, count
offset：指定從哪一條記錄開始返回資料（從0開始計數）。
count：指定返回記錄的數量。
使用方法二：
limit count
只指定count時，offset預設為0，即從第一條記錄開始返回資料。
[2]程式碼應用
(1)獲取前10條記錄
select * from emp limit 10;
(2)從第6條記錄開始，向後獲取5條記錄
select * from emp limit 5,5;
1.5 過濾條件之正規表示式
[1]概念
列名 REGEXP 表示式：檢查列中的值是否匹配正規表示式模式。
|
選項 |
說明 |
例子 |
匹配值示例 |
|
^ |
匹配文字的開始字元 |
‘^b’ 匹配以字母 b 開頭的字串 |
book、big、banana、bike |
|
$ |
匹配文字的結束字元 |
‘st$’ 匹配以 st 結尾的字串 |
test、resist、persist |
|
. |
匹配任何單個字元 |
‘b.t’ 匹配任何 b 和 t 之間有一個字元 |
bit、bat、but、bite |
|
* |
匹配前面的字元 0 次或多次 |
‘f*n’ 匹配字元 n 前面有任意個字元 f |
fn、fan、faan、abcn |
|
+ |
匹配前面的字元 1 次或多次 |
‘ba+’ 匹配以 b 開頭，後面至少緊跟一個 a |
ba、bay、bare、battle |
|
? |
匹配前面的字元 0 次或1次 |
‘sa?’ 匹配0個或1個a字元 |
sa、s |
|
字串 |
匹配包含指定字元的文字 |
‘fa’ 匹配包含‘fa’的文字 |
fan、afa、faad |
|
[字符集合] |
匹配字符集合中的任何一個字元 |
‘[xz]’ 匹配 x 或者 z |
dizzy、zebra、x-ray、extra |
|
[^] |
匹配不在括號中的任何字元 |
‘[^abc]’ 匹配任何不包含 a、b 或 c 的字串 |
desk、fox、f8ke |
|
字串{n,} |
匹配前面的字串至少 n 次 |
‘b{2}’ 匹配 2 個或更多的 b |
bbb、bbbb、bbbbbbb |
|
字串{n,m} |
匹配前面的字串至少 n 次， 至多 m 次 |
‘b{2,4}’ 匹配最少 2 個，最多 4 個 b |
bbb、bbbb |
[2]程式碼應用
(1)準備資料
CREATE TABLE `info` (
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(40) NULL DEFAULT NULL,
`height` int(40) NULL DEFAULT NULL,
`gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
);
INSERT INTO `info` VALUES ('Thomas ', 25, 168, '男');
INSERT INTO `info` VALUES ('Tom ', 20, 172, '男');
INSERT INTO `info` VALUES ('Dany', 29, 175, '男');
INSERT INTO `info` VALUES ('Jane', 27, 171, '男');
INSERT INTO `info` VALUES ('Susan', 24, 173, '女');
INSERT INTO `info` VALUES ('Green', 25, 168, '女');
INSERT INTO `info` VALUES ('Henry', 21, 160, '女');
INSERT INTO `info` VALUES ('Lily', 18, 190, '男');
INSERT INTO `info` VALUES ('LiMing', 19, 187, '男');
(2)查詢name列以 j 開頭的記錄
select * from info where name regexp "^j";
(3)查詢name列以 y 結尾的記錄
select * from info where name regexp "y$";
(4)查詢name列的值包含a 和 y，且兩個字母之間只有一個字母的記錄
select * from info where name regexp "a.y";
(5)查詢name列的值包含T，且 T 後面出現字母 h 0次或多次的記錄
select * from info where name regexp "Th*";
(6)查詢name列的值包含T，且 T 後面至少出現h 一次的記錄
select * from info where name regexp "Th+";
(7)查詢name列的值包含S，且 S 後面出現 a 0次或一次的記錄
select * from info where name regexp "Sa?";
2. 多表查詢與子查詢
2.1 概念
子查詢：
子查詢是一種巢狀在另一個查詢中的查詢。子查詢可以出現在 SELECT, INSERT, UPDATE, 或 DELETE 語句中，並且必須被圓括號包圍。
類比於解決問題的方式(一步一步解決)、將一條SQL語句的查詢結果加括號當做另外一條SQL語句的查詢條件。
多表查詢：
多表查詢通常使用 JOIN 關鍵字來組合來自兩個或多個表的資料。
將多張表拼接在一起，形成一張表，然後基於單表查詢資料。
2.2 準備資料
create table dep(
id int PRIMARY KEY AUTO_INCREMENT,
name varchar(20)
);
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
gender ENUM("male","female") NOT NULL DEFAULT "male",
age INT,
dep_id INT
);
insert into dep values
("200","開發"),
("201","運營"),
("202","銷售"),
("203","財務"),
("204","設計");
insert into emp(name,gender,age,dep_id) values
("ronaldo","male",18,200),
("avril","female",18,201),
("kylian","male",38,202),
("haaland","male",18,203),
("cristiano","male",28,204),
("lavigne","female",18,205);
2.3 子查詢
(1)案例1：獲取員工ronaldo所在的部門名稱
方法一：分步查詢
先在員工表中查詢員工ronaldo部門id，再去部門表中根據部門id查詢部門名稱
select dep_id from emp where name="ronaldo";
select name from dep where id=200;
方法二：子查詢
將方法一的兩步合併為一步，將一個SQL語句的結果加上括號作為查詢條件
select name from dep where id=(select dep_id from emp where name="ronaldo");
(2)案例2：查詢開發或運營部門的員工資訊
方法一：分步查詢
先在部門表中查詢對應部門id，再去員工表中根據部門id查詢對應員工資訊
select id from dep where name="開發" or name="運營";
select * from emp where dep_id=200 or dep_id=201;
方法二：子查詢
將方法一的兩步合併為一步
select * from emp where dep_id in (select id from dep where name="開發" or name="運營");
2.4 多表查詢
[1]笛卡爾積
（1）笛卡爾積的概念
笛卡爾積是一個數學概念，它描述的是兩個集合之間所有可能的元素組合的數量。
具體來說，如果集合A有n個元素，集合B有m個元素，則它們的笛卡爾積的大小為nm。
（2）笛卡爾積的理解
笛卡爾積是透過組合兩個集合的所有元素來建立一個新的集合的過程。
在最簡單的例子中，如果有一個集合包含三個元素a、b和c，另一個集合包含兩個元素x和y，那麼這兩個集合的笛卡爾積將包含六個元素：ax、ay、bx、by、cx和cy。
（3）MySQL中的笛卡爾積
在SQL中，當使用JOIN操作將兩個或更多的表連線在一起時，結果集中的行數是所有連線表的行數的乘積。這就是所謂的笛卡爾積。
例如，假設有兩個表A和B，其中A有5行，B有3行。
如果使用INNER JOIN將這兩個表連線起來，那麼結果集中將會有5 x 3 = 15行。
這是因為對於每一行A，可以從B中選擇任意一行進行匹配。
因此，總共有5種不同的方式來組合A表中的每一行和B表中的每一行，這導致了最終結果集的大小為5 x 3 = 15。
這個過程就是笛卡爾積，它是數學中的一種運算，用於計算兩個集合的所有可能的元素組合的數量。
在這個情況下，每個元素都是一個表格中的行。
所以，當在MySQL中使用JOIN操作時，結果集的大小實際上是所有連線表的行數的乘積，這就是為什麼稱其為笛卡爾積的原因。
[2]拼虛擬表
拼虛擬表的結果即為笛卡爾積
select * from dep,emp;
[3]虛擬表去除無效資料
雖然部門表和員工表沒有透過外來鍵直接關聯，但是設計的初衷是部門表的id即為員工表的dep_id，因此拼接虛擬表生成了大量的無效資料，需要去除
select * from dep,emp where dep.id=emp.dep_id;
部門表的id=員工表的dep_id 的記錄才會被保留
[4]拼表關鍵字
(1)概念
inner join：內連線、交集
left join：左連線 、左表所有的資料都展示出來，右表中沒有對應的項就用null表示
right join：右連線 、右表所有的資料都展示出來，左表中沒有對應的項就用null表示
union：全連線 、合併兩個或多個 SELECT 語句的結果集，每個 SELECT 語句中的列數必須相同，並且對應列的資料型別也必須相容，預設去除重複的行
(2)內連線程式碼示例
inner join仍然為全部拼接，加上限制條件部門表的id=員工表的dep_id後，由於部門表的id沒有205，因此求交集被捨去
select * from emp inner join dep;
select * from emp inner join dep on emp.dep_id=dep.id;
(3)左連線程式碼示例
select * from emp left join dep on emp.dep_id=dep.id;
(4)右連線程式碼示例
select * from dep right join emp on emp.dep_id=dep.id;
(5)全連線程式碼示例
基本語法：
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
create table teacher(id int auto_increment primary key, name varchar(30));
insert into teacher (id, name) values (1, 'ronaldo'), (2, 'lavigne');
create table student(id int auto_increment primary key, name varchar(30));
insert into student (id, name) values (1, 'ronaldo'), (2, 'avril');
使用union：
去掉了重複的記錄
select id,name from teacher union select id,name from student;
