50个常用sql语句实例（学生表 - 课程表 - 成绩表 - 教师表）

--或者是select * from student where sno = '95001'

12： select * from student where sno like '95001'：--like用于字符串匹配

--百分号匹配多个字符，包括0个

13： select Sname,Sno,Sage from student where sname like '林%'

-- 一个下划线匹配单个字符

14：select sname from student where sname like '欧阳_'

15：select sname,sno from student where sname like '_燕%'

16：select sname from student where sname not like '刘%' 17：

select Ccredit from course where cname like 'DB\\_DESIGN' escape'\\'

--注意：这里不用使用 = null

18：select sno,cno from sc where grade is null

19：select sno,cno from sc where grade is not null

20: select sname from student where sdept='CS' and sage<20

21：select sno,grade from sc where cno=3 order by grade desc

22：select * from student order by sdept,sage desc

23:：select count(*) from student

24：select count(distinct sno) from sc

25: select avg(grade) from sc where cno='1'

26: select max(grade) from sc where cno='1'

group by 按照它后面的列值进行分组，相同的值被分在一组

27: select cno,count(sno) from sc group by cno

--having后面的条件是根据group by 分组后的结果再进行筛选，最后只给出满足条件的分组

--where筛选的对象是整个表，而having则是分组

28: select sno from sc group by sno having count(sno)>=3

29：select a.sname,b.cno from student a ,sc b where a.sno=b.sno

或者

select a.sname,b.cno from student a left outer join sc b

on a.sno=b.sno where b.cno is not null

--自身连接

30：select a.Cno,b.Cpno from course a,course b where a.Cpno=b.Cno

--31：

select student.sno,student.sname

from student,sc

where student.sno=sc.sno and

sc.cno='2' and

sc.grade>=90

--32：

select student.sno,student.sname,course.cname,sc.grade

from (student left join sc on student.sno=sc.sno)

left join course on sc.cno=course.cno

或者：

--忽略cname和grade都为null的行

Select student.sno,sname,cname,grade

From student,sc,course

Where student.sno=sc.sno and sc.cno=course.cno

--33：

select sname from student

where sdept=(select sdept from student where sname='林燕芳')

--34：

select sname,sage

from student

where sage

select sage from student

where sdept='is'

) and sdept<>'IS'

--35：利用 exists的查询

--exists根据是否存在行返回true/false

--如果要查询没有选修1号课程的学生姓名，只要使用NOT Exists即可

select *

from student

where exists(

select 1 from sc

where student.sno=sc.sno and cno='1' )

或者你可以使用连接查询

select * from student left join sc on student.sno=sc.sno

where sc.cno='1'

--36：

declare @temp1 int

declare @temp2 int

select @temp1=count(*) from course

select @temp2=sno from sc group by sno

having count(sno)=@temp1

select sname from student where sno in (@temp2)

或者

--就是转换成查询没有一门课程没有选修的学生姓名

--如果把两个not都去掉就是查询所有有选修课程的学生

Select sname from student where not exists(

Select 1 from course where not exists(

Select 1 from sc where student.sno=sc.sno and course.cno=sc.cno ) )

--37：

--同样要进行转换：查询这样的学生，没有95002选修的课程而学生X没有选修的

Select distinct sno

From sc scx