25个基础的MySQL Select命令示例

举报
Tiamo_T 发表于 2021/11/25 16:04:10 2021/11/25
【摘要】 如果您正在使用 MySQL 数据库，那么熟悉 mysql 命令行是必不可少的。 在本教程中，我们将通过几个实际示例解释如何使用 MySQL select 命令。

如果您正在使用 MySQL 数据库，那么熟悉 mysql 命令行是必不可少的。

在本教程中，我们将通过几个实际示例解释如何使用 MySQL select 命令。

1. 基本选择命令示例

首先，要连接到 MySQL 命令行，请从操作系统提示符执行以下操作。

# mysql -u root -p
Password:

接下来，查看所有可用的数据库。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| thegeekstuff       |
| crm                |
| bugzilla           |
+--------------------+
8 rows in set (0.00 sec)

使用您要处理的数据库。在此示例中，我选择了“thegeekstuff”数据库，“employee”表位于该数据库中，用作本文中解释的所有选择命令的示例。

mysql> USE thegeekstuff;
Database changed

mysql> DESC employee;

select 命令的基本用法是查看表中的行。以下选择命令示例将显示“employee”表中的所有行。

mysql> SELECT * FROM employee;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Sanjay | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
| 501 | Ritu   | Accounting |   NULL |
+-----+--------+------------+--------+
6 rows in set (0.01 sec)

或者，通过指定列名来选择特定的列（而不是 * 将给出所有列）。

mysql> SELECT name, salary FROM employee;
+--------+--------+
| name   | salary |
+--------+--------+
| Thomas |   5000 |
| Jason  |   5500 |
| Sanjay |   7000 |
| Nisha  |   9500 |
| Randy  |   6000 |
| Ritu   |   NULL |
+--------+--------+
6 rows in set (0.00 sec)

2.选择双-虚拟表

双是一个虚拟表。这真的不存在。但是，您可以使用此表来执行一些非表活动。

例如，您可以在双表上使用 select 来执行如下所示的算术运算。

mysql> SELECT 2+3 FROM DUAL;
+-----+
| 2+3 |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

您还可以使用双表查看当前日期和时间。MySQL 中的 now() 函数类似于 Oracle 数据库中的 sysdate() 函数。

mysql> SELECT NOW() FROM DUAL;
+---------------------+
| now()               |
+---------------------+
| 2013-09-14 09:15:35 |
+---------------------+
1 row in set (0.00 sec)

当您不指定任何表时，MySQL 将假定您要使用双。下面的例子和上面的完全一样。为了避免混淆，我建议您在这些情况下使用“from dual”以获得更好的可读性和清晰度。

mysql> SELECT 2+3;
+-----+
| 2+3 |
+-----+
|   5 |
+-----+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| now()               |
+---------------------+
| 2013-09-14 09:16:45 |
+---------------------+
1 row in set (0.00 sec)

3. 限制记录的基本 WHERE 条件

除了显示表中的所有记录外，您还可以使用 WHERE 条件来仅查看符合特定条件的记录，如下所示。

mysql> SELECT * FROM employee WHERE salary > 6000;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 300 | Sanjay | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
+-----+--------+------------+--------+
2 rows in set (0.00 sec)

与“大于>”类似，您也可以使用“小于=”、“不等于！=”，如下所示。

mysql> SELECT * FROM employee WHERE salary < 6000; mysql> SELECT * FROM employee WHERE salary  SELECT * FROM employee WHERE salary >= 6000;

mysql> SELECT * FROM employee WHERE salary = 6000;

mysql> SELECT * FROM employee WHERE salary != 6000;

4. 匹配WHERE条件下的字符串

前面的示例显示了如何根据数字条件限制记录。此示例说明如何基于字符串值限制记录。

字符串的精确匹配类似于使用“等于 =”的数字匹配，如下所示。此示例将显示属于技术部门的所有员工。

mysql> SELECT * FROM employee WHERE dept = 'Technology';
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 200 | Jason  | Technology |   5500 |
| 300 | Sanjay | Technology |   7000 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+
3 rows in set (0.00 sec)

请注意，这是不区分大小写的比较。所以，下面和上面的select命令完全一样。

mysql> SELECT * FROM employee WHERE dept = 'TECHNOLOGY';

您也可以使用 != 显示所有不属于技术部门的员工，如下所示。

mysql> SELECT * FROM employee WHERE dept != 'TECHNOLOGY';

您还可以在关键字中使用 % 执行部分字符串匹配。以下将显示姓氏以“John”开头的所有员工。

mysql> SELECT * FROM employee WHERE name LIKE '%SMITH';

以下将显示姓名以“Smith”结尾的所有员工。

mysql> SELECT * FROM 员工 WHERE name LIKE '%SMITH';

您也可以在开头和结尾都给出 %。在这种情况下，它将在字符串中的任何位置搜索给定的关键字。以下将显示所有名称中包含“John”的员工。

mysql> SELECT * FROM employee WHERE name LIKE '%JOHN%';

5. 使用 OR、AND 组合 WHERE 条件

您还可以在 WHERE 条件中使用 OR、AND、NOT 来组合多个条件。以下示例显示所有在“技术”部门且薪水 >= 6000 的员工。仅当同时满足两个条件时才会显示记录。

mysql> SELECT * FROM employee WHERE dept = 'TECHNOLOGY' AND salary >= 6000;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 300 | Sanjay | Technology |   7000 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+
2 rows in set (0.00 sec)

以下同上，但使用 OR 条件。因此，只要任何一个条件匹配，就会显示记录。

mysql> SELECT * FROM employee WHERE dept = 'TECHNOLOGY' OR salary >= 6000;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 200 | Jason  | Technology |   5500 |
| 300 | Sanjay | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+
4 rows in set (0.00 sec)

6.在select中使用CONCAT组合列值

您可以在 select commanda 中使用 CONCAT 函数来组合来自多个列的值并显示它。下面的例子结合了姓名和部门字段（仅用于显示），如下所示。

mysql> SELECT ID, CONCAT(NAME, ' FROM ', DEPT) AS NAME, SALARY FROM employee;
+-----+------------------------+--------+
| id  | name                   | salary |
+-----+------------------------+--------+
| 100 | Thomas from Sales      |   5000 |
| 200 | Jason from Technology  |   5500 |
| 300 | Sanjay from Technology |   7000 |
| 400 | Nisha from Marketing   |   9500 |
| 500 | Randy from Technology  |   6000 |
| 501 | Ritu from Accounting   |   NULL |
+-----+------------------------+--------+
6 rows in set (0.00 sec)

7. 统计记录总数

在 select 命令中使用 count(*) 来显示表中的记录总数。

mysql> SELECT COUNT(*) FROM employee;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

8. 选择命令中的分组依据

Group By 命令将根据特定条件对记录进行分组。以下示例显示每个部门的员工总数。

mysql> SELECT DEPT, COUNT(*) FROM employee GROUP BY DEPT;
+------------+----------+
| dept       | count(*) |
+------------+----------+
| Accounting |        1 |
| Marketing  |        1 |
| Sales      |        1 |
| Technology |        3 |
+------------+----------+
4 rows in set (0.00 sec)

请注意，当您使用 GROUP BY 时，您可以使用某些函数来获得更有意义的输出。在上面的例子中，我们使用了 count(*) group by 命令。同样，当您指定 GROUP BY 时，您可以使用 sum()、avg() 等。

9. 将 HAVING 与 GROUP BY 一起使用

当您使用 GROUP BY 时，您还可以使用 HAVING 进一步限制记录。

在以下示例中，它仅显示员工人数大于 1 的部门。

mysql> SELECT COUNT(*) AS CNT, DEPT FROM employee GROUP BY DEPT HAVING CNT > 1;
+-----+------------+
| CNT | dept       |
+-----+------------+
|   3 | Technology |
+-----+------------+
1 row in set (0.00 sec)

10. 使用“AS”关键字定义别名

您可以使用 AS 关键字在显示中使用您自己的名称，而不是显示表中指定的列名。

在下面的示例中，即使真正的列名称是 ID，它也会显示为 EMPID。

mysql> SELECT ID AS EMPID, NAME AS EMPNAME, DEPT AS DEPARTMENT FROM employee;
+-------+---------+------------+
| EMPID | EMPNAME | DEPARTMENT |
+-------+---------+------------+
|   100 | Thomas  | Sales      |
|   200 | Jason   | Technology |
|   300 | Sanjay  | Technology |
|   400 | Nisha   | Marketing  |
|   500 | Randy   | Technology |
|   501 | Ritu    | Accounting |
+-------+---------+------------+
6 rows in set (0.00 sec)

请注意，AS 关键字是可选的。下面的例子和上面的完全一样。

mysql> SELECT id empid, name empname, dept department FROM employee;

11. SELECT 命令中的 Left Join

在以下示例中，select 命令组合了两个表。即员工和部门。为了组合这些，它使用这两个表之间的公共列dept。输出中显示的“位置”列来自部门表。

mysql> SELECT employee.*, department.location FROM employee LEFT JOIN department ON ( employee.dept = department.dept );
+-----+--------+------------+--------+----------+
| id  | name   | dept       | salary | Location |
+-----+--------+------------+--------+----------+
| 100 | Thomas | Sales      |   5000 | USA      |
| 200 | Jason  | Technology |   5500 | USA      |
| 300 | Sanjay | Technology |   7000 | India    |
| 400 | Nisha  | Marketing  |   9500 | India    |
| 500 | Randy  | Technology |   6000 | UK       |
| 501 | Ritu   | Accounting |   NULL | USA      |
+-----+--------+------------+--------+----------+

您还可以在 JOIN 命令中使用表别名，如下所示。在此示例中，我使用“E”作为员工表的别名，使用“D”作为部门表的别名。这使得选择命令更小且更易于阅读。

mysql> SELECT E.*, d.location FROM employee AS E LEFT JOIN Department AS D ON (e.dept = d.dept);

注意：Join 本身是一个很大的话题，我们将在单独的教程中详细讨论。

12. 使用 EXPLAIN 进行性能分析

当您的选择查询很慢，或者行为方式您不理解时，请使用 EXPLAIN 命令，该命令将显示 MySQL 在内部用于执行查询的其他详细信息。这可能会让您对 MySQL select 命令的性能有所了解。

mysql> EXPLAIN SELECT E.*, D.LOCATION FROM employee AS E LEFT JOIN DEPARTMENT AS D ON ( E.DEPT = D.DEPT );
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+
|  1 | SIMPLE      | PS    | ALL    | NULL          | NULL    | NULL    | NULL            |    6 |       |
|  1 | SIMPLE      | P     | eq_ref | PRIMARY       | PRIMARY | 3       | acme.E.dept     |    1 |       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+
2 rows in set (0.00 sec)

13.强制选择查询使用索引

在执行选择查询并连接两个表时，MySQL 将决定如何有效地使用表上的任何可用索引。以下是SELECT命令中处理索引的几种方式。

  • USE INDEX (list_of_indexes) – 这将使用指定的索引之一来查询表中的记录。
  • IGNORE INDEX (list_of_indexes) – 这将使用指定的索引来查询表中的记录。
  • FORCE INDEX (index_name) – 这将强制 MySQL 使用给定的索引，即使 MySQL 认为有更好更快的查询记录方式可用。

在您决定使用上述任何一种之前，您应该真正了解这些命令的影响，如果这些命令使用不当，它会减慢您的 select 命令的速度。

以下示例强制 MySQL 为此查询使用employee_emp_nm_idx。

mysql> SELECT * FROM employee FORCE INDEX (EMPLOYEE_EMP_NM_IDX) WHERE NAME LIKE 'JOHN%';

要显示特定表上的所有可用索引，请使用“show index”命令。以下示例显示了雇员表上所有可用的索引。

mysql> SHOW INDEX FROM PROFILES;
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| employee |          0 | PRIMARY                 |            1 | id          | A         |         156 |     NULL | NULL   |      | BTREE      |         |
| employee |          0 | employee_emp_nm_idx     |            1 | name        | A         |         156 |     NULL | NULL   |      | BTREE      |         |
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

14. 使用 ORDER BY 对记录进行排序

注意：desc 将通过降序缩短。如果你什么都不给，它就是在提升。
以下记录将根据 dept 列按字母顺序对记录进行排序。

mysql> SELECT * FROM employee ORDER BY DEPT;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 501 | Ritu   | Accounting |   NULL |
| 400 | Nisha  | Marketing  |   9500 |
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Sanjay | Technology |   7000 |
| 500 | Randy  | Technology |   6000 |
+-----+--------+------------+--------+
6 rows in set (0.01 sec)

请注意，默认情况下，它将按升序排序。如果要按降序排序，请在“ORDER BY”后指定关键字“DESC”，如下所示。

mysql> SELECT * FROM employee ORDER BY DEPT DESC;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 200 | Jason  | Technology |   5500 |
| 300 | Sanjay | Technology |   7000 |
| 500 | Randy  | Technology |   6000 |
| 100 | Thomas | Sales      |   5000 |
| 400 | Nisha  | Marketing  |   9500 |
| 501 | Ritu   | Accounting |   NULL |
+-----+--------+------------+--------+
6 rows in set (0.00 sec)

您还可以按如下所示按多列排序。

mysql> SELECT * FROM employee ORDER BY DEPT, SALARY DESC;

15.限制记录数

您可以使用 LIMIT 限制 mysql 应显示的记录数，而不是显示所有记录，如下所示。

限制格式：

LIMIT start_record, number_of_records

以下示例将从记录号 0（即第 1 条记录）开始，并从那里显示 3 条记录。

mysql> SELECT * FROM employee LIMIT 0,3;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Sanjay | Technology |   7000 |
+-----+--------+------------+--------+
3 rows in set (0.00 sec)

下面将从第 1 条记录（即第 2 条记录）开始，并从那里显示 3 条记录。

mysql> SELECT * FROM employee LIMIT 1,3;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 200 | Jason  | Technology |   5500 |
| 300 | Sanjay | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
+-----+--------+------------+--------+
3 rows in set (0.00 sec)

您也可以省略 start_record，在这种情况下，它将始终从记录号 0（即第一条记录）开始。

在以下示例中，我们仅指定了一个值。因此，这将从记录号 0 开始，并从那里显示 3 条记录。

mysql> SELECT * FROM employee LIMIT 3;
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 100 | Thomas | Sales      |   5000 |
| 200 | Jason  | Technology |   5500 |
| 300 | Sanjay | Technology |   7000 |
+-----+--------+------------+--------+
3 rows in set (0.00 sec)

16.用OFFSET限制记录数

限制偏移格式：

LIMIT number_of_records OFFSET start_record

您还可以使用关键字 OFFSET，您将在关键字 OFFSET 之后指定开始记录。

以下将显示总共 3 条记录。由于偏移量指定为 1，它将从第 2 条记录开始。

mysql> SELECT * FROM employee LIMIT 3 OFFSET 1
+-----+--------+------------+--------+
| id  | name   | dept       | salary |
+-----+--------+------------+--------+
| 200 | Jason  | Technology |   5500 |
| 300 | Sanjay | Technology |   7000 |
| 400 | Nisha  | Marketing  |   9500 |
+-----+--------+------------+--------+
3 rows in set (0.00 sec)

17.从列中获取唯一值

要显示列中的所有唯一值，请使用 DISTINCT。

以下示例将显示员工表中所有唯一的部门值。

mysql> SELECT DISTINCT DEPT FROM employee;
+------------+
| dept       |
+------------+
| Sales      |
| Technology |
| Marketing  |
| Accounting |
+------------+

18. 列中所有值的总和

要添加列中的所有值，请使用 SUM() 函数。

以下示例将显示属于技术部门的所有员工的工资列总和。

mysql> SELECT SUM(SALARY) FROM employee WHERE DEPT = 'TECHNOLOGY';
+-------------+
| sum(salary) |
+-------------+
|       18500 |
+-------------+
1 row in set (0.01 sec)

19. 列中所有值的平均值

要平均一列中的所有值，请使用 AVG() 函数。

以下示例将显示每个部门的平均工资。这结合了 GROUP BY 和 AVG() 函数。

mysql> SELECT DEPT,AVG(SALARY) FROM employee GROUP BY DEPT;
+------------+-------------+
| dept       | avg(salary) |
+------------+-------------+
| Accounting |        NULL |
| Marketing  |   9500.0000 |
| Sales      |   5000.0000 |
| Technology |   6166.6667 |
+------------+-------------+
4 rows in set (0.03 sec)

20. SELECT 命令中的 SELECT

下面显示的示例非常蹩脚。没有理由这样做。但是，这向您展示了如何使用 select 命令。在这个例子中，“AS ACTION”为选择子查询提供了一个别名。您需要在此示例中指定别名。“行动”只是一个名字。您可以将其更改为您喜欢的任何内容。

mysql> SELECT * FROM (SELECT * FROM employee) AS ACTION WHERE ID

21. 将选择的输出保存到文件

使用 SELECT INTO，您可以将选择命令的输出保存到文件中。

下面的 select 命令示例将把 select 命令的输出存储到 /tmp/employee.txt 文件中，而不是在屏幕上显示输出。

mysql> SELECT * INTO OUTFILE '/tmp/employee.txt' FROM employee;
Query OK, 6 rows affected (0.00 sec)

# cat /tmp/employee.txt
100     Thomas  Sales   5000
200     Jason   Technology      5500
300     Sanjay  Technology      7000
400     Nisha   Marketing       9500
500     Randy   Technology      6000
501     Ritu    Accounting      \N

您还可以通过指定“FIELDS TERMINATED BY”将输出存储到逗号分隔的文件中，如下例所示。

mysql> SELECT * INTO OUTFILE '/tmp/employee1.txt'   FIELDS TERMINATED BY ',' FROM employee;
Query OK, 6 rows affected (0.00 sec)

# cat /tmp/employee1.txt
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Sanjay,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,\N

22. 在数据集上执行一个过程

您还可以调用一个 MySQL 过程，该过程将处理来自 select 命令输出的数据。

下面的示例将对给定的 select 命令的输出执行过程salary_report()。

mysql> SELECT ID, SALARY FROM employee PROCEDURE SALARY_REPORT();

23. 显示表格中的随机记录

使用 rand 命令可以显示表中的随机记录。这在类似于您从表格中随机显示当天的一些提示的情况下会很有帮助。

mysql> SELECT * FROM employee ORDER BY RAND() LIMIT 1;
+-----+-------+------------+--------+
| id  | name  | dept       | salary |
+-----+-------+------------+--------+
| 200 | Jason | Technology |   5500 |
+-----+-------+------------+--------+
1 row in set (0.00 sec)

下次执行相同的命令，将给出不同的记录，如下所示。

mysql> SELECT * FROM employee ORDER BY RAND() LIMIT 1;
+-----+--------+-------+--------+
| id  | name   | dept  | salary |
+-----+--------+-------+--------+
| 100 | Thomas | Sales |   5000 |
+-----+--------+-------+--------+
1 row in set (0.00 sec)

您还可以使用 now() 函数将当前日期和时间作为 salt 传递给 rand 命令，如下所示。

mysql> SELECT * FROM employee ORDER BY RAND(NOW()) LIMIT 1;
+-----+-------+-----------+--------+
| id  | name  | dept      | salary |
+-----+-------+-----------+--------+
| 400 | Nisha | Marketing |   9500 |
+-----+-------+-----------+--------+
1 row in set (0.00 sec)

24. 高优先级选择命令

当您在 select 语句中使用 high_priority 关键字时，它将为特定的 select 语句提供比对表进行任何更新更高的优先级。

使用此命令时要非常小心，因为您可能会减慢其他更新。仅在需要快速获取记录的情况下使用此选项。在执行之前，还要确保您提供的 select 命令本身得到了很好的优化。

mysql> SELECT HIGH_PRIORITY * FROM 员工 WHERE ID = 100;

25. 一致读入选择命令

如果你想要一致的阅读。即当您从表中选择行时，如果您不希望任何其他进程修改这些值，则需要在读取记录时启用共享锁定模式。

如果您不了解这些工作方式的影响，如果您尝试在大桌子上使用这些，您可能会陷入困境。

以下命令将不允许其他 MySQL 会话修改此 select 语句查询的记录，直到它读取所有这些记录。

mysql> SELECT * FROM employee WHERE ID = 100 LOCK IN SHARE MODE;

请注意，您还可以执行如下所示的“FOR UPDATE”，这将阻止其他会话执行“SELECT ... LOCK in SHARE MODE”，直到此事务结束。

mysql> SELECT * FROM 员工 WHERE ID = 100 FOR UPDATE;
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入