MySQL 数据库知识点整理MySQL 基础和 SQL 入门一、MySQL 概述 1. 什么是 MySQL？ MySQ

MySQL 基础和 SQL 入门

一、MySQL 概述

1. 什么是 MySQL？

MySQL 是一种开源的关系型数据库管理系统。
使用 SQL（Structured Query Language）进行数据的存储、查询和管理。
支持跨平台，性能稳定，广泛用于 Web 开发。

2. 安装 MySQL

MySQL 官网：dev.mysql.com/
常用工具：MySQL Workbench、phpMyAdmin 等。

二、数据库与数据表基础

1. 数据库（Database）

创建数据库：

CREATE DATABASE 数据库名;

查看所有数据库：

SHOW DATABASES;

使用指定数据库：

USE 数据库名;

删除数据库：

DROP DATABASE 数据库名;

2. 数据表（Table）

创建数据表：

CREATE TABLE 表名 (
    列名 数据类型 [约束条件],
    列名 数据类型 [约束条件],
    ...
);

示例：创建用户表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age INT
);

查看数据表：

SHOW TABLES;

查看表结构：

DESC 表名;

删除数据表：

DROP TABLE 表名;

三、SQL 基础语法

1. 数据操作（DML：Data Manipulation Language）

插入数据：`INSERT`

INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...);

示例：

INSERT INTO users (username, email, age)
VALUES ('Tom', 'tom@example.com', 25);

查询数据：`SELECT`

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

查询所有数据：
```
SELECT * FROM users;
```

查询特定数据：

SELECT username, email FROM users WHERE age > 20;

查询去重的数据：
```
SELECT DISTINCT age FROM users;
```

更新数据：`UPDATE`

UPDATE 表名 
SET 列名 = 新值, 列名2 = 新值2
WHERE 条件;

示例：

UPDATE users SET age = 30 WHERE id = 1;

删除数据：`DELETE`

DELETE FROM 表名 WHERE 条件;

示例：

DELETE FROM users WHERE id = 1;

2. 数据查询条件与筛选

条件筛选：`WHERE`

SELECT * FROM users WHERE age > 20;

常见操作符：

=：等于
!= 或 <>：不等于
>：大于
<：小于
LIKE：模糊匹配
BETWEEN：范围匹配
IN：列表匹配
AND、OR、NOT：逻辑条件

示例：

SELECT * FROM users 
WHERE age BETWEEN 20 AND 30 
  AND email LIKE '%@gmail.com';

3. 排序与分页

排序：`ORDER BY`

SELECT * FROM users ORDER BY age ASC; -- 升序
SELECT * FROM users ORDER BY age DESC; -- 降序

分页：`LIMIT`

SELECT * FROM users LIMIT 起始行, 行数;

示例：

SELECT * FROM users LIMIT 0, 5; -- 查询前5条数据

四、约束条件（Constraints）

PRIMARY KEY：主键，唯一标识记录。
NOT NULL：非空约束。
UNIQUE：唯一约束，确保数据不重复。
DEFAULT：默认值。
AUTO_INCREMENT：自动增长。

示例：

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) DEFAULT 0.00,
    stock INT NOT NULL
);

五、常见 SQL 操作

1. 外键约束（FOREIGN KEY）

示例：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_name VARCHAR(100),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

2. 聚合函数

COUNT：统计数量
SUM：求和
AVG：平均值
MAX：最大值
MIN：最小值

示例：

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;

3. 分组查询：`GROUP BY`

SELECT age, COUNT(*) 
FROM users 
GROUP BY age;

4. 联合查询：`JOIN`

用于连接多张表。

INNER JOIN（内连接）：

SELECT users.username, orders.product_name
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN（左连接）：

SELECT users.username, orders.product_name
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

六、MySQL 用户与权限管理

创建用户：

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

授权权限：

GRANT ALL PRIVILEGES ON 数据库.* TO 'username'@'host';

查看权限：

SHOW GRANTS FOR 'username'@'host';

删除用户：

DROP USER 'username'@'host';

七、总结

学习路线

掌握基础 SQL 语法：SELECT、INSERT、UPDATE、DELETE。
理解数据库设计：表、字段、主键、外键等。
熟练使用查询语句：条件、排序、分组、聚合函数。
学习高级功能：多表关联（JOIN）、事务处理等。
使用 MySQL 工具：MySQL Workbench 或其他数据库管理工具。

MySQL 单表操作、约束和事务

一、单表操作

1. 创建单表

基本语法：

CREATE TABLE 表名 (
    列名 数据类型 [约束条件],
    列名 数据类型 [约束条件],
    ...
);

示例：创建一个学生表

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 0),
    email VARCHAR(100) UNIQUE,
    enrollment_date DATE DEFAULT CURRENT_DATE
);

2. 修改单表结构

添加列

ALTER TABLE 表名 ADD 列名 数据类型 [约束条件];

示例：

ALTER TABLE students ADD address VARCHAR(200);

修改列的数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型;

示例：

ALTER TABLE students MODIFY age TINYINT;

删除列

ALTER TABLE 表名 DROP 列名;

示例：

ALTER TABLE students DROP address;

3. 删除与重命名表

删除表

DROP TABLE 表名;

示例：

DROP TABLE students;

重命名表

RENAME TABLE 旧表名 TO 新表名;

示例：

RENAME TABLE students TO new_students;

二、约束（Constraints）

约束用于限制表中的数据，有助于保证数据的完整性和准确性。

1. 常见约束类型

约束类型	作用
`PRIMARY KEY`	主键，唯一标识一条记录，不能为 `NULL`。
`NOT NULL`	非空约束，字段不能为空。
`UNIQUE`	唯一约束，确保数据唯一。
`CHECK`	条件约束，满足指定条件的数据。
`DEFAULT`	设置默认值。
`AUTO_INCREMENT`	自动递增，常用于主键列。
`FOREIGN KEY`	外键约束，建立表之间的关系。

2. 示例：约束的使用

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY, -- 主键，自动递增
    name VARCHAR(50) NOT NULL,             -- 非空
    email VARCHAR(100) UNIQUE,             -- 唯一
    salary DECIMAL(10, 2) CHECK (salary > 0), -- 条件约束
    hire_date DATE DEFAULT CURRENT_DATE    -- 默认值
);

3. 外键约束

外键用于维护表与表之间的关系，确保数据的 引用完整性。

语法：

CREATE TABLE 子表 (
    列名 数据类型,
    FOREIGN KEY (列名) REFERENCES 父表(列名)
);

示例：创建订单表并关联用户表

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

三、事务（Transaction）

1. 什么是事务？

事务是一组操作的集合，这些操作要么全部成功，要么全部失败，保证数据的 一致性 和 完整性。事务的操作遵循 ACID 特性：

A（Atomicity）：原子性，事务中的操作不可分割。
C（Consistency）：一致性，数据从一个正确状态转移到另一个正确状态。
I（Isolation）：隔离性，事务之间相互独立。
D（Durability）：持久性，事务一旦提交，数据永久保存。

2. 事务操作

开启事务

START TRANSACTION;

提交事务

COMMIT;

回滚事务

ROLLBACK;

3. 示例：事务的使用

假设有两个账户表 accounts，进行转账操作：

创建账户表

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2) NOT NULL
);

INSERT INTO accounts (account_id, balance) VALUES (1, 1000.00), (2, 500.00);

事务示例：用户 1 向用户 2 转账 200 元

START TRANSACTION; -- 开启事务

-- 扣除用户 1 的余额
UPDATE accounts SET balance = balance - 200 WHERE account_id = 1;

-- 增加用户 2 的余额
UPDATE accounts SET balance = balance + 200 WHERE account_id = 2;

-- 检查数据是否正确
SELECT * FROM accounts;

-- 如果一切正常，提交事务
COMMIT;

-- 如果出错，回滚事务
ROLLBACK;

4. 保存点（SAVEPOINT）

在事务中，可以通过 SAVEPOINT 设置保存点，允许回滚到特定的保存点，而不是整个事务。

语法：

SAVEPOINT 保存点名;
ROLLBACK TO 保存点名;

示例：

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT sp1;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
SAVEPOINT sp2;

ROLLBACK TO sp1; -- 回滚到 sp1 保存点

COMMIT;

四、总结

单表操作 包括表的创建、修改和删除，掌握 CREATE、ALTER、DROP 等语法。
约束可以保证数据的完整性，常见约束有 PRIMARY KEY、NOT NULL、UNIQUE、CHECK 和 FOREIGN KEY 等。

MySQL 多表、外键和数据库设计

一、多表操作

1. 多表关系

在数据库设计中，多表之间的关系主要包括：

关系类型	特点	示例
一对一 (1:1)	一个表中的一条记录对应另一个表中的一条记录。	用户信息与用户详情表
一对多 (1:N)	一个表中的一条记录对应另一个表中的多条记录。	用户与订单表
多对多 (M:N)	两个表之间的记录可以相互关联多次，需要中间表。	学生与课程表

2. 一对一关系

实现方式

使用 PRIMARY KEY 或 UNIQUE 约束来保证唯一性。
通常用于扩展表信息。

示例：用户与用户详情表

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE user_details (
    detail_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE, -- 保证唯一性
    address VARCHAR(255),
    phone VARCHAR(15),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

3. 一对多关系

实现方式

在“多”的一端的表中使用外键。

示例：用户与订单表

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT, -- 外键
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

4. 多对多关系

实现方式

通过中间表实现两个表之间的多对多关系。

示例：学生与课程表

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

二、外键约束

1. 外键的作用

保证数据的 引用完整性，防止无效数据。
建立表与表之间的关系。

2. 外键语法

CREATE TABLE 子表 (
    列名 数据类型,
    FOREIGN KEY (子表列名) REFERENCES 父表(父表列名)
);

3. 外键示例

示例：订单表关联用户表

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

4. ON DELETE 和 ON UPDATE

ON DELETE：当父表记录被删除时，子表的操作。
ON UPDATE：当父表记录被更新时，子表的操作。

选项：

CASCADE：级联操作。
SET NULL：设置为 NULL。
NO ACTION / RESTRICT：阻止操作。

示例：

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

三、数据库设计

1. 数据库设计的原则

避免冗余：数据不重复存储，减少存储空间。
保持一致性：数据的一致性和完整性。
可扩展性：数据库设计应能适应未来的扩展需求。
高性能：设计时考虑查询和维护的效率。

2. 规范化

规范化是数据库设计的重要步骤，主要分为几个范式：

范式	特点
第一范式 (1NF)	每个字段保持原子性，不能再分割。
第二范式 (2NF)	在 1NF 基础上，消除部分依赖。
第三范式 (3NF)	在 2NF 基础上，消除传递依赖。

3. 数据库设计示例

需求：设计一个学生管理系统

表：students、courses 和 student_courses

设计实现：

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    birth_date DATE
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

4. 设计时的注意事项

避免过度设计，确保结构简单且易于维护。
字段命名规范，表名、列名应清晰易懂。
数据类型选择合适，避免占用不必要的空间。
使用索引提高查询效率，但不要滥用索引。
外键约束在必要时使用，注意性能开销。

MySQL 索引、存储过程和触发器

1. 索引（Index）

索引是数据库中加速查询的一种机制。它通过创建数据的有序结构，使数据库能够快速找到特定的行。

常见的索引类型：

单列索引：对单个列创建索引。
联合索引：对多个列一起创建索引，适用于查询中涉及多个列的情况。
唯一索引：确保索引列中的值唯一。
全文索引：用于文本字段的全文搜索。
主键索引：数据库表的主键会自动创建索引，确保值唯一且非空。
自增索引：通常用于主键列，自动为每一行生成唯一编号。

索引的优缺点：

优点：

提高查询速度。
加速 ORDER BY 和 GROUP BY 操作。

缺点：

插入、删除、更新操作较慢，因为需要维护索引。
占用额外存储空间。

索引的创建和删除：

-- 创建单列索引
CREATE INDEX idx_name ON table_name (column_name);

-- 创建联合索引
CREATE INDEX idx_name ON table_name (column1, column2);

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

2. 存储过程（Stored Procedure）

存储过程是预编译的一组 SQL 语句的集合，可以封装逻辑，减少客户端和数据库的交互。

存储过程的优缺点：

优点：

提高性能：存储过程是预编译的，减少了 SQL 解析时间。
提高安全性：通过控制访问存储过程来保护数据库。
代码复用：存储过程可以多次调用，简化重复的操作。

存储过程的示例：

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE procedure_name()
BEGIN
    -- SQL 语句
    SELECT * FROM table_name;
END //
DELIMITER ;

调用存储过程：

CALL procedure_name();

3. 触发器（Trigger）

触发器是在表中发生特定事件时自动执行的存储过程。常用于确保数据的一致性和完整性。

触发器的类型：

BEFORE 触发器：在插入、更新、删除操作之前触发。
AFTER 触发器：在插入、更新、删除操作之后触发。

触发器的优缺点：

优点：

自动化：不需要手动干预，确保数据一致性。
提高安全性：可以在数据修改时自动进行审计或校验。

缺点：

性能问题：触发器的执行会影响操作的响应时间。
难以调试：触发器的执行是隐式的，难以追踪和调试。

创建触发器的示例：

-- 创建 BEFORE 触发器
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
    SET NEW.column_name = 'value';
END;

删除触发器：

DROP TRIGGER trigger_name;

MySQL 数据库知识点整理

MySQL 基础和 SQL 入门

一、MySQL 概述

1. 什么是 MySQL？

2. 安装 MySQL

二、数据库与数据表基础

1. 数据库（Database）

2. 数据表（Table）

三、SQL 基础语法

1. 数据操作（DML：Data Manipulation Language）

插入数据：INSERT

查询数据：SELECT

更新数据：UPDATE

删除数据：DELETE

2. 数据查询条件与筛选

条件筛选：WHERE

3. 排序与分页

排序：ORDER BY

分页：LIMIT

四、约束条件（Constraints）

五、常见 SQL 操作

1. 外键约束（FOREIGN KEY）

2. 聚合函数

3. 分组查询：GROUP BY

4. 联合查询：JOIN

六、MySQL 用户与权限管理

七、总结

学习路线

MySQL 单表操作、约束和事务

一、单表操作

1. 创建单表

2. 修改单表结构

添加列

修改列的数据类型

删除列

3. 删除与重命名表

删除表

重命名表

二、约束（Constraints）

1. 常见约束类型

2. 示例：约束的使用

3. 外键约束

三、事务（Transaction）

1. 什么是事务？

2. 事务操作

开启事务

提交事务

回滚事务

3. 示例：事务的使用

4. 保存点（SAVEPOINT）

四、总结

MySQL 多表、外键和数据库设计

一、多表操作

1. 多表关系

2. 一对一关系

实现方式

3. 一对多关系

实现方式

4. 多对多关系

实现方式

二、外键约束

1. 外键的作用

2. 外键语法

3. 外键示例

4. ON DELETE 和 ON UPDATE

三、数据库设计

1. 数据库设计的原则

2. 规范化

3. 数据库设计示例

需求：设计一个学生管理系统

4. 设计时的注意事项

MySQL 索引、存储过程和触发器

1. 索引（Index）

常见的索引类型：

索引的优缺点：

优点：

缺点：

索引的创建和删除：

2. 存储过程（Stored Procedure）

存储过程的优缺点：

插入数据：`INSERT`

查询数据：`SELECT`

更新数据：`UPDATE`

删除数据：`DELETE`

条件筛选：`WHERE`

排序：`ORDER BY`

分页：`LIMIT`

3. 分组查询：`GROUP BY`

4. 联合查询：`JOIN`