drop database DBScreate database DBSuse DBScreate table 院系表(YXBH CHAR(8) PRIMARY KEY CLUSTERED,--院系编号YXMC CHAR(20) NOT NULL,--院系名称YXZR CHAR(8) --院系主任)create table 教师表(jsh char(5) constraint pk_jsh primary key,jsm char(20))create table 课程表(kch char(3) constraint pk_kch primary key,kcm char(20))gocreate table 教师表(jsh char(5) constraint pk_jsh primary key,jsm char(20))create table 成绩表(xh char(7) references 学生表,--学号kch char(3) references 课程表,--课程号cj int default 0 check (cj >= 0and cj <= 100),--成绩jsh char(5) references 教师表--教师号constraint pk_cj primary key(xh,kch)--主码)CREATE TABLE 学生表(XH CHAR(7)CONSTRAINT PK_XH PRIMARY KEY NONCLUSTERED,--学号XM CHAR(20) NOT NULL,--姓名sfz char(18) unique nonclustered,--身份证yxbh char(8) references 院系表 --院系编号，外码)drop table 学生表insert into 学生表 values('0301001','李永年','350500198305214026','001')insert into 学生表 values('0301002','张丽珍','350500198512017017','001')insert into 学生表 values('0302001','陈俊雄','320300198503213042','001')insert into 学生表 values('0302002','李军','210200198409112402','001')insert into 学生表 values('0302003','王任芳','502400198401223341','001')insert into 学生表 values('0303001','赵雄伟','401200198312111123','001')select * from 学生表select top 4 * from 学生表select top 50 percent * from 学生表 order by xmselect distinct(kch) as kch from 学生表insert into 教师表 values('01002','王崇阳')insert into 教师表 values('01001','李穆')insert into 教师表 values('02001','吴赛')insert into 教师表 values('02002','冯远客')insert into 教师表 values('03001','李莉')insert into 教师表 values('03002','简方')insert into 教师表 values('01003','刘高')insert into 课程表 values('001','高等数学')insert into 课程表 values('002','计算机基础')insert into 课程表 values('003','网络基础')insert into 课程表 values('005','大学英语')insert into 院系表 values('001','计算机','冯远客')insert into 院系表 values('002','经管','简方')insert into 院系表 values('003','数学','黄梅')insert into 成绩表 values('0301001','001','89','01001')insert into 成绩表 values('0301002','001','78','01002')insert into 成绩表 values('0302001','002','85','02001')insert into 成绩表 values('0301001','005','69','02002')insert into 成绩表 values('0302001','001','56','01002')insert into 成绩表 values('0302002','001','93','02001')insert into 成绩表 values('0302003','001','67','01003')use DBScreate login teacher with password='123',default_database=DBScreate user teacher1 for login teacherexec sp_change_users_login 'update_one','teacher1','teacher';--建立登录账号与数据库用户之间的关系exec sp_addlogin 'student','0000','DBS',sp_addlogin loginname,password,database --"创建用户"grant all on 管理系统 to student with grant option--用存储过程创建账号revoke insert on 学生表 from studentrevoke all on 学生表 from publicdeny create database ,create table to student--5-72declare @var1 varchar(7) --声明局部变量select @var1='学生姓名' --为局部变量赋初始值select @var1=xm --查询结果赋值给变量from 学生表where xh='0302001'select @var1 as '学生姓名' --显示局部变量结果--5-73declare @var1 varchar(8)select @var1='学生姓名'select @var1=xm --查询结果赋值，返回的是整个列的全部值，但最后一个给变量from 学生表select @var1 as '学生姓名'--显示局部变量的结果--5-74declare @no varchar(10)set @no='0302001'--变量赋值select xh,xmfrom学生表where [email protected]--5-75select @@versionselect @@servername --本地服务器名--5-76select xh,cj,from 成绩表where cj=(select max(cj) from 成绩表)--5-77declare @StringTest char(10)SET @StringTest='Robin'select substring(@StringTest,3,Len(@StringTest))--5-78declare @StringTest char(10)set @StringTest=ascii('Robin ')select @StringTest--5-79declare @StringTest char(10)set @StringTest=ascii('Robin ')select char(@StringTest)--5-80create procedure checks_kch @param intas if(select count(kch) from 成绩表 where [email protected])>0 return 1else return 2declare @param intexec @param=check_kch '001'print @param--5-81--如果课程中有计算机课程，统计其数量，否则显示没有计算机课程if exists(select * from 课程表 where kcm like '计算机%')select count(*) as 计算机课程数量from 课程表where kcm like '计算机%'elseprint'数据库中没有计算机课程'--5-82if exists(select * from 课程表 where kcm='高等数学')select cpunt(*) as 选修高等数学人数from 成绩表,课程表where kcm='高等数学' and 成绩表.kch=课程表.kchelseif exists(select*from课程表 where kcm='编译原理')select count(*) as 选修编译原理人数from 成绩表,课程表where kcm='编译原理'elseprint'高等数学和编译原理都没开'--5-83if exists(select * from 课程表 where kcm='高等数学')beginselect count(*) as 高等数学from 课程表where kcm-'高等数学'endelsebeginif exists(select * from 课程表 where kcm='编译原理')select count(*) as 编译原理from 课程表where kcm='编译原理'elseprint'高等数学和编译原理都没开!'end--5-84declare @x intset @x=0while @x<3beginset @[email protected]+1print'x='+convert(char(1),@x)--类型转换函数convertend--5-85waitfor delay '00:00:30'select * form 学生表--5-86waitfor time '21:20:00'select * from 学生表--5-87select xh,'课程数量'=case count(*)when 1 then '选修了一门课'when 2 then '选修了两门课'when 3 then '选修了三门课'endfrom 成绩表group by xh--5-88select xh,count(*) as 数量,课程数量=casewhen count(*)=1 then '选修了一门课'when count(*)=2 then '选修了两门课'when count(*)=3 then '选修了三门课'endfrom 成绩表group by xh--5-89--创建函数create function age(@borntime datetime,@today datetime)--borntime 表示出生日期,today表示当前日期returns intas begindeclare #age intset @age=(year(@today)-year(@borntime))return(@age)end--结束函数定义do--调用函数select DBS.dbo.age('1999-7-1',getdate()) as some_age--5-90--创建函数create function course_grade(@kch varchar(30))--kch 表示课程号returns tableasreturn(select * from 成绩表 where [email protected])go--调用函数select * from DBS.dbo.course_grade('001')--5-91--创建函数create function choisecourse(@xm varchar(30))returns @choiseinfo table(学号 char(10),学生姓名 char(20),所选课程号 char(10),成绩 numeric(5,1))begininsert @choiseinfoselect 学生表.xh,xm,kch,cjfrom 学生表,成绩表where 学生表.xh=成绩表.xh and [email protected]returnendgo--调用函数select * from DBS.dbo.choisecourse('李永年')--5-92declare firstcur cursor for select xh,xm,sfz,yxbh from 学生表--5-93declare firstcur cursor for select xh,xm,sfz,yxbh from 学生表 for readonly--5-94declare firstcur cursor for select xh,xm,sfz,yxbh from 学生表 for update--5-95declare @stuname char(10)--定义游标学生信息_cursordeclare 学生信息_cur cursorlocal scroll for select xm from 学生表--打开游标open 学生信息_curfetch next from 学生信息_cur into @stuname--取游标中的数据while @@fetch_status --循环开始,系统默认@@fetch_status的初始值是beginprint @stunamefetch next from 学生信息_cur into @stunameendclose 学生信息_curdeallocate 学生信息_cur--5-96select * from 学生表 where xh='0302001'begin transaction exampletrans --开始一个事务update 学生表 --第一次更新set YXBH='001'where xh='0302001'gosave transaction change --设置保存点update 学生表 --第二次更新set xm='李小丽'where xh='0302001'goselect * from 学生表 where xh='0302001'rollback transaction change --回滚到保存点print'program go on'commit transactionselect * from 学生表 where xh='0302001'--5-97if exists(select name from sysobjectswhere name='cj_info_all' and type='P')drop procedure cj_info_allgocreate procedure cj_info_allasselect xm as 学生姓名,kcm as 课程名,jsm as 教师姓名,cj as 成绩from 成绩表 inner join 学生表 on 学生表.xh=成绩表.xhinner join 课程表 on 课程表.kch=成绩表.kchinner join 教师表 on 教师表.jsh=成绩表.jshgocj_info_all 存储过程可以通过以下方法执行:execute cj_info_all--5-98if exists(select name from sysobjectswhere name='cj_info' and type='P')drop procedure cj_infogocreate procedure cj_info@xm varchar(20)@jsm varchar(20)as select sm as 学生姓名,kcm as 课程名,jsm as 教师姓名,cj as 成绩from 成绩表 inner join 学生表 on 学生表.xh=成绩表.xhinner join 课程表 on 课程表.kch=成绩表.kchinner join 教师表 on 教师表.jsh=成绩表.jshwhere [email protected] adn [email protected]gocj_info 存储过程可以通过以下方式执行,第一条命令的运行结果execute cj_info '张丽珍','李穆'--orexecute cj_info @jsm='李穆',@xm='张丽珍'--orexecute cj_info @xm='张丽珍',@jsm='李穆'--orexecute cj_info '张丽珍','李穆'--orexecute cj_info @jsm='李穆',@xm='张丽珍'--orexecute cj_info @xm='张丽珍',@jsm='李穆'cj_info '张丽珍','李穆'--orcj_info @jsm='李穆',@xm='张丽珍'--orcj_info @xm='张丽珍',@jsm='李穆'--5-99if exists(select name from sysobjectswhere name='cj_info2' and type='P')drop procedure cj_info2create procedure cj_info2@xm varchar(20)='李%',@jsm varchar(20)='%'asselect xm as 学生姓名,kcm as 课程名,jsm as 教师姓名,cj as 成绩from 成绩表 inner join 学生表 on 学生表.xh=成绩表.xhinner join 课程表 on 课程表.kch=成绩表.kchinner join 教师表 on 教师表.jsh=成绩表.jshgo--cj_info_all 存储过程可以通过以下方法执行:execute cj_info2--查找张姓同学的成绩单execute cj_info2'张%'--查找陈姓同学的成绩单execute cj_info2 @xm='陈%'--查找张姓同学的成绩单execute cj_info2'[陈张]%'--查找张姓同学,选修李姓老师的成绩单execute cj_info2'张%','李%'--5-100if exists(select name from sysobjectswhere name='get_avg' and type='P')drop procedure get_avggocreate procedure get_avg@xm varchar(20)='张%',@avg int outputasbegindeclare @xh varchar(7)select @xh=学生表.xh from 成绩表,学生表where 成绩表.xh=学生表.xh and xm like @xmselect @avg=avg(xj) from 成绩表where [email protected] group by 成绩白哦.xhenddeclare @getavg intexecute get_avg '张%',@getavg outputif @getavg<60beginprint''print'张姓同学的成绩平均不及格'endelseselect '张姓同学的平均成绩是'+rtrim(cast(@getavg as varchar(20)))--5-101if exists(select name from sysobjectswhere name='xs_cursor' and type='P')drop procedure xs_cursorgocreate procedure xs_cursor@xs_cursor=cursor forward_only static forselect * from 学生表open @xs_cursorgodeclare @mycursor cursorexec xs_cursor @[email protected] outputwhile (@@fetch_status=0)beginfetch next from @mycursorendclose @mycursorgo--5-102if exists(select name from sysobjectswhere name='学生选课单' and type='P')drop procedure 学生选课单gocreate procedure 学生选课单 @@lname_pattern varchar(30)with recompileas select (rtrim(YXBH)+''+rtrim(xm)) as '专业及姓名'kcm as 课程名,jsm as 教师名from 学生表 sinner join 成绩表 t on s.xh-g.xhinner join 课程表 t on g.kch=c.kchinner join 教师表 t on g.jsh=c.jshwhere xm like @@lname_patternexec 学生选课单 '李%'--5-103if exists(select name from sysobjectswhere name='encryp_xs' and type='P')drop procedure encryp_xsgocreate procedure encryp_xs with encryptionasselect * from 学生表goexec sp_helptext encryp_xsselect c.id,c.textfrom sysobjects c inner join sysobjects o on c.id=o.idwhere o.name='encryp_xs'--5-104if exists(select name from sysobjectswhere name='sp_showindexes' and type='P')drop procedure sp_showindexesgocreate procedure sp_showindexes@@table varchar(30)='sys%'asselect o.name as table_name,i.name as index_name,indid as index_idfrom sysindexes i inner join sysobjects oon o.id=i.idwhere o.name like @@tablegoexec sp_showindexes'教%'--5-105if exists(select name from sysobjectswhere name='proc1' and type='P')drop procedure proc1go--创建一个查询并不存在的表格的存储过程create procedure proc1as select * from does_not_existexec proc1--5-106select o.id,c.textfrom sysobjects o inner join syscomments con o.id=c.idwhere o.type='P' and o.name='proc2'--5-107if exists(select name from sysobjectswhere name='proc2' and type='P')drop procedure proc2go--5-108create procedure proc2as declare @middle_init char(1)set @middle_init=nullselect xh,[email protected]_initfrom 学生表exec proc2--5-109select o.id,c.textfrom sysobjects o inner join syscomments con o.id=c.idwhere o.type='P' and o.name='proc2'--5-110if exists(select table_name from information_schema.viewswhere table_name='学生')drop view 学生gocreate view 学生asselect xh,xm,sfz from 学生表goselect * from 学生--5-111if exists(select table_name from information_schema.viewswhere table_name='成绩单')drop view 成绩单gocreate view 成绩单(学号,姓名,平均成绩) with encryptionasselect 成绩表.xh,xm,avg(cj) from 学生表,成绩表where xm like '张%' and 学生表.xh=成绩表.xhgroup by 成绩表.xh,xmgoselect * from 成绩单select c.id,c.textfrom syscomments c,sysobjects owhere c.id=o.id and o.name='成绩表'go--5-112if exists(select table_name from information_schema.viewswhere table_name='学生_计算机')drop view 学生_计算机gocreate view 学生_计算机(学号,姓名,身份证)asselect xh,xm,sfz from 学生表where ybxh='001'with check optiongoselect * from 成绩单--5-113if exists(select table_name from information_schema.viewswhere table_name='专业平均成绩统计')drop view 专业平均成绩统计gocreate view 专业平均成绩统计(院系,平均成绩)asselect yxbh,avg(cj) from 成绩表,学生表where 成绩表.xh=学生表.xhgroup by yxbhselect * from 专业平均成绩统计--5-114if exists(select table_name from information_schema.viewswhere table_name='行数统计')drop view 行数统计gocreate view 行数统计as select @@rowcount 查询返回的行数select * form 学生表select * from 行数统计--5-115--创建表格并插入记录create table 供应商1(supplyID int promary key check(supplyID between 1 and 150),supplier char(50))create table 供应商2(supplyID int promary key check(supplyID between 151 and 300),supplier char(50))create table 供应商3(supplyID int promary key check(supplyID between 301 and 450),supplier char(50))create table 供应商4(supplyID int promary key check(supplyID between 451 and 600),supplier char(50))insert 供应商1 values('1','CalifoniaCorp')insert 供应商1 values('5','BraziliaLtd')insert 供应商2 values('231','FarEast')insert 供应商2 values('280','NZ')insert 供应商3 values('321','EuroGroup')insert 供应商3 values('442','UKArchip')insert 供应商4 values('475','India')insert 供应商4 values('521','Afique')--创建一个包所有4个表格的视图create view 所有供应商视图(供应商编号,供应商地址)as select * from 供应商1union allselect * from 供应商2union allselect * from 供应商3union allselect * from 供应商4--查询所有供应商的视图，命令如下，select * from 所有供应商视图?