大数据库第四次实验报告材料视图tsql语句.docx
- 文档编号:28660855
- 上传时间:2023-07-19
- 格式:DOCX
- 页数:26
- 大小:670.09KB
大数据库第四次实验报告材料视图tsql语句.docx
《大数据库第四次实验报告材料视图tsql语句.docx》由会员分享,可在线阅读,更多相关《大数据库第四次实验报告材料视图tsql语句.docx(26页珍藏版)》请在冰豆网上搜索。
大数据库第四次实验报告材料视图tsql语句
实验十报告创建视图
兰州大学数据库实验报告实验目的
1.掌握创建视图的SQL语句的用法。
2.掌握使用企业管管理器创建视图的方法。
3.掌握使用创建视图向导创建视图的方法。
4.掌握查看视图的系统存储过程的用法。
5.掌握修改视图的方法。
一.实验准备
1.了解创建视图方法。
2.了解修改视图的SQL语句的语法格式。
3.了解视图更名的系统存储过程的用法。
4.了解删除视图的SQL语句的用法。
二.实验要求
1.用不同的方法创建视图。
2.提交实验报告,并验收实验结果。
三.实验内容
1.创建视图
(1)使用企业管理器创建视图
①在EDUC库中以“student”表为基础,建立一个名为“V_计算机系学生”的视图。
在使用该视图时,将显示“student”表中的所有字段.
视图如下:
(2)使用SQL语句创建视图
①在查询分析器中建立一个每个学生的学号、姓名、选修的课名及成绩的视图S_C_GRADE;
CreateVIEWS_C_GRADE
AS
SELECTstudent.sno,sname,cname,score
FROMstudent,course,student_course
WHEREstudent.sno=student_course.sno
ANDo=student_o;
视图如下:
②建立一个所有计算机系学生的学号、选修课程号以及平均成绩的视图COMPUTE_AVG_GRADE;
CreateVIEWCOMPUTE_AVG_GRADE
AS
SELECTstudent.sno,o,AVG(score)'平均成绩'
FROMstudent,course,student_course
WHEREstudent.sno=student_course.sno
ANDo=student_oANDstudent.dno='CS'
GROUPBYstudent.sno,o;
视图如下:
2.修改视图
(1)使用企业管理器修改视图
在企业管理器中将视图COMPUTE_AVG_GRADE中改成建立在数学系的学生学号、选修课程号以及平均成绩的视图。
(IS设为数学系)
(2)使用SQL语句修改视图
①在查询分析器中使用更改视图的命令将上面建立的视图“V_计算机系学生”更名为“V_计算机系男生”。
SP_RENAME'V_计算机系学生','V_计算机系男生';
3.删除视图
(1)使用企业管理器删除视图
用企业管理器删除视图“V_计算机系学生”
(2)使用SQL语句删除视图
用SQL语句删除视图COMPUTE_AVG_GRADE;
DROPVIEWCOMPUTE_AVG_GRADE;
实验十一使用视图
一.实验目的
1.熟悉和掌握对数据表中视图的查询操作和SQL命令的使用;
2.熟悉和掌握对数据表中视图的更新操作和SQL命令的使用,并注意视图更新与基本表更新的区别与联系
3.学习灵活熟练的进行视图的操作,认识视图的作用
二.实验准备
1.熟悉SQLSERVER工作环境。
2.能连接到EDUC数据库。
3.复习有关视图操作的SQL语言命令。
三.实验要求
1.在实验开始之前做好准备工作。
2.实验之后提交实验报告,思考视图和基本表的区别
四.实验内容
一.定义视图
在EDUC数据库中,已StudentCourse和Student_course表为基础完成一下视图定义
1.定义计算机系学生基本情况视图V_Computer;
CREATEVIEWV_Computer
AS
SELECTstudent.*
FROMstudent
WHEREstudent.dno='CS';
视图如下:
2.将StudentCourse和Student_course表中学生的学号,姓名,课程号,课程名,成绩定义为视图V_S_C_G
CREATEVIEWV_S_C_G
AS
SELECTstudent.sno,sname,o,ame,score
FROMstudent,course,student_course
WHEREstudent.sno=student_course.snoANDo=student_o;
视图如下:
3.将各系学生人数,平均年龄定义为视图V_NUM_AVG
CREATEVIEWV_NUM_AVG
AS
SELECTCOUNT(dno)'各系人数',AVG(sage)'平均年龄'
FROMstudent
GROUPBYdno;
视图如下:
4.定义一个反映学生出生年份的视图V_YEAR
CREATEVIEWV_YEAR
AS
SELECTsname,2014-sage'出生年份'
FROMstudent;
视图如下:
5.将各位学生选修课程的门数及平均成绩定义为视图V_AVG_S_G
CREATEVIEWV_AVG_S_G
AS
SELECTstudent.sno'学号',sname'姓名',COUNT(*)'选修门数',AVG(score)'平均成绩'
FROMstudent,student_course
WHEREstudent.sno=student_course.sno
GROUPBYstudent.sno,sname;
视图如下:
6.将各门课程的选修人数及平均成绩定义为视图V_AVG_C_G
CREATEVIEWV_AVG_C_G
AS
SELECTcno'课程号',COUNT(cno)'选修人数',AVG(score)'平均成绩'
FROMstudent_course
GROUPBYcno;
视图如下:
二.使用视图
1.查询以上所建的视图结果。
所建视图结果如上题截图所示
2.查询平均成绩为90分以上的学生学号、姓名和成绩;
SELECT学号,姓名,平均成绩
FROMV_AVG_S_G
WHERE平均成绩>90;
3.查询各课成绩均大于平均成绩的学生学号、姓名、课程和成绩;
SELECTV_S_C_G.sno,V_S_C_G.sname,V_S_C_G.cname,V_S_C_G.score
FROMV_S_C_G,V_AVG_S_G
WHEREV_S_C_G.sno=V_AVG_S_G.学号ANDV_S_C_G.score>V_AVG_S_G.平均成绩;
4.按系统计各系平均成绩在80分以上的人数,结果按降序排列;
1.先创建学生学号-系别视图V_STUDENT
CREATEVIEWV_STUDENT
AS
SELECTsno,dno
FROMstudent;
2.进行查询
SELECTV_STUDENT.dno'系别',COUNT(V_STUDENT.sno)'人数'
FROMV_STUDENT,V_AVG_S_G
WHEREV_STUDENT.sno=V_AVG_S_G.学号ANDV_AVG_S_G.平均成绩>80
GROUPBYV_STUDENT.dno;
三.修改视图
1.通过视图V_IS,分别将学号为“S1”和“S4”的学生姓名更改为“S1_MMM”,”S4_MMM”并查询结果;
UPDATEV_ISSETsname='S1_MMM'WHERESNO='32011';
UPDATEV_ISSETsname='S4_MMM'WHERESNO='32014';
SELECT*
FROMV_IS
WHEREsno='32011'ORsno='32014';
2.通过视图V_IS,新增加一个学生记录('S12','YANXI',19,'IS'),并查询结果
INSERTINTOV_IS(sno,sname,sex,dno,sage)
VALUES('S12','YANXI','女','IS','19')
SELECT*
FROMV_IS
WHEREsno='S12';
3.要通过视图V_AVG_S_G,将学号为“S1”的平均成绩改为90分,是否可以实现?
并说明原因
答:
不能实现,因为视图或函数'V_AVG_S_G'包含聚合、DISTINCT或GROUPBY子句或者PIVOT或UNPIVOT运算符,所以无法进行更新。
UPDATEV_AVG_S_GSET学号='32011'WHERE平均成绩='90';
实验十二更新语句
一.实验目的
1.熟悉使用UPDATE/INSERT/DELETE语句进行表操作;
2.能将这些更新操作应用于实际操作中去;
二.实验准备
1.了解这些更新语句的基本语法和用法;
三.实验要求
1.完成下面的实验内容,并提交实验报告;
2.在实验报告中附上相应的代码;
四.实验内容
1.对于student表,将所有专业号为‘001’的,并且入学年份为2006的学生,或是专业号为‘003’,并且年龄小于20岁的学生的班级号改为‘001’。
UPDATEstudentSETclassno='001'
WHERE(mno='001'ANDYEAR(sctime)='2006')OR(mno='003'AND(year(getdate())-year(sdate))<20);
2.对于student表,删掉所有年龄小于20岁,并且专业号为‘003’的学生的记录。
DELETEFROMstudent
WHEREmno='003'AND(year(getdate())-year(sdate))<20
3.对于student表,插入一条新记录,它的具体信息为,学号:
2007110011、姓名:
张三、性别:
男、出生日期:
19880808、院系编号:
‘001’、专业编号:
‘001’、班级号:
‘001’、入学时间:
20070901。
INSERTINTOstudent(sno,sname,sex,sdate,dno,mno,classno,sctime)
VALUES('2007110011','张三','男','1988-8-8','001','001','001','2007-9-1');
4.对于student表,将入学时间最晚的学生和年龄最小的学生的联系方式去掉。
UPDATEstudentSETtel=null
WHEREsctime>=ALL(selectsctimeFROMstudent)
ORyear(sdate)>=ALL(selectyear(sdate)fromstudent)
5.对于student表,将平均年龄最小的一个院系的院系编号改为‘008’。
UPDATEstudentSETdno='008'
wherednoin(
SELECTtop1dnoFROMstudent
GROUPBYdno
ORDERBYAVG(year(getdate())-year(sdate))
)
实验十三T-SQL编程
一、【实验目的】
1、掌握T_SQL编程的基本语法
2、常用函数的使用方法
二、【实验要求】
1、使用查询分析器练习T-SQL编程方法
2、练习函数的使用
三、【实验准备】
1、复习与本次实验内容相关知识
2、预习相关函数(请预查阅CONVERT、DATENAME、GFTDATE函数的用法)
四、【实验内容】
1、条件结构
自己编写一段程序判断一个年份(比如1900年)是否是闰年,是则显示1900年为闰年,否则显示1900年不是闰年.
DECLARE@yearINT
SELECT@year=2014
if
@year%4=0and@year%100<>0printCONVERT(CHAR(4),@year)+'是闰年'
elseprintCONVERT(CHAR(4),@year)+'不是闰年'
2、循环结构
(1)下面是计算1~100和的循环结构,执行之,体会循环结构程序,注意语句块标志BEGIN...END
DECLARE@SUMINT,@IINT
SELECT@I=1,@SUM=0
WHILE@I<=100
BEGIN
SELECT@SUM=@SUM+@I
SELECT@I=@I+1
END
PRINT'1...100的和为:
'+CONVERT(CHAR(4),@SUM)
(3)编写一个程序用于计算10的阶乘
DECLARE@SUMINT,@IINT
SELECT@I=1,@SUM=1
WHILE@I<=10
BEGIN
SELECT@SUM=@SUM*@I
SELECT@I=@I+1
END
PRINT'阶乘!
的结果为:
'+CONVERT(CHAR(10),@SUM)
(4)自己编制一个程序,要求其能打印出100内的素数(只能被其自身和1整除的数).--循环结构+条件结构
SELECT
main.number
FROM
master..spt_valuesASmain
WHERE
type='P'
ANDnumberBETWEEN3AND100
ANDNOTEXISTS(
SELECT
1
FROM
master..spt_valuesASsub
WHERE
type='P'
ANDsub.numberBETWEEN2ANDmain.number-1
ANDmain.number%sub.number=0)
3、CASE结构
(1)下面SQL查询图书的信息,并根据图书定价判断是否适合作为教材
select书名,出版社,作者,
case
when定价>50then'定价太高,不适合作教材'
else'定价'+CAST(定价asvarchar(5))+',可以作教材'
end可否作为教材
from图书
(2)请自己编程实现各位同学的成绩以等级分显示即:
90分及以上为优,80分及以上到90以下为良,70分及以上到80分以下为中,60分及以上到70分以下为及格,其余为不及格.
SELECTstudent.sno,sname,cname,
CASE
WHENstudent_course.score>=90THEN'优'
whenstudent_course.score>=80andstudent_course.score<=90THEN'良'
whenstudent_course.score>=70andstudent_course.score<=80THEN'中'
whenstudent_course.score>=60andstudent_course.score<=70THEN'及格'
else'不及格'
ENDGRADE
FROMstudent_course,course,student
WHEREstudent_o=oANDstudent_course.sno=student.sno
4、函数使用
下面查询,显示当前日期,显示格式为:
"今天是XXXX年XX月XX日,星期X",执行之,体会系统函数的用法.
SELECT'今天是'+DATENAME(YEAR,GETDATE())+'年'
+DATENAME(MONTH,GETDATE())+'月'
+DATENAME(DAY,GETDATE())+'日,'
+DATENAME(WEEKDAY,GETDATE())
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 第四 实验 报告 材料 视图 tsql 语句