【MySQL】SQL简述

个人主页：Guiat
归属专栏：MySQL

正文

1. SQL基础概念

SQL（Structured Query Language）是一种用于管理关系型数据库的标准语言。MySQL是一种流行的开源关系型数据库管理系统，它使用SQL作为其主要语言。

1.1 SQL的组成部分

SQL语言主要由以下几个部分组成：

• DDL（数据定义语言）：用于定义数据库结构
• DML（数据操作语言）：用于对数据进行增删改查
• DCL（数据控制语言）：用于权限控制
• TCL（事务控制语言）：用于事务管理

1.2 MySQL的特点

• 开源免费
• 性能稳定可靠
• 支持多种存储引擎
• 跨平台
• 支持大型数据库

2. 数据库与表操作

2.1 数据库操作

-- 创建数据库
CREATE DATABASE my_database;

-- 使用数据库
USE my_database;

-- 删除数据库
DROP DATABASE my_database;

-- 查看所有数据库
SHOW DATABASES;

2.2 表操作

-- 创建表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT,
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

-- 查看表结构
DESC employees;

-- 修改表结构（添加列）
ALTER TABLE employees ADD COLUMN hire_date DATE;

-- 删除表
DROP TABLE employees;

-- 查看所有表
SHOW TABLES;

3. 数据操作语言(DML)

3.1 插入数据

-- 插入单行数据
INSERT INTO employees (name, age, department, salary)
VALUES ('张三', 30, '技术部', 10000.00);

-- 插入多行数据
INSERT INTO employees (name, age, department, salary) VALUES
('李四', 25, '市场部', 8000.00),
('王五', 35, '人事部', 12000.00),
('赵六', 28, '技术部', 9500.00);

3.2 查询数据

-- 查询所有列
SELECT * FROM employees;

-- 查询特定列
SELECT name, department, salary FROM employees;

-- 条件查询
SELECT * FROM employees WHERE department = '技术部';

-- 排序
SELECT * FROM employees ORDER BY salary DESC;

-- 分页
SELECT * FROM employees LIMIT 10 OFFSET 0;

3.3 更新数据

-- 更新单个字段
UPDATE employees SET salary = 11000.00 WHERE name = '张三';

-- 同时更新多个字段
UPDATE employees 
SET salary = salary * 1.1, department = '研发部'
WHERE department = '技术部';

3.4 删除数据

-- 删除特定记录
DELETE FROM employees WHERE name = '赵六';

-- 删除所有记录
DELETE FROM employees;
-- 或
TRUNCATE TABLE employees;

4. 高级查询

4.1 聚合函数

-- 计数
SELECT COUNT(*) FROM employees;

-- 求和
SELECT SUM(salary) FROM employees;

-- 平均值
SELECT AVG(salary) FROM employees;

-- 最大值和最小值
SELECT MAX(salary), MIN(salary) FROM employees;

-- 分组统计
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

4.2 JOIN操作

假设我们有departments表：

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    location VARCHAR(100)
);

INSERT INTO departments VALUES
(1, '技术部', 'A栋5楼'),
(2, '市场部', 'B栋3楼'),
(3, '人事部', 'A栋2楼');

-- 添加外键
ALTER TABLE employees ADD dept_id INT;
ALTER TABLE employees ADD FOREIGN KEY (dept_id) REFERENCES departments(dept_id);

-- 更新员工表中的部门ID
UPDATE employees SET dept_id = 1 WHERE department = '技术部';
UPDATE employees SET dept_id = 2 WHERE department = '市场部';
UPDATE employees SET dept_id = 3 WHERE department = '人事部';

JOIN查询：

-- 内连接
SELECT e.name, e.salary, d.dept_name, d.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

-- 左连接
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- 右连接
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

4.3 子查询

-- 子查询作为条件
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 子查询作为临时表
SELECT dept_name, emp_count
FROM (
    SELECT d.dept_name, COUNT(*) as emp_count
    FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
    GROUP BY d.dept_name
) AS dept_stats
WHERE emp_count > 2;

5. 索引与性能优化

5.1 索引类型

• 主键索引：表中的主键自动创建索引
• 唯一索引：确保索引列中的值是唯一的
• 普通索引：加速查询
• 全文索引：用于全文搜索
• 复合索引：多列索引

5.2 创建和管理索引

-- 创建普通索引
CREATE INDEX idx_name ON employees(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON employees(email);

-- 创建复合索引
CREATE INDEX idx_dept_salary ON employees(department, salary);

-- 删除索引
DROP INDEX idx_name ON employees;

-- 查看表的索引
SHOW INDEX FROM employees;

5.3 索引性能对比

下面的Mermaid图表展示了有索引和无索引的查询性能差异：

5.4 查询优化建议

1. 尽量避免在WHERE子句中使用函数或运算符
2. 使用覆盖索引减少回表操作
3. 合理使用EXPLAIN分析查询计划
4. 避免SELECT *，只查询需要的列
5. 合理设计表结构和选择数据类型

6. 事务处理

6.1 ACID特性

• 原子性(Atomicity)：事务是不可分割的单位
• 一致性(Consistency)：事务前后数据库的完整性约束不被破坏
• 隔离性(Isolation)：一个事务的执行不被另一个事务干扰
• 持久性(Durability)：事务一旦提交，其结果永久保存

6.2 事务控制

-- 开始事务
START TRANSACTION;

-- 执行操作
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- 提交事务
COMMIT;

-- 或回滚事务
-- ROLLBACK;

6.3 事务隔离级别

MySQL支持的四种事务隔离级别：

7. 存储过程和函数

7.1 创建存储过程

DELIMITER //
CREATE PROCEDURE get_employee_by_dept(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM employees e
    JOIN departments d ON e.dept_id = d.dept_id
    WHERE d.dept_name = dept_name;
END //
DELIMITER ;

-- 调用存储过程
CALL get_employee_by_dept('技术部');

7.2 创建函数

DELIMITER //
CREATE FUNCTION calculate_bonus(salary DECIMAL(10,2), rating INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE bonus DECIMAL(10,2);
    IF rating = 5 THEN
        SET bonus = salary * 0.2;
    ELSEIF rating = 4 THEN
        SET bonus = salary * 0.15;
    ELSE
        SET bonus = salary * 0.1;
    END IF;
    RETURN bonus;
END //
DELIMITER ;

-- 使用函数
SELECT name, salary, calculate_bonus(salary, 5) AS bonus
FROM employees;

8. 数据库安全

8.1 用户管理与权限

-- 创建用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'password';

-- 授予权限
GRANT SELECT, INSERT, UPDATE ON my_database.* TO 'app_user'@'localhost';

-- 查看用户权限
SHOW GRANTS FOR 'app_user'@'localhost';

-- 收回权限
REVOKE UPDATE ON my_database.* FROM 'app_user'@'localhost';

-- 删除用户
DROP USER 'app_user'@'localhost';

8.2 SQL注入防范

不安全的代码示例：

$username = $_POST['username'];
$query = "SELECT * FROM users WHERE username = '$username'";

安全的代码示例：

$username = $mysqli->real_escape_string($_POST['username']);
$stmt = $mysqli->prepare("SELECT * FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();

9. 备份与恢复

9.1 数据备份

# 使用mysqldump进行备份
mysqldump -u root -p my_database > backup.sql

# 备份特定表
mysqldump -u root -p my_database employees departments > backup_tables.sql

# 仅备份结构
mysqldump -u root -p --no-data my_database > schema_backup.sql

9.2 数据恢复

# 恢复数据库
mysql -u root -p my_database < backup.sql

# 在MySQL客户端中恢复
mysql> source backup.sql

10. MySQL性能监控与优化

10.1 性能监控工具

• SHOW PROCESSLIST：显示当前运行的查询
• SHOW STATUS：显示系统状态变量
• PERFORMANCE_SCHEMA：提供服务器内部执行情况
• 慢查询日志：记录执行时间超过阈值的查询

10.2 性能优化方法

下图显示了MySQL性能优化的几个关键领域：

10.3 监控关键指标

-- 查看数据库连接数
SHOW STATUS LIKE 'Connections';

-- 查看缓冲区使用情况
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

-- 查看查询缓存命中率
SHOW STATUS LIKE 'Qcache%';

结语
感谢您的阅读！期待您的一键三连！欢迎指正！