数据库基础__SQL_Server2
实验一_建表
create database JiaoXue go use JiaoXue go create table Department( DepID int NOT NULL , DName nvarchar(20) NOT NULL, Location nvarchar(50), Leader char(8), constraint DK primary key(DepID) ) go CREATE 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]') ) go create 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 go create 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 go insert 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 go insert 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 go insert 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 go insert 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 go insert 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.CNo from Course C,Teacher T where 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,SC where S.SNo = SC.SNo group 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) gavg from sc group by sno) sc1 where 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 sc1.cno<>sc2.cno) 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 Class from Student where SNo='105') go --查询修选课程名为“高等数学”的学生学号、姓名。 SELECT SNo,SName from Student where SNo in(select SNo from SC where CNo in(select CNo from Course where CName='高等数学')); go --查询选修学生人数多于4人的某课程的任课教师姓名 select Tname from Teacher T ,Course C,(select SC.CNo from 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 C where 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 course where not exists (select * from sc where student.sno=sc.sno and course.cno=sc.cno)) --查询没有选修李诚老师所授课程的学生的学号、姓名、--【年龄】 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 c.cno=sc.cno and c.tno=t.tno 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 SC group by SNo having 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 SC group by SNo having 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,SName from SC,Student T where 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,SName from SC,Student T where 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 go OPEN SC_Cursor go declare @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 begin if @score<60 set @d=@d+1 if @score>=60 and @score<79 set @c=@c+1 if @score>=80 and @score<89 set @b=@b+1 if @score>=90 set @a=@a+1 FETCH SC_Cursor into @score end print @a print @b print @c print @d go CLOSE 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 name from sysobjects where name='T2C_Del' and type='TR') drop trigger T2C_Del go create trigger T2C_Del on Teacher for delete as if @@rowcount=0 return delete Course from Course t,deleted d where t.TNo=d.Tno return if exists(select name from sysobjects where name='T2C_Update' and type='TR') drop trigger T2C_Update go create trigger T2C_Update on Teacher for update as declare @num_rows int select @num_rows=@@rowcount if @num_rows=0 return if update(TNo) begin if @num_rows>1 begin raiserror 33333 '不支持多个pub_id值的修改' rollback transaction return end update Course set TNo=i.TNo from Course t,deleted d,inserted i where t.TNo=d.TNo end return --维护从表Course到主表Teacher方向的数据完整性 if exists(select name from sysobjects where name='C2T_IU' and type='TR') drop trigger C2T_IU go create trigger C2T_IU on Teacher for insert,update as declare @num_rows int select @num_rows=@@rowcount if @num_rows=0 return if (select count(*) from Teacher t,inserted i where t.TNo=i.TNo)!=@num_rows begin raiserror 33334 '试图插入或修改非法的TNo值到Course表中' rollback transaction return end 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 as declare @num_rows int select @num_rows=@@rowcount if @num_rows=0 return if (select count(*) from SC t,inserted i where t.CNo=i.CNo)>100 begin raiserror 33335 '选择同一门课程的人数超过100人，出错！' rollback transaction return end 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)) AS delete from Student where SNo=@SNo delete from SC where SNo=@SNo go --在查询分析器中利用存储过程delStudent删除学号为 ‘111001’ 的学生记录 EXEC delStudent '111001' go --在范例数据库中pubs中如果存在存储过程 ‘titles_sum’，则删除它 if exists(select name from sysobjects where name='titles_sum' and type='p') drop procedure titles_sum go --在范例数据库pubs中创建存储过程 salequa ，查询某一库存ID为@Stor_id的所有销售记录，并返回一销售量参数@sum if exists(select name from sysobjects where name='salequa' and type='p') drop procedure salequa go create procedure salequa(@Stor_id char(4),@sum smallint output) AS select @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 Course alter 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 AS declare @oldrank smallint,@newrank smallint if update(prof) begin select @oldrank= case d.prof when '助教' then 1 when '讲师' then 2 when '副教授' then 3 when '教授' then 4 end from Teacher t,deleted d where t.prof=d.prof select @newrank= case i.prof when '助教' then 1 when '讲师' then 2 when '副教授' then 3 when '教授' then 4 end from Teacher t,inserted i where t.prof=i.prof if(@newrank < @oldrank) begin print'教师职称只能升高，不能降低' 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 FOR SELECT Cno, Cname,Grade FROM Course FOR READ ONLY go OPEN Course_csr go FETCH Course_csr into @C_Cno,@C_Cname,@C_Grade set @message='课程号:'+@C_Cno+',姓名:'+@C_Cname+',年级:'+@C_Grade print @message while @@FETCH_STATUS=0 BEGIN PRINT @message WAITFOR DELAY '00:00:01' FETCH Course_csr into @C_Cno,@C_Cname,@C_Grade set @message='课程号:'+@C_Cno+',姓名:'+@C_Cname+',年级:'+@C_Grade END CLOSE Course_csr DEALLOCATE Course_csr go --把上一个只读游标定义成可更新游标 DECLARE Course_csr CURSOR FOR SELECT Cno, Cname,Grade FROM Course FOR update --在范例数据库Pubs中的Course表中定义一个可更新游标 csr_Grade_Term --将Cno等于’6-166’的记录的Grade,Term都增加1 DECLARE @Grade smallint,@Term smallint DECLARE csr_Grade_Term CURSOR FOR SELECT 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 BEGIN UPDATE Course SET Grade=Grade+1,Term=Term+1 WHERE CURRENT OF csr_Grade_Term FETCH 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 delstu delete from tblstudent where studentnum=@studentnum if @@ERROR<>0 ROLLBACK delete from tblselcourse where studentnum=@studentnum if @@ERROR<>0 ROLLBACK else COMMIT TRAN GO --增加了事务的存储过程salequa CREATE PROCEDURE salequa(@Stor_id char(4),@sum smallint output) AS BEGIN TRAN salequerry select * from sales where stor_id=@Stor_id if @@ERROR<>0 ROLLBACK select @sum=sum(qty) from sales where stor_id=@Stor_id if @@ERROR<>0 ROLLBACK else COMMIT TRAN return GO DROP 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 go create 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;
实验六 数据库安全性
