常见的嵌套查询

标题几种常见的嵌套查询

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 关键词的嵌套查询

对于含比较运算符的嵌套查询而言，嵌套部分查询语句结果返回单个值，如果嵌套部分的查询返回多个值，并且父查询和子查询之间任然可以使用比较运算符进行连接时，就需要使用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 != '管理系';