當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL（四）复合查询与联合查询

發布時間：2024/4/11 数据库 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL（四）复合查询与联合查询小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

文章目錄

復合查詢
- 復合函數
- - AVG
  - COUNT
  - SUM
  - MAX
  - MIN
- 分組查詢
- - GROUP BY
  - HAVING
聯合查詢
- 內連接
- 外連接
- - 左連接
  - 右連接
- 自連接
- 子查詢
- 合并查詢
- - UNION
  - UNION ALL

復合查詢

復合函數

通常對于統計數量，計算平均值，尋找最大值等操作，都可以通過復合函數來完成
MySQL給出了以下五種聚合函數

AVG(): 返回查詢到的數據的平均值
COUNT():返回查詢到的數據的數量
SUM():返回查詢到的數據的總和
MAX():返回查詢到的數據的最大值
MIN():返回查詢到的數據的最小值

注意：復合函數僅能作用于數字

下面對這個表進行案例演示

+----+------+--------+------------+-------+---------+ | id | age | name | birth | math | english | +----+------+--------+------------+-------+---------+ | 1 | 11 | 李華 | 2020-08-12 | 76.50 | 87.50 | | 2 | 15 | 李梅 | 2020-08-13 | 88.50 | 90.50 | | 3 | 18 | 張三 | 2020-08-14 | 84.50 | 70.50 | | 4 | 16 | 李四 | 2020-08-15 | 75.60 | 40.80 | | 5 | 14 | 王五 | 2020-08-16 | 48.80 | 91.90 | | 6 | 13 | 馬六 | 2020-08-17 | 85.90 | 95.40 | +----+------+--------+------------+-------+---------+

AVG

返回查詢到的數據的平均值

//計算平均數學成績 SELECT AVG(math) FROM student; +-----------+ | AVG(math) | +-----------+ | 76.633333 | +-----------+

COUNT

返回查詢到的數據的數量

//統計班級中有多少個學生 SELECT COUNT(*) FROM student; +----------+ | COUNT(*) | +----------+ | 6 | +----------+

SUM

返回查詢到的數據的總和

//計算班上學生的年齡總和 SELECT SUM(age) FROM student; +----------+ | SUM(age) | +----------+ | 87 | +----------+

MAX

返回查詢到的數據的最大值

//找出英語最高分 SELECT MAX(english) FROM student; +--------------+ | MAX(english) | +--------------+ | 95.40 | +--------------+

MIN

返回查詢到的數據的最小值

//找出總分最低的 SELECT MIN(english + math) AS TOTAL FROM student; +--------+ | TOTAL | +--------+ | 116.40 | +--------+

分組查詢

分組查詢即使用GROUP BY子句對指定列進行分組查詢

注意：SELECT指定的字段必須是“分組依據字段”，其他字段若想出現在SELECT 中則必須包含在聚合函數中。

GROUP BY

語法

SELECT 查詢項 FROM 表名 GROUP BY 分組依據; //建立一個職員表 create table emp( id int primary key auto_increment comment '工號', name varchar(20) not null comment '職員姓名', role varchar(20) not null comment '職位', salary numeric(11,2) comment '月薪' ); //插入一些數據 insert into emp(name, role, salary) values ('李華','實習生', 2500.20), ('李梅','普通職員', 7000.99), ('張三','普通職員', 6000.11), ('李四','普通職員', 5833.5), ('王五','實習生', 2700.33), ('馬六','經理', 12000.66);

接下來按照職位進行分組，查詢各職位的平均月薪、最高月薪和最低月薪

select role,max(salary),min(salary),avg(salary) from emp group by role;+--------------+-------------+-------------+--------------+ | role | max(salary) | min(salary) | avg(salary) | +--------------+-------------+-------------+--------------+ | 實習生 | 2700.33 | 2500.20 | 2600.265000 | | 普通職員 | 7000.99 | 5833.50 | 6278.200000 | | 經理 | 12000.66 | 12000.66 | 12000.660000 | +--------------+-------------+-------------+--------------+

HAVING

如果使用GROUP BY進行分組，如果需要使用條件判斷來過濾數據，就不能再使用WHERE，而是要使用HAVING
語法

SELECT 查詢項 FROM 表名 GROUP BY 分組依據 HAVING 條件; //查詢平均工資超過5000元的職位 select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary) > 5000;+--------------+-------------+-------------+--------------+ | role | max(salary) | min(salary) | avg(salary) | +--------------+-------------+-------------+--------------+ | 普通職員 | 7000.99 | 5833.50 | 6278.200000 | | 經理 | 12000.66 | 12000.66 | 12000.660000 | +--------------+-------------+-------------+--------------+

聯合查詢

為了方便用例，首先建立學生表、班級表、課程表、成績表

DROP TABLE IF EXISTS course; CREATE TABLE course(id int PRIMARY KEY AUTO_INCREMENT,name varchar(8) );DROP TABLE IF EXISTS classes; CREATE TABLE classes (id INT PRIMARY KEY auto_increment,name VARCHAR(20),`desc` VARCHAR(100) );DROP TABLE IF EXISTS student; CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT, sn INT UNIQUE,name VARCHAR(20) DEFAULT 'unkown',qq_mail VARCHAR(20),classes_id INT,FOREIGN KEY (classes_id) REFERENCES classes(id) );DROP TABLE IF EXISTS score; CREATE TABLE score (id INT PRIMARY KEY auto_increment,score DECIMAL(3, 1),student_id int,course_id int,FOREIGN KEY (student_id) REFERENCES student(id),FOREIGN KEY (course_id) REFERENCES course(id) );

內連接

內連接即查找兩個表中的交集，找到兩個表中同時符合條件的數據，進行連接。
語法

select 字段 from 表1 別名1 [inner] join 表2 別名2 on 連接條件 and 其他條件; select 字段 from 表1 別名1,表2 別名2 where 連接條件 and 其他條件; //查找名為白素貞的學生的成績 select stu.name, sco.score from student stu inner join score sco on stu.id = sco.id and stu.name = "白素貞";+-----------+-------+ | name | score | +-----------+-------+ | 白素貞 | 33.0 | +-----------+-------+//使用另外一個語法 select stu.name, sco.score from student stu, score sco where stu.id = sco.id and stu.name = "白素貞";+-----------+-------+ | name | score | +-----------+-------+ | 白素貞 | 33.0 | +-----------+-------+

外連接

外連接又分左外連接和右外連接
簡單來說就是，如果左邊的表完全顯示就是左連接，右邊的表完全顯示就是右連接

左連接

對于左連接，以左表的數據為基準，在右表中查找符合條件的數據，找不到的以也會NULL展示。
語法

select 字段名 from 表名1 left join 表名2 on 連接條件;

示例

//左連接，以學生表為基準，查找成績表中所有學生的成績 select * from student stu left join score sco on stu.id=sco.student_id;```sql +----+-------+-----------------+------------------+------------+------+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score | student_id | course_id | +----+-------+-----------------+------------------+------------+------+-------+------------+-----------+ | 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 1 | 70.5 | 1 | 1 | | 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 2 | 98.5 | 1 | 3 | | 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 3 | 33.0 | 1 | 5 | | 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 4 | 98.0 | 1 | 6 | | 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60.0 | 2 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 6 | 59.5 | 2 | 5 | | 3 | 391 | 白素貞 | NULL | 1 | 7 | 33.0 | 3 | 1 | | 3 | 391 | 白素貞 | NULL | 1 | 8 | 68.0 | 3 | 3 | | 3 | 391 | 白素貞 | NULL | 1 | 9 | 99.0 | 3 | 5 | | 4 | 31 | 許仙 | xuxian@qq.com | 1 | 10 | 67.0 | 4 | 1 | | 4 | 31 | 許仙 | xuxian@qq.com | 1 | 11 | 23.0 | 4 | 3 | | 4 | 31 | 許仙 | xuxian@qq.com | 1 | 12 | 56.0 | 4 | 5 | | 4 | 31 | 許仙 | xuxian@qq.com | 1 | 13 | 72.0 | 4 | 6 | | 5 | 54 | 不想畢業 | NULL | 1 | 14 | 81.0 | 5 | 1 | | 5 | 54 | 不想畢業 | NULL | 1 | 15 | 37.0 | 5 | 5 | | 6 | 51234 | 好好說話 | say@qq.com | 2 | 16 | 56.0 | 6 | 2 | | 6 | 51234 | 好好說話 | say@qq.com | 2 | 17 | 43.0 | 6 | 4 | | 6 | 51234 | 好好說話 | say@qq.com | 2 | 18 | 79.0 | 6 | 6 | | 7 | 83223 | tellme | NULL | 2 | 19 | 80.0 | 7 | 2 | | 7 | 83223 | tellme | NULL | 2 | 20 | 92.0 | 7 | 6 | | 8 | 9527 | 老外學中文 | foreigner@qq.com | 2 | NULL | NULL | NULL | NULL | +----+-------+-----------------+------------------+------------+------+-------+------------+-----------+

可以看到，此時進行左連接，以學生表為基準在成績表中查找，所以對于成績表中不存在的學生，會用NULL表示而不是直接忽略

右連接

對于右連接，以右表的數據為基準，在左表中查找符合條件的數據，找不到的以也會NULL展示。
語法

語法

select 字段 from 表名1 right join 表名2 on 連接條件;

示例

//右連接，以成績表為基準，查找學生表中有成績的學生 select * from student stu right join score sco on stu.id=sco.student_id;+------+-------+-----------------+-----------------+------------+----+-------+------------+-----------+ | id | sn | name | qq_mail | classes_id | id | score | student_id | course_id | +------+-------+-----------------+-----------------+------------+----+-------+------------+-----------+ | 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 1 | 70.5 | 1 | 1 | | 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 2 | 98.5 | 1 | 3 | | 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 3 | 33.0 | 1 | 5 | | 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | 4 | 98.0 | 1 | 6 | | 2 | 835 | 菩提老祖 | NULL | 1 | 5 | 60.0 | 2 | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | 6 | 59.5 | 2 | 5 | | 3 | 391 | 白素貞 | NULL | 1 | 7 | 33.0 | 3 | 1 | | 3 | 391 | 白素貞 | NULL | 1 | 8 | 68.0 | 3 | 3 | | 3 | 391 | 白素貞 | NULL | 1 | 9 | 99.0 | 3 | 5 | | 4 | 31 | 許仙 | xuxian@qq.com | 1 | 10 | 67.0 | 4 | 1 | | 4 | 31 | 許仙 | xuxian@qq.com | 1 | 11 | 23.0 | 4 | 3 | | 4 | 31 | 許仙 | xuxian@qq.com | 1 | 12 | 56.0 | 4 | 5 | | 4 | 31 | 許仙 | xuxian@qq.com | 1 | 13 | 72.0 | 4 | 6 | | 5 | 54 | 不想畢業 | NULL | 1 | 14 | 81.0 | 5 | 1 | | 5 | 54 | 不想畢業 | NULL | 1 | 15 | 37.0 | 5 | 5 | | 6 | 51234 | 好好說話 | say@qq.com | 2 | 16 | 56.0 | 6 | 2 | | 6 | 51234 | 好好說話 | say@qq.com | 2 | 17 | 43.0 | 6 | 4 | | 6 | 51234 | 好好說話 | say@qq.com | 2 | 18 | 79.0 | 6 | 6 | | 7 | 83223 | tellme | NULL | 2 | 19 | 80.0 | 7 | 2 | | 7 | 83223 | tellme | NULL | 2 | 20 | 92.0 | 7 | 6 | +------+-------+-----------------+-----------------+------------+----+-------+------------+-----------+

可以看到，此時進行右連接，以成績表為基準在學生表中查找，所以對于學生表中不存在的學生，會用NULL表示而不是直接忽略

自連接

自連接即將自己的表進行連接，需要對表名進行別名顯示

例如要查詢本班中所有JAVA成績比計算機原理成績高的成績信息

//join on語句 select * from score s1 join score s2 on s1.student_id = s2.student_id and s1.score > s2.score and s1.id = 1 and s2.id = 3;//where語句 select * from score s1,score s2 where s1.student_id = s2.student_id and s1.score > s2.score and s1.id = 1 and s2.id = 3;+----+-------+------------+-----------+----+-------+------------+-----------+ | id | score | student_id | course_id | id | score | student_id | course_id | +----+-------+------------+-----------+----+-------+------------+-----------+ | 1 | 70.5 | 1 | 1 | 3 | 33.0 | 1 | 5 | +----+-------+------------+-----------+----+-------+------------+-----------+

子查詢

子查詢又叫做嵌套查詢，其實就是嵌入其他sql語句中的select語句，一般用于查詢的條件是另一條語句的結果這一情況。

例如

//單行子查詢，查找與白素貞同班的同學。即查詢到白素貞所在的班號，再通過班號查詢該班學生 select * from student where classes_id= (select classes_id from student where name='白素貞');+----+------+-----------------+-----------------+------------+ | id | sn | name | qq_mail | classes_id | +----+------+-----------------+-----------------+------------+ | 1 | 9982 | 黑旋風李逵 | xuanfeng@qq.com | 1 | | 2 | 835 | 菩提老祖 | NULL | 1 | | 3 | 391 | 白素貞 | NULL | 1 | | 4 | 31 | 許仙 | xuxian@qq.com | 1 | | 5 | 54 | 不想畢業 | NULL | 1 | +----+------+-----------------+-----------------+------------+//多行子查詢，查詢java和英語的成績信息。即先查詢到java和英語的課程號，再通過課程號查詢成績表 select * from score where course_id in (select id from course where name='java' or name='英文');

合并查詢

UNION

該操作符用于取得兩個結果集的并集。當使用該操作符時，會自動去掉結果集中的重復行
語法

結果集A UNION 結果集B;

例如

//查找id小于2或者名字叫做語文和英語的課程 SELECT * FROM course WHERE id < 3 UNION SELECT * FROM course WHERE name in ("語文", "英文");+----+--------------------+ | id | name | +----+--------------------+ | 1 | Java | | 2 | 中國傳統文化 | | 4 | 語文 | | 6 | 英文 | +----+--------------------+

同樣的結果也可以通過or語句來得到

SELECT * FROM course WHERE id < 2 OR name in ("語文", "英文");+----+--------+ | id | name | +----+--------+ | 1 | Java | | 4 | 語文 | | 6 | 英文 | +----+--------+

但是OR這個邏輯運算符會忽略索引，所以會導致在海量數據查詢中性能會下降很多。

UNION ALL

該操作符用于取得兩個結果集的并集。當使用該操作符時，不自動去掉結果集中的重復行
與上面的使用相同，但是不會去掉重復數據

SELECT * FROM course WHERE id < 3 UNION ALL SELECT * FROM course WHERE name = "英文"; +----+--------------------+ | id | name | +----+--------------------+ | 1 | Java | | 2 | 中國傳統文化 | | 6 | 英文 | | 10 | 英文 | +----+--------------------+

總結

以上是生活随笔為你收集整理的MySQL（四）复合查询与联合查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

MySQL

上一篇： MySQL（三）数据库的六种约束、表的关
下一篇： ucontext族函数的使用及原理分析