mysql之表的DDL操作

表的 DDL
表的DDL语句
mysql> ? contents
You asked for help about help category: “Contents”
For more information, type 'help ', where is one of the following
categories:
Account Management
Administration
Compound Statements
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Help Metadata
Language Structure
Plugins
Procedures
Storage Engines
Table Maintenance
Transactions
User-Defined Functions
Utility
mysql> ? data definition
You asked for help about help category: “Data Definition”
For more information, type 'help ', where is one of the following
topics:
ALTER DATABASE
ALTER EVENT
ALTER FUNCTION
ALTER INSTANCE
ALTER LOGFILE GROUP
ALTER PROCEDURE
ALTER SERVER
ALTER TABLE
ALTER TABLESPACE
ALTER VIEW
CONSTRAINT
CREATE DATABASE
CREATE EVENT
CREATE FUNCTION
CREATE INDEX
CREATE LOGFILE GROUP
CREATE PROCEDURE
CREATE SERVER
CREATE TABLE
CREATE TABLESPACE
CREATE TRIGGER
CREATE VIEW
DROP DATABASE
DROP EVENT
DROP FUNCTION
DROP INDEX
DROP PROCEDURE
DROP SERVER
DROP TABLE
DROP TABLESPACE
DROP TRIGGER
DROP VIEW
RENAME TABLE
TRUNCATE TABLE
mysql> ? create table
Name: ‘CREATE TABLE’
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,…)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,…)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,…)
[index_option] …
| {INDEX|KEY} [index_name] [index_type] (index_col_name,…)
[index_option] …
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,…)
[index_option] …
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,…)
[index_option] …
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,…) reference_definition
| CHECK (expr)

column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT ‘string’]
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
| data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
[NOT NULL | NULL] [[PRIMARY] KEY]

data_type:
BIT[(length)]
| TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
| DATE
| TIME[(fsp)]
| TIMESTAMP[(fsp)]
| DATETIME[(fsp)]
| YEAR
| CHAR[(length)] [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length) [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,…)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,…)
[CHARACTER SET charset_name] [COLLATE collation_name]
| JSON
| spatial_type

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING {BTREE | HASH}

index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT ‘string’

reference_definition:
REFERENCES tbl_name (index_col_name,…)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION

table_options:
table_option [[,] table_option] …

table_option:
ENGINE [=] engine_name
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] ‘string’
| COMPRESSION [=] {‘ZLIB’|‘LZ4’|‘NONE’}
| CONNECTION [=] ‘connect_string’
| DATA DIRECTORY [=] ‘absolute path to directory’
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {‘Y’ | ‘N’}
| INDEX DIRECTORY [=] ‘absolute path to directory’
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] ‘string’
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| STATS_AUTO_RECALC [=] {DEFAULT|0|1}
| STATS_PERSISTENT [=] {DEFAULT|0|1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name
| UNION [=] (tbl_name[,tbl_name]…)

partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] …)]

partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] ‘comment_text’ ]
[DATA DIRECTORY [=] ‘data_dir’]
[INDEX DIRECTORY [=] ‘index_dir’]
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] …)]

subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] ‘comment_text’ ]
[DATA DIRECTORY [=] ‘data_dir’]
[INDEX DIRECTORY [=] ‘index_dir’]
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]

query_expression:
SELECT … (Some valid select or union statement)

CREATE TABLE creates a table with the given name. You must have the
CREATE privilege for the table.
Rules for permissible table names are given in
http://dev.mysql.com/doc/refman/5.7/en/identifiers.html. By default,
the table is created in the default database, using the InnoDB storage
engine. An error occurs if the table exists, if there is no default
database, or if the database does not exist.
mysql>
一、CREATE TABLE 语句
1.1、创建表
语法：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [database_name.] <table_name>(
<column_name> <data_type> [[not] null],…
)
TEMPORARY：指明创建临时表,退出当前会话临时表消失
IF NOT EXISTS：如果要创建的表已经存在，强制不显示错误消息
database_name：数据库名
table_name：表名
column_name：列名
data_type：数据类型
例： 在名为test的数据库中创建penalties表
CREATE TABLE test.penalties(
paymentno INTEGER NOT NULL PRIMARY KEY,
playerno INTEGER NOT NULL,
payment_date DATE NOT NULL,
amount DECIMAL(7,2) NOT NULL);
首先test数据库必须已经存在。其次，语句完成后，test数据库不会自动成为当前数据库
确认表创建成功：
DESC test.penalties;
1.2、创建临时表
临时表是一张表，用来临时保存一些数据。保存这些数据的时候，尽量少的占用资源，删除数据的是时候，尽量少的占用资源。它只对创建它的用户可见。当会话结束时，MySQL自动删除临时表。
临时表消耗资源非常小，建立临时表，删除临时表以及里面的数据消耗资源都非常小。
例12 ：创建临时表sumpenalties，保存所有罚款的总和
CREATE TEMPORARY TABLE sumpenalties(total DECIMAL(10,2));
INSERT INTO sumpenalties
SELECT sum(amount) FROM penalties;
SELECT * FROM sumpenalties;
断开连接，再重新连接后执行查询，抛出异常：
错误代码： 1146
Table ‘tennis.sumpenalties’ doesn‘t exist
用户可以创建一个和已有的普通表名字相同的临时表。在这种情况下，该用户只能看到临时表而看不见同名的普通表。当临时表被删除后，才可以看到普通表
例13：使用相同的名字创建一个普通表和临时表
CREATE TABLE testable (c1 INTEGER);
INSERT INTO testable VALUES(1);
CREATE TEMPORARY TABLE testable (c1 INTEGER,c2 INTEGER);
INSERT INTO testable VALUES(3,5);
SELECT * FROM testable;

DROP TABLE testable;
SELECT * FROM testable;

1.3、使用if not exists选项
当创建表时，如果表已经存在了，则MySQL会返回出错消息。添加IF NOT EXISTS选项，则强制不显示这个出错消息。语句执行失败。
例14：创建teams表，如果表已经存在，不显示错误消息
CREATE TABLE IF NOT EXISTS teams(
teamno INTEGER NOT NULL PRIMARY KEY,
playerno INTEGER NOT NULL,
division char(6) NOT NULL);
1 queries executed, 1 success, 0 errors, 1 warnings
该选项也可以用于临时表
1.4、根据已有的表来创建新表
语法1：CREATE TABLE new_tbl LIKE orig_tbl;
这种语法，将从源表复制列名、数据类型、大小、非空约束以及索引。而表的内容以及其它约束不会复制，新表是一张空表。
例15：
CREATE TABLE copy_teams LIKE teams;
语法2：CREATE TABLE new_tbl [AS] SELECT {*|column,…} FROM orig_tbl;
新表的结构由select列表决定。同时把查询返回的结果集中的行插入到目标表中。这种语法同样只能把非空约束带入到新表中。也不会复制索引
例16：
CREATE TABLE copy_teams2
AS SELECT * FROM teams;
如果想控制新表的列名，则在select查询中使用列别名。
例17：
CREATE TABLE copy_teams3 AS
SELECT teamno tno,playerno pno,division
FROM teams;
例18 ：创建teams表的一个临时副本，并且给这个表分配相同的名字
CREATE TEMPORARY TABLE teams
AS SELECT * FROM teams;
这种方式做练习最合适。当会话结束后，临时表被自动删除，原始表teams中的内容不受任何影响

如果在表名后面指定了列名（和原始表中的列名相同），则可以改变列的大小和非空约束
例19 ：创建teams表的副本，其中playno列允许空值，并且division列的大小从6变为10
CREATE TABLE teams_copy5(
teamno INTEGER NOT NULL PRIMARY KEY,
playerno INTEGER NULL,
division char(10) NOT NULL
) AS SELECT * FROM teams;
如果表名后面指定的列名和原始表中的列名不同，则它作为一个新的列。
例20：
CREATE TABLE teams_copy6(
playerno INTEGER NULL,
comments char(100)
) AS SELECT * FROM teams;
SELECT * FROM teams_copy6;
注意：这种方式中的select语句可以非常复杂，想要创建什么复杂的信息时可以将select语句先写出来，然后再加上create语句。
二、DROP TABLE语句
2.1、删除表之前要考虑：
1、确认这个表没有人在使用，确认是一个废除的表
2、确认数据库mysql>status；
DROP TABLE语句：
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] … [RESTRICT | CASCADE]
TEMPORARY：用于删除临时表。删除临时表不会结束当前的事务。
IF EXISTS：用于在表不存在时，阻止错误消息的显示
[RESTRICT | CASCADE]：现在什么也不做
当一个表被删除时，所有依赖于该表的对象也被删除，例如约束、索引、视图和权限等
注意：当有一个临时表和表重名时，删除时不加temporary也是删除临时表，要慎重。
例1：
DROP TABLE teams_new;

DROP TABLE departments;
错误代码： 1051
Unknown table ‘tennis.departments’
DROP TABLE IF EXISTS departments;
共 0 行受到影响， 1 个警告

当一个表有外键指向它时，则不能被删除
DROP TABLE dept;
错误代码： 1217
Cannot DELETE OR UPDATE a parent ROW: a FOREIGN KEY CONSTRAINT fails
2.2、重命名表
RENAME TABLE tbl_name
TO new_tbl_name [, tbl_name2 TO new_tbl_name2] …
可以使用ALTER TABLE语句替换该语句
三、ALTER TABLE语句（非常危险）
修改表的操作
1、增加一个列
alter table t1 add COLUMN sex char（1） default ‘男’ not null；默认加在最后面
alter table t1 add COLUMN b_date date not null after name；加在name后面
2、删除一个列
alter table t1 drop column b_date；
3、修改一个列的数据类型
varchar–int
alter table t1 modify sex varchar(10)；
4、修改一个列的数据类型的宽度
Varchar（10）—varchar（20）
alter table t1 modify sex varchar(15)；
5、对表进行约束的添加和删除
联合主键
几个列合起来形成一个主键

对表进行操作时需要做一下几步：
1、字典级别改一下
快
2、对100万个数据快进行修改（该步骤需要将所有的数据调入内存进行修改）
时间很长

在对表进行操作时，别人是不能对表进行任何操作的，可以认为这个表是挂起的，所以要慎重。
mysql> help alter table
Name: ‘ALTER TABLE’
Description:
Syntax:
ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] …]
[partition_options]
alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,…)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,…) [index_option] …
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,…) [index_option] …
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,…) [index_option] …
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,…) [index_option] …
| ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,…) [index_option] …
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,…)
reference_definition
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO|AS] new_tbl_name
| RENAME {INDEX|KEY} old_index_name TO new_index_name
| ORDER BY col_name [, col_name] …
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| FORCE
| {WITHOUT|WITH} VALIDATION
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| DISCARD PARTITION {partition_names | ALL} TABLESPACE
| IMPORT PARTITION {partition_names | ALL} TABLESPACE
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| REMOVE PARTITIONING
| UPGRADE PARTITIONING
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT ‘string’
table_options:
table_option [[,] table_option] … (see CREATE TABLE options)
partition_options:
(see CREATE TABLE options)
1、添加、修改和删除列
ALTER [IGNORE] TABLE tbl_name
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] |
ADD [COLUMN] (col_name column_definition,…)|
MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] |
DROP [COLUMN] col_name |
CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
3.1、添加列
新列自动成为表中的最后一个列，除非指定了BEFORE或AFTER选项
表中已有的行在新列上自动得到NULL值或默认值
CREATE TABLE teams_copy1
AS SELECT * FROM teams;
例：添加type1列给teams_copy1表，该列的值表示它是男子队或女子队
ALTER TABLE teams_copy1
ADD type1 char(1);
SELECT * FROM teams_copy1;

ALTER TABLE teams_copy1
ADD type2 char(1) DEFAULT ‘1’;
SELECT * FROM teams_copy1;

例：指定新列的位置
ALTER TABLE teams_copy1
ADD type3 char(1) AFTER playerno ;
SELECT * FROM teams_copy1;

如果添加新列时指定了NOT NULL约束，则数字列自动得到0，字符串列自动得到空字符串，日期类型自动得到0日期，时间类型自动得到00:00:00
ALTER TABLE teams_copy1
ADD (type4 char(1) NOT NULL,
type5 INTEGER NOT NULL,
type6 DATE NOT NULL
type7 time not null);
SELECT * FROM teams_copy1;

3.2、删除列
列值是否有数据都可以删除。依赖于该列的其它数据库对象，如索引、权限也将被删除
例：
ALTER TABLE teams_copy1
DROP type6;
SELECT * FROM teams_copy1;
3.3、修改列
例：
CREATE TABLE players_copy1
AS SELECT * FROM players;
把town列的宽度从30增加到40
ALTER TABLE players_copy1
MODIFY town VARCHAR(40) NOT NULL;

DESC players_copy1;
SELECT * FROM players_copy1;

减少列宽必须保证原有的值要放得下，否则出错
例：把town列的宽度从40减少到5
ALTER TABLE players_copy1
MODIFY town VARCHAR(5) NOT NULL;
错误代码： 1265
DATA truncated FOR COLUMN ‘town’ AT ROW 1

DESC players_copy1;

改变数据类型时，列中的值必须能转变为新的类型
例：把playerno的类型从integer改为smallint
ALTER TABLE players_copy1
MODIFY playerno SMALLINT NOT NULL;
例： 把name列的类型从varchar改为smallint
ALTER TABLE players_copy1
MODIFY NAME SMALLINT NOT NULL;
错误代码： 1366
Incorrect INTEGER VALUE: ‘Parmenter’ FOR COLUMN ‘name’ AT ROW 1
修改列名：
例：把列名birth_date改为date_of_birth
ALTER TABLE players_copy1
CHANGE birth_date date_of_birth DATE;
SELECT * FROM players_copy1;
3.4、修改约束
可以添加或删除约束，但是不能修改一个已有的约束
ALTER [IGNORE] TABLE tbl_name
ADD [CONSTRAINT [symbol]] PRIMARY KEY(col_name,…) |
ADD [CONSTRAINT [symbol]] UNIQUE (col_name,…) |
ADD [CONSTRAINT [symbol]] FOREIGN KEY (col_name,…) References parent_table(col_name)|
DROP PRIMARY KEY| DROP FOREIGN KEY fk_symbol| DROP {INDEX|KEY} index_name
例如：将一个表的两个列定义为一个主键
alter table chengji add constraint p_chengji primary key （s_id，k_id）；
例：定义两张表t1和t2
CREATE TABLE t1(
a INTEGER NOT NULL PRIMARY KEY,
b INTEGER NOT NULL);
CREATE TABLE t2(
a INTEGER NOT NULL PRIMARY KEY,
b INTEGER NOT NULL,
CONSTRAINT c1 UNIQUE(b),
CONSTRAINT fk1 FOREIGN KEY(a) REFERENCES t1(a));
添加外键约束
ALTER TABLE t1
ADD CONSTRAINT fk2 FOREIGN KEY(a) REFERENCES t2(a);
删除players_small表的主键
ALTER TABLE players_small
DROP PRIMARY KEY;
删除外键fk2
ALTER TABLE t1
DROP FOREIGN KEY FK2;
删除唯一性约束：删除对应的索引即可
ALTER TABLE t2
DROP INDEX c1;