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 test1modify (   grand number(2),   phone varchar2(20) default '010-12345678')注释：修改操作会受到当前表中已有数据的影响，当需修改的字段中只包含空值时，类型、大小都可以修改，否则有可能会修改失败.      修改的缺省值设置,只对此后新插入的记录有效(3)修改表结构  - 删除字段alter table test1drop (  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_nameadd[constraint constraint_name] constraint_type(column);举例：create table student(  sid number(2),  name varchar2（20）);alter table studentadd constraint sutdent_pk_constraint primary key(sid);特例：非空约束必须使用modify子句添加alter table student modify(name  not null);此处的modify相当于修改字段，我们可以添加更多的约束.alter table studentmodify (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)在相同数据上提供不同的视图，便于数据共享。简单视图与复杂视图：

创建视图/删除视图通过在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服务器都会查询一下底层的物理表，所以基表中数据的更新，会立刻反映到查询结果上。

18、强制创建视图可以使用force选项，强制创建视图.语法格式:create [or replace][force | noforce] view [schema.] view_name[(alias[,aliaxs]...)] as subquery;举例：create or replace view myview2as 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 myview2as select empno ,empname,job,sal from emp2 where deptno =20with read only;