数据库知识要点

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

-- 创建数据库
CREATE DATABASE school;

-- 使用数据库
USE school;

数据类型
数值类型：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
);

基本表操作
创建表：使用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;

插入数据
使用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');

查询数据
基本查询：使用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;

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

-- 更新学生成绩
UPDATE students SET score = 88.50 WHERE name = '张三';

-- 批量更新，将所有学生年龄增加1岁
UPDATE students SET age = age + 1;

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

-- 删除指定学生
DELETE FROM students WHERE name = '李四';

-- 删除所有记录（不删除表结构）
DELETE FROM students;

常用函数
字符串函数：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;

联合查询（JOIN）
内连接（INNER JOIN）：返回两个表中满足条件的交集。
左连接（LEFT JOIN）：返回左表的所有记录，即使右表没有匹配。
右连接（RIGHT JOIN）：返回右表的所有记录，即使左表没有匹配。

-- 创建成绩表
CREATE TABLE scores (
 student_id INT,
 subject VARCHAR(20),
 score DECIMAL(5, 2)
);

-- 插入成绩数据
INSERT INTO scores (student_id, subject, score)
VALUES (1, '数学', 90), (1, '英语', 85), (2, '数学', 88);

-- 内连接查询学生及其成绩
SELECT s.name, sc.subject, sc.score
FROM students s
INNER JOIN scores sc ON s.id = sc.student_id;

索引
定义：索引用于提高查询效率，可以在表的列上创建索引。
类型：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;

视图
定义：视图是基于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;

存储过程
定义：存储过程是存储在数据库中的一组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);