3. 创建表的例子

　　CREATE TABLE DEPT(

　　EPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,

　　DNAME VARCHAR2(14),

　　LOC VARCHAR2(13)) ;

　　CREATE TABLE region(

　　ID number(2) NOT NULL PRIMARY KEY,

　　postcode number default ‘0′ NOT NULL,

　　areaname varchar2(30) default ‘ ‘ NOT NULL);

　　4. 创建表时的命名规则和注意事项

　　表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#

　　大小写不区分

　　不用SQL里的保留字, 一定要用时可用双引号把字符串括起来．

　　用和实体或属性相关的英文符号长度有一定的限制

　　注意事项:

　　建表时可以用中文的字段名, 但最好还是用英文的字段名

　　创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面

　　建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引

　　一个表的最多字段个数也是有限制的，254个.

　　5. 约束名的命名规则和语法

　　约束名的命名规则约束名如果在建表的时候没有指明，系统命名规则是SYS_Cn(n是数字)

　　约束名字符串的命名规则同于表和字段名的命名规则

　　6. 使用约束时的注意事项

　　约束里不能用系统函数,如SYSDATE和别的表的字段比较

　　可以用本表内字段的比较

　　想在事务处理后, 做约束的检查

　　SQL> alter session set constraints deferred.

　　7. 由实体关系图到创建表的例子 s_dept

　　前提条件:已有region表且含唯一关键字的字段id

　　SQL> CREATE TABLE s_dept

　　(id NUMBER(7)

　　CONSTRAINT s_dept_id_pk PRIMARY KEY,

　　name VARCHAR2(25)

　　CONSTRAINT s_dept_name_nn NOT NULL,

　　region_id NUMBER(7)

　　CONSTRAINT s_dept_region_id_fk REFERENCES region (id),

　　CONSTRAINT s_dept_name_region_id_uk UNIQUE(name, region_id));

　　8. 较复杂的创建表例子

　　SQL> CREATE TABLE s_emp (

　　id NUMBER(7)   CONSTRAINT s_emp_id_pk PRIMARY KEY,

　　last_name VARCHAR2(25)  CONSTRAINT s_emp_last_name_nn NOT NULL,

　　first_name VARCHAR2(25),

　　userid VARCHAR2 CONSTRAINT s_emp_userid_nn NOT NULL

　　CONSTRAINT s_emp_userid_uk UNIQUE,

　　start_date DATE DEFAULT SYSDATE,

　　comments VARCHAR2(25),

　　manager_id NUMBER(7),

　　title VARCHAR2(25),

　　dept_id NUMBER(7)   CONSTRAINT s_emp_dept_id_fk REFERENCES s_dept(id),

　　salary NUMBER(11,2),

　　commission_pct NUMBER(4,2)  CONSTRAINT s_emp_commission_pct_ck CHECK

　　(commission_pct IN(10,12.5,15,17.5,20))

　　);

　　9. 通过子查询建表

　　通过子查询建表的例子

　　SQL>CREATE TABLE emp_41 AS SELECT id, last_name, userid, start_date

　　FROM s_emp WHERE dept_id = 41;

　　SQL> CREATE TABLE A as select * from B where 1=2;

　　只要表的结构.

　　10. 用子查询建表的注意事项

　　可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。

　　用子查询方式建立的表，只有非空NOT NULL的约束条件能继承过来,

　　其它的约束条件和默认值都没有继承过来.

　　根据需要，可以用alter table add constraint ……再建立其它的约束条件，如primary key等.

　　11. Foreign Key的可选参数ON DELETE CASCADE

　　在创建Foreign Key时可以加可选参数:

　　ON DELETE CASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除.

　　如果没有ON DELETE CASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.

　　12. 如果数据库表里有不满足的记录存在，建立约束条件将不会成功.

　　13. 给表创建和删除同义词的例子

　　SQL> CREATE SYNONYM d_sum

　　2 FOR dept_sum_vu;

　　SQL> CREATE PUBLIC SYNONYM s_dept

　　2 FOR alice.s_dept;

　　SQL> DROP SYNONYM s_dept;

　　十、ORACLE里的数据字典

　　1. 什么是数据字典?ORACLE的数据字典是数据库的重要组成部分之一，它随着数据库

　　的产生而产生, 随着数据库的变化而变化, 体现为sys用户下所有的一些表和视图.

　　2. 数据字典里存了以下内容：

　　用户信息

　　用户的权限信息

　　所有数据对象信息表的约束条件统计分析数据库的视图等

　　不能手工修改数据字典里的信息.

　　3. 常用的数据字典

　　Dictionary 存放所有数据表，视图，同义词名称和解释

　　Dict_columns 数据字典里字段名称的和解释

　　Dba_users 用户 Dba_tablespaces 表空间

　　Dba_data_files 数据库的文件 Dba_free_space 空闲表空间

　　Dba_rollback_segs 回滚段

　　User_objects 数据对象 User_constraints 约束条件

　　User_sequences 序列号 User_views 视图

　　User_indexes 索引 User_synonyms 同义词

　　Session_roles 用户的角色 User_role_privs 用户的角色权限

　　User_sys_privs 用户的系统权限 User_tab_privs 用户的表级权限

　　V$session 实时用户情况 V$sysstat 实时系统统计

　　V$sesstat 实时用户统计 V$sgastat 实时SGA使用

　　V$locked_object 实时锁 V$controlfile 控制文件

　　V$logfile 日志文件 V$parameter 参数文件

　　4. 数据字典的分类

　　数据字典四大类别

　　User_ 用户下所有数据库对象

　　All_ 用户权限范围内所有的数据库对象

　　Dba_ 所有的数据库对象

　　V$Content$nbsp; 统计分析数据库的视图 赋于oem_monitor权限非DBA用户也可查询V$*视图

　　5. 查询数据字典

　　SQL> select * from dictionary where instr(comments,’index’)>0;

　　SQL> select constraint_name, constraint_type,

　　2 search_condition, r_constraint_name

　　3 from user_constraints

　　4 where table_name = ‘&table_name’; [NextPage] 　十一. 控制数据

　　1 、INSERT(往数据表里插入记录的语句)

　　SQL> insert into 表名(字段名1, 字段名2, ……) values ( 值1, 值2, ……);

　　SQL> insert into 表名(字段名1, 字段名2, ……) select (字段名1, 字段名2, ……)

　　from 另外的表名 where 条件;

　　可以用&标记变量的方法多次输入记录

　　快速插入数据的方法, 一般用于大于128M的数据转移

　　SQL> insert /*+ append */ into 表名

　　select * from 另外的用户名 .另外的表名 WHERE 条件;

　　SQL> commit;

　　注意事项：

　　用INSERT /*+ APPEND */ 的方法会对target_tablename产生级别为6的独占锁，

　　如果运行此命令时还有对target_tablename的DML操作会排队在它后面,

　　对OLTP系统在用的表操作是不合适的。

　　2. 插入字符串类型的字段的注意事项:

　　字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’

　　如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个 单引号’ ’

　　字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验

　　‘’ 标记是NULL, user 标明当前用户

　　日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒

　　用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)

　　TO_DATE( )还有很多种日期格式, 可以参看ORACLE DOC.

　　年-月-日 小时:分钟:秒 的格式YYYY-MM-DD HH24:MI:SS

　　NSERT时最大可操作的字符串长度小于等于4000个单字节,

　　如果要插入更长的字符串, 请考虑字段用CLOB类型,

　　方法借用ORACLE里自带的DBMS_LOB程序包.

　　3、UPDATE (修改数据表里记录的语句)

　　SQL> UPDATE 表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;

　　如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL,

　　最好在修改前进行非空校验;

　　值N超过定义的长度会出错, 最好在插入前进行长度校验.

　　新功能，可以修改子查询后的结果集

　　例子：SQL> update (select * from s_dept) set id=50 where id=60;

　　4、DELETE (删除数据表里记录的语句)

　　SQL> DELETE FROM 表名 WHERE 条件;

　　注意：删除记录并不能释放ORACLE里被占用的数据块表空间.

　　它只把那些 被删除的数据块标成unused.

　　如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间

　　SQL> TRUNCATE TABLE 表名;

　　此操作不可回退.

　　5、 SQL语句的分类

　　数据定义语言(DDL)：create、alter、drop（创建、修改结构、删除）（其他：rename）

　　数据操纵语言(DML)：insert、delete、select、update（增、删、查、改）（其他：truncate）

　　数据控制语言(DCL)：grant、revoke（授权、回收）、set role

　　事务控制：commit、rollback、savepoint（其他：lock table、set constraint、set transaction）

　　审计控制：audit、noaudit

　　系统控制：alter system 会话控制：alter session

　　其他语句：comment（添加注释）、explain plan、analyze、validate、call

　　6、ORACLE里事务控制

　　Commit 提交事务

　　Rollback 回退事务

　　Savepoint 设置断点, 在事务中标记位置, 事务结束, 断点释放

　　事务结束的情况遇到commit或者rollback遇到DDL和DCL语句发现错误，如死锁用户退出

　　SQL*PLUS系统重启或崩溃

　　7. DML操作的注意事项

　　以上SQL语句对表都加上了行级锁, 确认完成后,

　　必须加上事物处理结束的命令COMMIT 才能正式生效,

　　否则改变不一定写入数据库里.行级锁也未能得到释放.

　　如果想撤回这些操作, 可以用命令 ROLLBACK 复原.

　　在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围,

　　应该把它限定在较小 (一万条记录) 范围内,. 否则ORACLE处理这个事物用到很大的回退段.

　　程序响应慢甚至失去响应. 如果记录数上十万以上这些操作,

　　可以把这些SQL语句分段分次完成, 其间加上COMMIT 确认事物处理.

　　太过频繁的commit不好

　　十二、改变表和约束条件

　　1. 改变表的几种情况(1) 运行时会加表级锁

　　改变表的名称

　　SQL> RENAME 表名1 TO 表名2; SQL> ALTER TABLE 表名1 RENAME TO 表名2;

　　在表的后面增加一个字段

　　SQL> ALTER TABLE 表名 ADD 字段名 字段名描述

　　[ DEFAULT expr ][ NOT NULL ][ ,字段名2 ……];

　　修改表里字段的定义描述

　　SQL> ALTER TABLE 表名 MODIFY 字段名1 字段名1描述 [ DEFAULT expr ][ NOT NULL ]

　　[ ,字段名2 ……]; 记录为空时，可以减少字段长度，

　　改变字段类型修改DEFAULT值只作用于修改后的INSERT和UPDATE的记录修改NOT NULL

　　约束只对现存含非空记录的字段起作用

　　1. 改变表的几种情况(2) 运行时会加表级锁

　　删除表里的某个字段

　　SQL> ALTER TABLE 表名 DROP 字段名;

　　给表里的字段加上/禁止/启用约束条件

　　SQL> ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 约束名

　　PRIMARY KEY (字段名1[,字段名2 ……]);

　　SQL> ALTER TABLE 表名 ADD | DISABLE | ENABLE CONSTRAINT 约束名

　　UNIQUE (字段名1[,字段名2 ……]);

　　加唯一关键字或者唯一约束条件时自动建立索引

　　说明：禁止唯一关键字和唯一约束时索引仍然存在，可以被使用.

　　1. 改变表的几种情况(3) 运行时会加表级锁

　　删除表里的约束条件

　　SQL> ALTER TABLE 表名 DROP CONSTRAINTS 约束名 [CASCADE];

　　会把约束相关的索引一起删除. CASCADE能同时删去外键的约束条件.

　　把表放在或取出数据库的内存区

　　SQL> ALTER TABLE 表名 CACHE;

　　SQL> ALTER TABLE 表名 NOCACHE;

　　改变表存储的表空间

　　SQL> ALTER TABLE 表名 MOVE TABLESPACE 表空间名 ;

　　注意: 如果被转移表空间的表含有索引, 表转移后索引变得不可用.

　　我们要删除旧索引,建立新索引

　　2. 删除表及表里的数据

　　删除表

　　SQL> DROP TABLE 表名 [CASCADE CONSTRAINTS];

　　清空表里的记录

　　SQL> TRUNCATE TABLE 表名;

　　按时间清空日志表里的记录，使用重新命名的方法

　　(应用程序可能有短暂出错, 可以选择在不繁忙的时间执行)

　　按原来表A的建表语句创建新表A1,把表A重命名为A2（如果表A上有较频繁的DML操作,

　　会对表加上行级锁，重命名过程用递归的方式循环做，直到DML操作结束，命名成功).

　　把创建新表A1重命名为A

　　历史记录表A2备份或删除

　　3. 删除表后应该注意的问题

　　删除表后把表里的索引一起删去.

　　删除表后会结束基于它的悬而未决的事物

　　删除表后根据表创建的views,synonym,stored procedure,stored function依然存在，

　　但views,synonym变成非法的. 需要手工找出它们并删除

　　如果用了CASCADE CONSTRAINTS会把与它相关的约束一起删除

　　此操作不可回退

　　4. 给表加注释

　　加注释的语法

　　SQL> COMMENT ON TABLE 表名 | COLUMN表名.字段名 IS ‘text‘

　　加注释的例子

　　SQL> comment on table s_emp is ‘Enployee information‘;

　　SQL> comment on column s_emp.last_name is ‘‘;

　　十三、创建序列号

　　1. 创建序列号里各参数的解释

　　SQL> CREATE SEQUENCE name [INCREMENT BY n]

　　[START WITH n] [{MAXVALUE n | NOMAXVALUE}]

　　[{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}]

　　[{CACHE n | NOCACHE}]

　　INCREMENT BY n 一次增长n 个数字

　　NOMAXVALUE 缺省值10E+27

　　NOMINVALUE 缺省值1

　　NOCYCLE 不循环, 常用于唯一关键字

　　CACHE n 在内存里缓存n个序列,出错回退时会丢失

　　oracle8i里默认的n是20

　　序列号的名称一般可以采用“表名_字段名”的命名规则

　　2. 插入自动增长序列号字段的方法

　　INSERT时如果要用到从1开始自动增长的数字做唯一关键字, 应该先建立一个序列号.

　　CREATE SEQUENCE 序列号的名称 (最好是表名+序列号标记) INCREMENT BY 1 START WITH 1

　　MAXVALUE 99999 NOCYCLE NOCACHE;

　　其中最大的值按字段的长度来定,比如定义的自动增长的序列NUMBER , 最大值为999999

　　INSERT 语句插入这个字段值为: 序列号的名称.NEXTVAL

　　例子: SQL> insert into s_dept(id, name, region_id) values (s_dept_id.nextval, ‘finance’, 2);