习题练习
1、英语不及格
select name, english from table where english < 60;
2、语文80~90
select name, chinese from table where chinese >=80 AND chinese <=90;
select name, chinese from table between 80 and 90;
3、数学成绩58、59、98、99
select name, math from table where math=58 -or math=59 or math=98 or math=99;
select name, math from table where math in(58,59,98,99);
4、姓孙或者孙某
select * from table where name like '孙%';
select * from table where name like '孙_';
5、语文成绩>英语成绩
select name, chinese, english from table where chinese>english;
6、总分<200
select name, math+chinese+english as total from table where math+chinese+english<200;
注意:where后不能跟别名!!!
7、语文成绩>80且不姓孙
select name,chinese from table where chinese > 80 and name not like '孙%';
8、孙某,否则要求总成绩>200 并且 语文<数学 并且 英语成绩>80
select name, maht, chinese, english, ma+ch+eng as total from table
where name like '孙%'
or (ma+ch+eng > 200 and ch < math and english > 80);
9、查询qq为空,查询qq非空
select * from where qq is null;
select * from where qq is not null;
注意:null不能和数字相加,不参与任何运算
10、数学成绩的降序
select math from table order by math desc:
11、数学升序,英语降序,语文升序
select * from table order by math, english desc, chinese;
12、孙同学或曹同学的数学成绩,结果按照降序显示
select name, math from table
where name like '孙%' or name like '曹%'
order by math desc;
13、一页显示三条数据
select * from table limit 3、select * from table limit 0,3
14、第一页
select * from table limit 0, 3、select * from table limit 3 offset 0、
15、第二页
select * from table limit 3, 3、select * from table limit 3 offset 3、
修改语句
16、唐三藏的数学->80
update table set math=80 where name='唐三藏';
17、唐三藏的数学->60,chinese->70
update table set math=60, chinese=70 where name='唐三藏';
18、将总成绩倒数前三的数学成绩+30
select name, ma+ch+eng as total from table order by total limit 3
update table set math=math+30 order by ma+ch+eng limit 3;
19、将所有人的语文成绩*2
update table set chinese=chinese*2;
删除
20、删除唐三藏的数据
delete from table where name='唐三藏';
group by子句
显示每个部门的平均工资和最高工资
select deptno,avg(sal),max(sal) from EMP group by deptno;
显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),job, deptno from EMP group by deptno, job;
统计各个部门的平均工资
select avg(sal) from EMP group by deptno;
having和group by配合使用,对group by结果进行过滤
select avg(sal) as myavg from EMP group by deptno having myavg<2000;