从学生表Student(Sno,Sname,Ssex,Sage,Sdept)中查询出全体学生的学号与姓名
1.
查询全体学生的详细记录
2.
显示前 5条纪录
3.
显示前 50%条纪录
4.
查询所有年龄在17岁以下的学生姓名及其年龄。
5.
某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。(成绩为null)
6.
查所有有成绩的学生学号和课程号
7.
查询学生的所有信息,按学号的降序排列
1.select * from student
2.select top 5 * from student
3.select top 50 percent * from student
4.select sname,sage from student where sage<17
5.select sno,cno from sc where score is NULL
6.select sno,cno from sc where score is not NULL
7.select * from student order by sno desc
8 查询选修了课程的学生学号
9.
查全体学生的姓名及其出生年份,显示两列:姓名、出生年份
10.
查询年龄在15~17岁(包括15岁和17岁)之间的学生的姓名、年龄。
11.
查询年龄不在15~17岁之间的学生姓名、系别和年龄。
12.
查询年龄不在15~17岁之间的男生姓名、系别和年龄。
13.
将上
查询的结果插入一个新表中。
8.select distinct sno from sc
9.select sname,2010-sage as 出生年份 from student
10.select sname,sage from student where sage between 15 and 17
11.select sname,sdept,sage from student where sage is not between 15 and 17
12.select sname,sdept,sage from student where ssex='男' and sage is not between 15 and 17
13.select sname,sdept,sage into newtable from student where ssex='男' and sage is not between 15 and 17
1.
查询学生总人数。
2.
查询选修了课程的学生人数。
3.
计算1001号课程的学生平均成绩。
4.
查询选修1号课程的学生最高分数。
5.
求各个课程号及相应的选课人数。(group by)
6.
查询选修了1门以上课程的学生学号。(having)
7.
请说明union的作用。
1.select count(*) from student
2.select count(distinct sno) as 人数 from sc
3.select avg(score) as 平均成绩 from sc where cno =1001
4.select max(score) as 最高分数 from sc where cno =1
5.select cno,count(*) as 选课人数 from sc group by cno
6.select cno, count(*) as 选课人数 from sc group by cno having count(*)>1
1.
查询学生总人数。
2.
查询选修了课程的学生人数。
3.
计算1001号课程的学生平均成绩。
4.
查询选修1001号课程的学生最高分数。
5.
求各个课程号及相应的选课人数。(group by)
6.
查询选修了1门以上课程的学生学号。(having)
7.
请说明union的作用。
1.select count(*) as 总人数 from student
>2.select count(distinct sno) as 总人数 from sc
>3.select avg(score) as 平均成绩from sc where cno=1001 >4.select max(score) from sc where cno=1001
>5.select cno,count(*)as 人数,max(score ) from sc group by cno
>6.select sno from sc group by sno having count(cno)>1
>7.在列数和列的顺序相同且数据类型相同的前提下,将多个select语句返回的结果组合到同一个结果当中。
>请举例说明With cube和With rollup的作用。
select cno,cname,count(cno)as 人数 from course group by cno,cname with cube说明每一个分组统计的总数
select cno,cname,count(cno)as 人数from course group by cno,cname with rollup说明每一个小分组的统计总数
>3.
使用compute 汇总所有学生的成绩平均分。
select sno,cno,score from sc compute avg(score)
统计所有内容,求出平均成绩
>4.
使用compute by汇总每个人的选修课程数。
select * from sc order by sno,cno compute count(cno) by sno按SNO,CNO分组进行统计
>使用ANSI连接和sql server 连接两种方式完成:
>1.
查询每个学生的学号、姓名及其选修课程的课程号、成绩。
使用ANSI:select student.sno,sname,sc.sno,cno from student inner join sc on student.sno=sc.sno
使用sql server:select student.sno,sname,sc.sno,cno from student,sc where student.sno=sc.sno
>2.
查询出'101'号学生选修的课程的课程名称和学分
使用ANSI:select cname,ccredit from course inner join sc on
course.cno=sc.cno
使用sql server:select cname,ccredit from course,sc where course.cno=sc.cno and sc.sno='101'
查询出选修‘1002’号课程的学生的学号、姓名。
使用ANSI select student.sno,sname from student inner join sc on student.sno=sc.sno and sc.cno='1002'
使用sql server:select student.sno,sname from student,sc where student.sno=sc.sno and sc.cno='1002'
--查询与“name2”在同一个系学习的学生信息。
select *from student where sdept in (select sdept from student where sname='name2')and sname!='name2'
查男女各有多少人 select ssex ,count(*)as 人数 from student group by ssex
按降序排列:group by是分组 order by 是排序
select ssex ,count(*)as 人数 from student group by ssex order by ssex desc
选课多余2的人数select cno,count(*)from sc group by cno having count(*)>'2'
查询出‘101’号学生选修的课程的课程名称和学分。
使用sql server:select cname,ccredit from sc,course where sno='101' and sc.cno=course.cno
使用ANSI:select cname,ccredit from course inner join sc on sc.cno=course.cno and sno='101'
嵌套查询:select cname,ccredit from course where cno in (select cno from sc where sno='101')
exists查询:select cname,ccredit from course where exists(select * from sc where cno=course.cno and sno='101')
--查询选修课程号为“1001”的所有男生的姓名和该科成绩。
sql server:select sname, score from student,sc where student.sno=sc.sno and sc.cno='1001'and student.ssex='男'
ANSI:select sname,score from student inner join sc on student.sno=sc.sno and student.ssex='男'and sc.cno='1001'
--查询出‘101’号学生选修的课程的学分总和。
--使用sql server:select sum(ccredit) as 总学分from sc,course where sc.sno='101'and sc.cno=course.cno
--使用ANSI:select sum(ccredit) as 总学分 from course inner join sc on sc.sno='101'and sc.cno=course.cno
--嵌套查询:
select sum(ccredit) as 总学分 from course where cno in(select cno from sc where sno='101')
--exists:select sum(ccredit) from coursewhere exists(select cno from sc where sno='101' and sc.cno=course.cno)
--查询出每个学生已经修过的总学分。
sql server:select sno,sum(ccredit) as 总学分 from sc,course where course.cno=sc.cno group by sc.sno order by sum(ccredit) desc
--使用ANSI:select sno,sum(ccredit) as 总学分 from scinner join course on course.cno=sc.cno group by sc.sno
order by sum(ccredit) desc
--查询出选修‘c语言’的学生的学号、姓名和成绩
--使用sql server:
select student.sno,sname,score
from student,sc,course
where course.cname='c'
and student.sno=sc.sno
and sc.cno=course.cno
--查询出选修了学分是4的课程的学号
--使用sql server:
select course.cno,sno
from sc,course
where sc.cno=course.cno
and course.ccredit='4'
--查询出选修了学分是4的课程的姓名
--使用sql server:
select cname,sno
from sc,course
where sc.cno=course.cno
and course.ccredit='4'
--查询出没有选修学分是4的课程的学号
--使用sql server:
select sno ,course.cno
from sc,course
where sc.cno=course.cno
and course.ccredit<>'4'
1.将一个新生记录(学号:111;姓名:陈冬;性别:男;所在系:IS;年龄:18岁)插入到Student表中。
insert into
student (sno,sname,ssex,sage,sdept)
values ('111','陈冬','男','18','IS')
2.插入一条选课记录(sno: '111',cno:'1111 '),新插入的记录在score列上将会取空值。能插入吗?
存在外键则不能
3.student数据库中,有一个表Deptage(Sdept,Avgage)用来存放每个系的学生平均成绩,但还没有数据。请你对每一个系求学生的平均年龄,并把结果存入表Deptage。
select sdept,avg(sage)as avgage
into avger
from student
group by sdept
任务2(update):
1.将学生'101'的年龄改为19岁。
update student
set sage='19'
where sno='101'
2.将所有学生的年龄增加1岁。
update student
set sage=sage+1
3.将信息系全体学生的成绩置零。
update sc
set score=0
where sno in (
select sno from student
where sdept='信息')
任务3(delete):
1.删除学号为'102'的学生选课记录。
delete sc
where sno='102'
2.删除所有的学生选课记录。
delete sc
3.删除信息系所有学生的选课记录。
delete sc
where sno in(
select sno
from student
where sdept='信息')
4.你能删除student表中学号为'101'的学生记录吗?问什么?
不能删除
DELETE 语句与 REFERENCE 约
束"FK_sc_student1"冲突。该冲突发生于数据
库"students",表"dbo.sc", column 'sno'。
任务1:回答以下问题:
1.
什么是视图
2.
使用视图的优点
3.
创建视图的注意事项
步骤1:每个人独立完成,15分钟。
步骤2:提问学生任务完成情况,5分钟
步骤3:教师补充点评,5分钟。
任务2:创建一个视图view1,查询选修课程号为"1001"的所有女生的姓名和该科成绩。
create view view1
as
select sname,score from student inner join sc on student.sno=sc.sno
where ssex='女' and cno='1001'
任务3:完成以下操作:创建一个视图v1,在视图中包含sc表中及格的选课信息。
create view v1
as select *from sc
where score>=60
1.
插入数据记录:将这两条记录插入视图v1 中('105','1001',69)('105','1002',50)。观察视图和表的记录变化。
insert into v1
values ('105','1001',69)
insert into v1
values ('105','1002',50)
2.
修改数据记录:修改视图v1,将('105','1001',69)的成绩改为99
UPDATE v1
set score='99'
where sno='105' and cno='1001'。
3.
删除数据记录 :修改视图v1,删除105号学生的选课记录。
delete from v1
where sno='105'
步骤1:每个人独立完成,5分钟。
步骤2:与你的同组搭档得出一个小组结果,5分钟。
步骤3:学生介绍任务完成情况,3分钟。
步骤4:教师补充点评,2分钟。
任务4:创建视图v1,
create view v1 as
select student.sno,sname,cno,score
from student inner join sc
on student.sno=sc.sno
完成:
1.
将满足sno='101' and cno='1003'的记录sname改为n1.
UPDATE v2
set sname='n1'
where sno='101' and cno='1003'
2.
将满足sno='101' and cno='1004'的记录sname改为n1,cno改为1002.
不行
3.
将sno='109',sname='name9'的记录插入v1.
4.
将sno='110',sname='nam10',score=99的记录插入v1,能够正确执行吗?
5.
删除sno='101' and cno='1004'的记录,可以吗?
不可以,因为关系到多个基表
任务1:通过学习教材及
回答以下问题:
1.
标识符命名规则
答:标识符分为标准标识符和分隔标识符两大类
2.
如何注释
3.
局部变量的声明、赋值方式
4.
全局变量的特点,使用。
5.
声明一个变量x并为x赋值,查询成绩大于x(x是局部变量)的学生学号和选修的课程号.
declare @x int
set @x=90
select sno,cno,score from sc where score>=@x
6.
声明一个变量avgscore,并将sc表中成绩的平均分赋值给变量avgscore。你有几种赋值方式?
declare @avgscore int
set @avgscore=(select avg(score) from sc)
select @avgscore 平均成绩
go
5.
声明一个变量x并为x赋值,查询成绩大于x(x是局部变量)的学生学号和选修的课程号.
declare @x int
set @x=60
select sno,cno from sc where score>@x
6.
声明一个变量avgscore,并将sc表中成绩的平均分赋值给变量avgscore。你有几种赋值方式?
declare @avgscore int
set @avgscore=(select avg(score) from sc)
print @avgscore
select @avgscore=avg(score) from sc
print @avgscore
>>--no是student表的identity列
>>insert into student(sno,sname) values('110','name10')
>>select @@identity identity1
>>go
>>
>>
>>select * from student where sno='101' or sno='102'
>>go
>>select @@rowcount
>>go
>>
>>
>>
>>use students
>>go
>>select @@trancount as trancount1,@@servername servername1,@@version version1,@@identity identity1,@@language language1
>>go
>>--no是student表的identity列
>>insert into student(sno,sname) values('110','name10')
>>select @@identity identity1
1.(if)如果sc表中所有学生的平均成绩大于80,显示'成绩优异',并显示出成绩大于80的选课记录。否则显示学生成绩一般。
declare @avgscore int
set @avgscore=(select avg(score) from sc where score is not NULL)
if @avgscore>=80
begin
print '成绩优异'
select * from sc where score>=80
end
else
begin
print '成绩一般'
end
--在student中查询学生信息,如果存在此生,则将学生的信息输出;如果不存在此生,则输出数据库中没有该生信息
declare @name nchar(8)
set @name='name1'
if exists(select * from student where sname=@name)
begin
print ''
select *from student where sname=@name
end
else
begin
print'数据库中没有该生信息。'
end
--3.(while)使用while循环,对course表中的学分总和进行检查,
--若学分总和小于50,对每门课程学分加1,直到学分总和不小于50为止。
while ((select sum(ccredit) from course)<50)
begin
update course
set ccredit=ccredit+1
if(select sum(ccredit) from course)>=50
break
end
//使用while循环,对course表中的学分总和进行检查,若学分总和小于50,
//对每门课程学分加1,直到学分总和不小于50为止。
declare @sumc int
set @sumc=(select sum(ccredit) from course)
WHILE @sumc<50
BEGIN
UPDATE course
SET ccredit= ccredit+1
set @sumc=(select sum(ccredit) from course)
END
WHILE (SELECT sum(ccredit) FROM course) < 50
BEGIN
UPDATE course
SET ccredit= ccredit+1
END
--4.(try catch)在try中删除student表中学号是'101'的记录,
--若不能删除,在catch中给出提示信息。
begin try
delete from student where sno='101'
end try
begin catch
print '出错信息为: '+error_message()
end catch
--Waitfor delay '00:00:03'
Waitfor time '17:01:00'
begin try
delete student where sno='101'
end try
begin catch
print error_message()
end catch
练习题:
--(1)返回当前日期的年月日
select getdate() as 当前时间
--(2)在update语句中使用@@rowcount变量来检测是否存在发生更改的记录
update student
set sno='108'
where sno='110'
if @@rowcount=0
print'警告:没有发生记录更新'
--(3)运用case语句
select sc.sno,sname,成绩=
case
when score<60 then '不及格'
when score>=60 and score<70 then '及格'
when score>=70 and score<80 then '中等'
when score>=80 and score<90 then '良好'
when score>=90 then '优秀'
else '无成绩'
end
from student,sc
where student.sno=sc.sno
练习:编写一个用户自定义函数zf1,要求根据输入学号,返回该某生总分。
CREATE FUNCTION zf1(@xh nchar(3))
RETURNS int
AS
BEGIN --该语句不可缺
DECLARE @xszf int
SELECT @xszf =SUM(score)
FROM sc
WHERE sno = @xh
RETURN(@xszf)
END --该语句不可缺
go
Select dbo.zf1('101') as 某生总分
go
练习:编写一个用户自定义函数zf,要求根据输入课程号,返回该门课的成绩平均值。
[例]:
一函数可查询出某学生选课信息
CREATE FUNCTION cxchj(@xh nchar(3))
RETURNS TABLE
AS
RETURN (Select *
from sc
Where sno= @xh)
调用:
Select * from cxchj('101')
练习:设计一函数可查询出选修某课程的选课信息
CREATE FUNCTION xk(@no nchar(4))
RETURNS TABLE
AS
RETURN (Select *
from sc
Where cno= @no)
调用:
select * from xk('1001')
【例】编写一个自定义函数xuanke,查询出@cno1和@cno2号课程的选课信息。
CREATE FUNCTION xuanke1(@cno1 nchar(4),@cno2 nchar(4))
RETURNS @xuanke TABLE
(sno nchar(3),
cno nchar(4)
)
BEGIN
--多个insert语句
INSERT INTO @xuanke
SELECT sno,cno from sc where cno=@cno1
INSERT INTO @xuanke
SELECT sno,cno from sc where cno=@cno2
RETURN --不可缺
END
--调用函数
--select * from xuanke1('1001','1002')
go
declare @cno1 nchar(4),@cno2 nchar(4)
select @cno1='1001',@cno2='1002'
select * from xuanke1(@cno1,@cno2)
go
练习1 (标量函数、内嵌表值函数) :编写一个自定义函数f_avg,查询出某个系的学生平均成绩
方法一:
create function f_avg(@se nchar(20))
returns table
as
return(select avg(score) as 平均分
from sc where
sno in
(select sno from student
where sdept=@se))
go
select * from f_avg('信息')
方法二:
CREATE FUNCTION f_avg(@xi nchar(20))
RETURNS TABLE
AS
RETURN
(Select sdept,avg(score)as 平均成绩
from student inner join sc
on student.sno=sc.sno
group by sdept
having sdept=@xi)
调用:
select * from f_avg('信息')
方法三:
CREATE FUNCTION f_avg5(@xi nchar(20))
RETURNS int
AS
begin
declare @a int
Select @a=avg(score)
from student inner join sc
on student.sno=sc.sno
where sdept=@xi
return(@a)
End
select dbo.f_avg5('旅外')
方法四:
CREATE FUNCTION f_avg(@xi nchar(20))
RETURNS TABLE
AS
RETURN
(Select avg(score)as 平均成绩
from student inner join sc
on student.sno=sc.sno
where sdept=@xi)
练习2 (多语句表值函数) :编写一个自定义函数xi_avg,查询出每个系的学生平均成绩
------------------------------------------------------------------------------------------
1.(无参数)用命令方式创建并执行一个存储过程proc_sc1,从sc表中查询101号学生的选课信息
use students
go
create proc proc_sc99
as
select * from sc where sno='101'
go
exec proc_sc99
2.(带输入参数)用命令方式创建并执行一个的存储过程proc_sc2,该存储过程带有输入参数@stu_no,从sc表中查询学号是@stu_no的学生的选课信息。
use students
go
create proc proc_sc100
@stu_no nchar(3)='102'
as
select * from sc where sno=@stu_no
go
exec proc_sc100 @stu_no=default
(exec proc_sc103 @stu_no='102')
3.(带输入参数和输出参数)用命令方式创建并执行一个的存储过程proc_sc3,该存储过程带有输入参数@stu_no和输出参数@stu_avg,从sc表中查询学号是@stu_no的学生的成绩平均分并赋值给输出参数@stu_avg。
use students
go
create proc proc_sc007
@stu_no nchar(3),
@stu_avg int output
as
select @stu_avg=avg(score)
from sc
where sno=@stu_no
go
declare @stu_avg int
exec proc_sc001 '102',@stu_avg output
print '该生平均成绩为:'+cast(@stu_avg as char(6))
4.(带输入参数、输出参数、返回值)用命令方式创建并执行一个的存储过程proc_sc4,该存储过程带有输入参数@stu_no和输出参数@stu_avg,从sc表中查询学号是@stu_no的学生的成绩平均分并赋值给输出参数@stu_avg 。从sc表中查询学号是@stu_no的学生的成绩总分并用return返回。
use students
go
create proc proc_sc88
@stu_no nchar(3),@stu_sum int output,
@stu_avg int output
as
select @stu_avg=avg(score),@stu_sum=sum(score)
from sc
where sno=@stu_no
return(@stu_sum)
go
declare @stu_sum int, @stu_avg int
exec proc_sc88 '102',@stu_sum output,@stu_avg output
print '该存储过程执行结果如下:'
print'平均分='+cast(@stu_avg as char(4))
print'总分='+cast(@stu_sum as char(4))
P146第二题第二小题:
create proc proc_list
@cour_no nchar(4)
as
declare @score int
select top 5 * into a from sc where cno=@cour_no order by score desc,sno asc
select @score=min(score) from a
insert into a
select * from sc where cno=@cour_no and score=@score and sno not in(select sno from a)
select * from a order by score desc
drop table a
go
exec proc_list '1001'
select * from sc where cno='1001' order by score desc
-------------------------------------------------------------------------------------------
使用游标:
declare stu_cursor insensitive scroll cursor
for
select student.sno,sname,sc.sno,cno
from student,sc
where student.sno=sc.sno
for read only
go
open stu_cursor
go
fetch next from stu_cursor
go
close stu_cursor
-------------------------------------------------------------------------------------------
使用游标:
create proc proc_list
@cour_no nchar(4)
as
declare @score int
select top 5 * into a from sc where cno=@cour_no order by score desc,sno asc
--select * from a order by score desc
select @score=min(score) from a
insert into a
select * from sc where cno=@cour_no and score=@score and sno not in(select sno from a)
select * from a order by score desc
drop table a
go
exec proc_list '1001'
-------------------------------------------------------------------------------------------
使用游标:
alter proc proc_list
@cour_no nchar(4)
as
declare cursor_sc scroll cursor --声明游标
for select top 5 score from sc where cno=@cour_no order by score desc,sno
open cursor_sc
declare @score int
fetch last from cursor_sc into @score
close cursor_sc --关闭游标
deallocate cursor_sc --删除游标
select top 5 * into a from sc where cno=@cour_no order by score desc,sno
insert into a
select * from sc where cno=@cour_no and score=@score and sno not in(select sno from a)
select * from a order by score desc
drop table a
go
exec proc_list '1001'
--------------------------------------------------------------------------------------------
create trigger remenbr
on sc
for insert,update
as
if update(score)
begin
print'处理正在进行……'
declare @data int
select @data=score from inserted
if @data<60
begin
print'数据太小!'
rollback transaction
end
end
update sc
set score=120
where sno='101' and cno='1002'
go
select * from sc
-------------------------------------------------------------------------------------
declare sc_cursor insensitive scroll cursor
for
select *from sc where cno='1001'
for read only
go
open sc_cursor
go
fetch next from sc_cursor
fetch first from sc_cursor
go
close sc_cursor
go
deallocate sc_cursor
------------------------------------------------------------------------------------------
Create TRIGGER reminder
ON sc
FOR UPDATE
AS
IF UPDATE(score)
BEGIN
PRINT '触发器正在执行……'
DECLARE @Newqty int
SELECT @Newqty=score FROM INSERTED
IF @Newqty>=60
BEGIN
declare @stuno nchar(3)
declare @couno nchar(4)
declare @coucre int
select @stuno=sno from inserted
select @couno=cno from inserted
select @coucre=ccredit from course where cno=@couno
update sumcredit set scsum=scsum+@coucre where sno=@stuno
END
END
go
--------------------------------------------------------------------------------------------
1. sql server的身份验证方式有哪些?如何设置?
答:两种模式(1)windows身份验证(2)混合模式
2.请创建一个windows登陆账号win_user和一个sql server 登陆账号sql_use.
帐户包含两种:登录者和数据库用户
3.请创建一个students用户win_user ,其对应的登陆账号是win_user,创建一个students用户sql_use ,其对应的登陆账号是win_user。
4.应将登陆账号sql_use添加到哪个服务器角色中,该账户才能执行sql server的任何操作?请完成添加操作。
sysadmin服务器角色
5.应将students用户win_user 添加到哪个数据库角色,该用户才能执行所有数据库任何操作?请完成添加操作。
db_owner数据库角色
6. 修改students用户win_user 的权限,使其只能对sc表进行select操作。
对象权限、语句权限、固定角色权限