目录
一、视图（view）
1. 概念
视图就是建立在表|结果集|视图上的虚拟表，相当于一个封装工具把写好的sql代码拷贝一份包装起来，对视图的删除不会删除原有表的数据。注意：不是所有的用户都有创建视图的权限
2. 视图的作用
- 简化:select 查询语句
- 重用：封装select语句 命名
- 隐藏：内部细节
- 区分：相同数据不同查询
3. 示例
求部门经理人中平均薪水最低的部门名称。
--求部门经理人中平均薪水最低的部门名称
--数据：部门名称 -> 最低平均薪水 -> 每个部门的部门经理人的平均薪水
--1）找出所有的经理人编号
select distinct mgr from emp where mgr is not null;
--创建一个视图来储存所有的经理人编号
create or replace view vw_mgr as select distinct mgr from emp where mgr is not null;
--2)获取所有经理人所属部门的部门编号、员工编号以及工资
select deptno，empno, sal
from emp where empno in (select distinct mgr from emp where mgr is not null);
--创建一个视图存储所有经理人所属部门的部门编号
create or replace view vw_mgr_deptno as select deptno, empno, sal
from emp where empno in
(select mgr
from vw_mgr);
--3)对经理人进行按部门分组，得出各部门经理人的平均工资
select deptno, avg(sal)
from (select deptno, empno, sal
from emp where empno in
(select distinct mgr
from emp where mgr is not null))
group by deptno;
-- 创建一个视图存储经理人的部门编号和平均工资
create or replace view vw_mgr_deptno_avgsal as select deptno, avg(sal) avg_sal
from (select * from vw_mgr_deptno) group by deptno;
--4)各部门的经理人中的最低平均工资
select min(avg(sal))
from (select deptno, empno, sal
from emp where empno in
(select distinct mgr
from emp where mgr is not null))
group by deptno;
--创建一个视图储存各部门的经理人中的最低平均工资
create or replace view vw_mgr_min_avgsal as select min(avg_sal) avg_min_sal
from vw_mgr_deptno_avgsal;
--5)最低平均工资的部门编号
select deptno
from (select avg(sal) avg_mgr_sal, deptno
from (select empno, sal, deptno
from emp where empno in
(select distinct mgr
from emp where mgr is not null))
group by deptno)
where avg_mgr_sal = (
select min(avg(sal))
from (select empno, sal, deptno
from emp where empno in
(select distinct mgr
from emp where mgr is not null))
group by deptno);
--创建一个视图储存最低平均工资的部门编号
create or replace view vw_mgr_min_avgsal_deptno as select deptno
from vw_mgr_deptno_avgsal where avg_sal = (select * from vw_mgr_min_avgsal);
--6)--最低平均工资的部门的名称
select dname
from dept where deptno =(select deptno
from (select avg(sal) avg_mgr_sal, deptno
from (select empno, sal, deptno
from emp where empno in
(select distinct mgr
from emp where mgr is not null))
group by deptno)
where avg_mgr_sal = (select min(avg(sal))
from (select empno, sal, deptno
from emp where empno in
(select distinct mgr
from emp where mgr is not null))
group by deptno));
--创建一个视图储存最低平均工资的部门的名称
create or replace view vw_mgr_min_avgsal_dname as select dname
from dept where deptno = (select * from vw_mgr_min_avgsal_deptno);二、索引
1. 概念
- 索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
- 索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。
- 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
- 索引一旦建立，在表上进行 DML 操作时（例如在执行插入、修改或者删除相关操作时），oracle 会自动管理索引，索引删除，不会对表产生影响。
- 索引对用户是透明的，无论表上是否有索引，sql 语句的用法不变。
- oracle 创建主键时会自动在该列上创建索引。
2. 索引的特点和用途
提高查询速度的一种手段 -->目录
- 唯一性较好字段适合建立索引
- 大数据量才有效果
- 主键|唯一: 唯一索引
3. 索引的创建
create index 索引名 on表名 (字段列表...)
drop index 索引名
create index idx_emp on emp(sal,ename);
drop index idx_emp;
select * from emp order by sal,ename;三、表设计
1. 规范
设计表首先应该按需遵循三范式
- 确定表名
- 确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯一)
- 主键： 唯一标识一条记录（唯一并且非空）
- 唯一： 唯一
- 非空：不能为空
- 默认： 当没给值时使用给定一个默认值
- 外键：参考其他表（自己）的某个（某些）字段
- 检查：自定义的规则
2. 示范
用户表
- 表名 tb_user
- 主键 userid
- 字段名 中文 类型 为空 默认值 其他说明
- userid 流水号 number(5) 否 主键
- username 用户名 varchar2(30) 否 长度在4-20
- userpwd 密码 varchar2(20) 否 长度在4-18
- age 年龄 number(3) 18 大于>=18
- gender 性别 char(2) 男 男or 女
- email 邮箱 varchar2(30) 唯一
- regtime 注册日期 date sysdate
- 备注
文章表
- 表名 tb_txt
- 主键 txtid
- 字段名 中文 类型 为空 默认值 其他说明
- txtid 流水号 number(10) 否 主键
- title 标题 varchar2(32) 否 长度在4-30
- txt 正文 varchar2(1024)
- pubtime 发布时间 date sysdate
- userid 发布人 number(5) 外键，参考tb_user的userid列
- 备注
三、表创建
1. 不加约束
（1）创建新表
create table 表名(
字段名 类型(长度) 约束,
...其他字段....
..约束........
);创建用户表
--用户表
--创建
create table tb_user(
userid number(5),
username varchar2(30),
userpwd varchar2(20),
age number(3) ,
gender char(2) ,
email varchar2(30),
regtime date
);
--加入注释
comment on table tb_user is '用户表';
comment on column tb_user.userid is '流水号,主键';
comment on column tb_user.username is '用户名';
comment on column tb_user.userpwd is '密码';
comment on column tb_user.age is '年龄';
comment on column tb_user.gender is '性别';
comment on column tb_user.email is '邮箱';
comment on column tb_user.regtime is '注册日期';
创建文章表
--文章表
--创建
create table tb_txt(
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)
);
--注释
comment on table tb_txt is '文章表';
comment on column tb_txt.txtid is '流水号,主键';
comment on column tb_txt.title is '标题';
comment on column tb_txt.txt is '正文';
comment on column tb_txt.pubtime is '发布时间';
comment on column tb_txt.userid is '发布人,外键，参考 tb_user 的 userid 列';
--删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;（2）已有表中拷贝结构
create table 表名 as select 字段列表 from 已有表 where 1!=1;
--拷贝结构 emp
create table emp_his as select ename,sal from emp where 1!=1;
--拷贝结构 emp +数据
create table emp_his2 as select ename,sal from emp where sal>2000;（3）删除表
drop table 表名 cascade constraints
--删除表
drop table emp_his2 cascade constraints;
drop table emp_his cascade constraints;2. 同时创建约束+默认名称
这种在创建表的同时创建约束并使用默认约束名称的方式，后期不方便排错， 所以不推荐使用。其主要的优点是简单。
--用户表
--创建
create table tb_user(
userid number(5) primary key,
username varchar2(30) check(length(username) between 4 and 20) not null ,
userpwd varchar2(20) not null check(length(userpwd) between 4 and 18),
age number(3) default(18) check(age>=18),
gender char(2) default('男') check(gender in('男','女')),
email varchar2(30) unique,
regtime date default(sysdate)
);--文章表
--创建
create table tb_txt(
txtid number(10) primary key,
title varchar2(32) not null check(length(title)>=4 and length(title)<=30),
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5) references tb_user(userid) on delete set null
);
--删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;3. 同时创建约束+指定名称
创建表的同时创建约束并指定约束的名称，后期方便排错，推荐使用。
--用户表
--创建
create table tb_user(
userid number(5),
username varchar2(30) constraint nn_user_name not null ,
userpwd varchar2(20) constraint nn_user_pwd not null ,
age number(3) default(18) ,
gender char(2) default('男'),
email varchar2(30),
regtime date default(sysdate),
constraint pk_user_id primary key (userid),
constraint ck_user_name check(length(username)between 4 and 20) ,
constraint ck_user_pwd check(length(userpwd) between 4 and 18),
constraint ck_user_age check(age>=18),
constraint ck_user_gender check(gender in('男','女')),
constraint uq_user_email unique(email)
);
--文章表
--创建
create table tb_txt(
txtid number(10),
title varchar2(32) constraint nn_txt_title not null,
txt varchar2(1024),
pubtime date default(sysdate),
userid number(5) ,
constraint pk_txt_id primary key(txtid),
constraint ck_txt_id check(length(title)>=4 and length(title)<=30),
constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid) on delete cascade
);
--删除 （先删除从表 再删除主表 ；同时删除约束）
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;4. 追加创建约束+指定名称
推荐这种， 便于后期排错。
--用户表
--创建
create table tb_user(
userid number(5),
username varchar2(30) ,
userpwd varchar2(20) ,
age number(3) ,
gender char(2) ,
email varchar2(30),
regtime date default(sysdate)
);
--追加约束
alter table tb_user add constraint pk_user_id primary key (userid);
alter table tb_user add constraint ck_user_name check(length(username)between 4 and 20) ;
alter table tb_user add constraint ck_user_pwd check(length(userpwd) between 4 and 18);
alter table tb_user add constraint ck_user_age check(age>=18);
alter table tb_user add constraint ck_user_gender check(gender in('男','女'));
alter table tb_user add constraint uq_user_email unique(email);
--非空与默认
alter table tb_user modify (username constraint nn_user_name not null);
alter table tb_user modify (userpwd constraint nn_user_pwd not null);
alter table tb_user modify (age default(18));
alter table tb_user modify (gender default('男'));--文章表
--创建
create table tb_txt(
txtid number(10),
title varchar2(32),
txt varchar2(1024),
pubtime date,
userid number(5)
);
--追加约束
alter table tb_txt add constraint pk_txt_id primary key(txtid);
alter table tb_txt add constraint ck_txt_id check(length(title)>=4 and length(title)<=30);
--三种级联删除规则
alter table tb_txt add constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid);
alter table tb_txt add constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid) on
delete cascade ;
alter table tb_txt add constraint fk_txt_ref_user_id foreign key(userid) references tb_user(userid) on
delete set null;
--注意非空 默认
alter table tb_txt modify (title constraint nn_txt_title not null) ;
alter table tb_txt modify (pubtime default(sysdate));四、表约束
在 oracle中所有的一切都是对象， 约束也是一个个的对象，除了能创建约束我们还能对约束进行一些其他的操作。
1. 查看某个用户的约束
select constraint_name, constraint_type
from user_constraints
where owner = upper('scott');2. 查看表的约束
select constraint_name,constraint_type
from user_constraints
where table_name=upper('tb_user');3. 查看字段名+约束
select constraint_name, column_name
from user_cons_columns
where table_name = upper('tb_user');4. 约束的禁用与启用
alter table tb_user disable constraint nn_user_name;
alter table tb_user enable constraint nn_user_name;5. 删除约束
alter table tb_user drop constraint uq_user_email cascade;6. 修改约束
--非空
alter table tb_user modify (username varchar2(20));
--默认
alter table tb_user modify (age default null);五、表的其他操作
1. 修改表结构
一般用不到。
- 修改表名 :rename to
- 修改列名: alter table 表名 rename column to
- 修改类型: alter table 表名 modify(字段 类型)
- 修改约束: 先删除 后添加
- 添加列: alter table 表名 add 字段 类型
- 删除列:alter table 表名 drop column 字段
--修改表名
rename tb_txt to tb_txt_new;
--修改列名
alter table tb_txt_new rename column txtid to tid;
--修改类型
alter table tb_txt_new modify(tid varchar2(20));
--添加列
alter table tb_txt_new add col varchar2(30);
--删除列
alter table tb_txt_new drop column col;
select * from tb_txt_new;2. 删除表（drop）
- 先删除从表 再删除主表
- 删除表的同时删除约束
- 外键所在表的为主表，而主键可能存在于主表也可能存在于从表。
drop table tb_txt_new cascade constraints;
drop table tb_user cascade constraints;3. 截断数据（truncate）
截断所有的数据 ,如果截断的是主表，结构不能存在外键关联。
truncate与delete的区别：
truncate 截断数据同时，从结构上检查，只要主键的位置一旦为引用，布管有没有数据都不允许删除。
delete 删除数据，从数据上检查，只要主键所在的值没有被引用就可以删除。
create table emp_his as select * from emp where 1=1;
select * from emp_his;
--截断所有的数据
truncate table emp_his;
--不能截断: truncate table dept;六、序列
使用工具|程序管理流水号，序列在创建时 没有与表关联 ，在操作数据时与表关联。
1. 创建
create sequence 序列名 start with 起始值 increment by 步进;2. 使用
在操作数据 添加 更新 -->主键
- currval：当前值
- nextval：下个值
create sequence seq_tb_user start with 2 increment by 2;
drop sequence seq_tb_user;
--下个值
select seq_tb_user.nextval from dual;
--当前值
select seq_tb_user.currval from dual3. 删除
drop sequence 序列名
七、事务
1. 概念
- 事务是指作为单个逻辑工作单元执行的一组相关操作。
- 这些操作要求全部完成或者全部不完成。
- 使用事务是为了保证数据的安全有效。
2. 特点（ACID）
- 原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。
- 一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事务进行的所有数据修改，必须在所有相关的表中得到反映。
- 隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。
- 持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢失。
3. 事务的开启
自动开启于 DML 之 insert delete update
4. 事务的结束
（1）成功
- 正常执行完成的 DDL语句：create、alter、drop
- 正常执行完 DCL 语句 GRANT、REVOKE
- 正常退出的 SQLPlus 或者 SQL Developer 等客户端
- 如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接)
- 手动提交 ：使用 commit
（2）失败
- rollback ，手动回滚
- 非法退出 意外的断电
rollback 只能对未提交的数据撤销，已经 Commit 的数据是无法撤销的，因为 commit之后已经持久化到数据库中。
八、DML
SQL结构图
数据定义DDL(如:创, 该, 删数据库对象)
数据操纵DML(如:insert, update, delete, select)
数据控制DCL(如:grant授予权限, revoke删除权限)
事务控制TCL(如:commit, rollback, savepoint) sql
DDL（Data Definition Language 数据定义语言）用于操作 对象 和 对象的属性 。
DDL 的主要语句（操作）
| 语句 | 作用 |
|---|---|
| Create | 可以创建数据库和数据库的一些对象 |
| Drop | 可以删除数据表、索引、条件约束等 |
| Alter | 修改数据表定义以及属性 |
DML（Data Manipulation Language 数据操控语言）用于操作数据库 对象中包
含的数据 ，也就是说操作的单位是 记录 。
DML 的主要语句（操作）
使用场景
| insert | 注册 |
| update | 修改密码 |
| delete | 退出、删除、剔除会员 |
| select | 登录|查看会员 |
1. insert
insert 为添加数据。
（1）格式
insert into 表名 [(字段列表)] values(值列表)；添加记录
（2）需要满足的条件
- 类型 长度 兼容：字段 兼容值
- 值满足约束：主键 (唯一+非空) 非空(必填) 唯一(不重复 ) 默认(没有填写使用默认值) 检查(满足条件) 外键(参考主表主键列的值)
- 个数必须相同：指定列，个数顺序与列相同；没有指定，个数与表结构的列个数和顺序相同 (null也得占位，没有默认值)
（3）添加数据
- insert into 表(指定列) select 查询列 from 源表 where 过滤数据；
- insert into 表(指定列) values(值列表)；
- insert into 表名 select 查询列 from 源表 where 过滤数据；
- insert into 表名 values(值列表 )；
（4）示范
创建序列： 一般为一张表准备一个序列
create sequence seq_user_id increment by 1 start with 1 ;
create sequence seq_txt_id increment by 1 start with 1 ;eg：添加所有列
--没有列|所有列 所有字段,值个数必须为7个，没有默认值，非空可以使用null占位
insert into tb_user values (seq_user_id.nextval,'test','test123',null,'女',null,sysdate);eg：添加指定列(推荐: 方便阅读操作、更改顺序、非空列和默认值列 可以选填)
--指定所有列
insert into tb_user(username,userid,userpwd,gender,age,email,regtime) values
('shsxt',seq_user_id.nextval,'verygood','男',27,'bjsxt@qq.com.cn',sysdate);
--指定部分列(非空列和默认值列 可以选填, 必填项|主键列 必须指定 ,没有指定列 如果存在默认值，使用
默认值填充，否则null填充)
insert into tb_user(username,userid,userpwd) values
('shsh',seq_user_id.nextval,'shanghai');
commit;
insert into tb_user(username,userid,userpwd,age) values
('穷屌丝男',seq_user_id.nextval,'极品女士',20);
commit;eg：添加外键
--添加同时查询 :使用一条sql 查询外键 ,直接添加 (少用，外键只有一个值，多个值运行错误)一条sql搞
定
insert into tb_txt(txtid,title,userid) values(seq_txt_id.nextval,'iphone6来了',(select userid from tb_user
where username='穷屌丝男'));
--先查询后添加 :
--查询值
select userid from tb_user where username='穷屌丝男';
--添加
insert into tb_txt(txtid,title,userid) values(seq_txt_id.nextval,'您的肾值钱吗?',10);
commit;eg：添加时间
--添加时间
insert into tb_user(username,userid,userpwd,age,regtime) values
('java程序猿',seq_user_id.nextval,'bjsxt',20,to_date('2014-10-31','yyyy-mm-dd'));
commit;eg：从已有表中添加数据
select * from emp_his;
insert into emp_his(empno,ename,job) select empno,ename,job from emp where sal>2000;
rollback;
select * from emp_his;
insert into emp_his select empno, ename, job, mgr, hiredate, sal , comm, deptno from emp where
deptno=20;2. update
update 为修改数据。
（1）格式
update 表名 set 字段=值 [,....] where 过滤行记录;
（2）要求
- 记录存在
- 类型 长度 兼容: 字段 兼容值
- 个数相同
（3）更改数据
- 从已有表中查询数据更改字段值：update 表名 set(字段列表)=(select 字段列表 from 源表 where 过滤源表记录 ) where 更新记录的条件。
- 手动更改字段值：update 表名 set 字段=值 [,....] where 过滤行记录。
（4）示例
eg：手动更改字段值
select * from tb_user;
--重置所有人员的密码 8888
update tb_user set userpwd=8888 where 1=1;
rollback;
--修改 shsxt 的密码 和年龄
update tb_user set userpwd='good',age=29 where username='shsxt' and userpwd='verygood';
commit;eg：从表中获取
--将用户名与密码对换
update tb_user set(username,userpwd)=(select userpwd,username from tb_user where userid=6)
where userid=6;
--select 只能返回一行数据: update tb_user set(username,userpwd)=(select userpwd,username from
tb_user ) where userid=6;3. delete
delete 删除数据。
（1）格式
delete [from] 表名 where 过滤行记录
（2）说明
- delete 可以删除指定部分记录，删除全部记录。
- 记录上存在主外键关联时， 删除存在关联的主表的记录时,注意 参考外键约束， 约束强制不让删除先删除从表再删除主表。
--删除全部数据
delete from tb_user where 1=1;
--删除指定数据
delete from tb_user where userid<10;
主外键关联时，注意 参考约束， 约束强制不让删除。
--先删除从表 再删除主表
delete from tb_txt where 1=1;
delete from tb_user where 1=1;
commit;（3）截断数据与删除数据区别 truncate 与delete 区别
- truncate -->ddl ，不涉及事务，就不能回滚；delete -->dml ,涉及事务，可以回滚。
- truncate 截断所有的数据 delete 可以删除全部 或者部分记录。
- truncate从结构上检查是否存在主外键，如果存在，不让删除；而 delete 从记录上检查是否存在主外键，如果存在，按参考外键约束进行删除。
（4）测试
truncate table tb_user;
delete from tb_user;
- ddl：异常则失败,没有异常就是成功
- dml：insert update delete --> 异常则失败，记录数 <=0 则失败；记录数 >0 就是成功
- select --> 异常则失败,返回结果集 存在结果集就是成功
create table emp_his as select * from emp;
delete from emp_his;
3852
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
