Oralce笔记01

1、使用子查询创建表

(1)指定要创建表的字段
create table myemp(编号,姓名,年薪) as select empno, ename, sal*12 from emp;

(2)当查询字句中有表达式时（sal * 12）必须指定别名(annsal)
create table myemp2 as select empno ,ename, sal*12 annsal from emp;

2、修改表结构

(1)修改表结构-添加字段
alter table test1 
add (
   grade number(3), 
   phone varchar2(20) default '无'
);

(2)修改表结构 -修改字段
alter table test1
modify (
   grand number(2),
   phone varchar2(20) default '010-12345678'
)


注释：修改操作会受到当前表中已有数据的影响，当需修改的字段中只包含空值时，类型、大小都可以修改，否则有可能会修改失败.
      修改的缺省值设置,只对此后新插入的记录有效

(3)修改表结构  - 删除字段

alter table test1
drop (
  grand,phone
) 

注释:如果要删除的字段有某些约束事，可能会删除失败.

3、清空表中的数据

trancate table test1;
注释：此语句一经执行是清除所有记录，无法按照条件删除，而且一经执行无法回滚。

4、重命名表
rename old_name to new_name;
注释：表重命名后，表中的记录不会受影响.

5、非空约束
create table student (
   sid number(3) not null,
   sname varchar2(20),
   birth date constraint student_birth_nn not null
);

注释：birth 字段的非空约束与sid的效果是一样的，只不过sid没有指定约束名，而student_birth_nn 是我们为 birth字段指定的约束名. 非空约束只能在字段级定义

6、查看定义在某一张表上的所有的约束条件

select * from user_constraint where low(table_name) = 'student';
注释：如果区分大小写，可以先把表名转化为小写后在进行查询.

7、唯一性约束(unique)
（1）字段级约束
create table student (
  sid number(3) unique,
  sname varchar(20)

)
(2)表级约束
create table student (
  sid number(3) ,
  sname varchar(20),
  constraint student_sid_un unique(sid)
  --联合字段唯一约束
  --constraint student_sid_un unique(sid,sname) 
)

注释：唯一约束用于确保所在的字段或字段组合不出现重复值、唯一约束字段允许出现空值、oracle会自动为唯一约束创建对应的唯一性索引、唯一约束可以在表级定义、也可以在字段级定义.


8、主键约束(primary key)
（1）字段级约束
create table student (
  sid number(3) primary key,
  sname varchar(20)

)
(2)表级约束
create table student (
  sid number(3) ,
  sname varchar(20),
  constraint student_sid_pk primary key(sid),
  --联合主键约束
  --constraint student_sid_pk primary key(sid,sname) 
)

注释：主键用于标示表中唯一的一条记录.主键约束相当于：唯一性约束与非空约束的组合. Oralce会自动为主键字段创建对应的唯一性索引.一个表中只允许有一个主键，可以是单个字段或者多个字段的组合。主键约束可以在字段级定义也可以在表级定义。
关于联合主键：
(1)由多个字段组成的主键称为联合主键
(2)联合主键中的每个字段值都不得为空
(3)联合主键组合的值不能出现重复
(4)联合主键只能定义为表级约束

9、外键约束
关键要素：
(1)外键用于确保两个相关字段之间的参照关系，以实现参照完整性约束
(2)外键约束通常构建于来自两个不同表之间的字段。
(3)字表外键列的值必须在主表参照列值得范围内，或者为空。
(4)外键参照的必须是表的主键或唯一键
(5)主表外键值被字表参照时，相关字表记录不允许被删除。

10、检查约束(check)
(1)定义每一行都必须满足的条件
(2)以条件表达式的形式给出数据需要符合的条件
(3)条件表达式中不允许出现以下内容：
   currval,nextval,level,rownum等伪列
   sysdate,uid,user,userenv等函数.
   对其他字段值的引用
(4)只能在字段级定义
举例：
create table test1(
  name varchar2(20),
  age  number check(age>=0 and age <=120)
);
check的检查非空约束与非空约束not null类似 例如name属性可以表示为：

--name varchar2(20) check(name is not null)


11、查看约束:
(1)表级：select * from user_constraints;
(2)字段级：select * from user_cons_columns;

12、建表后添加约束：(表级约束)
基本语法：
alter table table_name
add[constraint constraint_name] constraint_type(column);
举例：
create table student(
  sid number(2),
  name varchar2（20）
);
alter table student
add constraint sutdent_pk_constraint primary key(sid);

特例：非空约束必须使用modify子句添加
alter table student 
modify(name  not null);
此处的modify相当于修改字段，我们可以添加更多的约束.
alter table student
modify (name char(15) defalut 'Tom' not null);

13、删除约束
基本语法：alter table table_name drop constraint constraint_name;

删除约束举例：
create table student(
  sid number（10）,
  name varchar2（20）,
  constraint student_sid_pk primary key（sid） 
);
alter table student drop constraint student_sid_pk;

删除主键约束的另一种方式:
alter table student drop primary key; 因为主键约束一张表中只允许有一个，所以可以不用指定名称，直接删除。

14、删除级联约束
说明：如果在删除约束时，还存在与该约束相关联的其他约束，则删除会失败，此时可以使用cascade关键字将其他关联约束一并删除。

--员工信息表
create table empinfo(
  sid number(10) constraints empinfo_constraint_pk primary key,
  ename varchar2(20)
  --
);
--员工薪水表
create table salary(
 id number(10) references empinfo(sid)
 --
);

alter table empinfo drop constraints empinfo_constraint_pk cascade;

在删除表中字段时，如果该字段处于多字段联合约束条件(联合主键，联合唯一键，存在参照当前字段的外键)中，则删除会失败，此时可以使用cascade constraints字句将与字段相关联的约束一并删除。
举例：

create table record(
   student_id number(3),
   subject_id varchar2(30),
   record number(3),
   constraint record_stuId_subId_pk primary key(student_id,subject_id)
);

alter table record drop (student_id) cascade constraints;

15、约束的启用与禁用
(1)禁用约束：在alter table 语句中还可以使用 disable constraint 来禁用相关约束,也可以使用cascade将相关联约束一并禁用。
基本语法：
alter table table_name disable constraint constraint_name[cascade];
举例：
create table student(
  sid number(3),
  sname varchar2(20),
  constraint constraint_sid_pk(sid)
);

alter table student disable constraint constraint_sid_pk;

(2)启用约束：
启用约束与禁用约束语法类似，只需要将disable关键字改为enable

说明：与禁用约束不同的是，此时无法使用cascade关键字一并启用相关联的约束。

16、视图
视图的概念：
(1)视图是从一个表或者多个表(视图)中提取数据而来。
(2)视图是一种虚拟表(并不存储物理数据，而是从其他表中提取数据）。
(3)视图一经创建，可以当做表来使用。

使用视图的好处：
(1)简化数据查询(可以将多个表之间的复杂查询定义为视图)
(2)提高运行效率(视图的定义是编译后的sql存在在Oracle服务器端的，这要比我们直接写的sql,Oracle服务器每次都要编译后执行速度要快);
(3)屏蔽数据库表结构，实现数据逻辑独立性。
(4)限制数据库访问
(5)在相同数据上提供不同的视图，便于数据共享。
简单视图与复杂视图：

[img]http://dl.iteye.com/upload/attachment/545103/dc9ddbfd-3e40-33f6-b4b2-6de3c6990730.jpg[/img]

创建视图/删除视图

通过在create view语句中嵌入子查询的方式创建视图
create[or replace] view [schema.]view[字段1,字段2] as subquery
举例：(create or replace:创建或替换：为了防止已经存在的视图一般加上replace关键字，如果有则替换已存在的视图)
create or replace view  myview1(编号,姓名,职工,工资)
as select empno,ename,job,sal from emp where empno = 20;

查看视图结构：
desc myview1;
删除视图：
drop view myview1;

查看视图相关信息：
desc myview1;

特别说明：
(1)在上例中如果myview1后面不跟字段名称(编号,姓名,职工,工资)，默认字段名称与子查询字段名称一致。
(2)可以在子查询后面加上字段别名 例如上例子查询可写为：as select empno 编号,enmae 姓名, job 职工, sal 工资 ..........;
(3)如果查询字句中有表达式，则必须制定别名，否则报错 例如字句：

.........as select empno ,ename,job,sal*12 年薪 from emp .........

如果 sal * 12不指定别名 是会报错的.

17、查询视图

select * from myview1;

原理图解析：客户端的每一次访问视图，Oracle服务器都会查询一下底层的物理表，所以基表中数据的更新，会立刻反映到查询结果上。

[img]http://dl.iteye.com/upload/attachment/545122/9642ecc7-50bc-3245-97d4-a617347588c7.jpg[/img]

18、强制创建视图
可以使用force选项，强制创建视图.
语法格式:
create [or replace][force | noforce] view [schema.] view_name[(alias[,aliaxs]...)] as subquery;
举例：
create or replace view myview2
as select empno, ename,job,sal from emp2 where deptno = 20;
说明：假定现在我们还没有给出emp2表的定义，那使用force选项创建视图，该视图是可以创建成功的。当不加该关键字时默认使用noforce选项。

19、创建复杂视图
复杂视图的创建主要是体现在子查询的复杂性：
例一、
create or replace view v_sal(deptno,maxsal,minsal,avgsal)
as select deptno,max(sal),min(sal),avg(sal) from emp group by deptno;

例二、
create or replace view v_emp(工号,姓名,职位,年薪,"工龄(月)",部门编号,部门名称)
as select empno,ename,job,sal * 12,months_between(sysdate,hiredate,dmpno,dname) from emp, dept where emp.deptno = dept.deptno;

20、更新视图
可在更新视图上进行DML操作，可以修改基表中的数据
(1)可更新视图的定义中，不能使用分组函数,group by 字句，distinct关键字,rownum等伪列，字段的定义不能为表达式。
(2)由两个以上基表中导出的数据不可更新。
(3)基表中非空的列在视图定义中未包括，在不可在视图上进行insert操作。

在视图上进行DML操作，语法与在表上相同：insert update,delete（开发中并不常用）

21、创建只读视图
语法只需要在创建视图时使用with read only选项即可。创建的只读视图不能进行任何更新。
create or replace view myview2
as select empno ,empname,job,sal from emp2 where deptno =20
with read only;