创建数据库：

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)