大三数据库实验报告

最新推荐文章于 2024-10-11 14:41:05 发布

RunAtWorld

最新推荐文章于 2024-10-11 14:41:05 发布

阅读量6.1k

点赞数 1

分类专栏： DateBase 文章标签：数据库 sql database

本文链接：https://blog.csdn.net/RunAtWorld/article/details/40193399

版权

DateBase 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

数据库实用技术实验报告（一）

数据库的创建和管理

1：使用CREATE DATABASE创建一个新的数据库，名称为“STUDENT2”，其他所有参数均取默认值

2：创建一个名称为STUDENT3的数据库，该数据库的主文件逻辑名称为STUDENT3_data，物理文件名为STUDENT3.mdf，初始大小为3MB，最大尺寸为无限大，增长速度为15%；数据库的日志文件逻辑名称为STUDENT3_log，物理文件名为STUDENT3.ldf，初始大小为2MB，最大尺寸为50MB，增长速度为1MB；要求数据库文件和日志文件的物理文件都存放在E盘的DATA文件夹下。

Script

create database Student3

(name=Student3_data,

filename='E:\DataBase_MyHomework\Student3.mdf',

size=3,

maxsize=unlimited,

filegrowth=15

)

log on

(name=Student3_log,

filename='E:\DataBase_MyHomework\Student3.ldf',

size=2mb,

maxsize=50mb,

filegrowth=1mb)

Screenshot

3：创建一个指定多个数据文件和日志文件的数据库。该数据库名称为STUDENTS，有1个5MB和1个10MB的数据文件和2个5MB的事务日志文件。数据文件逻辑名称为STUDENTS1和STUDENTS2，物理文件名为STUDENTS1.mdf和STUDENTS2.ndf。主文件是STUDENTS1，由PRIMARY指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名为STUDENTSLOG1和STUDENTSLOG2，物理文件名为STUDENTSLOG1.ldf和STUDENTSLOG2.ldf，最大尺寸均为50MB，文件增长速度为1MB。要求数据库文件和日志文件的物理文件都存放在E盘的DATA文件夹下。

Script

create database test_Students

on primary

(name=Students1,

filename='E:\DataBase_MyHomework\Students1.mdf',

size=5,

maxsize=unlimited,

filegrowth=10%),

(name=Students2,

filename='E:\DataBase_MyHomework\Students2.mdf',

maxsize=100,

filegrowth=1)

log on

(name=Studentslog1,

filename='E:\DataBase_MyHomework\Studentslog1.ldf',

size=5mb,

maxsize=50mb,

filegrowth=1mb),

(name=Studentslog2,

filename='E:\DataBase_MyHomework\Studentslog2.ldf',

size=5mb,

maxsize=50mb,

filegrowth=1mb)

Screenshot

4：删除已创建的数据库STUDENTS。

5：将已存在的数据库STUDENT3重命名为STUDENT_BACK。

Script

drop database test_Students

exec sp_renamedb'Student3','Student_Back'

Screenshot

数据表的创建和管理

1：在数据库STUDENT中创建一个学生基本信息表（名为t_student），表中各列的要求如下表所示。表中的s_number字段创建非空约束，定义s_number为主键，其中s_name字段具有唯一性。输入性别字段值时，只能接受“男”或者“女”，而不能接受其他数据，sex字段创建默认约束“男”

表学生基本信息表（名为t_student）组成

字段名称	字段类型	大小	默认值
s_number	char	10
s_name	char	8
sex	char	2	男
birthday	datetime
polity	char	4

Script

create table T_Student

(S_number char(10) primary key,

S_name char(8) unique,

sex char(2) default '男'

constraint C1 check (sex in('男','女')),

Birthday datetime,

Polity char(4)

)

Screenshot

2：创建t_score表，各个字段：s_number,c_number ,score，并为t_score创建外键约束，该约束把表t_score中的学生学号（s_number）字段和表t_student中的学生学号（s_number）字段关联起来，实现t_score中的学生学号（s_number）字段的取值要参照表t_student中的学生学号（s_number）字段的数据值。

Script

create table T_Score

(Sno char(10),

Cno char(10),

Score char(10),

primary key(Sno,Cno),

foreign key(Sno) references [dbo].[T_Student](S_number)

)

Screenshot

3: 在数据库STUDENT中创建一个t_course1表，各个字段：c_number l,

c_name,hours,credit 。然后在表中增加一个c_pnumber字段，删除表中的credit字段，并且修改c_name字段的数据长度。

Script

create table T_Course1

(C_number char(10),

C_name char(20),

hours char(4),

credit smallint

)

ALTER TABLE T_Course1 add C_pnumber char(10)

ALTER TABLE T_Course1 drop COLUMN credit

ALTER table T_Course1 alter column C_name char(10)

Screenshot

4：（1）显示STUDENT数据库中所有对象的信息。（2）显示t_student数据表的信息。

Script

Screenshot

5：删除STUDENT数据库中的表

Script

DECLARE tables_cursor CURSOR --定义游标

FOR

SELECT name FROM sysobjects WHERE type = 'lpf' --选择用户表名

OPEN tables_cursor --打开游标连接

DECLARE @tablename sysname -- 定义变量

FETCH NEXT FROM tables_cursor INTO @tablename --结果集中一行一行读取表名

WHILE (@@FETCH_STATUS <> -1) --判断游标状态

BEGIN

EXEC ('TRUNECATE TABLE ' + @tablename) --清空表中的数据

FETCH NEXT FROM tables_cursor INTO @tablename --下一行数据

END

DEALLOCATE tables_cursor --关闭游标

Screenshot

数据库实用技术实验报告（二）

表中数据的操作和视图的创建和使用

假设T_STUDENT表中的数据如下表所示。

表 T_STUDENT表中的数据

S_NUMBER	S_NAME	SEX	BIRTHDAY	POLITY
B0451101	张小航	男	1984-12-20	党员
B0451102	王文广	男	1985-5-16	团员
B0451103	李艳红	女	1984-6-12	群众
B0451104	张丽霞	女	1984-7-22	群众
B0451105	王强	男	1984-11-26	党员
B0451106	张保田	男	1984-7-5	群众
B0451107	李博文	男	1984-8-9	团员
B0451108	刘芳芳	女	1985-4-14	党员
B0451109	李海	男	1984-2-16	团员
B0451110	常江宁	男	1984-3-21	群众

1：

（1）查询表中的所有记录

（2）查询前3条记录的S_NUMBER、S_NAME和BIRTHDAY字段

（3）查询所有记录的SEX字段，并去掉重复值

（4）查询所有记录的S_NUMBER（别名为学号）、S_NAME（别名为姓名）和BIRTHDAY（别名为出生日期）字段。

（5）查询得到每个学生的年龄

（6）统计男同学的人数

Script

USE Student_Back

SELECT * FROM T_Student

SELECT TOP(3) S_NUMBER,S_NAME,BIRTHDAY FROM T_Student

SELECT DISTINCT SEX FROM [dbo].[T_Student]

SELECT S_NUMBER AS '学号',S_NAME AS '姓名',BIRTHDAY AS '出生日期' FROM [dbo].[T_Student]

SELECT S_NUMBER AS '学号',S_NAME AS '姓名',DATEDIFF(YEAR,BIRTHDAY,GETDATE())AS '年龄' FROM [dbo].[T_Student]

SELECT COUNT(S_NUMBER) AS '男同学人数' FROM [dbo].[T_Student] WHERE SEX='男'

Screenshot

2：使用INTO子句创建一个新表（T_STUDENT1）。

Script

USE Student_Back

SELECT S_NAME AS '名字',POLITY AS '政治面貌',GETDATE() AS '注册时间'

INTO [dbo].[T_Student1]

FROM [dbo].[T_Student]

Screenshot

3：假设T_SCORE表中的数据如下表所示。

表 T_SCORE表中的数据

S_NUMBER	C_NUMBER	SCORE
B0451101	10010218	82
B0451102	10010218	75
B0451103	10010218	93
B0451104	10010218	81
B0451105	10010218	68
B0451106	10010218	77
B0451107	10010218	52
B0451108	10010218	85
B0451109	10010218	73
B0451110	10010218	87
B0451101	30020215	77
B0451102	30020215	84
B0451103	30020215	56

（1）查询表T_SCORE和表T_STUDENT中的数据，满足条件：

T_STUDENT.S_NUMBER=T_SCORE.S_NUMBER

Script

USE Student_Back

SELECT * FROM [dbo].[T_Student],[dbo].[T_Score]

WHERE T_Score.S_NUMBER = T_Student.S_NUMBER

Screenshot

（2）上述查询也可以用以下方法实现：联合查询表T_SCORE和表T_STUDENT中的数据。

Script

USE Student_Back

SELECT * FROM [dbo].[T_Student] INNER JOIN [dbo].[T_Score]

ON T_Score.S_NUMBER = T_Student.S_NUMBER

Screenshot

4：从表T_SCORE中查询不及格学生的信息。

Script

USE Student_Back

SELECT [dbo].[T_Student].S_NUMBER AS '学号',S_NAME AS '姓名',SCORE AS '成绩'

FROM [dbo].[T_Student] ,[dbo].[T_Score]

WHERE [dbo].[T_Student].S_NUMBER=[dbo].[T_Score].S_NUMBER AND SCORE<60

Screenshot

5：从表T_STUDENT中查询是党员的男学生的信息。

Script

USE Student_Back

SELECT * FROM [dbo].[T_Student]

WHERE SEX = '男' AND POLITY = '党员'

Screenshot

6：从表T_SCORE中查询成绩在80~90分的学生的信息。

Script

USE Student_Back

SELECT * FROM [dbo].[T_Student] INNER JOIN [dbo].[T_Score]

ON [dbo].[T_Student].S_NUMBER=[dbo].[T_Score].S_NUMBER

WHERE SCORE BETWEEN 80 AND 90

Screenshot

7：从表T_STUDENT中查询所有非党员的学生信息。

Script

USE Student_Back

SELECT * FROM [dbo].[T_Student]

WHERE POLITY != '党员'

Screenshot

8：从表T_STUDENT中查询所有姓张的学生信息。

Script

USE Student_Back

SELECT * FROM [dbo].[T_Student]

WHERE S_NAME LIKE '李%'

Screenshot

9：对表T_STUDENT中的记录按性别进行分组，查询相应的人数。

Script

USE Student_Back

SELECT [SEX],COUNT(SEX) AS QUENTITY FROM [dbo].[T_Student]

GROUP BY SEX

Screenshot

10：在表T_SCORE中求选修了两门及以上课程的学生学号。

Script

USE Student_Back

SELECT S_NUMBER AS '学号',COUNT(S_NUMBER)AS '选课门数' FROM T_Score

GROUP BY S_NUMBER

HAVING COUNT(S_NUMBER)>1

Screenshot

11：在表T_SCORE中查询选修了“10010218”课程的学生成绩，并按成绩的降序进行排序。

Script

USE Student_Back

SELECT [dbo].[T_Student].[S_NUMBER],[S_NAME],C_NUMBER,SCORE FROM [dbo].[T_Student] INNER JOIN [dbo].[T_Score]

ON [dbo].[T_Student].S_NUMBER=[dbo].[T_Score].S_NUMBER

WHERE C_NUMBER ='10010218'

ORDER BY SCORE DESC

Screenshot

12：假设表T_STUDENT1中的数据如下表所示。对表T_STUDENT1和表T_STUDENT进行联合查询。

表 T_STUDENT1中的数据

S_NUMBER	S_NAME	SEX	BIRTHDAY	POLITY
0554101	刘洋	男	1984-10-14	党员
0554102	李志娟	女	1984-11-12	团员
0554103	赵雪梅	女	1984-6-25	团员
0554104	王华	女	1984-9-11	团员
0554105	张金辉	男	1985-5-16	群众
0554106	贾波	男	1984-10-3	党员
0554107	周萍	女	1984-7-12	群众
0554108	马帅	男	1984-5-22	团员
0554109	唐芸	女	1985-8-25	群众
0554110	王华	男	1984-1-20	团员

13：查询选修了两门及以上课程的学生的基本信息。

Script

USE Student_Back

SELECT * FROM T_Student WHERE EXISTS

(SELECT S_NUMBER,COUNT(S_NUMBER)AS '选课门数' FROM T_Score

WHERE T_Student.S_NUMBER= T_Score.S_NUMBER

GROUP BY S_NUMBER

HAVING COUNT(S_NUMBER)>1)

Screenshot

14：利用INSERT语句向表T_STUDENT中插入一行数据，只包含S_NUMBER、S_NAME和BIRTHDAY三列。

Script

INSERT INTO [Student_Back].[dbo].[T_Student]

([S_NUMBER],[S_NAME] ,[BIRTHDAY])

VALUES

('B0451110','毛小驴',1992-12-23)

Screenshot

15：利用INSERT语句向表T_STUDENT中插入一行数据，所有的字段都要给出相应的值。

Script

INSERT INTO [Student_Back].[dbo].[T_Student]

VALUES ('B0451111','吕大布','男',1990-12-23,'党员')

Screenshot

16：利用INSERT语句向表T_STUDENT中插入一批数据，数据来源于另一个已有的数据表。

Script

USE Student_Back

INSERT INTO T_Student([S_NUMBER],[S_NAME],[SEX],[BIRTHDAY],[POLITY])

SELECT [S_NUMBER],[S_NAME],[SEX],[BIRTHDAY],[POLITY] FROM T_Student1

Screenshot

17：一个带有WHERE条件的修改语句。

Script

USE Student_Back

UPDATE T_Student SET SEX='男' WHERE S_NAME='毛小驴'

Screenshot

18：一个简单的修改语句。

Script

USE Student_Back

UPDATE T_Student SET SEX='男' WHERE S_NAME='毛小驴'

Screenshot

19：一个简单的删除语句。

Script

USE Student_Back

DELETE FROM T_Student1 WHERE S_NAME='毛小驴'

Screenshot

20：一个没有WHERE条件的删除语句。

Script

USE Student_Back

DELETE FROM T_Student1

Screenshot

21：创建一个视图，要求显示T_STUDENT表中所有姓张的同学，并按性别进行升序排序。

Script

USE Student_Back

CREATE VIEW NAME_Li AS (SELECT * FROM T_Student WHERE S_NAME LIKE '李%' )

SELECT * FROM NAME_Li ORDER BY SEX ASC

Screenshot

22：使用Transact-SQL语句创建一个新视图，命名为VIEW_SCORE，要求基表的来源为：T_STUDENT、T_SCORE和T_COURSE，选择的字段为：T_STUDENT表中的S_NUMBER和S_NAME字段、T_COURSE表中的C_NAME字段及T_SCORE表中的SCORE字段，要求查询的数据为05541班学生的考试成绩。

Script

USE Student_Back

CREATE VIEW VIEW_SCORE AS

SELECT T_Student.S_NUMBER,T_Student.S_NAME,T_Score.SCORE

FROM T_Student,T_Score

WHERE T_Student.S_NUMBER=T_Score.S_NUMBER

SELECT * FROM VIEW_SCORE WHERE S_NUMBER LIKE '%05541%'

Screenshot

23：使用Transact-SQL语句创建新视图VIEW_SCORE_NotPass。对数据表和对字段的选择同上，要求只显示各班不及格的学生的学号、姓名、课程名及成绩。并加密视图的定义。

Script

USE Student_Back

CREATE VIEW VIEW_SCORE_NotPass

WITH ENCRYPTION

(SELECT T_Student.S_NUMBER,T_Student.S_NAME,T_Score.SCORE

FROM T_Student,T_Score

WHERE T_Student.S_NUMBER=T_Score.S_NUMBER AND SCORE<60)

SELECT [S_NUMBER],[S_NAME],[SCORE]

FROM [Student_Back].[dbo].[VIEW_SCORE_NotPass]

Screenshot

24：使用Transact-SQL语句创建新视图VIEW_SCORE_SumAndAverage。要求计算各个班级各门课程的总分及平均分。

Script

USE Student_Back

CREATE VIEW VIEW_SCORE_SumAndAverage

(SELECT AVG(SCORE) AS 平均成绩

FROM T_Student,T_Score

WHERE T_Student.S_NUMBER=T_Score.S_NUMBER)

SELECT 平均成绩

FROM VIEW_SCORE_SumAndAverage

Screenshot

25：使用系统存储过程sp_help显示VIEW_SCORE视图的特征信息。

Script

sp_help VIEW_SCORE

Screenshot

26：使用sp_helptext显示VIEW_SCORE视图在系统表中的定义。

Script

sp_helptext VIEW_SCORE

Screenshot

27：查看加密视图的定义信息。

Script

sp_help VIEW_SCORE_NotPass

Screenshot

28：使用sp_depends显示VIEW_SCORE视图所依赖的对象。

Script

sp_depends VIEW_SCORE

Screenshot

29：修改视图VIEW_SCORE_NotPass，在该视图中增加一个新的条件，要求只显示高等数学不及格的学生信息。

Script

USE Student_Back

ALTER VIEW VIEW_SCORE_NotPass

(SELECT T_Student.S_NUMBER,T_Student.S_NAME,T_Score.SCORE,T_Student.SEX

FROM T_Student,T_Score

WHERE T_Student.S_NUMBER=T_Score.S_NUMBER AND SCORE<60)

SELECT [S_NUMBER],[S_NAME],[SCORE],[SEX]

FROM [Student_Back].[dbo].[VIEW_SCORE_NotPass]

Screenshot

30：使用系统存储过程sp_rename来完成把视图“VIEW_SCORE_NotPass”重命名为“VIEW_SCORE_NotPass_高等数学”。

Script

sp_rename VIEW_SCORE_NotPass,VIEW_SCORE_NotPass_高数

Screenshot

31：创建一个基于表T_STUDENT的新视图VIEW_STUDENT_05541，要求包含05541班的学生记录。

Script

USE Student_Back

CREATE VIEW VIEW_Student_05541

(SELECT *

FROM T_Student

WHERE S_NUMBER LIKE '5541%')

SELECT *

FROM VIEW_Student_05541

Screenshot

32：首先创建一个包含限制条件的视图VIEW_STUDENT_05541_男，限制条件为显示05541班的男同学信息，然后插入了一条不满足限制条件的记录，再用SELECT语句检索视图和表。

Script

USE Student_Back

CREATE VIEW VIEW_Student_05541_男

(SELECT *

FROM T_Student

WHERE S_NUMBER LIKE '5541%' AND SEX='男' )

WITH CHECK OPTION

SELECT *

FROM VIEW_Student_05541_男

INSERT INTO VIEW_Student_05541_男 VALUES('554119','BOS','男','1984-10-14','党员')

Screenshot

33：在上例的基础上添加WITH CHECK OPTION选项。

Script

见 32

Screenshot

34：创建一个基于表T_STUDENT的视图VIEW_STUDENT_05541_Modify，条件是显示05541班的学生的学号、姓名和性别，然后通过该视图修改表T_STUDENT中的记录。

Script

USE Student_Back

CREATE VIEW VIEW_Student_05541_Modify

(SELECT T_Student.S_NUMBER, T_Student.S_NAME,T_Student.SEX

FROM T_Student

WHERE S_NUMBER LIKE '5541%' )

Update VIEW_Student_05541_Modify SET S_NAME='Liu洋' WHERE S_NAME='刘洋'

SELECT *

FROM VIEW_Student_05541_Modify

Screenshot

35：利用视图VIEW_STUDENT_05541，先插入一条记录，然后删除此条记录。

Script

USE Student_Back

CREATE VIEW VIEW_Student_05541_Modify

(SELECT T_Student.S_NUMBER, T_Student.S_NAME,T_Student.SEX

FROM T_Student

WHERE S_NUMBER LIKE '5541%' )

INSERT VIEW_Student_05541_Modify VALUES('554111','Bottec','女')

SELECT *

FROM VIEW_Student_05541_Modify

DELETE FROM VIEW_Student_05541_Modify WHERE S_NUMBER='554111'

Screenshot

36：删除视图VIEW_STUDENT_05541_Modify和视图VIEW_STUDENT_05541_男。

Script

USE Student_Back

DROP VIEW VIEW_Student_05541_Modify

DROP VIEW VIEW_Student_05541_男

Screenshot

数据库实用技术实验报告（三）

Transact-SQL程序设计

1：创建了一个变量@CurrentDateTime，然后将GETDATE()函数的值放在变量中，最后输出@CurrentDateTime变量的值。

Script

DECLARE @Current DateTime

SET @Current = GETDATE()

SELECT @Current

Screenshot

2：查询T_STUDENT表，将返回的记录数赋给变量@RowsReturn。

Script

DECLARE @RowsReturn INT

SET @RowsReturn =( SELECT COUNT(S_NUMBER) FROM T_Student)

SELECT @RowsReturn AS '记录总数'

Screenshot

3：在SELECT语句中使用由SET赋值的变量。

Script

DECLARE @name char(20)

SET @name =(SELECT S_NAME FROM T_Student WHERE S_NUMBER = '554101')

SELECT @name

Screenshot

4：在T_SCORE表中，求05541班学生高等数学课程的最高分和最低分的学生信息，包括学号、姓名、课程名、成绩四个字段。

Script

USE Student_Back

DECLARE @ScoreMax FLOAT,@ScoreMin FLOAT

SELECT @ScoreMax = MAX(SCORE) FROM T_Score

SELECT @ScoreMin = MIN(SCORE) FROM T_Score

SELECT T_Student.S_NUMBER AS '学号',S_NAME AS '姓名',T_Course.CourseName AS '课程名',Score '成绩'

FROM T_Student,T_Score,T_Course

WHERE T_Student.S_NUMBER = T_Score.S_NUMBER AND T_Course.C_NUMBER = T_Score.C_NUMBER

AND Score=@ScoreMax

SELECT T_Student.S_NUMBER AS '学号',S_NAME AS '姓名',T_Course.CourseName AS '课程名',Score '成绩'

FROM T_Student,T_Score,T_Course

WHERE T_Student.S_NUMBER = T_Score.S_NUMBER AND T_Course.C_NUMBER = T_Score.C_NUMBER

AND Score=@ScoreMin

Screenshot

5：计算表达式的值，并将结果赋给变量@ExpResult。

Script

DECLARE @ExpResult FLOAT,@x INT,@y INT

SET @x=4;

SET @y=5;

SET @ExpResult=@x * @y

PRINT '结果'

PRINT @ExpResult

Screenshot

6：查询05541班的学生信息，要求列出的字段为：班级、本班内的学号、姓名、性别、出生日期、政治面貌。

Script

USE Student_Back

SELECT SUBSTRING([S_NUMBER],1,4) AS '班级'

,RIGHT([S_NUMBER],2) AS '班内学号'

,[S_NAME]

,[SEX]

,[BIRTHDAY]

,[POLITY]

FROM [Student_Back].[dbo].[T_Student]

WHERE S_NUMBER LIKE '5541%'

Screenshot

7：使用比较运算符计算表达式的值。

Script

USE Student_Back

SELECT [S_NUMBER]

,[S_NAME]

,[SEX]

,[BIRTHDAY]

,[POLITY]

FROM [Student_Back].[dbo].[T_Student]

WHERE BIRTHDAY >= '1985-01-01 00:00:00.000'

Screenshot

8：使用比较运算符计算表达式的值。

Script

USE Student_Back

SELECT [S_NUMBER]

,[S_NAME]

,[SEX]

,[BIRTHDAY]

,[POLITY]

FROM [Student_Back].[dbo].[T_Student]

WHERE BIRTHDAY >=

(SELECT BIRTHDAY FROM [Student_Back].[dbo].[T_Student] WHERE S_NAME = '王文广')

Screenshot

9：使用字符串连接运算符计算表达式的值。

Script

USE Student_Back

SELECT '编号：'+[S_NUMBER]+' 姓名：'+[S_NAME] AS '新列'

,[SEX]

,[BIRTHDAY]

,[POLITY]

FROM [Student_Back].[dbo].[T_Student]

WHERE BIRTHDAY >=

(SELECT BIRTHDAY FROM [Student_Back].[dbo].[T_Student] WHERE S_NAME = '王文广')

Screenshot

10：使用括号改变表达式的计算顺序。

Script

DECLARE @X INT,@Y INT

DECLARE @Z INT

SET @X=3

SET @Y=30

SET @Z=@X

PRINT ('Y='+CAST(@Y AS VARCHAR))

IF (@X*(@Y+@Z)>1)

PRINT ('结果为：'+CAST((@X*(@Y+@Z)) AS VARCHAR))

Screenshot

11：从STUDENT数据库中返回T_STUDENT表的第4列的名称。

Script

sp_help T_Student

Screenshot

12：从STUDENT数据库中返回T_STUDENT表的S_NUMBER列的长度。

Script

sp_help T_Student

Screenshot

13：检查sysdatabases中的每一个数据库，使用数据库标识号来确定数据库名称。

Script

USE master

SELECT dbid, DB_NAME(dbid) AS DB_NAME

FROM sysdatabases

ORDER BY dbid

Screenshot

14：在T_STUDENT表中先插入三条新记录，其中的POLITY字段的值为NULL，要求对记录进行查询时，对应的NULL值在显示时显示“群众”。

Script

INSERT INTO T_Student values

('872391','吴中天','男',GETDATE(),NULL)

INSERT INTO T_Student values

('872392','屈大器','男',GETDATE(),NULL)

INSERT INTO T_Student values

('872391','叶则','女',GETDATE(),NULL)

SELECT [S_NUMBER]

,[S_NAME]

,[SEX]

,[BIRTHDAY]

,'群众' AS POLITY

FROM [Student_Back].[dbo].[T_Student]

WHERE POLITY IS NULL

Screenshot

15：利用@@ERROR返回程序的错误代码。

Script

print @@ERROR

Screenshot

16：显示到当前日期和时间为止试图登录SQL Server的次数。

Script

Screenshot

17：返回在程序执行过程中上一条SQL语句影响的记录数。

Script

print @@ROWCOUNT

Screenshot

18：返回SQL Server安装的日期、版本和处理器类型。

Script

print @@VERSION

Screenshot

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)

Mar 29 2009 10:11:52

Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

19：使用RTRIM函数删除字符型字段中的尾部空格。

Script

DECLARE @S VARCHAR(20)

SET @S='BABSDDD '

SELECT RTRIM(@S)

Screenshot

20：使用SUBSTRING函数、LEFT函数和RIGHT函数求T_STUDENT中的班级名称和学号。

Script

SELECT SUBSTRING(S_NUMBER,1,6) AS 班级,RIGHT(S_NUMBER,2)AS 学号,S_NAME

FROM T_STUDENT

Screenshot

21：在第一个字符串（Good Morning）中删除从第6个位置（字符M）开始的7个字符，然后在删除的起始位置插入第二个字符串（Afternoon），创建并返回一个新的字符串。

Script

DECLARE @S1 VARCHAR(20)

DECLARE @S2 VARCHAR(20)

SET @S1='Good Morning'

SET @S2='Afternoon'

PRINT STUFF(@S1,6,7,@S2)

PRINT '@S1:'+@S1

PRINT '@S2:'+@S2

Screenshot

Good Afternoon

@S1:Good Morning

@S2:Afternoon

22：查询T_STUDENT表的学生信息，要求显示的字段为：学号、姓名、性别和学生的年龄。

Script

SELECT TOP 1000 [S_NUMBER]

,[S_NAME]

,[SEX]

,CAST(BIRTHDAY AS DATE) AS 生日

,DATEDIFF(year,BIRTHDAY,GETDATE()) AS 'Age'

FROM [Student_Back].[dbo].[T_Student]

Screenshot

23：在T_STUDENT表中查询每个学生的出生月份。

Script

SELECT [S_NUMBER]

,[S_NAME]

,[SEX]

,CAST(BIRTHDAY AS DATE) AS 生日

,MONTH(BIRTHDAY) AS '生日月份'

FROM [Student_Back].[dbo].[T_Student]

Screenshot

24：从系统当前日期中返回系统当前的年份数、月份数和天数。

Script

PRINT 'YEAR:'+CAST(YEAR(GETDATE()) AS VARCHAR(20))

PRINT 'MONTH:'+CAST(MONTH(GETDATE()) AS VARCHAR(20))

PRINT 'DAY:'+CAST(DAY(GETDATE()) AS VARCHAR(20))

Screenshot

YEAR:2014

MONTH:11

DAY:7

25：在同一表达式中使用CEILING（），FLOOR（），ROUND（）函数。

Script

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)

SELECT FLOOR($123.45), FLOOR($-123.45), FLOOR($0.0)

GOSELECT ROUND(123.4545, 2);

SELECT ROUND(123.45, -2);

Screenshot

26：查询T_STUDENT表中出生年份为1985年的学生信息。

Script

SELECT TOP 1000 [S_NUMBER]

,[S_NAME]

,[SEX]

,[BIRTHDAY]

,[POLITY]

FROM [Student_Back].[dbo].[T_Student]

WHERE YEAR(BIRTHDAY)=1985

Screenshot

27：编写一个用户自定义函数fun_SumScores，要求根据输入的班级号和课程号，求此班此门课程的总分。

Script

USE Student_Back

CREATE FUNCTION fun_SumScores(@Class_id varchar(10),@Course_id float)

RETURNS float

AS BEGIN

DECLARE @sum_score float

SELECT @sum_score=SUM(SCORE) FROM T_Score

WHERE S_NUMBER LIKE @Class_id+'%' AND C_NUMBER = @Course_id

RETURN @sum_score

END

SELECT [dbo].[fun_SumScores]('B04511',10010218)

Screenshot

28：使用自定义函数fun_SumScores，求T_SCORE表中的各个班级的各门课程的总分。

Script

PRINT [dbo].[fun_SumScores]('B04511',10010218)

PRINT [dbo].[fun_SumScores]('B04511',30020215)

Screenshot

773

217

29：根据T_SCORE表中的考试成绩，查询05541班学生高等数学的平均成绩，并根据平均成绩输出相应的提示信息。

Script

USE Student_Back

DECLARE @c_id float

SELECT @c_id=C_NUMBER FROM T_Course

WHERE CourseName='高等数学'

SELECT AVG(SCORE) AS '平均成绩'

FROM T_Score

WHERE C_NUMBER=@c_id AND S_NUMBER LIKE '%04511%'

Screenshot

30：查询05541班学生的考试情况，并使用CASE语句将课程号替换为课程名进行显示。

Script

DECLARE @c float

DECLARE @S VARCHAR(20)

SET @c=10010218

SELECT @S=

CASE @c

WHEN 10010218 THEN '高等数学'

WHEN 10010215 THEN '大学英语'

WHEN 10010219 THEN '大学物理'

END

SELECT @S AS '结果'

Screenshot

31：查询05541班学生的考试情况，并根据考试分数输出考试等级，当分数大于等于90分，输出“优”，当分数在80至90之间，输出“良”，当分数在70至80之间，输出“中”，当分数在60至70之间，输出“及格”，当分数在60分以下，输出“不及格”。

Script

USE Student_Back

-------------------------判断等级的函数---------------------

CREATE FUNCTION GradeRank(@g float)

RETURNS VARCHAR(8)

AS BEGIN

DECLARE @r VARCHAR(8)

SET @r=

CASE

WHEN @g>=90 THEN '优'

WHEN @g>=80 THEN '良'

WHEN @g>=70 THEN '中'

WHEN @g>=60 THEN '及格'

ELSE '不及格'

END

RETURN @r

END

-------------使用游标逐行取出成绩做判断并输出等级-----------------------

DECLARE CUR SCROLL CURSOR

FOR

SELECT S_NUMBER,Score FROM T_Score

OPEN CUR

DECLARE @num VARCHAR(10)

DECLARE @tmp float

BEGIN

FETCH NEXT FROM CUR INTO @num,@tmp

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @num AS '学号',S_NAME AS '姓名',@tmp AS '成绩',[dbo].[GradeRank](@tmp) AS '等级'

FROM T_Student,T_Score WHERE T_Student.S_NUMBER=T_Score.S_NUMBER AND T_Student.S_NUMBER=@num AND C_NUMBER=10010218

FETCH NEXT FROM CUR INTO @num,@tmp

END

CLOSE CUR

DEALLOCATE CUR

Screenshot

32：使用WHILE…CONTINUE…BREAK语句求5的阶乘。

Script

DECLARE @A float

DECLARE @i int

SET @i=1

SET @A=1

WHILE @i<5

BEGIN

SET @A=@A*(@i+1)

SET @i=@i+1

IF @A>20

BEGIN

PRINT @A;CONTINUE;

END

Screenshot

120

33：在程序中使用WAITFOR语句，使WAITFOR后面的语句等待10秒钟后再继续执行。

Script

DECLARE @i int

SET @i=1

WHILE @i<30

BEGIN

PRINT @i

PRINT CAST(GETDATE() AS datetime2)

WAITFOR DELAY '00:00:00.010'

SET @i=@i+1

END

Screenshot

2014-11-07 20:56:02.5470000

2014-11-07 20:56:02.5570000

2014-11-07 20:56:02.5670000

2014-11-07 20:56:02.5770000

2014-11-07 20:56:02.5870000

2014-11-07 20:56:02.5970000

2014-11-07 20:56:02.6070000

2014-11-07 20:56:02.6170000

2014-11-07 20:56:02.6270000

2014-11-07 20:56:02.6370000

2014-11-07 20:56:02.6470000

2014-11-07 20:56:02.6570000

2014-11-07 20:56:02.6670000

2014-11-07 20:56:02.6770000

2014-11-07 20:56:02.6870000

2014-11-07 20:56:02.6970000

2014-11-07 20:56:02.7070000

2014-11-07 20:56:02.7170000

2014-11-07 20:56:02.7270000

2014-11-07 20:56:02.7370000

2014-11-07 20:56:02.7470000

2014-11-07 20:56:02.7570000

2014-11-07 20:56:02.7670000

2014-11-07 20:56:02.7770000

2014-11-07 20:56:02.7870000

2014-11-07 20:56:02.7970000

2014-11-07 20:56:02.8070000

2014-11-07 20:56:02.8170000

2014-11-07 20:56:02.8270000

34：编写一个自定义函数，功能是查询给定姓名的学生，如果没有找到则返回0，否则返回满足此条件的学生人数，主程序调用这个函数，查询姓名为“王华”的学生，并根据函数的返回值进行输出。

Script

USE Student_Back

CREATE FUNCTION Query(@s varchar(10))

RETURNS int

AS BEGIN

DECLARE @i int

SELECT @i=COUNT(S_NUMBER) FROM T_Student WHERE S_NAME=@s

RETURN @i

END

PRINT '姓名为王华的人数：'+ CONVERT(CHAR,[dbo].[Query]('王华'))

Screenshot

数据库实用技术实验报告（四）

存储过程的创建和使用

1：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：在T_STUDENT表中查询05541班学生的学号、姓名、性别、出生日期和政治面貌五个字段的内容。

Script

CREATE PROCEDURE StuInfo

BEGIN

SELECT [S_NUMBER],[S_NAME],[SEX],[BIRTHDAY],[POLITY] FROM T_Student WHERE S_NUMBER LIKE '5541%'

END

EXEC StuInfo

Screenshot

2：创建一个存储过程StuScoreInfo，完成的功能是在表T_STUDENT、表T_COURSE和表t_SCORE中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。

Script

USE Student_Back

CREATE PROCEDURE StuScoreInfo

BEGIN

SELECT substring(T_Student.S_NUMBER,1,5) as 班级,T_Student.S_NUMBER,S_NAME,SEX,CourseName,SCORE

FROM [T_Student],[T_Course],[T_Score]

WHERE T_Student.S_NUMBER=T_Score.S_NUMBER and T_Course.C_NUMBER=T_Score.C_NUMBER

END

exec StuScoreInfo

Screenshot

3：创建一个带有参数的存储过程Stu_Info，该存储过程根据传入的学生编号，在T_STUDENT中查询此学生的信息。

Script

USE Student_Back

CREATE PROCEDURE StuInfo

@num nvarchar(100)

BEGIN

SELECT * FROM [Student_Back].[dbo].[T_Student] where S_NUMBER=@num

END

exec StuInfo'B0451101'

Screenshot

4：创建一个带有参数的存储过程Stu_Age，该存储过程根据传入的学生编号，在T_STUDENT中计算此学生的年龄，并根据程序的执行结果返回不同的值，程序执行成功，返回整数0，如果执行出错，则返回错误号。

Script

USE Student_Back

CREATE PROCEDURE Stu_Age

@num nvarchar(100)

BEGIN

SELECT S_NUMBER,datediff(year,BIRTHDAY,getdate()) as 年龄

FROM [Student_Back].[dbo].[T_Student] where S_NUMBER=@num

if (@@error!=0)

return 0;

else

return @@error;

END

Screenshot

5：行前面创建的StuInfo存储过程，它是一个无参的存储过程。

Script

create PROCEDURE [dbo].[StuInfo3]

BEGIN

SELECT * FROM [T_Student]

END

exec [dbo].[StuInfo3]

Screenshot

6：存储过程StuScoreInfo。

Script

exec StuScoreInfo

Screenshot

7：执行存储过程Stu_Info，该存储过程有一个输入参数“学号”，在执行时需要传入一个学号值。

Script

exec StuInfo'B0451101'

Screenshot

8：执行存储过程Stu_Age，该存储过程有一个输入参数“学号”，另外，还有一个输出参数@Age。存储过程执行完后，有一个返回的状态值，这个值可以从变量@ErrorValue得到。

Script

USE Student_Back

alter PROCEDURE Stu_Age

@num nvarchar(100),

@age int out

BEGIN

SELECT S_NUMBER,datediff(year,BIRTHDAY,getdate()) as 年龄

FROM [Student_Back].[dbo].[T_Student] where S_NUMBER=@num

SELECT @age=datediff(year,BIRTHDAY,getdate())

FROM [Student_Back].[dbo].[T_Student] where S_NUMBER=@num

if (@@error!=0)

return 0;

else

return @@error;

END

DECLARE @age int

exec Stu_Age'B0451101',@age out;

SELECT @age as GetAge;

Screenshot

9：使用系统存储过程查看Stu_Age存储过程的参数及其数据类型。

Script

exec sp_helpStu_Age

Screenshot

10：使用系统存储过程查看StuScoreInfo存储过程的相关的数据库对象。

Script

exec sp_dependsStuScoreInfo

Screenshot

11：修改前面创建的Stu_Info存储过程，使之完成以下功能：根据传入的学号，在表T_STUDENT、表T_COURSE和表t_SCORE中查询此学生的班级、学号、姓名、性别、考试课程名称和考试分数。

Script

ALTER PROCEDURE [StuInfo]

@num nvarchar(100)

BEGIN

SELECT substring(T_Student.S_NUMBER,1,5) as 班级,T_Student.S_NUMBER,S_NAME,SEX,CourseName,SCORE

FROM [T_Student],[T_Course],[T_Score]

WHERE T_Student.S_NUMBER=T_Score.S_NUMBER and T_Course.C_NUMBER=T_Score.C_NUMBER and T_Student.S_NUMBER=@num

END

EXEC StuInfo'B0451101'

Screenshot

12：使用系统存储过程将Stu_Info存储过程的名称修改为Stu_Info_SCORE。

Script

exec sp_rename'StuInfo','Stu_Score_Info'

Screenshot

13：使用DROP命令删除StuInfo和StuScoreInfo两个存储过程。

Script

drop proc Stu_Score_Info

drop proc StuScoreInfo1

Screenshot

数据库实用技术实验报告（五）

触发器的创建和使用

1：创建一个INSERT触发器“TR_Stu_Insert”，当在T_STUDENT表中插入一条新记录时，触发该触发器，并给出“你插入了一条新记录！”的提示信息。

Script

USE Student_Back

CREATE TRIGGER TR_Stu_Insert

ON T_STUDENT

AFTER insert

BEGIN

print '您插入了一条新纪录！'

END

insert into T_Student values ('B0451200','Hec', '男',GETDATE(),'党员')

Screenshot

2：创建了Stu_Insert触发器后，查看向T_STUDENT表中插入数据时，此触发器所完成的功能。

Script

insert into T_Student values ('B0451200','Hec', '男',GETDATE(),'党员')

Screenshot

3：创建一个AFTER触发器，要求实现以下功能：在T_SCORE表上创建一个插入、更新类型的触发器TR_ScoreCheck，当在SCORE字段中插入或修改考试分数后，触发该触发器，检查分数是否在0-100之间。

Script

USE Student_Back

create TRIGGER TR_ScoreCheck

ON T_SCORE

for update,insert

BEGIN

declare @score int;

if UPDATE(Score)

begin

select @score=(select Score from inserted)

if (@score not between 0 and 100)

print('输入分数应在-100之间，请重新输入！');

rollback;

end

END

insert into T_SCORE values ('B0451101','10010218',103)

Screenshot

4：创建一个AFTER触发器，要求如下：在T_STUDENT表上创建一个删除类型的触发器TR_Stu_Delete，当在T_STUDENT表中删除某一条记录后，触发该触发器，在T_SCORE表中删除与此学号对应的记录。

Script

USE Student_Back

create TRIGGER TR_Stu_Delete

ON T_STUDENT

for delete

BEGIN

declare @num nvarchar(100);

select @num =(select s_number from deleted)

delete from T_Score where S_NUMBER=@num;

END

delete from T_Student where S_NUMBER='B0451101';

Screenshot

5：创建一个INSTEAD OF触发器，要求实现以下功能：在T_COURSE表上创建一个删除类型的触发器TR_NotAllowDelete，当在T_COURSE表中删除记录时，触发该触发器，显示不允许删除表中数据的提示信息。

Script

USE Student_Back

create TRIGGER TR_NotAllowDelete

ON T_COURSE

for delete

BEGIN

print '不允许删除该表信息';

rollback;

END

delete from T_COURSE where C_NUMBER='10010218';

Screenshot

6：使用系统存储过程查看TR_Stu_Insert触发器的一般信息。

Script

exec sp_help TR_Stu_Insert

Screenshot

7：使用系统存储过程查看T_STUDENT表涉及到的所有触发器。

Script

select * from dbo.sysobjects

where OBJECTPROPERTY(id,N'IStrigger') =1

Screenshot

8：使用系统命令删除TR_Stu_Insert触发器。

Script

drop trigger TR_Stu_Insert

Screenshot

SQL Server的安全性管理

1：创建一个 SQL Server 登录帐户USER1，密码为Abc123#$。

2：删除SQL Server帐户USER1。

3：在STUDENT数据库中为SQL Server登录帐户USER1添加数据库用户，并取名为USER1，默认架构为STU。

4：从STUDENT数据库中删除数据库用户USER1。

5：为STUDENT数据库新创建一个数据库用户USER2，并为其赋予查询T_COURSE表的权限。

6：将对数据库STUDENT中T_SCORE表的SELECT权限授予数据库用户USER2。

7：拒绝USER2对T_COURSE表中的C_NUMBER列的SELECT权限。

8：收回USER2对T_SCORE表的SELECT权限。

9：将在STUDENT数据库中创建数据表的权限授予用户USER2。

Script

Screenshot

RunAtWorld

关注

1
点赞
踩
15

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录