数据库实验报告-实验一：SQL语言

2022-03-30 18:56:07

实验1-1数据库及数据表的创建与删除

一、实验目的
掌握利用Oracle Database Configuration Assistant工具来创建和删除Oracle数据库，掌握Oracle中的用Create命令定义表的方法，以及表的完整性定义，并掌握Oracle中的用Alter命令 和Drop命令对表的修改和删除。
二、实验原理
基于数据库系统概论，熟悉实验环境，熟悉基本表等本次实验的基本概念，了解创建数据表及其相关操作的语法。根据要求，编写相应的SQL代码，并运行、记录和分析结果，测试所编写代码是否满足步骤要求，完成实验。
三、使用仪器、材料
Oracle 11g，windows10；
四、实验内容

  1. 创建以下数据表（Student（主码为SNO）、Course（主码为CNO）、SC（主码为（SNO、CNO）），其中SNO引用Student的SNO属性，CNO引用Course的CNO属性）
  2. 向三个表格中插入3条数据，数据内容自编。
  3. 修改Student表格，用SQL语句为Student表格添加一个“入学时间”属性，属性名为Senrollment。
  4. 限定Ssex的值只能为“男”或者“女”。
  5. 修改Course表格，用SQL语句为Course表格添加一个“说明”属性，属性名为“Cdesc”，类型为varchar2，长度为200。
  6. 更改Course表格的Cdesc属性，使其长度变为500。
  7. 删除刚建立的属性Cdesc。
  8. 修改Course表的CPNO，使其为外码，引用Course表的CNO属性。
    数据库实验报告-实验一：SQL语言

五、 实验过程原始记录（实验过程、数据、图表、计算等）
1、

CREATE TABLE STUDENT 
(
  SNO VARCHAR2(17) PRIMARY KEY ,
  SNAME VARCHAR2(10) NOT NULL ,
  SAGE INT ,
  SSEX VARCHAR2(2) ,
  SDEPT VARCHAR2(20)
);
COMMENT ON COLUMN STUDENT.SNO IS '学生编号';
COMMENT ON COLUMN STUDENT.SNAME IS '姓名';
COMMENT ON COLUMN STUDENT.SAGE IS '年龄';
COMMENT ON COLUMN STUDENT.SSEX IS '性别';
COMMENT ON COLUMN STUDENT.SDEPT IS '所在系';

CREATE TABLE COURSE 
(
  CNO VARCHAR2(5) NOT NULL ,
  CNAME VARCHAR2(20) NOT NULL ,
  CPNO VARCHAR2(5) ,
  CCREDIT INTEGER ,
  CONSTRAINT COURSE_PK PRIMARY KEY 
  (
    CNO 
  )
  ENABLE 
);
COMMENT ON COLUMN COURSE.CNO IS '课程编号';
COMMENT ON COLUMN COURSE.CNAME IS '课程名';
COMMENT ON COLUMN COURSE.CPNO IS '先修课程';
COMMENT ON COLUMN COURSE.CCREDIT IS '学分';

CREATE TABLE SC 
(
  SNO VARCHAR2(17) NOT NULL ,
  CNO VARCHAR2(5) NOT NULL ,
  GRADE NUMERIC(5,2) ,
  CONSTRAINT SC_PK PRIMARY KEY 
  (
    SNO ,
    CNO 
  )
  ENABLE 
);
COMMENT ON COLUMN SC.SNO IS '学生编号';
COMMENT ON COLUMN SC.CNO IS '课程编号';
COMMENT ON COLUMN SC.GRADE IS '成绩';
2、
INSERT INTO STUDENT (SNO, SNAME, SAGE, SSEX, SDEPT) VALUES ('1906100067', '小明', '19', '男', '计算机科学与技术');
INSERT INTO STUDENT (SNO, SNAME, SAGE, SSEX, SDEPT) VALUES ('1906100068', '小黑', '20', '女', '软件工程');
INSERT INTO STUDENT (SNO, SNAME, SAGE, SSEX, SDEPT) VALUES ('1906100069', '小白', '21', '男', '网络工程');

INSERT INTO COURSE (CNO, CNAME, CPNO, CCREDIT) VALUES ('3', '数据结构', '5', '3');
INSERT INTO COURSE (CNO, CNAME, CPNO, CCREDIT) VALUES ('7', '数据库', '6', '4');
INSERT INTO COURSE (CNO, CNAME, CPNO, CCREDIT) VALUES ('5', '操作系统', '2', '4');

INSERT INTO SC (SNO, CNO, GRADE) VALUES ('1906100067', '3', '91');
INSERT INTO SC (SNO, CNO, GRADE) VALUES ('1906100068', '7', '85');
INSERT INTO SC (SNO, CNO, GRADE) VALUES ('1906100069', '5', '92');

3、ALTER TABLE Student ADD Senrollment DATE;
4、ALTER TABLE STUDENT add check(SSEX IN ('男','女')) ENABLE;
5、ALTER TABLE COURSE ADD Cdesc varchar2(200);
6、ALTER TABLE COURSE modify (Cdesc varchar2(500));
7、ALTER TABLE COURSE drop column Cdesc;
8、ALTER TABLE COURSE ADD constraint CONO foreign key("CPNO") references "COURSE"("CNO");

六、实验结果及分析
1.
数据库实验报告-实验一：SQL语言
数据库实验报告-实验一：SQL语言
数据库实验报告-实验一：SQL语言
2、
数据库实验报告-实验一：SQL语言
数据库实验报告-实验一：SQL语言
数据库实验报告-实验一：SQL语言
3、
数据库实验报告-实验一：SQL语言
4、
数据库实验报告-实验一：SQL语言
5、
数据库实验报告-实验一：SQL语言
6、
数据库实验报告-实验一：SQL语言
7、
数据库实验报告-实验一：SQL语言
8、
数据库实验报告-实验一：SQL语言
七、实验心得体会
1、首次使用oracle11g进行数据库实验感觉还是有一点生疏，但按照提示步骤勉强能够完成实验。
2、通过本次实验，巩固了课堂上学习的知识，把课堂的理论知识用在了实际操作上。
3、从实验中总体上增强了对SQL语言的运用和对oracle11g的可视化操作能力。

实验1-2 SQL语言

一、实验目的
熟悉并掌握创建表，插入记录，查询记录，删除记录，修改记录。
创建索引，删除索引。
创建视图，使用视图，删除视图。
二、实验原理
基于数据库系统概论，熟悉实验环境，熟悉基本表等本次实验的基本概念，了解创建数据表及其相关操作的语法。根据要求，编写相应的SQL代码，并运行、记录和分析结果，测试所编写代码是否满足步骤要求，完成实验。
三、使用仪器、材料
Oracle 11g，windows10；
四、实验内容及步骤
现有一个单位内部的小型图书借阅系统，假设每本图书的数量无限制，并且可以借给任何单位成员，每个单位成员可以借多本书，单位成员与图书的关系是多对多的关系。假设系统中仅有三个关系模式。
数据表结构
数据库实验报告-实验一：SQL语言
1、 创建新的用户并授权：
2、 以用户CC的身份建立连接，并在此连接下执行后面的操作；
3、 拷贝代码运行，删去旧的同名数据表：
4、 建立表格Reader
5、 拷贝代码运行，向Reader表格中插入十条数据
6、 查询记录：在Reader表中查询直接上司是“李四”的员工的名字
7、 修改记录：在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”
8、 删除记录：在Reader表中把直接上司未赋值（NULL）是记录删去
9、 删去数据表：把整个Reader表删去
10、重新执行第一、二步，即建立数据表、插入数据。
11、创建表格Reader2，比较Reader2和Reader中的记录和结构是否相同
12、分别执行下面的每行语句，查看语句是否执行成功，分析为什么？
update reader set RNO=‘R001’ where Rname=‘张三’;
update reader2 set RNO=‘R001’ where Rname=‘张三’;
insert into reader2(RNO,Rname,Rsex,Rage,Rboss, Raddress) values(null,‘lisi’,null,null,null,null);
13、删除Reader2表格；
14、拷贝代码运行，建立表格Book
15、拷贝代码运行，向Book表格中插入5条数据
16、拷贝代码运行，建立表格RB
17、拷贝代码运行，向RB表格中插入13条数据；

1、 执行insert into RB(RNO,BNO) values(‘R010’,‘B005’);
2、 写出删除Reader表格中编号为’R010’的员工，如果执行错误，分析错误原因。
3、 想办法删除Reader中的’R010’员工；
方法一：先把RB中所有’R010’的借书记录都删掉
方法二：修改数据表READER的结构，允许级联删除（注：ORACLE不支持级联更新）。
4、 为Reader表添加一个属性列“出生年份”，名为Rbirthday，整数；
5、 对于Reader表格，员工编号可以确定年龄，年龄又可以确定出生年份，因此存在传递函数依赖关系，删除Rbirthday列，使关系模式符合第三范式要求；
6、 修改Reader表格的Raddress属性，使其长度为50，数据类型不变；
7、 修改book表的Bprice属性，使其值得范围在10到100之间；
8、 修改Reader表的Rage属性，使其值得范围为16到60之间。
9、 试试是否可以删除Reader表，使用CASCADE是否可以删除？
10、删除Reader、Book和RB表。

索引的建立与删除
1、重新执行前面的代码创建三个数据表并插入数据；
2、为Reader表格的Rname建立UNIQUE索引
如何修改表格数据，再建索引？
3、删除索引。

视图
1、 如果上面的运行是在CC的连接中，则需要回到在sysdba 的连接中，执行：
grant resource, connect, DBA to cc;
否则系统显示没有创建视图的权限。执行完之后再回到CC的连接：
在ORACLE SQL Developer 的左上方：
2、 建立在416办公室工作的视图V416，视图包括员工的编号、姓名、年龄等信息
3、 从V416中查询年龄大于30的员工信息
4、 向视图V416中插入一条新的员工记录，然后从V416中查找该条记录，测试是否可以找到；
插入成功了吗？为什么通过视图插入的纪录在视图中看不见？
5、 建立在417办公室工作的视图V417，视图包括员工的编号、姓名、性别、年龄等信息，视图定义带with check option选项；
6、 向视图V417中插入一条新的员工记录，然后从V417中查找该条记录，测试是否可以找到；
7、 通过视图删除刚插入的员工记录，在基本表Reader中查看是否已经删除成功？
8、 在视图V417上建立所有女员工信息的视图FV417，查询视图结果
9、 删除视图V417中没有借阅图书的员工信息
10、建立视图GV，数据包括每本图书的编号及其借阅数量。思考是否可以向GV中插入数据，为什么？
11、删除视图V417

五、实验过程原始记录(实验过程、数据、图表、计算等)
1、创建新用户并授权，再连接到新用户
create user cc identified by ccpassword ;
grant resource, connect, DBA to cc;

2、删去旧的同名数据表
Declare
tmp integer default 0;
Begin
select count() into tmp from user_tables where table_name=‘RB’;
if(tmp>0) then
execute immediate ‘drop table RB’;
end if;
select count() into tmp from user_tables where table_name=‘READER’;
if(tmp>0) then
execute immediate ‘drop table READER’;
end if;
select count(*) into tmp from user_tables where table_name=‘BOOK’;
if(tmp>0) then
execute immediate ‘drop table BOOK’;
end if;
end;
问：为何要先删去RB？能不能先删去READER？
因为RB引用了Reader的RNO作为外码，所以，要先删去RB，才能删去Reader。

3、先创建Reader表
CREATE TABLE Reader
(
RNO VARCHAR2(4) PRIMARY KEY ,
Rname VARCHAR2(10) NOT NULL ,
Rsex VARCHAR2(2),
Rage INTEGER,
Rboss VARCHAR2(10),
Raddress VARCHAR2(30)
);
COMMENT ON COLUMN Reader.RNO IS ‘员工编号’;
COMMENT ON COLUMN Reader.Rname IS ‘员工姓名’;
COMMENT ON COLUMN Reader.Rsex IS ‘性别’;
COMMENT ON COLUMN Reader.Rage IS ‘年龄’;
COMMENT ON COLUMN Reader.Rboss IS ‘直接上司’;
COMMENT ON COLUMN Reader.Raddress IS ‘办公地点’;

4、向表中插入数据
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R001’,‘小明’,20,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R002’,‘张三’,35,‘女’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R003’,‘李四’,30,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R004’,‘王五’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R005’,‘马六’,40,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R006’,‘刘三’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R007’,‘王四’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R008’,‘李小龙’,20,‘男’,‘李四’,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R009’,‘王小倩’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R010’,‘王一小’,20,‘男’,‘李四’,‘417’);

5、查询记录：在Reader表中查询直接上司是“李四”的员工的名字
select Rname
from Reader
where (Rboss=‘李四’);

6、修改记录：在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”
UPDATE Reader
set Raddress=‘420’
where Rboss=‘李四’;

7、删除记录：在Reader表中把直接上司未赋值（NULL）是记录删去
DELETE
FROM Reader
WHERE Rboss is NULL;

8、删去数据表：把整个Reader表删去
DROP TABLE Reader;

9、重新执行第一、二步，即建立数据表、插入数据。
CREATE TABLE Reader
(
RNO VARCHAR2(4) PRIMARY KEY ,
Rname VARCHAR2(10) NOT NULL ,
Rsex VARCHAR2(2),
Rage INTEGER,
Rboss VARCHAR2(10),
Raddress VARCHAR2(30)
);
COMMENT ON COLUMN Reader.RNO IS ‘员工编号’;
COMMENT ON COLUMN Reader.Rname IS ‘员工姓名’;
COMMENT ON COLUMN Reader.Rsex IS ‘性别’;
COMMENT ON COLUMN Reader.Rage IS ‘年龄’;
COMMENT ON COLUMN Reader.Rboss IS ‘直接上司’;
COMMENT ON COLUMN Reader.Raddress IS ‘办公地点’;

INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R001’,‘小明’,20,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R002’,‘张三’,35,‘女’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R003’,‘李四’,30,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R004’,‘王五’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R005’,‘马六’,40,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R006’,‘刘三’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R007’,‘王四’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R008’,‘李小龙’,20,‘男’,‘李四’,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R009’,‘王小倩’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R010’,‘王一小’,20,‘男’,‘李四’,‘417’);

10、创建表格Reader2，比较Reader2和Reader中的记录和结构是否相同
create table reader2 as select * from reader;

查看这两个数据表的结构、内容是否相同？分别执行：
desc reader;
desc reader2;
我的设备在sqldeveloper上似乎无法执行desc reader和desc reader2，故而到命令行中执行。

也可以在可视化界面查看这两个表所附带的约束
两个表的约束条件不同

11、分别执行下面的每行语句，查看语句是否执行成功，分析为什么？
UPADTE Reader set RNO=‘R001’ where Rname=‘张三’;
执行失败。表reader中的RNO是primary key,更新后的RNO与现存的RNO重复，违反唯一约束条件

UPADTE Reader2 set RNO=‘R001’ where Rname=‘张三’;

INSERT INTO Reader2(RNO,Rname,Rsex,Rage,Rboss,Raddress) values(null,‘lisi’,null,null,null,null);

12、删除Reader2表格
DROP TABLE Reader2;

13、建立表格Book
CREATE TABLE Book
(
BNO VARCHAR2(4) PRIMARY KEY ,
Bname VARCHAR2(50) NOT NULL ,
Bauthor VARCHAR2(50),
Bpress VARCHAR2(50),
Bprice numeric(6,2)
);
COMMENT ON COLUMN Book.BNO IS ‘书本编号’;
COMMENT ON COLUMN Book.Bname IS ‘书名’;
COMMENT ON COLUMN Book.Bauthor IS ‘作者’;
COMMENT ON COLUMN Book.Bpress IS ‘出版社’;
COMMENT ON COLUMN Book.Bprice IS ‘价格’;

15、向Book表格中插入5条数据
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B001’,‘严蔚敏’,‘数据结构’,‘清华大学出版社’,null);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B002’,‘唐发根’,‘数据结构’,‘北航出版社’,24);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B003’,‘王珊’,‘数据库原理’,‘高等教育出版社’,40);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B004’,‘张飞’,‘数据库原理’,‘清华大学出版社’,30);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B005’,‘王珊’,‘数据库原理’,‘清华大学出版社’,null);

16、建立表格RB
create table RB
(
RNO varchar2(4),
BNO varchar2(4),
RBdate date default sysdate,
primary key(RNO,BNO),
foreign key (RNO) references Reader(RNO),
foreign key (BNO) references Book(BNO)
);
COMMENT ON COLUMN RB.RNO IS ‘员工编号’;
COMMENT ON COLUMN RB.BNO IS ‘书本编号’;
COMMENT ON COLUMN RB.RBdate IS ‘借阅日期’;

17、向RB表格中插入13条数据；
INSERT INTO RB (RNO,BNO) values (‘R001’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R001’,‘B002’);
INSERT INTO RB (RNO,BNO) values (‘R001’,‘B004’);
INSERT INTO RB (RNO,BNO) values (‘R002’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R003’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R004’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R004’,‘B002’);
INSERT INTO RB (RNO,BNO) values (‘R005’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B003’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B005’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B002’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B004’);

1、 创建新的用户并授权：
2、 以用户CC的身份建立连接，并在此连接下执行后面的操作；
3、 拷贝代码运行，删去旧的同名数据表：
4、 建立表格Reader
5、 拷贝代码运行，向Reader表格中插入十条数据
6、 查询记录：在Reader表中查询直接上司是“李四”的员工的名字
7、 修改记录：在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”
8、 删除记录：在Reader表中把直接上司未赋值（NULL）是记录删去
9、 删去数据表：把整个Reader表删去
10、重新执行第一、二步，即建立数据表、插入数据。
11、创建表格Reader2，比较Reader2和Reader中的记录和结构是否相同
12、分别执行下面的每行语句，查看语句是否执行成功，分析为什么？
update reader set RNO=‘R001’ where Rname=‘张三’;
update reader2 set RNO=‘R001’ where Rname=‘张三’;
insert into reader2(RNO,Rname,Rsex,Rage,Rboss, Raddress) values(null,‘lisi’,null,null,null,null);
13、删除Reader2表格；
14、拷贝代码运行，建立表格Book
15、拷贝代码运行，向Book表格中插入5条数据
16、拷贝代码运行，建立表格RB
17、拷贝代码运行，向RB表格中插入13条数据；

1、 执行insert into RB(RNO,BNO) values(‘R010’,‘B005’);
2、 写出删除Reader表格中编号为’R010’的员工，如果执行错误，分析错误原因。
3、 想办法删除Reader中的’R010’员工；
方法一：先把RB中所有’R010’的借书记录都删掉
方法二：修改数据表READER的结构，允许级联删除（注：ORACLE不支持级联更新）。
4、 为Reader表添加一个属性列“出生年份”，名为Rbirthday，整数；
5、 对于Reader表格，员工编号可以确定年龄，年龄又可以确定出生年份，因此存在传递函数依赖关系，删除Rbirthday列，使关系模式符合第三范式要求；
6、 修改Reader表格的Raddress属性，使其长度为50，数据类型不变；
7、 修改book表的Bprice属性，使其值得范围在10到100之间；
8、 修改Reader表的Rage属性，使其值得范围为16到60之间。
9、 试试是否可以删除Reader表，使用CASCADE是否可以删除？
10、删除Reader、Book和RB表。

索引的建立与删除
1、重新执行前面的代码创建三个数据表并插入数据；
2、为Reader表格的Rname建立UNIQUE索引
如何修改表格数据，再建索引？
3、删除索引。

视图
1、 如果上面的运行是在CC的连接中，则需要回到在sysdba 的连接中，执行：
grant resource, connect, DBA to cc;
否则系统显示没有创建视图的权限。执行完之后再回到CC的连接：
在ORACLE SQL Developer 的左上方：
2、 建立在416办公室工作的视图V416，视图包括员工的编号、姓名、年龄等信息
3、 从V416中查询年龄大于30的员工信息
4、 向视图V416中插入一条新的员工记录，然后从V416中查找该条记录，测试是否可以找到；
插入成功了吗？为什么通过视图插入的纪录在视图中看不见？
5、 建立在417办公室工作的视图V417，视图包括员工的编号、姓名、性别、年龄等信息，视图定义带with check option选项；
6、 向视图V417中插入一条新的员工记录，然后从V417中查找该条记录，测试是否可以找到；
7、 通过视图删除刚插入的员工记录，在基本表Reader中查看是否已经删除成功？
8、 在视图V417上建立所有女员工信息的视图FV417，查询视图结果
9、 删除视图V417中没有借阅图书的员工信息
10、建立视图GV，数据包括每本图书的编号及其借阅数量。思考是否可以向GV中插入数据，为什么？
11、删除视图V417

五、实验过程原始记录(实验过程、数据、图表、计算等)
1、创建新用户并授权，再连接到新用户
create user cc identified by ccpassword ;
grant resource, connect, DBA to cc;

2、删去旧的同名数据表
Declare
tmp integer default 0;
Begin
select count() into tmp from user_tables where table_name=‘RB’;
if(tmp>0) then
execute immediate ‘drop table RB’;
end if;
select count() into tmp from user_tables where table_name=‘READER’;
if(tmp>0) then
execute immediate ‘drop table READER’;
end if;
select count(*) into tmp from user_tables where table_name=‘BOOK’;
if(tmp>0) then
execute immediate ‘drop table BOOK’;
end if;
end;
问：为何要先删去RB？能不能先删去READER？
因为RB引用了Reader的RNO作为外码，所以，要先删去RB，才能删去Reader。

3、先创建Reader表
CREATE TABLE Reader
(
RNO VARCHAR2(4) PRIMARY KEY ,
Rname VARCHAR2(10) NOT NULL ,
Rsex VARCHAR2(2),
Rage INTEGER,
Rboss VARCHAR2(10),
Raddress VARCHAR2(30)
);
COMMENT ON COLUMN Reader.RNO IS ‘员工编号’;
COMMENT ON COLUMN Reader.Rname IS ‘员工姓名’;
COMMENT ON COLUMN Reader.Rsex IS ‘性别’;
COMMENT ON COLUMN Reader.Rage IS ‘年龄’;
COMMENT ON COLUMN Reader.Rboss IS ‘直接上司’;
COMMENT ON COLUMN Reader.Raddress IS ‘办公地点’;

4、向表中插入数据
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R001’,‘小明’,20,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R002’,‘张三’,35,‘女’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R003’,‘李四’,30,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R004’,‘王五’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R005’,‘马六’,40,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R006’,‘刘三’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R007’,‘王四’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R008’,‘李小龙’,20,‘男’,‘李四’,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R009’,‘王小倩’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R010’,‘王一小’,20,‘男’,‘李四’,‘417’);

5、查询记录：在Reader表中查询直接上司是“李四”的员工的名字
select Rname
from Reader
where (Rboss=‘李四’);

6、修改记录：在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”
UPDATE Reader
set Raddress=‘420’
where Rboss=‘李四’;

7、删除记录：在Reader表中把直接上司未赋值（NULL）是记录删去
DELETE
FROM Reader
WHERE Rboss is NULL;

8、删去数据表：把整个Reader表删去
DROP TABLE Reader;

9、重新执行第一、二步，即建立数据表、插入数据。
CREATE TABLE Reader
(
RNO VARCHAR2(4) PRIMARY KEY ,
Rname VARCHAR2(10) NOT NULL ,
Rsex VARCHAR2(2),
Rage INTEGER,
Rboss VARCHAR2(10),
Raddress VARCHAR2(30)
);
COMMENT ON COLUMN Reader.RNO IS ‘员工编号’;
COMMENT ON COLUMN Reader.Rname IS ‘员工姓名’;
COMMENT ON COLUMN Reader.Rsex IS ‘性别’;
COMMENT ON COLUMN Reader.Rage IS ‘年龄’;
COMMENT ON COLUMN Reader.Rboss IS ‘直接上司’;
COMMENT ON COLUMN Reader.Raddress IS ‘办公地点’;

INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R001’,‘小明’,20,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R002’,‘张三’,35,‘女’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R003’,‘李四’,30,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R004’,‘王五’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R005’,‘马六’,40,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R006’,‘刘三’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R007’,‘王四’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R008’,‘李小龙’,20,‘男’,‘李四’,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R009’,‘王小倩’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R010’,‘王一小’,20,‘男’,‘李四’,‘417’);

10、创建表格Reader2，比较Reader2和Reader中的记录和结构是否相同
create table reader2 as select * from reader;

查看这两个数据表的结构、内容是否相同？分别执行：
desc reader;
desc reader2;
我的设备在sqldeveloper上似乎无法执行desc reader和desc reader2，故而到命令行中执行。

也可以在可视化界面查看这两个表所附带的约束
两个表的约束条件不同

11、分别执行下面的每行语句，查看语句是否执行成功，分析为什么？
UPADTE Reader set RNO=‘R001’ where Rname=‘张三’;
执行失败。表reader中的RNO是primary key,更新后的RNO与现存的RNO重复，违反唯一约束条件

UPADTE Reader2 set RNO=‘R001’ where Rname=‘张三’;

INSERT INTO Reader2(RNO,Rname,Rsex,Rage,Rboss,Raddress) values(null,‘lisi’,null,null,null,null);

12、删除Reader2表格
DROP TABLE Reader2;

13、建立表格Book
CREATE TABLE Book
(
BNO VARCHAR2(4) PRIMARY KEY ,
Bname VARCHAR2(50) NOT NULL ,
Bauthor VARCHAR2(50),
Bpress VARCHAR2(50),
Bprice numeric(6,2)
);
COMMENT ON COLUMN Book.BNO IS ‘书本编号’;
COMMENT ON COLUMN Book.Bname IS ‘书名’;
COMMENT ON COLUMN Book.Bauthor IS ‘作者’;
COMMENT ON COLUMN Book.Bpress IS ‘出版社’;
COMMENT ON COLUMN Book.Bprice IS ‘价格’;

15、向Book表格中插入5条数据
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B001’,‘严蔚敏’,‘数据结构’,‘清华大学出版社’,null);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B002’,‘唐发根’,‘数据结构’,‘北航出版社’,24);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B003’,‘王珊’,‘数据库原理’,‘高等教育出版社’,40);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B004’,‘张飞’,‘数据库原理’,‘清华大学出版社’,30);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B005’,‘王珊’,‘数据库原理’,‘清华大学出版社’,null);

16、建立表格RB
create table RB
(
RNO varchar2(4),
BNO varchar2(4),
RBdate date default sysdate,
primary key(RNO,BNO),
foreign key (RNO) references Reader(RNO),
foreign key (BNO) references Book(BNO)
);
COMMENT ON COLUMN RB.RNO IS ‘员工编号’;
COMMENT ON COLUMN RB.BNO IS ‘书本编号’;
COMMENT ON COLUMN RB.RBdate IS ‘借阅日期’;

17、向RB表格中插入13条数据；
INSERT INTO RB (RNO,BNO) values (‘R001’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R001’,‘B002’);
INSERT INTO RB (RNO,BNO) values (‘R001’,‘B004’);
INSERT INTO RB (RNO,BNO) values (‘R002’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R003’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R004’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R004’,‘B002’);
INSERT INTO RB (RNO,BNO) values (‘R005’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B003’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B005’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B002’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B004’);

以下为查询READER表中创建的约束类型，其中constraint_type为约束类型，该属性值的取值Type Code如下表所示。（可以根据上课所讲的实体完整性、参照完整性、用户自定义完整性理解以下约束类型；直接在SQL DEVELOPER中双击表格也可以查看在表上所建立的约束）
select table_name,constraint_name,constraint_type from user_constraints where table_name =‘READER’;

Type Code Type Description Acts On Level
C Check on a table Column
O Read Only on a view Object
P Primary Key Object
R Referential AKA Foreign Key Column
U Unique Key Column
V Check Option on a view Object

1、 执行insert into RB(RNO,BNO) values(‘R010’,‘B005’)

2、 写出删除Reader表格中编号为’R010’的员工，如果执行错误，分析错误原因。
DELETE from Reader where rno=‘R010’;

RB表中的RNO是外码，直接删除Reader表格中编号为’R010’的员工违反了参照完整性，不能直接删除，想删除Reader表中编号为’R010’的员工，要先把RB中所有’R010’的借书记录都删掉。

3、 想办法删除Reader中的’R010’员工
先删除R010的借书记录
再删除Reader表中编号为’R010’的员工
DELETE
FROM RB
WHERE RNO=‘R010’;
DELETE from Reader where rno=‘R010’;

4、 为Reader表添加一个属性列“出生年份”，名为Rbirthday，整数
ALTER TABLE Reader ADD Rbirthday INTEGER;
COMMENT ON COLUMN Reader.Rbirthday IS ‘出生年份’;

5、 对于Reader表格，员工编号可以确定年龄，年龄又可以确定出生年份，因此存在传递函数依赖关系，删除Rbirthday列，使关系模式符合第三范式要求
ALTER TABLE reader DROP COLUMN Rbirthday;

6、 修改Reader表格的Raddress属性，使其长度为50，数据类型不变
ALTER TABLE Reader modify Raddress varchar2(50);

7、 修改book表的Bprice属性，使其值得范围在10到100之间；
ALTER TABLE Book add constraint bp check (Bprice BETWEEN 10 and 100);

8、 修改Reader表的Rage属性，使其值得范围为16到60之间。
ALTER TABLE Reader add constraint rr check (Rage BETWEEN 16 and 60);

9、 试试是否可以删除Reader表，使用CASCADE是否可以删除？
DROP TABLE Reader CASCADE CONSTRAINTS ;

10、删除Reader、Book和RB表。
DROP TABLE Book CASCADE CONSTRAINTS ;
DROP TABLE RB CASCADE CONSTRAINTS ;

索引的建立与删除
1、 重新执行前面的代码创建三个数据表并插入数据；
2、 为Reader表格的Rname建立UNIQUE索引
CREATE UNIQUE INDEX ReaderRname ON Reader(Rname);
报告出错：“cannot CREATE UNIQUE INDEX; duplicate keys found”
如何修改表格数据，再建索引？
因为有两个同样的Rname,而题目是要建立唯一索引，所以只需要把重复的数据修改一下就行了。

3、 删除索引。
DROP INDEX ReaderRname;

视图
1、 如果上面的运行是在CC的连接中，则需要回到在sysdba 的连接中，执行：
grant resource, connect, DBA to cc;
否则系统显示没有创建视图的权限。执行完之后再回到CC的连接
2、 建立在416办公室工作的视图V416，视图包括员工的编号、姓名、年龄等信息
CREATE VIEW V416 as select rno,rname,rage from reader where raddress=‘416’

确认该视图的存在：
select * from v416

3、 从V416中查询年龄大于30的员工信息
SELECT Rno,Rname FROM V416 WHERE Rage>30;

4、 向视图V416中插入一条新的员工记录，然后从V416中查找该条记录，测试是否可以找到；
INSERT INTO v416 VALUES(‘R999’,‘new reader’,99);
select * from reader
select * from v416

插入成功了吗？为什么通过视图插入的纪录在视图中看不见？
插入成功了，但刚插入数据条件不满足此视图的条件，插入员工记录的Raddress为null，而不是416。

5、 建立在417办公室工作的视图V417，视图包括员工的编号、姓名、性别、年龄等信息，视图定义带with check option选项；
CREATE VIEW V417 as select Rno,Rname,Rsex,Rage,Raddress FROM Reader WHERE Raddress=‘417’ with check option;
SELECT * FROM v417

6、 向视图V417中插入一条新的员工记录，然后从V417中查找该条记录，测试是否可以找到；
insert into v417( rno,rname,rage) values(‘R777’,‘417 reader’,66);
执行不成功。

insert into v417( rno,rname,rage,raddress) values(‘R777’,‘417 reader’,66,‘417’);
select * from v417;
执行成功。

7、 通过视图删除刚插入的员工记录，在基本表Reader中查看是否已经删除成功？
select * from v416;
delete from v416 where rname=‘王小倩’;
select * from v416;

8、 在视图V417上建立所有女员工信息的视图FV417，查询视图结果
CREATE VIEW FV417 as select * from V417 where RSEX=‘女’;
select * from FV417;

9、 删除视图V417中没有借阅图书的员工信息
SELECT * FROM V417;
Delete from v417 where v417.rno not in (SELECT rno from rb);
SELECT * FROM V417;

10、 建立视图GV，数据包括每本图书的编号及其借阅数量。思考是否可以向GV中插入数据，为什么？
SELECT BNO,COUNT() FROM RB GROUP BY BNO;
CREATE VIEW GV(BNO,COUNTS) AS SELECT BNO,COUNT() FROM RB GROUP BY BNO;
SELECT * FROM GV;
不可以向GV插入数据，因为GV的内容并非数据，而是数据和相关数据统计结果。

11、删除视图V417
drop view v417

六、实验心得体会
通过这次实验，我对Oracle的使用更加熟练了，对数据库的创建、查询、修改和删除也更加的熟悉，也掌握了对索引和视图的操作，从实验中增强了SQL语言的运用能力。

实验1-3 SQL查询数据

一、实验目的
熟悉并掌握创建表，插入记录，查询记录，删除记录，修改记录。
创建索引，删除索引。
创建视图，使用视图，删除视图。
二、实验原理
基于数据库系统概论，熟悉实验环境，熟悉基本表等本次实验的基本概念，了解创建数据表及其相关操作的语法。根据要求，编写相应的SQL代码，并运行、记录和分析结果，测试所编写代码是否满足步骤要求，完成实验。
三、使用仪器、材料
Oracle 11g，windows10；
四、实验内容
现有一个单位内部的小型图书借阅系统，假设每本图书的数量无限制，并且可以借给任何单位成员，每个单位成员可以借多本书，单位成员与图书的关系是多对多的关系。假设系统中仅有三个关系模式。
数据表结构

1、 创建新的用户并授权：
2、 以用户CC的身份建立连接，并在此连接下执行后面的操作；
3、 拷贝代码运行，删去旧的同名数据表：
4、 拷贝代码运行，建立表格及输入数据：

单表查询
1、 查询全体员工的姓名和出生年份；
2、 查询工作在416房间的员工的所有信息；
3、 查询年龄在30到50岁之间的员工姓名、年龄；
4、 查询借了书的员工的编号，排除相同的元素；
5、 查询名字中包含字“小”的员工姓名、办公地点；
6、 查询名字中第二个字查询所有不姓“李”的员工姓名、性别；
7、 为“小”的员工姓名、办公地点；
8、 查询Book表中价格不为空值的书名、出版社；
9、 查询清华大学出版社和高等教育出版社出版的所有书籍，按照出版社降序、书名升序排列；
10、查询员工的总人数
11、查询借了书的员工的人数；
12、查询“张三”所借图书的数量；
13、查询最贵的书籍的作者姓名；
14、查询Book表中书籍的平均价格，查询结果说明了什么；
15、查询book中包含的各个出版社及其出版书籍的数量；

复合查询
1、 查询每个员工及其借书情况，列出员工编号、姓名和借书日期
2、 查询每个员工及其借书所有字段（没有借书的员工也列出来）；（用左外连接）
3、 查询与“李小龙”工作在同一个办公室的员工信息（用两种方法：自身连接、子查询）；
4、 查询借阅了“数据库原理”的员工所有信息（两种方法：连接查询、子查询）
5、 查询小于或等于同一办公室中成员的平均年龄的员工编号、姓名、年龄。
6、 查询比所有数据库原理价格都低、并且不是清华大学出版社出版的书籍的信息；
7、 查询借阅了B001的员工的编号、姓名、办公室；
8、 查询没有借阅B001的员工的编号、姓名、办公室；
9、 查询借阅了所有书籍的员工的姓名；
10、查询至少借阅了编号为R004的员工借阅的全部书籍的员工姓名、性别、办公室
11、查询年龄大于30岁或者工作在416的员工信息（用集合查询完成）
12、查询年龄大于30岁并且工作不在416的员工信息（用集合查询完成）

五、实验过程原始记录
1、 创建新的用户并授权：
create user cc identified by ccpassword ;
grant resource, connect, DBA to cc;
2、 以用户CC的身份建立连接，并在此连接下执行后面的操作；
3、 拷贝代码运行，删去旧的同名数据表：
Declare
tmp integer default 0;
Begin
select count() into tmp from user_tables where table_name=‘RB’;
if(tmp>0) then
execute immediate ‘drop table RB’;
end if;
select count() into tmp from user_tables where table_name=‘READER’;
if(tmp>0) then
execute immediate ‘drop table READER’;
end if;
select count(*) into tmp from user_tables where table_name=‘BOOK’;
if(tmp>0) then
execute immediate ‘drop table BOOK’;
end if;
end;

问：为何要先删去RB？能不能先删去READER？
因为RB表中定义了外码RNO（关联Reader）和BNO（关联Book），如果先删去READER就会违反了参照完整性。

3、建立表格插入数据
CREATE TABLE Reader
(
RNO VARCHAR2(4) PRIMARY KEY ,
Rname VARCHAR2(10) NOT NULL ,
Rsex VARCHAR2(2),
Rage INTEGER,
Rboss VARCHAR2(10),
Raddress VARCHAR2(30)
);
COMMENT ON COLUMN Reader.RNO IS ‘员工编号’;
COMMENT ON COLUMN Reader.Rname IS ‘员工姓名’;
COMMENT ON COLUMN Reader.Rsex IS ‘性别’;
COMMENT ON COLUMN Reader.Rage IS ‘年龄’;
COMMENT ON COLUMN Reader.Rboss IS ‘直接上司’;
COMMENT ON COLUMN Reader.Raddress IS ‘办公地点’;

INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R001’,‘张三’,20,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R002’,‘张三’,35,‘女’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R003’,‘李四’,30,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R004’,‘王五’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R005’,‘马六’,40,‘男’,null,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R006’,‘刘三’,20,‘男’,null,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R007’,‘王四’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R008’,‘李小龙’,20,‘男’,‘李四’,‘417’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R009’,‘王小倩’,40,‘男’,‘李四’,‘416’);
INSERT INTO Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values(‘R010’,‘王一小’,20,‘男’,‘李四’,‘417’);

CREATE TABLE Book
(
BNO VARCHAR2(4) PRIMARY KEY ,
Bname VARCHAR2(50) NOT NULL ,
Bauthor VARCHAR2(50),
Bpress VARCHAR2(50),
Bprice numeric(6,2)
);
COMMENT ON COLUMN Book.BNO IS ‘书本编号’;
COMMENT ON COLUMN Book.Bname IS ‘书名’;
COMMENT ON COLUMN Book.Bauthor IS ‘作者’;
COMMENT ON COLUMN Book.Bpress IS ‘出版社’;
COMMENT ON COLUMN Book.Bprice IS ‘价格’;

INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B001’,‘严蔚敏’,‘数据结构’,‘清华大学出版社’,null);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B002’,‘唐发根’,‘数据结构’,‘北航出版社’,24);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B003’,‘王珊’,‘数据库原理’,‘高等教育出版社’,40);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B004’,‘张飞’,‘数据库原理’,‘清华大学出版社’,30);
INSERT INTO book (BNO,Bauthor,Bname, bpress, bprice) values(‘B005’,‘王珊’,‘数据库原理’,‘清华大学出版社’,null);

create table RB
(
RNO varchar2(4),
BNO varchar2(4),
RBdate date default sysdate,
primary key(RNO,BNO),
foreign key (RNO) references Reader(RNO),
foreign key (BNO) references Book(BNO)
);
COMMENT ON COLUMN RB.RNO IS ‘员工编号’;
COMMENT ON COLUMN RB.BNO IS ‘书本编号’;
COMMENT ON COLUMN RB.RBdate IS ‘借阅日期’;

INSERT INTO RB (RNO,BNO) values (‘R001’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R001’,‘B002’);
INSERT INTO RB (RNO,BNO) values (‘R001’,‘B004’);
INSERT INTO RB (RNO,BNO) values (‘R002’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R003’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R004’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R004’,‘B002’);
INSERT INTO RB (RNO,BNO) values (‘R005’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B001’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B003’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B005’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B002’);
INSERT INTO RB (RNO,BNO) values (‘R006’,‘B004’);

初始数据
reader表
RNO RNAME RSEX RAGE RBOSS RADDRESS
R001 张三 男 20 李四 416
R002 张三 女 35 417
R003 李四 男 30 416
R004 王五 男 20 417
R005 马六 男 40 416
R006 刘三 男 20 417
R007 王四 男 40 李四 416
R008 李小龙 男 20 李四 417
R009 王小倩 男 40 李四 416
R010 王一小 男 20 李四 417

book表中数据
BNO BNAME BAUTHOR BPRESS BPRICE
B002 数据结构 唐发根 北航出版社 24
B003 数据库原理 王珊 高等教育出版社 40
B004 数据库原理 张飞 清华大学出版社 30
B005 数据库原理 王珊 清华大学出版社
B001 数据结构 严蔚敏 清华大学出版社

RB表中数据
RNO BNO RBDATE（实验时间）
R001 B001 15-5月 21
R001 B002 15-5月 21
R001 B004 15-5月 21
R002 B001 15-5月 21
R003 B001 15-5月 21
R004 B001 15-5月 21
R004 B002 15-5月 21
R005 B001 15-5月 21
R006 B001 15-5月 21
R006 B003 15-5月 21
R006 B005 15-5月 21
R006 B002 15-5月 21
R006 B004 15-5月 21

单表查询
1、 查询全体员工的姓名和出生年份；
SELECT RNAME,‘Year of Birth:’,2021-Rage FROM Reader;

2、 查询工作在416房间的员工的所有信息；
SELECT*
From reader
WHERE raddress=‘416’;

3、 查询年龄在30到50岁之间的员工姓名、年龄；
SELECT Rname,Rage
From reader
WHERE Rage BETWEEN 30 AND 50;

4、 查询借了书的员工的编号，排除相同的元素；
SELECT DISTINCT rno
From RB;

5、 查询名字中包含字“小”的员工姓名、办公地点；
SELECT Rname,Raddress
From Reader
WHERE rname LIKE ‘%小%’;

6、 查询名字中第二个字为“小”的员工姓名、办公地点；
SELECT Rname,Raddress
From Reader
WHERE rname LIKE ‘_小%’;

7、 查询所有不姓“李”的员工姓名、性别；
SELECT Rname,Raddress
From Reader
WHERE rname NOT LIKE ‘李%’;

8、 查询Book表中价格不为空值的书名、出版社；

SELECT Bname,Bpress

From Book
WHERE bprice IS NOT NULL;

9、 查询清华大学出版社和高等教育出版社出版的所有书籍，按照出版社降序、书名升序排列；
SELECT* from Book;
上面的语句用来查看后面的语句结果对不对。

SELECT*
FROM book
WHERE bpress=‘清华大学出版社’ OR bpress=‘高等教育出版社’
ORDER BY bpress DESC,bname ASC;

10、查询员工的总人数
SELECT COUNT(*)
FROM Reader;

11、查询借了书的员工的人数；
SELECT COUNT(DISTINCT rno)
FROM RB;
或：
SELECT COUNT(*)
FROM READER
WHERE READER.RNO IN (SELECT RNO FROM rb);

12、查询“张三”所借图书的数量；
select COUNT(*)
from rb
where rno in (SELECT RNO FROM reader where rname=‘张三’)

13、查询最贵的书籍的作者姓名；
select bname,bauthor,bprice
from book
where bprice=(SELECT MAX(bprice)FROM BOOK);

14、查询Book表中书籍的平均价格，查询结果说明了什么；
select AVG(bprice)
from book;
价格为空的数据不加入计算

15、查询book中包含的各个出版社及其出版书籍的数量；
SELECT bpress,COUNT(bpress)
FROM book GROUP BY bpress;

复合查询
1、 查询每个员工及其借书情况，列出员工编号、姓名和借书日期
SELECT reader.RNO,Rname,RBdate
FROM reader,RB
WHERE reader.rno = rb.rno;

2、 查询每个员工及其借书所有字段（没有借书的员工也列出来）；（用左外连接）

SELECT reader.rno,rname,rsex,rb.bno,rbdate

FROM Reader LEFT OUTER JOIN RB ON (reader.rno=rb.rno);

3、 查询与“李小龙”工作在同一个办公室的员工信息（用两种方法：自身连接、子查询）；
自身连接：
select b.rname
from reader a, reader b
where a.raddress=b.raddress AND a.rname=‘李小龙’ AND b.rname!=‘李小龙’;
子查询：
SELECT Rname
FROM reader
WHERE raddress IN(SELECT raddress FROM reader WHERE rname=‘李小龙’);

4、 查询借阅了“数据库原理”的员工所有信息（两种方法：连接查询、子查询）
连接查询：
Select rbdate,reader.rno,rname,rsex,raddress,rage,rboss
from reader,rb,book
where book.bname='数据库原理’and book.bno=rb.bno and rb.rno=reader.rno;
子查询：
Select*
from reader
where RNO IN(SELECT RNO FROM rb WHERE BNO IN (SELECT BNO FROM book WHERE Bname=‘数据库原理’));

问：为什么最后的子句不能是bno=( )？
reader表中没有bno这个属性

5、 查询小于或等于同一办公室中成员的平均年龄的员工编号、姓名、年龄。
select RNO,RNAME,RAGE,RADDRESS from READER order by raddress;

select raddress,avg(rage) from reader group by raddress ;

上面的两句用来查看后面的语句结果对不对。
select a.rno,a.rname,a.rage,a.raddress
from reader a
where a.rage<=(SELECT avg(rage)from reader b where a.raddress=b.raddress);

6、 查询比所有数据库原理价格都低、并且不是清华大学出版社出版的书籍的信息；
select * from BOOK where BNAME=‘数据库原理’;

select min(BPRICE) from BOOK where BNAME=‘数据库原理’;

select * from BOOK;

上面的语句用来查看后面的语句结果对不对。
select *
from book
where bprice<(SELECT MIN(bprice) FROM book WHERE bname=‘数据库原理’) AND bpress<>‘清华大学出版社’;

7、 查询借阅了B001的员工的编号、姓名、办公室；
select READER.RNO,RNAME,RADDRESS from READER;

select * from rb where bno=‘B001’;

上面的语句用来查看后面的语句结果对不对。
select reader.rno,rname,raddress
from reader,rb where rb.rno=reader.rno and rb.bno=‘B001’;

8、 查询没有借阅B001的员工的编号、姓名、办公室；
select reader.rno,rname,raddress
from reader where not EXISTS (SELECT * FROM RB WHERE rb.rno=reader.rno and rb.bno=‘B001’);

9、 查询借阅了所有书籍的员工的姓名；
select bno from book;

select reader.rno,rname,rb.bno from rb left outer join reader on (reader.rno=rb.rno);

上面的语句用来查看后面的语句结果对不对。
SELECT Rname
FROM Reader
WHERE NOT EXISTS
(SELECT *
FROM Book WHERE NOT EXISTS
(SELECT *
FROM rb
WHERE bno=book.bno and rno=reader.rno));

10、查询至少借阅了编号为R004的员工借阅的全部书籍的员工姓名、性别、办公室
Select * From Rb Where Rno=‘R004’;

select reader.rno,rname,rb.bno from rb left outer join reader on (reader.rno=rb.rno);

上面的语句用来查看后面的语句结果对不对。
Select Rname,Rsex,raddress
From Reader
Where not exists
(select *
from rb a
where a.rno='R004’AND
not exists
(select *
from rb b
where b.rno=reader.rno
and a.bno=b.bno));

11、查询年龄大于30岁或者工作在416的员工信息（用集合查询完成）
Select *
from reader
where rage>30
union Select * from reader where raddress=‘416’;

12、查询年龄大于30岁并且工作不在416的员工信息（用集合查询完成）
Select *
from reader
where rage>30
minus Select * from reader where raddress=‘416’ ;

六、实验心得体会
完成这个实验，我收获了很多。一些在课堂上似懂非懂的知识得到了巩固，对数据库语言的认识也更上一层楼。实验中通过对数据库一些细节的要求以及基本属性的设定，加深了对SOL语句的理解，在对一些约束的建立过程中，对相应的语句规则也有了进一步的理解，对今后学习SQL语句带来了很大的帮助。这次实验让我在实践的过程中认识到了自己的不足，并且通过自己的亲身实践得到了更好的改正。

因为这个实验比较简单，都是按照步骤就可以完成，但是特别繁琐，图片很多，后面就不一一放上来了。
这篇文章可能阅读上会有些乱，大家体谅一下，喜欢的话可以点个赞关注一下哦

上一篇：C# 动态加载卸载 DLL


下一篇：工具idea 基于maven 创建springMVC项目