Oracle的基本操作-dml，ddl，dcl
二、SQL语言
SQL structure query language 结构化查询语言
SQL语句 ： DDL语句 DML语句 、DCL语句
DDL--data define language --create、alter、drop --数据定义语言
DML--data management language --insert、update、delete --数据操作语言
DCL--data control language --grant、revoke --数据控制语言
1.DDL语句的介绍
1.1 创建一个表
SQL> conn scott/scott;
Connected.
SQL> create table t(
2 t1 varchar2(20),
3 t2 date,
4 t3 number,
5 t4 char(7)
6 );
Table created.
SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2(20)
T2 DATE
T3 NUMBER
T4 CHAR(7)
varchar2(20):变长的字符串类型
char(7):定长的字符串类型，不足7位用空格补足7位
number(长度，精度) 默认number(12,2)
date : 时间类型
上面四种类型是最常见的数据类型，还有非结构化的数据类型
长文本 long
大对象 blob clob lob （论坛帖子，微博，长微博，博客） --大数据平台
查看表的结构：
SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2(20)
T2 DATE
T3 NUMBER
T4 CHAR(7)
1.2 给表添加一列
SQL> alter table t add t5 varchar2(5);
Table altered.
SQL> desc t;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2(20)
T2 DATE
T3 NUMBER
T4 CHAR(7)
T5 VARCHAR2(5)
1.3 修改列的数据类型
SQL> alter table t modify t5 number;
Table altered.
注意：
如果t5这一列上面存储了数据，那么这条命令不成功的，会报一个错：ORA-01439的错
1.4 修改表的名字和列的名字
SQL> alter table t rename to test; --修改表名
Table altered.
SQL> desc t;
ERROR:
ORA-04043: object t does not exist
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2(20)
T2 DATE
T3 NUMBER
T4 CHAR(7)
T5 NUMBER
SQL> alter table test rename column t5 to t51;
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2(20)
T2 DATE
T3 NUMBER
T4 CHAR(7)
T51 NUMBER
1.5 删除列
SQL> alter table test drop column t51; --删除一列
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2(20)
T2 DATE
T3 NUMBER
T4 CHAR(7)
SQL> alter table test drop(t3,t4); --删除多列
Table altered.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
T1 VARCHAR2(20)
T2 DATE
1.6 给表和列添加注释
SQL> comment on table test is 'test table'; --给表添加注释
怎样查询表的注释:
select * from user_tab_comments where table_name='TEST';
SQL> comment on column test.t1 is 'test column'; --给列添加注释
Comment created.
怎样查询列的注释：
select * from user_col_comments where table_name='TEST';
1.7 删除一个表
注意：删除表的时候会把表的定义还有数据全部删掉
SQL> drop table test;
Table dropped.
二、表的主外键约束参考关系的原理和创建
（1）实体完整性
主键
（2）参考完整性
外键
（3）用户自定义的完整性
一般不在数据库中实现
2.1 给表添加主键
SQL> alter table t add constraint PK_T primary key(t1);
Table altered.
查询用户自己所创建的主键：
select * from user_constraints;
2.2 将表中的主键删除
SQL> alter table test drop constraint SYS_C006822;
Table altered.
2.3 给表添加外键
A. 克隆表emp_t,给这个表添加主键
SQL> create table emp_t as select * from emp;
Table created.
SQL> alter table emp_t add constraint PK_EMP_T primary key(empno);
Table altered.
B. 克隆表dept_t,给这个表添加主键
SQL> create table dept_t as select * from dept;
Table created.
SQL> alter table dept_t add constraint PK_DEPT_T primary key(deptno);
Table altered.
C. 在emp_t上做外键参考dept_t
SQL> alter table emp_t add constraint FK_EMP_T foreign key(deptno) references dept_t(deptno);
Table altered.
2.4 删除外键分析
（1） 尝试删除参考源
SQL> drop table dept_t;
drop table dept_t
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
分析：被参考的源是不能够被删除的，如果一定要删除的话，可以使用下面这两种方法：
方法1：先删除掉副表，在去删除主表
方法2：先删除掉外键，再去删除主表
SQL> alter table emp_t drop constraint FK_EMP_T;
Table altered.
SQL> drop table dept_t;
Table dropped.
方法3：将外键禁用掉，在删除主表
SQL> alter table emp_t disable constraint FK_EMP_T;
Table altered.
SQL> drop table dept_t;
drop table dept_t
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
（2）删除主表上的数据
SQL> delete from dept_t where deptno=10;
delete from dept_t where deptno=10
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_EMP_T) violated - child record found
分析：因为在emp_t表中有关于deptno=10的记录，为了参考完整性，oracle禁止删除该数据
如果一定要删除
方法1：删除掉外键后再删除数据
方法2：将副表上deptno=10删除再去删除主表的数据
删除主表上的数据，主表上的主键数据在副表上没有对应，比如删除deptno=40的数据
2.5 给表添加约束（定义用户自定义的完整性）
增加一个check
SQL> alter table emp_t add constraint dept_no_ck check(deptno in(10,20,30,40));
Table altered.
3. DML语句的介绍
3.1 insert语句
SQL> insert into t values('x11',sysdate);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
NAME NOWDATE
-------------------- -------------------
x11 2014-06-09 15:23:42
SQL> insert into t values('x12',to_date('20131212','yyyy-mm-dd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
NAME NOWDATE
-------------------- -------------------
x11 2014-06-09 15:23:42
x12 2013-12-12 00:00:00
SQL> select sysdate from dual;
SYSDATE
-------------------
2014-06-09 15:25:42
dual ：哑元表 n列单行的一个表
3.2 update语句
SQL> update t set nowdate=sysdate+1 where name='x12';
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t;
NAME NOWDATE
-------------------- -------------------
x11 2014-06-09 15:23:42
x12 2014-06-10 15:28:35
3.3 delete语句
SQL> delete from t where name='x12';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t;
NAME NOWDATE
-------------------- -------------------
x11 2014-06-09 15:23:42
整个表删除的话
SQL> delete from t;
1 row deleted.
SQL> commit;
Commit complete.
3.4 截断表的操作
SQL> truncate table t;
Table truncated.
SQL> select * from t;
no rows selected
delete全表和truncate全表区别：
1.delete操作会写日志，truncate操作不写日志
2.delete操作比较慢，truncate快
3.delete操作可以有where条件，而truncate必须是全表删除
truncate比较危险，数据可能找不回来。
3.5 表的克隆--CAST方式创建表
（1）克隆表的数据跟结构
SQL> create table dept_r as select * from dept;
Table created.
SQL> desc dept_r;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> select * from dept_r;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
（2）只克隆表的结构而不克隆表的数据
SQL> drop table dept_r;
Table dropped.
SQL> create table dept_r as select * from dept where 1=2;
Table created.
SQL> desc dept_r;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> select * from dept_r;
no rows selected
通过逻辑操作的方式插入数据
SQL> insert into dept_r select * from dept;
4 rows created.
SQL> commit;
Commit complete.
总结：
克隆表只能克隆表的结构或者数据，约束是无法克隆的。
4. DCL语句的介绍
grant、revoke
SQL> conn xp/xp;
Connected.
SQL> conn /as sysdba
Connected.
SQL> revoke connect from xp;
Revoke succeeded.
SQL> conn xp/xp;
ERROR:
ORA-01045: user XP lacks CREATE SESSION privilege; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn xp/xp;
Connected.
SQL> create table t(id number);
create table t(id number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> conn /as sysdba
Connected.
SQL> grant resource to xp;
Grant succeeded.
SQL> conn xp/xp;
Connected.
SQL> create table t(id number);
Table created.
两个权限一起授予：
SQL> grant connect,resource to xp;
Grant succeeded.