【必知必會的MySQL知識】⑤DQL語言-有解無憂

一、前言
二、基礎查詢
- 2.1 語法
- 2.2 實踐操作
三、條件查詢
- 3.1 語法
- 3.2 where 陳述句運算子
- 3.3 實踐操作
四、排序查詢
- 4.1 語法格式
- 4.2 實踐操作
五、分組查詢
- 5.1 語法
- 5.2 聚集函式
  - 5.2.1 聚集函式簡單使用
- 5.3 實踐操作
- 5.4 規定與小結
六、連接查詢（多表查詢）
- 6.1 簡介
- 6.2 笛卡兒積
- 6.3 連接分類
- 6.4 語法格式
  - 6.4.1 內連接之等值連接sql92標準
  - 6.4.2 內連接之等值連接sql99標準
  - 6.4.3 內連接之非等值連接
  - 6.4.3 內連接之自連接
  - 6.4.4 外連接之右連接
  - 6.4.5 外連接之左連接
- 6.5 實踐操作
七、子查詢
- 7.1 簡介
- 7.2 出現的位置
- 7.3 實踐操作
八、組合查詢
- 8.1 簡介
- 8.2 語法
- 8.3 特點
- 8.4 實踐操作
九、分頁查詢
- 9.1 簡介
- 9.2 語法
  - 9.2.1 limit 語法
  - 9.2.2 分頁查詢語法
- 9.3 實踐操作

一、前言

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/551676.html

標籤：其他

上一篇：SQL Server判斷資料庫、表、列、視圖、存盤程序、函式是否存在

下一篇：返回列表