MySQL 高阶语句

最新推荐文章于 2024-10-02 06:14:43 发布

pokemon..

最新推荐文章于 2024-10-02 06:14:43 发布

阅读量1k

点赞数 30

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Jin_0612_/article/details/141866506

版权

在 MySQL 数据库的查询中，除了基本的查询操作外，有时候我们需要对查询的结果集进行处理，比如只取前几条数据、对结果进行排序或分组等。本文将介绍一些常用的高级查询操作，包括排序、筛选、分组、限制结果条目以及设置别名。

一、常用查询语句

假设我们有一个名为 ky37 的表，记录了学生的 id、姓名、分数、地址和爱好：

CREATE TABLE ky37 (
    id INT PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    score DECIMAL(5,2),
    address VARCHAR(20),
    hobbid INT(5)
);

INSERT INTO ky37 VALUES (1, 'xiewei', 80, 'beijing', 2);
INSERT INTO ky37 VALUES (2, 'wjh', 98, 'shengzheng', 2);
INSERT INTO ky37 VALUES (3, 'cxc', 60, 'shanghai', 4);
INSERT INTO ky37 VALUES (4, 'jhw', 99, 'hangzhou', 5);
INSERT INTO ky37 VALUES (5, 'yss', 98, 'laowo', 3);
INSERT INTO ky37 VALUES (6, 'xuyekang', 69, 'nanjing', 3);
INSERT INTO ky37 VALUES (7, 'jqg', 50, 'nanjing', 5);

+----+----------+-------+----------+--------+
| id | name     | score | address  | hobbid |
+----+----------+-------+----------+--------+
|  1 | xiewei   | 80.00 | beijing  |      2 |
|  2 | wjh      | 98.00 | shenzhen |      2 |
|  3 | cxc      | 60.00 | shanghai |      4 |
|  4 | jhw      | 99.00 | hangzhou |      5 |
|  5 | yss      | 98.00 | laowo    |      3 |
|  6 | xuyekang | 69.00 | nanjing  |      3 |
|  7 | jqg      | 50.00 | nanjing  |      5 |
+----+----------+-------+----------+--------+

1. 按关键字排序

使用 ORDER BY 语句可以对查询结果进行排序。排序可以针对一个或多个字段进行，排序方式可以是升序（默认）或降序。

语法：

SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

ASC 是升序（默认排序方式，可以省略）。
DESC 是降序排序。

按分数升序排序：

SELECT id, name, score FROM ky37 ORDER BY score;

按分数降序排序：

SELECT id, name, score FROM ky37 ORDER BY score DESC;

结合 WHERE 语句进行条件过滤：

SELECT name, score FROM ky37 WHERE address = 'hangzhou' ORDER BY score DESC;

按多个字段排序：

SELECT id, name, hobbid FROM ky37 ORDER BY hobbid DESC, id DESC;

SELECT id, name, hobbid FROM ky37 ORDER BY hobbid DESC, id;

2. 区间判断及查询不重复记录

使用 AND 和 OR 进行条件查询：

AND：前后条件都要满足
OR：满足其中一个条件

SELECT * FROM ky37 WHERE score > 70 AND score <= 90;

SELECT * FROM ky37 WHERE score > 70 OR score <= 90;

使用嵌套条件：

SELECT * FROM ky37 WHERE score > 70 OR (score > 75 AND score < 90);

查询不重复记录：

SELECT DISTINCT hobbid FROM ky37;

3. 对结果进行分组

使用 GROUP BY 语句可以对查询结果进行分组，并结合聚合函数（如 COUNT、SUM、AVG、MAX、MIN）进行统计分析。

语法：

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

示例：

按 hobbid 分组，计算每组的学生人数：

SELECT COUNT(name), hobbid FROM ky37 GROUP BY hobbid;

结合 WHERE 语句进行筛选：

SELECT COUNT(name), hobbid FROM ky37 WHERE score >= 80 GROUP BY hobbid;

按学生人数排序：

SELECT COUNT(name), score, hobbid FROM ky37 WHERE score >= 80 GROUP BY hobbid ORDER BY COUNT(name) ASC;

4. 限制结果条目

使用 LIMIT 语句可以限制查询结果的返回行数。

语法：

SELECT column1, column2, ... FROM table_name LIMIT [offset,] number;

offset 是位置偏移量（可选）。
number 是返回的记录行数。

示例：

显示前 4 行记录：

SELECT * FROM ky37 LIMIT 4;

从第 4 行开始，显示 3 行记录：

SELECT * FROM ky37 LIMIT 3, 3;

按 id 升序排列并显示前三行记录：

SELECT id, name FROM ky37 ORDER BY id LIMIT 3;

显示最后三行记录：

SELECT id, name FROM ky37 ORDER BY id DESC LIMIT 3;

5. 设置别名（alias）

在 MySQL 查询时，可以使用 AS 关键字为列或表设置别名，以提高查询的可读性和简洁性。

语法：

列别名：SELECT column_name AS alias_name FROM table_name;
表别名：SELECT column_name(s) FROM table_name AS alias_name;

示例：

列别名：

SELECT name AS 姓名, score AS 成绩 FROM ky37;

表别名：

SELECT i.name AS 姓名, i.score AS 成绩 FROM ky37 AS i;

查询表中的记录数并设置别名：

SELECT COUNT(*) AS number FROM ky37;

克隆表结构：

CREATE TABLE t1 AS SELECT * FROM ky37;

创建条件表：

CREATE TABLE test1 AS SELECT * FROM ky37 WHERE score >= 60;

注意事项：

别名不可以与数据库中其他表的名称冲突。
列别名会在结果中显示，而表别名仅在查询中使用，不会显示在结果中。

6. 通配符

(1)查询名字以 x 开头的记录

使用 % 通配符可以匹配以特定字符开头的记录。

SELECT id, name FROM ky37 WHERE name LIKE 'x%';

+----+----------+
| id | name     |
+----+----------+
|  1 | xiewei   |
|  6 | xuyekang |
+----+----------+

(2)查询名字中 j 和 w 之间有一个字符的记录

使用 _ 通配符可以匹配一个特定字符。

SELECT id, name FROM ky37 WHERE name LIKE 'j_w';

+----+------+
| id | name |
+----+------+
|  4 | jhw  |
+----+------+

(3)查询名字中包含 e 的记录

使用 % 通配符可以匹配包含特定字符的记录。

SELECT id, name FROM ky37 WHERE name LIKE '%e%';

+----+----------+
| id | name     |
+----+----------+
|  1 | xiewei   |
|  6 | xuyekang |
+----+----------+

(4)查询 j 后面有两个字符的名字记录

使用 _ 通配符可以匹配固定长度的字符。

SELECT id, name FROM ky37 WHERE name LIKE 'j__';

+----+------+
| id | name |
+----+------+
|  4 | jhw  |
|  7 | jqg  |
+----+------+

7. 子查询

子查询（也称为内查询或嵌套查询）是 SQL 中一个强大的功能，它允许在一个查询语句中嵌套另一个查询语句。子查询先于主查询执行，其结果集用于主查询的条件判断。子查询可以用在 SELECT、INSERT、UPDATE 和 DELETE 语句中。下面我们将详细讲解子查询的用法及其应用场景。

子查询基础

子查询的基本语法结构如下：

<主查询语句> WHERE <条件> IN (SELECT <字段> FROM <子查询表> WHERE <条件>);

在这个结构中，SELECT 子查询首先被执行，然后其结果用于主查询的条件判断。

子查询与 IN 关键字

IN 用于判断某个值是否在给定的结果集中。子查询的结果集可以用于 IN 关键字的条件中。

示例：

SELECT name, score FROM ky37 WHERE id IN (SELECT id FROM ky37 WHERE score > 80);

+------+-------+
| name | score |
+------+-------+
| wjh  | 98.00 |
| jhw  | 99.00 |
| yss  | 98.00 |
+------+-------+

子查询与不同表

子查询不仅可以用于同一张表，还可以与不同的表进行关联查询。

示例：

创建另一个表

CREATE TABLE ky30 (id INT);
INSERT INTO ky30 VALUES (1), (2), (3);

多表查询：

SELECT id, name, score FROM ky37 WHERE id IN (SELECT id FROM ky30);

子查询在 INSERT、UPDATE 和 DELETE 中的应用

错误发生的原因可能是：

列数不匹配：ky30 表的列数与 ky37 表的列数不一致。如果 ky37 表中的列数多于或少于 ky30 表的列数，MySQL 会抛出这个错误。
列的顺序不匹配：即使列数相同，如果列的顺序不匹配（例如类型不一致），也可能导致错误。

1.INSERT 语句：

INSERT INTO kyky SELECT * FROM ky37 WHERE id IN (SELECT id FROM ky37);

2. UPDATE 语句：

UPDATE ky37 SET score = 50 WHERE id IN (SELECT id FROM ky30 WHERE id = 2);

3. DELETE 语句：

DELETE FROM ky37 WHERE id IN (SELECT id FROM ky37 WHERE score > 80);

使用 NOT IN 可以表示否定条件：

DELETE FROM kyky WHERE id NOT IN (SELECT id FROM ky37 WHERE score >= 80);

EXISTS 关键字

EXISTS 用于判断子查询的结果集是否为空。如果子查询返回非空结果，则主查询返回 TRUE。

示例：

查询 ky37 表中是否存在分数为 80 的记录：

SELECT COUNT(*) FROM ky37 WHERE EXISTS (SELECT id FROM ky37 WHERE score = 80);

使用子查询时的别名

在进行子查询时，给子查询结果集设置别名是很重要的。否则，MySQL 会报错提示需要别名。

示例：

查询 ky37 表中的 id 和 name 字段：

SELECT id, name FROM ky37;

将结果集作为一张表进行查询时需要使用别名：

SELECT a.id FROM (SELECT id, name FROM ky37) a;

8. MySQL视图

视图是数据库中的虚拟表，它不保存实际数据，而是从一个或多个基础表中动态生成数据。可以将视图理解为数据表的“镜像”，即对基础表数据的映射。视图的作用主要有：

简化查询：视图可以简化复杂的查询操作。
安全性：可以控制用户对数据的访问，只展示他们有权限查看的数据。
灵活性：视图可以展示不同用户不同的结果集。

视图与表的区别和联系：

区别：
1. 编译：视图是编译好的 SQL 语句，而表则不是。
2. 存储：视图没有实际的物理存储，而表有。
3. 修改：表可以直接修改，而视图只能通过创建和删除语句进行修改。
4. 使用：视图用于数据的展示和查询，表用于实际的数据存储。
联系：视图是建立在基础表之上的，其结构和内容都来自基础表。一个视图可以对应一个或多个基础表，是对基础表的逻辑抽象。

示例：

1. 创建视图（单表）：

CREATE VIEW v_score AS 
SELECT * FROM ky37 WHERE score >= 80;

查看视图数据：

SELECT * FROM v_score;

+----+--------+-------+----------+--------+
| id | name   | score | address  | hobbid |
+----+--------+-------+----------+--------+
|  1 | xiewei | 80.00 | beijing  |      2 |
|  2 | wjh    | 98.00 | shenzhen |      2 |
|  4 | jhw    | 99.00 | hangzhou |      5 |
|  5 | yss    | 98.00 | laowo    |      3 |
+----+--------+-------+----------+--------+

2. 创建多表视图：

创建一个test01表

+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | xiewei | 25   |
|    2 | wjh    | 22   |
|    3 | cxc    | 21   |
+------+--------+------+

创建多表视图：

CREATE VIEW v_info (id, name, score, age) AS 
SELECT ky37.id, ky37.name, ky37.score, test01.age 
FROM ky37 
JOIN test01 ON ky37.name = test01.name;

查看v_info

+----+--------+-------+------+
| id | name   | score | age  |
+----+--------+-------+------+
|  1 | xiewei | 80.00 | 25   |
|  2 | wjh    | 98.00 | 22   |
|  3 | cxc    | 60.00 | 21   |
+----+--------+-------+------+
3 rows in set (0.00 sec)

3.修改视图数据：

update ky37 set score=60 where name='yss';

修改原表内容后，判断语句不符合，视图表该内容消失

select * from v_score;

+----+--------+-------+----------+--------+
| id | name   | score | address  | hobbid |
+----+--------+-------+----------+--------+
|  1 | xiewei | 80.00 | beijing  |      2 |
|  2 | wjh    | 98.00 | shenzhen |      2 |
|  4 | jhw    | 99.00 | hangzhou |      5 |
+----+--------+-------+----------+--------+

（通过视图修改原表）

update v_score set score=80 where name='jhw';

查看原表内容：

mysql> select * from ky37;

+----+----------+-------+----------+--------+
| id | name     | score | address  | hobbid |
+----+----------+-------+----------+--------+
|  1 | xiewei   | 80.00 | beijing  |      2 |
|  2 | wjh      | 98.00 | shenzhen |      2 |
|  3 | cxc      | 60.00 | shanghai |      4 |
|  4 | jhw      | 80.00 | hangzhou |      5 |
|  5 | yss      | 60.00 | laowo    |      3 |
|  6 | xuyekang | 69.00 | nanjing  |      3 |
|  7 | jqg      | 50.00 | nanjing  |      5 |
+----+----------+-------+----------+--------+
7 rows in set (0.00 sec)

9. NULL 值

---- NULL 值的基本概念

在数据库表中，NULL 表示某个字段没有值，即值未知或缺失。如果在创建表时，没有指定字段为 NOT NULL，则该字段默认可以为空，即可以存储 NULL 值。

需要注意的是，NULL 值与数字 0 或空字符串（''）不同。NULL 表示“没有值”，而 0 和空字符串则是实际存在的值。

在 SQL 中，可以使用 IS NULL 来判断某个字段是否为 NULL，使用 IS NOT NULL 判断某个字段是否不是 NULL。

---- NULL 与空值的区别

空值：长度为 0，不占用存储空间。
NULL 值：无具体长度，占用一定的存储空间。
查询空值：可以使用 = 或者 <> 进行判断。
查询 NULL 值：需要使用 IS NULL 或 IS NOT NULL。
COUNT() 函数：在统计数据时，会忽略 NULL 值，但会将空值计入统计结果中。

示例：

使用 NULL 和空值操作

以下是对表 ky37 进行的一些操作示例。

1. 更新表结构和数据

向表中添加一个新的字段 n_null 并更新部分记录：

ALTER TABLE kyky ADD COLUMN n_null varchar(50);

UPDATE kyky SET n_null='' WHERE name='wjh';

+----+----------+-------+----------+--------+--------+
| id | name     | score | address  | hobbid | n_null |
+----+----------+-------+----------+--------+--------+
|  1 | xiewei   | 80.00 | beijing  |      2 | NULL   |
|  2 | wjh      | 98.00 | shenzhen |      2 |        |
|  3 | cxc      | 60.00 | shanghai |      4 | NULL   |
|  4 | jhw      | 99.00 | hangzhou |      5 | NULL   |
|  5 | yss      | 98.00 | laowo    |      3 | NULL   |
|  6 | xuyekang | 69.00 | nanjing  |      3 | NULL   |
|  7 | jqg      | 50.00 | nanjing  |      5 | NULL   |
|  1 | xiewei   | 80.00 | beijing  |      2 | NULL   |
|  2 | wjh      | 98.00 | shenzhen |      2 |        |
|  3 | cxc      | 60.00 | shanghai |      4 | NULL   |
|  4 | jhw      | 99.00 | hangzhou |      5 | NULL   |
|  5 | yss      | 98.00 | laowo    |      3 | NULL   |
|  6 | xuyekang | 69.00 | nanjing  |      3 | NULL   |
|  7 | jqg      | 50.00 | nanjing  |      5 | NULL   |
+----+----------+-------+----------+--------+--------+

查询 NULL 值

查询表中 addr 字段为 NULL 的记录：

SELECT * FROM kyky WHERE n_null IS NULL;

查询非 NULL 值

查询表中 addr 字段不为 NULL 的记录：

SELECT * FROM kyky WHERE n_null IS NOT NULL;

10. 连接查询

MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，基于这些表之间的共同字段，进行数据的拼接。首先，要确定一个主表作为结果集，然后将其他表的行有选择性的连接到选定的主表结果集上。使用较多的连接查询包括：内连接、左连接和右连接

10.1内连接

内连接是指返回两张或多张表中同时满足连接条件的记录行。通常使用 INNER JOIN 关键字在 FROM 子句中连接多张表，并在 ON 子句中设置连接条件。

示例：

创建两个测试表 test1 和 test2：

CREATE TABLE test1 (
  a_id INT(11) DEFAULT NULL,
  a_name VARCHAR(32) DEFAULT NULL,
  a_level INT(11) DEFAULT NULL
);

CREATE TABLE test2 (
  b_id INT(11) DEFAULT NULL,
  b_name VARCHAR(32) DEFAULT NULL,
  b_level INT(11) DEFAULT NULL
);

INSERT INTO test1 VALUES (1, 'aaaa', 10), (2, 'bbbb', 20), (3, 'cccc', 30), (4, 'dddd', 40);
INSERT INTO test2 VALUES (2, 'bbbb', 20), (3, 'cccc', 30), (5, 'eeee', 50), (6, 'ffff', 60);

执行内连接查询：

SELECT test1.a_id, test1.a_name
FROM test1 
INNER JOIN test2 ON test1.a_id = test2.b_id;

a_id	a_name
2	bbbb
3	cccc

解释：内连接返回两个表中 a_id 和 b_id 相同的记录行，只有符合条件的记录会被返回。

10.2 左连接

左连接也称为左外连接，返回左表的所有行，即使右表中没有匹配的记录。使用 LEFT JOIN 关键字来表示左连接。

示例：

在之前的 test1 和 test2 表上执行左连接查询：

SELECT test1.a_id, test1.a_name, test2.b_name
FROM test1 
LEFT JOIN test2 ON test1.a_id = test2.b_id;

结果：

a_id	a_name	b_name
1	aaaa	NULL
2	bbbb	bbbb
3	cccc	cccc
4	dddd	NULL

解释：左连接返回 test1 表的所有行，并根据 a_id = b_id 在 test2 表中进行匹配。如果 test2 中没有匹配的记录，结果会用 NULL 补足。

10.3 右连接

右连接也称为右外连接，返回右表的所有行，即使左表中没有匹配的记录。使用 RIGHT JOIN 关键字来表示右连接。

示例：

在之前的 test1 和 test2 表上执行右连接查询：

SELECT test1.a_id, test1.a_name, test2.b_name
FROM test1 
RIGHT JOIN test2 ON test1.a_id = test2.b_id;

结果：

a_id	a_name	b_name
2	bbbb	bbbb
3	cccc	cccc
NULL	NULL	eeee
NULL	NULL	ffff

解释：右连接返回 test2 表的所有行，并根据 a_id = b_id 在 test1 表中进行匹配。如果 test1 中没有匹配的记录，结果会用 NULL 补足。

总结

内连接：返回两个表中都满足条件的记录行，未匹配的行会被过滤掉。
左连接：返回左表的所有行，右表中未匹配的部分用 NULL 填充。
右连接：返回右表的所有行，左表中未匹配的部分用 NULL 填充。

pokemon..

关注

30
点赞
踩
21

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫