MySQLDay 07(練習問題)
18786 ワード
/*
*2、
* ： 【 】
*select deptno,avg(sal) as avgsal from emp group by deptno;
* : t,t emp e
* t.deptno=e.deptno and e.sal>t.avgsal
*select
* e.ename,e.sal,t.*
*from
* emp e
*join
* (select deptno,avg(sal) as avgsal from emp group by deptno) t
*on
* t.deptno=e.deptno and e.sal>t.avgsal;
*3、 ( )
*3.1、 （ ）
* ：
*select deptno,avg(sal) as avgsal from emp group by deptno;
* ： t,t salgrade s ， ：t.avgsal between s.losal and s.hisal
*select
* t.*,s.grade
*from
* salgrade s
*join
* (select deptno,avg(sal) as avgsal from emp group by deptno) t
*on
* t.avgsal between s.losal and s.hisal;
*3.2、 ( )
* ：
*select
* e.ename,e.sal,s.grade
*from
* emp e
*join
* salgrade s
*on e.sal between s.losal and s.hisal;
* ： sql ， ，
*select
* e.deptno,avg(s.grade)
*from
* emp e
*join
* salgrade s
*on
* e.sal between s.losal and s.hisal
*group by
* e.deptno;
*4、 （MAX） （ ）
* ， ，
*select sal from emp order by sal desc limit 1;
* ：
*select sal from emp where sal not in(select distinct a.sal from emp a join emp b on a.sal5、
* ：
* ：
*select deptno,avg(sal) as avgsal from emp group by deptno;
* ：
*select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1;
* ：
*select
* deptno,avg(sal) as avgsal
*from
* emp
*group by
* deptno
*having
* avg(sal) = (select avg(sal) as avgsal from emp group by deptno order by avgsal desc limit 1);
* ：max
*select
* deptno,avg(sal) as avgsal
*from
* emp
*group by
* deptno
*having
* avg(sal)=(select max(t.avgsal) from (select avg(sal) from emp group by deptno) t );
*6、
*select
* d.dname,avg(sal) as avgsal
*from
* emp e
*join
* dept d
*on
* e.deptnoo=d.deptno
*group by
* d.dname
*having
* avg(sal) =(select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);
*7、
* ：
*select deptno,avg(sal) as avgsal from emp group by deptno;
*select
* t.dname,t.avgsal,s.grade
*from
* (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
*join
* salgrade s
*on
* t.avgsal between s.losal and s.hisal;
* ：
*select
* max(s.grade)
*from
* (select avg(sal) as avgsal from emp group by deptno) t
*join
* salgrade s
*on
* t.avgsal between s.losal and s.hisal;
* ：
*select
* t.dname,t.avgsal,s.grade
*from
* (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.deptno = d.deptno group by d.dname) t
*join
* salgrade s
*on
* t.avgsal between s.losal and s.hisal
*where
* s.grade =(
*select
* max(s.grade)
*from
* (select avg(sal) as avgsal from emp group by deptno) t
*join
* salgrade s
*on
* t.avgsal between s.losal and s.hisal
*);
*8、 （ mgr ）
* ：
*select * from emp where empno not in (select distinct mgr from emp);
* ， not in null， null
*select * from emp where empno not in(select distinct mgr from emp where magr is not null);
* null
* ：
*select max(sal) from emp where empno not in(select distinct mgr from emp where magr is not null);
* ：
*select ename,sal from emp where sal >(select max(sal) from emp where empno not in(select distinct mgr from emp where magr is not null);)
*
*not in ， in
*select * from emp where empno in(select distict from emp);
*
*case...when...then...when...then..else...end DQL ， java switch...case
*select
* ename,sal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end) as newsal
*from
* emp
* else NULL
*select
* ename,sal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
*from
* emp;
*9、
*select ename,sal from emp order by sal desc limit 5;
*10、
*select ename,sal from emp order by sal desc limit 5,5;
*11、 5
*select ename,hiredate from emp order by hiredate desc limit 5;
*12、
* ：
*select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
* ： ， grade ，
*select
* e.ename,count(*)
*from
* emp e
*join
* salgrade s
*on
* e.sal between s.losal and s.hisal
*group by
* s.grade;
* S（ ），C( ），SC( )
*S(SNO,SNAME) （ ， ）
*C(CNO,CNAME,CTEACHER) （ ， ， ）
*SC(SNO,CNO,SCGRADE) ( ， ， )
* ：
*1、 " "
* ：
*select cno from c where cteacher=' ';
* ： " " cno= sno sno
*select sno from sc where cno=(select cno from c where cteacher=' ')；
* ： sno not in
*select
* sname
*from
* s
*where
* sno not in(select sno from sc where cno=(select cno from c where cteacher=' '));
*
*2、 2 （ 2 ）
* ： 2 ( )
*select
* sc.sno,s.sname
*from
* sc
*join
* s
*on
* sc.sno = s.sno
*where
* sc.grade <60
*group by
* sc.sno,s.sname
*having
* count(*)>=2;
* ：
*select sno,avg(scgrade) as avgscore from sc group by sno;
* ：
*select
* t1.sname,t2.avgscore
*from
* (select
* sc.sno,s.sname
* from
* sc
* join
* s
* on
* sc.sno = s.sno
* where
* sc.grade<60
* group by
* sc.sno,s.sname
* having
* count(*)>=2)t1
*join
* (select sc.sno,avg(sc.scgrade) as avgscore from sc group by sc.sno)t2
*on
* t1.sno=t2.sno;
*3、 1 2
* ： 1
*select sno from sc where cno=1;
* ： 2
*select sno from sc where cno =2;
* ：
*select s.sname,sc.sno from sc join s on sc.sno = s.sno where sc.cno=1 and sc.sno in(select sno from sc where cno=2);
*S
*sno(pk) sname
*--------------
*C
*cno(pk) cname cteacher
*----------------
*sc
*sno cno scgrade(sno+cno ， ， sno ，cno ， 2 )
*-----------------
*/