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

xiezhr發表於2023-05-04

一、前言

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

【必知必會的MySQL知識】④DCL語言
2023-05-03
MySql
【必知必會的MySQL知識】③DML語言
2023-05-02
MySql
【必知必會的MySQL知識】①初探MySQL
2023-05-01
MySql
【必知必會的MySQL知識】②使用MySQL
2023-05-02
MySql
【必知必會的MySQL知識】mysql5.7安裝教程
2023-05-04
MySql
MySQL 必知必會
2019-04-13
MySql
MySQL必知必會筆記——查詢的基礎知識
2021-06-11
MySql筆記
MYSQL中的鎖必知必會
2020-12-21
MySql
mysql必知必會筆記
2020-02-10
MySql筆記
Mysql必知必會練習
2020-09-26
MySql
01-mysql必知必會
2021-09-21
MySql
必須懂的mysql知識
2018-12-27
MySql
大前端開發人員必知必會的HTTP知識
2019-04-09
前端HTTP
Docker 必知必會1----初識
2024-05-07
Docker
《MySQL必知必會》萬用字元 ( like , % , _ ,)
2020-08-16
MySql字元
MySQL必知必會》正規表示式
2020-08-23
MySql
MySQL必知必會(1-12章)
2020-09-23
MySql
《Mysql必知必會》讀書筆記
2018-03-25
MySql筆記
MySQL必知必會詳細總結
2020-12-10
MySql
必知必會——SQL語句基本語法整理
2024-03-14
SQL
Elasticsearch必知必會的乾貨知識二：ES索引操作技巧
2020-07-27
Elasticsearch索引
Linux必會必知
2019-01-19
Linux
git必會必知
2019-04-08
Git
Redis 必知必會
2019-03-13
Redis
ThreadLocal必知必會
2020-05-22
thread
Activity 必知必會
2019-03-04
JSON 必知必會
2018-04-04
JSON
HashMap必知必會
2020-12-25
HashMap
《MySQL 必知必會》C25-C28
2020-11-02
MySql
精選Spring Boot三十五道必知必會知識點！
2019-03-13
Spring Boot
HTTP 必知必會的那些
2019-03-04
HTTP
notion database 必知必會
2024-03-13
Database
Linux shell必知必會
2020-03-14
Linux
Linux 程式必知必會
2020-07-10
Linux
Nacos必知必會：這些知識點你一定要掌握！
2023-05-15
12.MySQL必知必會之分組資料
2018-05-29
MySql
MySQL 必知概念
2024-09-22
MySql
必知必會Java命令-jps
2023-12-10
Java

【必知必會的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 實踐操作

相關文章