创建数据库：
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]
添加、删除、修改字段： 如为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)
查看更多关于MySQL基本SQL语句之常用管理SQL的详细内容...