Oracle数据库

Oracle数据库

什么是数据库

数据库是保存，管理和操作数据的软件。

为什么使用数据库

1. 源于应用软件中的数据持久化需求；内存空间有限，况且内存保存数据是一种临时操作，如果程序出现例外，数据就会丢失。
2. 数据持久化是将数据写入能够持久化存储数据的硬件设备中，如磁盘。

什么是关系型数据库

数据库分为关系型数据库和非关系型数据库。
关系型数据库指的是，保存在数据库中的数据之间存在一定的关联关系。主要体现在：
表和表之间存在关联关系（一对一，一对多，多对多）
表自身的行和列也存在二元关系
关系型数据库中，数据不能脱离表存在。
非关系型数据库通常没有表的概念，保存的数据之间也没有关系。

sql命令中有五种分类

1.DDL Data Definition Language数据定义语言

DDL主要针对Oracle中的对象结构进行操作，不能操作表中的数据，针对列进行操作。
create创建 drop删除 alter改 truncate截断

2.DCL Data Control Language 数据控制语言

主要控制用户的权限
grant 向用户赋予权限 grant 角色/权限 to 用户名；
revoke 撤销用户权限 revoke 角色/权限 from 用户名；

3.DML Data Manipulation Language，数据操纵语言

insert 新增 delete 删除 update 修改
DML具有事务性，除DML之外，其他语句一旦执行就会立即生效，而且不能撤销.而DML中增删改操作执行之后不会立即生效，要使其生效，需要进行一次提交事务操作。在提交事务之前，如果发现数据有误，还可以通过回滚指令退回到上一次提交之后的状态，使数据恢复。

DQL Data Query Language数据查询语言

指select查询语句

DTL/TCL Data Transaction Language数据事务语言,Transaciton Control Language事务控制语言

commit 提交事务。执行完毕，确认生效。一旦提交事务，则不可撤销
rollback 回滚事务。发现有误，撤回上一个节点。

DDL中也有增删改查，和DML中的有什么区别吗

1. create和insert：create影响的是表结构，将一个表从无到有创建出来。insert是在表存在的情况下，向表中插入一条数据。
2. drop和delete：drop删除是将表删掉，delete是删除表中的数据
3. alter和update：alter修改的是字段的名称，约束，类型，默认值等结构设定；update修改的是具体的字段值。

查询语句

1. 查询语句的基本语法
   select 字段名 from 表名
   distinct关键字作用是消除查询结果中重复的内容。例：select distinct name from s_dept 注：distinct关键字只出现一次，并且，如果查询的字段不止一个，那么只有在多个字段值相等时，才会判定重复，消除重复。

2. 算术运算（只有数值类型和日期类型支持）
   1）select id，salary * 12 from s_emp;
   算术运算查询不会对表中的数据值产生变化，算术运算只是在查到的结果字段值的基础上进行运算，并没有修改表中的数值。
   2）日期类型的算术运算
   select sysdate from dual；（sysdate是一个函数，用来返回一个Date类型的当前日期和时间）
   select sysdate -1 from dual；（查询昨天此时此刻的时间）

   综上所述，select关键字后面定义的内容不一定必须与某些表中的字段有关。实际上，我们可以从一张表中去查询和这张表字段没有任何关联的内容，例如可以进行一些算术运算或者函数的调用。

出现这样结果的原因：

select查询操作实际上就是在对一张表进行遍历，因为后面可能会通过where关键字添加一些限定性条件。所以说SQL执行引擎必须把每一条数据进行一次判断，符合where条件的留下，不符合where条件的去除掉

大致流程：

拿到表中的第一条数据，判断是否符合where条件。但是没有定义where子句，视为直接满足要求。满足要求之后，就会把select后面定义的内容进行取值、计算，并且返回输出。接下来，拿到表中的第二条数据，判断是否符合where条件。但是没有定义where子句，视为直接满足要求。满足要求之后，就会把select后面定义的内容进行取值、计算，并且返回输出……再比如，现在要查询当前系统时间。为了能够让函数调用的结果显示出来，我们必须把sysdate函数的调用定义在select后面。出现了select关键字，那么就必须要伴随着from关键字。但是，sysdate只是一个系统函数，它不依赖于任何一张表而存在。也可以理解为我们可以从任何一张表中查询sysdate函数的返回值。因为select后面可以声明的内容，未必与from后边声明的这张表有绝对关联。但是，从常规的表中查询sysdate的弊端就是会产生多条重复结果。被查询的表中有多少行，查询结果中就会有多少条完全相同的系统时间显示。其实我们只需要一条结果即可，不需要这么多重复的数据。为了解决这样的问题，方便我们借助select以及数据库的计算和处理能力进行某些运算，我们可以自己单独创建一张表格。这张表格不用于存储数据，里面固定有一条数据。之后，我们如果需要使用select执行一些和表中字段值没有关联的计算或者函数调用，就可以固定地从这张表里查询，它能够确保查询的结果永远只有一条。实际上Oracle数据库中已经为我们提供了一张这样的表格，不需要我们自己创建。Oracle中提供的这张表叫做dual，意思是哑表，或虚表、伪表，代表它并不是一张真正的用来存储数据的表格。它存在的目的只是为了在某些select查询中满足select的语法结构要求。这张哑表不能插入数据，也不能删除数据。它能够确保表中数据永远只有一条。我们无论使用这张哑表查询任何计算、调用任何函数，结果永远只有一条。

查询语句中的函数

1. 处理空值nvl（参数1，参数2）
   例如select id, salary + salary * nvl(commission_pct, 0) / 100 from s_emp; 当commission_pct的值为空时，nvl返回值为0；若commission_pct的值不为空时，nvl返回值为该值。
2. 字符串的拼接
   1）可以使用||符号拼接，||可以连续拼接
   例如select first_name || last_name from s_emp;
   2）使用concat函数拼接 concat（参数1，参数2）
   例如：select concat(first_name, last_name) from s_emp;
   注：在mysql中concat的参数不限数量，但是oracle中要使用concat函数处理两个以上的字符串拼接，则需要函数的嵌套使用。

起别名有以下四种写法：

1）select 字段名 as 别名 from 表名;
2）select 字段名 别名 from 表名;
3）select 字段名 as “别名” from 表名;
4）select 字段名 “别名” from 表名;

指令

column指令用来修改查询结果中字段的显示格式。对于字符串类型的字段来说，主要就是用来修改显示长度。
语法：column 字段名 format 格式;
注：column指令修改的格式，仅针对于当前会话生效。重新开启会话，之前设置的column效果就会失效。可以使用clear columns指令清空column指令之前设定的效果

排序

关键字order by
语法： select 字段 from 表名 order by 排序字段 [排序规则]; 排序规则有两种DESC降序排列 ASC升序排列
排序中空值被视为无限大；如果有两种排序规则时，只有当第一种排序规则无法排出结果，才会使用第二种排序规则
between（not between）
语法：where 字段 between 较小值 and 较大值;
例如：
select id,salary
from s_emp
where salary >= 1000 and salary <= 1500;where salary between 1000 and 1500;
in（not in）
语法：where 字段名 in(值1,值2,值3…)

模糊查询

模糊匹配也叫模糊查询、关键字检索，主要针对字符串类型字段值。
模糊匹配中，不能再使用之前的“=”进行条件限定，改为like单词。
同时还需要用到两个用于匹配的符号：

• % 通配符，匹配任意数量任意字符
• _ 占位符，匹配1个任意字符
• 【案例】查询last_name中包含’s’的员工信息？
  select id,last_name
  from s_emp
  where last_name like ‘%s%’;

【案例】查询last_name中不包含’s’以及’S’的员工信息？
select id,last_name
from s_emp
where last_name not like ‘%s%’ and last_name not like ‘%S%’;

【案例】查询last_name中第二个字母是’g’的员工信息？
select id,last_name
from s_emp
where last_name like ‘_g%’;

多表查询

基本语法
select … from 表1，表2…

外连接

原则：A左外连接到B，就可以查出来没有B的A。
需要查出来没有部门的员工，就需要把员工表左外连接到部门表。

语法：

select 字段
from 表1 left [outer] join 表2
on 连接条件;

上述SQL应该改为：

select e.id, d.name
from s_emp e left join s_dept d
on e.dept_id = d.id;

b）右外连接

右外连接和左外连接功能是相同的，只不过连接方向相反。

表A左外连接到表B，就相当于表B右外连接到表A。

之前：员工表左外连接到部门表
也可以：部门表右外连接到员工表

select e.id, d.name
from s_dept d right outer join s_emp e
on e.dept_id = d.id;

c）全连接

同时使用左右两种外连接的效果。

【案例】查询出所有员工的id、部门的名称？要求把没有员工的部门和没有部门的员工全都查询出来？

语法：from 表1 full [outer] join 表2

select e.id, d.name
from s_emp e full join s_dept d
on e.dept_id = d.id;

4）自连接

自己和自己进行连接。实际上用的还是等值连接、外连接的语法，只不过数据都来自于同一张表。

5）集合连接

集合：指的是一次select查询语句的结果

连接的就是两次查询的结果。

从书写形式上来说，执行两次或多次select查询语句，使用集合连接的关键词把查询的结果连接成为一个结果。

相当于数学上集合的运算。

• union 取并集，会消除掉集合中重复的数据。

  例如现在有A = {a, b, c} B = {a, c ,d}，A union B = {a, b, c, d}

• union all 取并集，不会消除掉重复数据。

  例如现在有A = {a, b, c} B = {a, c ,d}，A union B = {a, a, b, c, c, d}

• intersect 取交集。

  例如现在有A = {a, b, c} B = {a, c ,d}，A intersect B = {a, c}

• minus 取差集。

  例如现在有A = {a, b, c} B = {a, c ,d}，A minus B = {b}

例如：分别用两条select语句查询工作在41或42部门的员工，以及工资大于1200元的所有员工信息，

只需要把两次查询结果取交集，得到的就是两者皆满足的员工信息。
select id,last_name,salary,dept_id
from s_emp
where dept_id = 41 or dept_id = 42
intersect
select id,last_name,salary,dept_id
from s_emp
where salary > 1200;

注意：使用集合连接的语法连接两条select语句的时候，要求两次查询结果中的字段结构必须是相似的。

相似：1）字段数量必须相同

​ 2）字段的类型也必须兼容（一般情况下都是相同的）

5、伪列

不是一个真正的字段。

不存在于任何一张表中，也可以认为伪列是存在于任何一张表中的隐藏字段。

主要出现在每一次查询结果当中。

伪列有两个：rowid、rownum，我们在任何一个表中都没有定义过这两个字段，但是每一次查询，查询任何一张表，都可以把这两个字段查询出来。

1）rowid 了解即可

数据库表中每一条数据对应的唯一标识，值代表物理存储地址。

2）rownum 学会使用

描述每一次查询结果中的行号，给每一次查询出来的每一条数据从1开始标上序号。

rownum值代表这条数据在本次查询结果当中是第几行。

rownum在实际开发过程中可以用来实现分页功能。

假设：现在每页10条数据，查询第一页：1-10条，第二页：11-20条，第三页：21-30条

【案例】假设用户在浏览器端请求第一页数据，每页10条。需要从数据库中查询某张表中第1-10条数据。
select id,last_name,rownum
from s_emp
where rownum <= 10;

select语句六大关键字定义顺序：

select、from、where、group by、having、order by

但是要注意，六大关键字实际解析执行的顺序与定于顺序并不相同：
1.from 找到查询的表
2.where 把不符合条件的行排除
3.group by 把剩余数据分组
4.having 将不符合条件的组数据排除
5.select 返回查询的列，执行一些函数运算
6.order by将查询结果排序

子查询（嵌套查询）

子查询也叫嵌套查询，核心思想是把一次select查询语句的结果，当成一张表格，使用另外一条select语句从这张表中进行查询。把一次select的结果作为另外一次select查询的输入。

之前我们都是从现成表中查询，子查询指的是从一次select查询结果中查询。

事务的四大特性ACID

• 原子性：Atomicity

  一次事务就是最小原子，不可再分。一次事务中所包含的所有的DML语句，都必须当成一个整体来执行。要么同时成功，要么同时失败。不允许拆分开来一部分成功，一部分失败。

  成功：所有的DML全部执行成功，并且提交事务。

  失败：有任何一条DML语句出现问题，都执行rollback回滚。

• 一致性：Consisitency

  事务执行的结果必须是使数据库从一个一致性状态，进入到另外一个一致性状态。

• 隔离性：Isolation

  事务操作应该相互独立。

• 持久性：Durability

  在一个事务结束之后，它所造成的影响是持久性的。

  假设，在事务没有正常结束的情况下，插入了一些数据，非正常关掉终端，再次打开会发现数据并没有插入到数据库中。

  只要通过提交或回滚方式结束了事务，那么所有的改变就都永久生效。

  此时，查询的任何结果，与重新打开终端之后查询的任何结果都是相同的。