MySQL——基础题练习

#1 查询2张表的数据，使用内连接和左外连接
#INNER JOIN LEFT JOIN
select * from emp;
select * from dept;
select d.dno,d.dname,e.eno,e.ename from emp e INNER JOIN dept d ON e.dno=d.dno;
select d.dno,d.dname,e.eno,e.ename from emp e LEFT JOIN dept d ON e.dno=d.dno;
#2 查询部门101和105部门所有员工工资，并按照降序排列
#相当于是多行多列的子查询，一张虚拟表的查询，可用把查询结果放在from后，作为一张虚拟表
select dno,salary from emp where dno IN(101,105);
select dno,salary from (select dno,salary from emp where dno IN(101,105)) e ORDER BY salary DESC;

#3 查询部门101和105所有员工工资，并且按照工资降序排列，如果部门中工资相同，则按照性别排列（女在前）
select 
select dno,esex ,salary from emp where dno=101 or dno=105 ORDER BY salary DESC ,esex ASC;

#4 查询部门名称为主公的所有员工信息
select dno from dept where dname='主公' ;#单行单列
select * from emp where dno=(select dno from dept where dname='主公');

select *  from emp e inner JOIN  dept d ON e.dno=d.dno where d.dname='主公';
select * from emp e left JOIN  dept d ON e.dno=d.dno where d.dname='主公';

#5 查询102部门的所有员工人数
#看错题目了：谨慎，有个题没写出来
select * from emp where dno;
select COUNT(*) from emp where dno='102';
select COUNT(*) from emp e inner JOIN  dept d ON e.dno=d.dno where d.dno=102;

#6 查询105部门的平均工资
select dno,AVG(salary) from emp where dno='105';
select d.dno,AVG(salary) from emp e inner JOIN  dept d ON e.dno=d.dno where d.dno=105;

#7 查询103部门中工资最高的和最低的
select d.dno,MAX(salary) '最高的',MIN(salary) '最低的' from emp e inner JOIN  dept d ON e.dno=d.dno where d.dno=103;
select dno,MAX(salary) '最高的',MIN(salary) '最低的' from emp where dno=103;

#8 查询每一个部门的工资总和并按照降序排列
select SUM(salary) from emp GROUP BY dno;
select SUM(salary) from(select SUM(salary) from emp GROUP BY dno) e ORDER BY SUM(salary) DESC;

select d.dno,sum(salary) from emp e inner JOIN  dept d ON e.dno=d.dno GROUP BY d.dno;
select d.dno,sum(salary) from emp e inner JOIN  dept d ON e.dno=d.dno GROUP BY d.dno ORDER BY SUM(salary) DESC;

#9 查询每一个部门的工资总和,要求只显示总工资大于90w的部门信息
select dno,SUM(salary) from emp GROUP BY dno;
select dno,SUM(salary) from emp GROUP By dno HAVING SUM(salary)>900000;

select d.dno,sum(salary) from emp e inner JOIN  dept d ON e.dno=d.dno GROUP BY d.dno;
select d.dno,sum(salary) from emp e inner JOIN  dept d ON e.dno=d.dno GROUP BY d.dno HAVING SUM(salary)>900000;

#1、 查询Student表中的所有记录的Sname、Ssex和Class列。
 select * from student;
select sname,ssex,class from student;

#2、 查询教师所有的单位即不重复的Depart列。
select * from teacher;
select DISTINCT depart from teacher;

#3、 查询Student表的所有记录。
select * from student;

#4、 查询Score表中成绩在60到80之间的所有记录。
select * from score;
select * from score where degree>=60 and degree<=80;

#5、 查询Score表中成绩为85，86或88的记录。
select * from score 
select * from score where degree IN(85,86,89);
select degree from score GROUP BY  degree HAVING degree in (85,86,89);

#6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from student;
select * from student where class='95031' or ssex='女';

#7、 以Class降序查询Student表的所有记录。
select * from student;
select * from student ORDER BY class DESC;

#8、 以Cno升序、Degree降序查询Score表的所有记录。
select * from score;
select * from score ORDER BY cno ASC,degree DESC;

#9、 查询“95031”班的学生人数。
select * from student;
select class,count(*) from student where class='95031';