MySQL高级查询

1. 子查询、联合查询

-- select max(score) from stu

-- 子查询
select *
from
(select max(score) from stu) s1;

-- 子查询，查询最高分的学生信息
select * from stu where (select max(score) from stu) = score;

select score,count(*) cc from stu group by score having cc>1 order by cc desc;

select num level,count(*) 人数 from
(select if(score>90,'优秀(>90)',if(score>80,'良好(>80)',if(score>60,'及格','补考'))) num from stu) ss
group by num order by 人数 desc;

# 单行子查询
select dname from dept where deptno = (select deptno from emp where empno=7788);

# 多行子查询
--查询工资大于2000的员工的部门名称
  select dname from dept where deptno =any(select deptno from emp where sal > 2000);
  ANY   ALL
  =ANY 含义就是in   >any  大于最小值   <any  小于最大值
  >all 大于最大值    <all 小于最小值
  
-- 查询大于所在部门的平均工资的员工信息。
-- 关联查询
   1.分组每个部门平均工资
   select * from emp e,(select deptno,avg(sal) avg from emp group by deptno) e1
   where e.deptno = e1.deptno and e.sal > e1.avg
-- 子查询(主查询可以将数据传递给子查询)
   select * from emp e where sal > (select avg(sal) from emp e1 where e1.deptno = e.deptno)
   1.先执行主查询,将deptno传给子查询
   2.子查询拿到deptno，计算查询的结果,返回给主查询
   3.主查询拿到子查询返回的结果执行查询
 
-- 查询薪水大于2000  emp 的部门名称  dept
 
   select dname from dept where deptno in(
   select deptno from emp where sal > 2000);
   
   select dname from dept d where EXISTS(
   select * from emp e where sal > 2000 and d.deptno = e.deptno)
   
#  联合查询
select 1
union 
select 2
union
select 3
union all
select 1
union all
select 2
union all
select 3;

2. 多表查询

select count(*) from stu a,stu b,stu c;

select count(0) from stu a;

select pow(48,3),48*48*48
-- 多表查询
select * from (select 1,2,3) s1,(select 4,5,6) s2;

show tables;

select * from student;
select * from teacher;

-- 学生信息  id name tname tgender tbirthday
select s.id,s.name,t.name,t.gender,t.birthday
from student s,teacher t where s.tid = t.id;

3. 连接查询 left join

#1------笛卡儿积
select * from stu a,stu b,stu c;

#2----- 内连接
-- inner join…on…
select * from emp inner join dept on emp.deptno = dept.deptno

-- inner join…using…
select * from emp
INNER JOIN dept
using(deptno)
select * from student,teacher;

select s.id,s.name,t.name from student s,teacher t where s.tid = t.id;


-- inner join 内连接查询
select s.id,s.name,t.name from student s inner join teacher t on s.tid = t.id;

/*
 
join using 后面接 两张表中都存在的字段 (字段名称 一样)
join on    后面接 两张表中中需要关联的字段 (字段名称不需要一样 a.id = b.id )
 */
select s.id,s.name,t.name from student s inner join teacher t using(id);


#---------------------  自然连接
-- 自然连接(等值连接,表的字段名称必须相同,去除重复行)
select * from emp NATURAL JOIN dept

#-----------------------------  外连接 left right cross
select * from student;
insert into student values(null,'张三丰',1000),(null,'jack',null),(null,'gosling',null)

select s.id 学号,s.name 姓名,ifnull(t.name,'') 授课老师
from student s left join teacher t on s.tid = t.id;

select t.id,t.name,s.name
from student s right join teacher t on s.tid = t.id

#--------------------- full join
select * from student full join teacher;

#---------------------- cross join 笛卡儿积
select count(*) from student cross join teacher;  

select count(*) from teacher
union
select count(*) from student
union
select count(*) from student cross join teacher;


#自连接
-- 查询所有的员工的姓名和上级领导的姓名  emp(内连接)
select e1.ename ename ,e2.ename mgrname from emp e1,emp e2
where e1.mgr=e2.empno
 
select e1.ename ename ,e2.ename mgrname from emp e1
left join emp e2
on e1.mgr=e2.empno
--        ----------------自连接
select c1.id,c1.name,c2.name from ws_goods_category  c1 left join ws_goods_category  c2 
on c2.pid = c1.id;

学生表

成绩表

选课表

老师表

作业：练习连接查询 left join