SQL 基础之DDL语句创建和管理表(十四)

简介:

数据库对象


Object 描述

基本的数据存储集合,由行和列组成
View 从一张表或多张表中抽出的 逻辑上相关的数据集合
序列
生成规律的数值
index 索引
提高查询性能
Synonym 别名 给对象起的别名


表名和列名注意事项:

  • 必须以字母开头

  • 必须在 1–30 个字符之间

  • 必须只能包含 A–Z, a–z, 0–9, _, $, 和 #

  • 必须不能和用户定义的其他对象重名

  • 必须不能是Oracle的保留字

  • 表的列的数量最多1000个


CREATE TABLE  语句

必须具有:

– CREATE TABLE 权限

– 存储空间

必须指定:

– 表名

– 字段名称,字段数据类型和字段大小

语法:

CREATE TABLE [schema.]table_name (column datatype [DEFAULT expr][, ...]);


引用其他用户的表

  • 其他用户定义的表不在当前用户的方案中

  • 应该使用用户名作为前缀,引用其他用户定义的对象


wKiom1jShP6D2EDUAABFgoaW46I433.jpg

DEFAULT  选项

在插入的过程中,为列指定一个默认值。

... hire_date DATE DEFAULT SYSDATE, ...

字符串, 算数表达式, 或SQL 函数都是合法的

其它列的列名和 伪列 是非法的

默认值必须满足列的数据类型定义

例如:CREATE TABLE hire_dates  (id NUMBER(8), hire_date DATE DEFAULT SYSDATE);


创建表

create table dept

(deptno number(2),

dname varchar2(14),

loc varchar2(13),

create_date date default sysdate);


确认创建是否成功:

desc dept


 Name   Null?    Type

 ----------------------------------------- -------- ----------------------------

 DEPTNO     NUMBER(2)

 DNAME    VARCHAR2(14)

 LOC    VARCHAR2(13)

 CREATE_DATE    DATE


数据类型

 

数据类型 描述
VARCHAR2(size) 可变长字符数据
CHAR(size) 定长字符数据
NUMBER(p,s) 可变长数值数据 
DATE 日期和时间值
LONG 可变长字符数据(最大可达到 2 GB)
CLOB 字符数据(最大可达到 4 GB)
RAW and LONG RAW 二进制数据
BLOB 二进制数据 (最大可达到 4 GB)
BFILE 存储外部文件的二进制数据 (最大可达到 4 GB) 
ROWID base-64 系统编码的行唯一地址 


日期数据类型

数据类型
TIMESTAMP 带小数秒的日期型
INTERVAL YEAR TO MONTH 作为年和月的时间间隔存储
INTERVAL DAY TO SECOND 作为天、小时、分和秒的时间间隔存储


包含的约束

  • 约束为表级的强制规则。

  • 如果表有依赖关系,约束能防止表的删除。

  • 下面是有效的约束类型:

       – NOT NULL

       – UNIQUE

       – PRIMARY KEY

       – FOREIGN KEY

       – CHECK


约束准则

  •  如果不指定约束名 Oracle server 自动按照 SYS_Cn 的格式指定约束名

  •  在什么时候创建约束:

– 建表的同时

– 建表之后

  •  可以在表级或列级定义约束

  •  可以通过数据字典视图查看约束


定义约束

 语法:

create table [schema.]table

(column datatype [default expr]

[column_constraint],

...

[table_constraint][,...]);


列级约束语法

column [CONSTRAINT constraint_name] constraint_type,


表级约束语法:

column,...

[CONSTRAINT constraint_name] constraint_type

(column, ...),


列级约束示例

create table employees(

employee_id number(6)

constraint emp_emp_id_pk primary key,

first_name varchar2(20),

...);


表级约束语法:

column,...

[constraint constraint_name] constraint_type

(column, ...),


列级约束示例:

create table employees(

employee_id number(6)

constraint emp_emp_id_pk primary key,

first_name varchar2(20),

...);


表级约束示例:

create table employees(

employee_id number(6),

first_name varchar2(20),

...

job_id varchar2(10) not null,

constraint emp_emp_id_pk

primary key (employee_id));


NOT NULL 约束

确保该列不允许空值:

wKiom1jSiniTno1CAAD1SX4KDBU584.jpg


UNIQUE 约束

wKiom1jSisSyWGdZAABot330xUA537.jpg


可以定义在表级或者列级:

create table employees(

employee_id number(6),

last_name varchar2(25) not null,

email varchar2(25),

salary number(8,2),

commission_pct number(2,2),

hire_date date not null,

...

constraint emp_email_uk unique(email));


PRIMARY KEY  约束

wKioL1jSi0yTxni9AACVKgqmzP4575.jpg


FOREIGN KEY  约束

wKiom1jSjGbS4wEeAACkpQXohLw082.jpg


可以定义在表级或者列级:

create table employees(

employee_id number(6),

last_name varchar2(25) not null,

email varchar2(25),

salary number(8,2),

commission_pct number(2,2),

hire_date date not null,

...

department_id number(4),

constraint emp_dept_fk foreign key (department_id)

references departments(department_id),

constraint emp_email_uk unique(email));


FOREIGN KEY约束:关键字

  •  FOREIGN KEY:在表级指定子表中的列

  •  REFERENCES:标识在父表中的列

  •  ON DELETE CASCADE:当父表中的列被删除时,子表中相对应的列也被删除

  •  ON DELETE SET NULL:子表中相应的列置空


CHECK 约束

定义每一行必须满足的条件

以下的表达式是不允许的:

– 出现CURRVAL, NEXTVAL, LEVEL, 和ROWNUM 伪列

– 使用 SYSDATE, UID, USER, 和 USERENV 函数

– 在查询中涉及到其它行的值


CREATE TABLE:  示例

create table employees

( employee_id number(6)

constraint emp_employee_id primary key

, first_name varchar2(20)

, last_name varchar2(25)

constraint emp_last_name_nn not null

, email varchar2(25)

constraint emp_email_nn not null

constraint emp_email_uk unique

, phone_number varchar2(20)

, hire_date date

constraint emp_hire_date_nn not null

, job_id varchar2(10)

constraint emp_job_nn not null

, salary number(8,2)

constraint emp_salary_ck check (salary>0)

, commission_pct number(2,2)

, manager_id number(6)

constraint emp_manager_fk references

employees (employee_id)

, department_id number(4)

constraint emp_dept_fk references

departments (department_id));


违反约束

update employees

set department_id = 55

where department_id = 110;


wKiom1jSjm3Cb_A2AAB5DgNrPaE484.jpg


主键行被另一张表外键引用,那么您不能删除主键行

wKiom1jSj8WTkQhGAACXtIZC0Gs841.jpg


使用子查询创建表

 使用 CREATE TABLE 语句和 AS subquery 选项,将创建表和插入数据结合起来完成。

CREATE TABLE table  [(column, column...)]  AS subquery;


指定的列和子查询中的列要一一对应。

通过列名和默认值定义列。


create table dept80

as

select employee_id, last_name,

salary*12 annsal,

hire_date

from employees

where department_id = 80;


ALTER TABLE 语句

使用 ALTER TABLE 语句您可以:

  •  添加一个新列

  •  修改现有的列定义

  •  新的列定义默认值

  •  删除一列

  •  重命名列

  •  将表更改为只读状态



Read-Only 表

使用 ALTER TABLE 将表改为只读模式:

 表修改过程中阻止DDL和DML的更改

 将表改回到 读/写 模式

alter table employees read only;

-- perform table maintenance and then

-- return table back to read/write mode

alter table employees read write;


删除表

  •  表移动到回收站

  •  使用 PURGE 子句可彻底删除表

  •  表上依赖的对象失效同时删除对象权限

drop table dept;





本文转自 yuri_cto 51CTO博客,原文链接:http://blog.51cto.com/laobaiv1/1909412,如需转载请自行联系原作者
相关文章
|
2月前
|
SQL 大数据 HIVE
每天一道大厂SQL题【Day20】华泰证券真题实战(二)表转置
每天一道大厂SQL题【Day20】华泰证券真题实战(二)表转置
23 0
|
2月前
|
存储 SQL 关系型数据库
Windows server 2016——SQL server 数据库和表的管理
Windows server 2016——SQL server 数据库和表的管理
44 0
|
2月前
|
SQL 存储 大数据
【大数据技术Hadoop+Spark】Hive基础SQL语法DDL、DML、DQL讲解及演示(附SQL语句)
【大数据技术Hadoop+Spark】Hive基础SQL语法DDL、DML、DQL讲解及演示(附SQL语句)
47 0
|
27天前
|
SQL Oracle 关系型数据库
SQL 的 with as 临时中间表
SQL 的 with as 临时中间表
22 1
|
1月前
|
SQL 数据库
SQL-修改表操作
SQL-修改表操作
|
1月前
|
SQL 存储 关系型数据库
SQL语句-表操作
SQL语句-表操作
|
1月前
|
SQL Java 数据库连接
这个问题是由于Flink在执行SQL语句时,无法找到合适的表工厂来处理JOIN操作。
【1月更文挑战第17天】【1月更文挑战第85篇】这个问题是由于Flink在执行SQL语句时,无法找到合适的表工厂来处理JOIN操作。
19 8
|
1月前
|
SQL 流计算
在Flink SQL中,如果在使用表别名后,WHERE子句中使用了别名,可能会出现找不到表的问题
【1月更文挑战第4天】【1月更文挑战第18篇】在Flink SQL中,如果在使用表别名后,WHERE子句中使用了别名,可能会出现找不到表的问题
20 1
|
1月前
|
SQL 数据挖掘 数据处理
「SQL面试题库」 No_75 重新格式化部门表
「SQL面试题库」 No_75 重新格式化部门表
|
1月前
|
SQL 数据挖掘 数据处理
「SQL面试题库」 No_4 组合两个表
「SQL面试题库」 No_4 组合两个表