实验一 SQL语言
一、实验目的
(1)通过上机实践，熟悉Oracle的SQL * Plus环境及使用方法
(2)掌握SQL语言，能熟练运用SQL语言进行数据定义和数据操纵(3)加深对关系数据模型的数据结构和约束的理解
二、实验环境
硬件：微机
软件：ORACLE 8i或 9i
三、实验内容及要求
1.了解并掌握SQL * Plus环境的使用
2.用SQL的DDL语句图书管理系统创建基表
3.为基表“读者”补充定义：职称只能取初级、中级、高级之一4.用SQL的DML语句向上述基表中增加、修改和删除数据5.用SQL的QL语句完成指定查询6.用SQL的DDL语句创建视图、索引
7.为自己创建一张结构与scott用户的员工表emp相同的员工表。
四、实验过程
1、用SQL的DDL语句图书管理系统创建基表
图书（图书编号、分类号、书名、作者、出版单位、单价）
CREATE TABLE 图书(图书编号 VARCHAR(4) PRIMARY KEY,分类号 VARCHAR(5),书名
VARCHAR(18),作者 VARCHAR(8),出版单位 VARCHAR(10),单价 DEC(5,2));
读者（借书证号、姓名、单位、职称）
CREATE TABLE 读者(借书证号 VARCHAR(6) PRIMARY KEY,姓名 VARCHAR(10),单位 VARCHAR(10),
职称 VARCHAR(10));
借阅（借书证号、图书编号、借阅日期、备注）
CREATE TABLE 借阅(借书证号 VARCHAR(6),图书编号 VARCHAR(4), 借阅日期 DATE,备注
VARCHAR(30),PRIMARY KEY(借书证号,图书编号),FOREIGN KEY(借书证号)REFERENCES 读者(借书证号),FOREIGN KEY(图书编号) REFERENCES 图书(图书编号));
2、为基表“读者”补充定义：职称只能取初级、中级、高级之一ALTER TABLE 读者 ADD CHECK(职称 IN(‘初级’,’中级’,’高级’));
3、向三个基表中插入指定数据。图书表中数据：
0001 TP31计算机基础 WANG高等教育 17.000002 TP32数据库原理 16.50
0003 TN31并行计算机 YANG 清华大学 12.800004 TP33 高等数学 WANG 高等教育 25.000005 TN32 大学英语 ZHAO 高等教育 22.500006 TN33 数据库系统 LIU 人民邮电 24.00
INSERT INTO 图书 VALUES(0001,’TP31’,’计算机基础’,’WANG’,’高等教育’,17.00);
INSERT INTO 图书 VALUES(0002,’TP32’,’数据库原理’,NULL,NULL,16.50);INSERT INTO 图书 VALUES(0003,’TN31’,’并行计算机’,’YANG’,’清华大学’,12.80);
INSERT INTO 图书 VALUES(0004,’TP33’,’ 高等数学’,’ WANG’,’ 高等教育’,25.00);
INSERT INTO 图书 VALUES(0005,’TN32’,’ 大学英语’,’ ZHAO’,’ 高等教育’,22.50);
INSERT INTO 图书 VALUES(0006,’TN33’,’ 数据库系统’,’ LIU’ , ’ 人民邮电’,24.00);
读者表中数据：
T201 LIXIN 计算机系 中级S981 WANG 通信系 高级Z003 CHEN 工厂 初级T205 ZHAO 英语系 中级
INSERT INTO 读者 VALUES (‘T201’,’LIXIN’,’计算机系’,’中级’);INSERT INTO 读者 VALUES (‘S981’,’WANG’,’通信系’,’高级’);INSERT INTO 读者 VALUES (‘Z003’,’CHEN’,’工厂’,’初级’);INSERT INTO 读者 VALUES (‘T205’,’ ZHAO’,’ 英语系’,’中级’);
借阅表中数据：
S981 0002 2001-2-20Z003 0001 2001-3-3T201 0001 2001-3-10T201 0002 2016-4-11
T201 0003 2016-4-12T201 0004 2016-4-13T201 0005 2016-4-14
INSERT INTO 借阅 VALUES (‘S981’,0002,to_date(‘2001-2-20’,’yyyy-mm-dd’),null);
INSERT INTO 借阅 VALUES (‘Z003’,0001,to_date(‘2001-3-3’,’yyyy-mm-dd’),null);
INSERT INTO 借阅 VALUES (‘T201’,0001,to_date(‘2001-3-10’,’yyyy-mm-dd’),null);
INSERT INTO 借阅 VALUES (‘T201’,0002,to_date(‘2016-4-11’,’yyyy-mm-dd’),null);
INSERT INTO 借阅 VALUES (‘T201’,0003,to_date(‘2016-4-12’,’yyyy-mm-dd’),null);
INSERT INTO 借阅 VALUES (‘T201’,0004,to_date(‘2016-4-13’,’yyyy-mm-dd’),null);
INSERT INTO 借阅 VALUES (‘T201’,0005,to_date(‘2016-4-14’,’yyyy-mm-dd’),null);
4、数据的修改和删除
①为编号为 0002的图书填上作者和出版单位。
UPDATE 图书 SET 作者=’LIU’,出版单位=’电子工业’ WHERE 图书编号=0002;
②将所有图书单价上调 5％(即：原值×1.05)。UPDATE 图书 SET 单价=单价*1.05;
③将书名包含‘计算机’的书的分类号改为‘TP38’。
UPDATE 图书 SET 分类号=’TP38’ WHERE 书名 LIKE ‘%计算机%’;
④删除借书证号以 S开头的所有读者信息和借阅信息。DELETE FROM 借阅 WHERE 借书证号 LIKE ‘S%’;
5、用SQL的QL语句完成以下查询
(1)列出图书馆中所有藏书的书名及出版单位。SELECT 书名,出版单位FROM 图书;
(2) 查询工厂所有借阅了图书的读者姓名和职称。SELECT 姓名,职称FROM 读者,借阅
WHERE 读者.借书证号=借阅.借书证号 AND 单位=’工厂’;
(3) 查询藏书中比高等教育出版社所有图书单价更高的书籍。SELECT *FROM 图书
WHERE 单价>ALL(SELECT 单价 FROM 图书
WHERE 出版单位=’高等教育’);
(4) 查询各出版社图书的最高价、最低价和平均价格。SELECT MAX(单价),MIN(单价),AVG(单价)FROM 图书
GROUP BY 出版单位;
(5) 列出当前至少借阅了5本图书的读者及所在单位。SELECT 姓名,单位FROM 读者
WHERE 借书证号 IN (SELECT 借书证号 FROM 借阅
GROUP BY 借书证号 HAVING COUNT(*)>4);
6、用SQL的DDL语句创建视图、索引
(1)建立各单位当前借阅图书情况的简单统计视图，视图中包括单位名称，借书人数和借阅人次。
CREATE VIEW V1(单位,借书人数,借阅人次)AS SELECT 单位,COUNT(*),COUNT(借书证号)FROM 读者
WHERE 借书证号 IN (SELECT 借书证号 FROM 借阅) GROUP BY 单位;
(2) 对该视图进行查询。
SELECT 单位,借书人数,借阅人次FROM V1;
(3) 按出版社为图书表建立一个降序索引。CREATE INDEX 索引 ON 图书(出版单位 DESC);
7.为自己创建一张结构与scott用户的员工表 emp相同的员工表。connect scott/tiger
grant all privileges on emp to wang;connect wang/root
create table 员工表 as select * from scott.emp;
五、实验小结
