实验二 数据定义
一、实验目的
1、掌握数据库引擎的使用方法，加深对SQL的语句的理解； 2、使用SQL语句创建数据库、删除数据库
3、熟练掌握DDL语言的数据定义操作，包括CREATE、ALTER、DROP。
二、实验内容
在SQL查询分析器中用CREATE、DROP、ALTER语句创建、删除、修改基本表。
三、实验步骤
1、根据课本中的说明，分析S表，P表，J表和SPJ表的主码和外码：
(1) S表，P表，J表的主码分别是哪个属性，S表,P表,J表这三个表有没有外码？如果有，请写出。
S表的主码 SNO P表的主码PNO J表的主码JNO 没有外码
(2) SPJ表的主码是由哪几个属性组成的属性组？SPJ表的外码有几个，分别是哪些属性？分别参照哪些表的哪个属性？
SPJ主码（SNO,PNO,JNO）
外码SNO参照S表的SNO，外码PNO参照P表的PNO，外码JNO参照J表的JNO。
2. 新建数据库SPJ，并在数据库SPJ中创建S表，P表，J表和SPJ表。 （1）创建供应商表S的sql语句，要求设置主码： CREATE TABLE S (
SNO CHAR(4) PRIMARY KEY, SNAME VARCHAR(40), STATUS SMALLINT, CITY VARCHAR(20) );
（2）创建零件表P的sql语句，要求设置主码并将该约束命名为P_PK：
CREATE TABLE P(
PNO CHAR(4) CONSTRAINT P_PK PRIMARY KEY, PNAME VARCHAR(40), COLOR CHAR(2), WEIGHT SMALLINT );
（3）创建工程项目表J的sql语句，要求设置主码并命名为J_PK： CREATE TABLE J (
JNO CHAR(4) CONSTRAINT J_PK PRIMARY KEY, JNAME VARCHAR(40), CITY VARCHAR(20) );
（4）创建基本表供应情况表SPJ的sql语句，要求设置主码和外码： CREATE TABLE SPJ (
SNO CHAR(4) REFERENCES S(SNO), PNO CHAR(4) REFERENCES P(PNO), JNO CHAR(4) REFERENCES J(JNO), QTY SMALLINT,
PRIMARY KEY(SNO,PNO,JNO) );
3. 在窗口下键入DROP TABLE S命令，运行后观察结果。 （1）运行结果是什么？ 不能执行
（2）为什么会出现这种结果？
破坏参照完整性。
4. 打开S表，并往S表中输入第一行数据。 （1）如何保存输入的数据？
（2）输入第二行数据时如果不输入属性SNO的值，将出现什么情况？为什么？
不能执行
违背实体完整性。
5. 在S表中增加一个新的字段“TEL”，数据类型为char(11) （1）sql语句：
ALTER TABLE S ADD TEL CHAR(11)
（2）观察属性TEL的值，值为 NULL 。
6. 修改S表中属性SNAME的数据类型改为varchar(20)，其sql语句是
ALTER TABLE S ALTER COLUMN SNAME VARCHAR(20);
7. 删除S表中的属性TEL的SQL语句：
ALTER TABLE S DROP COLUMN TEL;
8. 为P表添加约束C1：零件重量必须在0到100之间，其SQL语句：
ALTER TABLE P ADD CHECK(WEIGHT BETWEEN 0 AND 100); 9. 验证完整性约束：
（1）在S表中输入第一条记录，若再往S表中输入一条“S1，盛锡”的记录，能不能输入，为什么？
不能，主键取值唯一。
（2）往P表、J表和SPJ表中分别输入第一条记录，若往SPJ表中输入第二条记录，能不能实现？为什么？ 不能，违背参照完整性。
（3）此时若要删除S表中的第一条记录能不能删除？为什么？
不能，违背参照完整性。
（4）往P表中输入一条记录“P7，齿轮，蓝，150”，能不能实现？为什么？ 不能，违背CHECK约束。
四、实验小结（实验过程中遇到什么问题？如何解决？）
实验三 单表查询
一、实验目的
1、掌握数据库引擎的使用方法，加深对SQL的语句的理解； 2、使用SELECT语句进行单个表格数据查询； 3、熟练掌握简单表的数据查询、数据排序的操作方法。 二、实验内容
1、使用SELECT语句完成简单查询操作。
2、该实验包括投影、选择条件表达，数据排序，查询结果分组等。 三、实验步骤
1、查询所有供应商的姓名和所在城市。
Sql语句：
SELECT SNAME, CITY FROM S; 2、查询所有零件的名称颜色和重量。
Sql语句：
SELECT COLOR,WEIGHT FROM P; 3、查询S1供应商所供应零件的工程号码。
Sql语句：
SELECT JNO FROM SPJ WHERE SNO=’S1’; 4、查询名称带有“厂”字的工程项目的详细信息。
Sql语句：
SELECT * FROM J WHERE JNAME LIKE ‘%厂%’; 5、查询供应了零件的供应商号。
Sql语句：
SELECT DISTINCT SNO FROM SPJ; 6、查询重量在10到20之间的红色零件。
Sql语句：
SELECT * FROM P
WHERE WEIGHT BETWEEN 10 AND 20 AND COLOR=’红’; 7、查询所有零件的名称，并按重量降序排列
Sql语句：
SELECT PNAME FROM P ORDER BY WEIGHT; 8、查询地址在长春、北京或天津的工程项目的详细信息。
Sql语句：
SELECT * FROM J WHERE CITY IN (‘长春’,’北京’,’天津’); 9．查询零件表中零件的平均重量。
Sql语句：
SELECT AVG(WEIGHT) FROM P; 10. 统计每个供应商各供应了多少个零件。
Sql语句：
SELECT SNO,SUM(QTY) NUM FROM SPJ GROUP BY SNO; 11. 查询至少使用了三种或以上零件的工程项目有哪些。
Sql语句：
SELECT JNO FROM SPJ
GROUP BY JNO HAVING COUNT(DISTINCT PNO)>=3; 12. 查询至少提供了200个P1零件的供应商号。
Sql语句：
SELECT SNO FROM SPJ WHERE PNO=’P1’ GROUP BY SNO HAVING SUM(QTY)>=200;
实验四 连接查询、嵌套查询
一、实验目的
1、掌握SQL Server 2005数据库引擎的使用方法，加深对SQL的语句的理解； 2、使用数据库引擎查询文档用SELECT语句进行数据查询； 3、熟练掌握运用DML语言对表中的数据进行连接查询和嵌套查询 二、实验内容
利用连接查询和嵌套查询等方法实现多表查询。 三、实验步骤
1、找出工程项目J2使用的各种零件的名称及其数量
Sql语句：
SELECT PNAME,QTY FROM P,SPJ WHERE P.PNO=SPJ.PNO AND JNO=’J2’; 该查询能否用嵌套查询实现？为什么？ 不能，因为查询结果涉及到两张表的信息。 2、找出没有使用天津产的零件的工程号码
Sql语句：
SELECT JNO FROM J WHERE JNO NOT IN( SELECT JNO FROM SPJ WHERE SNO IN( SELECT SNO FROM S WHERE CITY=’天津’ ) ); 或
SELECT JNO FROM J WHERE JNO NOT IN( SELECT JNO FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND CITY=’天津’ );
3、找出使用上海产的零件的工程名称
Sql语句（使用连接和嵌套两种方法来实现）： 连接查询
SELECT JNAME FROM S,SPJ,J
WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND S.CITY=’上海’; 嵌套查询
SELECT JNAME FROM J WHERE JNO IN( SELECT JNO FROM SPJ WHERE SNO IN( SELECT SNO FROM S WHERE CITY=’上海’ ) ); 或
SELECT JNAME FROM J WHERE JNO IN( SELECT JNO FROM SPJ,S
WHERE SPJ.SNO=S.SNO AND S.CITY=’上海’ ) );
4、找出既使用S1供应商提供的零件又使用S2供应商提供的零件的工程号
Sql语句：
SELECT JNO FROM SPJ WHERE SNO=’S1’ AND JNO IN( SELECT JNO FROM SPJ WHERE SNO=’S2’ ); 或
SELECT X.JNO FROM SPJ X, SPJ Y
WHERE X.JNO=Y.JNO AND X.SNO=’S1’ AND Y.SNO=’S2’;
5、求使用S2供应商供应的P3零件数目超过丰泰盛供应商供应的P6零件数目的工程号
Sql语句：
SELECT JNO FROM SPJ X
WHERE SNO=’S2’ AND PNO=’P3’ AND QTY >( SELECT QTY FROM SPJ
WHERE JNO=X.JNO AND PNO=’P6’ AND SNO IN(
SELECT SNO FROM S WHERE SNAME=’丰泰盛’ ) ); 或
SELECT X.JNO FROM SPJ X,SPJ Y
WHERE X.JNO=Y.JNO AND X. SNO=’S2’ AND X.PNO=’P3’ AND X.QTY>Y.QTY AND Y.PNO=’P6’ AND Y.SNO IN( SELECT SNO FROM S WHERE SNAME=’丰泰盛’ );
实验五 数据更新
一、实验目的
1、掌握更新语句的基本语法和用法，加深对SQL的语句的理解； 2、了解DML语言的INSERT、UPDATE、DELETE对数据的操作，； 3、能将这些更新操作应用于实际操作中去，学会在SQL Server 2005中用DML语言对表中的数据进行插入、删除和修改。 二、实验内容
1、在查询分析器中使用INSERT、UPDATE、DELETE语句进行数据更新。 三、实验步骤
1、由S5供给J4的零件P6改为由S3供应，请做必要的修改
Sql语句：
UPDATE SPJ SET SNO=’S3’
WHERE SNO=’S5’ AND JNO=’J4’ AND PNO=’P6’;
2、向零件表中插入一个零件记录(零件代码：P7；零件名：螺钉；颜色：黑；重量：10)
Sql语句：
INSERT INTO P VALUES(‘P7’,’螺钉’,’黑’, 10);
3、从SPJ表中把S1供应某工程P1零件数量低于S1供应各工程P1零件数量平均值的供应元组全部删去。
Sql语句：
DELETE FROM SPJ WHERE SNO=’S1’ AND PNO=’P1’ AND QTY <(
SELECT AVG(QTY) FROM SPJ WHERE SNO=’S1’ AND PNO=’P1’ );
4、将P表中零件重量小于等于20的零件颜色全部改为黄色。
Sql语句：
UPDATE P SET COLOR=’黄’ WHERE WEIGHT<20;
5、求每个供应商供应给J1工程的零件总数,并把结果存入数据库中。
Sql语句：
CREATE TABLE SJ1_SUM( SNO CHAR(4), J1NUM SMALLINT );
INSERT INTO SJ1_SUM
SELECT SNO,SUM(QTY) FROM SPJ WHERE JNO=’J1’ GROUP BY SNO; 6、将S2供应商供应给J1工程的蓝色零件的零件数目增加5%
Sql语句：
UPDATE SPJ SET QTY=QTY*1.05
WHERE SNO=’S2’ AND JNO=’J1’ AND PNO IN( SELECT PNO FROM P WHERE COLOR=’蓝’ );
7、从供应商关系中删除S2的记录。
Sql语句：
DELETE FROM SPJ WHERE SNO=’S2’; DELETE FROM S WHERE SNO=’S2’;
思考：当进行数据的插入、删除和修改的时候需要注意哪些方面？ 语法，是否违背完整性。
实验六 视图的创建、查询与更新
一、实验目的
1、学会使用CREATE、DROP等SQL语句创建和删除视图
2、学会使用SQL Server 2005的SQL Server Management Studio对视图进行查询和更新
二、实验内容
1、在数据库引擎查询文档中使用CREATE、DROP语句创建和删除视图 2、使用SELECT和UPDATE语句查询和更新视图
三、实验步骤
请为三建工程项目建立一个供应情况的视图，包括供应商代码、零件代码、供应数量。
SQL语句：
CREATE VIEW SJQTY AS
SELECT SNO,PNO,QTY FROM SPJ WHERE JNO IN( );
写出针对该视图完成下列查询所需的SQL语句：
⑴找出三建工程项目使用的各种零件代码及其数量 SELECT PNO,QTY FROM SJQTY; ⑵找出供应商S1的供应情况
SELECT * FROM SJQTY WHERE SNO=’S1’;
⑶删除三建视图中零件P1的记录，并观察基本表的变化情况。 DELETE FROM SJQTY WHERE PNO=’P1’;
⑷若向三建视图中插入一条新的记录，能不能插入？为什么？ 不能，违背实体完整性。
⑸将三建视图中S5供应商供应的P3零件的数量改为800，并观察基本表的变化情况。
UPDATE SJQTY SET QTY=800 WHERE SNO=’S5’ AND PNO=’P3’;
SELECT JNO FROM J WHERE JNAME=’三建’
2、将SPJ中每个供应商供应某个工程的零件数大于他所供应的工程的零件数的平均值的元组定义成一个视图
Sql语句：
CREATE VIEW SQTY AS SELECT * FROM SPJ X WHERE QTY>(
SELECT AVG(QTY) FROM SPJ WHERE SNO=X.SNO );
实验七 数据库安全性
一、 实验目的
SQL的数据控制通过DCL(数据控制语言) 实现。DCL通过对数据库用户的授权和收权命令来实现有关数据的存取控制，以保证数据库的安全性。
1、了解DCL语言的GRANT和REVOKE语句对数据库存取权限的控制 2、学会SQL Server 2005的数据库引擎查询文档中用DCL语言对数据库存取权限进行设定 二、实验内容
1、启动Microsoft SQL Server Manager管理器，用DBA身份连接数据库，新建一个数据库引擎查询文档，运用GRANT语句对数据库存取权限进行授权操作。 2、启动Microsoft SQL Server Manager管理器，用一般用户身份连接数据库，新建一个数据库引擎查询文档，验证该用户对数据的读写权限。 2、用REVOKE语句回收一般用户对数据库的操作权限。 三、实验步骤
描述实验步骤并给出相应的SQL语句：
1、以DBA身份连接数据库，创建用户USERA，并授予用户USERA对SPJ表的查询权限以及对属性QTY的更新权限。
先建立登录名USERA；CREATE USER USERA; GRANT SELECT,UPDATE(QTY) ON SPJ TO USERA;
3、以DBA身份连接数据库，创建用户USERB，并授予用户USERB对S表的增删改查的权限，并允许用户USERB将S表的查询权限授予其他用户。
先建立登录名USERB；CREATE USER USERB; GRANT UPDATE,DELETE,INSERT ON S TO USERB; GRANT SELECT ON S TO USERB WITH GRANT OPTION;
5、以DBA身份连接数据库，创建用户USERC，并授予用户USERC查询每个供应商供应的零件总数的权限。
先建立登录名USERC；CREATE USER USERC; CREATE VIEW SQTYSUM AS
SELECT SNO,SUM(QTY) NUM FROM SPJ GROUP BY SNO;
GRANT SELECT ON SQTYSUM TO USERC;
6、回收用户USERB对S表的查询权限，检验USERA是否还能查询S表的信息。
REVOKE SELECT ON S FROM USERB CASCADE;
SQL语言综合实验：图书馆借阅系统设计
一、实验目的
1、通过完成从用户需求分析、数据库设计到上机编程、调试和应用等全过程，进一步了解和掌握本书中所讲解的内容；
2、提高学生分析能力和抽象能力的目的。
二、实验内容
1、设计一个简单的图书借阅系统的数据库。
2、用SQL实现数据库的设计，并在 SQL Server 2005上调试通过。
三、实验要求
1 不能删除已借出的图书的基本信息。 2 限定读者的年龄只能在18～60之间。 3 借书 4 还书
5 查看某读者的当前借阅信息以及借阅历史记录。 6 能查看某读者还能借阅的图书数。
7 读者有多种类型，不同类型的读者能借阅的图书数量不同。 8 统计图书借阅次数。 9 查看指定类型的图书情况。 10 查询当前未借阅图书的读者信息。 11 查看从没被借阅过的图书
12 查看书名包含相关信息的图书的信息。 13 查看借阅相关图书的读者的姓名。（两种方法） 14 统计某读者当前借阅的图书的总数及总价。 15 对数据库进行合理的完整性控制。
四、实验步骤
1、 图书管理系统的E-R图：
2、 图书管理系统的关系模式： 读者（编号，姓名，年龄，类型）
图书（图书号，书名，价格，出版社，图书类型） 借阅（编号，图书号，借书时间，还书时间，）
各关系模式之间的相互关系： 读者（编号，姓名，年龄，类型） 主键：编号
图书（图书号，书名，价格，出版社，图书类型） 主键：图书号
借阅（编号，图书号，借书时间，还书时间，） 主键：(编号，图书号) 外键：编号，图书号
3、基本表定义的sql语句（包含约束）： create table type ( );
create table reader (
rdno char(10) not null primary key, rdname char(4), tpno char(2) not null, tpno char(2) primary key, tpname char(10), tpnum int
age int check( age<60 and age>18), );
create table book ( );
DROP TABLE book DROP TABLE jieyue create table booktype ( );
create table jieyue ( );
4、基本表查询的SELECT语句： --借书 insert
into jieyue(rdno,bkno,BorTime,Return)
rdno char(10), bkno char(10), BorTime datetime, RetTime datetime, Return bit,
primary key (rdno,bkno),
foreign key (rdno) references reader(rdno), foreign key (bkno) references book (bkno) bono char(3) primary key, boname char(10)
bkno char(10) not null primary key, bkname char(10), price int, Press char(20), bono char(5)
values ('1101','001','2015-6-14','0'); insert
into jieyue(rdno,bkno,BorTime,Return) values ('1101','003','2015-6-15','0'); insert
into jieyue(rdno,bkno,BorTime,Return) values ('1102','001','2015-6-13','0'); --还书 update jieyue
set RetTime='2015-6-15',Return='1' where rdno='1101' and bkno=001;
--5 查看某读者的当前借阅信息以及借阅历史记录。 select * from jieyue where rdno='1101';
--6 能查看某读者还能借阅的图书数。
select rdname name, type.tpnum-count(jieyue.rdno) num from reader,jieyue,type
where reader.rdno=jieyue.rdno and reader.tpno=type.tpno and reader.rdno='1101' and Return='0'
group by rdname,type.tpnum
--7 读者有多种类型，不同类型的读者能借阅的图书数量不同。 --8 统计图书借阅次数。 select * from jieyue where bkno='001'
group by rdno,bkno,BorTime,RetTime,Return --9 查看指定类型的图书情况。 select book.* from booktype,book
where booktype.bono=book.bono and boname='军事' --10 查询当前未借阅图书的读者信息。 select reader.* from reader
where reader.rdno not in ( )
--11 查看从没被借阅过的图书 select book.* from book where bkno not in ( )
--12 查看书名包含相关信息的图书的信息。 select book.* from book
where bkname like'战争%'
--13 查看借阅相关图书的读者的姓名。（两种方法） --(1)
select rdname,book.bkname from reader,jieyue,book
where reader.rdno=jieyue.rdno and book.bkno=jieyue.bkno and jieyue.bkno='001' --(2) select rdname from reader where rdno in(
select bkno from jieyue select rdno from jieyue
select rdno from jieyue where bkno in ( select bkno from book
where bkname='孙子校释' )
)
--14 统计某读者当前借阅的图书的总数及总价。
select reader.rdname,count(jieyue.bkno) bsum,sum(book.price) psum from reader,jieyue,book where
jieyue.rdno='1101'
and
jieyue.bkno=book.bkno
reader.rdno=jieyue.rdno and jieyue.Return='0'
group by reader.rdname
and