mysql多表查询与子查询

代码如下：

/* 笛卡尔积 */
select * from students,class
/* 内连接 */
-- 隐式内连接
select username,classname from students e,class c where e.classid = c.id
-- 显示内连接
select username,classname from students e inner join class c on e.classid = c.id
/* 外连接 */
-- 左外连接
select username,classname from students e left outer join class c on e.classid = c.id
-- 右外连接
select username,classname from students e right outer join class c on e.classid = c.id
/* 自连接 */
select * from students
-- 查询员工以及直属领导的名称
select s.username,leader.username from students s join students leader on s.managerid = leader.id
-- 查询员工以及直属领导的名称,没有领导的也需要查询出来，如果没有领导就显示老大
select s.username,ifnull(leader.username,'老大') from students s left join students leader on s.managerid = leader.id
/* 联合查询 */
-- 把学生表年龄小于50的与学生分表中薪水小于8000的都查询出来
-- UNION all 直接把查询结果进行合并
select * from students where age>40
UNION all
select * from student_cq where salary <8000
-- UNION 把查询结果合并之后去重
select * from students where age>40
UNION 
select * from student_cq where salary <8000

子查询：(后面把子查询拆分成另外一个，然后把这个文章转载成我自己的文章)

/*
   子查询分类：
     标量子查询（子查询结果是单个值）
     列子查询（子查询结果是一列）
     行子查询（子查询结果是一行）
     表子查询（子查询结果是一个表）
   子查询位置：where , from ,select 都有可能
*/
/*  标量子查询 */
-- 查询1213班的所有学生信息
select * from class where ClassName = '计网1213'
select * from students where classid = 3
select * from students where classid = (select id from class where ClassName = '计网1213')
-- 查询紫嫣之后入职的员工
/*  列子查询
       可以使用in(在什么里边)
    all（一列的每个条件都需要满足）
    any(满足一个即可)
*/
-- 查询计网1211班与1212班所有的员工信息
-- 查询比计网1213班所有人工资都高的人（比任何一个都大这里可以使用all，或者max函数）
-- 第一步：查询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班任意一人工资高的人(不需要比所有人都高)（可以使用any）
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 where (salary,managerid) = (12500,1)
select * from students where (salary,managerid) = (select salary,managerid from students where username ='诸葛亮')
/*表子查询*/
-- 查询与赵云和猪八戒职位和薪资都相同的员工（和上面一样使用in即可）
-- 方法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：直接in效率会快不少
select * from students where (job,salary) in (select  job,salary from students where username='赵云' or username='诸八戒')
-- 查询入职员工1990后之后的员工信息与班级
select u.username,c.ClassName from (select * from students where entrydate > '1990-1-1') u join class c
on u.classid = c.id