一、基础查询

1、基本查询

SELECT ：查询所有列。SELECT * FROM table_name;

SELECT 列1, 列2, ... : 查询指定列。SELECT column1, column2 FROM table_name;

SELECT DISTINCT 列名: 去重查询。SELECT DISTINCT column_name FROM table_name;

2、条件查询

WHERE 子句 ：用于筛选满足特定条件的记录。SELECT * FROM table_name WHERE condition;

比较运算符 ：=,!=,<,>,<=,>=。SELECT * FROM table_name WHERE age > 30;

逻辑运算符 ：AND,OR,NOT。SELECT * FROM table_name WHERE age > 30 AND gender = 'male';

模糊查询 ：使用LIKE，% 表示任意多个字符，_ 表示一个字符。SELECT * FROM table_name WHERE name LIKE 'J%';

3、排序与分页

ORDER BY 子句 ：按指定列排序。SELECT * FROM table_name ORDER BY column_name ASC;（升序），DESC（降序）

LIMIT 子句 ：限制返回的行数。SELECT * FROM table_name LIMIT 10; 或SELECT * FROM table_name LIMIT 10 OFFSET 5;（分页查询）

4、聚合函数

COUNT() ：计算行数。SELECT COUNT(*) FROM table_name;

SUM() ：计算总和。SELECT SUM(column_name) FROM table_name;

AVG() ：计算平均值。SELECT AVG(column_name) FROM table_name;

MAX() ：最大值。SELECT MAX(column_name) FROM table_name;

MIN() ：最小值。SELECT MIN(column_name) FROM table_name;

5、分组查询

GROUP BY 子句 ：按列分组。SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

HAVING 子句 ：过滤分组结果。SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1;

二、高级查询

1、联结查询

INNER JOIN ：内连接。SELECT a.column1, b.column2 FROM table1 AS a INNER JOIN table2 AS b ON a.common_field = b.common_field;

LEFT JOIN ：左连接。SELECT a.column1, b.column2 FROM table1 AS a LEFT JOIN table2 AS b ON a.common_field = b.common_field;

RIGHT JOIN ：右连接。SELECT a.column1, b.column2 FROM table1 AS a RIGHT JOIN table2 AS b ON a.common_field = b.common_field;

FULL OUTER JOIN ：全外连接（MySQL中通过UNION实现）。

     SELECT a.*, b.* FROM table1 AS a
     LEFT JOIN table2 AS b ON a.common_field = b.common_field
     UNION
     SELECT a.*, b.* FROM table1 AS a
     RIGHT JOIN table2 AS b ON a.common_field = b.common_field;

CROSS JOIN ：交叉连接。SELECT a.column1, b.column2 FROM table1 AS a CROSS JOIN table2 AS b;

2、子查询

嵌套子查询 ：在一个查询中嵌入另一个查询。

     SELECT column1 FROM table1 WHERE column2 IN (SELECT column3 FROM table2);

相关子查询 ：内外层查询相关联。

     SELECT e1.employee_id, e1.salary
     FROM employees AS e1
     WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees AS e2 WHERE e2.department_id = e1.department_id);

3、组合查询

UNION ：合并多个查询结果，去除重复。SELECT column1 FROM table1 UNION SELECT column1 FROM table2;

UNION ALL ：合并多个查询结果，包括重复。SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;

4、插入、更新与删除

INSERT INTO ：插入新记录。INSERT INTO table_name (column1, column2) VALUES (value1, value2);

UPDATE ：更新现有记录。UPDATE table_name SET column1 = value1 WHERE condition;

DELETE ：删除记录。DELETE FROM table_name WHERE condition;

掌握这些MySQL查询语句，可以帮助你有效地操作和管理数据库，进行高效的数据检索、插入、更新和删除操作，希望这篇《MySQL查询语句大全》能为你提供有价值的参考和帮助！