Oracle

1、表

A.表的命名要求和表中列的命名要求

1、必须以字母开头
2、长度不能超过30个字符
3、只能包含 A–Z, a–z, 0–9, _, $, and #
4、不能与数据库中的已有对象重名
5、不能使用Oracle 数据库的保留字

B.建表语句的语法

  1. CREATE TABLE [schema.]table
  2.  (column datatype [DEFAULT expr][, ...]);

从一个子查询快速建表的语法

  1. CREATE TABLE [schema.]table
  2.  (column datatype [DEFAULT expr][, ...]);

常用于复制表

  1. CREATE TABLEA as select * from tableb

只保留表结构，但不要数据

  1. CREATE TABLEA as select * from tableb where 1=2

C.更改表的语法

1.添加列

  1. ALTER TABLE table
  2. ADD (column datatype [DEFAULT expr]
  3.  [, column datatype]...);

2.更改列

  1. ALTER TABLE table
  2. MODIFY (column datatype [DEFAULT expr]
  3.  [, column datatype]...);

3.删除列

  1. ALTER TABLE table
  2. DROP (column);

D.删除表

  1. DROP TABLE tableName;

表被删除后，任何依赖于这张表的视图、Package等数据库对象都自动变为无效

E.更改表名

  1. RENAME oldtablename to newtableName;

F.一次性清空一张表中的所有内容，但保留表结构

  1. TRUNCATE TABLE tableName;

注意TRUNCATE 与DELETE FROM table 的区别：
1）没有Rollback机会
2）HWM标记复位

2、约束

A.约束的概念

Oracle 数据库使用“约束”来阻止对数据库表中数据的不合法的“增删改”动作。
常用的约束有如下几种：
NOT NULL （非空约束）
UNIQUE （唯一性约束）
PRIMARY KEY （主键约束）
FOREIGN KEY （外键约束）
CHECK （自定义约束）

B.约束的创建方法

1、在创建表的时候同时创建约束

  1. CREATE TABLE [schema.]table
  2.  (column datatype [DEFAULT expr]
  3. [column_constraint],
  4. ...
  5. [table_constraint][,...]);
  7. CREATE TABLE employees(
  8.  employee_id NUMBER(6),
  9.  first_name VARCHAR2(20),
  10.  ...
  11.  job_id VARCHAR2(10) NOT NULL,
  12.  CONSTRAINT emp_emp_id_pk
  13.  PRIMARY KEY (EMPLOYEE_ID));

2、另外单独创建约束

  1. ALTER TABLE tablename ADD CONSTRAINT constraintname
  2.  constrainttype (column1,…);
  4.  ALTER TABLE CUX_LES_JE_LINES ADD CONSTRAINT CUX_LES_JE_LINES_PK
  5.  PRIMARY KEY(JE_LINE_ID);

C.外键约束：也称为引用数据完整性约束

外键约束类型：
•REFERENCES：表示列中的值必须在父表中存在
•ON DELETE CASCADE：当父表记录删除的时候自动删除子表中的相应记录.
•ON DELETE SET NULL：当父表记录删除的时候自动把子表中相应记录的值设为NULL

D.删除约束

  1. ALTER TABLE employees
  2. DROP CONSTRAINT emp_manager_fk;
  4. Table altered.
  6. ALTER TABLE departments
  7. DROP PRIMARY KEY CASCADE;
  9. Table altered.

E.失效/生效约束

  1. ALTER TABLE employees
  2. DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
  4. Table altered.
  6. ALTER TABLE employees
  7. ENABLE CONSTRAINTemp_emp_id_pk;
  9. Table altered.

3、视图

视图的概念

有的时候需要关联多张表获得一个查询结果集，为了解决这些复杂的SQL语句的重复使用性，可以把这些结果集创建为视图-View

简单视图和复杂视图的主要区别如下

特性 简单视图 复杂视图
关联的表数量 1个 1个或多个
查询中包含函数 否 是
查询中包含分组数据 否 是
允许对视图进行DML操作 是 否

视图的创建语法

  1. CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
  2.  [(alias[, alias]...)]
  3. AS subquery
  4. [WITH CHECK OPTION [CONSTRAINT constraint]]
  5. [WITH READ ONLY [CONSTRAINT constraint]];
  7. #简单视图
  8. CREATE VIEW empvu80
  9. AS SELECT employee_id, last_name, salary
  10.  FROM employees
  11.  WHERE department_id = 80;
  13.  #复杂视图
  14.  CREATE VIEW dept_sum_vu
  15.  (name, minsal, maxsal, avgsal)
  16. AS SELECT d.department_name, MIN(e.salary),
  17.  MAX(e.salary),AVG(e.salary)
  18.  FROM employees e, departments d
  19.  WHERE e.department_id = d.department_id
  20.  GROUP BY d.department_name;

删除视图

  1. DROP VIEW empvu80;
  3. View dropped.

4、序列

A.序列的概念

有的时候定义某一张表中某一列为主键，当往表中插入数据的时候，对于主键字段的赋值要求唯一性，希望能有个自增长类型的数据库对象，每获取一次，它自动增长，保证下次获取时肯定是不一样的值，这样就方便了，Oracle 数据库提供“序列”这种对象来满足要求。

B.序列的创建

  1. CREATE SEQUENCE sequence
  2.  [INCREMENT BY n]
  3.  [START WITH n]
  4.  [{MAXVALUE n | NOMAXVALUE}]
  5.  [{MINVALUE n | NOMINVALUE}]
  6.  [{CYCLE | NOCYCLE}]
  7.  [{CACHE n | NOCACHE}];

C.序列使用

从序列取值： CURRVAL 取当前值，NEXTVAL取下一个值

  1. INSERT INTO departments(department_id,
  2.  department_name, location_id)
  3. VALUES (dept_deptid_seq.NEXTVAL,
  4.  'Support', 2500);
  5. SELECT dept_deptid_seq.CURRVAL
  6. FROM dual;

D.更改序列定义

  1. ALTER SEQUENCE dept_deptid_seq
  2.  INCREMENT BY 20
  3.  MAXVALUE 999999
  4.  NOCACHE NOCYCLE;

E.删除序列

  1. DROP SEQUENCE dept_deptid_seq;

5、索引

A.索引的概念

在原数据库表中，增加一列有序列，便于执行查找

B.索引创建

  1. CREATE INDEX emp_last_name_idx
  2. ON employees(last_name);
  4. Index created.

C.在什么样的情况下创建索引对加快查询有利

答：查询条件中使用到这个列（或者这个列于其他列的组合），且这个列（或者与其他列的组合）上的数字 范围跨度很大，而大多数情况下要获取的数据的量占整个表的数据总量 小于4%；

D.在什么样的情况下不适合创建索引呢

答: 被查询的表本身就很小，即是是全表扫描也非常快； 或者基于这张表的查询，大多数情况下需要获取 的数据量都超过了总量的4%；或者这张表需要频繁的被更新，建立索引的话会引起索引的频繁更新，从而反而 降低数据库的整体效率。

E.函数索引

当查询语句的Where条件中，对于某些列使用了函数表达式时，普通索引对查询没有帮助，如果想利用索引，则 必须创建函数索引，比如在下面的例子中

  1. SELECT *
  2. FROM departments
  3. WHERE UPPER(department_name) = 'SALES';

对于上述查询语句，如果建立普通索引，比如create index dp_idx2 on departments(department_name) 那么上述SQL执行的时候，Oracle是不会走索引的，需要建立函数索引

  1. CREATE INDEX upper_dept_name_idx
  2. ON departments(UPPER(department_name));

6、同义词

A.同义词的概念

当数据库用户A要访问数据库用户B中的一张表Table1的时候，需要加前缀
select * from B.table1
但要通过DB-LINK访问另一个数据库中的某张表的时候需要加@后缀
select * from table1@db-link-name
为了在程序中能够简化写法，Oracle 提供同义词，也就是可以在A用户下建立一个同义词指向B用户下的 Table1，以后访问的时候可以直接访问这个同义词，而不用加前缀了。

B.同义词的定义

  1. CREATE SYNONYM Table1 for B.Table1
  2. #创建同义词后就可以直接通过创建的名称进行访问
  3. Select * from Table1