我在b站学数据库 （五）：DQL练习

DQL基本查询操作练习

练习一： 数据准备： 1、查询表中所有学生信息

select * 
from student;

2、查询表中所有学生的姓名和对应的英语成绩

select name,english 
from student;

3、过滤掉重复数据

selcet distct * 
from student;

4、统计每个学生的总分

select name,(chinese + english + math) as total_score 
from student;

5、在所有学生的总分上加上10分特长分

select name,(chinese + english + math +10) as total_score 
from student;

6、使用别名表示学生分数

select name,chinese语文成绩,english英语成绩,math数学成绩 
from student;

7、查询英语成绩大于90的同学

select * 
from student 
where english > 90;

8、查询总分大于200的所有同学

select * 
from student
where (chinese + english + math)> 200;

9、查询英语成绩在80-90之间的同学

select * 
from student
where english between 80 and 90;
或者
select * 
from student
where english >= 80 and <= 90;

10、查询英语成绩不在80-90之间的同学

第一种
select * 
from student
where not (english between 80 and 90);
第二种
select * 
from student
where english not between 80 and 90;
第三种
select * 
from student
where not (english >= 80 and <=90);
第四种
select * 
from student
where english < 80 || english > 90;

11、查询数学分数为89，90，91的同学

select * 
from student
where math in(89,90,91);

12、查询所有姓李的学生英语成绩

select name,english
from student
where name like 李%;

13、查询数学分80且语文分80的同学

select * 
from student
where math = 80 and chinese = 80;

14、查询英语80或者总分200的同学

select * 
from student
where english = 80 and (chinese + english + math) = 200;

15、对数学成绩降序排序后输出

select *
from stdent 
order by math desc;

16、对总分排序后输出，然后再按从高到低的顺序输出

select *
from stdent 
order by (chinese + english + math) desc;

17、对姓李的学生成绩排序输出

select *
from student 
where name like 李% 
order by (chinese + english + math) desc;

18、查询男生和女生分别有多少人，并将人数降序排序输出,查询出人数大于4的性别人数

select gender,count(*) as toatl-cnt
from student 
group by gender
having total_cnt > 4
order by total_cnt desc

练习二 数据准备： 1、按员工 编号升序排列不在10号部门工作的员工信息

select * 
from emp
where deptno != 10
order by empno desc;

2、查询姓名第二个字母不是’A’且薪水大于1000元的员工信息，按年薪降序排列

select *
from emp
where ename not like _A% and sal >1000
order by (12 * sal + innull(comm,0))
#ifnull(comm,0) 如果comm的值为null，则当作0，不为null，则还是原来的值

3、求每个部门的平均薪水

第一种
select deptno,avg(sal)
from emp
group by deptno;
第二种
select deptno,avg(sal) as avg_sal 
from emp
group by deptno
order by avg_sal desc;

4、求每个部门的最高薪水

select deptno,max(sal) max_avg 
from emp 
group by deptno;

5、求每个部门每个岗位的最高薪水

select deptno,job,max(sal)
from emp
group by deptno,job
order by deptno;

6、求平均薪水大于2000的部门编号

select deptno,avg(sal) avg_sal
from emp
group by deptno
having avg_sal >2000;

7、将部门平均薪水大于1500的部门编号列出来，按部门平均薪水降序排列

select deptno,avg(sal) avg_sal
from emp
group by deptno
having avg_sal >1500
order by avg_sal desc;

8、选择公司中有奖金的员工姓名，工资

select *
from emp
where comm is not null;

9、查询员工最高工资和最低工资的差距

select max(sal) - min(sal) 薪资差距
from emp;