mysql高阶语句

目录

排序 (ORDER BY)

区间查询 (BETWEEN)

AND/OR

嵌套查询

GROUP BY

COUNT、SUM、AVG、MAX、MIN

LIMIT

表和字段的别名

子查询

链表查询

内连接 (INNER JOIN)

左连接 (LEFT JOIN)

右连接 (RIGHT JOIN)

以下表为例

排序 (ORDER BY)

按照分数 (score) 降序排序：

SELECT * FROM class1_score ORDER BY score DESC;

按照年龄 (age) 升序排序：

SELECT * FROM class1_infor ORDER BY age ASC;

区间查询 (BETWEEN)

查询分数在 80 到 90 之间的记录：

SELECT * FROM class1_score WHERE score BETWEEN 80 AND 90;

查询分数在 60 到 80 之间的记录：

SELECT * FROM class1_score WHERE score>60 AND score<80;

AND/OR

查询分数在 80 到 90 之间且年龄大于 25 岁的记录：

SELECT * FROM class1_infor WHERE age > 25 AND id IN (SELECT id FROM class1_score WHERE score BETWEEN 80 AND 90);

嵌套查询

查询名字为 'Alice' 的分数：

SELECT * FROM class1_score WHERE id = (SELECT id FROM class1_infor WHERE name = 'Alice');

GROUP BY

按照兴趣爱好 (hobbyid) 进行分组，并统计每组的人数：

SELECT hobbyid, COUNT(*) AS count FROM class1_infor GROUP BY hobbyid;

COUNT、SUM、AVG、MAX、MIN

统计总人数：

SELECT COUNT(*) AS total FROM class1_infor;

计算总分数、平均分数、最高分和最低分：

SELECT SUM(score) AS total_score, AVG(score) AS avg_score, MAX(score) AS max_score, MIN(score) AS min_score FROM class1_score;

LIMIT

查询前 5 条记录：

SELECT * FROM class1_infor LIMIT 5;

表和字段的别名

给表起别名，查询名字和分数：

SELECT c.name AS name, s.score AS score
FROM class1_infor c
JOIN class1_score s ON c.id = s.id;

子查询

查询分数最高的学生的信息：

SELECT *
FROM class1_infor
WHERE id = (SELECT id FROM class1_score ORDER BY score DESC LIMIT 1);

链表查询

内连接 (INNER JOIN)

内连接会返回两个表中匹配的行。

SELECT ci.id, ci.name, ci.address, cs.score
FROM class1_infor ci
INNER JOIN class1_score cs ON ci.id = cs.id;

左连接 (LEFT JOIN)

左连接会返回左表（class1_infor）中的所有行，以及右表（class1_score）中匹配的行。如果右表中没有匹配的行，则会返回 NULL 值。

SELECT ci.id, ci.name, ci.address, cs.score
FROM class1_infor ci
LEFT JOIN class1_score cs ON ci.id = cs.id;

右连接 (RIGHT JOIN)

右连接会返回右表（class1_score）中的所有行，以及左表（class1_infor）中匹配的行。如果左表中没有匹配的行，则会返回 NULL 值。

SELECT ci.id, ci.name, ci.address, cs.score
FROM class1_infor ci
RIGHT JOIN class1_score cs ON ci.id = cs.id;