mysql常用知识

1. MySQL 基础知识

1.1 数据库基本概念

• 数据库：存储数据的仓库
• 表：数据以表格形式组织，由行和列组成
• 字段：表的列，表示数据的属性
• 记录：表的行，表示一条完整的数据

1.2 常用数据类型

• 整数：INT, BIGINT, SMALLINT
• 小数：DECIMAL, FLOAT, DOUBLE
• 字符串：CHAR, VARCHAR, TEXT
• 日期时间：DATE, TIME, DATETIME, TIMESTAMP
• 布尔：BOOLEAN (实际上是TINYINT(1))

2. 创建示例数据库和表

下面我将创建一个简单的学生管理系统，包含学生表、课程表和成绩表。

-- 创建数据库
CREATE DATABASE IF NOT EXISTS school_management;
USE school_management;

-- 创建学生表
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女') NOT NULL,
    birth_date DATE,
    email VARCHAR(100),
    class VARCHAR(20),
    enrollment_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建课程表
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    credit DECIMAL(3,1) NOT NULL,
    teacher VARCHAR(50),
    course_hours INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建成绩表
CREATE TABLE scores (
    score_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT NOT NULL,
    course_id INT NOT NULL,
    score DECIMAL(5,2) CHECK (score >= 0 AND score <= 100),
    exam_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
    UNIQUE KEY unique_student_course (student_id, course_id, exam_date)
);

3. 插入测试数据

-- 插入学生数据
INSERT INTO students (name, gender, birth_date, email, class, enrollment_date) VALUES
('张三', '男', '2000-03-15', 'zhangsan@example.com', '计算机1班', '2018-09-01'),
('李四', '女', '2001-05-20', 'lisi@example.com', '计算机1班', '2018-09-01'),
('王五', '男', '2000-11-08', 'wangwu@example.com', '计算机2班', '2018-09-01'),
('赵六', '女', '2001-02-14', 'zhaoliu@example.com', '计算机2班', '2018-09-01'),
('钱七', '男', '2000-07-30', 'qianqi@example.com', '计算机1班', '2018-09-01');

-- 插入课程数据
INSERT INTO courses (course_name, credit, teacher, course_hours) VALUES
('数据库原理', 3.0, '王教授', 64),
('数据结构', 4.0, '李教授', 72),
('操作系统', 3.5, '张教授', 68),
('计算机网络', 3.0, '刘教授', 64),
('软件工程', 2.5, '陈教授', 48);

-- 插入成绩数据
INSERT INTO scores (student_id, course_id, score, exam_date) VALUES
(1, 1, 85.5, '2023-01-15'),
(1, 2, 92.0, '2023-01-20'),
(1, 3, 78.5, '2023-01-18'),
(2, 1, 90.0, '2023-01-15'),
(2, 2, 88.5, '2023-01-20'),
(2, 4, 95.0, '2023-01-22'),
(3, 1, 76.0, '2023-01-15'),
(3, 3, 82.5, '2023-01-18'),
(3, 5, 79.0, '2023-01-25'),
(4, 2, 91.5, '2023-01-20'),
(4, 4, 87.0, '2023-01-22'),
(4, 5, 93.5, '2023-01-25'),
(5, 1, 84.0, '2023-01-15'),
(5, 3, 89.5, '2023-01-18'),
(5, 4, 77.0, '2023-01-22');

得到如下3张表和数据:

4. 常用查询示例

4.1 基本查询

-- 查询所有学生
SELECT * FROM students;

-- 查询特定列
SELECT student_id, name, class FROM students;

-- 条件查询
SELECT * FROM students WHERE gender = '女';

-- 排序查询
SELECT * FROM students ORDER BY enrollment_date DESC;

-- 限制结果数量
SELECT * FROM students LIMIT 3;

4.2 连接查询

-- 内连接 查询学生成绩已经课程信息，AS 用于命名key
SELECT st.student_id, st.name, co.course_name, sc.score AS score_num 
FROM
    scores sc
    INNER JOIN students st ON sc.student_id = st.student_id
    INNER JOIN courses co ON sc.course_id = co.course_id
LIMIT 100

-- 左连接：查询所有学生及其成绩（包括没有成绩的学生）
SELECT s.name, c.course_name, sc.score
FROM students s
LEFT JOIN scores sc ON s.student_id = sc.student_id
LEFT JOIN courses c ON sc.course_id = c.course_id;

4.3 聚合函数

-- 统计每门课程的平均分、最高分、最低分
SELECT courses.course_name, SUM(scores.score), AVG(scores.score), MAX(scores.score), MIN(scores.score)
FROM courses
    LEFT JOIN scores ON scores.course_id = courses.course_id
GROUP BY
    courses.course_name;

-- 统计每个学生的平均分
SELECT 
    s.name,
    AVG(sc.score) as avg_score,
    COUNT(sc.score) as course_count
FROM students s
LEFT JOIN scores sc ON s.student_id = sc.student_id
GROUP BY s.student_id, s.name;

4.4 子查询

-- 查询高于平均分的学生成绩
SELECT s.name, c.course_name, sc.score
FROM scores sc
JOIN students s ON sc.student_id = s.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.score > (SELECT AVG(score) FROM scores);

-- 查询没有成绩的学生
SELECT name
FROM students
WHERE student_id NOT IN (SELECT DISTINCT student_id FROM scores);

5. 数据更新和删除操作

-- 更新数据
UPDATE students SET email = 'new_email@example.com' WHERE student_id = 1;

-- 删除数据
DELETE FROM scores WHERE score < 60;

-- 注意：在实际应用中，删除操作要非常小心，通常建议使用软删除（添加is_deleted字段）

6. 索引优化

-- 为常用查询字段创建索引
CREATE INDEX idx_student_name ON students(name);
CREATE INDEX idx_score_student_course ON scores(student_id, course_id);
CREATE INDEX idx_score_date ON scores(exam_date);