SQL之DDL,DML,DCL,TCL -手记-优快云博客

SQL
DQL：数据库查询语言
DDL：数据定义语言定义构成数据库数据的结构
create，alter，drop，rename
DML：数据操作语言对数据库的表进行操作
insert，delete，update，merge
DCL：数据库控制语言控制用户对数据库对象的操作权限
grant，revoke
TCL：事务处理语言保证数据的一致性
commit，rollback，savepoint

references:
dba_ dba下查看所有
all_ 有权限访问的
user_ 当前用户的

DML：数据操作语言
行和列
DML操作行
rowid：前6位表示存储在哪个数据对象，接下来3为表示哪个数据文件，接下来6位表示哪个块，最后3位表示在哪一行
dbms_rowid包在dba_sources视图中
tab dict v$datafile

insert
insert into table values (...)

insert all
when 1=1
into tab1 (col1,col2,col3...)
values (c1,c2,c3...)
when condition2
into tab2 (col1,col2,col3...)
values (c1,c2,c3...)
...
select c1,c2,c3... from tab where ...

update
update table set column='new' where ...
update table set dname=(select...) where...
delete
delete from tab where ...
truncate
是DDL语句，自动提交
merge

merge into employees e using new_employees n
on (e.employee_id=n.employee_id)
when matched then
update set e.salary=n.salary
when not matched then
insert (employee_id,last_name,salary)
values (n.employee_id,n.last_name,n.salary)
如果on 后面比较的值不唯一的话就相当与多次更新
会报ORA-30926: unable to get a stable set of rows in the source tables错误
即比较列需要唯一

TCL：事务处理语言
commit,rollback,savepoint，select for update
SQL> show autocommit
show autocommit
autocommit OFF
SQL> set autocommit on

SQL> select * from dept1;
7 rows selected
SQL> savepoint s1;
SQL> insert into dept1 select * from dept;
SQL> select * from dept1;
11 rows selected.
SQL> savepoint s2;
SQL> delete from dept2;
SQL> rollback to savepoint s2;
SQL> select * from dept1;
11 rows selected.
SQL> rollback

select * from emp for update;
在提交或回滚以前其它会话不能操作这些行

DCL：数据库控制语言
控制用户对数据库访问的权限
对数据库对象的访问权限
系统权限： create ..，drop ..,alter(system,dateabase).., create [d,a] any ..
sys/from system_privilege_map 当前数据库版本支持的权限
sys/dba_sys_privs 查看当前可以授予用户的系统权限
user：
user_sys_privs 当前用户具备哪些系统权限
session_privs 当前用户具备哪些会话权限
user_role_privs 查看授予的角色
role_sys_privs 查看角色授予的系统权限
grant create table to a with admin option; 回收时级联的系统权限不回收
revoke create table from a；

对象权限
select,insert,update,delete,alter,execute,all
grant select on hr.employees to a;
revoke update on hr.employees from scott;
sys：
dba_tab_privs 查询用户被授予的对象权限 sys
dba_col_privs 用户具有哪些操作对象列的权限
table_privilege_map 查询系统有哪些对象权限
当前用户：
user_tab_privs 查看当前用户的对象权限
user_col_privs 查看当前用户对列的权限

级联：with grant option
撤销对象权限会级联，授予的权限全部被回收

角色权限
dba_roles：查看角色
dba_role_privs 用户被授予哪些角色
user_role_privs 当前用户被授予的角色
role_sys_privs/role_tab_privs查看当前用户被哪些角色授予的系统/对象权
role_role_privs 角色具有哪些角色权限

create role boss;
grant to boss;
grant boss to me;
revoke programer from me;

alter user me defalt role none; 用户默认不启用角色
set role boss; 启用角色
alter user me default role connect; 修改默认角色
create role boss identified using ;
以特定的过程启用角色才生效

DDL：数据定义语言
表：
create table
create table ... default tablespace ...
create table tmp as select * from emp; 不复制主键唯一外键约束
表名1-30 以字母开头，可以数字$,_,#
char() 固定字符类型 2000
varchar2() 可变长字符 4000
number(m,n) 数字类型
date 日期型
clob 字符型长度可以达到2GB-1
blob 4G-1
long
五大约束
列级约束(直接在表后面)，表级约束(与列与逗号分隔)
not null 只有列级约束
primary key
unique 唯一性约束，可以在表级定义
foreign key
create table p2(mgr number references p1(empno));
check

user_constraints 查看有哪些约束
user_cons_columns 查看约束的列

修改约束
alter table t1 drop constraint t1_pk
alter table t1 add constraint t1_uq unique(id);
alter table t1 enable constraint t1_uq;
alter table t1 disable constraint t1_uq;
修改表：
rename t1 to tt1;
alter table
alter table t1 add ;
alter table t1 del column ;
alter table t1 rename column c1 to c2;
alter table t1 modify (column)
alter table emp1 set unused column comm;
alter table emp1 drop unused columns; 一次删除表中未使用的列
select * from USER_UNUSED_COL_TABS where table_name='EMP1';

drop table
删除后在回收站
SQL> show recyclebin;
SQL> select * from recyclebin;
SQL> select * from "BIN$679hBBlFK1rgQ28BqMAzcQ==$0";
select * from "BIN$679hBBlFK1rgQ28BqMAzcQ==$0";

ID MAIL
---------- --------------------
1 12345@qq.com
闪回恢复
flashback table "BIN$679hBBlFK1rgQ28BqMAzcQ==$0" to before drop;
彻底删除 purge table "BIN$679hBBlFK1rgQ28BqMAzcQ==$0";
drop table c1 purge;
show parameter recyclebin; 查看回收站的状态
alter session set recyclebin=off; 关闭回收站
purge recyclebin; 清空回收站
alter table t1 read only; 11g新功能，表的只读
表只读的情况下可以进行表移动(消除碎片)
alter table t1 move;

rownum
视图：查询语句的别名
简化语句的书写，提高数据的安全性
简单视图可以做DML 不可以使用函数不要使用order by
复杂视图不可以使用DML 可以使用函数
create [or replace] [force] view v1 as select .. from .. [with read only]
group by/distinct/rownum 创建的视图不能进行delete操作
drop view ..
select * from user_views; 查看当前所有的视图 dba_views(dba下)
force 强制执行，及时表不存在也创建视图
with read only 只允许查询，不允许进行DML

序列sequence: 自动增长的一个值
create sequence <>
increment by <> 步长
start with <> 起始值
minvalue <>|nominvalue 默认无最小，最小能达到10^-26
maxvalue <>|nomaxvalue 默认无最大，最大能达到10^27
cycle|nocycle 默认不循环
cache <>|nocache 缓存，默认不打开 cache 3代表存3个数字

nextval:序列的下一个值
cuurval:序列的当前值

alter sequence <> increment by <> minvalue<> maxvalue <> cycle cache..
初始值不能修改，其它的都可以修改,使用cycle时要使用cache

user_sequences：查看当前用户的序列
dba_sequences：在dba下查看 where sequence_name='S1'

同义词synonym：对象的别名
create synonym <> for ..
私有同义词，不能跟对象同名需要create synonym权限
公有同义词，可以跟对象同名需要create public synonym权限
如果同义词对象同名，先访问对象，域私有同义词同名，先私有
使用同义词时需要对同义词引用的对象的访问权限
如果同义词引用的对象删除后重建，使用同义词时要重新编译
alter synonym <> compile;
user_synonyms 查看当前私有同义词
dba_synonyms 用dba用户查看共有同义词
1.简化sql书写；2.提高安全性；3.数据库迁移后不修改程序；4.方便远程连接
drop [public] synonym <>;

索引index：加快查询速度
索引分类：
普通索引(b-tree)，反键索引，hash索引，唯一性索引，位图索引，分区索引...
哪些列上可以建索引：
经常在where子句中出现的列
经常要查询的列
主键的列
唯一性约束的列
join 中出现的列
group by出现的列
自动创建： primary key和unique列
不需要有索引的列：
数据量很少的列
很少使用的列
需要查询的数据很多需要的信息少于2%需要创建索引
经常修改，查询用的很少
创建：
create index <> on tabname(column) tablespace <>
可以创建联合索引，最多32个列
set timing on; 打开时间跟踪
user_indexes; 查看索引

——————————————————————————————————————————————————————————————————————
——————————————————————————————————————————————————————————————————————
SQL> select rowid,deptno,dname,loc from dept;
select rowid,deptno,dname,loc from dept;

ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAASZFAAEAAAACHAAA 10 ACCOUNTING NEW YORK
AAASZFAAEAAAACHAAB 20 RESEARCH DALLAS
AAASZFAAEAAAACHAAC 30 SALES CHICAGO
AAASZFAAEAAAACHAAD 40 OPERATIONS BOSTON

SQL> select dbms_rowid.rowid_relative_fno(rowid) fid,dbms_rowid.rowid_block_number(rowid) rid from dept where deptno=10;
select dbms_rowid.rowid_relative_fno(rowid) fid,dbms_rowid.rowid_block_number(rowid) rid from dept where deptno=10;

FID RID
---------- ----------
4 135

SQL> select dbms_rowid.rowid_object(rowid) oid,dbms_rowid.rowid_relative_fno(rowid) fid,dbms_rowid.rowid_block_number(rowid) rid ,dbms_rowid.rowid_row_number(rowid) nid from dept where deptno=10;
select dbms_rowid.rowid_object(rowid) oid,dbms_rowid.rowid_relative_fno(rowid) fid,dbms_rowid.rowid_block_number(rowid) rid ,dbms_rowid.rowid_row_number(rowid) nid from dept where deptno=10;

OID FID RID NID
---------- ---------- ---------- ----------
75333 4 135 0

SQL> grant create session to a ;
grant create session to a ;

Grant succeeded.

SQL> conn a/a;
conn a/a;
Connected.

SQL> select * from user_sys_privs;
select * from user_sys_privs;

USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
A CREATE SESSION NO

SQL> conn / as sysdba;
conn / as sysdba;
Connected.
SQL> grant create table,create synonym to a;
grant create table,create synonym to a;

Grant succeeded.

SQL> conn scott/tiger;
conn scott/tiger;
Connected.
SQL> select * from user_sys_privs; #当前用户所具有的系统权限

USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT UNLIMITED TABLESPACE NO

SQL> select * from session_privs; #当前系统的会话权限
select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

10 rows selected.

SQL> select * from user_role_privs; #查看授予的角色
select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
SCOTT CONNECT NO YES NO
SCOTT RESOURCE NO YES NO

SQL> select * from role_sys_privs; #查看角色授予的系统权限
select * from role_sys_privs;

ROLE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE CLUSTER NO
CONNECT CREATE SESSION NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE TABLE NO

9 rows selected.

SQL> conn /as sysdba;
conn /as sysdba;
Connected.
SQL> revoke create session,create table,create synonym from a;

Revoke succeeded.

SQL> select grantee,owner,table_name,owner,privilege from dba_tab_privs where
grantee='A';
select grantee,owner,table_name,owner,privilege from dba_tab_privs where
grantee='A';

GRANTEE OWNER
------------------------------ ------------------------------
TABLE_NAME OWNER
------------------------------ ------------------------------
PRIVILEGE
----------------------------------------
A HR
EMPLOYEES HR
SELECT

SQL> select * from table_privilege_map;
select * from table_privilege_map;

PRIVILEGE NAME

SQL> grant select,update,insert on hr.employees to scott;
grant select,update,insert on hr.employees to scott;

Grant succeeded.

SQL> revoke update on hr.employees from scott;
revoke update on hr.employees from scott;

Revoke succeeded.

SQL> grant update(department_id) on hr.employees to scott;
grant update(department_id) on hr.employees to scott;

Grant succeeded.

SQL> grant all on hr.employees to scott;
grant all on hr.employees to scott;

Grant succeeded.

SQL> select * from dba_roles where role='RESOURCE';
select * from dba_roles where role='RESOURCE';

ROLE PASSWORD AUTHENTICAT
------------------------------ -------- -----------
RESOURCE NO NONE

SQL> SELECT * FROM dba_role_privs where granted_role='RESOURCE';
SELECT * FROM dba_role_privs where granted_role='RESOURCE';

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
WMSYS RESOURCE NO YES
SCOTT RESOURCE NO YES
SPATIAL_CSW_ADMIN_USR RESOURCE NO YES
HR RESOURCE NO YES
LOGSTDBY_ADMINISTRATOR RESOURCE NO YES
EXFSYS RESOURCE NO YES
SPATIAL_WFS_ADMIN_USR RESOURCE NO YES
OE RESOURCE NO YES
PM RESOURCE NO YES
A RESOURCE NO YES
CTXSYS RESOURCE NO YES

GRANTEE GRANTED_ROLE ADM DEF
------------------------------ ------------------------------ --- ---
MDSYS RESOURCE NO YES
OLAPSYS RESOURCE NO YES
XDB RESOURCE NO YES
APEX_030200 RESOURCE YES YES
BI RESOURCE NO YES
SYS RESOURCE YES YES
SYSMAN RESOURCE NO YES
IX RESOURCE NO YES
SH RESOURCE NO YES
OUTLN RESOURCE NO YES
MDDATA RESOURCE NO YES

22 rows selected.

SQL> create table n1(id number(2) not null);
SQL> alter table n1 modify(id number(2) null);
SQL> alter table n1 add (name varchar2(10));

SQL> create table u1(id number unique);
SQL> create table u2(id number constraint u_id unique);
SQL> create table u2(id number,unique(id));
SQL> create table u3(id number,constraint u_id unique(id));
SQL> create table u4(id number,name varchar2(10),constraint un_id unique(id,name));

SQL> create table p2(mgr number references p1(empno));
SQL> create table p3(mgr number constraint fk_p1_empno references p1(empno));
SQL> create table p4(mgr number,foreign key(mgr) references p1(empno));
SQL> create table p4(mgr number,constraint fk_mgr_p1_empno foreign key(mgr) references p1(empno));

SQL> alter table p4 add constraint pk_p2_mgr primary key(mgr);

SQL> create table c1(id number(5),mail varchar2(20) check(mail like '%_%@%_%.%_%'));
SQL> insert into c1 values(1,'12345@qq.com');

SQL> select constraint_name,table_name,status from user_constraints;
SQL> select constraint_name,table_name,column_name from user_cons_columns;

SQL> alter table n1 drop primary key
SQL> alter table n1 drop unique(id);
SQL> alter table n1 drop constraint SYS_C0011272;

SQL> show recyclebin;
SQL> select * from recyclebin;
SQL> select * from "BIN$679hBBlFK1rgQ28BqMAzcQ==$0";
select * from "BIN$679hBBlFK1rgQ28BqMAzcQ==$0";

ID MAIL
---------- --------------------
1 12345@qq.com

SQL> flashback table "BIN$679hBBlFK1rgQ28BqMAzcQ==$0" to before drop;
Flashback complete.
SQL> select * from c1;
select * from c1;

ID MAIL
---------- --------------------
1 12345@qq.com

SQL> purge table "BIN$679hBBlDK1rgQ28BqMAzcQ==$0";
Table purged.

QL> drop table c1 purge;

SQL> conn / as sysdba;
conn / as sysdba;
Connected.
SQL> show parameter recyclebin;
SQL> desc recyclebin;

SQL> create view v1 as select * from emp where deptno=20;

SQL> create view v2 as select deptno from dept;
create view v2 as select deptno from dept;

View created.

SQL> select * from v2;
select * from v2;

DEPTNO
----------
10
20
30
40

SQL> insert into v2 values(50);
insert into v2 values(50);

1 row created.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50

SQL> delete from v2 where deptno=50;
SQL> select * from dept;
select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select rownum rr,deptno,dname,loc from dept;
select rownum rr,deptno,dname,loc from dept;

RR DEPTNO DNAME LOC
---------- ---------- -------------- -------------
1 10 ACCOUNTING NEW YORK
2 20 RESEARCH DALLAS
3 30 SALES CHICAGO
4 40 OPERATIONS BOSTON

SQL> create sequence s1 increment by 1 start with 2 minvalue 1 maxvalue 10;
SQL> create table s11(id number(2),name varchar2(10));
SQL> select s1.nextval from dual;
NEXTVAL
----------
2
SQL> select s1.currval from dual;
CURRVAL
----------
2
SQL> insert into s11 values(s1.currval,'AA');
SQL> insert into s11 values(s1.nextval,'BB');
SQL> insert into s11 values(s1.nextval,'CC');
SQL> select * from s11;
ID NAME
---------- ----------
2 AA
3 BB
4 CC

SQL> alter sequence s1 cycle cache 2; #修改序列为循环

SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
--------------- ---------- ---------- ------------ - - ---------- -----------
S1 1 10 1 Y N 2 3

SQL> conn /as sysdba;
conn /as sysdba;
Connected.
SQL> grant create synonym to scott;

SQL> create synonym hremp for hr.employees;
SQL> select * from hremp;

SQL> select * from system_privilege_map where name like '%SYNONYM%';
select * from system_privilege_map where name like '%SYNONYM%';

PRIVILEGE NAME PROPERTY
---------- ---------------------------------------- ----------
-80 CREATE SYNONYM 0
-81 CREATE ANY SYNONYM 0
-82 DROP ANY SYNONYM 0
-85 CREATE PUBLIC SYNONYM 0
-86 DROP PUBLIC SYNONYM 0

SQL> grant create public synonym to scott;
grant create public synonym to scott;

Grant succeeded.

SQL> set timing on;
set timing on;
SQL> select index_name,table_name,TABLESPACE_NAME from user_indees;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28713356/viewspace-1061330/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28713356/viewspace-1061330/