数据库知识要点

1. MySQL简介
   MySQL是一个关系型数据库管理系统（RDBMS），以其高效、易用、开源等优点广泛应用于Web开发、数据存储和管理等领域。

2. 数据库的基本概念
   数据库：数据的有序集合，可以看作一个电子化的文件柜。
   表：数据库的基本存储结构，由行（记录）和列（字段）组成。
   字段：表的列，每一列有一个名称，指定了数据的类型。
   记录：表中的一行数据。

   -- 创建数据库
   CREATE DATABASE school;
   
   -- 使用数据库
   USE school;
   

3. 数据类型
   数值类型：INT, FLOAT, DOUBLE, DECIMAL等。
   字符类型：CHAR, VARCHAR, TEXT等。
   日期和时间类型：DATE, TIME, DATETIME, TIMESTAMP等。
   布尔类型：BOOLEAN，通常用TINYINT(1)实现。

   -- 创建学生表，包含不同数据类型
   CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    score DECIMAL(5, 2),
    birth_date DATE
   );

4. 基本表操作
   创建表：使用CREATE TABLE语句。
   修改表：使用ALTER TABLE语句，可以增加、删除、修改字段。
   删除表：使用DROP TABLE语句删除整个表结构和数据。

   -- 添加新字段
   ALTER TABLE students ADD COLUMN email VARCHAR(100);
   
   -- 修改字段
   ALTER TABLE students MODIFY COLUMN age TINYINT;
   
   -- 删除字段
   ALTER TABLE students DROP COLUMN email;
   
   -- 删除表
   DROP TABLE students;
   

5. 插入数据
   使用INSERT INTO语句将数据插入到表中，可以插入单条或多条记录。

   -- 插入单条记录
   INSERT INTO students (name, age, score, birth_date)
   VALUES ('张三', 18, 85.50, '2005-09-01');
   
   -- 插入多条记录
   INSERT INTO students (name, age, score, birth_date)
   VALUES 
    ('李四', 17, 90.00, '2006-08-15'),
    ('王五', 18, 78.00, '2005-07-20');
   

6. 查询数据
   基本查询：使用SELECT语句查询表中的数据。
   条件查询：WHERE子句用于筛选记录。
   排序：ORDER BY用于按指定列排序。
   分组：GROUP BY用于对查询结果进行分组。
   分页：LIMIT用于限制查询结果的数量。

   -- 查询所有学生
   SELECT * FROM students;
   
   -- 条件查询，查找年龄大于17的学生
   SELECT * FROM students WHERE age > 17;
   
   -- 按成绩降序排序
   SELECT * FROM students ORDER BY score DESC;
   
   -- 分组统计各年龄的学生数量
   SELECT age, COUNT(*) AS count FROM students GROUP BY age;
   
   -- 分页查询，获取前两条记录
   SELECT * FROM students LIMIT 2;
   

7. 更新数据
   使用UPDATE语句更新表中的数据，需要结合WHERE子句指定更新条件。

   -- 更新学生成绩
   UPDATE students SET score = 88.50 WHERE name = '张三';
   
   -- 批量更新，将所有学生年龄增加1岁
   UPDATE students SET age = age + 1;
   

8. 删除数据
   使用DELETE语句删除表中的记录，谨慎使用WHERE子句，否则会删除整张表的数据。

   -- 删除指定学生
   DELETE FROM students WHERE name = '李四';
   
   -- 删除所有记录（不删除表结构）
   DELETE FROM students;
   

9. 常用函数
   字符串函数：CONCAT(), UPPER(), LOWER(), SUBSTRING()等。
   数学函数：ABS(), ROUND(), CEIL(), FLOOR()等。
   日期函数：NOW(), CURDATE(), DATE_ADD(), DATEDIFF()等。
   聚合函数：COUNT(), SUM(), AVG(), MIN(), MAX()等。

   -- 使用字符串函数将姓名拼接
   SELECT CONCAT(name, '同学') AS new_name FROM students;
   
   -- 数学函数，取绝对值
   SELECT ABS(-10) AS absolute_value;
   
   -- 日期函数，计算两个日期之间的天数
   SELECT DATEDIFF('2024-09-01', '2024-08-01') AS days_diff;
   
   -- 聚合函数，计算平均成绩
   SELECT AVG(score) AS avg_score FROM students;
   

10. 联合查询（JOIN）
    在关系型数据库中，连表查询（Join）是用来从多个表中提取数据的操作。主要的连表方式有三种：内连接（INNER JOIN）、左连接（LEFT JOIN） 和 右连接（RIGHT JOIN）。每种连接方式的区别在于它们对匹配和不匹配数据的处理方式。

区别

1. 内连接（INNER JOIN）

   • 只返回两个表中符合连接条件的匹配记录。
   • 如果没有匹配的记录，结果中不会包含这些记录。

2. 左连接（LEFT JOIN）

   • 返回左表的所有记录，以及左表中每条记录对应的右表的匹配记录。
   • 如果左表中的记录在右表中没有匹配，结果中该记录的右表字段为 NULL。

3. 右连接（RIGHT JOIN）

   • 返回右表的所有记录，以及右表中每条记录对应的左表的匹配记录。
   • 如果右表中的记录在左表中没有匹配，结果中该记录的左表字段为 NULL。

示例表格

表 1: students（学生信息表）

表 2: classes（班级信息表）

查询及结果

1. 内连接（INNER JOIN）

SELECT students.name, classes.class_name
FROM students
INNER JOIN classes
ON students.class_id = classes.class_id;

结果：

说明：

• 仅返回 students.class_id 和 classes.class_id 匹配的记录。
• Charlie 和 David 的记录因为没有匹配的 class_id，被排除。

2. 左连接（LEFT JOIN）

SELECT students.name, classes.class_name
FROM students
LEFT JOIN classes
ON students.class_id = classes.class_id;

结果：

说明：

• 返回 students 表的所有记录。
• Charlie 和 David 的 class_name 为 NULL，因为没有匹配的 class_id。

3. 右连接（RIGHT JOIN）

SELECT students.name, classes.class_name
FROM students
RIGHT JOIN classes
ON students.class_id = classes.class_id;

结果：

说明：

• 返回 classes 表的所有记录。
• class_id = 104 的记录没有匹配的学生，name 为 NULL。

总结表格

这种不同的连接方式根据查询需求使用，可以灵活提取数据。

11. 索引
    定义：索引用于提高查询效率，可以在表的列上创建索引。
    类型：PRIMARY KEY（主键索引）、UNIQUE（唯一索引）、INDEX（普通索引）。
    创建与删除索引：使用CREATE INDEX和DROP INDEX语句。

    -- 创建索引
    CREATE INDEX idx_name ON students (name);
    
    -- 查询使用索引
    SELECT * FROM students WHERE name = '张三';
    
    -- 删除索引
    DROP INDEX idx_name ON students;
    

12. 视图
    定义：视图是基于SQL查询结果的虚拟表，不存储实际数据。
    优点：简化复杂查询、提高数据安全性。
    创建与删除视图：使用CREATE VIEW和DROP VIEW语句。

    -- 创建视图
    CREATE VIEW student_view AS
    SELECT name, age FROM students WHERE age >= 18;
    
    -- 查询视图
    SELECT * FROM student_view;
    
    -- 删除视图
    DROP VIEW student_view;
    

13. 存储过程
    定义：存储过程是存储在数据库中的一组SQL语句，可以接收参数并执行复杂的逻辑。
    优点：提高代码重用性、封装逻辑、减少与数据库的交互次数。
    创建与调用存储过程：使用CREATE PROCEDURE和CALL语句。

    -- 创建存储过程
    DELIMITER //
    CREATE PROCEDURE GetStudentByAge(IN min_age INT)
    BEGIN
    SELECT * FROM students WHERE age >= min_age;
    END //
    DELIMITER ;
    
    -- 调用存储过程
    CALL GetStudentByAge(18);