第1章 多表查询
1.1. 等连接
通过两个表具有相同意义的列,建立连接条件。查询结果只显示两个列中的值是等值条件的行数据。表中同名列被选择时必须添加表名前缀进行修饰 否则无法确定这一列是属于哪个表
HR@ENMOEDU> conn hr/hr
Connected.
HR@ENMOEDU>SELECT count(*)
FROM departments d, employees t
WHERE d.department_id = t.department_id;
COUNT(*)
----------
106
HR@ENMOEDU> --注：下面sql语句中 INNER 是可以省略不写的。
HR@ENMOEDU>SELECT COUNT(*)
FROM departments d
INNER JOIN employees t
ON d.department_id = t.department_id;
COUNT(*)
----------
106
HR@ENMOEDU>SELECT COUNT(*)
FROM departments d
JOIN employees t
USING (department_id);
COUNT(*)
----------
106
HR@ENMOEDU>
表名前缀（因为两张表都存在字段department_id，故报此错误）
HR@ENMOEDU>select department_id, employee_id, first_name, department_name
from employees d, departments t
where d.department_id = t.department_id;
select department_id, employee_id, first_name, department_name
*
ERROR at line 1:
ORA-00918: column ambiguously defined
HR@ENMOEDU>select d.department_id, employee_id, first_name, department_name
from employees d, departments t
where d.department_id = t.department_id;
DEPARTMENT_ID EMPLOYEE_ID FIRST_NAME DEPARTMENT_NAME
----------------------- -------------------- ------------------------------
10 200 Jennifer Administration
20 201 Michael Marketing
20 202 Pat Marketing
30 114 Den Purchasing
30 115 Alexander Purchasing
30 116 Shelli Purchasing
30 117 Sigal Purchasing
30 118 Guy Purchasing
30 119 Karen Purchasing
40 203 Susan Human Resources
50 120 Matthew Shipping
HR@ENMOEDU>
请问：下面两个sql的结果是否相同？为什么？相同，and是并行满足，而where是过滤只要满足这一个条件。
SQL1:
SELECT *
FROM departments d
JOIN employees t
ON d.department_id= t.department_id
AND d.department_name = 'SALES';
SQL2:
FROM departments d
JOIN employees t
ON d.department_id = t.department_id
WHERE d.department_name = 'SALES';
1.2. 自然连接
自然连接只发生在两个表中有相同名字和数据类型的列上，如果单纯的列名相同而类型不同，那么会报语法错误。
HR@ENMOEDU>desc departments
Name Null? Type
------------------------------ -------- ----------------------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
HR@ENMOEDU>desc employees
Name Null? Type
--------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
HR@ENMOEDU> SELECT d.department_name, d.location_id, t.first_name
FROM departments d NATURAL JOIN employees t;
DEPARTMENT_NAME LOCATION_ID FIRST_NAME
------------------------------ ----------- --------------------
Executive 1700 Neena
Executive 1700 Lex
IT 1400 Bruce
IT 1400 David
IT 1400 Valli
IT 1400 Diana
Finance 1700 Daniel
Finance 1700 John
Finance 1700 Ismael
Finance 1700 Jose Manuel
Finance 1700 Luis
Purchasing 1700 Alexander
Purchasing 1700 Shelli
Purchasing 1700 Sigal
Purchasing 1700 Guy
Purchasing 1700 Karen
Shipping 1500 Laura
Shipping 1500 Mozhe
HR@ENMOEDU>
1.3. using子句
1) using子句的用法：
HR@ENMOEDU>select d.department_name,d.location_id,t.first_name
from departments d join employees t using (department_id);
DEPARTMENT_NAME LOCATION_ID FIRST_NAME
------------------------------ ----------- --------------------
Administration 1700 Jennifer
Marketing 1800 Pat
Marketing 1800 Michael
Purchasing 1700 Sigal
Purchasing 1700 Karen
Purchasing 1700 Shelli
Purchasing 1700 Den
Purchasing 1700 Alexander
Purchasing 1700 Guy
Human Resources 2400 Susan
Shipping 1500 Kevin
Shipping 1500 Jean
Shipping 1500 Adam
Shipping 1500 Timothy
Shipping 1500 Ki
Shipping 1500 Girard
HR@ENMOEDU>
2) using子句的注意事项
HR@ENMOEDU> select d.department_name,d.location_id,t.first_name
from departments d join employees t
using (department_id)
where department_id=10;
DEPARTMENT_NAME LOCATION_ID FIRST_NAME
------------------------------ ----------- --------------------
Administration 1700 Jennifer
HR@ENMOEDU>select d.department_name,d.location_id,t.first_name
from departments d join employees t
using (department_id) where d.department_id=10;
using (department_id) where d.department_id=10
*
ERROR at line 3:
ORA-25154: column part of USING clause cannot have qualifier
HR@ENMOEDU>
通过上面的例子可以得出，using子句中，相同名字的字段不允许有任何限定符，否则将报ORA-25154:错误，此限制同样适用于natural join（自然连接）
1.4. 不等连接
A表中的某列数据和B表中一列或多列的关系是非等值关系,大于,小于,不等于,等条件都属于不等连接的范畴
就是排除完全相等条件以外的 >,<,!=, <=, >=, between and主要在于不同表之间显示特定范围的信息(也可以理解成包含关系)
HR@ENMOEDU>create table job_grades (s_grade varchar2(2),low_salary number,high_salary number);
insert into job_grades (s_grade, low_salary, high_salary) values ('A', 0, 10000);
insert into job_grades (s_grade, low_salary, high_salary) values ('B', 10001, 20000);
insert into job_grades (s_grade, low_salary, high_salary) values ('C', 20001, 30000);
HR@ENMOEDU> commit;
Commit complete.
HR@ENMOEDU>select first_name,salary,s_grade
from employees a,job_grades b
where salary between low_salary and high_salary and b.s_grade='B';
FIRST_NAME SALARY S_
-------------------- ---------- --
Neena 17000 B
Lex 17000 B
Nancy 12008 B
Den 11000 B
John 14000 B
HR@ENMOEDU>
between A and B (大于等于A并且小于等于B)
HR@ENMOEDU> with t as (
select rownum id from dual connect by rownum<=10)
select id from t where id between 2 and 8;
ID
---------
2
3
4
5
6
7
8
7 rows selected.
HR@ENMOEDU>
1.5. 自连接
数据都来自一张表,所以在from子句中需要对表添加别名,添加表别名后才能合法化的引用表中的列名。本质就是将一张表虚拟成了两张表
HR@ENMOEDU>SELECT d.last_name || ' works for ' || t.last_name
FROM employees d, employees t
WHERE d.manager_id = t.employee_id;
D.LAST_NAME||'WORKSFOR'||T.LAST_NAME
-------------------------------------------------------------
Smith works for Cambrault
Ozer works for Cambrault
Kumar works for Cambrault
……
中间省略n行
……
Hutton works for Zlotkey
Grant works for Zlotkey
Abel works for Zlotkey
106 rows selected.
HR@ENMOEDU>
1.6. 外连接
选择出满足等连接条件的及其以外的记录。(+)修饰符号用法:放置在选出结果只包含等连接的列后,则另一列的结果就是等值行+非等值行
1.6.1. 左外连接
在等连接的基础上，还包含左表中所有不符合条件的数据行，并在其中的右表列填写NULL
1) Oracle自己的写法（不推荐）：
HR@ENMOEDU>col id for 99
HR@ENMOEDU>col name for a4
HR@ENMOEDU> with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1,t2 where t1.id=t2.id(+);
ID NAME ID NAME
----- -------- ----- --------
2 b 2 bb
3 c 3 cc
1 a
HR@ENMOEDU>
2) SQL99写法
HR@ENMOEDU> with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1 left outer join t2 on t1.id=t2.id;
ID NAME ID NAME
----- -------- ----- --------
2 b 2 bb
3 c 3 cc
1 a
HR@ENMOEDU>
注：outer 可以省略
请问：下面的sql结果是否相同，思考为什么？不一样，and是同时满足，而where是只满足。
SQL1：
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1 left join t2 on t1.id=t2.id and t2.id=2;
SQL2：
with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1 left join t2 on t1.id=t2.id where t2.id=2;
1.6.2. 右外连接
在等连接的基础上，还包含右表中所有不符合条件的数据行，并在其中的左表列填写NULL
1) Oracle自己的写法（不推荐）：
HR@ENMOEDU> with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1,t2 where t1.id(+)=t2.id;
ID NAME ID NAME
----- -------- ----- --------
2 b 2 bb
3 c 3 cc
4 dd
HR@ENMOEDU>
2) SQL99写法
HR@ENMOEDU> with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select t1.id,t1.name,t2.id,t2.name from t1 right outer join t2 on t1.id=t2.id;
ID NAME ID NAME
----- -------- ----- --------
2 b 2 bb
3 c 3 cc
4 dd
HR@ENMOEDU>
1.6.3. 全外连接
在等连接的基础上，还包含两个表中所有不符合条件的数据行，并在其中的左表、和右表列填写NULL
HR@ENMOEDU>col n1 for a5
HR@ENMOEDU>col n2 for a5
HR@ENMOEDU>with
t1 as (
select 1 as id,'a' as name from dual union all
select 2,'b' from dual union all
select 3,'c' from dual),
t2 as (
select 2 as id,'bb' as name from dual union all
select 3,'cc' from dual union all
select 4,'dd' from dual)
select nvl(t1.id,t2.id) as id ,t1.name as n1, t2.name as n2
from t1 full join t2 on t1.id=t2.id order by 1 asc;
ID N1 N2
----- ----- -----
1 a
2 b bb
3 c cc
4 dd
HR@ENMOEDU>
1.7. 交叉连接（笛卡尔）
将两个表的所有行进行组合，连接后的行数为两个表的乘积数。
往往有人会说要避免笛卡尔积，上学的时候，老师也把它说的很恐怖，其实则不然，我倒是认为如若弄明白它的原理，则可合理利用它。
1) 写法一：
HR@ENMOEDU> WITH t1 AS (SELECT 'range' AS r FROM dual
UNION ALL SELECT 'list' FROM dual),
t2 AS (SELECT 'range' AS l FROM dual
UNION ALL SELECT 'list' FROM dual),
t3 AS (SELECT 'hash' AS h from dual)
SELECT r,l FROM t1 CROSS JOIN t2
UNION ALL
SELECT l,h FROM t2 CROSS JOIN t3;
R L
----- -----
range range
range list
list range
list list
range hash
list hash
6 rows selected.
HR@ENMOEDU>
2) 写法二：
HR@ENMOEDU> WITH t1 AS (SELECT 'range' AS r FROM dual
UNION ALL SELECT 'list' FROM dual),
t2 AS (SELECT 'range' AS l FROM dual
UNION ALL SELECT 'list' FROM dual),
t3 AS (SELECT 'hash' AS h from dual)
SELECT r,l FROM t1,t2
UNION ALL
SELECT l,h FROM t2,t3;
R L
----- -----
range range
range list
list range
list list
range hash
list hash
6 rows selected.
HR@ENMOEDU>
第2章 创建和管理表
语法：
CREATE TABLE [schema.]table
(columndatatype [DEFAULT expr][, ...]);
2.1. 创建表
HR@ENMOEDU>CREATE TABLE dept2
(department_id NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13));
Table created.
HR@ENMOEDU>
2.2. 日期数据类型
TIMESTAMP 带小数秒的日期型
INTERVAL YEAR TO MONTH 作为年和月的时间间隔存储
INTERVAL DAY TO SECOND 作为天、小时、分和秒的时间间隔存储（DAY, HOUR, MINUTE, SECOND）
HR@ENMOEDU>create table ha (id number,x INTERVAL YEAR TO MONTH,y INTERVAL DAY TO SECOND );
Table created.
HR@ENMOEDU>
--x字段插入：5年
--y字段插入：2天
HR@ENMOEDU>insert into ha values(1,INTERVAL '5' year,INTERVAL '2' day);
1 row created.
HR@ENMOEDU>
--x字段插入：10个月
--y字段插入：2小时
HR@ENMOEDU>insert into ha values(2,INTERVAL '10' month,INTERVAL '2' hour);
1 row created.
HR@ENMOEDU>
--x字段插入：2年零6个月
--y字段插入：1天零12小时30分钟1秒
HR@ENMOEDU>insert into ha values(3,INTERVAL '2-6' YEAR TO MONTH,INTERVAL '1 12:30:01' DAY TO SECOND);
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
--利用系统时间加上x、y字段来验证这两个数据类型
HR@ENMOEDU>select id,sysdate,sysdate+x,sysdate+y from ha;
ID SYSDATE SYSDATE+X SYSDATE+Y
--- ------------------- ------------------- -------------------
1 2012-09-09 13:19:32 2017-09-09 13:19:32 2012-09-11 13:19:32
2 2012-09-09 13:19:32 2013-07-09 13:19:32 2012-09-09 15:19:32
3 2012-09-09 13:19:32 2015-03-09 13:19:32 2012-09-11 01:49:33
HR@ENMOEDU>
2.3. 用子查询语法创建表
HR@ENMOEDU>create table dept3 as select * from departments;
Table created.
HR@ENMOEDU>
HR@ENMOEDU>select count(*) from dept3;
COUNT(*)
----------
27
HR@ENMOEDU>desc dept3;
Name Null? Type
--------------------------------- -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
HR@ENMOEDU>
2.4. 修改表
1) 增加字段
HR@ENMOEDU>alter table dept3 add (job_id number(5));
Table altered.
HR@ENMOEDU>desc dept3;
Name Null? Type
------------------------------ -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
JOB_ID NUMBER(5)
HR@ENMOEDU>
2) 修改字段大小
HR@ENMOEDU>alter table dept3 modify (job_id number(10));
Table altered.
HR@ENMOEDU>desc dept3;
Name Null? Type
--------------------------------- -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
JOB_ID NUMBER(10)
HR@ENMOEDU>
3) 删除字段
HR@ENMOEDU>alter table dept3 drop column job_id;
Table altered.
HR@ENMOEDU>desc dept3;
Name Null? Type
--------------------------------- -------- ----------------------------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
HR@ENMOEDU>
2.5. 字段UNUSED
UNUSED 选项标记一个或多个未使用的列。
特别注意：unused是不可逆的。
那么为什么还要这么做呢，主要还是因为，这个动作是对数据字典进行了删除，性能非常的好，对数据库没什么压力。如果数据库压力很大，同时又不想要某个字段了，那么就可以用这种方式，等压力小了，再做清除的处理
1) 创建测试表test（两个字段x和y）并插入一条记录
HR@ENMOEDU>create table test(x number(1),y number(2));
Table created.
HR@ENMOEDU>insert into test values(1,2);
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
2) 将字段y设置为unused
HR@ENMOEDU> alter table test set unused (y);
Table altered.
HR@ENMOEDU>
3) 查看表，y字段已经消失
HR@ENMOEDU>select * from test;
X
----------
1
HR@ENMOEDU>
4) 查看字典表中，表设置为unused字段个数，由于unused是不可逆的，所以，此处只显示字段个数，不显示具体的字段
desc user_unused_col_tabs
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE_NAME NOT NULL VARCHAR2(30)
COUNT NUMBER
HR@ENMOEDU> select * from user_unused_col_tabs;
TABLE_NAME COUNT
------------------------------ ----------
TEST 1
HR@ENMOEDU>
5) 我们可以加入一个字段y（varchar2类型），查看test，y字段创建成功
HR@ENMOEDU>alter table test add(y varchar2(10));
Table altered.
HR@ENMOEDU>select * from test;
X Y
---------- ----------
1
HR@ENMOEDU>
6) 删除设置为unused的字段
HR@ENMOEDU>alter table test drop unused COLUMNS;
Table altered.
HR@ORA11GR2>select * from user_unused_col_tabs;
no rows selected
2.6. 删除表
HR@ENMOEDU>drop table test;
Table dropped.
HR@ENMOEDU>
注：此种方式删除后，是进入回收站的，如果不进入回收站直接删除的话，那么在删除的时候加上purge子句，详见《闪回》章节。
2.7. 改变对象的名字
1) 查看当前用户下所有表对象
HR@ENMOEDU>select tname from tab where tabtype='TABLE';
TNAME
------------------------------
EMPLOYEES
DEPARTMENTS
DEPT2
......
HR@ENMOEDU>
2) 重命名DEPT2为DEPT5
HR@ENMOEDU>rename DEPT2 to DEPT5;
Table renamed.
3) 再次查看，DEPT2已经不见了，已经重命名为DEPT5
HR@ENMOEDU>select tname from tab where tabtype='TABLE';
TNAME
------------------------------
EMPLOYEES
DEPARTMENTS
DEPT5
......
HR@ENMOEDU>
2.8. 截断表
? TRUNCATE TABLE 语句
- 删除表中所有的行
- 释放该表所使用的存储空间，降低高水位
? DDL操作，不能回退用 TRUNCATE 删除的行
? 如果想删除某几行，那么可以用 DELETE 语句删除行
1) 创建测试表，并查看分配的区
HR@ENMOEDU>create table tt_tab as select * from all_objects;
Table created.
HR@ENMOEDU>col SEGMENT_NAME for a12
HR@ENMOEDU>col SEGMENT_TYPE for a12
HR@ENMOEDU>col TABLESPACE_NAME for a15
HR@ENMOEDU>col EXTENT_ID for 999999999
HR@ENMOEDU>col BLOCKS for 999999
HR@ENMOEDU>set pagesize 300
HR@ENMOEDU>
select segment_name,segment_type,tablespace_name,extent_id,bytes/1024 as "size(k)",blocks from user_extents where segment_name=upper('tt_tab');
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID size(k) BLOCKS
------------ ------------ --------------- ---------- ---------- -------
TT_TAB TABLE USERS 0 64 8
TT_TAB TABLE USERS 1 64 8
TT_TAB TABLE USERS 2 64 8
TT_TAB TABLE USERS 3 64 8
TT_TAB TABLE USERS 4 64 8
TT_TAB TABLE USERS 5 64 8
TT_TAB TABLE USERS 6 64 8
TT_TAB TABLE USERS 7 64 8
TT_TAB TABLE USERS 8 64 8
TT_TAB TABLE USERS 9 64 8
TT_TAB TABLE USERS 10 64 8
TT_TAB TABLE USERS 11 64 8
TT_TAB TABLE USERS 12 64 8
TT_TAB TABLE USERS 13 64 8
TT_TAB TABLE USERS 14 64 8
TT_TAB TABLE USERS 15 64 8
TT_TAB TABLE USERS 16 1024 128
TT_TAB TABLE USERS 17 1024 128
TT_TAB TABLE USERS 18 1024 128
TT_TAB TABLE USERS 19 1024 128
TT_TAB TABLE USERS 20 1024 128
TT_TAB TABLE USERS 21 1024 128
22 rows selected.
HR@ENMOEDU>
2) DDL（truncate）操作后，不能回退用 TRUNCATE 删除的行，因为是DDL操作，无需要显示提交，当然，回滚也无济于事。
HR@ENMOEDU>truncate table tt_tab;
Table truncated.
HR@ENMOEDU>select count(*) from tt_tab;
COUNT(*)
----------
0
HR@ENMOEDU>
--验证DDL语句无法rollback
HR@ENMOEDU>rollback;
Rollback complete.
HR@ENMOEDU>select count(*) from tt_tab;
COUNT(*)
----------
0
HR@ENMOEDU>
3) 降低高水位，我们发现分配的区已经全部收回
HR@ENMOEDU>select segment_name,segment_type,tablespace_name,extent_id,bytes/1024 as "size(k)",blocks from user_extents where segment_name=upper('tt_tab');
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID size(k) BLOCKS
------------ ------------ --------------- ---------- ---------- -------
TT_TAB TABLE USERS 0 64 8
HR@ENMOEDU>
自测题：
请大家按照以上的方法自行验证用delete方式清空表后（delete from tablename），区的分配情况。
第3章 内置约束
3.1. NOT NULL
注：not null约束只能在列级定义。
HR@ENMOEDU>create table t_notnull
(x number not null,
y number constraint nn_tnotnull_y not null
);
Table created.
HR@ENMOEDU>select constraint_name,constraint_type,search_condition
from user_constraints
where table_name=upper('t_notnull');
CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION
--------------- --------------- -------------------------
SYS_C0011054 C "X" IS NOT NULL
NN_TNOTNULL_Y C "Y" IS NOT NULL
3.2. UNIQUE
唯一键约束
HR@ENMOEDU>create table student1
(sno number(5) not null,
sname varchar2(20) not null constraint uk_student_sname1 unique,
idcard varchar2(18),
createtime date default sysdate not null,
constraint uk_student_idcard1 unique(idcard)
);
Table created.
HR@ENMOEDU>
HR@ENMOEDU>create table student2
(sno number(5) not null,
sname varchar2(20) not null unique,
idcard varchar2(18),
createtime date default sysdate not null,
constraint uk_student_idcard2 unique(idcard)
);
Table created.
HR@ENMOEDU>create table student3
(sno number(5) not null,
sname varchar2(20) not null unique,
idcard varchar2(18),
createtime date default sysdate not null,
unique(idcard)
);
Table created.
HR@ENMOEDU>col table_name for a12
HR@ENMOEDU>col column_name for a15
HR@ENMOEDU>col constraint_name for a20
HR@ENMOEDU>select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('student_');
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
------------ --------------- --------------------
STUDENT3 IDCARD SYS_C0011070
STUDENT3 SNAME SYS_C0011069
STUDENT3 CREATETIME SYS_C0011068
STUDENT3 SNAME SYS_C0011067
STUDENT3 SNO SYS_C0011066
STUDENT2 IDCARD UK_STUDENT_IDCARD2
STUDENT2 SNAME SYS_C0011064
STUDENT2 CREATETIME SYS_C0011063
STUDENT2 SNAME SYS_C0011062
STUDENT2 SNO SYS_C0011061
STUDENT1 IDCARD UK_STUDENT_IDCARD1
STUDENT1 SNAME UK_STUDENT_SNAME1
STUDENT1 CREATETIME SYS_C0011058
STUDENT1 SNAME SYS_C0011057
STUDENT1 SNO SYS_C0011056
15 rows selected.
HR@ENMOEDU>
3.3. PRIMARY KEY
主键约束
1) 行级定义主键，系统分配主键名称
HR@ENMOEDU>CREATE TABLE t_pk1
(
sno NUMBER(5) primary key,
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>
2) 表级定义主键，自定义主键名称
HR@ENMOEDU>CREATE TABLE t_pk2
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE,
constraint pk_t_pk2_sno primary key(sno)
);
Table created.
HR@ENMOEDU>
3) 表级定义主键，系统分配主键名称
HR@ENMOEDU>CREATE TABLE t_pk3
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE,
primary key(sno)
);
Table created.
HR@ENMOEDU>
4) 行级定义主键，自定义主键名称，并且指定索引使用的表空间
HR@ENMOEDU>CREATE TABLE t_pk4
(
sno NUMBER(5) constraint pk_t_pk4_sno primary KEY using index tablespace example,
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>
5) 表级定义主键，自定义主键名称，并且指定索引使用的表空间
HR@ENMOEDU>CREATE TABLE t_pk5
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE,
constraint pk_t_pk5_sno primary key(sno) using index tablespace example
);
Table created.
HR@ENMOEDU>
6) 查看刚刚建表的主键情况
HR@ENMOEDU>select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('t_pk_');
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
---------- -------------- --------------------
T_PK1 SNO SYS_C0011132
T_PK2 SNO PK_T_PK2_SNO
T_PK3 SNO SYS_C0011134
T_PK4 SNO PK_T_PK4_SNO
T_PK5 SNO PK_T_PK5_SNO
HR@ENMOEDU>
7) 查看主键自动创建索引情况
HR@ENMOEDU>select index_name,uniqueness,tablespace_name from user_indexes where table_name like upper('t_pk_');
INDEX_NAME UNIQUENES TABLESPACE_NAME
-------------------------- --------- ------------------------------
SYS_C0011132 UNIQUE USERS
PK_T_PK2_SNO UNIQUE USERS
SYS_C0011134 UNIQUE USERS
PK_T_PK4_SNO UNIQUE EXAMPLE
PK_T_PK5_SNO UNIQUE EXAMPLE
HR@ENMOEDU>
8) 清理环境
HR@ENMOEDU>
drop table t_pk1 purge;
drop table t_pk2 purge;
drop table t_pk3 purge;
drop table t_pk4 purge;
drop table t_pk5 purge;
Table dropped.
Table dropped.
Table dropped.
Table dropped.
Table dropped.
HR@ENMOEDU>
9) 创建测试表t_pk6，命令行增加主键，自定义主键名称
HR@ENMOEDU>CREATE TABLE t_pk6
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>alter table t_pk6 add constraint pk_t_pk6_sno primary key (sno);
Table altered.
HR@ENMOEDU>
10) 创建测试表t_pk7，命令行增加主键，自定义主键名称，并且指定自动创建索引的表空间
HR@ENMOEDU>CREATE TABLE t_pk7
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>
HR@ENMOEDU>
HR@ENMOEDU>alter table t_pk7 add constraint pk_t_pk7_sno primary key (sno)using index tablespace example;
Table altered.
HR@ENMOEDU>
11) 创建测试表t_pk8，命令行增加主键，自定义主键名称，并且指定创建索引的名称、类型及表空间
HR@ENMOEDU>CREATE TABLE t_pk8
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>
HR@ENMOEDU>alter table t_pk8 add constraint pk_t_pk8_sno primary key (sno) using index (create unique index un_t_pk8_sno on t_pk8(sno) tablespace example);
Table altered.
HR@ENMOEDU>
12) 创建测试表t_pk6，t_pk7，t_pk8，主键情况
HR@ENMOEDU>select table_name,column_name,constraint_name from user_cons_columns where table_name like upper('t_pk_');
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME
----------- ------------ --------------------
T_PK6 SNO PK_T_PK6_SNO
T_PK7 SNO PK_T_PK7_SNO
T_PK8 SNO PK_T_PK8_SNO
HR@ENMOEDU>
13) 创建测试表t_pk6，t_pk7，t_pk8，索引情况
HR@ENMOEDU>select index_name,uniqueness,tablespace_name from user_indexes where table_name like upper('t_pk_');
INDEX_NAME UNIQUENES TABLESPACE_NAME
-------------- --------- ---------------
PK_T_PK6_SNO UNIQUE USERS
PK_T_PK7_SNO UNIQUE EXAMPLE
UN_T_PK8_SNO UNIQUE EXAMPLE
HR@ENMOEDU>
14) 主键的索引并一定都是唯一索引，也可以是普通的B*tree索引
HR@ENMOEDU>CREATE TABLE t_pk9
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE
);
Table created.
HR@ENMOEDU>alter table t_pk9 add constraint pk_t_pk9_sno primary key (sno) using index (create index un_t_pk9_sno on t_pk9(sno) tablespace example);
Table altered.
HR@ENMOEDU>select index_name,uniqueness,tablespace_name from user_indexes where table_name like upper('t_pk_');
INDEX_NAME UNIQUENES TABLESPACE_NAME
-------------- --------- ---------------
PK_T_PK6_SNO UNIQUE USERS
PK_T_PK7_SNO UNIQUE EXAMPLE
UN_T_PK6_SNO UNIQUE EXAMPLE
UN_T_PK9_SNO NONUNIQUE EXAMPLE
HR@ENMOEDU>
3.4. FOREIGN KEY
外键约束
语法：
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id)
[ON DELETE {CASCADE|SET NULL}]
FOREIGN KEY: 在表约束级别，定义子表中的列。
REFERENCES: 确定父表中的表和列。
ON DELETE CASCADE: 当父表中的行被删除时，删除子表中相依赖的行(级联删除)
ON DELETE SET NULL: 当父表的值被删除时，转换外键值为空。
1) 清理测试环境
HR@ENMOEDU>drop table student purge;
Table dropped.
HR@ENMOEDU>
2) 创建籍贯表，插入测试数据
HR@ENMOEDU>create table nativeplace
(
nid number(2) primary key, --主键id
np_name varchar2(20) not null --籍贯名称
);
Table created.
HR@ENMOEDU>insert into nativeplace values(1,'heilongjiang');
1 row created.
HR@ENMOEDU>insert into nativeplace values(2,'jilin');
1 row created.
HR@ENMOEDU>insert into nativeplace values(3,'liaoning');
1 row created.
HR@ENMOEDU>insert into nativeplace values(4,'beijing');
1 row created.
HR@ENMOEDU>commit;
HR@ENMOEDU>select * from nativeplace;
NID NP_NAME
---------- --------------------
1 heilongjiang
2 jilin
3 liaoning
4 beijing
HR@ENMOEDU>
3) 创建学生表，插入测试数据
HR@ENMOEDU>CREATE TABLE student
(
sno NUMBER(5) constraint pk_student primary KEY using index, --学生编号（主键）
sname VARCHAR2(20) not null, --学生名称
idcard VARCHAR2(18) not null, --身份证号
createtime DATE default SYSDATE not null, --创建时间
native_place number(2), --籍贯id
constraint fk_student foreign key (native_place) REFERENCES nativeplace (nid) ON DELETE SET NULL
);
Table created.
HR@ENMOEDU>
insert into student values(1,'zhangsan','123456789123456780',default,1);
1 row created.
HR@ENMOEDU>
insert into student values(2,'lisi','123456789123456781',default,2);
1 row created.
HR@ENMOEDU>
insert into student values(3,'wangwu','123456789123456782',default,3);
1 row created.
HR@ENMOEDU>
insert into student values(4,'zhaoliu','123456789123456783',default,4);
1 row created.
HR@ENMOEDU>col sno for 999
HR@ENMOEDU>col sname for a10
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
---- ---------- ------------------ ------------------- ------------
1 zhangsan 123456789123456780 2012-09-14 16:06:06 1
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13 3
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
HR@ENMOEDU>
4) 创建课程表，插入测试数据
HR@ENMOEDU>create TABLE course
(
cno NUMBER(5) CONSTRAINT pk_course PRIMARY KEY NOT NULL, --课程编号（主键）
cname VARCHAR2(20) NOT NULL --课程名称
);
Table created.
HR@ENMOEDU>insert into course VALUES(1,'Oracle');
1 row created.
HR@ENMOEDU>insert into course VALUES(2,'Java');
1 row created.
HR@ENMOEDU>insert into course VALUES(3,'C++');
1 row created.
HR@ENMOEDU>select * from course;
CNO CNAME
--------- --------------
1 Oracle
2 Java
3 C++
HR@ENMOEDU>
5) 创建成绩表，插入测试数据
HR@ENMOEDU>create TABLE sc
(
sno NUMBER(5), --学生编号
cno NUMBER(5), --课程编号
grade NUMBER(4,1), --成绩
constraint pk_sc primary key (sno,cno),
constraint fk_sc_sno foreign key (sno) REFERENCES student (sno) ON DELETE CASCADE,
constraint fk_sc_cno foreign key (cno) REFERENCES course (cno)
);
Table created.
HR@ENMOEDU>
insert into sc values(1,1,91);
insert into sc values(1,2,92);
insert into sc values(1,3,93);
insert into sc values(2,1,88);
insert into sc values(2,2,77);
insert into sc values(2,3,99);
insert into sc values(3,1,65);
insert into sc values(3,2,75);
insert into sc values(3,3,85);
insert into sc values(4,1,80);
insert into sc values(4,2,88);
insert into sc values(4,3,89);
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>select * from sc;
SNO CNO GRADE
---- -------- ---------
1 1 91
1 2 92
1 3 93
2 1 88
2 2 77
2 3 99
3 1 65
3 2 75
3 3 85
4 1 80
4 2 88
4 3 89
12 rows selected.
HR@ENMOEDU>
6) 测试，子表插入父表不存在的数据的时候，报错的效果（往成绩表中插入数据，学生编号不存在）
HR@ENMOEDU>insert into sc values(5,1,100);
insert into sc values(5,1,100)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_SC_SNO) violated - parent key not
found
HR@ENMOEDU>
7) 往成绩表中插入数据，课程编号不存在
HR@ENMOEDU>insert into sc values(1,4,100);
insert into sc values(1,4,100)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_SC_CNO) violated - parent key not
found
HR@ENMOEDU>
8) 开始测试，验证set null，查看表nativeplace
HR@ENMOEDU>select * from nativeplace;
NID NP_NAME
--------- --------------------
1 heilongjiang
2 jilin
3 liaoning
4 beijing
HR@ENMOEDU>
9) 查看表student
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
---- ---------- ------------------ ------------------- ------------
1 zhangsan 123456789123456780 2012-09-14 16:06:06 1
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13 3
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
HR@ENMOEDU>
10) 删除籍贯表中nid为3的记录
HR@ENMOEDU>delete nativeplace where nid=3;
1 row deleted.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
11) 查看籍贯表，nid为3的记录已经删除
HR@ENMOEDU>select * from nativeplace;
NID NP_NAME
--------- --------------------
1 heilongjiang
2 jilin
4 beijing
HR@ENMOEDU>
12) 查看学生表，此时之前籍贯为3的籍贯id已经变为null，此时，外键约束set null起了作用
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
---- ---------- ------------------ ------------------- ------------
1 zhangsan 123456789123456780 2012-09-14 16:06:06 1
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
13) 验证级联删除
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
--- ---------- ------------------ ------------------- ------------
1 zhangsan 123456789123456780 2012-09-14 16:06:06 1
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
HR@ENMOEDU>select * from sc;
SNO CNO GRADE
---- -------- ---------
1 1 91
1 2 92
1 3 93
2 1 88
2 2 77
2 3 99
3 1 65
3 2 75
3 3 85
4 1 80
4 2 88
4 3 89
12 rows selected.
HR@ENMOEDU>
14) 删除学生表中，学号为1的记录，查询验证，学号为1的记录已经删除
HR@ENMOEDU>delete student where sno=1;
1 row deleted.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>select * from student;
SNO SNAME IDCARD CREATETIME NATIVE_PLACE
---- -------- ------------------ ------------------- ------------
2 lisi 123456789123456781 2012-09-14 16:06:10 2
3 wangwu 123456789123456782 2012-09-14 16:06:13
4 zhaoliu 123456789123456783 2012-09-14 16:06:16 4
HR@ENMOEDU>
15) 查看成绩表，此时发现，学号为1的学生的成绩已经全部删除，此时外键的级联删除起了作用
HR@ENMOEDU>select * from sc;
SNO CNO GRADE
---- -------- ---------
2 1 88
2 2 77
2 3 99
3 1 65
3 2 75
3 3 85
4 1 80
4 2 88
4 3 89
9 rows selected.
HR@ENMOEDU>
16) 验证默认约束规则，提示无法删除
HR@ENMOEDU>delete course where cno=1;
delete course where cno=1
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.FK_SC_CNO) violated - child record found
HR@ENMOEDU>
3.5. CHECK
1) 删除环境中原来的学生表
HR@ENMOEDU>drop table student1 purge;
Table dropped.
HR@ENMOEDU>
创建学生表，要求，建表的同时，创建两个check，一个保证身份证内容都为小写，一个是保证所有学生的名字都为大写
HR@ENMOEDU>CREATE TABLE student1
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18) check (idcard=lower(idcard)),
createtime DATE,
CONSTRAINT ck_student_sname CHECK (sname=upper(sname))
) ;
Table created.
HR@ENMOEDU>
2) 插入测试数据，验证check约束
HR@ENMOEDU>insert into student1 values(1,'AA','123a',sysdate);
1 row created.
HR@ENMOEDU>insert into student1 values(2,'aa','123a',sysdate);
insert into student1 values(2,'aa','123a',sysdate)
*
ERROR at line 1:
ORA-02290: check constraint (HR.CK_STUDENT_SNAME) violated
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
请问：
下面语句是否能够执行成功？
insert into student1 values(1,null,null,sysdate);可以，null空值是不受check约束的
注：下面两个建表脚本是不被允许的。
Drop table student1 purge;
CREATE TABLE student1
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE check(createtime <sysdate)
);
CREATE TABLE student1
(
sno NUMBER(5),
sname VARCHAR2(18) check(sname=idcard),
idcard VARCHAR2(18),
createtime DATE
);
如下表达式在check是不被允许的
- 涉及到 CURRVAL, NEXTVAL, LEVEL 和 ROWNUM 伪列
- 调用 SYSDATE, UID, USER 和 USERENV 函数
- 涉及其它行中其它值的查询
第4章 创建视图
语法：
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];
|
OR REPLACE |
如果视图已经存在重新创建它 |
|
FORCE |
创建视图，而不管基表是否存在 |
|
NOFORCE |
只在基表存在的情况下创建视图(这是默认值) |
|
View |
视图的名字 |
|
Alias |
为由视图查询选择的表达式指定名字(别名的个数必须与由视图选择的表达式的个数匹配) |
|
Subquery |
是一个完整的 SELECT 语句(对于在 SELECT 列表中的字段你可以用别名) |
|
WITH CHECK OPTION |
指定只有可访问的行在视图中才能被插入或修改 |
|
Constraint |
为 CHECK OPTION 约束指定的名字 |
|
WITH READ ONLY |
确保在该视图中没有 DML 操作被执行 |
注：如果基表不存在，那么
Force 可以创建成功，但会提示“编译错误”
Noforce 直接提示表或视图不存在
4.1. 简单视图、复杂视图区别及测试
|
特性 |
简单视图 |
复杂视图 |
|
表的数目 |
一个 |
一个或多个 |
|
包含函数 |
无 |
有 |
|
包含数据分组 |
无 |
有 |
|
通过视图进行DML操作 |
是 |
不允许 |
1) 创建学生表
HR@ENMOEDU>CREATE TABLE student
(
sno NUMBER(5),
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE default SYSDATE not null,
native_place number(2)
);
2) 学生表插入数据
insert into student values(1,'zhangsan','123456789123456780',default,1);
insert into student values(2,'lisi','123456789123456781',default,2);
insert into student values(3,'wangwu','123456789123456782',default,3);
insert into student values(4,'zhaoliu','123456789123456783',default,4);
3) 创建简单视图和复杂视图 grant create view to scott;
简单视图：
HR@ENMOEDU>create view vw_student1 as select sno,sname,createtime from student;
View created.
HR@ENMOEDU>create view vw_student2 as select sno,sname,createtime,sysdate as dd from student;
View created.
复杂视图：
HR@ENMOEDU>create view vw_student3 as select sno,sname,to_char(createtime,'yyyy-mm-dd') as ctime from student;
View created.
HR@ENMOEDU>create view vw_student4 as select native_place,count(*) as num from student group by native_place;
View created.
HR@ENMOEDU>
4) 插入数据测试
HR@ENMOEDU>insert into vw_student1 values(5,'test',sysdate);
1 row created.
HR@ENMOEDU>insert into vw_student2(sno,sname,createtime) values(6,'test1',sysdate);
1 row created.
HR@ENMOEDU>insert into vw_student3(sno,sname,ctime) values(7,'test2',to_char(sysdate,'yyyy-mm-dd'));
insert into vw_student3(sno,sname,ctime) values(7,'test2',to_char(sysdate,'yyyy-mm-dd'))
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
HR@ENMOEDU>insert into vw_student4 values(1,1);
insert into vw_student4 values(1,1)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
HR@ENMOEDU>
4.2. WITH CHECK OPTION
视图中的check约束
1) 建表、插入测试数据
HR@ENMOEDU>drop table student purge;
Table dropped.
HR@ENMOEDU>CREATE TABLE student
(
sno NUMBER(5) constraint pk_student primary KEY using index,
sname VARCHAR2(20),
idcard VARCHAR2(18),
createtime DATE default SYSDATE,
native_place number(2)
);
Table created.
HR@ENMOEDU>
insert into student values(1,'zhangsan','123456789123456780',default,1);
insert into student values(2,'lisi','123456789123456781',default,2);
insert into student values(3,'wangwu','123456789123456782',default,3);
insert into student values(4,'zhaoliu','123456789123456783',default,2);
insert into student values(5,'zhaosi','123456789123456783',default,2);
insert into student values(6,'maba','123456789123456783',default,2);
commit;
2) 创建没有约束的简单视图
HR@ENMOEDU>create or replace view vw_student7
as
select sno,sname,idcard,native_place
from student where native_place=2;
View created.
HR@ENMOEDU>
3) 创建有约束的简单视图，（with check option，即指定只有可访问的行在视图中才能被插入或修改）
HR@ENMOEDU>create or replace view vw_student8
as
select sno,sname,idcard,native_place
from student where native_place=2
with check option;
View created.
HR@ENMOEDU>
4) 插入数据测试
HR@ENMOEDU>
insert into vw_student8 values(7,'zhu88','123456789123456783',3);
insert into vw_student8 values(7,'zhu88','123456789123456783',3)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
HR@ENMOEDU>
insert into vw_student7 values(7,'zhu88','123456789123456783',3);
1 row created.
HR@ENMOEDU>
insert into vw_student8 values(8,'niu99','123456789123456783',2);
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
小结：
1) 当往有约束的简单视图中插入不符合约束的数据时，数据库则会抛出错误，当插入符合约束条件的数据，则插入正常。
2) 当往没有约束的视图中插入符合标准的数据时，则可以正常插入
4.3. WITH READ ONLY
只读视图
HR@ENMOEDU>create or replace view vw_student9
as
select sno,sname,idcard,native_place
from student
where native_place=2
with read only;
View created.
HR@ENMOEDU>
HR@ENMOEDU>insert into vw_student9 values(9,'r_only','123456789123456783',2);
insert into vw_student9 values(9,'r_only','123456789123456783',2)
*
ERROR at line 1:
ORA-42399: cannot perform a DML operation on a
read-only view
第5章 其他数据库对象
5.1. 序列
(一)语法：
1) 创建序列
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
|
sequence |
是序列发生器的名字 |
|
INCREMENT BY n |
指定序列号之间的间隔，在这儿 n 是一个整数 (如果该子句被省略，序列增量为 1) |
|
START WITH n |
指定要产生的第一个序列数 (如果该子句被省略，序列从 1开始) |
|
MAXVALUE n |
指定序列能产生的最大值 |
|
NOMAXVALUE |
对于升序序列指定 10^27 为最大值，对于降序序列指定-1为最大值 (这是默认选项) |
|
MINVALUE n |
指定最小序列值 |
|
NOMINVALUE |
对于升序序列指定 1 为最小值，对于降序序列指定-(10^26)为最小值 (这是默认选项) |
|
CYCLE|NOCYCLE |
指定序列在达到它的最大或最小值之后，是否继续产生 (NOCYCLE 是默认选项) |
|
CACHE n|NOCACHE |
指定 Oracle 服务器预先分配多少值，并且保持在内存中(默认情况下，Oracle 服务器缓冲 20 个值) |
2) 修改序列：
ALTER SEQUENCE sequence
[INCREMENT BY n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
3) 删除序列：
DROP SEQUENCE sequence;
(二)使用NEXTVAL和CURRVAL的规则
1) 可以使用nextval和currval
? 一个不是子查询的一部分的 SELECT 语句的 SELECT 列表
? 在一个 INSERT 语句中子查询的 SELECT 列表
? 一个 INSERT 语句中的 VALUES 子句
? 一个 UPDATE 语句的 SET 子句
2) 不允许使用nextval和currval
? 一个视图的 SELECT 列表
create or replace view vw_a as select seq_test.nextval,1 as id from dual;
? 一个带 DISTINCT 关键字的 SELECT 语句
? 一个带 GROUP BY、HAVING 或 ORDER BY 子句的 SELECT 语句
? 一个在 SELECT、DELETE 或 UPDATE 语句中的子句
? 在 CREATE TABLE 或 ALTER TABLE 语句中的 DEFAULT 表达式
(三)序列测试
HR@ENMOEDU>create sequence SEQ
minvalue 1
maxvalue 100
start with 2
increment by 1;
Sequence created.
--在没有执行nextval的时候，无法执行currval
HR@ENMOEDU>select seq.currval from dual;
select seq.currval from dual
*
ERROR at line 1:
ORA-08002: sequence SEQ.CURRVAL is not yet defined in this session
HR@ENMOEDU>select seq.nextval from dual;
NEXTVAL
----------
2
HR@ENMOEDU>select seq.nextval from dual;
NEXTVAL
----------
3
HR@ENMOEDU>
新打开一个会话:
HR@ENMOEDU> select seq.currval from dual;
select seq1.currval from dual
*
ERROR at line 1:
ORA-08002: sequence SEQ.CURRVAL is not yet defined in this session
小结：
1) 在一个新的会话中，不能直接使用currval，必须先nextval后再currval
2) 新建的一个序列，第一次nextval的值为start with的值
(四)序列和触发器配合应用
HR@ENMOEDU>create table t_seq(nid number(10),name varchar2(10));
Table created.
HR@ENMOEDU>create sequence seq_t_seq;
Sequence created.
HR@ENMOEDU>create trigger trg_t_seq
before insert on t_seq
for each row
begin
if :new.nid is null then
select seq_t_seq.nextval
into :new.nid
from dual;
end if;
end;
/
Trigger created.
HR@ENMOEDU>insert into t_seq (name) values('a');
1 row created.
HR@ENMOEDU>insert into t_seq (name) values('b');
1 row created.
HR@ENMOEDU>
上面的insert语句，有这样的误解，nid不是主键，也非not null，插入两条记录很正常，那么我们将nid修改为主键，如下：
HR@ENMOEDU>alter table t_seq add primary key(nid);
Table altered.
HR@ENMOEDU>insert into t_seq (name) values('c');
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>
HR@ENMOEDU>select * from t_seq;
NID NAME
---------- ----------
1 a
2 b
3 c
HR@ENMOEDU>
此时我们发现，主键字段也是有数据的，主键的数据就是来源于触发器，这个是一个比较典型的触发器利用序列协助写入主键的例子。
(五)查询序列
1) 查看序列SEQ_T_SEQ
HR@ENMOEDU>select sequence_name,min_value,max_value,increment_by,cycle_flag,last_number from user_sequences where sequence_name='SEQ_T_SEQ';
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------- ---------- ---------- ------------ -----------
SEQ_T_SEQ 1 1.0000E+28 1 21
HR@ENMOEDU>
注：如果指定NOCACHE 选项，那么LAST_NUMBER显示序列中下一个有效的值，'SEQ_T_SEQ'这个序列的LAST_NUMBER显示的就不是下一个有效值
2) 查看创建SEQ_T_SEQ的脚本，确认cache
HR@ENMOEDU>set long 999999
HR@ENMOEDU>select dbms_metadata.get_ddl(object_type => 'SEQUENCE',name => 'SEQ_T_SEQ') from dual;
DBMS_METADATA.GET_DDL(OBJECT_TYPE=>'SEQUENCE',NAME=>'SEQ_T_SEQ')
------------------------------------------------------------------------
CREATE SEQUENCE "HR"."SEQ_T_SEQ" MINVALUE 1 MAXVALUE 9999999999999999999999
999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE
HR@ENMOEDU>
3) 下面举我们创建一个NOCACHE的例子
HR@ENMOEDU>select sequence_name,min_value,max_value,increment_by,cycle_flag,last_number from user_sequences where sequence_name='SEQ_T_SEQ';
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------- ---------- ---------- ------------ -----------
SEQ_T_SEQ 1 1.0000E+28 1 21
HR@ENMOEDU>create sequence seq_nocache nocache;
Sequence created.
4) 执行一次sequence，得到的结果为1，下一个值应该是2
HR@ENMOEDU>select seq_nocache.nextval from dual;
NEXTVAL
----------
1
HR@ENMOEDU>
5) 查看序列SEQ_NOCACHE的LAST_NUMBER，此时我们发现LAST_NUMBER值为2，也就是下一个有效值
HR@ENMOEDU>select sequence_name,min_value,max_value,increment_by,last_number from user_sequences where sequence_name='SEQ_NOCACHE';
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER
------------- ---------- ---------- ------------ -----------
SEQ_NOCACHE 1 1.0000E+28 1 2
HR@ENMOEDU>
(六)删除序列
HR@ENMOEDU>drop sequence seq_nocache;
Sequence dropped.
HR@ENMOEDU>
5.2. 索引
(一)自动创建
创建主键或唯一键约束会在相应字段上创建唯一索引
1) 创建测试表
HR@ENMOEDU>create table t_a_index(x number,y number);
Table created.
HR@ENMOEDU>
2) 修改表，增加主键，主键的名字为pk_t_a_index
HR@ENMOEDU>alter table t_a_index add constraint pk_t_a_index primary key(x);
Table altered.
HR@ENMOEDU>
3) 修改表，增加唯一键，唯一键的名字为uk_t_a_index
HR@ENMOEDU>alter table t_a_index add constraint uk_t_a_index unique(y);
Table altered.
HR@ENMOEDU>
4) 查看表T_A_INDEX的相关索引，发现有两个与约束名称相同的索引
HR@ENMOEDU>select index_name,index_type,table_owner,table_name,uniqueness from user_indexes where table_name='T_A_INDEX';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENES
------------ ---------- ----------- ---------- ---------
UK_T_A_INDEX NORMAL HR T_A_INDEX UNIQUE
PK_T_A_INDEX NORMAL HR T_A_INDEX UNIQUE
HR@ENMOEDU>
通过这个测试，我们了解到，主键和唯一键会自动创建唯一索引，索引的名称与约束的名称相同。
(二)手工创建
在一个或多个列上创建索引，语法：
CREATE INDEX index ON table (column[, column]...);
1) 创建测试表
HR@ENMOEDU>create table t_m_index(x number,y number,z number);
Table created.
HR@ENMOEDU>
2) 在x字段上创建普通索引
HR@ENMOEDU>create index idx_t_m_index_x on t_m_index(x);
Index created.
HR@ENMOEDU>
3) 在y,z字段上创建普通复合索引
HR@ENMOEDU>create index idx_t_m_index_yz on t_m_index(y,z) tablespace users;
Index created.
HR@ENMOEDU>
4) 查看表T_M_INDEX相关索引
HR@ENMOEDU>select index_name,index_type,table_owner,table_name,uniqueness from user_indexes where table_name='T_M_INDEX';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME UNIQUENES
---------------- ---------- ----------- ---------- ---------
IDX_T_M_INDEX_YZ NORMAL HR T_M_INDEX NONUNIQUE
IDX_T_M_INDEX_X NORMAL HR T_M_INDEX NONUNIQUE
HR@ENMOEDU>
注：字典表user_indexes的UNIQUENES字段
comment on column SYS.USER_INDEXES.UNIQUENESS is 'Uniqueness status of the index: "UNIQUE", "NONUNIQUE", or "BITMAP"';
5) 查看T_M_INDEX相关索引及索引字段
HR@ENMOEDU>SELECT t.index_name, t.column_name, t.column_position col_pos, d.uniqueness FROM user_indexes d, user_ind_columns t
WHERE t.index_name = d.index_name
AND t.table_name = 'T_M_INDEX';
INDEX_NAME COLUMN_NAME COL_POS UNIQUENES
---------------- ------------ ---------- ---------
IDX_T_M_INDEX_X X 1 NONUNIQUE
IDX_T_M_INDEX_YZ Y 1 NONUNIQUE
IDX_T_M_INDEX_YZ Z 2 NONUNIQUE
HR@ENMOEDU>
5.3. 同义词
同义词，简单点儿理解就是一个别名，一个映射的关系。
(一)同义词的分类
Oracle同义词有两种类型，分别是公用Oracle同义词与私有Oracle同义词。
1) 公用Oracle同义
由一个特殊的用户组Public所拥有。顾名思义，数据库中所有的用户都可以使用公用同义词。公用同义词往往用来标示一些比较普通的数据库对象，这些对象往往大家都需要引用。
2) 私有Oracle同义词（默认创建）
它是跟公用同义词所对应，他是由创建他的用户所有。当然，这个同义词的创建者，可以通过授权控制其他用户是否有权使用属于自己的私有同义词。
(二)语法
1) 创建同义词
create [or replace] [public] synonym [schema.]synonym
for [schema.]object[@dblink];
2) 删除同义词
drop [public] synonym [schema.]synonym [force];
(三)先决条件
1) 要自己的schema下创建私有的同义词，你必须拥有CREATE SYNONYM系统权限。
2) 要在其他schema下创建一个私有同义词，你必须有CREATE ANY SYNONYM系统权限。
3) 创建一个公共的同义词，你必须有CREATE PUBLIC SYNONYM系统权限。
(四)私有同义词测试
1) 在scott下想查看hr下的employees，没有权限查看，如果不想直接把employees的查询权限给scott，那么可以考虑创建一个同义词
SYS@ENMOEDU> create user scott identified by scott ;
User created.
SYS@ENMOEDU> grant connect,resource,create synonym to scott;
Grant succeeded.
SYS@ENMOEDU> conn scott/scott
Connected.
SCOTT@ENMOEDU> select count(*) from hr.employees;
select count(*) from hr.employees
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@ENMOEDU>
2) 连接到scott用户下创建hr用户下的employees的同义词syn_employees
SCOTT@ENMOEDU> conn hr/hr
Connected.
HR@ENMOEDU> grant select on employees to scott;
Grant succeeded.
HR@ENMOEDU> conn scott/tiger
Connected.
SCOTT@ENMOEDU>create synonym syn_employees for hr.employees;
create synonym syn_employees for hr.employees
*
ERROR at line 1:
ORA-01031: insufficient privileges
SCOTT@ENMOEDU >conn / as sysdba
Connected.
SYS@ENMOEDU >grant create any synonym to scott;
Grant succeeded.
SYS@ENMOEDU >conn scott/tiger;
Connected.
SCOTT@ENMOEDU >create synonym syn_employees for hr.employees;
Synonym created.
3) 在scott用户下查询hr下的同义词
HR@ENMOEDU> conn scott/tiger
Connected.
SCOTT@ENMOEDU> select count(*) from syn_employees;
COUNT(*)
----------
107
SCOTT@ENMOEDU>
(五)公共同义词测试
1) 在sys用户下创建公共同义词dept
SYS@ENMOEDU>create or replace public synonym dept for hr.departments;
Synonym created.
SYS@ENMOEDU>
2) 连接到scott用户测试公共同义词是否可以查询
查询结果为表或视图不存在，之所以报这个错误，是因为scott没有查询hr.departments的权限
SYS@ENMOEDU> conn scott/scott
Connected.
SCOTT@ENMOEDU> select * from dept;
select * from dept
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@ENMOEDU>
3) 我们到拥有DBA权限的system用户可以查看到dept同义词
SCOTT@ENMOEDU> conn system/oracle
Connected.
SYSTEM@ENMOEDU>select count(*) from dept;
COUNT(*)
----------
27
SYSTEM@ENMOEDU>
4) 我们可以将同义词departments的select权限赋予public
SYSTEM@ENMOEDU> conn hr/hr
Connected.
HR@ENMOEDU>grant select on departments to public;
Grant succeeded.
HR@ENMOEDU>
5) 我们再回到scott用户，此时就可以查询了
HR@ENMOEDU> conn scott/scott
Connected.
SCOTT@ENMOEDU> select count(*) from dept;
COUNT(*)
----------
27
SCOTT@ENMOEDU>
其实规则是这样的：
- 首先查看发出命令的用户对表或者视图是否存在并且有访问权限
- 如果表或视图不存在，则检查私有同义词
- 如果私有同义词不存在，则访问公共同义词
- 如果公共同义词仍不存在，Oracle返回消息“ORA-00942 table or view does not exist”.
第6章 使用集合运算
6.1. UNION
A集合和B集合的合并,去掉两集合重复的部分并且排序
HR@ENMOEDU>select department_id,first_name from employees where department_id in (10,20);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
20 Michael
20 Pat
HR@ENMOEDU>select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
30 Den
30 Alexander
30 Shelli
30 Sigal
30 Guy
30 Karen
7 rows selected.
HR@ENMOEDU>
select department_id,first_name from employees where department_id in (10,20)
union
select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
20 Michael
20 Pat
30 Alexander
30 Den
30 Guy
30 Karen
30 Shelli
30 Sigal
9 rows selected.
HR@ENMOEDU>
6.2. UNION ALL
A集合和B集合的合并，不去重，不排序
HR@ENMOEDU>
select department_id,first_name from employees where department_id in (10,20)
union all
select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
20 Michael
20 Pat
10 Jennifer
30 Den
30 Alexander
30 Shelli
30 Sigal
30 Guy
30 Karen
10 rows selected.
HR@ENMOEDU>
6.3. INTERSECT
两个集合的交集部分，排序并去重
HR@ENMOEDU>
select department_id,first_name from employees where department_id in (10,20)
intersect
select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
10 Jennifer
HR@ENMOEDU>
6.4. MINUS
取两个集合的差集，返回A集合中存在,B集合中不存在的数据
HR@ENMOEDU>
select department_id,first_name from employees where department_id in (10,20)
minus
select department_id,first_name from employees where department_id in (10,30);
DEPARTMENT_ID FIRST_NAME
------------- --------------------
20 Michael
20 Pat
HR@ENMOEDU>
6.5. UNION/UNION ALL注意事项
HR@ENMOEDU>create table t_union1(col number);
Table created.
HR@ENMOEDU>
HR@ENMOEDU>create table t_union2(col number);
Table created.
HR@ENMOEDU>
思考题：
在先不考虑t_union1和t_union2两张表中的数据的情况下，请问如下两个sql的结果是否相同？相同
SQL1：
Select 1 as type,col from t_union1
Union
Select 2,col from t_union2;
SQL2：
Select 1 as type,col from t_union1
Union all
Select 2,col from t_union2;
1) 测试：
HR@ENMOEDU>insert into t_union1 values(1);
1 row created.
HR@ENMOEDU>insert into t_union1 values(1);
1 row created.
HR@ENMOEDU>insert into t_union2 values(1);
1 row created.
HR@ENMOEDU>insert into t_union2 values(2);
1 row created.
HR@ENMOEDU>commit;
Commit complete.
HR@ENMOEDU>select * from t_union1;
COL
----------
1
1
HR@ENMOEDU>select * from t_union2;
COL
----------
1
2
HR@ENMOEDU>select 1 as type,col from t_union1;
TYPE COL
---------- ----------
1 1
1 1
HR@ORA11GR2>select 2 as type,col from t_union2;
TYPE COL
---------- ----------
2 1
2 2
HR@ENMOEDU>Select 1 as type,col from t_union1
Union
Select 2,col from t_union2;
TYPE COL
--------- ---------
1 1
2 1
2 2
HR@ENMOEDU>Select 1 as type,col from t_union1
Union all
Select 2,col from t_union2;
TYPE COL
--------- ---------
1 1
1 1
2 1
2 2
HR@ENMOEDU>
2) 小结
通过上面的测试我们发现，虽然有常量字段“type”，但是这两个结果集还是不一样的，因为union的去重操作不单单是两个结果集之间的去重，而是整个结果集的一个去除重复的动作（即先是两个结果集的分别去重，然后再两个结果集的合并，最后再去重排序），所以，我们在使用union和union all的时候要格外注意，特别是在性能优化的时候，不要不假思索的把union替换成union all。