之前的SQL基礎1中已經介紹了部分Select的內容，但是，實際使用中select 還有很多其他的用法，本文會再介紹部分select的其他用法，
1. 去重查詢
1.1 創建演示表
創建2張表用于演示，表名分別為student和class，后續也將繼續用這2張表演示，2張表的資料如下：
student表
class表
1.2 查詢student表的所有class_no內容
mysql> select class_no from student; +----------+ | class_no | +----------+ | 201801 | | 201901 | | 201901 | | 201902 | | 201902 | | 201902 | | 201902 | +----------+
可見，查詢結果中很多重復的情況，
1.3 去重查詢所有的class_no
去重使用 DISTINCT 關鍵字即可
mysql> select distinct class_no from student; +----------+ | class_no | +----------+ | 201801 | | 201901 | | 201902 | +----------+ 3 rows in set (0.00 sec)
2. 條件查詢
條件查詢可以有很多種組合，其中用 AND 或 OR連接不同的條件，同時可以用in , not in , >、>=、 <、<=、 =等條件進行范圍查詢等
2.1 AND條件
AND的意義相當于“且”，也就是AND前后的條件必須同時成立，例如：
查詢class_no為201901 并且age>=22的學生
mysql> select * from student where age>=22 and class_no='201901'; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 1 | 李白 | 25 | 201901 | +----+----------+------+----------+ 1 row in set (0.00 sec)
2.2 OR條件
OR意味著“或”，即OR前后條件中的一個滿足條件及成立，例如：
查詢student表中age>=24 后者班級號為201801的學生
mysql> select * from student where age>=24 or class_no='201801'; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 1 | 李白 | 25 | 201901 | | 4 | 喬峰 | 30 | 201801 | | 7 | 杜甫 | 24 | 201902 | +----+----------+------+----------+ 3 rows in set (0.00 sec)
2.3 AND和OR的優先級
AND和OR的優先級順序是 AND大于OR，有括號的 先處理括號的 ，即 AND和OR同時出現時，先處理AND 再與OR判斷，但是出現括號，有出現括號的先處理括號里的，例如：
/** 沒有括號 先處理and 最后處理or **/ mysql> select * from student where age<23 and class_no='201902' or class_no='201801'; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 4 | 喬峰 | 30 | 201801 | | 3 | 王維 | 21 | 201902 | | 5 | 陳六 | 22 | 201902 | +----+----------+------+----------+ 3 rows in set (0.00 sec) /** 有括號時，先庫哦李括號里的or,最后處理 外層的AND ** / mysql> select * from student where age<23 and (class_no='201902' or class_no='201801'); +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 3 | 王維 | 21 | 201902 | | 5 | 陳六 | 22 | 201902 | +----+----------+------+----------+ 2 rows in set (0.00 sec)
建議： 實際使用時，如果嵌套的關系太多，當確定需要先處理哪個關系時建議都加上括號，已避免寫法錯誤導致結果與預期不一致，
2.4 IN或NOT IN
IN 或 NOT IN的使用頻率也是非常高的，例如：
/** in **/ mysql> select * from student where class_no in ('201901','201902'); +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 1 | 李白 | 25 | 201901 | | 2 | 蘇軾 | 20 | 201901 | | 3 | 王維 | 21 | 201902 | | 5 | 陳六 | 22 | 201902 | | 7 | 杜甫 | 24 | 201902 | | 8 | 岳飛 | 23 | 201902 | +----+----------+------+----------+ 6 rows in set (0.00 sec) /** not in **/ mysql> select * from student where class_no not in ('201901','201902'); +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 4 | 喬峰 | 30 | 201801 | +----+----------+------+----------+ 1 row in set (0.00 sec)
3. 排序及分頁
3.1 排序
排序使用order by來進行，可以指定一個或多個欄位進行排序，同時可以指定升序（ASC，默認的是升序）或降序（DESC），
/** 按照age升序排序 **/ mysql> select * from student order by age asc; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 2 | 蘇軾 | 20 | 201901 | | 3 | 王維 | 21 | 201902 | | 5 | 陳六 | 22 | 201902 | | 8 | 岳飛 | 23 | 201902 | | 7 | 杜甫 | 24 | 201902 | | 1 | 李白 | 25 | 201901 | | 4 | 喬峰 | 30 | 201801 | +----+----------+------+----------+ 7 rows in set (0.00 sec) /** 按照age降序排列 **/ mysql> select * from student order by age desc; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 4 | 喬峰 | 30 | 201801 | | 1 | 李白 | 25 | 201901 | | 7 | 杜甫 | 24 | 201902 | | 8 | 岳飛 | 23 | 201902 | | 5 | 陳六 | 22 | 201902 | | 3 | 王維 | 21 | 201902 | | 2 | 蘇軾 | 20 | 201901 | +----+----------+------+----------+ 7 rows in set (0.00 sec) /** 先按照class_no升序，class_no 相同時按照age降序排列 **/ mysql> select * from student order by class_no,age desc; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 4 | 喬峰 | 30 | 201801 | | 1 | 李白 | 25 | 201901 | | 2 | 蘇軾 | 20 | 201901 | | 7 | 杜甫 | 24 | 201902 | | 8 | 岳飛 | 23 | 201902 | | 5 | 陳六 | 22 | 201902 | | 3 | 王維 | 21 | 201902 | +----+----------+------+----------+ 7 rows in set (0.00 sec)
注：當表的資料量較大時，建議排序欄位上有索引，
3.2 分頁
分頁查詢在資料展示上是使用頻率非常高的功能之一，1其語法為：
select field_names from tbname where filter order by oderby_fieldname limit offset,row_counts
其中 offset是偏移量，即之前遍歷了的資料量，row_counts指每頁的資料量，
例如，分頁遍歷其中一個表的記錄，每頁3條記錄，例如：
/** 首頁 **/ mysql> select * from student order by id limit 3*0,3; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 1 | 李白 | 25 | 201901 | | 2 | 蘇軾 | 20 | 201901 | | 3 | 王維 | 21 | 201902 | +----+----------+------+----------+ 3 rows in set (0.00 sec) /** 第二頁 **/ mysql> select * from student order by id limit 3*1,3; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 4 | 喬峰 | 30 | 201801 | | 5 | 陳六 | 22 | 201902 | | 7 | 杜甫 | 24 | 201902 | +----+----------+------+----------+ 3 rows in set (0.00 sec) /** 第三頁 **/ mysql> select * from student order by id limit 3*2,3; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 8 | 岳飛 | 23 | 201902 | +----+----------+------+----------+ 1 row in set (0.00 sec)
注： order by 的欄位上一定要有索引,否則 可能遍歷額結果有重復，相關例子可以自行測驗，
4. 聚合查詢
很多情況下，我們都需要進行一些匯總類的操作，即聚合查詢，聚合查詢通常需要配合GROUP BY關鍵字進行分組聚合，下面使用幾種常見的聚合查詢操作，
4.1 count
count是指統計記錄條數，
4.1.1 不分組的情況下的聚合：
/** 查看student表的記錄總數 **/ mysql> select count(*) from student; +----------+ | count(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) /** 查詢age大于24的記錄數 **/ mysql> select count(*) from student where age>=24; +----------+ | count(*) | +----------+ | 3 | +----------+ 1 row in set (0.00 sec)
4.1.2 分組情況下聚合：
/** 按照class_no分組統計，即查詢每個班級的人數 **/
mysql> select class_no, count(*) from student group by class_no; +----------+----------+ | class_no | count(*) | +----------+----------+ | 201801 | 1 | | 201901 | 2 | | 201902 | 4 | +----------+----------+ 3 rows in set (0.00 sec) /**查詢每個班級的人數，同時按照人數降序排列 **/ mysql> select class_no, count(*) from student group by class_no order by count(*) desc ; +----------+----------+ | class_no | count(*) | +----------+----------+ | 201902 | 4 | | 201901 | 2 | | 201801 | 1 | +----------+----------+ 3 rows in set (0.00 sec)
/**查詢每個班級的人數，同時只回傳人數大于1個的class_no及人數 **/
mysql> select class_no, count(*) from student group by class_no having count(*)>1 ; +----------+----------+ | class_no | count(*) | +----------+----------+ | 201901 | 2 | | 201902 | 4 | +----------+----------+ 2 rows in set (0.00 sec)
4.2 min、max 、avg 、sum
除了count的聚合操作外，還有min（最小）、max（最大） 、avg （平均）、sum（求和）等聚合操作，其操作和count類似，
例如：
mysql> select max(age),min(age),avg(age),sum(age) from student; +----------+----------+----------+----------+ | max(age) | min(age) | avg(age) | sum(age) | +----------+----------+----------+----------+ | 30 | 20 | 23.5714 | 165 | +----------+----------+----------+----------+ 1 row in set (0.00 sec)
5. 子查詢
當進行查詢下的時候 需要查詢的條件是另外一個select陳述句的結果的時候可以用到子查詢來處理，此時要用in、not in 、exists、not exists以及=、!=等，
例如：
/** 查詢存在于class表的student的記錄 **/ mysql> select * from student where class_no in (select class_no from class); +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 1 | 李白 | 25 | 201901 | | 2 | 蘇軾 | 20 | 201901 | | 3 | 王維 | 21 | 201902 | | 5 | 陳六 | 22 | 201902 | | 7 | 杜甫 | 24 | 201902 | | 8 | 岳飛 | 23 | 201902 | +----+----------+------+----------+ 6 rows in set (0.00 sec) /** 查詢不存在于class表的student的記錄 **/ mysql> select * from student where class_no not in (select class_no from class); +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 4 | 喬峰 | 30 | 201801 | +----+----------+------+----------+ 1 row in set (0.00 sec)
exists和not exists在此時（子查詢不存在null的情況下）查詢結果是等價的（查詢效率有差異，且和資料量有關），對于此問題，各位可以自己測一下，
6. 表連接
當需要同時展示多個表的欄位時，需要用表連接的方式將多張表的欄位在一個查詢中展示，
表連接的方式從大類上來說可以分為內連接和外連接，
6.1 內連接
內連接是查詢2張表同時存在的記錄，即兩張表的交集，
例如：
/** 同時查詢兩張表中class_no 相同的student及class表的所有欄位內容 **/ mysql> select * from student a,class b -> where a.class_no=b.class_no; +----+----------+------+----------+----------+--------------+------------------+ | id | stu_name | age | class_no | class_no | class_name | location | +----+----------+------+----------+----------+--------------+------------------+ | 1 | 李白 | 25 | 201901 | 201901 | 2019級01班 | 博學北樓A401 | | 2 | 蘇軾 | 20 | 201901 | 201901 | 2019級01班 | 博學北樓A401 | | 3 | 王維 | 21 | 201902 | 201902 | 2019級02班 | 博學北樓B401 | | 5 | 陳六 | 22 | 201902 | 201902 | 2019級02班 | 博學北樓B401 | | 7 | 杜甫 | 24 | 201902 | 201902 | 2019級02班 | 博學北樓B401 | | 8 | 岳飛 | 23 | 201902 | 201902 | 2019級02班 | 博學北樓B401 | +----+----------+------+----------+----------+--------------+------------------+ 6 rows in set (0.00 sec)
注：
a) 例子中是列舉出所有欄位，所有可以用* ，當需要列出指定欄位時，可以列出指定欄位名展示，通過表名.欄位名的方式列出
b) 內連接的寫法可以向上述例子中那樣，也可以用inner join ... on...這種方式來寫，其中inner可以省略，例如：
mysql> select a.stu_name,b.class_name from student a inner join class b on a.class_no=b.class_no; +----------+--------------+ | stu_name | class_name | +----------+--------------+ | 李白 | 2019級01班 | | 蘇軾 | 2019級01班 | | 王維 | 2019級02班 | | 陳六 | 2019級02班 | | 杜甫 | 2019級02班 | | 岳飛 | 2019級02班 | +----------+--------------+ 6 rows in set (0.00 sec)
c） in可以用內連接的方式來改寫，尤其是多層子查詢時，這也是SQL優化中給的一種方案，例如之前in例子就可以改寫為：
mysql> select distinct a.* from student a inner join class b on a.class_no=b.class_no; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 1 | 李白 | 25 | 201901 | | 2 | 蘇軾 | 20 | 201901 | | 3 | 王維 | 21 | 201902 | | 5 | 陳六 | 22 | 201902 | | 7 | 杜甫 | 24 | 201902 | | 8 | 岳飛 | 23 | 201902 | +----+----------+------+----------+ 6 rows in set (0.00 sec)
6.2 外連接
外連接分為左連接和右連接，其中：
a) 左連接是指包含左邊表中的記錄，即使左表中含有和右表匹配不上的記錄也會保留，
b) 右連接是指包含右邊表中的記錄，即使右表中含有和左表匹配不上的記錄也會保留，
例如：
/** 左連接 **/ mysql> select a.stu_name,b.class_name from student a left join class b on a.class_no=b.class_no; +----------+--------------+ | stu_name | class_name | +----------+--------------+ | 李白 | 2019級01班 | | 蘇軾 | 2019級01班 | | 王維 | 2019級02班 | | 陳六 | 2019級02班 | | 杜甫 | 2019級02班 | | 岳飛 | 2019級02班 | | 喬峰 | NULL | /** 改記錄的class_no不存在與右表中 **/ +----------+--------------+ 7 rows in set (0.00 sec) /** 右連接 **/ mysql> select a.stu_name,b.class_name from student a right join class b on a.class_no=b.class_no; +----------+--------------+ | stu_name | class_name | +----------+--------------+ | 李白 | 2019級01班 | | 蘇軾 | 2019級01班 | | 王維 | 2019級02班 | | 陳六 | 2019級02班 | | 杜甫 | 2019級02班 | | 岳飛 | 2019級02班 | +----------+--------------+
注： 也可以使用外連接來改寫not in ,例如之前not in的例子可以按照如下方式改寫：
mysql> select distinct a.* from student a left join class b on a.class_no=b.class_no where b.class_no is null; +----+----------+------+----------+ | id | stu_name | age | class_no | +----+----------+------+----------+ | 4 | 喬峰 | 30 | 201801 | +----+----------+------+----------+ 1 row in set (0.00 sec)
7. 記錄聯合
記錄聯合是指將多個查詢結果合并到一起展示，需要用到UNION 、UNION ALL 關鍵字，其中UNION ALL不對多個查詢的結果去重，全部展示出來(即使查詢結果完全相同)，union 會對結果中的重復記錄進行去重后展示，
例如：
/** union all **/ mysql> select a.stu_name,b.class_name from student a left join class b on a.class_no=b.class_no -> union all -> select a.stu_name,b.class_name from student a right join class b on a.class_no=b.class_no; +----------+--------------+ | stu_name | class_name | +----------+--------------+ | 李白 | 2019級01班 | | 蘇軾 | 2019級01班 | | 王維 | 2019級02班 | | 陳六 | 2019級02班 | | 杜甫 | 2019級02班 | | 岳飛 | 2019級02班 | | 喬峰 | NULL | | 李白 | 2019級01班 | | 蘇軾 | 2019級01班 | | 王維 | 2019級02班 | | 陳六 | 2019級02班 | | 杜甫 | 2019級02班 | | 岳飛 | 2019級02班 | +----------+--------------+ 13 rows in set (0.00 sec) /** union **/ mysql> select a.stu_name,b.class_name from student a left join class b on a.class_no=b.class_no -> union -> select a.stu_name,b.class_name from student a right join class b on a.class_no=b.class_no; +----------+--------------+ | stu_name | class_name | +----------+--------------+ | 李白 | 2019級01班 | | 蘇軾 | 2019級01班 | | 王維 | 2019級02班 | | 陳六 | 2019級02班 | | 杜甫 | 2019級02班 | | 岳飛 | 2019級02班 | | 喬峰 | NULL | +----------+--------------+ 7 rows in set (0.00 sec)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101347.html
標籤：MySQL