第5课: 数据查询基础

SELECT、WHERE、ORDER BY 完整指南

基础查询 (SELECT)

SELECT 是 SQL 中最常用的语句，用于从数据库中检索数据。

SQL查询执行流程示意图

-- 示例1: 查询所有字段
SELECT * FROM users;

-- 示例2: 查询指定字段
SELECT username, email, age FROM users;

-- 示例3: 使用别名
SELECT
    username AS 用户名,
    email AS 邮箱,
    age AS 年龄
FROM users;

条件查询 (WHERE)

WHERE 子句用于过滤记录，只返回满足条件的数据。

-- 示例4: 基本条件查询
SELECT * FROM users WHERE age > 18;

-- 示例5: 多条件查询（AND）
SELECT * FROM users
WHERE age >= 18 AND age <= 30;

-- 示例6: 多条件查询（OR）
SELECT * FROM products
WHERE category = '电子产品' OR category = '家电';

-- 示例7: LIKE 模糊查询
SELECT * FROM users WHERE username LIKE '张%';  -- 以"张"开头
SELECT * FROM users WHERE email LIKE '%@gmail.com';  -- 以@gmail.com结尾
SELECT * FROM users WHERE username LIKE '%明%';  -- 包含"明"

-- 示例8: IN 查询
SELECT * FROM products
WHERE category IN ('手机', '电脑', '平板');

-- 示例9: BETWEEN 范围查询
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

排序 (ORDER BY)

ORDER BY 用于对查询结果进行排序。

-- 示例10: 升序排序（默认）
SELECT * FROM products ORDER BY price ASC;

-- 示例11: 降序排序
SELECT * FROM users ORDER BY created_at DESC;

-- 示例12: 多字段排序
SELECT * FROM products
ORDER BY category ASC, price DESC;

限制结果数量 (LIMIT)

-- 查询前10条记录
SELECT * FROM users LIMIT 10;

-- 分页查询：跳过前20条，取10条
SELECT * FROM products LIMIT 20, 10;

-- 等价写法
SELECT * FROM products LIMIT 10 OFFSET 20;

去重查询 (DISTINCT)

-- 查询所有不重复的分类
SELECT DISTINCT category FROM products;

-- 查询不重复的城市和省份组合
SELECT DISTINCT city, province FROM users;

查询优化技巧：

避免 SELECT *：只查询需要的字段，减少数据传输
使用索引：WHERE 条件中的字段应建立索引
LIMIT 分页：大数据量时使用 LIMIT 限制返回行数
避免前导通配符：LIKE '%abc' 无法使用索引
使用 BETWEEN：比多个 OR 条件效率更高
合理使用 DISTINCT：去重操作消耗资源，确保必要时使用

NULL 值处理

-- 查询 NULL 值
SELECT * FROM users WHERE phone IS NULL;

-- 查询非 NULL 值
SELECT * FROM users WHERE phone IS NOT NULL;

-- 使用 COALESCE 处理 NULL
SELECT username, COALESCE(phone, '未填写') AS phone FROM users;

实战场景：电商商品查询

-- 查询价格在100-500之间的手机，按价格降序
SELECT product_name, price, stock
FROM products
WHERE category = '手机'
  AND price BETWEEN 100 AND 500
  AND stock > 0
ORDER BY price DESC
LIMIT 20;

-- 查询本月新注册的用户
SELECT username, email, created_at
FROM users
WHERE created_at >= DATE_FORMAT(NOW(), '%Y-%m-01')
ORDER BY created_at DESC;

-- 查询热门商品（销量前10）
SELECT product_name, sales_count, price
FROM products
WHERE status = 'active'
ORDER BY sales_count DESC
LIMIT 10;

练习题：

查询所有年龄大于20岁的用户的姓名和邮箱
查询价格在50到200之间的所有商品
查询用户名包含"李"字的所有用户
查询分类为"图书"或"文具"的商品，按价格升序排列
查询最近注册的20个用户（按注册时间降序）
查询库存为0的商品数量（使用COUNT）
查询所有不同的商品分类
查询手机号为空的用户

项目案例：学生管理系统

基于所学的基础查询知识，设计并实现一个简单的学生管理系统。

-- 1. 创建数据库和表结构
CREATE DATABASE student_management;
USE student_management;

-- 创建学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    birth_date DATE,
    class_id INT,
    email VARCHAR(100),
    phone VARCHAR(20),
    address VARCHAR(200),
    enrollment_date DATE NOT NULL,
    status ENUM('在读', '毕业', '休学') DEFAULT '在读'
);

-- 创建班级表
CREATE TABLE classes (
    class_id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(50) NOT NULL,
    department VARCHAR(100) NOT NULL,
    grade VARCHAR(20) NOT NULL,
    instructor VARCHAR(50)
);

-- 创建课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    credit INT NOT NULL,
    department VARCHAR(100) NOT NULL,
    instructor VARCHAR(50)
);

-- 创建成绩表
CREATE TABLE grades (
    grade_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score DECIMAL(5,2) NOT NULL,
    exam_date DATE NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 2. 插入示例数据
INSERT INTO classes (class_name, department, grade, instructor) VALUES
('计算机科学与技术1班', '计算机学院', '2021级', '王老师'),
('计算机科学与技术2班', '计算机学院', '2021级', '李老师'),
('软件工程1班', '计算机学院', '2021级', '张老师');

INSERT INTO students (student_name, gender, birth_date, class_id, email, phone, address, enrollment_date, status) VALUES
('张三', '男', '2003-01-15', 1, 'zhangsan@example.com', '13800138001', '北京市海淀区', '2021-09-01', '在读'),
('李四', '女', '2003-02-20', 1, 'lisi@example.com', '13800138002', '北京市朝阳区', '2021-09-01', '在读'),
('王五', '男', '2003-03-10', 2, 'wangwu@example.com', '13800138003', '北京市西城区', '2021-09-01', '在读'),
('赵六', '女', '2003-04-05', 3, 'zhaoliu@example.com', '13800138004', '北京市东城区', '2021-09-01', '在读');

INSERT INTO courses (course_name, credit, department, instructor) VALUES
('高等数学', 4, '基础部', '刘老师'),
('大学英语', 3, '基础部', '陈老师'),
('数据结构', 4, '计算机学院', '王老师'),
('数据库原理', 3, '计算机学院', '李老师'),
('操作系统', 4, '计算机学院', '张老师');

INSERT INTO grades (student_id, course_id, score, exam_date) VALUES
(1, 1, 85.5, '2022-01-10'),
(1, 2, 90.0, '2022-01-11'),
(1, 3, 88.5, '2022-01-12'),
(2, 1, 78.0, '2022-01-10'),
(2, 2, 85.5, '2022-01-11'),
(2, 3, 92.0, '2022-01-12'),
(3, 1, 95.0, '2022-01-10'),
(3, 2, 82.0, '2022-01-11'),
(3, 3, 89.5, '2022-01-12'),
(4, 1, 88.0, '2022-01-10'),
(4, 2, 91.5, '2022-01-11'),
(4, 3, 86.0, '2022-01-12');

-- 3. 实战查询练习

-- 3.1 基本查询
-- 查询所有学生的基本信息
SELECT * FROM students;

-- 查询所有课程的名称和学分
SELECT course_name, credit FROM courses;

-- 3.2 条件查询
-- 查询计算机学院的所有课程
SELECT * FROM courses WHERE department = '计算机学院';

-- 查询成绩大于90分的学生和课程
SELECT s.student_name, c.course_name, g.score
FROM grades g
JOIN students s ON g.student_id = s.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE g.score > 90;

-- 3.3 排序查询
-- 按照成绩从高到低查询学生的高等数学成绩
SELECT s.student_name, g.score
FROM grades g
JOIN students s ON g.student_id = s.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE c.course_name = '高等数学'
ORDER BY g.score DESC;

-- 3.4 聚合查询
-- 计算每个学生的平均成绩
SELECT s.student_name, AVG(g.score) AS avg_score
FROM grades g
JOIN students s ON g.student_id = s.student_id
GROUP BY s.student_id, s.student_name
ORDER BY avg_score DESC;

-- 计算每门课程的平均成绩
SELECT c.course_name, AVG(g.score) AS avg_score
FROM grades g
JOIN courses c ON g.course_id = c.course_id
GROUP BY c.course_id, c.course_name
ORDER BY avg_score DESC;

-- 3.5 复杂查询
-- 查询每个班级的学生人数和平均成绩
SELECT cl.class_name, COUNT(s.student_id) AS student_count, AVG(g.score) AS avg_score
FROM classes cl
LEFT JOIN students s ON cl.class_id = s.class_id
LEFT JOIN grades g ON s.student_id = g.student_id
GROUP BY cl.class_id, cl.class_name
ORDER BY student_count DESC, avg_score DESC;

-- 查询不及格（<60分）的学生和课程
SELECT s.student_name, c.course_name, g.score
FROM grades g
JOIN students s ON g.student_id = s.student_id
JOIN courses c ON g.course_id = c.course_id
WHERE g.score < 60
ORDER BY s.student_name, c.course_name;

-- 4. 项目扩展
-- 4.1 添加索引优化查询性能
CREATE INDEX idx_students_class_id ON students(class_id);
CREATE INDEX idx_grades_student_id ON grades(student_id);
CREATE INDEX idx_grades_course_id ON grades(course_id);
CREATE INDEX idx_grades_score ON grades(score);

-- 4.2 创建视图简化复杂查询
CREATE VIEW view_student_grades AS
SELECT
    s.student_id,
    s.student_name,
    s.gender,
    cl.class_name,
    c.course_name,
    g.score,
    g.exam_date
FROM grades g
JOIN students s ON g.student_id = s.student_id
JOIN classes cl ON s.class_id = cl.class_id
JOIN courses c ON g.course_id = c.course_id;

-- 使用视图查询
SELECT * FROM view_student_grades WHERE score < 60;
SELECT student_name, AVG(score) AS avg_score FROM view_student_grades GROUP BY student_id, student_name;

项目要求：

创建完整的学生管理系统数据库结构
插入合理的示例数据
实现上述所有查询功能
添加至少3个额外的查询需求并实现
为数据库添加适当的索引优化性能
创建至少2个视图简化复杂查询
编写一个简单的应用程序连接数据库并展示数据

上一课下一课