增删改查¶
Insert增（Create）Delete删（Delete）Update改（Update）Select查（Retrieve）
增 Insert¶
-- key：顺序不必和表头字段顺序一致，有默认值、可为空、可自增等约束时可不写
-- value：与key的顺序必须一一对应
insert into <表名> (key1, key2, key3, ...)
VALUES
(value1, value2, value3, ...),
(value1, value2, value3, ...);
-- 示例
INSERT INTO demo.test
(barcode,goodsname,price)
VALUES ('0001','本',3);
-- 将查询结果作为数据插入
INSERT INTO statistics (class_id, average)
SELECT class_id, AVG(score)
FROM students
GROUP BY class_id;
-- 插入记录时如果已存在则更新
INSERT INTO students (id, class_id, name, gender, score)
VALUES (1, 1, '小明', 'F', 99)
ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
-- 插入记录时如果已存在则忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score)
VALUES (1, 1, '小明', 'F', 99);
-- 替换
REPLACE INTO students (id, class_id, name, gender, score)
VALUES (1, 1, '小明', 'F', 99);
删 Delete¶
-- 清空表，由于安全模式的存在会报错，防止误操作
delete from <表名>;
-- 删除指定条件的行数据
delete from <表名>
where ...;
改 Update¶
-- 修改主键时要小心
update <表名>
set key1=value1, key2=value2
where ...; -- 不带条件限制时则会更新所有行数据
查 Select¶
字段和表都可以使用 AS 设置别名，方便引用
-- SELECT可以直接计算
SELECT 1+2; -- 3
-- 完整语法
SELECT [DISTINCT] 字段列表|*
/*
加 DISTINCT 可以去除重复行，但不会去除 NULL 行，通常与 WHERE x IS NOT NULL 一起使用
* 为通配符
可以接聚合函数
*/
FROM 数据源 -- 数据源可以是表
-- 也可以是其它查询结果（即虚拟表，也叫派生表，或者子查询），必须要用AS起一个别名
WHERE 条件 -- 查询条件
GROUP BY 字段 -- 分组，常与聚合函数一起使用
HAVING 条件 -- 筛选 GROUP 分组后的结果
ORDER BY 字段A ASC|DESC, 字段B ASC|DESC -- 默认升序 ASC
-- 先按字段A顺序排，如果有相同的则按字段B顺序排
LIMIT 起始索引，行数 -- 索引0表示从第一行起
语句执行顺序¶
/*
FROM
WHERE
JOIN
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
*/
从执行顺序可以看出，WHERE 是先筛选后连接（即可以先缩小范围再连接），而 HAVING 是先连接后筛选，由于 WHERE 在连接前缩小了范围，所以效率更高，但 HAVING 在 GROUP BY 后，可以用来过滤分组，另外还可以接聚合函数。
所以在一些复杂的查询时要善用 HAVING，并且可以结合 WHERE 提高性能。
表达式¶
SELECT *
FROM students
WHERE (score < 80 OR score > 90)
AND gender = 'M';
= -- 等于
>
<
<> -- 不等于，也可以写作 !=，或者 NOT ... = ...
>=
<=
IS NULL -- 空
IS NOT NULL -- 非空，等价于 <> NULL
LIKE '张_' -- _ 表示一个任意字符
LIKE 'a%' -- % 表示多个任意字符
/*
示例：
以a开头：a%
以a结尾：%a
包含a：%a%
*/
条件1 AND 条件2 -- AND 的优先级高于 OR
x BETWEEN 60 AND 90 -- 等价于 x >=60 AND x < 90
条件1 OR 条件2
xx IN ('xxx', 'xx') -- 可视作 OR 的简写
NOT 条件
/*
NOT IN
NOT LIKE
NOT BETWEEN
NOT EXISTS
IS NOT NULL
*/
函数¶
数值函数¶
SELECT ABS(-5); -- 返回 5
SELECT ROUND(1.2345, 2); -- 返回 1.23
字符串函数¶
SELECT UPPER('hello'); -- 返回 'HELLO'
SELECT CONCAT('Hello', ' ', 'World'); -- 返回 'Hello World'
日期时间函数¶
SELECT NOW(); -- 返回当前日期和时间
SELECT DATEDIFF('2025-01-06', '2025-01-01'); -- 返回 5
聚合函数¶
COUNT(*|字段) -- 计数，会忽略 NULL 的行
COUNT(DISTINCT x) -- 通常与 DISTINCT 一起使用，去重
SUM() -- 求和
MAX() -- 最大值
MIN() -- 最小值
AVG() -- 平均值
LEFT(str，n) -- 返回字符串左边的几个字符
-- 统计查询结果数量
SELECT COUNT(*) <列名>
FROM <表名>;
+-----------+
| <列名> |
+------------+
| 10 |
+-----------+
-- 通常与分组一起使用
-- 比如先按班级分组，再按性别分组，然后统计出各班男女生数量各多少
SELECT class_id, gender, COUNT(*) num -- num 是虚拟字段的别名
FROM students
GROUP BY class_id, gender; -- 通常分组字段可被写在SELECT后，方便查询结果中区分
逻辑函数¶
-- IF 根据工资返回 'High' 或 'Low'
SELECT IF(salary > 5000, 'High', 'Low') FROM employees;
-- CASE 用于实现复杂的条件判断
SELECT
CASE
WHEN salary > 5000 THEN 'High'
WHEN salary > 3000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees;
-- 返回第一个不为 NULL 的值
SELECT COALESCE(NULL, NULL, 'Hello', 'World'); -- 'Hello'
-- 如果 expr1 为 NULL，则返回 expr2，否则返回 expr1 的值
SELECT IFNULL(commission_pct, 0) AS commission_pct
-- 如果 expr1 等于 expr2，则返回 NULL，否则返回 expr1
SELECT NULLIF(10, 10); -- 返回 NULL
多表查询¶
CROSS JOIN 笛卡尔集¶
交叉连接，返回两个表的笛卡尔积。这意味着第一个表中的每一行都会与第二个表中的每一行进行组合。如果第一个表有 M 行，第二个表有 N 行，那么结果集将包含 M × N 行。
- 语法
SELECT *
FROM table1
CROSS JOIN table2;
-- 这是一种古老的写法，不推荐使用
SELECT *
FROM table1, table2;
- 举例
假设有两张表,都只有一个字段，行数据如下
Colors.Color: (Red), (Green)
Sizes.Size: (S), (M), (L)
SELECT *
FROM Colors
CROSS JOIN Sizes;
会有 2*3=6 条记录
Color Size
Red S
Red M
Red L
Green S
Green M
Green L
INNER JOIN 交集¶
内连接，根据条件进行过滤后的连接，只返回满足条件的行。
大多数 DBMS 中都可简写为 JOIN，必须用 ON 或 USING 子句指定连接条件
- 语法
SELECT *
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
-- 如果两个表的列名相同&表示相同含义，则可以使用 USING 简写
SELECT *
FROM employees
INNER JOIN departments USING (department_id); -- 等价于 ON employees.department_id = departments.department_id
-- NATURAL JOIN 是 USING 的自动化版本。它会自动找出两个表中所有同名的列，并使用它们作为等值连接的条件
-- 虽然很简洁，但非常不推荐在生产代码中使用 NATURAL JOIN，因为它依赖于表结构，很不安全。
- 举例
Students (学生表)
ID StudentName
1 张三
2 李四
3 王五
Courses (课程表)
ID CourseName
101 数学
102 英语
103 物理
StudentCourses (学生选课表)
StudentID CourseID
1 101
1 102
2 102
2 103
3 101
SELECT
s.StudentName AS 学生姓名,
c.CourseName AS 课程名称
FROM
Students s -- 首先从学生表开始
INNER JOIN
StudentCourses sc ON s.ID = sc.StudentID -- 连接选课表，找到学生对应的选课记录
INNER JOIN
Courses c ON c.ID = sc.CourseID -- 再连接课程表，通过选课记录找到对应的课程名
ORDER BY
s.StudentName, c.CourseName; -- 按学生姓名和课程名称排序，使结果更清晰
学生姓名 课程名称
张三 数学
张三 英语
李四 英语
李四 物理
王五 数学
OUTER JOIN 并集¶
保留一个表或两个表中的所有记录，即使它在另一个表中没有匹配项（用 NULL 值填充）
Customers 表
customer_id customer_name
1 张三
2 李四
3 王五
Orders 表
order_id order_amount customer_id
101 200.50 1
102 99.99 1
103 500.00 2
104 150.00 NULL
- LEFT JOIN 返回左表和关联表共有的数据，最常用的外连接，只需要记住这一个即可
- RIGHT JOIN 返回右表和关联表共有的数据，通常可以用左连接实现相同效果
- FULL JOIN 把想要连接的两个表的所有数据都展示出来，能匹配上的就匹配，匹配不上的就用 NULL 补全，MySQL 不支持，可以通过 LEFT JOIN 和 RIGHT JOIN 的 UNION 来模拟
SELECT
c.customer_name,
o.order_id,
o.order_amount
FROM
Customers c
LEFT JOIN
Orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_name;
-- 结果
customer_name order_id order_amount
张三 101 200.50
张三 102 99.99
李四 103 500.00
王五 NULL NULL
UNION 纵向并集¶
用于连接多个表的行，即记录合并，纵向扩展
UNION 与 UNION ALL 的区别在于前者会去重，而后者不会去重