SQL培训

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 对一个集合进行处理