数据表对象 (oracle字段类型 建表 约束)

目录

​​1.创建数据表​​

​​1.1数据类型​​

​​1)字符类型​​

​​2)数值数据类型​​

​​3)日期时间数据类型​​

​​4)LOB数据类型​​

​​1.2创建数据表​​

​​1).直接设计表​​

​​2）利用现有的表或者视图建立新表(create table语句嵌套子查询)​​

​​1.3数据表的特性​​

​​1)存储参数​​

​​2)数据块管理参数​​

​​2）initrans 参数​​

​​3)重做日志参数​​

​​4.缓存参数​​

​​2.维护数据表​​

​​2.1增加和删除字段​​

​​2.2修改字段​​

​​2.3重命名表​​

​​2.4修改表空间和存储参数​​

​​1）修改表空间​​

​​2)修改存储参数​​

​​2.5 删除表​​

​​2.6修改表状态​​

​​小笔记1:表空间 表状态 查询小结​​

​​3.数据完整性和约束性​​

​​3.1非空约束​​

​​1.建立表时指定非空约束​​

​​2.建表后使用 alter table modify 语句为已经创建的表删除或重新定义 not null 约束​​

​​3.2主键约束​​

​​建表时指定主键约束​​

​​建表后指定主键约束                                                                                  ​​

​​3.3唯一性约束​​

​​建表时指定唯一性约束​​

​​建表后添加唯一键约束​​

​​3.4外键约束​​

​​3.5禁用和激活约束​​

​​1）在定义约束时禁用​​

​​2)禁用已有约束​​

​​3）启用约束​​

​​3.6删除约束（通用方式 知道约束名即可）​​

​​小笔记2: 对键的查询删除操作小结​​

1.创建数据表

1.1数据类型

1)字符类型

字符类型用于存储包含字母、数字数据的字段。有定长字符串(char)和变长字符串（varchar2）之分

• char数据类型

char类型用于存储固定长度的字符串。一旦定义了char类型的列，该列就会一直保持声明时规定的长度。为其赋予长度较短的值，空余的部分会用空格自动填充；字符串长度大于规定的长度，则oracle会产生错误信息。char类型长度范围1~2000

• varchar2数据类型

varchar2存储变长字符串。该类型字段的长度会根据实际字符数据的长度自动调整；（改列的字符串长度小于定义时的长度不会使用空格填充，保留实际长度）。因此大多数情况下都会使用varchar2类型替换char数据类型

2)数值数据类型

用于存储带符号的整数或浮点数。oracle中的number数据类型具有精度（precision）和范围（scale）。精度指所有数字位的个数，范围指小数位数，两个参数都是可选的。若插入字段的数据超过指定的位数，oracle将自动进行四舍五入。eg:number(5,2),插入3.1415926,则实际上字段中保存的数据为3.14

3)日期时间数据类型

oracle提供的日期时间数据类型是date,可以存储日期时间的组合数据。用date类型可以使用oracle提供的日期时间函数方便地处理数据。

sysdate:获取当前系统时间

to_date('2019-5-29','yyyy-mm-dd'):获取日期类型数据，值为2019-5-29

oracle默认的日期时间格式由初始化参数NLS_DATE_FORMAT指定，一般为dd-mm-yy.可以修改:

--session当前会话有效 scott有资格

alter session set NLS_DATE_FORMAT='yyyy-mm-dd';

select ename,hiredate from emp where hiredate = '1982-01-23';

4)LOB数据类型

LOB数据类型用于存储大型的、未被结构化的数据，例如，二进制文件、图片文件和其他类型的外部文件。LOB类型分为BLOB、CLOB、BFILE

• BLOB

用于存储二进制对象。典型的BLOB可以包括图像、音频文件、视频文件等。在BLOB类型的字段中能够存储最大为128MB字节的二进制对象

• CLOB

用于存储字符格式的大型对象，最大120MB.先将数据转换为Unicode格式的编码，然后将它存储在数据库中

• BFILE

用于存储二进制格式的文件；可以将最大为128MB字节的二进制文件作为操作系统文件存储在数据库外部。BFILE类型的字段中仅仅保存二进制文件指针，并且BFILE字段是只读的，不能通过数据库对其中的数据进行修改。

5）ROWID类型

“伪列类型”，oracle为每个表都设置了一个列，rowid，保存表中每条数据记录的物理地址，select rowid from tablename;可以查询。用rowid来处理数据记录可以获得最快的访问速度。

1.2创建数据表

在用户自己的模式下（oracle下一个数据库有多个用户，用户下才有表。比如实验一创建的数据库YGGL. oracle系统默认提供的数据库ORCL，先有很大的数据库(创建成功若服务不关，特别占内存)，然后在数据库下建立用户，用户下建立表。本学期实验使用的都是默认数据库orcl，操作的基本是样例用户scott下的表）建立一个表，需要有create table的系统权限。如果要在其他用户模式中创建表，则必须有create any table的系统权限。

1).直接设计表

eg:创建一个学生档案信息表students，该表包括了学生编号、学生姓名、性别、年龄、系别编号、班级编号、建档日期等信息

create table students(

stuno number(10) not null, --学号

stuname varchar2(8), --姓名

sex char(2), --性别

age int, --年龄

departno varchar2(2) not null, --系别编号

classno varchar2(4) not null, --班级编号

regdate date default sysdate --建档日期 最后一行不要有多余的, ‘，’表示后面还有列

);

看表结构

desc students;

2）利用现有的表或者视图建立新表(create table语句嵌套子查询)

eg1:建立一个dept表的副本

create table dept_2 as select * from dept; -- 复制数据

create table dept_3 as select * from dept where 1=2; --不复制数据只是复制表结构 让where后面的逻辑表达式为false即可，这样就查不到任何数据，只有一个表结构了

eg2:根据视图建立一个表（虚表变实表）

create table table_emp_dept as select * from view_emp_dept;

desc table_emp_dept;

1.3数据表的特性

常用四个特性及其设置说明

1)存储参数

建表时允许使用storage子句设置存储。也即oracle允许用户指定该表如何使用磁盘上的存储空间。如果仅为表指定了表空间，而没有设置存储参数，则该表将采用所属表空间的默认存储参数。显然默认的不一定适合所有的，此时需要建表时显示指定存储参数以替换表空间的默认存储参数。

建表时通过storage子句来设置存储参数，这样可以控制表中盘区的分配管理方式。对于本地化管理的表空间而言

1.如果指定盘区的管理方式为autoallocate（即自动化管理），则可以在storage子句中指定initial、next、minextents 

  3个存储参数，oracle将根据这3个存储参数的值为表分配的数据段初始化盘区大小，以后盘区的分配将由oracle自动管理。

2.如果指定的盘区管理方式为uniform（即是等同大小管理），这时不能为表指定任何storage子句，盘区的大小将是统一大小。

storage子句3个参数:

1.参数next用于指定为存储表中的数据分配的第二个盘区大小。

    该参数在字典管理的表空间中起作用，而在本地化管理的表空间中不再起作用，因为随后分配的盘区将由oracle自动决定其大      小。

2.参数minextents用于指定允许为表中的数据所分配的最小盘区数目

    同样在本地化管理的表空间中该参数也不再起作用。(因此主要设置的是第3个参数initial)

3.该参数用于为表指定分配的第一个盘区大小，以KB或MB为单位。当为已知大小的数据建立表时，可以将initial设置为一个可以

    容纳所有数据的数值，这样可以将表中的所有数据存储在一个盘区从而避免产生碎片。

eg:创建一个用于存储学生档案信息的students_3数据表(该表的结构与上面的students表的结构相同)，并通过storage子句中的

   initial存储参数为该表分配第一个盘区大小。

创建表空间 MyTbs， 同时创建一个 50MB 的数据文件

create tablespace tbsp_1

    datafile 'E:/APP/ADMINISTRATOR/ORADATA/ORCL/TbspDataFile01.dbf'

    size 50m;

--查看表空间

select * from v$tablespace;

--删除表空间

-- drop tablespace 表空间名称 including contents and datafiles;

drop tablespace tbsp_1 including contents and datafiles;

--建表

create table students_3(

    stuno number(10) not null,         --学号

    stuname varchar2(8),            --姓名

    sex char(2),                    --性别

    age int,                        --年龄

    departno varchar2(2) not null,    --系别编号

    classno varchar2(4) not null,    --班级编号

    regdate date default sysdate    --建档日期  不要傻傻多加，

)tablespace tbsp_1                    --表空间

storage(initial 256k)                --指定为该表分配的第一个盘区大小   此行最后的';'不能有

/

--查看students_3表的存储情况 在user_tables数据字典中查看表students_3的第一个盘区大小

--注意''内必须大写

select initial_extent from user_tables where table_name='STUDENTS_3';

2)数据块管理参数

对于一般不带有LOB类型的数据表而言，一个数据块可以存放表的多行记录，用户可以设置的数据块管理参数主要分为以下两类

(1)pctfree和pctused

这两个参数用于控制数据块中空闲空间的使用方法。

对于本地化管理的表空间而言，若使用segment space management 子句设置段的管理方式为auto(自动)，则oracle会对数据库

的空闲空间进行自动管理。此时不需要设置数据块管理参数pctfree和pctused

若表空间的段管理方式为segment space management manual(手动管理)，则用户可以设置pctfree与pctused参数对数据块中的

空闲空间手工管理。其中

pctfree用于指定数据库中必须保留的最小空闲空间比例，当数据块达到pctfree参数的限制后，该数据块将被标记为不可用，默认值10

   eg:建表时pctfree为20 则会预留20%的空闲空间，这些空闲空间将用于保存更新记录时增加的数据。

         pctfree过小，为现有数据行更新所预留的空间越少，可能导致由于一个数据块小到无法容纳一行记录而产生迁移记录和链接记录。

         pctfree参数值越大，则浪费磁盘空间。

 为表设置pctfree和pctused参数时，pctfree与pctused两个参数值的和必须小于等于100。一般而言，两个参数的和与100相差越大，存储效率就越高。

• 实际应用中:当使用update操作较多，并且更新操作会增加记录的大小时，可以将pctfree值设置得大一点，使得记录变大时新纪录还在原数据块中；而将pctused值设置得小一点，这样在频繁地进行更新操作时，能够减少由于数据块在可用与不可用状态之间反复切换而造成的系统开销。推荐pctfree为20,而pctused为40
• 在实际应用中，当使用insert和delete操作较多时，并且update操作不会增加记录的大小时，可以将pctfree参数设置得比较小，因为大部分更新操作不会增加记录的大小；而pctused参数设置得比较大，以便尽快重新利用被delete操作释放的存储空间。推荐设置参数pctfree为5，而pctused为60

eg:创建表student_4（结构同上），设置其pctfree和pctused参数分别为20和40.

create table students_4(

    stuno number(10) not null,         --学号

    stuname varchar2(8),            --姓名

    sex char(2),                    --性别

    age int,                        --年龄

    departno varchar2(2) not null,    --系别编号

    classno varchar2(4) not null,    --班级编号

    regdate date default sysdate    --建档日期  不要傻傻多加，

)tablespace tbsp_1                    --表空间

storage(initial 256k)                --指定为该表分配的第一个盘区大小   此行最后的';'不能有

pctfree 20

pctused 40;

/

--查看所有的表

select table_name from user_tables order by table_name;

--查看pctfree

select table_name,pct_free from user_tables where table_name='STUDENTS_4';

2）initrans 参数

该参数用于指定一个数据块所允许的并发事务的数目。当一个事务访问表中的一个数据块时，该事务会在数据块的头部保存一个条目，以标识该事务正在使用这个数据块。当该事务结束时，它所对应的条目将被删除。

在创建表时，oracle会在表的每个数据块头部分配可以存储initrans个事务条目的空间，这部分空间是永久的，只能用于存储事务条目。当数据块的头部空间已经存储了initrans个事务条目后，如果还有其他事务要访问这个数据块，oracle将在数据块的空闲空间中为事务分配空间，这部分空间是动态的。当事务结束后，这部分存储空间将被回收以存储其他数据。能够访问一个数据块的事务总数由maxtrans参数决定，在oracle11g中，对于单个数据块而言，oracle默认最大支持255个并发事务。但在实际上，maxtrans参数已经被废弃。下面来看一个关于‘initrans’参数的例子

eg：创建students_5表(该表的结构与上面的students表的结构相同)，并指定在数据块头部存放10个事务条目。

create table students_5(

    stuno number(10) not null,         --学号

    stuname varchar2(8),            --姓名

    sex char(2),                    --性别

    age int,                        --年龄

    departno varchar2(2) not null,    --系别编号

    classno varchar2(4) not null,    --班级编号

    regdate date default sysdate    --建档日期  不要傻傻多加，

)tablespace tbsp_1                    --表空间

storage(initial 256k)                --指定为该表分配的第一个盘区大小   此行最后的';'不能有

pctfree 20

pctused 40

initrans 10                        --数据块管理参数，10个事务条目 不能有';'

/

--查询ini_trans和max_trans参数值

--在user_tables数据字典中查询students_5表的ini_trans和max_trans参数值

select ini_trans,max_trans from user_tables where table_name='STUDENTS_5';

3)重做日志参数

重做日志记录了数据库中数据的改变情况，发生故障时可以在重做日志中获取被操作的数据。这样防止数据丢失

当使用create table语句创建表时，如果使用了nologging子句，则对该表的创建、删除、修改等操作（即DDL操作）就不会记录到日志中，但对该表进行DML操作时（如insert,update，delete等）时，系统仍然会产生重做日志记录。在创建表时，默认情况下使用logging子句（不是nologging），这样对该表的所有操作（包括创建、删除、重命名等操作）都会被记录到重做日志中。

在决定是否使用nologging子句时，用户必须综合考虑所获取的收益和风险。使用nologging子句时，可以节省重做日志文件的存储空间，并减少创建表需要的时间。但如果没有在重做日志文件中记录对表的操作，可能会无法用数据库恢复操作来恢复丢失的数据。

eg:创建students_6表(该表的结构与上面的students表的结构相同)，并且在创建该表时使用nologging子句，使用户对该表的创建

删除、修改等操作不被记录到重做日志文件中。

create table students_6(

    stuno number(10) not null,         --学号

    stuname varchar2(8),            --姓名

    sex char(2),                    --性别

    age int,                        --年龄

    departno varchar2(2) not null,    --系别编号

    classno varchar2(4) not null,    --班级编号

    regdate date default sysdate    --建档日期  不要傻傻多加，

)tablespace tbsp_1                    --表空间

storage(initial 256k)                --指定为该表分配的第一个盘区大小   此行最后的';'不能有

pctfree 20

pctused 40

initrans 10                        --数据块管理参数，10个事务条目

nologging                        --对ddl操作不产生日志 本行也不能有';'

/

4.缓存参数

当在oracle中执行全表搜索时，读入缓存中的数据块将会存储在lru列表的最近最少使用的一端。这意味着如果进行查询操作，并且必须向缓存中存储数据时，就会将刚读入的数据块换出缓存。

在建立表时，可以使用cache子句改变这种行为，使得当在使用cache子句建立的表中执行全表搜索时，将读入的数据块放置到lru中最近最常用的一端。这样，数据库缓存中利用lru算法对缓存块进行换入、换出调度时，就不会将属于这个表的数据块立即换出，从而提高了针对该表的查询效率。

在创建表时默认使用nocache子句。对于比较小且又经常查询的表，用户在创建表时指定cache子句，以便利用系统缓存来提高对该表的查询执行效率。

eg:在user_tables数据字典中查看表students_6表是否启用了缓存功能，代码如下：

select table_name,cache

    from user_tables

    where table_name='STUDENTS_6';

2.维护数据表

普通用户只能对自己模式中的表进行修改，想要对任意模式中的表进行修改，用户必须具有alter any table系统权限

2.1增加和删除字段

添加字段:alter table 表名 add(字段名称1 字段描述1，字段名称2 字段描述2...);

eg: 在students_6 表中增加一个province(省份)新字段

alter table students_6 add(province varchar2(11));

eg:一次添加多个字段

alter table staff_sql0 add(salary number(5,2),salary_add number(3,1));

删除字段:

不能删除表中所有字段，也不能删除sys模式中的任何表的字段。仅需要删除一个字段，则必须在字段名前指定关键字column

删除单个字段:alter table 表名 drop column 字段名;            --必须有column

删除多个字段:alter table 表名 drop(字段名1,字段名2...);     --必须有()  不能有column

eg1:在students_6表中删除province(省份)这个字段

alter table students_6 drop column province;

eg2:在students_6表中同时删除sex和age字段

alter table students_6 drop(sex,age);

2.2修改字段

注意:不可随意修改字段，通常修改为兼容类型时只能把数据的长度从短到长改变，否则会出现数据溢出，影响原有数据。

修改字段:alter table 表名 modify 列名 列描述;

eg：将students_6表中的departno字段的长度由2更改为4

alter table students_6 modify departno varchar2(4);

desc students_6;

2.3重命名表

谨慎修改表名，因为改名容易但影响大。修改表名后oracle可以自动更新数据字典中的外键、约束定义以及表关系，但是却不能更新数据库中的存储过程、客户应用、以及依赖该对象的其他对象

重命名表: alter table 表名 rename to 新表名;

eg: 将students_6表重命名为students_7

alter table students_6 rename to students_7;

select table_name from user_tables;

2.4修改表空间和存储参数

1）修改表空间

将一个"非分区"表移动到一个新的表空间，则可以使用alter table...move tablespace MYTBS;

格式:alter table 表名 move tablespace 新表空间名;

eg：将students_7表由表空间tbsp_1 移动到 MYTBS.

--先查询当前表的表空间  管理员身份运行

select table_name,tablespace_name from dba_tables where table_name = 'STUDENTS_7';

--修改表空间

alter table students_6 move tablespace mytbs;

--再次验证

select table_name,tablespace_name from dba_tables where table_name = 'STUDENTS_7';

select table_name,tablespace_name from user_tables where table_name = 'STUDENTS_7'; --也行

2)修改存储参数

修改存储参数主要是修改pctfree和pctused，若改变了这两个参数值，则表中所有数据块都要受到影响，不论是否使用。

格式:alter table 表名 pctfree新值 pctused 新值;

eg:修改表students_7的pctfree和pctused参数

--查询旧值

select table_name,pct_free,pct_used from user_tables where table_name='STUDENTS_7';

--修改

alter table students_7 pctfree 25 pctused 45;

--查询新值

select table_name,pct_free,pct_used from user_tables where table_name='STUDENTS_7';

2.5 删除表

一般用户只能删除自己模式中的表，若要删除其他模式中的表，则必须具有drop any table 系统权限

格式: drop table 表名 [cascade constraints];

注: 1.若被删除的表存在约束、关联的视图和触发器等，则必须使用cascade constraints  级联删除（此时所有引用此表的视图、约束或触发器等都被删除了）

    2.delete from table;--删除表数据

      drop table 表名;--数据数据同时删除结构

eg:删除students_5以及所有引用这个表的视图、约束或触发器等

drop table students_5 cascade constraints;

闪回技术还原表:flashback table 表名 to before drop;

--删除的表还可以闪回进行还原

eg:还原表students_5

--确定表不存在

desc students_5;

--查询表是否在回收站

select object_name,original_name from recyclebin where original_name='STUDENTS_5';

--使用flashback table语句恢复被删除的表

flashback table students_5 to before drop;

--检测确实恢复了

desc students_5;

想要彻底删除而不进回收站（立即释放空间）需要用pureg选项

彻底删除表:drop table 表名 purge;

eg：彻底删除表students_5

drop table students_5 cascade constraints purge;

--验证删除

select object_name,original_name from recyclebin where original_name='STUDENTS_5';

2.6修改表状态

oracle 11g新特性可以将表置为read only(只读)状态，此时表不能执行dml和某些ddl操作。

（oracle 11g之前只能将整个表空间或数据库置于只读，不好）

格式: alter table 表名 read only;

eg:将表students_4置于只读的read only 状态。

准备工作

--查询表空间

select table_name,tablespace_name from user_tables where table_name = 'STUDENTS_4';

--插入数据

insert into students_4 values(1001,'bob','男',18,10,3,sysdate);

修改表状态

alter table students_4 read only;

检测工作

--查询表状态

select table_name,read_only from user_tables where table_name='STUDENTS_4';

--尝试修改

update students_4 set sex='男' where stuno>5000;

报错了，说明，处于只读状态的表不能不能执行DML操作。

但还可以将处于只读状态的表更改表空间，如下

--把students_4 从 tbsp_1 表空间移动到mytbaalter table students_4 move tablespace mytbs;

--查询表空间

select table_name,tablespace_name from user_tables where table_name='STUDENTS_4';

恢复读写状态

--重新置于read writealter table students_4 read write;

--再次检测状态

select table_name,read_only from user_tables where table_name='STUDENTS_4';

小笔记1:表空间 表状态 查询小结

sys登录
创建表空间 MyTbs， 同时创建一个 50MB 的数据文件
create tablespace tbsp_1
  datafile 'E:/APP/ADMINISTRATOR/ORADATA/ORCL/TbspDataFile01.dbf'
  size 50m;

--查看表空间
select * from v$tablespace;

--删除表空间
-- drop tablespace 表空间名称 including contents and datafiles;
drop tablespace tbsp_1 including contents and datafiles;


--查询表空间语句如下
select t.table_name, t.tablespace_name from dba_tables t where t.table_name = 'STUDENTS_7';
select table_name,tablespace_name from dba_tables where table_name = 'STUDENTS_7';
select table_name,tablespace_name from user_tables where table_name = 'STUDENTS_7';
--如果TABLESPACE_NAME为空，则说明为默认表空间，则执行下列语句 注意:用户名需要大写
select username,default_tablespace from dba_users  where username='SCOTT';
--普通用户模式下查
select username,default_tablespace from user_users  where username='SCOTT';

--查询 pctfree
select table_name,pct_free,pct_used from user_tables where table_name='STUDENTS_7';      

3.数据完整性和约束性

通过约束保证存储数据的正确性。约束种类:非空约束、主键约束、唯一约束、外键约束、检查约束、默认约束

和基本sql一样，可以建表时指定约束，也可以表建立好后alter table添加约束，实际应用中通常使用后者（开发阶段不提倡加约束，上线才会补上各种约束）

3.1非空约束

1.建立表时指定非空约束

create table Books(

    BookNo number(4) not null,             --图书编号不为空

    BookName varchar2(20),                --图书名称

    Author varchar2(10),                --作者

    SalePrice number(9,2),                --定价

    PublisherNo varchar2(4) not null,    --出版社编号，不为空

    PublishDate date,                    --出版日期

    ISBN varchar2(20) not null            --ISBN，不为空

);

2.建表后使用 alter table modify 语句为已经创建的表删除或重新定义 not null 约束

格式：alter table 表名 modify 字段名 not null;

           alter table 表名 modify 字段名 null;

--为Books表中BookName(图书名称) 字段设置非空约束

alter table books modify BookName not null;

alter table books modify BookName null;

3.2主键约束

一个表只能有一个主键，主键分行级约束(一个列组成)，和表级约束（多个列组成联合主键）

建表时指定主键约束

创建表Books_1，并为该表定义行级主键约束BOOK_PK(主键列为bookno)

create table Books1(

    BookNo number(4) not null,             --图书编号不为空

    BookName varchar2(20),                --图书名称

    constraint BOOK_PK primary key(BookNo)    --创建主键和主键约束

);

或者

create table Books1(

    BookNo number(4) primary key,             --图书编号不为空

    BookName varchar2(20)                    --图书名称

);

联合主键(表级约束) 必须使用第一种方式设置，方法:括号内些多个列','隔开即可

create table see(

sno char(5) references staff(sno) on delete cascade, --此外键约束 级联删除

hno char(5) references hospital(hno),

sdate date,

primary key(sno,hno,sdate)

);

查询主键

select table_name,constraint_name from user_constraints where table_name='BOOKS1' and constraint_type='P';

-- 删除主键

alter table Books1 drop primary key;

建表后指定主键约束                                                                                  

-- 添加主键 

alter table Books1 add constraint Books_PK primary key(BookNo); 

--constraint Books_PK 可以省略 只是为主键命名而已 省略后会有一个系统默认的名字 非常不好看   先删除主键，然后执行下行

alter table Books1 add primary key(BookNo);

--此时再查询会发现名字很怪

注意:若某列已经有重复数据了，此时再将该列设置为主键会报错

3.3唯一性约束

比主键约束弱一点，不允许重复，但允许null值(且运行多个null值，也即空值可以重复)

建表时指定唯一性约束

create table Members(

    MemNo number(4) not null,                    --会员编号

    MemName    varchar2(20) not null,                --会员名称

    Phone varchar2(20),                            --联系电话

    Email varchar2(30),                            --电子邮件地址

    QQ varchar2(20) constraint QQ_UK unique,    --QQ号，并设置为unique约束

    ProvCode varchar2(2) not null,                --省份代码

    OccuCode varchar2(2) not null,                --职业代码

    InDate date default sysdate,                --入会日期

    constraint Men_PK primary key(MemNo)        --主键约束列为MemNo

);

查看外键约束

select table_name,constraint_name from user_constraints where table_name='MEMBERS' and constraint_type='U';

null值可以重复

insert into Members values(1,'bob','110','123','[email protected]','1','1',null);

insert into Members values(2,'bob','110','123','[email protected]','1','1',null); 

insert into Members values(2,'bob','110','123',null,'1','1',null);

insert into Members values(3,'bob','110','123',null,'1','1',null);

删除外键约束2种放式

alter table members drop unique(QQ);

alter table members add constraint QQ_UK unique(QQ);

alter table members drop constraint QQ_UK;

select table_name,constraint_name from user_constraints where table_name='MEMBERS' and constraint_type='U';

建表后添加唯一键约束

格式:alter table 表名 add [constraint 唯一键名] unique(字段);

--表中email列若有重复值，会添加失败! '无法验证- 找到重复关键字'

alter table members add constraint Email_UK1 unique(email);

alter table members add unique(email);

补充：

check约束

限制某列仅几个特定的值

-- check(列名 in(值1,值2,值3...))

create table business(

bno char(10) primary key,

bname char(50) not null,

btype char(4) check(btype in('企业','事业','私有')),

baddress char(20),

btel char(13)

);

范围值

--此处额外指定了名称 且定义时禁用了约束

create table Student(

    StuNo varchar2(4) not null,

    StuName varchar2(10) not null,

    Age int constraint Age_CK check(age>0 and age<120) disable,

    SchoolName varchar2(50)

);

references外键约束 级联删除   后面加个on delete cascade即可

create table see(

sno char(5) references staff(sno) on delete cascade, --此外键约束 级联删除

hno char(5) references hospital(hno),

sdate date,

primary key(sno,hno,sdate)

);

或

alter table student

add constraint fk_student_class foreign key (sid)

references class (cid)

on delete cascade;

3.4外键约束

建表时指定外键约束

--直接后面紧接references 主表(id)即可

​create table staff(

sno char(5) primary key,

sname char(20) not null,

ssex char(2) check(ssex in('男','女')),

stel char(15) unique,

cno char(15) references card(cno),

bno char(10) references business(bno)

);

--复合外键

--复合外键

create table emp0(

id number,

name varchar(8),

constraint fk_nam1e foreign key(id,name) references emp9(id,name1)

);

建表后插入外键约束

格式: alter table 从表 add constraint 外键约束名 foreign key(外键列名) references 主表(主表主键名)

--1.创建客户表custemer,订单表orders，并为订单表添加外键约束。

create table custemer(

    cusid number primary key,

    cname varchar2(20),

    caddress varchar2(20),

    cphone varchar2(50)

);

--2.创建订单表

create table orders(

    oid number primary key,

    proname varchar2(20),

    procount varchar2(20),

    cid number

);

--3.为订单表添加外键约束orders

alter table orders add constraint fk_order foreign key(cid) references custemer(cusid);

--查看外键约束

select table_name,constraint_name from user_constraints where table_name='ORDERS' and constraint_type='R';

--验证外键约束

insert into orders values(80,'裤子','1000',7);

3.5禁用和激活约束

约束创建后就一直起作用，想让其临时不起作用，可以禁用。禁用也有好处，提高效率，否则每次插入都要检查数据是否满足定义的约束条件。

禁用可以对现有的约束执行，也可以在定义约束时执行

1）在定义约束时禁用

建立学生表 年龄列定义一个disable状态的check约束

create table Student(

    StuNo varchar2(4) not null,

    StuName varchar2(10) not null,

    Age int constraint Age_CK check(age>0 and age<120) disable,

    SchoolName varchar2(50)

);

查询check约束及其状态

select table_name,constraint_name,status from user_constraints where table_name='STUDENT' and constraint_type='C';

2)禁用已有约束

格式: alter table 表名 disable constraint 约束名;

eg:禁用emp_temp表中约束 temp_departid_fk

alter table emp_temp disable constraint temp_departid_fk;

注：在禁用主键约束时，oracle会默认删除约束对应的唯一索引，而在重新激活约束时，oracle将会重新建立唯一索引。如果希望在删除约束时保留对应的唯一索引，可以在禁用约束时使用关键字keep index(通常放在约束名称后)

       在禁用唯一性约束或主键约束时，如果有外键约束正在引用该列，则无法禁用唯一性约束或主键约束。这时可以先禁用外键约束，然后再禁用唯一性约束或主键约束；或者在禁用唯一性约束或主键约束时使用cascade关键字，这样可以级联禁用引用这些列的外键约束。

3）启用约束

格式: alter table 表名 enable [novalidate|validate] constraint con_name;

novalidate:此关键字表示在激活约束时不验证表中已经存在的数据是否满足约束条件，若没有使用该关键字或者使用了validate，则在激活约束时系统将验证表中的数据是否满足约束的定义

eg:激活表Student的Age_CK约束

alter table Student enable constraint AGE_CK;

--验证

select table_name,constraint_name,status from user_constraints where table_name='STUDENT' and constraint_type='C';

3.6删除约束（通用方式 知道约束名即可）

格式: alter table 表名 drop constraint 约束名;

eg:删除student表的Age_CK约束

alter table Student drop constraint Age_CK;

小笔记2: 对键的查询删除操作小结

--查询主键

select table_name,constraint_name from user_constraints where table_name='BOOKS1' and constraint_type='P';

--查询唯一键

select table_name,constraint_name from user_constraints where table_name='MEMBERS' and constraint_type='U';

--查询外键

select table_name,constraint_name from user_constraints where table_name='ORDERS' and constraint_type='R';

--查询check约束及其状态

select table_name,constraint_name,status from user_constraints where table_name='STUDENT' and constraint_type='C';--★查询指定表所有约束

select constraint_name,table_name,constraint_type,search_condition from user_constraints where table_name='STUDENT';

--★查询指定表所有约束所有信息 

set linesize 1000;

select * from user_constraints where table_name='STUDENT';

还可以细细调:

set linesize 1000;

col owner for a8;

col constraint_name for a15;

col table_name for a15;

col search_condition for a25;

col r_owner for a8;

col r_constraint_name for a15;

select * from user_constraints where table_name=upper('insurance');

--查看所有表的约束信息 不要加where限制即可

select constraint_name,table_name,constraint_type,search_condition from user_constraints;

select * from user_constraints;

--删除主键 只有一个 不用外键名

alter table Books1 drop primary key;

--删除唯一键

alter table members drop unique(QQ);

alter table members drop constraint QQ_UK;

查看字段信息

--2)利用sql*plus从数据字典dba_tab_columns查看员工医疗保险系统所有表的字段信息

-- col owner for a8;

-- col table_name for a10;

-- col column_name for a10;

-- col data_type for a10;

-- select owner,table_name,column_name,data_type,data_length  from dba_tab_columns where owner=upper('han');

--或者直接han用户下

col table_name for a10;

col column_name for a10;

col data_type for a10;

select table_name,column_name,data_type,data_length from user_tab_columns;

--暴力查询所有信息  cmd查看屏幕缓冲区宽度调到1000

set linesize 1000;

select * from user_tab_columns;

普通用户登录OEM需要的权限：

--登录 普通用户需要以下权限 否则登录不成功

grant connect,resource,select_catalog_role to han,scott;

建立约束时最好都指定下名称 即:constraint pk_student,方便维护管理

本次实验:

--oracle的数据库太重了，新建一个数据库消耗太大，而oracle先有数据库，一个数据库下有多个用户，用户下再有多个表

--管理员模式下执行
--创建用户 han ,口令tiger   默认表空间MYTBS,临时表空间temp 该用户在表空间上不受限制
create user han identified by tiger
  default tablespace mytbs
  temporary tablespace temp
  quota unlimited on mytbs;

--新建立的用户没有任何权限，甚至是连接数据库的权限。需要一一为其授权
--授予连接和开发数据库的权限
grant connect,resource to han;
--授予建表和session的权限
grant create session,create table to han with admin option;
--授予建立视图、索引、序列、同义词的权限  --报错:权限缺失->index前加个any
grant create view,create ayn index,create sequence,create synonym,create public synonym to han;


--修改定义权限
grant alter any table,alter any index,alter any sequence,alter session to han;
--删除权限
grant delete any table,drop any index,drop any sequence,drop any synonym,drop any table,drop any view to han;
--插入权限
grant insert any table to han;
--查询权限
grant select any sequence,select any table to han;
--修改数据权限
grant update any table to han;

--修改命令行名称 SQL改成:韩竹安
set sqlprompt 韩竹安>;

--查看表  此时肯定为空
select table_name from user_tables;

--创建企业(business)表结构
create table business(
  bno char(10) primary key,
  bname char(50) not null,
  btype char(4) check(btype in('企业','事业','私有')),
  baddress char(20),
  btel char(13)
);

--创建医院(hospital)表
create table hospital(
  hno char(5) primary key,
  hname char(40) not null,
  haddress char(60)
);

--创建医保卡(card)表
create table card(
  cno char(15) primary key,
  ctype char(8) check(ctype in('企业','事业','灵活就业')),
  cmoney number(7,2) not null
);


--员工(staff)表
create table staff(
  sno char(5) primary key,
  sname char(20) not null,
  ssex char(2) check(ssex in('男','女')),
  sbirthday date,
  saddress char(20),
  stel char(15) unique,
  cno char(15) references card(cno),
  bno char(10) references business(bno)
);


--就诊表(see)
create table see(
  sno char(5) references staff(sno) on delete cascade,  --次外键约束 级联删除
  hno char(5) references hospital(hno),
  sdate date,
  primary key(sno,hno,sdate)
);


--消费表(consume)
create table consume(
  cno char(15) references card(cno) on delete cascade,
  hno char(5) references hospital(hno),
  csdate date not null,
  mname char(20),
  mnum int not null,
  csmoney number(7,2) not null
);

alter table consume add primary key(cno,hno,csdate);


--医保表(insurance)
create table insurance(
  idate date,
  cno char(15) references card(cno),
  imoney number(5,2) not null,
  bno char(10) references business(bno),
  primary key(idate,cno)
);

--查看表
select table_name from user_tables;
select table_name from all_tables where owner='HAN';

--查看主键约束
select table_name,constraint_name from user_constraints where table_name=upper('insurance') and constraint_type='P';

--查看外键约束
select table_name,constraint_name from user_constraints where table_name=upper('insurance') and constraint_type='R';

--查看指定表所有约束
select constraint_name,table_name,constraint_type,search_condition from user_constraints where table_name=upper('insurance');

--查询指定表所有约束所有信息 
set linesize 1000;
col owner for a8;
col constraint_name for a15;
col table_name for a15;
col search_condition for a25;
col r_owner for a8;
col r_constraint_name for a15;
select * from user_constraints where table_name=upper('insurance');

--查看所有表信息 不要加where限制即可
select constraint_name,table_name,constraint_type,search_condition from user_constraints;
select * from user_constraints;


--2.查看表结构
--1)利用可视化工具查看所有表的字段信息和约束信息(查看ER图)
--注：
--登录 普通用户需要以下权限 否则登录不成功
grant connect,resource,select_catalog_role to han,scott;

--图

--2)利用sql*plus从数据字典dba_tab_columns查看员工医疗保险系统所有表的字段信息
-- col owner for a8;
-- col table_name for a10;
-- col column_name for a10;
-- col data_type for a10;
-- select owner,table_name,column_name,data_type,data_length  from dba_tab_columns where owner=upper('han');
--或者直接han用户下
col table_name for a10;
col column_name for a10;
col data_type for a10;
select table_name,column_name,data_type,data_length from user_tab_columns;

--暴力查询所有信息  cmd查看屏幕缓冲区宽度调到1000
set linesize 1000;
select * from user_tab_columns;


--3)利用sql*plus从数据字典dba_constraints查看员工医疗保险系统所有表的约束信息
--select * from dba_constraints where owner=upper('han');
--或者直接当前用户下查user_constraints
select table_name,constraint_name,constraint_type,search_condition from user_constraints;
select * from user_constraints;



--3.修改表结构
--(1)利用sqlplus将表"staff_sql"重新命名为"staff_sql0"
--没有对应的表，先建立对应的表
create table staff_sql as select * from staff where 1=2;
set linesize 50;
desc staff_sql;
--修改表名
alter table staff_sql rename to staff_sql0;
desc staff_sql;
desc staff_sql0;


--(2)利用企业管理器为"staff_sql0"表添加"ageINT"字段，利用desc命令查看"staff_sql0"表的字段信息
alter table staff_sql0 add(ageINT int);
desc staff_sql0;

--(3)利用sqlplus为"staff_sql0"表添加"salary number(5,2)"、"salary_add number(3,1)"两个字段,
--利用desc命令查看"staff_sql0"表的字段信息
alter table staff_sql0 add(salary number(5,2),salary_add number(3,1));
desc staff_sql0;

--(4)利用sql*plus向"staff_sql0"表添加"sname"字段唯一性约束，从数据字典dba_constraints查看"staff_sql0"表的约束信息
alter table staff_sql0 add unique(sname);
-- 查看约束信息
-- select constraint_type,constraint_name,search_condition from dba_constraints where owner=upper('han') and table_name=upper('staff_sql0');
-- select * from dba_constraints where owner=upper('han') and table_name=upper('staff_sql0');
-- 其实直接查user_constraints就行了
select constraint_name,constraint_type,search_condition 约束描述 from user_constraints where table_name=upper('staff_sql0');
select * from user_constraints where table_name=upper('staff_sql0');

--(5)利用sql*plus删除"staff_sql0"表上"sname"字段唯一性约束，从数据字典dba_constraints查看"staff_sql0"表的约束信息
--方法1 比较慢
alter table staff_sql0 drop unique(sname);
--方法2 直接利用约束名删除 （先加上约束，在调试)
select constraint_name from user_constraints where table_name=upper('staff_sql0') and constraint_type='U';
--从上面的查询结果中筛选出对应唯一键约束名 SYS_C0011481
alter table staff_sql0 drop constraint SYS_C0011481;
--直接从user_constraints查看
select constraint_name,constraint_type,search_condition 约束描述 from user_constraints where table_name=upper('staff_sql0');

--(6)利用企业管理器
alter table staff_sql0 drop column AGEINT;
desc staff_sql0;

--(7)从"staff_sql0"表删除"salary"、"salary_add"两个字段，并desc查看表结构
alter table staff_sql0 drop(salary,salary_add);
desc staff_sql0;

--(8) 将"staff_sql0"表的"sname"字段长度修改为30，并desc查看表结构
alter table staff_sql0 modify sname char(30);
set linesize 100;
desc staff_sql0;

---------------------------------------------------------------------------------------------
--4.删除表结构
--1)利用sql*plus删除员工表3，看能否成功删除，同时记录外键约束表删除顺序的影响
--没有表 先建表
--员工(staff)表
create table staff3(
  sno char(5) primary key,
  sname char(20) not null,
  ssex char(2) check(ssex in('男','女')),
  sbirthday date,
  saddress char(20),
  stel char(15) unique,
  cno char(15) references card(cno),
  bno char(10) references business(bno)
);


drop table staff3;
--没有数据 不足以引发外键冲突，可以删除

--然而插入数据，直接删除时若引发外键冲突，就无法删除了  staff表外键没有设置级联删除
select constraint_type,constraint_name from user_constraints where table_name=upper('staff3');
--建立约束时最好都指定下名称
--就诊表(see3)
create table see3(
  sno char(5) references staff3(sno) on delete cascade,  --次外键约束 级联删除(级联删除是针对delete的，而不是drop的)
  hno char(5) references hospital(hno),
  sdate date,
  primary key(sno,hno,sdate)
);
insert into staff3 values(1001,'bob','男','1998-05-12','北京','110',null,null);
insert into hospital values(1001,'人民医院','蜀山');
insert into see3 values(1001,1001,sysdate);

--cascase是针对delete的，不是drop 结构被删了不可能级联删除其他表的字段吧...
drop table staff3;
-- 第 1 行出现错误:
-- ORA-02449: 表中的唯一/主键被外键引用
--原因:see3表主键外键关联了staff3表的主键sno,  staff3表有数据被see3表引用，那么staff3表不可先删除
--想要删除被引用表，得先删除see3表中所有引用了staff3表的记录，或者直接删除外键


-- (2)利用企业管理器删除员工表2，看能否删除。从原理上解释原因
-- 原理同上