数据库对象日常操作
1.用户
1.1基本语句
SYS用户执行:
--查看所有用户信息
select * from dba_users;
Select username,password,account_status,default_tablespace,temporary_tablespace,profile from dba_users;
--查询数据库dcs_sys用户下的所有对象
select * from all_objects where owner='DCS_SYS';
select owner,object_name,object_type,status from all_objects where owner='DCS_SYS';
#状态status: INVALID无效,VALID有效
-- 创建用户
create user test01 identified by test01; #默认是user表空间
-- 授权
grant connect,resource to test01;
-- 撤销权限
revoke connect from test01;
-- 修改密码
alter user test01 identified by 123456;
--解锁用户
alter user test account unlock;
-- 删除用户,加上cascade则将用户连同其创建的东西全部删除
drop user test01 cascade;
--当前登录用户执行
select * from all_users; 查看你能管理的所有用户!
select * from user_users; 查看当前用户信息 !
1.2用户权限
1) 系统权限–对用户而言
connect:拥有Connect权限的用户只可以登录Oracle,不可以创建实体,不可以创建数据库结构。
resource:RESOURCE:拥有Resource权限的用户只可以创建实体,不可以创建数据库结构。
dba:拥有全部特权,是系统最高权限,只有DBA才可以创建数据库结构。
2) 实体权限 –针对表或视图而言的
select, update, insert, alter, index, delete, all //all包括所有权限,execute //执行存储过程权限
1.查询用户拥有的系统权限
SQL> SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE = 'SCOTT';(dba权限)
2.查询用户拥有的对象权限
SQL> SELECT GRANTEE,TABLE_NAME,PRIVILEGE FROM DBA_TAB_PRIVS WHERE GRANTEE = 'SCOTT';(dba权限)
3.查询用户拥有的角色
SQL> SELECT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE ='SCOTT';(dba权限)
4.查看用户角色
SELECT * FROM USER_ROLE_PRIVS;
5.查看当前用户权限:
select * from session_privs;
6.查看所有用户所拥有的角色
SELECT * FROM DBA_ROLE_PRIVS;(dba权限)
7.查看所有角色
select * from dba_roles;(dba权限)
1.3用户会话
1、查询用户会话信息:
SQL> select username, sid, serial#, machine from v$session;
SQL> select * from v$session;
STATUS:这列用来判断session状态是:
Achtive:正执行SQL语句(waiting for/using a resource)
Inactive:等待操作(即等待需要执行的SQL语句)
Killed:被标注为删除
2、删除用户会话信息:
SQL> Alter system kill session 'sid, serial#';
在ORACLE数据库杀掉会话进程有三种方式:
1: ALTER SYSTEM KILL SESSION
关于KILL SESSION Clause ,如下官方文档描述所示,alter system kill session实际上不是真正的杀死会话,它只是将会话标记为终止。等待PMON进程来清除会话。
可以使用ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE 来快速回滚事物、释放会话的相关锁、立即返回当前会话的控制权。
2: ALTER SYSTEM DISCONNECT SESSION
ALTER SYSTEM DISCONNECT SESSION 杀掉专用服务器(DEDICATED SERVER)或共享服务器的连接会话,它等价于从操作系统杀掉进程。它有两个选项POST_TRANSACTION和IMMEDIATE, 其中POST_TRANSACTION表示等待事务完成后断开会话,IMMEDIATE表示中断会话,立即回滚事务。
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
3: KILL -9 SPID (Linux) 或 orakill ORACLE_SID spid (Windows)
可以使用下面SQL语句找到对应的操作系统进程SPID,然后杀掉。当然杀掉操作系统进程是一件危险的事情,尤其不要误杀。所以在执行前,一定要谨慎确认。
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND';
1.4数据库最大连接数
select count(*) from v$process --当前的连接数
select value from v$parameter where name = 'processes' --数据库允许的最大连接数
*修改最大连接数:
alter system set processes = 1000 scope = spfile
(需要重启数据库才能实现连接数的修改)
2. 表
-- 查询当前用户下有哪些表
select * from user_tables;
select table_name,tablespace_name,table_lock from user_tables;
-- 查询当前用户下可以访问哪些表
SELECT * FROM all_tables
-- 查询当前数据库所有的表, 需要你有 DBA 的权限
SELECT * FROM dba_tables
2.1表结构操作
--查看当前用户下表结构
select * from user_tab_columns;
1、创建表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表:
A:select * into table_new from table_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only<仅适用于Oracle>
--查询建表语句
SELECT DBMS_METADATA.GET_DDL('TABLE','table_name') FROM DUAL ;
2、删除表
drop table tabname
--删除表及关联
drop table tabname cascade constraints;
3、重命名表
说明:alter table 表名 rename to 新表名
eg:alter table tablename rename to newtablename
4、增加字段
说明:alter table 表名 add (字段名 字段类型 默认值 是否为空);
例:alter table tablename add (ID int);
eg:alter table tablename add (ID varchar2(30) default '空' not null);
5、修改字段(不能有数据)
说明:alter table 表名 modify (字段名 字段类型 默认值 是否为空);
eg:alter table tablename modify (ID number(4));
6、重命名字段(不能有数据)
说明:alter table 表名 rename column 列名 to 新列名 (其中:column是关键字)
eg:alter table tablename rename column ID to newID;
7、删除字段
说明:alter table 表名 drop column 字段名;
eg:alter table tablename drop column ID;
8、添加主键
alter table tabname add primary key(col)
9、删除主键
alter table tabname drop primary key(col)
10、创建索引
create [unique] index idxname on tabname(col….)
11、删除索引
drop index idxname
注:索引是不可更改的,想更改必须删除重新建。
12、创建视图
create view viewname as select statement
13、删除视图
drop view viewname
2.2表数据操作
1、数据查询
select <列名> from <表名> [where <查询条件表达试>] [order by <排序的列名>[asc或desc]]
2、插入数据
insert into 表名 values(所有列的值);
insert into test values(1,'zhangsan',20);
insert into 表名(列) values(对应的值);
insert into test(id,name) values(2,'lisi');
插入空值
insert into student (xh,xm,sex,birthday) values(‘021’,’BLYK’,’男’,null);
3、更新数据
update 表 set 列=新的值 [where 条件] -->更新满足条件的记录
update test set name='zhangsan2' where name='zhangsan'
update 表 set 列=新的值 -->更新所有的数据
update test set age =20;
4、删除数据
- delete from 表名 where 条件 -->删除满足条件的记录
delete from test where id = 1;
delete from test -->删除所有
commit; -->提交数据
rollback; -->回滚数据
delete方式可以恢复删除的数据,但是提交了,就没办法了 delete删除的时候,会记录日志 -->删除会很慢很慢
- truncate table 表名
删除所有数据,不会影响表结构,不会记录日志,数据不能恢复 -->删除很快
- drop table 表名
删除所有数据,包括表结构一并删除,不会记录日志,数据不能恢复-->删除很快
2.3表结构/数据复制
- 表数据复制(able2已经存在,表结构和table1一样)
insert into table1 (select * from table2);
- 存在一个表B和表A的表结构不一致,将A中的数据复制给B表
insert into B (x1,x2,x3...) select x1,x2,x3... from A;
- 复制表结构(只复制表结构,不复制其他表对象,如主外键等)
create table table1 select * from table2 where 1>1;
- 复制表结构,如果目标表table1不存在,使用下面语句
create table table1 as select * from table2 where 1>1;
- 复制表结构和数据(只复制表结构和数据,不复制其他表对象,如主外键等)
create table table1 select * from table2;
- 复制表结构和数据,如果目标表table1不存在,使用下面语句
create table table1 as select * from table2
- 复制指定字段
create table table1 as select id, name from table2 where 1>1;
3. 约束
--查询约束
1.USER_CONSTRAINTS
2.USER_CONS_COLUMNS
SQL> select constraint_name,table_name,column_name from user_cons_columns where table_name='T1';
--查看当前用户约束,重点字段信息
select constraint_name,
constraint_type,
table_name,
status,
validated,
index_owner,
index_name
from user_constraints;
SQL> select * from USER_CONSTRAINTS where table_name='T1';
constraint_type 中C对应CHECK,P->primary key, r->FOREIGN KEY , U-->UNIQUE
唯一约束
唯一约束
要求该列唯一,允许为空
1.唯一约束 ( unique )
--例如1:
create table test19(
id number ,
name varchar2(30) ,
address varchar2(30) ,
primary key(id) ,
unique(address)
);
---例如2:
create table test20(
id number primary key ,
name varchar2(30) ,
address varchar2(30) unique
);
--例如3:
create table test21(
id number primary key ,
name varchar2(30) ,
address varchar2(30)
);
--给建好的表加上唯一约束
ALTER TABLE test21 ADD unique(address);
主键约束
主键是定位表中单个行的方式,可唯一确定表中的某一行,关系型数据库要求所有表都应该有主键,不过Oracle没有遵循此范例要求,Oracle中的表可以没有主键(这种情况不多见)。关于主键有几个需要注意的点:
- 键列必须必须具有唯一性,且不能为空,其实主键约束 相当于 UNIQUE+NOT NULL
- 一个表只允许有一个主键
- 主键所在列必须具有索引(主键的唯一约束通过索引来实现),如果不存在,将会在索引添加的时候自动创建
1.主键约束 ( primary key )
--例如1:
create table test(
c number(10) primary key
);
--例如2:
create table test1(
c number(10) constraint pk_c primary key
);
--例如3:
create table test2(
c number(10) ,
primary key(c)
);
--例如4:
create table test3(
c number(10),
c1 number(10),
primary key (c,c1)
);
--例如5:
create table test4(
c number(10) ,
constraint pk_test4_c primary key (c)
);
2.给建好的表创建主键:
--例如6:
create table test5(
c number(10)
);
alter table test5 add primary key (c);
3.给建好的表添加主键:不使用默认主键名,自定义主键名
--例如7:
create table test6(
c number(10)
);
alter table test6 add constraint pk_test6_c primary key(c);
外键约束
外键约束定义在具有父子关系的子表中,外键约束使得子表中的列对应父表的主键列,用以维护数据库的完整性。不过出于性能和后期的业务系统的扩展的考虑,很多时候,外键约束仅出现在数据库的设计中,实际会放在业务程序中进行处理。外键约束注意以下几点:
- 外键约束的子表中的列和对应父表中的列数据类型必须相同,列名可以不同
- 对应的父表列必须存在主键约束(PRIMARY KEY)或唯一约束(UNIQUE)
- 外键约束列允许NULL值,对应的行就成了孤行了
其实很多时候不使用外键,很多人认为会让删除操作比较麻烦,比如要删除父表中的某条数据,但某个子表中又有对该条数据的引用,这时就会导致删除失败。
1.外键约束 ( foreign key )
--例如1:Fk 使用“列级约束”来进行建表:
create table test8(
a1 number(10) primary key
);
create table test9(
b1 number(10) primary key,
b2 number(10) references test8(a1)
);
--例如2:Fk 使用“表级约束”来进行建表:
Create table test10 (
a1 number(10) primary key
);
Create table test11(
b number(10) primary key,
b2 number(10),
foreign key(b2)references test10(a1)
);
--例如3:
Create table test12 (
a1 number(10) primary key
);
Create table test13(
b number(10) primary key,
b2 number(10),
foreign key(b2)references test12(a1)
);
--例如4:
Create table test14 (
a1 number(10)
);
alter table test14 add constraint pk_test14_c primary key(a1);
Create table test16(
b number(10),
b2 number(10)
);
alter table test16 add constraint pk_test16_c foreign key(b) references test14(a1);
2.级联删除:
--例如一:(如果删除父表中的某条记录,子表相应记录也被删除)
create table test17(
id number primary key
);
插入操作:insert into test17(id)values(1);
create table test18(
id number primary key,
p_id number references test17(id) on delete cascade
);
插入操作:insert into test18 values(1,1);
删除操作:delete from test17 where id=1;--注意:发现子表的数据已经没有了
--例如二:(如果删除父表某条记录,子表相应记录被置空)
create table parent(
id number primary key
);
insert into parent values (1);
create table chile(
id number primary key,
p_id number references parent(id) on delete set null
);
插入操作:insert into chile values(1,1);
删除操作:delete from parent where id=1;
--注意:删除父表时子表对应列为空
3. Foreign Key 的可选参数 ON DELETE CASCADE
在创建 Foreign Key 时可以加可选参数:
ON DELETE CASCADE 它的含义是如果删除外键主表里的内容,子表里相关的内容将一起被删除.
如果没有 ON DELETE CASCADE 参数,子表里有内容,父表里的主关键字记录不能被删除掉.
检查约束
1.检查约束 ( check )
某列取值范围限制、格式限制等
2.检查只能是男或者女
create table test29(
id number primary key,
sex varchar2(2) check(sex in ('男,女'))
);
create table test30(
id number primary key,
sex varchar2(2) check(sex ='男' or sex='女')
);
create table test31(
id number primary key,
sex varchar2(2)
);
alter table test31 add constraint chkk check (sex ='男' or sex='女');
alter table test31 add constraint chkk check (sex in('男','女'));
3.在一个范围中间
create table test32(
id number primary key,
age number check(age>0 and age<120)
);
create table test33(
id number primary key,
age number check(age between 12 and 30)
);
create table test34(
id number primary key ,
age number
);
alter table test34 add constraint ch_test34 check(age>0 and age<120);
alter table test34 add constraint ch_test34 check(age between 12 and 30);
4.长度大于某个值
create table test35(
id number primary key,
password varchar2(10) check(length(password)=6)
);
create table test36(
id number primary key ,
password varchar2(20)
);
alter table test36 add constraint check_test36 check(length(password)=6);
5.数大于某个值
create table test37(
id number(10)primary key ,
no number(10) check(no>1)
);
create table test38(
id number(10) primary key,
no number(10)
);
alter table test38 add constraint ch_test38 check(no>1);
---------------
6.只能是8位字符,前两位是 0 ,3~4位为数字,第 5 位为"_"下划线,6~8位为字母
create table test39(
id number(10) primary key,
password varchar2(20) check((password like '00[0-9][0-9]/_[a-z,A-Z][a-z,A-Z][a-z,A-Z]%' escape '/')and(length(password)=8) )
);
insert into test39 values (1,'0011_aaa');
create table test40(
id number(10) primary key ,
password varchar2(10)check((password like '00[0-9][0-9][_][a-z,A-Z][a-z,A-Z][a-z,A-Z]%')and(length(password)=8) ));
);
alter table test40 modify password varchar2(10)check((password like '00[0-9][0-9][_][a-z,A-Z][a-z,A-Z][a-z,A-Z]%')and(length(password)>1)
insert into test40 values(1,'0012_abc');
---------------
7.电子邮箱要含有@符号check(字段 like '%@%')
create table test41(
id number(10) primary key,
email varchar2(10) check (email like '%@%')
);
insert into test41 values(1,'12@126.com');
8.SQL中用check约束一列的首字母为's'check(字段 like 's%')
create table test42(
id number(10) primary key ,
name varchar2(10) check(name like 's%')
);
insert into test42 values(1,'sname');
------------
9.检查约束前3位和后8位均为数字字符:check(字段 like '[0-9][0-9][0-9]%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
create table test43(
id number(10) primary key,
no varchar2(10)check(no like '[0-9][0-9][0-9]%[0-9][0-9][0-9][0-9][0-9]')
);
insert into test43 values(1,'12345678');
------------
10.如何建立检查身份证的约束,身份证是18位,最后一位还有可能是X
create table test44(
id number(10) primary key,
no values(18) check( length(no)=18 and right(no,17)like '[0-9]' or right (no,17) like 'x' )
);
insert into test44 values (1,'12345678912345678x');
select 身份证号 from 表名where len(身份证号) = 18 and (right(身份证号,17) like '[0-9]'or right(身份证号,17) like 'x')
11.如何设置区号由0-9之间的数字组成CONSTRAINT
quhao CHECK (quhao LIKE '[0-9][0-9][0-9]'
or quhao LIKE '[0-9][0-9][0-9][0-9]'
or quhao LIKE '[0-9][0-9][0-9][0-9][0-9]'));
解释:quhao LIKE '[0-9]...[0-9]'的号码由表示n位从0到9中的数组成。
quhao LIKE '[0-9][0-9][0-9]' 表示3位的区号,如北京010;
quhao LIKE '[0-9][0-9][0-9][0-9]'表示4位的区号,如三门峡0398;
quhao LIKE '[0-9][0-9][0-9][0-9][0-9]'表示5位的区号,如香港00852
12.最后回复时间 TLastClickT 发贴时间 TTime最后回复时间 必须晚于 发贴时间 并且小于等于当前时间
使用GetDate()函数获取当前时间
设计表在TLastClickT上右击选择约束,新建,
填入([TLastClickT] > [TTime] and [TLastClickT] < GetDate())
或者TiastReply(回帖时间)大于Ttime(发帖时间)
在创表的同时创建表的时候应该还没有回帖吧,为什么要用默认值?
可以添加一个约束
alter table topic alter column add check(TlastReply is null or TlastReply > Ttime)
13.定义前八位为数字或者 -一共是15位,为CHAR型
alter table 表名add constraint chk check(字段 like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'),
constraint chklen check(len(字段)=15)
14.如何限制varchar字段不允许出现单引号的检查约束 !!!
设表为TALBENAME,不能有引号的字段为FIELDNAME 则:
ALTER TABLE tablename ADD CONSTRAINT CK_fieldname CHECK (not fieldname like '%''%')
15.在表中的某列中通过检查约束,让其有某些固定的值
check(sid like 'bd[0-9][0-9][0-9][0-9][0-9][0-9]')
add const ck_num check(num like '[1][2] [4][_] [0-9][0-9] [0-9][a-z]')
16.如何限制varchar字段不允许出现字符串的检查约束 !!!
设表名为TABLENAME,VARCHAR类型的字段为VAR_FIELD.则有:
ALTER TABLE [dbo].[TABLENAME]
ADD CONSTRAINT [CK_TABLENAME]
CHECK (isnumeric([VAR_FIELD]) = 1)
这样,在VAR_FIELD只要出现字段一旦出现非数字内容就会报告错误。
17.电话号码的格式必须为xxxx-xxxxxxxx或手机号11位
alter 表名 add constraint ck_字段名 check (字段 like '[0-9][0-9][0-9][0-9]_[0-9]......'
or length(字段)=11)
18.身份证号是18位且唯一的
alter 表名 add constraint ck_字段名 check (len(字段名)=18 ),
constraint uk_字段名 unique(字段名)
约束状态
很多时候由于业务需要,比如我们有大量的历史数据,需要和现有数据合并,当前表存在数据库约束(如非空约束),而这些历史数据又包含违背非空约束的数据行,为了避免导入时由于违反约束而导入失败,我们通过调整约束状态来达到目的。
数据库约束有两类状态
启用/禁用(enable/disable):是否对新变更的数据启用约束验证
验证/非验证 (validate/novalidate) :是否对表中已客观存在的数据进行约束验证
这两类四种状态从语法角度讲可以随意组合,默认是 enable validate
下面我们来看着四类组合会分别出现什么样的效果:
enable validate : 默认的约束组合状态,无法添加违反约束的数据行,数据表中也不能存在违反约束的数据行;
enable novalidate : 无法添加违反约束的数据行,但对已存在的违反约束的数据行不做验证;
disable validate : 可以添加违反约束的数据行,但对已存在的违反约束的数据行会做约束验证(从描述中可以看出来,这本来就是一种相互矛盾的约束组合,只不过是语法上支持这种组合罢了,造成的结果就是会导致DML失败)
disable novalidate : 可以添加违法约束的数据行,对已存在的违反约束的数据行也不做验证。
拿上面的例子来说,我们需要上传大量违反非空约束的历史数据(从业务角度讲这些数据不会造成系统功能异常),可以临时将约束状态转为 disable novalidate,以保证这些不合要求的数据导入表中
SQL> alter table emp modify constraint emp_ename_nn disable novalidate;
在数据导入完成之后,我们再将约束状态转为enable novalidate 以确保之后添加的数据不会再违反约束
SQL> alter table emp modify constraint emp_ename_nn enable novalidate;
约束维护
1 增加约束
(1) 如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须使用ALTER TABLE语句的ADD子句;
(2) 如果增加NOT NULL约束,那么必须使用ALTER TABLE语句的MODIFY子句,如:
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
constraint_type (column,...)
ALTER TABLE table_name MODIFY column
[CONSTRAINT constraint_name] NOT NULL;
2 修改约束名
在同一个方案中,约束名必须惟一,并且约束名也不能与其他对象同名。当用IMPDP工具或者IMP工具导入其他对象时,如发现有同名的对象,将会出错
语法:
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name
TO new_constraint_name;
例:
ALTER TABLE emp01 RENAME CONSTRAINT SYS_C005028
TO ck_emp01_salary;
SQL> alter table t1 rename constraint PK_T1_ID to new pk01_t1_id;
alter table t1 rename constraint PK_T1_ID to new pk01_t1_id
*
ERROR at line 1: --------主键无法更改名字
ORA-23290: This operation may not be combined with any other operation
SQL> alter table t2 rename constraint fk_t2_id to fk01_t2_id;
SQL> select constraint_name,table_name from user_constraints where table_name='T2';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
FK01_T2_ID T2 -------------外键可以更改名字
SQL> alter table t1 add constraint un_t1_qq unique(qq);
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_T1_ID T1
UN_T1_QQ T1
SQL> alter table t1 rename constraint un_t1_qq to un01_t1_qq;
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_T1_ID T1
UN01_T1_QQ T1
3 禁止约束
禁止约束指使约束临时失效。当禁止了约束之后,约束规则将不再生效。在使用SQL*LOADER或INSERT装载数据之前,为了加快数据装载速度,应该首先禁止约束,然后装载数据。
语法:
ALTER TABLE table_name
DISABLE CONSTRAINT constaint_name [CASCAED];--CASCAED用于指定级联禁止从表的外部键
SQL> insert into t2 values(2,2);
insert into t2 values(2,2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FK01_T2_ID) violated - parent key not
found
SQL> alter table t2 disable constraint fk01_t2_id;
Table altered.
SQL> insert into t2 values(2,2);
1 row created.
#动态SQL批量修改
select 'alter table ' || table_name || ' disable constraint ' || constraint_name || ';' from user_constraints where constraint_type='R';
4 激活约束
语法:
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
例:
SQL> alter table t2 enable constraint fk01_t2_id;
alter table t2 enable constraint fk01_t2_id
*
ERROR at line 1: ---外键激活失败,原因是在外键表中含有主键表中没有的数据
ORA-02298: cannot validate (SYS.FK01_T2_ID) - parent keys not found
SQL> delete t2 where id=2; -------删除数据
SQL> alter table t2 enable constraint fk01_t2_id; ----激活成功
5 删除约束
当删除特定表的主键约束时,如果该表具有相关的从表,那么在删除主键约束时必须带有CASCAED选项
语法:
ALTER TABLE table_name DROP
CONSTRAINT constraint_name |PRIMARY KEY
例一:(删除唯一性约束)
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_T1_ID T1
UN01_T1_QQ T1
SQL> alter table t1 drop constraint un01_t1_qq;
Table altered.
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_T1_ID T1
例二:(删除主键约束,级联删除外键约束)
SQL> alter table t1 drop primary key cascade;
Table altered.
SQL> select constraint_name,table_name from user_constraints where table_name='T2';
no rows selected
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
no rows selected
4. index索引
4.1索引的分类
在关系数据库中,每一行都由一个行唯一标识RowID。RowID包括该行所在的文件、在文件中的块数和块中的行号。索引中包含一个索引条目,每一个索引条目都有一个键值和一个RowID,其中键值可以是一列或者多列的组合。
(一)索引按存储方法分类,可以分为2类:B*树索引和位图索引。
(1)B*树索引的存储结构类似书的索引结构,有分支和叶两种类型的存储数据块,分支块相当于书的大目录,叶块相当于索引到的具体的书页。Oracle用B*树机制存储索引条目,以保证用最短路径访问键值。默认情况下大多使用B*树索引,该索引就是通常所见的唯一索引、逆序索引。
(2)位图索引存储主要用于节省空间,减少oracle对数据块的访问。它采用位图偏移方式来与表的行ID号对应,采用位图索引一般是重复值太多的表字段。位图索引之所以在实际密集型OLTP(联机事物处理)中用的比较少,是因为OLTP会对表进行大量的删除、修改、新建操作。Oracle每次进行操作都会对要操作的数据块加锁。以防止多人操作容易产生的数据库锁等待甚至死锁现象。在OLAP(联机分析处理)中应用位图有优势,因为OLAP中大部分是对数据库的查询操作,而且一般采用数据仓库技术,所以大量数据采用位图索引节省空间比较明显。当创建表的命令中包含有唯一性关键字时,不能创建位图索引,创建全局分区索引时也不能用位图索引。
(二)索引按功能和索引对象分还有以下类型。
(1)唯一索引意味着不会有两行记录相同的索引键值。唯一索引表中的记录没有RowID,不能再对其建立其他索引。在oracle10g中,要建立唯一索引,必须在表中设置主关键字,建立了唯一索引的表只按照该唯一索引结构排序。
(2)非唯一索引不对索引列的值进行唯一性限制。
(3)分区索引是指索引可以分散地存在于多个不同的表空间中,其优点是可以提高数据查询的效率。
(4)未排序索引也称为正向索引。Oracle10g数据库中的行是按升序排序的,创建索引时不必指定对其排序而使用默认的顺序。
(5)逆序索引也称反向索引。该索引同样保持列按顺序排列,但是颠倒已索引的每列的字节。
(6)基于函数的索引是指索引中的一列或者多列是一个函数或者表达式,索引根据函数或表达式计算索引列的值。可以将基于函数的索引建立创建成位图索引。
另外,按照索引所包含的列数可以把索引分为单列索引和复合索引。索引列只有一列的索引为单列索引,对多列同时索引称为复合索引。
4.2查询/创建索引
--查看当前用户下所有索引
select * from user_indexes;
select index_name,index_type,table_name,tablespace_name,status from user_indexes;
# 索引状态status: VALID 索引有效;UNUSABLE 索引不可用
--查看该表的所有索引
select * from user_indexes where table_name = 'TS_GATE'
--查看该表的所有索引列
select * from user_ind_columns where table_name= 'TS_GATE';
1.创建索引语法
CREATE [UNIQUE] | [BITMAP] INDEX index_name --unique表示唯一索引ON table_name([column1 [ASC|DESC],column2 --bitmap,创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1] --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING] --表示创建和重建索引时允许对表做DML操作,默认情况下不应该使用
[NOLINE]
[NOSORT]; --表示创建索引时不进行排序,默认不适用,如果数据已经是按照该索引顺序排列的可以使用
如果不加创建索引的类型,默认是创建非唯一索引
创建不唯一索引
create index emp_ename on employees(ename)
tablespace users
storage(......)
pctfree 0;
创建唯一索引
create unique index emp_email on employees(email)
tablespace users;
创建位图索引
create bitmap index emp_sex on employees(sex)
tablespace users;
创建反序索引
create unique index order_reinx on orders(order_num,order_date)
tablespace users
reverse;
创建函数索引(函数索引即可以是普通的B树索引,也可以是位图索引)
create index emp_substr_empno
on employees(substr(empno,1,2))
tablespace users;
2.修改索引
1)重命名索引
alter index index_sno rename to bitmap_index;
2)合并索引(表使用一段时间后在索引中会产生碎片,此时索引效率会降低,可以选择重建索引或者合并索引,合并索引方式更好些,无需额外存储空间,代价较低)
alter index index_sno coalesce;
3)重建索引
方式一:删除原来的索引,重新建立索引
方式二:alter index index_sno rebuild;
3.删除索引
drop index index_sno;
4.查看索引
select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';
-- eg: create index index_sno on student('name');select * from all_indexes where table_name='student';
4.3重建索引
一、重建索引的前提
1、表上频繁发生update,delete操作;
2、表上发生了alter table ..move操作(move操作导致了rowid变化)
二、重建索引的方式
1 、直接重建:
举例:
alter index indexname rebuild;
alter index indexname rebuild online;
说明:第二种方式比较快,可以在24*7环境中实现,建议使用此方式。
2、drop 原来的索引,然后再创建索引;
举例:
删除索引:drop index IX_PM_USERGROUP;
创建索引:create index IX_PM_USERGROUP on T_PM_USER (fgroupid);
说明:此方式耗时间,无法在24*7环境中实现,不建议使用。
注意点:
1、 执行rebuild操作时,需要检查表空间是否足够;
2、虽然说rebuild online操作允许dml操作,但是还是建议在业务不繁忙时间段进行;
Rebuild操作会产生大量redo log ;
5. Sequence序列
5.1定义
在Oracle数据库中,序列(SEQUENCE)其实是序列号生成器,可以为表中的行自动生成序列号,产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值,可以在插入语句中引用,也可以通过查询检查当前值,或使序列增至下一个值。
创建序列需要CREATE SEQUENCE系统权限。
序列的创建语法如下:
CREATE SEQUENCE 序列名 [INCREMENT BY n] [START WITH n] [{MAXVALUE/ MINVALUE n|NOMAXVALUE}] [] [{CACHE n|NOCACHE}];
参数说明如下:
INCREMENT BY 用于定义序列的步长,如果省略,则默认为1,如果出现负值,则代表序列的值是按照此步长递减的。
START WITH 定义序列的初始值(即产生的第一个值),默认为1。
MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项,代表没有最大值定义,这时对于递增序列,系统能够产生的最大值是10的27次方;对于递减序列,最大值是-1。
MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项,代表没有最小值定义,这时对于递减序列,系统能够产生的最小值是?10的26次方;对于递增序列,最小值是1。
CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环,NOCYCLE代表不循环。如果循环,则当递增序列达到最大值时,循环到最小值;对于递减序列达到最小值时,循环到最大值。如果不循环,达到限制值后,继续产生新值就会发生错误。
CACHE(缓冲)定义存放序列的内存块的大小,默认为20。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲,可以改善序列的性能。
5.2 查询/创建使用
--查询用户下的所有序列
select * from user_squences
1、Create Sequence
首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限。
创建语句如下:
CREATE SEQUENCE seqTest
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXvalue -- 不设置最大值
NOCYCLE -- 一直累加,不循环
CACHE 10; --设置缓存cache个序列,如果系统down掉了或者其它情况将会导致序列不连续,也可以设置为---------NOCACHE
2、得到Sequence值
定义好sequence后,你就可以用currVal,nextVal取得值。
CurrVal:返回 sequence的当前值
NextVal:增加sequence的值,然后返回 增加后sequence值
得到值语句如下:
SELECT Sequence名称.CurrVal FROM DUAL;
如得到上边创建Sequence值的语句为:
select seqtest.currval from dual
在Sql语句中可以使用sequence的地方:
- 不包含子查询、snapshot、VIEW的 SELECT 语句
- INSERT语句的子查询中
- INSERT语句的values中
- UPDATE 的 SET中
如在插入语句中
insert into 表名(id,name)values(seqtest.Nextval,'sequence 插入测试');
注:
- 第一次NEXTVAL返回的是初始值;随后的NEXTVAL会自动增加你定义的INCREMENT BY值,然后返回增加后的值。
CURRVAL 总是返回当前SEQUENCE的值,但是在第一次NEXTVAL初始化之后才能使用CURRVAL,否则会出错。
一次NEXTVAL会增加一次 SEQUENCE的值,所以如果你在同一个语句里面使用多个NEXTVAL,其值就是不一样的。
- 如果指定CACHE值,ORACLE就可以预先在内存里面放置一些sequence,这样存取的快些。cache里面的取完后,oracle自动再取一组 到cache。 使用cache或许会跳号, 比如数据库突然不正常down掉(shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。
3、Alter Sequence
拥有ALTER ANY SEQUENCE 权限才能改动sequence. 可以alter除start至以外的所有sequence参数.如果想要改变start值,必须 drop sequence 再 re-create。
例:
alter sequence SEQTEST maxvalue 9999999;
另: SEQUENCE_CACHE_ENTRIES参数,设置能同时被cache的sequence数目。
4、Drop Sequence
DROP SEQUENCE seqTest;
5、一个例子
复制代码
create sequence SEQ_ID
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocache
order;
建解发器代码为:
create or replace trigger tri_test_id
before insert on S_Depart --S_Depart 是表名
for each row
declare
nextid number;
begin
IF :new.DepartId IS NULLor :new.DepartId=0 THEN --DepartId是列名
select SEQ_ID.nextval --SEQ_ID正是刚才创建的
into nextid
from sys.dual;
:new.DepartId:=nextid;
end if;
end tri_test_id;