【MySQL】查询数据 笔记

基本查询

要查询数据库表的数据，我们可以使用如下的语句

SELECT * FROM <表名>

假设表名是students，要查询students表的所有行，我们用如下SQL语句：

SELECT * FROM students;

在以上的语句中，SELECT是关键字，表示将要执行一个查询，* 表示“所有列”，FROM表示将要从哪个表查询。

SELECT语句其实并不要求一定要有FROM子句，如：

SELECT 100+200;

我们可以从这个语句中直接计算出表达式的结果。

条件查询

条件查询是通过使用WHERE子句来过滤查询结果的查询方式。
在条件查询中，你可以指定一组条件，只有符合这些条件的行才会被包含在查询结果中。

条件查询的语法是：

SELECT * FROM <表名> WHERE <条件表达式>

例如，要指定条件“分数在80分或以上的学生”，我们用如下SQL语句：

SELECT * FROM students WHERE score >= 80

其中，WHERE关键字后面的score >= 80就是条件。score是列名，该列存储了学生的成绩。

条件查询可用于组合多个条件，使用逻辑运算符（如AND、OR、NOT）来构建复杂的查询条件，以满足特定的查询需求。

条件表达式可以用
① <条件1> AND <条件2> 表达满足条件1并且满足条件2。

例如，我们要把符合条件“分数在80分或以上”，并且还符合条件“男生”写出来，可以用以下的语句：

SELECT * FROM students WHERE score >= 80 AND gender = 'M';

②<条件1> OR <条件2>，表示满足条件1或者满足条件2。
例如，我们要把符合条件分数在80分或以上”或者“男生”写出来，可以用以下的语句：

SELECT * FROM students WHERE score >= 80 OR gender = 'M';

③NOT <条件>，表示“不符合该条件”的记录。

例如，“不是2班的学生”这个条件，语句可以这样写：

SELECT * FROM students WHERE NOT class_id = 2;

④要组合三个或者更多的条件，就需要用小括号()表示如何进行条件运算。
例如，编写一个复杂的条件：分数在80以下或者90以上，并且是男生：

SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';

常用的条件表达式

投影查询

投影查询是数据库查询的一种类型，它允许你选择你想从数据库表中获取的特定列，而不是选择所有列。这种查询类型在你只关心表中的某些信息，而不是所有信息时非常有用。

例如：

SELECT name, email FROM Users;

这将返回一个只包含 name 和 email 列的结果集。

投影查询同样可以接WHERE条件，实现复杂的查询：

SELECT id, score points, name FROM students WHERE gender = 'M';

排序

在数据库查询中，排序是指根据某个或某些字段的值将结果集进行排序的操作。这通常通过 ORDER BY 子句来实现。

ORDER BY 子句可以根据一个或多个列对结果集进行排序。你可以选择升序（ASC）或降序（DESC）排序。ASC可以省略

例如按照成绩从低到高进行排序：

SELECT id, name, gender, score FROM students ORDER BY score;

例如，如果你有一个包含 id、name 和 age 的 Users 表，你可以按照 age 进行升序排序：

SELECT * FROM Users ORDER BY age ASC;

你也可以按照 age 进行降序排序：

SELECT * FROM Users ORDER BY age DESC;

还可以按照多个列进行排序

SELECT * FROM Users ORDER BY age ASC, name DESC;

这将返回一个首先按照 age 排序，然后在 age 相同的情况下按照 name 排序的结果集。

如果有WHERE子句，那么ORDER BY子句要放到WHERE子句后面。例如，查询一班的学生成绩，并按照倒序排序：

SELECT id, name, gender, score
FROM students
WHERE class_id = 1
ORDER BY score DESC;

分页查询

分页查询是数据库查询的一种类型，它允许你在大量数据中分批次获取一部分数据，而不是一次性获取所有数据。这种查询类型在处理大量数据时非常有用，可以提高应用程序的性能和响应速度。

在 SQL 中，分页查询可以通过 LIMIT 和 OFFSET 子句来实现。LIMIT 子句用于指定返回的记录数，OFFSET 子句用于指定开始选择记录的位置。

例如，如果你有一个包含大量用户的 Users 表，你可以使用分页查询来每次只获取 10 条记录：

SELECT * FROM Users LIMIT 10;

我们把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

如果你想获取第二页的数据（即跳过前 10 条记录），你可以使用 OFFSET 子句：

SELECT * FROM Users LIMIT 10 OFFSET 10;

如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

查询第3页的时候，OFFSET应该设定为6:

SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 6;

分页查询的关键在于，首先要确定每页需要显示的结果数量pageSize（这里是3），然后根据当前页的索引pageIndex（从1开始），确定LIMIT和OFFSET应该设定的值：

• LIMIT总是设定为pageSize；
• OFFSET计算公式为pageSize * (pageIndex - 1)。

注意
OFFSET是可选的，如果只写LIMIT 15，那么相当于LIMIT 15 OFFSET 0。

在MySQL中，LIMIT 15 OFFSET 30还可以简写成LIMIT 30, 15。

使用LIMIT <M> OFFSET <N>分页时，随着N越来越大，查询效率也会越来越低。

聚合查询

聚合查询是数据库查询的一种类型，它允许你对一组值进行统计计算，如求和、求平均值、求最大值、求最小值等。这种查询类型在你需要对数据进行统计分析时非常有用。

在 SQL 中，聚合查询可以通过聚合函数来实现，常见的聚合函数有 SUM()、AVG()、MAX()、MIN()、COUNT() 等。

例如，如果你有一个包含用户年龄的 Users 表，你可以使用聚合查询来计算所有用户的平均年龄：

SELECT AVG(age) FROM Users;

或者，你可以计算用户年龄的最大值和最小值：

SELECT MAX(age), MIN(age) FROM Users;

你还可以结合 GROUP BY 子句来对某个字段的每个值进行聚合计算。
例如，你可以计算每个城市的用户数量：

SELECT city, COUNT(*) FROM Users GROUP BY city;

这将返回一个包含每个城市及其用户数量的结果集。

COUNT(*)和COUNT(id)实际上是一样的效果。另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL：

SELECT AVG(score) average FROM students WHERE gender = 'X';

分组

在数据库查询中，分组是指根据某个或某些字段的值将结果集进行分组的操作。这通常通过 SQL 的 GROUP BY 子句来实现。

GROUP BY 子句可以根据一个或多个列对结果集进行分组。在 GROUP BY 子句后面，你通常会使用聚合函数（如 SUM()、AVG()、MAX()、MIN()、COUNT() 等）来对每个组进行计算。

例如，如果你有一个包含 id、name、age 和 city 的 Users 表，你可以按照 city 进行分组，然后计算每个城市的用户数量：

SELECT city, COUNT(*) FROM Users GROUP BY city;

或者，你也可以按照 city 进行分组，然后计算每个城市的用户平均年龄：

SELECT city, AVG(age) FROM Users GROUP BY city;

你还可以按照多个列进行分组。例如，你可以先按照 city 进行分组，然后在每个城市内部，按照 age 进行分组，最后计算每个城市每个年龄的用户数量：

SELECT city, age, COUNT(*) FROM Users GROUP BY city, age;

这将返回一个首先按照 city 分组，然后在每个城市内部按照 age 分组，最后计算每个组的用户数量的结果集。

如果我们要统计一班的学生数量，可以用SELECT COUNT(*) num FROM students WHERE class_id = 1;

多表查询

多表查询是数据库查询的一种类型，它允许你从多个表中获取数据，并根据某些条件将这些数据组合在一起。这种查询类型在你需要从多个相关表中获取数据时非常有用。

SELECT查询不但可以从一张表查询数据，还可以从多张表同时查询数据。查询多张表的语法是：SELECT * FROM <表1> <表2>。

例如，同时从students表和classes表的“乘积”，即查询数据，可以这么写：

SELECT * FROM students, classes;

注意，多表查询时，要使用表名.列名这样的方式来引用列和设置别名，这样就避免了结果集的列名重复问题。SQL还允许给表设置一个别名，让我们在投影查询中引用起来稍微简洁一点.

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c;

注意到FROM子句给表设置别名的语法是FROM <表名1> <别名1>, <表名2> <别名2>。这样我们用别名s和c分别表示students表和classes表。多表查询也是可以添加WHERE条件的:

SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

在 SQL 中，多表查询还可以通过 JOIN 子句来实现。JOIN 子句可以根据一个或多个列将两个表的行组合在一起。常见的 JOIN 类型有 INNER JOIN（内连接）、LEFT JOIN（左连接）、RIGHT JOIN（右连接）和 FULL JOIN（全连接）。

例如，如果你有一个 Users 表和一个 Orders 表，你可以使用 JOIN 子句来获取每个用户的所有订单：

SELECT Users.name, Orders.order_id
FROM Users
INNER JOIN Orders ON Users.id = Orders.user_id;

这将返回一个包含用户名称和订单 ID 的结果集。在这个查询中，INNER JOIN 子句将 Users 表和 Orders 表的行组合在一起，ON 子句指定了组合的条件（即 Users.id = Orders.user_id）。

连接查询

连接查询是数据库查询的一种类型，它允许你从两个或更多的表中获取数据，并根据某些条件将这些数据组合在一起。

连接查询对多个表进行JOIN运算，简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。

例如，如果你有一个 Users 表和一个 Orders 表，你可以使用 JOIN 子句来获取每个用户的所有订单：

SELECT Users.name, Orders.order_id
FROM Users
INNER JOIN Orders ON Users.id = Orders.user_id;

这将返回一个包含用户名称和订单 ID 的结果集。在这个查询中，INNER JOIN 子句将 Users 表和 Orders 表的行组合在一起，ON 子句指定了组合的条件（即 Users.id = Orders.user_id）。

注意INNER JOIN查询的写法是：

 1. 先确定主表，仍然使用FROM <表1>的语法；
 2. 再确定需要连接的表，使用INNER JOIN <表2>的语法；
 3. 然后确定连接条件，使用ON <条件...>，这里的条件是s.class_id =
    c.id，表示students表的class_id列与classes表的id列相同的行需要连接；
 4. 可选：加上WHERE子句、ORDER BY等子句。

常见的 JOIN 类型:

1. INNER JOIN（内连接）：返回两个表中匹配的行。如果在一个表中存在某行，但在另一个表中没有匹配的行，则该行不会出现在结果集中。

SELECT Users.name, Orders.order_id
FROM Users
INNER JOIN Orders ON Users.id = Orders.user_id;

2. LEFT JOIN（左连接）：返回左表的所有行，以及与之匹配的右表的行。如果在右表中没有匹配的行，则结果是 NULL。

SELECT Users.name, Orders.order_id
FROM Users
LEFT JOIN Orders ON Users.id = Orders.user_id;

3. RIGHT JOIN（右连接）：返回右表的所有行，以及与之匹配的左表的行。如果在左表中没有匹配的行，则结果是 NULL。

SELECT Users.name, Orders.order_id
FROM Users
RIGHT JOIN Orders ON Users.id = Orders.user_id;

4. FULL JOIN（全连接）：返回左表和右表中的所有行。如果某一侧没有匹配的行，则结果是
   NULL。需要注意的是，不是所有的数据库系统都支持 FULL JOIN。

SELECT Users.name, Orders.order_id
FROM Users
FULL JOIN Orders ON Users.id = Orders.user_id;

在这些例子中，ON 子句定义了连接条件，即 Users.id = Orders.user_id。这意味着只有当 Users 表的 id 列和 Orders 表的 user_id 列相等时，行才会被组合在一起。