SQL 基础知识 |选择语句 |数据库管理-mysql教程-PHP中文网

介绍

SQL Fundamentals | SELECT Statement | Database Management

SELECT 语句是 SQL 中最常用的语句之一，用于选择表中的数据。本实验室将学习 SELECT 以及如何将其应用到实际实践中。

学习目标

SELECT 基本语法
数学符号条件
和或
通配符
排序
SQL内置函数和计算
子查询和连接查询

准备

开始之前，先下载相关数据表，并创建一个名为mysql_labex的数据库（3个表：部门、员工、项目）。

启动 MySQL 服务并以 root 身份登录。

cd ~/project
sudo service mysql start
mysql -u root

登录后复制

有两个文件create-database.sql和insert-data.sql，位于~/project/。

加载文件中的数据。需要在MySQL控制台输入命令来构建数据库：

source ~/project/create-database.sql
source ~/project/insert-data.sql

登录后复制

在数据库操作语句中，最常用、也算最重要的就是SELECT查询。在之前的实验中，我们使用了 SELECT * FROM table_name;在很多地方都可以看到表中的所有内容。 SELECT 可以与具有多种约束的关键字一起使用，这些约束包含多种功能。本实验室将详细介绍这些用途。

基本 SELECT 语句

SELECT语句的基本格式：

SELECT row name  FROM table name WHERE constraint;

登录后复制

如果要查询表的所有内容，则查询列名带星号*，代表将查询表中的所有列。大多数情况下，我们只需要查看一个表的指定列，比如查看员工表的姓名和年龄：

USE mysql_labex;
SELECT name,age FROM employee;

登录后复制

MariaDB [mysql_labex]> SELECT name,age FROM employee;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Jack |   24 |
| Rose |   22 |
| Jim  |   35 |
| Mary |   21 |
| Alex |   26 |
| Ken  |   27 |
| Rick |   24 |
| Joe  |   31 |
| Mike |   23 |
| Jobs | NULL |
| Tony | NULL |
+------+------+
12 rows in set (0.000 sec)

登录后复制

数学符号条件

SELECT 语句通常带有 WHERE 约束，用于实现更准确的查询。 WHERE 约束可以具有数学符号（=、、>=、

SELECT name,age FROM employee WHERE age>25;

登录后复制

筛选年龄超过 25 岁的结果：

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age>25;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Jim  |   35 |
| Alex |   26 |
| Ken  |   27 |
| Joe  |   31 |
+------+------+
5 rows in set (0.000 sec)

登录后复制

或者查找名为 Mary 的员工的姓名、年龄和电话：

SELECT name,age,phone FROM employee WHERE name='Mary';

登录后复制

结果：

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE name='Mary';
+------+------+--------+
| name | age  | phone  |
+------+------+--------+
| Mary |   21 | 100101 |
+------+------+--------+
1 row in set (0.000 sec)

登录后复制

“与”和“或”

WHERE之后可以有多个约束，根据这些条件的逻辑关系，我们可以使用OR和AND来连接：

筛选 - 年龄小于 25 岁，或年龄大于 30

SELECT name,age FROM employee WHERE age<25 OR age>30;

登录后复制

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age<25 OR age>30;
+------+------+
| name | age  |
+------+------+
| Jack |   24 |
| Rose |   22 |
| Jim  |   35 |
| Mary |   21 |
| Rick |   24 |
| Joe  |   31 |
| Mike |   23 |
+------+------+
7 rows in set (0.000 sec)

登录后复制

筛选 - 年龄大于 25 岁，年龄小于 30

SELECT name,age FROM employee WHERE age>25 AND age<30;

登录后复制

如果我们需要包括 25 岁和 30 岁，请使用 年龄在 25 岁到 30 岁之间 :

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age>25 AND age<30;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Alex |   26 |
| Ken  |   27 |
+------+------+
3 rows in set (0.000 sec)

MariaDB [mysql_labex]> SELECT name,age FROM employee WHERE age BETWEEN 25 AND 30;
+------+------+
| name | age  |
+------+------+
| Tom  |   26 |
| Alex |   26 |
| Ken  |   27 |
+------+------+
3 rows in set (0.000 sec)

登录后复制

进入与不进入

关键字IN和NOT IN用于过滤一定范围内的结果。例如，我们想要查找 dpt3 或 dpt4 中的人：

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');

登录后复制

对于 NOT IN，例如在以下命令中，我们将获取不在 dpt1 或 dpt3 中的人员：

SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');

登录后复制

MariaDB [mysql_labex]> SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
+------+------+--------+--------+
| name | age  | phone  | in_dpt |
+------+------+--------+--------+
| Tom  |   26 | 119119 | dpt4   |
| Rose |   22 | 114114 | dpt3   |
| Rick |   24 | 987654 | dpt3   |
| Mike |   23 | 110110 | dpt4   |
| Tony | NULL | 102938 | dpt3   |
+------+------+--------+--------+
5 rows in set (0.000 sec)

MariaDB [mysql_labex]> SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');
+------+------+--------+--------+
| name | age  | phone  | in_dpt |
+------+------+--------+--------+
| Tom  |   26 | 119119 | dpt4   |
| Jack |   24 | 120120 | dpt2   |
| Mary |   21 | 100101 | dpt2   |
| Joe  |   31 | 110129 | dpt2   |
| Mike |   23 | 110110 | dpt4   |
| Jobs | NULL |  19283 | dpt2   |
+------+------+--------+--------+
6 rows in set (0.000 sec)

登录后复制

通配符

关键字LIKE在SQL语句中与通配符一起使用，通配符代表未知字符。 SQL 中的通配符是 _ 和 %。其中_代表未指定字符，%代表不定未指定字符。

例如，如果您只记得电话号码的前四位是1101，而忘记了后两位，则可以用两个_通配符代替：

SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';

登录后复制

这里有以 1101 开头的电话号码：

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
+------+------+--------+
| name | age  | phone  |
+------+------+--------+
| Joe  |   31 | 110129 |
| Mike |   23 | 110110 |
+------+------+--------+
2 rows in set (0.000 sec)

登录后复制

还有一种情况，比如你只记得名字的第一个字母，并且不知道名字的长度，那就用%通配符代替不定字符：

SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

登录后复制

这里有以J开头的名字：

MariaDB [mysql_labex]> SELECT name,age,phone FROM employee WHERE name LIKE 'J%';
+------+------+--------+
| name | age  | phone  |
+------+------+--------+
| Jack |   24 | 120120 |
| Jim  |   35 | 100861 |
| Joe  |   31 | 110129 |
| Jobs | NULL |  19283 |
+------+------+--------+
4 rows in set (0.000 sec)

登录后复制

对结果进行排序

为了使查询结果更有条理、更容易理解，我们可能需要按照一定的规则对它们进行排序。 ORDER BY 派上用场。默认情况下，ORDER BY是按升序排列的，通过使用ASC和DESC，我们也可以得到升序和降序的结果订购。

比如我们对工资进行降序排序，SQL语句：

SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;

登录后复制

MariaDB [mysql_labex]> SELECT name,age,salary,phone FROM employee ORDER BY salary DESC;
+------+------+--------+--------+
| name | age  | salary | phone  |
+------+------+--------+--------+
| Jobs | NULL |   3600 |  19283 |
| Joe  |   31 |   3600 | 110129 |
| Ken  |   27 |   3500 | 654321 |
| Rick |   24 |   3500 | 987654 |
| Mike |   23 |   3400 | 110110 |
| Tony | NULL |   3400 | 102938 |
| Alex |   26 |   3000 | 123456 |
| Mary |   21 |   3000 | 100101 |
| Jim  |   35 |   3000 | 100861 |
| Rose |   22 |   2800 | 114114 |
| Jack |   24 |   2500 | 120120 |
| Tom  |   26 |   2500 | 119119 |
+------+------+--------+--------+
12 rows in set (0.000 sec)

登录后复制

SQL内置函数和计算

SQL允许对表中的数据进行计算。在这方面，SQL 有五个内置函数来执行 SELECT 的结果：

Function:	COUNT	SUM	AVG	MAX	MIN
For:	count numbers	sum up	average	maximum value	minimum value

The COUNT function can be used for any data type (because it is only a count), while SUM and AVG functions can only calculate numeric data types. MAX and MIN can be used for numeric, string, or datetime data types.

For example, when we want to calculate the maximum and minimum value of salary, we use a statement like this:

SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;

登录后复制

You may have noticed a tiny detail. Use AS keyword can rename value. E.g. Max value is renamed into max_salary:

MariaDB [mysql_labex]> SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
+------------+-------------+
| max_salary | MIN(salary) |
+------------+-------------+
|       3600 |        2500 |
+------------+-------------+
1 row in set (0.000 sec)

登录后复制

Subqueries

The SELECT statements discussed above all involve data in only one table, but sometimes you have to process multiple tables to get the information you need. For example, you want to know a few projects done by the department where the employee named "Tom" is located. Employee information is stored in the employee table, but the project information is stored in the project table.

We can use subqueries to deal with such situations:

SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

登录后复制

MariaDB [mysql_labex]> SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
    -> WHERE of_dpt IN
    -> (SELECT in_dpt FROM employee WHERE name='Tom');
+--------+---------------+
| of_dpt | count_project |
+--------+---------------+
| dpt4   |             2 |
+--------+---------------+
1 row in set (0.000 sec)

登录后复制

Subqueries can also be extended to three, four or more layers.

Join

When dealing with multiple tables, the subquery is only useful when the results are from the same table. However, if you need to display data in two or more tables, you must use the join operation.

The basic idea is to connect two or more tables as a new table to operate, as follows:

SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

登录后复制

This result is the number of employees in each department, where employee id and name from the employee table, people_num from the department table:

MariaDB [mysql_labex]> SELECT id,name,people_num
    -> FROM employee,department
    -> WHERE employee.in_dpt = department.dpt_name
    -> ORDER BY id;
+----+------+------------+
| id | name | people_num |
+----+------+------------+
|  1 | Tom  |         15 |
|  2 | Jack |         12 |
|  3 | Rose |         10 |
|  4 | Jim  |         11 |
|  5 | Mary |         12 |
|  6 | Alex |         11 |
|  7 | Ken  |         11 |
|  8 | Rick |         10 |
|  9 | Joe  |         12 |
| 10 | Mike |         15 |
| 11 | Jobs |         12 |
| 12 | Tony |         10 |
+----+------+------------+
12 rows in set (0.000 sec)

登录后复制

Another connection statement format is to use the JOIN ON syntax. The statement is the same as:

SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;

登录后复制

Result is the same.

Summary

In this lab we learned the basic use of SELECT statement:

Basic syntax
Mathmatical symbol conditions
AND OR IN
Wildcards
Sort
SQL built-in functions and calculations
Sunqueries and connection queries

? Practice Now: SQL's SELECT Statement

Want to Learn More?

? Learn the latest MySQL Skill Trees
? Read More MySQL Tutorials
? Join our Discord or tweet us @WeAreLabEx

以上是SQL 基础知识 |选择语句 |数据库管理的详细内容。更多信息请关注PHP中文网其他相关文章！