数据库上机课实验题

最新推荐文章于 2024-04-23 16:30:42 发布

zzsaixuexi

最新推荐文章于 2024-04-23 16:30:42 发布

阅读量1w

点赞数 20

本文链接：https://blog.csdn.net/zzsaixuexi/article/details/120561572

版权

sql 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

文章目录

操作数据库

实验目的：理解和掌握数据库DDL语言，能够熟练地使用SQLDDL语句创建、修改和删除数据库和基本表。
实验要求：理解和掌握SQLDDL语句的语法，特别是各种参数的具体定义和使用方法；使用SQL语句创建、修改和删除数据库和基本表。掌握SQL语句常见语法错误的调试方法。
实验内容：
（1）定义数据库创建名为test的数据库。

CREATE DATABASE test;

（2）定义基本表在test数据库中创建3个基本表。
学生表：student(sno,sname,ssex,sage,sdept)
课程表：course（cno,cname,cpno,ccredit）
学生选课表：sc（sno,cno,grade）
要求：不需要添加完整性约束条件，注意各属性列的类型选择。

 CREATE TABLE student (
  sno INT,
  sname CHAR(5),
  ssex CHAR(5),
  sage INT,
  sdept CHAR(5)
) ;

CREATE TABLE course (
  cno INT,
  cname CHAR(5),
  cpno INT,
  ccredit INT
) ;

CREATE TABLE sc (
sno INT,
 cno INT, 
 grade INT) ;

（3）
1.修改基本表给student表增加“入学日期”属性列（entrancetime），其数据类型为date型。

ALTER TABLE student 
  ADD entrancetime DATE ;

2.给course表增加“平均成绩”属性列（avggrade），其数据类型为int型。

ALTER TABLE course 
  ADD avggrade INT ;

3.将student表中sage的数据类型改为int型。

ALTER TABLE student 
  MODIFY sage INT ;

4.将course表中avggrade的数据类型改为decimal(4,1)型。

ALTER TABLE course 
  MODIFY avggrade DECIMAL (4, 1) ;

5.修改student表中entrancetime属性列的名字，将其改为entime。

ALTER TABLE student 
  CHANGE entrancetime entime DATE ;

6.删除student表中的entime属性列。

ALTER TABLE student 
  DROP entime ;

7删除course表中的avggrade属性列。

ALTER TABLE course 
  DROP avggrade ;

8为student表的sname添加非空值的约束条件。

ALTER TABLE student 
  MODIFY sname CHAR(5) NOT NULL ;

（4）插入数据按照书P79插入三个表的数据。

INSERT INTO student (sno, sname, ssex, sage, sdept) 
VALUES
  (201215121, '李勇', '男', 20, 'CS'),
  (201215122, '刘晨', '女', 19, 'CS'),
  (201215121, '王敏', '女', 18, 'MA'),
  (201215121, '张立', '男', 19, 'IS') ;
  INSERT INTO course (cno, cname, cpno, ccredit) 
  VALUES
    (1, '数据库', 5, 4),
    (2, '数学', NULL, 2),
    (3, '信息系统', 1, 4),
    (4, '操作系统', 6, 3),
    (5, '数据结构', 7, 4),
    (6, '数据处理', NULL, 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) ;

（5）删除student表、course表、sc表

DELETE  FROM sc ;
DELETE  FROM student;
DELETE  FROM course;

（6）删除test数据库

DROP DATABASE test ;

简单查询

实验目的：掌握SQL程序设计基本规范，熟练运用SQL语言实现数据基本查询，包括单表查询和分组统计查询。实验要求：针对学生选课数据库设计各种单表查询SQL语句、分组统计查询语句。理解和掌握SQL查询语句各个子句的特点和作用，按照SQL程序设计规范写出具体的SQL查询语句，并调试通过。实验内容：
（1）查询全体学生的学号、姓名和所在系。
（2）查询所有课程的详细记录。
（3）查询所有学生的姓名和出生年份。
（4）查询没有选修2号课程的学生的学号，查询结果按学号升序排列。
（5）查询成绩在80-90之间的学生的学号和成绩，查询结果按学号升序排列，同一学号的学生按成绩降序排列。
（6）查询选修了2号或3号课程的学生的学号和课程号。
（7）查询与“数据”相关课程的课程号和课程名，查询结果按课程号降序排列。
（8）计算每门课程的学生的平均成绩。
（9）查询选修人数大于2人的课程号和该课程的选修人数。
（10）查询平均成绩大于等于85分的课程的课程号，查询结果按平均成绩升序排列。
表数据
sc表
在这里插入图片描述
course表

student表

实验内容以及结果：
（1）查询全体学生的学号、姓名和所在系。

SELECT * FROM student;

结果
在这里插入图片描述

（2）查询所有课程的详细记录。

SELECT * FROM sc;

结果
在这里插入图片描述

（3）查询所有学生的姓名和出生年份。

SELECT sname,sage FROM student;

结果
在这里插入图片描述

（4）查询没有选修2号课程的学生的学号，查询结果按学号升序排列。

SELECT student.sno FROM student,sc
WHERE sc.sno=student.sno AND NOT sc.cno =2 
GROUP BY student.sno ;

结果
在这里插入图片描述

（5）查询成绩在80-90之间的学生的学号和成绩，查询结果按学号升序排列，同一学号的学生按成绩降序排列。

SELECT student.sno,sc.grade FROM student,sc
WHERE sc.grade BETWEEN 80  AND  90
AND sc.sno=student.sno  
GROUP BY student.sno ;

结果
在这里插入图片描述

（6）查询选修了2号或3号课程的学生的学号和课程号。

SELECT student.sno,sc.cno FROM student,sc
WHERE sc.cno IN (2,3)
AND sc.sno=student.sno  
GROUP BY student.sno ;

结果
在这里插入图片描述

（7）查询与“数据”相关课程的课程号和课程名，查询结果按课程号降序排列。

SELECT * FROM course
WHERE cname LIKE '%数据%'
ORDER BY cno  DESC;

结果
在这里插入图片描述

（8）计算每门课程的学生的平均成绩。

SELECT sc.cno,AVG(sc.grade) FROM sc
GROUP BY sc.cno ;

结果
在这里插入图片描述

（9）查询选修人数大于2人的课程号和该课程的选修人数。

SELECT sc.cno,COUNT(sc.sno) FROM sc
GROUP BY sc.cno 
HAVING COUNT(sc.sno)>2;

结果
在这里插入图片描述

（10）查询平均成绩大于等于85分的课程的课程号，查询结果按平均成绩升序排列。

SELECT sc.cno,AVG(sc.grade) FROM sc
GROUP BY sc.cno 
HAVING AVG(sc.grade)>85
ORDER BY AVG(sc.grade) ;

结果
在这里插入图片描述

注意查询语句需要逐个运行，否者只能查出第一个查询的

操作表

实验目的：熟悉数据库的更新操作，能够使用SQL语句对数据库进行数据的插入、修改、删除操作。针对学生选课数据库设计单元组插入、批量数据插入、修改数据和删除数据等SQL语句。理解和掌握INSERT、UPDATE和DELETE语法结构的各个组成部分，结合嵌套SQL子查询，分别设计几种不同形式的插入、修改和删除数据的与，并调试成功。
实验内容：
1、INSERT基本语句
（1）向student表中插入两条完整的学生记录，要求每一列都给出一个合理的值。
（2）向course表中插入一条新课程的记录，课程名为“PYTHON程序设计”，课程号为“8”。2、批量数据INSERT语句
（1）创建一个新表，表名为“ISstudent”,把student表中所有IS系的学生都插入到ISstudent表中。
（2）创建一个选课统计表（表名：countsc），记录每个学生及其选课总数和平均成绩。（3）创建一个学生基本信息统计表（表名：countstudent），记录每个系学生的人数以及平均年龄。
（4）为（3）中的coutstudent表增加两个属性列boynum和girlnum，分别记录每个系的男生人数、女生人数。（选做）效果如下：
3、UPDATE语句
（1）给3号课程的所有考试成绩加2分。
（2）把student表中所有男生的系都改为‘CS’。
（3）把所有IS系的同学的成绩设置为空值。
（4）给sc表增加一列，属性名为endgrade，用grade属性列的值填充该列，然后把2号课程的所有endgrade的值都改为空值。
4、DELETE语句
（1）删除“张立”同学的选课记录。
（2）删除“信息系统”和“数据库”这两门课的选课记录。
注此处表数据用的是实验二的表数据

实验代码
1、INSERT基本语句
（1）向student表中插入两条完整的学生记录，要求每一列都给出一个合理的值。

INSERT INTO student
 VALUES (201215031,'王五','男',20,'CS'),(201215032,'李丽','女',19,'IS');

结果
在这里插入图片描述

（2）向course表中插入一条新课程的记录，课程名为“PYTHON程序设计”，课程号为“8”。

INSERT INTO course VALUES (8,'PYTHON程序设计',NULL,NULL)

结果
在这里插入图片描述

2、批量数据INSERT语句

（1）创建一个新表，表名为“ISstudent”,把student表中所有IS系的学生都插入到ISstudent表中。

 CREATE TABLE   ISstudent(
sno  INT  ,
sname   VARCHAR(20) ,
ssex   VARCHAR(2) ,
sage  INT ,
 sdept  VARCHAR(10)
);

插入语句

INSERT INTO  isstudent SELECT  *  FROM student WHERE sdept='IS'

结果
在这里插入图片描述
注图片有点点问题 sdept下方应该全为IS

（2）创建一个选课统计表（表名：countsc），记录每个学生及其选课总数和平均成绩。
建表语句

 CREATE TABLE   countsc(
sno  INT  ,
 SUNcno INT, 
AVGgrade  INT
);

插入语句

INSERT INTO  countsc
	SELECT cno, COUNT(cno),AVG(sc.grade) 
	FROM sc
	GROUP BY sc.cno ;

结果
在这里插入图片描述

（3）创建一个学生基本信息统计表（表名：countstudent），记录每个系学生的人数以及平均年龄。
建表语句

 CREATE TABLE   countstudent(
sdept  VARCHAR(20)  ,
SUNstudent  INT,
 AVGsage INT
);

插入语句

INSERT INTO countstudent
	SELECT sdept, COUNT(sno),AVG(sage) FROM student
	GROUP BY sdept ;

结果
在这里插入图片描述

（4）为（3）中的coutstudent表增加两个属性列boynum和girlnum，分别记录每个系的男生人数、女生人数。（选做）
添加列表

ALTER TABLE countstudent ADD man INT
ALTER TABLE countstudent ADD wuman INT

插入信息

UPDATE countstudent a
INNER JOIN (SELECT sdept, COUNT(sno) wuman  FROM student WHERE ssex='女' GROUP BY sdept) b ON a.sdept=b.sdept
SET a.wuman=b.wuman;
UPDATE countstudent a
INNER JOIN (SELECT sdept, COUNT(sno) man  FROM student WHERE ssex='男' GROUP BY sdept) b ON a.sdept=b.sdept
SET a.man=b.man;

结果
在这里插入图片描述

3、UPDATE语句
（1）给3号课程的所有考试成绩加2分。

UPDATE sc SET grade=grade-2 WHERE cno=3;

结果
在这里插入图片描述

（2）把student表中所有男生的系都改为‘CS’。

UPDATE student SET sdept='CS' WHERE ssex='男';

结果
在这里插入图片描述

（3）把所有IS系的同学的成绩设置为空值。

UPDATE sc SET grade=NULL WHERE sc.sno IN (SELECT  student.sno  FROM student WHERE sdept='IS');

结果

（4）给sc表增加一列，属性名为endgrade，用grade属性列的值填充该列，然后把2号课程的所有endgrade的值都改为空值。

ALTER TABLE sc ADD endgrade INT;
UPDATE sc SET endgrade = grade;
UPDATE sc SET endgrade = NULL WHERE cno=2;

结果
在这里插入图片描述

4、DELETE语句
（1）删除“张立”同学的选课记录。

DELETE FROM sc WHERE sc.sno IN (SELECT  student.sno  FROM student WHERE sname='张立')

结果
在这里插入图片描述

（2）删除“信息系统”和“数据库”这两门课的选课记录。

DELETE FROM sc 
WHERE sc.cno IN 
(SELECT  cno  FROM course WHERE cname IN('信息系统','数据库'))

结果
在这里插入图片描述

复杂查询

复杂查询实验目的：掌握SQL程序设计基本规范，熟练运用SQL语言实现连接查询。
实验要求：针对学生选课数据库，实现单个表针对自身的连接查询，以及多个表的连接查询。理解和掌握SQL查询语句各个子句的特点和作用，按照SQL程序设计规范写出具体的SQL查询语句，并调试通过。
实验内容：（1）查询选修了数据库这门课的学生的学号和姓名
（2）查询选修了3号课程并且成绩在85分以上的学生的学号和姓名
（3）查询信息系统这门课的间接先行课的课程号
（4）查询所有学生选课的情况（包括没有选课的学生）
（5）查询选修了2号课程的学生的学号、姓名、课程名称和成绩
（6）查询信息系统这门课的间接先行课的课程号和课程名称（选作）
注此处表数据用的是实验二的表数据

实验代码：（1）查询选修了数据库这门课的学生的学号和姓名

SELECT sno,sname 
FROM student 
WHERE sno IN(SELECT sno FROM sc WHERE cno  IN(SELECT cno FROM course WHERE cname='数据库'))

结果
在这里插入图片描述

（2）查询选修了3号课程并且成绩在85分以上的学生的学号和姓名

SELECT sno,sname 
FROM student
 WHERE sno IN(SELECT sno FROM sc WHERE cno=3 AND grade>85)

结果
在这里插入图片描述

（3）查询信息系统这门课的间接先行课的课程号

SELECT cpno 
FROM course 
WHERE    cno=(
	SELECT cpno 
	FROM course 
	WHERE cname='信息系统')

结果
在这里插入图片描述

（4）查询所有学生选课的情况（包括没有选课的学生）


SELECT student.sno,sc.cno  
FROM sc 
RIGHT JOIN student   ON student.sno=sc.sno

结果

在这里插入图片描述

（5）查询选修了2号课程的学生的学号、姓名、课程名称和成绩

SELECT student.sno,student.sname ,(SELECT cname FROM course WHERE cno=2)cname ,sc.grade 
FROM student,sc 
WHERE  student.sno=sc.sno AND sc.cno=2

结果
在这里插入图片描述

（6）查询信息系统这门课的间接先行课的课程号和课程名称（选作）

SELECT cno, cname 
FROM course 
WHERE    cno=(SELECT cpno 
		FROM course 	
		WHERE    cno=(
		SELECT cpno 
		FROM course 
		WHERE cname='信息系统'))

结果
在这里插入图片描述

视图与索引

实验内容：
一、创建视图
1、建立数学系（MA）学生的视图(student_MA)。

CREATE VIEW student_MA  AS SELECT * FROM student WHERE sdept='MA'

2、建立年龄在20岁以下（包括20岁）的女生的视图（girl_view）。

CREATE VIEW girl_view  AS SELECT * FROM student WHERE sage<=20 AND ssex='女'

3、建立一个学生的视图(student_year)，包括学号、姓名、出生年份三个属性。

CREATE VIEW student_year   AS SELECT sno,sname,sage  FROM student

4、建立选修了2号课程的学生的视图（course2_view），包括学号、姓名、课程名称、成绩四个属性。

CREATE VIEW course2_view   AS 
SELECT student.sno,student.sname ,(SELECT cname FROM course WHERE cno=2)cname ,sc.grade 
FROM student,sc 
WHERE  student.sno=sc.sno AND sc.cno=2

二、查询视图
1、在course2_view视图中，查询数学课程的平均成绩。

SELECT AVG(grade) FROM course2_view

实验结果
在这里插入图片描述

2、查询计算机系（CS）的同学选修信息系统课程的平均成绩。（提示：先建立一个视图，然后基于这个视图进行查询）
先建立一个计算机系（CS）的同学选修信息表

CREATE VIEW student_CS1  AS SELECT student.sno,student.sname,student.ssex,sc.cno,sc.grade FROM student,sc WHERE sc.sno=student.sno AND sdept='cs'

然后查询

SELECT AVG(grade) FROM student_CS1 WHERE cno=(SELECT cno FROM course WHERE cname='信息系统')

实验结果
在这里插入图片描述

在这里插入图片描述

因为之前的实验我没有改回数据所以是这个结果建议自己运行一次

3、查询每个同学选修课程中，成绩最高的那门课程的名称和成绩（要求查询结果包括学生学号、姓名、课程名称、最高成绩4个属性）（选做）

–三、更新视图
1、将数学系学生视图student_MA中，学号为“201215126”的学生姓名改为“赵红”。

UPDATE   student_ma
		set sname='赵红'
		WHERE sno=201215126

–2、带有with check option视图的更新
–1.建立计算机系（cs）学生的视图（student_cs），包含学号（sno）、姓名（sname）、性别（ssex）、年龄（sage）、系（sdept），并带有withcheckoption子句。

          CREATE view student_cs
					as
					SELECT sno,sname,ssex,sage,sdept
					FROM student
					WHERE  sdept='cs'
				with check OPTION

–2.向student_cs视图中插入一条记录(201215131,‘赵志国’,‘男’,21,‘CS’)，观察student_cs视图和student基本表数据的变化情况。(student 也增加了)

INSERT 
						into  student_cs(sno,sname,ssex,sage,sdept)
						VALUES(201215131,'赵志国','男',21,'CS')

实验结果

在这里插入图片描述

–3.向student基本表中插入下面两条记录：(201215132,‘王梅梅’,‘女’,19,‘MA’)(201215133,‘李玉坤’,‘男’,20,‘CS’)观察student基本表和student_cs视图中数据的变化情况。(视图中王梅梅没有了)

 INSERT 
						into  student(sno,sname,ssex,sage,sdept)
						VALUES(201215132,'王梅梅','女',19,'MA'),(201215133,'李玉坤','男',20,'CS')

–4.向student_cs视图中插入一条记录(201215134,‘王伟’,‘男’,20,‘IS’)，结果如何？(没有发生变化)

 INSERT 
						into  student_cs(sno,sname,ssex,sage,sdept)
						VALUES(201215134,'王伟','男',20,'IS')

结果
在这里插入图片描述

–四、删除视图
–删除数学系学生视图student_MA

 DROP view   student_MA

–五、创建索引为student表、course表和sc表建立索引。
–其中student表按学号升序建唯一索引，course表按课程号升序建唯一索引，sc表按学号升序和课程号降序建唯一索引。

CREATE UNIQUE INDEX stusno on student(sno);
CREATE UNIQUE INDEX coucno  on course(cno);
CREATE UNIQUE INDEX  scno  on sc(sno ASC,cno DESC);

–查询索引

show index from student
show index from course
show index from sc

数据完整性实验

实验内容：
1、创建student表，sno为主码，sname不能取空值，学号在9000-9999之间（要求使用constraint命名子句）。student（sno,sname,ssex,sage,sdept）
因为冲突

CREATE DATABASE test1;

CREATE table student(sno char(9) PRIMARY KEY  CONSTRAINT C1 check (sno BETWEEN 9000 and 9999),
sname char(20) not null , 
ssex char(2), 
sage SMALLINT,
sdept char(20)
);

2、创建course表，要求定义实体完整性，并且cname属性不允许取空值、重复值。Course(cno,cname,cpno,ccredit)

CREATE TABLE course (cno int ,
cname char(20) NOT null,
cpno  int ,
ccredit int 
);

3、创建sc表，定义sc中的实体完整性和参照完整性，并且sno的值在9000-9999之间，grade的值应该在0-100之间。要求两种写法，一种使用constraint命名子句，一种不使用constraint命名子句。sc(sno,cno,grade)

CREATE TABLE sc(
sno char(9) CONSTRAINT C3 CHECK(sno BETWEEN 9000 and 9999),
cno int ,
grade int CONSTRAINT C2 check (grade between 0 and 100)

)

4、修改student表中的约束条件，将学号改为在1000-2000之间。
–先删除约束性条件

ALTER table student
 DROP CONSTRAINT C1;

–再修改

ALTER table student 
add CONSTRAINT C1 check (sno BETWEEN 1000 and 2000)

5、为course表增加一个完整性约束条件，ccredit属性的值应在1-10之间。

ALTER table course
add CONSTRAINT C4 CHECK (ccredit BETWEEN 1 and 10)

存储过程

实验目的：理解存储过程的定义、存储过程运行、存储过程删除以及存储过程的参数传递。掌握T-SQL编程语言和编程规范，规范设计存储过程。
实验内容：利用存储过程实现下面的应用，并按要求执行这些存储过程。

本实验使用新数据
student表
classinfo表
course表
sc表

注：我用的是SQLyog

1、创建存储过程cu1，查询所有班老师的姓名，并执行该存储过程，查询每个班老师的名字。

DELIMITER $$
CREATE 
PROCEDURE cu1()
BEGIN
SELECT classname,teachername FROM classinfo;
END $$
DELIMITER ;

CALL cu1()

在这里插入图片描述

2、创建存储过程cu2，查询某个学号学生的姓名，并执行该存储过程，查询学号为“201215121”的学生的姓名。

DELIMITER $$
CREATE 
PROCEDURE cu2(OUT divid INT,IN div2 INT )
BEGIN
SELECT sname FROM student
WHERE sno=div2;
END $$
DELIMITER ;

SET @divid INT;
SET @div2=201215121;
CALL cu2(@divid,@div2);

在这里插入图片描述

3、创建一个存储过程cu3，查询某学号段的学生信息，并执行该存储过程，查询学号在“201215121-201215125”之间的学生的信息。

DELIMITER $$
CREATE 
PROCEDURE cu3(IN div1 INT,IN div2 INT)
BEGIN
SELECT * FROM student
WHERE sno>=div1 AND sno<=div2;
END $$
DELIMITER ;

CALL cu4(201215121,201215125);

结果
在这里插入图片描述

4、创建一个存储过程cu4，用于查询选修了某号课程的学生的学号、姓名、课程名称和成绩，并执行该存储过程，查询选修了2号课程的学生的学号、姓名、课程名称和成绩。

DELIMITER $$
CREATE 
PROCEDURE cu4(IN div1 INT)
BEGIN
SELECT student.sno,student.sname ,(SELECT cname FROM course WHERE cno=2)cname ,sc.grade 
FROM student,sc 
WHERE  student.sno=sc.sno AND sc.cno=div1;
END $$
DELIMITER ;

CALL cu4(2);

在这里插入图片描述

5、创建一个存储过程cu5，用于查询选修了某号课程，且成绩在某分数以上的学生的学号和姓名，并执行该存储过程，查询选修了2号课程并且成绩在60分以上的学生的学号和姓名。

DELIMITER $$
CREATE 
PROCEDURE cu5(IN div1 INT,IN div2 INT)
BEGIN
SELECT student.sno,student.sname 
FROM student,sc 
WHERE  student.sno=sc.sno AND sc.cno=div1 AND grade >div2;
END $$
DELIMITER ;

CALL cu5(2,60);

在这里插入图片描述

6、创建一个存储过程cu6，用于判断要插入记录的学号是否已存在，如果不存在，插入该用户信息，返回消息；如果已存在，则返回信息。

DELIMITER $$
CREATE 
PROCEDURE cu6(IN div1 INT,IN div2 CHAR(50),IN div3 CHAR(50),IN div4 INT,IN div5 CHAR(50))
BEGIN
IF(SELECT COUNT(*) FROM student WHERE sno= div1)=0
THEN
INSERT INTO student VALUES (div1 ,div2,div3,div4,div5);
SELECT * FROM student WHERE sno= div1;
ELSE 
SELECT * FROM student WHERE sno= div1;
END IF;
END $$
DELIMITER ;


cu6(123,'张三','女',18,'MA')

在这里插入图片描述

这个有一点点小问题如果有正确的请评论

7、删除存储过程cu1

DROP PROCEDURE cu1

在这里插入图片描述

乏了以下皆为摘抄所得

触发器

实验目的：掌握触发器的设计和使用方法。定义INSERT、DELETE、UPDATE触发器，能够理解触发器的作用和执行原理，验证触发器的有效性。
实验内容：
一、准备数据
1、创建classinfo表和studentinfo表，表结构及表中数据如下图所示：
在这里插入图片描述

要求：

创建classinfo表和studentinfo表，
classinfo表

  CREATE TABLE classinfo(
	classno char(3),
	classname char(5),
	totalnum char(8),
	teachername char(10))

studentinfo表

CREATE TABLE studentinfo(
	stuname char(10),
	stuno char(20),
	stuclass char(3),
	ssex char(5))

–查询classinfo表
SELECT *FROM classinfo
2、向test数据库中导入teacherinfo.xls和student.xls数据文件

二、INSERT触发器
#定义触发器←#after可以换成fore
create trigger t addstudente
on studentinfo
after insert
as
update classinfo set totalnum=totalnum+1
where classno in(select stuclass from inserted)
检验触发器
insert into studentinfo
values（‘李勇’，201215121，1，‘男’）
insert into studentinfo
values（‘刘晨’，201215122，2，‘女’）
insert into studentinfo
values（‘王敏’，201215123，1，‘女’）
select* from classinfo

三、DELETE触发器
定义触发器
create trigger t _deleteteacher
on teacherinfo
for delete
as
select TeacherName as 教师姓名，Sex，Age from deleted
检验触发器←
delete from teacherinfo
where teacherid=‘T001’
四、update触发器
定义触发器←
create trigger t_updateclassinfo
on classinfo
for update
as
select * from inserted
检验触发器
update classinfo
set teachername=‘赵红’
where classno=1
实验结果：

在这里插入图片描述

游标实验

1：
实验目的：掌握T-SQL游标的设计、定义和使用方法。游标声明使用DECLARECURSOR语句，游标的使用包括打开游标、读取数据、关闭游标、删除游标等，分别使用OPEN、FETCH、CLOSE、DEALLOCATE语句。理解T-SQL游标按行操作和SQL按结果集操作的区别和联系。
2：实验内容：
1、建立一个游标，其功能为按学号升序列出选修课程的学生信息，包括学号、姓名、院系及其所修课程的名称和考试成绩。
1：准备数据<
create table studente
（sno nvarchar（13），
sname nvarchar (20),
sdept nvarchar (5),
sage int
）
)
insert into student
values（201215121，‘李明’，‘CS’，21），（201215122，'赵雪
'，‘MA’，20），（201215123，‘陈成’，‘IS’，21），（201215124，‘李峰’，‘CS’，19）
declare youbiao cursore
for
select student.sno, sname, sdept,course.cname, sc.grade
from student, course, sc
where student.sno=sC.sno and sC.cno=course.cno
order by student.sno

打开游标：
Open youbiao
声明游标提取数据要存放的变量
推进游标读取记录
Declare @Ysno nvarchar （255）
Declare @Yname nvarchar （20）
Declare @Ysdept nvarchar (5)
Declare @YCname nvarchar （30）
Declare @Ygrade nvarchar （10）
fetch next from youbiao into @Ysno,eYname,@Ysdept,@YCname,@Ygrade while（@@FETCH STATUS=0）
begin
print char (23)
print’学号∶’+@Ysno+’’+‘姓名∶’+@Yname+’’+‘院系∶’+@Ysdept+’‘课程∶’+@YCname+’ '+‘成绩∶’+@Ygrade
fetch next from youbiao into
@Ysno, @Yname, @Ysdept,@YCname,@Ygrade
end
–关闭游标
close youbiao
–释放游标←
deallocate youbiao
2、建立一个游标，其功能为根据价格设置每本书的等级。价格<50，等级：便宜；价格在50-100之间，等级：中等；价格>100，等级：贵。
-准备数据
create table LAPTO
(id int,
name nvarchar (50),
eu price decimal (18,2),
level nvarchar (10)
)
)
insert into LAPTOP (id, name, price)
values（1，‘数据库原理及应用’，10.00），（2，‘PYTHON程序设计’，45.00），（3，‘西游记’，67.60），，（4，‘三国演义’，149.50）
—声明游标–
Declare cur_set_lever cursor
For select id,price from LAPTOR
—打开游标-
Open cur_set_lever
–定义两个变量来存数据。注意，定义的变量的类型应该与数据库中对应字段的类型—致.
declare @id int
declare @price decimal （18，2）
–读取数据一
fetch next from cur_setlever into @id，@price
while（@@FETCH STATUS=0）
begin
if （@price<50）
update LAPTOP set level=‘便宜’ where ID=@id
else if(0price<100)
update LAPTOP set level=‘中等’ where ID=@ide
else
update LAPTOP set level=‘贵’ where ID=@ide
fetch next from cur _set lever into @id,eprice

关闭游标
Close cur_set_lever
删除/释放游标
Deallocate cur_set_lever
查询是否修改成功
Select *
From LAPTOR

实验结论：
学完这一章以后，我觉得得学会以下几个点：
1：声明游标
2：打开游标
3：使用游标
4：关闭游标
5：从一个游标中查找信息
6：了解游标的优点和缺点
7：游标的种类以及它的生命周期。

数据库安全

（实验目的、实验原理、实验步骤、内容、程序代码、实验数据、结论等）
1：实验目的：理解 SQL Server 2008 中数据库的安全管理问题。掌握身份验证、登陆账号、数据库用户、角色、权限的创建和使用方法。能够熟练设置身份验证模式，创建和删除登录账号、数据库用户、数据库角色，以及应用图形化界面方法和 T-SQL 语句进行权限的管理。
2：实验内容：一、新建登录名使用图形化界面方法和 T-SQL 新建三个登录名，分别是 U1、 U2、U3，要求使用登录名 U3 首次连接服务器时提示更改初始密码。
若是使用图形化界面分以下几个步骤：
1：连接数据库
2：选择安全性
3：选择登陆名
4：新建登录名
5：在弹出的新建登录名窗口，设置如下图：输入登录名和密码，禁用“强制实施密码策略”复选框，并选择“test”数据库。
6：点击左侧的状态，选中授予和启用，点击确定。
7：刷新登录名，在安全性→登录名中出现刚刚创建的 U1。

2：使用T-SQL 创建登录名
CREATE LOGIN U2 WITH PASSWORD = ‘123’,DEFAULT_DATABASE = test
需要指定数据库，要不就默认为master数据库
CREATE LOGIN U3 WITH PASSWORD = ‘123’ MUST_CHANGE, CHECK_EXPIRATION = ON, DEFAULT_DATABASE = test
二、创建用户使用图形化界面方法创建三个用户，分别是王平、李刚和陈红。这三个用户分别对应 U1、U2、U3 三个登陆名。
和上面的截图大致相同只需要需改人名即可
三、用户授权 1、把对表 SC 的 INSERT 权限授予用户王平，并允许将此权限再授予其他用户。
1：在查询窗口输入代码：
grant insert
on sc
to 王平
WITH GRANT OPTION
此时就已经将sc的insert权限授给王平了，可以双击用户王平，打开数据库用户窗口，点击左侧“安全对象”，就可以在右侧看到王平的权限。

2、用户王平将对表 SC 的 INSERT 权限授予用户李刚，并允许用户李刚将此权限再授予其他用户。
1：打开连接窗口,登陆名填“U1”，密码填创建 U1 时设置的密码，点击连接。
2：在对象资源管理器中找到 U1 实例。
3：点击 U1 实例，右键选择“新建查询”，并输入：
Grant insert
on sc
to 李刚
WITH GRANT OPTION
4:接着就可以查询李刚的权限了。
3、收回王平对 sc 表的 insert 权限，同时级联收回了李刚的 insert 权限。
REVOKE INSERT
ON SC
FROM 王平 cascade

四、创建角色
1、创建角色 R1
create role R1
2、给角色 R1 授予对 SC 表的 SELECT、UPDATE 和 INSERT 权限。
GRANT SELECT,UPDATE,INSERT
ON SC
TO R1

3、将角色 R1 所包含的全部权限授予用户王平和李刚。
EXEC sp_addrolemember ‘R1’,‘王平’
EXEC sp_addrolemember ‘R1’,‘李刚’

4、收回用户李刚具有的角色 R1 所包含的全部权限。

EXEC sp_droprolemember ‘R1’,‘李刚’
实验结论：
通过学习之后，对数据库的管理更加熟悉，学会了使用数据可创建用户，给用户赋值一定的权限，也会查询某用户的权限，让我对sqlserver的理解越来越深，动手能力也越来越强。

数据库的连接与访问

实验目的：理解和掌握SQL语句的语法，特别是各种参数的具体定义和使用方法；能够使用SQL语句对数据库中的基本表进行操作。

实验内容：我校教师管理数据库TeacherManagement，由下面四个表组成：教师(教师编号，姓名，性别，年龄，学历，工资)；学院(学院编号，学院名称)；工作(教师编号，学院编号，工作年限)；课程（课程编号，课程名称，任课教师编号）。关系名和属性名可用英文表示。
1、创建数据库TeacherManagement1、创建数据库TeacherManagement。
Create datebase Teachermanagement
2、分别创建教师表、学院表、工作表和课程表，表结构如下：
在这里插入图片描述

教师表。
create table 教师
(教师编号 char(7),
姓名 char(10),
性别 char(5),
年龄 int,
学历 char(10),
工资 int
)
create table 学院
(学院编号 char(5),
学院名称 char(20)
)
create table 工作表
(
教师编号 char(7),
学院编号 char(5),
工作年限 int
)
create table 课程表
( 教师编号 char(7),
课程名称 char(30),
任课老师编号 char(7)
2、向四个表中插入如下数据：
insert into 教师
values(‘z0001’,‘李晨’,‘男’,‘35’,‘本科’,‘4570’),
(‘z0002’,‘赵敏’,‘女’,‘24’,‘本科’,‘4100’),
(‘z0003’,‘李向阳’,‘男’,‘30’,‘研究生’,‘5500’),
(‘z0004’,‘赵海’,‘男’,‘23’,‘本科’,‘4000’),
(‘z0005’,‘孙玉清’,‘男’,‘38’,‘研究生’,‘5800’),
(‘z0006’,‘王慧敏’,‘女’,‘40’,‘研究生’,‘6780’),
(‘z0007’,‘张子萱’,‘女’,‘33’,‘本科’,‘4900’),
(‘z0008’,‘程浩’,‘男’,‘39’,‘研究生’,‘6000’),
(‘z0009’,‘李海’,‘男’,‘28’,‘本科’,‘4500’),
(‘z0010’,‘陈美美’,‘女’,‘32’,‘研究生’,‘5700’)
insert into 工作表
values(‘z0001’,‘101’,13),(‘z0002’,‘102’,1),(‘z0003’,‘101’,6),(‘z0004’,‘103’,1),(‘z0005’,‘102’,14),(‘z0006’,‘101’,16),(‘z0007’,‘103’,9),(‘z0008’,‘101’,15),(‘z0009’,‘102’,4),(‘z0010’,‘103’,8)
insert into 课程
values(‘01’,‘数据库原理及应用’,‘z0001’),(‘02’,‘计算机网络’,‘z0002’),(‘03’,‘操作系统’,‘z0001’),(‘04’,‘PYTHON程序设计’,‘z0001’),(‘05’,‘算法设计与分析’,‘z0002’),(‘06’,‘人工智能与大数据技术’,‘z0003’),(‘07’,‘数据挖掘与可视化’,‘z0002’),(‘08’,‘数据结构’,‘z0004’),(‘09’,‘专业英语’,‘z0005’),(‘10’,‘Java程序设计’,‘z0006’),(‘11’,‘C语言程序设计’,‘z0007’),(‘12’,‘大学数学’,‘z0003’),(‘13’,‘大学英语’,‘z0008’),(‘14’,‘教育学’,‘z0009’),(‘15’,‘文学赏析’,‘z0007’)
insert into 学院
values(‘101’,‘信息与工程学院’),(‘102’,‘通识学院’),(‘103’,‘设计与创意学院’)
3、使用SQL语句完成下列操作：
（1）查询信息与工程学院所有教师的编号、姓名和年龄。
select 教师.姓名,教师.教师编号,年龄
from 教师,学院,工作表
where 学院.学院编号=工作表.学院编号 and 工作表.教师编号 = 教师.教师编号 and 学院.学院名称=‘信息与工程学院’