第八章 使用SET操作符

1．  集合操作符：并(UNION, UNION ALL)，交(INTERSECT)，相减(MINUS)

2．  使用SET操作符的条件：查询列的数量必须相同，列名字可以不同，查询列的前后顺序也要一样，列的数据类型必须属于同一个数据类型组。如果在某个表中不存在那一列，要用TO_CHAR(NULL)，TO_NUMBER(NULL)或0补齐。

如SELECT location_id, department_name “Dep”, TO_CHAR(NULL)  “Location”

   FROM  departments

   UNION

   SELECT location_id, TO_CHAR(NULL) “Dep”, state_province  “Location”

   FROM  locations;

   SELECT employee_id, job_id, salary

   FROM employees

   UNION

   SELECT employee_id,job_id,0

   FROM job_history;

3．  ORACLE中集合操作的注意要点：除了UNION ALL外的三种操作，自动消除重复的行；最终结果使用第一个查询结果的第一个列名，除了UNION ALL外，输出结果是按照第一个查询结果的第一列自动升序排序的

4．  ORDER BY 只能出现在查询子句的最后，即集合处理完毕后再排序；ORDER BY 后面只能跟第一个查询中的列名，或者是用数字表示，1表示根据第一个查询的第一列进行排序，2表示根据第二个查询的第二列进行排序，依次类推。。。

第九章     操纵/改变数据

1．  用DML来操纵和改变数据：INSERT, DELECT, UPDATE

2．  DESC命令可以简单看一个表的约束条件—是否可以为空？

3．  用&替换变量，如：

     INSERT INTO copy_departments(department_id,department_name,location_id)

     VALUES(&dep_id, ’&dep_name’, &location)

4．  批量插入，将一个子查询的结果批量插入到一个表中，如：

     INSERT INTO sales_reps ( id, name, salary, commission_pct)

        SELECT employee_id, last_name, salary, commission_pct

        FROM employees

        WHERE job_id LIKE ‘%REP’;

5．  更新表中的数据

UPDATE table

SET column=value[,column=value,…]

 [WHERE condition];

6．  在UPDATE语句中使用子查询；

7．     DELETE  [ FROM]  table

[WHERE  condition];

不使用WHERE子句中删除所有的记录，使用WHERE子句时删除指定的记录。

8．  在DELECT语句的WHERE子句中使用子查询。

9．  TRUNCATE TABLE table_name 把一个表中的所有记录删除，但是表结构保持不变。

10．              TRUNCATE是一个DDL语言，不像DML语言，可以回滚。TRUNCATE后将高水位重新置位。

11．              一个TRANSACTIONS可以是（1）一捆DML (INSERT,UPDATE,DELECT) 语句；（2）一个DDL (CREATE, ALTER, DROP, RENAME, TRUNCATE )语句；（3）一个DCL (GRANT, REVOKE)语句。

12．              一个TRANSACTIONS的开始是当第一条DML语句执行的时候，结束的时候有4种情况：（1）COMMIT或ROLLBACK语句被执行的时候；（2）一个DDL语句或DCL语句执行的时候，oracle自动将该事务提交，又因为一个DDL语句或一个DCL语句本身就是一个TRANSACTIONS，执行完这个DDL语句或DCL语句时oracle隐式的执行一次commit；（3）用户退出SQL Developer或SQL* PLUS时，这两个客户端自动将没提交的事务commit；（4）oracle系统崩溃时，下次再启动时，oracle去UNDO表空间去查看有没有上次没提交的事务，如果有的话自动将上次未提交的事务rollback.

13．              SAVEPOINT  a  ：用来打标签，ROLLBACK TO a ；

                     COMMIT；

                 从下条语句开始一个Transaction就开始了

                     DELETE….

                     SAVEPOINT A          //设置一个标签A

                     INSERT….

                     UPDATE….

                     SAVEPOINT B

                     INSERT…

                     …

                     ROLLBACK TO B

14．              可以在SQL*PLUS中设置自动commit，SQL>set autocommit on

15．              在SELECT语句中加上FOR UPDATE语句，可以把查询出来的行锁住，其它用户不可以再对该行进行操作，除非你执行COMMIT或ROLLBACK将锁释放掉，如：

　　　　　SELECT　employee_id, salary, commission_pct, job_id

          FROM  employees

          WHERE  job_id = ‘SA_REP’

          FOR UPDATE

          ORDER BY employee_id；

也可以用FOR UPDATE 锁住整个表，如：

      SELECT e.employee_id, e.salary, e.commission_pct

      FROM employee e JOIN departments d

      USING (department_id)

      WHERE job_id = ‘ST_CLERK’ AND location_id = 1500

 FOR UPDATE

 ORDER BY e.employee_id;

用FOR UPDATE OF column_name 去锁定column_name所在的那个表。

第十章   使用DDL语句创建和管理表

1．  数据库对象包括：表，视图VIEW（一般是只读的，并不真正的存储数据，它存储的是查询的语句，你去查看这个VIEW的时候，就去执行VIEW里的SELECT语句，然后从一个或多个表中把数据查询出来，所以说数据是存储在VIEW所存储的基表上），序列SEQUENCT，索引INDEX，同义词SYNONYN（给一个表起个别名），存储过程,触发器等等。

2．  数据库对象的起名规则：不区分大小写，必须以字母开头，最大长度是30个字符，可以是A-Z，a-z，0-9，_，不能包含ORACLE的保留关键字。

3．  CREATE TABLE [schema.] table

                  (column datatype [DEFAULT expr] [,…]);

4．  列的数据类型：VARCHAR2(size)，CHAR(size)，NUMBER(p,s)，DATE，CLOB，BLOB，BFINE，ROWID。

5．  日期数据类型：TIMESTAMP， INTERVAL YEAR TO MANTH， INTERVAL DAY TO SECOND

6．  约束：NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK,一个约束可以在列级别，也可以在表级别。

7．  约束是存储在数据字典中的。

8．  定义列级别的约束：

CREATE TABLE emp1(

                  Employee_id  NUMBER(6)

                          CONSTRAINT emp1_emp_id_pk PRIMARY_KEY,

                  First_name varchar2(20),

                  Job_id varchar2(10) NOT NULL)；

定义表级别的约束：

CREATE TABLE emp1(

                  Employee_id  NUMBER(6),

                  First_name varchar2(20),

                  Job_id varchar2(10) NOT NULL,

 CONSTRAINT emp1_emp_id_pk PRIMARY_KEY(Employee_id));

9．  如何查询一个表的详细的约束信息？

       SELECT dbms_metadata.get_ddl(‘TABLE’,’EMP1’) FROM dual;

10．PRIMARY KEY 是NOT NULL 和 UNIQUE 的合并，UNIQUE是惟一的意思，有UNIQUE约束的列可以是空值，因为两个空值是没法比较的，依然认为它们是不同的值。

11． 如果给一个表中的某一列创建UNIQUE约束，ORACLE自动给这一列创建一个索引，索引的名字与这个UNIQUE约束的名字相同，以后ORACLE都是通过这个索引来快速的判断这一列的值是不是惟一的。

12．如果给一个表中的某一列创建PRIMARY KEY约束，ORACLE也自动给这一列创建一个索引，索引的名字与这个PRIMARY KEY约束的名字相同，如果没有为PRIMARY KEY取名字，则索引的名字由ORACLE自动取，格式为SYS_C### , 以后ORACLE都是通过这个索引来快速的判断这一列的值是不是惟一的。

13．FOREIGN KEY约束可以为空，定义在子表中，ON DELETE CASCADE表明删除父表中的一条记录，则对应的子表中的记录也被删除；ON DELETE SET NULL表明删除父表的一条记录，则子表中对应外键的记录将被置为空。

格式为：

CONSTRAINT 约束名 FOREIGN KEY (子表中的列名)  REFERENCES 父表名（父表中的列名）ON DELETE CASCADE/ON DELETE SET NULL

14．CHECK约束：可以针对某一列施加很多个CHECK约束，格式为：

            ..., salary  NUMBER(2)

              CONSTRAINT emp_salary_min

                   CHECK ( salary > 0 ), …

15．使用子查询创建表：CREATE TABLE table [(column,column)] AS subquery，使用子查询创建表时，原表中除了NOT　NULL之外的约束外，其它的约束都不传递到新表中。

如果只想拷贝表结构，不想拷贝原表中的数据，可以用下列的语句：

　　　CREATE TABLE mytable  AS SELECT * FROM user_objects WHERE 1=0;

16．把一个表变成只读模式：ALTER TABLE table_name READ ONLY;

再把这个表变成普通模式：ALTER TABLE table_name READ WRITE;

17．删除一张表：DROP TABLE table_name;ORACEL 10g之后删除一个表就是把这个表放在回收站里，如果不想放在回收站里，在后面加一个PURGE关键字，如：DROP TABLE table_name PURGE；

第十二章  创建除了表之外的其它对象

1．  视图VIEW中并不存储数据，它只是一个去查询基表数据的一个窗口，它里面只存放查询语句，当需要查询数据的时候再到基表中去查找。但ORACLE中有一种物化视图MATERIAL VIEW，它是为了提高查询效率，将数据临时存放在一张表中，当需要查询数据的时候直接取来，而不需要去查询基表。

2．  CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW view_table[(alias[,alias]…)]

    AS  SELECT employee_id,last_name,salary

         FROM employees

         WHERE department_id=80;

    [WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH  READ ONLY [CONSTRAINT constraint]];

3．简单的视图不一定是只读的，可以对其中的数据进行修改。

4．如果创建视图时，视图的列名用的是子查询基表中的列名，如果修改视图中的一行数据，则对应基表中的数据也被修改了，但如果通过在子查询中使用别名或者在创建视图时指定列的名字，那么修改视图中的一行数据时，是否对应的基表的数据也被修改了呢？待考证。。。

5．修改视图命令：ALTER VIEW 或者CREATE OR REPLACE VIEW

6．简单视图是基于一张表的，复杂视图是基于多张表的，复杂视图中可能包含组函数。

   CREATE OR REPLACE VIEW dept_sum_vu (name,minsal,maxsal,avgsal)

       AS SELECT d.department_name, MIN(e.salary), MAX(e.salary), AVG(e.salary)

          FROM employees e JION departments d

          ON  (e.department_id = d.department_id)

          GROUP BY  d.department_name

7．对视图进行增，删，改操作的规则：对一个简单的视图进行增删改操作就相当于对这个视图所在的基表进行增删改操作。而对一个复杂的视图，如果该视图中包含以下信息：（1）有组函数；（2）GROUP BY；（３）关键字DISTINCT；（４）关键字ROWNUM（行号，这是一个伪列）；（5）该VIEW的某一列是由表达式计算出的；则不可对该视图进行DML操作。

8．如果在创建视图时加“WITH CHECK OPTION CONSTRAINT constraints”子句，对视图进行修改时不能和WHERE 子句发生冲突。

   CREATE OR REPLACE VIEW empvu20

      AS SELECT * FROM employees

         WHERE department_id = 20

         WITH CHECK OPTION CONSTRAINT empvu20_ck;

9．序列SEQUENCES，是一个独立的对象，它可以自动的产生惟一的不同的值，它是一个可以共享的对象，可以在CACHE里缓存，ORACLE.缺省在内在中放20个。

          CREATE SEQUENCE sequence

                  [ INCREMENT BY  n]

                  [START WITH  n]

                  [{MAXVALUE n | NOMAXVALUE}]

                  [{MINVALUE n | NOMINVALUE}]

                  [{CYCLE | NOCYCLE}]

                  [{CACHE n | NOCACHE}];

10．函数NEXTVAL返回下一个SEQUENCES的值，函数CURRVAL返回SEQUENCE的当前值，如果你想取得CURRVAL的值，你必须先调用NEXTVAL。

         SELECT my_seq.NEXTVAL  FROM DUAL;

         SELECT my_seq.CURRVAL  FROM DUAL;

11．使用SEQUENCE：

         INSERT INTO departments(department_id, department_name, location_id)

                 VALUES ( dept_deptid_seq.NEXTVAL, ‘Support’, 2500);

12．SEQUENCE在下面三种情况中可能有间隔：如有回滚操作，系统崩溃掉，在其它的对象中使用SEQUENCE.

13．DROP SEQUENCE name;

14．索引INDEX：创建索引有两种方式，一种是自动方式，加PRIMARY KEY 或UNIQUE约束的时候，ORACLE自动创建索引；另一种是手动方式：

        CREATE [UNIQUE][BITMAP] INDEX index ON table (column[,column]…);

15．索引的使用是由ORACLE的优化器自动选择使用的。

16．以下情况适合使用索引：（1）某一列包含的值非常广；（2）一列包含大量的NULL值；（3）一列或多列经常被在where子句中使用；（4）表很大，要查询的数据只占所有数据的2%-4%。

17．以下情况不适合使用索引：（1）某一列不经常作为WHERE 子句的查询条件；（2）表很小，要查询的数据多；（3）表经常被更新；（4）索引列作为表达式的一部分。

18．DROP  INDEX  index;

18．同义词Synonym：为一个对象起一个别名。

        CREATE  [PUBLIC]  SYNONYM  synonym  FOR  object;