mysql多表查询与子查询 电脑版发表于:2022/5/3 23:07 代码如下: ``` /* 笛卡尔积 */ 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 ```