数据分析之MySQL——几种常见的嵌套查询

嵌套查询，也称为子查询，是实际工作中经常用到的一种查询方式。子查询其实就是在已有的查询语句中的where后面再嵌套一层查询语句，也就是把内层查询结果当做外层查询参照的数据表来使用。

4种常见的子查询：

含有比较运算符（>、>=、<、<=、=、！=）

IN关键词

ANY/ALL关键词

EXISTS关键词

# 创建学员信息表

CREATE TABLE stu_info(

id INT AUTO_INCREMENT PRIMARY KEY,

iname VARCHAR(20),

gender CHAR(1),

department VARCHAR(10),

age TINYINT,

province VARCHAR(10),

email VARCHAR(50),

mobilephone CHAR(11)

);

# 向学员表中插入数据

INSERT INTO stu_info(iname,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');

select * from stu_info;

# 同理

# 创建学员成绩表

CREATE TABLE stu_score(

id INT ,

Excel TINYINT,

Tableau TINYINT,

MySQL TINYINT

);

# 向成绩表中插入数据

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

# 查询年龄超过所有学员平均年龄的学员信息

SELECT * FROM stu_info

WHERE age >= (SELECT AVG(age) FROM stu_info)

# 查询年龄不低于所属系平均年龄的学员信息

SELECT  department,AVG(age) FROM stu_info

group by department;

SELECT * FROM stu_info as t1

where age >= (SELECT AVG(age) from stu_info as t2

WHERE t1.department = t2.department)

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

SELECT * FROM stu_info

WHERE age > ALL(SELECT DISTINCT age FROM stu_info

WHERE department = '管理系')

AND department != '管理系';

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

SELECT * FROM stu_info 

WHERE department IN (SELECT department FROM stu_info 

WHERE iname IN('张勇','刘伟'));

# 查询MySQL成绩大于85分的学员信息

SELECT * FROM stu_info

WHERE id IN(SELECT id FROM stu_score WHERE MySQL > 85);

EXISTS的作用就是“判断是否存 在满足某种条件的记录”，如果存在这样的记录就返回真（True）

# 查询MySQL成绩大于85分的学员信息

SELECT * FROM stu_info

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

内部是否成立，外和内相联系

# 查询数学系和计算机系之外的学员信息 

# 方法一 

SELECT * FROM stu_info WHERE department NOT IN('数学系','计算机系');

适用于外面的表大

#方法二 

SELECT * FROM stu_info WHERE NOT EXISTS(SELECT * FROM stu_score WHERE department IN('数学系','计算机系') and stu_score.id = stu_info.id);

与EXISTS相反

适用于外面的表比较小，内大