模糊查询 like;
select id,name from info where name like '张'; #查询info表里面姓张的人
select i.name,s.grade from student i inner join score s on i.id=s,sid where name like '刘%'; #查询所有姓刘的学生成绩
模糊查询 is null
select name,grade from score where grade is null; #查询score表中成绩为空的人
模糊查询 between
select age,name from student where between 18 and 22; #查询student表中年龄为18-22岁的人
数据查询排序 order by
1.按单列排序
select id,grade from score order by grade desc; #单列排序 将score表中的成绩从高到低排序
2.按多列排序
select id,grade from score order by grade,id asc; #多列排序 将score表中的成绩从低到高进行排序,成绩相同的按照id排序
分组查询 group by (主要是配合聚合函数使用)
select id,sum(grade) from score group by grade having max(grade)>300; #查询总成绩大于300的人
EXISTS 子查询
select distinct grade from score where exitst (select 1 from score where grade>60) order by grade limit 3;
采用EXISTS检测是否有人考试成绩达到60分以上,如果有,使用SELECT语句按成绩从高到低排序,显示前3名学员学号和成绩
NOT EXISTS 子查询
select age from student a where not exists (select 1 from student b where a.age<b.age); #求最大年龄