SQL

2025-05-27

9 0

一、引言：为什么要学MySQL？

在数字化时代，数据已成为企业的核心资产。作为全球使用最广泛的开源关系型数据库，MySQL凭借其高效、稳定、易扩展的特点，成为开发者必备的核心技能。无论是Web应用、大数据分析还是移动应用，MySQL都扮演着关键角色。掌握MySQL不仅能提升数据处理能力，更是迈向高级开发岗位的必经之路。

二、MySQL核心基础：从安装到核心概念

1. 数据库相关概念

数据库（DB）：有序存储数据的“仓库”，如员工信息库、订单库。
数据库管理系统（DBMS）：操作数据库的软件，MySQL是其中之一，其他还有Oracle、SQL Server等。
SQL：结构化查询语言，操作关系型数据库的统一标准，用于增删改查数据。

2. MySQL版本与安装

社区版 vs 商业版：社区版免费开源，适合学习和中小型项目；商业版收费，提供官方技术支持。
安装与启动：
- 下载地址：MySQL官方下载页
- 启动命令：net start mysql80（Windows）；停止命令：net stop mysql80
- 客户端连接：通过命令行工具（mysql -u root -p）或图形化工具（Navicat、DataGrip）。

三、SQL语法与核心操作分类

1. 通用语法规则

语句支持单行/多行，以分号;结尾，关键字建议大写（如SELECT、FROM）。
注释：单行--或#，多行/* ... */。

2. SQL分类详解

（1）数据定义语言（DDL）—— 定义数据库对象

数据库操作：

  CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARSET utf8mb4; -- 创建数据库
  USE mydb; -- 使用数据库
  SHOW DATABASES; -- 查询所有数据库
  DROP DATABASE IF EXISTS mydb; -- 删除数据库

表操作：

  -- 创建表（员工表）
  CREATE TABLE employee (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(10) NOT NULL UNIQUE,
      age INT CHECK (age > 0 AND age <= 120),
      status CHAR(1) DEFAULT '1'
  );
  
  DESC employee; -- 查询表结构
  ALTER TABLE employee ADD COLUMN email VARCHAR(50); -- 添加字段
  DROP TABLE IF EXISTS employee; -- 删除表

（2）数据操作语言（DML）—— 操作表中数据

增删改：

  INSERT INTO employee (name, age) VALUES ('张三', 25); -- 插入数据
  UPDATE employee SET age = 26 WHERE name = '张三'; -- 修改数据
  DELETE FROM employee WHERE age < 18; -- 删除数据（带条件）

（3）数据查询语言（DQL）—— 查询数据

基础查询：

  SELECT id, name FROM employee; -- 查询指定字段
  SELECT DISTINCT age FROM employee; -- 去重查询

条件与排序：

  SELECT * FROM employee WHERE age BETWEEN 20 AND 30 AND gender = '男'; -- 条件查询
  SELECT * FROM employee ORDER BY age ASC, salary DESC; -- 排序（升序/降序）

聚合与分组：

  SELECT COUNT(*) AS total, AVG(age) AS avg_age FROM employee; -- 聚合函数
  SELECT gender, COUNT(*) FROM employee GROUP BY gender HAVING COUNT(*) > 10; -- 分组查询

（4）数据控制语言（DCL）—— 管理用户与权限

用户管理：

  CREATE USER 'user'@'%' IDENTIFIED BY 'password'; -- 创建用户
  ALTER USER 'user'@'%' IDENTIFIED BY 'new_password'; -- 修改密码
  DROP USER 'user'@'%'; -- 删除用户

权限控制：

  GRANT SELECT, INSERT ON mydb.* TO 'user'@'%'; -- 授予权限
  REVOKE UPDATE ON mydb.employee FROM 'user'@'%'; -- 撤销权限

四、函数：提升数据处理效率的利器

1. 字符串函数

CONCAT(s1, s2)：拼接字符串，如CONCAT(name, '年龄：', age)。 TRIM(str)：去除首尾空格，TRIM(' 张三 ') → 张三。 SUBSTRING(str, start, len)：截取子串，SUBSTRING('2023-10-01', 1, 4) → 2023。

2. 数值函数

ROUND(x, y)：四舍五入，ROUND(3.1415, 2) → 3.14。 RAND()：生成0-1随机数，FLOOR(RAND()*100) → 0-99随机整数。

3. 日期函数

NOW()：获取当前日期时间，2023-10-01 15:30:00。 DATEDIFF(date1, date2)：计算天数差，DATEDIFF('2023-10-01', '2023-09-01') → 30。

4. 流程函数

IF(condition, t, f)：条件判断，IF(score >= 60, '及格', '不及格')。 CASE WHEN ... THEN ... END：多条件分支，适用于复杂逻辑。

五、约束：保证数据完整性的规则

1. 常见约束类型

非空约束（NOT NULL）：字段值不能为NULL，如name VARCHAR(10) NOT NULL。
唯一约束（UNIQUE）：字段值唯一，如email VARCHAR(50) UNIQUE。
主键约束（PRIMARY KEY）：唯一标识记录，非空且唯一，常搭配自增AUTO_INCREMENT。
外键约束（FOREIGN KEY）：建立表间关联，如员工表dept_id关联部门表id。

2. 外键实战案例

-- 创建部门表（父表）
CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 创建员工表（子表），添加外键
CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(id)
);

六、多表查询：处理复杂业务场景

1. 表关系分类

一对多：如部门与员工（员工表含部门外键）。
多对多：如学生与课程（通过中间表关联）。
一对一：如用户与用户详情（外键加唯一约束）。

2. 查询方式详解

内连接（INNER JOIN）：查询交集数据，获取员工及其部门：

  SELECT e.name, d.name AS dept_name
  FROM employee e
  INNER JOIN department d ON e.dept_id = d.id;

左外连接（LEFT JOIN）：查询左表所有数据，包括交集：

  SELECT e.name, d.name AS dept_name
  FROM employee e
  LEFT JOIN department d ON e.dept_id = d.id;

子查询：嵌套查询，如查询薪资高于平均的员工：

  SELECT * FROM employee
  WHERE salary > (SELECT AVG(salary) FROM employee);

七、事务：确保数据一致性的关键

1. 事务核心特性（ACID）

原子性：操作要么全成功，要么全回滚。
一致性：事务前后数据状态合法。
隔离性：事务间互不干扰。
持久性：提交后数据永久保存。

2. 事务操作实战

START TRANSACTION; -- 开启事务
UPDATE account SET balance = balance - 100 WHERE id = 1; -- 扣钱
UPDATE account SET balance = balance + 100 WHERE id = 2; -- 加钱
COMMIT; -- 提交（若中间出错则ROLLBACK回滚）