MySQL DQL分組查詢、條件查詢、子查詢

1.分組函數

1.分組函數又稱統計函數、組函數、聚合函數。

2.功能：用作統計使用

3.分類：sum求和、avg平均值、max最大值。min最小值、count計數（非空）。

/*
分組函數：統計函數、組函數、聚合函數
sum 求和、avg 平均值、max 最大值、min 最小值、count 計數（非空）
列的值如果是null不會被統計，一般使用主鍵或者*，*所有列，遇到一個為空的列，重新找不為空的列進行統計
*/

-- sum求和
SELECT SUM(stu_grade) FROM t_student
-- avg平均值
SELECT AVG(stu_grade) FROM t_student
-- max最大值
SELECT MAX(stu_grade) FROM t_student
-- min最小值
SELECT MIN(stu_grade) FROM t_student
-- count計數 
SELECT COUNT(stu_sex) FROM t_student

           

2.分組查詢

1.分組查詢：會将相同内容分到同一組。

2.group by：根據某一列資料進行分組。

3.where後是在分組前對資料進行篩選過濾。

4.having後是對分組完成後的結果進行條件過濾。

-- 分組查詢 分組 會将相同内容分到同一組      例如使用性别分組
-- select 結果 from 表 group by 分組列
SELECT * FROM t_student GROUP BY stu_sex    -- 會報錯

-- 統計男生、女生各有多少人
SELECT stu_sex,COUNT(*) FROM t_student GROUP BY stu_sex

-- 添加查詢操作
SELECT stu_sex,COUNT(*) c
FROM t_student
WHERE stu_grade>0  -- 在分組前對資料進行篩選過濾
GROUP BY stu_sex
ORDER BY c DESC  -- 對分組後的結果進行排序

--  查詢性别人數大于2的是哪個性别    對分組後的結果進行條件篩選
SELECT stu_sex,COUNT(*) c
FROM t_student
GROUP BY stu_sex
HAVING c>2   -- 對分組後的結果進行條件過濾
           

3.條件查詢

1.使用where子句，将不滿足條件的行過濾掉，where子句緊随from子句。

文法：select <結果> from <表名> where <條件>

比較：=、!=或<>、<、>、<=、>=

邏輯運算：and 和、or 或、not 非

2.模糊查詢

like：是否比對于一個模式，一般和通配符搭配使用，可以判斷字元型數值或數值型。

通配符：%表示若幹個字元，包含0個字元；_表示任意單個字元。

3.between and：兩者之間，包含臨界值。

4.in：判斷某字段的值是否屬于in清單中的某一項。

5.is null（為空的）或is not null（不為空的）。

6.union和union all

一般union用的比union all多，union all是直接連接配接的，取得所有的值，記錄可能有重複，而union是取唯一值，記錄沒有重複。

效率：

UNION和UNION ALL關鍵字都是将兩個結果集合并為一個，但這兩者從使用和效率上來說都有所不同。

（1）、對重複結果的處理：UNION在進行表連結後會篩選掉重複的記錄，Union All不會去除重複記錄。

（2）、對排序的處理：Union将會按照字段的順序進行排序；UNION ALL隻是簡單的将兩個結果合并後就傳回。

從效率上說，UNION ALL 要比UNION快很多，是以，如果可以确認合并的兩個結果集中不包含重複資料且不需要排序時的話，那麼就使用UNION ALL。

7.排序 order by

查詢結果排序，使用order by子句排序：order 排序列 asc/desc，asc表示的是升序，desc表示的是降序，不寫預設為升序。

order by子句中可以支援單個字段、多個字段、表達式、函數、别名

8.數量限制 limit

limit子句：對查詢結果限制數目，添加在sql語句末端

有三種方式：

（1）select 查詢結果 from table limit 5;

限制隻顯示5行

（2）select 查詢結果 from table limit 0,3;

0表示起始位置，3表示顯示行數

（3）select 查詢結果 from table limit 3 offset 0;

0表示起始位置，3表示顯示行數。

-- select 結果 from 表名 where 資料條件過濾 group by    having    order by     limit

-- =在where子句後面表示  等于
SELECT * FROM t_student
SELECT * FROM t_student WHERE stu_sex='男'

-- 不等于 != 或者 <>
SELECT * FROM t_student WHERE stu_sex!='男'
SELECT * FROM t_student WHERE stu_sex<>'男'


-- and 多個條件并列，必須全部成立
SELECT * FROM t_student WHERE stu_grade<=100 AND stu_grade>=60

-- or 多個條件隻要滿足一個即可
SELECT * FROM t_student WHERE stu_grade>90 OR stu_sex='男'

-- 列名 between 區間開始 and 區間結束    包含60和100
SELECT * FROM t_student WHERE stu_grade BETWEEN 60 AND 100

-- 模糊查詢 like
SELECT * FROM t_student WHERE stu_name='Jim'

-- 查詢姓李得人 like '關鍵字%_'  %比對若幹個（包括0個）字元，_比對單個字元
SELECT * FROM t_student WHERE stu_name LIKE '李_'
SELECT * FROM t_student WHERE stu_name LIKE '李%'

-- in   查詢成績為99和89的學生
SELECT * FROM t_student WHERE stu_grade=99 OR stu_grade=89
-- in(值1,值2,.....)
SELECT * FROM t_student WHERE stu_grade IN(99,89) 

-- not 不在，不是   查詢成績不為99，89的
SELECT * FROM t_student WHERE stu_grade NOT IN(99,89) 

-- is null為空   is not null 不為空
SELECT * FROM t_student WHERE stu_birthday IS NULL
SELECT * FROM t_student WHERE stu_address IS NOT NULL

/*
union 将多個查詢結果合并，要求查詢出來的列數形同
sql1
union
sql2

union 去除兩個查詢結果中重複資料，排序
union all 隻是單一合并，不排序
*/
SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='男'
UNION
SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='女'

SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='男'
UNION
SELECT stu_num,stu_name,stu_sex FROM t_student WHERE stu_sex='女'

SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='男'
UNION
SELECT stu_num,stu_name,stu_grade FROM t_student WHERE stu_sex='男'

-- order by  排序  預設使用升序排序  ASC 升序   DESC降序
-- 預設使用主鍵升序排序
SELECT * FROM t_student
SELECT * FROM t_student ORDER BY stu_grade DESC

--   order by 指定排序列 排序規則
-- 在條件後面添加排序


-- 行數限制  limit
--  SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset;
--                       提取前三行資料
SELECT * FROM t_student LIMIT 3
-- 查詢成績前兩名同學
SELECT * FROM t_student ORDER BY stu_grade DESC LIMIT 2

--  limit  開始位置（從0開始）,數量
SELECT * FROM t_student LIMIT 0,2

--  limit 數量 offset 開始位置
SELECT * FROM t_student LIMIT 3 OFFSET 0

           

4.子查詢

1.含義：出現在其他語句中的select語句

2.其他語句：insert、update、delete、select

3.在select語句中隻有子查詢

（1）按照子查詢結果分為：

标量子查詢（一行一列）；

列子查詢（一列多行）；

行子查詢（一行多列）；

表子查詢（多行多列）。

（2）按子查詢出現的位置：

select後面：僅僅支援标量子查詢；

from後面：支援表子查詢；

where或者having後面：支援表子查詢、列子查詢、行子查詢；

exists後面：支援表子查詢。

-- 子查詢：出現在其他語句中的select語句
-- 其他語句：insert、update、delete、select

ALTER TABLE t_student MODIFY stu_num INT AUTO_INCREMENT
-- 在insert語句中使用子查詢
INSERT INTO t_student(stu_name,stu_sex) SELECT stu_name,stu_sex FROM t_student WHERE stu_num=104

-- 在update語句中使用子查詢，注意查詢結果不能在這張表中
UPDATE t_student SET stu_name='李五' WHERE stu_num=(SELECT stu_num FROM student WHERE stu_name='李四') 

-- 在delete語句中使用子查詢,注意查詢結果不能在這張表中
DELETE FROM t_student WHERE stu_num=(SELECT stu_num FROM student WHERE stu_name='李四')

/*
在select語句中使用子查詢
按功能、結果集的行列數不同：
标量子查詢（結果集隻有一行一列）
列子查詢（結果集隻有一列多行）
行子查詢（結果集有一行多列）
表子查詢（結果集一般為多行多列）
*/

-- 在select語句後加子查詢隻能是标量子查詢
SELECT (SELECT NOW()) FROM t_student

-- 在from後面，支援表子查詢  每一個派生表必須有自己的别名
-- 性别人數大于2的性别
SELECT stu_sex FROM (SELECT stu_sex,COUNT(*) c FROM t_student GROUP BY stu_sex) tabless WHERE c>2

-- 列子查詢（一列多行）
SELECT stu_name FROM t_student WHERE stu_grade IN(SELECT stu_grade FROM t_student WHERE stu_grade>80)

-- 行子查詢（一行多列）  查詢學号最小成績最高的學生       傳回的是一個一行多列
SELECT * FROM t_student WHERE (stu_num,stu_grade)=(SELECT MIN(stu_num),MAX(stu_grade) FROM t_student)

-- 查詢成績最高的學生資訊
SELECT * FROM t_student WHERE stu_grade=(SELECT MAX(stu_grade) FROM t_student)