前言
干货,长期更新,有异议评论区交流。
注意:所有的sql示例均为虚假的例子,具体语句需要自己根据项目实况编写!!!
SQL
1、某一天或几天的数据查询
在 MySQL 中,可以使用 DATE()
函数来提取日期部分,或者使用范围查询来表示某一天。
# 20.751s -> 220367条
select did,ctime from boiler_guolu where DATE(ctime) = '2024-08-15'
# 20.385s -> 220367条
select did,ctime from boiler_guolu where ctime >= '2024-08-15' and ctime < '2024-08-16'
# 26.615s -> 220367条
select did,ctime from boiler_guolu where DATE_FORMAT(ctime, '%Y-%m-%d') = '2024-08-15'
# 结论: DATE和>=|<两种查询方式速度相当,DATE_FORMAT效果最差
# 查询 某一天或不固定的几天,建议使用DATE
# 查询 连续的时间范围,建议使用>=和< 方案
2、分页查询
对于大数据集,使用 LIMIT
和 OFFSET
来分页查询数据,避免一次性加载所有数据。
SELECT *
FROM table_name
ORDER BY column_name
LIMIT 10 OFFSET 20; -- 从第21行开始,取10行数据
3、使用 IN
代替多个 OR
当需要查询多个值时,使用 IN
代替多个 OR
条件,这样可以提高查询性能。
SELECT *
FROM table_name
WHERE column_name IN ('value1', 'value2', 'value3');
4、 检查是否有符合条件的记录
对于大型表,避免使用 COUNT(*)
来计算行数。如果只需要检查是否有记录,可以使用 EXISTS
。
-- 检查是否有记录
SELECT EXISTS (SELECT 1 FROM table_name WHERE column = 'value');
-- 使用 COUNT(*) 获取行数(不推荐)
SELECT COUNT(*) FROM table_name WHERE column = 'value';
5、重复行处理
查询重复行
SELECT name, email, COUNT(*)
FROM employees
GROUP BY name, email
HAVING COUNT(*) > 1;
如果你只想查询唯一的行,你可以使用 DISTINCT
关键字来选择表中的唯一行。
SELECT DISTINCT column_name FROM your_table;
为了删除新索引下的重复行,你可以使用 DELETE
语句并利用键进行排序
# 在这个例子中,将只保留最小id的重复行。
# 如果想保留最大,则使用max(id)进行筛选。
# 删除操作需谨慎,请严格做好备份工作!
DELETE FROM your_table
WHERE id NOT IN (SELECT MIN(id) FROM your_table GROUP BY column_name);
6、模糊查询
LIKE 关键字允许你使用通配符来进行模糊匹配。常见的通配符包括:
%:表示零个或多个字符
_:表示一个单独的字符
# 查找名字中包含 “John” 的记录
SELECT * FROM employees
WHERE name LIKE '%John%';
# 匹配以 “John” 开头的名字
SELECT * FROM employees
WHERE name LIKE 'John%';
# 匹配以 “John” 结尾的名字
SELECT * FROM employees
WHERE name LIKE '%John';
# 名字中第二个字符是 ‘a’ 的记录
SELECT * FROM employees
WHERE name LIKE '_a%';
REGEXP
关键字允许你使用正则表达式进行更复杂的模糊查询。它比 LIKE
更强大,但也更复杂。
# 假设你想查找名字中包含 “John” 或 “Jane” 的记录
SELECT * FROM employees
WHERE name REGEXP 'John|Jane';
# 如果你想查找名字中包含字母 ‘a’ 和 ‘e’ 的记录
SELECT * FROM employees
WHERE name REGEXP 'a.*e';
# 查找包含两个 “John” 的记录
SELECT * FROM employees
WHERE name REGEXP 'John.*John';
7、一些函数
# 聚合函数
# COUNT():计算指定列或行的数量。
SELECT COUNT(column_name) FROM table_name;
# SUM():计算指定列的总和。
SELECT SUM(column_name) FROM table_name;
# AVG():计算指定列的平均值。
SELECT AVG(column_name) FROM table_name;
# MIN():返回指定列的最小值。
SELECT MIN(column_name) FROM table_name;
# MAX():返回指定列的最大值。
SELECT MAX(column_name) FROM table_name;
# 字符串函数
# CONCAT():连接两个或多个字符串。
SELECT CONCAT(column1, column2) FROM table_name;
# SUBSTRING():从字符串中提取子字符串。
SELECT SUBSTRING(column_name, start_position, length) FROM table_name;
# LENGTH():返回字符串的长度。
SELECT LENGTH(column_name) FROM table_name;
# REPLACE():替换字符串中的某个子字符串。
SELECT REPLACE(column_name, 'old_value', 'new_value') FROM table_name;
# UPPER():将字符串转换为大写。
SELECT UPPER(column_name) FROM table_name;
# LOWER():将字符串转换为小写。
SELECT LOWER(column_name) FROM table_name;
# TRIM():删除字符串两端的空白字符。
SELECT TRIM(column_name) FROM table_name;
# 日期和时间函数
# 将时间转换为字符串输出
select date_format(create_time, '%Y-%m-%d %H:%i:%s') from table_name
# NOW():返回当前日期和时间。
SELECT NOW();
# CURDATE():返回当前日期。
SELECT CURDATE();
# CURTIME():返回当前时间。
SELECT CURTIME();
# DATE_ADD():在日期上增加指定的时间间隔。
SELECT DATE_ADD(date_column, INTERVAL 1 DAY) FROM table_name;
# DATE_SUB():从日期中减去指定的时间间隔。
SELECT DATE_SUB(date_column, INTERVAL 1 MONTH) FROM table_name;
# DATEDIFF():计算两个日期之间的差异(以天为单位)。
SELECT DATEDIFF(date1, date2) FROM table_name;
# 数学函数
# ROUND():对数值进行四舍五入。
SELECT ROUND(column_name, decimal_places) FROM table_name;
# FLOOR():向下取整。
SELECT FLOOR(column_name) FROM table_name;
# CEIL():向上取整。
SELECT CEIL(column_name) FROM table_name;
# ABS():返回绝对值。
SELECT ABS(column_name) FROM table_name;
8、联表查询
# INNER JOIN:返回两个表中满足条件的记录。
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
# LEFT JOIN:返回左表所有记录和右表中匹配的记录。
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;
# RIGHT JOIN:返回右表所有记录和左表中匹配的记录。
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;
# FULL JOIN:返回两个表中所有记录,匹配的记录合并。
SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;