oracle最新sql回憶性演練1
drop table example;
CREATE TABLE example(
ID Number(4) NOT NULL ,
NAME VARCHAR(25),
constraint example_id primary key(id)
);
drop SEQUENCE example_sequence;
CREATE SEQUENCE example_sequence
INCREMENT BY 1 -- 每次加幾個
START WITH 1 -- 從1開始計數
NOMAXVALUE -- 不設置最大值
NOCYCLE -- 一直累加，不循環
NOCACHE -- 不建緩沖區
;
drop TRIGGER example_triger;
CREATE TRIGGER example_triger
BEFORE INSERT ON example
FOR EACH ROW
WHEN (new.id is null) --只有在id為空時，啟動該觸發器生成id號
begin
select example_sequence.nextval into :new.id from dual;
end;
insert into example(name) values('張三');
insert into example(id, name) values(111,'李四');
insert into example(name) values('張三1');
insert into example(name) values('張三2');
insert into example(name) values('張三3');
select * from example;
drop table xxx;
create table xxx as select * from example; --復制一個表的數據和結構，但約束不會被復制
select * from xxx;
--修改主鍵
--1查主鍵
SELECT * from user_cons_columns c where c.table_name = 'example';
SELECT * from user_cons_columns c where c.table_name = 'xxx';
--2刪除主鍵
alter table example drop constraint example_id;
--3增加新的主鍵約束
alter table example add constraint example_id primary key(id);
--4添加列
alter table example add age number;
--5刪除列
alter table example drop column age;
--6修改列(修改列類型的前提：需刪除存在的數據庫)
alter table example modify age varchar(2);
alter table example modify id varchar(20);
--7添加約束
-- foreign key
alter table example add constraint fk_example_xxx foreign key(pid) references xxx(pid);
insert into example(name,age) values('張三1',1);
insert into example(name,age) values('張三2',2);
insert into example(name,age) values('張三3',113);
insert into xxx(name,pid) values('張三2',21);
insert into xxx(name,pid) values('張三3',1111);
select * from example;
select * from xxx;
drop table xxx;
create table xxx as select * from example;
select * from xxx;
--alter table xxx add pid number(10);
-----處理復制表約束
alter table xxx rename column id to sid;
alter table xxx add id number;
update xxx set id=sid;
alter table xxx drop column sid;
-----處理添加字段並為主鍵
alter table xxx add pid varchar2(20);
update xxx set pid=id;
alter table xxx add constraint xxx_pid primary key(pid);
select * from xxx ;
----為example表添加外鍵
alter table xxx add constraint fk_xxx foreign key(id) references example(id);
--刪除約束
alter table xxx drop constraint fk_example_xxx;
---------處理已有數據的字段類型修改(前4步操作會使表中的約束丟失)
--1重命名字段
alter table example rename column id to sid;
--2添加id字段
alter table example add id varchar2(200);
--3更新數據
update example set id = sid;
--4刪除備份數據的字段
alter table example drop column sid;
--5新增約束
alter table example add constraint exmaple_id primary key(id);
-------------------------------------------------------------------
alter table example add unique(age);
alter table example drop unique(age);
--查找表的唯一性約束（包括名稱，構成列）
select column_name
from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name
and cu.table_name = 'example';
-----視圖( 如果權限不足，grant connect,resource,dba to 你的實例)
create table bbb as select * from example;
CREATE OR REPLACE VIEW exam_sum_v1
(name,age)
AS
SELECT d.name,d.age
FROM bbb d;
insert into exam_sum_v1(name,age) values('lisii',1221);
insert into exam_sum_v1(name,age) values('lisii',1221);
insert into exam_sum_v1(name,age) values('lisii',0000);
insert into exam_sum_v1(name,age) values('lisii',111);
select * from exam_sum_v1;
select * from bbb;
alter table bbb drop column id;
select * from example;
select * from xxx;
--USER_TAB_COLS中記錄了用戶表的列信息
SELECT USER_TAB_COLS.TABLE_NAME as 表名,
USER_TAB_COLS.COLUMN_NAME as 列名,
USER_TAB_COLS.DATA_TYPE as 數據類型,
USER_TAB_COLS.DATA_LENGTH as 長度,
USER_TAB_COLS.NULLABLE as 是否為空,
USER_TAB_COLS.COLUMN_ID as 列序號,
user_col_comments.comments as 備注
FROM USER_TAB_COLS
inner join user_col_comments
on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME
and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME；
--關聯到表的所有字段信息
select col.column_name,
uc.constraint_type,
case uc.constraint_type
when 'P' then
'√'
else
''
end "PrimaryKey"
from user_tab_columns col
left join user_cons_columns ucc
on ucc.table_name = col.table_name
and ucc.column_name = col.column_name
left join user_constraints uc
on uc.constraint_name = ucc.constraint_name
and uc.constraint_type = 'P'
where col.table_name = 'example';
--查詢某個表中的外鍵字段名稱、所引用表名、所應用字段名
select distinct (col.column_name), r.table_name, r.column_name
from user_constraints con,
user_cons_columns col,
(select t2.table_name, t2.column_name, t1.r_constraint_name
from user_constraints t1, user_cons_columns t2
where t1.r_constraint_name = t2.constraint_name
and t1.table_name = 'example') r
where con.constraint_name = col.constraint_name
and con.r_constraint_name = r.r_constraint_name
and con.table_name = 'example';
