基础知识《MySQL数据库》

一、MySQL基础概述

定义：MySQL是一款开源关系型数据库管理系统（RDBMS），采用SQL语言进行数据操作，支持跨平台运行。官方文档（dev.mysql.com/doc）。

核心特点：

开源免费（社区版）
支持事务处理（ACID兼容）
提供多种存储引擎（InnoDB、MyISAM等）
支持主从复制和集群部署
兼容标准SQL（ANSI SQL 99）

典型应用场景：

Web应用程序（如WordPress、Joomla）
数据仓库
日志记录系统
嵌入式系统

二、安装与配置

安装方法：

Windows：官网下载MSI安装包，图形化向导安装
Linux：sudo apt-get install mysql-server（Ubuntu）
macOS：使用Homebrew安装 brew install mysql

启动服务：

# Linux
sudo systemctl start mysqld# macOS
brew services start mysql

安全初始化：

sudo mysql_secure_installation

登录数据库：

mysql -u root -p

三、数据库基本操作

创建数据库：

CREATE DATABASE school DEFAULT CHARSET=utf8mb4;

选择数据库：

USE school;

删除数据库：

DROP DATABASE school;

查看所有数据库：

SHOW DATABASES;

四、数据表操作

1. 创建表

CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,age TINYINT UNSIGNED,gender ENUM('M','F') DEFAULT 'M',enroll_date DATE
) ENGINE=InnoDB;

常用数据类型：

整型：INT, TINYINT, BIGINT
浮点：FLOAT, DOUBLE, DECIMAL
字符串：VARCHAR(255), CHAR(10), TEXT
日期：DATE, DATETIME, TIMESTAMP
二进制：BLOB, JSON

2. 修改表结构

ALTER TABLE students 
ADD COLUMN email VARCHAR(100) AFTER name;ALTER TABLE students
MODIFY COLUMN age SMALLINT;ALTER TABLE students
DROP COLUMN gender;

3. 删除表

DROP TABLE students;

4. 查看表结构

DESC students;

五、数据操作（CURD）

1. 插入数据

INSERT INTO students (name, age, enroll_date) 
VALUES ('张三', 18, '2023-09-01'),('李四', 19, CURDATE());

2. 查询数据（安全的重中之重）

基础查询：

SELECT * FROM students WHERE age > 18;

条件查询：

SELECT name, age 
FROM students 
WHERE gender = 'F' AND enroll_date BETWEEN '2023-01-01' AND '2023-12-31';

排序与分页：

SELECT * FROM students 
ORDER BY age DESC 
LIMIT 10 OFFSET 20;

聚合函数：

SELECT COUNT(*) AS total, AVG(age) avg_age 
FROM students;

分组统计：

SELECT gender, COUNT(*) 
FROM students 
GROUP BY gender 
HAVING COUNT(*) > 5;

3. 更新数据

UPDATE students 
SET age = age + 1 
WHERE enroll_date < '2023-06-01';

4. 删除数据

DELETE FROM students 
WHERE id = 100;

六、索引管理

1. 创建索引

CREATE INDEX idx_name ON students(name);
CREATE UNIQUE INDEX uni_email ON students(email);

2. 查看索引

SHOW INDEX FROM students;

3. 删除索引

DROP INDEX idx_name ON students;

索引使用原则：

常作为查询条件的列
避免过度索引（影响写性能）
优先使用组合索引

七、多表查询

1. 内连接

SELECT s.name, c.course_name 
FROM students s
INNER JOIN courses c ON s.course_id = c.id;

2. 左外连接

SELECT s.name, c.grade 
FROM students s
LEFT JOIN scores c ON s.id = c.student_id;

3. 子查询

SELECT name 
FROM students 
WHERE age > (SELECT AVG(age) FROM students);

八、事务处理

事务特性（ACID）：

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

事务控制语句：

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- 提交事务
-- ROLLBACK; 回滚事务

隔离级别设置：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

九、用户权限管理

1. 创建用户

CREATE USER 'dev_user'@'localhost' IDENTIFIED BY 'Password123!';

2. 授权

GRANT SELECT, INSERT ON school.* TO 'dev_user'@'localhost';

3. 撤销权限

REVOKE DELETE ON school.* FROM 'dev_user'@'localhost';

4. 查看权限

SHOW GRANTS FOR 'dev_user'@'localhost';

十、存储过程示例

DELIMITER //
CREATE PROCEDURE GetStudentCount(IN min_age INT, OUT total INT)
BEGINSELECT COUNT(*) INTO total FROM students WHERE age >= min_age;
END //
DELIMITER ;-- 调用存储过程
CALL GetStudentCount(18, @count);
SELECT @count;

十一、备份与恢复

逻辑备份：

mysqldump -u root -p school > school_backup.sql

物理备份：直接复制数据目录（需停止服务）

恢复数据：

mysql -u root -p school < school_backup.sql

十二、性能优化建议

使用EXPLAIN分析查询计划

EXPLAIN SELECT * FROM students WHERE age > 18;

避免使用SELECT *
合理使用连接池
定期优化表

OPTIMIZE TABLE students;

配置合适的缓冲池大小（InnoDB Buffer Pool）

重点介绍：sql查询语句

一、基础用法：查询的“骨架”

1. SELECT 基本结构

SELECT 列名1, 列名2 FROM 表名 WHERE 条件;

作用：从表中筛选符合条件的列和行。
案例：查询员工表中所有工资大于5000的员工姓名和部门。

SELECT name, department FROM employees WHERE salary > 5000;

2. 条件过滤（WHERE）

运算符：=, >, <, >=, <=, !=, BETWEEN, IN, LIKE
逻辑组合：AND, OR, NOT
案例：查询财务部或市场部，工资在6000-10000之间的员工。

SELECT * FROM employees 
WHERE (department = '财务部' OR department = '市场部')AND salary BETWEEN 6000 AND 10000;

3. 排序与去重

排序：ORDER BY 列名 ASC/DESC
去重：SELECT DISTINCT 列名
案例：查询不重复的部门名称，并按工资降序显示。

SELECT DISTINCT department FROM employees ORDER BY salary DESC;

二、扩展用法：进阶的“组合技”

1. 聚合函数：统计数据

常用函数：COUNT(), SUM(), AVG(), MAX(), MIN()
案例：统计每个部门的平均工资。

SELECT department, AVG(salary) FROM employees GROUP BY department;

2. 分组与过滤分组（GROUP BY + HAVING）

GROUP BY：按某列分组。
HAVING：对分组后的结果过滤（类似 WHERE，但用于分组）。
案例：查询平均工资大于7000的部门。

SELECT department, AVG(salary) 
FROM employees 
GROUP BY department 
HAVING AVG(salary) > 7000;

3. 多表连接（JOIN）

连接类型：
- INNER JOIN：只返回匹配的行。
- LEFT JOIN：左表全部保留，右表不匹配的补 NULL。
案例：查询员工及其部门信息（员工表 + 部门表）。

SELECT e.name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.id;

4. 子查询：查询嵌套查询

场景：查询结果作为另一个查询的条件或数据源。
案例：查询工资高于公司平均工资的员工。

SELECT name, salary FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

5. 分页查询（LIMIT + OFFSET）

语法：LIMIT 每页数量 OFFSET 跳过行数
案例：查询第2页数据（每页10条）。

SELECT * FROM employees LIMIT 10 OFFSET 10;

三、深层逻辑：数据库的“思考方式”

1. 执行计划（EXPLAIN）

作用：查看 MySQL 如何执行一条查询（相当于查询的“路线图”）。
关键字段：
- type：访问类型（如 ALL 全表扫描，index 索引扫描）。
- key：实际使用的索引。
- rows：预估扫描的行数。
使用方式：

EXPLAIN SELECT * FROM employees WHERE department = '财务部';

2. 索引的底层逻辑

索引类型：
- B+树索引：默认结构，适合范围查询。
- 哈希索引：精确匹配快，不支持范围查询。
索引失效场景：
- 对索引列使用函数（如 WHERE YEAR(date) = 2023）。
- 模糊查询以 % 开头（如 LIKE '%财务'）。
- 不符合最左前缀原则（复合索引 (A,B,C) 时，查询条件必须有 A）。

3. 事务与锁机制

事务特性（ACID）：
- 原子性（Atomicity）：要么全部成功，要么全部回滚。
- 一致性（Consistency）：数据状态符合业务规则。
- 隔离性（Isolation）：并发事务互不干扰。
- 持久性（Durability）：提交后数据永久保存。
锁类型：
- 行锁：锁定单行（InnoDB 默认）。
- 表锁：锁定整张表（MyISAM 默认）。

4. 优化器如何选择执行计划

成本模型：MySQL 会估算不同执行计划的成本（CPU、IO 等），选择成本最低的。
影响因素：
- 索引的区分度（重复值越少，索引效率越高）。
- 数据分布（如 WHERE 条件过滤掉 90% 的数据，可能走索引）。

5. 日志与恢复机制

日志类型：
- Binlog：记录所有数据变更（用于主从复制、数据恢复）。
- Redo Log：记录事务的物理修改（保证事务持久性）。
- Undo Log：记录事务前的数据版本（用于回滚和 MVCC）。

四、总结：从查询到原理的闭环

基础：掌握 SELECT + WHERE + JOIN 的语法组合。
扩展：通过聚合、分组、子查询解决复杂问题。
原理：
- 索引优化查询速度，事务保证数据安全。
- 执行计划和优化器决定查询效率。
- 日志机制确保数据可恢复。

实践建议：

多用 EXPLAIN 分析慢查询。
根据业务场景设计索引（读写平衡）。
理解隔离级别对并发的影响（如 READ COMMITTED vs REPEATABLE READ）。