MySQL常用SQL语句总结包含复杂SQL查询

MySQL是世界上最流行的开源关系型数据库管理系统之一，其SQL语句是进行数据操作的基础。本篇文章主要总结了MySQL中的一些常用SQL语句，包括复杂查询，以帮助读者更好地理解和运用。 1. 单表查询 - (1) 选择指定列：`SELECT column_name AS alias FROM table_name;` - (2) 查询所有列：`SELECT * FROM table_name;` - (3) 列名重命名：`SELECT column_name AS alias, (expression) AS alias FROM table_name;` - (4) 去除重复行：`SELECT DISTINCT column_name FROM table_name;` - (5) 条件查询：`SELECT column_name FROM table_name WHERE condition;` 2. 大小比较 - 查询满足特定条件的记录：`SELECT column_name FROM table_name WHERE column_name operator value;` - 示例：查询Sdept为'IS'的所有学生姓名：`SELECT Sname AS 学生姓名 FROM student WHERE Sdept = 'IS';` 3. 确定范围 - 使用BETWEEN操作符：`SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2;` - 示例：查询年龄在20至23岁之间的学生信息：`SELECT Sname AS 姓名, Sdept AS 系别, Sage AS 年龄 FROM student WHERE Sage BETWEEN 20 AND 23;` 4. IN和NOT IN - IN用于匹配多个值：`SELECT column_name FROM table_name WHERE column_name IN (value1, value2, ...);` - 示例：查询IS或CS系的学生姓名和性别：`SELECT Sname AS 姓名, Ssex AS 性别 FROM student WHERE Sdept IN ('IS', 'CS');` - NOT IN用于排除匹配的值：`SELECT column_name FROM table_name WHERE column_name NOT IN (value1, value2, ...);` - 示例：查询非IS且非MA系的学生姓名和年龄：`SELECT Sname AS 姓名, Sage AS 年龄 FROM student WHERE Sdept NOT IN ('IS', 'MA');` 5. 字符匹配（LIKE % _） - LIKE操作符配合通配符%和_进行模糊匹配： - `%`代表任意数量的字符：`SELECT column_name FROM table_name WHERE column_name LIKE 'pattern%';` - `_`代表单个字符：`SELECT column_name FROM table_name WHERE column_name LIKE 'pattern_';` - 示例：查询所有姓李的学生：`SELECT Sname AS 姓名, Ssex AS 性别 FROM student WHERE Sname LIKE '李%';` 6. 涉及空值的查询 - IS NULL用于查找空值：`SELECT column_name FROM table_name WHERE column_name IS NULL;` - 示例：查询没有先修课的课程：`SELECT Cno AS 课程号, Cname AS 课程名, Cpno FROM course WHERE Cpno IS NULL;` - IS NOT NULL用于查找非空值：`SELECT column_name FROM table_name WHERE column_name IS NOT NULL;` - 示例：查询所有有成绩的学生学号、课程号和成绩：`SELECT Sno AS 学号, Cno AS 课程号, Grade AS 成绩 FROM SC WHERE Grade IS NOT NULL;` 7. 查询结果排序（ORDER BY） - 使用ORDER BY对查询结果进行升序或降序排序：`SELECT column_name FROM table_name ORDER BY column_name ASC|DESC;` - 示例：按成绩降序查询选修3号课程的学生信息：`SELECT Sno AS 学号, Cno AS 课程号, Grade AS 成绩 FROM SC WHERE Cno = '3' ORDER BY Grade DESC;` 以上内容涵盖了MySQL中常见的查询语句，包括基本查询、条件筛选、范围查询、集合操作、模糊匹配、空值处理以及结果排序。通过这些知识点，用户可以有效地从数据库中提取所需信息。在实际应用中，还可以结合其他高级查询技术，如JOIN、子查询、聚合函数等，来实现更复杂的业务需求。