Oracle数据库基础入门

一，了解Oracle数据库

1.1 什么是数据库

数据库(Database)是按照数据结构来组织、存储和管理数据的仓库

1.2 常见的数据库

Oracle，MySQL，DB2，SQLserver等

1.3 DBMS数据库管理系统

数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS

大部分DBMS提供数据定义语言DDL（Data Definition Language）和数据操作语言DML，供用户定义数据库的模式结构与权限约束，实现对数据的追加、删除等操作。

分类

根据存储模型可将数据库划分为关系型数据库和非关系型数据库。关系型数据库，是建立在关系模型基 础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单来说，关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

1.4 SQL语言

SQL(Structured Query Language)结构化查询语言

可简单划分为三部分：

分类	命令
DDL 数据定义语言	create：创建；drop：删除；alter：修改；rename: 重命名; truncate：截断
DML 数据管理语言	insert：插入；delete：删除；update：更新；select：查询
DCL 数据库控制语言	grant：授权；revoke：回收权利；commit:提交事务；rollback：回滚事务

二，数据库–表

表是逻辑表(概念表)，不是物理表

块(8k) —>区（连续块）–>段(连续区) -->表(多个段) ，数据段不全是表，表一定是数据段。还有其他段: 如索引段

2.1 表结构

表由表名、字段(名称+类型+约束)、记录 组成。

-- 表名tb_student
create table tb_student(-- 字段id number(4) ,name varchar(20),course varchar(20),score number(5,2)
);

三范式

在设计数据库时，存在行业的标准，这个标准也称为条件，即范式 Normal Form。一般遵循三个条件即 可，也就是”三范式”(3NF)。

简而言之，最终的目的避免数据重复冗余，1NF–>列不可再分最小原子(避免重复);2NF–>主键依赖(确定唯一)；3NF–>消除传递依赖(建立主外键关联拆分表);

三，SELECT查询

SELECT [DISTINCT] {*,column alias,..}
FROM table alias
WHERE 条件表达式
GROUP BY 分组
HAVING 分组条件
ORDER BY 排序字段列表 [asc|desc]

执行顺序

from --> where --> group --> having --> select --> order by

3.1 查询列（字段）

• SELECT * FROM 表名; ->查询某个表中所有的记录的所有字段信息
• SELECT 列名 FROM 表名; ->查询某个表中所有的记录的指定字段信息
• SELECT 列名1,列名2 FROM 表名; -> 查询某个表中所有的记录的字段1 字段2
• SELECT distinct 列名 FROM 表名; ->去除重复记录
• SELECT 表达式 FROM 表名; ->查询表达式
• SELECT xxx as 别名 FROM 表名 表别名 ->使用别名

查询特定字段

-- 检索单个列
select ename from emp; -- 查询雇员姓名
-- 检索多个列
select deptno,dname,loc from dept; -- 查询部门表的deptno,dname, loc 字段的数据。

*代表通配符，能够查询表中所有字段

-- 检索所有字段
select * from emp; -- 查询emp表所有字段

3.2 distinct去重

使用distinct去重，确保查询结果的唯一性

select distinct deptno from emp; -- 去重

注意：如果有多条重复的数据，使用distinct可实现去重，如果只有单字段重复，则无法实现多字段去重，只能实现单字段。

distinct只是实现查询去重，对原数据表不做改变

如果要实现对表数据进行去重

-- 通过对rowid取反，删除多余重复的数据
delete from 表名 
where rowid not in 
-- 将重复数据通过分组合并在一起，取最小的rowid
(select min(rowid) from 表名 group by 分组条件);

3.3 别名

使用别名便于操作识别 、隐藏底层信息。存在字段别名和表别名

select ename as "雇员 姓名" from emp;
select ename "雇员姓名" from emp;
select ename 雇员姓名 from emp;
select ename as 雇员姓名 from emp;
select ename as " Ename" from emp;

• as: 字段别名可以使用as;表别名不能使用as
• "":原样输出，可以存在 空格与区分大小写

3.4 字符串

使用单引号''来区分字符串，而非双引号"",字符串拼接使用 ||

select 'my' from emp;sql
select ename||'a'||'-->' info from emp;

3.5 伪列

不存在的列，构建虚拟的列，每个数据库里面都存在虚表dual

-- 1*2，cmj就是伪列
select empno, 1*2 as count,'cmj' as name,deptno from emp;

3.6 null

null 遇到数字参与运算的结果为 null,遇到字符串为空串

select 1+null from dual;			-- 结果为null
select '1'||null from dual;			-- 结果为1

查询非空数据

-- 通过is not null 来筛选非空数据
select * from emp where comm is not null;

去空

通过nvl(去空值，代替空的值)替换空值达到去空效果

-- 如果comm的值为空，则使用0代替
select nvl(comm,0) from emp;

四，查询行

4.1 where查询语句

4.1.1 比较条件

= 、 >、 <、 >=、 <=、 !=、 <>、 between and

-- 查询EMP表显示工资超过2850的雇员姓名和工资。
select ename,sal from emp where sal > 2850;
-- 查询EMP表显示工资不在1500～2850之间的所有雇员及工资。
select * from emp where sal between 1500 and 2850

• between 值1 and 值2 等价于 >=值1 and <=值2

4.1.2 且，或，非

and、 or、 not

-- 查询 岗位 为 CLERK 且部门编号为 20的员工名称 部门编号，工资
select * from emp where job='CLERK' and deptno=20
-- 查询 岗位 为 CLERK 或部门编号为 20的员工名称 部门编号，工资
select * from emp where job='CLERK' or deptno=20
-- 查询 岗位 不是 CLERK 员工名称 部门编号，工资
select * from emp where not job = 'CLERK'

4.1.3 null

null不能使用条件判断，只能使用is

-- 存在佣金的员工名称
select * from emp where comm is null;
-- 不存在佣金的员工名称
select * from emp where comm is not null;
select * from emp where not comm is null;

4.1.4 集合操作

Union、Union All、Intersect、Minus

• Union，并集(去重) 对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；
• Union All，全集(不去重) 对两个结果集进行并集操作，包括重复行，不进行排序 ；
• Intersect，交集(找出重复) 对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排 序；
• Minus，差集(减去重复) 对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

-- 查询工资大于1500 或 含有佣金的人员姓名
-- union 去除重复行
select ename from emp where sal>1500
union
select ename from emp where comm is not null;
-- union all 不去除重复行
select ename from emp where sal>1500
union all
select ename from emp where comm is not null;
-- 查询显示不存在雇员的所有部门号。
select deptno from dept
minus
select distinct deptno from emp
-- 查询工资大于1500 且 含有佣金的人员姓名
select ename,sal,comm from emp where sal>1500 intersect
select ename,sal,comm from emp where comm is not null;

4.1.5 模糊查询

通配符：

• %:零个及以上(任意个数的)的字符
• _:一个字符
• 遇到内容中包含 % _ 使用escape(‘单个字符’)指定转义符

-- 查询员工姓名中包含字符A的员工信息
select * from emp where ename like '%A%';
-- 查询员工姓名中包含第二个A的员工名称信息
select * from emp where ename like '_A%';
-- 查询员工姓名中包含字符%的员工名称 岗位 工资 部门编号
select ename,job,sal,deptno from emp where ename like '%a%%' escape('a');

4.1.6 in与exists

in相当于使用or的多个等值，定值集合 ,如果存在 子查询，确保 类型相同、字段数为1，如果记录多，效 率不高，用于 一些 少量定值判断上

--10或30部门的雇员信息
select * from emp where deptno in(10,30);

exists条件为true,存在记录则返回结果，后续不再继续 比较查询，与查询的字段无关，与记录有关

-- exists :条件为true,存在记录，则返回结果，后续不再继续 ,与字段无关，与记录有关
-- exists 难点: 外层结果集 内层结果集 关系列(没有关系列 true)
select *
from emp
where exists
(select deptno,dname from dept where dname in ('SALES', 'ACCOUNTING'));

4.2 排序

使用 ORDER BY 排序，排序不是真实改变存储结构的顺序，而是获取的集合的顺序。

• 顺序 :asc升序(默认) desc降序
• 多字段: 在前面字段相等时，使用后面的字段排序
• 空排序: 降序为 desc，注意 null 为最后

-- 按工资降序
select * from emp order by sal desc;
-- 多字段排序,按雇员编号降序排序，如果与相同则按部门编号升序排序，部门编号相同则按姓名降序排序
select * from emp order by empno desc,deptno,ename desc;

五，函数

5.1 单行函数

5.1.1 日期函数

oracle以内部数字格式存储日期年月日小时分钟秒

• sysdate/current_date 以date类型返回当前的日期
• add_months(d,x) 返回加上x月后的日期d的值
• LAST_DAY(d) 返回的所在月份的最后一天
• months_between(date1,date2) 返回date1和date2之间月的数目
• next_day(sysdate,星期一) 下一个星期一

获取当前时间

select current_date from dual; 
select sysdate from dual;

修改日期

-- 两天后的时刻
select sysdate+2 from dual;

修改月份

-- 当前5个月后的时间
select add_months(sysdate,5) from dual; 
-- 雇佣日期 2个月的时间
select ename,hiredate, add_months(hiredate,2) after from emp;

月份差

-- 雇佣日期 距离现在的 月份数
select ename, months_between(sysdate , hiredate) from emp;

最后一天

-- 返回雇佣日期 当月最后一天的时间
select last_day(sysdate) from dual;

下一个星期的时间

-- 下一个星期二
select next_day(sysdate, '星期二') from dual;

5.1.2 日期格式转换

• to_date(c,m) -> 字符串以指定格式转换为日期
• to_char(d,m) -> 日期以指定格式转换为字符串

select to_date('2017-3-21 18:12:12', 'yyyy-mm-dd hh24:mi:ss') time from dual;
select to_char(sysdate, 'yyyy-mm-dd') from dual;
select to_char(sysdate, 'yyyy/mm/dd') from dual;
select to_char(sysdate, 'yyyy\mm\dd') from dual;
-- 注意中文的问题
-- select to_char(sysdate,'yyyy年mm月dd日') from dual;
select to_char(sysdate, 'yyyy"年"mm"月"dd"日"') from dual;

5.2 多行 | 聚合 | 组函数

• count :统计记录数 count() -->* 或一个列名
• max min: 最大值 最小值
• sum：求和
• avg:平均值

注意:

1. 组函数仅在选择列表和Having子句中有效
2. 出现组函数，select 只能有组函数或分组字段

5.2.1 count 求个数

-- null不参与运算
select count(1) from emp;

5.2.2 max最大值，min最小值

-- 查询所有员工的 最高薪水 ，最低薪水，员工总数 -->组信息
select max(sal) maxSal , min(sal) minSal , count(1) from emp;

5.2.3 sum求和

-- 查询10部门的所有员工的工资总和
select sum(sal) from emp where deptno=10;

5.2.4 avg 平均

-- 查询工资低于平均工资的员工编号，姓名及工资
select empno, ename,sal from emp where sal<(select avg(sal) from emp);

5.3 分组

group by : 分组

1)、select出现分组函数,就不能使用 非分组信息，可以使用group by 字段

2)、group by字段 可以不出现 select 中 ，反之select 除组函数外的，其他字段必须出现在group by 中

having : 过滤组

1. where : 过滤行记录，不能使用组函数
2. having : 过滤组 可以使用组函数

-- 按 部门 查询 平均工资
select avg(sal) from emp group by deptno;
-- 按 部门 查询 平均工资，且平均工资大于2000的部门编号
select avg(sal), deptno from emp group by deptno having avg(sal)>2000;

5.4 行转列

select name,
min(decode(course, '语文', score)) 语文,
min(decode(course, '数学', score)) 数学,
min(decode(course, '英语', score)) 英语
from tb_student
group by name;

六，rowid 与 rownum

6.1 rowid

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

利用rowid删除表中重复数据

-- 1、找出重复数据 :哪个学生 哪门课重复了
select name,course,count(1) from tb_student group by name,course; 
select name,course,count(1) from tb_student group by name,course having count(1)>1;
-- 2、删除重复数据 :删除重复记录
-- 每条记录的唯一标识
select s.* , rowid from tb_student s;
-- 找出保留的rowid
select min(rowid) from tb_student group by name,course;
-- 删除
delete from tb_student where rowid not in (select min(rowid) from tb_student
group by name,course);

6.2 rownum

ROWNUM 是一种伪列，它会根据返回记录生成一个序列化的数字。排序后的结果集的顺序号，每一个 结果集都有自己顺序号 ，不能直接查询大于 1 的数。利用ROWNUM，我们可以生产一些原先难以实现的结果输出。 例如实现分页操作。

rownum :1、必须排序 2、不能直接取大于 1 的数

oracle 中 索引从 1 开始，java 程序 从 0 开始

-- 最底层 rownum 数据库默认顺序号 -->没有用的
select emp.*, rownum
from emp；
select emp.*, rownum from emp order by sal;
-- 自己 排序后结果集的顺序号
select e.*, rownum from (select * from emp order by sal desc) e;
-- 取出工资前5名
select e.*, rownum
from (select * from emp order by sal desc) e
where rownum <= 5;
-- 取出 工资 3-5 名
select e.*, rownum
from (select * from emp order by sal desc) e
where rownum <= 5
and rownum >= 3;
-- 三层模板 (分页)
select e.*
from (select e.*, rownum rn
from (select * from emp order by sal desc) e
where rownum <= 5) e
where rn >= 3;
/*
select 字段列表 from (select e.*,rownum rn from (select from 表 order by 字段) e
where rownum<= 最大值)
where rn>=最小值
*/
select e.*
from (select e.*, rownum rn
from (select * from emp order by sal desc) e
where rownum <= 10) e
where rn >= 6;

七，表连接

当我们获取的数据不是来自于同一张表而是来自于多张表时就需要使用到表连接

7.1 表连接(92连接)

7.1.1 笛卡尔积

非* 必须区分 使用表名 或别名.区分

select * from emp , dept;
select ename , dname from emp , dept;
select ename, dname, e.deptno from emp e, dept d;

7.1.2 等值连接

-- 员工名称及部门名称
select ename, dname, e.deptno from emp e, dept d where e.deptno = d.deptno;
-- 找出30部门的员工名称及部门名称:先关联后过滤
select ename, dname, e.deptno
from emp e, dept d
where e.deptno = d.deptno
and e.deptno = 30;
-- 记录很多时 :先过滤后关联
-- 数据来源： emp (select * from emp where deptno=30) e , dept(select * from dept where deptno=30) d
select * from emp where deptno = 30;
select * from dept where deptno = 30;
-- 查询的字段：ename, dname, e.deptno
-- 条件：e.deptno=d.deptno , deptno=30
select ename, dname, e.deptno
from (select * from emp where deptno = 30) e,
(select * from dept where deptno = 30) d
where e.deptno = d.deptno;

7.1.3 非等值连接 > < != <> between and

-- 查询员工姓名，工资及等级
-- 900 属于哪个等级
select grade
from salgrade
where 900 > losal
and 900 < hisal;
select grade from salgrade where 900 between losal and hisal;
-- 查询员工姓名，工资及等级
-- 数据源： emp e, salgrade s
-- 字段： ename, grade, sal
-- sal between losal and hisal
select ename, grade, sal
from salgrade s, emp e
where sal between losal and hisal;

7.1.4 自连接(数据来源于同一张表)

-- 找出 存在上级的员工姓名 及上级名称
-- 数据来源： emp e, emp m
-- 字段： e.ename, m.ename
-- 条件： e.mgr=m.empno
select e.ename, m.ename from emp e, emp m where e.mgr = m.empno;

7.1.5 外连接

-- 找出 所有的员工姓名 及上级名称
-- 找出 所有部门的员工数 及部门名称
select dname, nu
from dept d, (select count(1) nu, deptno from emp group by deptno) e
where d.deptno(+) = e.deptno;

看逗号, 主表在,的左边就叫左外连接主表在,的右边叫右连接

7.2 表连接(99连接)

• 交叉连接 cross join —>笛卡尔积
• 自然连接(主外键、同名列)
• natural join -->等值连接 join using连接(同名列) -->等值连接
• [inner]join on 连接 -->等值连接 非等值 自连接 (解决一切) 关系列必须区分
• left|right [outer] join on|using -->外连接
• full join on|using -->全连接 满足直接匹配，不满足 相互补充null ，确保 所有表的记录 都至少出 现一次

7.2.1 交叉连接

cross join

select * from emp cross join dept;

7.2.2 自然连接

select * from emp natural join dept;
-- 在指定列过程中同名列归共同所有(*除外)
select deptno,e.ename,d.dname from emp e natural join dept d;

7.2.3 using连接

指定同名字段做等值连接

select deptno,e.ename,d.dname from emp e join dept d using(deptno);

7.2.4 on连接

-- natrual 等值
select ename, dname from emp natural join dept where deptno = 30;
-- using
select ename, dname from emp join dept using (deptno) where deptno = 30;
-- on
select ename, dname
from emp
join dept
on emp.deptno = dept.deptno
where emp.deptno = 30;

7.2.5 外连接

-- 所有部门的 部门名称，员工数
-- 左外
select dname, n
from dept d
left outer join (select deptno, count(1) n from emp group by deptno) i
on d.deptno = i.deptno;
select dname, n
from dept d
left outer join (select deptno, count(1) n from emp group by deptno) i
using (deptno);
-- 右外
select dname, n
from (select deptno, count(1) n from emp group by deptno) i
right outer join dept d
on d.deptno = i.deptno;

7.2.6 全连接

select *
from (select 1 no, 'a' "name"
from dual
union
select 2 no, 'b' "name"
from dual) a
full join (select 1 no, 'c' "name"
from dual
union
select 3 no, 'd' "name"
from dual) b
on a.no = b.no;

八，视图与索引

8.1 视图view

视图:建立在表|结果集|视图上的虚拟表，有以下作用

1. 简化:select 查询语句
2. 重用:封装select语句 命名
3. 隐藏:内部细节
4. 区分:相同数据不同查询

不是所有的用户都有创建视图的权限

1. 前提： create view -->组 connect resource dba

2. 授权: -->sqlplus /nolog

   • sys登录 conn sys/123456@orcl as sysdba

   • 授权: grant dba to scott;

     回收: revoke dba from scott;

   • 重新登录

create or replace view 视图名 as select语句 [with read only];

要求:所有列必须存在名称。

8.2 索引

前提 : 设计表首先应该按需遵循三范式

1. 确定表名
2. 确定字段名 类型 +约束(主键 外键 非空 默 检查认 唯一)
   • 主键： 唯一标识一条记录（唯一并且非空）
   • 唯一： 唯一
   • 非空：不能为空
   • 默认： 当没给值时使用给定一个默认值
   • 外键：参考其他表（自己）的某个（某些）字段
   • 检查：自定义的规则

索引: 提高查询速度的一种手段 -->目录

1. 唯一性较好字段适合建立索引
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;

九，表数据

9.1 设计表

用户表
表名 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
备注

9.2 创建表

表名必须唯一，如果存在 ，必须删除

create table 表名(
字段名 类型(长度) 约束,
...其他字段....
..约束........
);

9.2.1 创建表，不加约束

create table tb_user(
userid number(5),
username varchar2(30),
userpwd varchar2(20),
age number(3) ,
gender char(3) ,
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 '注册日期';

9.2.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(3) default('男') check(gender in('男','女')),
email varchar2(30) unique,
regtime date default(sysdate)
);

9.2.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)
);

9.2.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('男'));

-- 三种级联删除规则
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;

9.3 从已有表中拷贝

-- 语法
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;

9.4 约束

9.4.1 查看某个用户的约束

select constraint_name, constraint_type
from user_constraints
where owner = upper('SCOTT');

9.4.2 查看表的约束

select constraint_name, constraint_type
from user_constraints
where table_name = upper('emp');

9.4.3 查看字段+约束

select constraint_name, column_name
from user_cons_columns
where table_name = upper('emp');

9.4.4 约束的禁用与启用

ALTER TABLE tb_user disable constraint nn_user_name;
ALTER TABLE tb_user enable constraint nn_user_name;

9.4.5 删除约束

alter table tb_user drop constraint uq_user_email cascade;

9.4.6 修改约束

-- 非空
alter table tb_user modify (username varchar2(20));
-- 默认
alter table tb_user modify (age default null);

十，DDL

10.1 删除表

drop table 表名 (cascade constraints)
-- 删除表
drop table emp_his;
-- 主从表关系下删除表
-- 先删除从表 再删除主表 ；同时删除约束
drop table tb_txt cascade constraints;
drop table tb_user cascade constraints;
-- 删除主表的同时级联删除约束
drop table emp_his cascade constraints;

10.2 修改表数据

1. 修改表名 :rename to
2. 修改列名: alter table 表名 rename column to
3. 修改类型: alter table 表名 modify(字段 类型)
4. 修改约束: 先删除 后添加
5. 添加列: alter table 表名 add 字段 类型
6. 删除列: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;

十一，事务

使用事 务是为了保证数据的安全有效。

11.1 事务的特点

事务有一下四个特点：（ACID）

1. 原子性（Atomic）：事务中所有数据的修改，要么全部执行，要么全部不执行。
2. 一致性（Consistence）：事务完成时，要使所有所有的数据都保持一致的状态，换言之：通过事 务进行的所有数据修改，必须在所有相关的表中得到反映。
3. 隔离性（Isolation）：事务应该在另一个事务对数据的修改前或者修改后进行访问。
4. 持久性（Durability）：保证事务对数据库的修改是持久有效的，即使发生系统故障，也不应该丢 失。

11.2 事务的隔离级别

隔离级别	脏读	不可重复读	幻象
Read uncommitted(读未提交)	√	√	√
Read committed(读已提交)	×	√	√
Repeatable read(可重复读)	×	×	√
Serializable(串行读)	×	×	×

• Oracle 默认的隔离级别是 read committed。
• Oracle 支持上述四种隔离级别中的两种:read committed 和 serializable。除此之外， Oralce 中还定义 Read only 和 Read write 隔离级别。
• Read only：事务中不能有任何修改数据库中数据的操作语句，是 Serializable 的一个子集。
• Read write：它是默认设置，该选项表示在事务中可以有访问语句、修改语句，但不经常使用。

丢失更新：两个事务同时存储， 一个存储 100 ， 一个存储 200，最终可能至存储了 200 或者 100，那 另一个的更新就没成功，即结果不为预想的 300

脏读：事务 T1 更新了一行数据，还没有提交所做的修改，T2 读取更新后的数据，T1回滚，T2 读取的数 据无效，这种数据称为脏读数据。

不可重复读：事务 T1 读取一行数据，T2 修改了 T1 刚刚读取的记录，T1 再次查询，发现与第一次读取 的记录不相同，称为不可重复读。

幻读：事务 T1 读取一条带 WHERE 条件的语句，返回结果集，T2 插入一条新纪录，恰好也是 T1 的 WHERE 条件，T1 再次查询，结果集中又看到 T2 的记录，新纪录就叫做幻读。

11.3 事务的开启

自动开启于 DML 之 insert delete update

11.4 事务的结束

成功

• 正常执行完成的 DDL 语句：create、alter、drop
• 正常执行完 DCL 语句 GRANT、REVOKE
• 正常退出的 SQLPlus 或者 SQL Developer 等客户端
• 如果人工要使用隐式事务，SET AUTOCOMMIT ON (只针对一个连接)
• 手动提交 ：使用 commit

失败

• rollback ，手动回滚
• 非法退出 意外的断电

注意：rollback 只能对未提交的数据撤销，已经 Commit 的数据是无法撤销的，因为 commit 之后已经持久化 到数据库中。

十二，DML

DML（Data Manipulation Language 数据操控语言）用于操作数据库对象中包含的数据，也就是说操 作的单位是记录。

DML 的主要语句（操作）

语句	作用
Insert	向数据表张插入一条记录
Delete	删除数据表中的一条或多条记录，也可以删除数据表中的所有记录，但是，它的操作 对象仍是记录
Update	用于修改已存在表中的记录的内容

12.1 序列

使用工具|程序管理流水号，序列在创建时 没有与表关联 ,在操作数据时与表关联

12.1.1 创建

create sequence 序列名 start with 起始值 increment by 步进;
create sequence seq_tb_user start with 2 increment by 2;

12.1.2 使用

/*
在操作数据 添加 更新 -->主键
1)、currval :当前值
2)、nextval:下个值
*/
select seq_tb_user.nextval from dual;
select seq_tb_user.currval from dual

12.1.3 删除

drop sequence 序列名;
drop sequence seq_tb_user;

12.2 Insert

insert into 表名 [(字段列表)] values(值列表); 添加记录

添加记录需要满足的条件

• 类型 长度 兼容: 字段 兼容值
• 值满足约束 :主键 (唯一+非空) 非空(必填) 唯一(不重复 ) 默认(没有填写使用默认值) 检查(满足条件) 外键(参考主表主键列的值)
• 个数必须相同: 指定列，个数顺序与列相同；没有指定，个数与表结构的列个数和顺序相同 (null也 得占位，没有默认值)

添加所有列

insert into tb_user
values
(seq_user_id.nextval, 'test', 'test123', null, '女', null, sysdate);

指定添加

-- 指定所有列
insert into tb_user
(username, userid, userpwd, gender, age, email, regtime)
values
('shsxt',
seq_user_id.nextval,
'verygood',
'男',
27,
'bjsxt@qq.com.cn',
sysdate);

12.3 Update

update 表名 set 字段=值 [,....] where 过滤行记录;

要求

1. 记录存在
2. 类型 长度 兼容: 字段 兼容值
3. 个数相同

select * from tb_user;
-- 重置所有人员的密码 8888
update tb_user set userpwd=8888 where 1=1;
-- 修改 shsxt 的密码 和年龄
update tb_user set userpwd='good',age=29 where username='shsxt' and
userpwd='verygood';
commit;

12.4 Delete

1. delete 可以删除指定部分记录，删除全部记录
2. 记录上存在主外键关联时， 删除存在关联的主表的记录时,注意 参考外键约束， 约束强制不让删除 先删除从表 再删除主表

-- 删除全部数据
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;
-- 删除主表数据,并设置从表数据为null
-- 删除主表数据,级联删除从表数据

12.5 截断数据

truncate: 截断所有的数据 ,如果截断的是主表，结构不能存在外键关联，截断数据同时从结构上检查

create table emp_his as select * from emp where 1=1;
select * from emp_his;
-- 截断所有的数据
truncate table emp_his;
-- 不能截断: truncate table dept;

截断数据与删除数据区别 truncate 与delete 区别

• truncate -->DDL，不涉及事务，就不能回滚 delete -->DML,涉及事务，可以回滚

• truncate 截断所有的数据 delete 可以删除全部 或者部分记录

• truncate从结构上检查是否存在主外键，如果存在，不让删除 delete 从记录上检查是否存在主外键，

  如果存在，按参考外键约束进行删除。
  update 表名 set 字段=值 [,…] where 过滤行记录;