第3章 数据库的创建和操作

1、第第3章章 数据库的创建和操作数据库的创建和操作3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）3. 2 以界面方式操作数据库（采用以界面方式操作数据库（采用SQL Developer）3. 3 命令方式操作数据库（采用命令方式操作数据库（采用SQL*Plus）3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（1）启动DBCA，出现“欢迎使用”界面，如图3.1所示，单击“下一步”按钮进入创建数据库的向导。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（2）在“操作”窗口中，用户可以选择要执行的操作，这里选中“创建数据库”

2、选项，如图3.2所示，单击“下一步”按钮。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（3）在“数据库模板”窗口中，选择相应选项后单击“显示详细资料”按钮可查看该数据库模板的各种信息。这里选择“一般用途或事务处理”选项，如图3.3所示，单击“下一步”按钮。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（4）在“数据库标识”窗口中输入“全局数据库名”和“SID”，如图3.4所示，单击“下一步”按钮。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（5）在“管理选项”窗口中可以选择配置Enterprise Manager

3、企业管理器或者配置Database Control管理本地数据库，这里保持默认设置，如图3.5所示，单击“下一步”按钮。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（6）在“数据库身份证明”窗口中，将所有账户设置为同一管理口令（Mm123456），如图3.6所示，单击“下一步”按钮。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（7）在“数据库文件所在位置”窗口中，选择“所有数据库文件使用公共位置”，单击“浏览”按钮选择数据库文件的存放路径，如图3.7所示，单击“下一步”按钮。（8）在“恢复配置”窗口中采取默认的配置，单击“下一步”按钮继续

4、。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（9）在“数据库内容”窗口中勾选“示例方案”，如图3.8所示，这样就可以在学习的过程中参考标准设置，也可了解基础的数据库创建方法和SQL语言。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（10）在“初始化参数”和“数据库存储”窗口中也保持默认配置，两次单击“下一步”按钮。（11）在“创建选项”窗口中选择“创建数据库”选项，如图3.9所示，单击“完成”按钮，之后会弹出确认创建的对话框，单击“确定”按钮开始创建数据库。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（12）

5、创建数据库期间显示的创建进度窗口如图3.10所示，过程较为漫长，读者要耐心地等待3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）（13）创建数据库完毕后，系统会弹出窗口显示相关的提示信息，如图3.11所示，需要在这一步解锁SCOTT、SYSTEM、SYS账户并设置其口令，单击窗口中的“口令管理”按钮，弹出“口令管理”对话框。3. 1 以界面方式创建数据库（采用以界面方式创建数据库（采用DBCA）至此，所有步骤都已全部完成。现在，系统服务中应该已经有SID为XSCJ的服务选项并已置为“自动”启动，服务正在运行，如图3.12所示，访问XSCJ数据库前必须保证已启动了这两个系

6、统服务：OracleOraDb11g_home1TNSListener和OracleServiceXSCJ。3. 2 以界面方式操作数据库（采用以界面方式操作数据库（采用SQL Developer）3.2.1 表操作表操作1表的概念表的概念表是日常工作和生活中经常使用的一种表示数据及其关系的形式，表3.1就是用来表示学生信息的一个学生表。学 号姓 名性 别出 生 时 间专 业总 学 分备 注151101王林男1997-02-10计算机50 151103王燕女1996-10-06计算机50 151108林一帆男1996-08-05计算机52已提前修完一门课151202王林男1996-01-29通

7、信工程40有一门课不及格，待补考151204马琳琳女1996-02-10通信工程42 3.2.1 表操作表操作关系数据库使用表（即关系）来表示实体及其联系。表包含下列概念。（1）表结构表结构：每个表都包含一组固定的列，而列由数据类型（DATATYPE）和长度（LENGTH）两部分组成，以描述该表所代表的实体的属性。（2）记录记录：每个表包含了若干行数据，它们是表的“值”，其中的一行称为一个记录，因此，表是记录的有限集合。（3）字段字段：每个记录由若干个数据项构成，将构成记录的数据项称为字段。例如，表3.1的XSB，其表结构为（学号，姓名，性别，出生时间，专业，总学分，备注），包含7个字段，由5

8、个记录组成。（4）关键字关键字：若表中记录的某个字段或字段组合能唯一标识记录，则称该字段（字段组合）为候选关键字（Candidate Key）。若一个表有多个候选关键字，则选定其中一个为主关键字（Primary Key），也称为主键。3.2.1 表操作表操作2数据类型数据类型在设计表的列时，必须为其指定数据类型，它决定了该列数据的取值、范围和存储格式。列的数据类型可以是Oracle提供的系统数据类型，其中主要的类型列于表3.2中。3.2.1 表操作表操作3表结构设计表结构设计创建表的实质就是定义表结构以及设置表和列的属性。创建表之前，先要确定表的名字、表的属性，同时确定表所包含的列名、列的数据

9、类型、长度、是否可为空值、约束条件、默认值设置、规则以及所需索引、哪些列是主键、哪些列是外键等属性，这些属性构成表结构。这里以本书要使用到的学生成绩管理系统的三个表：学生表（表名为XSB）、课程表（表名为KCB）和成绩表（表名为CJB）为例介绍如何设计表的结构。最终设计出XSB的表结构如表3.3所示。列 名数 据 类 型是 否 可 空默 认 值说 明学号char(6)无主键姓名char(8)无 性别char(2)“男” 出生时间date无 专业char(12)无 总学分number(2)00 总学分100备注varchar2(200)无 3.2.1 表操作表操作参照XSB表结构的设计方法，同样

10、可以设计出其他两个表的结构。如表3.4所示是KCB的表结构，表3.5是CJB的表结构。列 名数 据 类 型是 否 可 空默 认 值说 明课程号char(3)无主键课程名char(16)无 开课学期number(1)1只能为18学时number(2)0 学分number(1)0 列 名数 据 类 型是 否 可 空默 认 值说 明学号char(6)无主键课程号char(3)无主键成绩number(2)无 表3.4 KCB的表结构表3.5 CJB的表结构3.2.1 表操作表操作4创建表创建表（1）启动SQL Developer，在“连接”节点下打开数据库连接myorcl（已创建）。右击“表”节点，选

11、择“新建表”菜单项。（2）进入“创建表”窗口，在“名称”栏中填写表名XSB，在“表”选项卡的“列名”、“类型”、“大小”、“非空”、“主键”栏中分别填入（选择）XSB表的“学号”列的列名、数据类型、长度、非空性和是否为主键等信息，完成后单击“添加列”按钮输入下一列，直到所有的列输入完为止，如图3.13所示。3.2.1 表操作表操作（3）输完最后一列的信息后，选中右上角的“高级”复选框，这时会显示出更多的表选项，如表类型、列的默认值、约束条件、外键和存储选项等，如图3.14所示，例如要设置默认值可以在“列属性”选项页中该列的“默认”栏中输入默认值。3.2.1 表操作表操作5修改表修改表使用SQL

12、 Developer工具修改表的方法很简单。XSB表创建完成后在主界面的“表”目录下可以找到该表。右击XSB表选择“编辑”菜单项，进入“编辑表”窗口（类似图3.14的界面），在该窗口中的“列”选项页右侧单击 按钮可以添加新列，单击 按钮可以删除列，在“列属性”选项页的各栏中可以修改列的属性。表的主键列不能直接删除，要删除必须先取消主键。单击窗口左侧的“主键”选项，在窗口右边的“所选列”栏会显示已被设为主键的列，如图3.15所示。3.2.1 表操作表操作6删除表删除表以删除XSB表为例，在“表”目录下右击XSB表选择“表”菜单下的“删除”子菜单项，如图3.16所示，之后弹出“删除”确认对话框，选

13、中“级联约束条件”复选框，单击“应用”按钮，弹出表已删除的提示消息，单击“确定”按钮即可。3.2.2 表数据操作表数据操作1插入记录插入记录首先，启动SQL Developer，打开myorcl连接（需要输入SCOTT用户口令），展开“表”目录，单击“XSB”表，在右边窗口中单击“数据”选项卡，切换到表数据窗口，如图3.17所示。3.2.2 表数据操作表数据操作说明：在输入“出生时间”列数据时，Oracle 11g默认的日期格式为“DD-MM 月-YY”，例如，日期“1997-02-10”应该输入“10-2月-97”。为能使用我们所习惯的输入方式，这里先要修改一下数据库默认的日期格式，在SQL

14、 Developer命令窗口中执行如下语句（具体操作见稍后的3.2.3节）：ALTER SESSIONSET NLS_DATE_FORMAT = YYYY-MM-DD;3.2.2 表数据操作表数据操作输入完一行数据后，单击 （提交更改）按钮将数据保存到数据库中，同时下方的“消息 - 日志”子窗口列出用于插入数据的INSERT语句，如图3.18所示。3.2.2 表数据操作表数据操作2修改记录修改记录修改记录的方法与插入类似，在“数据”选项页找到要修改的记录所在行，修改后该行的行号前会出现一个“*”号，如图3.19所示，更改完成单击“提交”按钮保存修改的数据。3.2.2 表数据操作表数据操作3删除

15、记录删除记录如果要删除一行记录，选中该行，单击 （删除所选行）按钮，之后该行的行号前会出现一个“-”号，如图3.20所示，单击“提交”按钮确认删除。3.2.2 表数据操作表数据操作4撤销操作撤销操作如果需要撤销之前对表中记录所做的操作，只需在单击“提交”按钮之前单击 （回退）按钮即可，如图3.21所示，为撤销上一步的删除操作。3.2.3 执行执行SQL命令命令使用SQL Developer不仅可以以图形界面方式操作数据库表中的记录，还可以直接编辑和运行SQL语句。启动SQL Developer，单击工具栏 按钮的右下箭头选择“myorcl”，界面上将出现命令编辑区，如图3.22所示，在其中输入

16、要运行的SQL语句，如下：CREATE TABLE XSB(学号 char(6) NOT NULL PRIMARY KEY,姓名 char(8) NOT NULL,性别 char(2) DEFAULT 男 NOT NULL,出生时间 date NOT NULL,专业 char(12) NULL,总学分 number(2) NULL,备注 varchar2(200) NULL);3.2.3 执行执行SQL命令命令图3.22 在SQL Developer中运行SQL语句3.2.3 执行执行SQL命令命令这里是以执行创建表的CREATE TABLE命令（稍后在3.3节中详细介绍）为例，操作前须先删除

17、之前已创建的XSB表，输完命令后单击窗口上方的 或 按钮即可执行该SQL语句重新创建XSB表。表建好后，往其中录入样本数据（见附录A）以备后用，如图3.23所示。3. 3 命令方式操作数据库（采用命令方式操作数据库（采用SQL*Plus）3.3.1 创建表创建表在以自己的模式创建表时，必须拥有CREATE TABLE系统权限；在其他用户模式下创建表时，必须拥有CREATE ANY TABLE系统权限。Oracle创建表使用CREATE TABLE语句，基本的语法格式为：CREATE TABLE . ( DEFAULT DEFAULT ,n,n)AS 3.3.1 创建表创建表说明：说明：（1）：

18、用户方案是指该表所属的用户，如果省略则默认为当前登录的用户。（2）DEFAULT：关键字DEFAULT指定某一列的默认值。默认值的数据类型必须与该列的数据类型相匹配，列的长度必须足以容纳这一表达式值。（3）：定义一个完整性约束作为列定义的一部分，该子句的语法为：NOT NULLUNIQUE PRIMARY KEY REFERENCES . ()CHECK()（4）：定义一个完整性约束作为表定义的一部分。（5）AS ：表示将由子查询返回的行插入到所创建的表中，子查询的使用将在4.2.2节中具体介绍。3.3.1 创建表创建表【例3.1】 利用CREATE TABLE命令为XSCJ数据库建立表KCB

19、。表结构参照表3.4。CREATE TABLE KCB(课程号 char(3) NOT NULL PRIMARY KEY,课程名 char(16) NOT NULL,开课学期 number(1) NULL,学时 number(2) NULL, 学分 number(1)NOT NULL)3.3.1 创建表创建表创建完毕，用命令“DESCRIBE KCB;”可查看KCB表结构，如图3.24所示。3.3.1 创建表创建表如果表的主键由两个或多个列构成，则必须使用PRIMARY KEY关键字定义为表的完整性约束，语法格式如下：CREATE TABLE ( DEFAULT ,nPRIMARY KEY(,

20、 ,.n)3.3.1 创建表创建表【例3.2】 利用CREATE TABLE命令为XSCJ数据库建立表CJB。表结构参照表3.5。CREATE TABLE CJB( 学号 char(6) NOT NULL,课程号 char(3) NOT NULL,成绩 number(2) NULL,PRIMARY KEY(学号, 课程号) )同样可用命令“DESCRIBE CJB;”查看CJB表结构。3.3.1 创建表创建表【例3.3】 创建XSB表中计算机专业学生的记录备份表，表名为XS_JSJ。CREATE TABLE XS_JSJAS SELECT * FROM XSBWHERE 专业= 计算机 ;创建

21、完毕，可用SQL Developer查看结果，如图3.25所示，可以看到XS_JSJ表中存储（备份）了XSB表中计算机专业全部11名学生的记录。3.3.2 修改表修改表修改表结构使用ALTER TABLE语句，语法格式为：ALTER TABLE . ADD( DEFAULT 列约束,n) /*增加新列*/ MODIFY( DEFAULT 列约束,n) /*修改已有列属性*/ /*删除列或约束条件*/3.3.2 修改表修改表说明：说明：（1）ADD子句：用于向表中增加一个新列，新的列定义和创建表时定义列的格式一样，一次可添加多个列，中间用逗号隔开。（2）MODIFY子句：用于修改表中某列的属性（

22、数据类型、默认值等）。在修改数据类型时需要注意，如果表中该列所存数据的类型与将要修改的列类型冲突，则会发生错误。例如，原来char类型的列要修改为number类型，而原来列值中有字符型数据“a”，则无法修改。（3）DROP子句：该子句用于从表中删除指定的字段或约束，语法格式为：DROP COLUMN PRIMARY KEY UNIQUE (,n) CONSTRAINT CASCADE 3.3.2 修改表修改表【例3.4】 使用ALTER TABLE语句修改XSCJ数据库中的XS_JSJ表。（1）在表XS_JSJ中增加两列：奖学金等级、等级说明。ALTER TABLE XS_JSJADD (奖学

23、金等级 number(1),等级说明 varchar2(40) DEFAULT 奖金1000元);运行结果如图3.26所示。3.3.2 修改表修改表（2）在XS_JSJ表中修改“等级说明”列的默认值。ALTER TABLE XS_JSJMODIFY (等级说明 DEFAULT 奖金800元 );运行语句后，打开SQL Developer的“编辑表”窗口查看XS_JSJ表的列属性，可见“等级说明”列的默认值已改为“奖金800元”，如图3.27所示。3.3.2 修改表修改表（3）在表XS_JSJ中删除“奖学金等级”和“等级说明”列。ALTER TABLE XS_JSJDROP COLUMN 奖学金

24、等级;ALTER TABLE XS_JSJDROP COLUMN 等级说明;运行结果如图3.28所示。3.3.2 修改表修改表（4）为XS_JSJ表添加主键。ALTER TABLE XS_JSJADD (CONSTRAINT PK_JSJ PRIMARY KEY(学号) );运行语句后，打开SQL Developer的“编辑表”窗口，可以看到“学号”列已被设为主键，如图3.29所示。3.3.3 删除表删除表语法格式：DROP TABLE . 例如要删除表XS_JSJ，使用如下语句：DROP TABLE XS_JSJ;执行结果如图3.30所示。3.3.4 插入记录插入记录1INSERT语句语句插

25、入记录一般使用INSERT语句，语法格式为：INSERT INTO (,n) VALUES(,n)该语句的功能是向指定的表中加入一行，由VALUES指定各列的值。【例3.5】 向XSCJ数据库的表XSB中插入如下的一行：151114 周何骏 计算机 男 1998-09-25 90可以使用如下的SQL语句：INSERT INTO XSB(学号, 姓名, 性别, 出生时间, 专业, 总学分) VALUES(151114, 周何骏, 男,TO_DATE(19980925,YYYYMMDD), 计算机, 90);或者执行下列命令的效果相同：INSERT INTO XSB VALUES(151114,

26、周何骏, 男, 1998-09-25, 计算机, 90, NULL);然后再运行COMMIT命令：COMMIT;3.3.4 插入记录插入记录最后，使用SELECT语句查询是否添加了该行记录：SELECT 学号, 姓名, 性别, 出生时间, 专业, 总学分 FROM XSBWHERE 学号= 151114;运行结果如图3.31所示。3.3.4 插入记录插入记录【例3.6】 向具有默认值字段的表中插入记录。创建一个具有默认值字段的表test：CREATE TABLE test(姓名 char(20) NOT NULL, 专业 varchar2(30) DEFAULT(计算机), 年级 number

27、 NOT NULL);用INSERT向test表中插入一条记录：INSERT INTO test(姓名, 年级) VALUES(周何骏, 3);3.3.4 插入记录插入记录运行结果如图3.32所示。利用INSERT语句还可以把一个表中的部分数据插入到另一个表中，但结果集中每行数据的字段数、字段的数据类型要与被操作的表完全一致，语法格式为：INSERT INTO 3.3.4 插入记录插入记录【例3.7】 用如下的CREATE语句建立表XSB1：CREATE TABLE XSB1( num char(6) NOT NULL, name char(8) NOT NULL, speiality cha

28、r(12) NULL);然后用INSERT语句向XSB1表中插入数据，如下：INSERT INTO XSB1 SELECT 学号, 姓名, 专业 FROM XSB WHERE 姓名= 王林;3.3.4 插入记录插入记录这条INSERT语句将XSB表中姓名为“王林”的所有学生的学号、姓名和专业名列的值插入到XSB1表的各行中。运行结果如图3.33所示。3.3.4 插入记录插入记录2MERGE语句语句在Oracle 12c中有MERGE语句，用于根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在一个表中找到的差异在另一个表中插入、更新或删除行，这种方法可以对两个表进行信息同步。语

29、法格式如下：MERGE INTO USING ON ()WHEN MATCHED THEN UPDATE SET| DELETEWHEN NOT MATCHED THEN INSERT() VALUES()3.3.4 插入记录插入记录【例3.8】 创建表a，将XSB表中的数据添加至该表。创建表a的语句如下：CREATE TABLE a( XH char(6)NOT NULL PRIMARY KEY, XM char(8) NOT NULL, XB char(2) NOT NULL, CSSJ date NOT NULL, ZY char(12) NULL, ZXF number(2) NULL, BZvarchar(200) NULL);进行信息同步使用如下语句：MERGE INTO aUSING XSB ON (a.XH=XSB.学号)WHEN MATCHED THEN UPDATE SET a.XM=XSB.姓名, a.XB=XSB.性别, a.CSSJ=XSB.出生时间,a.ZY=XSB.专业, a.ZXF=XSB.总学分, a.BZ=XSB.备注WHEN NOT MATCHEDTHEN INSERT VALUES(XSB.学号,XSB.姓名,XSB.性别,XSB.出生时间,XSB.专业,XSB.总学分, XSB.备注);