Oracle数据库笔记一（介绍；表空间；用户、权限、角色；完整性约束、主键、唯一、检查、外键约束；非空、唯一性、默认、检查约束；删除主表；数据类型）

（本节包含：介绍关系型数据库；表空间；用户、权限、角色；完整性约束、主键、唯一、检查、外键约束；非空、唯一性、默认、检查约束；删除主表；oracle数据类型）

关系型数据库

在关系模型中，现实世界的实体以及实体间的各种联系均用单一的结构类型即关系来表示

什么是关系型数据库：

1、只包含单一的数据结构----关系

2、一个数据库是由一组数据表（table）构成

3、表中的每一行称为记录（record）

4、表中的每一列称为字段（field）

创建表空间

基于应用、性能和管理方面的考虑，最好为不同的用户创建独立的表空间

通过CREATE TABLESPACE命令创建表空间：

CREATE TABLESPACE user1_tablespace

创建用户

语法：

CREATE USER user

IDENTIFIED BY PASSWORD

【DEFAULT TABLESPACE tablespace】

【TEMPORARY TABLESPACE tablespace】

权限和角色

用户必须赋予相应的权限

1、权限指执行特定类型的SQL命令或访问其它对象的权力

2、分为系统权限和对象权限

    系统权限允许用户执行某些数据

    对象权限允许用户对某一特定对象执行特定的操作

为了简化权限管理，引入了角色的概念

1、角色是具有名称的一组权限的组合

2、常用系统预定义角色

      CONNECT：临时用户

      RESOURCE：更为可靠和正式的用户

      DBA：数据库管理员角色，拥有管理数据库的最高权限

语法：

1、分配权限或角色：

GRANT privileges or role TO user;

2、撤销权限或角色：

REVOKE privileges or role FROM user;

SYS和SYSTEM用户区别

SYS和SYSTEM用户都是Oracle的系统用户，它们都使用SYSTEM表空间，SYS拥有更大的权限

四种完整性约束

提供四种完整性约束来保证完整性

实体完整性：

　　约束方法：唯一约束、主键约束

　　　　　　（同一条记录不能插入两次）

域完整性：

　　约束方法：限制数据类型、检查约束、外键约束、非空约束

　　　　　　（学号要求7位字符，不能不符合要求）

引用完整性：

　　约束方法：外键约束

　　　　　　（从表插入的记录的外键在主表要有相对应的主键）

自定义完整性：

　　触发器：检查信用值

　　约束方法：规则、存储过程、触发器

约束：

约束的目的：确保表中数据的完整性

常用的约束类型

 外键：如主键列值为1，2，3，5，7，则外键则从1，2，3，5，7中选择，（外键可以为null，也可以有多个null，代表还未被分配）

添加约束

语法：

ALTER TABLE 表名

　　ADD CONSTRAINT  约束名  约束类型(具体的约束说明）

• 约束名的取名规则推荐采用：约束类型_约束字段 （约束名方便后面删除）
• 主键（Primary Key）约束：如PK_stuNo
• 唯一（Unique）约束：如UQ_stuId
• 检查（Check）约束：如CK_stuAge
• 外键（Foreign Key）约束：如FK_stuNo

例如：

alter table tb_emp1

　　add constraints pk_id primary key(id); 

（constraint与contraints暂未发现不同点）

删除约束

语法：

ALTER TABLE 表名

　　DROP CONSTRAINT 约束名

 例如删除stuInfo表中的年龄约束

ALTER TABLE stuinfo

　　DROP CONSTRAINT ck_stuAge

主键

用于唯一的标识表中的每一条记录，可以定义表中的一列或者多列。

如，定义author表，该表给每一个作者分配一个“作者编号”，该编号作为数据表的的主键，如果出现相同的值，将提示错误，系统不能确定查询的究竟是哪一条记录；

如果把作者的“姓名”作为主键，则不能出现重复的姓名，这与现实不相符合，因此，“姓名”字段不适合作为主键

create table tb_emp1(

　　id number(11) primary key,  --注意：primary key 后面是逗号，primary key前面没有逗号

　　salary number(9,2));

--查看表（oraclelog不适合）

desc tb_emp1

primary key(name,deptId)  #由name和deptId共同担任主键

外键

外键用来在两个表的数据之间建立连接，它可以是一列或者多列。

一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

外键首先是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。

外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保证数据的一致性和完整性。

主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。

从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

关联：

指的是在关系型数据库中，相关表之间的联系。它是通过相容或相同的属性或属性组来表示的。

子表的外键必须关联父表的主键，且关联字段的数据类型必须匹配，如果类型不一样，则创建子表时，就会出现错误。

设置外键的两种方式：

--第一种，在建表时，直接在最后一行添加

constraint fk_emp_dept1 foreign key(deptId) references tb_dept1(id)

--第二种，在建表时，忘记添加外键，则后面添加

alter table tb_emps

　　add constraints fk_emp_dept1 foreign key(deptId)

　　peference tb_dept1(id)

　　on delete cascade;      --该行表示级联，在删除外键所在行时同时删除主键所在行

删除外键：

alter table tb_emps

　　drop constraints fk_emp_dept1;

在建表时忘加not null：（name设为不为空）

alter table tb_emps modify name not null;

非空约束

非空约束指字段的值不能为空，对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统会报错

唯一性约束（Unique）

要求该列唯一，允许为空，但只能出现一个空值，唯一约束可以确保一列或者几列不出现重复值。

设置唯一性两种方式：

--1、直接在列后加unique

name varchar2(20) unique--2、最后一行加

constraint abc unique(name)

解除唯一性：

alter table tb_dept3

　　drop constraints abc;

unique和primary key的区别：

一个表中可以有多个字段声明为unique，但只能有一个primary key声明；声明为primary key的列不允许为空值，但是声明为unique的字段允许为空值（NULL）的存在（但只能出现一个NULL），其他值也不能相等

默认约束：

指定某列的默认值。如男性同学较多，性别就可以默认为“男”，如果插入一条新的记录时，没有为这个字段赋值，那么系统会自动为这个字段赋值为“男”

设置方法：直接在列后加default 默认值

例如：

deptId number(11) default 1111   （deptId默认值为1111）

检查约束：

为check约束，规定每一列能够输入的值，从而可以确保数值的正确性，例如：性别字段中可以规定只能输入“男”或“女”，此时，可以用到检查性约束

设置方法：

--1、建表时，最后一行添加

constraint ck_gender check(gender='男' or gender='女')

--2、建表时，忘记设置，后面补设

alter table tb_emps add constraint ck_age check (age>=15 and age<=25);

删除约束

alter table tb_emps drop constraints ck_age;

删除被其它表关联的主表

数据表之间存在外键关联的情况下，如果直接删除父表，结果会显示失败，原因是直接删除，将破坏表的参照完整性。

如果必须删除，可以先删除与它关联的子表，再删除父表，但这样会同时删除两个表的数据。

但有的情况下可能要保留子表，这时如果要单独删除父表，只需将关联的表的外键约束条件取消，然后就可以删除父表。

ORACEL数据类型

创建数据库表：通过CREATE TABLE命令来创建

主要有数值数据类型、日期和时间类型以及字符串类型等

1、数值数据类型：包括整数类型和小数类型--->number(m,n)  m：总位数，不是整数位数  n：小数点后几位

2、日期和时间类型：包括DATE和TIMESTAMP（时间戳）

3、字符串类型：包括CHAR、VARCHAR2、NVARCHAR2和LONG五种

日期和时间类型：

DATE用来存储日期和时间，取值范围是公元前4712年到公元9999年12月31日

TIMESTAMP（时间戳）：用来存储日期和时间，与DATE类型的区别是显示日期和时间时更精确

修改日期的格式：如果用户想按照指定的格式输入时间，需要修改时间的默认格式。

例如输入格式为年-月-日，修改SQL语句如下：

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

补充：

--1、将当前时间插入表格中

insert into temp4 values(sysdate);

--2、删除表中记录

delete from temp4;

--3、删除表

drop table temp4;

--4、插入date，使用to_date函数

insert into temp4 values(to_date('2005-01-01 13:15:19','yyyy-mm-dd HH24:mi:ss'));

--5、插入TIMESTAMP，精确到毫秒

insert into temp4 values(to_timestamp('2005-01-01 13:15:19:9999','yyyy-mm-dd HH24:mi:ss:ff'));

数值数据类型

VARCHAR2、NVARCHAR2和LONG类型是变长类型，对于其存储需求取决于列值的实际长度，而不是取决于类型的最大可能尺寸。

例如：一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度

定义字符串（char）在存储时长度是固定的，而变长字符串（varchar2）的存储长度根据实际插入的数据长度而定。

例如：

1、建表

create table temp6(

　　ch char(4)，

　　vch varchar2(4));

2、插入数据

insert into temp6 values('ab','ab');

3、查看长度

select length(ch) from temp6;  --可得到temp6表中ch字段ab的长度为4

select length(vch) from temp6;  --可得到temp6表中vch字段ab的长度为2