oracle学习笔记(第二章:基本语法)

创建表：
CREATE TABLE tab_nm(col1 CHAR(2) NOT NULL ,
                    col2 VARCHAR2(100) NOT NULL,
                    col3 NUMBER(4,3) DEFAULT 0,
                    col4 CHAR(4) NOT NULL,
                    CONSTRAINT pk_nm PRIMARY KEY(col1,col2),
                    CONSTRAINT fk_nm FOREIGN KEY(col2) REFERENCES tab_nm2(col2),
                    CONSTRAINT un_check UNIQUE(col4),
                    CONSTRAINT ch_nm CHECK(col3>0))
建立 TABLE 注意事项﹕
1.Table_name , column_name 不分大小写
2.若 table_name 与 column_name 用双引号 “ 括起来, 则大小写是可区分的
3.命名时使用双引号，之后对table或 column 进行操作时，也必须使用双引号.
4.table中的栏名不可重复.
创建视图：
CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] VIEW view_name [ (别名,...)]
AS select_statement
[ WITH CHECK OPTION [ CONSTRAINT  constraint_name]]
[ WITH READ ONLY ]
-- FORCE 不管引用的表是否存在,视图都会创建
-- NOFORCE 只有引用的表存在时,才创建视图,orace的默认值
-- WITH CHECK OPTION 所插入或修改的数据必须满足视图所定义的蠽条件?
-- WITH READ ONLY 保证 在该视图上不能进行任何DML操作

View 是否能更新的条件
----------视图必须是可以唯一的定义每一笔记录 , 且 select 中, 拥有所有识别唯一键值的字段(为保留键的表格)
----------不能包含以下内容﹕
----------1﹑ROWNUM
----------2﹑表格阶层:START WITH, CONNECT BY
----------3﹑集合运算:UNION, MINUS
----------4﹑群组函数: DISTINCT, GROUP BY, HAVING
----------5﹑数学函数:COUNT, MAX, MIN
----------6﹑每一段叙述只能对单一表格进行
----------insert , update 只能对持有键表格有作用
----------delete 只能在 view 中,仅有一个持有键表格时进行
VIEW 与记录层次安全性
---------利用view 可限制用户数据的存取
---------可使用 where 条件, 限制使用者账号名称或权限下的数据显示
内嵌式视图(Inline Views)
-----------把查询语句放在FROM 子句中,并为该子句定义一个别名来定义内嵌式视图
-----------注意:内嵌式视图不属于任何用户,也不是对象

同义词﹕表或视图的另外一个名字﹐同一词有两种﹕provate和public﹐private同义词只对创建它的用户是可见的﹐而public对所有用户都是可见的。同义词和视图很相似﹕两者都可以使用一个不同的名字引用一个表﹐但是同义词不能限制列或者为它们重新命名。
语法﹕
create [public] synonym synonm_name for ower.object_name;
synonm_name:是同义词的名字
ower﹕是拥有引用或视图的Oracle账户
object_name﹕是被同义词引用的表或视图名
例子﹕
create synonm synonm_name for liyongjie.empm;
select * from synonm_name ;

-----快照(Snapshot)﹕它基于一个﹐该查询连接远程数据库﹐可以把该快照设置成只读方式或可更新方式。若要改善性能﹐
-----可以索引快照使用本地表。根据快照基本查询的复杂度﹐可以使用快照日志来提高复制操作的性能﹐复制操作根据每
-----个用户为每个快照的安排自动完成。
-----快照分为﹕复杂快照和简单快照。简单快照中每一行都基于远程数据库表中的一行﹐而复杂快照基于远程表的多行
-----语法﹕
Create Snapshot [schema.]Snapshot_name
[Pctfee integer]
[Pctused integer]
[Inirans integer]
[Maxtrans integer]
[Storage storage_slasue]----------快照的存储特征
[TableSpace tablespace_name]----指定表空间
[Using Index [Pctfee integer1]----使用索引
[Pctused intege2][Initrans integer3][Maxtrans integer4]]
[Refresh [Fast | Complete | Force ][Start With date1][Next date2]]----指定快照的刷新特性的信息

---删除快照
Drop Snapshot Snapshot_name

约束 constraint,语法格式
---主键 primary key
CONSTRAINT PK_Table_name PRIMARY KEY(col1,col2,...)
---外键 foreign key
CONSTRAINT FK_name FOREIGN KEY(col1,col2,...) REFERENCES tab_name(col1,col2,...)
---唯一性 unique
CONSTRAINT UN_name Unique(col1,col2,...)
---check 约束
CONSTRAINT  CK_name CHECK (条件)
--例如
CONSTRAINT CK_name CHECK(emp_sex='1' OR emp_sex='0')

约束的维护
---语法格式:
---1,删除约束 drop
ALTER TABLE table_name DROP  PRIMARY KEY
ALTER TABLE table_name DROP  CONSTRAINT  constraint_name [可以是主键/外键/CHECK/UNIQUE] [CASCADE]  
---CASCADE 的作用是可以关闭有完整性关系的约束,如,当要删除某个主键时,提示有外键指向的错误时,可在 放弃
---主键约束的语句后 加 CASCADE
--2,禁用/启用约束 disable/enable
ALTER TABLE table_name DISABLE CONSTRAINT constraint_name [可以是主键/外键/CHECK/UNIQUE] [CASCADE]
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name  [可以是主键/外键/CHECK/UNIQUE] [CASCADE]
--修改主键/外键
--如果主键/外键已经存在﹐只能先删除﹐再增加

外键约束注意事项
--定义外键时:
--外键字段的数据类型和长度要与参考的主表的字段的数据类型和长度相同
----delete
--只能先删除 从表(子表),再删除主表(父表)
----insert
--只有子表的外键存在于父表,才能插入
----update
--同时要考虑到主表和从表

修改表(alter table tableName )
   有四中类型﹕
   增加列alter table tableName add  (column1  dataType  [isNull,UNIQUE]﹐column2  dataType  [isNull,unque],…..)
   删除列alter table tableName drop(column1,column2….) 或 alter table tableName drop column column1 //(方法2一次只能删除一列)
   修改列alter table tableName modify (column1 dataType(n)   [isNull  UNIQUE],column2 dataType(n) [isNull unque],….)
   修改列的类型﹐长度﹐是否为空等
         ALTER TABLE ableName add  (column1 datatype [DEFAULT 'value'] [NOT NULL] ) --增加有默认值的列,且不为空                      
   修改表会对系统的效率带来很大的冲击﹐一个折衷的方案是在 alter table 语句中使用 set unused 子句
   例如﹕将某列设置成不可用的(unused):
    alter table table_name set unused(column_name1,column_name2..)或
    alter table table_name set unused column column_name //一次只能设置一列
    删除不可用的列﹕alter table table_name drop unused columns;
----截断表﹕当一个表中的数据已经不再使用时可使用 truncate table ,它有如下特性
        1﹐删除表中的所有数据行﹐但是保留表的结构
        2﹐如果没有备份﹐锁删除的数据是无法恢复的
        3﹐释放表所占用的磁盘空间
        4﹐它并不触发表的删除触发器
它与 drop table 语句的特性 的区别﹕
        1﹐它删除表中所有的数据行和表的结构
        2﹐它删除表的所有索引
        3﹐如果没有备份﹐所删除的表无法恢复
        4﹐它提交所有挂起的事物
        5﹐所有基于该表的视图和别名依然保留但是已经无效
-- 修改表名
RENAME old_tab_name TO new_tab_name;
RENAME命令﹕改变对象的名称﹐例如当你创建了一个 table,function,trigger,procedure 等﹐由于当时你考虑不周现在想改变对象名称则可使用﹕rename 对象原名 to 对象现在的名字 ;

Select：语句
联集(并集)   UNION ， UNION  ALL
交集   INTERSECT
差集   MINUS
集合中的数据须符合以下两点：
1.数据型态须相同
2.数据字段数目要相同
3.列名可以不同
并集
select emp_no from empm
union  --两个表中有重复的只显示一笔
select emp_no from empimg;
等价于
SELECT empm.emp_no
FROM empm,empimg
WHERE empm.emp_no = empimg.emp_no(+)

select emp_no from empm
union all   --将两个表中重复的列全部显示
select emp_no from emping;
差集:去掉两个表中相同的值 ﹐结果显示第一个表中的有而第二个表中没有的列值
差集1
select emp_no from empm
minus
select emp_no from empimg;
差集2
select emp_no from empimg
minus
select emp_no from empm;
--注意﹕差集1与差集2的结果是不一样的
交集:显示两个表中都有的列值
select emp_no from empm
intersect
select emp_no from empimg;
交集(intersect)结果等价与﹕
select empm.emp_no from empm,empimg
where empm.emp_no=empimg.emp_no;
交集﹑并集﹐差集的列名可以不同的情况
并集
select emp_no from emp_m ---------情况1
union  --两个表中有重复的只显示一笔
select emp_job_no from emp_d;

select emp_img from empimg  --情况2
union  --两个表中有重复的只显示一笔
select emp_no from empm;
注意﹕情况1与情况2所显示的列名是不同的﹐情况1显示的列名为 emp_no﹐情况2显示的列名为 emp_job_no
如果交集﹐并集﹐差集的列名不同﹐结果显示的列是第一个表的列
-------------
select emp_no from emp_m
union all-------将两个表中重复的列全部显示
select emp_job_no from emp_d;
------------------------差集
select emp_no from emp_m
minus
select emp_job_no from emp_d;
-----------------------交集:显示两个表中都有的列值
select emp_no from emp_m
intersect
select emp_job_no from emp_d;
-----------------------------------------------------------------------------------------------
-- in 的优先级比 union 高
SELECT emp_no FROM emp_m
WHERE emp_no IN (SELECT emp_no FROM emp_d) --先执行 in
UNION
(SELECT emp_no FROM emp_m)
---
SELECT emp_no FROM emp_m
WHERE emp_no IN ((SELECT emp_no FROM emp_d) --先执行 union
UNION
(SELECT emp_no FROM emp_m))


使用merge命令
--ORACLE9i允许在单个命令中使用merge命令对一个执行 insert 和 update﹐
--根据指定的条件﹐oracle将提取源数据（可以是一个表或视图或一个select查询）﹐如果条件满足﹐则更新已有的值﹐如果不满足﹐将插入此行
--例子
--------oracle8i中不支持merge   
merge into emp_m2 L2     ---------------------命名目标表并给出别名L2
using (select * from emp_m) L        ---------提供merge操作的源数据﹐并给出别名L
on (L2.emp_no=L.emp_no)             ---------------------给出合并条件
when matched then             ---------------------如果条件匹配﹐则执行更新
update set emp_name=L.emp_name
when not matched then        ----------------------如果条件不匹配﹐则插入该行
insert (L2.emp_no,L2.emp_no,L2.emp_sex,L2.emp_age,L2.emp_birthday)
values (L.emp_no,L.emp_name,L.emp_sex,L.age,L.emp_birthday);


索引 INDEX 的使用
---创建索引的语法:
CREATE [UNIQUE] INDEX index_name ON table_name(column_name1[column_name2,...])
----1,索引可以提高 select ,update ,delete语句的性能,但会降低insert的性能
----2,索引列应出现在where 语句之后,否则缩影是不会用到的
----3,索引列在where 语句之后,但使用不等号操作符,索引也不会被用到
----4,索引列在where 语句之后,但使用 is null 或 is not null,索引也不会被用到
----5,如果索引列出现在函数中,索引也不会被用到

同一词
----------同一词﹕表或视图的另外一个名字﹐同一词有两种﹕provate和public﹐private同一词只对创建它的用户
----------是可见的﹐而public对所有用户都是可见的。同一词和视图很相似﹕两者都可以使用一个不同的名字引用一
----------个表﹐但是同一词不能限制列或者为它们重新命名。
----语法﹕
create [public] synonym synonm_name for ower.object_name@DATABASE;

-----------synonm_name:是同一词的名字
-----------ower﹕是拥有引用或视图的Oracle账户
-----------object_name﹕是被同一词引用的表或视图名
----例子﹕
create synonym synonm_name for lyjdb.emp_m;
create synonym synonm_name2 for scott.emp_d;

快照(Snapshot)﹕它基于一个﹐该查询连接远程数据库﹐可以把该快照设置成只读方式或可更新方式。﹐若要改善性能﹐
-----可以索引快照使用本地表。根据快照基本查询的复杂度﹐可以使用快照日志来提高复制操作的性能﹐复制操
-----作根据每个用户为每个快照的安排自动完成。
-----快照分为﹕复杂快照和简单快照。简单快照中每一行都基于远程数据库表中的一行﹐而复杂快照基于远程表
-----的多行
-----语法﹕
Create Snapshot [schema.]Snapshot_name
[Pctfee integer]
[Pctused integer]
[Inirans integer]
[Maxtrans integer]
[Storage storage_slasue]----------快照的存储特征
[TableSpace tablespace_name]----指定表空间
[Using Index [Pctfee integer1]----使用索引
[Pctused intege2][Initrans integer3][Maxtrans integer4]]
[Refresh [Fast | Complete | Force ][Start With date1][Next date2]]----指定快照的刷新特性的信息

---删除快照
Drop Snapshot Snapshot_name


------------------------------------------------创建表控间tablespace
CREATE TABLESPACE "USERS"
    LOGGING
    DATAFILE 'C:\ORACLE\ORADATA\STUDENT\USERS01.DBF' SIZE 108M REUSE
    AUTOEXTEND
    ON NEXT  1280K MAXSIZE  32767M DEFAULT
    STORemp_age ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096
    PCTINCREASE 0 )
--------------
create tablespace "lyjdb"
logging
datafile 'd:\oracletest\lyjdb01.dbf' size 108m reuse
autoextend
on next 1280k maxsize 32767m default
storemp_age (initial 128k next 128k minextents 1 maxextents 4096 pctincrease 0)
----------------------------------------------------创建用户
create user user_name
[identifled by password | externally | globally as 'external_name' ]//表明oracle如何验证用户
[default tablespace tablespace_name]//标识用户创建的对象的默认表空间
[temporary tablespace tablespace_name]//标识用户的零时段的表空间
[quota integer K | integer M | unlimted on tablestace_name]//用户规定表空间存储对象﹐最多可达到这个定额的尺寸
[profile profile_name]//将指定的概要文件分配给用户
[default role role1,role2... | all [except role1,role2...] | none]
[password expire] //使用的用户失效﹐这种设置强制使用户在登录之前更改口令
[accont lock | nulock]//账户是否锁定
-------------------------------------------------- 创建角色
CREATE ROLE role_name
[NOT IDENTIFIED | IDENTIFIED { BY PASSWORD | USING [SCHEMA.] PACKAGE | EXTERNALLY | GLOBALLY }];
--将一个角色授予另一个角色,但是不能循环授权
GRANT role_name1 TO role_name2;
-- 为角色添加密码,用户想激活这个角色,就需要密码
ALTER ROLE role_name IDENTIFIED BY password_string
-- 角色可以与操作系统权限相关联
ALTER ROLE role_name IDENTIFIED EXTERNALLY;
-- 删除角色密码
ALTER ROLE role_name NOT IDENTIFIED;
-- 为用户创建一个默认的角色
ALTER USER user_name DEFAULT ROLE {[role1,role2][ALL | ALL EXCEPT role1,role2][NONE]};
--------------------------------------------------授权
---system privileges
GRANT system_priv | role_name TO user_name  //system_priv是要授予的系统权限﹐role要授予的角色
[with ADMIN OPTION]   //把向其它用户授权的能力传递给被授予者
---object privileges
GRANT object_privileges ON table_name TO user_name [WITH GRANT OPTION]
--例如:
GRANT UPDATE
ON "LIYONGJIE"."EMP_M" TO "LYJ"

CREATE USER "lyjdb"  PROFILE "DEFAULT"
IDENTIFIED BY "123456"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
ACCOUNT UNLOCK
----权限:分为系统权限和对象权限
----系统权限:访问(洏)恇据库( 系统资源 )的权力( 能力)
----对象权限:维护数据库中的对象的 权力(能力)
---常用的系统权限: 以下 nay 表示在任何模式中都有定义的权限
CREATE USER :创建其他的用户(搨n具有DBA的角色权限)
DROP USER :删除其他用户
SELECT ANY TABLE:查询任何用户的表和视图的权力
CREATE ANY TABLE :在任何模式下创建表
DROP ANY TABLE :删除任何模式中所创建的表
CREATE SESSION :连接数据库
CREATE TABLE : 在用户自己的模式中创建表
CREATE VIEW : 在自己的模式中创建视图
CREATE SEQUENCE : 在自己的模式中创建序列号
CREATE PROCEDURE : 在自己的模式中创建过程

------创建一个序列sequence:产生唯一的号码
-------创建语法﹕
create sequence sequence_name
increment by n ----递增值
start with n---------起始值
[maxvalue n | nomaxvalue]   --------maxvalue最大值;nomaxvalue的值为10的27次方,oracle的默认值
[minvalue n | nominvalue]    ------minvalue最小值;nominvalue的值为-11的26次方,oracle的默认值
[nocyle | cyle n |  cycle 20]-----nocycle非循环的,序列号达到最大值(或是最小值)n后,不再继
                                  -----续产生序列号;cycle n 序列号达到最大值(或是最小值)n后,继
                                 -----续产生序列号; cycle 20,oracle的默认值
[cache 20 noorde | nocache]---------有n个序列号保存在oracle服务器的内存中
-------------序列号sequence:的修改
ALTER SEQUENCE sequence_name
increment by n ----递增值
start with n---------起始值
[maxvalue n | nomaxvalue]     ------maxvalue最大值;nomaxvalue的值为10的27次方,oracle的默认值
[minvalue n | nominvalue]     ------minvalue最小值;nominvalue的值为-11的26次方,oracle的默认值
[nocyle | cyle n |  cycle 20]-----nocycle非循环的,序列号达到最大值(或是最小值)n后,不再继
                                   -----续产生序列号;cycle n 序列号达到最大值(或是最小值)n后,继
                                   -----续产生序列号; cycle 20,oracle的默认值
[cache 20 noorde | nocache]---------有n个序列号保存在oracle服务器的内存中
--修改后的 sequence 只影响以后的数据
--序列sequence 的 nextval 和 currval 使用注意事项:
1,使用 NEXTVAL 时必须在 NEXTVAL 前冠以 序列号名称
2,在使用 CURRVAL 必须在当前会话中(账号下)使用nextval产生一个序列号值
3,不能在 SELECT , UPDATE ,DELETE 子查询中的 SELECT 中使用 NEXTVAL 和 CURRVAL
4,不能在视图的 SELECT 中使用 NEXTVAL 和 CURRVAL
5,不能包含 DISTINCT 关键词的 查询中使用 NEXTVAL 和 CURRVAL
6,不能在包含 ORDER BY,GROUP BY , HAVING 子句的 SELECT 语句中使用 NEXTVAL 和 CURRVAL
7,不能在包含 DEFAULT 表达式的 CREATE TABLE 或 ALTER TABLE 语句中

--例子﹕
CREATE SEQUENCE "lyjdb"."P_NO" INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER
--用法
--某一列的值可以使用多个序列sequence值
insert into empm values('998','lily','1',p_no.nextval,'1983-1-3');
insert into emp_m values('999','jeke','1',p_no1.nextval,'1983-1-3');

--sequence_name.nextval 下一个值
--查询 sequence_name 的下一个值
select sequence_name.nextval from dual;
--sequence_name.currval 当前值
--查询此sequences_name的当前值
select sequence_name.currval from dual;
oracle规定:在引用currval之前,必须在当前会话中(账号下)使用nextval产生一个序列号值

--虚拟列与虚拟表
--虚拟字段于SELECT时,会提供一个具特殊意义的值
1. LEVEL                       
2. NULL
3. ROWID                 
4. ROWNUM
5. SysDATE                      
6. UID
7. USER

--database link
create public database LINK testd
  connect to nbmis  IDENTIFIED  BY nbmis
using '(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.14.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )';


自动事务
1， 自动事务指示：
2， 自动事务属性
3， 自动事务结束