oracle 回忆功能,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';

原文:http://blog.csdn.net/luozhonghua2014/article/details/45729669

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值