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

(DQL語言) 一、前言上一節中我們說了DML 數據操作語言，這一篇到了DQL語言，DQL語言就是我們常說的select 語句。它是從一個表或多個表中根據各種條件，檢索出我們想要的數據集。 DQL語句算是我們工作中最長用也是最複雜的SQL語句了。二、基礎查詢 2.1 語法 -- ① 查詢欄位 ...

一、前言
二、基礎查詢
- 2.1 語法
- 2.2 實踐操作
三、條件查詢
四、排序查詢
- 4.1 語法格式
- 4.2 實踐操作
五、分組查詢
六、連接查詢（多表查詢）
七、子查詢
八、組合查詢
九、分頁查詢

一、前言

上一節中我們說了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 INT