一、表、索引、视图的创建
实验题目：数据定义
实验目和要求：
- 掌握数据表的创建，修改，删除的方法
- 熟悉常用数据类型
- 掌握索引的创建，删除。通过实验理解唯一索引，聚簇索引
- 掌握视图的概念，视图的创建和删除
- 掌握利用视图进行数据查询的方法
实验步骤：
- 按实验内容要求完成各项操作
- 根据题目要求给出解决方案
- 提交实验报告
实验内容：
- 数据表的建立
建立s，c，sc三章数据表，并给每个属性定义合适数据类型，声明主外码
CREATE TABLE Student
(
Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) UNIQUE,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
CREATE TABLE Course
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY(Cpno) REFERENCES Course(Cno)
);
CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY( Sno, Cno),
FOREIGN KEY(Sno) REFERENCES Student(Sno),
FOREIGN KEY(Cno) REFERENCES Course(Cno)
);
INSERT INTO Student(Sno,Sname,Ssex,Sdept,Sage) VALUES( '201215121', '李勇', '男', '20', 'CS'),
( '201215122', '刘晨', '女', '19', 'CS'),
( '201215123', '王敏', '女', '18', 'MA'),
( '201215125', '张立', '男', '19', 'IS');
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES( '1', '数据库', '5', '4'),
( '2', '数学', '', '2'),
( '3', '信息系统', '1', '4'),
( '4', '操作系统', '6', '3'),
( '5', '数据结构', '7', '4'),
( '6', '数据处理', '', '2'),
( '7', 'PASCAL语言', '6', '4');
INSERT INTO SC(Sno,Cno,Grade) VALUES( '201215121', '1', '92'),
( '201215121', '2', '85'),
( '201215121', '3', '88'),
( '201215122', '2', '90'),
( '201215122', '3', '80');
- 数据表的修改
给c表增加字段teacher
ALTER TABLE Course
ADD Cteacher CHAR(20);
- 数据表的删除
建立一个表名为test的表，并删除
CREATE TABLE Test
(
CNO CHAR(20),
GRADE SMALLINT,
);
DROP TABLE Test;
- 数据表的索引建立和删除
- 为s表的sname字段建立唯一索引
CREATE CLUSTER INDEX Sname ON Student(Sname);
- 为s表的sdept建立聚簇索引
CREATE CLUSTERED INDEX Ssdept ON Student(Sdept);
- 删除s表的唯一索引
DROP INDEX Student.Sname;
- 视图的建立、删除和查询
- 建立一个计算机系学生基本信息视图CSV（SNO，SNAME，SEX，AGE）
go
CREATE VIEW CSV
AS
SELECT Sno,Sname,Ssex,Sage
FROM Student
WHERE Sdept = 'CS';
go
- 在CSV视图中查询1983年以后出生的计算机系学生基本信息。
SELECT *
FROM CSV
WHERE Sage < (2020 - 1983);
- 建立一个计算机系学生成绩视图JSGV（SNO，CNO，GRADE）。
go
CREATE VIEW JSGV(Sno,Cno,Grade)
AS
SELECT SC.Sno,SC.Cno,Grade
FROM Student,SC
WHERE Sdept = 'CS';
go
- 在JSGV中查询计算机系学生选课多于3门的学生学号。
SELECT Sno
FROM JSGV
GROUP BY Sno
HAVING COUNT(*)>3;
- JSGV中查询计算机系学生2号课不及格的学生学号和成绩。
SELECT Sno,Grade
FROM JSGV
WHERE Cno='2' AND Grade<60;
- 删除CSV视图
DROP VIEW CSV;
二、数据表的查询
实验题目：数据表的查询
实验目和要求：
1)掌握SQL语句灵活的查询功能
2)熟悉SQL语言的基本查询，包括单表查询，分组统计查询，连接查询
3)掌握SQL的复杂查询，包括嵌套查询，集合查询
4）理解和掌握SQL查询语句各个子句的特点和作用，按照SQL语言规范写出具体的SQL查询语句并调试通过
实验步骤：
1）按实验内容要求完成各项操作
2）根据题目要求给出解决方案
3）交实验报告
实验内容：
1)查询学生的学号和所在系
SELECT Sno,Sdept
FROM Student;
2)查询管理系的学生所选修的课程和成绩
SELECT Cno,Grade
FROM Student,SC
WHERE Sdept = '管理系' AND Student.Sno=SC.Sno;
3）统计有学生选修的课程门数。
SELECT COUNT(DISTINCT Cno)
FROM SC
GROUP BY Cno;
4）统计HU老师所授每门课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC,Course
GROUP BY Sno
WHERE Cteacher='HU' AND Course.Cno=SC.Cno;
5）统计所有选修人数多于20的课程号和选课人数，并按人数降序排列，若人数相等，则按课程号升序排列。
SELECT COUNT(Sno),Cno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>20
ORDER BY Cno,COUNT(Sno) DESC;
6）检索所有缓考即成绩为NULL的同学学号、姓名和缓考课程号。
SELECT SC.Sno,Cno,Sname
FROM SC,Student
WHERE Grade=NULL AND SC.Sno=Student.Sno;
7）检索‘OS’课成绩高于该课平均成绩的同学学号。
SELECT SC.Sno
FROM SC,Course
WHERE SC.Cno=Course.Cno AND Cname='IO'
AND Grade>=(
SELECT AVG(Grade)
FROM SC,Course
WHERE SC.Cno=Course.Cno AND Cname='IO'
);
8）检索计算机系女生的学号和姓名。
SELECT Sname,Sno
FROM Student
WHERE Sdept='CS' AND Ssex='女';
9）检索全体学生都选修的课程号和课程名。
SELECT Cname,Cno
FROM Course;
10）检索未选修任何课程的学生学号。
SELECT Sno
FROM SC,Course
WHERE SC.Cno=Course.Cno AND Course.Cno NOT IN (
SELECT Cno
FROM Course
);
11）检索WANG老师所授课程号、课程名。
SELECT Cno,Cname
FROM Course
WHERE Cteacher LIKE '王%';
12）检索所有姓LI同学的基本信息。
SELECT *
FROM Student
WHERE Sname LIKE '李%';
13）检索选修‘DATABASE’课程的学生学号。
SELECT Sno
FROM SC,Course
WHERE Cname='数据库' AND Course.Cno=SC.Cno;
14）检索年龄介于LIPING同学年龄和28岁之间的学生基本信息。
SELECT Sno
FROM Student
WHERE Sage<28 AND Sage>(
SELECT Sage
FROM Student
WHERE Sname='李平'
);
15）检索至少选修了一门TIAN老师所授课程的学生姓名、学号。
SELECT SC.Sno,Sname
FROM SC,Course,Student
WHERE SC.Sno=Student.Sno AND Course.Cno=SC.Cno AND Course.Cno IN (
SELECT COUNT(Cno)
FROM Course
WHERE Cteacher LIKE '田%'
);
三、数据表的更新
实验题目：数据表的更新
实验目和要求：
1)掌握SQL语句的数据操纵功能
2)掌握SQL语言的数据插入,修改,删除操作
3)掌握与嵌套查询相结合的插入,删除,修改数据的SQL语句
实验步骤：
1）按实验内容要求完成各项操作
2）根据题目要求给出解决方案
3）提交实验报告
实验内容
在学生表中插入一新生信息(‘200213808’,’HUJING’,’女’,22,’计算机’)
INSERT
INTO Student(Sno,Sname,Ssex,Sage,Sdept)
VALUES ( '200213808', '胡静', '女', '92','CS');
新建一个学生平均成绩表spjcj,将学生的平均成绩插入到spjcj表中
CREATE TABLE spjcj
(
Sno CHAR(10) PRIMARY KEY,
AVG_Grade SMALLINT
);
INSERT
INTO spjcj(Sno,AVG_Grade)
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno;
删除数据库中学号为’200213801’的退学学生有关信息。
DELETE
FROM Student
WHERE Sno='200213801';
将计算机系学生2号课成绩全部提高5%。
UPDATE SC
SET Grade=Grade*1.05
WHERE Cno='2' AND Sno IN(
SELECT Sno
FROM Student
WHERE Sdept='CS'
);
删除没有成绩的学生选课记录
DELETE
FROM SC
WHERE Sno IN(
SELECT Sno
FROM SC
WHERE Grade=NULL
);
四、数据库的完整性
实验题目：数据库的完整性
实验目的和要求：
- 掌握数据库的实体完整性约束定义，完整性检查及违约处理方式。
- 掌握数据库的参照完整性约束定义，完整性检查及违约处理方式。
- 掌握数据库的用户定义完整性约束定义，完整性检查及违约处理方式。
- 掌握触发器的定义及使用。
实验步骤： - 按实验内容要求完成各项操作
- 根据题目要求给出解决方案
- 提交实验报告
实验内容：
1.定义S， C表，定义实体完整性约束，定义学生的年龄在19到22岁之间，课程名唯一的约束
CREATE TABLE S
(
Sno CHAR(10),
Sname CHAR(10),
Ssex CHAR(10),
Sage SMALLINT CONSTRAINT C4 CHECK(Sage<22 AND Sage>19),
Sdept CHAR(10),
PRIMARY KEY NONCLUSTERED(Sno)
);
CREATE TABLE C
(
Cno CHAR(10),
Cname CHAR(10) UNIQUE,
Ccredit CHAR(10),
Cteacher CHAR(10),
PRIMARY KEY NONCLUSTERED(Cno)
);
- 在C表中增加cpno字段，cpno字段为先修课号，定义cpno为外码，参照C表的cno，并定义当删除被参照表中相关记录时，要求违约处理方式为置空，当发生修改操作时，违约处理方式为级联。
CREATE TABLE C
(
Cno CHAR(10),
Cname CHAR(10) UNIQUE,
Ccredit CHAR(10),
Cteacher CHAR(10),
Cpno CHAR(10),
PRIMARY KEY NONCLUSTERED(Cno),
FOREIGN KEY(Cpno) REFERENCES C(Cno)
ON DELETE NO ACTION
ON UPDATE CASCADE
);
- 定义SC表的实体集参照完整性约束，要求当其被参照表发生删除操作时，S表违约处理的方式为级联，当其被参照表发生修改操作时，违约处理的方式为拒绝。
CREATE TABLE SC
(
Cno CHAR(10),
Sno CHAR(10),
Grade SMALLINT,
PRIMARY KEY(Cno,Sno),
FOREIGN KEY(Sno) REFERENCES S(Sno)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY(Cno) REFERENCES C(Cno)
ON DELETE CASCADE
ON UPDATE CASCADE
);
- 触发器
☆ 建立一DML触发器，每当学生的成绩发生更新时，将更新的学号，成绩存入g-log表内
CREATE TABLE g-log
(
Sno CHAR(10),
Grade SMALLINT
);
go
CREATE TRIGGER RTI_UP
ON SC
FOR UPDATE
AS
IF UPDATE(GRADE)
BEGIN
INSERT INTO g-log
SELECT SNO,GRADE
FROM INSERTED
WHERE GRADE BETWEEN 0 AND 100
END
RETURN
go
☆ 建立一个INSTEAD OF触发器，每当删除课程表中记录时，先检查此课程是否已被选修，如选修则不允许删除，且给出提示信息“此课程已有学生已选修，无法删除”
go
CREATE TRIGGER C_CHECK1
ON C
INSTEAD OF DELETE
AS
IF EXISTS(SELECT CNO
FROM SC
WHERE CNO=SC.CNO)
BEGIN
PRINT '此课程已有学生已选修，无法删除'
ROLLBACK TRANSACTION
END
go
五、数据库的安全性
实验题目：数据库的安全性
实验目的和要求：
- 掌握SQL Server2005的安全控制机制
- 掌握SQLServer2005的身份验证模式
- 理解数据库用户帐户的基本概念
- 理解角色的概念
实验步骤： - 按实验内容要求完成各项操作
- 根据题目要求给出解决方案
- 提交实验报告
实验内容： - 定义登录log1，log2，log3
- 定义用户user1，user2，user3
- 掌握SQL SERVER 2005架构和用户分离的概念
- 数据库的授权、授权语句
☆ 将查询SC表和修改GRADE属性的权限授予用户user1。
GRANT SELECT,UPDATE(Grade)
ON SC
TO user1;
☆ 将对表S的插入权限授予用户user2，并允许他将此权限授予其他用户。
GRANT INSERT
ON S
TO user2
WITH GRANT OPTION;
☆ 允许用户user3拥有对cs系学生的查询权限
GRANT SELECT(Sdept='CS')
ON S
TO user3;
☆ 创建角色R1，授予R1 课程表查询和插入的权限，并授予用户user3
CREATE ROLE R1;
GRANT SELECT,INSERT
ON C
TO R1;
GRANT R1
TO user3;
☆ 删除角色R1课程表的插入权限
REVOKE INSERT
ON C
FROM R1;
☆ 收回所有用户对表S的插入权限。
REVOKE INSERT
ON TABLE S
FROM user1，user2，user3;
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
