技巧总结

当我们在做练习题的时候有时候会遇见这样的需求：如果没有只出现一次的数字，输出 null
也就是说当我们查询出来的结果为空时，我们需要输出null。
那么什么时候我们可以准确的输出null值，不至于我们的输出在这个点上报错呢？
这里，我总结了一个表格：
20251031154831

可以使用聚合函数进行空值null值的转换，具体的聚合函数包括SUM/AVG/MAX/MIN
可以使用select语句进行转换，但空值应直接写在select中而非from中
limit语句无法出现新的null值
where和having同样无法出现新的null值

数据操作

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 插入单条数据
INSERT INTO table_name (column1, column2, column3) 
VALUES (value1, value2, value3);

-- 插入多条数据
INSERT INTO table_name (column1, column2, column3) 
VALUES 
    (value1, value2, value3),
    (value4, value5, value6),
    (value7, value8, value9);

-- 插入查询结果
INSERT INTO table1 (col1, col2) 
SELECT col3, col4 FROM table2 WHERE condition;

更新数据

1
2
3
4
5
6
UPDATE table_name 
SET column1 = value1, column2 = value2 
WHERE condition;

-- 示例
UPDATE users SET age = 25, updated_at = NOW() WHERE id = 1;

删除数据

1
2
3
4
DELETE FROM table_name WHERE condition;

-- 删除所有数据（谨慎使用）
DELETE FROM table_name;

查询操作

基本查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 查询所有列
SELECT * FROM table_name;

-- 查询指定列
SELECT column1, column2 FROM table_name;

-- 去重查询
SELECT DISTINCT column_name FROM table_name;

-- 别名
SELECT column_name AS alias_name FROM table_name;
SELECT t1.column1, t2.column2 FROM table1 t1, table2 t2;
WHERE 条件查询
SELECT * FROM table_name WHERE condition;

-- 比较运算符
SELECT * FROM users WHERE age > 18;
SELECT * FROM products WHERE price BETWEEN 10 AND 100;
SELECT * FROM users WHERE name IN ('Alice', 'Bob', 'Charlie');
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

排序和限制

1
2
3
4
5
6
7
-- 排序
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

-- 限制结果数量
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 5 OFFSET 10;  -- 跳过10条，取5条
SELECT * FROM table_name LIMIT 10, 5;  -- 同上

分组查询

1
2
3
4
5
6
7
8
9
SELECT column1, COUNT(*) 
FROM table_name 
GROUP BY column1;

-- 分组后筛选
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

聚合函数

常用聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 计数
SELECT COUNT(*) FROM table_name;  -- 所有行数
SELECT COUNT(column_name) FROM table_name;  -- 非空值数量
SELECT COUNT(DISTINCT column_name) FROM table_name;  -- 去重计数

-- 求和
SELECT SUM(column_name) FROM table_name;
SELECT SUM(DISTINCT column_name) FROM table_name;

-- 平均值
SELECT AVG(column_name) FROM table_name;
SELECT AVG(DISTINCT column_name) FROM table_name;
-- 四舍五入,保留3位小数
ROUND(..., 3)

-- 最大值/最小值
SELECT MAX(column_name) FROM table_name;
SELECT MIN(column_name) FROM table_name;

-- 分组统计
SELECT 
    department,
    COUNT(*) as total_employees,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary,
    SUM(salary) as total_salary
FROM employees
GROUP BY department;

正则表达式

如果你被要求去匹配一个字符串,那么最先想到的就应该是正则表达式.

正则表达式提供各种功能，以下是一些相关功能：
REGEXP_LIKE
语法：REGEXP_LIKE(字符串, 正则模式, [修饰符])

第三个参数:
'i'= 忽略大小写
'c'= 区分大小写
'm'= 多行模式

定位符：^开始 $结束
量词：*0次或多次 +1次或多次 ?0次或1次 {n}精确n次 {n,}至少n次 {n,m}n到m次
字符类：.任意字符 [abc]字符集合 [^abc]排除字符 [a-z]范围 \d数字 \w单词字符 \\s空格
其他：|或操作 ()分组捕获

  • ^：表示一个字符串或行的开头

  • [a-z]：表示一个字符范围，匹配从 a 到 z 的任何字符。

    • [0-9]：表示一个字符范围，匹配从 0 到 9 的任何字符。
    • [a-zA-Z]：这个变量匹配从 a 到 z 或 A 到 Z 的任何字符。请注意，你可以在方括号内指定的字符范围的数量没有限制，您可以添加想要匹配的其他字符或范围。
    • [^a-z]：这个变量匹配不在 a 到 z 范围内的任何字符。请注意，字符 ^ 用来否定字符范围，它在方括号内的含义与它的方括号外表示开始的含义不同。

  • [a-z]*：表示一个字符范围，匹配从 a 到 z 的任何字符 0 次或多次。

  • [a-z]+：表示一个字符范围，匹配从 a 到 z 的任何字符 1 次或多次。

  • .：匹配任意一个字符。

  • \.：表示句点字符。请注意，反斜杠用于转义句点字符，因为句点字符在正则表达式中具有特殊含义。还要注意，在许多语言中，你需要转义反斜杠本身，因此需要使用\\.。

  • $：表示一个字符串或行的结尾。

高级聚合函数

SUBSTRING(column_name, start, length)：这将从列的值中提取一个子字符串，从指定的起始位置开始，直到指定的长度。

UPPER(expression)：这会将字符串表达式转换为大写。

LOWER(expression)：这会将字符串表达式转换为小写。

CONCAT(string1, string2, ...)：这会将两个或多个字符串连接成一个字符串。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 标准差和方差
SELECT STDDEV(column_name) FROM table_name;
SELECT VARIANCE(column_name) FROM table_name;

-- 分组拼接
SELECT 
    department,
    GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') as employees
FROM employees
GROUP BY department;

-- 百分位数（MySQL 8.0+）
SELECT 
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median_salary
FROM employees;

多表查询

内连接

1
2
3
4
5
6
7
8
9
10
11
12
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

-- 多表内连接
SELECT 
    o.order_id, 
    u.name, 
    p.product_name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN products p ON o.product_id = p.id;

外连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 左外连接
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

-- 右外连接
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

-- 全外连接（MySQL不支持，但可以模拟）
SELECT columns FROM table1 LEFT JOIN table2 ON condition
UNION
SELECT columns FROM table1 RIGHT JOIN table2 ON condition;

子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- 在WHERE中使用子查询
SELECT * FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 在FROM中使用子查询
SELECT dept_name, avg_salary
FROM (
    SELECT department as dept_name, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE avg_salary > 5000;

-- 在SELECT中使用子查询
SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees) as company_avg_salary
FROM employees;

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 1000
);

联合查询

1
2
3
4
5
6
7
8
9
-- UNION（去重）
SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

-- UNION ALL（不去重）
SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

数据库操作

创建数据库

1
2
3
CREATE DATABASE database_name;
CREATE DATABASE IF NOT EXISTS database_name;
CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看数据库

1
2
SHOW DATABASES;
SHOW CREATE DATABASE database_name;

选择数据库

1
USE database_name;

删除数据库

1
2
DROP DATABASE database_name;
DROP DATABASE IF EXISTS database_name;

数据表操作

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 带外键约束
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

查看表结构

1
2
3
4
DESC table_name;
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;
SHOW CREATE TABLE table_name;

修改表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 添加列
ALTER TABLE table_name ADD COLUMN new_column VARCHAR(100);

-- 修改列
ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(200);
ALTER TABLE table_name CHANGE old_name new_name VARCHAR(100);

-- 删除列
ALTER TABLE table_name DROP COLUMN column_name;

-- 添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);
ALTER TABLE table_name ADD UNIQUE unique_index (column_name);

-- 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;
RENAME TABLE old_table TO new_table;

删除表

1
2
3
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;
TRUNCATE TABLE table_name;  -- 清空表数据

索引操作

创建索引

1
2
3
4
5
6
7
8
9
10
11
-- 普通索引
CREATE INDEX index_name ON table_name (column_name);

-- 唯一索引
CREATE UNIQUE INDEX unique_index_name ON table_name (column_name);

-- 复合索引
CREATE INDEX composite_index ON table_name (col1, col2, col3);

-- 全文索引（适用于文本搜索）
CREATE FULLTEXT INDEX ft_index ON table_name (text_column);

查看和删除索引

1
2
3
4
5
6
-- 查看索引
SHOW INDEX FROM table_name;

-- 删除索引
DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

事务操作

基本事务

1
2
3
4
5
6
7
8
9
10
11
12
13
START TRANSACTION;
-- 或
BEGIN;

-- 执行多个操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

事务保存点

1
2
3
4
5
6
7
8
9
10
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT savepoint1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 如果出现问题，可以回滚到保存点
ROLLBACK TO savepoint1;

COMMIT;

用户和权限管理

用户管理

1
2
3
4
5
6
7
8
9
10
11
-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 修改密码
ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

-- 删除用户
DROP USER 'username'@'host';

-- 查看用户
SELECT user, host FROM mysql.user;

权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 授予权限
GRANT privilege_type ON database_name.table_name TO 'username'@'host';

-- 示例
GRANT SELECT, INSERT ON mydb.* TO 'username'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

-- 撤销权限
REVOKE privilege_type ON database_name.table_name FROM 'username'@'host';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看权限
SHOW GRANTS FOR 'username'@'host';

常用权限类型

1
2
3
4
5
6
7
8
SELECT：查询数据
INSERT：插入数据
UPDATE：更新数据
DELETE：删除数据
CREATE：创建数据库/表
DROP：删除数据库/表
ALTER：修改表结构
ALL PRIVILEGES：所有权限

实用技巧

批量操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 批量插入时忽略重复键
INSERT IGNORE INTO table_name (col1, col2) VALUES (...);

-- 批量插入时更新重复键
INSERT INTO table_name (col1, col2) 
VALUES (...) 
ON DUPLICATE KEY UPDATE col2 = VALUES(col2);

-- 使用CASE WHEN
UPDATE products 
SET price = CASE 
    WHEN category = 'A' THEN price * 1.1
    WHEN category = 'B' THEN price * 0.9
    ELSE price
END;

日期时间函数

1
2
3
4
5
6
7
8
9
10
-- 当前时间
SELECT NOW(), CURDATE(), CURTIME();

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);
SELECT DATEDIFF('2024-01-01', '2023-01-01');

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

杂项

DISTINCT去重

SELECT TWEET_ID FROM TWEETS WHERE CHAR_LENGTH(CONTENT) >15;
char长度

datediff(日期1, 日期2)
得到的结果是日期1与日期2相差的天数。

timestampdiff(时间类型, 日期1, 日期2)
这个函数和上面diffdate的正、负号规则刚好相反。
日期1大于日期2，结果为负，日期1小于日期2，结果为正。

AVG(...) 求平均值

ROUND(..., 3) 四舍五入,保留3位小数

许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。