oracle中的表的增、删、查、改、视图、序列

学习oralce的一些笔记，记录在这里方便以后查阅。

以下操作是在scott方案下执行的：

--创建表
create table publish
(
publishid char(8) primary key,
publishname varchar2(50),
publishaddress varchar2(50),
publishphone varchar2(15),
publishemail varchar2(30)
);
commit;

--删除表
drop table publish;
commit;

--在表中增加一列并且默认值是good
alter table publish 
add pulishcredit char(20) default 'good';
commit;

--删除表中的一列
alter table publish 
drop column pulishcredit;
commit;

--给表中的字段写注释
comment on column publish.publishmail is '出版社邮箱';

--向表中插入数据
insert into publish t
  (t.publishid,
   t.publishname,
   t.publishaddress,
   t.publishphoneno,
   t.publishemail)
values
  ('20150721', '工业出版社', '郑州', '010.11111', 'limin@163.com');
commit;

--向表中播放多条数据
insert into publish
  (publishid, publishname, publishaddress, publishphoneno, publishemail)
  select '20160801', '北大出版社', '北京', '010.99999', 'aaa@qq.com'
    from dual
  union all
  select '20170506', '机械出版社', '南京', '010.66666', 'liu@forxmail.com'
    from dual
  union all
  select '2018080', '上海出版社', '上海', '010.88888', 'zhang@126.com'
    from dual;
commit;

--更新一条数据
update publish set publishphoneno = '110' where publishname = '电子出版社';
commit;

--删除表中的数据(不清空)
delete from publish where publishid = '20170506';
commit;

--清空表中数据
truncate table publish;

select *from book;
--更改表中某个字段的名字
alter table publish
rename column publishname to publishnamenew;
commit;

--更改表的名字
alter table publish
rename to publishnew;
commit;

--更改表中字段的约束
alter table publish
modify publishemail check(publishemail like '%@%') ;
commit;

--给scott创建视图的权限(以dba登陆)
grant create view to scott;

--创建视图
create or replace view publish_view(id,name,phone)
as select publishid,publishname,publishphoneno
from publish;
commit;

--删除视图
drop view publish_view;
commit;

--创建索引
create index publishindex
on publish(publishid desc)

--删除索引
drop index publishindex;

--创建序列
create sequence seq_publishnum
start with 20191010
increment by 1
cache 20;

--删除序列
drop sequence seq_pubishnum;

--查看序列
select seq_publishnum.nextval from dual;
select seq_publishnum.currval from dual;

--使用序列作为publishid的值
insert into publish
  (publishid, publishname, publishaddress, publishphoneno, publishemail)
values
  (seq_publishnum.nextval, '西安出版社', '西安', '010.77777', 'wang@qq.com');
commit;

--like 查询
select * from publish where publishid like '%2017%';

select * from publish where publishaddress like '_京';

--查询publishaddress以北和南开头
select * from publish where regexp_like(publishaddress, '^[北南]')--正则表达式

--查询publishaddress不以北南开头的
select * from publish where regexp_like(publishaddress,'^[^北南]')--正则表达式

先记录到这里吧，有空再写吧。