在本章中,我们将介绍以下配方:
我们将在下面的食谱中学到很多东西。让我们仔细看看每一个。
到目前为止,您已经了解了如何在各种平台上安装 MySQL 8.0。在安装过程中,您将获得名为mysql
的命令行客户端实用程序,我们使用它连接到任何 MySQL 服务器。
首先,您需要知道需要连接到哪个服务器。如果您在一台主机上安装了 MySQL 服务器,并且试图从另一台主机(通常称为客户端)连接到该服务器,则应指定服务器的主机名或 IP 地址,并在客户端上安装mysql-client
包。在上一章中,您安装了 MySQL 服务器和客户端软件包。如果您已经在服务器上(通过 SSH),则可以指定localhost
、127.0.0.1
或::1
。
其次,由于您已连接到服务器,接下来需要指定的是要连接到服务器上的哪个端口。默认情况下,MySQL 在端口3306
上运行。所以,您应该指定3306
。
现在你知道在哪里连接了。下一个明显的问题是登录服务器的用户名和密码。您尚未创建任何用户,因此请使用根用户进行连接。安装时,如果您提供了密码,请使用该密码进行连接。如果您更改了密码,请使用新密码。
可以使用以下任意命令连接到 MySQL 客户端:
shell> mysql -h localhost -P 3306 -u <username> -p<password>
shell> mysql --host=localhost --port=3306 --user=root --password=<password>
shell> mysql --host localhost --port 3306 --user root --password=<password>
强烈建议不要在命令行中提供密码,您可以将该字段留空;系统将提示您输入密码:
shell> mysql --host=localhost --port=3306 --user=root --password
Enter Password:
-P
参数(大写)用于指定端口。-p
参数(小写)。-p
参数后面没有空格。=
后没有空格。默认主机为localhost
,端口为3306
,用户为当前 shell 用户。
shell> whoami
exit
:mysql> ^DBye
shell>
或使用:
mysql> exit;
Bye
shell>
mysql
提示符后,可以执行后跟分隔符的语句。默认分隔符是分号(;
:mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
\c
:mysql> SELECT ^C
mysql> SELECT \c
Connecting to MySQL using the root user is not recommended. You can create users and restrict the user by granting appropriate privileges, which will be discussed in the Creating Users and Granting and revoking access to users sections. Till then, you can use the root user to connect to MySQL.
连接后,您可能会注意到一个警告:
Warning: Using a password on the command line interface can be insecure.
要了解安全连接方式,请参阅第 14 章、安全。
Once you are connected to the command-line prompt, you can execute the SQL statements, which can be terminated by ;
, \g
, or \G
.
;
or \g
—output is displayed horizontally, \G
—output is displayed vertically.
那么,您已经安装了 MySQL 8.0 并连接到它。现在是时候在其中存储一些数据了,毕竟这就是数据库的用途。在任何r**相关数据库管理系统(RDBMS**)中,数据都是按行存储的,这是数据库的基本构建块。行包含可以存储多组值的列。
例如,如果要在数据库中存储有关客户的信息。
以下是数据集:
customer id=1, first_name=Mike, last_name=Christensen country=USA
customer id=2, first_name=Andy, last_name=Hollands, country=Australia
customer id=3, first_name=Ravi, last_name=Vedantam, country=India
customer id=4, first_name= Rajiv, last_name=Perera, country=Sri Lanka
您应该将它们保存为行:(1, 'Mike', 'Christensen', 'USA')
、(2, 'Andy', 'Hollands', 'Australia')
、(3, 'Ravi', 'Vedantam', 'India')
、(4, 'Rajiv', 'Perera', 'Sri Lanka')
。对于该数据集,有四行由三列(id, first_name, last_name and country)
描述,它们存储在一个表中。表可以容纳的列数应该在创建表时定义,这是 RDBMS 的主要限制。但是,我们可以随时更改表的定义,但在执行此操作时应重新生成完整的表。在某些情况下,执行 alter 时该表将不可用。更改表格将在第 9 章、表格维护中详细讨论。
数据库是许多表的集合,数据库服务器可以容纳许多这样的数据库。流程如下:
数据库服务器->数据库->表(由列定义)->行
数据库和表称为数据库对象。任何操作,如创建、修改或删除数据库对象,都称为数据定义语言(DDL。
作为数据库构造蓝图的数据组织(分为数据库和表)称为模式。
连接到 MySQL 服务器:
shell> mysql -u root -p
Enter Password:
mysql> CREATE DATABASE company;
mysql> CREATE DATABASE `my.contacts`;
回勾字符(``sql用于引用标识符,如数据库和表名。当数据库名称包含特殊字符时,需要使用它,例如点(
.`。
您可以在数据库之间切换:
mysql> USE company
mysql> USE `my.contacts`
```sql
通过在命令行中指定数据库,您可以直接连接到所需的数据库,而不是切换:
shell> mysql -u root -p company
要查找连接到的数据库,请使用以下命令:
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | company | +------------+ 1 row in set (0.00 sec)
要查找您有权访问的所有数据库,请使用:
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | company | | my.contacts | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec)
数据库被创建为`data directory`内的目录。默认的`data directory`是`/var/lib/mysql`用于基于存储库的安装,而`/usr/local/mysql/data/`用于通过二进制文件的安装。要了解您当前的`data directory`,您可以执行:
mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | datadir | /usr/local/mysql/data/ | +---------------+------------------------+ 1 row in set (0.00 sec)
检查`data directory`内的文件:
shell> sudo ls -lhtr /usr/local/mysql/data/ total 185M -rw-r----- 1 mysql mysql 56 Jun 2 16:57 auto.cnf -rw-r----- 1 mysql mysql 257 Jun 2 16:57 performance_sche_3.SDI drwxr-x--- 2 mysql mysql 4.0K Jun 2 16:57 performance_schema drwxr-x--- 2 mysql mysql 4.0K Jun 2 16:57 mysql -rw-r----- 1 mysql mysql 242 Jun 2 16:57 sys_4.SDI drwxr-x--- 2 mysql mysql 4.0K Jun 2 16:57 sys -rw------- 1 mysql root 1.7K Jun 2 16:58 ca-key.pem -rw-r--r-- 1 mysql root 1.1K Jun 2 16:58 ca.pem -rw------- 1 mysql root 1.7K Jun 2 16:58 server-key.pem -rw-r--r-- 1 mysql root 1.1K Jun 2 16:58 server-cert.pem -rw------- 1 mysql root 1.7K Jun 2 16:58 client-key.pem -rw-r--r-- 1 mysql root 1.1K Jun 2 16:58 client-cert.pem -rw------- 1 mysql root 1.7K Jun 2 16:58 private_key.pem -rw-r--r-- 1 mysql root 451 Jun 2 16:58 public_key.pem -rw-r----- 1 mysql mysql 1.4K Jun 2 17:46 ib_buffer_pool -rw-r----- 1 mysql mysql 5 Jun 2 17:46 server1.pid -rw-r----- 1 mysql mysql 247 Jun 3 13:55 company_5.SDI drwxr-x--- 2 mysql mysql 4.0K Jun 4 08:13 company -rw-r----- 1 mysql mysql 12K Jun 4 18:58 server1.err -rw-r----- 1 mysql mysql 249 Jun 5 16:17 employees_8.SDI drwxr-x--- 2 mysql mysql 4.0K Jun 5 16:17 employees -rw-r----- 1 mysql mysql 76M Jun 5 16:18 ibdata1 -rw-r----- 1 mysql mysql 48M Jun 5 16:18 ib_logfile1 -rw-r----- 1 mysql mysql 48M Jun 5 16:18 ib_logfile0 -rw-r----- 1 mysql mysql 12M Jun 10 10:29 ibtmp1
# 另见
您可能想知道您尚未创建的其他文件和目录,例如`information_schema`和`performance_schema`。`information_schema`将在*获取数据库和表的信息*一节中讨论,`performance_schema`将在[第 13 章](mysql8-tutorial-13.html#CRVJ00-faa69fe6f4c04957afca3568dcd9cd83)、*性能调优*以及*使用性能模式*一节中讨论。
# 创建表
在表中定义列时,应提及列的名称、数据类型(整数、浮点、字符串等)和默认值(如果有)。MySQL 支持各种数据类型。有关更多详细信息,请参阅 MySQL 文档([https://dev.mysql.com/doc/refman/8.0/en/data-types.html](https://dev.mysql.com/doc/refman/8.0/en/data-types.html)) 。下面是所有数据类型的概述。`JSON`数据类型是一个新的扩展,将在[第 3 章](mysql8-tutorial-13.html#CRVJ00-faa69fe6f4c04957afca3568dcd9cd83)*中使用 MySQL(高级)*进行讨论:
1. 数字:`TINYINT`、`SMALLINT`、`MEDIUMINT`、`INT`、`BIGINT`和`BIT`。
2. 浮点数:`DECIMAL`、`FLOAT`、`DOUBLE`。
3. 字符串:`CHAR`、`VARCHAR`、`BINARY`、`VARBINARY`、`BLOB`、`TEXT`、`ENUM`和`SET`。
4. 还支持空间数据类型。参见[https://dev.mysql.com/doc/refman/8.0/en/spatial-extensions.html](https://dev.mysql.com/doc/refman/8.0/en/spatial-extensions.html) 了解更多详情。
5. `JSON`数据类型-将在下一章详细讨论。
您可以在数据库中创建许多表。
# 怎么做。。。
该表包含列定义:
mysql> CREATE TABLE IF NOT EXISTS company
.customers
(
id
int unsigned AUTO_INCREMENT PRIMARY KEY,
first_name
varchar(20),
last_name
varchar(20),
country
varchar(20)
) ENGINE=InnoDB;
这些选项解释如下:
* **点表示法**:可以使用*数据库名称*(`database.table`)引用表。如果您连接到数据库,您可以简单地使用`customers`而不是`company.customers`。
* `IF NOT EXISTS`:如果存在同名的表,并且您指定了这个子句,MySQL 只会抛出一个警告,表示该表已经存在。否则,MySQL 将抛出一个错误。
* `id`:声明为整数,因为它只包含整数。除此之外,还有两个关键词:`AUTO_INCREMENT`和`PRIMARY KEY`。
* `AUTO_INCREMENT`:系统会自动生成一个线性递增序列,因此您不必担心为每一行分配`id`。
* `PRIMARY KEY`:每行由一个`UNIQUE`列标识,即`NOT NULL`。表中只应定义其中一列。如果表中包含`AUTO_INCREMENT`列,则将其视为`PRIMARY KEY`。
* `first_name`、`last_name`、`country`:包含字符串,定义为`varchar`。
* **引擎**:除了列定义之外,还应该提到存储引擎。一些类型的存储引擎包括`InnoDB`、`MyISAM`、`FEDERATED`、`BLACKHOLE`、`CSV`和`MEMORY`。在所有引擎中,`InnoDB`是唯一的事务引擎,也是默认引擎。有关事务的更多信息,请参阅[第 5 章](mysql8-tutorial-02.html)、*事务*。
要列出所有存储引擎,请执行以下操作:
mysql> SHOW ENGINES\G 1. row Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tables Transactions: NO XA: NO Savepoints: NO 2. row Engine: FEDERATED Support: NO Comment: Federated MySQL storage engine Transactions: NULL XA: NULL Savepoints: NULL 3. row Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keys Transactions: YES XA: YES Savepoints: YES 4. row Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears) Transactions: NO XA: NO Savepoints: NO 5. row Engine: CSV Support: YES Comment: CSV storage engine Transactions: NO XA: NO Savepoints: NO 6. row Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tables Transactions: NO XA: NO Savepoints: NO 7. row Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance Schema Transactions: NO XA: NO Savepoints: NO 8. row Engine: ARCHIVE Support: YES Comment: Archive storage engine Transactions: NO XA: NO Savepoints: NO 9. row Engine: MyISAM Support: YES Comment: MyISAM storage engine Transactions: NO XA: NO Savepoints: NO 9 rows in set (0.00 sec)
可以在数据库中创建多个表。
再创建一个表以跟踪付款:
mysql> CREATE TABLE company
.payments
(
customer_name
varchar(20) PRIMARY KEY,
payment
float
);
要列出所有表,请使用:
mysql> SHOW TABLES; +-------------------+ | Tables_in_company | +-------------------+ | customers | | payments | +-------------------+ 2 rows in set (0.00 sec)
要查看表的结构,请执行以下操作:
mysql> SHOW CREATE TABLE customers\G
1. row
Table: customers
Create Table: CREATE TABLE customers
(
id
int(10) unsigned NOT NULL AUTO_INCREMENT,
first_name
varchar(20) DEFAULT NULL,
last_name
varchar(20) DEFAULT NULL,
country
varchar(20) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
或者使用以下命令:
mysql> DESC customers; +------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(20) | YES | | NULL | | | country | varchar(20) | YES | | NULL | | +------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
MySQL 在`data directory`中创建`.ibd`文件:
shell> sudo ls -lhtr /usr/local/mysql/data/company total 256K -rw-r----- 1 mysql mysql 128K Jun 4 07:36 customers.ibd -rw-r----- 1 mysql mysql 128K Jun 4 08:24 payments.ibd
# 克隆表结构
可以将一个表的结构克隆到新表中:
mysql> CREATE TABLE new_customers LIKE customers; Query OK, 0 rows affected (0.05 sec)
您可以验证新表的结构:
mysql> SHOW CREATE TABLE new_customers\G
1. row
Table: new_customers
Create Table: CREATE TABLE new_customers
(
id
int(10) unsigned NOT NULL AUTO_INCREMENT,
first_name
varchar(20) DEFAULT NULL,
last_name
varchar(20) DEFAULT NULL,
country
varchar(20) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
# 另见
参见[https://dev.mysql.com/doc/refman/8.0/en/create-table.html](https://dev.mysql.com/doc/refman/8.0/en/create-table.html) 用于`Create Table`中的许多其他选项。表分区和表压缩将分别在[第 10 章](mysql8-tutorial-10.html#A1CDI0-faa69fe6f4c04957afca3568dcd9cd83)、*表维护*和[第 11 章](mysql8-tutorial-11.html#BI0Q80-faa69fe6f4c04957afca3568dcd9cd83)、*管理表空间*中讨论。
# 插入、更新和删除行
`INSERT`、`UPDATE`、`DELETE`和`SELECT`操作称为**数据操作语言**(**DML**语句。`INSERT`、`UPDATE`和`DELETE`也称为写操作,或简称为**写操作**。`SELECT`是一种读取操作,简称为**读取**。
# 怎么做。。。
让我们详细地看看每一个。我相信你会喜欢学习这个。我建议你以后也自己尝试一些事情。在本食谱结束时,我们还将掌握截断表的方法。
# 插入
`INSERT`语句用于在表中创建新记录:
mysql> INSERT IGNORE INTO company
.customers
(first_name, last_name,country)
VALUES
('Mike', 'Christensen', 'USA'),
('Andy', 'Hollands', 'Australia'),
('Ravi', 'Vedantam', 'India'),
('Rajiv', 'Perera', 'Sri Lanka');
如果您想插入具体的`id`,也可以明确提到`id`列:
mysql> INSERT IGNORE INTO company
.customers
(id, first_name, last_name,country)
VALUES
(1, 'Mike', 'Christensen', 'USA'),
(2, 'Andy', 'Hollands', 'Australia'),
(3, 'Ravi', 'Vedantam', 'India'),
(4, 'Rajiv', 'Perera', 'Sri Lanka');
Query OK, 0 rows affected, 4 warnings (0.00 sec) Records: 4 Duplicates: 4 Warnings: 4
`IGNORE`:如果行已经存在并且给出了`IGNORE`子句,则忽略新数据,`INSERT`语句仍然成功生成警告和大量重复。否则,如果没有给出`IGNORE`子句,`INSERT`语句将产生错误。行的唯一性由主键标识:
mysql> SHOW WARNINGS; +---------+------+---------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------+ | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' | | Warning | 1062 | Duplicate entry '2' for key 'PRIMARY' | | Warning | 1062 | Duplicate entry '3' for key 'PRIMARY' | | Warning | 1062 | Duplicate entry '4' for key 'PRIMARY' | +---------+------+---------------------------------------+ 4 rows in set (0.00 sec)
# 更新
`UPDATE`语句用于修改表中已有的记录:
mysql> UPDATE customers SET first_name='Rajiv', country='UK' WHERE id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
`WHERE`:用于过滤的条款。在对`WHERE`子句求值并更新筛选的行之后,发出任何条件。
The `WHERE` clause is **mandatory**. Failing to give it will `UPDATE` the whole table.
It is recommended to do data modification in a transaction, so that you can easily rollback the changes if you find anything wrong. You can refer to [Chapter 5](mysql8-tutorial-05.html#65D4E0-faa69fe6f4c04957afca3568dcd9cd83), *Transactions* to learn more about transactions.
# 删除
删除记录的操作如下所示:
mysql> DELETE FROM customers WHERE id=4 AND first_name='Rajiv'; Query OK, 1 row affected (0.03 sec)
The `WHERE` clause is **mandatory**. Failing to give it will `DELETE` all the rows of the table.
It is recommended to do data modification in a transaction, so that you can easily rollback the changes if you find anything wrong.
# 在重复密钥更新时替换、插入
在许多情况下,您需要处理重复项。行的唯一性由主键标识。如果一行已经存在,`REPLACE`只需删除该行并插入新行。如果不存在行,`REPLACE`的行为与`INSERT`相同。
如果行已经存在,则当您想要采取操作时使用`ON DUPLICATE KEY UPDATE`。如果指定了`ON DUPLICATE KEY UPDATE`选项,`INSERT`语句导致`PRIMARY KEY`中出现重复值,MySQL 将根据新值更新旧行。
假设您希望在收到同一客户的付款时更新以前的金额,如果该客户是第一次付款,则同时插入一条新记录。为此,您将定义“金额”列,并在收到新付款时更新该列:
mysql> REPLACE INTO customers VALUES (1,'Mike','Christensen','America'); Query OK, 2 rows affected (0.03 sec)
您可以看到两行受到影响,删除了一个重复行,并插入了一个新行:
mysql> INSERT INTO payments VALUES('Mike Christensen', 200) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment); Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO payments VALUES('Ravi Vedantam',500) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment); Query OK, 1 row affected (0.01 sec)
当`Mike Christensen`下次支付 300 美元时,这将更新行并将此付款添加到以前的付款中:
mysql> INSERT INTO payments VALUES('Mike Christensen', 300) ON DUPLICATE KEY UPDATE payment=payment+VALUES(payment); Query OK, 2 rows affected (0.00 sec)
`VALUES`(付款):指`INSERT`报表中给出的数值。付款指的是表中的列。
# 截断表
删除整个表需要很多时间,因为 MySQL 会逐行执行操作。删除表中所有行(保留表结构)的最快方法是使用`TRUNCATE TABLE`语句。
Truncate 是 MySQL 中的 DDL 操作,意味着一旦数据被截断,就不能回滚:
mysql> TRUNCATE TABLE customers; Query OK, 0 rows affected (0.03 sec)
# 加载样本数据
您已经创建了模式(数据库和表)和一些数据(通过`INSERT`、`UPDATE`和`DELETE`。为了解释后面的章节,需要更多的数据。MySQL 提供了一个样本`employee`数据库和大量数据供用户使用。在本章中,我们将讨论如何获取数据并将其存储在数据库中。
# 怎么做。。。
1. 下载压缩文件:
shell> wget 'https://codeload.github.com/datacharmer/test_db/zip/master' -O master.zip
2. 解压缩文件:
shell> unzip master.zip
3. 加载数据:
shell> cd test_db-master
shell> mysql -u root -p < employees.sql mysql: [Warning] Using a password on the command line interface can be insecure. INFO CREATING DATABASE STRUCTURE INFO storage engine: InnoDB INFO LOADING departments INFO LOADING employees INFO LOADING dept_emp INFO LOADING dept_manager INFO LOADING titles INFO LOADING salaries data_load_time_diff NULL
4. 验证数据:
shell> mysql -u root -p employees -A mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 35 Server version: 8.0.3-rc-log MySQL Community Server (GPL) Copyright (c) 2000, 2017, 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> SHOW TABLES; +-------------------------+ | Tables_in_employees | +-------------------------+ | current_dept_emp | | departments | | dept_emp | | dept_emp_latest_date | | dept_manager | | employees | | salaries | | titles | +-------------------------+ 8 rows in set (0.00 sec)
mysql> DESC employees\G 1. row Field: emp_no Type: int(11) Null: NO Key: PRI Default: NULL Extra: 2. row Field: birth_date Type: date Null: NO Key: Default: NULL Extra: 3. row Field: first_name Type: varchar(14) Null: NO Key: Default: NULL Extra: 4. row Field: last_name Type: varchar(16) Null: NO Key: Default: NULL Extra: 5. row Field: gender Type: enum('M','F') Null: NO Key: Default: NULL Extra: 6. row Field: hire_date Type: date Null: NO Key: Default: NULL Extra: 6 rows in set (0.00 sec)
# 选择数据
您已在表中插入并更新了数据。现在是学习如何从数据库中检索信息的时候了。在本节中,我们将讨论如何从我们创建的样本`employee`数据库中检索数据。
你可以用`SELECT`做很多事情。本节将讨论最常见的用例。有关语法和其他用例的更多详细信息,请参阅[https://dev.mysql.com/doc/refman/8.0/en/select.html](https://dev.mysql.com/doc/refman/8.0/en/select.html) 。
# 怎么做。。。
从`employee`数据库的`departments`表中选择所有数据。您可以使用星号(`*`从表中选择所有列。不建议使用它,您应始终仅选择所需的数据:
mysql> SELECT * FROM departments; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+ 9 rows in set (0.00 sec)
# 选择列
假设您需要来自`dept_manager`的`emp_no`和`dept_no`:
mysql> SELECT emp_no, dept_no FROM dept_manager; +--------+---------+ | emp_no | dept_no | +--------+---------+ | 110022 | d001 | | 110039 | d001 | | 110085 | d002 | | 110114 | d002 | | 110183 | d003 | | 110228 | d003 | | 110303 | d004 | | 110344 | d004 | | 110386 | d004 | | 110420 | d004 | | 110511 | d005 | | 110567 | d005 | | 110725 | d006 | | 110765 | d006 | | 110800 | d006 | | 110854 | d006 | | 111035 | d007 | | 111133 | d007 | | 111400 | d008 | | 111534 | d008 | | 111692 | d009 | | 111784 | d009 | | 111877 | d009 | | 111939 | d009 | +--------+---------+ 24 rows in set (0.00 sec)
# 计数
从`employees`表中查找员工人数:
mysql> SELECT COUNT() FROM employees; +----------+ | COUNT() | +----------+ | 300024 | +----------+ 1 row in set (0.03 sec)
# 基于条件的过滤器
查找`first_name`为`Georgi`且`last_name`为`Facello`的员工`emp_no`:
mysql> SELECT emp_no FROM employees WHERE first_name='Georgi' AND last_name='Facello'; +--------+ | emp_no | +--------+ | 10001 | | 55649 | +--------+ 2 rows in set (0.08 sec)
所有过滤条件通过`WHERE`条款给出。除整数和浮点外,其他所有内容都应放在引号内。
# 操作员
MySQL 支持许多用于过滤结果的运算符。参见[https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html](https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html) 获取所有操作员的列表。我们将在这里讨论几个操作符。`LIKE`和`RLIKE`将在下面的示例中详细说明:
* **相等**:参考前面的示例,您已经使用`=`进行了过滤。
* `IN`:检查一个值是否在一组值内。
例如,查找姓氏为`Christ`、`Lamba`或`Baba`的所有员工的计数:
mysql> SELECT COUNT() FROM employees WHERE last_name IN ('Christ', 'Lamba', 'Baba'); +----------+ | COUNT() | +----------+ | 626 | +----------+ 1 row in set (0.08 sec)
* `BETWEEN...AND`:检查一个值是否在一个值的范围内。
例如,找出 1986 年 12 月雇佣的员工人数:
mysql> SELECT COUNT() FROM employees WHERE hire_date BETWEEN '1986-12-01' AND '1986-12-31'; +----------+ | COUNT() | +----------+ | 3081 | +----------+ 1 row in set (0.06 sec)
* `NOT`:您可以简单地通过前面的`NOT`操作符来否定结果。
例如,找出 1986 年 12 月`NOT`雇佣的员工人数:
mysql> SELECT COUNT() FROM employees WHERE hire_date NOT BETWEEN '1986-12-01' AND '1986-12-31'; +----------+ | COUNT() | +----------+ | 296943 | +----------+ 1 row in set (0.08 sec)
# 简单模式匹配
您可以使用`LIKE`操作符。使用下划线(`_`精确匹配一个字符。使用`%`匹配任意数量的字符。
* 查找名字以`Christ`开头的所有员工的计数:
mysql> SELECT COUNT() FROM employees WHERE first_name LIKE 'christ%'; +----------+ | COUNT() | +----------+ | 1157 | +----------+ 1 row in set (0.06 sec)
* 查找名字以`Christ`开头,以`ed`结尾的所有员工的计数:
mysql> SELECT COUNT() FROM employees WHERE first_name LIKE 'christ%ed'; +----------+ | COUNT() | +----------+ | 228 | +----------+ 1 row in set (0.06 sec)
* 查找名字包含`sri`的所有员工的计数:
mysql> SELECT COUNT() FROM employees WHERE first_name LIKE '%sri%'; +----------+ | COUNT() | +----------+ | 253 | +----------+ 1 row in set (0.08 sec)
* 查找名字以`er`结尾的所有员工的计数:
mysql> SELECT COUNT() FROM employees WHERE first_name LIKE '%er'; +----------+ | COUNT() | +----------+ | 5388 | +----------+ 1 row in set (0.08 sec)
* 查找名字以任意两个字符开头,后跟`ka`,然后后跟任意数量字符的所有员工的计数:
mysql> SELECT COUNT() FROM employees WHERE first_name LIKE '__ka%'; +----------+ | COUNT() | +----------+ | 1918 | +----------+ 1 row in set (0.06 sec)
# 正则表达式
您可以通过使用`RLIKE`或`REGEXP`运算符在`WHERE`子句中使用正则表达式。使用`REGEXP`的方式很多,请参考[https://dev.mysql.com/doc/refman/8.0/en/regexp.html](https://dev.mysql.com/doc/refman/8.0/en/regexp.html) 更多示例:
| **表达式** | **说明** |
| `*` | 零次或多次重复 |
| `+` | 一次或多次重复 |
| `?` | 可选字符 |
| `.` | 任何字符 |
| `\.` | 时期 |
| `^` | 开始于 |
| `$` | 以 |
| `[abc]` | 只有*a*、*b*或*c* |
| `[^abc]` | 既不是*a*、*b*也不是*c* |
| `[a-z]` | 字符 a 至*z* |
| `[0-9]` | 数字 0 到 9 |
| `^...$` | 开始和结束 |
| `\d` | 任何数字 |
| `\D` | 任何非数字字符 |
| `\s` | 有空白吗 |
| `\S` | 任何非空白字符 |
| `\w` | 任何字母数字字符 |
| `\W` | 任何非字母数字字符 |
| `{m}` | *m*重复 |
| `{m,n}` | *m*至*n*重复 |
* 查找名字以`Christ`开头的所有员工的计数:
mysql> SELECT COUNT() FROM employees WHERE first_name RLIKE '^christ'; +----------+ | COUNT() | +----------+ | 1157 | +----------+ 1 row in set (0.18 sec)
* 查找姓氏以`ba`结尾的所有员工的计数:
mysql> SELECT COUNT() FROM employees WHERE last_name REGEXP 'ba$'; +----------+ | COUNT() | +----------+ | 1008 | +----------+ 1 row in set (0.15 sec)
* 查找姓氏不包含元音(a、e、i、o 或 u)的所有员工的计数:
mysql> SELECT COUNT() FROM employees WHERE last_name NOT REGEXP '[aeiou]'; +----------+ | COUNT() | +----------+ | 148 | +----------+ 1 row in set (0.11 sec)
# 极限结果
选择`hire_date`在 1986 年之前的任何 10 名员工的姓名。您可以通过使用语句末尾的`LIMIT`子句来获得:
mysql> SELECT first_name, last_name FROM employees WHERE hire_date < '1986-01-01' LIMIT 10; +------------+------------+ | first_name | last_name | +------------+------------+ | Bezalel | Simmel | | Sumant | Peac | | Eberhardt | Terkki | | Otmar | Herbst | | Florian | Syrotiuk | | Tse | Herber | | Udi | Jansch | | Reuven | Garigliano | | Erez | Ritzmann | | Premal | Baek | +------------+------------+ 10 rows in set (0.00 sec)
# 使用表别名
默认情况下,`SELECT`子句中给出的任何列都将出现在结果中。在前面的示例中,您已经找到了计数,但它显示为`COUNT(*)`。您可以使用`AS`别名进行更改:
mysql> SELECT COUNT(*) AS count FROM employees WHERE hire_date BETWEEN '1986-12-01' AND '1986-12-31'; +-------+ | count | +-------+ | 3081 | +-------+ 1 row in set (0.06 sec)
# 排序结果
可以根据列或别名列对结果进行排序。您可以指定`DESC`为降序,或者指定`ASC`为升序。默认情况下,排序将是升序。您可以将`LIMIT`子句与`ORDER BY`组合以限制结果。
# 怎么做。。。
查找前五名高薪员工的员工 ID。
mysql> SELECT emp_no,salary FROM salaries ORDER BY salary DESC LIMIT 5; +--------+--------+ | emp_no | salary | +--------+--------+ | 43624 | 158220 | | 43624 | 157821 | | 254466 | 156286 | | 47978 | 155709 | | 253939 | 155513 | +--------+--------+ 5 rows in set (0.74 sec)
除了指定列名,您还可以在`SELECT`语句中提到列的位置。例如,您在`SELECT`语句的第二个位置选择薪资。因此,您可以指定`ORDER BY 2`:
mysql> SELECT emp_no,salary FROM salaries ORDER BY 2 DESC LIMIT 5; +--------+--------+ | emp_no | salary | +--------+--------+ | 43624 | 158220 | | 43624 | 157821 | | 254466 | 156286 | | 47978 | 155709 | | 253939 | 155513 | +--------+--------+ 5 rows in set (0.78 sec)
# 分组结果(聚合函数)
您可以使用列上的`GROUP BY`子句对结果进行分组,然后使用`AGGREGATE`函数,例如`COUNT`、`MAX`、`MIN`和`AVERAGE`。还可以在 GROUPBY 子句中的列上使用该函数。参见`SUM`示例,您将在其中使用`YEAR()`功能。
# 怎么做。。。
这里将详细介绍前面提到的每个聚合函数。
# 计数
1. 查找男性和女性员工的数量:
mysql> SELECT gender, COUNT(*) AS count FROM employees GROUP BY gender; +--------+--------+ | gender | count | +--------+--------+ | M | 179973 | | F | 120051 | +--------+--------+ 2 rows in set (0.14 sec)
2. 您希望找到员工最常见的 10 个名字。您可以使用`GROUP BY first_name`对所有名字进行分组,然后使用`COUNT(first_name)`查找组内的计数,最后使用`ORDER BY`计数对结果进行排序。`LIMIT`这些结果进入前 10 名:
mysql> SELECT first_name, COUNT(first_name) AS count FROM employees GROUP BY first_name ORDER BY count DESC LIMIT 10; +-------------+-------+ | first_name | count | +-------------+-------+ | Shahab | 295 | | Tetsushi | 291 | | Elgin | 279 | | Anyuan | 278 | | Huican | 276 | | Make | 275 | | Panayotis | 272 | | Sreekrishna | 272 | | Hatem | 271 | | Giri | 270 | +-------------+-------+ 10 rows in set (0.21 sec)
# 总和
找出每年发放给员工的工资总额,并按工资对结果进行排序。`YEAR()`函数返回给定日期的`YEAR`:
mysql> SELECT '2017-06-12', YEAR('2017-06-12'); +------------+--------------------+ | 2017-06-12 | YEAR('2017-06-12') | +------------+--------------------+ | 2017-06-12 | 2017 | +------------+--------------------+ 1 row in set (0.00 sec)
mysql> SELECT YEAR(from_date), SUM(salary) AS sum FROM salaries GROUP BY YEAR(from_date) ORDER BY sum DESC; +-----------------+-------------+ | YEAR(from_date) | sum | +-----------------+-------------+ | 2000 | 17535667603 | | 2001 | 17507737308 | | 1999 | 17360258862 | | 1998 | 16220495471 | | 1997 | 15056011781 | | 1996 | 13888587737 | | 1995 | 12638817464 | | 1994 | 11429450113 | | 2002 | 10243347616 | | 1993 | 10215059054 | | 1992 | 9027872610 | | 1991 | 7798804412 | | 1990 | 6626146391 | | 1989 | 5454260439 | | 1988 | 4295598688 | | 1987 | 3156881054 | | 1986 | 2052895941 | | 1985 | 972864875 | +-----------------+-------------+ 18 rows in set (1.47 sec)
# 平均的
找出平均工资最高的 10 名员工:
mysql> SELECT emp_no, AVG(salary) AS avg FROM salaries GROUP BY emp_no ORDER BY avg DESC LIMIT 10; +--------+-------------+ | emp_no | avg | +--------+-------------+ | 109334 | 141835.3333 | | 205000 | 141064.6364 | | 43624 | 138492.9444 | | 493158 | 138312.8750 | | 37558 | 138215.8571 | | 276633 | 136711.7333 | | 238117 | 136026.2000 | | 46439 | 135747.7333 | | 254466 | 135541.0625 | | 253939 | 135042.2500 | +--------+-------------+ 10 rows in set (0.91 sec
# 不同的
您可以使用`DISTINCT`子句筛选表中的不同条目:
mysql> SELECT DISTINCT title FROM titles; +--------------------+ | title | +--------------------+ | Senior Engineer | | Staff | | Engineer | | Senior Staff | | Assistant Engineer | | Technique Leader | | Manager | +--------------------+ 7 rows in set (0.30 sec)
# 使用 have 进行过滤
您可以通过添加`HAVING`子句来过滤`GROUP BY`子句的结果。
例如,找到平均工资超过 140000 的员工:
mysql> SELECT emp_no, AVG(salary) AS avg FROM salaries GROUP BY emp_no HAVING avg > 140000 ORDER BY avg DESC; +--------+-------------+ | emp_no | avg | +--------+-------------+ | 109334 | 141835.3333 | | 205000 | 141064.6364 | +--------+-------------+ 2 rows in set (0.80 sec)
# 另见
还有许多其他聚合函数,请参见[https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html) 了解更多信息。
# 创建用户
到目前为止,您只使用 root 用户连接到 MySQL 并执行语句。访问 MySQL 时不应使用 root 用户,除了来自`localhost`的管理任务。您应该创建用户、限制访问、限制资源使用等。要创建新用户,您应该拥有`CREATE USER`特权,这将在下一节中讨论。在初始设置期间,您可以使用 root 用户创建其他用户。
# 怎么做。。。
使用 root 用户连接 mysql,执行`CREATE USER`命令创建新用户。
mysql> CREATE USER IF NOT EXISTS 'company_read_only'@'localhost' IDENTIFIED WITH mysql_native_password BY 'company_pass' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
如果密码不强,可能会出现以下错误。
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
前面的语句将创建具有以下内容的用户:
* `* Username`:`company_read_only`。
* `* access only from`:`localhost`。
* 您可以限制对 IP 范围的访问。例如:`10.148.%.%`。通过给出`%`,用户可以从任何主机访问。
* `* password`:`company_pass`。
* `* using mysql_native_password`(默认)身份验证。
* 您还可以指定任何可插入的身份验证,例如`sha256_password`、`LDAP`或 Kerberos。
* 用户一小时内可以执行的`* maximum number of queries`为 500。
* 用户一小时内可以执行的`* maximum number of updates`为 100。
当客户端连接到 MySQL 服务器时,它会经历两个阶段:
1. 访问控制连接验证
2. 访问控制请求验证
在连接验证期间,服务器通过用户名和连接的主机名标识连接。服务器为用户调用身份验证插件并验证密码。它还检查用户是否被锁定。
在请求验证阶段,服务器检查用户是否有足够的权限执行每个操作。
在前面的语句中,您必须以明文形式给出密码,该密码可以记录在命令历史文件`$HOME/.mysql_history`中。为了避免这种情况,可以在本地服务器上计算哈希值,并直接指定哈希字符串。其语法相同,只是`mysql_native_password BY 'company_pass'`改为`mysql_native_password AS 'hashed_string'`:
mysql> SELECT PASSWORD('company_pass'); +-------------------------------------------+ |PASSWORD('company_pass') | +-------------------------------------------+ | *EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18 | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> CREATE USER IF NOT EXISTS 'company_read_only'@'localhost' IDENTIFIED WITH mysql_native_password
AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18' WITH MAX_QUERIES_PER_HOUR 500 MAX_UPDATES_PER_HOUR 100;
You can directly create users by granting privileges. Refer to the next section on how to grant privileges. However, MySQL will deprecate this feature in the next release.
# 另见
参见[https://dev.mysql.com/doc/refman/8.0/en/create-user.html](https://dev.mysql.com/doc/refman/8.0/en/create-user.html) 查看创建用户的更多选项。更多使用其他身份验证方法的安全选项,如 SSL,将在[第 14 章](mysql8-tutorial-14.html#EQTO40-faa69fe6f4c04957afca3568dcd9cd83)、*安全*中讨论。
# 授予和撤消对用户的访问权限
您可以限制用户访问特定的数据库或表,也可以只访问特定的操作,如`SELECT`、`INSERT`、`UPDATE`。要向其他用户授予权限,您应该拥有`GRANT`权限。
# 怎么做。。。
在初始设置期间,您可以使用 root 用户授予权限。您还可以创建管理帐户来管理用户。
# 授予特权
* 将`READ ONLY(SELECT)`权限授予`company_read_only`用户:
mysql> GRANT SELECT ON company.* TO 'company_read_only'@'localhost'; Query OK, 0 rows affected (0.06 sec)
星号(`*`表示数据库中的所有表。
* 将`INSERT`权限授予新`company_insert_only`用户:
mysql> GRANT INSERT ON company.* TO 'company_insert_only'@'localhost' IDENTIFIED BY 'xxxx'; Query OK, 0 rows affected, 1 warning (0.05 sec)
mysql> SHOW WARNINGS\G 1. row Level: Warning Code: 1287 Message: Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. 1 row in set (0.00 sec)
* 将`WRITE`权限授予新`company_write`用户:
mysql> GRANT INSERT, DELETE, UPDATE ON company. TO 'company_write'@'%' IDENTIFIED WITH mysql_native_password AS 'EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18'; Query OK, 0 rows affected, 1 warning (0.04 sec)
* 限制到特定的表。从`employees`表中将`employees_read_only`用户限制为`SELECT`:
mysql> GRANT SELECT ON employees.employees TO 'employees_read_only'@'%' IDENTIFIED WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18'; Query OK, 0 rows affected, 1 warning (0.03 sec)
* 您可以进一步限制为特定列。将`employees_ro`用户限制在`employees`表的`first_name`和`last_name`列:
mysql> GRANT SELECT(first_name,last_name) ON employees.employees TO 'employees_ro'@'%' IDENTIFIED WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18'; Query OK, 0 rows affected, 1 warning (0.06 sec)
* 扩大赠款。您可以通过执行新授权来扩展授权。扩展`employees_col_ro`用户访问`salaries`表薪资的权限:
mysql> GRANT SELECT(salary) ON employees.salaries TO 'employees_ro'@'%'; Query OK, 0 rows affected (0.00 sec)
* 创建`SUPER`用户。您需要一个管理帐户来管理服务器。`ALL`表示除`GRANT`权限外的所有权限:
mysql> CREATE USER 'dbadmin'@'%' IDENTIFIED WITH mysql_native_password BY 'DB@dm1n'; Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL ON . TO 'dbadmin'@'%'; Query OK, 0 rows affected (0.01 sec)
* 授予`GRANT`特权。用户应具有`GRANT OPTION`权限,可以将权限授予其他用户。您可以将`GRANT`权限扩展到`dbadmin`超级用户:
mysql> GRANT GRANT OPTION ON . TO 'dbadmin'@'%'; Query OK, 0 rows affected (0.03 sec)
参见[https://dev.mysql.com/doc/refman/8.0/en/grant.html](https://dev.mysql.com/doc/refman/8.0/en/grant.html) 获取更多特权类型。
# 支票补助金
您可以检查所有用户的授权。检查`employee_col_ro`用户的授权:
mysql> SHOW GRANTS FOR 'employees_ro'@'%'\G
1. row
Grants for employees_ro@%: GRANT USAGE ON . TO employees_ro
@%
2. row
Grants for employees_ro@%: GRANT SELECT (first_name
, last_name
) ON employees
.employees
TO employees_ro
@%
3. row
Grants for employees_ro@%: GRANT SELECT (salary
) ON employees
.salaries
TO employees_ro
@%
检查`dbadmin`用户的授权。您可以看到`dbadmin`用户可获得的所有授权:
mysql> SHOW GRANTS FOR 'dbadmin'@'%'\G
1. row
Grants for dbadmin@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO dbadmin
@%
WITH GRANT OPTION
2. row
Grants for dbadmin@%: GRANT BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,REPLICATION_SLAVE_ADMIN,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN ON . TO dbadmin
@%
2 rows in set (0.00 sec)
# 撤销补助金
撤销授权与创建授权的语法相同。您授予用户权限`TO`并撤销用户权限`FROM`。
* 撤销`'company_write'@'%'`用户的`DELETE`访问权限:
mysql> REVOKE DELETE ON company.* FROM 'company_write'@'%'; Query OK, 0 rows affected (0.04 sec)
* 撤销`employee_ro`用户对工资栏的访问权:
mysql> REVOKE SELECT(salary) ON employees.salaries FROM 'employees_ro'@'%'; Query OK, 0 rows affected (0.03 sec)
# 修改 mysql.user 表
所有用户信息以及权限都存储在`mysql.user`表中。如果您有访问`mysql.user`表的权限,可以直接修改`mysql.user`表来创建用户并授予权限。
如果您使用`GRANT`、`REVOKE`、`SET PASSWORD`或`RENAME USER`等账户管理语句间接修改授权表,服务器会注意到这些更改,并立即将授权表重新加载到内存中。
如果您使用诸如`INSERT`、`UPDATE`或`DELETE`之类的语句直接修改授权表,则您所做的更改不会影响权限检查,除非您重新启动服务器或告诉它重新加载表。如果直接更改授权表,但忘记重新加载它们,则在重新启动服务器之前,所做的更改将无效。
可以通过发出`FLUSH PRIVILEGES`语句来重新加载`GRANT`表。
查询`mysql.user`表,找出`dbadmin`用户的所有条目:
mysql> SELECT * FROM mysql.user WHERE user='dbadmin'\G 1. row Host: % User: dbadmin Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: Y References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *AB7018ADD9CB4EDBEB680BB3F820479E4CE815D2 password_expired: N password_last_changed: 2017-06-10 16:24:03 password_lifetime: NULL account_locked: N Create_role_priv: Y Drop_role_priv: Y 1 row in set (0.00 sec)
您可以看到,`dbadmin`用户可以从任何主机(%)访问数据库。您只需更新`mysql.user`表并重新加载授权表,即可将其限制为`localhost`:
mysql> UPDATE mysql.user SET host='localhost' WHERE user='dbadmin'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
# 为用户设置密码到期时间
您可以在特定的时间间隔内使用户的密码过期;之后,他们需要更改密码。
当应用程序开发人员请求访问数据库时,您可以使用默认密码创建帐户,然后将其设置为过期。您可以与开发人员共享密码,然后他们必须更改密码才能继续使用 MySQL。
所有账户的密码到期日均等于`default_password_lifetime`变量,默认情况下禁用该变量:
* 创建密码过期的用户。当开发人员第一次登录并尝试执行任何语句时,`ERROR 1820 (HY000):`被抛出。在执行此语句之前,必须使用`ALTER USER`语句重置密码:
mysql> CREATE USER 'developer'@'%' IDENTIFIED WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18' PASSWORD EXPIRE; Query OK, 0 rows affected (0.04 sec
shell> mysql -u developer -pcompany_pass mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 31 Server version: 8.0.3-rc-log
Copyright (c) 2000, 2017, 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> SHOW DATABASES; ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
开发人员必须使用以下命令更改其密码:
mysql> ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY 'new_company_pass'; Query OK, 0 rows affected (0.03 sec)
* 手动使现有用户过期:
mysql> ALTER USER 'developer'@'%' PASSWORD EXPIRE; Query OK, 0 rows affected (0.06 sec)
* 要求每 180 天更改一次密码:
mysql> ALTER USER 'developer'@'%' PASSWORD EXPIRE INTERVAL 90 DAY; Query OK, 0 rows affected (0.04 sec)
# 锁定用户
如果您发现帐户有任何问题,可以锁定它。MySQL 支持在使用`CREATE USER`或`ALTER USER`时锁定。
通过在`ALTER USER`对账单中添加`ACCOUNT LOCK`条款锁定账户:
mysql> ALTER USER 'developer'@'%' ACCOUNT LOCK; Query OK, 0 rows affected (0.05 sec)
开发人员将收到一个错误,说明帐户已锁定:
shell> mysql -u developer -pnew_company_pass mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 3118 (HY000): Access denied for user 'developer'@'localhost'. Account is locked.
您可以在确认以下内容后解锁帐户:
mysql> ALTER USER 'developer'@'%' ACCOUNT UNLOCK; Query OK, 0 rows affected (0.00 sec)
# 为用户创建角色
MySQL 角色是一个命名的特权集合。与用户帐户一样,角色可以被授予或撤销权限。可以向用户帐户授予角色,这将授予该帐户角色权限。前面,您为读取、写入和管理创建了单独的用户。对于写入权限,您已将`INSERT`、`DELETE`和`UPDATE`授予用户。相反,您可以将这些权限授予角色,然后将用户分配给该角色。通过这种方式,您可以避免单独向可能多个用户帐户授予权限。
* 创建角色:
mysql> CREATE ROLE 'app_read_only', 'app_writes', 'app_developer'; Query OK, 0 rows affected (0.01 sec)
* 使用`GRANT`语句为角色分配权限:
mysql> GRANT SELECT ON employees.* TO 'app_read_only'; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT INSERT, UPDATE, DELETE ON employees.* TO 'app_writes'; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL ON employees.* TO 'app_developer'; Query OK, 0 rows affected (0.04 sec)
* 创建用户。如果您未指定任何主机,则将采用`%`:
mysql> CREATE user emp_read_only IDENTIFIED BY 'emp_pass'; Query OK, 0 rows affected (0.06 sec)
mysql> CREATE user emp_writes IDENTIFIED BY 'emp_pass'; Query OK, 0 rows affected (0.04 sec)
mysql> CREATE user emp_developer IDENTIFIED BY 'emp_pass'; Query OK, 0 rows affected (0.01 sec)
mysql> CREATE user emp_read_write IDENTIFIED BY 'emp_pass'; Query OK, 0 rows affected (0.00 sec)
* 使用`GRANT`语句将角色分配给用户。您可以为一个用户分配多个角色。
例如,您可以为`emp_read_write`用户分配读写权限:
mysql> GRANT 'app_read_only' TO 'emp_read_only'@'%'; Query OK, 0 rows affected (0.04 sec)
mysql> GRANT 'app_writes' TO 'emp_writes'@'%'; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT 'app_developer' TO 'emp_developer'@'%'; Query OK, 0 rows affected (0.00 sec)
mysql> GRANT 'app_read_only', 'app_writes' TO 'emp_read_write'@'%'; Query OK, 0 rows affected (0.05 sec)
作为安全措施,避免使用`%`并限制对部署应用程序的 IP 的访问。
# 将数据选择到文件和表中
您可以使用`SELECT INTO OUTFILE`语句将输出保存到文件中。
可以指定列分隔符和行分隔符,以后可以将数据导入其他数据平台。
# 怎么做。。。
可以将输出目标另存为文件或表。
# 另存为文件
* 要将输出保存到文件中,您需要`FILE`权限。`FILE`是一个全局权限,这意味着您不能对特定数据库进行限制。但是,您可以限制用户选择的内容:
mysql> GRANT SELECT ON employees. TO 'user_ro_file'@'%' IDENTIFIED WITH mysql_native_password AS 'EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18'; Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT FILE ON . TO 'user_ro_file'@'%' IDENTIFIED WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18'; Query OK, 0 rows affected, 1 warning (0.00 sec)
* 在 Ubuntu 上,默认情况下,MySQL 不允许您写入文件。您应该在配置文件中设置`secure_file_priv`并重新启动 MySQL。您将在[第 4 章](mysql8-tutorial-02.html)、*配置 MySQL*中了解更多有关配置的信息。在 CentOS 上,Red Hat,`secure_file_priv`设置为`/var/lib/mysql-files`,这意味着所有文件都将保存在该目录中。
* 现在,请像这样启用。打开配置文件并添加`secure_file_priv = /var/lib/mysql`:
shell> sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
* 重新启动 MySQL 服务器:
shell> sudo systemctl restart mysql
以下语句将输出保存为 CSV 格式:
mysql> SELECT first_name, last_name INTO OUTFILE 'result.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM employees WHERE hire_date<'1986-01-01' LIMIT 10; Query OK, 10 rows affected (0.00 sec)
您可以检查文件的输出,该文件将在`{secure_file_priv}/{database_name}`指定的路径中创建,本例中为`/var/lib/mysql/employees/`。如果文件已存在,则该语句将失败,因此每次执行或将文件移动到其他位置时,都需要提供唯一的名称:
shell> sudo cat /var/lib/mysql/employees/result.csv "Bezalel","Simmel" "Sumant","Peac" "Eberhardt","Terkki" "Otmar","Herbst" "Florian","Syrotiuk" "Tse","Herber" "Udi","Jansch" "Reuven","Garigliano" "Erez","Ritzmann" "Premal","Baek"
# 另存为表格
您可以将`SELECT`语句的结果保存到表中。即使该表不存在,也可以使用`CREATE`和`SELECT`创建该表并加载数据。如果表已经存在,可以使用`INSERT`和`SELECT`加载数据。
您可以将标题保存到新的`titles_only`表中:
mysql> CREATE TABLE titles_only AS SELECT DISTINCT title FROM titles; Query OK, 7 rows affected (0.50 sec) Records: 7 Duplicates: 0 Warnings: 0
如果表已经存在,可以使用`INSERT INTO SELECT`语句:
mysql> INSERT INTO titles_only SELECT DISTINCT title FROM titles; Query OK, 7 rows affected (0.46 sec) Records: 7 Duplicates: 0 Warnings: 0
为了避免重复,您可以使用`INSERT IGNORE`。但是,在本例中,`titles_only`表上没有`PRIMARY KEY`。因此,`IGNORE`条款没有任何区别。
# 将数据加载到表中
您可以将表数据转储到文件中,也可以将表数据转储到文件中,即将数据从文件加载到表中。这广泛用于加载批量数据,是将数据加载到表中的一种超快速方法。可以指定列分隔符以将数据加载到相应的列中。您应该拥有桌上的`FILE`特权和`INSERT`特权。
# 怎么做。。。
之前,您已将`first_name`和`last_name`保存到一个文件中。您可以使用同一个文件将数据加载到另一个表中。在加载之前,您应该创建表。如果表已经存在,可以直接加载。表的列应与文件的字段匹配。
创建一个表以保存数据:
mysql> CREATE TABLE employee_names (
first_name
varchar(14) NOT NULL,
last_name
varchar(16) NOT NULL
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)
确保文件存在:
shell> sudo ls -lhtr /var/lib/mysql/employees/result.csv -rw-rw-rw- 1 mysql mysql 180 Jun 10 14:53 /var/lib/mysql/employees/result.csv
使用`LOAD DATA INFILE`语句加载数据:
mysql> LOAD DATA INFILE 'result.csv' INTO TABLE employee_names FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; Query OK, 10 rows affected (0.01 sec) Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
文件可以作为完整路径名指定其确切位置。如果作为相对路径名给出,则该名称将相对于启动客户端程序的目录进行解释。
* 如果文件包含任何要忽略的标题,请指定`IGNORE n LINES`:
mysql> LOAD DATA INFILE 'result.csv' INTO TABLE employee_names FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
* 您可以指定`REPLACE`或`IGNORE`来处理重复项:
mysql> LOAD DATA INFILE 'result.csv' REPLACE INTO TABLE employee_names FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; Query OK, 10 rows affected (0.01 sec) Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
mysql> LOAD DATA INFILE 'result.csv' IGNORE INTO TABLE employee_names FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'; Query OK, 10 rows affected (0.06 sec) Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
* MySQL 假定要加载的文件在服务器上可用。如果您从远程客户端计算机连接到服务器,则可以指定`LOCAL`加载位于客户端的文件。本地文件将从客户端复制到服务器。文件保存在服务器的标准临时位置。在 Linux 机器中,它是`/tmp`:
mysql> LOAD DATA LOCAL INFILE 'result.csv' IGNORE INTO TABLE employee_names FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
# 连接表
到目前为止,您已经了解了如何从单个表中插入和检索数据。在本节中,我们将讨论如何组合两个或多个表来检索结果。
一个很好的例子是,您想用`emp_no: 110022`查找员工的姓名和部门号:
* 部门编号和名称存储在`departments`表中
* 员工编号和其他详细信息,如`first_name`和`last_name`存储在`employees`表中
* 员工和部门的映射存储在`dept_manager`表中
如果您不想使用`JOIN`,可以这样做:
1. 从`employee`表中查找`emp_no`为`110022`的员工姓名:
mysql> SELECT emp.emp_no, emp.first_name, emp.last_name
FROM employees AS emp
WHERE emp.emp_no=110022;
+--------+------------+------------+
| emp_no | first_name | last_name |
+--------+------------+------------+
| 110022 | Margareta | Markovitch |
+--------+------------+------------+
1 row in set (0.00 sec)
2. 从`departments`表中查找部门编号:
mysql> SELECT dept_no FROM dept_manager AS dept_mgr WHERE dept_mgr.emp_no=110022; +---------+ | dept_no | +---------+ | d001 | +---------+ 1 row in set (0.00 sec)
3. 从`departments`表中查找部门名称:
mysql> SELECT dept_name FROM departments dept WHERE dept.dept_no='d001'; +-----------+ | dept_name | +-----------+ | Marketing | +-----------+ 1 row in set (0.00 sec)
# 怎么做。。。
为了避免使用三个语句在三个不同的表上查找,您可以使用`JOIN`将它们合并。这里需要注意的重要一点是连接两个表,您应该有一个或多个公共列来连接。您可以加入员工和基于`emp_no`的`dept_manager`,他们都有`emp_no`栏。虽然名称不需要匹配,但您应该找出可以加入的列。类似地,`dept_mgr`和`departments`将`dept_no`作为公共列。
和列别名一样,您可以为表指定别名,并使用别名引用该表的列。例如,您可以使用`FROM employees AS emp`为员工提供别名,并使用点符号引用`employees`表中的列,例如`emp.emp_no`:
mysql> SELECT emp.emp_no, emp.first_name, emp.last_name, dept.dept_name FROM employees AS emp JOIN dept_manager AS dept_mgr ON emp.emp_no=dept_mgr.emp_no AND emp.emp_no=110022 JOIN departments AS dept ON dept_mgr.dept_no=dept.dept_no; +--------+------------+------------+-----------+ | emp_no | first_name | last_name | dept_name | +--------+------------+------------+-----------+ | 110022 | Margareta | Markovitch | Marketing | +--------+------------+------------+-----------+ 1 row in set (0.00 sec)
让我们看另一个例子,你想知道每个部门的平均工资。为此,您可以使用`AVG`功能和`dept_no`分组。要查找部门名称,您可以将结果与`dept_no`上的`departments`表关联:
mysql> SELECT dept_name, AVG(salary) AS avg_salary FROM salaries JOIN dept_emp ON salaries.emp_no=dept_emp.emp_no JOIN departments ON dept_emp.dept_no=departments.dept_no GROUP BY dept_emp.dept_no ORDER BY avg_salary DESC; +--------------------+------------+ | dept_name | avg_salary | +--------------------+------------+ | Sales | 80667.6058 | | Marketing | 71913.2000 | | Finance | 70489.3649 | | Research | 59665.1817 | | Production | 59605.4825 | | Development | 59478.9012 | | Customer Service | 58770.3665 | | Quality Management | 57251.2719 | | Human Resources | 55574.8794 | +--------------------+------------+ 9 rows in set (8.29 sec)
# 使用自联接识别重复项
您希望在表中查找特定列的重复行。例如,您希望找出哪些员工具有相同的`first_name`、相同的`last_name`、相同的`gender`和相同的`hire_date`。在这种情况下,您可以将`employees`表与其自身连接起来,同时指定要在`JOIN`子句中查找重复项的列。您需要为每个表使用不同的别名。
您需要在要加入的列上添加索引。这些指标将在[第 13 章](mysql8-tutorial-13.html#CRVJ00-faa69fe6f4c04957afca3568dcd9cd83)、*性能调整*中讨论。现在,您可以执行以下命令来添加索引:
mysql> ALTER TABLE employees ADD INDEX name(first_name, last_name); Query OK, 0 rows affected (1.95 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT emp1.* FROM employees emp1 JOIN employees emp2 ON emp1.first_name=emp2.first_name AND emp1.last_name=emp2.last_name AND emp1.gender=emp2.gender AND emp1.hire_date=emp2.hire_date AND emp1.emp_no!=emp2.emp_no ORDER BY first_name, last_name; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 232772 | 1962-05-14 | Keung | Heusch | M | 1986-06-01 | | 493600 | 1964-01-26 | Keung | Heusch | M | 1986-06-01 | | 64089 | 1958-01-19 | Marit | Kolvik | F | 1993-12-08 | | 424486 | 1952-07-06 | Marit | Kolvik | F | 1993-12-08 | | 40965 | 1952-05-11 | Marsha | Farrow | M | 1989-02-18 | | 14641 | 1953-05-08 | Marsha | Farrow | M | 1989-02-18 | | 422332 | 1954-08-17 | Naftali | Mawatari | M | 1985-09-14 | | 427429 | 1962-11-06 | Naftali | Mawatari | M | 1985-09-14 | | 19454 | 1955-05-14 | Taisook | Hutter | F | 1985-02-26 | | 243627 | 1957-02-14 | Taisook | Hutter | F | 1985-02-26 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (34.01 sec)
你必须提到`emp1.emp_no != emp2.emp_no`,因为员工会有不同的`emp_no`。否则,将显示相同的员工。
# 使用子查询
子查询是另一个语句中的`SELECT`语句。假设您想找到在`1986-06-26`上以`Senior Engineer`开头的员工的姓名。
您可以从`titles`表中获取`emp_no`,从`employees`表中获取名称。您也可以使用`JOIN`来了解结果。
要从标题中获取`emp_no`:
mysql> SELECT emp_no FROM titles WHERE title="Senior Engineer" AND from_date="1986-06-26"; +--------+ | emp_no | +--------+ | 10001 | | 84305 | | 228917 | | 426700 | | 458304 | +--------+ 5 rows in set (0.14 sec)
要查找名称,请执行以下操作:
mysql> SELECT first_name, last_name FROM employees WHERE emp_no IN (< output from preceding query>)
mysql> SELECT first_name, last_name FROM employees WHERE emp_no IN (10001,84305,228917,426700,458304); +------------+-----------+ | first_name | last_name | +------------+-----------+ | Georgi | Facello | | Minghong | Kalloufi | | Nechama | Bennet | | Nagui | Restivo | | Shuzo | Kirkerud | +------------+-----------+ 5 rows in set (0.00 sec
MySQL 中还支持其他子句,如`EXISTS`和`EQUAL`。请参阅参考手册,[https://dev.mysql.com/doc/refman/8.0/en/subqueries.html](https://dev.mysql.com/doc/refman/8.0/en/subqueries.html) ,有关更多详细信息:
mysql> SELECT first_name, last_name FROM employees WHERE emp_no IN (SELECT emp_no FROM titles WHERE title="Senior Engineer" AND from_date="1986-06-26"); +------------+-----------+ | first_name | last_name | +------------+-----------+ | Georgi | Facello | | Minghong | Kalloufi | | Nagui | Restivo | | Nechama | Bennet | | Shuzo | Kirkerud | +------------+-----------+ 5 rows in set (0.91 sec)
查找工资最高的员工:
mysql> SELECT emp_no FROM salaries WHERE salary=(SELECT MAX(salary) FROM salaries); +--------+ | emp_no | +--------+ | 43624 | +--------+ 1 row in set (1.54 sec)
`SELECT MAX(salary) FROM salaries`是给出最高工资的子查询,要查找该工资对应的员工编号,可以在`WHERE`子句中使用该子查询。
# 查找表之间不匹配的行
假设要在一个表中查找不在其他表中的行。您可以通过两种方式实现这一点。使用`NOT IN`子句或使用`OUTER JOIN`。
要查找匹配的行,可以使用普通`JOIN`,如果要查找不匹配的行,可以使用`OUTER JOIN`。正常`JOIN`表示*A 交叉口 B*。`OUTER JOIN`给出了*A*和*B*的匹配记录,也给出了*A*与`NULL`的不匹配记录。如果您想要`A-B`的输出,可以使用`WHERE <JOIN COLUMN IN B> IS NULL`子句。
要了解`OUTER JOIN`的用法,请创建两个`employee`表并插入一些值:
mysql> CREATE TABLE employees_list1 AS SELECT * FROM employees WHERE first_name LIKE 'aa%'; Query OK, 444 rows affected (0.22 sec) Records: 444 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE employees_list2 AS SELECT * FROM employees WHERE emp_no BETWEEN 400000 AND 500000 AND gender='F'; Query OK, 39892 rows affected (0.59 sec) Records: 39892 Duplicates: 0 Warnings: 0
您已经知道如何查找两个列表中的员工:
mysql> SELECT * FROM employees_list1 WHERE emp_no IN (SELECT emp_no FROM employees_list2);
或者您可以使用`JOIN`:
mysql> SELECT l1.* FROM employees_list1 l1 JOIN employees_list2 l2 ON l1.emp_no=l2.emp_no;
要了解存在于`employees_list1`但不存在于`employees_list2`的员工:
mysql> SELECT * FROM employees_list1 WHERE emp_no NOT IN (SELECT emp_no FROM employees_list2);
或者您可以使用`OUTER JOIN`:
mysql> SELECT l1.* FROM employees_list1 l1 LEFT OUTER JOIN employees_list2 l2 ON l1.emp_no=l2.emp_no WHERE l2.emp_no IS NULL;
外部联接为每个不匹配的行创建联接列表中第二个表的`NULL`列。如果您使用`RIGHT JOIN`,第一个表将为不匹配的行获取`NULL`值。
您还可以使用`OUTER JOIN`查找匹配的行。将`WHERE l2.emp_no IS NULL`改为`WHERE emp_no IS NOT NULL`:
mysql> SELECT l1.* FROM employees_list1 l1 LEFT OUTER JOIN employees_list2 l2 ON l1.emp_no=l2.emp_no WHERE l2.emp_no IS NOT NULL;
# 存储过程
假设您需要在 MySQL 中执行一系列语句,而不是每次都发送所有 SQL 语句,您可以将所有语句封装在一个程序中,并在需要时调用它。存储过程是一组不需要返回值的 SQL 语句。
除了 SQL 语句之外,您还可以使用变量来存储结果,并在存储过程中执行编程操作。例如,您可以编写`IF`、`CASE`子句、逻辑运算和`WHILE`循环。
* 存储函数和过程也称为存储例程。
* 要创建存储过程,您应该具有`CREATE ROUTINE`权限。
* 存储的函数将有一个返回值。
* 存储过程没有返回值。
* 所有代码都写在`BEGIN and END`块中。
* 存储的函数可以在`SELECT`语句中直接调用。
* 可以使用`CALL`语句调用存储过程。
* 由于存储例程中的语句应该以分隔符(`;`结尾,因此必须更改 MySQL 的分隔符,以便 MySQL 不会用普通语句解释存储例程中的 SQL 语句。创建过程后,可以将分隔符更改回默认值。
# 怎么做。。。
例如,您希望添加新员工。您应该更新三个表,即`employees`、`salaries`和`titles`。您可以开发一个存储过程并调用它来创建一个新的`employee`,而不是执行三条语句。
您必须通过员工的`first_name`、`last_name`、`gender`、`birth_date`以及员工加入的部门。您可以使用输入变量传递这些信息,并且应该将员工编号作为输出。存储过程不返回值,但它可以更新变量,您可以使用它。
下面是一个简单的存储过程示例,用于创建新员工并更新`salary`和`department`表:
/ DROP the existing procedure if any with the same name before creating /
DROP PROCEDURE IF EXISTS create_employee;
/ Change the delimiter to $$ /
DELIMITER $$
/ IN specifies the variables taken as arguments, INOUT specifies the output variable/
CREATE PROCEDURE create_employee (OUT new_emp_no INT, IN first_name varchar(20), IN last_name varchar(20), IN gender enum('M','F'), IN birth_date date, IN emp_dept_name varchar(40), IN title varchar(50))
BEGIN
/ Declare variables for emp_dept_no and salary /
DECLARE emp_dept_no char(4);
DECLARE salary int DEFAULT 60000;
/* Select the maximum employee number into the variable new_emp_no */
SELECT max(emp_no) INTO new_emp_no FROM employees;
/* Increment the new_emp_no */
SET new_emp_no = new_emp_no + 1;
/* INSERT the data into employees table */
/* The function CURDATE() gives the current date) */
INSERT INTO employees VALUES(new_emp_no, birth_date, first_name, last_name, gender, CURDATE());
/* Find out the dept_no for dept_name */
SELECT emp_dept_name;
SELECT dept_no INTO emp_dept_no FROM departments WHERE dept_name=emp_dept_name;
SELECT emp_dept_no;
/* Insert into dept_emp */
INSERT INTO dept_emp VALUES(new_emp_no, emp_dept_no, CURDATE(), '9999-01-01');
/* Insert into titles */
INSERT INTO titles VALUES(new_emp_no, title, CURDATE(), '9999-01-01');
/* Find salary based on title */
IF title = 'Staff'
THEN SET salary = 100000;
ELSEIF title = 'Senior Staff'
THEN SET salary = 120000;
END IF;
/* Insert into salaries */
INSERT INTO salaries VALUES(new_emp_no, salary, CURDATE(), '9999-01-01');
END $$ / Change the delimiter back to ; / DELIMITER ;
要创建存储过程,您可以:
* 将其粘贴到命令行客户端
* 保存在文件中,使用`mysql -u <user> -p employees < stored_procedure.sql`导入 MySQL
* 来源于`mysql> SOURCE stored_procedure.sql`文件
要使用存储过程,请将执行权限授予`emp_read_only`用户:
mysql> GRANT EXECUTE ON employees.* TO 'emp_read_only'@'%'; Query OK, 0 rows affected (0.05 sec)
使用`CALL stored_procedure(OUT variable, IN values)`语句和例程名称调用存储过程。
使用`emp_read_only`账号连接 MySQL:
shell> mysql -u emp_read_only -pemp_pass employees -A
传递要存储`@new_emp_no`输出的变量,并传递所需的输入值:
mysql> CALL create_employee(@new_emp_no, 'John', 'Smith', 'M', '1984-06-19', 'Research', 'Staff'); Query OK, 1 row affected (0.01 sec)
选择`emp_no`的值,该值存储在`@new_emp_no`变量中:
mysql> SELECT @new_emp_no; +-------------+ | @new_emp_no | +-------------+ | 500000 | +-------------+ 1 row in set (0.00 sec)
检查是否在`employees`、`salaries`和`titles`表中创建了行:
mysql> SELECT * FROM employees WHERE emp_no=500000; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 500000 | 1984-06-19 | John | Smith | M | 2017-06-17 | +--------+------------+------------+-----------+--------+------------+ 1 row in set (0.00 sec)
mysql> SELECT * FROM salaries WHERE emp_no=500000; +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 500000 | 100000 | 2017-06-17 | 9999-01-01 | +--------+--------+------------+------------+ 1 row in set (0.00 sec)
mysql> SELECT * FROM titles WHERE emp_no=500000; +--------+-------+------------+------------+ | emp_no | title | from_date | to_date | +--------+-------+------------+------------+ | 500000 | Staff | 2017-06-17 | 9999-01-01 | +--------+-------+------------+------------+ 1 row in set (0.00 sec)
您可以看到,即使`emp_read_only`对表没有写访问权限,它也可以通过调用存储过程进行写操作。如果存储过程的`SQL SECURITY`被创建为`INVOKER`,则`emp_read_only`不能修改数据。请注意,如果您使用`localhost`进行连接,请为`localhost`用户创建权限。
要列出数据库中的所有存储过程,请执行`SHOW PROCEDURE STATUS\G`。要检查现有存储例程的定义,可以执行`SHOW CREATE PROCEDURE <procedure_name>\G`。
# 还有更多。。。
存储过程也用于增强安全性。用户需要存储过程的`EXECUTE`权限才能执行它。
根据存储例程的定义:
* `DEFINER`子句指定存储例程的创建者。如果未指定任何内容,则使用当前用户。
* `SQL SECURITY`子句指定存储例程的执行上下文。它可以是`DEFINER`或`INVOKER`。
`DEFINER`:即使只有例程的`EXECUTE`权限的用户也可以调用并获取存储的例程的输出,而不管该用户是否拥有对底层表的权限。如果`DEFINER`有特权就足够了。
`INVOKER`:安全上下文切换到调用存储例程的用户。在这种情况下,调用程序应该可以访问基础表。
# 另见
有关更多示例和语法,请参阅文档,位于[https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html](https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html) 。
# 功能
与存储过程一样,您可以创建存储函数。主要区别在于函数应该有一个返回值,可以在`SELECT`中调用。通常,创建存储函数是为了简化复杂的计算。
# 怎么做。。。
下面是一个如何编写函数和如何调用函数的示例。假设一位银行家希望根据收入水平发放信用卡,而不是公开实际工资,您可以公开此函数以了解收入水平:
shell> vi function.sql; DROP FUNCTION IF EXISTS get_sal_level; DELIMITER $$ CREATE FUNCTION get_sal_level(emp int) RETURNS VARCHAR(10) DETERMINISTIC BEGIN DECLARE sal_level varchar(10); DECLARE avg_sal FLOAT;
SELECT AVG(salary) INTO avg_sal FROM salaries WHERE emp_no=emp;
IF avg_sal < 50000 THEN SET sal_level = 'BRONZE'; ELSEIF (avg_sal >= 50000 AND avg_sal < 70000) THEN SET sal_level = 'SILVER'; ELSEIF (avg_sal >= 70000 AND avg_sal < 90000) THEN SET sal_level = 'GOLD'; ELSEIF (avg_sal >= 90000) THEN SET sal_level = 'PLATINUM'; ELSE SET sal_level = 'NOT FOUND'; END IF; RETURN (sal_level); END $$ DELIMITER ;
要创建函数,请执行以下操作:
mysql> SOURCE function.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec)
You have to pass the employee number and the function returns the income level. mysql> SELECT get_sal_level(10002); +----------------------+ | get_sal_level(10002) | +----------------------+ | SILVER | +----------------------+ 1 row in set (0.00 sec)
mysql> SELECT get_sal_level(10001); +----------------------+ | get_sal_level(10001) | +----------------------+ | GOLD | +----------------------+ 1 row in set (0.00 sec)
mysql> SELECT get_sal_level(1); +------------------+ | get_sal_level(1) | +------------------+ | NOT FOUND | +------------------+ 1 row in set (0.00 sec)
要列出数据库中存储的所有函数,请执行`SHOW FUNCTION STATUS\G`。要检查现有存储函数的定义,可以执行`SHOW CREATE FUNCTION <function_name>\G`。
It is very important to give the `DETERMINISTIC` keyword in the function creation. A routine is considered `DETERMINISTIC` if it always produces the same result for the same input parameters, and `NOT DETERMINISTIC` otherwise. If neither `DETERMINISTIC` nor `NOT DETERMINISTIC` is given in the routine definition, the default is `NOT DETERMINISTIC`. To declare that a function is deterministic, you must specify `DETERMINISTIC` explicitly.
Declaring a `NON DETERMINISTIC` routine as `DETERMINISTIC` might lead to unexpected results, by causing the optimizer to make incorrect execution plan choices. Declaring a `DETERMINISTIC` routine as `NON DETERMINISTIC` might diminish performance, by causing available optimizations not to be used.
# 内置函数
MySQL 提供了许多内置函数。您已经使用`CURDATE()`函数获取当前日期。
您可以使用`WHERE`子句中的函数:
mysql> SELECT * FROM employees WHERE hire_date = CURDATE();
* 例如,以下函数给出了一周前的日期:
mysql> SELECT DATE_ADD(CURDATE(), INTERVAL -7 DAY) AS '7 Days Ago';
* 添加两个字符串:
mysql> SELECT CONCAT(first_name, ' ', last_name) FROM employees LIMIT 1; +------------------------------------+ | CONCAT(first_name, ' ', last_name) | +------------------------------------+ | Aamer Anger | +------------------------------------+ 1 row in set (0.00 sec)
# 另见
有关完整的功能列表,请参阅 MySQL 参考手册,位于[https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html](https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html) 。
# 触发
触发器用于在触发事件之前或之后激活某些内容。例如,可以在插入到表中的每一行之前或更新的每一行之后激活触发器。
在不停机的情况下更改表时,触发器非常有用(请参阅*使用在线模式更改工具*部分中的[第 10 章](mysql8-tutorial-10.html#A1CDI0-faa69fe6f4c04957afca3568dcd9cd83)、*表维护*,也可用于审计目的。假设您想找出一行的前一个值,可以编写一个触发器,在更新之前将这些行保存在另一个表中。另一个表用作具有以前记录的审核表。
触发动作时间可以是`BEFORE`或`AFTER`,表示每一行修改前后触发动作。
触发事件可以是`INSERT`、`DELETE`或`UPDATE`:
* `INSERT`:每当通过`INSERT`、`REPLACE`或`LOAD DATA`插入新行时,触发器被激活
* `UPDATE`:通过`UPDATE`语句
* `DELETE`:通过`DELETE`或`REPLACE`语句
在 MySQL 5.7 中,一个表可以同时有多个触发器。例如,一个表可以有两个`BEFORE INSERT`触发器。您必须使用`FOLLOWS`或`PRECEDES`指定哪个触发器应该先触发。
# 怎么做。。。
例如,您希望在将薪资插入`salaries`表之前对其进行四舍五入。`NEW`指正在插入的新值:
shell> vi before_insert_trigger.sql
DROP TRIGGER IF EXISTS salary_round;
DELIMITER $$
CREATE TRIGGER salary_round BEFORE INSERT ON salaries
FOR EACH ROW
BEGIN
SET NEW.salary=ROUND(NEW.salary);
END
$$
DELIMITER ;
通过源文件创建触发器:
mysql> SOURCE before_insert_trigger.sql; Query OK, 0 rows affected (0.06 sec) Query OK, 0 rows affected (0.00 sec)
通过在工资中插入一个浮点数来测试触发器:
mysql> INSERT INTO salaries VALUES(10002, 100000.79, CURDATE(), '9999-01-01'); Query OK, 1 row affected (0.04 sec)
您可以看到工资四舍五入:
mysql> SELECT * FROM salaries WHERE emp_no=10002 AND from_date=CURDATE(); +--------+--------+------------+------------+ | emp_no | salary | from_date | to_date | +--------+--------+------------+------------+ | 10002 | 100001 | 2017-06-18 | 9999-01-01 | +--------+--------+------------+------------+ 1 row in set (0.00 sec)
类似地,您可以创建一个`BEFORE UPDATE`触发器来对薪资进行四舍五入。另一个例子:您想记录哪个用户已插入到`salaries`表中。创建一个`audit`表:
mysql> CREATE TABLE salary_audit (emp_no int, user varchar(50), date_modified date);
注意以下触发器位于`salary_round`触发器之前,由`PRECEDES salary_round`指定:
shell> vi before_insert_trigger.sql DELIMITER $$ CREATE TRIGGER salary_audit BEFORE INSERT ON salaries FOR EACH ROW PRECEDES salary_round BEGIN INSERT INTO salary_audit VALUES(NEW.emp_no, USER(), CURDATE()); END; $$ DELIMITER ;
将其插入`salaries`中:
mysql> INSERT INTO salaries VALUES(10003, 100000.79, CURDATE(), '9999-01-01'); Query OK, 1 row affected (0.06 sec)
通过查询`salary_audit`表,找出谁插入了薪资:
mysql> SELECT * FROM salary_audit WHERE emp_no=10003; +--------+----------------+---------------+ | emp_no | user | date_modified | +--------+----------------+---------------+ | 10003 | root@localhost | 2017-06-18 | +--------+----------------+---------------+ 1 row in set (0.00 sec)
如果`salary_audit`表掉落或不可用,`salaries`表上的所有插入都将被阻止。如果不想执行审核,则应先删除触发器,然后再删除表。
触发器会根据其复杂性在写入速度上产生开销。
要检查所有触发器,请执行`SHOW TRIGGERS\G`。
要检查现有触发器的定义,请执行`SHOW CREATE TRIGGER <trigger_name>`。
# 另见
请参阅 MySQL 参考手册,位于[https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html](https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html) ,了解更多详情。
# 意见
视图是基于 SQL 语句结果集的虚拟表。它也将有行和列,就像一个真正的表一样,但很少有限制,这将在后面讨论。视图隐藏了 SQL 的复杂性,更重要的是,提供了额外的安全性。
# 怎么做。。。
假设您只想访问`salaries`表的`emp_no`和`salary`列,`from_date`在`2002-01-01`之后。为此,可以使用提供所需结果的 SQL 创建视图。
mysql> CREATE ALGORITHM=UNDEFINED
DEFINER=root
@localhost
SQL SECURITY DEFINER VIEW salary_view
AS
SELECT emp_no, salary FROM salaries WHERE from_date > '2002-01-01';
现在创建了`salary_view`视图,您可以像查询任何其他表一样查询它:
mysql> SELECT emp_no, AVG(salary) as avg FROM salary_view GROUP BY emp_no ORDER BY avg DESC LIMIT 5;
您可以看到视图可以访问特定的行(即`from_date > '2002-01-01'`),而不是所有的行。您可以使用该视图限制用户对特定行的访问。
要列出所有视图,请执行:
mysql> SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';
要检查视图的定义,请执行:
mysql> SHOW CREATE VIEW salary_view\G
您可能已经注意到了`current_dept_emp`和`dept_emp_latest_date`视图,它们是`employee`数据库的一部分。您可以探索定义并找出它们的用途。
可以更新没有子查询、`JOINS`、`GROUP BY`子句、union 等的简单视图。`salary_view`是一个简单视图,如果基础表具有默认值,则可以更新或插入该视图:
mysql> UPDATE salary_view SET salary=100000 WHERE emp_no=10001; Query OK, 1 row affected (0.01 sec) Rows matched: 2 Changed: 1 Warnings: 0
mysql> INSERT INTO salary_view VALUES(10001,100001); ERROR 1423 (HY000): Field of view 'employees.salary_view' underlying table doesn't have a default value
如果该表具有默认值,则即使该行与视图中的筛选条件不匹配,也可以插入该行。为了避免这种情况,并插入满足视图条件的行,必须在定义中提供`WITH CHECK OPTION`。
`VIEW`算法:
* `MERGE`:MySQL 将输入查询和视图定义组合成一个查询,然后执行组合查询。`MERGE`算法只允许在简单视图上使用。
* `TEMPTABLE`:MySQL 将结果存储在临时表中,然后对该临时表执行输入查询。
* `UNDEFINED`(默认):MySQL 自动选择`MERGE`或`TEMPTABLE`算法。MySQL 更喜欢`MERGE`算法而不是`TEMPTABLE`算法,因为`MERGE`算法效率更高。
# 事件
就像 Linux 服务器上的 cron 一样,MySQL 有`EVENTS`来处理预定的任务。MySQL 使用一个称为事件调度线程的特殊线程来执行所有调度的事件。默认情况下,事件调度程序线程未启用(版本<8.0.3),要启用它,请执行:
mysql> SET GLOBAL event_scheduler = ON;
# 怎么做。。。
假设您不再需要保留超过一个月的薪资审核记录,您可以安排每天运行的事件,并从`salary_audit`表中删除一个月前的记录。
mysql> DROP EVENT IF EXISTS purge_salary_audit; DELIMITER $$ CREATE EVENT IF NOT EXISTS purge_salary_audit ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_DATE DO BEGIN DELETE FROM salary_audit WHERE date_modified < DATE_ADD(CURDATE(), INTERVAL -7 day); END $$ DELIMITER ;
创建事件后,它将自动执行清除薪资审核记录的工作。
* 要检查事件,请执行:
mysql> SHOW EVENTS\G 1. row Db: employees Name: purge_salary_audit Definer: root@localhost Time zone: SYSTEM Type: RECURRING Execute at: NULL Interval value: 1 Interval field: MINUTE Starts: 2017-06-18 00:00:00 Ends: NULL Status: ENABLED Originator: 0 character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
* 要检查事件的定义,请执行:
mysql> SHOW CREATE EVENT purge_salary_audit\G
* 要禁用/启用事件,请执行以下操作:
mysql> ALTER EVENT purge_salary_audit DISABLE; mysql> ALTER EVENT purge_salary_audit ENABLE;
# 访问控制
所有存储程序(过程、函数、触发器和事件)和视图都有一个`DEFINER`。如果未指定`DEFINER`,则创建对象的用户将被选择为`DEFINER`。
存储例程(过程和函数)和视图具有一个值为`DEFINER`或`INVOKER`的`SQL SECURITY`特性,用于指定对象是在定义器上下文中执行还是在调用器上下文中执行。触发器和事件没有`SQL SECURITY`特性,总是在定义器上下文中执行。服务器根据需要自动调用这些对象,因此没有调用用户。
# 另见
安排活动的方式很多,请参见[https://dev.mysql.com/doc/refman/8.0/en/event-scheduler.html](https://dev.mysql.com/doc/refman/8.0/en/event-scheduler.html) 了解更多详情。
# 获取有关数据库和表的信息
您可能已经注意到数据库列表中有一个`information_schema`数据库。`information_schema`是由所有数据库对象的元数据组成的视图集合。您可以连接到`information_schema`并浏览所有表格。本章介绍了使用最广泛的表格。您可以查询`information_schema`表,也可以使用`SHOW`命令,这基本上是相同的。
`INFORMATION_SCHEMA`查询作为`data dictionary`表上的视图实现。`INFORMATION_SCHEMA`表中有两种类型的元数据:
* **静态表元数据**:`TABLE_SCHEMA`、`TABLE_NAME`、`TABLE_TYPE`和`ENGINE`。这些统计数据将直接从`data dictionary`中读取。
* **动态表元数据**:`AUTO_INCREMENT`、`AVG_ROW_LENGTH`、`DATA_FREE`。动态元数据经常变化(例如,`AUTO_INCREMENT`值会在每次`INSERT`之后提前)。在许多情况下,动态元数据也会带来一些按需准确计算的成本,而准确度可能对典型查询不利。考虑到一个表中的空闲字节数,一个缓存值通常是足够的。
在 MySQL 8.0 中,动态表元数据将默认为缓存。这可以通过`information_schema_stats`设置(默认缓存)进行配置,并且可以更改为`SET @@GLOBAL.information_schema_stats='LATEST'`,以便始终直接从存储引擎检索动态信息(以略高的查询执行为代价)。
或者,用户也可以在表上执行`ANALYZE TABLE`,以更新缓存的动态统计数据。
大多数表都有`TABLE_SCHEMA`列(表示数据库名称)和`TABLE_NAME`列(表示表名称)。
参见[https://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/](https://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/) 了解更多详情。
# 怎么做。。。
检查所有表的列表:
mysql> USE INFORMATION_SCHEMA; mysql> SHOW TABLES;
# 桌子
`TABLES`表包含关于该表的所有信息,例如哪个数据库属于`TABLE_SCHEMA`、行数(`TABLE_ROWS`)、`ENGINE`、`DATA_LENGTH`、`INDEX_LENGTH`、`DATA_FREE`:
mysql> DESC INFORMATION_SCHEMA.TABLES; +-----------------+--------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ | TABLE_CATALOG | varchar(64) | NO | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | NULL | | | TABLE_NAME | varchar(64) | NO | | NULL | | | TABLE_TYPE | enum('BASE TABLE','VIEW','SYSTEM VIEW') | NO | | NULL | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | int(2) | YES | | NULL | | | ROW_FORMAT | enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') | YES | | NULL | | | TABLE_ROWS | bigint(20) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(20) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(20) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(20) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(20) unsigned | YES | | NULL | | | DATA_FREE | bigint(20) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(20) unsigned | YES | | NULL | | | CREATE_TIME | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | UPDATE_TIME | timestamp | YES | | NULL | | | CHECK_TIME | timestamp | YES | | NULL | | | TABLE_COLLATION | varchar(64) | YES | | NULL | | | CHECKSUM | bigint(20) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(256) | YES | | NULL | | | TABLE_COMMENT | varchar(256) | YES | | NULL | | +-----------------+--------------------------------------------------------------------+------+-----+-------------------+-----------------------------+ 21 rows in set (0.00 sec)
例如,您想知道`employees`数据库中的`DATA_LENGTH`、`INDEX_LENGTH`和`DATE_FREE`:
mysql> SELECT SUM(DATA_LENGTH)/1024/1024 AS DATA_SIZE_MB, SUM(INDEX_LENGTH)/1024/1024 AS INDEX_SIZE_MB, SUM(DATA_FREE)/1024/1024 AS DATA_FREE_MB FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='employees'; +--------------+---------------+--------------+ | DATA_SIZE_MB | INDEX_SIZE_MB | DATA_FREE_MB | +--------------+---------------+--------------+ | 17.39062500 | 14.62500000 | 11.00000000 | +--------------+---------------+--------------+ 1 row in set (0.01 sec)
# 柱
此表列出了每个表的所有列及其定义:
mysql> SELECT * FROM COLUMNS WHERE TABLE_NAME='employees'\G
# 文件夹
您已经看到,MySQL 将`InnoDB`数据存储在`data directory`目录中的`.ibd`文件中(与数据库名称同名)。要获得更多关于文件的信息,您可以查询`FILES`表:
mysql> SELECT * FROM FILES WHERE FILE_NAME LIKE './employees/employees.ibd'\G
EXTENT_SIZE: 1048576
AUTOEXTEND_SIZE: 4194304
DATA_FREE: 13631488
您应该对`DATA_FREE`感兴趣,它表示未分配的段加上段内由于碎片而空闲的数据。重建表时,可以释放`DATA_FREE`中显示的字节。
# INNODB_SYS_ 表空间
`INNODB_TABLESPACES`表中还提供了文件的大小:
mysql> SELECT * FROM INNODB_TABLESPACES WHERE NAME='employees/employees'\G 1. row SPACE: 118 NAME: employees/employees FLAG: 16417 ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 32505856 ALLOCATED_SIZE: 32509952 1 row in set (0.00 sec)
您可以在文件系统中验证这一点:
shell> sudo ls -ltr /var/lib/mysql/employees/employees.ibd -rw-r----- 1 mysql mysql 32505856 Jun 20 16:50 /var/lib/mysql/employees/employees.ibd
# INNODB_ 表状态
索引的大小和大致行数见`INNODB_TABLESTATS`表:
mysql> SELECT * FROM INNODB_TABLESTATS WHERE NAME='employees/employees'\G 1. row TABLE_ID: 128 NAME: employees/employees STATS_INITIALIZED: Initialized NUM_ROWS: 299468 CLUST_INDEX_SIZE: 1057 OTHER_INDEX_SIZE: 545 MODIFIED_COUNTER: 0 AUTOINC: 0 REF_COUNT: 1 1 row in set (0.00 sec)
# 进程列表
最常用的视图之一是进程列表。它列出了服务器上运行的所有查询:
mysql> SELECT * FROM PROCESSLIST\G 1. row ID: 85 USER: event_scheduler HOST: localhost DB: NULL COMMAND: Daemon TIME: 44 STATE: Waiting for next activation INFO: NULL 2. row ID: 26231 USER: root HOST: localhost DB: information_schema COMMAND: Query TIME: 0 STATE: executing INFO: SELECT * FROM PROCESSLIST 2 rows in set (0.00 sec
或者您可以执行`SHOW PROCESSLIST;`以获得相同的输出。
**其他表**:`ROUTINES`包含函数和存储例程的定义。`TRIGGERS`包含触发器的定义。`VIEWS`包含视图的定义。
# 另见
欲了解`INFORMATION_SCHEMA`中的改进,请参考[http://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/](http://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/) 。