数据库基础

数据库基础

 

 

 

实验一_建表

create database JiaoXue
go
use JiaoXue
gocreate table Department(
DepID    int    NOT NULL  ,
DName    nvarchar(20)    NOT NULL,
Location    nvarchar(50),
Leader    char(8),
constraint DK primary key(DepID)
)
goCREATE TABLE Teacher(
TNo    char(8)    NOT NULL,
TName    nchar(4)    NOT NULL,
Sex    nchar(1)    NULL,
Prof    nchar(5)    NULL,
Mobile    char(11)    NULL,
DepID    Int    NOT NULL,
CONSTRAINT TKey PRIMARY KEY(TNo),
constraint T2D foreign key(DepID) references Department(DepID),
constraint TSex check(Sex='男' or Sex='女'),
constraint TProf check(prof in ('助教','讲师','正教','副教')),
constraint TMobile check(Mobile like '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
gocreate table Course(
CNo    char(10)    NOT NULL ,
CName    varchar(50)    NOT NULL,
Grade    smallint,
Term    smallint,
TNo    char(8),
DepID    int
constraint CKey primary key(CNo),
constraint C2T foreign key(TNo) references Teacher(TNo), --此处引用到Teacher表，得先创建Teacher表
constraint C2D foreign key(DepID) references Department(DepID),
constraint CGrade check(Grade in(1,1.5,2,2.5,3,3.5,4,4.5,5)),
constraint CTerm check(Term in(1,2,3,4,5,6,7,8))
)
--drop table Course
--drop database mao
gocreate table Student(
SNo    char(10)    NOT NULL,
SName    nchar(8)    NOT NULL,
Sex    nchar(1)    NULL,
BirthDate    smalldatetime    NULL ,
Class    int    NOT NULL,
constraint SKey primary key(SNo),
constraint SSex check(Sex='男' or Sex='女')
)create table SC(
SNo    char(10)    NOT NULL,
CNo    char(10)    NOT NULL,
SDate    smalldatetime    NULL,
Score    decimal(4,1)    NULL,
constraint SCKey primary key (SNo,CNo),
constraint SC2S foreign key(SNo) references Student(SNo),--先创建Student表
constraint SC2C foreign key(CNo) references Course(CNo),
constraint SCScore check(Score>=0 and Score<=100 )
)

 

 

实验二_插入数据

use JiaoXue
goinsert into Department(DepID, DName    ,Location,    Leader)values(1,'计算机系','麓山南路184号',804)
insert into Department values(2,'应化系','求实路24号',854)
insert into Department values(3,'数学系','北京路35号',822)
insert into Department(DepID, DName    ,Leader) values(4,'电子工程系',836)
--select * from Department
goinsert into Teacher(TNo,TName,Sex,Prof,Mobile,DepID)values(804,'李诚','男','副教',13712884353,1)
insert into Teacher values('856','张旭','男','讲师','13578453321',4)
insert into Teacher values('825','王萍','女','助教',null ,1)
insert into Teacher values('831','刘冰','女','助教','13686751234',4)
insert into Teacher values('822','周刚','男','正教','13512484756',3)
insert into Teacher values('827','张旺才','男','副教',NULL,3)
insert into Teacher values('854','沈长生','男','正教',NULL,2)
insert into Teacher values('836','刘明','女','副教','13813536608',4)
--select * from Teacher
goinsert into Course(Cno,CName,Grade,Term,Tno,DepID) values('3-105','计算机导论',3,1,'825',1)
insert into Course values('3-245','操作系统',4,4,'804',2)
insert into Course values('6-166','数字电路',2,3,'856',1)
insert into Course values('9-888','高等数学',4,1,'831',3)
--select * from Course
--delete from Course
goinsert into Student(SNo,SName,Sex,BirthDate,Class)values('108','曾华','男','1982-09-01',95033)
insert into Student values('105','匡明','男','1982-10-02',95031)
insert Student values('107','王丽','女','1981-01-23',95033)
insert Student values('101','李军','男','1983-02-20',95033)
insert Student values('109','王芳','女','1982-02-10',95031)
insert Student values('103','陆君','男','1980-06-03',95031)
--select * from Student
goinsert into SC(SNo,CNo,SDate,Score)values('103','3-245','2004-05-01',86)
insert SC values('105','3-245','2004-06-11',75)
insert SC values('109','3-245','2004-06-23',68)
insert SC values('103','3-105','2002-10-11',92)
insert SC values('105','3-105','2002-10-14',88)
insert SC values('109','3-105','2002-10-15',76)
insert SC values('101','3-105','2002-10-20',64)
insert SC values('107','3-105','2002-10-17',88)
insert SC values('108','3-105','2002-10-18',78)
insert SC values('101','6-166','2003-05-18',85)
insert SC values('107','6-166','2003-06-21',79)
insert SC values('108','6-166','2003-06-08',81)
--select * from SC
go--将刘冰的职称改为讲师
--select * from Teacher
update Teacher
set prof='讲师'
where TName='刘冰'
go--将王丽的计算机导论的考试成绩改为90分
--select * from Student
--select * from Course
--select * from SC
update SC 
set score=90 
where SC.Sno in (select s.sno from Student S where S.SName='王丽') 
AND   SC.CNo in(select c.CNo from Course C where  C.CName='计算机导论')
go--删除101号学生所有的选课记录
--select * from SC
delete from SC
where SNo='101'
go--删除选修了李诚老师所授课程的所有成绩
--select * from Teacher
--select * from Course
--select * from SC
delete from SC
where SC.CNo in(select C.CNofrom Course C,Teacher Twhere C.Tno=T.TNo and T.TName='李诚')
go--4．插入子查询结果
--创建SAVG表
create table SAVG(
Sno char(10) ,
Sname nchar(4) not null,
AVGrade decimal(4,1),
constraint SKey2 primary key(Sno),
constraint SU unique(Sname)
)
go
--select * from SAVG
--将查询结果插入到SAVG表
insert into SAVG(Sno,Sname,AVGrade)select SC.SNo,SName,AVG(Score)from Student S,SCwhere S.SNo = SC.SNogroup by SC.Sno,Sname--sname在这里并不起分组作用，只是避免语法错/*
--或者给中间查询结果其别名，让其在参与运算实现如下
insert into savg(sno,sname,gavg)select sc1.sno,s.sname,sc1.gavg from student s,(select sno,AVG(score) gavgfrom scgroup by sno) sc1where sc1.sno=s.sno
*/

 

 

实验三_查询操作

use JiaoXue
go--投影查询
--询Student表中所有记录的SName、Sex和Class列,并且改变查询结果中显示的列名（SName->姓名，Sex->性别，Class->班级）
select SName '姓名',Sex '性别',Class '班级' 
from Student
go--简单查询:
--查询SC表中成绩在60到80之间的所有记录
select *
from SC
where Score between 60 and 80
go--询Student表中所有姓王的学生记录
select *
from Student
where SName like '王%'
go--询SC表中成绩为85、86、88或90的记录
select *
from SC
where Score in (85,86,88,90)
go--查询Student表中“95031”班或性别为“女”的学生记录
select *
from Student
where Class=95031 or sex='女'
--或
select * from student where class=95031
union
select * from student where sex='女'
go--查询SC表中，选修了课程但没有参加考试的学生学号
select SNo
from SC
where Score is null
go--排序查询
--查询SC表中的所有记录，并且以Cno升序、Score降序显示
select *
from SC
order by CNo ASC ,Score DESC
go--使用聚集函数
--查询统计Student表中“95031”班学生人数
select count(distinct SNo) as '统计人数' --as可去掉
from Student
where Class=95031
go--查询统计SC表中，“3-105”号课程的平均分
select AVG(Score)  '平均分'
from SC
where CNo='3-105'
go--数据分组
--查询SC表中选修人数多于4人，并且课程号以3开头的课程的平均成绩
select AVG(Score)  '平均分'
from SC
where CNo like '3%'
group by CNo having count(SNo)>4
go--查询至少有两门课程的成绩在80分以上学生的学号
select SNo
from SC
where Score>80
group by SNo having count(CNo)>=2
go--------------------------------------------------------------------------------------------
--表的连接
--l    查询至少选修了两门以上课程的学生的学号、姓名和所在班级
/*
select distinct s.sno,sname,class 
from (select sc1.sno from sc sc1,sc sc2 where sc1.sno=sc2.sno and sc1o<>sc2o) as scc, student s
where s.sno=scc.sno
*/
--【OK】
select distinct SC.SNo,SName,Class
from Student,SC
where Student.SNo = SC.SNo
and SC.SNo in(select  SNo from SC group by SNo having COUNT(CNo) >=2); 
go--查询所有学生（包括未选课的学生）的学号、姓名（SName）、该学生所选课程的平均分
/*
select t.sno,t.sname,平均分 
from student t left  OUTER JOIN 
( select sno,AVG(score) 平均分 from SC group by sno )as scc
on t.sno=scc.sno
*/
--【OK】
select distinct SC.SNo,Sname,AVGrade
from SC full outer join SAVG     --全外联接，join联接on (SAVG.SNo = SC.SNo);  --连接条件   
--select * from SAVG
--select * from Student
go--嵌套查询：
--查询Student表中与“105”号学生在同一个班级的学生的SNo，SName和BirthDate
select SNo,SName,BirthDate
from Student
where Class in(select Classfrom Studentwhere SNo='105')
go--查询修选课程名为“高等数学”的学生学号、姓名。
SELECT SNo,SName
from Student
where SNo in(select SNo from SC where CNo in(select CNo from Coursewhere CName='高等数学'));
go--查询选修学生人数多于4人的某课程的任课教师姓名
select Tname 
from Teacher T ,Course C,(select SC.CNofrom SC group by SC.CNo having count(SC.Sno)>=4) SC2 
where T.Tno=C.Tno and SC2.CNo=C.CNo
go--查询所有任课教师的TName和DepID
select distinct TName,DepID
from Teacher T
where T.TNo in(select C.TNo from Course Cwhere C.CNo in(select SC.CNo from SC))
go--------------------------------------------------------------------------
--【!!!】
--查询成绩比该课程平均成绩低的学生成绩表
select SC.Sno ,S.Sname,SC.Score,SC3.average 
from SC,Student S,(select sc2.CNo,avg(score) as average from SC sc2 group by sc2.CNo) sc3
where sc.CNo=sc3.CNo and sc.sno=S.sno and sc.score<sc3.average--查询选修了所有课程的学生的学号，姓名，年龄
select sno,sname,datediff(yy,birthdate,getdate()) as age 
from student
where not exists(select * from coursewhere not exists(select * from scwhere student.sno=sc.sno and courseo=sco))--查询没有选修李诚老师所授课程的学生的学号、姓名、--【年龄】select sno,sname,datediff(yy,birthdate,getdate()) as age 
from student s
where not exists(select * from course c,teacher t,sc where sc.sno=s.sno and co=&# and tname='李诚')
/*
SELECT SNo,SName,Sex
from Student
where SNo not in
(select SNo FROM SC WHERE CNo IN
(SELECT CNo FROM Course WHERE TNo IN
(SELECT TNo FROM Teacher WHERE TName ='李诚')));
*/
---------------------------------------------------------------------------------------查询结果的并、交、差
--查询所有教师和学生的编号、姓名、性别
select S.SNo,SName,Sex
from Student S
union
select TNo,TName,Sex
from Teacher
go

 

 

实验四_1_函数

use JiaoXue
go--1．集合函数
--统计选修了课程的学生人数
select count(distinct SNo)
from SC
go--用GROUP BY统计不同职称的教师人数
select Prof,count(TNo)
from Teacher
group by Prof
go--【？】-----------------------------------------------
--compute by统计不同职称的教师人数
select tno,prof 
from teacher 
order by prof 
compute count(tno) by prof--2、数据类型转换函数
--从Teacher表中查询教师的电话号码中的最后8位数字能被9整除的教师信息
select * 
from  Teacher
where convert(int, right(mobile,8)) %9 = 0
--convert(A,B):将B类型转换成A类型
--right(mobile,8):返回mobile字符串从最右端数到第8位中间的字符串
go--查询学生的学号、姓名、出生日期。要求将出生日期转化为整数后显示
/*
select SNo,SName,convert(int,Birthdate)
from Student
*/
select SNo,SName,convert(char(8),BirthDate,112)
from Student
go--3、日期函数
--把当前日期转换为样式 101
select getdate() 
select convert(char(10),getdate(),101) 
--select getdate():得到当前时间
--convert(类型符，表达式[,style])：将表达式按指定风格转换成指定的类型,style只用于日期
go--日期元素：日期中的某元素：yy,qq,mm,dyy,dy,dd,wk,hh等
--datename(日期元素,日期表达式)：返回字符串，如:June
--datepart(日期元素,日期表达式)：返回数字，如:6
--dateadd(日期元素，数值，日期表达式)：将数值转换成日期元素指定的部分加到日期表达式上返回。
--datediff(日期元素，之前日期表达式，较晚日期表达式)：两个日期相减后，按日期元素指定部分转化后返回。--计算所有学生的平均年龄
select avg(datediff(yy,birthdate,getdate())) 
from  student
go--查询最近两年选修课程的课程号
select distinct sno
from sc
where datediff(yy,SDate,getdate())<=2
go--分别获取数据库当前时间的年、月、日、小时
select datepart(yy,GETDATE()) as y,datepart(mm,GETDATE()) as m,datepart(dd,GETDATE()) as d,datepart(hh,GETDATE()) as h
go             --4、数学函数
select ceiling(99.2)    --100,ceiling()向上取整
select floor(99.9)        --99,floor()向下取整
select round(66.5337,2)    --66.53
select round(66.5397,2)    --66.54,round()四舍五入
select power(2,10)        --1024--以2为随机数种子产生一随机数
select rand(2) --rand()随机数--对数字1223.75进行整数的四舍五入和取整
select round(1223.75,0) ,ceiling(1223.75),floor(1223.75)       --5、字符串函数【!】
--求在字符串‘They call me the hunter'中从5个字符开始，"the"出现的位置
select charindex('the','They call me the hunter',5) 
go--返回Teacher表中的所有老师的姓氏（不重复）
select distinct substring(Tname,1,1) 
from Teacher
go--用 ZZZ 替换 abcdefghi 中的字符串 cde
select replace('abcdefghi','cde','ZZZ') 
go--组合字符串‘hello‘和‘join‘，中间加入一空格
select 'hello'+' '+'join' 
go--6、系统函数
--全局变量：引用时前缀为@@
--局部变量：引用时前缀为@，局部变量用declare定义，用select或set赋值
--定义：DECLARE   @myqty   int,  @msg   varchar(40)
--赋值1：select  @myqty=60
--赋值2：SELECT  @s=price  FROM   titles   WHERE  title_id=‘bu395’
--查看变量：SELECT  @变量名
--请输出系统信息，并用字符串函数将其连接起来：当前数据库ID,数据库名,主机ID,主机名：
--【？】----------------------------7．用户自定义函数--用户自定义函数：标量函数,内嵌表值函数,多语句表值函数【!!】--创建标量函数fun1，从SC表中查询指定学生（由输入的学号确定）的平均成绩。
/*
创建标量函数:
create function fun1(@SNo varchar(20))
returns numeric(4,1)
begin
declare @avgscore numeric(4,1)
select @avgscore = AVG(score)from SCgroup by SNohaving SNo=@sno
return @avgscore
end
go
select dbo.fun1('108')
go
*/
create function fun1(@SNo varchar(20))
returns numeric(4,1)
begin
declare @avgscore numeric(4,1)
select @avgscore = AVG(score)from SCgroup by SNohaving SNo=@sno
return @avgscore
end
go
select dbo.fun1('108')
go--创建内嵌表值函数fun2，从tblstudent和tblselcourse表中查询指定年份出生的学生的学号、姓名、年龄平均成绩。
/*
创建表值函数：
create function fun2(@year datetime)
returns table
as
return(select SC.SNo,SNamefrom SC,Student Twhere SC.SNo=T.SNo and datediff(yy,@year,birthdate)=0)
go
select * from dbo.fun2('1980-11-11')
go
*/
create function fun2(@year datetime)
returns table
as
return(select SC.SNo,SNamefrom SC,Student Twhere SC.SNo=T.SNo and datediff(yy,@year,birthdate)=0)
go
select * from dbo.fun2('1980-11-11')
go--删除函数fun1
drop function dbo.fun1--8． T-SQL编程
--用流控制语言统计SC表中的成绩为A、B、C、D各个层次的学生数。假如规定：A--[90，100}  B--[80，89]  C[60，80]  D[0，60]
/*
游标的使用：【!!!】
declare SC_Cursor cursor 
FOR
select Score from SC 
go
OPEN SC_Cursor
go
*******
FETCH SC_Cursor into @score
go
CLOSE SC_Cursor
DEALLOCATE SC_Cursor
go
*/declare SC_Cursor cursor 
FOR
select Score from SC
goOPEN SC_Cursor
godeclare @a int,@b int,@c int,@d int
set @a=0
set @b=0
set @c=0
set @d=0
declare @score decimal(4,1)
FETCH SC_Cursor into @score
while @@FETCH_STATUS = 0
beginif @score<60 set @d=@d+1if @score>=60 and @score<79 set @c=@c+1if @score>=80 and @score<89 set @b=@b+1if @score>=90  set @a=@a+1  FETCH SC_Cursor into @score
end
print @a
print @b
print @c
print @d
goCLOSE SC_Cursor
DEALLOCATE SC_Cursor
go

 

 

实验四_2_触发器_存储过程

use JiaoXue
go--1、触发器
/*
`触发器只能在表上建立，且一张表最多建立3个触发器
`操作时被触发而执行，不能由用户直接调用。
`触发器是在表上建立的，我们将触发器所依附的表称为触发器表。当修改表的数据而引发了触发器时，触发器将执行一系列T-SQL命令，在执行这些动作之前系统首先自动建立两个表，Deleted表与Inserted表。这两个表临时驻留在内存当中，其结构与触发器表完全相同，一旦触发器执行完毕，两个表也随之消失。
`UPDATE操作相当于先执行DELETE操作，然后再执行INSERT操作，因此要用到Deleted与Inserted两个表
`触发器的创建：CREATE TRIGGER trigger_name ON {table|view}{FOR|AFTER|INSTEAD OF}  --指定触发器的类型,after或instead of,默认为after{[INSERT],[DELETE],[UPDATE]}  --指定触发条件insert,delete,update[ WITH ENCRYPTION ] --用于加密处理AS[ { IF UPDATE ( column )　　－－当特定列被更新时触发触发器[ {AND|OR} UPDATE ( column ) ][ ...n ]sql_statement [ ...n ]   --判断某列是否被修改} `修改触发器语法：ALTER TRIGGER trigger_name ON { table | view } { FOR | AFTER | INSTEAD OF } { [ INSERT ]， [ DELETE ] ，[ UPDATE ] }[ WITH ENCRYPTION ]AS[ { IF UPDATE ( column )[ { AND | OR } UPDATE ( column ) ][ ...n ]sql_statement [ ...n ]  } 
`删除触发器：DROP TRIGGER trigger_name
`禁止或启用触发器：ALTER TABLE talbe_name
{ENABLE|DISABLE} TRIGGER
{ALL|trigger_name[,…]}*/--分别在教师表Teacher和课程表Course上建立触发器
if exists(select namefrom sysobjects where name='T2C_Del' and type='TR')
drop trigger T2C_Del
go
create trigger T2C_Del
on Teacher
for delete
asif @@rowcount=0 returndelete Course from Course t,deleted dwhere t.TNo=d.Tno
returnif exists(select namefrom sysobjectswhere name='T2C_Update' and type='TR')
drop trigger T2C_Update
go
create trigger T2C_Update
on Teacher
for update
asdeclare @num_rows intselect @num_rows=@@rowcountif @num_rows=0 returnif update(TNo)beginif @num_rows>1beginraiserror 33333 '不支持多个pub_id值的修改'rollback transactionreturnendupdate Courseset TNo=i.TNofrom Course t,deleted d,inserted iwhere t.TNo=d.TNoend
return--维护从表Course到主表Teacher方向的数据完整性
if exists(select namefrom sysobjectswhere name='C2T_IU' and type='TR')
drop trigger C2T_IU
go
create trigger C2T_IU
on Teacher
for insert,update
asdeclare @num_rows intselect @num_rows=@@rowcountif @num_rows=0 returnif (select count(*) from Teacher t,inserted i where t.TNo=i.TNo)!=@num_rowsbeginraiserror 33334 '试图插入或修改非法的TNo值到Course表中'rollback transactionreturnend
return--在选课表SC上建立触发器，当选择同一门课程的人数超过100人时（上机时可设为5），给出错误信息
if exists(select name from sysobjects where name='SC_IUD' and type='TR')
drop trigger SC_IUD
go
create trigger SC_IUD
on SC
for insert,update
asdeclare @num_rows intselect @num_rows=@@rowcountif @num_rows=0 returnif (select count(*) from SC t,inserted i where t.CNo=i.CNo)>100beginraiserror 33335 '选择同一门课程的人数超过100人，出错！'rollback transactionreturnend
return-------------------------------------------------------------------------------------------------------
--2、存储过程
--创建存储过程delStudent，删除学生编号等于传入参数的学生记录，并删除该学生的选课记录
/*
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'delStudent' AND type = 'P')DROP PROCEDURE delStudent
GO
创建存储过程：
create procedure delStudent(@SNo varchar(20))
AS********
*/
create procedure delStudent(@SNo varchar(20))
ASdelete from Student where SNo=@SNodelete from SC where SNo=@SNo
go--在查询分析器中利用存储过程delStudent删除学号为 ‘111001’ 的学生记录
EXEC delStudent '111001'
go--在范例数据库中pubs中如果存在存储过程 ‘titles_sum’，则删除它
if exists(select namefrom sysobjectswhere name='titles_sum' and type='p')
drop procedure titles_sum
go--在范例数据库pubs中创建存储过程 salequa ，查询某一库存ID为@Stor_id的所有销售记录，并返回一销售量参数@sum
if exists(select namefrom sysobjectswhere name='salequa' and type='p')
drop procedure salequa
go
create procedure salequa(@Stor_id char(4),@sum smallint output)
ASselect @sum=sum(qty) from sales where stor_id=@stor_id
return
go/*执行上一步创建的存储过程 salequa,输入参数@stor_id=‘7131‘，并根据输出参数产生销售信息，格式如下：销售等级为'等级', 销售量为'销售量'如果销售量<=50, 则'等级'为3；如果50<销售量<=100, 则'等级'为2；如果销售量>100, 则'等级'为1。*/-----------------------------【？】
declare @summ smallint,@level smallint
exec dbo.salequa @Stor_id='7131',@sum=@summ output
set @level=2
if @summ<=50 set @level=3
if @summ>100 set @level=1
print '销售等级为【'+convert(char(1),@level)+ '】, 销售量为【' + rtrim(convert(char(10),@summ))+'】'
go

 

 

实验五_完整性约束

use JiaoXue
go--1．主键约束：
--首先在选课表SC中添加主键约束，主键为（Sno,Cno），然后试着将SC中的某条记录的课程号字段改为空，体会主键约束的作用。
select * from SC
insert into SC(sno,cno,sdate) values('112',null,2001-02-03);
--或
update SC
set CNo = null 
where CNo = '3-105'
--试着将学生表Student中的两条记录的学号字段改为相同的值，体会主键约束的作用。
select * from Student
update Student
set SNo = '108'
where SNo = '103'
go--2．惟一约束：
--对Department表中的系名字段施加惟一约束，并在该表中添加一条新纪录。
--其中，新纪录中的系名字段为NULL，体会与主键约束的不同。
alter table Department
add constraint DepID_unique unique(DepID)
go
insert into Department values(null,'计算机系','麓山南路184号',804)--3．外键约束：
--将课程表Course中的授课教师编号设为外键（参照教师表中的教师编号）。--C2T
select * from Teacher
select * from Coursealter table Course drop constraint C2T
alter table Course
add constraint C2T_2 foreign key (TNo) references Teacher(TNo) 
on delete set null 
on update cascade
go--当从教师表中删除某个教师信息时，该教师所授课程自动置为NULL；
delete from Teacher where TNo='804'
--insert into Teacher(TNo,TName,Sex,Prof,Mobile,DepID) values(804,'李诚','男','副教',13712884353,1)
--当修改一个教师编号时，级联修改该教师所授课程。
update Teacher set TNo = '855' where TNo='825'
go--4．默认约束：
--创建默认sdefault为‘男’。
--将sdefault绑定到学生表的性别列。
select * from Student
alter table Student
add constraint default_sex default '男' for sex
insert into Student(SNo,SName,BirthDate,Class) values('0138','张三','2012-01-01',20099)--5．规则约束：
--创建规则prule使其取值必须是（’助教’，’讲师’，’副教授’ ，’教授’）。
--将规则prule绑定到教师表的职称列。
DECLARE @prof as nchar(5);
go
CREATE RULE prule AS @prof in('助教','讲师','副教授','教授')
go
exec sp_bindrule prule,'teacher.prof';
go
insert into teacher values('857','陈嘉欣','女','副处长','13859634221',4);--6．动态约束：
--在教师表Teacher上定义触发器，使其在更新教师职称时只能升高不能降低，但可以破格（如：讲师－>教授）。
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tch_up' AND type = 'TR')DROP TRIGGER tch_up
GO
CREATE TRIGGER tch_up
ON Teacher
FOR UPDATE 
ASdeclare @oldrank smallint,@newrank smallintif update(prof)beginselect @oldrank=case  d.profwhen '助教' then    1when '讲师' then    2when '副教授' then 3when '教授' then    4endfrom Teacher t,deleted d where t.prof=d.prof select @newrank=case  i.profwhen '助教' then   1when '讲师' then   2when '副教授' then 3when '教授' then   4endfrom Teacher t,inserted i where t.prof=i.prof    if(@newrank < @oldrank) beginprint'教师职称只能升高，不能降低'rollback transaction  end              end  
return  --测试：
update teacher set prof='助教' where Tname='沈长生' ;update teacher set prof='讲师' where tname='王萍';
select * from teacher where tname='王萍';select * from teacher

 

 

实验七_游标事务

USE JiaoXue
GO
--在范例数据库Pubs上定义一个只读游标，它查询课程表Course的Cno, Cname,Grade字段
DECLARE 
@C_Cno char(10),
@C_Cname varchar(50),
@C_Grade smallint,
@message VARCHAR(100)DECLARE Course_csr CURSOR FORSELECT Cno, Cname,Grade FROM CourseFOR READ ONLY
goOPEN Course_csr
goFETCH Course_csr into @C_Cno,@C_Cname,@C_Gradeset @message='课程号:'+@C_Cno+',姓名:'+@C_Cname+',年级:'+@C_Grade
print @messagewhile @@FETCH_STATUS=0
BEGINPRINT @messageWAITFOR DELAY '00:00:01'FETCH Course_csr into @C_Cno,@C_Cname,@C_Gradeset @message='课程号:'+@C_Cno+',姓名:'+@C_Cname+',年级:'+@C_Grade
END
CLOSE Course_csr
DEALLOCATE Course_csr
go--把上一个只读游标定义成可更新游标
DECLARE Course_csr CURSOR FORSELECT Cno, Cname,Grade FROM CourseFOR update--在范例数据库Pubs中的Course表中定义一个可更新游标 csr_Grade_Term 
--将Cno等于’6-166’的记录的Grade,Term都增加1
DECLARE @Grade smallint,@Term smallint
DECLARE csr_Grade_Term CURSOR FORSELECT Grade,Term FROM Course WHERE Cno='6-166'FOR UPDATE OF Grade,Term
OPEN csr_Grade_Term
FETCH  csr_Grade_Term into @Grade,@Term
WHILE @@FETCH_STATUS=0
BEGINUPDATE Course SET Grade=Grade+1,Term=Term+1 WHERE CURRENT OF csr_Grade_TermFETCH  csr_Grade_Term into @Grade,@Term
END
CLOSE  csr_Grade_Term
DEALLOCATE csr_Grade_Term--运行下列sql语句，写出运行结果，体会事务的作用
select top 5 title_id, stor_id from sales order by title_id, stor_id
begin tran
delete  sales 
select top 5 title_id, stor_id from sales order by title_id, stor_id
go
rollback tran
select top 5 title_id, stor_id from sales order by title_id, stor_id
--事务的作用：未提交的事务并没有真正对数据库更新，可通过回滚操作来消除这种影响--实验六中的存储过程delStudent、salequa包括两条以上的sql语句，请在其中加入事务处理和出错处理
--增加了事务的存储过程delStudent
CREATE PROCEDURE delStudent @studentnum varchar(20) 
AS BEGIN TRAN delstudelete from tblstudent where studentnum=@studentnumif @@ERROR<>0 ROLLBACK delete from tblselcourse where studentnum=@studentnumif @@ERROR<>0 ROLLBACK else COMMIT TRAN
GO
--增加了事务的存储过程salequa
CREATE PROCEDURE salequa(@Stor_id char(4),@sum smallint output) 
AS BEGIN TRAN salequerryselect * from sales where stor_id=@Stor_idif @@ERROR<>0 ROLLBACK select @sum=sum(qty) from sales where stor_id=@Stor_idif @@ERROR<>0 ROLLBACK else COMMIT TRAN  
return
GODROP PROCEDURE salequa

 

 

实验八_数据库备份恢复

--1.创建备份设备
exec sp_addumpdevice 'disk','jiaoxue_bak','F:jiaoxue_back.bak'--2.查看备份设备
exec sp_helpdevice--2.查看备份设备
exec sp_helpdevice jiaoxue_bak--3.创建Student数据库的完整备份
BACKUP DATABASE JiaoXue to DISK='F:jiaoxue_back' with INIT
go--4.创建student数据库的差异备份
BACKUP DATABASE JiaoXue to DISK='F:jiaoxue_back_2' with DIFFERENTIAL
go--5．创建sutdent数据库的事务日志备份
BACKUP LOG JiaoXue to DISK='F:jiaoxue_back_3'
go--6．创建文件组备份  ?????
BACKUP DATABASE JiaoXue FILE = 'jiaoxue' TO DISK='F:JiaoXue_file_back'
go--7．在student数据库上创建一个表并输入记录，然后完全备份该数据库。
--接着再用SQL语句修改表中内容，随后进行数据库恢复，比较恢复前后这个数据库的不同点。恢复数据库
--创建表
use JiaoXue
gocreate table phone
(
PNo char(10) not null CONSTRAINT pk_pno PRIMARY KEY,
phone char(11)  constraint check_phone check(phone like'1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
);
go
--select * from phone--对表phone插入数据
insert into phone(pNo,phone) values('709','15989678549');
insert into phone(pNo,phone) values('956','13578453321');
insert into phone(pNo,phone) values('925',null);
insert into phone(pNo,phone) values('931','13686751234');
insert into phone(pNo,phone) values('922','13512484756');
go
--drop table phone--第一次完整备份数据库Student
BACKUP DATABASE JiaoXue to DISK='F:test_back' with INIT
go
--更新表phone中的数据
use JiaoXue
go
update phone set phone='15989678549' where pno='709';
--对数据库Student的事务日志进行备份
BACKUP LOG JiaoXue to DISK='F:test_back_2'
--对Student数据库进行数据库恢复
restore database JiaoXue from DISK='F:test_back'
WITH FILE=1,
RECOVERY;

 

 

实验六 数据库安全性 

 

 

 

转载于:.html