1.模糊查询
like is null where age between 22 and 90
其中包括上下限
in:where sid in (1,3,5,7)
2.聚合函数
拿到sql where 条件过滤后的结果进行聚合运算
①sum()
select sum(score) from score where sid=13;
②max()
select max(score) from score where sid=13;
③min()
select min(score) from score where sid=13;
结果同max()
④count()
求当前查询结果的总记录条数(过滤NULL的值,空值会列入计算)可以对当前的任意字段进行count,从sql性能上讲,统计一般使用主键进行避免使用count(*)
⑤avg()
select avg(score) from score where sid=13;
avg可以对中文进行求平均,不会报错,但是没有什么实际意义
关键字:distinct
去掉查询结果中的重复行
select distinct name from students;
3.分组查询
关键字:group by 字段
select avg(score),sid,cid from score group by cid,sid;
需求:知道每一个学生的每门课程的最终成绩,如果学生有补考,则一门课程有两个成绩
分组条件过滤:
having是对分组之后的数据进行过滤出,过滤的数据是对每个组的数据,而不是每组中的具体数据行的数据,描述数据(原始行)的数据(分组后的行)
select sid,cid,count(score) as 补考次数,avg(score) 平均成绩 from score group by sid,cid having count(score) > 1;
4.关联查询/连接查询
在一条SQL中去查询多张表,然后把多张表中的字段进行显示,多表连接查询需建立在主外键关系基础上
分类:
①内连接/等值连接
select sid,score.cid,cname,score from score,course where score.cid = course.cid;
取别名:
select sid,c.cid,cname,score from score as s,course c where s.cid = c.cid;
inner join:
select sid,c.cid,cname,score from score as s inner join course c on c.cid = s.cid;
三表连接:
select s.sid,name,c.cid,cname,score
from score as s
inner join course c on c.cid = s.cid
inner join student st on st.sid = s.sid;
select s.sid,name,c.cid,cname,score
from score as s,student st,course c
where c.cid = s.cid and s.sid = st.sid;
不加条件则为笛卡尔积:2x3
select sid,name,s.aid,number from address a,students s;
②外连接
左外连接(left join):以左表为基表,返回右表中连接字段相等的行
右外连接(right join):以右表为基表,返回左表中连接字段相同的行
③自连接
自己连接自己
树形结构的表
④union/union all
作用:把两条SQL的结果组合成一个查询结果
要求:两条SQL的查询列数一致,不要求类型一致
union:去重复行
union all:不去重复行
select * from course union all select * from course;
select * from course union select * from course;
5.子查询
一个查询中包含另外一个查询,父查询和子查询
每个学生每一门课程的最终分数—>每个学生的总分?
select sid, sum(score) from
(select sid,cid,avg(score) score
from score group by sid,cid) as t
group by sid;
①比较运算符(>、<、=、<>)
需求:查询出年龄比张三大的所有学生
A、获取王五年龄 :33
B、age>33
select * from student where age > (select age from student where name = '王五');
子查询的结果只有一个字段一个值
②in / not in
A in (B)
A字段的值只要在B子查询结果中存在即可
select * from student where age in (select age from student
where name = '王总' or name = '王五' or name = '丰王') and name != '王总' and name != '王五' and name<>'丰王';
查询成绩刚好及格的学生信息(sid,name,score)
select s.sid,name from student s , score sc where s.sid = sc.sid and score = 60;
select sid,name from student where sid in (select sid from score where score=60);
select * from student where name like ‘张%’ and age > 20 and sex=’男’
③exists/not exists
where filed exists (B)
当exists子查询有数据行返回时则条件为真
select * from student where exists (select * from course where 1!=1);
相关子查询:
select * from student s where
exists (select scid from score sc where cid = 1 and sc.sid = s.sid);
④from后的子查询
把子查询结果作为父查询的源表
select scid,sid from (select score,cid from score where cid > 5) as t;
⑤ any、in、some
ANY:只能接在比较运算符后,ANY指的是任意一个值,所以>ANY即表示大于最小的,In 是 =any 的别名
Some 和any 是同等效果
⑥ALL
ALL:只能接在比较运算符后,ALL指的是所有值,所有>ALL是指大于最大值
6.相关子查询
子查询分为相关子查询和非相关子查询
非相关(嵌套子查询):子查询和父查询是没有关系
相关:子查询的执行需要依赖于父查询传入的数据作为条件执行
需求:查询出成绩大于当前课程平均分的学生信息
A、求出指定课程的平均分 x
B、select * from student where score > x
select distinct s.sid ,name from score s,student st where s.score >
(select avg(score) from score sc where sc.cid = s.cid) and s.sid=st.sid;