MySQL常用命令速查表-51CTO.COM

速查表，顾名思义就是为了在需要的时候方便快速查询相关知识点。本文精心为大家整理了 MySQL 中最常使用的语句和命令，绝对是日常开发、求职面试的必备良方！

连接服务器

使用 mysql 客户端工具连接 MySQL 服务器的命令行如下：

mysql [-h host_name] [-P port_num] [-u user_name] [-p] [-D db_name]

其中，host_name 表示服务器主机名，默认为本机；port_num 表示 MySQL 服务端口，默认为 3306；user_name 表示用户名，默认为当前操作系统用户；-p 提示输入密码；db_name 表示要连接的数据库。例如：

[root@sqlhost ~]# mysql -u root -p
Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.19 MySQL Community Server - GPL


Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查看帮助

在 mysql 提示符中输入 help; 或者 \h 获取使用帮助。

mysql> help


For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/


List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
notee     (\t) Don't write into outfile.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.


For server side help, type 'help contents'

使用 help contents 命令可以获取 MySQL 服务器相关的命令参考：

mysql> help contents;
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Components
   Compound Statements
   Contents
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql 中的 SQL 命令支持以 ;、\g 或者 \G 结束并发送到服务器。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

查看连接

使用 show processlist 命令可以查看所有连接到服务器的进程：

mysql> show processlist;
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
| Id | User            | Host            | db   | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
|  4 | event_scheduler | localhost       | NULL | Daemon  | 107092 | Waiting on empty queue | NULL             |
| 23 | root            | localhost:64802 | NULL | Query   |      0 | starting               | show processlist |
+----+-----------------+-----------------+------+---------+--------+------------------------+------------------+
2 rows in set (0.00 sec)

使用 kill pid 命令杀掉指定连接进程：

mysql> kill 23;
ERROR 1317 (70100): Query execution was interrupted

其中，23 是当前连接进程的 pid，所以上面的命令断开了当前连接。

退出连接

使用 exit;、quit; 或者 \q 命令退出 mysql 客户端：

mysql> exit;
Bye

账户和权限

创建用户

使用 CREATE USER 语句创建一个用户：

CREATE USER [IF NOT EXISTS] user_name@host IDENTIFIED BY 'auth_string';

其中，user_name 表示用户名；host 表示允许用户从哪个主机连接 MySQL 服务器，如果省略（等价于 %）表示任何主机；IDENTIFIED BY 用于指定用户的密码。例如：

mysql> create user tony identified by 'Pswd123!';
Query OK, 0 rows affected (0.14 sec)

查看用户

MySQL 中的用户信息存储在 mysql.user 系统表中：

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| tony             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

使用 user() 或者 current_user() 函数查看当前用户：

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

修改密码

使用 ALTER USER 语句修改用户的密码：

mysql> alter user tony identified by 'Pswd123@';
Query OK, 0 rows affected (0.12 sec)

锁定/解锁用户

使用 ALTER USER 语句锁定或者解锁用户：

mysql> alter user tony account lock;
Query OK, 0 rows affected (0.10 sec)




mysql> select user,host,account_locked from mysql.user;
+------------------+-----------+----------------+
| user             | host      | account_locked |
+------------------+-----------+----------------+
| tony             | %         | Y              |
| mysql.infoschema | localhost | Y              |
| mysql.session    | localhost | Y              |
| mysql.sys        | localhost | Y              |
| root             | localhost | N              |
+------------------+-----------+----------------+
5 rows in set (0.00 sec)




mysql> alter user tony account unlock;
Query OK, 0 rows affected (0.10 sec)

其中，account lock 表示锁定；account unlock 表示解锁。

用户授权

MySQL 使用 GRANT 语句给用户授权：

GRANT priv_type [, priv_type] ...
   ON priv_level
   TO user_name@host；

其中，priv_type 表示权限，例如查询（SELECT）、执行（EXECUTE）、全部（ALL）权限等；priv_level 表示权限的级别，分为全局（*.*）、数据库（db.*）、表级（db.table）权限等。以下语句为用户 tony 授予 hrdb 数据库中 employees 表上的增删改查权限：

mysql> grant select,insert,update,delete 
    -> on hrdb.employees
    -> to tony;
Query OK, 0 rows affected (0.07 sec)

查看权限

使用 SHOW GRANTS [FOR user_name] 语句查看授予用户的权限和角色：

mysql> SHOW GRANTS FOR tony;
+--------------------------------------------------------------------------+
| Grants for tony@%                                                        |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tony`@`%`                                         |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `hrdb`.`employees` TO `tony`@`%` |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)

如果省略 FOR user_name，返回当前用户的权限和角色。

撤销权限

撤销授予用户的权限和角色使用 REVOKE 语句：

REVOKE priv_type [, priv_type] ...
    ON priv_level
  FROM user_name@host；

其中的参数与 GRANT 语句相同。以下语句撤销用户 tony 对 employess 表的删除权限：

mysql> revoke delete 
    -> on hrdb.employees
    -> from tony;
Query OK, 0 rows affected (0.01 sec)

管理角色

角色（Role）是 MySQL 8.0 引入的新功能，本质上就是一个命名的权限集合（用户也是）。

创建角色

创建角色使用 CREATE ROLE 语句：

CREATE ROLE [IF NOT EXISTS] role_name@host;

以下语句用于创建角色 test：

mysql> create role test;
Query OK, 0 rows affected (0.04 sec)

角色授权

为角色授权和用户授权相同，只需要把用户名改成角色名。以下语句将数据库 hrdb 上所有表的查看权限授予 test 角色：

mysql> grant select
    -> on hrdb.*
    -> to test;
Query OK, 0 rows affected (0.01 sec)

授予角色

为用户授权角色与授予权限几乎相同，以下语句 test 角色授予 tony 用户：

mysql> grant test
    -> to tony;
Query OK, 0 rows affected (0.01 sec)


mysql> show grants for tony;
+------------------------------------------------------------------+
| Grants for tony@%                                                |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tony`@`%`                                 |
| GRANT SELECT, INSERT, UPDATE ON `hrdb`.`employees` TO `tony`@`%` |
| GRANT `test`@`%` TO `tony`@`%`                                   |
+------------------------------------------------------------------+
3 rows in set (0.01 sec)

撤销角色

撤销角色与撤销权限类似：

mysql> revoke test from tony;
Query OK, 0 rows affected (0.01 sec)

删除角色

删除角色使用 DROP ROLE 语句：

mysql> drop role test;
Query OK, 0 rows affected (0.00 sec)

删除用户

删除用户账号使用 DROP USER 语句：

mysql> drop user if exists tony;
Query OK, 0 rows affected (0.01 sec)

管理数据库

查看数据库

使用 show databases; 命令查看当前 MySQL 服务器中所有可用的数据库：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| ds                 |
| hrdb               |
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
| world_x            |
+--------------------+
8 rows in set (0.00 sec)

创建数据库

使用 CREATE DATABASE 语句创建一个新的数据库：

CREATE DATABASE [IF NOT EXISTS] db_name [CHARACTER SET charset_name];

其中，db_name 是数据库的名称；如果该数据库已经存在，将会返回错误；此时如果指定了 IF NOT EXISTS 选项，不会返回错误。

mysql> create database testdb;
Query OK, 1 row affected (0.15 sec)


mysql> create database testdb;
ERROR 1007 (HY000): Can't create database 'testdb'; database exists
mysql> create database if not exists testdb;
Query OK, 1 row affected, 1 warning (0.05 sec)


mysql> show warnings;
+-------+------+-------------------------------------------------+
| Level | Code | Message                                         |
+-------+------+-------------------------------------------------+
| Note  | 1007 | Can't create database 'testdb'; database exists |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)

切换数据库

使用 USE db_name; 语句切换当前默认的数据库：

mysql> use testdb;
Database changed

以上语句将 testdb 设置为默认数据库。

删除数据库

使用 DROP DATABASE 语句删除一个数据库，该数据库中的所有对象以及与该数据库相关的数据文件也会被删除：

DROP DATABASE [IF EXISTS] db_name;

我们将 testdb 数据库和相关的数据文件删除：

mysql> drop database testdb;
Query OK, 0 rows affected (0.23 sec)

管理表

创建表

MySQL 使用 CREATE TABLE 语句创建表：

CREATE TABLE [IF NOT EXISTS] table_name(
   col1 data_type column_constraint,
   col2 data_type column_constraint,
   ...,
   table_constraints
) ENGINE=storage_engine;

其中，data_type 定义字段的数据类型，常用的数据类型包括：SMALLINT、INT、BIGINT、DECIMAL、CHAR、VARCHAR、TEXT、DATE、DATETIME 等。数据库约束包括主键约束（PRIMARY KEY）、外键约束（FOREIGN KEY）、唯一约束（UNIQUE）、非空约束（NOT NULL）、检查约束（CHECK）以及默认值（DEFAULT）。storage_engine 指定存储引擎，常用的存储引擎包括 InnoDB（默认）、MyISAM、Memory 、Archive、NDB 等。

CREATE TABLE departments
    ( department_id    INTEGER NOT NULL
    , department_name  CHARACTER VARYING(30) NOT NULL
    , manager_id       INTEGER
    , location_id      INTEGER
    , CONSTRAINT dept_id_pk
                PRIMARY KEY (department_id)
    ) ;


CREATE TABLE jobs
    ( job_id         CHARACTER VARYING(10) NOT NULL
    , job_title      CHARACTER VARYING(35) NOT NULL
    , min_salary     INTEGER
    , max_salary     INTEGER
    , CONSTRAINT job_id_pk
               PRIMARY KEY(job_id)
    ) ;


CREATE TABLE employees
    ( employee_id    INTEGER NOT NULL
    , first_name     CHARACTER VARYING(20)
    , last_name      CHARACTER VARYING(25) NOT NULL
    , email          CHARACTER VARYING(25) NOT NULL
    , hire_date      DATE NOT NULL
    , job_id         CHARACTER VARYING(10) NOT NULL
    , salary         NUMERIC(8,2)
    , commission_pct NUMERIC(2,2)
    , manager_id     INTEGER
    , department_id  INTEGER
    , CONSTRAINT     emp_emp_id_pk
                     PRIMARY KEY (employee_id)
    , CONSTRAINT     emp_salary_min
                     CHECK (salary > 0) 
    , CONSTRAINT     emp_email_uk
                     UNIQUE (email)
    , CONSTRAINT     emp_dept_fk
                     FOREIGN KEY (department_id)
                      REFERENCES departments(department_id)
    , CONSTRAINT     emp_job_fk
                     FOREIGN KEY (job_id)
                      REFERENCES jobs(job_id)
    , CONSTRAINT     emp_manager_fk
                     FOREIGN KEY (manager_id)
                      REFERENCES employees(employee_id)
    ) ;

查看所有表

使用 SHOW TABLES 语句查看当前数据库中的所有表：

mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| departments      |
| employees        |
| jobs             |
+------------------+
3 rows in set (0.01 sec)

查看表结构

MySQL 提供了查看表结构的 DESCRIBE 语句：

mysql> desc employees;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employee_id    | int          | NO   | PRI | NULL    |       |
| first_name     | varchar(20)  | YES  |     | NULL    |       |
| last_name      | varchar(25)  | NO   |     | NULL    |       |
| email          | varchar(25)  | NO   | UNI | NULL    |       |
| hire_date      | date         | NO   |     | NULL    |       |
| job_id         | varchar(10)  | NO   | MUL | NULL    |       |
| salary         | decimal(8,2) | YES  |     | NULL    |       |
| commission_pct | decimal(2,2) | YES  |     | NULL    |       |
| manager_id     | int          | YES  | MUL | NULL    |       |
| department_id  | int          | YES  | MUL | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
11 rows in set (0.01 sec)

另外，可以通过 SHOW CREATE TABLE 语句显示创建表的语句：

mysql> show create table employees\G
*************************** 1. row ***************************
       Table: employees
Create Table: CREATE TABLE  employees  (
   employee_id  int NOT NULL,
   first_name  varchar(20) DEFAULT NULL,
   last_name  varchar(25) NOT NULL,
   email  varchar(25) NOT NULL,
   hire_date  date NOT NULL,
   job_id  varchar(10) NOT NULL,
   salary  decimal(8,2) DEFAULT NULL,
   commission_pct  decimal(2,2) DEFAULT NULL,
   manager_id  int DEFAULT NULL,
   department_id  int DEFAULT NULL,
  PRIMARY KEY ( employee_id ),
  UNIQUE KEY  emp_email_uk  ( email ),
  KEY  emp_dept_fk  ( department_id ),
  KEY  emp_job_fk  ( job_id ),
  KEY  emp_manager_fk  ( manager_id ),
  CONSTRAINT  emp_dept_fk  FOREIGN KEY ( department_id ) REFERENCES  departments  ( department_id ),
  CONSTRAINT  emp_job_fk  FOREIGN KEY ( job_id ) REFERENCES  jobs  ( job_id ),
  CONSTRAINT  emp_manager_fk  FOREIGN KEY ( manager_id ) REFERENCES  employees  ( employee_id ),
  CONSTRAINT  emp_salary_min  CHECK (( salary  > 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

增加字段

增加字段使用 ALTER TABLE ... ADD COLUMN 语句，以下命令为 employees 表增加一个字段 phone_number：

mysql> alter table employees add column phone_number varchar(20);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改字段

修改字段使用 ALTER TABLE ... MODIFY 语句，以下命令为字段 phone_number 增加一个唯一约束：

mysql> alter table employees modify phone_number varchar(20) unique;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除字段

删除字段使用 ALTER TABLE ... DROP COLUMN 语句：

mysql> alter table employees drop column phone_number;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表

要删除表可以使用 DROP TABLE 语句：

mysql> drop table if exists employees, departments, jobs;
Query OK, 0 rows affected (0.09 sec)

索引与约束

创建索引

MySQL 为主键和唯一约束自动创建相应的索引，我们也可以创建额外的索引。创建索引的命令如下：

CREATE [UNIQUE] INDEX index_name
ON table_name(col1 ASC, col2 DESC);

查看索引

查看表中的所以可以使用 SHOW INDEXES 语句：

mysql> show indexes from departments\G
*************************** 1. row ***************************
        Table: departments
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: department_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.01 sec)

修改索引

修改索引支持设置索引的可见性：

ALTER INDEX index VISIBLE | INVISIBLE;

可见性是指对优化器而言，默认为可见（VISIBLE）；INVISIBLE 表示不可见。

删除索引

删除索引使用以下命令：

DROP INDEX index_name ON table_name;

增加约束

为表增加约束可以使用 ALTER TABLE 语句：

ALTER TABLE table_name ADD CONSTRAINT symbol PRIMARY KEY(col1,...);
ALTER TABLE table_name ADD CONSTRAINT symbol UNIQUE(col1,...);
ALTER TABLE table_name ADD CONSTRAINT symbol FOREIGN KEY (col1) REFERENCES other_table(col1);
ALTER TABLE table_name ADD CONSTRAINT symbol CHECK (expr);
ALTER TABLE table_name ALTER COLUMN col_name SET DEFAULT (expr); 
ALTER TABLE table_name MODIFY COLUMN col_name data_type NOT NULL;

删除约束

删除约束同样可以使用 ALTER TABLE 语句：

ALTER TABLE table_name DROP CHECK|CONSTRAINT symbol;
ALTER TABLE table_name ALTER COLUMN col_name DROP DEFAULT;
ALTER TABLE table_name MODIFY COLUMN col_name data_type NULL;

查询语句

单表查询

查询单个表中的字段：

SELECT col1, col2, ... FROM t;

查询所有字段：

SELECT * FROM t;

排除查询结果中的重复数据：

SELECT DISTINCT col1, col2, ...
FROM t;

查询条件

使用 WHERE 指定查询条件：

SELECT col1, col2, ... 
FROM t
WHERE conditions;

常用的查询条件包括：=、!=、<>、<、<=、>、>=、BETWEEN、IN、EXISTS、LIKE、AND、OR、NOT、IS [NOT] NULL 等。

模糊匹配

使用 LIKE 运算符进行简单的字符串模式匹配：

expr LIKE pattern [ESCAPE escape_character]

其中，pattern 用于指定一个匹配模式，百分号（%）匹配任意多个字符，下划线（_）匹配任意单个字符；escape_character 指定转义字符

mysql> select first_name
    -> from employees
    -> where first_name like '%s_n';


+------------+
| first_name |
+------------+
| Harrison   |
| Jason      |
| Susan      |
+------------+
3 rows in set (0.01 sec)

另外， REGEXP 或者 RLIKE 运算符可以进行更强大的正则表达式匹配。

排序操作

指定排序字段的方式如下：

SELECT col1, col2, ... 
FROM t 
ORDER BY col1 ASC, col2 DESC;

限定数量

限制返回结果的数量：

SELECT col1, col2, ... 
FROM t 
ORDER BY col1 ASC, col2 DESC
LIMIT offset, rows;

分组操作

指定分组和过滤：

SELECT col1, col2, agg_func() 
FROM t
GROUP BY col1, col2 WITH ROLLUP 
HAVING conditions;

常用的聚合函数：AVG、COUNT、MIN、MAX、SUM 等。

多表连接

连接查询用于从多个表中查询关联数据：

SELECT t1.col1, t2.col1, ... 
FROM table1 AS t1
[INNER | LEFT | RIGHT | CROSS] JOIN t2 
ON conditions;

子查询

子查询是指嵌套在其他查询语句中的查询：

SELECT t.col1, t.col2, ... 
FROM (SELECT ...) t ;

EXISTS 与关联子查询：

SELECT t1.col1, t1.col2, ...
FROM t1
WHERE EXISTS ( SELECT 1
               FROM t2
               WHERE t2.col1 = t1.col1);

集合运算

集合运算包括并集、交集和差集：

SELECT col1, col2, ...
FROM t1
UNION [ALL] | INTERSECT | MINUS
SELECT c1, c2, …
FROM t2;

UNION ALL 保留结果中的重复数据，其他运算符消除了重复结果。

通用表表达式

通用表表达式类似于派生表或者语句级别的视图，但是可读性和性能更好，并且支持递归调用。以下查询生成 1 到 10 的数字序列：

WITH RECURSIVE cte(n) AS (
    SELECT 1 AS n -- 初始查询
    UNION ALL
    SELECT n+1 FROM cte WHERE n < 10)  -- 递归查询
SELECT * FROM cte;

DML 语句

插入数据

插入数据使用 INSERT 语句：

INSERT INTO table(col1,col2,...)
VALUES (val1,val2,...);

一次插入多条记录：

INSERT INTO table(col1,col2,...)
VALUES (val11,val12,...), (val21,val22,...), (val31,val32,...);

插入查询语句的结果：

INSERT INTO table(col1,col2,...)
SELECT ...;

更新数据

更新数据使用 UPDATE 语句：

UPDATE table_name
SET col1 = val1, 
    col2 = val2, 
    ...
WHERE conditions;

UPDATE 语句支持跨表更新：

UPDATE t1, t2,
[INNER JOIN | LEFT JOIN] t1 ON t1.col1 = t2.col1
SET t1.col2 = t2.col2,
    t2.c3 = expr
WHERE conditions;

删除数据

删除数据使用 DELETE 语句：

DELETE FROM table_name
WHERE conditions;

DELETE语句支持多表删除：

DELETE t1, t2
FROM t1
INNER JOIN t2 ON t1.col1 = t2.col1
WHERE conditions;


DELETE t1
FROM t1
LEFT JOIN t2 ON t1.col1 = t2.col1
WHERE t2.key IS NULL;

另外，TRUNCATE TABLE 语句用于快速清除表中的全部数据：

TRUNCATE [TABLE] table_name;

合并数据

合并语句同时执行了 INSERT 和 UPDATE 操作：

INSERT INTO table(col1,col2,...)
VALUES (val1,val2,...) 
ON DUPLICATE KEY UPDATE
    col1 = val1, 
    col2 = val2, 
    …;

与此类似的操作还有 REPLACE INTO 语句：

REPLACE INTO table_name(col1, col2, ...)
VALUES (val1,val2,...);

事务控制

数据库事务是一组相关的操作，要么全部成功，要么全部失败。

开始事务

START TRANSACTION 语句用于开始一个事务：

START TRANSACTION;

BEGIN 或者 BEGIN WORK 的作用也一样。

提交事务

提交事务的命令如下：

COMMIT;

回滚事务

回滚事务的命令如下：

ROLLBACK;

事务保存点

事务保存点可以用于回滚部分事务：

SAVEPOINT identifier;
ROLLBACK TO identifier;
RELEASE SAVEPOINT identifier;

视图

创建视图

使用以下命令创建视图：

CREATE [OR REPLACE] VIEW view_name
AS
  select-statement
  WITH CHECK OPTION;

WITH CHECK OPTION 选项可以阻止通过视图修改或者插入视图范围之外的基础表数据。

查看所有视图

SHOW TABLES 语句支持查看视图：

SHOW FULL TABLES
WHERE table_type = 'VIEW';

查看视图定义

查看视图的定义：

SHOW CREATE VIEW view_name;

重命名视图

重命名视图与重命名表类似：

RENAME TABLE view_name TO new_view;

删除视图

删除视图的命令如下：

DROP VIEW [IF EXISTS] view_name;

存储过程/函数

创建存储过程/函数

创建存储过程使用 CREATE PROCEDURE 语句：

DELIMITER //
CREATE PROCEDURE GetEmployeeNameById(IN pn_empid INT)
BEGIN
    SELECT first_name, last_name FROM employees
    WHERE employee_id = pn_empid;
END //
DELIMITER ;

创建函数使用 CREATE FUNCTION 语句：

DELIMITER //
CREATE FUNCTION add_numbers(p1 INT, p2 INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN p1 + p2;
END //
DELIMITER ;

调用存储过程/函数

调用存储过程使用 CALL 语句：

mysql> CALL GetEmployeeNameById(100);
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Steven     | King      |
+------------+-----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

存储函数可以像系统函数一样使用：

mysql> select add_numbers(1, 2);
+-------------------+
| add_numbers(1, 2) |
+-------------------+
|                 3 |
+-------------------+
1 row in set (0.00 sec)

删除存储过程/函数

删除存储过程/函数使用 DROP 语句：

mysql> drop procedure if exists GetEmployeeNameById;
Query OK, 0 rows affected (0.03 sec)




mysql> drop function if exists add_numbers;
Query OK, 0 rows affected (0.01 sec)

触发器

触发器是一个特殊的存储过程，当表中的数据被修改（INSERT、UPDATE、DELETE）时自动执行。

创建触发器

创建触发器使用 CREATE TRIGGER 语句：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON table_name FOR EACH ROW
trigger_body;

对于 INSERT，可以使用NEW 变量；对于 UPDATE，可以使用 OLD 和 NEW 变量；对于 DELETE，可以使用 OLD 变量。

查看触发器

使用以下命令查看触发器：

SHOW TRIGGERS
[FROM | IN database_name]
[LIKE 'pattern' | WHERE condition];

删除触发器

删除触发器的语句如下：

DROP TRIGGER [IF EXISTS] trigger_name;

备份与恢复

使用 mysqldump 备份数据库

mysqldump 是 MySQL 逻辑备份工具，用于导出创建数据库（CREATE DATABASE）和生成数据（INSERT 语句或者文本文件）的 SQL 脚本。

使用 mysqldump 备份所有的数据库的命令如下：

mysqldump -u user_name -p -r file_name --all-databases

其中，user_name 表示用户名；file_name 表示备份文件名；-\-all-databases 表示备份所有的数据库。

导出特定数据库的命令如下：

mysqldump -u user_name -p -r file_name --databases db_name1 db_name2

其中，--databases 表示需要导出的数据库。

mysqldump 也可以选择导出指定的表：

mysqldump -u user_name -p -r file_name db_name table1 table2

以上命令表示导出数据库 db_name 中的表 table1 和 table2。

使用 mysql 还原数据库

mysql 客户端可以用于导入备份生成的 SQL 文件：

mysql -u user_name -p < file_name