mysql DDL常用命令

32ew

    DDL（Data Definition Languages）语句：数据定义语言，简单说就是对数据库内部的对象进行创建、修改、删除的操作语言。DDL语句更多的被数据库管理人员（DBA）使用，一般开发人员很少用

Mysql数据库以后会经常使用而且命令比较多，为加深记忆，特此总结：
1、show database；查看当前服务器上的所有数据库：

2、create database [MYDATA]；创建一个数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
[DEFAULT] CHARACTER SET [=] charset_name 指定默认字符集
[DEFAULT] COLLATE [=] collation_name 指定默认排序规则
如果不指定，则会以此继承上级的字符集或排序：服务器--->库--->表---->字段

3、DROP MYDATA；删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

删除     数据库           如果存在    数据库名

更改数据库的字符集：
ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']
4、use [MYDATA];使用一个数据库   

5、SHOW TABLES；查看数据库里面的表有哪些

6、创建表；
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)  创建定义
[table_options]  表选项
[partition_options] 分隔属性

(create_definition,...)中包含：
字段的定义：字段名、类型和类型修饰符
键、约束或索引：
PRIMARY KEY, UNIQUE KEY, FOREIGN KEY, CHECK
{INDEX|KEY}


7、查看表的信息和默认引擎：
SHOW TABLE STATUS LIKE 'mytab'G;  #G自动换行显示表信息
创建的表默认引擎为InooDB
MyISAM表，每个表有三个文件，都位于数据库目录中，
tb_name.frm:表结构定义
tb_name.MYD：数据文件
tb_name.MYI：索引文件
InnoDB有两种存储方式：
1、默认方式：每表有一个独立文件和一个共享的文件：
tb_name.frm:表结构定义，位于数据库目录中
ibdata#共享的表空间文件，默认位于数据目录（datadir指向的目录）中
2、独立的表空间
tb_name.frm；每表有一个表结构文件
tb_name.idb:一个独有的表空间文件

8、创建表第一种方式，并指定引擎;
创建表的选项：
ENGINE [=] engine_name #引擎类型
|AUTO_INCREMENT [=] value 指定 AUTO_INCREMENT的值
[DEFAULT] CHARACTER SET [=] charset_name 默认字符集
CHECKSUM [=] {0 | 1} 插入数据是否效验表，会增大系统开销
[DEFAULT] COLLATE [=] collation_name 排序规则
COMMENT [=] 'string' 表注释
DATA DIRECTORY [=] 'absolute path to directory' 数据目录位置
DELAY_KEY_WRITE [=] {0 | 1} 延迟写入
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} 表格式
TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] 指定表空间

9、表创建第二种方式：（复制表数据）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
select_statement
mysql> CREATE table t4 SELECT *  FROM t3;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0
10、表创建第三种方式：基于某张表的结构创建一张空表，即等于复制表的结构而不包含数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
mysql> CREATE TABLE t5 LIKE t3;
Query OK, 0 rows affected (0.29 sec)
10、表删除
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

11、表修改：
mysql> HELP ALTER TABLE;
Name: 'ALTER TABLE'
Description:
Syntax:
ALTER [ONLINE | OFFLINE] [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
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | 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
  | 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
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
  | 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}
  | PARTITION BY partitioning_expression
  | REMOVE 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)

举例：
在Nanme下面插入年龄，并指定默认为F：
mysql> ALTER  TABLE t3  ADD AG ENUM('F','M') NOT NULL DEFAULT 'F' AFTER Name ;
Query OK, 0 rows affected (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc t3;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| Name   | varchar(50)         | NO   | PRI | NULL    |       |
| Age    | enum('F','M')       | NO   |     | F       |       |
| Gender | tinyint(3) unsigned | NO   |     | NULL    |       |
+--------+---------------------+------+-----+---------+-------+

12、表修改删除相关：：
删除字段：

mysql> ALTER TABLE t3  DROP AG;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
修改字段：ALTER TABLE Tab_name；

只修改字段名称、位置或属性

MODIFY [COLUMN] col_name column_definition  [FIRST | AFTER col_name]

修改字段类型和属性等：：
CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]

举例：
将表3中的Gender字段更改为NewGender并修改默认属性和位置：


修改表名称：
方法一：ALTER TABLE

方法二：RENAME



修改表的引擎：
ALTER TABLE Tab_name ENGINE=[MyISAM | InnoDB];





指定排序标准的字段：
ORDER BY col_name [, col_name] ...

转换字符集及排序规则：
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

添加与删除索引：