mysql ddl可以改_mysql之DDL(CREATE ALTER DROP)

DDL：数据定义语言

CREATE

ALTER

DROP

主要操作集中在数据库和表。

数据库：

创建数据库：

CREATE DATEBASE|SCHEMA [IF NOT EXISTS] DBNANE [CREATE_SPECIFICATION];

CREATE_SEPECIFICATION

[DEFAULT]  CHARACTER SET [=] CHARSET_NAME 指定默认字符集

COLLATE [=] CLOOATION_NAME] 指定排序规则

修改数据库：

ALTER DATABASE|SCHEMA DBNAME ALTER_SPECIFICATION;

ALTER_PSECIFICATION:

CHARACTER SET [=] CHARSET_NAME 默认字符集

COLLATE [=] CLOOATION_NAME 排序规则

ALTER DATABASE|SCHEMA DBNAME UPGRADE DATA DIRECTORY NAME; 升级数据字典名称。例如5.0数据库迁移到5.1的服务器。在5.7.6以后可能会被废除。

删除数据库：

DROP DATEBASE|SCHEMA  [IF EXISTS] DBNAME;

设定默认数据库：

USE DBNAME;

实例：

1.查看数据库支持的字符集和排序规则：

mysql>SHOW CHARSET;

2.创建一个名为test的数据库，默认字符集为utf8，默认排序规则为uft8_general_ci

mysql>CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET=utf8  DEFAULT COLLATE = uft8_general_ci

mysql>SHOW DATABASES;

3.查看当前数据库使用的字符集：

mysql>USE test;

mysql>STATUS;

或

mysql>SHOW VARIABLES LIKE '%char%';

4.修改test2的字符集为big5，排序规则为big5_chinese_ci

mysql>ALTER DATABASE IF NOT EXISTS test CHARACTER SET=big5 COLLATE = big_chinese_ci

5.删除test数据库：

mysql>DROP DATABASE test;

6.升级mysql服务器后升级test库的数据字典

mysql>ALTER DATABASE test UPGRADE DATA DIRECTOYR NAME;

表：

创建表：

CREATE TABLE [IF NOT EXISTS] DBNAME.TBNAME(CREATE_DEFINITION,...) [TABLE_OPTION] [PARTITION_OPTIONS];

直接定义空表。

CREATE TABLE [IF NOT EXISTS] DBNAME.TBNAME(CREATE_DEFINITION,...) SELECT_STATEMENT;

从其他表中查询数据，并以此创建新表。创建完毕后表中已有数据。

CREATE TABLE [IF NOT EXISTS] DBNAME.TBNAME LIKE OLD_TBNAME;

仿照OLD_DBNAME表为模板,创建按空表。

CREATE_DEFINITION

COLNAME COLUMN_DEFINITION

PRIMARY KEY (COLNAME,...) 主键

UNIQUE INDEX|KEY(COLNAME,....) 唯一键

INDEX (COLNAME,...) 索引

COLNAME 字段名称

COLUMN_DEFINITION 字段定义

DATA_TYPE [NULL|NOT NULL] [DEFAULT VALUE] 指定数据类型

[AUTO_INCREMENT] [UNQIUE[KEY]|PRIMARY KEY]  自增长的主键或唯一键

COMMENT 'STRING' 描述信息

COLUMN_FORMAT{FIXED|DYNAMIC|DEFAULT}

STORAGE {DISK|MEMORY|DEFAULT}

REFERENC_DEFINITION 引用相关

TABLE_OPTION

ENGINE [=] ENGNAME 存储引擎

AUTO_INCREMENT [=] VALUE

AVG_ROW_LENGTH [=] VALUE 平均行的长度

[DEFAULT] CHARACTER SET [=] CHARSETNANE 字符集

CHECKSUM [=] {0|1} 是否启用校验和

[DEFAULT] COLLATE [=] COLLNAME 排序规则

COMMENT [=] 'STRING' 注释信息

DATA DIRECTORY [=] '/PATH/DIR' 数据目录，绝对路径

DELAY_KEY_WRITE [=] {0|1} 是否启用延迟键写入

INDEX_DIRECTORY [=] '/PATH/DIR' 索引目录，绝对路径

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} 行格式

TABLESPACE TABSPACENAME [STORAGE {DISK|MEMORY|DEFAULT}]

UNION [=] (TBNAME,...) 表空间

REFERENCE_DEFINITION 引用描述

REFERENCES TBNAME (INDEX_COLNAME,...) 指定要引用的表名

[MATCH FULL|PARTIAL|SIMPLE] 指定匹配范围

[ON DELETE REFERENCE_OPTION] 指定引用的表删除后的操作

[ON UPDATE REFERENCE_OPTION] 指定引用的表更新后的操作

REFERENCE_OPTION:

RESTRICT  严格。不允许。默认

CASCADE 级联，跟随引用表改动

SET NULL 设定为空

NO ACTION 无动作

修改表：

ALTER TABLE TBNAME [ALTER_SPECIFICATION,...] [PARTITION_OPTIONS]

ALTER_SPECIFICATION

ADD [COLUMN] COLNAME COLUMN_DEFINITION [FIRST|ATFER COLNAME] 添加字段

ADD [COLUMN] (COLNAME COLUMN_DEFINITION,...) 添加多个字段

ADD [INDEX|KEY] [INDEX_NAME] [INDEX_TYPE] (INDEX_COLNAME,...) [INDEX_OPTION] 添加INDEX

ADD PRIMARY KEY (COLNAME COLUMN_DEFINITION,...) 添加主键

ADD UNIQUE KEY (COLNAME COLUMN_DEFINITION,...) 添加唯一键

ADD FOREIGN KEY (INDEX_COLNAME,...) REFERENCE_DEFINITION 添加外键

ADD FULLTEXT (COLNAME COLUMN_DEFINITION,...) 添加全文索引

ADD SPATIAL (COLNAME COLUMN_DEFINITION,...) 添加空间索引

CHANGE [COLUMN] OLD_COLNAME NEW_COLNAME CLOLUMN_DEFINITION [FIRST|AFTER COLANME] 可以修改字段名，位置和字段定义

MODIFY [COLUMN] COLNAME COLUMN_DEFINITION [FIRST|AFTER CLONAME] 简单修改，修改位置或者是字段定义

RENAME  [TO|AS] NEW_TABNAME 表重命名。

COVNERT TO CHARACTER SET [=] CHARSET_NAME [COLLATE COLLATION_NAME] 更改字符集

重命名表：

RENAME TABLE OLD_TBNAME TO NEW_TBNAME

删除表：

DROP TABLE TBNAME [RESTRICT|CASCADE]

RESTRICT 严格。

CASCADE 级联。删除表的时候，若表被其他表引用，则会连被引用的表一起删除。

查看表属性状态：

SHOW TABLE STATUS LIKE ‘TBNAME’[\G]

查看某个库中的所有表：

SHOW TABLES [FROM DBNAME]

查看表的结构：

DESC TBNAME

实例：

1.直接创建表：

mysql>USE test

mysql>CREATE TABLE student (id INT UNSIGNED NOT NULL AUTO_INCREMENT  PRIMARY KEY,name CHAR(20) NOT NULL ,age TINYINT NOT NULL,gender enum(''M,'F'));

mysql>CREATE TABLE student2  (id INT UNSIGNED NOT NULL AUTO_INCREMENT,name CHAR(20) NOT NULL ,age TINYINT NOT NULL,PRIMARY KEY(id)，UNIQUE KEY(name),INDEX(age));

mysql>CREATE TABLE courses (CID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,CourName VARCHAR(50) NOT NULL,);

2.查看表中各自段数据类型：

mysql>DESC students;

3.删除表：

mysql>DROP TABLE student2;

4.列出当前数据库中所有的表：

mysql>SHOW TABLES;

5.重新创建student2表，指定存储引擎为MyISAM：

mysql>CREATE TABLE student2  (id INT UNSIGNED NOT NULL AUTO_INCREMENT,name CHAR(20) NOT NULL ,age TINYINT NOT NULL,PRIMARY KEY(id)，UNIQUE KEY(name),INDEX(age)) ENGINE = MyISAM;

6.查看student2表的各个属性状态：

mysql>SHOW TABLE STATUS LIKE 'student2'\G;

7根据现有表中的内容创建新表：

注意：通过这种方式创建的表，虽然字段和内容一样，但是新表和旧表相比，会丢失部分的表格式和表属性。

mysql>CREATE TABLE  test2 SELECT * FROM student2;

mysql>SHOW TABLES;

mysql>SELECT * FROM student2;

mysql>SELECT * FROM test2;

mysql>DESC courses;

mysql>DESC testcourses;

mysql>SHOW TABLE STATUS LIKE 'student2';

mysql>SHOW TABLE STATUS LIKE 'test2';

10.以其某张表为模板创建空表

注意：使用此方式创建的表和原表格式是完全一样的。

mysql>CREATE TABLE test3 LIKE student2;

mysql>DESC test3;

mysql>SHOW TABLES STATUS LIKE 'test3';

11.修改字段的名称和属性：

mysql>ALTER TABLE course CHANGE CourName CouName VARCHAR(50) NOT NULL;

13.新增字段：

mysql>ALTER TABLE coures ADD StartTime date DEFAULT '2015-07-05';

14.修改表名：

mysql>ALTER TABLE testcourses RENAME TO testcourse1;

或

mysql>RENAME TABLE testcourses TO testcourses1;

mysql>SHOW TABLES;

16.更改表的存储引擎：

注意：重命名的代价非常大。实质上是创建一个和旧表完全一样的新的空表，导入数据，删除旧表。

mysql>ALTER TABLE courses ENGINE = InnoDB;