数据库实验笔记

发布于 / 笔迹 / 2 条评论

不保证自己写的一定是正确的,所以仅供参考。若文中存在错误,请帮忙指出,共同学习,共同进步。

数据库第一次实验 – 数据查询

  • 表名:Student

  • 表名:Course
课程号cno 课程名cname 任课老师teacher 先行课程号pcno
C601 高等数学 周振兴 Null
C602 大学英语 王志伟 Null
C603 数据结构 刘剑平 C601
C604 操作系统 刘剑平 C603
  • 表名:Study
学号sno 课程号cno 成绩grade
1101 C601 90
1102 C601 88
1202 C601 72
1202 C602 85
1202 C603 87
1202 C604
1203 C603 78
1203 C604 80
1305 C601 68
1305 C602 70
-- USE STU

/*(1)查询选修了课程的学生的学号。 */

select distinct 学号
from Study

/*(2)查询学生学号和出生年份。*/

select 学号, year(getdate())-年龄
from Student

/*(3)查询计算机系学生的学号、姓名。*/

select 学号, 姓名
from Student
where 所在系 = '计算机系'

/*(4)查询年龄在20岁与22岁之间(包括20和22岁)的学生姓名和年龄。*/

select 姓名, 年龄
from Student
where 年龄 >= 20 and 年龄 <=22

/*(5)查询学生姓名中含有“波”字的学生情况。*/

select *
from DBO.Student
WHERE 姓名 like '%波%'

/*(6)查询缺少学习成绩的学生学号和课程号。*/

select 学号, 课程号
from Study
where 成绩 is NULL

/*(7)查询选修C601课程的学生的学号和成绩,并按分数的降序排列。*/

select 学号, 成绩
from Study
where 课程号 = 'C601'
order by 成绩 desc

/*(8)求选修了课程的学生人数。*/

select count(distinct 学号)
from Study

/*(9)求课程C601的平均成绩。*/

select avg(成绩) as 平均成绩
from Study
where 课程号 = 'C601'

/*(10)求选修课程超过2门的学生学号。*/

select 学号
from Study
group by 学号
having count(*) >= 3

/*(11)查询参加学习的学生所学的课程号和总分。*/

select 课程号,sum(成绩) as 总分
from Study
group by 课程号

/*(12)查询选修高等数学课程且成绩在80分以上的学生的学号、姓名。*/

select Student.学号, Student.姓名 from Student, Study, Course
where Student.学号 = Study.学号 
and Study.课程号 = Course.课程号 
and Study.成绩 >= 80
and Course.课程名 = '高等数学'

/*(13)查询每一课程的间接先行课(即先行课的先行课)。*/

select C1.课程号, C3.课程号 from Course C1, Course C2, Course C3
where C1.先行课程号 = C2.课程号 and C2.先行课程号 = C3.课程号

/*(14)查询年龄低于所有工程系学生的学生姓名、所在系、年龄。 */

select 姓名, 所在系, 年龄 from Student S
where S.年龄 < (select 年龄 from Student where Student.所在系 = '工程系')

/*(15)找出刘建平老师所开全部课程的课程号、课程名。*/

select 课程号,课程名 from Course
Where Course.任课老师 = '刘建平'

/*(16)找出全部课程的任课教师。*/

select distinct 任课老师 from Course

/*(17)求学生1203所学课程的总分。*/

select sum(成绩) as 总分 from Study
where Study.学号 = 1203

/*(18)找出所有姓张的学生的姓名和籍贯。*/

select 姓名, 籍贯 from Student
where Student.姓名 like '张%'

/*(19)找出至少一门课程的成绩在90分以上的女学生的姓名。*/

select 姓名 from Student, Study
where Student.学号 = Study.学号 and Study.成绩 > 90 and Student.性别 = '女'

/*(20)求出每一年龄上人数超过2的男生的具体人数,并按年龄从小到大排序。*/

select 年龄, count(学号) as 人数 from Student
group by 年龄 having count(学号) >= 2 
order by 年龄

数据库第二次实验 – 数据定义与数据更新

-- (1)把学生的学号及他的平均成绩定义为一个视图GRADE_VIEW。并查询此视图的所有记录。

create view GRADE_VIEW(学号, 姓名, 平均成绩)

SELECT dbo.Student.学号, dbo.Student.姓名, AVG(CAST(dbo.Study.成绩 AS decimal)) AS 平均成绩
FROM dbo.Study INNER JOIN dbo.Student 
ON dbo.Study.学号 = dbo.Student.学号
GROUP BY dbo.Student.学号, dbo.Student.姓名

select * from GRADE_VIEW

-- 注意强制类型转换的用法

-- (2)创建一个从student、course、study表中查出计算机系的学生及其成绩的视图computer_view,要求显示学生的学号sno、姓名name、课程号cno、课程名称cname、成绩grade。并查询此视图的所有记录。

create view computer_view(学号, 姓名, 课程号, 课程名称, 成绩)

select Student.学号, Student.姓名, Study.课程号, Course.课程名称, Study.成绩
from Student, Study, Course
where Student.所在系 = '计算机系' and Student.学号 = Study.学号 and Study.课程号 = Course.课程号

/*
SELECT dbo.Student.学号, dbo.Student.姓名, dbo.Study.课程号, dbo.Course.课程名, dbo.Study.成绩
FROM  dbo.Student INNER JOIN dbo.Study 
ON dbo.Student.学号 = dbo.Study.学号 
INNER JOIN dbo.Course 
ON dbo.Study.课程号 = dbo.Course.课程号
WHERE (dbo.Student.所在系 = '计算机系')
*/

select * from computer_view

--(3)在Student表中依据学生姓名创建索引name_index。

create index name_index on dbo.Student(姓名)

--(4)在Study表中依据sno和cno创建索引main_index。

create index main_index on dbo.Study(学号, 课程号)

--(5)将课程C603的任课老师改为“赵明”。

update Course
set 任课老师 = '赵明'
where 课程号 = 'C603'

--(6)在Student表中插入两个学生记录:'1201','吴华', '女',20,'成都','计算机系' '1102','张军', '男',21,'上海','工程系'

insert into Student
values('1201', '吴华', '女', 20, '成都', '计算机系', '东南大学'), ('1102', '张军', '男', 21, '上海','工程系', 'NULL')


--(7)删除年龄在21岁以上的学生记录。

delete from Student where 年龄 > 21

--(8)在course表中依据teacher创建索引teacher _index,然后删除此索引。

create index teacher_index on Course(任课老师) 
drop index Course.teacher_index

-- (9)先创建一个新表Study_Back,表的结构Study表相同。使用INSERT语句将Study表中记录存入Study_Back表中。

create table Study_Back
(
 学号 smallint,
 课程号 char(8),
 成绩 smallint
)

insert into Study_Back 
select * from Study


数据库第三次实验 – 用户管理和权限管理

-- 创建一个登录名student,口令为123,缺省数据库为STUDENT;

use master
exec sp_addlogin student, '123', 'STU'

-- 将其加入到STUDENT数据库的用户中;

/*
use STU
create user student for login student 
*/
use STU
exec sp_grantdbaccess  'student','student'

-- 将其加入到服务器的sysadmin角色中;

exec sp_addsrvrolemember 'student', 'sysadmin';

-- 将其加入到STUDENT数据库的db_owner角色中;

exec sp_addrolemember 'db_owner', 'STU'

-- 授予他在STUDENT数据库中创建视图、创建表的权限;

USE STU
GRANT CREATE VIEW,CREATE TABLE  
TO student  

-- 授予他对STUDENT数据库中的study表具有所有权限,且可将这些权限授予他人;

USE STU
GRANT ALL PRIVILEGES
ON Study 
TO student
WITH GRANT OPTION

---撤销他对STUDENT数据库中的study表的修改权限;

USE STU
REVOKE UPDATE  
ON Study
FROM student 

-- 禁止他对STUDENT数据库中的study表的删除权限。

USE STU
DENY DELETE ON Study to student 


数据库第四次实验 – 数据完整性

(1)在查询分析器中使用CREATE TABLE语句,在STUDENT数据库中创建符合下表中完整性约束条件的学生表S。

USE STUDENTS 
CREATE TABLE S
    (SNO    CHAR(6)     NOT NULL constraint PK_S PRIMARY KEY CLUSTERED,
     SN     CHAR(8)     NOT NULL,
     AGE    NUMERIC(2)  NOT NULL,
     SEX    CHAR(2)     NOT NULL    DEFAULT '男',
     DEPT   CHAR(10)    NOT NULL)

(2)在查询分析器中使用CREATE TABLE语句,在STUDENT数据库中创建符合下表中完整性约束条件的选课表SC。

注:组合主键、聚集索引定义在SNO和CNO上;外键上还需定义一个非聚集索引。

USE STUDENTS
CREATE TABLE SC
    (SNO    CHAR(6) NOT NULL CONSTRAINT PK_SNO FOREIGN KEY REFERENCES S(SNO),
     CNO    CHAR(8) NOT NULL CONSTRAINT PK_CNO FOREIGN KEY REFERENCES C(CNO),
     SCORE  NUMERIC(2) CHECK (SCORE > 0 AND SCORE < 100),
     CONSTRAINT PK PRIMARY KEY (SNO, CNO))

(3)使用企业管理器为STUDENT数据库创建一个age_rule规则,并将其绑定到学生表S的AGE列,使AGE在18到50之间取值。

USE STUDENTS 
GO
CREATE RULE age_rule AS @AGE >= 18 AND @AGE <= 50 
GO
EXEC sp_bindrule 'age_rule', 'S.AGE'
GO

(4)使用企业管理器为STUDENT数据库创建一个score_default默认,并将其绑定到选课表SC的SCORE列,设置该列的默认值为0。

USE STUDENTS 
GO
CREATE DEFAULT score_default AS 0
GO
EXEC sp_bindefault score_default, 'SC.SCORE'
GO

(5)使用企业管理器为STUDENT数据库创建一个TestTab,列名和数据类型自定,利用设计表窗口和属性对话框创建和修改约束,掌握企业管理器建立主键约束、外键约束和检查约束等方面的技能(


数据库第五次实验 – 数据库的一致性和并发性

执行查询和数据修改语句,并且执行sp_lock来决定SQL Server在表上设置何种类型的锁

为每条语句做以下操作:

USE STUDENT
执行BEGIN TRAN
执行语句
执行sp_lock
执行ROLLBACK TRAN

基本框架

use STUDENT 
go 
begin tran 
            -- 查询或者修改语句
exec sp_lock -- 查询锁的使用情况
commit tran -- rollback tran  // 提交或者回滚

比如(我的学生表是STU):

  • 查询语句
use STU
go
begin tran

select * from Student 

exec sp_lock

rollback tran 

use STU
go
begin tran

select * from Student (holdlock) 

exec sp_lock

rollback tran 

其中holdlock表示长锁,注意加与不加的区别。

  • 修改语句
use STU
go
begin tran

update Student set 年龄 = 19 where 姓名 = '李波' 

exec sp_lock

rollback tran 

use STU
go
begin tran

update Student set 年龄 = 19 where 姓名 = '李波' 

exec sp_lock

commit tran 

注意rollback trancommit tran的区别,一个是回滚一个是提交,使用commit tran能对表完成修改操作。


数据库第六次实验 – 数据库的备份与恢复

待更新。


To be continued
2017-11-28 星期二

转载原创文章请注明,转载自: 太傅 » 数据库实验笔记
  1. DengSir

    路过大佬底盘,看见大佬鼠标样式很棒,遂求之

    1. TaiFu_S
      @DengSir

      欢迎!已发!?