SQL语句 第9章 数据描述语言(DDL)与数据控制语言(DCL)

9.1  数据控制语言（DCL）
9.1.1  数据库权限
3．用户
（1）建立用户Creating a User
例9.1_1 建立用户wang并指定口令为office
CONNECT system/huali1963
CREATE USER wang IDENTIFIED BY office;
CONNECT wang/office

例9.1_2 建立用户xiaoli并指定口令为finance。
CONNECT system/huali1963
CREATE USER xiaoli IDENTIFIED BY finance
  DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp;
CONNECT xiaoli/finance

例9.1_3 建立用户zhang和zhao并分别指定口令为archives与office。
CONNECT system/huali1963
CREATE USER zhang IDENTIFIED BY archives;
CREATE USER zhao IDENTIFIED BY office;

（2）修改用户口令Changing a User's Password
例9.1_4 将用户wang的口令修改为gold。
CONNECT system/huali1963
ALTER USER wang IDENTIFIED BY gold;

（3）删除用户Deleting a User
例9.1_5 将用户wang删除。
CONNECT system/huali1963
DROP USER wang;

9.1.2  权限控制
1．系统权限控制
（1）授予用户系统权限Granting System Privileges to a User
例9.1_6 授予用户xiaoli CREATE SESSION, CREATE USER, CREATE TABLE等权限。授予用户zhang zhao CREATE SESSION权限。
CONNECT system/huali1963
GRANT CREATE SESSION, CREATE USER, CREATE TABLE TO xiaoli;

GRANT CREATE SESSION TO zhang;
GRANT CREATE SESSION TO zhao;

例9.1_7 授予用户xiaoli EXECUTE ANY PROCEDURE权限。并WITH ADMIN OPTION。
CONNECT system/huali1963
GRANT EXECUTE ANY PROCEDURE TO xiaoli WITH ADMIN OPTION;

例9.1_8 通过用户xiaoli授予用户zhang EXECUTE ANY PROCEDURE权限。
CONNECT xiaoli/finance
GRANT EXECUTE ANY PROCEDURE TO zhang;

例9.1_9
CONNECT system/huali1963
GRANT CREATE SESSION, EXECUTE ANY PROCEDURE TO PUBLIC;

（2）查看授予用户的系统权限Checking System Privileges Granted to a User
例9.1_10 查看授予用户xiaoli的系统权限。
CONNECT xiaoli/finance
SELECT *
  FROM user_sys_privs;

例9.1_11 查看授予用户zhang的系统权限。
CONNECT zhang/archives
SELECT *
  FROM user_sys_privs;

（3）收回用户系统权限Revoking System Privileges from a User
例9.1_12 收回用户xiaoli CREATE TABLE的系统权限，之后再查看授予用户xiaoli的系统权限。
CONNECT system/huali1963
REVOKE CREATE TABLE FROM xiaoli;
CONNECT xiaoli/finance
SELECT *
  FROM user_sys_privs;

2．对象权限控制
（1）授予用户对象权限Granting Object Privileges to a User
例9.1_13 授予用户xiaoli在表Departments上具有SELECT, INSERT, UPDATE对象权限。
CONNECT system/huali1963
GRANT SELECT, INSERT, UPDATE ON Departments TO xiaoli;

例9.1_14 授予用户xiaoli在表Teachers的wage, bonus列上具有UPDATE对象权限。
CONNECT system/huali1963
GRANT UPDATE (wage, bonus) ON Teachers TO xiaoli;

例9.1_15 授予用户xiaoli在表Students上具有SELECT,对象权限。并WITH GRANT OPTION
CONNECT system/huali1963
GRANT SELECT ON Students TO xiaoli WITH GRANT OPTION;
CONNECT xiaoli/finance
SELECT * FROM system.Students;

例9.1_16 通过用户xiaoli授予用户zhao 在表Students上具有SELECT,对象权限。
CONNECT xiaoli/finance
GRANT SELECT ON system.Students TO zhao;
CONNECT zhao/office
SELECT * FROM system.Students;

（2）查看授予用户的对象权限
例9.1_17 查看用户xiaoli在表departments上所具有哪些对象权限
CONNECT xiaoli/finance
SELECT owner, table_name, grantor, privilege, grantable
  FROM user_tab_privs_recd;

（3）收回用户对象权限Revoking Object Privileges
例9.1_18 收回用户xiaoli在表Departments上具有INSERT对象权限。
CONNECT system/huali1963
REVOKE INSERT ON Departments FROM xiaoli;

例9.1_19 收回用户zhao在表Students上具有SELECT对象权限。
CONNECT xiaoli/finance
REVOKE SELECT ON system.Students FROM zhao;

9.2  表
9.2.1  建立表Creating a Table
1．从头创建
例9.2_1 建立表Departments1。其中department_id设为PRIMARY KEY，department_name设为NOT NULL。
CONNECT system/huali1963
CREATE TABLE Departments1(
  department_id NUMBER(3)
    CONSTRAINT d1_pk PRIMARY KEY,
  department_name VARCHAR2(8) NOT NULL,
  director_id NUMBER(5)
);
例9.2_2 建立表Teachers1，同时将Departments1的department_id设为外关键字。
CREATE TABLE Teachers1(
  teacher_id NUMBER(5)
    CONSTRAINT t1_pk PRIMARY KEY,
  name VARCHAR2(6) NOT NULL,
  job_title  VARCHAR2(10),
  hire_date DATE,
  bonus NUMBER(4) DEFAULT 800,
  wage NUMBER(5),
  department_id NUMBER(3)
    CONSTRAINT t1_fk_d1
    REFERENCES Departments1(department_id)
);
例9.2_3 建立表Students1，同时使用CHECK子句约束sex列值，为register_date设置默认值，为phone_number设置Unique约束。
CREATE TABLE Students1(
  student_id NUMBER(5)
    CONSTRAINT s1_pk PRIMARY KEY,
  name VARCHAR2(10) NOT NULL,
  sex VARCHAR2(6)
  CONSTRAINT sex_chk1 CHECK(sex IN ('男','女')),
  register_date DATE DEFAULT SYSDATE,
  phone_number VARCHAR2(12) CONSTRAINT pnum_uq UNIQUE
);

2．由已存在的创建（复制表）
例9.2_4 由表Teachers复制生成表Teachers2。并且表Teachers2具有与表Teachers相同的结构和相同的数据记录。
CREATE TABLE Teachers2 AS SELECT * FROM Teachers;

例9.2_5 由表Students生成表Students2。并且表Students2复制表Students 的student_id和name两列，复制其中计算机专业的学生记录。（部分行，部分列）
CREATE TABLE Students2 AS
  SELECT student_id, name FROM Students
    WHERE specialty='计算机';

例9.2_6 由表Teachers 生成表Teachers3。通过UNION操作复制其中部门号为101和102的教师记录。
CREATE TABLE Teachers3 AS (
  SELECT * FROM Teachers WHERE department_id=101
UNION
  SELECT * FROM Teachers WHERE department_id=102);

例9.2_7 由表Teachers和表Departments 生成表Teachers4。通过连接查询复制表Teachers中的teacher_id和name列，复制表Departments中的department_name，并复制连接条件匹配的教师记录。
CREATE TABLE Teachers4 AS
  SELECT t.teacher_id, t.name, department_name
    FROM Teachers t, Departments d
      WHERE t.department_id=d.department_id;

9.2.2  获得表信息
1．获得表的基本信息Getting Information on Tables
例9.2_8 在数据字典中，获取表DEPARTMENTS1、TEACHERS1、TEACHERS2、STUDENTS1、STUDENTS2、TEACHERS3、TEACHERS4的table_name、tablespace_name、 temporary的等信息。
SELECT table_name, tablespace_name, temporary
  FROM user_tables
    WHERE table_name IN ('DEPARTMENTS1', 'TEACHERS1', 'TEACHERS2');

SELECT table_name, tablespace_name, temporary FROM user_tables
  WHERE table_name IN ('STUDENTS1', 'STUDENTS2', 'TEACHERS3', 'TEACHERS4');

2．获得表中列的信息Getting Information on Columns in Tables
例9.2_9 使用SQL*PLUS的DESCRIBE命令显示表中列的信息。以表Teachers2为例。
DESCRIBE Teachers2

DESCRIBE Departments1
DESCRIBE Teachers1
DESCRIBE Students1
DESCRIBE Students2
DESCRIBE Teachers3
DESCRIBE Teachers4

例9.2_10 在数据字典中，获取表Teachers2中列的信息。
COLUMN column_name FORMAT a15
COLUMN data_type FORMAT a10
SELECT column_name, data_type, data_length, data_precision, data_scale
  FROM user_tab_columns
    WHERE table_name = 'TEACHERS2';

SELECT
column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'DEPARTMENTS1';
SELECT
column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEACHERS1';
SELECT
column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'STUDENTS1';
SELECT
column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'STUDENTS2';
SELECT
column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEACHERS3';
SELECT
column_name, data_type, data_length, data_precision, data_scale
FROM user_tab_columns
WHERE table_name = 'TEACHERS4';

9.2.3  修改表Altering a Table
1．增/删/改表的列Add, modify, or drop a column
（1）添加表的列Add a column
例9.2_11在表Students2中添加sex列，取字符型数据，（例9.2_5）
DESCRIBE Students2
ALTER TABLE Students2
  ADD sex VARCHAR2(6);
DESCRIBE Students2

例9.2_12在表Students2中添加enrollment_grade列，取数字型数据（例9.2_5）

ALTER TABLE Students2
  ADD enrollment_grade NUMBER(3);
DESCRIBE Students2

例9.2_13在表Students2中添加register_date列，取日期型数据，并把系统日期作为默认值。（例9.2_5）

ALTER TABLE Students2
  ADD register_date DATE DEFAULT SYSDATE;
DESCRIBE Students2

（2）删除表的列drop a column
The following example uses ALTER TABLE to drop the initially_created column from order_status2:
例9.2_14删除表Students2的sex列。

ALTER TABLE Students2
  DROP COLUMN sex;
DESCRIBE Students2

ALTER TABLE Students2
DROP COLUMN enrollment_grade;
DESCRIBE Students2

ALTER TABLE Students2
DROP COLUMN register_date;
DESCRIBE Students2

（3）修改表的列modify a column
例9.2_15 修改Teachers1表wage列的数字精度，由原来的NUMBER(5)修改为NUMBER(7,2)。
DESCRIBE Teachers1
ALTER TABLE Teachers1
  MODIFY wage NUMBER(7,2);
DESCRIBE Teachers1

例9.2_16 修改Teachers1表name列的字符宽度，由原来的VARCHAR2(6)修改为VARCHAR2(10)。
ALTER TABLE Teachers1
  MODIFY name VARCHAR2(10);
DESCRIBE Teachers1

例9.2_17 修改Teachers1表teacher_id列的数据类型，由原来的NUMBER(5)修改为VARCHAR2(5)。
ALTER TABLE Teachers1
  MODIFY teacher_id VARCHAR2(5);
DESCRIBE Teachers1

例9.2_18 将Teachers1表bonus列的默认值由800修改为1000。
select DATA_DEFAULT from user_tab_columns  
  where table_name = 'TEACHERS1' AND COLUMN_NAME='BONUS';
ALTER TABLE Teachers1
  MODIFY bonus DEFAULT 1000;
select DATA_DEFAULT from user_tab_columns  
  where table_name = 'TEACHERS1' AND COLUMN_NAME='BONUS';

2．添加/删除约束Add or drop a constraint
（1）添加约束Add a constraint
例9.2_19 给表Students1添加主关键字约束。主关键字为student_id，约束名字为s1_pk。
ALTER TABLE Students1
  ADD CONSTRAINT s1_pk PRIMARY KEY(student_id);

例9.2_20给表Teachers1添加外关键字约束。外关键字为department_id，参考Departments1表中的department_id，约束名字为t1_fk_d1。
ALTER TABLE Teachers1 ADD CONSTRAINT t1_fk_d1
  FOREIGN KEY(department_id) REFERENCES Departments1(department_id);

例9.2_21给表Students1的name列添加NOT NULL约束
ALTER TABLE Students1
  MODIFY name NOT NULL;

例9.2_22给表Students1的sex列添加CHECK约束，使其只能取男、女二字，约束名字为sex_chk。
ALTER TABLE Students1
  ADD CONSTRAINT sex_chk1
    CHECK(sex IN ('男','女'));

例9.2_23 给表Students1的phone_number列添加Unique约束，使其不能取重复值，约束名字为pnum_uq。
ALTER TABLE Students1
  ADD CONSTRAINT pnum_uq UNIQUE(phone_number);

（2）删除约束drop a constraint
例9.2_24 删除表Students1的主关键字约束。
ALTER TABLE Students1
  DROP CONSTRAINT s1_pk;

例9.2_25删除表Teachers1的外关键字约束。
ALTER TABLE Teachers1
  DROP CONSTRAINT t1_fk_d1;

例9.2_26 删除Students1表name列的NOT NULL约束。
ALTER TABLE Students1
  MODIFY name NULL;

例9.2_27删除Students1表sex 列的CHECK约束。
ALTER TABLE Students1
  DROP CONSTRAINT sex_chk1;

例9.2_28删除Students1表phone_number 列的Unique约束。
ALTER TABLE Students1
  DROP CONSTRAINT pnum_uq;

3．允许/禁止约束Enable and disable a constraint
（1）禁止约束Disabling a Constraint
例9.2_29 在Students1表phone_number列上建立UNIQUE pnum_uq的同时，使用DISABLE禁止约束（新建约束时）
（ALTER TABLE Students1
DROP CONSTRAINT pnum_uq）
ALTER TABLE Students1
  ADD CONSTRAINT pnum_uq UNIQUE(phone_number) DISABLE;

例9.2_30 将已建立的Students1表sex列的CHECK约束禁止。（已建约束时）
ALTER TABLE Students1
  DISABLE CONSTRAINT sex_chk1;

（2）学生1()允许约束Enabling a Constraint
例9.2_31将表Students1的约束pnum_uq置为允许。
ALTER TABLE Students1
  ENABLE CONSTRAINT pnum_uq;

4．获得有关约束的信息
例9.2_ 32 通过查询视图user_constraints，获取表teachers1的owner、constraint_name、constraint_type、status等信息。
SELECT owner,constraint_name, constraint_type,status
  FROM user_constraints
    WHERE table_name = 'TEACHERS1';

9.2.4  修改表名Renaming a Table
例9.2_ 33将表Departments1的表名修改为Dep1。
RENAME Departments1 TO Dep1;

9.2.5  删除表
例9.2_34  使用TRUNCATE语句删除表Dep1中的所有记录。
TRUNCATE TABLE Dep1;

TRUNCATE TABLE Teachers1;
TRUNCATE Teachers2
TRUNCATE Teachers3
TRUNCATE Teachers4
TRUNCATE Students1
TRUNCATE Students2

例9.2_35 使用DROP TABLE语句删除表Dep1
DROP TABLE Teachers2;

9.3  索引
9.3.1  建立索引

例9.3_1在Students1表name列上建立索引name_idx。
CREATE INDEX name_idx ON Students1(name);

例9.3_2在Teachers1表wage列上建立索引wage_idx。
CREATE INDEX wage_idx ON Teachers1(wage);

例9.3_3在Students1表register_date列上建立索引register_date_idx。
CREATE INDEX register_date_idx ON Students1(register_date);

例9.3_4在Students1表phone_number列上建立索引phone_number_idx
CREATE UNIQUE INDEX phone_number_idx ON Students1(phone_number);

9.3.2  获得索引信息Get information on an index from the data dictionary
1．获得索引的基本信息Getting Information on Indexes
例9.3_5 利用数据字典获得表Students1 Teachers1索引的基本信息。其中包括index_name, table_name, uniqueness, status等。
SELECT index_name, table_name, uniqueness, status
  FROM user_indexes
    WHERE table_name IN ('STUDENTS1', 'TEACHERS1');

2．获得索引中列的信息Getting Information on the Indexes on a Column
例9.3_6利用数据字典获得表Students1 Teachers1索引中列的信息。其中包括index_name, table_name, column_name等。获得索引中列的信息
COLUMN table_name FORMAT a15
COLUMN column_name FORMAT a15
SELECT index_name, table_name, column_name
  FROM user_ind_columns
    WHERE table_name IN ('STUDENTS1', 'TEACHERS1');

9.3.3  修改索引Modify an index
例9.3_7  将索引name_idx的名字修改为Students1_name_idx。
ALTER INDEX name_idx RENAME TO Students1_name_idx;

9.3.4  删除索引Drop an index

例9.3_8  删除索引Students1_name_idx。
DROP INDEX Students1_name_idx;

DROP INDEX wage_idx;
DROP INDEX register_date_idx;
DROP INDEX phone_number_idx;

9.4  视图
9.4.1  建立视图Create and use a view/Creating and Using Simple Views
例9.4_1在Departments表上建立视图Departments_view，视图Departments_view映射表Departments 的全部行列。
CREATE VIEW Departments_view AS
  SELECT * FROM Departments;

例9.4_2 在Students表上建立视图Students_view，视图Students_view映射表Students 的全部列和其中男生的记录行。
CREATE VIEW Students_view AS
  SELECT * FROM Students
    WHERE sex='男';

例9.4_3在Teachers表上建立视图Teachers_view，视图Teachers_view映射表Teachers 的teacher_id、name、bonus、wage等列和其中职称为教授的记录行。
CREATE VIEW Teachers_view AS
  SELECT teacher_id, name, bonus, wage
    FROM Teachers
      WHERE title='教授';

例9.4_4在Teachers表上建立视图Teachers_view1，视图Teachers_view1映射表Teachers 的全部列和其中部门号为101和102的记录行。
CREATE VIEW Teachers_view1 AS (
  SELECT * FROM Teachers WHERE department_id=101
UNION
  SELECT * FROM Teachers WHERE department_id=102);

例9.4_5在Teachers和Departments表上建立视图Teachers_view2，视图Teachers_view2映射表Teachers 的teacher_id, name列，表Departments 的department_name列。
CREATE VIEW Teachers_view2 AS
  SELECT t.teacher_id, t.name, d.department_name
    FROM Teachers t, Departments d
      WHERE t.department_id=d.department_id;

9.4.2  使用视图Create and use a view/Creating and Using Simple Views
1．查询Performing a SELECT on a View
例9.4_6 在视图Departments_view上查询其中的所有行列。
SELECT * FROM Departments_view;

例9.4_7在视图Students_view上查询其中的student_id、name、dob列和全部行。
SELECT student_id, name, dob
  FROM Students_view;

例9.4_8 在视图Teachers_view1上查询职称为讲师的教师信息，其中包括teacher_id, name、title、department_id列。
SELECT teacher_id, name, title, department_id
  FROM Teachers_view1 WHERE title='讲师';

例9.4_9在视图Teachers_view2上查询其中的所有行列。
SELECT * FROM Teachers_view2;

2．插入Performing an INSERT Using a View
例9.4_10 利用视图Students_view插入李石强同学的记录。
INSERT INTO Students_view
  VALUES(10177,NULL,'李石强', '男', '07-1月-1989','计算机');

SELECT * FROM Students；

例9.4_11 利用视图Teachers_view插入教师孔夫之记录。
INSERT INTO Teachers_view VALUES (10168, '孔夫之', 1000, 3000);

SELECT * FROM Teachers;

3．修改
例9.4_12 利用视图Students_view修改学号为10177的学生出生日期。
UPDATE Students_view
  SET dob = '07-2月-1989' WHERE student_id = 10177;

4．删除
例9.4_13 利用视图Students_view删除学号为   的学生信息。
DELETE FROM Students_view WHERE student_id = 10177;
 
DELETE FROM Teachers_view WHERE teacher_id = 10168;

9.4.3  获得视图信息Get details of a view from the data dictionary
例9.4_14 显示视图students_view的结构。
DESCRIBE students_view;

例9.4_15  通过查询数据字典中的user_views视图，获得student_view视图的视图名（view_name）、定义视图子查询的字符个数（text_length）以及定义视图子查询的正文（text）。
SELECT view_name, text_length, text FROM user_views;

9.4.4  修改视图Modify a view

例9.4_16  修改视图Student_view。
CREATE OR REPLACE VIEW Student_view AS
  SELECT student_id, name, specialty
    FROM Students WHERE sex='男';

9.4.5  删除视图Drop a view
例9.4_17 删除视图Departments_view。
DROP VIEW Departments_view;

DROP VIEW Students_view;
DROP VIEW Teachers_view;
DROP VIEW Teachers_view1;
DROP VIEW Teachers_view2;