Oracle高级查询

本章将学习的内容有：

• 如何使用集合操作符，集合操作符用于将两个或多个查询返回的行组合起来

• 使用TRANSLATE函数
  

1、使用集合操作符

    注意：当使用集合操作符的时候，必须牢记这样一条限制：所有查询返回的列数以及列的类型必须匹配，但列名可以不同。

    使用INTERSECT查找两次检索结果中有相同的结果

    使用MINUS操作返回第一个结果去除第二个查询结果之后的结果。（有兴趣的童鞋可以自行测试下）

2、使用TRANSLATE函数

    TRANSLATE（x,from_string,to_string）函数在x中查找from_string中的字符，并将其转换成to_string中对应的字符。

3、使用DECODE()函数

    DECODE(value,search_value,result,default_value) 对value与search_value进行比较。如果这两个值相等，DECODE()返回result，否则返回default_value。

DECODE()允许在SQL中执行if-then-else类型的逻辑处理。

4、使用CASE表达式

    case表达式可以在SQL中实现if-then-else型逻辑，case表达式的工作方式与decode()类似，但是我们应该使用case，因为它兼容ANSI，并且已经成为SQL/92标准的一部分另外case表达式易读。语法：

case search_expression

when expression1 then result1

when expression2 then result2

...

when expressionN then resultN

else default_result

end

5、使用分析函数

    数据库中有很多内置的分析函数，能够执行复杂的计算，例如查找每月销量最高的商品类行，业绩最佳的销售员等。分析函数可以分为以下几类。

• 评级函数：可计算等级、百分比等

• 反百分比函数：可计算对应于百分点的数值

• 窗口函数：可计算累积和移动总计

• 报表函数：可计算注入市场占有率之类的结果

• 延迟与领先函数：可获得与当前记录的记录为若干条记录的那个记录的值

• 首函数与末函数：可获得某个已经排序的第一个和最后一个返回值

• 线性回归函数：可用一条普通最小平方回归曲线来拟合一组数值对

5、修改表内容

使用INSERT语句添加行

当省略列的列表时，指定的顺序必须与DESCRIBE命令输出结果显示的列的顺序一致

要为控制指定NULL值

从一个表向另一个表复制行。要求：源表和目标表的列数以及列的类型必须匹配

SQL>INSERT INTO cboss.fault_barrier_base_code(code_type,code_id)
    SELECT c.code_type,c.code_id
    FROM cboss.cboss_base_code
    WHERE c.serialno < 100

使用UPDATE语句修改行

使用RETURNING子句，返回使用聚合函数如AVG()计算的结果。

使用DELETE语句删除行

6、数据库的完整性

6.1、主键约束。主键的每一个值必须是唯一的primary key

6.2、外键约束。foreign key。。 references

6.3、使用默认值default

6.4、使用MERGE合并行

6.5、数据库事务（transaction）是一组SQL语句，这组SQL语句就是一个逻辑工作单元。事务是不可分割的SQL语句，其结果应该作为一个整体永久性地修改数据库的内容

或者取消对数据库的修改

6.5.1、事务的提交和回滚

    事务提交需要执行COMMIT语句，从而提交（commit）事务。要取消SQL语句的结果，需要执行ROLLBACK语句，从而回滚（rollback）事务，将所有行重新设置为原始状态。

6.52、事务的开始与结束

    事务是用来分割SQL语句的逻辑工作单元。事务既有起点也有终点。执行DML语句的时候事务开始。执行COMMIT或者ROLLBACK时事务结束。执行DDL语句，如CREATE自动提交事务。执行DCL语句，例如GRANT语句，自动提交事务。

6.5.3、设置保存点，SAVEPOINT，可以将修改回滚到保存点。这对一个大事务来说很有用。

6.5.4、事务的ACID特性

    原子性（Atomic）事务是原子的，不可分割

    一致性（Consist）事务必须确保数据库的状态保持一致，事务结束，数据库状态时结束，事务开始，数据库状态也是开始

    隔离性（Isolated）多个事务之间是独立运行的，互不影响

    持久性（Durale）一旦事务被提交，数据的变化就会被永远的保留下来。

6.5.5、并发事务

    Oracle数据库软件支持多个用户同时与数据库进行交互，每个用户都可以同时运行自己的事务。这种事务成为并发事务（concurrent transaction）。

    要支持并发事务，Oracle数据库软件必须保证表中的数据一直有效，这可以通过锁（Lock）来实现。

7、用户、特权和角色

创建用户：

CREATE USER user_name IDENTIFIED BY password
[DEFAULT TABLESPACE default_tablespace]
[TEMPORARY TABLESPACE temporary_tablespace]

给用户授权：

GRANT PRIVILIGE TO USER;
示例：
GRANT CREATE,DROP,CONNECT TO USER_NAME;
查看用户系统特权
SELECT * FROM USER_SYS_PRIVS ORDER BY PRIVILEGE;
查看用户对象特权
SELECT * FROM USER_TAB_PRIVS;
撤销用户特权
REMOVE PRIVELES FROM USER_NAME
示例
REMOVE CREATE,CONNECT FRMO USER_NAME;

角色（role）就是一组特权，他可以分配给一个用户或者其他角色

角色的优点和特性：

• 并不是一次一个地将特权直接授予一个用户，而是先创建角色，向该角色授予一些特权，然后再将角色授予多个用户和角色

• 在增加和删除一个角色的某种特权时，被授予该角色的所有用户和角色都会自动获得新加的特权或者自动失去这种特权

• 可以将多个角色授予一个用户或角色

• 可以为角色设置密码

创建角色
CREATE ROLE user_manager;
设置密码
CREATE ROLE admin_manager IDENTIFIED BY admin_password;
#为角色授权
GRANT PRIVILEGES ON TABLE_NAME TO USER_MANAGER;
#将角色授予用户
GRANT ROLE_NAME TO USER_NAME;