22 Oracle数据库基础入门

1.Oracle数据库的介绍

　　ORACLE 数据库系统是美国ORACLE 公司（甲骨文）提供的以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器(CLIENT/SERVER)或 B/S 体系结构的数据库之一。比如SilverStream  就是基于数据库的一种中间件。ORACLE  数据库是目前世界上使用最为广泛的数据库管理系统，作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库， 它是一个完备关系的产品；作为分布式数据库它实现了分布式处理功能。但它的所有知识，只 要在一种机型上学习了 ORACLE 知识，便能在各种类型的机器上使用它。

2.Oracle数据库的体系结构

<1>数据库(Database)
Oracle 数据库是数据的物理存储。这就包括（数据文件ORA 或者 DBF、控制文件、联机日志、参数文件）。其实Oracle 数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle 就只有一个大数据库。
<2>实例(Instance)
一个 Oracle 实例（Oracle Instance）有一系列的后台进程（Backguound Processes)和内存结构（Memory Structures)组成。一个数据库可以有 n 个实例。
<3>用户
用户是在实例下建立的。不同实例可以建相同名字的用户。
<4>表空间
表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻辑映射。一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。每 个数据库至少有一个表空间(称之为system 表空间)。

3.创建表空间创建用户以及用户授权

可以利用navicat实现，具体操作步骤见：https://www.cnblogs.com/luckyplj/p/11365245.html

4.Oracle的数据类型

 

5.Oracle自带的Scott用户

默认密码：tiger

注意：默认情况下，scott用户是锁着的，需要利用SYSTEM这个超级管理员用户进行解锁。

进入如下界面：

6.Oracle的单行函数

(1)字符函数

<1>把小写的字符转换成大写的字符

SELECT UPPER('yes') FROM dual;

<2>把大写字符变成小写字符

SELECT LOWER('LUCKY') FROM dual;

(2)数值函数

<1>四舍五入函数：ROUND()

SELECT ROUND(56.12) FROM dual;

<2>直接截取，不看小数部分

SELECT TRUNC(56.12) FROM dual;

(3)日期函数

<1>查询scott用户下的数据库emp表中所有员工的入职距离现在几天

SELECT SYSDATE-e.HIREDATE FROM EMP e;

注意：EMP e是给EMP表取了一个别名。

<2>将日期类型转换为yyyy-mm-dd hh:mm:ss的格式的字符串

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MM:SS') FROM dual;

(4)通用函数

案例：算出emp表中所有员工的年薪，奖金里面有null值，又因为null值和任何数字做算数运算，结果都为null,所有会出现异常现象。

初始sql:

SELECT e.sal*12+e.comm FROM EMP e;

出现如下现象：

应将sql语句修改为：

SELECT e.sal*12+NVL(e.COMM,0) FROM EMP e;

7.Oracle的条件表达式

(1)案例：给emp表中的员工起别名

SELECT
    e.ENAME,
CASE
        e.ENAME 
        WHEN 'SMITH' THEN
        '曹操' 
        WHEN 'ALLEN' THEN
        '刘备' 
        WHEN 'WARD' THEN
        '司马懿' ELSE '无名' 
    END 
FROM
    EMP e;

结果：

8.多行函数(聚合函数) 

(1)统计记录数 count()

范例：查询出所有员工的记录数

SELECT COUNT(1) FROM emp;

查询结果图：

(2)最小值查询 min()

范例：查询出来员工最低工资

SELECT MIN(sal) FROM emp;

查询结果图：

(3)最大值查询 max()

范例：查询出员工的最高工资

SELECT MAX(sal) FROM emp;

查询结果图：

(4)查询平均值 avg()

范例：查询出员工的平均工资

SELECT AVG(sal) FROM emp;

查询结果图：

(5)求和函数 sum()

范例：查询出 20 号部门的员工的工资总和

SELECT SUM(sal) FROM emp e WHERE e.DEPTNO=20;

查询结果图：

9.分组查询

分组查询需要使用 GROUP BY 来分组

分组查询中，出现在group by 后面的原始列，才能出现在select后面，没有出现在group by 后面的列，想加在select后面，必须加上聚合函数。聚合函数有一个特性，可以把多行记录变成一个值。

范例1：查询出每个部门的平均工资。

SELECT
    e.DEPTNO,
    AVG( sal ) 
FROM
    emp e 
GROUP BY
    e.DEPTNO;

查询结果图：

范例2：查询出平均工资高于2000的部门信息。(该案例还涉及到了SQL语言中的having函数)

SELECT
    e.DEPTNO,
    AVG( sal ) 
FROM
    emp e 
GROUP BY
    e.DEPTNO
HAVING AVG(sal)>2000;

查询结果图：

范例3：查询出每个部门工资高于800的员工的平均工资。

SELECT
    e.DEPTNO,
    AVG( sal ) 
FROM
    emp e 
WHERE e.SAL>800
GROUP BY
    e.DEPTNO

查询结果图：

where条件语句和having语句的区别:
where是过滤分组前的数据，having是过滤分组后的数据。
表现形式：where必须在group by 之前，having是在group by 之后。

10.多表查询

使用一张以上的表做查询就是多表查询。

范例：查询出雇员的编号，姓名，部门的编号和名称，地址

SELECT
    e.EMPNO,
    e.ENAME,
    d.DEPTNO,
    d.dname,
    d.loc 
FROM
    emp e,
    dept d 
WHERE
    e.DEPTNO = d.DEPTNO;

查询结果图：

11.自连接

自连接概念：其实就是站在不同的角度把一张表看成多张表。

范例1：查询出员工姓名和员工领导姓名

SELECT e1.ENAME,e2.ENAME
FROM emp e1,emp e2
WHERE e1.MGR=e2.EMPNO;

sql语句解释：e1表的领导是e2表的员工，可以将e1表看成员工表，e2表看成领导表。

查询结果图：

范例2：查询出员工姓名、员工部门名称、员工领导名称、员工领导部门名称

SELECT
    e1.ENAME,
    d1.DNAME,
    e2.ENAME,
    d2.dname 
FROM
    emp e1,
    emp e2,
    dept d1,
    dept d2 
WHERE
    e1.MGR = e2.EMPNO 
    AND e1.DEPTNO = d1.DEPTNO 
    AND e2.DEPTNO = d2.DEPTNO;

查询结果图：

12.子查询

子查询：在一个查询的内部还包括另一个查询，则此查询称为子查询。
Sql 的任何位置都可以加入子查询。

(1)子查询返回一个值

范例：查询出工资和员工SCOTT一样的员工信息。

SELECT
    * 
FROM
    emp e 
WHERE
    e.SAL IN ( SELECT e.SAL FROM emp e WHERE e.ENAME = 'SCOTT' )

sql语句：*表示所有

查询结果图：

(2)子查询返回一个集合

范例：查询出工资和10号部门任意员工一样的员工信息。

SELECT
    * 
FROM
    emp e 
WHERE
    e.SAL IN ( SELECT e.SAL FROM emp e WHERE e.DEPTNO=10 )

查询结果图：

(3)子查询返回一张表

范例：查询每个部门的最低工资和最低工资员工姓名和所在部门名称

SELECT
    t.DEPTNO,
    t.MSAL,
    e.ename,
    d.dname
FROM
    ( SELECT e.DEPTNO, MIN( e.SAL ) msal FROM emp e GROUP BY e.DEPTNO ) t,
    emp e,
    dept d 
WHERE
    t.deptno = e.DEPTNO 
    AND t.MSAL = e.SAL 
    AND e.DEPTNO = d.DEPTNO;

sql语句分析：

//这两句是找到每一个部门中工资最少的员工
t.deptno = e.DEPTNO 
AND t.MSAL = e.SAL 

查询结果图：