SQL Server 2008 实验报告 - 第五次实验报告
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |
修改：在后边创建视图的时候注意 AS 后边不用加双引号，我加了但好像也操作对了，应该不加！ |
实验五 使用T-SQL语句定义数据、操纵数据
一．实验目的：
- 掌握使用T-SQL语句创建、修改、打开和删除数据库。
- 掌握使用T-SQL语句创建、修改和删除表。
- 掌握使用T-SQL语句对表中的数据进行增删改。
- 掌握使用T-SQL语句创建、修改和删除视图。
二．实验内容：（将所有题写到实验报告中）
1.使用T-SQL语句创建数据库
创建数据库stuinfo_2，要求：
（1）包含三个数据文件（MF.mdf、F1.ndf、F2.ndf）和两个日志文件（L1.ldf、L2.ldf），文件的其他属性自定义。
（2）F1.ndf和F2.ndf放到自定义的文件组G1中。
（3）数据库文件存储到D:\SQLDATA。
CREATE DATABASE stuinfo_2
ON
PRIMARY
(
Name = stuinfo_2_1,
Filename = 'D:\SQLDATA\MF.mdf'),
FILEGROUP G1
(
Name = stuinfo_2_2,
Filename = 'D:\SQLDATA\F1.ndf'),
(
Name = stuinfo_2_3,
Filename = 'D:\SQLDATA\F2.ndf')
LOG ON
(
Name = stuinfo_2_Log_1,
Filename = 'D:\SQLDATA\L1.ldf'),
(
Name = stuinfo_2_Log_2,
Filename = 'D:\SQLDATA\L2.ldf')
使用T-SQL语句创建数据库示意图
2.使用T-SQL语句修改数据库
向数据库stuinfo_2中添加一个文件组G2，再向该文件组中添加一个数据文件F3.ndf，文件的属性自定义
ALTER DATABASE stuinfo_2 ADD FILEGROUP G2;
ALTER DATABASE stuinfo_2 ADD FILE
(
Name = stuinfo_2_4,
Filename = 'D:\SQLDATA\F3.ndf')
TO FILEGROUP G2;
使用T-SQL语句修改数据库示意图
3.使用T-SQL语句创建表及约束 （1）在stuinfo库中创建student_2表，字段及数据类型同student表，另外要求： ①sno列为主键; ②sname取值唯一且可以为空； ③ssex列的默认值设为“男”；
```sql
USE stuinfo;
CREATE TABLE student_2 ([sno] [CHAR](5) CONSTRAINT pk_sno PRIMARY KEY,
[sname] [CHAR](10) CONSTRAINT uq_sname UNIQUE,
[ssex] [CHAR](2) CONSTRAINT def_ssex DEFAULT '男',
[sbirthday] [datetime],
[sclass] [char](10)
);
sp_help student_2; # 查看新建表的结构
```
使用T-SQL语句创建表及约束示意图
（2）在stuinfo创建score_2表，字段及数据类型同score表，另外要求： ①sno列和cno列的组合为主键； ②degree列的取值在0到100之间； ③sno列为外键，参照student表的sno列； ④cno列为外键，参照course表的cno列。
USE stuinfo;
ALTER TABLE course ADD CONSTRAINT pr_cno PRIMARY KEY (cno); # 需要先对 course 表进行修改
CREATE TABLE score_2 ([sno] [CHAR](5),
[cno] [CHAR](6),
[degree] [float](8),
CONSTRAINT pk_sno_cno PRIMARY KEY(sno, cno),
CHECK(degree >= 0 AND degree <= 100),
FOREIGN KEY (sno) REFERENCES student(sno),
FOREIGN KEY (cno) REFERENCES course(cno)
)
sp_help student_2; # 查看新建表的结构
在stuinfo创建score_2表，字段及数据类型同score表 示意图
4.使用T-SQL语句修改表 （1）修改teacher表，向该表中添加一列communist（是否党员），类型为逻辑型（bit）。
ALTER TABLE teacher ADD communist BIT;
sp_help teacher; # 查看修改后表的结构
修改teacher表，向该表中添加一列communist 示意图
（2）修改course表，添加一列cpno（先行课），类型为char(6)，设该列为外键，参照自身表的cno列，添加cname列的值唯一约束。
ALTER TABLE course ADD cpno CHAR(6) FOREIGN KEY REFERENCES course(cno);
ALTER TABLE course ADD CONSTRAINT un_cname UNIQUE (cname);
sp_help course; # 查看修改后表的结构
修改course表，添加一列cpno 示意图
（3）删除teacher表的communist列。
ALTER TABLE teacher DROP COLUMN communist; # 不附截图5.使用T-SQL语句操作表中数据
（1）向student表中添加一条记录（’111’，’李强’，default，’1998-01-01’，’95031’）。
（2）将score表的101号学生的3-105课程的成绩改为80。
（3）删除student表中姓名是“李强”的学生。
INSERT INTO student VALUES ('111', '李强', default, '1998-01-01', '95031');
UPDATE score SET degree = '80' WHERE sno = '101' AND cno = '3-105';
# 查看修改后结果
SELECT * FROM student WHERE sno = '111';
SELECT degree FROM score WHERE sno = '101' AND cno = '3-105';
# 删除‘李强’
DELETE FROM student WHERE sname = '李强'; # 删除不附截图
使用T-SQL语句操作表中数据 示意图
6.使用T-SQL语句创建视图
（1）创建“95031”班学生的视图V-student-95031_2。
CREATE VIEW V_student_95031_2 AS
SELECT * FROM student WHERE sclass = '95031'; # 不附截图（2）创建“95031”班学生的选课视图V-score-95031_2，包括的列为：学号、姓名、课程名、成绩，并使用汉字做列名。
CREATE VIEW V_score_95031_2 AS
SELECT sno AS '学号', sname AS '姓名', cname AS '课程名', degree AS '成绩' FROM student, score, course WHERE sclass = '95031' AND student.sno = score.sno AND score.cno = course.cno;
sp_help V_score_95031_2; # 查看
创建“95031”班学生的选课视图 示意图
（3）创建“6-166”课的选课视图V-score-6-166_2，包括的列为：课程号、课程名、学号、姓名、成绩，并使用汉字做列名。
CREATE VIEW V_score_6_166_2 AS
SELECT course.cno AS '课程号', cname AS '课程名', student.sno AS '学号', sname AS '姓名', degree AS '成绩' FROM student, score, course WHERE score.cno = '6-166' AND student.sno = score.sno AND score.cno = course.cno;
sp_help V_score_6_166_2; # 查看
创建“6-166”课的选课视图 示意图
（4）创建每个学生平均成绩的视图VS-avgdegree-1_2，包括的列为：班级、学号、姓名、平均成绩，并使用汉字做列名。
CREATE VIEW VS_avgdegree_1_2 AS
SELECT sclass AS '班级', student.sno AS '学号', sname AS '姓名', AVG(degree) AS '平均成绩' FROM student, score WHERE student.sno = score.sno GROUP BY student.sno, sclass, sname;
sp_help VS_avgdegree_1_2; # 查看
创建每个学生平均成绩的视图 示意图
（5）创建其平均成绩高于等于80分的学生视图VS-avgdegree-2_2，包括的列为：班级、学号、姓名、平均成绩，并使用汉字做列名。
CREATE VIEW VS_avgdegree_2_2 AS
SELECT sclass AS '班级', student.sno AS '学号', sname AS '姓名', AVG(degree) AS '平均成绩' FROM student, score WHERE student.sno = score.sno GROUP BY student.sno, sclass, sname HAVING AVG(degree) > '80';
sp_help VS_avgdegree_2_2; # 查看
创建其平均成绩高于等于80分的学生视图 示意图
（6）创建各门课平均成绩的视图VC-avgdegree_2，包括的列为：课程号、课程名、平均成绩，并使用汉字做列名。
CREATE VIEW VS_avgdegree_2 AS
SELECT course.cno AS '课程号', course.cname AS '课程名', AVG(degree) AS '平均成绩' FROM score, course WHERE course.cno = score.cno GROUP BY course.cno, course.cname;
sp_help VS_avgdegree_2; # 查看
创建各门课平均成绩的视图 示意图
（7）创建缺考学生的视图V-student-absent_2，包括的列为：学号、姓名、课程号、课程名，并使用汉字做列名。
CREATE VIEW V_student_absent_2 AS
SELECT student.sno AS '学号', sname AS '姓名', course.cno AS '课程号', course.cname AS '课程名' FROM score, course, student WHERE degree is null AND course.cno = score.cno AND student.sno = score.sno;
sp_help V_student_absent_2; # 查看
创建缺考学生的视图 示意图
（8）将视图V-score-6-166_2中的成绩都减去5分。
UPDATE V_score_6_166_2 SET '成绩' = '成绩' - '5';
SELECT * FROM V_score_6_166_2; # 显示修改后的结果
将视图V-score-6-166_2中的成绩都减去5分 示意图
| 阿里云国内75折 回扣 微信号：monov8 |
| 阿里云国际，腾讯云国际，低至75折。AWS 93折 免费开户实名账号 代冲值 优惠多多 微信号：monov8 飞机：@monov6 |