mysql子查询

代码如下：

/*
   子查询：一个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：（作业）