Oracle_SQL基础与PL/SQL

发表于 | 分类于 |

Oracle-SQL

SQL语句分为以下三种类型：

  1. DML 数据操纵语言
    1. DML用于查询与修改数据记录；如INSERT、UPDATE、DELECT、SELECT
  2. DDL 数据定义语言
    1. DDL用于定义数据库的结构；如CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE INDEX、DROP INDEX
  3. DCL 数据控制语言
    1. DCL用来控制数据库的访问；GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT、LOCK

1.基本的SQL-SELECT语句

基本SELECT语句

1
2
SELECT 选择哪些列
FROM 从个表中;

选择全部列

1
2
SELECT *
FROM departments;


选择特定的列
1
2
SELECT department_id, location_id
FROM departments;

数字和日期使用的算术运算符
| 操作符 | 描述 |
| :——- | :—-: |
| + | 加 |
| - | 减 |
| | 乘 |
| / | 除 |
*操作符优先级

  1. 乘除优先级高于加减
  2. 同一优先级别运算符从左向右执行
  3. 括号内的运算先执行
1
2
SELECT last_name, salary, salary*12+1000, 12*(salary + 1000)
FROM employees;

日期可以做加减，但是不可以进行乘和除。

1
2
SELECT SYSDATE, SYSDATE + 1, SYSDATE-2 
FROM dual;

定义空值

  1. 空值是无效的，未指定的，未知的或不可预知的值
  2. 空值不是空格或者0

空值不同于0，凡是空值参与的运算，结果都为空(NULL)。

列的别名

1
2
SELECT employee_id id, last_name name, 12*salary annual_sal
FROM employees;

列的别名可以使用双引号

1
2
SELECT employee_id "id", last_name "name", 12*salary "annual_sal"
FROM employees;

连接符

  1. 把列与列，列与字符连接在一起
  2. 用’||’表示
  3. 可以用来’合成’列
1
2
SELECT last_name || '`s job_id is ' || job_id
FROM employees;

字符串

  1. 字符串可以是SELECT列表中的一个字符，数字，日期；
  2. 日期和字符只能在单引号中出现；
  3. 每当返回一行时，字符串被输出一次。

重复行

默认情况下，查询会返回全部行，包括重复行。

1
2
SELECT department_id
FROM employees;

使用DISTINCT关键字可以删除重复行。
1
2
SELECT DISTINCT department_id
FROM employees;

下列语法错误
1
2
SELECT last_name, DISTINCT department_id
FROM employees;

SQL*Plus命令

  1. 一种环境
  2. Oracle的特性之一
  3. 关键字可以缩写
  4. 命令不能改变数据库中的数据的值
  5. 集中运行

描述表的结构

1
DESC[RIBE] employees;

第一节测试

  1. SQL*PLUS命令可以控制数据库吗？ 不可以！
  2. 下面的语句是否可以执行成功： 可以
    1
    2
    SELECT last_name, job_id, salary AS sal
    FROM employees;
  3. 下面的语句是否可以执行成功： 可以
    1
    SELECT * FROM employees;
  4. 找出下面语句中的错误：标点符号需要英文格式；双引号也需要英文格式
    1
    SELECT employee_id, last_name，salary*12 “ANNUAL SALARY”;
  5. 显示表departments的结构，并查询其中的全部数据
    1
    2
    DESC employees;
    SELECT * FROM departments;
  6. 显示出employees中的全部job_id（不能重复）
    1
    SELECT DISTINCT job_id FROM employees;
  7. 显示出表employees的全部列，各个列之间用都逗号连接，列头显示成OUT_PUT
    1
    2
    SELECT employee_id || ',' ||last_name|| ',' || salary "OUT_PUT"
    FROM employees;

2.过滤和排序数据

学习目标：

  1. 在查询中过滤行；
  2. 在查询中对行进行排序。
1
2
3
SELECT employee_id, last_name
FROM employees
WHERE employee_id > 200;
1
2
3
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 5000;
1
2
3
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 90;
1
2
3
SELECT employee_id, last_name, salary
FROM employees
WHERE last_name = 'higgins';

字符和日期

  1. 字符和日期要包含在单引号中；
  2. 字符大小写敏感，日期格式敏感；

比较运算

操作符 含义
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
<> 不等于
!= 不等于 
1
2
3
4
SELECT last_name, hire_date, salary
FROM empolyees
WHERE salary >= 4000
AND salary < 7000

其它比较运算符

操作符 含义
BETWEEN… AND … 在两个值之间(包含边界)
IN(set) 等于值列表中的一个
LIKE 模糊查询
IS NULL 为空值 
1
2
3
SELECT last_name, hire_date, salary
FROM employees
WHERE salary BETWEEN 4000 AND 7000;
1
2
3
SELECT last_name, hire_date, salary, department_id
FROM employees
WHERE department_id IN (90, 80, 70)
1
2
3
SELECT last_name, department_id, salary
FROM employees
WHERE last_name LIKE '%a%';
1
2
3
SELECT last_name, department_id, salary
FROM employees
WHERE last_name LIKE '_a%';
1
2
3
SELECT last_name, department_id, salary
FROM employees
WHERE last_name LIKE '%\_%' ESCAPE '\';

逻辑运算

操作符 含义
AND 逻辑与
OR 逻辑或
NOT 逻辑非 
1
2
3
SELECT last_name, hire_date, salary
FROM employees
WHERE department_id = 80 AND salary <= 5000;

优先级
| 优先级 | |
| :——- | :————————————-: |
| 1 | 算术运算符 |
| 2 | 连接符 |
| 3 | 比较符 |
| 4 | IS[NOT] NULL, LIKE, [NOT]IN |
| 5 | NOT[BETWEEN] |
| 6 | NOT |
| 7 | AND |
| 8 | OR |
括号可以改变优先级顺序

ORDER BY子句

使用ORDER BY子句排序：
-ASC:升序
-DESC：降序
ORDER BY子句在SELECT语句的结尾。默认为升序排序

1
2
3
SELECT last_name, department_id, salary
FROM employees
ORDER BY salary DESC;
1
2
3
SELECT last_name, department_id, salary
FROM employees
ORDER BY salary ASC, last_name DESC;

第二节总结

  1. 使用WHERE子句过滤数据
    1. 使用比较运算符
    2. 使用BETWEEN AND, IN, LIKE和NULL运算
    3. 使用逻辑运算符AND，OR和NOT
  2. 使用ORDER BY子句进行排序

第二节测试

  1. WHERE子句紧随FROM子句 正确
  2. 查询 last_name 为 ‘King’的员工信息
    1
    2
    3
    SELECT first_name, last_name
    FROM employees
    WHERE last_name = king;
    错误，King上没有加单引号；且单引号中的值区分大小写。
    1
    2
    3
    SELECT first_name, last_name
    FROM employees
    WHERE last_name = 'King';

  3. 查询1998-4-24来公司的员工有哪些？

    1
    2
    3
    SELECT last_name, hire_date
    FROM employees
    WHERE TO_CHAR(hire_date, 'yyyy-mm-dd') = '1998-04-24';

  4. 查询工资在5000—-10000之间的员工信息

    1
    2
    3
    4
    SELECT last_name, salary
    FROM employees
    -- WHERE salary >= 5000 AND salary <= 10000;
    WHERE salary BETWEEN 5000 AND 10000;

  5. 查询工资等于6000，7000，8000，9000，10000的员工信息

    1
    2
    3
    4
    SELECT *
    FROM employees
    -- WHERE salary = 6000 OR salary = 7000 OR salary = 8000 OR salary = 9000 OR salary = 10000;
    WHERE salary IN(6000, 7000, 8000, 9000, 10000);

  6. 查询LAST_NAME 中有’o’字符的所有员工信息

    1
    2
    3
    SELECT *
    FROM employees
    WHERE last_name LIKE '%o%';

  7. 查询LAST_NAME 中第二个字符是’o’字符的所有员工信息

    1
    2
    3
    SELECT *
    FROM employees
    WHERE last_name LIKE '_o%';

  8. 查询LAST_NAME中含有’_’字符的所有员工信息

    1
    2
    3
    SELECT *
    FROM employees
    WHERE last_name LIKE '%\_%' ESCAPE '\';

  9. 查询COMMISSION_PCT字段为空的所有员工信息

    1
    2
    3
    SELECT last_name, commission_pct
    FROM employees
    WHERE commission_pct IS NULL;

3.单行函数

学习目标：

  1. SQL中不同类型的函数
  2. 在SELECT语句中使用字符，数字，日期和转换函数
  3. 使用条件表达式

单行函数的分类
字符、数值、日期、转换、通用

字符函数

大小写控制函数

LOWER、UPPER、INITCAP

函数 结果
LOWER(‘SQL Course’) sql course
UPPER(‘SQL Course’) SQL COURSE
INITCAP Sql Course 
1
2
3
SELECT *
FROM employees
WHERE LOWER(last_name) = 'king';

字符控制函数

函数 结果
CONCAT(‘Hello’, ‘World’) HelloWorld
SUBSTR(‘HelloWorld’,2,4) ello
LENGTH(‘HelloWorld’) 10
INSTR(‘HelloWorld’, ‘W’) 6
LPAD(salary,10,’*’) *24000
RPAD(salary,10,’*’) 24000***
TRIM(‘H’ FROM ‘HelloWorld’) elloWorld (去除首尾)
REPLACE(‘abcd’,’b’,’m’) amcd (替换所有)

数字函数

  1. ROUND：四舍五入；ROUND(45.926, 2) -> 45.93
  2. TRUNC: 截断；TRUNC(45.926, 2) -> 45.92
  3. MOD: 求余； MOD(1600, 300) -> 100

ROUND(435.45,2) -> 435.45; ROUND(435.45) -> 435; ROUND(435.45, -2) -> 400

日期函数

  1. Oracle中的日期型数据实际含有两个值；日期和时间

函数SYSDATE返回：日期、时间

日期的数学运算

  1. 在日期上加上或减去一个数字结果仍为日期
  2. 两个日期相减返回日期之间相差的天数。日期不允许做加法运算，无意义。
  3. 可以用数字除24来向日期中加上或减去天数
1
2
SELECT SYSDATE, SYSDATE+1, SYSDATE-3
FROM dual;
1
2
SELECT employee_id, last_name, TRUNC(SYSDATE - hire_date) worked_days
FROM employees;

日期函数
| 函数 | 描述 |
| :——————- | :——————————————: |
| MONTHS_BETWEEN | 两个日期相差的月数 |
| ADD_MONTHS | 向指定日期中加上若干月数 |
| NEXT_DAY | 指定日期的下一个星期对应的日期 |
| LAST_DAY | 本月的最后一天 |
| ROUND | 日期四舍五入 |
| TRUNC | 日期截断 |

1
2
SELECT employee_id, last_name, MONTHS_BETWEEN(SYSDATE, hire_date)
FROM employees;
1
2
SELECT ADD_MONTHS(SYSDATE, 2), ADD_MONTHS(SYSDATE, -3), NEXT_DAY(SYSDATE, '星期日')
FROM dual;
1
2
3
4
--来公司的员工中，hire_date是每个月倒数第二天来公司的有哪些？
SELECT last_name, hire_date
FROM employees
where hire_date = LAST_DAY(hire_date)-1
1
2
SELECT ROUND(SYSDATE, 'month'), ROUND(SYSDATE, 'mm'), TRUNC(SYSDATE, 'hh')
FROM dual;

转换函数

  1. 隐性
  2. 显性

隐式数据类型转换：Oracle自动完成转换
| 源数据类型 | 目标数据类型 |
| :———————- | :—————: |
| VARCHAR2 or CHAR | NUMBER |
| VARCHAR2 or CHAR | DATE |
| NUMBER | VARCHAR2 |
| DATE | VARCHAR2 |

DATE <-> VARCHAR2 <-> NUMBER

1
2
SELECT '12' + 2 FROM dual;
--14
1
SELECT SYSDATE + '2' FROM dual;

显示数据类型转换

  1. CHARACTER ———TO_DATE———> DATE
  2. CHARACTER ———TO_NUMBER———> NUMBER
  3. DATE ——— TO_CHAR ———> CHARCTER
  4. NUMBER ———TO_CHAR ———> CHARCTER
1
2
3
SELECT employee_id, hire_date
FROM employees
WHERE TO_CHAR(hire_date,'yyyy-mm-dd') = '1996-02-03';
1
2
3
SELECT employee_id, hire_date
FROM employees
WHERE TO_CHAR(hire_date,'yyyy"年"mm"月"dd"日"') = '1996年02月03日';
1
2
3
SELECT employee_id, hire_date
FROM employees
WHERE hire_date = TO_DATE('1996-02-03','yyyy-mm-dd');

TO_CHAR函数对数字的转换:
| 标识 | 说明 |
| :—- | :—————: |
| 9 | 数字 |
| 0 | 零 |
| $ | 美元符 |
| L | 本地货币符号 |
| . | 小数点 |
| , | 千位符 |

1
2
SELECT TO_CHAR(1234567.89, '999,999,999.99') FROM dual;
-- 1,234,567.89 

1
2
SELECT TO_CHAR(1234567.89, '000,000,999.99') FROM dual;
-- 001,234,567.89 

1
2
SELECT TO_CHAR(1234567.89, '$000,000,999.99') FROM dual;
-- $001,234,567.89 

1
2
SELECT TO_CHAR(1234567.89, 'L000,000,999.99') FROM dual;
-- ￥001,234,567.89 

1
2
SELECT TO_NUMBER('￥001,234,567.89', 'L000,000,999.99') FROM dual;
-- ￥1234567.89

通用函数

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

  1. NVL(expr1, expr2)
  2. NVL2(expr1, expr2, expr3)；expr1不为NULL，返回expr2,若为NULL，返回expr3
  3. NULLIF(expr1, expr2)；相等返回NULL，不相等返回expr1
  4. COALESCE(expr1,expr2,…,exprn)；COALESCE与NVL相比的优点在于COALESCE可以同时处理交替的多个值，如果第一个表达式为空，则返回下一个表达式，对其他的参数进行COALESCE

NVL函数：将空值转换成一个已知的值：

  1. 可以使用的数据类型有日期、字符、数字。
  2. 函数的一般形式：
    1. -NVL(commission_pct, 0)
    2. -NVL(hire_date, ‘01-JAN-97’)
    3. -NVL(job_id, ‘NO Job Yet’)
1
2
3
4
5
-- 求公司员工的年薪(含commission_pct)
SELECT employee_id, 
last_name, 
salary * 12 *(1 + NVL(commission_pct, 0))
FROM employess;
1
2
3
-- 输出last_name,department_id，当department_id为NULL时，显示'没有部门'。
SELECT last_name, NVL(TO_CHAR(department_id, '999999'), '没有部门')
FROM employees;
1
2
3
-- 查询员工的奖金率，若为空，返回0.01；若不为空，则返回实际的奖金率+0.015
SELECT last_name, commission_pct, NVL2(commission_pct, commission_pct+0,015, 0,01)
FROM employees;

条件表达式

  1. 在SQL语句中使用IF-THEN-ELSE逻辑
  2. 使用两种方法：
    1. -CASE表达式
    2. -DECODE函数

CASE表达式：在需要使用IF-THEN-ELSE逻辑时：

1
2
3
4
5
CASE expr WHEN comparison_expr1 THEN return_expr1
   [WHEN comparison_expr2 THEN return_expr2
   WHEN comparison_exprn THEN return_exprn
   ELSE else_expr]
END

1
2
3
4
5
6
7
8
9
10
11
12
-- 查询部门号为10，20，30 的员工信息, 若部门号为10，则打印其工资的1.1倍；
-- 若20部门则打印其工资的1.2倍；
-- 若30部门打印其工资的1.3倍
SELECT employee_id, last_name, department_id, 
CASE department_id 
   WHEN 10 THEN salary * 1.1
   WHEN 20 THEN salary * 1.2
   WHEN 30 THEN salary * 1.3
   ELSE salary
END new_sal
FROM employees
WHERE department_id IN (10, 20, 30);

DECODE函数

1
2
3
4
5
6
SELECT last_name, job_id, salary
   DECODE(job_id, 'IT_PROG', 1.10 * salary,
                  'ST_CLERK', 1.15 * salary,
                  'SA_REP', 1.20 * salary,
                            salary) REVISED_SALARY
FROM employees;

1
2
3
4
5
6
7
SELECT employee_id, last_name,
      DECODE(department_id, 10, 1.1 * salary,
                            20, 1.2 * salary,
                            30, 1.3 * salary
                            salary) new_sal
FROM employees
WHERE department_id IN (10, 20, 30);

4.多表查询

学习目标：

  1. 使用等值或不等值连接在SELECT语句中查询多个表中的数据
  2. 使用自连接
  3. 使用外连接查询不满足连接条件的数据

笛卡尔积

笛卡尔积会在下面条件下产生：

  1. 省略连接条件
  2. 连接条件无效
  3. 所有表中的所有行互相连接

为了避免笛卡尔积，可以在WHERE加入有效的连接条件。

1
2
3
SELECT e.employee_id, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;
1
2
3
SELECT e.employee_id, d.department_id, d.department_name l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

连接

内连接

等值连接
连接N个表，至少需要N-1个连接条件。例如：连接三个表，至少需要两个连接条件。

非等值连接

1
2
3
SELECT employee_id, last_name, grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal

外连接

  1. 内连接：合并具有同一列的两个以上的表的行，结果集不包含一个表与另一个表不匹配的行
  2. 外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行，这种连接称为左(或右)外连接。没有匹配的行时，结果表中相应的列为空(NULL)。外连接的WHERE子句条件类似于内部链接，但连接条件中没有匹配行的表的列后面要加外连接运算符，即用圆括号括起来的加号(+)。
1
2
3
4
5
6
7
8
9
-- 左外连接
SELECT e.employee_id, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

-- 右外连接
SELECT e.employee_id, d.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
1
2
3
4
5
6
7
8
9
-- 左外连接
SELECT e.employee_id, d.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

-- 右外连接
SELECT e.employee_id, d.department_id, d.department_name
FROM employees RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;

自然连接

自然连接按照所有的相同字段名进行筛选

1
2
SELECT employee_id, department_id, department_name
FROM employees NATURAL JOIN departments

全连接

1
2
3
SELECT e.employee_id, d.department_id, d.department_name
FROM employees FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

自连接

1
2
3
4
-- 查询公司中员工'Chen'的manager的信息
SELECT emp.last_name, mana.last_name, mana.salary, mana.email
FROM employees emp, employees mana
WHERE emp.manager_id = mana.employee_id AND LOWER(emp.last_name) = 'chen'

5.聚合函数

什么是聚合函数
聚合函数作用于一组数据，并对一组数据返回一个值。

学习目标：

  1. 了解聚合函数
  2. 描述聚合函数的用途
  3. 使用GROUP BY子句对数据分组
  4. 使用HAVING子句过滤分组结果集

聚合函数类型：

  1. AVG
  2. COUNT：返回表中记录总数
  3. MAX
  4. MIN
  5. STDDEV：求标准差,可以做聚集和分析函数
  6. SUM

AVG、SUM函数

AVG和SUM可以用于数值型数据

1
2
SELECT AVG(salary), SUM(salary)
FROM employees;

MIN、MAX函数

MAX和MIN可以用于任意数据类型

1
2
SELECT MAX(last_name), MIN(last_name), MAX(hire_date), MIN(hire_date)
FROM employees;

COUNT函数

COUNT()适用于*任意数据类型

1
2
SELECT COUNT(1), COUNT(2), COUNT(*)
FROM employees;

COUNT：计数函数
COUNT(expr) 返回expr不为空的记录总数

1
2
SELECT COUNT(commission_pct)
FROM employees;
1
SELECT AVG(commission_pct), SUM(commission_pct) / COUNT(commission_pct), SUM(commission_pct) / COUNT(NVL(commission_pct, 1));

COUNT(DISTINCT expr)：返回expr非空且不重复的记录总数。

聚合数据

GROUP BY 字句语法
可以使用GROUP BY子句将表中的数据分成若干组

1
2
3
4
5
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

在SELECT列表中所有未包含在聚合函数中的列都应该包含在GROUP BY 子句中。
包含在GROUP BY子句中的列不必含在SELECT列表中。

1
2
3
4
5
-- 求出employees表中各部门的平均工资

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

多层分组

1
2
3
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id

非法使用聚合函数

  1. 所有包含于SELECT列表中，而未包含于聚合函数中的列都必须包含于GRPOU BY子句中。
  2. 不能在WHERE子句中使用聚合函数。

可以在HAVING子句中使用聚合函数。

1
2
3
4
5
6
-- 求出各部门中平均工资大于6000的部门，以及其平均工资

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 6000;

嵌套聚合函数

1
2
3
4
5
-- 显示各部门平均工资的最大值

SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

6.子查询

学习目标：

  1. 描述子查询可以解决的问题
  2. 定义子查询
  3. 列出子查询的类型
  4. 书写单行子查询和多行子查询
1
2
3
4
5
6
7
8
9
-- 谁的工资比Abel高

SELECT last_name
FROM employees
WHERE salary > (
   SELECT salary
   FROM employess
   WHERE last_name = 'Abel'
)

子查询语法

1
2
3
4
5
6
7
SELECT select_list
FROM table
WHERE expr operator
   (
      SELECT select_list
      FROM table
   )
  1. 子查询(内查询)在主查询之前一次执行完成。
  2. 子查询的结果被主查询(外查询)使用。

注意事项

  1. 子查询要包含在括号内
  2. 将子查询放在比较条件的右侧
  3. 单行操作符对应单行子查询，多行操作符对应多行子查询

单行子查询

  1. 只返回一行
  2. 使用单行比较操作符(=,>,>=,<,<=,<>)
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 返回job_id与141员工相同，salary比143员工多的员工姓名，job_id和工资

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (
   SELECT job_id
   FROM employees
   WHERE employee_id = 141
) AND salary > (
   SELECT salary
   FROM employees
   WHERE employees_id = 143
)
1
2
3
4
5
6
7
8
-- 返回公司工资最少的员工的last_name, job_id和salary

SELECT last_name, job_id, salary
FROM employees
WHERE salary = (
   SELECT MIN(salary)
   FROM employees
)
1
2
3
4
5
6
7
8
9
10
-- 查询最低工资大于50号部门最低工资的部门id和其最低工资

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
   SELECT MIN(salary)
   FROM employees
   WHERE department_id = 50
)

非法使用子查询

  1. 多行子查询使用单行比较符
  2. 子查询中的空值问题(子查询不返回任何行)

多行子查询

  1. 返回多行
  2. 使用多行比较操作符(IN(等于列表中的任意一个),ANY(和子查询返回的某一个值比较),ALL(和子查询返回的所有值比较))
1
2
3
4
5
6
7
8
9
10
-- 返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号，姓名，job_id以及salary

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id <> 'IT_PROG' AND
salary < ANY (
   SELECT salary
   FROM employees
   WHERE job_id = 'IT_PROG'
)
1
2
3
4
5
6
7
8
9
-- 返回其它部门中比job_id为‘IT_PROG’部门所有工资低的员工的员工号，姓名，job_id以及salary
SELECT employee_id, last_name,salary
FROM employees
WHERE job_id <> 'IT_PROG' AND
salary < ALL (
   SELECT salary
   FROM employees
   WHERE job_id = 'IT_PROG'
)

7.创建和管理表

学习目标：

  1. 描述主要的数据库对象
  2. 创建表
  3. 描述各种数据类型
  4. 修改表的定义
  5. 删除、重命名和清空表

常见的数据库对象

对象 描述
表 基本的数据存储集合，由行和列组成
视图 从表中抽出的逻辑上相关的数据集合
序列 提供有规律的数值
索引 提高查询的效率
同义词 给对象起别名

Oracle 数据库中的表
用户定义的表：

  1. 用户自己创建并维护的一组表
  2. 包含了用户所需的信息

SELECT FROM user_tables | 查看用户创建的表
SELECT DISTINCT object_type FROM user_objects | 查看用户定义的各种数据库对象
SELECT FROM user_catalog | 查看用户定义的表，视图，同义词和序列

数据字典：

  1. 由Oracle Server 自动创建的一组表
  2. 包含数据库信息

命名规则

表明和列明：

  1. 必须以字母开头
  2. 必须在1-30个字符之间
  3. 必须只能包含A-Z，a-z，0-9，_，$, 和#
  4. 必须不能和用户定义的其它对象重名
  5. 必须不能是Oracle的保留字

创建表

必须具备：

  1. CREATE TABLE权限
  2. 存储空间

必须指定：

  1. 表名
  2. 列明，数据类型，尺寸
1
2
CREATE TABLE [schema.]table
      (column datatype [DEFAULT expr] [, ...]);

第一种方式：重新创建

1
2
3
4
5
6
CREATE TABLE emp1(
   id number(10),
   name VARCHAR2(20),
   SALARY number(10,2),
   hire_date DATE
)

第二种方式：依托于现有表

1
2
3
4
SELECT TABLE emp2 
AS
SELECT employee_id id, last_name name, hire_date, salary
FROM employees

1
2
3
4
5
SELECT TABLE emp3
AS
SELECT employee_id id, last_name name, hire_date, salary
FROM employees
WHERE 1 = 2

数据类型
| 数据类型 | 描述 |
| :—————- | :—————————————————: |
| VARCHAR2 | 可变长字符数据 |
| CHAR(size) | 定长字符数据 |
| NUMBER(p, s) | 可变长数值数据 |
| DATE | 日期型数据 |
| LONG | 可变长字符数据，最大可达到2G |
| CLOB | 字符数据，最大可达4G |
| RAW | 原始的二进制数据 |
| BLOB | 二进制数据，最大可达4G |
| BFILE | 存储外部文件的二进制数据，最大可达4G |
| ROWID | 行地址 |

修改表

使用ALTER TABLE语句可以：

  1. 追加新的列
  2. 修改现有的列
  3. 为新追加的列定义默认值
  4. 删除一个列
  5. 重命名表的一个列名

追加新的列

1
2
ALTER TABLE table
ADD (column datatype [DEFAULT expr] [, column datatype]...)

1
2
ALTER TABLE emp1
ADD (email VARCHAR2(20));

修改现有的列

1
2
ALTER TABLE emp1
MODIFY (column datatype [DEFAULT expr] [, column datatype]...)

1
2
ALTER TABLE emp1
MODIFY (id NUMBER(15));

对默认值的修改只影响以后表的修改
1
2
ALTER table EMP1
MODIFY (salary NUMBER(20,2) DEFAULT 2000)

删除现有列

1
2
ALTER TABLE table
DROP COLUMN column_name;

重命名列名

1
2
ALTER TABLE table_name 
RENAME COLUMN old_column_name TO new_column_name

删除表

  1. 数据和结构都被删除
  2. 所有正在运行的相关事务被提交
  3. 所有相关索引被删除
  4. DROP TABLE 语句不能回滚
1
DROP TABLE emp5;

清空表

  1. TRUNCATE TABLE语句：
    1. 删除表中的所有数据
    2. 释放表的存储空间
      1
      TRUNCATE TABLE detail_dept;
  2. TRUNCATE语句不能回滚
  3. 可以使用DELETE 语句删除数据，可以回滚

改表名

执行RENAME语句改变表，视图，序列，或同义词的名称

1
RENAME emp2 TO employees2;

必须是对象的拥有者

8.数据处理

学习目标：

  1. 使用DML语句
  2. 向表中插入数据
  3. 更新表中数据
  4. 从表中删除数据
  5. 控制事务

数据操纵语言

DML数据操纵语言可以在下列条件下执行：

  1. 向表中插入数据
  2. 修改现存数据
  3. 删除现存数据

事务是由完成若干项工作的DML语句组成的。

插入数据

使用INSERT语句语法
使用INSERT语句向表中插入数据：

1
2
INSERT INTO table [(column [, column...])]
VALUES (value1 [,value2...]);

该语法一次只能向表中插入一条数据。

1
2
INSERT INTO emp1
VALUE(1001, 'AA', SYSDATE, 10000);
1
2
INSERT INTO emp1
VALUES(1002, 'BB', TO_DATE('1998-10-25', 'yyyy-mm-dd'), 20000);
1
2
INSERT INTO emp1
VALUES(1003, 'CC', TO_DATE('1999-12-21','yyyy-mm-dd'), null);
1
2
INSERT INTO emp1 (last_name, employee_id, hire_date)
VALUES('DD', 1004, TO_CHAR('1679-12-22', 'yyyy-mm-dd'))

从其它表中拷贝数据

  1. 在INSERT语句中加入子查询
  2. 不必书写VALUES子句
  3. 子查询中的值列表应与INSERT子句中的列明对应
1
2
3
4
5
INSERT INTO emp2 
SELECT *
FROM employees
WHERE department_id = 90

1
2
3
4
INSERT INTO sales_reps(id, name, salary, commission_pot)
SELECT employee_id, last_name, salary, commission_pot
FROM employees
WHERE job_id LIKE '%REP%';

创建脚本

  1. 在SQL语句中使用&变量指定列值
  2. &变量放在VALUES子句中
1
2
3
INSERT INTO departments
(department_id, department_name, location_id)
VALUES (&department_id, '&department_name', &location);

更新数据

  1. 使用UPDATE语句更新数据
    1
    2
    3
    UPDATE table_name
    SET column =  value1 [, column2 = value2, ...]
    [WHERE condition];
  2. 可以一次更新多条数据。
  3. 使用WHERE子句指定需要更新的数据。
  4. 如果省略WHERE子句，则表中的所有数据都将被更新。
1
2
3
4
5
6
7
8
9
-- 更新114号员工的工作和工资使其与205号员工相同。

UPDATE employees
SET (job_id, salary) = (
   SELECT job_id, salary
   FROM employees
   WHERE employee_id = 205
   ) 
WHERE employee_id = 114;

注意更新中的数据完整性错误。

删除数据

  1. 使用DELETE语句从表中删除数据
    1
    2
    DELETE FROM table_name
    [WHERE condition];
  2. 使用WHERE子句删除指定的记录
  3. 如果省略WHERE子句，则表中的全部数据将被删除
1
2
3
4
5
6
7
8
-- 从emp1表中删除dept1部门名称中含有Public字符的部门id

DELETE FROM emp1
WHERE department_id IN (
   SELECT department_id
   FROM departments
   WHERE department_name LIKE '%Public%'
)

注意删除中的数据完整性错误。

数据库事务

事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。

数据库事务由以下的部分组成：

  1. 一个或多个的DML语句
  2. 一个DDL语句
  3. 一个DCL语句

数据库事务：

  1. 以第一个DML语句的执行作为开始
  2. 以下面的其中之一作为结束：
    1. COMMIT或ROLLBACK语句
    2. DDL语句
    3. 用户会话正常结束
    4. 系统异常终止

回滚到保存点

  1. 使用SAVEPOINT语句在当前事务中创建保存点
  2. 使用ROLLBACK TO SAVEPOINT语句回滚到创建的保存点
1
2
3
4
5
6
UPDATE ...
SAVEPOINT update_done;

INSERT ...

ROLLBACK to update_done;

提交或回滚前的数据状态

  1. 改变前的数据状态是可以恢复的
  2. 执行DML操作的用户可以通过SELECT语句查询之前的修正
  3. 其它用户不能看到当前用户所做的改变，直到当前用户结束事务
  4. DML语句所涉及到的行被锁定，其他用户不能操作

提交后的数据状态

  1. 数据改变已经被保存到数据库中
  2. 改变前的数据已经丢失
  3. 所有用户可以看到结果
  4. 锁被释放，其他用户可以操作涉及到的数据
  5. 所有保存点被释放

数据回滚后的状态
使用ROLLBACK语句可以使数据变化失效：

  1. 数据改变被取消
  2. 修改前的数据状态被恢复
  3. 锁被释放

9.约束

学习目标：

  1. 描述约束
  2. 创建和维护约束

注意事项

  1. 如果不指定约束名，Oracle server自动按照SYS_Cn的格式指定约束名
  2. 创建和修改约束：
    1. 建表的同时
    2. 建表之后
  3. 可以在表级或列级定义约束
  4. 可以通过数据字典视图查看约束

表级约束和列级约束

  1. 作用范围：
    1. 列级约束只能作用在一个列上
    2. 表级约束可以作用在多个列上
  2. 定义方式：列约束必须跟在列的定义后面，表约束不与列一起，而是单独定义
  3. 非空(NOT NULL)约束只能定义在列上

NOT NULL约束

1
2
3
4
5
CREATE TABLE emp2(
   id NUMBER(10) CONSTRAINT emp2_id_nn NOT NULL
   name VARCHAR2(20) NOT NULL,
   salary NUMBER(10,2)
)

UNIQUE约束

唯一约束，要求列中插入的值是唯一的，可以为NULL。

1
2
3
4
5
6
7
8
CREATE TABLE emp3(
   id NUMBER(10) CONSTRAINT emp3_id_uk UNIQUE,
   name VARCHAR2(20) CONSTRAINT emp3_name_nn NOT NULL,
   salary NUMBER(10,2),
   email VARCHAR2(20),  
-- 表级约束
CONSTRAINT emp3_email_uk UNIQUE(email)
)

主键约束

1
2
3
4
5
6
7
8
CREATE TABLE emp4(
   id NUMBER(10) CONSTRAINT emp4_id_pk PRIMARY KEY,
   name VARCHAR2(20) CONSTRAINT emp4_name_nn NOT NULL,
   salary NUMBER(10,2),
   email VARCHAR2(20),  
-- 表级约束
CONSTRAINT emp4_email_uk UNIQUE(email)
)
1
2
3
4
5
6
7
8
9
CREATE TABLE emp4(
   id NUMBER(10) ,
   name VARCHAR2(20) CONSTRAINT emp4_name_nn NOT NULL,
   salary NUMBER(10,2),
   email VARCHAR2(20),  
-- 表级约束
CONSTRAINT emp4_email_uk UNIQUE(email),
CONSTRAINT emp4_id_pk PRIMARY KEY(id)
)

主键的特点：

  1. 非空
  2. 唯一

外键约束

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE emp5(
   id NUMBER(10) ,
   name VARCHAR2(20) CONSTRAINT emp5_name_nn NOT NULL,
   salary NUMBER(10,2),
   email VARCHAR2(20),
   department_id NUMBER(10)  
-- 表级约束
CONSTRAINT emp5_email_uk UNIQUE(email),
CONSTRAINT emp5_id_pk PRIMARY KEY(id),
CONSTRAINT emp5_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id)
)

—FOREIGN KEY：在表级指定子表中的列
—REFERENCES：标示在父表中的列
—ON DELETE CASCADE (级联删除):当父表中的列被删除时，子表中相对应的列也被删除
—ON DELETE SET NULL (级联置空)：子表中相应的列置空

1
2
3
4
5
6
7
8
9
10
CREATE TABLE emp5(
   id NUMBER(10) ,
   name VARCHAR2(20) CONSTRAINT emp5_name_nn NOT NULL,
   salary NUMBER(10,2),
   email VARCHAR2(20),
   department_id NUMBER(10)  
-- 表级约束
CONSTRAINT emp5_email_uk UNIQUE(email),
CONSTRAINT emp5_id_pk PRIMARY KEY(id),
CONSTRAINT emp5_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ON DELETE CASCADE

检查约束

定义每一行必须满足的条件

1
2
3
4
5
6
7
8
9
10
CREATE TABLE emp5(
   id NUMBER(10) ,
   name VARCHAR2(20) CONSTRAINT emp5_name_nn NOT NULL,
   salary NUMBER(10,2) CONSTRAINT emp5_salary CHECK(salary > 1500),
   email VARCHAR2(20),
   department_id NUMBER(10)  
-- 表级约束
CONSTRAINT emp5_email_uk UNIQUE(email),
CONSTRAINT emp5_id_pk PRIMARY KEY(id),
CONSTRAINT emp5_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id) ON DELETE CASCADE

添加约束的语法

使用ALTER TABLE语句：

  1. 添加或删除约束，但是不能修改约束
  2. 有效化或无效化约束
  3. 添加NOT NULL约束要使用MODIFY语句

添加或删除约束

1
2
ALTER TABLE emp5
MODIFY(salary NUMBER(10,2) NOT NULL)

删除约束

1
2
ALTER TABLE emp5
DROP CONSTRAINT emp5_name_nn

添加约束

1
2
ALTER TABLE emp5
ADD CONSTRAINT emp5_name_uk_unique(name)

无效化约束

在ALTER TABLE语句中使用DISABLE子句将约束无效化

1
2
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk;

有效化约束

在ALTER TABLE语句中使用ENABLE子句将约束有效化

1
2
ALTER ABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;

查询约束

查询数据字典视图 USER_CONSTRAINTS

1
2
3
SELECT constraint_name, constraint_type, search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

查询定义约束的列

查询数据字典视图USER_CONS_COLUMNS

1
2
3
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE table_name = 'EMPLOYEES';

10.视图

学习目标：

  1. 描述视图
  2. 创建和修改视图的定义，删除视图
  3. 从视图中查询数据
  4. 通过视图插入，修改和删除数据
  5. 使用“Top-N”分析

为什么使用视图

  1. 控制数据访问
  2. 简化查询
  3. 避免重复访问相同的数据

创建视图

在CREATE VIEW 语句中嵌入子查询

1
2
3
4
5
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

1
2
3
4
5
CREATE VIEW empview
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
1
2
3
UPDATE empview
SET salary = 20000
WHERE employee_id = 179;
1
2
3
SELECT employee_id, salary
FROM employees
WHERE employee_id = 179;

子查询可以是复杂的SELECT语句

1
2
3
4
5
CREATE VIEW empview2
AS
SELECT employee_id id, last_name name, salary, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id

查询视图

1
2
SELECT *
FROM salvu50;

修改视图

使用CREATE OR REPLACE VIEW 子句修改视图

1
2
3
4
5
6
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name || ` `|| last_name, salary, department_id
FROM employees
WHERE department_id = 80;

CREATE VIEW 子句中各列的别名应和子查询中各列相对应

删除视图

1
DROP VIEW empview3;

屏蔽DML操作

  1. 可以使用 WITH READ ONLY 选项屏蔽对视图的DML操作
  2. 任何DML操作都会返回一个Oracle server错误
1
2
3
4
5
6
CREATE VIEW empview2
AS
SELECT employee_id id, last_name name, salary, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
WITH READ ONLY;

简单视图和复杂视图
| 特性 | 简单视图 | 复杂试图 |
| :———- | :———: | ————-: |
| 表的数量 | 一个 | 一个或多个 |
| 函数 | 没有 | 有 |
| 分组 | 没有 | 有 |
| DML操作 | 可以 | 有时可以 |

复杂试图

1
2
3
4
5
6
CREATE OR REPLACE VIEW empview3
AS
SELECT department_name dept_name, AVG(salary) avg_sal
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name

视图中使用DML的规定

  1. 可以在简单视图中执行DML操作
  2. 当视图定义包含以下元素之一时不能使用DELETE：
    1. 聚合函数
    2. GROUP BY 子句
    3. DISTINCT 关键字
    4. ROWNUM 伪列
  3. 当视图定义包含以下元素之一时不能使用UPDATE：
    1. 聚合函数
    2. GROUP BY 子句
    3. DISTINCT 关键字
    4. ROWNUM 伪列
    5. 列的定义为表达式
  4. 当视图定义包含以下元素之一时不能使用INSERT：
    1. 聚合函数
    2. GROUP BY 子句
    3. DISTINCT 关键字
    4. ROWNUM 伪劣
    5. 列的定义为表达式
    6. 表中非空的列在视图定义中未包括

Top-N分析

Top-N分析查询一个列中最大或最小的N个值：

  1. 销售量最高的十种产品是什么
  2. 销售量最差的十种产品是什么

最大和最小的值的集合是Top-N分析所关心的

1
2
3
4
5
6
SELECT rownum, employee_id, last_name, salary
FROM (SELECT employee_id, last_name, salary
FROM employees
ORDER BY salary DESC
)
WHERE rownum <= 10;

注意：对ROWNUM 只能使用 < 或<= ；而用=, >, >= 都将不能返回任何数据。

1
2
3
4
5
6
7
8
9
SELECT rn, employee_id, last_name, salary
FROM (
   SELECT rownum rn, employee_id, last_name, salary
   FROM (SELECT employee_id, last_name, salary
         FROM employees
         ORDER BY salary DESC
   )
)
WHERE rn > 40 AND rn <= 50

11.其它数据库对象

序列

什么是序列

序列：可提供多个用户用来产生唯一数值的数据库对象

  1. 自动提供唯一的数值
  2. 共享对象
  3. 主要用于提供主键值
  4. 将序列值装入内存可以提高访问效率

创建序列

CREATE SEQUENCE语句
定义序列：

1
2
3
4
5
6
7
CREATE SEQUENCE sequence_name
[INCREMENT BY n] -- 每次增长的数值
[START WITH n] -- 从哪个值开始
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] -- 是否需要循环
[{CACHE n | NOCACHE}]; --是否缓存登录
1
2
3
4
5
6
CREATE SEQUENCE empseq
INCREMENT BY 10
START WITH 10
MAXVALUE 100
CYCLE
NOCACHE
1
SELECT empseq.currval FROM dual;
1
SELECT empseq.nextval FROM dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE emp01
AS
SELECT employee_id, lst_name, salary
FROM employees
WHERE 1 = 2;

-- NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
INSERT INTO emp01
VALUES(empseq.nextval, 'AA', 3400);

-- CURRVAL 中存放序列的当前值
INSERT INTO emp01
VALUES(empseq.nextval, 'BB', 3412);

-- NEXTVAL应在CURRVAL之前指定，否则会报CURRVAL尚未在此会话中定义的错误

修改序列

修改序列的增量，最大值，最小值，循环选型，或是否装入内存

1
2
3
ALTER SEQUENCE empseq
INCREMENT BY 1
NOCYCLE

修改序列的注意事项

  1. 必须是序列的拥有者或对序列有ALTER权限
  2. 只有将来的序列值会被改变
  3. 改变序列的初始值只能通过删除序列之后重建序列的方法实现

使用序列

  1. 将序列值装入内存可提高访问效率
  2. 序列在下列情况下出现裂缝：
    1. 回滚
    2. 系统异常
    3. 多个表同时使用同一序列
  3. 如果不将序列的值装入内存(NOCACHE),可使用表USER_SEQUENCES查看序列当前的有效值

查询序列

查询数据字典视图 USER_SEQUENCES 获取序列定义信息

1
2
SELECT sequence_name, min_value, max_value, increment_by, last_number
FROM user_sequences;

如果指定NOCACHE选项，则列LAST_NUMBER显示序列中下一个有效的值

删除序列

使用DROP SEQUENCE语句删除序列
删除之后，序列不能再次被引用

1
DROP SEQUENCE dept_deptid_seq;

索引

索引：

  1. 一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
  2. 索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度
  3. 索引一旦建立，Oracle管理系统会对其进行自动维护，而且Oracle管理系统决定何时使用索引。用户不用在查询语句中指定使用哪个索引
  4. 在删除一个表时，所有基于该表的索引会自动被删除
  5. 通过指针加速Oracle服务区的查询速度
  6. 通过快速定位数据的方法，减少磁盘I/O

创建索引

  1. 自动创建：在定义PRIMARY KEY 或者 UNIQUE约束后系统自动在相应的列上创建唯一索引
  2. 手动创建：用户可以在其它列上创建非唯一的索引，以加速查询

在一个或多个列上创建索引

1
2
CREATE INDEX index_name
ON table_name(column [,column]...);

在表EMPLOYEES的列LAST_NAME上创建索引

1
2
CREATEA INDEX emp_last_name
ON employees(last_name);

什么时候创建索引
以下情况可以创建索引：

  1. 列中数据值分布范围很广
  2. 列经常在WHERE子句或连接条件中出现
  3. 表经常被访问而且数据量很大，访问的数据大概占数据总量的2%~4%

什么时候不要创建索引
下列情况不要创建索引：

  1. 表很小
  2. 列不经常作为连接条件或出现在WHERE子句中
  3. 查询的数据大于2%~4%
  4. 表经常更新

查询索引

可以使用数据字典视图USER_INDEXES 和USER_IND_COLUMNS查看索引的信息

1
2
3
4
SELECT ic.index_name, ic.column_name, ic.column_position_col_pos, ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';

删除索引

  1. 使用DROP INDEX删除索引
    1
    DROP INDEX index_name;
  2. 删除索引UPPER_LAST_NAME_IDX
    1
    DROP INDEX upper_last_name_idx;
  3. 只有索引的拥有者或拥有DROP ANY INDEX 权限的用户才可以删除索引
  4. 删除操作是不可回滚的

同义词-SYNONYM

使用同义词访问相同的对象：

  1. 方便访问其它用户的对象
  2. 缩短对象名字的长度

创建和删除同义词

1
2
CREATE [PUBLIC] SYNONYM synonym_name
FOR object_name;
1
CREATE SYNONYM e FOR employees;
1
SELECT * FROM e;
1
DROP SYNONYM synonym_name;

12.控制用户权限

目标：

  1. 创建用户
  2. 创建角色
  3. 使用GRANT和REVOKE语句赋予和回收权限
  4. 创建数据库联接

权限

  1. 数据库安全性
    1. 系统安全性
    2. 数据安全性
  2. 系统权限：对于数据库的权限
  3. 对象权限：操作数据库对象的权限

系统权限

  1. 超过一百多种有效的权限
  2. 数据库管理员具有高级权限以完成管理任务：
    1. 创建新用户
    2. 删除用户
    3. 删除表
    4. 备份表

创建用户

DBA使用CREATE USER语句创建用户

1
2
CREATE USER user_name
IDENTIFIED BY pass_word;

1
2
CREATE USER wk01
IDENTIFIED BY wkwk;

用户的系统权限

  1. 用户创建之后，DBA会赋予用户一些系统权限
    1
    2
    GRANT privilege [,privilege...]
    TO user [, user| role, PUBLIC...];
  2. 以应用程序开发者为例，一般具有下列系统权限：
    1. CREATE SESSION (创建会话)
    2. CREATE TABLE (创建表)
    3. CREATE SEQUENCE (创建序列)
    4. CREATE VIEW (创建视图)
    5. CREATE PROCEDURE (创建过程)
1
2
GRANT CREATE SESSION
TO wk01;
1
2
GRANT CREATE TABLE
to WK01;

创建用户表空间

用户拥有CREATE TABLE权限之外，还需要分配相应的表空间才可以开辟存储空间用于创建表

1
2
ALTER USER wk01 QUOTA UNLIMITED
ON users;

1
2
ALTER USER wk01 QUOTA 5M
ON users;

修改密码
DBA可以创建用户和修改密码
用户本可以使用ALTER USER语句修改密码

1
2
ALTER USER wk01
IDENTIFIED BY wkok;

创建角色并赋予权限

创建角色

1
CREATE ROLE manager;

为角色赋予权限

1
2
GRANT CREATE SESSION, TABLE, CREATE VIEW
TO manager;

将角色赋予用户

1
GRANT manager TO DEHAAN, KOCHHAR;

对象权限

  1. 不同的对象具有不同的对象权限
  2. 对象的拥有者拥有所有权限
  3. 对象的拥有者可以向外分配权限
1
2
3
4
GRANT object_priv [columns]
ON object
TO [user|role|PUBLIC]
[WITH GRANT OPTION];

分配对象权限
分配表EMPLOYEES的查询权限

1
2
3
GRANT SELECT
ON employees
TO sue, rich;

分配表中各个列的更新权限
1
2
3
GRANT UPDATE
NO scoot.departments
TO wk01;

WITH GRANT OPTION 使用户同样具有分配权限的权利

1
2
3
4
GRANT SELECT, INSERT
ON departments
TO scott
WITH GRANT OPTION;

向数据库中所有用户分配权限
1
2
3
GRANT SELECT
ON alice.departments
TO PUBLIC;

收回对象权限

  1. 使用REVOKE语句收回权限
  2. 使用WITH GRANT OPTION 子句所分配的权限同样被收回
    1
    2
    3
    4
    REVOKE {privilege [, privilege...]|ALL}
    ON object
    FROM {user[, user...] |role|PUBLIC}
    [CASCADE CONSTRAINTS];
    1
    2
    3
    REVOKE SELECT
    ON employees
    FROM wk01;

13.集合运算符

学习目标：

  1. 描述集合操作符
  2. 将多个查询用集合操作符连接组成一个新的查询
    1. UNION/UNION ALL
    2. INTERSECT
    3. MINUS
  3. 排序ORDER BY

UNION 操作符

UNION操作符返回两个查询的结果集的并集

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE employee01
AS
SELECT *
FROM employees
WHERE department_id IN(70, 80);

CREATE TABLE employee02
AS
SELECT *
FROM employees
WHERE department_id IN(80, 90)
1
2
3
4
5
SELECT employee_id, department_id
FROM employees01
UNION
SELECT employee_id, department_id
FROM employees02
1
2
3
4
5
SELECT employee_id, department_id
FROM employees01
UNION ALL
SELECT employee_id, department_id
FROM employees02

INTERSECT 操作符

INTERSECT操作符返回两个结果集的交集

1
2
3
4
5
SELECT employee_id, department_id
FROM employees01
INTERSECT
SELECT employee_id, department_id
FROM employees02

MINUS操作符

MINUS操作符返回两个结果集的差集

1
2
3
4
5
SELECT employee_id, department_id
FROM employees01
MINUS
SELECT employee_id, department_id
FROM employees02

使用集合操作符注意事项

  1. 在SELECT列表中的列名和表达式在数量和数据类型上要相对应
  2. 括号可以改变执行的顺序
  3. ORDER BY 子句：
    1. 只能在语句的最后出现
    2. 可以使用第一个查询中的列名，别名或相对位置
  4. 除UNION ALL之外，系统会自动将重复的记录删除
  5. 系统将第一个查询的列明显示在输出中
  6. 除UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列
1
2
3
4
5
SELECT employee_id, department_id, TO_CHAR(NULL)
FROM employees01
UNION
SELECT TO_NUMBER(NULL), department_id, department_name
FROM employees02

使用相对位置排序举例

1
2
3
4
5
6
7
SELECT 'study at',2
FROM dual
UNION
SELECT 'I WANT TO',1
FROM dual
UNION 'aaa',3
ORDER BY 2 ASC

1
COLUMN a_dummy NOPRINT;

14.高级子查询

学习目标：

  1. 书写多列子查询
  2. 在FROM子句中使用子查询
  3. 在SQL中使用单列子查询
  4. 书写相关子查询
  5. 使用EXISTS和NOT EXISTS操作符
  6. 使用子查询更新和删除数据
  7. 使用WITH子句

子查询

子查询是嵌套在SQL语句中的另一个SELECT语句

1
2
3
4
5
6
7
SELECT ...
FROM ...
WHERE ... (
   SELECT...
   FROM...
   WHERE...
)

  1. 子查询在主查询执行之前执行
  2. 主查询使用子查询的结果
1
2
3
4
5
6
7
8
-- 查询last_name为Chen的manager的信息
SELECT employee_id, last_name
FROM employees
WHERE employee_id = (
   SELECT manager_id
   FROM employees
   WHERE last_name = 'Chen'
)

多列子查询

主查询与子查询返回的多个列进行比较

1
2
3
4
5
6
7
8
9
-- 查询与147号或174号员工的manager_id和department_id相同的其它员工的employee_id，manager_id，department_id

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN (
   SELECT manager_id, department_id
   FROM employees
   WHERE employee_id IN (147, 174)
);

列比较
多列子查询中的比较分为两种：

  1. 成对比较
  2. 不成对比较
1
2
3
4
5
6
7
8
9
-- 返回比本部门平均工资高的员工的last_name, department_id,salary及平均工资
SELECT last_name, department_id, salary, (SELECT AVG(salary) FROM employees e3 WHERE e1.department_id = e3.department_id GROUP BY department_id)
FROM employees e1
WHERE salary > (
   SELECT AVG(salary)
   FROM employees e2
   WHERE e1.department_id = e2.department_id
   GROUP BY department_id
)
1
2
3
4
5
6
7
8
-- 返回比本部门平均工资高的员工的last_name, department_id,salary及平均工资
SELECT last_name, e1.department_id, salary, e2.avg_sal
FROM employees e1, (
   SELECT department_id, AVG(salary) avg_sal
   FROM employees
   GROUP BY department_id
) e2
WHERE e1.department_id = e2.department_id AND e1.salary > e2.avg_sal

单列子查询表达式

  1. 单列子查询表达式是在一行中只返回一列的子查询
  2. Oracle8i只在下列情况下可以使用，例如：
    1. SELECT语句(FROM和WHERE子句)
    2. INSERT语句中的VALUES列表中
  3. Oracle9i中单列子查询表达式可在下列情况下使用：
    1. DECODE和CASE
    2. SELECT中除GROUP BY子句以外的所有子句中
1
2
3
4
5
-- 显示员工的employee_id，last_name和location，其中，若员工department_id与location_id为1800的department_id相同，则location为'Canada'，其余则为'USA'

SELECT employee_id, last_name, 
(CASE department_id WHEN (SELECT department_id FROM departments WHERE location_id = 1800) THEN 'Canada' ELSE 'USA' END) location
FROM employees

在ORDER BY子句中使用单列子查询

1
2
3
4
5
6
7
8
-- 查询员工的employee_id, last_name,要求按照员工的department_name排序

SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
          FROM departments d
          WHERE e.department_id = d.department_id
)

相关子查询

相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询

1
2
3
4
5
6
7
SELECT column1, column2,...
FROM table1 OUTER
WHERE column1 operator
      (SELECT colum1, column2
      FROM table2
      WHERE expr1 = auter.expr2
      );
1
2
3
4
5
6
7
8
9
-- 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id

SELECT last_name, salary, department_id
FROM employees e
WHERE salary > (
   SELECT AVG(salary)
   FROM employees
   WHERE department_id  = e.department_id
)
1
2
3
4
5
6
7
8
9
-- 若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id, last_name和其job_id

SELECT e.employee_id, e.last_name, job_id
FROM employees e
WHERE 2 <= (
   SELECT COUNT(*)
   FROM job_history
   WHERE employee_id = e.employee_id
)

EXISTS操作符

  1. EXISTS操作符检查在子查询中是否存在满足条件的行
  2. 如果在子查询中存在满足条件的行：
    1. 不在子查询中继续查找
    2. 条件返回TRUE
  3. 如果在子查询中不存在满足条件的行
    1. 条件返回FALSE
    2. 继续在子查询中查找
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查询公司管理者的employee_id, last_name, job_id, department_id

SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE e1.employee_id = (
   SELECT manager_id
   FROM employees e2
   WHERE e1.employee_id = e2.manager_id
)

SELECT DISTINCT employee_id, last_name, job_id, department_id
FROM employees e1, employees e2
WHERE e1.employee_id = e2.manager_id
1
2
3
4
5
6
7
8
-- 查询公司管理者的employee_id, last_name, job_id, department_id
SELECT employee_id, last_name, job_id, department_id
FROM employees e1
WHERE EXISTS(
   SELECT 'A'
   FROM employees e2
   WHERE e1.employee_id = e2.manager_id
)

NOT EXISTS操作符

1
2
3
4
5
6
7
8
9
10
-- 查询departments表中，不存在employees表中的部门的department_id和department_name

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
   SELECT 'X'
   FROM employees
   WHERE department_id = d.department_id
)

相关更新

1
2
3
4
5
6
UPDATE table1 alias1
SET column = (
   SELECT expression
   FROM table2 alias2
   WHERE alias1.column = alias2.column
);

使用相关子查询依据一个表中的数据更新另一个表的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE emp01
AS
SELECT *
FROM employees

ALTER TABLE emp01
ADD (department_name VARCHAR2(20))

UPDATE emp01 e
SET department_name = (
   SELECT department_name
   FROM departments
   WHERE department_id = e.department_id
)

相关删除

1
2
3
4
5
6
DELETE FROM table1 alias1
WHERE column operator
         (SELECT expression
         FROM table2 alias2
         WHERE alias1.column = alias2.column
         );

使用相关子查询依据一个表中的数据删除另一个表的数据

1
2
3
4
5
6
7
8
-- 删除表employees中，其与emp_history表皆有的数据

DELETE FROM employees E
WHERE employee_id = (
   SELECT employee_id
   FROM emp_history
   WHERE employee_id = E.employee_id
);

WITH子句

  1. 使用WITH子句，可以避免在SELECT语句中重复书写相同的语句块
  2. WITH子句将该子句中的语句块执行一次并存储到用户的临时表空间中
  3. 使用WITH子句可以提高查询效率
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查询公司中工资比Abel高的员工的信息

WITH Abel_sal AS (
   SELECT salary
   FROM employees
   WHERE last_name = 'Abel'
)

SELECT employee_id, salary
FROM employees
WHERE salary > (
   SELECT salary
   FROM Abel_sal
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH dept_sumsal AS (
SELECT department_name, SUM(salary) sum_sal
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY department_name
),
dept_avgsal AS (
SELECT SUM(sum_sal)/COUNT(*) avg_sum_sal
FROM dept_sumsal
)

SELECT *
FROM dept_sumsal
WHERE sum_sal > (
   SELECT avg_sum_sal
   FROM dept_avgsal
)

PL/SQL程序设计

PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对Oracle数据库进行访问。由于该语言集成于数据库服务器中，所以PL/SQL代码可以对数据库进行快速高效的处理。除此之外，可以在Oracle数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。

SQL与PL/SQL

什么是PL/SQL

PL/SQL是Procedure Language & Structured Query Language的缩写。Oracle的SQL是支持ANSI和ISO92标准的产品。PL/SQL是对SQL语言存储过程语言的扩展。从Oracle6以后，Oracle的RDBMS附带PL/SQL。它现在已成为一种过程处理语言，简称PL/SQL。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具的语法，如：在窗体上设置按钮等。

PL/SQL可用的SQL语言

PL/SQL是Oracle系统的核心语言，现在Oracle的许多部件是由PL/SQL写成。在PL/SQL中可以使用的SQL语言有：INSERT,UPDATE,DELETE,SELECT…INTO,COMMIT,ROLLBACK,SAVEPOINT。（在PL/SQL中只能用SQL语句中的DML部分，不能用DDL部分，如果要在PL/SQL中使用DDL的话，只能以动态的方式来使用）

  1. Oracle的PL/SQL组件在对PL/SQL程序进行解释时，同时对在其所使用的表名、列名及数据类型进行检查。
  2. PL/SQL可以在SQL*PLUS中使用。
  3. PL/SQL可以在高级语言中是哟。
  4. PL/SQL可以在Oracle的开发工具中使用。
  5. 其它开发工具也可以调用PL/SQL编写的过程和函数，如Power Builder等可以调用服务器端的PL/SQL过程。

运行PL/SQL程序

PL/SQL程序的运行是通过Oracle中的一个引擎来进行的。这个引擎可能在Oracle的服务器端，也可能在oracle应用开发的客户端。引擎执行PL/SQL中的过程性语句，然后将SQL语句发送给数据库服务器来执行。再将结果返回给执行端。

PL/SQL块

PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。PL/SQL块的结构如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE
/* 声明部分：在此声明PL/SQL用到的变量，类型及游标，以及局部的存储过程和函数
*/

BEGIN
/* 执行部分：过程及SQL语句，即程序的主要部分
*/

EXCEPTION
/* 执行异常部分：错误处理
*/

END;

其中，执行部分是必须的。

1
2
3
4
5
6
7
8
9
-- DECLARE
-- 声明的变量、类型、游标
BEGIN
-- 程序的执行部分
DBMS_OUTPUT.PUT_LINE('HelloWorld');
-- EXCEPTION
-- 针对BEGIN块中出现的异常，提供处理的机制
-- WHEN...THEN...
END;
1
2
3
4
5
6
7
8
9
10
DECLARE
   v_sal VARCHAR2(20);
BEGIN
   SELECT
      salary
   INTO v_sal
   FROM employees
   WHERE employee_id = 100;
   DBMS_OUTPUT.PUT_LINE(v_sal);
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE
   v_sal employees.salary%type;
   v_email VARCHAR2(20);
   v_hire_date DATE;
BEGIN
   SELECT
      salary,
      email,
      hire_date
   INTO
      v_sal,
      v_email,
      v_hire_date
   FROM employees
   WHERE employee_id = 100;

   DBMS_OUTPUT.PUT_LINE(v_sal ||',' || v_email || ',' || v_hire_date);
END;

变量类型

在Oracle8i中可以使用的变量类型有：
| 类型 | 子类 | 说明 | 范围 | Oracle限制 |
| :——————: | :————————————————————————————-: | :——————————————————————————————————————————————————————————-: | :———————————————————: | :————: |
| CHAR | Character String Rowid Nchar | Character定长字符串, Nchar为民族语言字符集 | 0~32767 可选，缺省=1 | 2000 |
| VARCHAR2 | Varchar,String NVARCHAR2 | 可变字符串，民族语言字符集 | 0~32767 4000 | 4000 |
| BINARY_INTEGER | | 带符号整数，为整数计算优化性能 | | |
| NUMBER(p,s) | Dec ；Double ；precision;Integer;Int;Numeric;Real;Small int | 小数，NUMBER的子类型高精度实数；整数，NUMBER的子类型 ;整数，NUMBER的子类型与NUMBER等价；与NUMBER等价；整数；比Integer小 | | |
| LONG | | 变长字符串 | 0~2147483647 | 32767字节 |
| DATE | | 日期型 | 公元前4712年1月1日至公元后4712年12月31日 | |
| BOOLEAN | 布尔型 | TRUE,FALSE,NULL | 不使用 |
| ROWID | | 存放数据库行号 | | |
| UROWID | | 通用行标识符，字符类型 | | |

复合类型

Oracle在PL/SQL中除了提供像前面介绍的各种类型外，还提供一种称为复合类型的类型 —-记录和表。

记录类型

记录类型是把逻辑相关的数据作为一个单元存储起来，称作PL/SQL RECORD的域(FIELD),其作用是存放互不相同但逻辑相关的信息。

定义记录类型语法如下：

1
2
3
4
5
6
TYPE record_type IS RECORD(
   Field1 type1 [NOT null] [:=EXP1],
   Field2 type2 [NOT null] [:=EXP2],
   ... ...
   Fieldn typen [NOT null] [:=EXPn]
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE
-- 声明一个记录类型
TYPE emp_record IS RECORD(
   v_sal employees.salary%type,
   v_email employees.email%type,
   v_hire_date employees.hire_date%type
);
-- 定义一个记录类型的成员变量
v_emp_record emp_record;

BEGIN
   SELECT
      salary,
      email,
      hire_date
   INTO
      v_emp_record
   FROM employees
   WHERE employee_id = 100;

   DBMS_OUTPUT.PUT_LINE(v_emp_record.v_sal ||',' || v_emp_record.v_email || ',' || v_emp_record.v_hire_date);
END;
Donate comment here