sql扫盲（4）

1、序列
（1）序列的作用: 可供多个用户用来产生唯一数值的数据库对象
自动提供唯一的数值
共享对象
主要用于提供主键值
将序列值装入内存可以提高访问效率
（2）定义序列：

CREATE SEQUENCE sequence
   [INCREMENT BY n]
   [START WITH n]
   [{MAXVALUE n | NOMAXVALUE}]
   [{MINVALUE n | NOMINVALUE}]
   [{CYCLE | NOCYCLE}]
   [{CACHE n | NOCACHE}];

（3）创建序列： 创建序列 DEPT_DEPTID_SEQ为表 DEPARTMENTS 提供主键
不使用 CYCLE 选项

create sequence dept_deptid_seq
    increment by 10
    start with 120
    maxvalue 9999
    nocache
    nocycle;

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

select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences;

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

 ALTER SEQUENCE dept_deptid_seq
       INCREMENT BY 20
       MAXVALUE 999999
       NOCACHE
       NOCYCLE;

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

2、索引：一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
（1）创建索引：
自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引。
手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询

create index index on table(column[,column]...);

在表 EMPLOYEES的列 LAST_NAME 上创建索引

create index emp_last_name_idx on employees(last_name);

（2）什么时候创建索引
以下情况可以创建索引:
a.列中数据值分布范围很广
b.列经常在 WHERE 子句或连接条件中出现
c.表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
下列情况不要创建索引:
a.表很小
b.列不经常作为连接条件或出现在WHERE子句中
c.查询的数据大于2%到4%
d.表经常更新
（3）查询索引
可以使用数据字典视图 USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息

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';

（4）删除索引

DROP INDEX index;

drop index upper_last_name_idx;

3、同义词
（1）使用同义词访问相同的对象:
a.方便访问其它用户的对象
b.缩短对象名字的长度
（2）创建和删除同义词

create SYNONYM d_sum for dept_sum_vu

drop SYNONYM d_sum;

4、高级子查询
（1）成对比较举例：查询和 178, 174 在同一个部门，且同一个 leader 的员工有哪些 ？

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(178,174))
and employee_id NOT IN(178,174);

（2）不成对比较举例

select employee_id,manager_id,department_id
from employees
where manager_id IN
        (select manager_id
        from employees
        where employee_id IN(174,178))
AND department_id IN
        (select department_id
        from employees
        where employee_id IN(174,178));
AND employee_id NOT IN(174,178);

（3）在 FROM 子句中使用子查询
查询工资比所在部门平均工资高的员工的 last_name, salary, department_id, salary

select a.last_name,a.salary,a.department_id,b.salavg
from employees a,(select department_id,AVG(salary) salavg from employees group by department_id) b
where a.department_id = b.department_id
AND a.salary > b.salavg;

（4）相关子查询
例如：查询比做同样的工作的平均工资高的员工的 employee_id, last_name, job_id

select employee_id,last_name,job_id
from employees emp
where salary>(select AVG(salary) from emp
    where job_id = emp.job_id)

（5）EXISTS 操作符：检查在子查询中是否存在满足条件的行

select employee_id,last_name,job_id,department_id
from employees outer
where exists (select 'X' from employees
        where manager_id = outer.employee_id);

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
（6）NOT EXISTS 操作符应用举例

select employee_id,last_name,job_id,department_id
from employees outer
where not exists (select 'X' from employees
        where manager_id = outer.employee_id);

（7）WITH 子句
使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块。WITH 子句将该子句中的语句块执行一次 并存储到用户的临时表空间中，使用 WITH 子句可以提高查询效率

WITH
dept_costs AS (
  SELECT d.department_name,SUM(e.salary) AS dept_total
  FROM employees e,departments d
  WHERE e.department_id = d.department_id
  GROUP BY d.department_name),
avg_cost AS (
  SELECT SUM(dept_total)/COUNT(*) AS dept_avg
  FROM dept_costs)
select * 
from dept_costs
where dept_total >
    (select dept_avg from avg_cost)
order by department_name;

5、SET操作符
（1）UNION：UNION 操作符返回两个查询的结果集的并集

select employee_id,job_id
from employees
union
select employee_id,job_id
from job_history;

UNION ALL 操作符返回两个查询的结果集的并集以及两个结果集的重复部分（不去重）

select employee_id,job_id
from employees
union all
select employee_id,job_id
from job_history;

（2）INTERSECT：INTERSECT 操作符返回两个结果集的交集

select employee_id,job_id
from employees
intersect
select employee_id,job_id
from job_history;

（3）MINUS：MINUS 操作符返回两个结果集的补集

select employee_id,job_id from employees
minus
select employee_id,job_id
from job_history

（4）注意事项
a.在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
b.ORDER BY 子句:只能在语句的最后出现
c.除 UNION ALL 之外，系统会自动将重复的记录删除，系统将第一个查询的列名显示在输出中
d.除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列