跳至主要內容

Mysql - 4 SQL语句2

code中间件Mysql约 2669 字大约 9 分钟

SQL语句

DQL (数据库查询语言)

DQL 的核心命令是 SELECT，用于从一个或多个表中检索数据

基本语法

SELECT 列名1, 列名2, ...
FROM 表名
WHERE 条件
GROUP BY 分组列
HAVING 分组条件
ORDER BY 排序列 ASC|DESC
LIMIT 限制行数;

单表查询

基础查询
-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT name, age FROM users;

-- 去重查询
SELECT DISTINCT department FROM users;
限制数量和分页
场景示例
获取排名前N的记录SELECT * FROM students ORDER BY score DESC LIMIT 5;
分页显示数据SELECT * FROM products LIMIT 20, 10;
随机抽样SELECT * FROM users ORDER BY RAND() LIMIT 100;
性能优化（避免大数据量）SELECT * FROM logs LIMIT 1000;

通过limit来限制查询的数量，只取前n个结果：

SELECT * FROM 表名 LIMIT 数量

也可以进行分页：

-- 语法：LIMIT 起始索引, 每页的记录数
-- 起始索引从0开始

-- 查询第1页（前10条）
SELECT * FROM users LIMIT 0, 10;

-- 查询第2页（第11-20条）
SELECT * FROM users LIMIT 10, 10;

-- 查询第3页（第21-30条）
SELECT * FROM users LIMIT 20, 10;

-- 第6条数据
SELECT * FROM users LIMIT 5, 1
OFFSET
-- 跳过前10条，返回接下来的10条
SELECT * FROM users LIMIT 10 OFFSET 10;
条件查询 (where)
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE name LIKE 'Zhang%';
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

条件：

比较运算符功能
>大于
>=大于等于
<小于
<=小于等于
=等于
<> 或 !=不等于
BETWEEN … AND …在某个范围内（含最小、最大值）
IN(…)在in之后的列表中的值，多选一
LIKE 占位符模糊匹配（_匹配单个字符，%匹配任意个字符）
IS NULL是NULL
逻辑运算符功能
AND 或 &&并且（多个条件同时成立）
OR 或 ||或者（多个条件任意一个成立）
NOT 或 !非，不是
排序查询 (ORDER BY)
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：

  • ASC: 升序（默认）
  • DESC: 降序

也可以可以同时添加多个排序：

SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC|DESC, 列名2 ASC|DESC

这样会先按照列名1进行排序，每组列名1相同的数据再按照列名2排序

分组查询

通过使用group by来对查询结果进行分组，它需要结合聚合函数一起使用

SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名

还可以添加having来限制分组条件：

SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件

例子：

-- 统计每个部门的人数
SELECT department, COUNT(*) as count 
FROM users 
GROUP BY department;

-- 分组后筛选
SELECT department, AVG(salary) as avg_salary
FROM users
GROUP BY department
HAVING avg_salary > 5000;
聚合函数

语法：

SELECT 聚合函数(字段列表) FROM 表名;

聚集函数一般用作统计，包括：

  • count([distinct]*)：统计所有的行数（distinct表示去重再统计，下同）
  • count([distinct]列名)：统计某列的值总和
  • sum([distinct]列名)：求一列的和（注意必须是数字类型的）
  • avg([distinct]列名)：求一列的平均值（注意必须是数字类型）
  • max([distinct]列名)：求一列的最大值
  • min([distinct]列名)：求一列的最小值
where 与 having 区别
  • 执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。
  • 判断条件不同：where不能对聚合函数进行判断，而having可以
  • 执行顺序：where > 聚合函数 > having
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT

多表查询

查询类型说明使用场景
内连接查询交集数据只需要匹配的记录
左外连接左表全部 + 交集保留左表所有数据
右外连接右表全部 + 交集保留右表所有数据
自连接表与自身连接树形结构、上下级关系
子查询嵌套查询复杂条件、分步查询
联合查询合并结果集合并多个独立查询结果

多表查询是同时查询的两个或两个以上的表，多表查询会提通过连接转换为单表查询

多表关系
  • 一对多（多对一）
  • 多对多
  • 一对一
一对多

一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键

多对多

一个学生可以选多门课程，一门课程也可以供多个学生选修

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

笛卡尔积 (合并查询)

合并查询（笛卡尔积，会展示所有组合结果）：

select * from employee, dept;

消除无效笛卡尔积：

select * from employee, dept where employee.dept = dept.id;
内连接（INNER JOIN）

隐式内连接：

SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

显式内连接：

SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;

显式性能比隐式高

-- 隐式内连接
SELECT emp.name, dept.name 
FROM emp, dept 
WHERE emp.dept_id = dept.id;

-- 显式内连接（推荐）
SELECT e.name, d.name
FROM emp e
INNER JOIN dept d ON e.dept_id = d.id;
外连接
左外连接 (LEFT JOIN)

查询左表所有数据，以及两表交集部分

SELECT e.name, d.name
FROM emp e
LEFT JOIN dept d ON e.dept_id = d.id;
-- 即使员工没有部门，也会显示员工信息，部门字段为NULL
右外连接 (RIGHT JOIN)

查询右表所有数据，以及两表交集部分

SELECT e.name, d.name
FROM emp e
RIGHT JOIN dept d ON e.dept_id = d.id;
-- 即使部门没有员工，也会显示部门信息，员工字段为NULL
自连接

当前表与自身的连接查询，自连接必须使用表别名

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

自连接查询，可以是内连接查询，也可以是外连接查询

-- 查询员工及其所属领导的名字
select a.name, b.name from employee a, employee b where a.manager = b.id;
-- 没有领导的也查询出来
select a.name, b.name from employee a left join employee b on a.manager = b.id;

子查询

SQL语句中嵌套SELECT语句，称谓嵌套查询，又称子查询。

子查询返回结果可用操作符示例
单行单列（标量）=, >, <, >=, <=, !=WHERE dept = (SELECT ...)
多行单列（列）IN, NOT IN, ANY, ALLWHERE dept IN (SELECT ...)
单行多列（行）=, INWHERE (col1, col2) = (SELECT ...)
多行多列（表）IN, EXISTS, 作为临时表FROM (SELECT ...) AS t

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询结果可以分为：

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果为一行）
  • 表子查询（子查询结果为多行多列）

根据子查询位置可分为：

  • WHERE 之后
  • FROM 之后
  • SELECT 之后
标量子查询

子查询返回的结果是单个值（数字、字符串、日期等）。 常用操作符：- < > > >= < <=

select * from employee 
where dept = (select id from dept where name = '销售部' LIMIT 1);

-- 如果返回多行需要使用 IN 处理多行结果
select * from employee 
where dept IN (select id from dept where name = '销售部');
列子查询
操作符说明
IN在指定集合范围内，多选一
NOT IN不在指定集合范围内
ANY子查询返回列表中，有任意一个满足即可
SOME与ANY等同
ALL子查询返回列表的所有值都必须满足

子查询返回一列（多行一列），常用操作符：IN, NOT IN, ANY, ALL

-- 查询在"销售部"和"市场部"工作的员工
SELECT * FROM emp
WHERE dept_id IN (SELECT id FROM dept WHERE name IN ('销售部', '市场部'));

-- 查询工资比所有财务部员工都高的员工
SELECT * FROM emp
WHERE salary > ALL (SELECT salary FROM emp WHERE dept_id = 3);
行子查询

子查询返回一行（一行多列）

-- 查询与"张三"的工资和部门都相同的员工
SELECT * FROM emp
WHERE (salary, dept_id) = (SELECT salary, dept_id FROM emp WHERE name = '张三');
表子查询

子查询返回多行多列，常作为临时表

-- 查询入职日期是"2020-01-01"之后的员工及其部门信息
SELECT e.*, d.name
FROM (SELECT * FROM emp WHERE entrydate > '2020-01-01') e
LEFT JOIN dept d ON e.dept_id = d.id;
相关子查询

相关子查询（Correlated Subquery）是指在子查询中引用了外部查询中的列。

换句话说，相关子查询是依赖于外部查询的，它无法独立执行，每一行的查询结果都依赖于外部查询当前行的数据。常用于需要逐行比较的情况。

假设我们有两个表：

  • employees (员工表)，包含列：id, name, salary
  • departments (部门表)，包含列：id, name, manager_id

我们想要找出所有薪水高于其部门经理薪水的员工。可以使用相关子查询

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT m.salary
    FROM employees m
    WHERE m.id = e.manager_id
)

  • 在这个查询中，子查询 SELECT m.salary FROM employees m WHERE m.id = e.manager_id 使用了外部查询中的 e.manager_id 来查找每个员工的经理的薪水。

  • 这里的子查询是相关的，因为它引用了外部查询中的 e.manager_id，并且每个外部查询的行都需要计算一次子查询的结果。

编写一个解决方案来查询分数的排名。排名按以下规则计算:

分数应按从高到低排列。 如果两个分数相等，那么两个分数的排名应该相同。 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

-- 排名：计算大于等于此分数的数量 + distinct
SELECT 
    s1.score,
    (
        SELECT
            COUNT(DISTINCT s2.score)
        FROM
            scores s2
        where
            s2.score >= s1.score
    ) as `rank`
FROM
    scores s1
ORDER BY
    s1.score DESC

联合查询

把多次查询的结果合并，形成一个新的查询集

SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...
  • UNION ALL 会有重复结果，UNION 不会
  • 联合查询比使用or效率高，不会使索引失效
上次编辑于: