|
从学生表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' 查询出选修‘ 使用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' --查询与“name 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') --查询选修课程号为“ 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' --查询出‘ --使用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)
------------------------------------------------------------------------------------------ 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操作。 对象权限、语句权限、固定角色权限 |
|
|
