（十四）常见SQL语句写法

转自：https://zhuanlan.zhihu.com/p/38354000

table：

Student(S#,Sname,,birth,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表 

 

一、创建表（create）

create table Student(

   S# int  auto_increment,

   Sname varchar(10),

   Ssex  char(1),

   primary key (S#)

  );

二、向表中插入数据（insert）

部分字段：insert into Student (Sname,sSex) values ('李明','男');

全部字段:insert into Student values (1,'李明','男');

三、修改数据（update）

update  Student set Sname='张三' where S#=1;

四、删除记录（delete）

delete from Student where S#=1;

五、查找数据（select）

①简单查询

• 查询姓“猴”的学生名单

select * from Student where Sname like '猴%' ;   %:任意字符，任意长度

• 查询姓名中最后一个字时“猴”的学生名单

select * from Student where Sname like '%猴' ;

• 查询姓名中带"猴"的学生名单

select * from Student where Sname like '%猴%' ;

②汇总分析

• 查询课程编号为"002"的总成绩

select sum(score) from SC where C#='002';

• 查询选了课程的学生总人数

（一个学生可以修多门课程，所以会有多个成绩）

select count(distinct S#) from SC;

③简单分组

• 查询各科成绩最高和最低的分

select C#,max(score),min(score)   from  SC  group by C#;

• 查询每门课程被选修的学生数

select C#,count(S#) from SC group by C#;

• 查询男、女生人数

select Ssex,count(S#) from Student group by Ssex;

④复杂分组 （having字句可以让我们筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而 having子句在聚合后对组记录进行筛选。）

• 查询平均成绩大于60分学生的学号和平均成绩

思路--->选课表：按学号分组，分组后对平均成绩大于60筛选

select   S#,average(score)

from  SC group by S# having average(score)>60;

• 查询至少选修两门课程的学生学号

select S# from SC group by S# having count(C#)>=2;

• 查询同名同姓的学生名单并统计同名人数

select  Sname,count(*) from Student group by Sname having count(*)>=2;（注意存在同名同姓，则说明人数>2）

• 查询不及格的课程并按课程号从大到小排序

select score,C# from SC where score<60  order by C# desc;（注意要写按照什么进行排序）

• 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select C#,average(score) from SC order by average(score) asc, C# desc; (注意多个排序写法)

• 检索课程编号为“0004”且分数小于60的学生学号，结果按按分数降序排列

select s# ,score from SC where C#='0004' and scroe<60 order by score desc;

• 统计每门课程的学生选修人数(超过2人的课程才统计).要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select C#,count(S#) from SC group by C# having count(S#)>2 order by count(S#) desc,C# asc;

• 查询两门以上不及格课程的同学的学号及其平均成绩

select S#,average(score) from SC where score<60 group by C#  having count(*)>2;

⑤复杂查询

• 查询所有课程成绩小于60分学生的学号、姓名

select S#,Sname from Student where  S# in(

(select S# from SC where score<60));

• 查询没有学全部课程的学生的学号、姓名

select S#,Sname from Student where Sname in(

select S# from SC group by S# having count(C#) <(select count(*) from C));

• 查询出只选修了两门课程的全部学生的学号和姓名

select S#,Sname from Student where Sname in(

select S# from SC group by S# having count(C#) =2);

⑥多表查询

• 查询所有学生的学号、姓名、选课数、总成绩

select Student.S#,Student.Sname,a.count(C#) as SC_count,a.sum(score) as sum_score

from (select S#,count(C#),sum(score) from SC group by S#) as a,Student

where a.S#=Student.S#;

• 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select Student.S#,Student.Sname,a.average(score) as average from

(select S# ,average(score) from SC group by S# having average(score)>85)as a,Student

where a.S#=Student.S#;

• 查询学生的选课情况：学号，姓名，课程号，课程名称

select S#,Sname,C#,Cname

from Student  inner join SC on Student.S#=SC.S#

inner join C on  Student.C# =C.C#;