1.显示所有年龄不到20岁的所有男生信息
Use jxgl go Select * From s Where sex=’男’ and datediff(yy,birth,getdate())<202.查询书名中含有“数据库”的图书信息
Select * From c Where cname like ‘%数据库%’3.查询书名中含有“DB_“的图书信息
Select * From c Where cname like ‘%DB@_%’escape ‘@’4.查询姓名以“张”或“李”开头的所有同学的信息
Select * From s Where sname like ‘[张李]%’5.查询姓名不是以“张”或“王”开头的所有同学的信息
Select * From s Where sname not like ‘[张李]%’6.查询图书定价在20至50元的图书信息
Select * Into aa From book Where price between 20 and 507.查询既不是“清华大学出版社”，也不是“高等教育出版社的”的图书信息
Select * From book Where bpc not in (‘清华大学出版社’，‘高等教育出版社’)8.查询BOOK表中的图书号、书名、图书价格及六折后的价格，并将六折后价格设置别名为“折后价”，将查询结果生成一个新表
use jxgl go select bno 图书号,bname 书名,price 图书价格,0.6*price 折后价 Into NEWBOOK from book9.查询清华大学出版社所有的图书信息，并按照定价降序排列；
use jxgl go select bno 图书号,bname 书名,price 图书价格,0.6*price 折后价 from book10.查询出版社为“清华大学出版社”和“高等教育出版社”的所有价格大于30的图书信息，并按照定价升序排序；
use jxgl go select * from book where bpc in ('清华大学出版社','高等教育出版社') and price>30 order by price asc11.查询BOOK表中清华大学出版社的图书信息，要求显示定价排前10的图书信息 use jxgl go select top 10 from book where bpc in (‘清华大学出版社’) order by price asc 12.查询所有先修课为空值的课程编号，课程名及学分
use jxgl go select cno,cname,credit from c where tperiod is null13.查询已开设的课程的课程号，并去掉重复行；
use jxgl go select distinct cno from c14.查询开设过的课程总门数；
use jxgl go select count(*) from c15.查询每个同学选修课程的信息，输出学号，总分数，平均分，最高分和最低分
```bash use jxgl go select count(*) from c use jxgl go select sno 学号,sum(grade) 总分,avg(grade) 平均分,max(grade) 最高分 ,min(grade) 最低分 from sc group by sno16.将全部选修成绩大于等于60分的课程，按照课程号分组求出该课程的平均分，显示平均分大于70分的课程的课程号和平均分。
use jxgl go select cno 课程号,avg(grade) 平均分 from sc where grade>=60 group by cno having avg(grade)>70（1）查询灾害信息工程系的学生数据并保存到ZHXX表中；
Use jxgl Go Select sno,sname,sex,birth,homadd,dname Into ZHXX From s,dept Where s.dno=dept.dno and dname=’灾害信息工程系’（2）查询选修了课程号为“2008583”的所有学生的学号、姓名、所在系名称；
Use jxgl Select s.sno,sname,dname From s,sc,dept Where s.sno=sc.sno and s.dno=dept.dno and cno=’2008583’ Group by s.ano,sname,dname（3）查询各系的女生人数，并分别对列重命名为“系名”和“女生人数”；
Use jxgl Go Select dname 系名，count(distinct sno) 女生人数 From s,dept Where dept.dno=s.dno and sex=’女’ Group by dname（4）查询每门课程的课程号、课程名、最高分、最低分和平均分；
Use jxgl Go Select c.cno 课程号，cname 课程名，max(grade) 最高分，min(grade) 最低分,avg(grade) 平均分 From c,sc Where c.cno=sc.cno Group by c.cno,cname（5）查询每门课程成绩都在90分以上学生的学号、姓名和所在系名称；
Use jxgl Go Select s.sno,sname,dname From s,sc,dept Where s.sno=sc.sno and s.dno=dept.dno Group by s.sno,sname,dname Having min(grade)>90（6）查询选修“2008583”课程的学生中，成绩比全校该门课程平均成绩高的学生人数；
Use jxgl Go Select count(distinct sno) From sc Where grade> (select avg(grade) From sc Where cno=’2008583’ and cno=’2008583’)（7）查询各系的学生人数、开设课程门数，要求输出系名、学生人数和课程门数；
Use jxgl Go Select dname 系名,count(distinct s.sno) 学生人数,count(distinct cno)课程门数 From s,sc,dept Where s.sno=sc.sno and s.dno=dept.dno Group by dname（8）查询比“机械工业出版社”出版的所有图书的定价都高的书号、书名和出版社；
Use jxgl Go Select bno 书号,bname 书名,bpc 出版社 From book Where price> (select max(price) From book Where bpc=’机械工业出版社’ And bpc<>’机械工业出版社’)（9）查询没有使用“数据库系统概论”作为教材的课程名称； ！=也是不是
Select cname 课程名 From c,book Where bname not in (‘数据库系统概论’) and c.bno=book.bno（10）查询所有没有选修课程的学生学号和姓名；
Use jxgl Go Select sno学号，sname 姓名 From s Where not exists (select * From sc Where sc.sno=s.sno)（11）查询选修了课程“2008583”或者选修了课程“2008585”的学生学号及姓名；
Use jxgl Go Select s.sno,sname From s,sc Where cno=’2008583’and s.sno=sc.sno Union Select s.sno,sname From s,sc Where cno=’2008585’and s.sno=sc.sno（12）查询与“数据库系统概论”出版社相同且作者相同的图书信息；
Select se.bno,se.bname,se.price From book f,book se Where f.bname=’数据库系统概论’ and f.bpc=se.bpc and f.author=se.author and se.bname<>f.bname ```bash 或者 Select * From book Where bpc=(select bpc From book Where bname=’数据库系统概论’) and author=(select author From book Where bname=’数据库系统概论’) and bname<>’数据库系统概论’（13）查询选修了课程“2008583”但没有选修课程“2008585”的学生学号及姓名。
Select s.sno,sname From s,sc Where cno=’2008583’ and s.sno=sc.sno Except Select s.sno,sname From s,sc Where cno=’2008585’and s.sno=sc.sno一、根据以下要求，完成相应的SQL语句。 （1）创建一个SPJ库，该数据库的主数据文件的逻辑名称是spj_data，操作系统文件是spj.mdf，初始大小是10MB，最大是30MB，以10%的速度增加；该数据库的日志文件的逻辑名称是spj_log，实际文件名是spj.ldf，初始大小是3MB，最大是10MB，以1MB的速度增加，文件存储在D盘data文件夹上。（10分）
create database spj on(name=spj_data,filename='D:\data\spj.mdf',size=10mb,maxsize=30mb,filegrowth=10%) log on (name=spj_log,filename='D:\data\spj.ldf',size=3mb,maxsize=10mb,filegrowth=1mb)（2）创建一张零件表P，包括以下字段：Pno（零件号） char(2)不为空，主键；Pname（零件名）varchar(10)不为空；color（颜色）varchar(10) 可为空；weight(重量) int 不为空，取值范围0-500。（10分）
create table P (pno char(2) not null primary key, pname varchar(10) not null, color varchar(10), weight int not null check(weight>=0 and weight<=500))（3）查询供应量在400以上的零件名称。（5分）
select distinct pname from p,SPJ where P.Pno=SPJ.Pno and qty>400（4）统计各供应商供应零件和供应工程的数量。（5分）
select sno,count(distinct pno)零件种类,count(distinct jno)工程数目 from spj group by sno（5）查询由编号为S1的供货商供应零件的工程名称和工程所在城市。（5分）
select distinct jname,city from j,spj where j.jno=spj.jno and sno='s1'（6）往零件表中插入一条记录（Pno=‘p6’,Pname=‘钢钉’,color=‘灰色’，weight=60）（5分）
insert into P values('p6','钢钉','灰色',60)（7）修改由S2供应商供应J1工程的P5零件的供应量为320。（5分）
update SPJ set qty=320 where sno='s2' and Jno='J1' and Pno='p5'（8）删除由西安供应商供应的相关信息。（5分）
delete from spj where sno in(select sno from s where scity='西安')（9）创建视图view_one,查询供应板手的供应商号。（5分）
create rule rule_num as @num>=10 and @num<=2000 sp_bindrule rule_num,'spj.qty'（10）创建规则rule_num,要求取值在10-2000,并将它绑定到SPJ表的qty字段（5分）。
create view view_one as select distinct sno from spj,p where spj.pno=p.pno and pname='板手'二、已知某医院以如下特点：每位医生属于且仅属于一个科室；每个科室可以聘用若干医生，对每个医生聘用时存有聘用期限和聘用日期；一个病人可以由不同的医生为其诊断，每个医生可以为若干病人诊治，每次诊断存有就诊日期，诊断结果，药方。设科室的属性包括科室号，科室名称，科室主任，科室简介；医生的属性包括工号，姓名和职称；病人的属性包括挂号ID，姓名、年龄、住址。 （1）设计E-R图，注明属性和联系类型。（10分） （2）将以上E-R转换为关系模式，并用下划线注明主键，用波浪线注明外键。（10分）
科室（科室号，科室名称，科室简介，科主任） 医生（工号，姓名，职称，聘用日期，聘用期限，科室号） 病人（挂号Id，姓名，年龄，住址） 诊治（工号，挂号id，就诊日期，诊断结果，药方）**
**
五 、 程序设计题 （题 本大题 12 小题 ，共 共 45 分 。） ）( 批改参考标准 ： 答案不唯一 ， 只要求解正确 ， 能够表示数据处理要 求均得分；程序中有语法错误，1 处扣 1 分，其中语句结构不正确者本题不得分，英文单词写错酌情扣分） 假设订单数据库 ORDERS 中有 3 张表： 客户表：Customers(Cid,Cname,City)，包括的属性分别为客户编号、 客户名、客户所在城市；产品表：Products(Pid,Pname,Quantity,Price)，包括的属性分别为产品编号、产品名称、产 品销售数量和产品单价；订单表：Orders(Ordno, Cid, Pid, Month, Qty)，包括的属性分别为订单号、客户编号、产品 编号、订货月份和订货数量。 1、 用关系代数表示：查询没有“成都”客户订购的产品编号及名称。（3 分） pid,pname(products) — pid,pname ( city=’ 成都 ’ (customers orders products)) …………3 分 2、 用关系代数表示：查询订购了客户“周林”所订购的所有产品的客户编号。（3 分） cid,pid(orders) ,pid ( cname=’ 周林 ’ (customers orders)) …………3 分 用 用 SQL 命令完成下列 3-12 的操作： 3、 创建 ORDERS 数据库，主数据库文件逻辑名称为 orders_data，物理名称为 D:\orders_data.mdf，数据文件初 始大小为 100MB，最大值为 5000MB，数据文件大小以 10MB 增量增加；日志文件逻辑名称为 orders_log， 物理名称为 D:\orders_log.ldf， 日志文件初始大小为 10MB，最大值 500MB，日志文件大小以 5％的增量增 加（6 分）。
CREATE DATABASE ORDERS ON PRIMARY ( NAME = orders_data, FILENAME = ' D :\ orders _data.mdf ', SIZE = 100MB, MAXSIZE = 5000MB, FILEGROWTH = 10MB) LOG ON ( NAME = orders _log, FILENAME = ' D :\ orders _log.ldf', SIZE = 10MB, MAXSIZE = 500MB, FILEGROWTH = 5%) GO4、 假设其它表已创建，写出创建表 Orders 的 SQL 语句，设置相应的主键和外键（如果有），订货月份的默认 值为 1。数据类型：Ordno、Cid、Pid 均为字符型，长度为 10。（6 分）
USE ORDERS GO CREATE TABLE ORDERS ( Ordno char(10) PRIMARY KEY, cid char(10) NOT NULL FOREIGN KEY REFERENCES CUSTOMERS(cid), pid char(10) NOT NULL FOREIGN KEY REFERENCES PRODUCTS(pid), month INT DEFAULT 1, qty INT ) )5、 创建一个规则 PR_RULE，PR_RULE 的值大于等于 0，小于等于 500，并绑定到 Products 表的 Price 列。（3 分）
USE ORDERS GO CREATE RULE PR_RULE AS @pr>=0 and @pr<=500 GO SP_BINDRULE PR_RULE , 'PRODUCTS. Price' GO6、向产品表中增加一个产品，名称为鼠标，编号为 P20，单价为 8.50 元，销售数量暂时未知。（3 分）
USE ORDERS GO INSERT INTO PRODUCTS VALUES(‘p20’,’鼠标’,8.50,NULL)7、将所有产品名称为“键盘”的产品单价提升 10%。（3 分）
USE ORDERS GO UPDATE PRODUCTS SET PRICE=PRICE*1.1 WHERE PNAME=’键盘’8、删除所有姓“毕”的客户在 2 月份的所有订单。（3 分）
USE ORDERS GO DELETE FROM ORDERS WHERE cid IN ( SELECT cid FROM CUSTOMERS WHERE cname LIKE ‘毕%’ ) AND month=2 查询月订单总数、月均订货量，要求赋予别名，并按月均订货量输出前 5 位。（3 分） USE ORDERS GO SELECT TOP 5 Months 月份, COUNT(*) 订单总数,AVG(Qty) 月均订货数量 FROM ORDERS GROUP BY Months ORDER BYAVG(Qty) DESC10、查询没有“成都”客户订购的产品信息，输出产品编号，产品名称和产品单价。（3 分）
USE ORDERS GO SELECT Pid, Pname, Price FROM PRODUCTS WHERE pid NOT IN (SELECt pid FROM ORDERS O, CUSTOMERS C WHERE O.cid=C.cid AND city=’成都’)11、查询产品名称“路由器”订购数量最大的客户信息，输出客户编号，客户名称，客户所在城市。（3 分）
USE ORDERS GO SELECT TOP 1 C.CID,CNAME,CITY,SUM(QTY) FROM PRODUCTS,CUSTOMERS,ORDERS WHERE PRODUCTS.Pid=ORDERS.Pid and CUSTOMERS.Cid=ORDERS.Cid and Pname=’路由器’ GROUP BY C.CID,CNAME,CITY ORDER BY SUM(QTY) DESC12、创建一个视图 V_CUS，要求输出订货总数量大于 100 的客户编号、客户名、产生的订单数量和订货的总 数量。（6 分）
USE ORDERS GO CREATE VIEW V_CUS AS SELECT ORDERS.cid, cname, SUM(Qty) 总订货数量, COUNT(pid) 订单数 FROM ORDERS,PRODUCTS WHERE ORDERS.pid=PRODUCTS.pid GROUP BY ORDERS.cid, cname HAVING SUM(Qty)>100（1）基于 S 表创建一个名为 SVIEW1 的视图，输出 sno、sname、sex、birth、homadd， 然后通过该视图查询学生信息；
Create view sview1 As Select sno 学号，sname 姓名,sex 性别,birth 生日,homadd 地址 From s Go Select * From sview1（2）基于 S 表创建一个男同学的名为 SVIEW2 的视图，输出 sno、sname、homadd，然 后通过该视图查询男生信息；
Create view sview2 As Select sno 学号,sname 姓名,homadd 地址 From s Where sex=’男’ Go Select * From sview2（3）基于 DEPT 表创建一个学生人数排在前五名的视图 DEPTVIEW1，输出 dno、dname、 dheader，然后通过视图查询系信息数据；
Create view DEPTVIEW1 As Select top 5 dno 系号，dname 系名，dheader 院长 From dept Group by dno,dname,dheader Order by count(*) desc Go Select * From deptview1不要group by 那行 （4）基于 S 和 SC 表创建一个按系别分组显示，选修课程考试平均成绩的视图 SVIEW3， 输出“系号”、“平均成绩”，然后通过视图查询’04’系的数据信息；
Create view SVIEW4 As Select dno 系号，avg(grade) 平均成绩 From s,sc Where s.sno=sc.sno Group by dno Go Select * From sview3 Where 系号=’D04’（5）基于 S、C 和 SC 创建一个联合视图 SVIEW4，要求输出“学号”、“姓名”、“课程名”、“学分”、“总学时”、“成绩”和“选修学期”；
Create view SVIEW4 As Select s.sno 学号，sname 姓名,cname 课程号,credit 学分,tperiod 总学时,grade 成绩,tine 选修学期 From s,c,sc Where s.sno=sc.sno and c.cno=sc.cno（6）基于（5）中的视图 SVIEW4 创建视图 SVIEW5，要求输出“姓名”、“课程名”和 “成绩”；并通过视图 SVIEW5 查询“数据库原理”课程的选修信息；
Create view sview5 As Select sname 姓名,cname课程名，grade 成绩 From s,c,sc Where s.sno=sc.sno and c.cno=sc.cno Go Select * From sview5 Where 课程名=’数据库原理’（7）基于（5）中创建的视图 SVIEW4 进行修改，要求输出“姓名”、“课程名”和“成 绩”；并对该视图进行加密；
Use jxgl Go Alter view SVIEW4 With encryption As Select sname 姓名，cname 课程名，grade 成绩 From s,c,sc Where s.sno=sc.sno and c.cno=sc.cno（6）查看视图的定义；
Sp_helptext sview4（8）将视图 SVIEW5 重新命名为 STUVIEW5；
Sp_rename’SVIEW5’,’STUVIEW5’（9）将视图 STUVIEW5 删除；
Use jxgl Go Drop view STUVIEW5（10）通过（1）中创建的名为 SVIEW1 的视图，向 S 表中插入数据（’15043102’,’兰一 飞’，’男’,’1993-11’,’北京市通州区焦王庄’）；
Use jxgl Go Insert into SVIEW1 Values(’15043102’,’兰一飞’，’男’,’1993-11’,’北京市通州区焦王庄’)（11）通过 SVIEW1 将（10）中插入的学号为 115043102 的学生姓名更新为“兰飞”；
Use jxgl Go Update SVIEW1 Set 姓名=‘兰飞’ Where 学号=115043102（12）通过（1）中创建的名为 SVIEW1 的视图，删除所有男同学的信息。
Use jxgl Go delete from SVIEW1 Where 性别=‘男’1、实验作业 （1）在 S 表 sno 列上创建一个唯一聚簇索引 S_INDEX，索引按列顺序为升序，填充因子50%；
use jxgl go create unique clustered index S_INDEX on s(sno asc) WITH FILLFACTOR = 50（2）在 DEPT 表 dname 列上创建一个唯一非聚簇索引 DEPT_INDEX，索引按列顺序为降 序；
use jxgl go create unique nonclustered index DEPT_INDEX on dept (dname desc)（3）用系统存储过程 SP_HELPINDEX 查看 S 表的索引信息;
SP_HELPINDEX s（4）用系统存储过程将 S 表的索引 S_INDEX 重新命名为 SNO_INDEX;
SP_RENAME ‘s.S_INDEX’,’SNO_INDEX’（5）将 S 表的索引“SNO_IINDEX”删除。
drop index s.SNO_IINDEX（6）在 SC 表上创建一个名为 SC_INDEX 的非聚簇复合索引，索引关键字为 sno,cno，升序，填充因子 50%。
USE JXGL GO CREATE NONCLUSTERED INDEX SC_INDEX ON SC(sno ASC , cno ASC) WITH FILLFACTOR = 502、实验作业 （1）为 C 表的 credit（学分）字段创建缺省约束，缺省值为 4；
alter table C add constraint default_credit default ‘4’for credit（2）为 S 表的 sex（性别）字段创建检查约束，值是 0 或 1；
alter table S add constraint check_sex check(sex=’0’or sex=’1’)（3）将 S 表的 sno（学号）设为主键（假如在创建表时没有设置主键约束）；
alter table S add constraint PK_SNO primary key clustered(sno)（4）为 SC 表 grade（成绩）字段创建一个检查约束，使成绩值在 0-100 之间。
USE JXGL GO ALTER TABLE SC ADD CONSTRAINT CHECK_GRADE CHECK (grade>=0 and grade<=100)（5）为 C 表的 cname 字段创建唯一约束；
alter table C add constraint unique_CNAME unique nonclustered(cname)（6）为表 S 创建外键 dno，外键 dno 参考表 DEPT 中的主键 dno；
alter table S add constraint FK_DNO foreigh key(dno) references DEPT(dno)（6） 创建一个名为 SEX_DEFAULT，值为 1 的默认值，并绑定到表 S 的 Sex 列，然后解 除这个绑定，绑定解除后将此默认值删除；
create default SEX_DEFAULT as ‘1’ go SP_BINDEFAULT SEX_S,’S.sex’ go SP_UNBINDEFAULT ’S.sex’ DROP DEFAULT SEX_DEFAULT go（7）创建一个规则 PRICE_RULE，PRICE_RULE 的值大于等于 0，小于等于 500，并绑 定到表 BOOK 的 price 列，然后解除这个绑定，绑定解除后将此规则删除。
create rule PRICE_RULE as @price>=0 and @price<=500 go SP_BINDRULE PRICE_RULE,’BOOK.price’ go SP_UNBINDRULE ’BOOK.price’ go drop rule PRICE_RULE【1】：在 JXGL 中创建两个自定义数据类型 a 和 b。
USE JXGL GO EXEC SP_ADDTYPE a, 'varchar(15)','NULL' EXEC SP_ADDTYPE b, 'char(15)','NOT NULL'【2】： ：删除用户自定义数据类型 a。
USE JXGL GO SP_DROPTYPE a【3】:声明两个整型的局部变量：a 和 b,并给 a 赋初值 5，给 b 赋值为 5 倍的 a, 显示 b 的结果。
DECLARE @a int, @b int SET @a=5 SET @b=@a*5 SELECT @b【4 】: 将 GETDATE 函数的结果转换为 varchar 数据类型，并将其打印输出： “本信息打印的时间是”。
PRINT '本信息打印的时间是' + (CONVERT(varchar(30), GETDATE( )) )+ ‘。’【5】： ：将 JXGL 数据库中表 BOOK 里的 bno 为 “b01”和“b02”记录显示出来， 其余的用“other”显示。
CIDP-ZYX USE JXGL GO SELECT BOOK.bno,bno= CASE bno WHEN 'b01' THEN 'b01' WHEN 'b02' THEN 'b02' ELSE 'OTHER' END FROM BOOK【6】：将 JXGL 中表 BOOK 里的 price 大于 50 的记录对应的 price_level 显示“high”， price 小于 20 的记录对应的 price_level 显示“low”，其余的显示“flat”。
USE JXGL GO SELECT bno, 'price level'= CASE WHEN price> 50 THEN 'high' WHEN price < 20 THEN 'low' ELSE 'flat' END FROM BOOK【7】： ：定义一个整形变量，如赋值为 1，显示“I am a student”，否则显示“I am a teacher”。
DECLARE @a int SELECT @a=2 IF @a=1 BEGIN PRINT 'I am a student' END ELSE PRINT 'I am a teacher' GO【8】： ：输出字符串“student”中每一个字符的 ASCII 值和字符。
DECLARE @p int, @str char(6) SET @p=1 SET @str='student' WHILE @p<=DATALENGTH(@str) BEGIN SELECTASCII(SUBSTRING( @str,@p,1))AS ASCCODE, char(ASCII(SUBSTRING( @str,@p,1)))AS ASCCHAR SET @p= @p+1 END【实验 9-9】 】： ：使用 IF 语句求 1 到 100 之间的累加和并输出结果。
DECLARE @sum int, @count int SELECT @sum=0, @count=1 CIDP-ZYX LABEL: SELECT @sum=@sum+@count SELECT @count=@count+ 1 IF @count<=100 GOTO LABEL SELECT @sum,@count【9】: 设置在 9:00 进行一次查询操作。
USE JXGL GO BEGIN WAITFOR TIME ‘18:00’ SELECT * FROM BOOK END【10】:设置在 5 分钟后进行一次查询操作。
USE JXGL GO BEGIN WAITFOR DELAY ‘00:00:05’ SELECT * FROM BOOK END存储过程的创建及应用 （1）创建存储过程 PROC1，要求返回学号和选修课程的门数及平均分；
```bash use jxgl go if exists(select name from sysobjects where name='PROC1' AND type='P') drop procedure PROC1 go create proc PROC1 AS select s.sno 学号, count(cno) 课程数,avg(grade) 平均分 from s,sc where s.sno=sc.sno group by s.sno exec PROC1（2）创建一个从 S 表查询学生信息的存储过程 PROC2，要查询的系号通过执行语句中 的输入参数传递给存储过程；
use jxgl go create proc PROC2 @dno char(10) as select * from s where dno=@dno go exec proc2 @dno=’D01’（3）创建一个查询课程信息的存储过程 PROC3，要查询的课程号通过执行语句中的输 入参数传递给存储过程，要求输出课程名、选修课程的人数、平均分；
use jxgl go create proc PROC3 @cno char(10) As select cname 课程名, count(sc.cno) 选课人数, avg(grade) 平均分 from c,sc where c.cno=sc.cno and sc.cno=@cno group by sc.cno ,c.cname go exec PROC3 @cno=’2008010’（4）为 JXGL 数据库建立一个存储过程 PROC4，通过执行存储过程将图书信息添加到 BOOK 表，执行该存储过程；
use jxgl go create proc PROC4 @bno char(10) = NULL, @bname varchar(30)=null, @author varchar(30)=null, @bpc nvarchar(80)=null, @price numeric(5,1)=null As If @bno is null or @bname is null or @author is null or @bpc is null Begin Print '请重新输入图书信息!' Print '你必须提供图书的书号、书名、作者、出版社.' Print '图书价格可以为空' Return End Begin transaction Insert into book (bno,bname,author,bpc,price) Values (@bno,@bname,@author,@bpc,@price) If @@error <>0 Begin Rollback tran Return End Commit transaction Print @bname+'的信息成功添加到表BOOK中' go exec PROC4 'B14','JAVA基础教程','耿祥义','清华大学出版社','45.0'（5）创建修改指定学生的系别的存储过程 PROC5，输入参数为：学号和修改后的系别， 修改后的系别默认为“d004”；
Use jxgl Go Create proc PROC5 @sno char(10), @dno char(10) As alter table s NOCHECK CONSTRAINT all update s set dno=@dno where sno=@sno Go Exec PROC5 @sno='125011502', @dno='d004'（6）创建带默认值参数的存储过程 PROC6，输入参数为书名（默认值为书名含有“数 据库”的图书），要求输出版社、定价和作者，并执行该存储过程；
Use jxgl Go Create proc PROC6 @bname varchar(30)=’%数据库%’ As select bpc 出版社, price 定价, author 作者 From book where bname like @bname Go Exec PROC6 --exec PROC6 ‘高等数学’（7）创建计算 1+2+3…一直加到指定值的存储过程 PROC7，要求：计算的终值由输入 参数决定，计算结果由输出参数返回给调用者；
Create proc PROC7 @sum int OUTPUT , @count int , @num int As Label:select @sum=@sum+@count Select @count=@count+1 If @count<=@num Goto label Go @sum int, @count int, @num int Set @sum=0 Set @count=1 Exec PROC7 @sum OUTPUT,@count,@num=100 print @sum（8） 创建带输入和输出参数的存储过程 PROC8，当输入学号时，给出该同学的姓名,性别，课程平均分和最低分；调用存储过程 PROC8：查询学号为“115043101” 同学的信息；
use jxgl go create proc PROC8 @sno char(10), @avgpjf int OUTPUT, @mincj int OUTPUT as select @avgpjf=avg(grade) from sc where sno=@sno select @mincj=min(grade) from sc where sno=@sno select sname 姓名, sex 性别, @avgpjf 平均分, @mincj 最低分 from s,sc where s.sno=sc.sno and sc.sno=@sno go declare @avg1 int declare @min1 int exec PROC8 '125051501 ',@avg1 OUTPUT ,@min1 OUTPUT（9）查看中存储过程 PROC8 的定义；
Sp_help PROC8（10）把（8）中存储过程 Proc8 重命名为 PSTU；
sp_rename ‘PROC8’ ,’PSTU’（11）把（10）中存储过程 PSTU 删除。
drop PRODEDURE.PSTU触发器的创建及应用
5、实验作业 （1）为 S 表创建一个插入触发器 S_INSERT，当向 S 表中添加数据时，如果添加的学 生性别不是“男”或者“女” ，则将禁止插入该学生的信息；
USE jxgl GO CREATE TRIGGER S_INSERT ON S FOR INSERT AS IF EXISTS (SELECT * FROM INSERTED WHERE sex<>'男' or sex<>'女') BEGIN PRINT'性别错误' ROLLBACK END GO（2）为 S 表建立删除触发器 S_DELETE，在删除学生记录时自动更新 DEPT 表中相应 系的学生人数；
CREATE TRIGGER S_DELETE ON S FOR DELETE AS IF UPDATE(dno) BEGIN UPDATE DEPT SET Snum= (SELECT COUNT(S.sno) FROM S,INSERTED I WHERE S.dno=I.dno) FROM DEPT,INSERTED I WHERE DEPT.dno=I.dno UPDATE DEPT SET snum= (SELECT COUNT(S.sno) FROM S,DELETED E WHERE S.dno=E.dno) FROM DEPT, DELETED E WHERE DEPT.dno=E.dno END GO（3）为 DEPT 表建立删除触发器 D_DELETE，在删除系部信息时，如果被删除的系部 有学生，则禁止删除操作；
CREATE TRIGGER D_DELETE ON DEPT FOR DELETE AS IF(EXISTS (SELECT dno FROM s WHERE dno in(Select dno FROM deleted))) BEGIN PRINT '删除记录操作不能完成！' ROLLBACK TRANSACTION RETURN END GO（4）为 BOOK 表创建一个更新触发器 B_UPDATE，当修改 BOOK 表中清华大学出版社 的图书定价时，如果修改值大于 1000，则不能修改此图书的定价；
USE JXGL GO CREATE TRIGGER B_UPDATE ON BOOK FOR UPDATE AS if exists (select * from inserted where price >1000 and bpc='清华大学出版社') begin print '修改值大于 1000，则不能修改此图书的定价' ROLLBACK END GO（5）为 SC 表定义一个插入和更新触发器 SC_IN_UP，在此触发器中保证成绩在 0~100；
USE jxgl GO CREATE TRIGGER SC_IN_UP ON SC FOR INSERT,UPDATE AS IF EXISTS (SELECT*FROM INSERTED WHERE grade<0 or grade>100) BEGIN PRINT '成绩必须在 0~100的范围内' ROLLBACK END GO（6）为 C 表定义一个插入触发器 C_INSERT，实现：向 C 表添加一行数据，确保选用 教材的图书(bno）存在 BOOK 表中；
USE jxgl GO CREATE TRIGGER C_INSERT ON C FOR INSERT AS IF(not EXISTS (SELECT bno FROM C WHERE bno in(Select bno FROM inserted))) BEGIN PRINT '插入操作不能完成！' PRINT '该图书不存在.' ROLLBACK TRANSACTION RETURN END GO（7）查看 BOOK 表中的触发器信息；
EXEC SP_HELPTRIGGER BOOK（8）查看触发器 B_UPDATE 的相关性；
EXEC SP_DEPENDS 'B_UPDATE'（9）删除触发器 B_UPDATE。
DROP TRIGGER B_UPDATE【1】：当某系增加一名学生，即向表 S 中 插入一行数据时，需要更改该学生所在 系的记录，以增加该系的学生总人数。用 请使用 INSERT 触发器自动完成这 个工作。
USE jxgl GO CREATE TRIGGER S_I ON S FOR INSERT AS DECLARE @nums tinyint SELECT @nums =d.snum FROM DEPT D , INSERTED I WHERE D.dno = I.dno IF (@nums > 0) BEGIN UPDATE DEPT SET snum = snum + 1 FROM DEPT D , INSERTED I WHERE D.dno = I.dno END ELSE BEGIN UPDATE DEPT SET snum = ( SELECT COUNT(S.sno) FROM S , INSERTED I WHERE S.dno = I.dno ) FROM DEPT D, INSERTED I WHERE DEPT. dno = I.dno END GO思考：向 S 表中插入数据行，测试某系部学生人数的变化;理解该存储过程的执行过程。 【2】： ：为表 S 创建一个 删除触发器，当删除表 S 中的一个学生信息时，将表 SC 中该同学相应的成绩记录删除掉。
USE JXGL GO CREATE TRIGGER S_D ON S FOR DELETE AS DECLARE @sno char(8) SELECT @sno=DELETED.sno FROM DELETED DELETE FROM SC WHERE SC.sno=@sno【3】:为 S 表建立 UPDATE 触发器，在学生数据变更时自动更新 DEPT 表的学 生人数。
USE JXGL GO CREATE TRIGGER S_U ON S FOR UPDATE AS IF UPDATE(dno) BEGIN UPDATE DEPT SET Snum = ( SELECT COUNT(S.sno) FROM S, INSERTED I WHERE S.dno = I.dno ) FROM DEPT, INSERTED I WHERE DEPT. dno = I.dno UPDATE DEPT SET snum = ( SELECT COUNT ( S.sno ) FROM S , DELETED E WHERE S.dno= E.dno ) FROM DEPT, DELETED E WHERE DEPT . dno= E. dno END GO【4】: 在 C 表中定义一个插入和更新触发器 C_IN_UP，在此触发器中保证学分 在 1~6 的范围内。( 该列子了解，能用约束实现的首先考虑基本约束)
USE jxgl GO CREATE TRIGGER C_IN_UP ON C FOR INSERT, UPDATE AS IF EXISTS ( SELECT * FROM INSERTED WHERE credit<1 or credit>6 ) BEGIN PRINT ‘学分必须在 0~6 的范围内’ ROLLBACK END GO【5】：向 SC 表添加一行数据，检查所插入数据的有效性。确保学生(sno）存在S 表中，课程（cno）存在于 C 表中。( 该列子了解，能用约束实现的首先考虑基本约束)
USE JXGL GO CREATE TRIGGER SC_I ON SC FOR INSERT AS IF ( ( NOT EXISTS ( SELECT sno FROM S WHERE sno IN ( SELECT sno FROM INSERTED ) ) ) OR ( NOT EXISTS ( SELECT cno FROM C WHERE cno IN ( SELECT cno FROM INSERTED ) ) ) ) BEGIN PRINT ‘添加记录操作不能完成！’ PRINT ‘输入的学号或课程号有错误。’ ROLLBACK TRANSACTION END【实验 11-6】 】：为 BOOK 表创建删除触发器，保证被选为教材的图书数据不能删除。(该 该 列子了解，能用约束实现的首先考虑基本约束)
CREATE TRIGGER BOOKR_DELETE ON BOOK FOR DELETE AS IF (EXISTS ( SELECT bno FROM C WHERE bno in ( Select bno FROM deleted ) ) ) BEGIN PRINT ‘删除记录操作不能完成！’ PRINT ‘该图书被选为课程的教材。‘ ROLLBACK TRANSACTION RETURN END GO数据库安全性 5、实验作业 （1）创建一个登录帐户 lm，密码为 1226、使用的默认数据库为 JXGL；创建登录帐户 zxy，密码为 615，使用默认数据库为 MODEL；
EXEC SP_ADDLOGIN 'lm','1226', 'JXGL' EXEC SP_ADDLOGIN 'zxy', '615', 'MODEL'（2）查看登录帐户 lm；删除登录帐户 zxy；
EXEC SP_HELPLOGINS 'lm' EXEC SP_DROPLOGIN 'zxy'（3）在混合验证模式下，为数据库 JXGL 登录帐户”lm”和“zxy”创建同名的数据库用户；
use jxgl go EXEC SP_GRAWINDOWSBACCESS 'lm' EXEC SP_GRAWINDOWSBACCESS 'zyx'（4）使用命令 SP_REVOKEDBACCESS 将数据库中的“zxy”删除掉；
SP_REVOKEDBACCESS 'zxy'（5）将登录名 zxy 加到 sysadmin 角色中；
SP_ADDSRVROLEMEMBER 'zxy', sysadmin（6）增加一个叫 fz 的自定义数据库角色；
SP_ADDROLE 'fz', dbo（7）使用系统存储过程 SP_ADDROLEMEMBER 将数据库用户 lm，zxy 作为成员添加 到数据库角色 fz 中，再将 lm 从数据库角色 fz 中删除；
EXEC SP_ADDROLEMEMBER 'fz',lm EXEC SP_ADDROLEMEMBER 'fz',zyx go SP_DROPROLEMEMBER 'fz',lm（8）授予用户 lm 在数据库 JXGL 中创建表及对学生表具有查询、插入权的许可；
GRANT CREATE TABLE TO lm GRANT SELECT ,INSERT ON C TO lm（9）撤消用户 lm 在数据库 JXGL 中创建表及对学生表具有查询、插入权的许可；
REVOKE CREATE TABLE FROM lm REVOKE SELECT,INSERT ON C FROM lm（10）禁止用户 lm 在数据库 JXGL 中对学生表执行插入、删除操作。
DENY INSERT, DELETE ON S TO lm游标对应三道练习题： （1）定义一个游标S_COURSOR1，通过游标将学生表S中性别为男的学生信息显示出来；
USE JXGL GO DECLARE @sno char(10), @sname char(10), @sex char(2), @birth smalldatetime, @endate smalldatetime, @homadd nvarchar(40), @dno char(10) --声明游标 DECLARE S_COURSOR1 SCROLL CURSOR FOR SELECT sno,sname,sex,birth,endate,homadd,dno FROM s where sex='男' FOR READ ONLY --打开游标 OPEN S_COURSOR1 --提取数据 FETCH FROM S_COURSOR1 INTO @sno,@sname,@sex,@birth,@endate,@homadd,@dno WHILE @@FETCH_STATUS=0 BEGIN PRINT '学号:'+@sno+' 姓名:'+@sname+' 性别:'+@sex+' 出生日期:'+convert(char(20),@birth,23)+'登记日期:'+convert(char(20),@endate,23)+'住址:'+@homadd+'系号:'+@dno FETCH FROM S_COURSOR1 INTO @sno,@sname,@sex,@birth,@endate,@homadd,@dno END --关闭游标 CLOSE S_COURSOR1 --释放游标 DEALLOCATE S_COURSOR1（2）定义一个游标S_COURSOR2，通过游标将学生表S中记录号为3的学生姓名改为“李萍”；
use jxgl go declare S_COURSOR2 scroll cursor for select sname from s for update of sname open S_COURSOR2 fetch absolute 3 from S_COURSOR2 update s set sname='李萍' where current of S_COURSOR2 fetch absolute 3 from S_COURSOR2 close S_COURSOR2 deallocate S_COURSOR2（3）定义一个游标S_COURSOR3，通过游标将学生表S中“d01”系的学生数据删除；
use jxgl go declare @sno char(10), @sname nvarchar(10), @sex char(2), @birth char(10), @endate char(10), @homadd nvarchar(40), @dno char(10) declare S_COURSOR3 scroll cursor for select sno,sname,sex,birth,endate,homadd,dno from s order by sname open S_COURSOR3 fetch next from S_COURSOR3 into @sno,@sname,@sex,@birth,@endate,@homadd,@dno while @@FETCH_STATUS=0 begin if @dno='d01' delete from s where current of S_COURSOR3 fetch next from S_COURSOR3 into @sno,@sname,@sex,@birth,@endate,@homadd,@dno end close S_COURSOR3 deallocate S_COURSOR3（4）使用游标编写存储过程，统计图书的被选为教材的情况（显示书名、选该图书作为教材的课程号，及选用图书作为教材的课程门数）；
use jxgl go create proc SCSNUMn(@bname char(4)) as begin declare @cno char(10),@sum int select @sum=0 declare g_sum cursor for select bname,cno from c,book where c.bno=book.bno open g_sum fetch next from g_sum into @bname,@cno while (@@FETCH_STATUS=0) begin print '书名：'+@bname+' '+'课程号：'+@cno select @sum=@sum+1 fetch next from g_sum into @bname,@cno end close g_sum deallocate g_sum print @bname+'作为教材的课程门数：'+str(@sum) end go（5）以事务的方式向BOOK表中插入三本图书的信息：（书号：b001,书名：数据库原理及应用, 出版社：电子工业出版社）；（书号：b002,书名：数据库系统, 出版社：机械工业出版社）；（书号：b003,书名：数据库技术, 出版社：），其中第三本书缺少出版社信息。注：BOOK表的bpc属性为NOT NULL。
use jxgl go begin tran BOOK_TRAN insert into BOOK(bno,bname,bpc) values('b001','数据库原理及应用','电子工业出版社') save tran aa insert into BOOK(bno,bname,bpc) values('b002','数据库系统','机械工业出版社') insert into BOOK(bno,bname,bpc) values('b003','数据库技术',null) go if @@error<>0 rollback tran aa go commit tran BOOK_TRAN go
