mysql命令简述

发表于 分类于

记录mysql常用命令。

SQL四部分

数据定义语言（Data Definition Language， DDL）

用来创建或删除数据库以及表等对象，主要包含以下几种命令：

  • DROP：删除数据库和表等对象
  • CREATE：创建数据库和表等对象
  • ALTER：修改数据库和表等对象的结构

数据操作语言（Data Manipulation Language， DML）

用来变更表中的记录，主要包含以下几种命令：

  • SELECT：查询表中的数据
  • INSERT：向表中插入新数据
  • UPDATE：更新表中的数据
  • DELETE：删除表中的数据

数据查询语言（Data Query Language， DQL）

用来查询表中的记录，主要包含 SELECT 命令，来查询表中的数据。

数据控制语言（Data Control Language， DCL）

用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对数据库中的用户设定权限。主要包含以下几种命令：

  • GRANT：赋予用户操作权限
  • REVOKE：取消用户的操作权限
  • COMMIT：确认对数据库中的数据进行的变更
  • ROLLBACK：取消对数据库中的数据进行的变更

SQL的基本书写规则

  • SQL 语句要以分号;结尾
  • SQL 语句不区分大小写
  • 字符串的使用英文单引号’将字符串括起来，例 ‘abc’
  • 单词需要用半角空格或者换行来分隔

数据库相关的操作

查看或显示数据库（SHOW DATABASES语句）

1
SHOW DATABASES [LIKE '数据库名']

语法说明如下：

  • LIKE 从句是可选项，用于匹配指定的数据库名称。LIKE 从句可以部分匹配，也可以完全匹配。
  • 数据库名由单引号’ ‘包围。

创建数据库（CREATE DATABASE语句）

1
2
3
CREATE DATABASE [IF NOT EXISTS] <数据库名>
    [[DEFAULT] CHARACTER SET <字符集>]
    [[DEFAULT] COLLATE <校对规则名>];

[]中的内容是可选的。语法说明如下：

  • <数据库名>：创建数据库的名称。MySQL 的数据存储区将以目录方式表示 MySQL 数据库，因此数据库名称必须符合操作系统的文件夹命名规则，不能以数字开头，尽量要有实际意义。注意在 MySQL 中不区分大小写。
  • IF NOT EXISTS：在创建数据库之前进行判断，只有该数据库目前尚不存在时才能执行操作。此选项可以用来避免数据库已经存在而重复创建的错误。
  • [DEFAULT] CHARACTER SET：指定数据库的字符集。指定字符集的目的是为了避免在数据库中存储的数据出现乱码的情况。如果在创建数据库时不指定字符集，那么就使用系统的默认字符集。
  • [DEFAULT] COLLATE：指定字符集的默认校对规则。

MySQL 的字符集（CHARACTER）和校对规则（COLLATION）是两个不同的概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则定义了比较字符串的方式。后面我们会单独讲解 MySQL 的字符集和校对规则。

示例：

1
2
3
4
mysql> CREATE DATABASE IF NOT EXISTS test_db_char
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_chinese_ci;
Query OK, 1 row affected (0.03 sec)

查看数据库创建语句

1
SHOW CREATE DATABASE <数据库名>;

示例：

1
2
3
4
5
6
7
mysql> SHOW CREATE DATABASE test_db_char;
+--------------+-----------------------------------------------------------------------+
| Database     | Create Database                                                       |
+--------------+-----------------------------------------------------------------------+
| test_db_char | CREATE DATABASE `test_db_char` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

修改数据库：ALTER DATABASE

1
2
3
4
ALTER DATABASE [数据库名] {
    [ DEFAULT ] CHARACTER SET <字符集名> |
    [ DEFAULT ] COLLATE <校对规则名>
}

语法说明如下：

  • ALTER DATABASE 用于更改数据库的全局特性。
  • 使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
  • 数据库名称可以忽略，此时语句对应于默认数据库。
  • CHARACTER SET 子句用于更改默认的数据库字符集。

示例：

使用命令行工具将数据库 test_db 的指定字符集修改为 gb2312，默认校对规则修改为 utf8_unicode_ci，输入 SQL 语句与执行结果如下所示：

1
2
3
4
5
6
7
8
9
10
11
mysql> CREATE DATABASE test_db
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE 'utf8_unicode_ci';

mysql> SHOW CREATE DATABASE test;
+----------+---------------------------------------------------------------------------------------+
| Database | Create Database                                                                       |
+----------+---------------------------------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+----------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库（DROP DATABASE语句）

1
DROP DATABASE [IF EXISTS] <数据库名>;

语法说明如下：

  • <数据库名>：指定要删除的数据库名。
  • IF EXISTS：用于防止当数据库不存在时发生错误。
  • DROP DATABASE：删除数据库中的所有表格并同时删除数据库。使用此语句时要非常小心，以免错误删除。如果要使用 DROP DATABASE，需要获得数据库 DROP 权限。

选择数据库（MySQL USE语句）

1
USE <数据库名>;

示例：

1
2
mysql> USE test_db;
Database changed

数据库表相关的操作

修改数据表的存储引擎

1
ALTER TABLE <表名> ENGINE=<存储引擎名>;

示例：

1
ALTER TABLE student ENGINE=MyISAM;

创建数据表（CREATE TABLE语句）

1
2
3
4
CREATE TABLE <表名> ([表定义选项])[表选项][区分选项];

其中，[表定义选项]的格式为：
<列名1> <类型1> [,…] <列名n> <类型n>

CREATE TABLE 语句的主要语法及使用说明如下：

  • CREATE TABLE：用于创建给定名称的表，必须拥有表CREATE的权限。

  • <表名>：指定要创建表的名称，在 CREATE TABLE 之后给出，必须符合标识符命名规则。表名称被指定为 db_name.tbl_name，以便在特定的数据库中创建表。无论是否有当前数据库，都可以通过这种方式创建。在当前数据库中创建表时，可以省略 db-name。如果使用加引号的识别名，则应对数据库和表名称分别加引号。例如，’mydb’.’mytbl’ 是合法的，但 ‘mydb.mytbl’ 不合法。

  • <表定义选项>：表创建定义，由列名（col_name）、列的定义（column_definition）以及可能的空值说明、完整性约束或表索引组成。

  • 默认的情况是，表被创建到当前的数据库中。若表已存在、没有当前数据库或者数据库不存在，则会出现错误。

提示：使用 CREATE TABLE 创建表时，必须指定以下信息：

  • 要创建的表的名称不区分大小写，不能使用SQL语言中的关键字，如DROP、ALTER、INSERT等。
  • 数据表中每个列（字段）的名称和数据类型，如果创建多个列，要用逗号隔开。

示例

1
2
3
4
5
6
7
8
mysql> CREATE TABLE tb_emp1
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT
    -> );
Query OK, 0 rows affected (0.37 sec)

查看表结构

1
2
3
DESCRIBE <表名>;
或
DESC <表名>;

示例：

1
2
3
4
5
6
7
8
9
10
mysql> DESCRIBE tb_emp1;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float        | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

其中，各个字段的含义如下：

  • Null：表示该列是否可以存储 NULL 值。
  • Key：表示该列是否已编制索引。PRI 表示该列是表主键的一部分，UNI 表示该列是 UNIQUE 索引的一部分，MUL 表示在列中某个给定值允许出现多次。
  • Default：表示该列是否有默认值，如果有，值是多少。
  • Extra：表示可以获取的与给定列有关的附加信息，如 AUTO_INCREMENT 等。

查看数据表创建信息

1
SHOW CREATE TABLE <表名>\G;

示例

1
2
3
4
5
6
7
8
9
10
mysql> SHOW CREATE TABLE tb_emp1\G
*************************** 1. row ***************************
       Table: tb_emp1
Create Table: CREATE TABLE `tb_emp1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.03 sec)

提示：使用 SHOW CREATE TABLE 语句不仅可以查看创建表时的详细语句，而且可以查看存储引擎和字符编码。如果不加“\G”参数，显示的结果可能非常混乱，加上“\G”参数之后，可使显示的结果更加直观，易于查看。

修改数据表

在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、更改原有列类型、重新命名列或表等。

1
ALTER TABLE <表名> [修改选项]

修改选项的语法格式如下：

1
2
3
4
5
6
7
8
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名>
| CHARACTER SET <字符集名>
| COLLATE <校对规则名> }

修改表名

1
ALTER TABLE <旧表名> RENAME [TO] <新表名>;

提示：修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构是相同的。用户可以使用 DESC 命令查看修改后的表结构

示例：

1
2
mysql> ALTER TABLE student RENAME TO tb_student_info;
Query OK, 0 rows affected (0.01 sec)

修改表字符集

1
ALTER TABLE <表名> [DEFAULT] CHARACTER SET <字符集名> [DEFAULT] COLLATE <校对规则>;

DEFAULT 为可选参数，使用与否均不影响结果。

修改字段名称

1
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;

其中：

  • 旧字段名：指修改前的字段名；
  • 新字段名：指修改后的字段名；
  • 新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。

示例：

1
2
3
4
mysql> ALTER TABLE tb_emp1
    -> CHANGE col1 col2 CHAR(30);
Query OK, 0 rows affected (0.76 sec)
Records: 0  Duplicates: 0  Warnings: 0

CHANGE 也可以只修改数据类型，实现和 MODIFY 同样的效果，方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。

提示：由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。

修改字段数据类型

1
ALTER TABLE <表名> MODIFY <字段名> <数据类型>;

其中：

  • 表名：指要修改数据类型的字段所在表的名称；
  • 字段名：指需要修改的字段；
  • 数据类型：指修改后字段的新数据类型。

删除字段

1
ALTER TABLE <表名> DROP <字段名>;

其中，“字段名”指需要从表中删除的字段的名称。

示例：

1
2
3
4
mysql> ALTER TABLE tb_emp1
    -> DROP col2;
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除数据表（DORP TABLE语句）

1
DROP TABLE [IF EXISTS] 表名1 [表名2, ...]

对语法格式的说明如下：

  • 表名1, 表名2, 表名3 ...表示要被删除的数据表的名称。DROP TABLE 可以同时删除多个表，只要将表名依次写在后面，相互之间用逗号隔开即可。
  • IF EXISTS 用于在删除数据表之前判断该表是否存在。如果不加 IF EXISTS，当数据表不存在时 MySQL 将提示错误，中断 SQL 语句的执行；加上 IF EXISTS 后，当数据表不存在时 SQL 语句可以顺利执行，但是会发出警告（warning）。

两点注意：

  • 用户必须拥有执行 DROP TABLE 命令的权限，否则数据表不会被删除。
  • 表被删除时，用户在该表上的权限不会自动删除。

删除被其它表关联的主表

数据表之间经常存在外键关联的情况，这时如果直接删除父表，会破坏数据表的完整性，也会删除失败。

删除父表有以下两种方法：

  • 先删除与它关联的子表，再删除父表；但是这样会同时删除两个表中的数据。
  • 将关联表的外键约束取消，再删除父表；适用于需要保留子表的数据，只删除父表的情况。

参考：http://c.biancheng.net/view/7200.html

数据表添加字段（三种方式）

在末尾添加字段

1
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件];

对语法格式的说明如下：

  • <表名> 为数据表的名字；
  • <新字段名> 为所要添加的字段的名字；
  • <数据类型> 为所要添加的字段能存储数据的数据类型；
  • [约束条件] 是可选的，用来对添加的字段进行约束。

示例

1
2
3
mysql> ALTER TABLE student ADD age INT(4);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

在开头添加字段

1
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;

FIRST 关键字一般放在语句的末尾。

示例

1
2
3
mysql> ALTER TABLE student ADD stuId INT(4) FIRST;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

在中间位置添加字段

1
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER [已经存在的字段名];

AFTER 的作用是将新字段添加到某个已有字段后面。

示例：

1
2
3
mysql> ALTER TABLE student ADD stuno INT(11) AFTER name;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

SELECT：数据表查询语句

1
2
3
4
5
6
7
8
9
10
SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

其中，各条子句的含义如下：

  • {*|<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称。
  • <表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。
  • WHERE <表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。
  • GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
  • [ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。
  • [LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。

DISTINCT：过滤重复数据

1
SELECT DISTINCT <地段名> FROM <表名>;

其中，“字段名”为需要消除重复记录的字段名称，多个字段时用逗号隔开。

使用 DISTINCT 关键字时需要注意以下几点：

  • DISTINCT 关键字只能在 SELECT 语句中使用。
  • 在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面。
  • 如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重。

示例：

1
2
3
4
5
6
7
mysql> SELECT DISTINCT age FROM student;  # 单个字段的去重

mysql> SELECT DISTINCT name,age FROM student;  # 组合字段的去重

mysql> SELECT DISTINCT * FROM student; # 所有字段的去重

mysql> SELECT COUNT(DISTINCT name,age) FROM student; # 查看不同的条数

AS：设置别名

AS 关键字来为表和字段指定别名

为表指定别名

1
<表名> [AS] <别名>

其中各子句的含义如下：

  • <表名>：数据库中存储的数据表的名称。
  • <别名>：查询时指定的表的新名称。
  • AS关键字可以省略，省略后需要将表名和别名用空格隔开。

注意：表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。在条件表达式中不能使用字段的别名，否则会出现“ERROR 1054 (42S22): Unknown column”这样的错误提示信息。

示例：

1
2
3
4
5
6
7
8
9
mysql> SELECT stu.name,stu.height FROM tb_students_info AS stu;
+--------+--------+
| name   | height |
+--------+--------+
| Dany   |    160 |
| Green  |    158 |
| Henry  |    185 |
+--------+--------+
3 rows in set (0.04 sec)

为字段指定别名**

1
<字段名> [AS] <别名>

其中，各子句的语法含义如下：

  • <字段名>：为数据表中字段定义的名称。
  • <字段别名>：字段新的名称。
  • AS关键字可以省略，省略后需要将字段名和别名用空格隔开。

示例：

1
2
3
4
5
6
7
8
9
mysql> SELECT name AS student_name, age AS student_age FROM tb_students_info;
+--------------+-------------+
| student_name | student_age |
+--------------+-------------+
| Dany         |          25 |
| Green        |          23 |
| Henry        |          23 |
+--------------+-------------+
3 rows in set (0.00 sec)

查看帮助命令(help)

1
HELP 查询内容

其中，查询内容为要查询的关键字。

  • 查询内容中不区分大小写。
  • 查询内容中可以包含通配符”%”和”_”，效果与 LIKE 运算符执行的模式匹配操作含义相同。例如，HELP ‘rep%’ 用来返回以 rep 开头的主题列表。
  • 查询内容可以使单引号引起来，也可以不使用单引号，为避免歧义，最好使用单引号引起来。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> HELP 'CREATE TABLE'
Name: 'CREATE TABLE'
Description:
Syntax:
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

拓展

  • MySQL 提供了 4 张数据表来保存服务端的帮助信息，即使用 HELP 语法查看的帮助信息。执行语句就是从这些表中获取数据并返回给客户端的，MySQL 提供的 4 张数据表如下：
  • help_category：关于帮助主题类别的信息
  • help_keyword：与帮助主题相关的关键字信息
  • help_relation：帮助关键字信息和主题信息之间的映射
  • help_topic：帮助主题的详细内容

常见错误代码一览表（带解析）

http://c.biancheng.net/view/7808.html

数据类型和存储引擎

http://c.biancheng.net/mysql/40/

查看存储引擎

1
SHOW ENGINES;
存储引擎 描述
ARCHIVE 用于数据存档的引擎，数据被插入后就不能在修改了，且不支持索引。
CSV 在存储数据时，会以逗号作为数据项之间的分隔符。
BLACKHOLE 会丢弃写操作，该操作会返回空内容。
FEDERATED 将数据存储在远程数据库中，用来访问远程表的存储引擎。
InnoDB 具备外键支持功能的事务处理引擎
MEMORY 置于内存的表
MERGE 用来管理由多个 MyISAM 表构成的表集合
MyISAM 主要的非事务处理存储引擎
NDB MySQL 集群专用存储引擎

查看和修改默认存储引擎

1
SHOW VARIABLES LIKE 'default_storage_engine%';  # 查看
1
SET default_storage_engine=<存储引擎名>;  # 设置

选择MySQL存储引擎

http://c.biancheng.net/view/7185.html

约束概述

http://c.biancheng.net/view/7576.html

查看表中的约束

1
SHOW CREATE TABLE <数据表名>;

常用运算符概述

http://c.biancheng.net/view/7575.html

MySQL函数简介

http://c.biancheng.net/view/7226.html

杂记

生成数据库下所有表的删除语句

1
2
3
4
5
SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') 
FROM  information_schema.tables 
WHERE table_schema='db_name';

注：db_name 为需要删除的数据库名字

示例：

1
2
3
4
5
6
7
8
9
10
11
12

MySQL [test]> select concat('drop table if exists ', table_name, ';') from  information_schema.tables where table_schema='test';
+--------------------------------------------------+
| concat('drop table if exists ', table_name, ';') |
+--------------------------------------------------+
| drop table if exists student;                    |
| drop table if exists tb_emp1;                    |
| drop table if exists tb_emp5;                    |
| drop table if exists tb_student2;                |
| drop table if exists tb_student3;                |
+--------------------------------------------------+
5 rows in set (0.00 sec)

使用一张表的一列更新另一张表的一列

使用MySQL中，在一张表etl_table_field_info上新增了一个字段tgt_table_en_name，该字段的值想从表etl_table_property_info的tgt_table_en_name获取。更新时的关联关系是字段src_table_en_name值相等。

1
2
3
UPDATE etl_table_field_info f, etl_table_property_info p
set f.tgt_table_en_name=p.tgt_table_en_name
where f.src_table_en_name=p.src_table_en_name
-------------本文结束感谢您的阅读-------------