导图社区 DDL语句
- 28
- 1
- 0
- 举报
DDL语句
DDL（Data Definition Language）语句： 数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等
编辑于2023-03-18 20:03:46 山东省
- DDL语句
DDL（Data Definition Language）语句： 数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等
- SQL查询之条件查询
SQL查询之条件查询的思维导图，语法有select column1,column2,... from table_name where condition；分类有运算符、条件连接、包含查询、范围查询、空值判断、any/all（集合）。
- SQL查询之行列转换
SQL查询之行列转换的思维导图依据现有数据，将以行（列）展示的数据转换成以列（行）的形式展示，介绍了要求、方法、专用函数的内容。
DDL语句
社区模板帮助中心，点此进入>>
- DDL语句
DDL（Data Definition Language）语句： 数据定义语言，主要是进行定义/改变表的结构、数据类型、表之间的链接等操作。常用的语句关键字有 CREATE、DROP、ALTER 等
- SQL查询之条件查询
SQL查询之条件查询的思维导图，语法有select column1,column2,... from table_name where condition；分类有运算符、条件连接、包含查询、范围查询、空值判断、any/all（集合）。
- SQL查询之行列转换
SQL查询之行列转换的思维导图依据现有数据，将以行（列）展示的数据转换成以列（行）的形式展示，介绍了要求、方法、专用函数的内容。
- 相似推荐
- 大纲
DDL语句
定义
Data Definition Language 数据库模拟定义语言
数据类型
数值类型
number(int) 数字类型，长度为int
number(int,format) 数字类型，长度为int，包含format个精度
integer : number的子类型，它等同于number(38,0),用来存储整数，若插入、更新的值有小数，则会被四舍五入，默认长度为11
float : 浮点型，number的子类型。float(n)中n是二进制有效位数，n值的范围是1~126，转10进制需要（乘以0.30103），即1~38
double : 双精度浮点型
pls_integer,binary_integer：存储整数用的
字符串类型
char(int)：定长字符串类型，假如字段长度为10，存储的数据长度为5，那么默认追加5个空格到最后，长度始终为10位
varchar2(int)：变长字符串类型，假如字段长度为10，存储的数据长度为5，那么最后长度是5
clob : 大文本文件类型，一般存储文件内容，word内容，在SQL语句使用时注意，需要用函数转化
blob ：大文件类型，可存放音乐，视频等，已淘汰。一般只存放文件地址。--(已淘汰)
long ：它存储变长字符串，最大可存放2G的字符数据（2GB是指2千兆字节，而不是2千兆字符），与varchar2或char类型一样，存储在long类型中的文本要进行字符集转换
日期类型
sysdate：日期类型，两个日期类型相减，得到的是天数，精确到秒
systimestamp：时间戳类型，两个时间戳类型相减，得到的是秒数，精确到毫秒
表
创建表
语法
create table 表名( 字段名 类型名(长度), 字段名 类型名(长度), ... );
命名规范
可包含字母，数字，下划线，特殊符号等
命名规则
1.不能以数字开头
2.不能以特殊字符开头
3.不能以关键字起表名，如果非要用，加上双引号，查询时也需要加上双引号查询
4.表名长度不能超过30个字符
5.可以起中文表名，但不建议
6.不能加单引号
7.不能包含括号
查询随机列创建新表
create table 表名 as select语句;
优点：快速创建表
缺点：不能把约束添加进来
修改表
需要修改表结构的，就用到alter语句
添加字段语法
alter table 表名 add 字段名 类型名(长度);
修改字段的语法
alter table 表名 modify 字段名 类型名(长度);
修改字段名的语法
alter table 表名 rename column 旧字段名 to 新字段名;
修改表名的语法
alter table 表名 rename to 新表名;
删除列名的语法
alter table 表名 drop column 字段名;
创建及修改默认值（缺省值）
create table 表名( 字段名 类型名 default 默认值, 字段名 类型名 default 默认值, ... )
（数据表已存在时） alter table 表名 modify 字段名 default 默认值;
查询默认值
select * from user_tab_columns;
修改表中的内容
update table_name set column1 = 新column1 where column2 = 'column2';
删除表
语法：drop table 表名;
注意：删除表时，相关表中约束也一同被删掉
关键字 TRUNCATE：截断表
语法：TRUNCATE TABLE TABLE_NAME;
与DELETE的相同之处
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
与DELETE的区别
1.DELETE FROM后面可以写条件，TRUNCATE不可以。
2.DELETE FROM记录是一条条删的，所删除的每行记录都会进日志，而TRUNCATE一次性删掉整个页，因此日至里面只记录页释放，简言之，DELETE FROM更新日志，TRUNCATE基本不，所用的事务日志空间较少。
3.DELETE FROM删空表后，会保留一个空的页，TRUNCATE在表中不会留有任何页。
4.当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。TRUNCATE始终锁定表和页，而不是锁定各行。
5.如果有自增id列，delete from后仍然从上次的数开始增加，即种子不变，而truncate后，种子会恢复初始。
TRUNCATE不会触发DELETE的触发器，因为TRUNCATE操作不记录各个行删除。
总结
1.TRUNCATE和 DELETE只删除数据不删除表的结构(定义) DROP语句将删除表的结构被依赖的约束(CONSTRAINT),触发器(TRIGGER),索引(INDEX); 依赖于该表的存储过程/函数将保留,但是变为INVALID状态。
2.DELETE语句是DML,这个操作会放到ROLLBACK SEGEMENT（回滚段）中,事务提交之后才生效;如果有相应的触发器（TRIGGER）,执行的时候将被触发。TRUNCATE,DROP是DDL, 操作立即生效,原数据不放到ROLLBACK SEGEMENT中,不能回滚. 操作不触发TRIGGER 。
3.DELETE语句不影响表所占用的EXTENT, 高水线(HIGH WATERMARK)保持原位置不动 显然DROP语句将表所占用的空间全部释放 TRUNCATE 语句缺省情况下见空间释放到 MINEXTENTS个 EXTENT,除非使用REUSE STORAGE;TRUNCATE会将高水线复位(回到最开始)。
4.速度,一般来说: DROP> TRUNCATE > DELETE。
5.安全性:小心使用DROP 和TRUNCATE,尤其没有备份的时候.否则哭都来不及。
6.使用上,想删除部分数据行用DELETE,注意带上WHERE子句. 回滚段要足够大. 想删除表,当然用DROP。 想保留表而将所有数据删除，如果和事务无关,用TRUNCATE即可. 如果和事务有关,或者想触发TRIGGER,还是用DELETE如果是整理表内部的碎片,可以用TRUNCATE跟上REUSE STORAGE,再重新导入/插入数据。
约束
定义
约束是强加在表上的规则或条件。确保数据库满足业务规则，保证数据的完整性。 当对表进行DML操作时，如果此操作会造成表中的数据违反约束条件或规则的话， 系统会拒绝执行这个操作。约束可以是列级别的，也可以是表级别的。 定义约束时，没有给出约束的名字，oracle系统将为该约束自动生成一个名字，其格式为SYS_Cn...,其中n为自然数（强烈建议在创建约束或增加约束时，给约束定义名称）
列级约束（行级约束）
列级定义是在定义列的同时定义约束：
column [constraint constraint_name] constraint_type;
表级约束
表级定义是指在定义了所有列后，再定义约束，这里需要注意，not null 约束只能再列级上定义。
[constraint constraint_name] constraint_type (column,...)
约束功能
实现一些业务规则，防止无效的垃圾数据进入数据库，维护数据库的完整性（完整性是指正确性与一致性）。 从而使数据库的开发和维护都更加容易。
约束分类（5种）
1）not null ：非空约束
如果在列上定义了 not null,那么当插入数据时，必须为列提供数据，数据不能为null，此约束只能在列级定义，不能在表级定义。
2）unique ：唯一约束
当定义了唯一约束后，该列值是不能重复的，但是可以为null。
3）primary key ：主键约束
用于唯一的标识表中数据，当定义了主键约束之后，该列不但不能重复，而且不能为null，一张表最多只能有一个主键，但可以有多个unique约束。
创建主键或唯一约束后，oracle会自动创建一个与约束同名的索引（uniquenes为unique唯一索引）。
4）foreign key ：外键约束
用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或unique约束
当定义外键约束后，要求外键列数据在主表的主键列存在，或是为null。
用来维护从表和主表之间的引用完整性，外键约束是个有争议的约束，它一方面能够维护数据库的数据一致性，数据的完整性。防止错误的垃圾数据入库；
另外一方面它会增加表插入、更新等操作时的SQL性能的额外开销，不少系统里面通过业务逻辑控制，来取消外键约束，例如在数据仓库中，就推荐禁用外键约束。
5）check ：检查性约束
用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在这个范围内就会报错。
命名规范
约束名称建议自己定义一套命名规则，否则使用系统生成的约束名，很难能把它和对应的表、字段联系起来
非空约束 NN_表名_列名
唯一约束 UK_表名_列名
主键约束 PK_表名
外键约束 FK_表名_列名
条件约束 CK_表名_列名
默认约束 DF_表名_列名
如果约束名称超过32位长度，建议应该缩写表名，而不应用NN_表名_列名。不过具体视情况而定，很多时候，DF_表名_列名 这样的命名，往往会超过32位字符，所以有时候，需要缩写表名或是采用其他规则
创建约束
1.非空约束（not null）
语法1： create table 表名( 字段名 类型名(长度) [constraint 约束名] not null, 字段名 类型名(长度), ... )；
语法2: alter table 表名 modify 列名 [constraint 约束名] not null;
2.唯一约束（unique）
语法1： drop table t1; create table 表名( 字段名 类型名 constraint 约束名 约束类型, 字段名 类型名 constraint 约束名 约束类型, ... )；
语法2： create table 表名( 字段名 类型名, 字段名 类型名, constraint 约束名 约束类型(字段名) );
语法3： --表已经创建好的基础上，添加一个唯一约束 alter table 表名 add constraint 约束名 约束类型(字段名);
3.主键约束（primary key）
语法1： create table 表名( 字段名 类型名 [constraint 约束名] primary key, 字段名 类型名, ... )；
语法2： create table 表名( 字段名 类型名, 字段名 类型名, constraint 约束名 primary key(字段名) )；
语法3： --表已经存在时 alter table 表名 add [constraint 约束名] primary key(字段名);
4.外键约束（foreign key）
语法1： create table 表名( 字段名 类型名 [constraint 约束名] references 主表名(字段名), 字段名 类型名, ... )；
语法2： create table 表名( 字段名 类型名, 字段名 类型名, constraint 约束名 foreign key(字段名) references 主表名(字段名) )；
语法3： --表已经存在时 alter table 表名 add constraint 约束名 foreign key(从表字段名) references 主表名(主表字段名);
当定义了外键约束后，要求外键列的数据必须在主表的主键列（或唯一列）中存在，或者为null。 foreign key 约束既可以在列级定义，也可以在表级定义。
关键字说明
1. foreign key ：该选项用于指定在表级定义外键约束。当在表级定义外键约束时，必须指定该选项，在列级定义外键约束，不需要定义该选项
2. references ：该选项用于指定主表名及其主键列，当定义外键约束时，该选项必须指定。
3. on delete cascade ：该选项用于指定级联删除选项，如果在定义外键约束时指定了该选项，那么当删除主表数据时会级联删除从表的相关数据。
4. on delete set null ：该选项用于指定转换相关的外键值为null，如果在定义外键约束时，指定了该选项，那么当删除主表数据时，会将从表外键列的数据设置为null。
5.检查性约束（check）
语法1： create table 表名( 字段名 类型名 constraint 约束名 check(条件), 字段名 类型名, ... );
语法2： create table 表名( 字段名 类型名, 字段名 类型名, constraint 约束名 check(条件) );
语法3： --表已经存在时 alter table 表名 add constraint 约束名 check(条件表达式);
6.维护约束
1）增加约束
1_ 除了非空（null/not null）约束外，如果增加约束必须使用alter table语句中的add子句
2_ 如果增加not null约束，那么必须使用alter table语句的modify子句
2）修改约束名
alter table 表名 rename constraint 旧约束名 to 新约束名;
alter table table_name rename constraint old_constraint_name to new_constraint_name;
7.禁止约束
alter table 表名 disable constraint 约束名 [cascade];
alter table table_name disable constraint constraint_name [cascade];
cascade 用于指定级联禁止从表的外键
8.激活约束
alter table t1 enable constraint ck_t1_age1;
9.删除约束
当删除特定表的主键约束时，如果该表具有相关的从表，那么删除主键约束时必须带有cascade选项
alter table 表名 drop constraint 约束名 | primary key
alter table table_name drop constraint constraint_name primary key;
查看约束
select constraint_name,table_name from user_constraints where table_name = 'T1';
删除唯一约束
alter table tb2 drop constraint SYS_C0013056 cascade；
删除主键约束
alter table t1 drop primary key cascade;
alter table t1 drop constraint pk...;
alter table t1 drop primary key; --没有从表数据时，可直接删除，不需要带上cascade
10.显示信息（数据字典）
select * from user_constraints;
select * from user_constraints where table_name = 'T1';
select * from user_cons_columns;
select * from user_cons_columns where table_name = 'T1';
11.添加注释
--给表t1添加注释
comment on table t1 is '测试表';
--给表t1的id添加注释
comment on column t1.id is '唯一识别号';
序列
概念
序列是oracle提供的一组能够自动增长的序号，常常用来生成每一条数据的唯一标识
创建序列语法
create sequence 序列名称
start with n --初始序号，递增：默认minvalue，递减: 默认maxvalue
increment by n --增长幅度，n为正：递增，n为负：递减
minvalue n | nominvalue --最小值n | 无最小值
maxvalue n | nomaxvalue --最大值n | 无最大值
cache n | nocache --缓存n个序号 | 无缓存 ，默认缓存20个序号 --尽量设置大一点
cycle | nocycle --循环到达极限时，是否重新循环生成序号
循环序列在创建时，缓存值限定在： CEIL((MAXVALUE - MINVALUE) / ABS(INCREMENT))以内，非循环序列缓存值设定没有限定，但实际缓存受所剩序列号影响。
--start with 数不可以小于minvalue值 --start with值不可以大于maxvalue值
查看序列
查看序列当前值
序列名称.currval
获取序列下一个值
序列名称.nextval
关于SCOTT用户创建的序列为何从2开始的问题解决办法
alter session set deferred_segment_creation=false;
修改序列
修改增长幅度的语法
alter sequence 序列名称 increment by n;
increment by 必须小于maxvalue 和minvalue的差
查看序列当前值
select 序列名称.currval from dual;
获取序列下一个值
select 序列名称.nextval from dual;
删除序列
drop sequence 序列名称;
拓展
GUID(UUID)：通用唯一识别码 由32位字符组成
SELECT SYS_GUID() FROM DUAL;
SELECT LOWER(SYS_GUID()) FROM DUAL;
视图
概念
视图(view)，也称虚表,?不占用物理空间，这个也是相对概念，因为视图本身的定义语句还是要存储在数字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。
视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。
一个视图也可以从另一个视图中产生。
查询scott用户下都有哪些表
select * from user_tables;
创建语法
create [or replace] view 视图名
as
select语句...
[with read only] 只读视图
[with check option] 检查性约束视图
视图创作权限
解决办法
1）登录sys用户
2）给scott用户赋予可创建视图权限
grant create any view to scott;
3）切换scott用户登录并创建视图
删除视图
drop view 视图名;
视图的优点
1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。灵活
2.用户通过简单的查询可以从复杂查询中得到结果。
3.维护数据的独立性，视图可从多个表检索数据。
4.对于相同的数据可产生不同的视图。
视图的分类
视图分为简单视图和复杂视图
1、简单视图只从单表里获取数据，复杂视图从多表或函数、运算、分组；
2、简单视图不包含函数和数据组，复杂视图包含；
3、简单视图可以实现DML操作，复杂视图不可以。
4、复杂视图创建时可包含多表关联、分组、函数等
5、视图一般都只用作只读视图
总结
1.视图中可以操做DML语句，操作结果，基表的数据也会更改
2.单表创建的视图，可以操作DML语句，基表也可以操作DML语句
索引
概念
1）索引是数据库对象之一，用于加快数据的检索，好比是书籍的目录，根据目录找内容，会很快找到想找的内容。在数据库中，索引可以减少数据库程序查询结果时需要读取的数据量。
2）索引是建立在表上的可选对象，索引的关键在于通过一组排序后，用索引键来取代默认的全表扫描的检索方式，从而提高检索效率。
3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或删除一个索引时，不会影响基本的表。
4）索引一旦建立，在表上进行DML操作时，oracle自动会管理索引。
5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变。
6）oracle创建主键时会自动在该列上创建索引。
原理
1）全盘扫描
按照条件在对应的数据列从第一条数据检索到最后一条数据，如果条件成立，就将对应的rowid取出，直到检索到最后一条数据，然后按照rowid去对应的物理位置获取整行数据。
2）索引扫描
按照条件在对应的数据列从大概位置开始检索，检索到最后一条符合条件的数据即停止检索，符合条件的数据获取对应的rowid，最后按照rowid去对应的物理位置获取整行数据。
分类
创建语法
create [bitmap] [unique] index 索引名称 on 表名(列名);
1.b_tree索引（也叫做b树索引）重点
1）概念
索引列原始数据+rowid
oracle的默认索引，工作中最常见、适用范围最广的索引;在所查询的数据位于总数据的百分之三十的时候运行最快。
2）创建语法
create index 索引名称 on 表名(列名);
create index idx_name on table_name(column_name)；
3）适用场景
列基数比较大的时候使用（例：行业，身高等）列基数：该列不重复的数据的个数
4）查找数据方式
根节点 -> 枝节点 -> 叶节点
2.位图索引
1）概念
创建位图索引时，oracle会扫描整张表，并为索引列的每个值建立一个位图
2）创建语法
create bitmap index 索引名称 on 表名(列名);
create bitmap index index_name on table_name(column_name);
3）适用场景
对于列基数小的列适合建立位图索引（例：性别）
3.唯一索引
1）概念
索引列不能出现重复值，经常会用在主键列
2）语法
create unique index 索引名称 on 表名(列名)
create unique index index_name on table_name(column_name);
4.反向键索引
索引列原始数据的反向存储+rowid
1）说明
可以视作一种特殊的b-tree索引，存储索引列的反向值
2）背景
为防止b-tree索引在某页上数据量占比过高而使用的一种索引
3）语法
create index 索引名称 on 表名列名) reverse;
create index idx_name on table_name(column1) reverse;
4）适用场景
原始数据分支不明显但反向数据分支明显的列
（身高：集中在一米七-一米八）
5.函数索引
将索引列原始数据经函数处理后存储+rowid
1）说明
可以视作一种特殊的b-tree索引，存储函数处理后的数据
2）背景
在某个字段上以原值建立了索引，但是在筛选时在该字段上经常加函数，导致索引无法生效
3）语法
create index 索引名称 on 表名(function(列名));
create index idx_name on table_name(function(column1));
4）适用场景
对某列进行筛选时经常需要配合函数使用
（例如查找姓名中的首字母大写）
6.复合索引（联合索引）
语法
create index 索引名称 on 表名(列名1,列名2,…)
create index ind_name on table_name(column1,column2,…)
联合索引查询时，column1,column2同时查询时会根据索引查询，单查询column1时也会根据索引查询；但，根据column2,或后面列查询时会根据全盘扫描查询,不走索引
修改索引
1）重命名索引
语法
alter index 索引名称 rename to 新索引名称;
alter index old_index_name rename to new_index_name;
2）合并索引
表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或合并索引，合并索引方式更好些，无需额外存储空间，代价较低
语法
alter index 索引名称 coalesce;
alter index idx_name coalesce;
3）重建索引
方式一、删除原来的索引，创建新的索引
方式二、alter index index_name rebuild;
删除索引
drop index 索引名称;
drop index index_name;
禁用索引
1）语法
alter index 索引名称 unusable;
2）禁用索引后，想重新启用，只能重建索引
3）注意：【插入完成后统一维护索引】比【一边插入一边维护】的速度要快。
alter index idx_deptno rebuild;
alter index idx_ename_deptno unusable;
索引的数据字典
1）所有索引
select index_name, --索引名称 index_type, --索引类型 table_name, --表名 uniqueness, --是否唯一 status, --索引状态 valid可用的unusable 不可用的 tablespace_name,--表空间 logging --是否记录日志 from user_indexes where 1=1 --and index_name = 'IND_EMP_DEPTNO' and table_name = 'EMP';
2）索引列
select index_name, --索引名称 table_name, --表名 column_name, --列名 column_position, --字段在索引中的位置 descend --排序方式 默认asc from user_ind_columns where 1=1 --and index_name ='IND_EMP_UPENAME' and table_name = 'EMP';
注意事项
1.通配符（%）在搜索词首次出现时，oracle不能使用索引
2.不要在索引列上使用not，可以采用其他方式代替 （oracle碰到not会停止使用索引，而采用全表扫描）
select * from emp where not (sal = 100); select * from emp where sal <> 100; select * from emp where sal != 100; --替换为 select * from emp where sal < 100 or sal > 100;
3.索引上使用空值比较将停止使用索引
索引建立原则总结
1. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引
2. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
3. 小表不要建立索引
4. 对于列基数大的列适合建立b树索引，对于基数小的列适合建立位图索引
5. 列中有很多空值，但经常查询该列上非空记录时应该建立索引
6. 经常进行连接查询的列应该创建索引
7. 使用create index时要将最常查询的列放在最前面
8. long（可变长字符串数据，最长2g）和long raw（可变长二进制数据，最长2g）列不能创建索引
9. 限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）