SQL

建立和管理表
建表
SQL> CREATE TABLE dept
  2 (deptno NUMBER(2),
  3  dname VARCHAR2(14),
  4  loc VARCHAR2(13));
Table created.

使用子查询建表
SQL> CREATE TABLE dept30
  2 AS
  3 SELECT    empno, ename, sal*12 ANNSAL, hiredate
  4 FROM    emp
  5 WHERE    deptno = 30;

Table created.

SQL> DESCRIBE dept30
Name                         Null?    Type
---------------------------- -------- -----
EMPNO                        NOT NULL NUMBER(4)
ENAME                                 VARCHAR2(10)
ANNSAL                                NUMBER
HIREDATE                              DATE

ALTER TABLE语句
使用 ALTER TABLE语句：
• 增加新列
• 修改已存在列
• 定义新列的默认值
ALTER TABLE table
ADD    (column datatype [DEFAULT expr]
   [, column datatype]...);
ALTER TABLE table
MODIFY    (column datatype [DEFAULT expr]
   [, column datatype]...);

增加列
• 使用ADD子句
SQL> ALTER TABLE dept30
  2  ADD    (job VARCHAR2(9));
Table altered.
• 新列变为最后一列
    EMPNO ENAME         ANNSAL HIREDATE  JOB
--------- ---------- --------- --------- ----
     7698 BLAKE          34200 01-MAY-81
     7654 MARTIN         15000 28-SEP-81
     7499 ALLEN          19200 20-FEB-81
     7844 TURNER         18000 08-SEP-81
...
6 rows selected.

修改列
• 可以改变列的数据类型、长度和默认值

ALTER TABLE dept30
MODIFY (ename VARCHAR2(15));
Table altered.


• 改变默认值只对将来插入起作用
•
删除表
SQL> DROP TABLE dept30;
Table dropped.
改变对象名
• 使用RENAME语句可以修改表、视图、同义词等对象的名称
SQL> RENAME dept TO department;
Table renamed.


• 改变对象名必须由对象所有者完成
•
截断表
SQL> TRUNCATE TABLE department;
Table truncated.
小结
语句 说明
CREATE TABLE 建表
ALTER TABLE 修改表结构
DROP TABLE 删除表
RENAME 修改对象名
TRUNCATE 截断表
COMMENT 增加注释






操作数据

数据操纵语言
• DML语句
– INSERT：增加行
– UPDATE：修改行
– DELETE：删除行
• 事务：由一组DML语句组成

INSERT语句
INSERT INTO table [(column [, column...])]
VALUES (value [, value...]);
• 插入新行要为每列提供值
• 值的顺序要与列的顺序保持一致
• 字符和日期值要用单引号引住

插入新行
SQL> INSERT INTO dept (deptno, dname, loc)
  2  VALUES (50, 'DEVELOPMENT', 'DETROIT');
1 row created.
SQL> INSERT INTO dept (deptno, dname )
  2  VALUES (60, 'MIS');
1 row created.

使用替代变量插入数据
SQL> INSERT INTO dept (deptno, dname, loc)
  2  VALUES   (&department_id,
  3                 '&department_name', '&location');

Enter value for department_id: 80
Enter value for department_name: EDUCATION
Enter value for location: ATLANTA

1 row created.

复制表行
SQL> INSERT INTO managers(id, name, salary, hiredate)
  2          SELECT empno, ename, sal, hiredate
  3          FROM   emp
  4          WHERE job = 'MANAGER';
3 rows created.

修改表数据UPDATE语句

UPDATE table
SET column = value [, column = value]
[WHERE condition];
更新表行
SQL> UPDATE emp
  2  SET    deptno = 20
  3  WHERE  empno = 7782;
1 row updated.
SQL> UPDATE employee
  2  SET    deptno = 20;
14 rows updated.
使用多列子查询更新
SQL> UPDATE  emp
  2  SET     (job, deptno) =
  3   (SELECT job, deptno
  4                          FROM    emp
  5                          WHERE   empno = 7499)
  6  WHERE   empno = 7698;
1 row updated.
DELETE语句
DELETE [FROM]   table
[WHERE   condition];
删除表行
SQL> DELETE FROM department
  2  WHERE dname = 'DEVELOPMENT';
1 row deleted.

SQL> DELETE FROM department;
4 rows deleted.

基于另一张表删除行
SQL> DELETE FROM emp
  2  WHERE deptno =
  3        (SELECT   deptno
  4          FROM     dept
  5          WHERE    dname ='SALES');
6 rows deleted.

提交数据
SQL> UPDATE emp
  2  SET deptno = 10
  3  WHERE empno = 7782;
1 row updated.

SQL> COMMIT;
Commit complete.

回退数据
SQL> DELETE FROM employee;
14 rows deleted.
SQL> ROLLBACK;
Rollback complete.

SQL> UPDATE...
SQL> SAVEPOINT update_done;
Savepoint created.
SQL> INSERT...
SQL> ROLLBACK TO update_done;
Rollback complete.

定义约束
CREATE TABLE [schema.]table
    (column datatype [DEFAULT expr]
[column_constraint],
…
[table_constraint]);

CREATE TABLE emp(
       empno  NUMBER(4),
         ename  VARCHAR2(10),
       …
       deptno  NUMBER(7,2) NOT NULL,
     CONSTRAINT emp_empno_pk
           PRIMARY KEY (EMPNO));
定义约束
• 列级约束
             column [CONSTRAINT constraint_name] constraint_type,

• 表级约束
             column,...
  [CONSTRAINT constraint_name] constraint_type
  (column, ...),

NOT NULL约束
SQL> CREATE TABLE emp(
  2  empno NUMBER(4),
  3 ename VARCHAR2(10) NOT NULL,
  4 job VARCHAR2(9),
  5 mgr NUMBER(4),
  6 hiredate DATE,
  7 sal NUMBER(7,2),
  8 comm NUMBER(7,2),
  9 deptno NUMBER(7,2) NOT NULL);

UNIQUE约束
SQL> CREATE TABLE   dept(
  2  deptno   NUMBER(2),
  3 dname   VARCHAR2(14),
  4 loc   VARCHAR2(13),
  5 CONSTRAINT dept_dname_uk UNIQUE(dname));

PRIMARY KEY约束
SQL> CREATE TABLE   dept(
  2  deptno   NUMBER(2),
  3 dname   VARCHAR2(14),
  4 loc   VARCHAR2(13),
  5 CONSTRAINT dept_dname_uk UNIQUE (dname),
  6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));




FOREIGN KEY约束
SQL> CREATE TABLE emp(
  2  empno NUMBER(4),
  3 ename VARCHAR2(10) NOT NULL,
  4 job VARCHAR2(9),
  5 mgr NUMBER(4),
  6 hiredate DATE,
  7 sal NUMBER(7,2),
  8 comm NUMBER(7,2),
  9 deptno NUMBER(7,2) NOT NULL,
10 CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
11 REFERENCES dept (deptno));
FOREIGN KEY 约束关键字
• FOREIGN KEY：在表级定义时需要
• REFERENCES：指定主表及其主键列
• ON DELETE CASCADE：级联删除选项

CHECK约束
• 定义每行数据必须满足的条件
• 定义CHECK约束有以下限制
– 不允许引用伪列CURRVAL、NEXTVAL、LEVEL和ROWNUM
– 不允许调用函数SYSDATE、UID、USER和 USERENV
..., deptno NUMBER(2),
      CONSTRAINT emp_deptno_ck 
            CHECK (DEPTNO BETWEEN 10 AND 99),...

增加约束
  ALTER TABLE table
  ADD [CONSTRAINT constraint] type (column);
注意事项：增加NOT NULL约束必须使用MODIFY子句
增加约束
SQL> ALTER TABLE     emp
  2  ADD CONSTRAINT  emp_mgr_fk
  3  FOREIGN KEY(mgr) REFERENCES emp(empno);
Table altered.

删除约束
SQL> ALTER TABLE   emp
  2  DROP CONSTRAINT  emp_mgr_fk;
Table altered.

SQL> ALTER TABLE dept
  2  DROP PRIMARY KEY CASCADE;
Table altered.

禁止约束
SQL> ALTER TABLE emp
  2  DISABLE CONSTRAINT emp_empno_pk CASCADE;
Table altered.

激活约束
SQL> ALTER TABLE emp
  2  ENABLE CONSTRAINT emp_empno_pk;
Table altered.

查看约束
SQL>  SELECT constraint_name, constraint_type,
  2 search_condition
  3   FROM user_constraints
  4   WHERE table_name = 'EMP';

CONSTRAINT_NAME          C SEARCH_CONDITION
------------------------ - -------------------------
SYS_C00674               C EMPNO IS NOT NULL 
SYS_C00675               C DEPTNO IS NOT NULL
EMP_EMPNO_PK      P
...
查看约束列
SQL> SELECT constraint_name, column_name
  2  FROM user_cons_columns
  3  WHERE table_name = 'EMP';

CONSTRAINT_NAME           COLUMN_NAME
------------------------- ----------------------
EMP_DEPTNO_FK             DEPTNO
EMP_EMPNO_PK              EMPNO
EMP_MGR_FK                MGR
SYS_C00674                EMPNO
SYS_C00675                DEPTNO



子查询
SELECT 查询列表
FROM 表
WHERE 表达式 操作符
(SELECT 查询列表
       FROM 表);

• 子查询(内查询)在主查询之前执行.
• 子查询的结果用于主查询 (外查询).
使用子查询
SQL> SELECT ename
  2  FROM   emp
  3  WHERE  sal >
  4     (SELECT sal
  5               FROM   emp
  6               WHERE  empno=7566);

ENAME
----------
KING
FORD
SCOTT

执行单行子查询
SQL> SELECT   ename, job
  2  FROM     emp
  3  WHERE    job =
  4 (SELECT  job
  5      FROM     emp
  6     WHERE    empno = 7369)
  7  AND      sal >
  8 (SELECT  sal
  9 FROM emp
  10 WHERE empno = 7876);

ENAME      JOB
---------- ---------
MILLER     CLERK

在子查询中使用组函数
SQL> SELECT ename, job, sal
  2  FROM emp
  3  WHERE sal =
  4 (SELECT MIN(sal)
  5 FROM emp);

ENAME      JOB             SAL
---------- --------- ---------
SMITH      CLERK           800


在HAVING子句中使用子查询
• Oracle服务器先执行子查询.
• Oracle服务器将结果返回给主查询的HAVING子句.
SQL> SELECT deptno, MIN(sal)
  2  FROM emp
  3  GROUP BY deptno
  4  HAVING MIN(sal) >
  5 (SELECT MIN(sal)
  6 FROM emp
  7 WHERE deptno = 20);

在多行子查询中使用ANY操作符
SQL> SELECT  empno, ename, job
  2  FROM    emp
  3  WHERE   sal < ANY
  4 (SELECT sal
  5 FROM emp
  6 WHERE job = 'CLERK')
  7  AND     job <> 'CLERK';

    EMPNO ENAME      JOB
--------- ---------- ---------
     7654 MARTIN     SALESMAN
     7521 WARD       SALESMAN

在多行子查询中使用ALL操作符(多列子查询在下章)
SQL> SELECT  empno, ename, job
  2  FROM    emp
  3  WHERE   sal > ALL
  4 (SELECT avg(sal)
  5 FROM emp
  6 GROUP BY deptno);

    EMPNO ENAME      JOB
--------- ---------- ---------
     7839 KING       PRESIDENT
     7566 JONES      MANAGER
     7902 FORD       ANALYST
     7788 SCOTT      ANALYST



使用多列子查询
显示满足工资和补贴分别和部门30中的雇员的这两项匹配的雇员的姓名，部门编号，工资和补贴.
SQL> SELECT ename, deptno, sal, comm
  2  FROM emp
  3  WHERE   (sal, NVL(comm,-1)) IN
  4 (SELECT sal, NVL(comm,-1)
  5       FROM   emp
  6       WHERE  deptno = 30);

子查询中的空值
SQL> SELECT employee.ename
  2  FROM emp employee
  3  WHERE employee.empno NOT IN
  4 (SELECT manager.mgr
  5 FROM   emp manager);
no rows selected.

在FROM子句中使用子查询
SQL> SELECT  a.ename, a.sal, a.deptno, b.salavg
  2  FROM    emp a, (SELECT   deptno, avg(sal) salavg
  3                  FROM     emp
  4                  GROUP BY deptno) b
  5  WHERE   a.deptno = b.deptno
  6  AND     a.sal > b.salavg;

ENAME            SAL    DEPTNO     SALAVG
---------- --------- --------- ----------
KING            5000        10  2916.6667
JONES           2975        20       2175
SCOTT           3000        20       2175
...
6 rows selected.