数据库实验一、实验二、实验三、实验四

Patty️

已于 2022-05-24 08:04:29 修改

阅读量1.7w

点赞数 22

分类专栏：数据库原理与应用文章标签： sql 数据库

于 2021-07-01 23:57:44 首次发布

本文链接：https://blog.csdn.net/pattyi/article/details/118400499

版权

数据库原理与应用专栏收录该内容

24 篇文章 33 订阅

订阅专栏

仅供参考，有错误的地方请指出

实验一：

使用Management Studio图形界面创建studentdb数据库，使用SQL语句创建spjdb。

在左侧的对象资源管理器中“数据库”上点右键，新建数据库，指定数据库名studentdb和各项参数如路径等，确定。

在上方工具条中点“新建查询”，新打开窗口中输入如下SQL语句：create database spjdb

然后点“执行”。

在studentdb数据库中新建查询，使用SQL语句创建以下3个表，同时完成数据完整性的定义（实体完整性、参照完整性和用户定义的域完整性）。注意创建完成后需要刷新当前数据库的表目录。
在studentdb中创建架构Production和Person并比较区别。

create schema Production --架构命名不能以数字开头

create schema Person AUTHORIZATION st

注意: 在创建Person架构前需要使用下面的三条语句先在当前数据库中添加用户，并仅仅授予该用户建表的权限。

CREATE LOGIN st WITH PASSWORD=‘suntao123’

CREATE USER st FOR LOGIN st

GRANT create table to st

然后用户st以SQL SERVER身份验证方式登录服务器，尝试执行如下的SQL语句：

create table Person.t1(id int,name char(10)) --成功

create table Production.t1(id int,name char(10)) --失败，原因？

修改表结构，具体要求如下：

(1) 将表course的cname列的数据类型改为varchar(40).

(2) 为表student增加一个新列: birthday(出生日期), 类型为datetime, 默认为空值.

(3) 将表sc中的grade列的取值范围改为小于等于150的正数.

(4) 为Student表的“Sex”字段创建一个缺省约束，缺省值为’男’

(5)为“Sdept”字段创建一个检查约束，使得所在系必须是’CS’、’MA’或’IS’之一。

(6)为Student表的“Sname”字段增加一个唯一性约束

(7)为SC表建立外键,依赖于Student表的fk_S_c约束。

(8)禁止启用Student表的“Sdept”的CHECK约束ck_student。

分别建立以下索引(如果不能成功建立,请分析原因)

(1) 在student表的sname列上建立普通降序索引.

(2) 在course表的cname列上建立唯一索引.

(3) 在sc表的sno列上建立聚集索引.

(4) 在spj表的sno(升序), pno(升序)和jno(降序)三列上建立一个普通索引.

索引能提高查询速度，可以使用下面的实验过程验证。

在“新建查询”窗口下，复制如下全部代码，直接点“执行”，在消息窗口的最下面，可以看到有索引和无索引的查询耗时。

create database spjdb;
create database studentdb;

use studentdb;
create table student
(
    sno   char(9) primary key not null,
    sname nchar(10)            not null,
    ssex  nchar(2),
    sage  smallint,
    sdept char(15),
    check (sage >= 12)
);
use studentdb;
create table course
(
    cno     char(4) primary key not null ,
    cname   nchar(20),
    cpno    char(4),
    ccredit smallint,
);

create table sc
(
    sno   char(9) not null ,
    cno   char(4) not null ,
    grade decimal(5,1),
    foreign key (sno)references student(sno),
    foreign key (cno)references course(cno),
    check (grade between 0 and 100)
);

use spjdb;
create table S(
    sno char(2) primary key not null ,
    sname nchar(10) not null ,
    status smallint,
    city nchar(10),
    check (status>0)
);

create table P(
    pno char(2) primary key not null ,
    pname nchar(10) not null ,
    color nchar(2),
    weight smallint,
    check (weight>0)
);

create table J(
    jno char(2) primary key not null ,
    jname nchar(10) not null ,
    city nchar(10)
);

create table SPJ(
    sno char(2) not null ,
    pno char(2) not null ,
    jno char(2) not null ,
    qty smallint,
    primary key (sno,pno,jno),
    foreign key (sno)references S(sno),
    foreign key (pno)references P(pno),
    foreign key (jno)references J(jno),
    check (qty>0)
);

use studentdb;
create schema Production;
CREATE LOGIN stu WITH PASSWORD='Sql@123456';
CREATE USER stu FOR LOGIN stu;

GRANT create table to stu;

create schema Person AUTHORIZATION  stu;

create table Person.t1(id int,name char(10));

--修改表
--(1)
--alter table course alter cname varchar(40); mysql
alter table course alter column cname varchar(40);

--(2) 
alter table student add birthday datetime default null;

--(3) 
alter table sc
drop constraint CK__sc__grade__2A4B4B5E;

alter table sc
add constraint grade_new check (grade<=150);

--(4) 
alter table student
add constraint ssex__new default '男' for ssex;

--(5)
alter table student
add constraint ck_student check (sdept in ('CS','MA','IS'));

--(6)
alter table student
add unique (sname);

--(7)
alter table sc
add constraint fk_S_c foreign key (sno) references student(sno);

--(8)
alter table student
nocheck constraint ck_student;

--6. 
--(1) 
create unique index sname on student(sname desc );

--(2) 
create unique index cname on course(cname);

--(3) 
create clustered index 索引名 on sc(sno);

--(4)

实验二：

使用INSERT语句将教材P82表中的数据添加到数据库STUDENTDB中.
将教材P70表中的数据添加到数据库SPJDB中. 体会执行插入操作时检查实体完整性规则、参照完整性规则和用户定义完整性规则的效果.
删除student表中学号为201215121的学生，体会执行删除操作时检查参照完整性规则的效果.将参照完整性中的删除规则改为“级联(层叠)”(CASCADE)，重新删除该学生信息。
更新student表中201215122学生的新学号为201215128，体会执行更新操作时检查参照完整性规则的效果.将参照完整性中的更新规则改为“级联”，重新更新该学生信息。
设计一组更新操作, 它需要另外一个表中的数据作为更新条件(如将选修了“信息系统”课程的成绩均提高15% )。
设计一个删除操作，它需要另外一个表中的数据作为删除条件(如将“刘晨”的选课记录删除 )。
完成P128习题4和习题5中(1)~(7)的查询。
完成以下查询：

(1)查询所有课程都及格的学生信息。

(2)统计每一门课程的最高分、最低分和平均分。

(3)从course表中查询课程名中包含“数据”二字的课程的信息。

(4)统计所有学生3号课程的平均成绩，要求某个学生3号课程成绩为NULL，如不存在空值，请先插入。

(5)查询每个学生及其选修课情况，显示学生姓名、课程名称和成绩，要求使用外连接输出所有学生和所有课程的信息。

(6)查询所有学生中平均成绩最高的那个学生的学号。

(7)查询只选修了2号课程的学生的信息，可以使用集合查询。

(8)查询所有计算机系学生的学号、选修课程号以及分数。(使用IN谓词)。

(9)查询选修了课程名为“操作系统”的学生学号和姓名。

(10)查询所有计算机系学生的学号、选修课程号以及分数（使用EXISTS谓词）。


use spjdb;
create unique index sno on SPJ(sno asc,pno asc,jno desc);

use studentdb;
insert into student values ('201215121',N'李勇',N'男',20,'CS',null);
insert into student values ('201215122',N'刘晨',N'女',19,'CS',null);
insert into student values ('201215123',N'王敏',N'女',18,'MA',null);
insert into student values ('201215125',N'张立',N'男',19,'IS',null);

insert into course values ('1',N'数据库','5',4);
insert into course values ('2',N'数学',null,2);
insert into course values ('3',N'信息系统','1',4);
insert into course values ('4',N'操作系统','6',3);
insert into course values ('5',N'数据结构','7',4);
insert into course values ('6',N'数据处理',null,2);
insert into course values ('7',N'PASCAL语言','6',4);

insert into sc values ('201215121','1',92);
insert into sc values ('201215121','2',85);
insert into sc values ('201215121','3',88);
insert into sc values ('201215122','2',90);
insert into sc values ('201215122','3',80);

use spjdb;
insert into S values ('S1',N'精益',20,N'天津');
insert into S values ('S2',N'盛锡',20,N'北京');
insert into S values ('S3',N'东方红',20,N'北京');
insert into S values ('S4',N'丰泰胜',20,N'天津');
insert into S values ('S5',N'为民',20,N'上海');

insert into P values ('P1',N'螺母',N'红',12);
insert into P values ('P2',N'螺栓',N'绿',17);
insert into P values ('P3',N'螺丝刀',N'蓝',14);
insert into P values ('P4',N'螺丝刀',N'红',14);
insert into P values ('P5',N'凸轮',N'蓝',40);
insert into P values ('P6',N'齿轮',N'红',30);

insert into J values ('J1',N'三建',N'北京');
insert into J values ('J2',N'一汽',N'长春');
insert into J values ('J3',N'弹簧厂',N'天津');
insert into J values ('J4',N'造船厂',N'天津');
insert into J values ('J5',N'机车厂',N'唐山');
insert into J values ('J6',N'无线电长',N'常州');
insert into J values ('J7',N'半导体厂',N'南京');

insert into SPJ values ('S1','P1','J1',200);
insert into SPJ values ('S1','P1','J3',100);
insert into SPJ values ('S1','P1','J4',700);
insert into SPJ values ('S1','P2','J2',100);
insert into SPJ values ('S2','P3','J1',400);
insert into SPJ values ('S2','P3','J2',200);
insert into SPJ values ('S2','P3','J4',500);
insert into SPJ values ('S2','P3','J5',400);
insert into SPJ values ('S2','P5','J1',400);
insert into SPJ values ('S2','P5','J2',100);
insert into SPJ values ('S3','P1','J1',200);
insert into SPJ values ('S3','P3','J1',200);
insert into SPJ values ('S4','P5','J1',100);
insert into SPJ values ('S4','P6','J3',300);
insert into SPJ values ('S4','P6','J4',200);
insert into SPJ values ('S5','P2','J4',100);
insert into SPJ values ('S5','P3','J1',200);
insert into SPJ values ('S5','P6','J2',200);
insert into SPJ values ('S5','P6','J4',500);

--3
use studentdb;
delete from student
where sno='201215121';

alter table sc add constraint sno_cascade
foreign key (sno) references student(sno)
on delete cascade on update cascade ;

delete from student
where sno='201215121';

--4
update student set sno='201215128' where sno='201215122';

--5
update sc set grade=grade * 1.15
where grade in(
    select grade from sc inner join course c on c.cno = sc.cno where c.cname=N'信息系统'
    );

--6
delete from sc where sno in(
    select student.sno from student where sname=N'刘晨'
    );

/*习题4*/
--1
use spjdb;
select distinct sno from SPJ
where jno='J1';

--2
select distinct sno from SPJ
where jno='J1' and pno='P1';

--3
select distinct sno from SPJ
inner join P on P.pno = SPJ.pno
where jno='J1' and color = N'红';

--4
select sno from SPJ
inner join J on J.jno = SPJ.jno

/*习题5*/
--1
use spjdb;
select sname,city from S;

--2
select pname,color,weight from P;

--2
select distinct jno from SPJ where sno='S1';

--4
select pname,qty from SPJ,p where jno='J2' and spj.pno=p.pno;

--5
select pno from SPJ
inner join S on S.sno = SPJ.sno
where city=N'上海';

--6
select distinct JNAME
from S,SPJ,J
where S.CITY='上海' and SPJ.SNO=S.SNO and SPJ.JNO=J.JNO;

--7
select distinct JNO
from SPJ
where JNO not in(
	select JNO
	from SPJ,S
	where SPJ.SNO=S.SNO and S.CITY='天津');

use studentdb;
--8.1
select * from student
where not exists(
    select * from sc where grade<60 and student.sno=sc.sno
          );
--8.2
select cno,max(grade),min(grade),avg(grade) from sc
group by cno;

--8.3
select * from course where cname like N'%数据%';

--8.4
insert into sc values ('201215125','3',null);
select avg(grade) from sc where cno=3;

--8.5
select sname,cname,grade from student full outer join sc s on student.sno = s.sno full join course c on c.cno = s.cno;

--8.6
select sno from sc
group by sno
having avg(grade)>=all(select avg(grade) from sc);

--8.7
select * from student where
not exists (select * from sc where cno in(select cno from sc where cno!=2) and student.sno=sno);
--8.8
select student.sno,cno,grade from student inner join sc s on student.sno = s.sno
where student.sno in(
    select sno from student where sdept='CS'
    );

--8.9
select student.sno,sname from student
inner join sc s on student.sno = s.sno
inner join course c on c.cno = s.cno
where cname=N'操作系统';

--8.10
select st.sno,cno,grade from student st
inner join sc s on st.sno = s.sno
where exists(
    select sno from student where sdept='CS' and st.sno=sno
          )

实验三：

1.基于实验1中建立的表，完成如下的视图操作：

(1)建立一个计算机系(CS)学生的视图COMPUTE_S，并要求进行修改和插入操作时仍需保证该视图只有计算机系的学生；

(2) 建立一个信息系(IS)选修了’1’号课程且成绩在80分以上的学生视图IS_S1；

(3) 建立一个所有计算机系学生的学号、姓名、选修课程名以及成绩的视图COMPUTE_GRADE;

对视图进行SELECT、INSERT、UPDATE、DELETE操作

（1）通过COMPUTE_S视图插入一个新学生的信息：201215127，王建，21，男。

要求插入成功后student表中该学生的系值必须为“CS”，如不是，请修改视图后重新插入。

（2）更新COMPUTE_S视图中所有男生的年龄，都增加一岁，注意观察非计算机系男生的年龄是否被修改。

（3）假如你是计算机学院的教务人员，通过COMPUTE_GRADE视图查询数据库这门课程所有不及格学生的名单，体会视图在简化用户操作过程中的作用。

（4）对COMPUTE_GRADE进行插入：201215128，张杰，数据库，80。观察结果并分析失败原因。

（5）删除COMPUTE_S视图中所有学生的信息

创建数据库用户和数据库角色，使之拥有对数据库StudentDB的一定权力。

（1）展开服务器的“安全性”目录树，右击“登录名”,新建登录名test1，SQL Server身份验证，默认数据库为studentdb，用户映射到studentdb数据库，角色成员为public。使用test1用户登录服务器并验证。

（2）使用SQL语句创建用户test2，使之能够访问studentdb数据库并具有建表权限。

CREATE LOGIN test2 WITH PASSWORD=‘test123’

Use studentdb

CREATE USER test2 FOR LOGIN test2

GRANT create table to test2

（3）使用系统存储过程创建用户test3，使之能够访问studentdb数据库并具有建表权限。

–登录用户的创建

exec sp_addlogin ‘test3’, ‘test123’,‘studentdb’

–更改当前数据库

use studentdb

–赋予用户访问当前数据库的权限

exec sp_grantdbaccess ‘test3’

–角色的创建

exec sp_addrole ‘teacher’

–添加用户到角色中

exec sp_addrolemember ‘teacher’,‘test3’

–给角色授权

grant create table to teacher

授予用户李勇查看自己成绩的权限，但不能查看其它人的成绩。

提示：先创建用户李勇，然后创建视图S，通过视图S能查看学生“李勇”的成绩，最后把视图S的查询权限赋予用户李勇。

启用SQL Server的审计功能并通过审计日志进行简单的审计跟踪。

右击左侧目录树最上端的服务器——>属性——>安全性，选中“启用c2审核跟踪”。

也可以通过如下存储过程启用c2审核跟踪。

–查看系统参数配置

sp_configure

–打开系统参数的高级选项

sp_configure ‘show advanced options’,1

RECONFIGURE

–再次查看系统参数配置

sp_configure

–启用c2级别的审计功能

sp_configure ‘c2 audit mode’,1

RECONFIGURE

启用c2审核跟踪后，重新启动服务器，在C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data目录下会产生以当前时间命名的trc跟踪文件。此时可以分别尝试使用错误口令和正确口令登录服务器并进行其它操作，然后停止服务器。

查看审计日志需要使用专门的查看工具，也可以使用操作系统的事件查看器或者SQL Server的扩展存储过程sp_readerrorlog。

--1
create view COMPUTE_S
as select * from student where sdept='CS';

select * from COMPUTE_S;

--
create view IS_S1
as select student.* from student inner join sc s on student.sno = s.sno
where sdept='IS' and cno=1 and grade>80;

select * from IS_S1;

--
create view COMPUTE_GRADE
as select st.sno,sname,cno,grade from student st inner join sc on st.sno = sc.sno
where sdept='CS';

--2
insert into COMPUTE_S values ('201215127',N'王建',N'男',21,'CS',null);

--
update COMPUTE_S set sage=sage+1;

--
select * from COMPUTE_GRADE where grade<60;

--
insert into COMPUTE_GRADE values ('201215128',N'张杰',N'数据库',80)

--
delete from COMPUTE_S;

--3
/*3(1)展开服务器的“安全性”目录树，右击“登录名”,新建登录名test1，SQL Server身份验证，
  默认数据库为studentdb，用户映射到studentdb数据库，角色成员为public。使用test1用户登录服务器并验证。*/


--3.2
CREATE LOGIN  test2 WITH PASSWORD='Sql@123456'
Use  studentdb
CREATE USER test2 FOR LOGIN test2
GRANT create table to test2

--3.3
--登录用户的创建
exec sp_addlogin 'test3', 'Sql@123456','studentdb'
--更改当前数据库
use studentdb
--赋予用户访问当前数据库的权限
exec  sp_grantdbaccess 'test3'
--角色的创建
exec sp_addrole 'teacher'
--添加用户到角色中
exec sp_addrolemember  'teacher','test3'
--给角色授权
grant create table to teacher

--4
create login 李勇 with password =N'李勇'
    use test
        create user 李勇 for login 李勇;
create view grade_liyong
    as
    select course.cname,sc.grade
        from course,sc,student
            where course.cno=sc.cno and student.sno=sc.sno;
grant select on grade_liyong to 李勇

--5
--查看系统参数配置
sp_configure
--打开系统参数的高级选项
sp_configure  'show advanced options',1
RECONFIGURE
--再次查看系统参数配置
sp_configure
--启用c2级别的审计功能
sp_configure 'c2 audit mode',1
RECONFIGURE

实验四：

定义变量并且通过select语句给变量赋值.

查询学号为“201215121”的学生的姓名和年龄，将其分别赋予变量name和age。

declare @name varchar(10)

declare @age int

select @name=sname,@age=sage from student where sno=‘201215121’

select @name as 姓名,@age as 年龄

运行结果如下：

姓名年龄

李勇 31

练习：查询学号为“201215121”的学生选修“1”号课程的成绩，并赋值给变量grade。

if-lese选择结构的使用

查询学生信息，如果学生人数多于10人，则只显示前5名，否则显示所有学生信息。

DECLARE @num int

SELECT @num=count(*) from student

print ‘学生人数:’+convert(varchar(5),@num)

IF (@num>10)

BEGIN

print '前五名学生信息'

SELECT TOP 5 * FROM student  ORDER BY sno DESC

END

ELSE

BEGIN

print '所有学生信息'

SELECT  * FROM student

END

练习：查询“1”号课程的平均成绩，如果平均成绩大于等于70，则显示前5名的成绩，否则显示后5名的成绩。

while循环结构的使用

创建一个测试表，并使用循环结构快速插入20000条记录。

create table TableIndex (

   ID int identity(1,1),

   DataValue decimal(18,2))

/—向TestIndex数据库表中插入20000条数据—/

declare @r numeric(15,8)

declare @n int

set @n = 1

while(1=1)

begin

   set @r = rand()

   insert into  TableIndex (DataValue) values(@r)

   set @n = @n + 1

if(@n>20000)

          break

end

   练习1：检查“1”号课程是否有不及格（60分及格）的学生。如有，每人加2分，直至所有学生选“1”号课程成绩均及格。

   练习2：检查“1”号课程是否有不及格（60分及格）的学生。如有，每人加2分，高于95分的学生不再加分，直至所有学生“1”号课程成绩均及格。

编写带有通配符参数的存储过程，查询学生表和成绩表，返回指定姓名的学生姓名、课程名和考试成绩。要求：执行该存储过程时，如果未提供参数，则使用预设的默认值（以“张”打头的姓）

/—创建存储过程—/

CREATE PROCEDURE sp_stuInfo

@sname varchar(50)=‘张%’

PRINT @sname+‘同学的考试成绩单如下：’

SELECT sname,cname,grade

FROM student,course,sc

WHERE student.sno=sc.sno and course.cno=sc.cno

and sname LIKE @sname

练习：创建存储过程，查看指定课程及格学生的成绩信息，并返回及格的人数。要求及格分数线作为参数由用户输入，默认为60。

创建触发器，当插入交易记录时，实现自动更新账户余额的功能。建表语句如下：

create table count(

 id char(3) primary key,

 balance int)

insert into count values(‘001’, 1000)

create table traninfo(

id char(3) foreign key references count(id),

amount int)

/—创建触发器—/

create trigger autoupdate_balance

on traninfo

for insert

 update count  set balance = balance +i.amount

 from inserted i

 where count.id=i.id

 练习1：删除交易信息时，要求自动备份被删除的数据到表backupTable中 。

练习2：跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。

编写事务进行银行转账。

–创建农行帐户表bank

CREATE TABLE bank(

customerName CHAR(10), --顾客姓名

currentMoney  int     --当前余额

)

/—添加约束：根据银行规定，帐户余额不能少于1元，除非销户----/

ALTER TABLE bank

ADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1)

/–插入测试数据：张三开户，开户金额为800 ；李四开户，开户金额1 —/

INSERT INTO bank(customerName,currentMoney) VALUES(‘张三’,1000)

INSERT INTO bank(customerName,currentMoney) VALUES(‘李四’,1)

/—创建事务—/

/–开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体–/

BEGIN TRANSACTION

/–定义变量，用于累计事务执行过程中的错误–/

DECLARE @errorSum INT

SET @errorSum=0 --初始化为0，即无错误

/–转帐：张三的帐户少1000元，李四的帐户多1000元/

UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName=‘张三’

SET @errorSum=@errorSum+@@error --累计是否有错误

UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName=‘李四’

SET @errorSum=@errorSum+@@error --累计是否有错误

/–根据是否有错误，确定事务是提交还是撤销—/

IF @errorSum<>0 --如果有错误

BEGIN

print '交易失败，回滚事务'

ROLLBACK TRANSACTION

END

ELSE

BEGIN

print '交易成功，提交事务，写入硬盘，永久的保存'

COMMIT TRANSACTION

END

练习：使用第5题中的count表和traninfo表，编写事务实现存取款过程。

要求首先插入交易记录，然后更新账户余额。

--1
declare @name nchar(10)
declare @age int
select @name=sname,@age=sage from student where sno='201215121'
select @name as 姓名,@age as 年龄;
--练习：查询学号为“201215121”的学生选修“1”号课程的成绩，并赋值给变量grade。
declare @grade decimal(5,1)
select @grade=grade from sc where sno='201215121' and cno=1
select @grade as 成绩;

--2
--查询学生信息，如果学生人数多于10人，则只显示前5名，否则显示所有学生信息。
DECLARE @num int
SELECT @num=count(*)  from student
print N'学生人数:'+convert(varchar(5),@num)
IF (@num>10)
  BEGIN
    print N'前五名学生信息'
    SELECT TOP 5 * FROM student  ORDER BY sno DESC
  END
ELSE
  BEGIN
    print N'所有学生信息'
    SELECT  * FROM student
  END
--练习：查询“1”号课程的平均成绩，如果平均成绩大于等于70，则显示前5名的成绩，否则显示后5名的成绩。
declare @avg decimal(5,1)
select @avg=avg(grade) from sc
print N'平均成绩:'+convert(varchar(5),@avg)
if(@avg>70)
begin
    print N'前5名的成绩'
    select * from sc
end
else
begin
    print N'后5名的成绩'
    select * from sc
end

--3

create table TableIndex (
       ID int identity(1,1),
       DataValue decimal(18,2))

/*---向TestIndex数据库表中插入20000条数据---*/

declare @r numeric(15,8)
declare @n int
set @n = 1
while(1=1)
  begin
       set @r = rand()
       insert into  TableIndex (DataValue) values(@r)
       set @n = @n + 1
    if(@n>20000)
              break
end

--练习1：检查“1”号课程是否有不及格（60分及格）的学生。如有，每人加2分，直至所有学生选“1”号课程成绩均及格。
declare @grade decimal(5,1)
select @grade=grade from sc where cno=1
while (@grade<60)
begin
    set @grade=@grade + 2
    if(@grade>=60)
    break
end
select * from sc where cno=1

--练习2：检查“1”号课程是否有不及格（60分及格）的学生。如有，每人加2分，高于95分的学生不再加分，直至所有学生“1”号课程成绩均及格。
declare @grade decimal(5,1)
select @grade=grade from sc where cno=1
while (@grade<60)
        if(@grade>95)
            begin
                set @grade=@grade
             end
        else
            begin
                set @grade=@grade+2
                if(@grade>=60)
                break
            end
select * from sc where cno=1

--4
/*---创建存储过程---*/
CREATE PROCEDURE sp_stuInfo
@sname varchar(50)='张%'
AS
  print  @sname+'同学的考试成绩单如下：'
  select sname,cname,grade
from  student,course,sc
  where student.sno=sc.sno and course.cno=sc.cno
and  sname like  @sname

--练习：创建存储过程，查看指定课程及格学生的成绩信息，并返回及格的人数。要求及格分数线作为参数由用户输入，默认为60。
CREATE PROCEDURE sp_stuGradInfo @cno varchar(2),@gradeLine int=60
AS declare @num int
begin
    select *from sc where cno=@cno and grade>=@gradeLine
    select @num=COUNT(*) from sc where cno=@cno and grade>=@gradeLine
    select @num as 一号课程及格人数
end

--5. 创建触发器，当插入交易记录时，实现自动更新账户余额的功能。建表语句如下：
create database count;
use count;
create table count(
     id char(3) primary key,
     balance int)
insert into count values('001', 1000)
create table traninfo(
  id char(3) foreign key references count(id),
  amount int
)
/*---创建触发器---*/
create trigger autoupdate_balance
on traninfo
for insert
as
     update count  set balance = balance +i.amount
     from inserted i
     where count.id=i.id
--练习1：删除交易信息时，要求自动备份被删除的数据到表backupTable中 。
create trigger autodelete_traninfo
    on traninfo
    for delete
    as
    if not exists(select * from sysobjects where name='backupTable')
    select * into backupTable from deleted
    else
    insert into backupTable
    select * from deleted
    select * from backupTable
--练习2：跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。
create trigger trig_update_tran
ON traninfo
for update
as
declare @beforeMoney money,@afterMoney money
/*从deleted表中获取交易前的余额，从inserted表中获取交易后的余额*/
select @beforeMoney=current money from deleted
select @afterMoney=current money from inserted
if ABS(@afterMoney-@beforeMoney)>20000/*交易金额是否>2万*/
begin
print '交易金额:'+convert(varchar(8),
ABS(@afterMoney-@beforeMoney))
raiserror ('交易失败',16,1)
rollback transaction /*回滚事务，撤销交易*/
end
--6
--创建农行帐户表bank
CREATE TABLE bank(
    customerName CHAR(10), --顾客姓名
    currentMoney  int     --当前余额
)
--添加约束：根据银行规定，帐户余额不能少于1元，除非销户
ALTER TABLE bank
  ADD CONSTRAINT CK_currentMoney CHECK(currentMoney>=1)
/*--插入测试数据：张三开户，开户金额为800 ；李四开户，开户金额1 ---*/
INSERT INTO bank(customerName,currentMoney) VALUES('张三',1000)
INSERT INTO bank(customerName,currentMoney) VALUES('李四',1)
/*---创建事务---*/
/*--开始事务（指定事务从此处开始，后续的T-SQL语句都是一个整体--*/
BEGIN TRANSACTION
/*--定义变量，用于累计事务执行过程中的错误--*/
DECLARE @errorSum INT
SET @errorSum=0  --初始化为0，即无错误
/*--转帐：张三的帐户少1000元，李四的帐户多1000元*/
UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='张三'
SET @errorSum=@errorSum+@@error  --累计是否有错误
UPDATE bank SET currentMoney=currentMoney+1000 WHERE customerName='李四'
SET @errorSum=@errorSum+@@error  --累计是否有错误
/*--根据是否有错误，确定事务是提交还是撤销---*/
  IF @errorSum<>0  --如果有错误
  BEGIN
    print '交易失败，回滚事务'
    ROLLBACK TRANSACTION
  END
ELSE
  BEGIN
    print '交易成功，提交事务，写入硬盘，永久的保存'
    COMMIT TRANSACTION
  END
--练习：使用第5题中的count表和traninfo表，编写事务实现存取款过程。
insert into count values (001,2340);
insert into count values (002,7000);
insert into traninfo values (001,400);
insert into traninfo values (002,-700);
create procedure transfor @outAccount char(3),@inAccount char(3),@amount float
as
begin transaction
    declare @errorSum int
    set @errorSum = 0
    update count set balance=balance-@amount
        where id=@outAccount
        set @errorSum=@errorSum+@@error
        update count set balance= balance-@amount
        where id = @inAccount
        set @errorSum=@errorSum+@@error
        if (@errorSum<>0)
            begin
                print '交易失败，回滚事务'
                rollback
             end
        else
            begin
                print '交易成功，提交事务，写入硬盘，永久的保存'
                commit
            end

实验五：


CREATE TABLE sales

(     id char(5),
      qty int,
       b int
);

insert into sales values( 'A0001', 0,10 );
--定义如下事务，运行结束前，停止SQL SERVER服务，然后重启服务查看qty和b的值是否有变化。
   begin tran
                 update sales set qty = qty - 10 where id = 'A0001'
                 waitfor delay '00:00:30.000'
                 update sales set b = b + 10 where id = 'A0001'
           commit tran
--定义如下SQL语句，运行结束前，停止SQL SERVER服务，然后重启服务查看qty和b的值是否有变化。
update sales set qty = qty - 10 where id = 'A0001'
              waitfor delay '00:00:30.000'
              update sales set b = b + 10 where id = 'A0001'
  declare @sl int

--2.1丢失修改
--例1：
--事务一和事务二代码相同，如下
       begin tran
              select @sl = qty from sales where id = 'A0001'
              waitfor delay '00:00:30.000'
              update sales set qty = @sl - 1 where id = 'A0001'
       commit tran
--执行结果：qty==1，事务一的修改丢失。
--原因：事务执行过程中对操作的数据没有加锁。

--2.2例2：
--丢失修改
--事务一代码
declare @i int
declare @sl int
select @i = 1
while( @i <= 2000) begin
       begin tran
              select @sl = qty from sales where id = 'A0001'
              waitfor delay '00:00:00.002'
              update sales set qty = @sl + 1 where id = 'A0001'
       commit tran
       select @i = @i + 1
end
--事物二代码
declare @i int
declare @sl int
select @i = 1
while( @i <= 2000) begin
       begin tran
              select @sl = qty from sales where id = 'A0001'
              waitfor delay '00:00:00.002'
              update sales set qty = @sl - 1 where id = 'A0001'
       commit tran
       select @i = @i + 1
end
--4. 不可重复读
--例1:
--事务一代码如下
        declare @qty int
        declare @b int
           begin tran
              select @qty = qty from sales where id = 'A0001'
                select @b = b from sales where id = 'A0001'
                print(@qty+@b)
              waitfor delay '00:00:5.000'
              select @qty = qty from sales where id = 'A0001'
                select @b = b from sales where id = 'A0001'
                print(@qty+@b)
           commit tran
--事务二代码
        declare @b int
           begin tran
              select @b = b from sales where id = 'A0001'
              update sales set b = @b *2 where id = 'A0001'
           commit tran

--例2：
--不可重复读
--事务一代码如下
declare @i int
declare @sl int
declare @s2 int
select @i = 1
while( @i <= 2000) begin
       begin tran
              select @sl = qty from sales where id = 'A0001'
              waitfor delay '00:00:00.002'
              select @s2 = qty from sales where id = 'A0001'
              if(@sl<>@s2) print('non-repeatable read')
       commit tran
       select @i = @i + 1
end

--事务二代码
declare @i int
declare @sl int
select @i = 1
while( @i <= 2000) begin
       begin tran
              select @sl = qty from sales where id = 'A0001'
              waitfor delay '00:00:00.002'
              update sales set qty = @sl + 1 where id = 'A0001'
       commit tran
        select @i = @i + 1
end
--5. 读脏数据
--事务一代码
        SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED
        declare @b int
           begin tran
                select @b = b from sales where id = 'A0001'
                print(@b)
                update sales set b = @b *2 where id = 'A0001'
                select @b = b from sales where id = 'A0001'
                print(@b)
              waitfor delay '00:00:20.000'
         rollback
--事务二代码
        SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED
        declare @b int
           begin tran
                select @b = b from sales where id = 'A0001'
                print(@b)
           commit tran
--6. 加锁实验
--(1) 一级封锁协议不会丢失修改，但不能保证不出现另两种异常丢失修改.
        declare @sl int
           begin tran
                 select @sl = qty from sales with (xlock) where id = 'A0001'
                 waitfor delay '00:00:10.000'
                 update sales set qty = @sl - 1 where id = 'A0001'
           commit tran
--不可重复读
--事务一
        declare @qty int
        declare @b int
       begin tran
              select @qty = qty from sales where id = 'A0001'
                select @b = b from sales where id = 'A0001'
                print(@qty+@b)
              waitfor delay '00:00:15.000'
              select @qty = qty from sales where id = 'A0001'
                select @b = b from sales where id = 'A0001'
                print(@qty+@b)
       commit tran
--事务二
        declare @b int
       begin tran
                select @b = b from sales with(xlock) where id = 'A0001'
              update sales set b = @b *2 where id = 'A0001'
       commit tran
--读脏数据
--事务一
        SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED
        declare @b int
       begin tran
                select @b = b from sales with (xlock) where id = 'A0001'
                print(@b)
                update sales set b = @b *2 where id = 'A0001'
                select @b = b from sales where id = 'A0001'
                print(@b)
              waitfor delay '00:00:20.000'
         rollback
--事务二
        SET TRANSACTION ISOLATION LEVEL  READ UNCOMMITTED
        declare @b int
       begin tran
              select @b = b from sales where id = 'A0001'
              print(@b)
       commit tran
--(2) 二级封锁协议可以避免丢失修改和读脏，但不能保证可重复读。在sql server中默认设置即可避免读脏。
--不可重复读
--事务一
        declare @qty int
       begin tran
              select @qty = qty from sales where id = 'A0001'
        select @b = b from sales where id = 'A0001'
        print(@qty+@b)
              waitfor delay '00:00:15.000'
              select @qty = qty from sales where id = 'A0001'
        select @b = b from sales where id = 'A0001'
        print(@qty+@b)
       commit tran
--事务二
        declare @b int
       begin tran
              select @b = b from sales with(xlock) where id = 'A0001'
              update sales set b = @b *2 where id = 'A0001'
       commit tran
--(3) 三级封锁协议可以完全避免这三种异常.
--可重复读
--事务一
        declare @qty int
        declare @b int
           begin tran
                 select @qty = qty from sales with(holdlock) where id = 'A0001'
           select @b = b from sales with(holdlock) where id = 'A0001'
           print(@qty+@b)
                 waitfor delay '00:00:15.000'
                 select @qty = qty from sales where id = 'A0001'
           select @b = b from sales where id = 'A0001'
           print(@qty+@b)
           commit tran
--事务二
        declare @b int
           begin tran
                select @b = b from sales with(xlock) where id = 'A0001'
                update sales set b = @b *2 where id = 'A0001'
           commit tran

Patty️

关注

22
点赞
踩
193

收藏

觉得还不错? 一键收藏
8
评论
数据库实验一、实验二、实验三、实验四

仅供参考，有错误的地方请指出实验一：实验二：实验三：实验四：实验五：实验一：使用Management Studio图形界面创建studentdb数据库，使用SQL语句创建spjdb。在左侧的对象资源管理器中“数据库”上点右键，新建数据库，指定数据库名studentdb和各项参数如路径等，确定。在上方工具条中点“新建查询”，新打开窗口中输入如下SQL语句：create database spjdb然后点“执行”。在studentdb数据库中新建查询，使用SQL语句创建以下3个表，同时完成数据
复制链接

扫一扫

专栏目录