SQL语句之：DDL

DDL语句

CREATE，ALTER，DROP

CREATE

数据库操作

• 语法：

  CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
      [create_specification] ...
  
  create_specification:
      [DEFAULT] CHARACTER SET [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
  

  示例：

  MariaDB [(none)]> CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
  Query OK, 1 row affected (0.00 sec)
  

表操作

• 语法1：直接创建

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      (create_definition,...)
      [table_options]
      [partition_options]
  

  示例：

  MariaDB [test]> CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED);
  Query OK, 0 rows affected (0.21 sec)
  

• 语法2：通过查询现存表创建；新表会被直接插入查询而来的数据

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      [(create_definition,...)]
      [table_options]
      [partition_options]
      select_statement
  

  示例：

  MariaDB [test]> CREATE TABLE test_user SELECT user,host,password FROM mysql.user;   
  Query OK, 6 rows affected (0.00 sec)
  Records: 6  Duplicates: 0  Warnings: 0
  
  MariaDB [test]> DESC test_user;
  +----------+----------+------+-----+---------+-------+
  | Field    | Type     | Null | Key | Default | Extra |
  +----------+----------+------+-----+---------+-------+
  | user     | char(16) | NO   |     |         |       |
  | host     | char(60) | NO   |     |         |       |
  | password | char(41) | NO   |     |         |       |
  +----------+----------+------+-----+---------+-------+
  3 rows in set (0.00 sec)
  
  MariaDB [test]> SELECT * FROM test_user;
  +---------+------------+-------------------------------------------+
  | user    | host       | password                                  |
  +---------+------------+-------------------------------------------+
  | root    | localhost  | *FF081C01A0F7F5463A32687F6655FC4BBCC87E46 |
  | root    | 127.0.0.1  | *FF081C01A0F7F5463A32687F6655FC4BBCC87E46 |
  | root    | ::1        | *FF081C01A0F7F5463A32687F6655FC4BBCC87E46 |
  | wpuser  | 172.16.0.% | *FF081C01A0F7F5463A32687F6655FC4BBCC87E46 |
  | zbxuser | 172.16.0.% | *FF081C01A0F7F5463A32687F6655FC4BBCC87E46 |
  | pmauser | 172.16.0.% | *FF081C01A0F7F5463A32687F6655FC4BBCC87E46 |
  +---------+------------+-------------------------------------------+
  6 rows in set (0.00 sec)
  

• 语法3：通过复制现存的表的表结构创建，但不复制数据

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      { LIKE old_tbl_name | (LIKE old_tbl_name) }
  

  示例：

  MariaDB [test]> CREATE TABLE test_user2 LIKE test_user;
  Query OK, 0 rows affected (0.00 sec)
  
  MariaDB [test]> SELECT * FROM test_user2;              
  Empty set (0.00 sec)
  
  MariaDB [test]> DESC test_user2;
  +----------+----------+------+-----+---------+-------+
  | Field    | Type     | Null | Key | Default | Extra |
  +----------+----------+------+-----+---------+-------+
  | user     | char(16) | NO   |     |         |       |
  | host     | char(60) | NO   |     |         |       |
  | password | char(41) | NO   |     |         |       |
  +----------+----------+------+-----+---------+-------+
  3 rows in set (0.00 sec)
  

ALTER

数据库操作

• 语法：

  ALTER {DATABASE | SCHEMA} [db_name]
      alter_specification ...
  ALTER {DATABASE | SCHEMA} db_name
      UPGRADE DATA DIRECTORY NAME
  
  alter_specification:
      [DEFAULT] CHARACTER SET [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
  

表操作

• 语法：

  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
    | MAX_ROWS = rows
    | 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
  

• 常见用法示例：
  help ALTER TABLE：查看帮助用法
  ALTER TABLE students RENAME s1;：修改表的名称
  ALTER TABLE s1 ADD gender ENUM('m','f');：添加一个字段
  ALTER TABLE s1 ADD phone varchar(11) AFTER name;：在表中某字段后添加一个字段
  ALTER TABLE s1 MODIFY phone int;：只修改字段类型
  ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);：修改字段名称和类型
  ALTER TABLE s1 CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;：修改字段名称并设定其相关属性
  ALTER TABLE s1 ADD UNIQUE KEY(name);：添加唯一键
  ALTER TABLE s1 ADD INDEX(age);：添加索引
  SHOW INDEXES FROM students;：查看索引
  ALTER TABLE s1 DROP COLUMN mobile;：删除字段
  ALTER TABLE s1 DROP age;：删除某字段
  DESC students;：查看表结构

DROP

数据库操作

• 语法：

  DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
  

  示例：

  MariaDB [test]> DROP DATABASE db1;
  Query OK, 0 rows affected (0.00 sec)
  

表操作

• 语法：

  DROP [TEMPORARY] TABLE [IF EXISTS]
      tbl_name [, tbl_name] ...
      [RESTRICT | CASCADE]
  

  示例：

  MariaDB [db1]> DROP TABLE user;
  Query OK, 0 rows affected (0.00 sec)