数据库SQL例题

SQL补充例题

例：比较学号为9512101和9512102两位同学的c02课的成绩，结果显示成绩高的同学的学号、成绩。

select sno, grade from sc

where grade=(

SELECT max(grade)

from sc

where sno in ('9512101' ,'9512102') and cno='c02')

select sno, grade

from sc

where sno in ('9512101' ,'9512102') and cno='c02'

and grade=(SELECT max(grade)

from sc

where sno in ('9512101' ,'9512102')

and cno='c02')

USE college

GO

select * from (select sno, grade

from sc

where sno in ('9512101' ,'9512102') and cno='c02') sc1

join

(SELECT sno, max(grade) as grade

from sc

where sno in ('9512101' ,'9512102') and cno='c02'

group by sno ) sc2

on sc1.sno=sc2.sno

例：查询学号为9512101的c02课的成绩，结果显示成绩按优、良、中、及格、不及格。

DECLARE @grade_str char(10), @grade tinyint

SELECT @grade=grade

from sc where sno='9512101' and cno='c02'

set @grade_str = CASE

WHEN @grade>=90 THEN WHEN @grade>=80 THEN WHEN @grade>=70 THEN WHEN @grade>=60 THEN ELSE '不及格'

'优'

良'

'中'

及格'

' '

END

print @grade_str

例 应用搜索CASE语句查询性别情况。

USE college

GO

SELECT sno as 学号,sname as 姓名,

CASE ssex

WHEN '男' THEN '男性'

WHEN '女' THEN '女性'

ELSE ' '

end as 性别

from student

ORDER BY sno

例 用WHILE CONTINUE语句，用加法实现3x6

DECLARE @i int, @total int

Set @i=0

set @total=0

WHILE @i<3

Begin

set @total=@total +6

set @i=@i +1

end

Print @total

练：等到 ‘12：00：00’时刻显示 hellow

WAITFOR TIME '12:00:00'

Print 'hellow'

练：延时10秒显示 hellow

WAITFOR DELAY '00:00:10'

Print ‘hellow’

函数补充例题

例1 显示服务器的当前系统日期和时间

Print GETDATE ()

例2 下例从日期 03/12/1998 中返回年份数。

SELECT 'Year Number' = YEAR('03/12/1998')

GO

例3 下例从日期 03/12/1998 中返回月份数。

SELECT 'Year Number' = MONTH('03/12/1998')

GO

（1）内嵌表值函数的定义

例：建立一个查询性别姓名的函数

CREATE FUNCTION mf_sname_sex (@ssex AS varchar(12))

RETURNS table

as

RETURN (select sname, ssex from student where ssex=@ssex)

索引补充例题

例：为sc表的主码sno，cno建立聚集索引sc_sno_cno

CREATE UNIQUE CLUSTERED

INDEX sc_sno_cno

ON sc (sno，cno)

例：为student的学生姓名sname建立非聚集索引student_sname

CREATE NONCLUSTERED

INDEX stucent_name

ON student (sname)

例：为student的系名称sdept建立非聚集索引sdept

CREATE INDEX sdept

ON student (sdept)