MYSQLDQL命令

MYSQLDQL命令

1. 课程基础与数据库表结构

1.1 课程目标总览

1.2 MySchool数据库表结构（核心业务表）

2. DQL核心语法与基础查询

2.1 DQL定义

DQL（Data Query Language，数据查询语言）：用于查询数据库数据，是数据库语言中最核心、使用频率最高的语句，支持单表查询、多表复杂查询、嵌套查询。

2.2 SELECT基础语法（完整结构）

SELECT [ALL | DISTINCT]
{ * | table.* | [table.field1 [AS alias1], table.field2 [AS alias2], ...] }
FROM table_name [AS table_alias]
    [LEFT/RIGHT/INNER JOIN table_name2 ON 关联条件]  -- 多表查询
    [WHERE 行过滤条件]                                -- 过滤行数据
    [GROUP BY 分组字段]                               -- 分组
    [HAVING 分组过滤条件]                             -- 过滤分组结果
    [ORDER BY 排序字段 [ASC/DESC]]                    -- 排序
    [LIMIT {[offset, ] row_count | row_count OFFSET offset}] ;  -- 分页

• []：可选内容；{}：必选内容；#或/* */：注释

2.3 基础查询操作

2.3.1 指定查询字段

2.3.2 AS子句（别名）

• 作用：给字段/表取别名，简化语法；重命名计算/统计结果列。
• 用法：AS可省略，直接用空格分隔。

  -- 字段别名（中文需加引号）
  SELECT StudentNo AS "学号", StudentName AS 姓名 FROM student;
  -- 表别名（简化多表查询）
  SELECT a.StudentNo FROM student AS a;
  -- 计算结果别名
  SELECT ClassHour + 10 AS "新课时" FROM subject;  -- 课时加10后重命名
  

2.3.3 DISTINCT去重

• 作用：去除查询结果中所有返回列值完全相同的重复记录，只保留1条。
• 语法：SELECT DISTINCT 字段1, 字段2... FROM 表名;
• 示例：查询成绩表中涉及的所有课程ID（去重）

  SELECT DISTINCT SubjectNo FROM result;  -- 避免重复课程ID
  

• 注意：ALL是默认值，返回所有记录（包括重复），与DISTINCT相反。

2.3.4 表达式列

• 组成：文本值、列值、NULL、函数、操作符（如+、-、*、/）。
• 应用场景：SELECT结果列、ORDER BY、HAVING、WHERE条件。
• 示例：

  -- 返回MySQL版本和计算结果
  SELECT version() AS MySQL版本, 100 * 3 AS 计算结果;
  -- 课程课时加10，重命名避免符号干扰开发程序
  SELECT SubjectName "课程名称", ClassHour + 10 AS "新学时" FROM subject;
  

3. 条件查询（WHERE子句）

3.1 WHERE子句作用

筛选符合条件的行数据，条件由“逻辑操作符+比较操作符”组成，结果为“真/假”。

3.2 逻辑操作符

3.3 比较操作符

3.4 关键注意点

1. 数值类型字段才能进行算术运算（如StudentResult + 5）；
2. 相同数据类型才能比较（如字符串不能和数值比较）；
3. NULL≠0≠空字符串""，判断NULL必须用IS NULL/IS NOT NULL，不能用=。

4. 连接查询（多表查询）

4.1 连接查询分类

用于获取多张表的关联数据，核心是通过“外键”建立表间关联（如student.GradeID = grade.GradeID）。

4.2 内连接（INNER JOIN）

• 逻辑：只返回两张表中匹配成功的记录（无匹配的记录不显示）。
• 语法：INNER JOIN与JOIN等效。

  SELECT 字段1, 字段2... FROM table1
  INNER JOIN table2 ON table1.关联字段 = table2.关联字段;
  

• 示例：查询课程名及所属年级名（关联subject和grade）

  -- 内连接方式
  SELECT s.SubjectName, g.GradeName 
  FROM subject AS s
  INNER JOIN grade AS g ON s.GradeID = g.GradeID;
  
  -- 等值连接（等效于内连接，逗号分隔表，WHERE写关联条件）
  SELECT s.SubjectName, g.GradeName 
  FROM subject AS s, grade AS g
  WHERE s.GradeID = g.GradeID;
  
  -- 非等值连接（无关联条件，返回两表记录数乘积，慎用）
  SELECT s.SubjectName, g.GradeName FROM subject AS s, grade AS g;
  

4.3 外连接（OUTER JOIN）

• 示例：查询所有学生及成绩（无成绩的学生也显示）

  SELECT s.StudentNo, s.StudentName, r.StudentResult
  FROM student AS s
  LEFT JOIN result AS r ON s.StudentNo = r.StudentNo;
  

4.4 自连接

• 逻辑：将一张表视为两张表，通过别名区分，实现表内数据关联查询（如查询父子栏目）。
• 示例：查询栏目表（category）的父栏目与子栏目名称

  -- 1. 创建栏目表
  CREATE TABLE IF NOT EXISTS category(
      categoryId INT(10) AUTO_INCREMENT PRIMARY KEY,  -- 栏目ID
      categoryName VARCHAR(32) NOT NULL,              -- 栏目名称
      pid INT(10)                                     -- 父栏目ID（关联categoryId）
  );
  
  -- 2. 自连接查询（a为子栏目表，b为父栏目表）
  SELECT a.categoryName AS "子栏目", b.categoryName AS "父栏目"
  FROM category AS a
  LEFT JOIN category AS b ON a.pid = b.categoryId;
  

4.5 不同JOIN对比

5. 结果排序与分页

5.1 ORDER BY排序

• 作用：对查询结果按指定字段排序。
• 语法：ORDER BY 字段1 [ASC/DESC], 字段2 [ASC/DESC]
  • ASC：升序（默认，如1→10，A→Z）；DESC：降序（如10→1，Z→A）。
• 示例：查询《数据库结构-1》的考试结果，按成绩降序排列

  SELECT r.StudentNo, r.StudentResult
  FROM result AS r
  INNER JOIN subject AS s ON r.SubjectNo = s.SubjectNo
  WHERE s.SubjectName = "数据库结构-1"
  ORDER BY r.StudentResult DESC;
  

5.2 LIMIT分页

• 作用：限制查询结果的行数，常用于分页显示（如第1页显示5条，第2页显示6-10条）。
• 语法：两种形式等效
  1. LIMIT [offset, row_count]：offset为起始偏移量（从0开始），row_count为返回行数；
  2. LIMIT row_count OFFSET offset。
• 示例：

  SELECT * FROM result LIMIT 5;          -- 返回前5条记录（offset=0，默认）
  SELECT * FROM result LIMIT 5, 10;      -- 返回第6-15条记录（offset=5，row_count=10）
  SELECT * FROM result LIMIT 10 OFFSET 5;-- 与上句等效
  

6. 子查询

6.1 定义

子查询：在WHERE条件子句中嵌套另一个查询语句，由里及外求解，子查询结果通常为集合（建议用IN匹配）。

6.2 示例

查询“《高等数学-2》且分数≥80分”的学生学号和姓名：

-- 1. 先查《高等数学-2》的课程ID（子查询）
-- 2. 再查该课程中分数≥80的学生ID，关联学生表获取姓名
SELECT s.StudentNo, s.StudentName
FROM student AS s
INNER JOIN result AS r ON s.StudentNo = r.StudentNo
WHERE r.SubjectNo = (SELECT SubjectNo FROM subject WHERE SubjectName = "高等数学-2")
  AND r.StudentResult >= 80;

6.3 注意点

• 子查询可多层嵌套；
• 若子查询返回多个结果，需用IN代替=（如查询多门课程的高分学生）。

7. 聚合函数与分组查询

7.1 聚合函数（统计函数）

对数据进行统计计算，常用函数如下：

• 注意：COUNT(*)效率较低，建议用COUNT(主键字段)（如COUNT(StudentNo)）。

7.2 GROUP BY分组查询

• 作用：按指定字段对结果分组，对每组进行聚合计算（如按课程分组算平均分）。
• 语法：GROUP BY 分组字段1, 分组字段2... [HAVING 分组条件]
• 关键区别：
  • WHERE：分组前过滤行数据（不能用聚合函数）；
  • HAVING：分组后过滤分组结果（可使用聚合函数）。

7.3 示例

按课程分组，计算每门课程的平均分、最高分、最低分，且只显示平均分≥60的课程：

SELECT s.SubjectName, 
       AVG(r.StudentResult) AS 平均成绩,
       MAX(r.StudentResult) AS 最高分,
       MIN(r.StudentResult) AS 最低分
FROM subject AS s
INNER JOIN result AS r ON s.SubjectNo = r.SubjectNo
GROUP BY s.SubjectNo, s.SubjectName  -- 分组字段需包含非聚合字段
HAVING AVG(r.StudentResult) >= 60;   -- 过滤平均分≥60的分组