DDL
DML
DCL
18. 查询名字中含有「风」字的学生信息
select * from student where sname like '%风%';
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select s.*,sc.cid,sc.score from student s join sc on s.sid=sc.sid where sc.cid ='01' and score<60 order by score desc;
5. 查询「李」姓老师的数量
select count(*) from teacher where tname like '李%';
3. 查询在 SC 表存在成绩的学生信息
select distinct s.* from student s join sc on s.sid = sc.sid where score is not null;
select * from student where sid in (select sid from sc where score is not null);--嵌套子查询自动去掉重复的
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.sid,sname,avg(score) from student s join sc on s.sid = sc.sid group by s.sid,sname having avg(score)>=60;
select s.sid,s.sname,avg_score from student s,(select sid,avg(score) as avg_score from sc group by sid having avg(score)>=60) ss where s.sid=ss.sid;
----------------------------------------------------------------------------------------------------------------------------
1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select s.*,a.score as score_01,b.score as score_02 from student s,(select sid,score from sc where cid='01') a,(select sid,score from sc where cid='02') b
where a.sid=b.sid and a.score > b.score and s.sid=a.sid;
select * from sc;
聚合函数：(有关集合) 对多个集合进行处理
聚集函数：max min sum avg count 对一个集合进行处理
