- **常用查询**
```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

ITPub博客

常用的SQL查询语句

最新文章