Day32 Oracle 3 常用数据库对象

数据库表table

设计要求：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息

学号是主键
姓名不能为空
性别默认值是男
年龄范围18—30岁
Email唯一

创建学生表

添加数据

缺陷：
学生编号可以相同，
性别、年龄的范围没有限制
姓名居然可以为空，
邮箱不唯一
原因
缺少必要的约束

create table student(
     sno number(6),
     sname varchar2(8) ,
     sex char(2),
     age number(3),
     --score number(5,1),
     enterdate date,
     clazz varchar2(10),
     email varchar2(20)
)

标准的建表语法：

    CREATE TABLE [schema.]table
      (column datatype [DEFAULT expr] , …
    );

在创建新表时，指定的表名必须不存在，否则将出错。
使用默认值：当插入行时如果不给出值，dbms将自动采用默认值。
在用Create语句创建基本表时，最初只是一个空的框架，用户可以使用insert命令把数据插入表中。
。

数据库表字段的数据类型

字符数据类型
CHAR：存储固定长度的字符串
VARCHAR2 ：存储可变长度的字符串
数值数据类型
NUMBER：存储整数和浮点数，格式为NUMBER(p, s)
column_name NUMBER { p = 38, s = 0}
column_name NUMBER (p) {整数} column_name NUMBER (p, s) {浮点数}
日期时间数据类型
DATE：存储日期和时间数据
TIMESTAMP：比DATE更精确
LOB数据类型
BLOB：存储二进制对象，如图像、音频和视频文件
CLOB：存储字符格式的大型对象

修改表

在基本表建立并使用一段时间后，可以根据实际需要对基本表的结构进行修改

增加新的列用“alter table … add …”语句
alter table emp add address varchar(20)
新增加的类不能定义为“not null”, 基本表在增加一列后，原有元组在新增加的列上的值都 定义为空值。
删除原有的列用“alter table … drop…”语句，语法格式：alter table 表名 drop column 列名
alter table emp drop column address
修改字段“alter table…modify…”
alter table emp modify(job varchar(50))

删除表

在基本表不需要时，可以使用“drop table”语句撤消。在一个基本表撤消后，所有的数据都丢弃。所有相关的索引被删除
drop table emp
drop table emp cascade constraints

查看表的结构
Desc tablename

可以使用RENAME语句改变表名（视图），要求必须是表（视图）的所有者
RENAME old_name TO new_name

数据库表的约束constraints

数据完整性约束

• 表的数据有一定的取值范围和联系，多表之间的数据有时也有一定的参照关系。
• 在创建表和修改表时，可通过定义约束条件来保证数据的完整性和一致性。
• 约束条件是一些规则，在对数据进行插入、删除和修改时要对这些规则进行验证，从而起到约束作用。

完整性约束分类

• 域完整性约束（非空not null，检查check）
• 实体完整性约束（唯一unique，主键primary key）
• 参照完整性约束（外键foreign key）

三种完整性约束的区别

域完整性约束:字段约束
实体完整性约束：行和行之间的约束
引用完整性约束：表和表之间的约束

创建约束的时机

在建表的同时创建
建表后创建

约束从作用上分类，可以分成两大类：

表级约束：可以约束表中的任意一列或多列。可以定义出了Not Null以外的任何约束。
列级约束：只能约束其所在的某一列。可以定义任何约束。

命名规则推荐采用：约束类型_约束字段

非空约束 　　　　NN__表名列名
唯一约束 　　　　UK_表名_列名
主键约束 　　　　PK_表名
外键约束 　　　　FK_表名_列名
检查约束 　　　　CK_表名_列名

主键约束

主键约束是数据库中最重要的一种约束。在关系中，主键值不可为空，也不允许出现重复，即关系要满足实体完整性规则。

主键从功能上看相当于非空且唯一
一个表中只允许一个主键
主键是表中能够唯一确定一个行数据的字段
主键字段可以是单字段或者是多字段的组合
Oracle为主键创建对应的唯一性索引
create table t3(
id number(4)， –primary key,
constraint t3_pk primary key(id)
)

唯一性约束

唯一性约束条件确保所在的字段或者字段组合不出现重复值
唯一性约束条件的字段允许出现空值，且可以多个空值
Oracle将为唯一性约束条件创建对应的唯一性索引
CREATE TABLE employees(
id NUMBER(6),
name VARCHAR2(25) NOT NULL UNIQUE,
email VARCHAR2(25),
salary NUMBER(8,2),
hire_date DATE NOT NULL,
CONSTRAINT emp_email_uk UNIQUE(email)
);

非空约束

确保字段值不允许为空
只能在字段级定义

CREATE TABLE employees(
    employee_id    NUMBER(6),
    name      VARCHAR2(25) NOT NULL,
    salary         NUMBER(8,2),
    hire_date      DATE CONSTRAINT emp_hire_date_nn NOT NULL
)

Check约束

Check约束用于对一个属性的值加以限制
在check中定义检查的条件表达式，数据需要符合设置的条件
create table emp3
( id number(4) primary key,
age number(2) check(age > 0 and age < 100),
salary number(7,2),
sex char(1),
constraint salary_check check(salary > 0)
)
在这种约束下，插入记录或修改记录时，系统要测试新的记录的值是否满足条件

外键约束

外键是表中的一个列，其值必须在另一表的主键或者唯一键中列出
作为主键的表称为“主表”，作为外键的关系称为“依赖表”
外键参照的是主表的主键或者唯一键
对于主表的删除和修改主键值的操作，会对依赖关系产生影响，以删除为例：当要删除主表的某个记录（即删除一个主键值，那么对依赖的影响可采取下列3种做法：
RESTRICT方式：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作。
CASCADE方式：将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除
SET NULL方式：将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值
FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)
[ON DELETE [CASCADE|SET NULL]] 如省略on短语，缺省为第一中处理方式。

添加约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名 约束类型 具体的约束说明

删除约束
ALTER TABLE 表名
DROP CONSTRAINT 约束名

可增加或删除约束，但不能直接修改

创建学生和班级表

create table clazz(
       cid number(2) primary key ,
       cname varchar2(10) not null unique
);
create table student(
       sno number(6) primary key,
       sname varchar2(10) not null,
       age number(3),
       sex char(2) check(sex='男' or sex='女'),
       email varchar2(20),
       clazzid number(2)
);

缺点
1.classid值可以是不存在的班级
2.删除班级后，相应的学生信息没有同步更新
原因：
虽然设计是要把classid参照clazz表的cid，但是语法没有指定
解决
alter table student add constraint fk_student_clazzid
foreign key (clazzid) references clazz(cid)

序列sequence

序列是oracle专有的对象，它用来产生一个自动递增的数列

创建序列的语法：
create sequence seq-name
increment by n
start with n
maxvalue n|nomaxvalue 10^27 or -1
minvalue n|no minvalue
cycle|nocycle
cache n|nocache

实例：create sequence seq_empcopy_id start with 1 increment by 1;

使用序列
select seq_empcopy_id.nextval from dual
insert into empcopy (empno,ename)
values (seq_empcopy_id.nextval, ‘TEST’);

查看序列状态
select seq_empcopy_id.currval from dual

删除序列
drop sequence seq_empcopy_id;

索引index

问题：
当数据库表中存在很多条记录，如大于10万条时，查询速度便成为一个问题

分析：
在字典中查询指定偏旁的汉字时，先查询目录中指定的偏旁位置，在查询指定笔画的汉字，找到汉字后根据页码找到汉字

在书中查询某内容时，首先在目录中查询所需知识点，然后根据目录中提供的页码找到要查询内容，大大缩短了查询时间

解决
可以建立类似目录的数据库对象，实现数据快速查询，这就是索引

索引类似字典的和课本目录，是为了加快对数据的搜索速度而设立的。索引有自己专门的存储空间,与表独立存放。

索引类型默认采用B树数据结构，数据全部集中在叶子节点

索引的作用：在数据库中用来加速对表的查询,通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O

索引创建以后，在用户撤销它之前并不会用到该索引的名字，但是索引在用户查询时会自动起作用。

索引的创建有两种情况
自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时,Oracle数据库自动创建一个对应的唯一索引.
手动: 用户可以创建索引以加速查询

索引的创建与使用

在一列或者多列上创建索引.
CREATE INDEX index ON table (column[, column]…);

下面的索引将会提高对EMP表基于 ENAME 字段的查询速度.
CREATE INDEX emp_last_name_idx
ON emp (ename)

通过DROP INDEX 命令删掉一个索引.
DROP INDEX index;

删掉 UPPER_LAST_NAME_IDX 索引.
DROP INDEX upper_last_name_idx;

开发中使用索引的要点：

1. 索引数据可能要占用大量的存储空间。

2. 索引改善检索操作的性能，但降低数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。
   3.限制表中索引的数目。索引越多，在修改表时对索引做出修改的工作量越大

3. 并非所有数据都适合于索引。唯一性不好的数据（如省）从索引得到的好处不比具有更多可能值的数据（如姓名）从索引得到的好处多

4. 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能是索引的备选。
5. 可以在索引中定义多个列（如省加城市），这样的索引只在以省加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

视图view

定义：

视图是从若干基本表和（或）其他视图构造出来的表。
在创建一个视图时，只是存放的视图的定义，也即是动态检索数据的查询语句，而并不存放视图对应的数据
在用户使用视图时才去求相对应的数据。所以视图被称作“虚表”

作用：

可以限制对数据的访问，可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
可以使复杂的查询变的简单。在编写查询后，可以方便地重用它而不必知道他的基本查询细节。
提供了对相同数据的不同显示

创建视图
CREATE [OR REPLACE] VIEW view
[(alias[, alias]…)]
AS subquery
[WITH READ ONLY];

查询视图
select * from viewname
不需要再写完全的Select查询语句，
删除视图
Drop view viewname；
删掉视图不会导致数据丢失，因为视图是基于数据库表的一个查询

总结

• 视图是一个虚拟表，对应一条SELECT语句，可将它的输出看作一个表
• 视图不存储数据
• 改变基本表的数据，也会反应到基于该表的视图上
• 视图可以基于基本表的若干行，若干列
• 视图可以基于一个表、多个表，甚至是基于其他的视图
• 使用视图可以提高数据访问的安全性，只显示指定的行列数据
• 使用视图可以降低查询的难度，定制数据显示
• 可以对视图进行CRUD操作，实际上是对基本表的CRUD操作
• 如果视图对应多个表，一般不允许添加操作，可以通过触发器解决
• 使用with read only定义只读视图

其他内容—事务

• 事务（Transaction）是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位。
• 事务是为了保证数据库的完整性

在oracle中，没有事务开始的语句。一个Transaction起始于一条DML(Insert、Update和Delete )语句，结束于以下的几种情况：

• 用户显式执行Commit语句提交操作或Rollback语句回退。
• 当执行DDL(Create、Alter、Drop)语句事务自动提交。
• 用户正常断开连接时，Transaction自动提交。
• 系统崩溃或断电时事务自动回退。

Commit表示事务成功地结束，此时告诉系统，数据库要进入一个新的正确状态，该事务对数据库的所有更新都以交付实施。每个Commit语句都可以看成是一个事务成功的结束，同时也是另一个事务的开始。

Rollback表示事务不成功的结束，此时告诉系统，已发生错误，数据库可能处在不正确的状态，该事务对数据库的更新必须被撤销，数据库应恢复该事务到初始状态。每个Rollback语句同时也是另一个事务的开始。

一旦执行了commit语句，将目前对数据库的操作提交给数据库（实际写入DB），以后就不能用rollback进行撤销。

执行一个 DDL ，DCL语句或从 SQL*Plus正常退出，都会自动执行commit命令。

提交或回滚前数据的状态

以前的数据可恢复
当前的用户可以看到DML操作的结果
其他用户不能看到DML操作的结果
被操作的数据被锁住,其他用户不能修改这些数据

提交后数据的状态

数据的修改被永久写在数据库中.
数据以前的状态永久性丢失.
所有的用户都能看到操作后的结果.
记录锁被释放,其他用户可操作这些记录.

回滚后数据的状态

语句将放弃所有的数据修改
修改的数据被回退.
恢复数据以前的状态.
行级锁被释放.

其他内容—rowid和rownum

rowid

rowid在记录创建时生成，而且是不变，直接指向硬件上的存储位置
用rowid直接访问是最快的，但也是人力所无法做到的
只要记录没被搬动过，rowid是不变的

rownum

rownum是个伪列，查询的时候除非特别指定，否则不会显示。
其主要的用处是控制查询返回的行数
只能使用：<,<=
当rownum和order by一起使用时，会首先选出符合rownum条件的记录，然后再进行排序，这会给我们的查询带来难度。
示例
Select rowid,rownum,e.* from emp Select rowid,rownum,e.* from emp order by sal
Select rowid,rownum,e.* from emp where rownum<=5

其他内容—分页

显示EMP表中薪水最高的前五个雇员信息

步骤1：显示按照工资排序的所有员工
select * from emp order by sal desc

步骤2：把步骤1结果看做一张表，查询该表的前5条数据
select rownum, t.*
from ( select * from emp order by sal desc ) t
where rownum <=5
获取TOP-N查询采用两层select即可实现

分页按照薪水高低显示数据，每页5条数据，显示第2页数据
步骤1：显示按照工资排序的所有员工
select * from emp order by sal desc
步骤2：把步骤1结果看做一张表，查询该表的前10条数据
select rownum, t.*
from ( select * from emp order by sal desc ) t
where rownum <=10
步骤3：以步骤2结果为表，获取前6-10条件数据
select *
from(select rownum r, t.*
from ( select * from emp e order by sal desc ) t
where rownum <=10)
where r>5
显示非首页信息，需要3层select查询

使用imp和exp导入导出数据

使用exp导出数据
exp位置为\ORACLE_HOME\BIN
导出dmp文件
支持三种导出方式：
表方式导出一个指定表
用户方式导出属于一个用户的所有对象，它是默认选项
全数据库方式导出数据库中所有对象，只有DBA可以

使用imp导入数据
Imp位置为\ORACLE_HOME\BIN

使用PL/SQL Developer导入/出数据

Oracle Export/ Oracle Import
扩展名dmp
使用的就是exp/imp命令
二进制文件，无法查看
可以跨平台，效率高、使用最广
SQL Inserts
扩展名sql
可使用记事本等查看，效率不如第一种
适合小数据量导入导出
不能导出blob、clob等字段
PL/SQL Developer
扩展名pde
PL/SQL Developer的自有文件格式，只能使用该软件来导入导出 很少使用

小结

• 数据库表table
• 数据库表的约束constrains
• 序列Sequence
• 索引index
• 视图view

• 其他内容：

  事务
  rowid和rownum
  分页查询
  导入导出数据

创建数据库表

/*设计要求：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息

学号是主键
姓名不能为空
性别默认值是男
年龄范围18---30岁
Email唯一
*/
create table student(
   sno number(6),
   sname varchar2(10),
   sex char(3),
   age number(3),
   enterdate date,
   --score number(3,1),
   clazzid number(3),
   email varchar2(30)
);

--删除数据库表
drop table student;

--添加数据  utf-8  3字节  gbk  2个字节
insert into student values(1,'张三丰','男',23,'23-12月-1256',1,'zhangsanf@wd.cn')
insert into student values(1,'张三丰','男',23,'23-12月-1256',1,'zhangsanf@wd.cn')
insert into student values(2,'张翠山','男',20,'23-12月-1296',2,'zhangsanf@wd.cn')
insert into student values(3,'张无忌','无',88,'23-12月-1316',3,'zhangwuji@wd.cn')
insert into student (sno,sex)values (3,'男')
--查看表的结构
desc student

--查询数据库表
select * from student

/**

缺点
1.学号可以相同
2.email居然可以相同
3.性别、年龄不符合要求
4.姓名可以为空

原因：没有定义约束
*/

--缺少分数一列
--增加一列  98.5  118.5  118.25
alter table student add score number(3,1);

select * from student
update student set score = 98.5 where sno = 1

--修改列
alter table student modify score number(5,2);
update student set score = 118.5 where sno = 1
update student set score = 118.55 where sno = 1

--删除列
alter table student drop column score

数据库表非外键约束

/*设计要求：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息

学号是主键
姓名不能为空
性别默认值是男
年龄范围18---30岁
Email唯一
*/
--方法1：对应列后面指定约束
create table student(
   sno number(6) primary key,
   sname varchar2(10) not null,
   sex char(3) default '男'  check (sex='男' or sex='女'),
   age number(3) check (age between 18 and 30),
   enterdate date,
   --score number(3,1),
   clazzid number(3),
   email varchar2(30) unique
);

--方法2：创建表示，所有列的后面指定约束
create table student(
   sno number(6),
   sname varchar2(10) not null,
   sex char(3) default '男',
   age number(2),
   enterdate date,
   clazzid number(3),   
   email varchar2(30),
   constraints pk_student primary key(sno),
   constraints uk_student_email unique(email),
   constraints ck_student_sex check (sex='男' or sex='女'),
   constraints ck_student_age check (age between 18 and 30)
);

--方法3：创建表之后，通过修改表来添加约束
create table student(
   sno number(6),
   sname varchar2(10) not null,
   sex char(3) default '男',
   age number(2),
   enterdate date,
   clazzid number(3),   
   email varchar2(30)

);

alter table student add constraints pk_student primary key(sno);
alter table student add constraints uk_student_email unique(email);
alter table student add constraints ck_student_sex check( sex='男'or sex='女');
alter table student add constraints ck_student_age check(age between 18 and 30);
 --约束要修改，需要先删除再添加
 alter table student drop constraints pk_student
  alter table student drop constraints uk_student_email
   alter table student drop constraints ck_student_sex
    alter table student drop constraints ck_student_age

--删除数据库表
drop table student;

--添加数据  utf-8  3字节  gbk  2个字节
insert into student values(1,'张三丰','男',23,'23-12月-1256',1,'zhangsanf@wd.cn')
insert into student values(4,'张三','男',23,'23-12月-1256',1,'zhangsan@wd.cn')
insert into student values(2,'张翠山','男',20,'23-12月-1296',2,'zhangcsh@wd.cn')
insert into student values(3,'张无忌','男',18,'23-12月-1316',3,'zhangwuji@wd.cn')
insert into student (sno,sname)values (5,'张丹枫')
--查看表的结构
desc student

--查询数据库表
select * from student

/**

缺点
1.学号可以相同
2.email居然可以相同
3.性别、年龄不符合要求
4.姓名可以为空

原因：没有定义约束

解决
1.学号可以相同  学号唯一，非空   主键约束 primary key
2.email居然可以相同  唯一  可以为空   唯一约束 unique
3.姓名可以为空   非空，但是不唯一   非空约束 not null
4.性别、年龄不符合要求  性别只能男或女，年龄18-30  检查约束  check
5.性别默认值是男  不属于约束  属于默认值  default
*/

/**
约束分类
1.主键约束 primary key
2.唯一约束 unique
3.检查约束 check
4.非空约束  not null
5.外键约束 foreign key 表和表之间

**/
select * from dept;
select * from emp;

数据库表外键约束

--外键约束
--什么外键：一个表的某一列的值参考另外一个表的主键列或者唯一列，该列称为外键 foreign key
--           emp表的deptno参考dept表的deptno
--特殊情况：可以参考当前表的主键或者唯一键，比如emp表的mgr参考的emp表的empno
select * from dept
select * from emp;

--为什么要分为dept和emp两张表，一张表不可以吗？
--1.可能导致数据的冗余（重复），导致空间的浪费
--2.可能导致更新异常
--分为两张表，节省空间，也避免了更新异常
--分为两张表后，通过外键列建立关联
create table t_clazz(
    clazzid number(3) primary key ,
    clazzname varchar2(14) not null,
    clazzroom varchar2(4)
);
create table t_student(
    sno number(6) primary key,
    sname varchar2(10) not null,
    sex char(3),
    age number(3),
    enterdate date,
    email varchar2(50) unique,
    --cid number(3) references t_clazz(clazzid)
    cid number(3)
    --constraints fk_student_cid foreign key (cid) references t_clazz(clazzid)    
)
alter table t_student add constraints fk_student_cid foreign key (cid) references t_clazz(clazzid)
drop table t_clazz
drop table t_student;
--添加数据
insert into t_clazz values(1,'Java001','R506');
insert into t_clazz values(2,'Java002','R406');
insert into t_clazz values(3,'Android001','R407');
commit;

insert into t_student values(1,'张三丰','男',23,'23-12月-1256','zhangsanf@wd.cn',1)
insert into t_student values(4,'张三','男',23,'23-12月-1256','zhangsan@wd.cn',1)
insert into t_student values(2,'张翠山','男',20,'23-12月-1296','zhangcsh@wd.cn',2)
insert into t_student values(3,'张无忌','男',18,'23-12月-1316','zhangwuji@wd.cn',5)  --未找到父项关键字
insert into t_student values(3,'张无忌','男',18,'23-12月-1316','zhangwuji@wd.cn',3) 

select * from t_clazz
select * from t_student

delete from t_clazz where clazzid = 1  --已找到子记录

--缺点1：t_student表中的cid可以是不存在的班级
--缺点2：t_student表中还具有某个班级的学生，但是可以直接删除t_clazz表中相应的班级。
--原因：cid是设计上的关联列，期望作为外键参考t_clazz的clazzid，但是没有在语法上创建约束
--解决：指定外键


--删除班级，如何处理该班级的学生呢？
--策略1：默认策略： 有学生，不让删除班级。要想删除班级，先处理学生(删除，或者清空外键)
--delete from t_student where cid = 1
update t_student  set cid = null  where cid =1
delete from t_clazz where clazzid = 1 

--策略2：级联删除 cascade
alter table t_student drop constraints fk_student_cid
alter table t_student 
      add constraints fk_student_cid foreign key (cid) references t_clazz(clazzid) on delete cascade ;

select * from t_student
select * from t_clazz
delete from t_clazz where clazzid = 1
--策略3：清空策略 set null
alter table t_student drop constraints fk_student_cid
alter table t_student 
      add constraints fk_student_cid foreign key (cid) references t_clazz(clazzid) on delete set null
delete from t_clazz where clazzid = 2;
delete from t_clazz where clazzid = 3;


/**
约束的分类
1.域完整性约束  约束某一列值的
        检查约束 check    非空约束  not null
2.实体完整性约束  约束的行和行之间关系
        唯一约束 unique   主键约束 primary key 
3.参照完整性约束  约束的表和表之间的关系
       外键约束 foreign key    



1.主键约束 primary key
2.唯一约束 unique
3.检查约束 check
4.非空约束  not null
5.外键约束 foreign key 表和表之间


*/

索引和序列

/**
事务  transaction  和日常生活中事务不同  
    若干（1，2，3.....）个数据库操作,整体作为一个，要么都成功，要么都回到初始状态
对象   方法   索引  关系  通知 

---生活案例1：银行转账
--A ----— —5000  update 
--B------- +5000   update

---生活案例2：下订单
--事务开始
--1.库存减少 update 
--2.下订单
         1.订单表    收件人  地址  联系方式 insert
         2.订单明细表  多条记录  n 个 insert 
                 商品1
                 商品2
                 商品3
--3.支付宝       2个  update     
       转账
--4  日志记录  insert 
--事务结束 

事务何时开始
遇到也DML语句就开始：insert  update delete

事务何时结束
--commit  rollback

实际使用中可能默认是1个DML操作一个事务，自动提交或者回滚
如果需要多个DML操作是一个事务，需要手动设置

--哪些操作需要事务
--1.select 不需要
--2.DML（insert update delete）需要，关键是可能多个DML操作属于一个整体，需要手动的结束事务
--3.DDL(create,alter,drop) 需要事务，一个操作一个事务，会自动提交
--
*/

select * from dept; --不需要事务
insert into dept values (50,'教学部','北京') --事务开始，没有真正的写入数据库
select * from dept; --当前用户可以查询到结果，其他用户查询不到（system登录查询不到）
insert into dept values(60,'教研部','北京');--事务进行进行，没有写到数据库中，2个insert操作了
commit;
rollback; -- 程序开发中，出现异常，捕获异常，并执行rollback
delete from dept where deptno = 50

视图和事务

--什么是视图view
  --对应一条SQL语句，背后是数据库表或者是其他视图  虚表
  --只是一条SQL语句，不存储数据，不占用存储空间
  --对视图的添加、查询、删除、修改都是对背后的数据库表的操作  虚表

--最简单的视图
create or replace view myview1
as
select * from emp where sal >= 3000

select * from myview1

select * from emp
insert into myview1 values(1111,'1111','1111',1111,'23-12月-1981',1111,1111,10);
insert into myview1 values(2222,'1111','1111',1111,'23-12月-1981',3333,1111,10);
drop view myview1

--只读视图
--简单:简化了SQL语句
--安全：可以只显示部分行和部分列的数据，还可以定义为只读视图
create or replace view myview2
as
select empno,ename,sal,comm
from emp
where job ='CLERK'
with read only

insert into myviews --只读视图，不可以进行insert等操作

select * from myview2

--多表视图
--查询各个部门平均工资的等级

select deptno,avg(sal) from emp group by deptno having deptno is not null

select * from salgrade

create or replace view myview3
as
select das.*,sg.grade
from ( select deptno,avg(sal) asal from emp group by deptno having deptno is not null)  das
join salgrade sg
on (das.asal between sg.losal and sg.hisal)
with read only



select * from  myview3

rowid+rownum

--rowid
--AAAE5TAAEAAAAFjAAA  18位
--ROWID是ORACLE中的一个重要的概念。
--用于定位数据库中一条记录的一个相对唯一地址值。
--ROWID它是一个伪列，它并不实际存在于表中。
--它是ORACLE在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。
--数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的。


--AAAE5TAAEAAAAFjAAA  18位
--第一部分6位表示：该行数据所在的数据对象的 data_object_id； 
--第二部分3位表示：该行数据所在的相对数据文件的id; 
--第三部分6位表示：该数据行所在的数据块的编号； 
--第四部分3位表示：该行数据的行的编号；

select * from emp;
select * from dept;

select e.*,rowid from emp e  --默认按照rowid排序

select e.*,rowid from emp e order by empno  --指定按照empno排序

select * from emp

select * from emp for update; --可以编辑的查询结果

select e.*,rowid from emp e; --可以编辑的查询结果

--rownum
select rownum,e.* from emp e
-- order by之后，rownum打乱；orderby子句最后执行，晚于select子句
select rownum,e.* from emp e order by sal desc  

--借助子查询
select rownum,e2.* from (
select rownum,e.* from emp e order by sal desc ) e2

select rownum,e2.* from (
select e.* from emp e order by sal desc ) e2

--rownum的特征 可以使用<  <=,但是不支持>  = >=
select rownum,e2.* from (
select e.* from emp e order by sal desc ) e2 where rownum <=5

select rownum,e2.* from (
select e.* from emp e order by sal desc ) e2 where rownum >5

select rownum,e2.* from (
select e.* from emp e order by sal desc ) e2 where rownum =5




Oracle中的rowid

ROWID是ORACLE中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。ROWID它是一个伪列，它并不实际存在于表中。它是ORACLE在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的。

要理解索引，必须先搞清楚ROWID。

B-Tree索引的每个索引条目具有两个字段。第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。第二个字段表示键值所对应的记录行的ROWID。所以索引能加快查询速度！

索引值→ROWID->将ROWID换算成一行数据的物理地址->得到一行数据

ROWID的格式：

第一部分6位表示：该行数据所在的数据对象的 data_object_id；
第二部分3位表示：该行数据所在的相对数据文件的id;
第三部分6位表示：该数据行所在的数据块的编号；
第四部分3位表示：该行数据的行的编号；

索引就是保存了rowid后三个部分的信息。索引是物理存在的，而rowid是伪列。所以索引可以用来快速地定位到数据行。

分页

--分页：每页5条数据，一共14记录

--获取第一页的数据（工资最高的5个人  top-N查询）
select rownum,e.* from (
select * from emp order by sal desc ) e where rownum <=5


--获取非第一页数据（第二页  6----10）
select rownum,e.* from (
select * from emp order by sal desc ) e where rownum <=10

minus

select rownum,e.* from (
select * from emp order by sal desc ) e where rownum <=5


--获取非第一页数据（第二页  6----10）
--1.获取所有的数据
select * from emp order by sal desc

--2.获取前10条语句（去尾）
select rownum,e.* from (
select * from emp order by sal desc ) e where rownum <=10

--3.从前10条数据中去掉前5条数据（掐头）
select e2.* from (
select rownum rn,e.* from (
select * from emp order by sal desc ) e where rownum <=10 ) e2
where rn >5  

导入导出

使用PL/SQL Developer导入/出数据
Oracle Export/ Oracle Import
扩展名dmp
使用的就是exp/imp命令
二进制文件，无法查看
可以跨平台，效率高、使用最广
SQL Inserts
扩展名sql
可使用记事本等查看，效率不如第一种
适合小数据量导入导出
不能导出blob、clob等字段
PL/SQL Developer
扩展名pde
PL/SQL Developer的自有文件格式，只能使用该软件来导入导出
很少使用

使用imp和exp导入导出数据
使用exp导出数据
exp位置为\ORACLE_HOME\BIN
导出dmp文件
支持三种导出方式：
表方式导出一个指定表
用户方式导出属于一个用户的所有对象，它是默认选项
全数据库方式导出数据库中所有对象，只有DBA可以

使用imp导入数据
Imp位置为\ORACLE_HOME\BIN