上一篇:我在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;