- **常用查询** ```bash 选择所有行和列：SELECT * FROM table_name; 选择特定列：SELECT column1, column2, ... FROM table_name; 选择不同的值：SELECT DISTINCT column1, column2, ... FROM table_name; 按条件选择：SELECT * FROM table_name WHERE condition; 使用多个条件选择：SELECT * FROM table_name WHERE condition1 AND condition2; 使用通配符选择：SELECT * FROM table_name WHERE column_name LIKE 'pattern'; 对结果进行排序：SELECT * FROM table_name ORDER BY column_name DESC; 对结果进行分组：SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name; 进行聚合操作：SELECT SUM(column_name) FROM table_name; 对结果进行计数：SELECT COUNT(*) FROM table_name; 对结果进行平均值计算：SELECT AVG(column_name) FROM table_name; 对结果进行最大值计算：SELECT MAX(column_name) FROM table_name; 对结果进行最小值计算：SELECT MIN(column_name) FROM table_name; 连接两个表：SELECT * FROM table1 JOIN table2 ON condition; 左连接两个表：SELECT * FROM table1 LEFT JOIN table2 ON condition; 右连接两个表：SELECT * FROM table1 RIGHT JOIN table2 ON condition; 使用子查询：SELECT * FROM table_name WHERE column_name IN (SELECT column_name FROM table_name WHERE condition); 插入新行：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); 更新行：UPDATE table_name SET column_name = value WHERE condition; 删除行：DELETE FROM table_name WHERE condition; 创建新表：CREATE TABLE table_name (column1 datatype1, column2 datatype2, ...); 更改表的结构：ALTER TABLE table_name ADD column_name datatype; 删除表：DROP TABLE table_name; 创建新索引：CREATE INDEX index_name ON table_name (column_name); 删除索引：DROP INDEX index_name; 对结果进行分页：SELECT * FROM table_name LIMIT offset, count; 使用聚合函数进行分组计算：SELECT column_name, AVG(column_name2) FROM table_name GROUP BY column_name; 连接多个表：SELECT * FROM table1 JOIN table2 ON condition JOIN table3 ON condition; 对结果进行逆序排列：SELECT * FROM table_name ORDER BY column_name DESC; 使用正则表达式选择数据：SELECT * FROM table_name WHERE column_name REGEXP 'pattern'; ``` - **其它推荐** https://mp.weixin.qq.com/s/nY7wFXNZE3HRTwWpSnLFzA https://mp.weixin.qq.com/s/VljCtHJS1LYwNR3_epiQCg https://mp.weixin.qq.com/s/DOaR81E3PzeX5MTSPtKIjw - **高JI查询** ```bash 使用子查询进行更新：UPDATE table_name SET column_name = (SELECT column_name FROM other_table WHERE condition) WHERE condition; 使用子查询进行删除：DELETE FROM table_name WHERE column_name IN (SELECT column_name FROM other_table WHERE condition); 使用CASE语句进行条件选择：SELECT column_name, CASE WHEN condition1 THEN 'value1' WHEN condition2 THEN 'value2' ELSE 'value3' END FROM table_name; 使用自连接进行查询：SELECT t1.column_name, t2.column_name FROM table_name t1 JOIN table_name t2 ON condition; 使用多列进行排序：SELECT column1, column2, ... FROM table_name ORDER BY column1 ASC, column2 DESC; 使用GROUP BY进行多个列的分组：SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2; 使用HAVING过滤分组：SELECT column_name, COUNT() FROM table_name GROUP BY column_name HAVING COUNT() > 1; 使用LIMIT和OFFSET进行分页：SELECT * FROM table_name LIMIT 10 OFFSET 20; 使用JOIN和子查询进行过滤：SELECT * FROM table1 JOIN (SELECT column_name FROM table2 WHERE condition) t ON t.column_name = table1.column_name; 使用UNION合并多个查询结果：SELECT column1 FROM table1 UNION SELECT column1 FROM table2; 使用UNION ALL合并多个查询结果（包括重复项）：SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2; 使用LEFT JOIN和COALESCE函数填充空值：SELECT t1.column_name, COALESCE(t2.column_name, 'NA') FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id; 使用子查询和NOT EXISTS进行反向过滤：SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.id = table2.id); 使用GROUP BY和WITH ROLLUP进行分组计算：SELECT column1, column2, SUM(column3) FROM table_name GROUP BY column1, column2 WITH ROLLUP; 使用INNER JOIN和GROUP BY进行多表分组计算：SELECT table1.column_name, SUM(table2.column_name) FROM table1 INNER JOIN table2 ON table1.id = table2.id GROUP BY table1.column_name; 使用ORDER BY和ROW_NUMBER进行分页：SELECT * FROM (SELECT column1, column2, ROW_NUMBER() OVER (ORDER BY column1 DESC) AS row_num FROM table_name) t WHERE row_num BETWEEN 10 AND 20; 使用INSERT INTO SELECT进行数据复制：INSERT INTO table1 (column1, column2, ...) SELECT column1, column2, ... FROM table2 WHERE condition; 使用INNER JOIN和COUNT DISTINCT进行多表去重计数：SELECT COUNT(DISTINCT table1.column_name) FROM table1 INNER JOIN table2 ON table1.id = table2.id WHERE condition; 使用PIVOT进行数据透视：SELECT * FROM (SELECT column1, column2, column3 FROM table_name) t PIVOT (SUM(column3) FOR column2 IN ('value1', 'value2', 'value3')) p; 使用COALESCE和LAG/LEAD函数进行比较：SELECT column_name, column_name2, COALESCE(column_name2 - LAG(column_name2 ``` - **优化SQL** https://mp.weixin.qq.com/s/nFCzzE7nrJWRZ1hH43PjCg https://mp.weixin.qq.com/s/rSIi_xHCYUyDj22m261nQw