Mysql 基础知识

Mysql 基础

Mysql定义语言 DDL（data definition language）

• CREATE

   // CREATE DATABASE 创建数据库指定字符集
    CREATE DATABASE db_name DEFAULT CHARACTER SET utf8

   // CREATE  TABLE 建索引 (建议在重建索引使用)
    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...);

   // CREATE  TABLE 建表（同时建立索引）
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(
        col_name type [NOT NULL | NULL] 
                    [DEFAULT default_value]
                    [AUTO_INCREMENT] 
                    [UNIQUE [KEY] | [PRIMARY] KEY] -- 这里的PRIMARY KEY == KEY 、UNIQUE KEY == UNIQUE
                    [COMMENT '注解'] 
                    [reference_definition],

          -- 主键（联合主键）key 的一种
          |[CONSTRAINT] PRIMARY KEY [USING BTREE|HASH] (index_col_name,...) -- 指定索引类型
          -- 索引（联合索引）实现层面
          | KEY [index_name] [USING BTREE|HASH] (index_col_name,...) 
          -- 索引（联合索引） 与key 不同是 物理层
          | INDEX [index_name] [USING BTREE|HASH] (index_col_name,...) 
          -- 唯一索引 （主键一定是唯一索引）
          | [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [USING BTREE|HASH] (index_col_name,...) 
          | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) [reference_definition]  
          | CHECK (expr) [USING BTREE|HASH]
    )

    ①[USING BTREE|HASH] -- 可以放在名称后面也可以放到最后（习惯放中间）
    ②key 与index 是同可以等价使用（除主键）
    ③ varchar 或char 类型需要长度设置index_col_name(length)
// 创建和old_tbl_name 和结构一样的表
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    LIKE old_tbl_name ;

• DROP

    // 删除库
    DROP {DATABASE | SCHEMA} [IF EXISTS] db_name；
     // 删除索引
    DROP INDEX index_name ON  tble_name
    // 删除表
    DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]

• ALTER

     // ALTER TABLE 修改表
    ALTER [IGNORE] TABLE tbl_name
        alter_specification [, alter_specification] 

    alter_specification:
        ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
      | ADD [COLUMN] (column_definition,...)
      | ADD INDEX [index_name] [index_type] (index_col_name,...)
      | ADD [CONSTRAINT [symbol]]
            PRIMARY KEY [index_type] (index_col_name,...)
      | ADD [CONSTRAINT [symbol]]
            UNIQUE [index_name] [index_type] (index_col_name,...)
      | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
      | 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 column_definition
            [FIRST|AFTER col_name]
      | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
      | DROP [COLUMN] col_name
      | DROP PRIMARY KEY
      | DROP INDEX index_name
      | DROP FOREIGN KEY fk_symbol
      | DISABLE KEYS
      | ENABLE KEYS
      | RENAME [TO] new_tbl_name
      | ORDER BY col_name
      | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
      | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
      | DISCARD TABLESPACE
      | IMPORT TABLESPACE
      | table_options
      | partition_options
      | ADD PARTITION partition_definition
      | DROP PARTITION partition_names
      | COALESCE PARTITION number
      | REORGANIZE PARTITION partition_names INTO (partition_definitions)
      | ANALYZE PARTITION partition_names
      | CHECK PARTITION partition_names
      | OPTIMIZE PARTITION partition_names
      | REBUILD PARTITION partition_names
      | REPAIR PARTITION partition_names

• RENAME

RENAME TABLE tbl_name TO new_tbl_name

Mysql操作语言 DML（data definition language）