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";





 drop table example;
 CREATE TABLE example(
     I