sql扫盲（3）

1、创建和管理表
（1）使用子查询创建表
使用 AS subquery 选项，将创建表和插入数据结合起来
create table tableName
[(column,column …)]
AS subquery
指定的列和子查询中的列要一一对应
通过列名和默认值定义列

create table dept80
  as
  select employee_id,last_name,salary*12 annsal,hiredate
  from employees where department_id = 80;

（2）使用alter table 语句追加、修改、删除列
使用 ADD 子句追加一个新列

alter table dept80 add (job_id varchar(9));

可以修改列的数据类型, 尺寸, 和默认值

alter table dept80 modify (last_name varchar(30);

使用 DROP COLUMN 子句删除不再需要的列.

alter table dept80 drop column job_id;

修改列名

ALTER TABLE table_name rename column old_column_name 
to new_column_name

（3）改变对象的名称
执行RENAME语句改变表, 视图, 序列, 或同义词的名称

rename dept to detail_dept

（4）清空表
TRUNCATE TABLE 语句:a).删除表中所有的数据 b).释放表的存储空间

trunscate table detail_dept;

TRUNCATE语句不能回滚
可以使用 DELETE 语句删除数据

2、约束
（1）约束是表级的强制规定
有以下五种约束:NOT NULL、UNIQUE 、PRIMARY KEY、FOREIGN KEY、CHECK
（2）作用范围：列级约束只能作用在一个列上，而表约束可以作用在多个列上（当然表约束也可以作用在一个列上）。
定义方式：列约束必须跟在列的定义里后面，表约束不与列一起，而是单独定义。
非空(not null) 约束只能定义在列上
（3）定义约束
列级

column [CONSTRAINT constraint_name] constraint_type,

表级

column,...
    [constraint constraint_name] constraint_type,
    (column,...)

例如：

create table employees(
    employee_id number(6),
    first_name varchar(20),
    ...
    job_id varchar(10) not null,
    constraint emp_emp_id_pk primary key(employee_id));

（4）UNIQUE约束：唯一约束，可以定义在表级或列级，别定义的列不允许有重复值

create table employees(
    employee_id number(6),
    last_name varchar2(25) not null,
    email varchar2(25),
    salary number(8,2),
    commission_pct number(2,2),
    hire_date date not null,
...
    constraint emp_email_uk unique(email));

（5）PRIMARY KEY 约束和FOREIGN KEY 约束：可以定义在表级和列级
primary key示例：

create table departments(
    department_id number(4),
    department_name varchar2(30)
      constraint dept_name_nn not null,
    manager_id number(6),
    location_id number(4),
    constraint dept_id_pk primary key(department_id));

foreign key 示例：

create table employees(
    employee_id number(6),
    last_name varchar2(25) not null,
    email varchar2(25),
    salary number(8,2),
    commission_pct number(2,2),
    hire_date date not null,
...
    department_id number(4),
    constraint emp_dept_fk foreign key (department_id)
      references departments(department_id),
    constraint emp_email_uk unique(email));

foreign key 约束的关键字
FOREIGN KEY：在表级指定子表中的列
REFERENCES：标示在父表中的列
ON DELETE CASCADE：当父表中的列被删除时，子表中相对应的列也被删除
ON DELETE SET NULL：子表中的相应列清空
（6）CHECK约束：定义每一行必须满足的条件

...,salary number(2)
    constraint emp_salary_min
        check(salary>0),...

（7）添加约束的语法
使用 ALTER TABLE 语句:
添加或删除约束, 但是不能修改约束
有效化或无效化约束
添加 NOT NULL 约束要使用 MODIFY 语句

alter table table
    add [constraint constraint] type (column);

添加和删除约束

alter table employees
    add constraint emp_manager_fk foreign key(manager_id)
    references employees(employee_id);
alter table employees
    drop constraint emp_manager_fk;

无效化约束

alter table employees
    disable constraint emp_emp_id_pk;

激活约束

alter table employees 
    enable constraint emp_emp_id_pk;

查询约束
查询数据字典视图 USER_CONSTRAINTS

select constraint_name,constraint_type,search_condition
from user_constraints
where table_name = 'EMPLOYEES';

3、视图
（1）为什么使用视图？
a.控制数据访问 b.简化查询 c.避免重复访问相同的数据
（2）创建视图
在 CREATE VIEW 语句中嵌入子查询

create view empvu80
  as select employee_id,last_name,salary
     from employees
     where department_id = 80;  

创建视图时在子查询中给列定义别名

create view salvu50
   as select employee_id ID_NUMBER,last_name NAME,salary*12 ANN_SALARY
   from employees
   where department_id = 50;

（3）查询视图

select * from salvu50;

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

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;

（5）视图中使用DML的规定
a. 当视图定义中包含以下元素之一时不能使用delete:
组函数
GROUP BY 子句
DISTINCT 关键字
ROWNUM 伪列
b. 当视图定义中包含以下元素之一时不能使用update :
组函数
GROUP BY子句
DISTINCT 关键字
ROWNUM 伪列
列的定义为表达式
c. 当视图定义中包含以下元素之一时不能使用insert :
组函数
GROUP BY 子句
DISTINCT 关键字
ROWNUM 伪列
列的定义为表达式
表中非空的列在视图定义中未包括
（6）删除视图
删除视图只是删除视图的定义，并不会删除基表的数据

drop view empvu80;

（7）Top-N 分析
查询最大的几个值的 Top-N 分析:

SELECT [column_list], ROWNUM  
FROM   (SELECT [column_list] 
        FROM table
        ORDER  BY Top-N_column)
WHERE  ROWNUM <=  N;

例如：查询工资最高的三名员工：

select rownum as rank,last_name,salary 
from (select last_name,salary from employees
      order by salary desc)
where rownum <=3;