SQL-进阶前奏-表结构变更/索引/视图/序列的基操

SQL-进阶前奏-表结构变更/索引/视图/序列的基操

在上一章Oracle基本查询的基础上，本篇blog还是用emp表做演示，由于涉及数据库变更，我们复制一张emp表myemp

SQL> create table myemp as select * from emp ; 
 
Table created

insert(增加)

语法：

INSERT  INTO表名[(列名1，列名2，...)]VALUES(值1，值2，...)

简单写法（不建议）

insert into myemp values(7789,'张三','开发',7839,to_date('1992-10-22', 'yyyy-MM-dd'), 2000, 200, 10)

使用简单写法必须按照表中的字段来插入值、若有空的字段使用null

update(修改)

全部修改

UPDATE 表名 SET 列名1=值1,列名2=值2,...

update myemp t set t.comm = 1000 

局部修改

UPDATE 表名 SET 列名1=值1，列名2=值2，....WHERE 修改条件；

update myemp t set t.comm=5000 where t.empno = 7790
#局部修改

delete(删除)

语法：

DELETE FROM 表名 WHERE 删除条件;

delete from myemp t where t.empno in (7788,7789,7790)

事务处理

所谓的事务管理就是要保证数据的完整性，要么同时成功，要么同时失败

当我们执行完delete语句时，我们去数据库中去查看数据，发现并没有我们删除数据，这是因为oracle的事务对数据库的变更的处理，我们必须做提交事务才能让数据真正的插入到数据库中，在同样在执行完数据库变更的操作后还可以把事务进行回滚，这样就不会插入到数据库。如果事务提交后则不可以再回滚。

提交：commit

回滚：rollback

表的管理

表的数据类型

No	数据类型	描述
1	Varchar， varchar2	表示一个字符串
2	NUMBER	NUMBER(n)表示一个整数，长度是n
NUMBER(m,n):表示一个小数，总长度是m，小数是n，整数是m-n
3	DATA	表示日期类型
4	CLOB	大对象，表示大文本数据类型，可存4G
5	BLOB	大对象，表示二进制数据，可存4G

建表

Create table 表名（

字段1	数据类型	[default 默认值],

字段2	数据类型	[default 默认值],

...

字段n	数据类型	[default 默认值]

）

• 范例：创建person表

create table person(
	pid number(10),
 	name varchar2(10),
    gender number(1) default 1, 
    birthday date 
);

表删除

语法

drop table 表名 ; 

表修改

在sql中使用alter来修改表

添加语法：ALTER TABLE 表名称 ADD(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)
修改语法：ALTER TABLE 表名称 MODIFY(列名1 类型 [DEFAULT 默认值]，列名1 类型 [DEFAULT 默认值]...)

范例：在person表中增加列address

alter table person add(address varchar2(10))

范例：在person表中修改列address长度为20

alter table person modify(address varchar2(20))

表截断

使用delete语句删除数据，则可以使用rollback来回滚，如果想要清空一张表的数据，同时不想回滚可以立即释放资源需要使用截断表的语法

语法

truncate table 表名; 

约束

在数据库开发中，约束是必不可少的，使用约束可以更高的保证数据的完整性

主键约束

主键约束在id上使用，内容不能为空，不可重复，建表的时候可以指定

• 创建一张表，把pid作为主键（primary key）

create table person(
	pid number(10) primary key , 
 	name varchar2(10),
    gender number(1) default 1, 
    birthday date 
);

非空约束

使用非空约束，可以使指定的字段不为空(not null)

create table person(
	pid number(10) not null , 
 	name varchar2(10), not null , 
    gender number(1) default 1, 
    birthday date 
);

唯一约束（unique）

使用唯一约束，表中的一个字段的内容是唯一的

create table person(
	pid number(10) , 
 	name varchar2(10), unique , 
    gender number(1) default 1, 
    birthday date 
);

唯一约束的名字也可以自定义

create table person(
	pid number(10) , 
 	name varchar2(10), 
    gender number(1) default 1, 
    birthday date 
    constraint person_name_uk unique(name)  #person_name_uk就是我们自定义的名称
);

检查约束（check）

使用检查约束可以来约束字段值的合法范围

create table person(
       pid      number(10) ,
       name     varchar2(10),
       gender   number(1)  check(gender in (1, 2)), #1男2女
       birthday date
);

外键约束

外键约束是两张表的约束，可以保证关联数据的完整性

• 范例：创建两张表，一张订单表，一张订单明细表，订单表和订单明细表是一对多的关系

#订单表的建立
create table orders(
       order_id      number(10) ,
       total_price   number(10,2),
       order_time date,
      constraint orders_order_id_pk primary key(order_id)
);

#订单明细表的建立
create table order_detail(
       detail_id      number(10) ,
       order_id   number(10),
       item_name  varchar2(10),
       quantity   number(10),
      constraint order_detail_detail_id_pk primary key(detail_id)
);

insert into orders values(1, 200, to_date('2015-12-12','yyyy-MM-dd'));

insert into order_detail values(1, 2, 'java',1);

我们在两张表中插入如上两条数据，我们发现在order_detail表中插入的order_id在order表中并不存在，这样在数据库中就产生了脏数据。此时需要外键来约束它。

create table order_detail(
       detail_id      number(10) ,
       order_id   number(10),
       item_name  varchar2(10),
       quantity   number(10),
      constraint order_detail_detail_id_pk primary key(detail_id),
      constraint order_detail_order_id_fk foreign key(order_id) references orders(order_id)
);

外键关联一定要注意

• 外键是主表的主键
• 删表先删子表再删主表，如果直接删除主表会出现由于约束存在无法删除外表的情况，但是可以强制删除（级联删除）（不建议）

drop table order_detail cascade constraint

• 删除主表的数据可以删除子表的关联数据，再删主表，也可以使用级联删除，级联删除在外键约束上要加上 on delete cascade

#在外键建表时
constraint order_detail_order_id_fk foreign key(order_id)
	references orders(order_id) on delete cascade
	#这样主表在删除数据的时候就会把子表的关联数据一同删除（主子全删）

rownum

rownum表示行号

• 范例：查询emp表带有rownum列

select rownum, t.* from emp t 

有了rownum，我们可以取结果集的前几行，比如前5行

select rownum, t.* from emp t  where rownum<6

但是单纯rownum只能取前几行或后几行，无法取中间行，如果想取中级行，我们需要配合子查询

select * from 
(select rownum rm a.* from (select * from emp) a where rownum<11) b  where b.rm>5

视图（view）

视图就是封装了一条复杂查询的语句

• 范例：建立一个视图，此视图包括了20部门的全部员工信息

create view empvd20 as select * from emp t where t.deptno=20
#此操作需要权限

​ 如果视图已经存在，想要覆盖视图，使用

create or replace view empvd20 as select * from emp t where t.deptno = 20

​ 设置视图为只读

create or replace view empvd20 as select * from emp t where t.deptno = 20 with read only

序列

在很多数据库中存在一个自动增长，如果现在想在oracle中完成自动增长的功能，则只能依靠序列完成，所有的自动增长操作，需要用户手工完成处理

语法

CREATE SEQUENCE 序列名 
[INCREMENT BY n] 
[START WITH n] 
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}] 
[{CYCLE|NOCYCLE}] 
[{CACHE n|NOCACHE}];

• 范例：创建一个seqpersonid的序列，验证自动增长的操作

create sequence seq ;
#在序列中提供了以下两种操作
#nextval:取得序列的下一个内容
#currval:取得序列的下一内容

select seq.nextval from dual 

select seq.currval from dual

在插入数据时需要自增的主键可以这样使用

insert into person(pid,name,gender,birthday)
values(seq.nextval,'张三','1',to_date('1999-11-11','yyyy-MM-dd'))

在实际项目中每一张表都会配一个序列，但是表和序列是没有必然联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列

序列的管理一般使用工具来管理

索引

索引是加速数据存储的数据对象，合理使用索引可以大大降低io次数，从而提高数据访问性能

图书馆

• 将书分类，然后再建一个箱子，箱子里面放卡片，卡片里面可以按类查询，按姓名查或者类别查
• 索引需要维护成本，故不能乱建

创建索引

• 单例索引：基于单个列所建立的索引

create index 索引名 on 表名（列名）

• 复合索引

复合索引是基于两个列或多个列的索引，在同一张表上可以有多个索引，但是要求列的组合必须不同，如

create index emp_idx1 on emp(ename,job) ; 
###
create index emp_idx1 on emp(job,ename) ;  

• 范例：给person表的name建立索引

create index pname_index on person(name) ; 

• 范例：给person表创建一个name和gender索引

create index pname_gender_index on person(name,gender) ; 

索引建立原则

• 在大表上建立索引才有意义
• 在where子句后面或者是连接条件上建立索引
• 索引的层次不要超过4层