Oracle——基本表

一、特殊的数据表dual

   dual表实际属于系统用户sys，具有了数据库基本权限的用户，均可查询该表的内容，如下所示：

            

     分析查询结果可知，dual表仅含有一行一列。该表并非为了存储数据而创建的，其存在的意义在于提供强制的数据源。主要用来选择系统变量或是求一个表达式的值。
   在oracle中，所有查询语句必须满足select column_name from table_name的格式。但是，在某些场景下，数据源table_name并不明确。例如，函数sysdate()用于返回当前日期，那么在SQL命令行下调用该函数时，很难有明确的数据源，此时即可使用dual表。
 --获得当前时间

SELECT sysdate FROM dual;

二、表信息查询

     查用户的表在user_tables；主键名称、外键在user_constraints；索引在user_indexes。但主键也会成为索引，所以主键也会在user_indexes里面。

--查询用户创建的对象
select * from user_objects;
--查看用户下所有的表  
select * from user_tables; 
--查询用户段信息
select * from user_segments;
--查看某表的大小
select sum(bytes)/(1024*1024) tablesize from user_segments where segment_name='&table_name';
--查找约束条件
select * from user_constraints;
--查看约束被约束的字段信息
select * from user_cons_columns
--查看索引信息
select * from user_indexes order by table_name;
--查看索引被索引的字段信息
select * from user_ind_columns;
--查询用户下字段信息
select * from col;
select * from user_tab_columns;
--查询某个表的字段
select * from col where tname='&Table_name';
select * from user_tab_columns where table_name='&Table_name'; 

在oracle的SQL命令行下，可以利用describe命令来查看已有数据表的表结构，如下查看dual表。

SQL> desc dual
Name  Type        Nullable Default Comments 
----- ----------- -------- ------- -------- 
DUMMY VARCHAR2(1) Y      

三、操作数据表结构

      不论创建表还是约束，与SQL Server基本相同。

表

创建表时的命名规则和注意事项:
 1)表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#；可以用中文的字段名, 但最好还是用英文的字段名
 2)大小写不区分
 3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来
 4)用和实体或属性相关的英文符号长度有一定的限制

 5)创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
 6)建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引
 7)一个表的最多字段个数也是有限制的，254个.
创建表格语法:     
create table 表名
(       
  字段名1  字段类型  [默认值][约束条件],        
  字段名2  字段类型  [默认值][约束条件]
)
[tablespace 表空间名]
1) 默认值 例如 : 日期字段 DEFAULT SYSDATE 这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间。在Oracle中default是一个值，而SQL Server中default是一个约束，因此Oracle的default设置可以在建表的时候创建。
2) 约束条件 例如: 非空 NOT NULL ，不允许重复 UNIQUE ，关键字 PRIMARY KEY ，按条件检查 CHECK (条件)， 外键 REFERENCES 表名(字段名)。约束名如果在建表的时候没有指明，系统命名规则是SYS_Cn(n是数字)
3) tablespace如果不特别指定，将使用该用户的默认表空间。

--建立部门表
create table dept
(  
   deptno number(3) primary key,  
   dname varchar2(10),  
   loc varchar2(13)   
);  
--建立雇员表      
create table employee_info
(  
   empno number(3),  
   deptno number(3),  
   ename varchar2(10),  
   sex char(1),  
   phone number(11),  
   address varchar2(50),  
   introduce varchar2(100)  
)
TABLESPACE LYTP

--查看用户下所有的表  
select * from user_tables; 
--重命名表
rename dept to dt;
--转移表空间
alter table employee_info move tablespace TABLESPACE1;
--向表添加注释 
comment on table employee_info is '雇员信息表';
--通过user_tab_comments视图获得对表的注释   
select * from user_tab_comments where table_name='EMPLOYEE_INFO';
--删除表  
drop table employee_info;

字段

1、添加字段的语法：alter table 表名 add (列名 datatype [default value][null/not null],….);
   注：此处没有column关键字；小括号内是列及列的数据类型；可以一次性为表增加多个列，各列之间使用逗号进行分隔。
     新添加的列不能定义为 “not null”。基本表在增加一列后，原有数组在新增加的列上的值都被定义为空值。

2、删除字段的语法：alter table 表名 drop column (列名)
    注：此处必须添加column，才能表示删除的目标是一个列。
3、修改字段的语法：alter table 表名 modify (列名)(修改内容)

--查看某个表中的字段
select * from user_tab_columns where table_name='DEPT';
--向列添加注释 
comment on column employee_info.ename is 'the name of employees'; 
comment on column dept.dname is 'the name of department';
-- 通过user_col_comments视图获得对表列的注释： 
select * from  user_col_comments where table_name='EMPLOYEE_INFO';  
--添加列
alter table employee_info add id varchar2(18);  
alter table employee_info add hiredate date default sysdate not null;
--删除列
alter table employee_info drop column introduce;
--修改列名
alter table dept rename column location to loc;
--修改列的长度 
alter table dept modify loc varchar2(50);
--修改列的精度 
alter table employee_info modify empno number(2);
--修改列的数据类型
alter table employee_info modify sex char(2);
--添加/修改默认值default
alter table employee_info modify hiredate default sysdate+1;
alter table employee_info modify sex char(2) default 'M';

  对于调整数据表结构来说，要特别注意严谨性。列的数据类型的修改，有可能会影响应用程序对数据库进行存取；而列的删除和重命名更需要检查应用程序是否会出现关联性错误。

约束

1、完整性约束主要有3种子句：主键子句（primary key）、外键子句（foreign key）和检查子句（check）
 有个constraint_type，他具体指下面几种类型:  
C:check,not null  
P:primary key  
R:foreign key  
U:unique  
V:check option on a view 
O:read only on a view 
2、添加约束使用alter table命令：alter table 表名 add constraint 约束名 约束内容。
-增加主键     
alter table 表名 add constraint 主键名 primary key (字段名1) [using index tablespace 表空间名];

-增加外键    
alter table 表名 add constraint 外键名 foreign key (字段名1) references 关联表 (字段名2)[on delete cascade];
-添加唯一约束

alter table 表名 add constraint 唯一约束名 unique (字段名1,字段名2,……) [using index tablespace 表空间名];

-添加检查

alter table 表名 add constraint 检查名 check (检查内容);

-删除约束

alter table 表名 drop constraint 约束名

3、Oracle数据库会为表的主键和包含唯一约束的列自动创建索引，如果不特别指定，那么这个索引的表空间和表格的空间是一样的，但是我们不建议放在一起。

4、Foreign Key的可选参数ON DELETE CASCADE 在创建Foreign Key时可以加可选参数: ON DELETE CASCADE它的含义是如果删除外键主表里的内容，子表里相关的内容将一起被删除. 如果没有ON DELETE CASCADE参数，子表里有内容，父表里的主关键字记录不能被删除掉.
5、如果数据库表里有不满足的记录存在，建立约束条件将不会成功.

--查找某个表约束条件
select * from user_constraints where table_name='EMPLOYEE_INFO';
--查看某表有关列的约束信息
select * from user_cons_columns where table_name='EMPLOYEE_INFO';
--我们将user_constraints视图与user_cons_columns视图连接起来  查看约束都指向哪些列  
select ucc.column_name,ucc.constraint_name,uc.constraint_type,uc.status  
from user_constraints uc,user_cons_columns ucc  
where uc.table_name=ucc.table_name and  
      uc.constraint_name=ucc.constraint_name and  
      ucc.table_name='EMPLOYEE_INFO';  
--添加主键primary key 
alter table employee_info add constraint pk_emp_info primary key(empno);
--删除主键  
alter table employee_info drop constraint PK_EMP_INFO
--添加外键foreign key 
alter table employee_info add constraint fk_emp_info foreign key(deptno)  references dept(deptno); 
--添加检查条件check
alter table employee_info add constraint ck_emp_info check (sex in ('F','M'));
--添加非空约束not null
alter table employee_info modify phone constraint not_null_emp_info not null;  
--添加唯一约束unique
alter table employee_info add constraint uq_emp_info unique(phone);
--禁用约束
alter table employee_info disable constraint uq_emp_info;
--启用约束 
alter table employee_info enable constraint uq_emp_info;  
--延迟约束
alter table employee_info drop constraint fk_emp_info;
alter table employee_info add constraint fk_emp_info foreign key(deptno) references dept(deptno) deferrable initially deferred;

带约束建表：

--案例一
CREATE TABLE s_dept
(
  did NUMBER(7) PRIMARY KEY,
  name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL
);
CREATE TABLE s_emp 
(
  eid NUMBER(7)              not null,
  did NUMBER(7)              references s_dept(did)on delete cascade ,
  last_name VARCHAR2(25)     CONSTRAINT s_emp_last_name_nn NOT NULL,
  first_name VARCHAR2(25),
  userid VARCHAR2(8)         CONSTRAINT s_emp_userid_nn NOT NULL CONSTRAINT s_emp_userid_uk UNIQUE, 
  start_date DATE            DEFAULT SYSDATE,
  salary NUMBER(11,2),
  commission_pct NUMBER(4,2) CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN(10,12.5,15,17.5,20)),
  CONSTRAINT s_emp_eid_userid_uk UNIQUE(eid,userid)  --eid和userid组合唯一
);

通过子查询建表

采用的是子查询方式 create table 新表 as select * from 旧的表 where 条件（旧的表字段满足的条件）
SQL> CREATE TABLE A as select * from B where 1=2; 只要表的结构.
用子查询建表的注意事项： 
1)可以关连多个表及用集合函数生成新表,注意选择出来的字段必须有合法的字段名称,且不能重复。
2)用子查询方式建立的表，只有非空NOT NULL的约束条件能继承过来, 其它的约束条件和默认值都没有继承过来.
3)根据需要，可以用alter table add constraint ……再建立其它的约束条件，如primary key等.