数据库原理实验一 SQL语言_sql 实验一-CSDN博客

本文链接：https://blog.csdn.net/weixin_74417251/article/details/140545733

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

一、实验目的

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

二、实验原理

1、学生选课关系数据库描述

学生选课关系数据库中仅仅描述学生、课程两个实体以及这两个实体存在的简单联系，其中学生的属性包括学号、姓名、性别、年龄、所在系，课程的属性包括课程号、课程名、学分，学生和课程之间的联系如下：

（1）一个学生可选修多门课程，一门课程可被多个学生选修，某个学生选修某门课程有个成绩；

（2）一门课程至多有一门直接先修课程，一门课程可以有多门后续课程。

2、概念模型（E-R图）

3、关系模式

（1）学生Student表。

属性名	类型	长度	约束	含义
SNO	varchar2	17	主码（非空）	学生编号
Sname	varchar2	10	取值唯一（非空）	姓名
Sage	integer	默认		年龄
Ssex	varchar2	6	限填“男”“女”	性别
Sdept	varchar2	30		所在系

（2）课程Course表

属名	类型	长度	约束	含义
CNO	varchar2	5	主码（非空）	课程编号
Cname	varchar2	30	取值唯一（非空）	课程名
CPNO	varchar2	5	外码，引用Course(CNO)	先修课程
Ccredit	numeric	5,2		学分

（3）学生选课SC表

属性名	类型	长度	约束	含义
SNO	varchar2	17	主属性（非空），外码	学生编号
CNO	varchar2	5	主属性（非空），外码	课程编号
Grade	numeric	5,2		成绩

三、使用仪器、材料

Oracle 11g（下载详情推荐看这位博主：http://t.csdnimg.cn/BOjp1），windows11；

注意网络监听配置的端口号（之后连接数据库要用到）

四、实验步骤

1. 用Oracle Database Configuration Assistant创建数据库

1）开始 → 所有程序 → Oracle-OraDb11g_home1 → 配置和移植工具 → Database Configuration Assistant

2）进入该工具的欢迎界面，点击“下一步”按钮；

3）进入“步骤1操作”，选择“创建数据库”，单击下“下一步”按钮；

4）进入“步骤2：数据库模版”窗口。该配置页面下选择“一般用途”，点击“下一步”按钮；（在Oracle11G中，一般用途和事务处理被合并为一个叫做一般用途或事务处理）

5）进入“步骤3：数据库标识”窗口。输入全局数据库名以及Oracle的系统标识符（SID）。注意：全局数据名是Oracle数据的唯一标识，用于区分该计算机上的其他任何实例，所以不能与已经有的数据库重名。打开Oracle数据的时候，将启动Oracle实例。在默认情况下，全局数据库名和SID同名。

6）进入“步骤4：管理选项”窗口。这里可以配置使用Oracle Enterprise Manager Grid Control集中管理每个Oracle数据库，或者其本地管理Oracle数据库。默认的是使用EM来进行本地数据库的配置，使用DataBase Control进行数据库的管理。在这个地方可以选择是否启用数据库的备份。点击“下一步”按钮

7）进入“步骤5：数据库身份证明”窗口。该窗口的配置作用是为SYS,SYSTEM,DBSNMP以及SYSMAN用户指定口令，可以选择所有的账户使用相同的口令（这里选用统一的口令：jaja），也可以分别设置这四个用户的口令。点击“下一步”按钮；

此时会提示口令设置：但也可以继续

8）进入“步骤6：数据库文件所在位置、存储选项”窗口。在这里可以选择数据库的存储机制和配置Oracle数据文件的位置，选择“文件系统”作为数据库的存储机制，选中“使用模版中的数据文件位置”，点击“下一步”按钮；

9）进入“步骤7：恢复配置”窗口。这里可以配置Oracle数据库的备份以及配置选项，单击右下角“文件位置变量”按钮来查看当前Oracle数据文件变量的相关设置。

默认的归档恢复区为{ORACLE_BASE}/flash_recovery_area，快速恢复区的大小为3912M。建议：将数据库文件以及恢复文件放到物理位置下面不同的磁盘中，以便于保护数据和提高性能。

10）进入“步骤8：数据库内容”窗口。示例方案选项卡，可以配置是否在新的数据库中安装示例方案，这个选项卡里面会创建一个叫做EXAMPLE的表空间，大小约为130M。定制脚本当中，可以指定创建数据库之后需要自动运行的sql脚本，例如创建默认的表等等，可以选择不运行或者运行指定的脚本。点击“下一步”按钮；

11）进入“步骤9：初始化参数”窗口。可以配置数据库的初始化参数：

内存：通常选择典型配置，也可以手动设置SGA（系统全局区）和PGA（程序全局区）的大小。这里设置少一点，点击“下一步”按钮；

调整大小：设置数据块的大小，指定可以同时链接数据库操作系统用户进程的最大数量。

字符集：设置数据库使用的字符集，通常使用操作系统的默认语言设置。

连接模式：Oracle提供了两种数据库的连接模式，专用服务模式将为每个客户机链接分配专用资源。当预期客户机连接总数较小或者客户机向数据库发出的请求时间较长，使用专用服务器模式。多个客户端连接共享一个数据库分配的资源池。当大量用户需要同时连接数据库并且需要有效的利用资源的时候，使用共享数据库模式。

12）进入“配置10：数据库存储”窗口。这里可以指定创建数据库的存储参数，可以产看和修改存储参数的对象，包括控制文件、数据文件、和重做日志组。

点击左侧的树状列表，选择查看或者修改的对象，在右侧窗口中将会显示对象的存储信息，通常可以使用默认数据。点击“下一步”按钮；

13）进入“步骤11：创建选项”窗口。这里可以选择“创建数据库”也可以保存编辑的数据库模版，还可以生成创建数据库的脚本。点击“完成”；

14）打开“确认”窗口，显示数据库模版，这里将提示用户创建新的数据库。

15）点击“确定”按钮后，开始创建数据库，并且显示数据库创建的过程以及进度：

创建数据库的时间取决于计算机的硬件配置以及数据库的配置情况，选择安装的组建越多，需要的时间就越长。创建完成之后，弹出“创建完成”窗口。可以打开“口令管理”对话框，编辑数据库中各个用户的口令，点击“退出”按钮，完成创建数据库的过程。

16）利用命令窗口连接新建的Mydatabase1数据库的system用户：

2. 用Oracle Database Configuration Assistant删除刚创建的数据库

1）点击“开始”-“所有程序”-“Oracle”-“配置和移植工具”-“Database Configuration Assistant”或者运行—‘cmd’—‘dbca ’

2）进入该工具的欢迎界面，点击“下一步”按钮；

3）进入“步骤1操作”，选择“删除数据库”，点击下一步；

4）进入‘数据库’选择界面，选择所要删除的数据库名，并且要输入据有SYSDBA权限的用户及其口令才可删除干净数据库；

5）点击完成，出现提示窗口，选择“是”即可删除所要删除的数据库。

删除后再进入已经没有这个数据库：

3. 打开SQLDEVELOPER

1）普通用户的数据库对象通常都存储在与用户对应的模式中，先利用管理员登录；

点击文件：用SQLDEVELOPER本地Oracle服务器

使用 SQL DEVELOPER 连接远程 Oracle 服务器：

若用sys连接，需要：

以SYSDBA的身份连接到数据库，则看到大量的系统数据表：

2）再利用 Create user 命令创建一个新用户，并授予新用户必要的权限，然后利用新建立的用户进行连接，在新创建的用户模式中创建数据库对象。以下代码创建dbls用户：

create user dbls identified by db15;    /*创建新用户 dbls，密码为 db15*/

grant connect,resource to dbls;         /*把 connect、resource 角色权限授予 dbls*/

上述两句可以简写成一句：

grant connect, resource to dbls identified by db15;

将光标放在每一个“;”后，逐句点击运行（或直接运行脚本），查看脚本的运行结果：

可以在“其他用户”栏目中可以看到新建的用户：

利用新建用户连接数据库：

结果：

注：也可以不用密码，用管理员身份登录到本地Oracle数据库，可以采用“操作系统验证”，连接类型选择“本地/继承”，角色选择SYSDBA。Oracle管理员SYS、SYSTEM的密码为oracle，若密码有误，启动CMD进入命令提示符，执行命令：

a）SQLPLUS / as SYSDBA；

b）Alter user SYSTEM identified by 新密码;（新密码自行设置）；

c）Conn SYSTEM/新密码; --用SYSTEM用户登录，同样可以修改SYS的密码；

4. 创建以下数据表（Student（主码为SNO）、Course（主码为CNO）、SC（主码为（SNO、CNO）），其中SNO引用Student的SNO属性，CNO引用Course的CNO属性）

在SQLDEVELOPER的工具选项选择SQL工作表：

选择在新建用户创建SQL工作表：

在新建的用户执行：

CREATE TABLE Student

(Sno VARCHAR2(17) PRIMARY KEY,

Sname VARCHAR2(10) NOT NULL UNIQUE,

Sage INTEGER,

Ssex VARCHAR2(2),

Sdept VARCHAR2(20));



CREATE TABLE Course

(CNO VARCHAR2(5) PRIMARY KEY,

 Cname VARCHAR2(20) NOT NULL UNIQUE,

 CPNO VARCHAR2(5),

 Ccredit NUMBER);



CREATE TABLE SC

(SNO VARCHAR2(17),

 CNO VARCHAR2(5),

 Grade NUMERIC(5),

 PRIMARY KEY(SNO,CNO),

 FOREIGN KEY(SNO) REFERENCES Student(SNO),

 FOREIGN KEY(CNO) REFERENCES Course(CNO));

在新连接的用户里，只看到用户自己创建的表：

Student

Course

5. 修改Student表格，用SQL语句为Student表格添加一个“入学时间”属性，属性名为Senrollment

执行下面的语句修改表结构：

ALTER TABLE Student ADD Senrollment DATE;

看到表的结构如下：

6. 向三个表格中插入5条数据，数据内容自编

向Student表插入数据：

INSERT INTO Student VALUES('32206100007','罗一隆',18,'男','网络工程','01-9月-2022');

INSERT INTO Student VALUES('32206100021','叶凌灵',17,'女','软件工程','01-9月-2022');

INSERT INTO Student VALUES('32206100026','陈尘',19,'女','计算机科学与技术','01-9月-2022');

INSERT INTO Student VALUES('32106100033','杨辉',20,'男','计算机科学与技术','01-9月-2021');

INSERT INTO Student VALUES('32206100019','林镇',19,'男','人工智能','01-9月-2022');

此时报错：

*修改Student表结构，把Ssex列的宽度设置为6个字节，Sdept列设置为30：

ALTER TABLE Student MODIFY Ssex VARCHAR2(6);

ALTER TABLE Student MODIFY Sdept VARCHAR2(30);

重新执行可以看到以下的结果：

向Course表插入数据：

INSERT INTO Course VALUES('1','程序设计','null','2');

INSERT INTO Course VALUES('2','数据结构','1','3');

INSERT INTO Course VALUES('3','操作系统','2','4');

INSERT INTO Course VALUES('4','高等数学','null','4');

INSERT INTO Course VALUES('5','计算机网络','1','3');

INSERT INTO Course VALUES('6','概率论与数理统计','4','2');

INSERT INTO Course VALUES('7','人工智能导论','6','4');

可以看到以下的结果：

向SC表插入数据：

INSERT INTO SC VALUES('32206100007','1','75');

INSERT INTO SC VALUES('32206100021','1','84');

INSERT INTO SC VALUES('32206100026','1','79');

INSERT INTO SC VALUES('32206100019','1','90');

INSERT INTO SC VALUES('32206100019','2','92');

INSERT INTO SC VALUES('32106100033','2','84');

INSERT INTO SC VALUES('32206100057','4','80');

INSERT INTO SC VALUES('32206100026','4','88');

INSERT INTO SC VALUES('32106100033','5','86');

可以看到以下的结果：

7. 限定Ssex的值只能为“男”或者“女”

写代码修改：

Ssex VARCHAR2(2) CHECK(Ssex IN('男','女'))

尝试一下用可视化界面输入不合规格的数据查看效果：

出现报错：

8. 修改Course表格，用SQL语句为Course表格添加一个“说明”属性，属性名为“Cdesc”，类型为varchar2，长度为200

执行：

ALTER TABLE Course ADD Cdesc VARCHAR2(200);

结果：（注：无论基本表中原来是否已有数据，新增的列一律为空值）

9. 更改Course表格的Cdesc属性，使其长度变为500

执行：

ALTER TABLE Course MODIFY Cdesc VARCHAR2(500);

结果：

10. 删除刚建立的属性Cdesc

执行：

ALTER TABLE Course DROP COLUMN Cdesc;

11. 修改Course表的CPNO，使其为外码，引用Course表的CNO属性

执行：

ALTER TABLE Course ADD FOREIGN KEY (CPNO) REFERENCES Course(CNO);

（注：若一开始就定义则FOREIGN KEY(CPNO) REFERENCES Course(CNO)）

五、实验注意项

1、首次使用oracle11g进行数据库实验注意事项及问题解决：

（1）启动oracle监听快捷方法

cmd命令行窗口下，输入lsnrctl start，回车即启动监听

（2）SQL Developer 无法启动问题

由于本机Windows 11系统是64位的，而Oracle 11g安装的Oracle SQL Developer版本(1.5.5)是32位的，不兼容64位系统；而Oracle自带的JDK却是64位的，SQL Developer与JDK不匹配，所以会出现指定时会出现警告：could not find jvm.cfg! in ......

解决方法：

方案一安装32位的1.5-1.6版的JDK 去兼容 Oracle自带的SQL Developer1.5.5。

方案二安装64位的1.6版以上的JDK + 安装高版本的SQL Developer代替原来。

（3）SQL Developer无法连接数据库原因总结

①机器没有安装 oracle

解决方法：连接旁边的机器做实验。

② 服务没有启动

解决方法：启动 OracleServiceOrcl 和监听器 listener 服务。

③参数配置错误

解决方法：利用 net manager 网络配置工具配置监听程序的监听地址。

④其他

如果使用了很多方法仍然无法连接数据库，试试使用 SQLPlus 连接，如果 SQL Plus 也

无法连接，则试试连接旁边的机器。

（4）如果表事先是空的，且有属性连接到一个表的主键，此时插入数据会报错：

原因：原先的表里没有一个元组的CNO是null的，需要手动在表里增加。

2、本次实验增加的认知总结

（1）Oracle数据库中SYS和SYSTEM用户的区别

①权限差异：

· SYS用户拥有DBA权限，并具备SYSDBA和SYSOPER权限，它能够执行包括启动和关闭数据库在内的所有管理操作，是Oracle数据库中权限最高的账号。

· SYSTEM用户也拥有DBA权限，但它没有SYSDBA或SYSOPER权限，因此不能执行启动或关闭数据库的操作。

②登录模式：

· SYS用户只能以SYSDBA或SYSOPER身份登录，不能使用普通用户模式登录。其密码保存在密码文件。

· SYSTEM用户通常使用Normal模式登录，除非被授予了SYSDBA或SYSOPER权限。其密码保存在数据库中。

③数据字典的拥有者：

· SYS用户是数据字典的拥有者，所有Oracle的数据字典基表和视图都存放在SYS用户中，由数据库自己维护，任何用户都不能手动更改。

· SYSTEM用户没有数据字典，它主要用于存放次一级的内部数据，如Oracle的一些特性或工具的管理信息。

（2）sqlplus有几种登陆方式：

C: > sqlplus "/as sysdba" --以操作系统权限认证的oracle sys管理员登陆

C: > sqlplus /nolog --不在cmd或者terminal当中暴露密码的登陆方式

SQL> conn /as sysdba

SQL> conn sys/password as sysdba

C: > sqlplus scott/tiger --非管理员用户登陆

C: > sqlplus scott/tiger@orcl --非管理员用户使用tns别名登陆

C: > sqlplus sys/password@orcl as sysdba --管理员用户使用tns别名登陆

C: > sqlplus --不显露密码的登陆方式

（3）Oracle中number、numeric和integer、int的区别

①NUMBER(P,S)、NUMERIC

NUMBER(P,S)既可以存浮点，也可以存整数。P指最大位数，默认38位；S指小数位数。

NUMERIC用来存储负、正的整数、分数和浮点数数据，是NUMBER的子类型。

②INTEGER、INT

都是NUMBER的子类型，等同于NUMBER(38,0)，若干插入、更新的数值有小数，会被四舍五入。INT是基础数据类型，默认值0；INTEGER是包装数据类型，默认值null，且必须实例化才可以使用。INT可以使用“==”来比较两个变量，INTEGER一定要用“equals”来比较。

（4）查看系统时间：

执行：SELECT SYSDATE FROM dual;

实验1-2 SQL语言晋阶

一、实验目的

1、熟悉并掌握创建表，插入记录，查询记录，删除记录，修改记录。

2、熟悉并掌握创建索引，删除索引。

3、熟悉并掌握创建视图，使用视图，删除视图。

二、实验原理

1、单位成员借阅关系数据库描述

现有一个单位内部的小型图书借阅系统，假设每本图书的数量无限制，并且可以借给任何单位成员，每个单位成员可以借多本书，单位成员与图书的关系是多对多的关系。假设系统中仅有三个关系模式。

2、概念模型（E-R图）

3、关系模式（数据结构）

单位成员Reader表

属性名	类型	长度	说明	含义
RNO	varchar2	4	主码（非空）	员工编号
Rname	varchar2	10	非空可不唯一	员工姓名
Rsex	varchar2	4		性别
Rage	integer	默认		年龄
Rboss	varchar2	10		直接上司
Raddress	varchar2	30		办公地点

图书Book表

属性名	类型	长度	说明	含义
BNO	varchar2	4	主码（非空）	书本编号
Bname	varchar2	50	非空可不唯一	书名
Bauthor	varchar2	50		作者
Bpress	varchar2	50		出版社
Bprice	varchar2	numeric(6,2)		价格

借阅信息RB表

属性名	类型	长度	是否空	含义
RNO	varchar2	4	主属性（非空），外码	员工编号
BNO	varchar2	4	主属性（非空），外码	书本编号
RBdate	date	默认		借阅日期

三、使用仪器、材料

Oracle 11g，windows11；

四、实验步骤

1、创建Reader、Book、RB三个表并对其进行操作

1）创建新的用户并授权，以用户LIB的身份建立连接，并在此连接下执行后面的操作：

CREATE USER lib IDENTIFIED BY lib;

GRANT RESOURCE,CONNECT,DBA TO lib;

2）拷贝代码运行，删去旧的同名数据表：（没数据无效）

Declare //声明部分开始

tmp integer default 0; //声明一个整数变量tmp并初始化为0

Begin //主体部分开始

//查询是否存在名为'RB'的数据表，并将结果存储在tmp变量中

select count(*) into tmp from user_tables where table_name='RB';

if(tmp>0) then //如果tmp大于0，说明存在名为'RB'的数据表

execute immediate 'drop table RB'; //则执行动态SQL语句，删除名为'RB'的数据表

end if; //条件结束

//查询是否存在名为'READER'的数据表，并将结果存储在tmp变量中

select count(*) into tmp from user_tables where table_name='READER';

if(tmp>0) then //如果tmp大于0，说明存在名为'READER'的数据表

//则执行动态SQL语句，删除名为'READER'的数据表

execute immediate 'drop table READER';

end if; //条件结束

//查询是否存在名为'BOOK'的数据表，并将结果存储在tmp变量中

select count(*) into tmp from user_tables where table_name='BOOK';

if(tmp>0) then //如果tmp大于0，说明存在名为'BOOK'的数据表

//则执行动态SQL语句，删除名为'BOOK'的数据表

execute immediate 'drop table BOOK';

end if; //条件结束

end; //主体结束

3）建立表格Reader

create table Reader

(RNO varchar2(4) primary key,

Rname varchar2(10) not null,

Rsex varchar2(4),

Rage integer,

Rboss varchar2(10),

Raddress varchar2(30)) ;

4）拷贝代码运行，向Reader表格中插入十条初始数据

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');

Reader表初始数据

5）查询记录：在Reader表中查询直接上司是“李四”的员工的名字

执行：

select Rname from Reader where (Rboss='李四');

查询结果：

6）修改记录：在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”

执行：

UPDATE Reader SET Raddress='420' WHERE Rboss='李四';

更改结果：5行更新

7）删除记录：在Reader表中把直接上司未赋值（NULL）是记录删去

执行：

DELETE FROM Reader WHERE Rboss is NULL;

删除记录结果：5行删除

8）删去数据表：把整个Reader表删去

执行：

DROP TABLE Reader;

删除Reader表：

9）重新执行第3）、4）步，即建立数据表、插入数据。

10）创建表格Reader2，比较Reader2和Reader中的记录和结构是否相同

执行：

create table reader2 as select * from reader; //创建表格reader2

Reader2表：与Reader表的内容一样，结构不一致

Reader2的列

Reader的列

在可视化界面查看这两个表所附带的约束：（Reader2少了一个主码的约束）

Reader2

Reader

或者分别执行：（desc--查看表结构的详细信息）

Reader2结构：desc reader2;

Reader结构：desc reader;

11）分别执行下面的每行语句，查看语句是否执行成功，分析为什么？

update reader set RNO='R001' where Rname='张三';

原因：表Reader中的RNO是主码，更新后的RNO与现存的RNO重复，违反唯一约束条件。

update reader2 set RNO='R001' where Rname='张三';

两行已更新。

成功原因：Reader2没有主码的约束。

insert into reader2(RNO,Rname,Rsex,Rage,Rboss, Raddress) values(null,'lisi',null,null,null,null);

1行已插入。

成功原因：Reader2没有主码的约束，可以插入主码空值。

12）删除Reader2表格；

DROP TABLE reader2;

13）建立表格Book

create table Book

(BNO varchar2(4), primary key(BNO),

Bname varchar2(50) not null,

Bauthor varchar2(50),

Bpress varchar2(50),

Bprice numeric(6,2));

14）向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);

15）建立表格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));

16）向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’的员工，如果执行错误，分析错误原因；

原因：有子记录依赖于要删除的主记录，在这种情况下，数据库会拒绝删除主记录，以维护引用完整性。

3）想办法删除Reader中的’R010’员工；

方法一：先把RB中所有’R010’的借书记录都删掉

方法二：修改数据表READER的结构，允许级联删除（注：ORACLE不支持级联更新）

alter table RB       //对RB表进行修改

add constraint MYFK    //添加一个名为MYFK的约束，这是外键约束的名称

foreign key(rno) references reader(rno)    //定义外键约束，rno列是外键

on delete cascade;      //删除Reader表的记录会触发RB表中相关记录的删除

系统提示：SQL 错误: ORA-02275: 此表中已经存在这样的引用约束条件

可见，首先要先删去原来的约束，因建表时没给该约束命名，故应查这个约束的名字，

才能删去。注意，这个名字由系统自动赋值，不同的环境下约束名可能不同）：

alter table rb drop constraint sys_c007032;

删除后再执行：

alter table rb add constraint MYFK foreign key(rno) references reader(rno) on delete cascade;

再执行：

delete from reader where rno='R010';

顺利删去一行

打开RB表，发现前面插入的('R010','B005')也不在了

4）为Reader表添加一个属性列“出生年份”，名为Rbirthday，整数；

alter table Reader ADD Rbirthday integer;

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之间；

//添加了一个名为"bp"的CHECK约束

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是否可以删除？

原因：在Oracle数据库中，DROP TABLE语句不支持CASCADE选项，因此无法直接在DROP TABLE语句中使用 CASCADE。需要先把关联的外键删除，才可以删除表。

drop table reader CASCADE CONSTRAINTS;

//关键字 CASCADE CONSTRAINTS 告诉数据库系统在删除表时，也同时删除所有与该表相关的约束。

10）删除Reader、Book和RB表。

drop table rb CASCADE CONSTRAINTS;

drop table book CASCADE CONSTRAINTS;

2、索引的建立与删除

1）重新执行前面的代码创建三个数据表并插入数据；

2）为Reader表格的Rname建立UNIQUE索引

create unique index myindex on reader(rname);

报告出错："cannot CREATE UNIQUE INDEX; duplicate keys found"

修改表格中数据：

UPDATE Reader SET Rname='张三儿' WHERE RNO='R002';

重复执行上面重新建立索引：

3）删除索引

drop index myindex;

3、视图的建立、使用与删除

1）如果上面的运行是在LIB的连接中，则需要回到在sysdba 的连接中，执行：

grant resource, connect, DBA to LIB;

否则系统显示没有创建视图的权限。

执行完之后再回到CC的连接：在ORACLE SQL Developer 的左上方

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( rno,rname,rage) values('R999','new reader',99);

select * from reader;

select * from v416;

原因：刚插入数据条件不满足此视图的条件了

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);

一般插入数据：插入数据不符合视图定义中的条件，执行不成功。

重新修改语句插入：

drop view v417;

CREATE VIEW V417

as select rno,rname,rsex,rage,raddress

from reader

where raddress='417' with check option;

insert into v417( rno,rname,rage,raddress) values('R777','417 reader',66,'417');

执行成功

7）通过视图删除刚插入的员工记录，在基本表Reader中查看是否已经删除成功？

delete from v416 where rname='王小倩';

select * from v416;

在视图中删除数据后，原基本表Reader中数据也删除：

8）在视图V417上建立所有女员工信息的视图FV417，查询视图结果

CREATE VIEW FV417 as select * from V417 where RSEX='女';

select * from FV417;

9）删除视图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;

对RB表进行聚合操作，根据BNO列对数据进行分组，并计算每个分组中的行数

CREATE VIEW GV(BNO,COUNTS)

AS SELECT BNO,COUNT(*) FROM RB GROUP BY BNO;

SELECT * FROM GV;

11）删除视图V417

drop view v417

五、解答一些小问题

1、基本表的删除操作

问：删去旧的同名数据表为何要先删去RB？能不能先删去READER？

答：通常情况，代码中的顺序并不会对执行结果产生影响。但是为了避免存在的问题影响：

①依赖关系：'RB'数据表可能包含其他数据表的外键，需要先删除'RB'以避免删除其他表时出现外键约束错误。

②数据保留策略：'RB'数据表可能中包含了敏感信息或者其他需要谨慎处理的数据，需要优先删除以确保数据安全。

问：为什么不能直接删除Reader表的员工？

答：有子记录依赖于要删除的主记录，在这种情况下，数据库会拒绝删除主记录，以维护引用完整性。删除子记录对'R010'的引用，才可以成功删除该元组。

问：为什么对Reader表直接级联删除不成功？

答：在Oracle数据库中，DROP TABLE语句不支持CASCADE选项，因此无法直接在DROP TABLE语句中使用 CASCADE。解决如下：

方法一：先删除表的相关对象，然后再删除表本身

ALTER TABLE Reader DROP CONSTRAINT RR;

DROP TABLE Reader;

方法二：可以使用关键字 CASCADE CONSTRAINTS 告诉数据库系统在删除表时，也同时删除所有与该表相关的约束。

DROP TABLE Reader CASCADE CONSTRAINTS;

2、索引的建立

问：报告出错："cannot CREATE UNIQUE INDEX; duplicate keys found"如何修改表格数据，再建索引？

答：而唯一索引要求索引列的值在整个表中是唯一的。要么把数据库的数据清理掉；要么把要创建唯一索引的那一列重复的数据删掉。

3、视图的创建

问：建立视图GV，数据包括每本图书的编号及其借阅数量。思考是否可以向GV中插入数据，为什么？

答：不可以向GV插入数据，因为GV的内容并非数据，而是数据和相关数据统计结果。

六、实验体会

1、增加认知

①DELETE

语法：DELETE FROM table_name WHERE condition;

作用：DELETE用于删除表中的行，但保留表的结构。可以使用WHERE子句来指定删除行，不使用WHERE子句，则会删除表中的所有行。

注意事项：DELETE会在表中删除满足条件的行，但不会删除表本身。DELETE操作会触发触发器、日志记录等操作，因此可以撤销（如果启用了事务）。如果要删除的行在其他表中被引用（外键约束），权限不足，锁定，或者有活动事务，会操作失败。

②DROP

语法：DROP TABLE table_name;

作用：DROP用于完全删除数据库中的对象，如表、索引、视图等。表及其所有相关的对象（如索引、触发器、约束等）都将被删除。

注意事项：DROP会永久性地删除数据库中的对象，而不是仅删除对象中的数据，是不可逆的。如果要删除的表有其他表的外键引用，权限不足，锁定，或者有活动事务，会操作失败。

③CHECK

语法：CREATE TABLE table_name (column1 datatype CONSTRAINT constraint_name1 CHECK (condition),

CONSTRAINT constraint_name_n CONSTRAINT_TYPE_n);

说明：一种用于限制列中值的范围或满足特定的条件的约束条件。可以在列的定义时使用或在表已经创建后使用 ALTER TABLE 添加。

④CONSTRAINTS

语法：ALTER TABLE table_name ADD CONSTRAINT constraint_name C_TYPE C_DETAILS;

说明：用于定义数据完整性约束的关键字，可以包含多种约束类型（CHECK、PRIMARY KEY、FOREIGN KEY、UNIQUE、DEFAULT ）。可以为表中的列或组合列添加多种类型的约束。

2、报错修正

问：为什么对Reader表直接级联删除不成功？

答：在Oracle数据库中，DROP TABLE语句不支持CASCADE选项，因此无法直接在DROP TABLE语句中使用 CASCADE。解决如下：

方法一：先删除表的相关对象，然后再删除表本身

ALTER TABLE Reader DROP CONSTRAINT RR;

DROP TABLE Reader;

方法二：可以使用关键字 CASCADE CONSTRAINTS 告诉数据库系统在删除表时，也同

时删除所有与该表相关的约束。

DROP TABLE Reader CASCADE CONSTRAINTS;

实验1-3 SQL查询数据

一、实验目的

熟练掌握使用SQL查询语言。完成各类查询操作（单表查询，连接查询，嵌套查询，集合查询）。

二、实验原理

1、查询原理

（1）单表查询：从单个表中检索数据的查询方式（过滤、排序等）。

（2）复合查询：从多个表中检索数据的查询方式（连接等）。

（3）嵌套查询（子查询）：在一个查询中嵌套另一个查询。内部查询的结果用于外部查询的条件或者比较。

（4）集合查询：用于对多个查询结果进行组合和比较，包括 UNION、UNION ALL、INTERSECT 和 EXCEPT（或 MINUS）

2、查询语法

SELECT: 指定要检索的列。

FROM: 指定要查询的表。

WHERE: 可选，用于指定过滤条件，只返回满足条件的行。

ORDER BY: 可选，用于按照指定的列对结果进行排序。

JOIN: 指定连接方式，可以是 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN 等。

ON: 指定连接条件，它指定了两个表之间的关联关系。

UNION：于合并两个或多个查询的结果集，并去除重复的行。

UNION ALL：与 UNION 类似，但不去除重复的行，保留所有行。

INTERSECT：从两个查询的结果中返回共有的行，即交集。

EXCEPT或MINUS：从第一个查询结果中返回不在第二个查询结果中的行，即差集。

三、使用仪器、材料

Oracle 11g，windows11；

四、实验步骤

1、原始数据创建

1）建立表格Reader

create table Reader

(RNO varchar2(4) primary key,

Rname varchar2(10) not null,

Rsex varchar2(4),

Rage integer,

Rboss varchar2(10),

Raddress varchar2(30)) ;

拷贝代码运行，向Reader表格中插入十条初始数据

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');

2）建立表格Book

create table Book

(BNO varchar2(4), primary key(BNO),

Bname varchar2(50) not null,

Bauthor varchar2(50),

Bpress varchar2(50),

Bprice numeric(6,2));

向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);

3）建立表格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));

向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');

2、单表查询

1）查询全体员工的姓名和出生年份；

SELECT RNAME,'Year of Birth: ' ,2024-RAGE FROM reader;

2）查询工作在416房间的员工的所有信息；

SELECT RNO,RNAME,RSEX,RAGE,RBOSS 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 '_小%';

注：ORACLE只需一个下划线符号代表一个汉字

7）查询所有不姓“李”的员工姓名、性别；

select rname,rsex from reader where rname not like '李%';

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

select bname,bpress,bprice from book where bprice is not null;

所有书：

价格不为空的书：默认升序排select bname,bpress,bprice from book;

9）查询清华大学出版社和高等教育出版社出版的所有书籍，按照出版社降序、书名升序排列；

select * from book where bpress='清华大学出版社'or bpress='高等教育出版社' ORDER by bpress desc,BNAME asc;

select * from book;（用来查看后面的语句结果对不对）

10）查询员工的总人数

SELECT COUNT(*) FROM READER

11）查询借了书的员工的人数；

SELECT COUNT(*)

FROM READER

WHERE READER.RNO IN (SELECT RNO FROM rb);

或

SELECT COUNT(DISTINCT 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,'nount of bpress: ',COUNT(bpress)

FROM book 

GROUP BY bpress, 'nount of bpress: ';

3、复合查询

1）查询每个员工及其借书情况，列出员工编号、姓名和借书日期

select reader.rno,rname,rbdate from reader,rb where reader.rno in 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 b.raddress = a.raddress and a.rname = '李小龙' and b.rname != '李小龙';

子查询：

select RNAME from READER

WHERE RADDRESS= (select RADDRESS from READER where rname='李小龙');

4）查询借阅了“数据库原理”的员工所有信息（两种方法：连接查询、子查询）

连接查询：

Select rbdate,rage,rboss,reader.rno,rname,rsex,raddress

from reader,rb,book

where book.bname='数据库原理'and book.bno=rb.bno and rb.rno=reader.rno;

子查询：

select RNO,RNAME,RSEX,RADDRESS

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 <any(SELECT 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 reader.rno=rb.rno and rb.bno='B001';

8）查询没有借阅B001的员工的编号、姓名、办公室；

select rno,rname,raddress

from reader

where rno NOT IN (select reader.rno

                  from reader,rb

                  where reader.rno=rb.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));

注：前两句SELECT是为了显示原数据表的数据，以检查查询结果是否正确。

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';

（union）

12）查询年龄大于30岁并且工作不在416的员工信息（用集合查询完成）

Select * from reader where rage>30 minus Select * from reader where raddress='416' ;

（minus）

注意：ORACLE的集合减，不是’except’，而是’ minus’

五、实验体会

1、单表查询注意事项：

-选择合适的列：只选择需要的列，避免检索不必要的数据

-使用WHERE子句过滤数据

-利用索引：确保查询条件中包含索引列

-NULL值处理：确保查询条件和结果集的处理能够正确处理NULL值

-聚合函数使用

-数据类型转换：确保在比较不同数据类型的列时进行正确的数据类型转换

-避免重复行：使用 DISTINCT 关键字或 GROUP BY 子句来消除查询结果中的重复行。

2、复合查询注意事项：

-确定连接类型

-使用ON子句：指定连接条件，确保连接的正确性和有效性