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

Mysql数据库以后会经常使用而且命令比较多,为加深记忆,特此总结:

1、show database;查看当前服务器上的所有数据库:

wKiom1Re4cHDx-qnAACVt2kH99A244.jpg

2、create database [MYDATA];创建一个数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

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

[DEFAULT] COLLATE [=] collation_name 指定默认排序规则

如果不指定,则会以此继承上级的字符集或排序:服务器--->库--->表---->字段

wKioL1Re4y-xF0LWAAB3Ccgj7yY180.jpg

3、DROP MYDATA;删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

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


更改数据库的字符集:

ALTER {DATABASE|SCHEMA} db_name [DEFAULT] [CHARACTER SET=''] [DEFAULT] [COLLATE='']

4、use [MYDATA];使用一个数据库   

wKiom1Re4u7BUrAMAAAs9XY12Yc817.jpg

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

wKioL1Re4-zhfDcgAABZJ81-iKQ330.jpg

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} 

wKioL1Re8oqyxsc3AAGR2HCHAAM874.jpg


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:一个独有的表空间文件

wKiom1Re8xuRHK5aAAGCIwE5mrI447.jpg

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}] 指定表空间


wKioL1Re9L2yIDYDAAH6ev6IC74609.jpg9、表创建第二种方式:(复制表数据)

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]

wKioL1RfFZPjOgy6AAEGiecmyTU474.jpg

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]

wKiom1RfHKjz00-XAAGEPefNZb0489.jpg


修改字段类型和属性等::

CHANGE [COLUMN] old_col_name new_col_name column_definition

[FIRST|AFTER col_name]


举例:

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

wKiom1RfHcSTrxH6AALHaIqkEEU047.jpg


修改表名称:

方法一:ALTER TABLE

wKioL1RfIFDAInqEAAC2nKfM-84054.jpg

方法二:RENAME

wKiom1RfIKLRBw60AAEeyqmEJeQ594.jpg


修改表的引擎:

ALTER TABLE Tab_name ENGINE=[MyISAM | InnoDB];




wKiom1RfIlnDKLvfAAOCuYz6IKg469.jpg


指定排序标准的字段:

ORDER BY col_name [, col_name] ...


转换字符集及排序规则:

CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]


添加与删除索引:

wKiom1RfJVWR7b9aAANqrHqxPpQ281.jpg