常用sql语句锦集

首先创建3个表，具体表结构如下：（因为各个数据库的脚本都不一样，所以在此就不贴出建表的脚本了）

（以下的sql语句可能有多种写法，'--'后为一种写法）

表1：学生表（student）：如下图

 

表2：课程表（course）：如下图

表3：选课表（sc）：如下图

以下sql语句都是围绕上面3个表进行的

 

常用“查询”

 

1.查询缺少成绩的学生的学号和相应的课程号

--select Sno ,Cno from sc where Grade=null
--select Sno ,Cno from sc where Grade is null

 

2.查询选修了3号课程的学生的学号级成绩，查询结果按成绩降序排列
--select Sno, Grade from sc where Cno=3 order by Grade desc

3.查询选修了3号课程的学生的学号级成绩，查询结果按成绩升序排列
--select Sno, Grade from sc where Cno=3 order by Grade asc

4.查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄升序排列
--select * from student order by Sdept asc ,Sage asc

5.查询学生总人数
--select count(*) from student

6.查询学生表里的总年龄
--select sum(Sage) from student

7.查询选了课程的学生人数
--select count(distinct Sno) from sc

8.统计选了3门及以上课程的学生人数#
--select count(distinct Sno) from sc group by Sno having count(cno)>=3  

9.计算2号课程的学生的平均成绩
--select AVG(Grade) AS avg_grade from sc where cno=2

10.查询1号课程的最高分
--select max(grade)as max_grade from sc where cno=1

11.查各个课程号及相应选课人数#
--select count(Sno)as renshu,cno from sc group by cno

12.查询选修两门及以上课程的学生姓名#
--select Sname from student right join sc on student.sno=sc.sno group by student.Sname having count(sc.cno)>=3
--select Sname from student inner join (select sc.sno from sc group by sc.sno having count(sc.cno)>=3) as studentNo on student.sno = studentNo.sno

13.查询每一门课程的间接选修课#
--select c1.cname ,c2.cname from course as c1, course as c2 where  c2.cpno=c1.cno
--select c1.cname ,c2.cname from course as c1 inner join course as c2 on  c2.cpno=c1.cno

14.查询与刘晨在同一个系的学生
--select Sname from Student where Sdept=(select Sdept from student where Sname='刘晨')   （非相关子查询）
--select Sname from Student stu1 where exists (select sdept from student stu2 where stu2.sname='刘晨' and stu1.sdept = stu2.sdept)     （相关子查询）

15.查询每个学生超过他选修课程平均成绩的课程号
--select s1.sno,s1.cno from sc s1 where s1.grade > (select avg(s2.grade) from sc s2 where s1.sno=s2.sno)

16.查询其他系中比计算机科学系（CS）某一学生年龄小的学生姓名和年龄
--select s1.Sname, s1.Sage from student s1 where s1.sage<(select max(s2.Sage) from student s2 where s2.Sdept='CS')  and s1.sdept <> 'cs'
--select s1.Sname, s1.Sage from student s1 where s1.sage< any (select s2.Sage from student s2 where s2.Sdept='CS')  and s1.sdept <> 'cs'

17.查询没有选修1号课程的学生姓名
--select distinct Sname from student ,sc where sc.cno<>1 and student.sno=sc.sno
--select distinct Sname from student right join sc on student.sno=sc.sno where sc.cno<>1
--select Sname from student where exists (select * from sc where sc.cno<>1 and student.sno=sc.sno)

18.查询选修了全部课程的学生姓名（没有一门课程是他不选修的）
--select Sname from student where not exists (select * from course where not exists (select * from sc where  sno=student.sno and cno=course.cno))

 

常用“增删改”

19.将相同表结构的表导入数据（向student1里导入student表里的数据）
--insert into student1 (sno) select sno from student

20.删除student1表里的所有数据
--delete from student1

21.查询计算机系(cs)的学生及年龄不大于19岁的学生
--select * from student where Sdept='CS' or Sage<=19
--select * from student where Sdept='CS' union select * from student where Sage<=19   

 (用union如果前后结果集中有相同的记录它只显示1条，如果union后面加all则是将前后两个结果集直接相加，不区分是否有相同数据)

22.查询计算机系(cs)的学生及年龄不大于19岁的学生的交集
--select * from student where Sdept='CS' and Sage<=19
--select * from student where Sdept='CS' intersect select * from student where Sage<=19    (intersect关键字是求前后结果集的交集)

23.查询计算机系(cs)的学生及年龄不大于19岁的学生的差集
--select * from student where Sdept='CS' and Sage>19
--select * from student where Sdept='CS' except select * from stuc.sno) = 'CS'     (相关子查询)

24.将学生200215121的年龄改为22岁
--update  student set Sage=22 where Sno=200215121

25.将所有学生的年龄增加1岁
--update student set sage=sage+1

26.查询计算机系全体学生的成绩
--select sc.grade,student.sdept from sc left join student on sc.sno = student.sno where student.sdept = 'cs'    (外连接)
--select c.grade from student s,sc c where s.sno=c.sno and s.sdept='cs'   (自然连接)
--select sc.grade from sc where sc.sno in (select student.sno from student where student.sdept='cs')      (非相关子查询)
--select grade from sc where  (select sdept from student where student.sno = sc.sno) = 'CS'     (相关子查询)

27.将计算机系全体学生的成绩归零
--update sc set grade=0 where sc.sno in (select student.sno from student where student.sdept='cs')       (非相关子查询)
--update sc set grade=100 where  (select sdept from student where student.sno = sc.sno) = 'CS'      (相关子查询)

28.删除学号为200215121的记录
--delete from student  where sno=200215121

29.删除计算机系所有学生的选课记录
--delete from sc where sno in (select sno from student where sdept='cs')
--delete from sc,student where sc.sno=student.sno and student.sdept='cs'
--delete from sc where (select sdept from student where student.sno = sc.sno)='cs'