数据表DDL

2020-11-09

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name
//DATABASE与SCHEMA语义相同

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

建数据表,TEMPORARY临时表；一个TEMPORARY表仅在当前会话中可见，并且在关闭会话时会自动删除。AUTO_INCREMENT每个表 只能有一个列，必须对其进行索引，并且不能有 DEFAULT值，对于MyISAM表，您可以指定第二列；auto_increment=value项设置表的自增值的步长，默认为1

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (
id int(11) [AUTO_INCREMENT] [PRIMARY KEY] [NOT NULL | NULL] [DEFAULT default_value] 
[COMMENT 'string'] [COLLATE collation_name] [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}],
`name` varchar(255)) 
[DEFAULT] CHARACTER SET [=] charset_name [DEFAULT] COLLATE [=] collation_name 
ENGINE [=] engine_name 
ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} 
AUTO_INCREMENT [=] value COMMENT [=] 'string';

创建数据表同时创建索引;FULLTEXT全文索引，SPATIAL空间数据类型，只有MyISAM和InnoDB 表格才支持空间类型 ，索引列必须声明为NOT NUL

CREATE [ TEMPORARY ] TABLE [ IF NOT EXISTS ] tbl_name ( 
id INT ( 11 ), `name` VARCHAR ( 255 ), 
{FULLTEXT | SPATIAL} [INDEX | KEY] [index1] (id)  [ASC | DESC],
{FULLTEXT | SPATIAL} [INDEX | KEY] [index2] (`name`(32))  [ASC | DESC]);

创建空表以另一个表的结构，包括在原始表中定义的任何列属性（包括生成的列）和索引

CREATE [TEMPORARY] TABLE new_tbl LIKE SELECT * FROM orig_tbl;

创建表以另一个表的指定结构和数据；不能复制生成的列；如果要在创建的表中包含索引，则应在SELECT语句之前指定这些索引;IGNORE | REPLACE选项指示如何处理使用复制表时复制唯一键值的行,IGNORE将删除在唯一键值上复制现有行的行,REPLACE新行将替换具有相同唯一键值的行,如果未指定则重复的唯一键值将导致错误

CREATE TABLE [AS] [IGNORE | REPLACE] SELECT * FROM TABLE1
例：CREATE TABLE bar (id int(11),UNIQUE (n)) SELECT n FROM foo;

生成的列（虚拟列）；VIRTUAL：列值不存储，但是在任何BEFORE触发器之后立即读取行时进行评估 。虚拟列不占用任何存储空间。STORED：插入或更新行时，将评估并存储列值。存储的列确实需要存储空间，并且可以建立索引。默认为VIRTUAL

CREATE TABLE new_tbl (col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] [NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string'])
例：CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);

如果没有其他列引用生成的列，则可以重命名或删除它们。
虚拟生成的列不能更改为存储的生成的列，反之亦然。要解决此问题，请删除该列，然后添加新定义。
可以将未生成数据的列更改为已存储但不能更改为虚拟的生成列。
可以将已存储但不是虚拟生成的列更改为未生成的列。存储的生成的值成为非生成列的值。

修改表列

//添加字段 FIRST，AFTER，字段排序，排序指定字段前或后
ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition  [FIRST | AFTER col_name]

//添加索引,FULLTEXT表示全文索引
ALTER TABLE tbl_name ADD {FULLTEXT} {INDEX | KEY} [index_name] (col_name [(length)] [ASC | DESC],...) [COMMENT 'string']

//添加或删除列默认值
ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

//修改原列的名字和类型
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]

//删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name

//删除索引
ALTER TABLE tbl_name DROP {INDEX | KEY} index_name

//删除主键
ALTER TABLE tbl_name DROP PRIMARY KEY

//删除外键
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

//修改列类型、排序
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

//对列排序
ALTER TABLE tbl_name ORDER BY col_name [, col_name] ...

//重命名索引
ALTER TABLE tbl_name RENAME {INDEX | KEY} old_index_name TO new_index_name

column_definition {
date_type [NOT NULL | NULL] [DEFAULT default_value] [COMMENT 'string'] [COLLATE collation_name] [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
}

修改表

//修改表的字符集和排序规则
ALTER TABLE tbl_name [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

//停止更新指定非唯一索引或重新创建丢失的索引。MyISAM用一种比一键插入键快得多的特殊算法来做到这一点，因此在执行批量插入操作之前禁用键应该可以大大提高速度。
ALTER TABLE tbl_name {DISABLE | ENABLE} KEYS 

//丢弃和导入InnoDB表空间
ALTER TABLE tbl_name {DISCARD | IMPORT} TABLESPACE

//重命名表名
ALTER TABLE tbl_name RENAME [TO | AS] new_tbl_name

//修改自增值
ALTER TABLE tbl_name AUTO_INCREMENT [=] value

//修改注释
ALTER TABLE tbl_name COMMENT [=] 'string'

//修改存储引擎
ALTER TABLE tbl_name ENGINE [=] engine_name

//修改表的压缩格式
ALTER TABLE tbl_name ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}

//修改表空间；即使该TABLESPACE属性未更改其先前的值，操作也会始终导致全表重建；不支持将表从临时表空间移动到持久表空间。
ALTER TABLE tbl_name TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]

//已何种算法执行ALTER TABLE操作默认为DEFAULT（INPLACE）
COPY：对原始表的副本执行操作，并将表数据从原始表逐行复制到新表。不允许并发DML
INPLACE：操作避免复制表数据，但可以在适当位置重建表。在操作的准备和执行阶段可以简短地获取表上的独占元数据锁定。通常，支持并发DML。
ALTER TABLE tbl_name ALGORITHM [=] {DEFAULT | INPLACE | COPY}

删除表

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

删除表会删除该表的所有触发器。但不会自动删除专门为该表授予的特权。必须手动删除它们

重命名表

RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...

RENAME TABLE old_table TO new_table
等同于
ALTER TABLE old_table RENAME new_table

将表从一个数据库移动到另一个数据库：
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;

注意：

  • 要执行RENAME TABLE，必须没有活动的事务或用LOCK TABLES锁定表。重命名操作是原子完成的；重命名过程中，没有其他会话可以访问任何表。
  • 如果表具有触发器，则RENAME TABLE为其他数据库失败
  • 不能重命名TEMPORARY表，使用ALTER TABLE可以重命名TEMPORARY表
  • 可以重命名视图，但是不能移动到其他数据库
  • RENAME TABLE到新数据库时，不会将指定给表或视图的权限移动到新数据路
  • 重命名表将自动更新外键约束，除非重命名的约束名称已存在，这种情况下必须删除并重新创建外键才能正常运行

清空数据库

TRUNCATE [TABLE] tbl_name
  • 为了实现高性能，它绕过了删除数据的DML方法。因此，它不能回滚，也不会引发ON DELETE触发器，也不能对InnoDB具有父子外键关系的表执行。
  • 截断操作可删除并重新创建表，这比一一删除行要快得多，特别是对于大型表。
  • 持有表锁的表，不能截断
  • 任何AUTO_INCREMENT值都将重置为其初始值。
  • 针对分区表，TRUNCATE TABLE将保留分区
  • TRUNCATE TABLE会将Performance Schema表的列重置为0或NUll，而不是删除行
CATALOG
标签
mysql redis sqlserver
{/if}