05.查詢表

查詢表

字段顯示可以使用別名:
- col1 AS alias1, col2 AS alias2, …
WHERE子句:指明過濾條件以實現“選擇"的功能:
- 過濾條件:
  - 布爾型表達式
  - 算術操作符:+,-,*,/,%
  - 比較操作符:=,<=>(相等或都為空),<>,!=(非標準SQL),>,>=,<,<=
  - 范圍查詢: BETWEEN min_num AND max_num
  - 不連續的査詢:IN(element1,element2,…)
  - 空查詢: IS NULL, IS NOT NULL
  - IN 判斷某字段是否在一組值中, NOT IN 判斷某字段不在一組值中,IN() 可以接受常量列表或子查詢
  - DISTINCT 去除重復行
  - 模糊査詢: LIKE 使用 % 表示任意長度的任意字符，_ 表示任意單個字符
  - RLIKE:正則表達式，索引失效，不建議使用
  - REGEXP:匹配字符串可用正則表達式書寫模式，同上
- 邏輯操作符:NOT，AND，OR，XOR
GROUP BY:根據指定的條件把查詢結果進行"分組"以用于做"聚合"運算
- 常見聚合函數: count(), sum(), max(),min(), avg(),注意:聚合函數不對null統計
- HAVING: 對分組聚合運算后的結果指定過濾條件
- 一旦分組 group by，select語句后只跟分組的字段，聚合函數
ORDER BY: 根據指定的字段對查詢結果進行排序
- 升序:ASC
- 降序:DESC
LIMIT [[offset,]row_count]:對查詢的結果進行輸出行數數量限制,跳過offset,顯示row_count行,offset默為值為0
對查詢結果中的數據請求施加“鎖”
- FOR UPDATE:寫鎖，獨占或排它鎖，只有一個讀和寫操作
- LOCK IN SHARE MODE:讀鎖，共享鎖，同時多個讀操作

1. 單表查詢

1.1 簡單查詢

mysql> select * from students where id < 5;
mysql> select * from students where gender = 'm';注意：第一條記錄的索引是 0
mysql> select * from students order by name desc limit 5;
mysql> select * from students order by name desc limit 0,5;# 判斷是否為NULL
mysql> select * from students where classid is null;
mysql> select * from students where classid <=> null;
mysql> select * from students where classid is not null;mysql> select * from students where stuid >= 2 and stuid <= 8;
mysql> select * from students where stuid between 2 and 8;mysql> select * from students where name like 's%';
mysql> select * from students where name rlike '.*[s].*';mysql> select * from students where classid in (1,2,3);
mysql> select * from students where classid not in (1,2,3);# 字段別名
mysql> select stuid 學員ID,name 姓名,gender 性別 from students;# ifnu11函數判斷指定的字段是否為空值,如果空值則使用指定默認值
mysql> select stuid 學號, name 姓名, ifnull(classid,'無班級') 班級 from students where classid is null;
+--------+-------------+-----------+
| 學號   | 姓名        | 班級      |
+--------+-------------+-----------+
|     24 | Xu Xian     | 無班級    |
|     25 | Sun Dasheng | 無班級    |
+--------+-------------+-----------+# 記錄去重
mysql> select distinct classid from students;
mysql> select distinct age,gender,classid from students;# 分頁查詢
mysql> select * from students limit 0,3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name        | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu |  22 | M      |       2 |         3 |
|     2 | Shi Potian  |  22 | M      |       1 |         7 |
|     3 | Xie Yanke   |  53 | M      |       2 |        16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.01 sec)mysql> select * from students limit 3,3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name      | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
|     4 | Ding Dian |  32 | M      |       4 |         4 |
|     5 | Yu Yutong |  26 | M      |       3 |         1 |
|     6 | Shi Qing  |  46 | M      |       5 |      NULL |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)# 查詢第n頁的數據,每頁顯示m條記錄
mysql>select * from students limit (n-1)*m,m;# 聚合函數
mysql> select count(*) from students where gender = 'm';
+----------+
| count(*) |
+----------+
|       15 |
+----------+
1 row in set (0.01 sec)mysql> select sum(age) from students where gender = 'm';
+----------+
| sum(age) |
+----------+
|      495 |
+----------+
1 row in set (0.00 sec)mysql> select sum(age)/count(*) from students where gender = 'm';
+-------------------+
| sum(age)/count(*) |
+-------------------+
|           33.0000 |
+-------------------+
1 row in set (0.00 sec)# 分組統計
注意:一旦使用分組group by,在select 后面的只能采用分組的列和聚合函數,其它的列不能放在select后面,否則根據系統變量SQL-MODE的值不同而不同的結果mysql> select classid,count(*) from students group by classid;
mysql> select gender,classid,count(*) from students group by gender,classid;mysql> select gender,classid,count(*) from students group by gender,classid having count(*) > 2;
mysql> select gender,classid,count(*) from students group by gender,classid having classid > 2;# group_concat函數實現分組信息的集合
mysql> select classid,group_concat(name) from students group by classid;# with rollup 分組后聚合函數統計后再做匯總
mysql> select ifnull(gender,'總計')性別,count(*) from students group by gender with rollup;# 排序
mysql> select * from students order by stuid desc limit 3;
mysql> select * from students order by stuid desc limit 3,3;
mysql> select * from students where classid is not null order by gender desc,age asc;# 正序排序時將NULL記錄排在最后
mysql> select classid from students order by -classid desc;# 分組后排序
mysql> select classid, count(*) from students group by classid order by classid desc;
mysql> select gender,classid,avg(age) from students where classid is not null group by gender,classid order by gender desc,classid desc;注意：分組和排序的次序 順序:group by,having,order by
mysql> select classid,count(*) from students group by classid having classid is not null order by classid asc;

2. 多表查詢

多表查詢，即查詢結果來自于多張表

子查詢:在SQL語句嵌套著查詢語句，性能較差，基于某語句的查詢結果再次進行的查詢
聯合查詢:UNION
交叉連接:笛卡爾乘積 CROSS JOIN
內連接:
- 等值連接:讓表之間的字段以"等值”建立連接關系
- 不等值連接
- 自然連接:去掉重復列的等值連接,語法: FROM table1 NATURAL JOIN table2;
外連接:
- 左外連接:FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
- 右外連接:FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
- 完全外連接: FROM tb1 FULL OUTER JOIN tb2 ON tb1.col=tb2.col 注意:MySQL不支持此SQL語法
自連接:本表和本表進行連接查詢
注意：ON 定義兩個表之間的連接條件， JOIN 把兩個表按照某種方式合并

2.1 子查詢

子查詢 subquery 即SQL語句調用另一個SELECT子句,可以是對同一張表,也可以是對不同表,

主要有以下四種常見的用法:

用于比較表達式中的子查詢;子查詢僅能返回單個值

mysql> select avg(age) from students;
mysql> select name,age from students where age > (select avg(age) from students);

用于IN中的子查詢:子查詢應該單獨查詢并返回一個或多個值重新構成列表

mysql> select name,age from students where age in (select age from teachers);

用于EXISTS 和 Not EXISTS

EXISTS（包括 NOT EXISTS）子句返回一個布爾值（TRUE 或 FALSE）。它內部包含一個子查詢（稱為內查詢），用于判斷該子查詢是否返回了任何行。

對于外查詢中的每一行數據，系統都會將該行的值帶入到內查詢中進行驗證。如果內查詢返回了至少一行結果，則 EXISTS 返回 TRUE，該行數據就會被包含在外查詢的結果集中；否則返回 FALSE，該行不會出現在最終結果中。

NOT EXISTS 的工作方式類似，只不過是在內查詢結果為空時才返回 TRUE。

mysql> select * from students s where exists (select * from teachers t where s.teacherid=t.tid);# 說明:
1、EXISTS(或 NOT EXISTS))用在 where之后，且后面緊跟子查詢語句(帶括號)
2、EXISTS(或 NOTEXISTS)只關心子查詢有沒有結果,并不關心子查詢的結果具體是什么
3、上述語句把students的記錄逐條代入到Exists后面的子查詢中，如果子查詢結果集不為空，即說明存在，那么這條students的記錄出現在最終結果集，否則被排除。

用于FROM子句中的子查詢

mysql> select classid,avg(age) age from students where classid is not null group by classid;# 主查詢 + 子查詢
mysql> select s.classid,s.age from (select classid,avg(age) age from students where classid is not null group by classid) s where s.age > 30;

子查詢優化

子查詢可以一次性完成很多邏輯上需要多個步驟才能完成的SQL操作。子查詢雖然可以使査詢語句很靈活，但執行效率不高。執行子查詢時，需要為內層查詢語句的查詢結果建立一個臨時表。然后外層查詢語句從臨時表中查詢記錄。查詢完畢后，再撤銷這些臨時表。因此，子查詢的速度會受到一定的影響。如果查詢的數據量比較大，這種影響就會隨之增大。

可以使用連接(J0IN)查詢來替代子査詢。連接査詢不需要建立臨時表，其速度比子查詢要快，如果查詢中使用到索引的話，性能會更好。

2.2 聯合查詢

聯合査詢 Union 實現的條件,多個表的字段數量相同,字段名和數據類型可以不同,但一般數據類型是相同的

mysql> select tid id,name,age,gender from teachers union select stuid id,name,age,gender from students;# 合并數據并去重 UNION, 合并數據且無需去重 UNION ALL
mysql> select * from teachers union select * from teachers;
mysql> select * from teachers union all select * from teachers;

2.3 交叉連接

cross join 即多表的記錄之間做笛卡爾乘積組合，并且多個表的列橫向合并相加,"雨露均沾

比如: 第一個表3行4列,第二個表5行6列,cross join后的結果為3*5=15行,4+6=10列

交叉連接生成的記錄可能會非常多,建議慎用

# 完全等價，都表示笛卡爾積
mysql> select * from teachers cross join students;
mysql> select * from teachers,students;

2.4 內連接

inner join 內連接取多個表的交集

mysql> select * from students s inner join teachers t on s.teacherid=t.tid;# 內連接后再過濾
mysql> select * from students s inner join teachers t on s.teacherid=t.tid where s.age > 30;# 自然連接
1. 當源表和目標表共享相同名稱的列時，就可以在它們之間執行自然連接，而無需指定連接列。
2. 在使用純自然連接時，如沒有相同的列時，會產生交叉連接(笛卡爾乘積)語法:SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2;

2.5 左和右外連接

左連接: 以左表為主根據條件查詢右表數據，如果根據條件查詢右表數據不存在使用nul值填充

右連接:以右表為主根據條件查詢左表數據，如果根據條件查詢左表數據不存在使用null值填充

mysql> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students s left join teachers t on s.teacherid=t.tid;mysql> select s.stuid,s.name,s.age,s.teacherid,t.tid,t.name,t.age from students s left outer join teachers t on s.teacherid=t.tid;# 先左外連接再過濾
mysql> select * from students s left join teachers t on s.teacherid=t.tid where s.teacherid is null;# 右外連接
mysql> select * from students s right join teachers t on s.teacherid=t.tid;
mysql> select * from students s right outer join teachers t on s.teacherid=t.tid;# 右外連接再過濾
mysql> select * from students s right outer join teachers t on s.teacherid=t.tid where t.tid is null;注意：右外連接其實就是把左外連接的表位置對換一下，結果是一樣的。

2.6 完全外連接

MySQL不支持完全外連接 full outer join語法

# 用這個方法替代 full outer join
mysql> select * from students left join teachers on students.teacherid=teachers.tid-> union-> select * from students right join teachers on students.teacherid=teachers.tid;