4、oracle表操作
4.1、dml操作：
1、查看当前用户下所有的表：
select * from user_tables;
2、查看某表的大小：
select sum(bytes)/(1024*1024) as "size(M)" from <表名>;
3、查看当前用户每个表占用空间的大小：
select segment_name,sum(bytes)/1024/1024 as "SIZE:(MB)" from user_extents group by segment_name;
4、查询插入语句：
（1）把A表中某些字段的数据插入B表中:
insert into <表B>(<字段名a>) (select <字段名a> from <表A>);
（2）如果两张表的字段完全一致：
insert into <表B> (select * from <表A>);
5、查询创表：
（1）保存A表中某些字段的数据为表B:
create table <表B> as (select <字段名> from <表A>);
（2）保存A表中所有字段的数据为表B:
create table <表B> as (select * from <表A>);
6、更新：
update <表名> set <字段名>= ‘<字符>‘ WHERE <字段名>!= ‘<字符>‘;
update damsup.BHL_SUP_SEARCHREGISTER up set up.sreg_date=(select down.sreg_date from dams.BHL_SUP_SEARCHREGISTER down where down.id=up.id) where id<‘7000‘;
update BHL_SUP_SEARCHREGISTER set sreg_date=replace(sreg_date,substr(sreg_date,1,8),‘20190827‘);
7、计算数据总条数：
select count(*) from <表名>;
8、删除重复数据：
（1）SELECT * FROM (SELECT * FROM <表名> WHERE <判重列> IN (SELECT <判重列> FROM <表名> GROUP BY <判重列> HAVING COUNT(<判重列>) >1) AND
<id号> NOT IN (SELECT MIN(<id号>) FROM <表名>)) WHERE <标识字段>=‘字段内容‘;
（2）delete from (SELECT * FROM BHL_ORIGINFILE WHERE ROOTPATH IN (SELECT ROOTPATH FROM BHL_ORIGINFILE GROUP BY ROOTPATH HAVING COUNT(ROOTPATH) >1) AND objno NOT IN (SELECT MIN(objno) FROM BHL_ORIGINFILE)) where dbid=‘335‘;
4.2、ddl操作：
添加、修改、删除多列的话，用逗号隔开
1、增加一个字段：
alter table <表名> add <字段名> varchar2(30) default ‘lc‘ not null;
2、修改一个字段：
alter table <表名> modify <字段名> varchar2(16) default ‘lc‘;
#需要注意的是如果某一列中已经存在值，如果你要修改的为比这些值还要小的列宽这样将会报错；
3、重命名字段名：
alter table <表名> rename column <旧字段名> to <新字段名>;
4、删除一个字段：
alter table <表名> drop column <字段名>;
5、重命名表：
ALTER TABLE <表名> RENAME <旧表名> TO <新表名>;
5.1、增加注释：
COMMENT ON TABLE <表名> IS ‘<描述信息>‘;
6、创建表的同时创建主键约束：
（1）不命名约束键：
create table student (
id number(10) primary key,
name varchar2(50),
age number(10));
（2）有命名约束键：
create table student1 (
id number(10) constraint student1_id_pk primary key,
name varchar2(50),
age number(10));
7、删除表中已有的主键约束：
（1）不命名约束键：
SELECT * from user_cons_columns where table_name=‘STUDENT‘;
alter table student drop constraint SYS_C0013729;
（2）命名约束键：
alter table students1 drop constraint student1_id_pk;
8、向表中添加主键约束：
alter table student add constraint student_id_pk primary key(id);
4.3、sequence 对象：
1、仅限于oracle，在mysql中可以在主键后加auto_increment；
2、sequence是oracle提供的数据库对象，为解决主键值和唯一键值的唯一性，
按照预定义的模式自动生成整数的一种机制，保证数字的自动增长；
3、sequence对象生成数字后，取出插入到表中主键或是唯一键(id序号)；
4、在oracle中sequence就是序号，每次取的时候它会自动增加，sequence与表是独立的，
在多张表使用一个sequence时，连续的序号会分散在不同的表中；
5、创建自定义sequence：
（1）用户要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限；
（2）创表：
create table user_table(
user_table_id number(10) primary key,
--主键、自增长
name varchar2(100) not null,
mail varchar2(100) not null,
password varchar2(100)
);
（3）创建sequence：
CREATE SEQUENCE user_table_id_sequence
START WITH 1
--从几开始计数
INCREMENT BY 1
--每次加几个
NOMAXVALUE
--不设置最大值
NOCYCLE
--一直累加，不循环
NOCACHE;
--不设置缓存cache个序列，避免系统down掉或者其它情况导致的序列不连续
补充如下：
删除sequence的方法：drop sequence user_table_id_sequence;
修改sequence的参数：alter sequence user_table_id_sequence increment by 1;
（4）创建触发器：
create or replace trigger user_table_id_trigger
before insert on user_table for each row when (new.user_table_id is null)
begin
select user_table_id_sequence.nextval into:new.user_table_id from dual;
end;
（5）插入数据：
insert into user_table(name,mail,password) values(‘tom‘,‘‘,‘123456‘);
#在使用触发器的情况下sequence序列会自动插入到user_table_id；
insert into user_table(user_table_id,name,mail,password) values(user_table_id_sequence.nextval,‘tom‘,‘‘,‘123456‘);
#在不使用触发器的情况下手动插入sequence序列；
（6）查看sequence当前的序号：
select user_table_id_sequence.currval from dual;
（7）查看哪些表的哪些字段使用了sequence序列：
SELECT
s.TABLE_NAME,
s.TABLE_ID,
s.SEQUENCE_NAME
FROM
(
SELECT
c.table_name AS TABLE_NAME,
A .sequence_name AS SEQUENCE_NAME,
SUBSTR (
b.text,
INSTR (LOWER(b.text), ‘:new.‘) + 5,
INSTR (LOWER(b.TEXT), ‘from‘) - INSTR (LOWER(b.text), ‘:new.‘) - 5
) AS TABLE_ID
FROM
(
SELECT
sequence_name
FROM
user_sequences
) A,
(
SELECT
text,
NAME
FROM
user_source
WHERE
TYPE = ‘TRIGGER‘
) b,
(
SELECT
table_name,
TRIGGER_name
FROM
user_triggers
) c
WHERE
c.TRIGGER_name = b. NAME
AND UPPER(b.text) LIKE ‘%‘ || upper(A .sequence_name) || ‘%‘
--精髓,oracle有些sequence创建名称是小写，但是数据库里面名称是大写
ORDER BY
table_name ASC
) s
WHERE
s.TABLE_ID IS NOT NULL;
4.4、建表语言：
1、表级约束(constraint)：
（1）面临的问题：
1）某列必须有值而且唯一；
2）某列的取值受到另一取值的限制；
（2）数据库提供解决的方法：
1）限制无效的数据进入到表中；
2）数据库层面的"安检；
2、主键约束(primary key)：
一张表只有一个pk;
（1）单一主键：
键数据具有唯一性，且不能出现null值；
create table stu_cour(
stu_id number(10) constraint stu_cour_stu_id_pk primary key
)
（2）联合主键：
两个键联合在一起数据具有唯一性，两个键分别可以出现重复值但不能为null；
create table stu_cour(
stu_id number(10),
cour_id number(10),
constraint stu_cour_stu_id_cour_id_pk primary key (stu_id,cour_id)
)
（3）给存在的表增加主键的约束：
alter table stu_cour add constraint stu_cour_stu_id_cour_id_pk primary key(stu_id,cour_id);
alter table stu_cour modify(stu_id constraint stu_cour_stu_id_pk primary key);
alter table stu_cour drop primary key(stu_id);
3、唯一键(unique)：
（1）单一唯一键：
键数据具有唯一性，允许null的重复；
create table unique_test(
c1 number(10) constraint unique_test_c1_pk primary key,
c2 number(10) constraint unique_test_c2_uk unique,
c3 number(10) constraint unique_test_c3_uk unique
);
（2）联合唯一键：
两个键联合起来数据具有唯一性，两个键分别可以重复；
两个键联合在一起数据可以都为null，允许重复；
两个键联合在一起数据不允许一边为null一边不为null的重复；
create table unique_test(
c1 number(10) constraint unique_test_c1_pk primary key,
c2 number(10),
c3 number(10),
constraint unique_test_c2_c3_uk unique(c2,c3)
);
4、非空键(not null)：
（1）键数据不能出现null值，键数据可以重复；
create table not_null(
c1 number(10) constraint not_null_c1_pk primary key,
c2 number(10) not null,
c3 number(10) not null
);
alter table test modify(c1 null);
#修改列为not null；
5、条件约束(check)：
（1）定义条件表达式，每个列值必须满足该条件；
（2）以下表达式不允许使用条件约束：
1）伪例：currval、nextval、level、rownum
2）函数：sysdate、uid、userenv
3）引用其它记录的其它值
（3）条件约束语句：
create table check_table (
c1 number(10) constraint check_table_c1_pk primary key,
c2 number(10) not null,
c3 number(10) not null,
constraint check_table_c2_c3_ck check ( ( c2+c3 ) > 100)
);
6、外键约束(foreign)：
（1）外键约束的特点：
1）实现父子表的联系约束，父表被子表引用；
2）保证一对多的关系；
3）通过外键 fk 可以与同一张表的主键 pk 或唯一键 uk 建立引用的关系，也可以与不同表的 pk
或者是 uk 建立引用的关系；
4）外键的取值必须匹配父表中已有的值，外键取值为null(可重复)可以匹配父表中任何值；
5）关键字：
FOREIGN KEY：用表级约束定义外键时使用该关键字；
REFERENCES：表示引用父表中的某列；
ON DELETE CASCADE：级联删除，删除父表的记录前，先删除子表里的相关记录；
ON DELETE SET NULL：删除父表的记录前，先将子表中外键列的相关值置空；
（2）建立主表：
create table parent (
c1 number(10) constraint parent_c1_pk primary key
);
（3）建立子表：
create table child (
c1 number(10) constraint child_c1_pk primary key,
c2 number(10) not null,
/*这里可以不写not null，因为建立外键后null在主表中找不相关的记录*/
constraint child_c2_fk foreign key(c2) references parent(c1)
);
（4）删表操作：
1）
插数据时，先插主表，再插子表；删数据时先删子表，再删父表；
如果使用 drop table cost purge; 命令直接删表会因父表和子表存在引用关系而无法删除，如果想要去除关系使用如下命令：
drop table cost cascade constraint purge;
#去除级联约束，先删除子表的约束再删主表；
分步操作：alter table child drop constraint child_c2_fk; drop table parent;
2）在建表的时候指定当删除父表时子表该做什么：
ON DELETE CASCADE：级联删除，删除父表的记录前，先删除子表里的相关记录；
ON DELETE SET NULL：删除父表的记录前，先将子表中外键列的相关值置空；
（5）建表：
create table cost(
cost_id number(4) constraint cost_cost_id_pk primary key,
cost_name varchar2(50) not null,
cost_base_durattion number(10),
cost_base_cost number(7,2),
cost_unit_cost number(7,4),
cost_status char(1),
constraint cost_cost_status_ck check( cost_status in(0,1)),
cost_descr varchar2(100)
);
desc cost;
（6）小结：
1）pk和uk的区别是对待null上的不同；
2）pk是uk和not null的结合；
3）pk一张表只有一个，uk和not null 一张表可以有多个；
4）用户下不同表的约束名不可以重(用户名+约束名)，不同表的列名可以重(用户名+表名+列名)；
5）一个用户可以选多个服务，一个服务只能有一个用户；