国产无码综合区,色欲AV无码国产永久播放,无码天堂亚洲国产AV,国产日韩欧美女同一区二区

<strong id="wgieu"></strong>

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

2年前作者：xiezhr分類：Toy博客閱讀(22)違法舉報

這篇具有很好參考價值的文章主要介紹了【必知必會的MySQL知識】⑤DQL語言。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

目錄

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

一、前言

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

二、基礎(chǔ)查詢

2.1 語法

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

2.2 實踐操作

新建user_profile信息表，并插入5條數(shù)據(jù)

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');

數(shù)據(jù)結(jié)構(gòu)如下

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

① 運營童鞋想要查看用戶信息表中所有的數(shù)據(jù)【查詢字段】

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)

② 運營童鞋想要用戶的設(shè)備id對應的性別、年齡和學校的數(shù)據(jù) 【查詢字段別名】

mysql> SELECT device_id AS 設(shè)備id,gender AS 性別,age AS 年齡,university AS 學校 FROM user_profile;
+--------+--------+------+----------+
| 設(shè)備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)

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

mysql 函數(shù)有很多，這里就不一一列舉了，后面寫一篇常用函數(shù)的使用

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

⑤ 運營童鞋想要構(gòu)建一個張三同學【查詢常量】

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`	`%` 通配符表示任何字符出現(xiàn)任意次數(shù) ;`_`通配符表示匹配一個字符
`between 取值1 and 取值2`	表示在取值1范圍和取值2范圍之間查詢,取值1為范圍的起始值；取值2為范圍的終止值。通常是時間范圍也可以是數(shù)字范圍
`not between 取值1 and 取值2`	與上面相反，不在取值1和取值2范圍之間的
`in`	指定條件范圍，范圍內(nèi)的每個條件都可以進行匹配。in 的取值全都括在括號中，每個值用逗號隔開
`is null`	表示某個字段為null
`is not null`	表示某個字段不為空

3.3 實踐操作

數(shù)據(jù)準備

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);

數(shù)據(jù)結(jié)構(gòu)如下

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

① 運營童鞋想要篩選出所有北京大學的學生進行用戶調(diào)研，請你從用戶信息表中取出滿足條件的數(shù)據(jù)，結(jié)果返回設(shè)備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歲以上的用戶開展分析，請你取出滿足條件的設(shè)備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歲及以下的用戶開展分析，請你取出滿足條件的設(shè)備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)

④ 運營童鞋想要查看除復旦大學以外的所有用戶明細，請你取出相應數(shù)據(jù)

-- ① 第一種寫法 使用<>
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)

⑤ 運營童鞋想要對用戶的年齡分布開展分析，在分析時想要剔除沒有獲取到年齡的用戶，請你取出所有年齡值不為空的用戶的設(shè)備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)的用戶進行調(diào)研，請你取出相關(guān)數(shù)據(jù)。

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)的用戶進行調(diào)研，請你取出相關(guān)數(shù)據(jù)（使用OR實現(xiàn)）

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)

⑧ 運營童鞋想要找到學校為北大、復旦和山大的同學進行調(diào)研，請你取出相關(guān)數(shù)據(jù)。

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)的復旦大學同學進行用戶調(diào)研，請你取出相應數(shù)據(jù)

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)

⑩ 運營童鞋想查看所有大學中帶有北京的用戶的信息，請你取出相應數(shù)據(jù)。


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
排序列表可以是單個字段、多個字段、別名、函數(shù)、表達式
order by的位置一般放在查詢語句的最后（除limit語句之外）

SELECT 
  查詢列表 
FROM
  表 
【W(wǎng)HERE 篩選條件】
ORDER BY 排序列表 【asc | desc】 ;

4.2 實踐操作

數(shù)據(jù)準備

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

①運營童鞋想要取出用戶信息表中的用戶年齡，請取出相應數(shù)據(jù)，并按照年齡升序排序。

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數(shù)據(jù)，并先按照gpa升序排序，再按照年齡升序排序輸出，請取出相應數(shù)據(jù)。

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)

③ 運營童鞋想要取出用戶信息表中對應的數(shù)據(jù)，并先按照gpa、年齡降序排序輸出，請取出相應數(shù)據(jù)。

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 聚集函數(shù)

運行在行組上，計算和返回單個值的函數(shù)

聚集函數(shù)	分組函數(shù)說明
`sum()`	返回某列值之和
`avg()`	返回某列平均值
`max()`	返回某列最大值
`min()`	返回某列最小值
`count()`	返回某列的函數(shù)

5.2.1 聚集函數(shù)簡單使用

*數(shù)據(jù)準備，新建一個產(chǎn)品信息表product

DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `productid` varchar(10) NOT NULL COMMENT '產(chǎn)品id',
  `productname` varchar(300) NOT NULL COMMENT '產(chǎn)品名稱',
  `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、計算所有產(chǎn)品單價之和
mysql> select sum(saleprice) from product;
+----------------+
| sum(saleprice) |
+----------------+
|           1625 |
+----------------+
1 row in set (0.00 sec)

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

-- 4、獲取所有產(chǎn)品中最小單價
mysql> select min(saleprice) from product;
+----------------+
| min(saleprice) |
+----------------+
|             55 |
+----------------+
1 row in set (0.00 sec)
-- 5、獲取一共有多少產(chǎn)品
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 實踐操作

數(shù)據(jù)準備

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的用戶的常用信息為使用的設(shè)備id為2138，性別為男，年齡21歲，北京大學，gpa為3.4在過去的30天里面活躍了7天，發(fā)帖數(shù)量為2，回答數(shù)量為12

①運營童鞋想查看每個學校用戶的平均發(fā)貼和回帖情況，尋找低活躍度學校進行重點運營，請取出平均發(fā)貼數(shù)低于5的學校或平均回帖數(shù)小于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)

--說明: 平均發(fā)貼數(shù)低于5的學?；蚱骄靥麛?shù)小于20的學校有2個
--屬于北京大學的用戶的平均發(fā)帖量為2.500，平均回答數(shù)量為21.000
--屬于浙江大學的用戶的平均發(fā)帖量為1.000，平均回答數(shù)量為2.000

② 運營童鞋想要查看不同大學的用戶平均發(fā)帖情況，并期望結(jié)果按照平均發(fā)帖情況進行升序排列，請你取出相應數(shù)據(jù)

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)

③ 運營童鞋想要對每個學校不同性別的用戶活躍情況和發(fā)帖數(shù)量進行分析，請分別計算出每個學校每種性別的用戶數(shù)、30天內(nèi)平均活躍天數(shù)和平均發(fā)帖數(shù)量

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)
--說明：
--第一行表示：北京大學的男性用戶個數(shù)為1，平均活躍天數(shù)為7天，平均發(fā)帖量為2
-- ...
-- 最后一行表示：山東大學的男性用戶個數(shù)為2，平均活躍天數(shù)為17.5天，平均發(fā)帖量為11

5.4 規(guī)定與小結(jié)

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

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

6.1 簡介

連接查詢又稱多表查詢，當查詢的字段來自于多個表時，就會用到連接查詢

6.2 笛卡兒積

表A有m行，表B有n行，結(jié)果=m*n行
產(chǎn)生原因：沒有有效的連接條件
避免方法：添加有效連接條件

6.3 連接分類

內(nèi)連接 連接表之間沒有主次關(guān)系,條件匹配上的就顯示，匹配不上的就不顯示
- 等值連接 連接表之間的連接條件為等值關(guān)系
- 非等值連接 連接表之間的連接條件為等值關(guān)系
- 自連接
外聯(lián)結(jié) 連接表之間有主次關(guān)系，主表全部顯示
- 左外連接（左連接） join右邊的表為主表
- 右外連接（有連接） join 左邊的表為主表

6.4 語法格式

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

6.4.1 內(nèi)連接之等值連接sql92標準

-寫法簡單，但是結(jié)構(gòu)不清晰，表的連接條件和后期篩選條件都放到where子句中

select 查詢列表
from 表1 t1,表2 t2
where t1 和 t2 的連接條件

6.4.2 內(nèi)連接之等值連接sql99標準

-表連接的條件時獨立的，連接之后，如果還需要進一步篩選，再往后加where 條件即可
內(nèi)連接中inner 關(guān)鍵字可以省去

select 查詢列表
from 表1 t1
inner join                  
表2 t2
on t1 和 t2 的等值連接條件
where 篩選條件

6.4.3 內(nèi)連接之非等值連接

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

6.4.3 內(nèi)連接之自連接

一張表看作兩張表

select 查詢列表
from 表1 t1
inner join                  
表1 t2
on t1 和 t2 的關(guān)聯(lián)條件
where 篩選條件

6.4.4 外連接之右連接

join 右邊的表“表2”作為主表，根據(jù)條件將表2中數(shù)據(jù)全部查出來

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 實踐操作

數(shù)據(jù)準備，一共三張表。部門表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, -- 直屬領(lǐng)導編號
    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 為等值關(guān)聯(lián)，所以上面例子時等值關(guān)聯(lián)查詢

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

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 為非等值關(guān)聯(lián)，所以上面例子時非等值關(guān)聯(lián)查詢

③ 查詢員工SMITH 和員工SCOTT 上級領(lǐng)導

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

④運營童鞋想要查看所有部門的員工信息，如果新設(shè)立的部門沒有員工也要將其顯示出來

任何左連接可以實現(xiàn)的右連接也可以實現(xiàn)
1、通過左連接實現(xiàn)

-- 部門編號為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、通過右連接實現(xiàn)

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 的部門薪資等級
想要完成運營童鞋的需求，需要關(guān)聯(lián)三張表，但也不是什么難事

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 出現(xiàn)的位置

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

7.3 實踐操作

數(shù)據(jù)準備，一共三張表。部門表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, -- 直屬領(lǐng)導編號
    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 簡介

將查詢結(jié)果集合并成新的結(jié)果集

8.2 語法

union關(guān)鍵字默認去重，如果使用union all可以包含重復項

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

8.3 特點

要查詢的結(jié)果來自于多個表且多個表沒有直接的連接關(guān)系，但查詢的信息一致時，可以使用聯(lián)合查詢
要求多條查詢語句的查詢列數(shù)是一致的
要求多條查詢語句的查詢的每一列的類型和順序一致

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','張?zhí)煊?,'男',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	張?zhí)煊?/td>	男	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)
-- 或者可以通過以下寫法實現(xiàn)
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)

-- 或者可以通過以下寫法實現(xiàn)
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 簡介

假設(shè)一個公司有10000名員工，界面上需要展示員工信息。這時候我們就需要使用分頁查詢，將員工信息按n頁展示，每頁顯示m名員工信息

9.2 語法

9.2.1 limit 語法

limit語句放在查詢語句的最后

- startindex 表示起始索引，size代表條目數(shù)
SELECT 
  查詢列表 
FROM
  表1 別名1
【連接類型】 JOIN 表2 別名2 ON 連接條件 
【W(wǎng)HERE 分組前的篩選】
【GROUP BY 分組字段】 
【HAVING 分組后的篩選 】
【ORDER BY 排序字段 ASC|DESC】
LIMIT [startindex] size ;

9.2.2 分頁查詢語法

分頁查詢展示可以提高用戶體驗

-- page 表示第幾頁
-- size 表示每頁顯示多少條數(shù)據(jù)
select 查詢列表 from 表 limit (page-1)*size,size;

9.3 實踐操作

按以下腳本準備一張員工表信息

CREATE TABLE EMP
    (
    EMPNO INT  PRIMARY KEY,  -- 員工編號
    ENAME VARCHAR(10),  -- 員工名稱
    JOB VARCHAR(9), -- 工作
    MGR DOUBLE, -- 直屬領(lǐng)導編號
    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文章來源地址http://www.zghlxwxcb.cn/news/detail-433161.html

-- 第一頁 (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 ;

到了這里，關(guān)于【必知必會的MySQL知識】⑤DQL語言的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！

本文來自互聯(lián)網(wǎng)用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務(wù)，不擁有所有權(quán)，不承擔相關(guān)法律責任。如若轉(zhuǎn)載，請注明出處：如若內(nèi)容造成侵權(quán)/違法違規(guī)/事實不符，請點擊違法舉報進行投訴反饋，一經(jīng)查實，立即刪除！

分享到：

領(lǐng)支付寶紅包贊助服務(wù)器費用

【必知必會的MySQL知識】mysql5.7安裝教程
下載地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads 下載zip免安裝版，可以省去很多事 my.ini文件內(nèi)容如下以上配置文件說明 basedir--mysql目錄 datadir--數(shù)據(jù)路徑 port --端口 skip-grant-tables --剛開始跳過登陸校驗安裝服務(wù)：初始化數(shù)據(jù)庫登陸命令：最后，我們當然不希望以后寫
2024年02月02日
瀏覽(49)
10個必知必會的VSCode實用快捷鍵
掌握10個必知必會的VS Code實用快捷鍵，提高工作效率。微信搜索關(guān)注《Python學研大本營》，加入讀者群，分享更多精彩代碼編輯器Visual Studio Code（VS Code）是很多開發(fā)者日常使用的IDE，本文將分享一些實用的VS Code快捷鍵和技巧，可以幫助開發(fā)者節(jié)省大量時間。 Ctrl+D 能讓用戶
2024年04月29日
瀏覽(18)
新手運維必知必會的常用技能或工具
說到工具，在行外可以說是技能，在行內(nèi)我們一般稱為工具，就是運維必須要掌握的工具。我就大概列出這幾方面，這樣入門就基本沒問題了。 linux系統(tǒng)如果是學習可以選用redhat或centos，特別是centos在企業(yè)中用得最多，當然還會有其它版本的，但學習者還是以這2個版本學習
2024年02月07日
瀏覽(16)
什么是數(shù)字開發(fā)？關(guān)于數(shù)字開放必知必會的內(nèi)容點
數(shù)據(jù)開發(fā)的基礎(chǔ)概念必知必會數(shù)據(jù)開發(fā)是指將數(shù)據(jù)從不同的來源整合、清洗、轉(zhuǎn)換、存儲和分析的過程。數(shù)據(jù)開發(fā)的目的是為了讓數(shù)據(jù)更加有用，以便于企業(yè)做出更好的決策。在本文中，我們將介紹數(shù)據(jù)開發(fā)的基本概念，包括數(shù)據(jù)倉庫、ETL、數(shù)據(jù)建模、數(shù)據(jù)挖掘和數(shù)據(jù)可視化
2023年04月24日
瀏覽(18)
MySql必知必會
Buffer Pool基本概念 Buffer Pool：緩沖池，簡稱BP。其作用是用來緩存表數(shù)據(jù)與索引數(shù)據(jù)，減少磁盤IO操作，提升效率。 Buffer Pool由緩存數(shù)據(jù)頁(Page) 和對緩存數(shù)據(jù)頁進行描述的控制塊組成, 控制塊中存儲著對應緩存頁的所屬的表空間、數(shù)據(jù)頁的編號、以及對應緩存頁在Buffer Poo
2024年01月22日
瀏覽(31)
SQL必知必會知識點匯總
?歡迎大家閱讀我的文章呀? 今天是SQL必知必會的最后一塊練習。希望你們在我的文章當中能有所收獲?。?！ SLogan:利用有限的時間，擼起袖子加油干！ ??本篇內(nèi)容主要總結(jié)了【SQL必知必會】當中的一些知識點以及我做題過程當中碰到的一些細節(jié)問題。雖然這一塊的題目
2023年04月11日
瀏覽(28)
MySQL必知必會（初級篇）
數(shù)據(jù)庫（DataBase，DB),是統(tǒng)一管理的、長期存儲在計算機內(nèi)的、有組織的相關(guān)數(shù)據(jù)的集合。特點是數(shù)據(jù)見聯(lián)系密切、冗余度小、獨立性高、易擴展，并且可以為各類用戶共享。 MySQL ：是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的
2023年04月08日
瀏覽(19)
《MySQL 必知必會》課程筆記（三）
創(chuàng)建和修改數(shù)據(jù)表，是數(shù)據(jù)存儲過程中的重要?環(huán)。我們不僅需要把表創(chuàng)建出來，還需要正確地設(shè)置限定條件，這樣才能確保數(shù)據(jù)的一致性和完整性。同時，表中的數(shù)據(jù)會隨著業(yè)務(wù)需求的變化而變化，添加和修改相應的字段也是常見的操作。首先，我們要知道 MySQL 創(chuàng)建表的
2024年02月03日
瀏覽(22)
Nacos必知必會：這些知識點你一定要掌握！
Nacos 是一個開源的服務(wù)發(fā)現(xiàn)、配置管理和服務(wù)治理平臺，是阿里巴巴開源的一款產(chǎn)品。 Nacos 可以幫助開發(fā)者更好地管理微服務(wù)架構(gòu)中的服務(wù)注冊、配置和發(fā)現(xiàn)等問題，提高系統(tǒng)的可靠性和可維護性。本文將介紹 Nacos 的必知必會知識點，包括服務(wù)注冊與發(fā)現(xiàn)、配置管理、命名
2024年02月05日
瀏覽(20)
MySQL必知必會：MySQL中的Schema與DataBase
涉及到數(shù)據(jù)庫的模式有很多疑惑，問題經(jīng)常出現(xiàn)在模式和數(shù)據(jù)庫之間是否有區(qū)別，如果有，區(qū)別在哪里。取決于數(shù)據(jù)庫供應商對schema（模式）產(chǎn)生疑惑的一部分原因是數(shù)據(jù)庫系統(tǒng)傾向于以自己的方式處理模式（1）MySQL的文檔中指出，在物理上，模式與數(shù)據(jù)庫是同義的，所以
2023年04月27日
瀏覽(22)

^{<mark id="x68lk"></mark>}