oracle基础知识总结 part 2 ： 其他语句，事务和常用数据对象

关于“oracle基础知识总结”系列博客的说明：

1 本系列博客是在本人学习过程中总结的笔记，学习资料为尚学堂马士兵老师的oracle视频，大部分示例来自于该资料；

2 做为数据库初学者，本系列博客主要讲解了oracle的基础知识，高手勿入勿喷；

3 由于是学习笔记，主要作用是便于本人以后复习和备忘。可能条理不是很清晰，上下文衔接的不是很好，请读本文的读者做好心理准备；

4 本系列博客中的语句主要使用了oracle中scott用户的表结构，该用户是数据库中自带的用户。

DML语句, 数据操作语句

insert  
update 
delete 

inser语句的三种形式:

insert into dept values (50, 'game', 'bj');
insert into dept (deptno, dname) values (60, 'develep');
insert into dept select * from dept2;

创建一张内容相同的表, 用于备份

create table dept2 as select * from dept; 

update语句的使用方法

update emp2 set sal = sal*2, ename = ename || '-' where deptno = 10;

delete语句的使用方法：

delete from emp2;
delete from emp2 where ename = 'ClARK';

DDL语句， 数据定义语言 （建表， 建视图等）

create table 表名 （列名 数据类型， 列名 数据类型， 。。。）;   --  建表

	ctrate table stu(id number(6), name varchar2(20));

drop table 表名 ；  --  删除表

	drop table stu;

alter table 表名 ；  -- 修改表结构

在现有的表上添加一个字段

alter table stu add (addr varchar2(100));  

 删除表中的一个字段

alter table stu drop (addr); 

 修改字段

alter table  stu modify (addr varchar2(150)); 

事务控制 transaction

事务是指 一系列操作要么同时完成， 要么什么都不做。

事务起始于第一条DML语句(insert , delete, update)。

rollback 回滚整个事务 ， commit 提交整个事务。   这两个语句结束事务。

一条DDL， 或DCL语句也会导致事务的提交。

正常断开连接（exit）时事务正常提交；
非正常断开连接（断点，直接关闭命令行窗口）事务回滚。

oracle数据库中的常用对象

一 表  （table）

建表语句的格式：

create table 表名 （列名 数据类型， 列名 数据类型， 。。。）;

常见数据类型：

varchar 变长字符串， 灵活

char 定长字符串， 效率高， 但可能浪费空间

number 数字

date 日期时间

long 变长字符串， 可大于4096

varchar2 变长字符串， 不能大于4096

blob

clob

建表语句中使用数据类型的举例

create table stu
(
id, number(6),
name varchar2(20),
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50)
);

二 约束 （constraint）

一共有5种约束条件：

1 非空约束  not null
2 唯一约束  unique
3 主键约束  primary key
4 外键约束  references
5 检查约束  check

约束举例：

create table stu
(
id, number(6),
name varchar2(20) not null, 
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50)
);

约束条件在数据库中也是一个对象， 可以给约束条件起名，如果不起名字， 系统就起一个默认的名字。
约束命名举例：

create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null, 
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50)
);

唯一约束举例：

create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null, 
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50) unique,
);

将约束直接写在字段的后面， 叫做字段级的约束，字段级的约束有一些限制，要约束多个字段，要使用表级的约束。
比如要求名字和email的组合不能重复（即组合在表中唯一）
表级约束举例：

create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null, 
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_name_email_uni unique(email, name)
);

主键约束， 主键标识表中的记录的唯一性， 数据库会自动为主键建立一个索引，增加查询速度
在语法上说， 主键要求字段不能为空， 并且是唯一的， 可以认为主键约束是非空约束和唯一约束两个的组合
但是在逻辑意义上还代表记录的唯一性
主键约束举例：

create table stu
(
id, number(6) primary key,
name varchar2(20) constraint stu_name_nn not null, 
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_name_email_uni unique(email, name)
);

主键约束可以加在字段上，也可以加在表上：

create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null, 
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_id_pk primary key(id),
constraint stu_name_email_uni unique(email, name)
);

主键约束还可以加在多个字段上，称之为复合主键。下面的例子， 把id和name设为复合主键

create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null, 
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_id_pk primary key(id, name),
constraint stu_name_email_uni unique(email, name)
);

外键约束
外键约束比较复杂，可能会涉及到两张表
外键约束可以建立在一张表的两个字段上， 也可以建在两张表的两个字段上， 但必须涉及到两个字段。
例如在向雇员表emp中插入一条记录的时候， 雇员的部门编号必须出现在部门表dept中，所以可以建立这样的约束，向emp插数据时，deptno字段必须参考dept表中的deptno字段。

create table class
(
id number(4) primary key,
name varchar2(20) not null,
);

create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null, 
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4) references class(id),
email varchar(50),
constraint stu_id_pk primary key(id),
constraint stu_name_email_uni unique(email, name)
);

class number(4) reference class(id) 表示stu表中的class这个字段参考class表中的id字段，也就是说stu表中class字段的值必须在class表中的id字段中。
也可将class字段的外键约束加在表级：

create table stu
(
id, number(6),
name varchar2(20) constraint stu_name_nn not null, 
sex number(1),
age number(3),
sdate date,
grade number(2) default 1,
class number(4),
email varchar(50),
constraint stu_class_fk foreign key (class) references class(id),
constraint stu_id_pk primary key(id),
constraint stu_name_email_uni unique(email, name)
);

在这个例子中，stu表中的class字段叫做参考字段， class表中的id叫做被参考字段。被参考字段必须为主键。
如果一个字段正在被参考，则不能删除它所在的记录，下面的delete语句会执行失败。 

delete from class where id = 1000;
delete from class;

要想删除表或删除表中的记录， 必须先删除class表中id字段的外键约束条件。下面会介绍删除约束条件的方法。

alter删除增加修改约束条件
删除约束条件

alter table stu drop constraint stu_class_fk;   

删除了外键约束， 比如删除了这个外键约束条件， 才能删除表或表中的字段，因为这个字段正在被其他字段引用。

增加约束条件

alter table stu add constraint stu_class_fk foreign key (class) references class(id);

三 索引 （index）

索引也是数据库中的一个数据对象
为某个字段建立一个索引，增加查询的效率
如为enmai字段创建索引

create index idx_stu_email on stu (email);

为两个字段的组合建立索引， 也就是查询这两个字段的组合的时候效率会更高

create index idx_stu_enami_name on stu (enail, name);

删除索引

drop index idx_stu_email;

查询索引：

select index_name from user_indexs;

给某个字段加约束条件时，oracle会为这个字段加一个索引。

不要轻易的建立索引，只有必要的时候才建立索引，因为索引在读取时会提高效率，而在修改或写入时会降低效率。
并且索引还会占用大量的空间。

四 视图 （view）

视图是一张虚表,严格来说，视图是一个子查询,只是为这个子查询起个名字
视图会简化我们的查询，并且可以保护我们的数据。但是表结构改了之后， 视图也要跟着修改，增加维护的难度。
创建视图：

create view v$_stu as select id, name, age from stu;

视图可以更新数据， 只是很少使用， 一般用来查询。

五 序列（sequence）

产生连续的数据， 一般用来做主键
序列是oracle中独有的。
为论坛上的帖子创建一张表

create table articale
(
id number,
title varchar2(l024),
cont long,
);

创建一个序列：

create sequence seq;

以下语句保证产生的数据不会重复

select seq.nextval from dual; 

插入数据时， 自动生成id；

insert into articale values (seq.nextval, 'a', 'aaaaaaaaaaaaaaa');

删除序列：

drop sequence seq;

数据库中常用对象总结

数据库中的常用对象：

1 表         table
2 约束 constraint
3 索引 index
4 视图 view
5 序列 sequence

oracle数据字典表

当前用户创建了哪些表，使用了哪些约束，有哪些视图等等信息，被oracle搜集到一张表中，称之为数据字典。
这些表中存储了数据库中几种常用对象的信息。
1  user_tables   当前用户有哪些表

select table_name from user_tables;

2  user_views    当前用户创建了哪些视图

select view_name from user_views;

3  user_xonstraints 当前用户使用了哪些约束

select constraint_name from user_xonstraints;

4  dictionary表， 记录了所有用户字典表的信息，可以看成用户字典的字典

下面语句查询出所有的数据字典， 大概600多个

select table_name from dictionary;