技術文章 » 【必知必會的MySQL知識】⑤DQL語言

【必知必會的MySQL知識】⑤DQL語言

2023-05-04 12:01:29

目錄

一、前言

上一節中我們說了DML 資料操作語言，這一篇到了DQL語言，DQL語言就是我們常說的select 語句。
它是從一個表或多個表中根據各種條件，檢索出我們想要的資料集。
DQL語句算是我們工作中最長用也是最複雜的SQL語句了。

二、基礎查詢

2.1 語法

-- ① 查詢欄位
select 欄位1 as 別名1，欄位2 as 別名2,欄位3 as 別名3 ...欄位 n 別名n from 表名; -- 當然了欄位也是有限的，as 別名也是非必須的
-- ② 查詢表示式
select 表示式; --
-- ③ 查詢函數
select 函數名(參數列);
-- ④ 查詢常數
select 常數值;  --字元型和日期型的常數值必須用單引號引起來，數值型不需要

2.2 實踐操作

新建user_profile資訊表，並插入5條資料

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大學','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'復旦大學','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大學','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大學','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山東大學','Shandong');

資料結構如下

id device_id gender age university province
1 2138 male 21 北京大學 BeiJing
2 3214 male None 復旦大學 Shanghai
3 6543 female 20 北京大學 BeiJing
4 2315 female 23 浙江大學 ZheJiang
5 5432 male 25 山東大學 Shandong

① 運營童鞋想要檢視使用者資訊表中所有的資料【查詢欄位】

mysql> SELECT id,device_id,gender,age,university,province FROM user_profile;
+----+-----------+--------+------+------------+----------+
| id | device_id | gender | age  | university | province |
+----+-----------+--------+------+------------+----------+
|  1 |      2138 | male   |   21 | 北京大學   | BeiJing  |
|  2 |      3214 | male   | NULL | 復旦大學   | Shanghai |
|  3 |      6543 | female |   20 | 北京大學   | BeiJing  |
|  4 |      2315 | female |   23 | 浙江大學   | ZheJiang |
|  5 |      5432 | male   |   25 | 山東大學   | Shandong |
+----+-----------+--------+------+------------+----------+
5 rows in set (0.01 sec)

② 運營童鞋想要使用者的裝置id對應的性別、年齡和學校的資料 【查詢欄位別名】

mysql> SELECT device_id AS 裝置id,gender AS 性別,age AS 年齡,university AS 學校 FROM user_profile;
+--------+--------+------+----------+
| 裝置id | 性別   | 年齡 | 學校     |
+--------+--------+------+----------+
|   2138 | male   |   21 | 北京大學 |
|   3214 | male   | NULL | 復旦大學 |
|   6543 | female |   20 | 北京大學 |
|   2315 | female |   23 | 浙江大學 |
|   5432 | male   |   25 | 山東大學 |
+--------+--------+------+----------+
5 rows in set (0.00 sec)

③ 運營童鞋想要查詢 2568*234/23+234 等於多少？【計算表示式】

mysql> SELECT 2568*234/23+234 as result;
+------------+
| result     |
+------------+
| 26360.6087 |
+------------+
1 row in set (0.00 sec)

④ 運營童鞋想要查詢當前時間 【查詢函數】

mysql 函數有很多，這裡就不一一列舉了，後面寫一篇常用函數的使用

mysql> SELECT NOW() AS currdate;
+---------------------+
| currdate            |
+---------------------+
| 2022-09-15 23:42:29 |
+---------------------+
1 row in set (0.00 sec)

⑤ 運營童鞋想要構建一個張三同學 【查詢常數】

mysql> SELECT '張三' AS user_name, '男' AS sex, 18 AS age, 150 AS wight;
+-----------+-----+-----+-------+
| user_name | sex | age | wight |
+-----------+-----+-----+-------+
| 張三      | 男  |  18 |   150 |
+-----------+-----+-----+-------+
1 row in set (0.00 sec)

三、條件查詢

3.1 語法

SELECT 查詢列表 FROM 表名 WHERE 篩選條件;

3.2 where 語句操作符

  • 條件運運算元
操作符 操作符說明
= 等於
<> 不等於
!= 不等於
< 小於
<= 小於等於
>= 大於等於
  • 邏輯運運算元
操作符 操作符說明
and 連線多個條件，表示滿足所有過濾條件的行
or 連線多個條件，表示滿足任意一個條件的行
not 否定之後所跟的條件
  • 模糊運運算元
操作符 操作符說明
like % 萬用字元表示任何字元出現任意次數 ;_萬用字元表示匹配一個字元
between 取值1 and 取值2 表示在取值1範圍和取值2範圍之間查詢,取值1為範圍的起始值；取值2為範圍的終止值。通常是時間範圍也可以是數位範圍
not between 取值1 and 取值2 與上面相反，不在取值1和取值2範圍之間的
in 指定條件範圍，範圍內的每個條件都可以進行匹配。in 的取值全都括在括號中，每個值用逗號隔開
is null 表示某個欄位為null
is not null 表示某個欄位不為空

3.3 實踐操作

資料準備

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大學',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'復旦大學',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大學',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大學',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山東大學',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京師範大學',3.3);

資料結構如下

id device_id gender age university gpa
1 2138 male 21 北京大學 3.4
2 3214 male 復旦大學 4
3 6543 female 20 北京大學 3.2
4 2315 female 23 浙江大學 3.6
5 5432 male 25 山東大學 3.8
6 2131 male 28 北京師範大學 3.3

① 運營童鞋想要篩選出所有北京大學的學生進行使用者調研，請你從使用者資訊表中取出滿足條件的資料，結果返回裝置id和學校。

mysql> SELECT t.`device_id`,t.`university` FROM user_profile t WHERE t.`university` = '北京大學';
+-----------+------------+
| device_id | university |
+-----------+------------+
|      2138 | 北京大學   |
|      6543 | 北京大學   |
+-----------+------------+
2 rows in set (0.00 sec)

② 運營童鞋想要針對24歲以上的使用者開展分析，請你取出滿足條件的裝置ID、性別、年齡、學校。

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE age >24;
+-----------+--------+------+------------+
| device_id | gender | age  | university |
+-----------+--------+------+------------+
|      5432 | male   |   25 | 山東大學   |
+-----------+--------+------+------------+
1 row in set (0.00 sec)

③ 運營童鞋想要針對20歲及以上且23歲及以下的使用者開展分析，請你取出滿足條件的裝置ID、性別、年齡。

mysql> SELECT t.`device_id`,t.`gender`,t.`age` FROM user_profile t WHERE age BETWEEN 20 AND 23;
+-----------+--------+------+
| device_id | gender | age  |
+-----------+--------+------+
|      2138 | male   |   21 |
|      6543 | female |   20 |
|      2315 | female |   23 |
+-----------+--------+------+
3 rows in set (0.00 sec)

④ 運營童鞋想要檢視除復旦大學以外的所有使用者明細，請你取出相應資料

-- ① 第一種寫法 使用<>
mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`university` <> '復旦大學';
+-----------+--------+------+------------+
| device_id | gender | age  | university |
+-----------+--------+------+------------+
|      2138 | male   |   21 | 北京大學   |
|      6543 | female |   20 | 北京大學   |
|      2315 | female |   23 | 浙江大學   |
|      5432 | male   |   25 | 山東大學   |
+-----------+--------+------+------------+
4 rows in set (0.00 sec)
-- ② 第二種寫法 使用!=
mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`university` != '復旦大學';
+-----------+--------+------+------------+
| device_id | gender | age  | university |
+-----------+--------+------+------------+
|      2138 | male   |   21 | 北京大學   |
|      6543 | female |   20 | 北京大學   |
|      2315 | female |   23 | 浙江大學   |
|      5432 | male   |   25 | 山東大學   |
+-----------+--------+------+------------+
4 rows in set (0.00 sec)

⑤ 運營童鞋想要對使用者的年齡分佈開展分析，在分析時想要剔除沒有獲取到年齡的使用者，請你取出所有年齡值不為空的使用者的裝置ID，性別，年齡，學校的資訊。

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`age` IS NOT NULL;
+-----------+--------+------+------------+
| device_id | gender | age  | university |
+-----------+--------+------+------------+
|      2138 | male   |   21 | 北京大學   |
|      6543 | female |   20 | 北京大學   |
|      2315 | female |   23 | 浙江大學   |
|      5432 | male   |   25 | 山東大學   |
+-----------+--------+------+------------+
4 rows in set (0.00 sec)

⑥ 運營童鞋想要找到男性且GPA在3.5以上(不包括3.5)的使用者進行調研，請你取出相關資料。

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university`,t.gpa FROM user_profile t WHERE t.gpa>3.5;
+-----------+--------+------+------------+------+
| device_id | gender | age  | university | gpa  |
+-----------+--------+------+------------+------+
|      3214 | male   | NULL | 復旦大學   |    4 |
|      2315 | female |   23 | 浙江大學   |  3.6 |
|      5432 | male   |   25 | 山東大學   |  3.8 |
+-----------+--------+------+------------+------+
3 rows in set (0.00 sec)

⑦ 運營童鞋想要找到學校為北大或GPA在3.7以上(不包括3.7)的使用者進行調研，請你取出相關資料（使用OR實現）

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university`,t.gpa FROM user_profile t WHERE (t.`university` = '北京大 學' OR t.`gpa`>3.7);
+-----------+--------+------+------------+------+
| device_id | gender | age  | university | gpa  |
+-----------+--------+------+------------+------+
|      2138 | male   |   21 | 北京大學   |  3.4 |
|      3214 | male   | NULL | 復旦大學   |    4 |
|      6543 | female |   20 | 北京大學   |  3.2 |
|      5432 | male   |   25 | 山東大學   |  3.8 |
+-----------+--------+------+------------+------+
4 rows in set (0.00 sec)

⑧ 運營童鞋想要找到學校為北大、復旦和山大的同學進行調研，請你取出相關資料。

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university`,t.gpa FROM user_profile t WHERE t.`university` IN ('北京大學','復旦大學','山東大學');
+-----------+--------+------+------------+------+
| device_id | gender | age  | university | gpa  |
+-----------+--------+------+------------+------+
|      2138 | male   |   21 | 北京大學   |  3.4 |
|      3214 | male   | NULL | 復旦大學   |    4 |
|      6543 | female |   20 | 北京大學   |  3.2 |
|      5432 | male   |   25 | 山東大學   |  3.8 |
+-----------+--------+------+------------+------+
4 rows in set (0.00 sec)

⑨ 運營童鞋想要找到gpa在3.5以上(不包括3.5)的山東大學使用者 或 gpa在3.8以上(不包括3.8)的復旦大學同學進行使用者調研，請你取出相應資料

mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university`,t.gpa FROM user_profile t WHERE (t.gpa>3.5 AND t.`university` = '山東大學') OR (t.gpa>3.8 AND t.`university` = '復旦大學');
+-----------+--------+------+------------+------+
| device_id | gender | age  | university | gpa  |
+-----------+--------+------+------------+------+
|      3214 | male   | NULL | 復旦大學   |    4 |
|      5432 | male   |   25 | 山東大學   |  3.8 |
+-----------+--------+------+------------+------+
2 rows in set (0.00 sec)

⑩ 運營童鞋想檢視所有大學中帶有北京的使用者的資訊，請你取出相應資料。


mysql> SELECT t.`device_id`,t.`gender`,t.`age`,t.`university` FROM user_profile t WHERE t.`university` LIKE '%北京%';
+-----------+--------+------+--------------+
| device_id | gender | age  | university   |
+-----------+--------+------+--------------+
|      2138 | male   |   21 | 北京大學     |
|      6543 | female |   20 | 北京大學     |
|      2131 | male   |   28 | 北京師範大學 |
+-----------+--------+------+--------------+
3 rows in set (0.00 sec)

四、排序查詢

4.1 語法格式

  • asc代表升序，desc代表降序，如果不寫，預設是asc
  • 排序列表可以是單個欄位、多個欄位、別名、函數、表示式
  • order by的位置一般放在查詢語句的最後（除limit語句之外）
SELECT 
  查詢列表 
FROM
  表 
【WHERE 篩選條件】
ORDER BY 排序列表 【asc | desc】 ;

4.2 實踐操作

資料準備

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大學',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',23,'復旦大學',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大學',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大學',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山東大學',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京師範大學',3.3);
id device_id gender age university gpa
1 2138 male 21 北京大學 3.4
2 3214 male 23 復旦大學 4
3 6543 female 20 北京大學 3.2
4 2315 female 23 浙江大學 3.6
5 5432 male 25 山東大學 3.8
6 2131 male 28 北京師範大學 3.3

①運營童鞋想要取出使用者資訊表中的使用者年齡，請取出相應資料，並按照年齡升序排序。

mysql> SELECT t.`device_id`,t.`age`FROM user_profile t ORDER BY t.age ASC;
+-----------+------+
| device_id | age  |
+-----------+------+
|      6543 |   20 |
|      2138 |   21 |
|      3214 |   23 |
|      2315 |   23 |
|      5432 |   25 |
|      2131 |   28 |
+-----------+------+
6 rows in set (0.00 sec)

②運營童鞋想要取出使用者資訊表中的年齡和gpa資料，並先按照gpa升序排序，再按照年齡升序排序輸出，請取出相應資料。

mysql> SELECT device_id,gpa,age FROM user_profile ORDER BY gpa ASC,age ASC;
+-----------+------+------+
| device_id | gpa  | age  |
+-----------+------+------+
|      6543 |  3.2 |   20 |
|      2131 |  3.3 |   28 |
|      2138 |  3.4 |   21 |
|      2315 |  3.6 |   23 |
|      5432 |  3.8 |   25 |
|      3214 |    4 |   23 |
+-----------+------+------+
6 rows in set (0.00 sec)

③ 運營童鞋想要取出使用者資訊表中對應的資料，並先按照gpa、年齡降序排序輸出，請取出相應資料。

mysql> SELECT device_id,gpa,age FROM user_profile ORDER BY gpa DESC,age DESC;
+-----------+------+------+
| device_id | gpa  | age  |
+-----------+------+------+
|      3214 |    4 |   23 |
|      5432 |  3.8 |   25 |
|      2315 |  3.6 |   23 |
|      2138 |  3.4 |   21 |
|      2131 |  3.3 |   28 |
|      6543 |  3.2 |   20 |
+-----------+------+------+
6 rows in set (0.00 sec)

五、分組查詢

5.1 語法

SELECT 
  查詢列表 
FROM
  表 
【where 篩選條件】 
GROUP BY 分組的欄位 
【having 分組後的篩選】
【order BY 排序的欄位】 ;

5.2 聚集函數

執行在行組上，計算和返回單個值的函數

聚集函數 分組函數說明
sum() 返回某列值之和
avg() 返回某列平均值
max() 返回某列最大值
min() 返回某列最小值
count() 返回某列的函數
5.2.1 聚集函數簡單使用

*資料準備，新建一個產品資訊表product

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `productid` varchar(10) NOT NULL COMMENT '產品id',
  `productname` varchar(300) NOT NULL COMMENT '產品名稱',
  `saleprice` decimal(10,0) DEFAULT NULL COMMENT '零售價',
  `author` varchar(200) DEFAULT NULL COMMENT '作者',
  PRIMARY KEY (`id`)
);
INSERT INTO `product` VALUES  (    1,    '10001',    '公眾號XiezhrSpace【Oralce從入門到放棄】',    100,    'xiezhr001'  );
INSERT INTO `product` VALUES  (    2,    '10002',    '公眾號XiezhrSpace【Linux核心命令快速上手】',    300,    'xiezhr'  );
INSERT INTO `product` VALUES  (    3,    '10003',    '公眾號XiezhrSpace【你寫註釋她幫你寫程式碼】',    80,    'xiezhr'  );
INSERT INTO `product` VALUES  (    4,    '10004',    '公眾號XiezhrSpace【Java從入門到精通】',    150,    'xiezhr001'  );
INSERT INTO `product` VALUES  (    5,    '10005',    '公眾號XiezhrSpace【gitee不能用了】',    55,    'xiezhr'  );
INSERT INTO `product` VALUES  (    6,    '10006',    '公眾號XiezhrSpace【如何快速搭建個人部落格】',    120,    'xiezhr'  );
INSERT INTO `product` VALUES  (    7,    '10007',    '公眾號XiezhrSpace【MySQL從入門到入土】',    320,    'xiezhr'  );
INSERT INTO `product` VALUES  (    8,    '10008',    '公眾號XiezhrSpace【idea從入門到上癮】',    500,    'xiezhr'  ) ;
id productid productname saleprice author
1 10001 公眾號XiezhrSpace【Oralce從入門到放棄】 100 xiezhr001
2 10002 公眾號XiezhrSpace【Linux核心命令快速上手】 300 xiezhr
3 10003 公眾號XiezhrSpace【你寫註釋她幫你寫程式碼】 80 xiezhr
4 10004 公眾號XiezhrSpace【Java從入門到精通】 150 xiezhr001
5 10005 公眾號XiezhrSpace【gitee不能用了】 55 xiezhr
6 10006 公眾號XiezhrSpace【如何快速搭建個人部落格】 120 xiezhr
7 10007 公眾號XiezhrSpace【MySQL從入門到入土】 320 xiezhr
8 10008 公眾號XiezhrSpace【idea從入門到上癮】 500 xiezhr 
-- 1、計算所有產品單價之和
mysql> select sum(saleprice) from product;
+----------------+
| sum(saleprice) |
+----------------+
|           1625 |
+----------------+
1 row in set (0.00 sec)

-- 2、計算所有產品單價平均值
mysql> select avg(saleprice) from product;
+----------------+
| avg(saleprice) |
+----------------+
|       203.1250 |
+----------------+
1 row in set (0.00 sec)
-- 3、獲取所有產品中最大單價
mysql> select max(saleprice) from product;
+----------------+
| max(saleprice) |
+----------------+
|            500 |
+----------------+
1 row in set (0.00 sec)

-- 4、獲取所有產品中最小單價
mysql> select min(saleprice) from product;
+----------------+
| min(saleprice) |
+----------------+
|             55 |
+----------------+
1 row in set (0.00 sec)
-- 5、獲取一共有多少產品
mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)
-- 或者
mysql> select count(1) from product;
+----------+
| count(1) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)

5.3 實踐操作

資料準備

drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` float,
`answer_cnt` float
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大學',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'復旦大學',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大學',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大學',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山東大學',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山東大學',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'復旦大學',3.6,9,6,52);
id device_id gender age university gpa active_days_within_30 question_cnt answer_cnt
1 2138 male 21 北京大學 3.4 7 2 12
2 3214 male 復旦大學 4.0 15 5 25
3 6543 female 20 北京大學 3.2 12 3 30
4 2315 female 23 浙江大學 3.6 5 1 2
5 5432 male 25 山東大學 3.8 20 15 70
6 2131 male 28 山東大學 3.3 15 7 13
7 4321 female 26 復旦大學 3.6 9 6 52

第一行表示:id為1的使用者的常用資訊為使用的裝置id為2138，性別為男，年齡21歲，北京大學，gpa為3.4在過去的30天裡面活躍了7天，發帖數量為2，回答數量為12

①運營童鞋想檢視每個學校使用者的平均發貼和回帖情況，尋找低活躍度學校進行重點運營，請取出平均發貼數低於5的學校或平均回帖數小於20的學校

mysql> SELECT
    ->   t.university,
    ->   AVG(question_cnt) AS avg_question_cnt,
    ->   AVG(answer_cnt) AS avg_answer_cnt
    -> FROM
    ->   user_profile t
    -> GROUP BY t.university
    -> HAVING avg_question_cnt < 5
    ->   OR avg_answer_cnt < 20;
+------------+------------------+----------------+
| university | avg_question_cnt | avg_answer_cnt |
+------------+------------------+----------------+
| 北京大學   |              2.5 |             21 |
| 浙江大學   |                1 |              2 |
+------------+------------------+----------------+
2 rows in set (0.00 sec)

--說明: 平均發貼數低於5的學校或平均回帖數小於20的學校有2個
--屬於北京大學的使用者的平均發帖量為2.500，平均回答數量為21.000
--屬於浙江大學的使用者的平均發帖量為1.000，平均回答數量為2.000

② 運營童鞋想要檢視不同大學的使用者平均發帖情況，並期望結果按照平均發帖情況進行升序排列，請你取出相應資料

mysql> SELECT
    ->   t.university,
    ->   AVG(question_cnt) AS avg_question_cnt
    -> FROM
    ->   user_profile t
    -> GROUP BY
    ->   t.university
    -> ORDER BY
    ->   avg_question_cnt;
+------------+------------------+
| university | avg_question_cnt |
+------------+------------------+
| 浙江大學   |                1 |
| 北京大學   |              2.5 |
| 復旦大學   |              5.5 |
| 山東大學   |               11 |
+------------+------------------+
4 rows in set (0.00 sec)

③ 運營童鞋想要對每個學校不同性別的使用者活躍情況和發帖數量進行分析，請分別計算出每個學校每種性別的使用者數、30天內平均活躍天數和平均發帖數量

mysql> SELECT
    ->   gender,
    ->   university,
    ->   COUNT(1) AS user_num,
    ->   AVG(active_days_within_30) AS avg_active_day,
    ->   AVG(question_cnt) avg_question_cnt
    -> FROM
    ->   user_profile
    -> GROUP BY gender,
    ->   university;
+--------+------------+----------+----------------+------------------+
| gender | university | user_num | avg_active_day | avg_question_cnt |
+--------+------------+----------+----------------+------------------+
| female | 北京大學   |        1 |        12.0000 |                3 |
| female | 浙江大學   |        1 |         5.0000 |                1 |
| male   | 北京大學   |        1 |         7.0000 |                2 |
| male   | 復旦大學   |        2 |        12.0000 |              5.5 |
| male   | 山東大學   |        2 |        17.5000 |               11 |
+--------+------------+----------+----------------+------------------+
5 rows in set (0.00 sec)
--說明：
--第一行表示：北京大學的男性使用者個數為1，平均活躍天數為7天，平均發帖量為2
-- ...
-- 最後一行表示：山東大學的男性使用者個數為2，平均活躍天數為17.5天，平均發帖量為11

5.4 規定與小結

  • group by 子句可以包含任意數目的列
  • group by 子句中列出的每個列都必須是檢索列或者有效表示式（不能是聚集函數）；select 語句中使用了表示式，group by 子句中也必須指定相同的表示式；不能使用別名。
  • 除聚集函數外，select 中的每一個列都必須在group by 子句中給出
  • 分組列中具有null值，則將null作為一個分組返回。如果列中有多個null值，將被分為一組
  • 各子句順序， select 子句 from 表名 where 子句 group by 子句 having 子句 order by 子句 limit 子句 使用時必須按照上面順序來
  • **where 和hiving的區別：where 在資料分組前過濾，hiving 在資料分組後過濾 **

六、連線查詢（多表查詢）

6.1 簡介

連線查詢又稱多表查詢，當查詢的欄位來自於多個表時，就會用到連線查詢

6.2 笛卡兒積

表A有m行，表B有n行，結果=m*n行
產生原因：沒有有效的連線條件
避免方法：新增有效連線條件

6.3 連線分類

  • 內連線 連線表之間沒有主次關係,條件匹配上的就顯示，匹配不上的就不顯示
    • 等值連線 連線表之間的連線條件為等值關係
    • 非等值連線 連線表之間的連線條件為等值關係
    • 自連線
  • 外聯結 連線表之間有主次關係，主表全部顯示
    • 左外連線 （左連線） join右邊的表為主表
    • 右外連線 （有連線） join 左邊的表為主表

6.4 語法格式

隨著mysql的升級，語法分為sql92標準、sql99標準

6.4.1 內連線之等值連線sql92標準

-寫法簡單，但是結構不清晰，表的連線條件和後期篩選條件都放到where子句中

select 查詢列表
from 表1 t1,表2 t2
where t1 和 t2 的連線條件
6.4.2 內連線之等值連線sql99標準

-表連線的條件時獨立的，連線之後，如果還需要進一步篩選，再往後加where 條件即可
內連線中inner 關鍵字可以省去

select 查詢列表
from 表1 t1
inner join                  
表2 t2
on t1 和 t2 的等值連線條件
where 篩選條件
6.4.3 內連線之非等值連線
select 查詢列表
from 表1 t1
inner join                  
表2 t2
on t1 和 t2 的非等值連線條件
where 篩選條件
6.4.3 內連線之自連線

一張表看作兩張表

select 查詢列表
from 表1 t1
inner join                  
表1 t2
on t1 和 t2 的關聯條件
where 篩選條件
6.4.4 外連線之右連線

join 右邊的表「表2」作為主表，根據條件將表2中資料全部查出來

select 查詢列表
from 表1 t1
right outer join             -- outer 可以省去                
表2 t2
on t1 和 t2 的非等值連線條件
where 篩選條件
6.4.5 外連線之左連線
select 查詢列表
from 表1 t1
left outer  join           -- outer 可以省去        
表2 t2
on t1 和 t2 的非等值連線條件
where 篩選條件

6.5 實踐操作

資料準備，一共三張表。部門表dept 、員工資訊表emp 、工資等級表SALGRADE

-- 部門表
CREATE TABLE DEPT(
    DEPTNO INT PRIMARY KEY,  -- 部門編號
    DNAME VARCHAR(14) ,  -- 部門名稱
    LOC VARCHAR(13) -- 部門地址
    ) ; 

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON 
-- 員工資訊表
CREATE TABLE EMP
    (
    EMPNO INT  PRIMARY KEY,  -- 員工編號
    ENAME VARCHAR(10),  -- 員工名稱
    JOB VARCHAR(9), -- 工作
    MGR DOUBLE, -- 直屬領導編號
    HIREDATE DATE,  -- 入職時間
    SAL DOUBLE, -- 工資
    COMM DOUBLE, -- 獎金
    DEPTNO INT, -- 部門號
    FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));

  INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980/12/17 800 null 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600 300 30
7521 WARD SALESMAN 7698 1981/2/22 1250 500 30
7566 JONES MANAGER 7839 1981/4/2 2975 null 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250 1400 30
7698 BLAKE MANAGER 7839 1981/5/1 2850 null 30
7782 CLARK MANAGER 7839 1981/6/9 2450 null 10
7788 SCOTT ANALYST 7566 1987/7/13 3000 null 20
7839 KING PRESIDENT null 1981/11/17 5000 null 10
7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30
7876 ADAMS CLERK 7788 1987/7/13 1100 null 20
7900 JAMES CLERK 7698 1981/12/3 950 null 30
7902 FORD ANALYST 7566 1981/12/3 3000 null 20
7934 MILLER CLERK 7782 1982/1/23 1300 null 10 
CREATE TABLE SALGRADE
      ( GRADE INT,  -- 工資等級
    LOSAL DOUBLE, -- 最低工資
    HISAL DOUBLE ); -- 最高工資
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

①運營童鞋想要查詢SMITH 員工所在部門

--1.sql92標準語法
mysql> SELECT
    ->   e.ename,
    ->   e.deptno,
    ->   d.dname
    -> FROM
    ->   emp e,
    ->   dept d
    -> WHERE e.deptno = d.deptno
    -> AND e.ename='SMITH';
+-------+--------+----------+
| ename | deptno | dname    |
+-------+--------+----------+
| SMITH |     20 | RESEARCH |
+-------+--------+----------+
1 row in set (0.00 sec)

--2.sql99標準語法
mysql> SELECT
    ->   e.ename,
    ->   e.deptno,
    ->   d.dname
    -> FROM
    ->   emp e
    ->   JOIN
    ->   dept d
    -> ON e.deptno = d.deptno
    -> WHERE e.ename='SMITH';
+-------+--------+----------+
| ename | deptno | dname    |
+-------+--------+----------+
| SMITH |     20 | RESEARCH |
+-------+--------+----------+
1 row in set (0.00 sec)

注 以上例子中e.deptno = d.deptno 為等值關聯，所以上面例子時等值關聯查詢

② 運營童鞋想要檢視每個員工的薪資等級，要求顯示員工名、薪資、薪資等級

mysql> SELECT
    ->   e.ename,
    ->   e.sal,
    ->   s.grade
    -> FROM
    ->   emp e
    -> JOIN
    ->   salgrade s
    -> ON e.sal BETWEEN s.losal AND s.hisal;
+--------+------+-------+
| ename  | sal  | grade |
+--------+------+-------+
| SMITH  |  800 |     1 |
| ALLEN  | 1600 |     3 |
| WARD   | 1250 |     2 |
| JONES  | 2975 |     4 |
| MARTIN | 1250 |     2 |
| BLAKE  | 2850 |     4 |
| CLARK  | 2450 |     4 |
| SCOTT  | 3000 |     4 |
| KING   | 5000 |     5 |
| TURNER | 1500 |     3 |
| ADAMS  | 1100 |     1 |
| JAMES  |  950 |     1 |
| FORD   | 3000 |     4 |
| MILLER | 1300 |     2 |
+--------+------+-------+
14 rows in set (0.00 sec)

注 以上例子中e.sal BETWEEN s.losal AND s.hisal 為非等值關聯，所以上面例子時非等值關聯查詢

③ 查詢員工SMITH 和員工SCOTT 上級領導

mysql> SELECT
    ->   e1.ename AS '員工名',
    ->   e2.ename AS '領導名'
    -> FROM
    ->   emp e1
    -> JOIN
    ->   emp e2
    -> ON e1.mgr=e2.empno
    -> WHERE e1.ename IN('SMITH','SCOTT');
+--------+--------+
| 員工名 | 領導名 |
+--------+--------+
| SMITH  | FORD   |
| SCOTT  | JONES  |
+--------+--------+
2 rows in set (0.01 sec)

④運營童鞋想要檢視所有部門的員工資訊，如果新設立的部門沒有員工也要將其顯示出來

任何左連線可以實現的右連線也可以實現
1、通過左連線實現

-- 部門編號為40的OPERATIONS 部門沒有員工也要顯示出來
mysql> SELECT d.deptno,d.dname,e.ename
    -> FROM dept d
    -> LEFT JOIN
    -> emp e
    -> ON d.deptno = e.deptno;
+--------+------------+--------+
| deptno | dname      | ename  |
+--------+------------+--------+
|     10 | ACCOUNTING | CLARK  |
|     10 | ACCOUNTING | KING   |
|     10 | ACCOUNTING | MILLER |
|     20 | RESEARCH   | SMITH  |
|     20 | RESEARCH   | JONES  |
|     20 | RESEARCH   | SCOTT  |
|     20 | RESEARCH   | ADAMS  |
|     20 | RESEARCH   | FORD   |
|     30 | SALES      | ALLEN  |
|     30 | SALES      | WARD   |
|     30 | SALES      | MARTIN |
|     30 | SALES      | BLAKE  |
|     30 | SALES      | TURNER |
|     30 | SALES      | JAMES  |
|     40 | OPERATIONS | NULL   |
+--------+------------+--------+
15 rows in set (0.00 sec)

2、通過右連線實現

mysql> SELECT d.deptno,d.dname,e.ename
    -> FROM emp e
    -> RIGHT JOIN
    -> dept d
    -> ON d.deptno = e.deptno;
+--------+------------+--------+
| deptno | dname      | ename  |
+--------+------------+--------+
|     10 | ACCOUNTING | CLARK  |
|     10 | ACCOUNTING | KING   |
|     10 | ACCOUNTING | MILLER |
|     20 | RESEARCH   | SMITH  |
|     20 | RESEARCH   | JONES  |
|     20 | RESEARCH   | SCOTT  |
|     20 | RESEARCH   | ADAMS  |
|     20 | RESEARCH   | FORD   |
|     30 | SALES      | ALLEN  |
|     30 | SALES      | WARD   |
|     30 | SALES      | MARTIN |
|     30 | SALES      | BLAKE  |
|     30 | SALES      | TURNER |
|     30 | SALES      | JAMES  |
|     40 | OPERATIONS | NULL   |
+--------+------------+--------+
15 rows in set (0.00 sec)

⑤ 運營童鞋想要檢視員工SMITH 的部門薪資等級
想要完成運營童鞋的需求，需要關聯三張表，但也不是什麼難事

mysql> SELECT e.ename,e.sal, d.dname,s.grade
    -> FROM emp e
    -> JOIN dept d
    -> ON e.deptno = d.deptno
    -> JOIN salgrade s
    -> ON e.sal BETWEEN s.losal AND s.hisal
    -> WHERE e.ename = 'SMITH';
+-------+------+----------+-------+
| ename | sal  | dname    | grade |
+-------+------+----------+-------+
| SMITH |  800 | RESEARCH |     1 |
+-------+------+----------+-------+
1 row in set (0.00 sec)

七、子查詢

7.1 簡介

select語句中巢狀select語句，被巢狀的select語句稱為子查詢

7.2 出現的位置

  • select 後面
  • from後面 將子查詢當作一張臨時表
  • where 或having後面 將子查詢當作一個條件
  • exists 後面

7.3 實踐操作

資料準備，一共三張表。部門表dept 、員工資訊表emp 、工資等級表SALGRADE

-- 部門表
CREATE TABLE DEPT(
    DEPTNO INT PRIMARY KEY,  -- 部門編號
    DNAME VARCHAR(14) ,  -- 部門名稱
    LOC VARCHAR(13) -- 部門地址
    ) ; 

INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON 
-- 員工資訊表
CREATE TABLE EMP
    (
    EMPNO INT  PRIMARY KEY,  -- 員工編號
    ENAME VARCHAR(10),  -- 員工名稱
    JOB VARCHAR(9), -- 工作
    MGR DOUBLE, -- 直屬領導編號
    HIREDATE DATE,  -- 入職時間
    SAL DOUBLE, -- 工資
    COMM DOUBLE, -- 獎金
    DEPTNO INT, -- 部門號
    FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));

SELECT * FROM emp;

  INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980/12/17 800 null 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600 300 30
7521 WARD SALESMAN 7698 1981/2/22 1250 500 30
7566 JONES MANAGER 7839 1981/4/2 2975 null 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250 1400 30
7698 BLAKE MANAGER 7839 1981/5/1 2850 null 30
7782 CLARK MANAGER 7839 1981/6/9 2450 null 10
7788 SCOTT ANALYST 7566 1987/7/13 3000 null 20
7839 KING PRESIDENT null 1981/11/17 5000 null 10
7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30
7876 ADAMS CLERK 7788 1987/7/13 1100 null 20
7900 JAMES CLERK 7698 1981/12/3 950 null 30
7902 FORD ANALYST 7566 1981/12/3 3000 null 20
7934 MILLER CLERK 7782 1982/1/23 1300 null 10 
CREATE TABLE SALGRADE
      ( GRADE INT,  -- 工資等級
    LOSAL DOUBLE, -- 最低工資
    HISAL DOUBLE ); -- 最高工資
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

① 運營童鞋想要查詢每個員工的部門名稱 (select 後面)

mysql> SELECT e.ename,(SELECT dname FROM dept d WHERE d.deptno = e.deptno) AS dpatname FROM emp e;
+--------+------------+
| ename  | dpatname   |
+--------+------------+
| SMITH  | RESEARCH   |
| ALLEN  | SALES      |
| WARD   | SALES      |
| JONES  | RESEARCH   |
| MARTIN | SALES      |
| BLAKE  | SALES      |
| CLARK  | ACCOUNTING |
| SCOTT  | RESEARCH   |
| KING   | ACCOUNTING |
| TURNER | SALES      |
| ADAMS  | RESEARCH   |
| JAMES  | SALES      |
| FORD   | RESEARCH   |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

② 運營童鞋想要查詢每個工作崗位的平均工資及等級 (from 後面)

mysql> SELECT
    ->   t.job,
    ->   t.avgsal,
    ->   s.grade
    -> FROM
    ->   (SELECT
    ->     e.job,
    ->     AVG(e.sal) AS avgsal
    ->   FROM
    ->     emp e
    ->   GROUP BY e.job) t
    ->   JOIN salgrade s
    ->     ON t.avgsal BETWEEN s.losal
    ->     AND s.hisal ;
+-----------+--------------------+-------+
| job       | avgsal             | grade |
+-----------+--------------------+-------+
| ANALYST   |               3000 |     4 |
| CLERK     |             1037.5 |     1 |
| MANAGER   | 2758.3333333333335 |     4 |
| PRESIDENT |               5000 |     5 |
| SALESMAN  |               1400 |     2 |
+-----------+--------------------+-------+
5 rows in set (0.00 sec)

③運營童鞋想要檢視比最低工資高的員工和姓名(where 後面)


mysql>    SELECT e.ename,e.sal FROM emp e WHERE sal >(SELECT MIN(sal) FROM emp );
+--------+------+
| ename  | sal  |
+--------+------+
| ALLEN  | 1600 |
| WARD   | 1250 |
| JONES  | 2975 |
| MARTIN | 1250 |
| BLAKE  | 2850 |
| CLARK  | 2450 |
| SCOTT  | 3000 |
| KING   | 5000 |
| TURNER | 1500 |
| ADAMS  | 1100 |
| JAMES  |  950 |
| FORD   | 3000 |
| MILLER | 1300 |
+--------+------+
13 rows in set (0.00 sec)

④運營童鞋想要查詢有員工的部門名或沒有員工的部門名(exists 後面)

-- 1.查詢有員工的部門
mysql>    SELECT d.deptno,d.dname FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE d.deptno = e.deptno);
+--------+------------+
| deptno | dname      |
+--------+------------+
|     10 | ACCOUNTING |
|     20 | RESEARCH   |
|     30 | SALES      |
+--------+------------+
3 rows in set (0.00 sec)
-- 2.查詢沒有員工的部門
mysql> SELECT d.deptno,d.dname FROM dept d WHERE NOT EXISTS (SELECT 1 FROM emp e WHERE d.deptno = e.deptno);
+--------+------------+
| deptno | dname      |
+--------+------------+
|     40 | OPERATIONS |
+--------+------------+
1 row in set (0.00 sec)

八、組合查詢

8.1 簡介

將查詢結果集合併成新的結果集

8.2 語法

union關鍵字預設去重，如果使用union all可以包含重複項

查詢語句1
union 【all】
查詢語句2
union 【all】
...

8.3 特點

  • 要查詢的結果來自於多個表且多個表沒有直接的連線關係，但查詢的資訊一致時，可以使用聯合查詢
  • 要求多條查詢語句的查詢列數是一致的
  • 要求多條查詢語句的查詢的每一列的型別和順序一致

8.4 實踐操作

如下所示，準備一張學生資訊表、一張教師資訊表

DROP TABLE IF EXISTS student;
CREATE TABLE student(
	stuNo	VARCHAR(5) PRIMARY KEY, -- 學生學號
	stuName VARCHAR(32) NOT NULL, -- 學生姓名
	gender VARCHAR(1) NOT NULL	DEFAULT '男', -- 學生性別
	age INT NOT NULL, -- 學生年齡
	school VARCHAR(100) --所屬學校
);

INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('001','李志','男',22,'北京大學');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('002','宋東野','男',23,'天津大學');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('003','趙雷','男',34,'山東大學');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('004','馬頔','男',32,'北京大學');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('005','陳粒','女',18,'山東大學');
INSERT INTO student(stuNo,stuName,gender,age,school) VALUES('006','筠子','女',23,'廈門大學');
stuNo stuName gender age school
1 李志 男 22 北京大學
2 宋東野 男 23 天津大學
3 趙雷 男 34 山東大學
4 馬頔 男 32 北京大學
5 陳粒 女 18 山東大學
6 筠子 女 23 廈門大學 

DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher(
	tNo 	VARCHAR(5) PRIMARY KEY, -- 教師編號
	tName  VARCHAR(32) NOT NULL, -- 教師姓名
	gender VARCHAR(1) NOT NULL	DEFAULT '男', -- 教師性別
	age INT NOT NULL, -- 教師年齡
	school VARCHAR(100)  -- 所屬學校
);

INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('001','李璇','女',35,'北京大學');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('002','張天宇','男',45,'廈門大學');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('003','劉曉','女',35,'天津大學');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('004','鐘鳴','男',32,'山東大學');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('005','宋小白','男',35,'雲南大學');
INSERT INTO teacher(tNo,tName,gender,age,school) VALUES('006','樑小如','女',35,'北京師範大學');
tNo tName gender age school
1 李璇 女 35 北京大學
2 張天宇 男 45 廈門大學
3 劉曉 女 35 天津大學
4 鐘鳴 男 32 山東大學
5 宋小白 男 35 雲南大學
6 樑小如 女 35 北京師範大學

① 運營童鞋想要查詢北京大學的所有老師和學生資訊

mysql> SELECT '學生' AS ptype, s.stuname,s.gender,s.age FROM student s WHERE s.school = '北京大學'
    -> UNION ALL
    -> SELECT '教師' AS ptype, t.tname,t.gender,t.age FROM teacher t WHERE t.school = '北京大學';
+-------+---------+--------+-----+
| ptype | stuname | gender | age |
+-------+---------+--------+-----+
| 學生  | 李志    | 男     |  22 |
| 學生  | 馬頔    | 男     |  32 |
| 教師  | 李璇    | 女     |  35 |
+-------+---------+--------+-----+
3 rows in set (0.01 sec)

② 運營童鞋想要檢視山東大學和北京大學的學生資訊

mysql> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school = '山東大學'
    -> UNION ALL
    -> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school = '北京大學';
+---------+--------+-----+----------+
| stuname | gender | age | school   |
+---------+--------+-----+----------+
| 趙雷    | 男     |  34 | 山東大學 |
| 陳粒    | 女     |  18 | 山東大學 |
| 李志    | 男     |  22 | 北京大學 |
| 馬頔    | 男     |  32 | 北京大學 |
+---------+--------+-----+----------+
4 rows in set (0.00 sec)
-- 或者可以通過以下寫法實現
mysql> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school IN ( '北京大學' ,'山東大學');
+---------+--------+-----+----------+
| stuname | gender | age | school   |
+---------+--------+-----+----------+
| 李志    | 男     |  22 | 北京大學 |
| 趙雷    | 男     |  34 | 山東大學 |
| 馬頔    | 男     |  32 | 北京大學 |
| 陳粒    | 女     |  18 | 山東大學 |
+---------+--------+-----+----------+
4 rows in set (0.00 sec)

-- 或者可以通過以下寫法實現
mysql> SELECT  s.stuname,s.gender,s.age,s.school FROM student s WHERE s.school = '北京大學' OR s.school = '山東大學';
+---------+--------+-----+----------+
| stuname | gender | age | school   |
+---------+--------+-----+----------+
| 李志    | 男     |  22 | 北京大學 |
| 趙雷    | 男     |  34 | 山東大學 |
| 馬頔    | 男     |  32 | 北京大學 |
| 陳粒    | 女     |  18 | 山東大學 |
+---------+--------+-----+----------+
4 rows in set (0.00 sec)

九、分頁查詢

9.1 簡介

假設一個公司有10000名員工，介面上需要展示員工資訊。這時候我們就需要使用分頁查詢，將員工資訊按n頁展示，每頁顯示m名員工資訊

9.2 語法

9.2.1 limit 語法
  • limit語句放在查詢語句的最後
- startindex 表示起始索引，size代表條目數
SELECT 
  查詢列表 
FROM
  表1 別名1
【連線型別】 JOIN 表2 別名2 ON 連線條件 
【WHERE 分組前的篩選】
【GROUP BY 分組欄位】 
【HAVING 分組後的篩選 】
【ORDER BY 排序欄位 ASC|DESC】
LIMIT [startindex] size ;
9.2.2 分頁查詢語法
  • 分頁查詢展示可以提高使用者體驗
-- page 表示第幾頁
-- size 表示每頁顯示多少條資料
select 查詢列表 from 表 limit (page-1)*size,size;

9.3 實踐操作

按以下指令碼準備一張員工表資訊

CREATE TABLE EMP
    (
    EMPNO INT  PRIMARY KEY,  -- 員工編號
    ENAME VARCHAR(10),  -- 員工名稱
    JOB VARCHAR(9), -- 工作
    MGR DOUBLE, -- 直屬領導編號
    HIREDATE DATE,  -- 入職時間
    SAL DOUBLE, -- 工資
    COMM DOUBLE, -- 獎金
    DEPTNO INT, -- 部門號
    FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));

  INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980/12/17 800 null 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600 300 30
7521 WARD SALESMAN 7698 1981/2/22 1250 500 30
7566 JONES MANAGER 7839 1981/4/2 2975 null 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250 1400 30
7698 BLAKE MANAGER 7839 1981/5/1 2850 null 30
7782 CLARK MANAGER 7839 1981/6/9 2450 null 10
7788 SCOTT ANALYST 7566 1987/7/13 3000 null 20
7839 KING PRESIDENT null 1981/11/17 5000 null 10
7844 TURNER SALESMAN 7698 1981/9/8 1500 0 30
7876 ADAMS CLERK 7788 1987/7/13 1100 null 20
7900 JAMES CLERK 7698 1981/12/3 950 null 30
7902 FORD ANALYST 7566 1981/12/3 3000 null 20
7934 MILLER CLERK 7782 1982/1/23 1300 null 10

① 運營童鞋想要檢視工資最高的5名員工

mysql> SELECT e.ename,e.sal FROM  emp e ORDER BY e.sal DESC LIMIT 5;
+-------+------+
| ename | sal  |
+-------+------+
| KING  | 5000 |
| FORD  | 3000 |
| SCOTT | 3000 |
| JONES | 2975 |
| BLAKE | 2850 |
+-------+------+
5 rows in set (0.00 sec)

② 運營童鞋想要檢視工資排再[3-5]名的員工


mysql> SELECT e.ename,e.sal FROM  emp e ORDER BY e.sal DESC LIMIT 2,3;
+-------+------+
| ename | sal  |
+-------+------+
| SCOTT | 3000 |
| JONES | 2975 |
| BLAKE | 2850 |
+-------+------+
3 rows in set (0.00 sec)

③ 運營童鞋想要分頁檢視員工資訊，一頁展示5條記錄

limit 後條件直接套用公式即可 limit (page-1)*size,size

-- 第一頁 (page-1)*size,size ==> (1-1)*5,5
mysql> SELECT
    ->   e.ename,
    ->   e.job,
    ->   e.sal,
    ->   d.dname
    -> FROM
    ->   emp e
    ->   LEFT JOIN dept d
    ->     ON e.deptno = d.deptno
    -> LIMIT 0, 5 ;
+--------+-----------+------+------------+
| ename  | job       | sal  | dname      |
+--------+-----------+------+------------+
| CLARK  | MANAGER   | 2450 | ACCOUNTING |
| KING   | PRESIDENT | 5000 | ACCOUNTING |
| MILLER | CLERK     | 1300 | ACCOUNTING |
| SMITH  | CLERK     |  800 | RESEARCH   |
| JONES  | MANAGER   | 2975 | RESEARCH   |
+--------+-----------+------+------------+
5 rows in set (0.00 sec)
-- 第二頁 (page-1)*size,size ==> (2-1)*5,5
mysql> SELECT
    ->   e.ename,
    ->   e.job,
    ->   e.sal,
    ->   d.dname
    -> FROM
    ->   emp e
    ->   LEFT JOIN dept d
    ->     ON e.deptno = d.deptno
    -> LIMIT 5, 5 ;
+-------+----------+------+----------+
| ename | job      | sal  | dname    |
+-------+----------+------+----------+
| SCOTT | ANALYST  | 3000 | RESEARCH |
| ADAMS | CLERK    | 1100 | RESEARCH |
| FORD  | ANALYST  | 3000 | RESEARCH |
| ALLEN | SALESMAN | 1600 | SALES    |
| WARD  | SALESMAN | 1250 | SALES    |
+-------+----------+------+----------+
5 rows in set (0.00 sec)
-- 第三頁 (page-1)*size,size ==> (3-1)*5,5
SELECT 
  e.ename,
  e.job,
  e.sal,
  d.dname 
FROM
  emp e 
  LEFT JOIN dept d 
    ON e.deptno = d.deptno 
LIMIT 10, 5 ;