Oracle 体系结构与基本概念

体系结构

Oracle服务器 ：是一个数据管理系统(RDBMS)，它提供开放的、 全面的、近乎完整的信息管理。由1个数据库和一个（或多个）实例组成。数据库位于硬盘上，实例位于内存中。

基本概念

表空间(users)和数据文件

1：n（表空间：数据文件）
逻辑概念：表空间由多个数据文件组成。位于实例上，在内存中。
物理概念：数据文件，位于硬盘之上。(C:\app\Administrator\oradata\orcl目录内后缀为.DBF的文件)
一个表空间可以包含一个或者是多个数据文件。
/home/oracle/app/oradata/orcl

段、区、块

段存在于表空间中；段是区的集合；区是数据块的集合；数据块会被映射到磁盘块。

Oracle数据库的基本元素

• 方案schema
  方案就是一个集合，包含多个表(tables)、视图(views)、序列(sequences)、存储过程（store procudures)、索引（indexes）、同义词（synonyms）。
  oracle中每个用户都对应一个方案，方案名就是用户名。
• 表table
  表示存储数据的逻辑容器，类似excel多行多列的表格。表创建的时候要定义它的每一列的列名以及该列的数据类型。（如：name char(10)，表示定义列名为name类型为字符串型）
  创建表之后才可以往里边添加数据。
• 视图view
  从多张表或者其他视图提取出用户所关心数据的一张虚表。
• 存储过程
  一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译。
• 序列sequence
  是oracle提供的用于产生一系列唯一数字的数据库对象。
• 索引index
  用于加快数据的检索，类似于书籍的索引。
• 同义词synonyms
  别名的意思，和视图的功能类似。就是一种映射关系。

Oracle数据库启动和关闭

前提条件: 使用Oracle用户登录Linux操作系统
[root@localhost ~]# su - oracle

数据库管理员用户:
用户名:sys
密 码:sys

Oracle数据库启动

第一步: 通过数据库管理员登录Oracle数据库
[oracle@localhost ~]$ sqlplus / as sysdba;

第二步: 启动Oracle数据库
SQL> startup

第三步: 启动监听服务

SQL> quit
[oracle@localhost ~]$ lsnrctl start

Oracle数据库关闭

第一步: 通过数据库管理员登录Oracle数据库
[oracle@localhost ~]$ sqlplus / as sysdba;

第二步: 关闭Oracle数据库

SQL> shutdown immediate

第三步: 关闭监听服务
SQL> quit
[oracle@localhost ~]$ lsnrctl stop

Sqlplus

Oracle的sqlplus是与oracle数据库进行交互的客户端工具，借助sqlplus可以查看、修改数据库记录。在sqlplus中，可以运行sqlplus命令与sql语句。
Sqlplus命令是用来修改以及调整sqlplus工具的命令。
Sql语句是用来让服务器执行相关动作的命令。

sqlplus 登录数据库管理系统

前提条件是Oracle数据库必须启动。

使用sqlplus登录Oracle数据库

sqlplus 用户名/密码
[oracle@localhost ~]$ sqlplus scott/11

远程登录

sqlplus 用户名/密码@//ip/实例名 ↙
远程登录oracle服务器方式，如(sqlplus scott/11@//192.168.161.130/orcl)

解锁用户

[oracle@localhost ~]$ sqlplus / as sysdba;
SQL> alter user scott account unlock;

修改用户密码

[oracle@localhost ~]$ sqlplus / as sysdba;
SQL> alter user scott identified by 11;

查看当前语言环境

SQL> select userenv('language') from dual;

sqlplus基本操作

前提条件是使用scott用户登录
[oracle@localhost ~]$ sqlplus scott/11

显示当前用户

SQL> show user

查看当前用户下的表

SQL> select *from tab;

查看员工表的结构

SQL> desc dept;

设置行宽

默认显示不好看，因此需要设置行宽。
SQL> set linesize 140;

设置页大小

SQL> set pagesize 100;

永久设置行宽、页宽

永久设置edit打开为gedit。

设置员工ename的列宽

SQL> col ename for a6 (a表示字符串)

设置薪水的列宽

col sal for 9999(一个9表示一位数字)

Oracle默认方案

安装oracle数据库默认会自动创建scott和hr两个用户以及其方案，这些都是oracle提供给我们学习和练习的数据，我们直接在上边练习就好。

scott方案

业务场景

Scott（斯科特）是一家软件公司的数据分析师，公司为其提供专门的数据库管理账号以及方案。
公司有总裁一名(president)、经理(manager)、店员(clerk)、销售专员(salesman)，分析师(analyst)若干。
公司设置有多个部门：会计部（accounting）、研发部(researching)、销售部(sales)。

根据公司业务抽离出的表

• 员工信息表EMP（Employee）
• 部门表DEPT（Department）
• 工资等级表（SALGRADE）
• 奖金表（BONUS）

grade: 薪水等级
losal: 最低薪水
hisal: 最高薪水

deptno: 部门编号
dname: 部门名称
loc: 部门位置

empno: 员工编号
ename: 员工姓名
job: 员工工种
mgr: 员工上司
hiredate: 雇佣日期
sal: 员工薪水
comm: 员工奖金
deptno: 员工部门编号

hr方案

SQL

结构化查询语言(Structured Query Language)简称SQL，是对数据库进行增、删、查、改等操作的语言。
SQL 是1986年10 月由美国国家标准局（ANSI）通过的数据库语言美国标准，接着，国际标准化组织（ISO）颁布了SQL正式国际标准。1989年4月，ISO提出了具有完整性特征的SQL89标准，1992年11月又公布了SQL92标准.
各种不同的数据库对SQL语言的支持与标准存在着细微的不同，这是因为，有的产品的开发先于标准的公布，另外，各产品开发商为了达到特殊的性能或新的特性，需要对标准进行扩展。

SQL语言的类型

DML

数据库中，称呼增删改查，为DML语句。(Data Manipulation Language 数据操纵语言)，如：
增 insert
删 delete
改 update
查 select

DDL

数据定义语言(Data Definition Language)。如：
create table(创建表)
alter table(修改表)
truncate table(清空表)
drop table(删除表)
create view(视图)
create index(索引)
create sequence(序列)
create synonym(同义词)

DCL

数据控制语言（Data Control Language）。如：
commit(提交)
rollback(回滚)

基本 SELECT 语句

基本语法格式为：

语法描述说明：
花括号{}括起的部分为必填部分。
中括号[]括起来的部分为可选部分。
多种形式的语法用竖线|来表示并列单选，或者的意思。

SQL语句使用注意事项：

• SQL 语言大小写不敏感。
• SQL 可以写在一行或者多行，以分号；作为一条sql语句的结束标志。
• 关键字不能被缩写也不能分行。
• 各子句一般要分行写。
• 使用缩进提高语句的可读性。

查询案例

查询员工号、姓名、薪水

增加查询年薪(使用表达式)

别名：as

解决乱码问题，修改系统字符集。

解决方法

DISTINCT关键字

去除结果集重复的行。

算数运算 + - * / 和 NULL值

• 乘除的优先级高于加减
• 优先级相同时，按照从左至右运算
• 可以使用括号改变优先级

查询: 员工号、姓名、月薪、年薪、奖金、年收入。

结果不正确。没有奖金的员工，年收入不正确。
NULL值问题：
包含NULL值的表达式都为空。
NULL != NULL
任何算数运算碰到NULL值都变成NULL，任何逻辑运算碰到NULL值都变成假

解决：滤空函数：nvl(a, b) 如果a为NULL， 函数返回b。所以：sal * 12 + nvl(comm, 0) 年收入。

NULL != NULL举例：

在SQL中，判断一值是否等于另外一值不用“=” 和“!=”而使用is和is not。

过滤和排序数据

where条件过滤

查询10号部门的员工信息

查询"KING"的信息

修改系统当前日期格式

查询入职日期为1981年11月17日的员工：

查看系统当前的日期格式：

获取系统当前日期格式：

SQL> select * from v$nls_parameters
修改日期格式：

SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd '

再次查询：

改回系统默认格式：SQL> alter session set NLS_DATE_FORMAT = ‘DD-MON-RR’;

比较运算

普通比较运算符：

= 等于(不是==)	> 大于
>= 大于等于	< 小于
<= 小于等于	<> 不等于(也可以是!=)

查询薪水不等于1250的员工信息：

查询工资在1000-2000之间的员工：


查询81年2月至82年2月入职的员工信息：

IN：在集合中。(not in 不在集合中)

查询部门号为10或20的员工信息：

like：模糊查询

‘%’匹配任意多个字符。
‘_’匹配一个字符。
查询名字以S开头的员工：

查询名字是4个字的员工：

转义字符：

增加测试例子：向表中插入员工：
SQL> insert into emp(empno, ename, sal, deptno) values(1001, ’ TOM_ABC ', 3000, 10)

查询名字中包含_的员工：

SQL> select * from emp where ename like '%\_% ' escape '\';

转义单引号本身：

SQL> select 'hello '' world' from dual;

使用两个单引号来完成转义。

逻辑运算

AND 逻辑与
OR 逻辑或
NOT 逻辑非

如果
……where 表达式1 and 表达式2;
……where 表达式2 and 表达式1;
这两句SQL语句功能一样吗？效率一样吗？
SQL在解析where的时候，是从右至左解析的。
所以and时应该将易假的值放在右侧，or时应该将易真的值放在右侧。

order by 排序

order by + 列名，表达式，别名，序号。 注意：语法要求order by 子句应放在select的结尾。

使用 ORDER BY 子句排序

• ASC（ascend）: 升序。默认采用升序方式。
• DESC（descend）: 降序

查询员工信息，按月薪排序：

order by后有多列时，列名之间用逗号隔分，order by会同时作用于多列。

desc 只作用于最近的一列，两列都要降序排，则需要两个desc。即：

order by + 列名，表达式，别名，序号。



查询员工信息， 按奖金由高到低排序：

结果前面的值为NULL， 数据在后面，如果是一个100页的报表，这样显示肯定不正确。较为人性化的显示应该将空值放在最后， 即：

排序的规则

• 可以按照select语句中的列名排序
• 可以按照别名列名排序
• 可以按照select语句中的列名的顺序值排序
• 如果要按照多列进行排序，则规则是先按照第一列排序，如果相同，则按照第二列排序；以此类推

单行函数

单行函数:只对一行数据进行计算，产生一个结果。函数可以没有参数，但必须要有返回值。如：concat、nvl

• 操作数据对象 hello world
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以转换数据类型
• 可以嵌套
• 参数可以是一列或一个值

字符函数

操作对象是字符串。
大致可分为两大类：一类是大小写控制函数，主要有
lower 转小写

upper 转大写

initcap 首字母大写

另一类是字符控制函数：有CONCAT、SUBSTR、LENGTH/LENGTHB、INSTR、LPAD | RPAD、TRIM、REPLACE

concat(a,b)：连接字符串a和b，只能连接两个

注意：SQL双引号“”表示别名，使用‘’来表示字符串。

另一种连接字符串的方法，使用||，可以做到多个字符串连接

substr(a, b)：从a中，第b位开始取(计数从1开始)，取到结尾。

substr(a, b, c)：从a中，第b位开始，向右取c位。

length：字符数， lengthb：字节数

instr：在母串中查找子串， 找到返回下标，计数从1开始。没有返回0

lpad：左填充，参1：待填充的字符串，参2：填充后字符串的总长度(字节)， 参3：填充什么
rpad：右填充。

trim：去掉前后指定的字符

replace：替换

数值函数

ROUND: 四舍五入

TRUNC: 截断

MOD: 求余

时间函数

在Oracle中日期型的数据，既有日期部分，也有时间部分。下一节介绍日期部分。

显示 昨天、今天、明天：

计算员工的工龄：

日期函数

上面求取员工工龄的结果不精确，如果想将其算准确，可以使用日期函数来做。

months_between：两个日期值相差的月数(精确值) 跟between…and无关

add_months：在某个日期值上，加上多少的月，正数向后计算，负数向前计算。

last_day：日期所在月的最后一天。

next_day：指定日期的下一个日期

转换函数

在不同的数据类型之间完成转换。将“123” 转换为 123。有隐式转换和显式转换之分。

隐式转换：

显式转换：

隐式转换的前提条件是：被转换的对象是可以转换的。

显示转换：借助to_char(数据，格式)、to_number、to_date函数来完成转换。

如果隐式转换和显示转换都可以使用，应该首选哪个呢？

SQL优化：如果隐式、显式都可以使用，应该首选显式，这样可以省去Oracle的解析过程。

在固定的格式里加入自定义的格式，是可以的，必须要加“”。

反向操作：已知字符串“2024-2-1 18:30:20 ”转化成日期。

查询员工的薪水：2位小数， 本地货币代码， 千位符

将$2,975.00转化成数字：

通用函数

这些函数适用于任何数据类型，同时也适用于空值：

• NVL (expr1, expr2)
  

• NVL2 (expr1, expr2, expr3)

• NULLIF (expr1, expr2)

• COALESCE (expr1, expr2, …, exprn)

nvl2： 是nvl函数的增强版。 nvl2(a, b, c) 当a = null 返回 c， 否则返回b。

nullif： nullif(a, b) 当 a = b 时返回null, 不相等的时候返回a值。

coalesce: coalesce(a, b, c, …, n) 从左向右找参数中第一个不为空的值。

条件表达式

例子：老板打算给员工涨工资, 要求：
总裁(PRESIDENT)涨1000，经理(MANAGER)涨800，其他人涨400。请将涨前、涨后的薪水列出。

但是在SQL中无法实现if else 逻辑。当有这种需求的时候，可以使用case 或者 decode
case： 是一个表达式，其语法为：



decode：是一个函数，其语法为：

分组函数

分组函数作用于一组数据，并对一组数据返回一个值。
如：AVG、COUNT、MAX、MIN、SUM操作的是一组数据，返回一个结果。
求员工的工资总额：

员工人数：

平均工资：

求员工的平均奖金：

☆NULL空值：组函数都有自动滤空功能(忽略空值)，所以：

如何屏蔽 组函数 的滤空功能：

但是实际应用中，结果为14和结果为4都有可能对，看问题本身是否要求统计空值。

count函数：求个数，如果要求不重复的个数，使用distinct。
求emp表中的工种：

分组数据

group by

按照group by 后给定的表达式，将from后面的table进行分组。针对每一组，使用组函数。

查询“部门”的平均工资

SQL> select deptno,avg(sal)from emp group by deptno;

上述SQL语句可以抽象成：select a, 组函数(x) from 表 group by a; 这样的格式。

如果select a, b 组函数(x) …… group by 应该怎么写？
注意：在SELECT 列表中所有没有包含在组函数中的列，都必须在group by的后面出现。所以上问应该写成group by a, b;没有b语法就会出错，不会执行SQL语句。但，反之可以。group by a，b，c; c可以不出现在select语句中。

group by后面有多列的情况

SQL> select deptno,job,avg(sal) from emp group by deptno,job order by 1;

因为deptno, job 两列没有在组函数里面，所以必须同时在group by后面。
该SQL的语义：按部门，不同的职位统计平均工资。先按第一列分组，如果第一列相同，再按第二列分组。
所以查询结果中，同一部门中没有重复的职位。

having

使用 HAVING 过滤分组:

1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。

语法：

查询平均薪水大于2000的部门

分析：该问题实际上是在分组的基础上过滤分组。
SQL> select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

注意：
不能在 WHERE 子句中使用组函数。
可以在 HAVING 子句中使用组函数。

从功能上讲，where和having都是将满足条件的结果进行过滤。但是差别是where子句中不能使用 组函数！所以上句中的having不可以使用where代替。

求10号部门的平均工资

在子句中没有使用组函数的情况下，where、having都可以，应该怎么选择？

SQL优化： 尽量采用where。
如果有分组的话，where是先过滤再分组，而having是先分组再过滤。当数据量庞大如1亿条，where优势明显。

多表查询

多表查询的过程中其实就会构造多个表的一个笛卡尔积。

笛卡尔积

笛卡尔积就是两个集合的乘积计算，其实跟我们普通的乘法分配律相像，不过是作用于集合：

如果有一个集合M(a,b) 和集合N(c,d) , 那么集合的乘积
M x N = (a,b) x (c,d)= ( a x (c,d), b x (c,d) ) = ( ac,ad,bc,bd)

具体到我们的数据库表如下：

笛卡尔积的行数 = table1的行数 x table2的行数
笛卡尔积的列数 = table1的列数 + table2的列数

比如：
select * from emp,dept; 会得到如下的一个表

多表查询就是按照给定条件(连接条件)，从笛卡尔全集中选出正确的结果。
比如上图理应该两个deptno 相同的数据才是有意义的数据。

根据连接条件的不同可以划分为：等值链接、不等值链接、外链接、自连接 。

等值连接

从概念上，区分等值连接和不等值连接非常简单，只需要辨别where子句后面的条件，是“=”为等值连接。不是“=”为不等值连接。

查询员工信息：员工号 姓名 月薪和部门名称

通常在进行多表查询的时，会给表起一个别名，使用“别名.列名”的方式来获取数据，直接使用“表名.列名”语法上是允许的，但是实际很少这样用。
SQL> select e.empno,e.ename,e.sal,e.deptno,d.dname,d.deptno from emp e , dept d where e.deptno= d.deptno;

如果：select e.empno, e.ename, e.sal, e.deptno, d.dname, d.deptno from emp e, dept d;
直接得到的是笛卡尔全集。其中有错误结果。所以应该加 where 条件进行过滤。
如果有N个表，where后面的条件至少应该有N-1个。

不等值连接

将上面的问题稍微调整下，查询员工信息：员工号 姓名 月薪 和 薪水级别(salgrade表)

分析：

看到员工总的薪水级别，共有5级，员工的薪水级别应该满足 >=当前级别的下限，<=该级别的上限：

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

外连接

按部门统计员工人数，显示如下信息：
部门号 部门名称 人数

分析：
人数：一定是在emp表中，使用count()函数统计emp表中任一非空列均可。
部门名称：在dept表dname中，直接读取即可。
部门号：任意，两张表都有。

注意：由于使用了组函数count()，所以组函数外的d.deptno和d.dname必须放到group by后。

但是select * from dept发现40号部门没有显示出来，原因是40号部门没有员工，where没满足。结果不对，40号部门没有员工，应该在40号部门位置显示0。

我们希望： 在最后的结果中，包含某些对于where条件来说不成立的记录 （外连接的作用）

左外连接：当 where e.deptno=d.deptno 不成立的时候，=左边所表示的信息，仍然被包含。
写法：与叫法相反：where e.deptno=d.deptno(+)

右外连接：当 where e.deptno=d.deptno 不成立的时候，=右边所表示的信息，仍然被包含。
写法：依然与叫法相反：where e.deptno(+)=d.deptno

以上我们希望将没有员工的部门仍然包含到查询的结果当中。因此应该使用外链接的语法。

右外链接写法

左外链接写法

自连接