oracle最新sql回憶性演練1

標籤：sql   oracle   

 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_sequenceINCREMENT 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';

oracle最新sql回憶性演練1