- SSH框架企业级应用实战
- 肖睿 郭泰 王丁磊
- 3673字
- 2025-04-14 04:46:26
任务3 使用SQL语句操作数据表
关键步骤如下。
使用CREATE TABLE创建数据表stuInfo。
使用SELECT语句操作数据表stuInfo。
使用COMMIT、ROLLBACK、SAVEPOINT管理事务。
SQL是高级的结构化查询语言。当用户使用SQL 语句进行数据操作时，只需要提出“做什么”，而不必指明“怎么做”，具体的执行过程由系统自动完成，大大减轻了用户负担。SQL语句是数据库服务器和客户端之间的重要沟通手段，用于存取数据及查询、更新和管理关系型数据库系统。
经过多年的发展，SQL已经成为关系型数据库的标准语言。SQL支持如下类别的命令。
数据定义语言（DDL）：CREATE（创建）、ALTER（更改）、TRUNCATE（截断）和DROP（删除）命令。
数据操纵语言（DML）：INSERT（插入）、SELECT（选择）、DELETE（删除）和UPDATE（更新）命令。
事务控制语言（TCL）：COMMIT（提交）、SAVEPOINT（保存点）和ROLLBACK（回滚）命令。
数据控制语言（DCL）：GRANT（授予）和REVOKE（回收）命令。
1.3.1 使用DDL操作数据表
数据定义语言（DDL）中，CREATE TABLE语句用来创建新表，ALTER TABLE语句用来修改表结构，TRUNCATE TABLE语句用来删除表中所有记录，DROP TABLE语句用来删除表。本节结合以前学过的DDL的知识，主要介绍数据定义语言中较常用的CREATE TABLE命令和相对较陌生的TRUNCATE TABLE命令。
1.CREATE TABLE命令
创建表的语法如下。
CREATE TABLE [schema.]table
(column datatype [, column datatype [, …]] );
其中：
schema表示对象的所有者，即模式的名称。如果用户在自己的模式中创建表，则可以不指定所有者名称。
table表示表的名称。
column表示列的名称。
datatype表示该列的数据类型及其宽度。
创建表时，需要指定唯一的表名称、表内唯一的列名称、列的数据类型及其宽度。
示例3
创建一个stuInfo表，用来存储有关学员的个人信息，如姓名、学号和年龄等。
关键代码
SQL>CREATE TABLE stuInfo /*-创建学员信息表-*/
(
stuNo CHAR(6) NOT NULL, --学号,非空(必填)
stuName VARCHAR2(20) NOT NULL, --学员姓名,非空(必填)
stuAge NUMBER(3,0) NOT NULL, --年龄,非空(必填)
stuID NUMBER(18,0), --身份证号,代表18位数字,小数位数为0
stuSeat NUMBER(2,0) --座位号
);
如果上述命令执行成功，将显示消息“表已创建”。
由此可以看出，表名应该严格遵循下列命名规则。
首字符应该为字母。
不能使用Oracle保留字来为表命名。
表名的最大长度为30个字符。
同一用户模式下的不同表不能具有相同的名称。
可以使用下划线、数字和字母，但不能使用空格和单引号。
提示
Oracle中的表名（还有列名、用户名和其他对象名）不区分大小写，系统会自动转换成大写。
Oracle中也有VARCHAR数据类型，但不建议使用，建议使用VARCHAR2，该数据类型是Oracle标准数据类型。
通过以上学习可以看出，Oracle在创建表时与MySQL基本没有区别。两者之间部分数据类型的默认映射情况如表1-3所示。
表1-3 Oracle和MySQL之间数据类型的默认映射情况
续表
2.TRUNCATE TABLE命令
如果存储在表中的数据不再使用，可以只删除表中的记录而不删除表结构。使用TRUNCATE TABLE 命令将删除表中的所有行且不记录日志，因此与DELETE命令删除表中全部记录相比，既节省资源，执行速度也较快。
TRUNCATE TABLE命令的语法如下。
TRUNCATE TABLE <tablename>;
1.3.2 使用DML操作数据表
数据操纵语言（DML）用于检索、插入和修改数据库信息，是最常用的SQL命令，如INSERT（插入）、UPDATE（更新）、SELECT（选择）、DELETE（删除）。在学习MySQL时已经详细介绍过，这里只做补充介绍。
stuInfo表中的数据如表1-4所示。
表1-4 stuInfo表中的数据
1.从语法的角度介绍DML操作
（1）选择无重复的行
要防止选择重复的行，可以在SELECT命令中包含DISTINCT子句。
示例4
不重复显示所有学员的姓名和年龄。
SQL>SELECT DISTINCT stuName,stuAge
FROM stuInfo;
输出结果：
stuName stuAge
------------------------
王五 15
张三 18
李四 20
张三 20
注意
使用DISTINCT子句后，对筛除结果集中内容全部相同的行仅保留一行。
（2）带条件和排序的SELECT命令
要从表中选择特定的行，可以在 select命令中包含WHERE子句，它只能出现在FROM子句后面，而且只检索符合WHERE条件的行。要根据某个预定义的顺序显示行，可以使用ORDER BY子句，它还可以以升序或降序来排列行和多个列。
示例5
按照姓名升序排序。如果姓名相同，则按照年龄降序排序。
SQL>SELECT stuNo,stuName, stuAge
FROM stuInfo
WHERE stuAge>17
ORDER BY stuName ASC, stuAge DESC;
输出结果：
stuNo stuName stuAge
-----------------------------------
2 李四 20
5 张三 20
4 张三 18
1 张三 18
（3）使用列别名
列别名是为列表达式提供的另一个名称，位于列表达式后面，并显示在列标题中。列别名不会影响列的实际名称。
示例6
使用别名显示姓名、年龄和身份证号。
SQL> SELECT stuName as “姓 名",stuAge as “年 龄", stuID as 身份证号
FROM stuInfo;
输出结果：
姓 名 年 龄 身份证号
--------------------------
张三 18
李四 20
王五 15
张三 18
张三 20
如列别名是含有特殊字符（如空格）的列标题，则使用双引号括起来。
（4）利用现有的表创建新表
Oracle允许利用现有的表创建新表，完成此操作的语法如下。
CREATE TABLE <newtable>
AS
SELECT { * | column（s）}
FROM <oldtable> [WHERE <condition> ];
此命令可以把现有表中的所有记录复制到新表中，也可以仅复制选定的列或只复制结构而不复制记录。
示例7
SQL> CREATE TABLE newStuInfo1
AS
SELECT * FROM stuInfo;
上述语句将创建newStuInfo1表，此表是stuInfo表及其所有记录的完全复制。也可以用选定的列创建新表。下列语句将创建一个名为newStuInfo2的新表，它包括来自stuInfo表的学员姓名、学员编号和年龄的所有记录。
SQL> CREATE TABLE newStuInfo2
AS
SELECT stuName,stuNo,stuAge FROM stuInfo;
还可以仅复制表的结构，而不复制记录。下列语句将创建一个名为newStuInfo3的新表，没有任何记录。
SQL> CREATE TABLE newStuInfo3
AS
SELECT * FROM stuInfo WHERE 1=2;
2.从使用技巧的角度介绍DML操作
（1）查看表中行数
示例8
执行语句：
SQL> SELECT COUNT(*) FROM stuInfo; --效率较低
执行语句：
SQL> SELECT COUNT(1) FROM stuInfo; --效率较高
（2）取出stuName、stuAge列不存在重复数据的记录
示例9
SQL> SELECT stuName,stuAge
FROM stuInfo
GROUP BY stuName,stuAge
HAVING(COUNT(stuName||stuAge) <2);
输出结果：
stuName stuAge
--------------------------
王五 15
李四 20
张三 20
提示
“||”操作符为连接操作符，将在本章SQL操作符部分介绍，功能是将两部分内容连接在一起。因为示例9中COUNT函数的参数只能有一个，所以用连接操作符连接。
（3）删除stuName、stuAge列重复的行（保留一行）
示例10
SQL>DELETE
FROM stuInfo
WHERE ROWID NOT IN(
SELECT MAX(ROWID)
FROM stuInfo
GROUP BY stuName,stuAge
);
删除后查看输出结果：
SQL> SELECT * FROM stuInfo;
stuNo stuName stuAge stuID stuSeat
----------------------------------------
1 张三 18 1
2 李四 20 2
3 王五 15 3
5 张三 20 5
（4）查看当前所有数据量大于100万的用户表的信息
示例11
SELECT table_name
FROM user_all_tables a
WHERE a.num_rows>1000000;
提示
user_all_tables为系统提供的数据视图，使用者可以通过查询该视图获得当前用户表的描述。
1.3.3 使用TCL管理事务
在Oracle数据库中，事务控制语句（TCL）主要由以下部分组成。
（1）COMMIT：提交事务，即永久保存事务中对数据库的修改。
（2）ROLLBACK：回滚事务，即取消对数据库所做的任何修改。
（3）SAVEPOINT：在事务中创建存储点。
（4）ROLLBACK TO <SavePoint_Name>：将事务回滚到存储点。
问答
问题1：何时开启事务？
解答：在Oracle中，事务在上一次事务结束以后，数据“第一次”被修改时自动开启。
问题2：何时结束事务？
解答：有以下两种情况。
（1）数据被提交。
发出COMMIT命令。
执行DDL或DCL语句后，当前事务自动被提交。
与Oracle分离，如退出PL/SQL Developer。
（2）数据被撤销。
发出ROLLBACK命令。
服务器进程异常结束。
DBA停止会话。
示例12
需求说明
事务控制语句应用举例，创建部门表（dept），插入部门记录。
关键代码
--执行步骤一：创建dept 表
SQL>CREATE TABLE dept
(
deptno NUMBER(2) PRIMARY KEY, --部门编号
dname VARCHAR2(14), --部门名称
loc VARCHAR2(13) --地址
);
--执行步骤二：插入数据
SQL>INSERT INTO dept VALUES (10,’ACCOUNTING’,’NEW YORK’);
SQL>INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
SQL>INSERT INTO dept VALUES (30,'SALES','CHICAGO');
SQL>INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
SQL>COMMIT;
--执行步骤三：操作dept 表
SQL>INSERT INTO dept VALUES(50,'a',NULL);
SQL>INSERT INTO dept VALUES(60,'b',NULL);
SQL>SAVEPOINT a;
SQL>INSERT INTO dept VALUES(70,'c',NULL);
SQL>ROLLBACK TO SAVEPOINT a;
--执行步骤四：查看dept 表,有50、60 号部门
SQL>SELECT * FROM dept;
--执行步骤五：回滚
SQL>ROLLBACK;--没有50、60 号部门
--执行步骤六：查看dept 表
SQL>SELECT * FROM dept;
分析
（1）回滚到保存点“a”，表示保存点以后的所有数据操作取消，故只插入了两个部门。
（2）事务没有结束，必须再执行COMMIT或者ROLLBACK命令来结束事务。
注意
执行了3个INSERT语句，如果要提交，则只能提交所有的SQL语句，不能部分提交；
如果要回滚，则可以利用“事务保存点”来做局部回滚，此时事务并没有结束。
1.3.4 使用DCL控制权限
数据控制语言为用户提供权限控制命令。数据库对象（如表）的所有者对这些对象拥有控制权限。所有者可以根据自己的意愿决定其他用户如何访问对象，授予其他用户权限（INSERT、SELECT、UPDATE…），使他们可以在其权限范围内执行操作。例如，如果一个用户仅被授予对某个表的SELECT权限，那么他只可以查看数据，而不能执行其他任何DML 操作。授予的权限还可以由所有者随时撤销。第2章会详细介绍数据控制语言。
技能训练
上机练习2——创建员工表，并对表中数据进行插入查询操作
训练要点
（1）创建员工表。
（2）插入数据。
（3）添加约束。
（4）添加列。
（5）删除列。
（6）查询数据。
需求说明
使用System用户登录，创建员工表employee，并为员工编号创建主键约束，部门编号列创建外键约束。根据提供的资料插入数据，显示员工表中薪水从高到低排序的记录。
实现思路及关键代码
（1）使用System用户连接orcl数据库。
（2）创建employee表。
SQL>CREATE TABLE employee /*-创建员工信息表-*/
(empno NUMBER(4)NOT NULL, --员工编号
ename VARCHAR2(10), --员工姓名
job VARCHAR2(9), --员工工种
mgr NUMBER(4), --上级经理编号
hiredate DATE, --受雇日期
sal NUMBER(7,2), --员工薪水
comm NUMBER(7,2), --福利
deptno NUMBER(2) --部门编号
);
（3）插入数据。可以直接插入数据，也可以利用Scott用户下emp表中的数据进行插入。
插入Scott.emp表数据的代码如下。
INSERT INTO employee SELECT * FROM SCOTT.emp;
（4）添加约束。将员工编号作为主键，部门编号作为外键与部门表相关联。
外键约束的代码如下。
ALTER TABLE employee
ADD CONSTRAINT FK_deptno
FOREIGN KEY(deptno) REFERENCES dept(deptno);
（5）向employee表添加empTel_no和empAddress两列。创建empTel_no列以存储员工的电话号码，empAddress列以存储地址。
添加列的代码如下。
ALTER TABLE employee
ADD (empTel_no VARCHAR2 (12),
empAddress VARCHAR2(20));
（6）删除empTel_no和empAddress两列。
（7）按照薪水从高到低显示数据。
上机练习3——根据创建的员工表，实现分页查询
训练要点
分页查询。
需求说明
使用System用户登录，根据employee表的现有记录，查询员工表中按薪水从高到低排序的第5～9条记录。
实现思路及关键代码
（1）使用System用户连接orcl数据库。
（2）对员工表倒序排序并生成临时结果集A。
SQL>SELECT *
FROM (SELECT *
FROM employee
ORDER BY sal DESC) A;
（3）利用ROWNUM为临时结果集A给出每行序号并生成临时结果集B。
（4）对临时结果集B进行查询，查找序号为5～9的记录。
思考
该练习相对较复杂，一共用到3层子查询。当编写程序时，要认真思考每一步、每一个语句的原理。例如，ORDER BY语句是针对结果集进行排序，即先产生查询结果集，再排序。在上面的练习中就用到了这个原理，你是否已经感受到了呢？如果将SQL语句写成
SELECT employee.*, ROWNUM FROM employee ORDER BY sal DESC;
得到的结果不符合上面练习的需求就是违背了这个原理。