Oracle--使用DDL语句管理表、约束详解

悠悠 2021-08-13 19:47 401阅读 0赞

#### 创建表空间 ####

表空间是Oracle数据库的逻辑单元。数据库–表空间。一个表空间可以与多个数据库文件（物理结构）关联一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。

create tablespace zhangs
    datafile 'd:\zhangs.dbf'
    size 100m
    autoextend on
    next 10m;

*  zhangs：为表空间名称
 *  datafile：指定表空间对应的数据文件
 *  size：定义的是表空间的初始大小
 *  autoextend on：自动增长，当表空间存储都占满时，自动增长
 *  next：指定的时一次自动增长的大小

#### 用户 ####

**1. 创建用户**

create user acton
    identified by 930205
    default tablespace zhangs;

*  identified by：用户的密码
 *  default tablespace：表空间名称

Oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。

**2. 用户赋权限**  
新创建的用户没有任何全新，登录后会提示  
![在这里插入图片描述][20190912183142452.png]  
Oracle中已存在三个重要的角色：connect角色，resource角色，dba角色。

*  **CONNECT角色：**  
    是授予最终用户的典型权力，最基本的  
    ALTER SESSION：修改会话  
    CREATE CLUSTER：建立聚簇  
    CREATE DATABASE LINK：建立数据库连接  
    CREATE SEQUENCE：建立序列  
    CREATE SESSION：建立会话  
    CREATE SYNONYM：建立同义词  
    CREATE VIEW：建立视图
 *  **RESOURCE角色：**  
    是授予开发人员的  
    CREATE CLUSTER：建立聚簇  
    CREATE PROCEDURE：建立过程  
    CREATE SEQUENCE：建立序列  
    CREATE TABLE：建表  
    CREATE TRIGGER：建立触发器  
    CREATE TYPE：建立类型
 *  **DBA角色：**  
    拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构，并且系统权限也需要DBA授出，且DBA用户可以操作全体用户的任意表，包括删除

grant dba to acton;

进入system用户下给用户赋予dba权限，否则无法正常登录。

#### 创建表 ####

语法：

create table [schema.] table
    	(column datatype [default expr][, ...]);

数据类型：

<table> 
 <thead> 
  <tr> 
   <th align="center">数据类型</th> 
   <th align="center">描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">VARCHAR2（size）</td> 
   <td align="center">可变长字符数据</td> 
  </tr> 
  <tr> 
   <td align="center">CHAR（size）</td> 
   <td align="center">定长字符数据</td> 
  </tr> 
  <tr> 
   <td align="center">NUMBER（p，s）</td> 
   <td align="center">可变长数值数据</td> 
  </tr> 
  <tr> 
   <td align="center">DATE</td> 
   <td align="center">日期型数据</td> 
  </tr> 
  <tr> 
   <td align="center">LONG</td> 
   <td align="center">可变长字符数据，最大可达到2G</td> 
  </tr> 
  <tr> 
   <td align="center">CLOB</td> 
   <td align="center">字符数据，最大可达到4G</td> 
  </tr> 
  <tr> 
   <td align="center">RAW and LONG RAE</td> 
   <td align="center">原始的二进制数据</td> 
  </tr> 
  <tr> 
   <td align="center">BLOB</td> 
   <td align="center">二进制数据，最大可达到4G</td> 
  </tr> 
  <tr> 
   <td align="center">BFILE</td> 
   <td align="center">存储外部文件的二进制数据，最大可达到4G</td> 
  </tr> 
  <tr> 
   <td align="center">ROWID</td> 
   <td align="center">行地址</td> 
  </tr> 
 </tbody> 
</table>

使用子查询创建表的语法：

create table t_table
    	[(column, column...)]
    as subquery;
    
    //如果只想创建表结构，不需要表中的数据，可以给一个永远为假的条件
    create table t_table1 as
    select * from t_table2 where 1=2;

示例：创建person表：

create table person(
           pid number(10),
           name varchar2(10),
           gender number(1) default 1,
           birthday date);
    
    insert into person(pid,name,gender,birthday)
    values(1,'张三',1,to_date('1999-12-22','yyyy-MM-dd'));
    
    select * from person;

![在这里插入图片描述][20190912184902316.png]  
**修改表**  
在sql中使用alter可以修改表

*  添加语法：

alter table 表名称 add(类名1 类型 [default 默认值]， 列名2 类型 [default 默认值]...)

*  修改语法：

alter table 表名称 modify(列名1 类型 [default 默认值]，列名2 类型 [default 默认值]...)

*  修改列名：

alter table 表名称 rename column 列名1 to 列名 2

示例：在person表中增加列address

alter table person add(address varchar2(10));

![在这里插入图片描述][20190912190043810.png]  
示例：把person表的address列的长度修改成20

alter table person modify(address varchar2(20));

**删除表**

drop table 表名

#### 约束 ####

在数据库开发中，约束是必不可少的，使用约束可以更好的保证数据库的完整性。在Oracle数据库中，约束的类型包括：

*  主键约束：Primary Key
 *  非空约束：Not Null
 *  唯一约束：Unique
 *  外键约束：Foreign Key
 *  检查性约束 Check

**1. 主键约束：**  
主键约束都是在id上使用，而且本身已经默认了内容不能为空，可以在建表的时候指定。创建一张表，把pid作为主键

create table person(
    	pid number(10) primary key,
    	name varchar2(10),
    	gender number(1) default 1,
    	birthday date);

主 键 不 可 重 复 ， S C O T T . S Y S C 0017981 是 系 统 自 动 分 配 的 约 束 名 字 \\color\{red\}\{主键不可重复，SCOTT.SYS\_C0017981是系统自动分配的约束名字\} 主键不可重复，SCOTT.SYSC0017981是系统自动分配的约束名字  
![在这里插入图片描述][20190912192523632.png] 主 键 不 可 为 空 \\color\{red\}\{主键不可为空\} 主键不可为空  
![在这里插入图片描述][20190912192602890.png]我们可以自己来指定主键约束的名字

create table person(
    	pid number(10),
    	name varchar2(10),
    	gender number(1) default 1,
    	birthday date,
    	constraint person_pk_pid primary key(pid));

![在这里插入图片描述][20190912192807990.png]  
**2. 非空约束：**  
使用非空约束，可以使指定的字段不可以为空。  
示例：建立一张pid和name不可以为空的表

create table person(
    	pid number(10) not null,
    	name varchar2(10) not null,
    	gender number(1),
    	birthday date);

![在这里插入图片描述][20190912211836111.png]  
**3. 唯一约束（unique）：**  
表中的一个字段的内容是唯一的  
范例：建一个name是唯一的表

create table person(
    	pid number(10),
    	name varchar2(10) unique,
    	gender number(1),
    	birthday date);

![在这里插入图片描述][20190912212250676.png]唯一约束的名字也可以自定义

create table person(
    	pid number(10),
    	name varchar2(10),
    	gender number(1),
    	birthday date,
    	constraint person_name_unique(name));

![在这里插入图片描述][20190912212420662.png]  
**5.检查约束：**  
使用检查约束可以来约束字段值的合法范围。  
示例：创建一张表性别只能是1 或 2

create table person(
    	pid number(10),
    	name varchar2(10),
    	gender number(1) check(gender in (1, 2)));

![在这里插入图片描述][20190912212714670.png]检查约束也可以自定义

create table person(
    	pid number(10),
    	name varchar2(10),
    	gender number(1),
    	birthday date,
    	constraint person_gender_ck check(gender in (1, 2)));

![在这里插入图片描述][2019091221290438.png]  
**5. 外键约束：**  
之前所讲的都是单表的约束，外键是两张表的约束，可以保证关联数据的完整性。  
示例：创建两张表，一张订单表，一张订单明细表，订单和明细是一对多的关系

create table orders(
    	order_id number(10),
    	total_price number(10, 2),
    	order_time date,
    	constraint orders_order_id_pk primary key(order_id));
    
    create table order_detail(
    	detail_id number(10),
    	order_id number(10),
    	item_name varchar2(10),
    	quantity number(10),
    	constraint order_detail_detail_id_pk primary key(detail_id));

insert into orders values(1, 200, to_date('2015-12-12', 'yyyy-MM-dd'));
    
    insert into order_detail values(1, 2, 'java', 1);

向两张表中插入如上两条数据，我们发现在order\_detail表中插入的order\_id在order表中并不存在，这样在数据库中就产生了脏数据。此时需要外键来约束它。

再次建表：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]  
注意：  
外键一定是主表的主键  
删表时一定先删子表再删主表，如果直接删主表会出现由于约束存在无法删除的问题  
SQL> drop table orders  
drop table orders  
 O R A − 02449 ： 表 中 的 唯 一 / 主 键 被 外 键 引 用 \\color\{red\}\{ORA-02449：表中的唯一/主键被外键引用\} ORA−02449：表中的唯一/主键被外键引用

但是可以强制删除：  
drop table orders cascade constraint;  
但是不建议使用

删除主表的数据可以先删除子表的关联数据，再删出表，也可以使用 级 联 删 除 \\color\{red\}\{级联删除\} 级联删除。  
级联删除在外键约束上要加上on delete cascade

constraint order_detail_order_id_fk foreign key(order_id)
    	references orders(order_id) on delete cascade

这 样 删 除 主 表 数 据 的 时 候 会 把 子 表 的 关 联 数 据 一 同 删 除 \\color\{red\}\{这样删除主表数据的时候会把子表的关联数据一同删除\} 这样删除主表数据的时候会把子表的关联数据一同删除  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]

#### 终极示例： ####

create table student
    (
       sid number constraint student_pk primary key,
       sname varchar2(20) constraint student_name_notnull not null,
       gender varchar2(2) constraint student_gender check (gender in ('男','女')),
       email varchar2(40) constraint student_email_unique unique
                          constraint student_email_notnull not null,
    deptno number constraint student_fk references dept(deptno) on delete set null
    );

[20190912183142452.png]: /images/20210813/f258cec6a60b4253a32b1f50190be39c.png
[20190912184902316.png]: /images/20210813/3452d4c8c53c4993bf4453247b534f69.png
[20190912190043810.png]: /images/20210813/8d68e7d7f3f44578b3cf11354fb68e09.png
[20190912192523632.png]: /images/20210813/2db452ba8a7c4c669f54299a84aa1185.png
[20190912192602890.png]: /images/20210813/517e3cfc307e4803988871c2e0d48e50.png
[20190912192807990.png]: /images/20210813/95e2b9d725d34a3ea55d678c35c5857f.png
[20190912211836111.png]: /images/20210813/93da5c7bd60246449d27b8f4366331e3.png
[20190912212250676.png]: /images/20210813/2e1d98a6871a4079a6149d1517a80864.png
[20190912212420662.png]: /images/20210813/e504b4c196ae49daaf19bee03acdf989.png
[20190912212714670.png]: /images/20210813/dc9caf52df234394a579bff229c22478.png
[2019091221290438.png]: /images/20210813/f3ebc423bb124dee91d1320251e82829.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/54738e27e3d647079c7c7e14dbd7ae15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/b8006fec79c843e98743c7f91109b512.png