# 常见的嵌套查询

7 篇文章 0 订阅

## 标题几种常见的嵌套查询

### 1.录入数据

# 创建学员信息表
CREATE TABLE stu_info(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20),
gender CHAR(1),
department VARCHAR(10),
age TINYINT,
province VARCHAR(10),
email VARCHAR(50),
mobilephone CHAR(11)
);

# 创建学员成绩表
CREATE TABLE stu_score(
id INT ,
MySQL TINYINT,
Python TINYINT,
Visualization TINYINT
);

# 向学员表中插入数据
INSERT INTO stu_info(NAME,gender,department,age,province,email,mobilephone) VALUES
('张勇','男','数学系',23,'河南','sfddf123dd@163.com','13323564321'),
('王兵','男','数学系',25,'江苏','lss1993@163.com','17823774329'),
('刘伟','男','计算机系',21,'江苏','qawsed112@126.com','13834892240'),
('张峰','男','管理系',22,'上海','102945328@qq.com','13923654481'),
('董敏','女','生物系',22,'浙江','82378339@qq.com','13428439022'),
('徐晓红','女','计算机系',24,'浙江','xixiaohong@gmail.com','13720097528'),
('赵伊美','女','数学系',21,'江苏','zhaomeimei@163.com','13417723980'),
('王建国','男','管理系',24,'浙江','9213228402@qq.com','13768329901'),
('刘清','女','统计系',23,'安徽','lq1128@gmail.com','17823651180'),
('赵家和','男','计算机系',28,'山东','dcrzdbjh@163.com','13827811311');

# 向成绩表中插入数据
INSERT INTO stu_score VALUES
(1,87,72,88),
(3,90,66,72),
(2,90,70,86),
(4,88,82,76),
(8,92,67,80),
(10,88,82,89),
(5,79,66,60),
(7,91,78,90),
(6,82,79,88),
(9,85,70,85);

SELECT * FROM stu_info;

SELECT * FROM stu_score;



### 2.含in关键词的嵌套查询

in关键词有两种用法：
Ⅰ将可枚举的离散值写在值列表中
Ⅱ语法中的嵌套，即把另一个查询语句块写在in关键词后面的括号内

①查询与张勇，刘伟同一个系的学员信息

SELECT
*
FROM
stu_info
WHERE department IN
(SELECT
department
FROM
stu_info
WHERE NAME IN ('张勇', '刘伟'));


②查询MySQL成绩大于90分的学员信息

#用in嵌套来写
SELECT * FROM stu_info
WHERE id IN (SELECT id FROM stu_score WHERE MySQL >90);

#用内连接来写（感觉麻烦了好多，小白不懂o(╥﹏╥)o）
SELECT
s1.id,
NAME,
gender,
department,
age,
province,
email,
mobilephone
FROM
stu_info AS s1
INNER JOIN stu_score AS s2
ON s1.id = s2.id
WHERE s2.MySQL > 90 ;


### 3.含exists关键词的嵌套查询

exists关键词的作用与in几乎一样，区别在于，通过exists查询返回的不是具体的值的集合，而是满足条件的逻辑值；通常情况下，exists的查询速度比in关键词查询速度快一些。
①查询MySQL成绩大于90分的学员信息

SELECT
*
FROM
stu_info
WHERE EXISTS
(SELECT
*
FROM
stu_score
WHERE stu_info.id = stu_score.id
AND MySQL > 90);


## 含比较运算符的嵌套查询

SELECT
*
FROM
stu_info AS s1
WHERE s1.age >=
(SELECT
AVG(age)
FROM
stu_info AS s2
WHERE s1.department = s2.department) ;


### 5.含any或all 关键词的嵌套查询

①查询非管理系中比管理系任意一个学员年龄小的学员信息

SELECT
*
FROM
stu_info
WHERE age < ANY
(SELECT DISTINCT
age
FROM
stu_info
WHERE department = '管理系')
AND department != '管理系';

#就是小于管理系中的最大年龄就OK了
SELECT
*
FROM
stu_info
WHERE age <
(SELECT
MAX(age)
FROM
stu_info
WHERE department = '管理系')
AND department != '管理系';


②查询非管理系中比管理系所有学员年龄小的学员信息

  SELECT
*
FROM
stu_info
WHERE age < ALL
(SELECT DISTINCT
age
FROM
stu_info
WHERE department = '管理系')
AND department != '管理系';

就是非管理系的要小管理系年龄最小的那个
SELECT
*
FROM
stu_info
WHERE age <
(SELECT
MIN(age)
FROM
stu_info
WHERE department = '管理系')
AND department != '管理系';


• 3
点赞
• 22
收藏
觉得还不错? 一键收藏
• 打赏
• 1
评论
05-11 179
08-14 2万+
08-02 313
04-26 585
06-03 2564
07-21 620
10-29 917
05-27 1622
07-30 3798
08-03 1621
04-15 7601
12-23 1492
10-20 7709
10-16
03-23

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

¥1 ¥2 ¥4 ¥6 ¥10 ¥20

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。