创建数据库：

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

如：

mysql> CREATE SCHEMA IF NOT EXISTS users CHARACTER SET 'gbk' COLLATE 'gbk_chinese_ci';

也可以：

mysql> CREATE DATABASE users;

删除数据库：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

创建表：

方法1：直接定义一张空表

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

例：
***************************************
创建表：

mysql> CREATE TABLE NAME_TB1(NAME_ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, NAME CHAR(30));

向该表插入数据：

mysql> INSERT INTO NAME_TB1 (NAME) VALUES ('zhangsan'),('lisi'),('wangwu');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看该表数据：

mysql> SELECT * FROM NAME_TB1;
+---------+----------+
| NAME_ID | NAME     |
+---------+----------+
|       1 | zhangsan |
|       2 | lisi            |
|       3 | wangwu   |
+---------+----------+
3 rows in set (0.00 sec)

查看表索引：

mysql> SHOW INDEXES FROM NAME_TB1;

***************************************

方法2：从其他表中查询数据，并以之创建新表

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

例：
**************************************
创建表：

mysql> CREATE TABLE test_name SELECT * FROM NAME_TB1 WHERE NAME_ID <=2;
Query OK, 2 rows affected (0.48 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看表内容：

mysql> SELECT * FROM test_name;
+---------+----------+
| NAME_ID | NAME     |
+---------+----------+
|       1 | zhangsan |
|       2 | lisi     |
+---------+----------+
2 rows in set (0.01 sec)

查看对比两个表结构：

mysql> DESC test_name;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| NAME_ID | int(10) unsigned | NO   |     | 0       |       |
| NAME    | char(30)         | YES  |     | NULL    |       |
+---------+------------------+------+-----+---------+-------+
2 rows in set (0.18 sec)

mysql> DESC NAME_TB1;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| NAME_ID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| NAME    | char(30)         | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

**************************************

方法3：以其他表为模板创建一张空表：

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

例：
**************************************
创建表：

mysql> CREATE TABLE NAME_TB2 LIKE NAME_TB1;
mysql> DESC NAME_TB2;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| NAME_ID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| NAME    | char(30)         | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
2 rows in set (0.05 sec)

**************************************

修改表定义：

ALTER [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]

1. 添加、删除、修改字段：

2. 如为name_tb1表添加一个age字段：

mysql> alter table name_tb1 add age int UNSIGNED not null;  ##此处对字段的定义同创建表

mysql> desc  name_tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)                   | NO   |     | NULL    |       |
| name  | char(20)             | YES  |     | NULL    |       |
| age   | int(10) unsigned   | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)

删除name_tb1表中的name字段：

mysql> alter table name_tb1  drop name;
Query OK, 0 rows affected (1.72 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  name_tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
| age   | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.08 sec)

修改name_tb1表中的age为name，并调整字段类型为char：

mysql> alter table name_tb1  CHANGE age name char(20);
Query OK, 0 rows affected (1.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  name_tb1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | NO   |     | NULL    |       |
| name  | char(20) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.16 sec)

如果仅需修改字段属性，使用：

mysql> alter table name_tb1  col_name column_definition;

添加、删除索引：

查看当前索引：

mysql> show indexes from name_tb1;
Empty set (0.00 sec)

##当前索引为空

添加索引：

如：为表name_tb1添加唯一键索引，字段为id

mysql> alter table name_tb1 add unique key (id);
Query OK, 0 rows affected (1.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除与删除修改字段类似，不在举例！

使用create index创建索引：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[algorithm_option | lock_option] ..

如：

mysql> CREATE INDEX name_on_usertb1 ON user_tb1 (name);
Query OK, 0 rows affected (1.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEXES FROM user_tb1;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user_tb1 |          0 | id              |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| user_tb1 |          1 | name_on_usertb1 |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

 

修改表名：

方法一：

mysql> show tables ;
+------------------+
| Tables_in_testdb |
+------------------+
| name_tb1         |
| name_tb2         |
+------------------+
2 rows in set (0.01 sec)

##将name_tb1修改为use_tb1

mysql> ALTER TABLE name_tb1 RENAME TO user_tb1;
Query OK, 0 rows affected (0.10 sec)

mysql> show tables ;
+------------------+
| Tables_in_testdb |
+------------------+
| name_tb2         |
| user_tb1         |
+------------------+
2 rows in set (0.00 sec)

方法二：

mysql> RENAME TABLE name_tb2 TO test_user;
Query OK, 0 rows affected (0.15 sec)

mysql> show tables ;
+------------------+
| Tables_in_testdb |
+------------------+
| test_user        |
| user_tb1         |
+------------------+
2 rows in set (0.00 sec)