代码如下： ``` /* 子查询：一个sql语句里边包含多条查询语句 子查询的分类：标量子查询、列子查询、行子查询、表子查询 标量子查询：返回一行一列，也就是单个值 列子查询：返回一列 行子查询：返回一行 表子查询：子查询的结果是一个表 */ /* 标量子查询 */ -- 查询1213班的所有学生信息 select * from class where ClassName = '计网1213' select * from students where classid = 3 select * from students where classid = (select id from class where ClassName = '计网1212') -- 查询紫嫣之后入职的员工 select * from students -- 第1步：查询紫嫣入职的时间 select entrydate from students where username = '紫嫣' -- 第2步：查询紫嫣入职之后的时间 select * from students where entrydate > (select entrydate from students where username = '紫嫣') /* 列子查询 可以使用in(在什么里边) all（一列的每个条件都需要满足） any(满足一个即可) */ -- 查询1211班与1212班所有学生的信息 select id from class where classname = '计网1211' or classname = '计网1212' select * from students where classid in (select id from class where classname = '计网1211' or classname = '计网1212') -- 查询比1213班所有人工资都高的学生 -- 第一步：查询1213班所有的人工资 select salary from students where classid = (select id from class where ClassName = '计网1213') -- 第二步：找出比这些工资都高的人,使用max函数 select * from students where salary > (select max(salary) from students where classid = (select id from class where ClassName = '计网1213')) -- 方法2：all关键字会挨着一个一个比较，要比所有的都高，其实就是比最高的高就行了 select * from students where salary > all (select salary from students where classid = (select id from class where ClassName = '计网1213')) -- 查询比1211班任意一个工资高的学生（不需要比所有人都高） select id from class where classname = '计网1211' select salary from students where classid = (select id from class where classname = '计网1211') -- 方法1 min函数 select * from students where salary > (select min(salary) from students where classid = (select id from class where ClassName = '计网1211')) -- 方法2 any select * from students where salary > any (select salary from students where classid = (select id from class where ClassName = '计网1211')) /* 行子查询 */ -- 查询与诸葛亮工资和直属领导相同的员工 select * from students -- a:查询诸葛亮的工资与直属领导是谁 select salary,managerid from students where username = '诸葛亮' -- b:查询薪水是12500，managerid为1 select * from students where salary = 12500 and managerid = 1 -- 方法1： select * from students where salary = (select salary from students where username = '诸葛亮') and managerid = (select managerid from students where username = '诸葛亮') -- 方法2： select * from students where (salary,managerid) = (12500,1) select * from students where (salary,managerid) = (select salary,managerid from students where username = '诸葛亮') /* 表子查询 返回的是多行多列，相当于一张表 */ -- 查询与赵云,诸八戒职位和工资都相同的员工 -- a: 赵云,猪八戒职位和工资 select job,salary from students where username='赵云' or username='诸八戒' -- 方法1：单个单个查询然后在合并结合 select * from students where (job,salary) = (select job,salary from students where username = '赵云') union select * from students where (job,salary) = (select job,salary from students where username = '诸八戒') -- 方法2： select * from students where (job,salary) in (select job,salary from students where username='赵云' or username='诸八戒') -- 查询1990之后入职员工的信息与班级 -- a:查询1990之后入职的员工信息 select * from students where entrydate > '1990-1-1' -- b:把1990之后入职的员工信息和班级进行一个连接 -- 方法1： select stu.username,stu.entrydate,class.classname from (select * from students where entrydate > '1990-1-1') stu join class on stu.classid = class.id -- 方法2：（作业） ```