文章目录
- 1.数据定义语言DDL
- 2.显示表结构
- 3.数据类型
- 4.新建表(create table)
- 4.1建表规则
- 4.2语法
- 4.3default值
- 4.4示例与注释
- 4.5使用子查询创建表
- 5.修改表结构(alter table)
- 5.1语法说明
- 5.2示例
- 5.2.1追加一个新列
- 5.2.2修改一个列
- 5.2.3删除一个列
- 5.2.4重命名一个列
- 5.3清空表(truncate)
- 5.4删除表(drop table)
- 6.重命名
- 7.约束
- 7.1说明
- 7.2非空约束
- 7.3唯一性约束
- 7.4主键约束
- 7.5外键约束
- 7.6check约束
- 7.7以上所有约束示例
- 7.8违反约束
- 8.视图
- 8.1说明
- 8.2创建视图
- 8.3查询视图
- 8.4修改视图
- 8.5视图中使用DML的规定
- 8.6屏蔽DML操作
- 8.7删除视图
- 9.序列
- 9.1说明
- 9.2创建序列
- 9.3NEXTVAL和CURRVAL伪列
- 9.4查询序列
- 9.5修改序列
- 9.6删除序列
- 10.同义词
- 10.1说明
- 10.2创建和删除同义词
- 11.索引
- 11.1说明
- 11.2创建索引
- 11.2.1什么时候创建索引
- 11.2.2什么时候不要创建索引
- 11.3查询索引
- 11.4删除索引
Oracle数据定义语言DDL
1.数据定义语言DDL
DDL(Data Definition Language，数据定义语言)，用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等，如CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
DDL操作是隐性提交的，不能rollback。
2.显示表结构
使用DESCRIBE命令，表示表结构
DESC[RIBE] tableName
示例：
SQL> describe emp;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4)
ENAME VARCHAR2(10) Y
JOB VARCHAR2(9) Y
MGR NUMBER(4) Y
HIREDATE DATE Y
SAL NUMBER(7,2) Y
COMM NUMBER(7,2) Y
DEPTNO NUMBER(2) Y
3.数据类型
| 数据类型 | 描述 |
|---|---|
| VARCHAR2(size) | 可变长字符数据 |
| CHAR(size) | 定长字符数据 |
| NUMBER(p,s) | 可变长数值数据 |
| DATE | 日期型数据 |
| LONG | 可变长字符数据，最大可达到2G |
| CLOB | 字符数据，最大可达到4G |
| RAW and LONG RAW | 原始的二进制数据 |
| BLOB | 二进制数据，最大可达到4G |
| BFILE | 存储外部文件的二进制数据，最大可达到4G |
4.新建表(create table)
4.1建表规则
表名和列名:
-
必须以字母开头
-
必须在1–30个字符之间
-
必须只能包含 A–Z，a–z，0–9，_，$和#
-
必须不能和用户定义的其他对象重名
-
必须不能是Oracle的保留字
-
Oracle默认存储是都存为大写
-
数据库名只能是1~8位，datalink可以是128位，和其他一些特殊字符
4.2语法
必须指定:
- 表名
- 列名, 数据类型, 数据类型的大小
要求必须具备以个资源才可以建表:
- CREATE TABLE权限
- 存储空间
4.3default值
- 执行insert操作时，可以为其指定默认值
… hire_date DATE DEFAULT SYSDATE, … - 值、表达式和SQL语句都可以作为默认值
- 其他的列名或者是伪列都是非法的
- 默认值的类型必须和该列的类型一致
4.4示例与注释
create table dept
(deptno NUMBER(2) not null,dname VARCHAR2(14),loc VARCHAR2(13)
);
创建带有约束的表
| 字段名 | 注释 | 约束 |
|---|---|---|
| id | 主键id | 主键 |
| name | 姓名 | 非空 |
| reg_date | 注册时间 | 默认值为当前时间 |
| id_card | 身份证号 | 非空、唯一 |
| age | 年龄 | 检查，大于等于0 |
| deptno | 部门编号 | 外键，应用dept.deptno |
create table t_user(id numberconstraint PK_USER_ID primary key,name varchar2(20)constraint NOTNULL_USER_NAME not null,reg_date datedefault sysdate,id_card varchar2(20)constraint NOTNULL_USER_CNUM not nullconstraint UNIQUE_CARDNUM unique,age numberconstraint CHECK_AGE check(age >= 0),deptno numberconstraint FK_USER_DEPTNO references dept(deptno)
);
-- Add comments to the table
comment on table t_useris '用户表';
-- Add comments to the columns
comment on column t_user.idis '主键id';
comment on column t_user.nameis '姓名';
comment on column t_user.reg_dateis '注册时间';
comment on column t_user.id_cardis '身份证号';
comment on column t_user.ageis '年龄';
comment on column t_user.deptnois '部门编号';
添加和修改注释使用一样的命令
-- 添加或修改表注释
comment on table t_useris '用户信息表';
-- 添加或修改列注释
comment on column t_user.idis '主键';
4.5使用子查询创建表
- 使用AS subquery选项，将创建表和插入数据结合起来
- 指定的列和子查询中的列要一一对应
- 通过指定别名指定新表的列名
- 如果子查询中的select中有表达式，则必须要指定别名
示例：
-- 创建表并插入数据
create table emp2
as
select empno,ename,sal
from emp
where deptno = '10';-- 只想要表结构，不要数据
create table t_emp2
as
select * from emp where 1=2 -- 如果子查询中的select中有表达式，则必须要指定别名
create table t_emp4
as
select empno, ename, sal, sal*12 total from emp where deptno=10
5.修改表结构(alter table)
5.1语法说明
使用ALTER TABLE语句可以:
- 追加新的列
- 修改现有的列
- 删除一个列
- 重命名一个列
使用ALTER TABLE语句追加，修改，删除或重命名列的语法
5.2示例
5.2.1追加一个新列
使用ADD子句追加一个新列
alter table emp2 add (deptno varchar2(2));
新列是表中的最后一列
5.2.2修改一个列
可以修改列的数据类型，尺寸和默认值
对默认值的修改只影响今后对表的修改。
alter table emp2 modify (ename varchar2(20));
5.2.3删除一个列
使用drop column子句删除不再需要的列。
alter table emp2 drop column deptno;
5.2.4重命名一个列
alter table emp2 rename column ename to empname;
5.3清空表(truncate)
TRUNCATE TABLE语句:
- 删除表中所有的数据
- 释放表的存储空间
- TRUNCATE语句不能回滚
- 可以使用DELETE语句删除数据
truncate table emp2;
5.4删除表(drop table)
- 数据和结构都被删除
- 所有正在运行的相关事务被提交
- 所有相关索引被删除
- DROP TABLE语句不能回滚，但是可以闪回
drop table emp2;
6.重命名
改变对象的名称
- 执行
RENAME语句改变表，视图，序列或同义词的名称 - 必须是对象的拥有者
rename emp2 to emp3;
7.约束
7.1说明
- 约束是表一级别或者列一级别的限制
- 如果存在依赖关系，约束可以防止错误的删除数据
- 约束的类型：
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- 约束规则：
- 用户可以自定义约束，也可以使用Oracle Server的sys_cn格式命名约束
- 约束创建的时机：
- 创建表的时候，同时创建约束
- 表结构创建完成后
- 约束可以定义在列一级，或者是表一级
- 通过数据字典(user_constraints)查看约束
select * from user_constraints where table_name = 'T_USER';
7.2非空约束
保证列的值不能为空。
7.3唯一性约束
7.4主键约束
7.5外键约束
- FOREIGN KEY：在子表中，定义了一个表级的约束
- REFERENCES：指定表和父表中的列
- ON DELETE CASCADE：当删除父表时，级联删除子表记录
- ON DELETE SET NULL：将子表的相关依赖记录的外键值置为null
7.6check约束
- 定义每一行记录所必须满足的条件
- 下面的表达式可以使用在check约束中:
- 引用CURRVAL，NEXTVAL，LEVEL和ROWNUM
- 调用SYSDATE，UID，USER和USERENV函数
- 另一个表的查询记录
7.7以上所有约束示例
7.8违反约束
不能删除有外键约束的记录。
8.视图
8.1说明
- 视图是一种虚表。
- 视图建立在已有表的基础上，视图赖以建立的这些表称为基表。
- 向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句。
- 视图是向用户提供基表数据的另一种表现形式。
可以通过视图修改表，增删改查都可以。在创建视图时有个选项WITH READ ONLY，如果加上这个，表示是只读的，就不能增删改了，只能查询。
看视图，是不知道从哪个表中来的数据，只有看创建视图的DDL才知道，这样比较安全。
- 视图的优点
- 简化复杂查询
- 限制数据访问
- 同样的数据，可以有不同的显示方式
注意：不建议通过视图对表进行修改。视图不能提高性能。
8.2创建视图
- FORCE：子查询不一定存在
- NOFORCE：子查询存在(默认)
- WITH READ ONLY：只能做查询操作
- WITH CHECK OPTION：遵循约束条件
with check option示例：
create view view2
as
select * from emp where deptno=10
with check option;
通过示例视图进行DML时，要遵循SQL语句中定义的限制。这里只限定了10号部门，所以通过视图只能操作10号部门。
创建视图
create view myview
as
select empno id, ename name, sal
from emp;create view deptview
as
select d.deptno, d.dname, count(e.empno) empcount
from emp e, dept d
where e.deptno=d.deptno
group by d.deptno, d.dname;
8.3查询视图
select * from myview;
8.4修改视图
- 使用CREATE OR REPLACE VIEW子句修改视图
create or replace view myview2
as
select empno id, ename name, sal
from emp
with read only;
- CREATE VIEW子句中各列的别名应和子查询中各列相对应
8.5视图中使用DML的规定
- 可以在简单视图中执行DML操作
当视图定义中包含以下元素之一时不能使用delete：
- 组函数
- GROUP BY子句
- DISTINCT关键字
- ROWNUM伪列
当视图定义中包含以下元素之一时不能使用update：
- 组函数
- GROUP BY子句
- DISTINCT关键字
- ROWNUM伪列
- 列的定义为表达式
当视图定义中包含以下元素之一时不能使用insert：
- 组函数
- GROUP BY子句
- DISTINCT关键字
- ROWNUM伪列
- 列的定义为表达式
- 表中非空的列在视图定义中未包括
8.6屏蔽DML操作
- 可以使用WITH READ ONLY选项屏蔽对视图的DML操作
- 任何DML操作都会返回一个Oracle Server错误
8.7删除视图
删除视图只是删除视图的定义，并不会删除基表的数据。
drop view myview;
9.序列
9.1说明
序列：可供多个用户用来产生唯一数值的数据库对象。
- 自动提供唯一的数值
- 共享对象
- 主要用于提供主键值
- 将序列值装入内存可以提高访问效率
- 序列在下列情况下出现裂缝(不连续)：
- 回滚
- 系统异常，如停电了，这时20个就没有了，再打开时会再重新生成20个
- 多个表同时使用同一序列
- 如果不将序列的值装入内存(NOCACHE)，可使用数据字典表USER_SEQUENCES查看序列当前的有效值
9.2创建序列
create sequence mysequence;
默认：increment by 1每次增加1，nocycle 序列不循环使用，cache缓存20个序列
9.3NEXTVAL和CURRVAL伪列
- NEXTVAL返回序列中下一个有效的值，任何用户都可以引用
select mysequence.nextval from dual;
- CURRVAL中存放序列的当前值
select mysequence.currval from dual;
刚创建的序列，第一次只能访问nextval。如果先使用currval，就会报错。
序列值插入到表中：
insert into dept values (mysequence.nextval,'AA','BB');
9.4查询序列
查询数据字典视图USER_SEQUENCES获取序列定义信息
select * from user_sequences;
默认是有缓存的，执行mysequence.nextval后LAST_NUMBER字段值变为21。
如果指定NOCACHE选项，则列LAST_NUMBER显示序列中下一个有效的值。
9.5修改序列
- 修改序列的注意事项
- 必须是序列的拥有者或对序列有ALTER权限
- 只有将来的序列值会被改变
- 改变序列的初始值只能通过删除序列之后重建序列的方法实现
修改序列的增量，最大值，最小值，循环选项或是否装入内存
alter sequence mysequence
increment by 2
maxvalue 24
cycle
nocache
9.6删除序列
使用DROP SEQUENCE语句删除序列
drop sequence mysequence;
10.同义词
10.1说明
使用同义词访问相同的对象：
- 方便访问其它用户的对象
- 缩短对象名字的长度
10.2创建和删除同义词
创建同义词
create synonym emp2 for emp;
删除同义词
drop synonym emp2;
11.索引
11.1说明
- 一种独立于表的模式对象，可以存储在与表不同的磁盘或表空间中
- 索引被删除或损坏，不会对表产生影响，其影响的只是查询的速度
- 索引一旦建立，Oracle管理系统会对其进行自动维护，而且由Oracle管理系统决定何时使用索引，用户不用在查询语句中指定使用哪个索引
- 在删除一个表时, 所有基于该表的索引会自动被删除
- 通过指针加速Oracle服务器的查询速度
- 通过快速定位数据的方法，减少磁盘I/O
11.2创建索引
- 自动创建：在定义PRIMARY KEY或UNIQUE约束后系统自动在相应的列上创建唯一性索引
- 手动创建：用户可以在其它列上创建非唯一的索引，以加速查询
create index idx_emp_name on emp(ename);
11.2.1什么时候创建索引
以下情况可以创建索引：
- 列中数据值分布范围很广
- 列经常在WHERE子句或连接条件中出现
- 表经常被访问而且数据量很大 ，访问的数据大概占数据总量的2%到4%
11.2.2什么时候不要创建索引
下列情况不要创建索引：
- 表很小
- 列不经常作为连接条件或出现在WHERE子句中
- 查询的数据大于2%到4%
- 表经常更新
11.3查询索引
可以使用数据字典视图USER_INDEXES和USER_IND_COLUMNS查看索引的信息
select ic.index_name,ic.column_name,ic.column_position col_pos,ix.uniqueness
from user_indexes ix, user_ind_columns ic
where ic.index_name = ix.index_name and ic.table_name = 'EMP';
11.4删除索引
drop index idx_emp_name;
本文参考:
https://www.cnblogs.com/study-s/p/5287529.html
