Oracle基础

1、Oracle介绍

1.1、概念介绍

​ Oracle Database，又名Oracle RDBMS，或简称Oracle，是甲骨文公司的一款关系数据库管理系统。它在数据库领域一直处于领先地位的产品。可以说Oracle数据库系统是目前世界上流行的关系数据库管理系统，系统可移植性好、使用方便、功能强，适用于各类大、中、小、微机环境。它是一种高效率、可靠性好的适应高吞吐量的数据库解决方案。

​ 平常所说的Oracle可以指Oracle数据库管理系统。Oracle数据库管理系统是管理数据库访问的计算机软件（Oracle database manager system）。他由Oracle数据库和Oracle实例（instance）构成（区分mysql，mysql没有实例的概念）。

​ 数据库（database）：物理操作系统文件或磁盘的集合。

​ Oracle实例：位于物理内存的数据结构，他由操作系统的多个后台进程和一个共享的内存池所组成，共享的内存可以被所有进程访问。Oracle用它们来管理数据库访问。用户如果要存取数据库（也就是硬盘上的文件）里的数据，必须通过Oracle实例才能实现，不能直接读取硬盘上的文件。实际上，Oracle实例就是平常所说的数据库服务（service）。在任何时刻，一个实例只能与一个数据库关联，访问一个数据库；而同一个数据库可由多个实例访问（RAC）

• 数据库

  • Oracle数据库是数据的物理存储。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。其实Oracle数据库的概念和其他数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。

• 数据库实例

  • 一个Oracle实例（Oracle Instance）有一系列的后台进程（Background Processes）和内存结构（Memory Structures）组成。一个数据库可以有n个实例。

    一组Oracle后台进程 / 线程以及一个共享内存区，这些内存由同一个计算机上运行的线程 / 进程所共享。这里可以维护易失的、非持久性内容（有些可以刷新输出到磁盘）。就算没有磁盘储存，数据库实例也能存在。

• 表空间

  • 表空间是一个用来管理数据存储逻辑概念，表空间只是和数据文件（ORA或者DBF文件）发生关系，数据文件是物理的，一个表空间可以包含多个数据文件，而一个数据文件只能隶属一个表空间。

• 用户

  • 用户是在实例下建立的。不同实例可以建相同名字的用户。
  • Oracle数据库建好后，要想在数据库里建表，必须先为数据库建立用户，并为用户指定表空间。

• 表

  • 有了数据库，表空间和用户，就可以用自定义的用户在自己的表空间中创建表了。有了表就可以存储数据了。

• 数据文件

  • 数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，指有删除其所属的的表空间才行。

    

注意：

​ 表的数据，是由用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中，由于oracle的数据库不是普通的概念，oracle是由用户和表空间对数据进行管理和存放的。但是表不是由表空间去查询的，而是由用户去查的。因为不同用户可以在同一个表空间建立同一个名字的表！这里的区分就是用户了！！！

1.2、创建用户和表空间

• 以超级管理员身份登录
• 创建表空间
• 创建用户
• 给用户授权
• 查询测试

sqlplus /nolog;            # 启动sqlplus不登录
conn sys/root as sysdba;   # 通过超级管理员以dba的身份登录
create tablespace 表空间名 datafile '文件路径\文件名.dbf' size 空间大小;   # 创建表空间
create user 用户名 identified by 密码 default tablespace 表空间;    # 创建用户并指定表空间
grant dba to 用户;    # 给用户授予dba的权限

2、表设计

2.1、表

​ 数据库中以表为组织单位存储数据。表用来存储一些事务的信息，首先需要一个表名，以及存储的信息。

员工表：名称，年龄，性别

name	age	gender
张三	18	男
李四	20	男

2.2、约束

​ 对于存储的每一个信息，都应该使用相应的数据进行表示，并且这些数据应该是合法的（包括实际和业务逻辑），在数据库中我们通过约束来对每个字段中的数据的合法性进行规范。

• 主键约束（PRIMARY KEY）
• 唯一约束（UNIQUE）
• 非空约束（NOT NULL）
• 外键约束（FOREIGN KEY）
• 检查约束（CHECK）

主键

​ 主键是定位表中单个行的方式，可唯一确定表中的某一行，关系型数据库要求所有表都应该有主键，不过Oracle没有遵循此范例要求，Oracle中的表可以没有主键（这种情况不多见）。关于主键有几个需要注意的点：

1. 键列必须具有唯一性，且不能为空，其实主键约束相当于 UNIQUE+NOT NULL
2. 一个表只允许有一个主键
3. 主键所在列必须具有索引（主键的唯一约束通过索引来实现），如果不存在，将会在索引添加的时候自动创建

唯一键

​ 唯一性约束可作用在单列或多列上，对于这些列或列组合，唯一性约束保证每一行的唯一性。UNIQUE允许null值，UNIQUE约束的列可存在多个null。

非空约束

​ 用于确保列不能为NULL，如果在列上定义了not null约束，那么当插入数据时，必须为该列提供数据，当更新列数据时，不能将其值设置为null

外键约束

	1. 外键约束的定义是，让另一张表的记录来约束自己。这里的另一张表就是主表。
	2. 当主表的记录删除时，我们可以跟随主表删除记录（ON DELETE CASCADE）、或者相应字段设置为空(ON DELETE SET NULL)、或者不允许删除（默认）。
     		1. 注意一：当主表被级联删除（DORP TABEL WEN CASCADE CONSTRAINTS）时，从表建立的与主表关联的外键约束将被删除，从表数据不会发生变化。
     		2. 注意二：从表外键约束指向的主表字段，必须是唯一性约束或主键约束的字段。

检查约束

​ 检查约束就是为了检测和过滤不符合实际意义的数据，比如说年龄1000，工资-500等这些数据。

2.4、SQL语言介绍

​ SQL（Structured Query Language）为数据库的语言，在1974年由Boyce【博伊斯】和Chamberlin【钱柏林】提出的一种介于关系代数与关系演算之间的结构化查询语言，是一个通用的、功能极强的关系型数据库语言。它包括三部分：

​ .DDL(Data Definition Languages)语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。常用的语句关键字主要包括create，drop，alter等。

​ .DML(Data Manipulation Languages)语句：数据库操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。常用的语句关键字主要包括insert、delete、update和select等。

​ .DCL(Data Control Language)语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别，主要的语句关键字包括grant、revoke等。

分类	命令
DDL	create:创建；drop:删除；alter:修改；rename:重命名；truncate:截断
DML	insert:插入；delete:删除；update:修改；select:查询
DCL	grant:授权；revoke:回收权利；commit:提交事务；rollback:回滚事务

Oracle命令不区分大小写，但是数据|内容 是区分大小写的

3、select语法

3.1、select基本结构

结构：

select *|colname [,...] from table [alias]

分析：

select关键字 后面跟要查询的内容 from关键字后面跟数据的来源

解析步骤：

1. from 找来源

2. select 挑数据

3.2、去重、别名和排序

3.2.1、去重

去除重复记录

结构：

select distinct 字段名 from 表名

3.2.2、列别名

给列名取名字

结构：

select 字段名 别名 from 表名
select 字段名 as 别名 from 表名

3.2.3、排序

给查询出来的结果按照指定顺序排序

结构：

select 字段名 from 表名 order by 需要排序的字段名 desc降序|asc升序

查询所有员工姓名、工资、所在部门编号，按照部门编号升序排序，同一部门的员工按照工资降序排序

select ename,sal,deptno from emp order by deptno asc, sal desc;

3.3、伪列和虚表

3.3.1、伪列和表达式

说明：查询不存在的列即伪列，当需要的结果不能直接从表中得到，需要经过计算来展示则可以使用伪列+表达式实现

select 1 from emp;
select ename, 1 from emp; # 这里的1就是伪列
# 查询员工姓名，月薪，年薪（月薪*12）
select ename,sal,sal*12 from emp;

1) null处理

nvl()

查询员工姓名、工资、提成（有的员工没有提成）、月收入

select ename,sal,comm,sal+nvl(comm,0) from emp;

2) 字符串拼接

||

select ename,ename||'a' from emp;

3.3.2、虚表

​ dual是一个虚表，虚拟表，是用来构成select的语法规则，oracle保证dual里面永远只有一条记录。该表只有一行一列，它和其他表一样，可执行插入、更新、删除操作，还可以执行drop操作。但是不要去执行drop表的操作，否则会使系统不能用，起不了数据库。

​ dual主要用来选择系统变量或是求一个表达式的值。如果我们不需要从具体的表来取得表中数据，而单纯的为了得到一些我们想得到的信息，并要通过select完成时，就要借助一个对象，这个对象就是dual

# 计算999*666
select 999*666 from dual;

3.4、条件查询

​ 当我们查询的数据需要经过筛选时，我们会给出一些条件，只有当表中的记录满足我们所给的条件，才会成为我们的目标数据，这就需要借助条件查询。

select 查询内容 from 数据来源 where 行记录条件

3.4.1、条件运算

=、>、<、>=、<=、<>、!=、^=、between and、in

# 查询10部门的员工信息
select * from emp where deptno = 10;
# 查询非10部门的员工信息
select * from emp where deptno != 10; # 推荐
select * from emp where deptno <> 10;
select * from emp where deptno ^= 10;

# 查询所有工资在2000以上的员工信息
select * from emp where sal > 2000;

# between and 查询工资在2000-4000之间的员工信息
select * from emp where sal between 2000 and 4000;

# in 查询在10部门或者20部门工作的员工信息
select * from emp where deptno in(10,20);

3.4.2、条件连接运算

and 、or、not

当查询条件多个时，可能需要同时满足，或者只满足其中一个，或者不满足某个条件，则需要用到 或、且、非

# 查询10部门并且性别为男的员工信息
select * from emp where deptno = 10 and gender = '男';

# 查询10部门或20部门的员工信息
select * from emp where deptno = 10 or dept = 20;

# 查询不在10部门的员工信息
select * from emp where not deptno = 10;

3.4.3、null运算nvl()

null比较特殊需要单独处理

is null、is not null、not…is null

# 查询所有可能获得奖金的员工信息
select * from emp where comm is not null;
select * from emp where not comm is null;
# 查询所有没有获得奖金的员工信息
select * from emp where comm is null;


# nvl() 当某个值为空时给出一个特定的值，否则该值就是他自己  
# nvl(1,100):不为空的时候就是1，为空的时候是100
select nvl(1,100) from dual;

# 查询获取到奖金的信息，奖金大于0
select * from emp where comm > 0;

3.4.4、模糊查询like

% 、 __

# 查询名字为包含‘S’的员工信息
select * from emp where ename like '%S%';
# 查询名称以‘A’开头的员工信息
select * from emp where ename like 'A%';
# 查询名称第二个字母为'A'的员工信息
select * from emp where ename like '_A%'

# 查询名称中包含 ‘%’的员工信息   a后面的%表示字符
select * from emp where ename like '%a%%' escape('a');
# 查询名称中包含 ‘a%’ 的员工信息
select * from emp where ename like '%aaa%%' escape('a');
# 查询名称中包含 ‘%’ 和 ‘_’ 的员工信息
select * from emp where ename like '%a%%a_%' escape('a');

3.4.5、where子句

判断条件不能直接得到，需要经过计算和过滤的

# 查询‘xxx’部门的员工信息
select * from emp where deptno=(select deptno from dept where dname='xxx');

4、函数

4.1、函数介绍

​ Oracle SQL提供了用于执行特定操作的专用函数，这些函数大大增强了SQL语言的功能，函数可以接受零个或多个输入参数，并且返回一个输出结果。这种由Oracle提供给我们的函数我们成为内置函数，除了有内置函数外还可以自定义函数。

​ 根据函数的返回结果，我们将函数分为单行函数和多行函数

1. 单行函数：对应在表记录时，一条记录返回一个结果。例如 lower（x），将参数转换为小写
2. 多行函数：也称 组函数 或 聚合函数（重点）：此类函数可同时对多条记录进行操作，并返回一个结果（重点）。例如max（x）求最大值。

4.2、常用单行函数

4.2.1、字符函数

concat(x,y) 连接字符串x和y

instr(x,str,start,n) 在x中查找str，可以指定从start开始，也可以指定从第n次开始

length(x) 返回x的长度

lower(x) x转换为小写

upper(x) x转换为大写

ltrim(x,trim_str) 把x左边戳去trim_str字符串，缺省截去空格

rtrim(x,trim_str) 把x右边戳去trim_str字符串，缺省截去空格

replace(x,old,new) 在x中查找old，并替换为new

substr(x,start,length) 返回x的字符串，从start处开始，截取length个字符，缺省length，默认到结尾

4.2.2、数学函数

ABS(m) 绝对值函数，用来返回m的绝对值

CEIL(m) 返回大于或等于m的最小整数

FLOOR(m) 返回小于或等于m的最大整数

MOD(m,n) 取余函数，返回两个整数m对n相除的余数，若n=0，返回m

POWER(m,n) 返回m的n次幂

EXP(m) 返回e的m次幂

ROUND(m,n) 返回m四舍五入到小数点右侧n位的值，若省略n，则四舍五入到整数位

SIGN(m) 返回m的符号，m为整数返回1，m为负数返回-1，m为0返回0

SQRT(m) 求平方根函数

TRUNC(m,n) 返回m舍入到知道n位的值，如果n为正，就截取到小数点右侧的这个数值处。如果指定的n为负就截取到小数点左侧的该数值处。如果没有指定n，就假定为0，截取到小数点处

LOG(m,n) 取对数函数，返回以m为底的数值n的对数。m为大于0且不等于1的正整数，n也为正整数

LN(m) 取自然对数函数

SIN(m) 取正弦值函数

COS(m) 取余弦值函数

TAN(m) 取正切值函数

ASIN(m) 取反正弦值函数，m要求大于等于-1且小于等于1

ACOS(m) 取反余弦值函数，m要求大于等于-1且小于等于1

SINH(m) 返回m的双曲正弦值

COSH(m) 返回m的双曲余弦值

TANH(m) 返回m的双阙正切值

4.2.3、日期函数

sysdate 当前系统时间

current_date 返回当前系统时间

add_months(d1,n1) 返回在日期d1基础上再加n1个月后新的日期

last_day(d1) 返回日期d1所在月份最后一天的日期

months_between(d1,d2) 返回日期d1到日期d2之间的月数

next_day(d1[,c1]) 返回日期d1在下周，星期几(参数c1)的日期

4.2.4、转换函数

to_char(x,c) 将日期或数据x按照c的格式转换为char数据类型

to_date(x,c) 将字符串x按照c的格式转换为日期

to_number(x) 将字符串x转化为数字型

4.3、常用组函数

组函数同时对多条记录进行操作，并返回一个结果

avg() 平均值

sum() 求和

min() 最小值

max() 最大值

count() 统计

注意：null不参与运算

4.4、group by分组

group by分组，将满足条件的记录进一步按照某特性进行分组。提取每组记录中的共性

结构：select … from … where … group by …

# 求每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

4.5、having过滤组信息

要获取的组信息也许要满足一定条件时，我们通过having来过滤组的条件。

结构： select … from … where … group by … having …

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

4.6、过滤行记录和组信息

​ 行记录的过滤是针对每条记录的筛选，组信息的过滤是针对组的筛选，是可以同时出现的，先筛选行，在过滤组。

where 筛选行，只能出现行信息

having 过滤组，只能出现组信息

结构：select … from … where … group by … having …

# 查询部门编号和部门里面的员工数量
# 只统计工资大于2000的员工
# 并且是工资大于2000的员工数量在2及以上的部门
select deptno,count(*) from emp where sal > 2000 group by deptno having count(*) >= 2;

5、分页和去重

5.1、分页介绍

​ 为什么会用到分页呢，因为列表内容太多，所以使用分页进行显示。数据过多单页面无法显示所有内容，则每一次只显示一部分的数据。

​ 分页，是一种将所有数据分段展示给用户的技术，用户每次看到的不是全部数据，而是其中一部分，如果在其中没有找到自己想要的内容，用户可以通过指定页码或是翻页的方式转换可见内容，直到找到自己想要的内容为止，其实这和我们阅读书籍类似。

实现分页的解决方案有两种：

1. 一次查询出数据库中的所有记录，然后在每页显示指定的记录

2. 对数据库进行多次查询，每次只获得本页的数据并显示

   ​ 如今网站建设中的数据都是海量的，若按方案1执行：无疑会加大服务器内存的负载，降低系统运行速度；若使用方案2执行，则可能会频繁操作数据库，也会影响响应效率；因而大家都会使用方案1+方案2来实现。

   ​ 分页的核心就是计算每页多少记录和总页数以及第几页。每一页的数据只需计算起始的记录和结束记录即可。

5.1.1、rownum

​ rownum不是一个真实存在的列，它是用于查询返回的行的编号，返回的第一行分配的是1，第二行是2，以此类推，这个伪字段可以用于限制查询返回的总行数。

​ 由于rownum总是结果集的编号，所以无法直接查询rownum>1的任何记录，因为总是从1开始的。

5.1.2、oracle分页实现

​ 虽然rownum不能直接查询大于1的记录，但是我们可以自己添加伪列，将查询的结果集中的rownum作为查询的来源，则此时来源中的rownum变成了普通字段，再通过这个rownum来进行某段记录的选取即可。

# 进行分页，每一页显示5条记录，查询第一页的数据
select * from emp where rownum <= 5;

# 查询第二页
# 查询所有员工信息，并加上伪列 rownum
select ename, sal, deptno, rownum from emp;
# 将查询出来的结果集作为临时的数据来源，此时，刚才临时的那个结果集rownum就变成了普通字段
select * from (select ename, sal, deptno, rownum rw from emp) where rw <= 10 and rw >5;


# 查询员工的信息，姓名，工资，部门编号，按照工资降序排序，实现分页，每一页显示3条记录，查询第一页记录
select ename, sal, deptno, r2
  from (select ename, sal, deptno, r1, rownum r2
          from (select ename, sal, deptno, rownum r1
                  from emp
                 order by sal desc))
 where r2 <= 3;
               
# 查询第二页
select ename, sal, deptno, r2
  from (select ename, sal, deptno, r1, rownum r2
          from (select ename, sal, deptno, rownum r1
                  from emp
                 order by sal desc))
 where r2 > (2 - 1) * 3
   and r2 <= 2 * 3;

# 查询第三页
 select ename, sal, deptno, r2
   from (select ename, sal, deptno, r1, rownum r2
           from (select ename, sal, deptno, rownum r1
                   from emp
                  order by sal desc))
  where r2 > (3 - 1) * 3
    and r2 <= 3 * 3;

5.2、去除重复记录

5.2.1、rowid介绍

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

​ 有时繁杂的数据检索时，普通检索条件不能达到要求，可以利用rowid来精确检索的结果

​ oracle中如果要查询某张表中多个字段，又只对某个字段去重的时候用distinct或者group by都不行，distinct和group by会对要查询的字段一起进行去重，也就是当查询的所有字段都相同，oracle才认为是重复的。这是用rowid是个不错的选择。

5.2.2、重复记录的查找

​ 题目场景：当我们表里出现了许多重复记录时，我们需要将重复的记录找出来

​ 实现步骤：1、按照重复内容分组 2、取出每一组中的一条并记录保留（注意具有唯一性） 3、删除未在保留范围的数据

-- 将所有相同的记录只保留一份
-- 1、将数据进行分组，按照重复信息进行分组
-- 2、在每一组中，选取一条记录进行保存，找到特性
-- 3、删除时，找那些不在保留范围内的数据

select min(rowid) from copy group by deptno, dname, loc; -- 保留的数据

select * from copy where rowid not in
(select min(rowid) from copy group by deptno, dname, loc); -- 需要删除的数据 

delete from copy where rowid not in
(select min(rowid) from copy group by deptno, dname, loc); -- 删除数据

6、表连接

6.1、表连接介绍

​ 当我们获取的数据不是来自同一张表而是来自于多张表时就需要使用到表连接。表连接就是一个表的行根据指定的条件跟另一个表的行连接起来形成新的行的过程。

​ 简单来说，我们将数据存在不同的表中，而不同的表有着它们自身的表结构，不同表之间可以是有关联的，大部分实际使用中，不会仅仅只需要一张表的信息，比如需要从一个班级表中找出北京地区的学生，再用这个信息去检索成绩表中它们的数学成绩，如果没有多表连接，那只能手动将第一个表的信息查询出来作为第二个表的检索信息去查询最终的结果，可想而知这将会是多么繁琐。

连接查询：

	1. 即查询的时候同时需要多张表（特别是存在外键关系的），此时需要多张表之间的值进行连接；
	2. 目前SQL标准提出过两种连接查询，第一种是较早的SQL92标准，第二种是目前使用广泛的较新的SQL99标准；
	3. 92形式简单，但编写较为冗长，99不仅在底层得到优化，而且形式看上去更加一目了然，逻辑性更强，一般建议使用00标准；

6.2.、92语法

​ 多张表需要全部放在from之后，所有的连接条件都放在where当中，因此SQL92中的等值连接、非等值连

接、外连接等其实只是where条件的筛选

结构：select … from table1,table2,table3 … where …

很多时候需要为表取别名（1、简化表名 2、可能存在自连接的情况）

连接的原理：按照from后面表的出现顺序，前面的表作为内存的for循环，后出现的表作为外层的for循环

6.2.1、笛卡尔积

​ 通过线性代数的人都知道，笛卡尔乘积通俗的说，就是两个集合中的每一个成员，都与对方集合中的任意一

个成员有关联。

例如有一个考勤记录表，记录着100个人的2011年4月的考勤信息，理论上这些人应该每天都有记录的。

但是实际上某些人在某些天上面的数据，缺少了。

一天一天的查询，还是一个人一个人的查询，都有些麻烦。

这种情况下，可以针对每个人与每一天做一个笛卡尔积的处理。

然后与实际的表去关联。就很容易查询出结果了。

select … from t1,t2

6.2.2、等值连接

在笛卡尔积的基础上取条件列相同的值

-- 查询出每一个有员工存在的部门的信息和部门人数
-- 查询数据：部门信息  对应的部门人数
-- 数据来源： dept，emp 经过计算
-- 连接条件：dept。deptno = 人数对应的.deptno
select * 
  from dept d,
       (select count(*), deptno from emp group by deptno) c where d.deptno = c.deptno;

6.2.3、非等值连接

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

-- 查询每一个员工的姓名，工资金额，入职时间，对应工资等级
-- 查询数据：ename，sal，hiredate，grade
-- 数据来源：emp,salgrade
-- 连接条件：emp.sql between losal and hisal

select ename, sal, hiredate, grade
  from emp e, salgrade s
 where e.sal between losal and hisal;

6.2.4、自连接

特殊的等值连接（来自同一张表）

-- 查询出每一个员工（有上级存在）自己的信息，以及上级的信息
-- 查询的数据： 员工信息，上级信息
-- 数据来源： emp e  emp m
-- 连接条件： e.mgr = m.empno
select * from emp e, emp m where e.mgr = m.empno;

6.2.5、外连接

内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。

外联接可以是左向外联接、右向外联接或完整外部联接。

左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某

行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

右向外联接是左向外联接的反尚联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返

回空值。

看+，带+的表为从表，对立面的表为主表

-- 外连接     主表（只要在主表出现的记录，就必须出现在结果中）
-- 查询每一个部门的信息和部门人数
-- 查询的数据：部门信息，部门人数
-- 数据来源： dept d， (select count(*), deptno form emp group by deptno) c
-- 连接条件： d.dept.no = c.deptno(+)
select *
  from dept d, (select count(*), deptno from emp group by deptno) c
 where d.deptno = c.deptno(+);

6.3、99语法

6.3.1、cross join

交叉连接，实现笛卡尔积

-- cross join
select * from dept d cross join emp e;

6.3.2、natural join

需要有（同列名、主外建）

自然连接，做等值连接

-- natural join（等值）   查询所有员工名称，员工编号，所属的部门编号，部门名称
-- 查询的数据：员工名称，员工编号，所属的部门编号，部门名称
-- 数据来源： emp  dept
-- 连接条件： emp.deptno = dept.deptno
select ename, empno, deptno, dname from emp natural join dept;

6.3.3、join using（同名列）

using连接，等值连接，必须有同名列

-- join using（等值）
-- 查询的数据：员工名称，员工编号，所属的部门编号，部门名称
-- 数据来源： emp  dept
-- 连接条件： emp.deptno = dept.deptno
select ename, empno, deptno, dname from emp join dept using(deptno);

6.3.4、join on

on连接，可做等值连接、非等值连接、自连接、可以解决一切连接，关系列必须要区分

-- 查看所有员工姓名，编号，以及所属部门编号，部门名称 （等值）
-- 同名列前是否要加限定词，如果使用的自然间连接 using连接，不能加，如果不是则需要加
select ename, empno, e.deptno, dname
  from emp e
  join dept d on e.deptno = d.deptno;
  
-- 查看每一个员工的姓名，工资，所属的部门编号，工资等级 （非等值）
-- 查询数据：员工的姓名，工资，所属的部门编号，工资等级
-- 数据来源： emp  salgrade
-- 连接条件： sal between losal and hisal
-- on 实现
select ename, sal, deptno, grade
  from emp e
  join salgrade s on sal between losal and hisal;
    
-- 三表关联
-- 查询30部门员工姓名，工资，部门编号，工资等级，部门名称
-- 查询数据：员工姓名，工资，部门编号，工资等级，部门名称
-- 数据来源：emp   dept   salgrade
-- 连接条件 sal between losal and hisal  emp.deptno=dept.deptno
select ename, sal, e.deptno, grade, dname
  from emp e
  join dept d on e.deptno = d.deptno
  join salgrade s on e.sal between losal and hisal
 where e.deptno = 30;

6.3.5、outer join

外连接，有主表和从表一说

left [outer] join on

left [outer] join using

right [outer] join on

right [outer] join using

-- 查看每一个员工编号，员工名称，上级编号，上级名称
-- 查询数据：员工编号，员工名称，上级编号，上级名称
-- 数据来源： emp e, emp m
-- 连接条件: e.mgr = m.empno
select e.empno 员工编号, e.ename 员工名称, e.mgr 上级编号, m.ename 上级名称
  from emp e
  left outer join emp m on e.mgr = m.empno;

6.4、集合操作

Union、Union All、Intersect、Minus

Union，并集（去重）对两个结果集进行并集操作，不包括重复行同时进行默认规则的排序；

Union All，全集（不去重）对两个结果集进行并集操作，包括重复行，不进行排序；

Intersect，交集（找出重复）对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；

Minus，差集（减去重复）对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序

-- 集合操作  --》两个结果集  字段个数和字段类型一一对应
-- union  并集（去重）
select 'a', 'b'
  from dual
union
select 'c', 'd'
  from dual
union
select 'a', 'd' from dual;

-- union all 全集（不去重）
select 'a', 'b'
  from dual
union
select 'c', 'd'
  from dual
union all
select 'a', 'd' from dual;

-- intersect  交集
(select 'a', 'b'
  from dual
union
select 'c', 'd'
  from dual)
intersect
(select 'a', 'b' from dual
  union
  select 'e','f' from dual
)

-- minus 差集
(select 'a', 'b'
  from dual
union
select 'c', 'd'
  from dual)
minus
(select 'a', 'b' from dual
  union
  select 'e','f' from dual
)

7、DDL语法

7.1、表操作

7.1.1、创建表

1）创建新表

create table 表名(
	字段名 类型（长度）,
    ...其他字段...
)；

2）从其他表拷贝结构

create table 表名 as select 字段列表 from 已有表 where 1!=1;

7.1.2、修改表结构

1）修改表名

raname 原表名 to 新表名

raname tb_txt to tb_txt_new;

2）修改列名

alter table 表名 rename column 列名 to

alter tb_txt_new rename column txtid to tid;

3）修改字段类型

alter table 表名 modify(字段 类型)

alter table tb_txt_new modify(tid vachar2(20));

4）添加列

alter table 表名 add 字段 类型

alter table tb_txt_new add col_test_name varchar2(30);

5）删除列

alter table 表名 drop column 字段

alter table tb_txt_new drop column col_test_name;

7.1.3、删除表

drop table 表名;

7.2、约束

​ 约束是数据库用来确保数据满足业务规则的手段，不过在真正的企业开发中，除了主键约

束这类具有强需求的约束，像外键约束，检查约束更多时候仅仅出现在数据库设计阶段，真实

环境却很少应用，更多是放到程序逻辑中去进行处理。这也比较容易理解，约束会一定程度上

较低数据库性能，有些规则直接在程序逻辑中处理就可以了，同时，也有可能在面对业务变更

或是系统扩展时，数据库约束会使得处理不够方便。不过在我看来，数据库约束是保证数据准

确性的最后一道防线，对于设计合理的系统，处于性能考虑数据库约束自然可有可无；不过若

是面对关联关系较为复杂的系统，且对系统而言，数据的准确性完整性要高于性能要求，那么

这些约束还是有必要的 （否则，就会出现各种相对业务规则来说莫名其妙的脏数据，本人可是

深有体会的。。）。总之，对于约束的选择无所谓合不合理，需要根据业务系统对于准确性和

性能要求的侧重度来決定。

数据库约束有五种：

• 主键约束 （PRIMARY KEY）
• 唯一性约束（UNIQUE）
• 非空约束（NOT NULL）
• 外键约束（FOREIGN KEY）
• 检查约束（CHECK）

7.2.1、创建表和约束

表名：tb_user（用户表）

编号	字段名	字段类型	说明
1	userid	number(5)	用户id，主键
2	username	varchar2(30)	用户名，非空，4~20个字符
3	userpwd	varchar2(20)	密码，非空，4~18个字符
4	age	number(3)	年龄，默认18，值大于等于18
5	gender	char(3)	性别，默认男，只能是男或女
6	email	varchar2(30)	邮箱，唯一
7	regtime	date	注册日期，默认当前日期

-- 创建表的同时，将约束进行创建，约束不设定名称

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) 
)

表名：tb_txt（文章表）

编号	字段名	字段类型	说明
1	txtid	number(5)	文章编号，主键
2	title	varchar2(32)	文章标题，非空，长度4~32字符
3	txt	varchar2(1024)	内容，最大长度1024
4	pubtime	date	发布日期，默认当前日期
5	userid	number(5)	作者，外键，参考用户表的用户id，删除时，自设为ull

create table tb_txt(
       txtid number(5) primary key,-- 唯一且非空
       title varchar2(32) not null check(length(title) between 4 and 32),
       txt varchar2(1024),
       pubtime date default(sysdate),
       userid number(5) references tb_user(userid) on delete set null
)

7.2.2、创建并追加约束

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 pk_user_name check(length(username) between 4 and 20);
alter table tb_user add constraint pk_user_pwd check(length(userpwd) between 4 and 18);
alter table tb_user add constraint pk_user_age check(age>=18);
alter table tb_user add constraint pk_user_gender check(gender in('男','女'));
alter table tb_user add constraint pk_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('男'));

7.2.3、禁用和启用的约束

​ 很多时候由于业务需要，比如我们有大量的历史数据，需要和现有数据合并，当前表存在数据库约束（如

非、空约束），而这些历史数据又包含违背非空约束的数据行，为了避免导入时由于违反约束而导入失败，我们通

过调整约束状态来达到目的。

数据库约束有两类状态

​ 启用/禁用 (enable/disable)：是否对新变更的数据启用约束验证

​ 验证/非验证 (validate/novalidate）：是否对表中已客观存在的数据进行约束验证

这两类四种状态从语法角度讲可以随意组合，默认是 enable validate

下面我们来看着四类组合会分别出现什么样的效果：

enable validate：默认的约束组合状态，无法添加违反约束的数据行，数据表中也不能存在违反约束的数据行；

enable novalidate ：无法添加违反约束的数据行，但对已存在的违反约束的数据行不做验证；

disable validate：可以添加违反约束的数据行，但对已存在的违反约束的数据行会做约束验证（从描述中可以看

出来，这本来就是一种相互矛盾的约束组合，只不过是语法上支持这种组合罢了，造成的结果就是会导致DML失败）

disable novalidate：可以廷加违法约束的数据行，对已存在的违反约束的数据也不做验证。拿上面的例子来

说，我们需要上传大量违反非空约束的历史数据（从业务角度讲这些数据不会造成系统功能异常），可以临时将约

束状态转为disable novalidate，以保证这些不合要求的数据导入表中

7.2.4、删除约束

alter table tb_user drop constraint uq_user_email cascade;

8、DML

8.1、DML介绍

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

位是记录。

Oracle数据库的DML表数据的操作有三种：

①insert （插入）； ②update （更新）； ③ delete（删除）。

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

8.2、DML之insert

​ 通过insert语句向指定的表中添加记录，添加记录时需要满足以下条件，类型和长度要兼容，（宇段 兼容

值)；值满足约束，主键(唯一十非空)，非空(必填)，唯一(不重复)，默认(没有填写使用默认值)，检查(满足条件)，

外键（参考主表主键列的值)；个数必须相同，指定列，个数顺序与列相同;没有指定，个数与表结构的列个数和顺

序相同(null也得占位，没有默认值)。

insert into 表名 [(字段列表)] values(值列表);

8.2.1、默认方式添加

insert into 表名 values （值列表）

此时的值列表顺序和个数以及类型需要和表结构一致，默认的，可以为空的列也都必须添上值。可以手写也可以从

别的表中获取

insert into tb_user values(1001,'test','test123',null,'女',null,'sysdate');

insert into tb_user_copy values(select * from tb_user);

8.2.2、添加时指定列和顺序

insert into 表名 （指定列） values（值列表）

此时的值列表要和指定的列个数、顺序、类型保持一致

insert into tb_user(username,userid,userpwd) values('tom',1002,'tompwd');

insert into tb_user_copy(username,userid,userpwd) (select username,userid,userpwd from tb_user);

当添加的记录中，存在外键关联时需要注意，可以采取先查询后添加的方式

insert into tb_txt(txtid,title,userid) (1001,'title01',(select userid from tb_user from where username='随笔大师'));

8.3、DML之update

通过update语句可以更新（修改）表中的记录值

update 表名 set 字段1=值1[，字段2=值2，…] where 过滤行记录；

update tb_user set userpwd=888 where 1=1;

update tb_user set userpwd='good',age='29' where username='zzz' and pwd='123';

update tb_user set(username,userpwd) = (select 'god','block' from dual) where userid = 1;

8.4、DML值之delete

通过delete语句可以删除表中的记录。（注意存在主外建约束的记录）

delete from 表名 where 条件；

delete from tb_user where userid<10;

lues(值列表);`

8.2.1、默认方式添加

insert into 表名 values （值列表）

此时的值列表顺序和个数以及类型需要和表结构一致，默认的，可以为空的列也都必须添上值。可以手写也可以从

别的表中获取

insert into tb_user values(1001,'test','test123',null,'女',null,'sysdate');

insert into tb_user_copy values(select * from tb_user);

8.2.2、添加时指定列和顺序

insert into 表名 （指定列） values（值列表）

此时的值列表要和指定的列个数、顺序、类型保持一致

insert into tb_user(username,userid,userpwd) values('tom',1002,'tompwd');

insert into tb_user_copy(username,userid,userpwd) (select username,userid,userpwd from tb_user);

当添加的记录中，存在外键关联时需要注意，可以采取先查询后添加的方式

insert into tb_txt(txtid,title,userid) (1001,'title01',(select userid from tb_user from where username='随笔大师'));

8.3、DML之update

通过update语句可以更新（修改）表中的记录值

update 表名 set 字段1=值1[，字段2=值2，…] where 过滤行记录；

update tb_user set userpwd=888 where 1=1;

update tb_user set userpwd='good',age='29' where username='zzz' and pwd='123';

update tb_user set(username,userpwd) = (select 'god','block' from dual) where userid = 1;

8.4、DML值之delete

通过delete语句可以删除表中的记录。（注意存在主外建约束的记录）

delete from 表名 where 条件；

delete from tb_user where userid<10;