MySQL – 基础语句一览表

表格一览

作用 语句
查看当前所有的数据库 SHOW DATABASES;
查看当前数据库中所有的表 SHOW TABLES;
查看当前表的结构 DESC table_name;
查看当前所有用户 SELECT User, Host FROM mysql.User;
选择指定数据库 USE database_name;
创建数据库 CREATE DATABASE database_name;
删除数据库 DROP DATABASE database_name;
查看支持的引擎 SHOW ENGINES \G 1

建立表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] [IGNORE | REPLACE] [AS] query_expression CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }

例子：2 

mysql> CREATE TABLE stu_info( -> account INT NOT NULL PRIMARY KEY UNIQUE KEY, -> name varchar(20) NOT NULL); Query OK, 0 rows affected (0.02 sec) mysql> DESC stu_info; +---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+
| account | int(11)     | NO   | PRI | NULL    |       |
| name | varchar(20) | NO | | NULL | | +---------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

删除表

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

例子：

mysql> DROP TABLE stu_info;
Query OK, 0 rows affected (0.02 sec)
  1. ego (\G) Send command to mysql server, display result vertically. ↩
  2. PRIMARY KEY ：主键 ； UNIQUE KEY： 唯一键约束 ↩