mysql 基础教程

MySQL 是目前最流行的关系型数据库管理系统（RDBMS）之一，数据以表格形式存储，并通过 SQL（结构化查询语言）进行操作。

数据库核心概念

• 数据库（Database）： 存储数据的容器。
• 表（Table）： 数据的结构化集合，由行（Row/Record）和列（Column/Field）组成。
• 主键（Primary Key）： 唯一标识表中每一行数据的字段，不能重复且不可为空。
• 外键（Foreign Key）： 用于关联两个表数据的字段。

一、 常用数据类型

• 数值类： INT（整数）、BIGINT（大整数）、DECIMAL(p,s)（高精度小数，如金额）。
• 字符串类： VARCHAR(n)（可变长字符串，节省空间）、TEXT（长文本）。
• 日期时间： DATE（日期 YYYY-MM-DD）、DATETIME（日期时间 YYYY-MM-DD HH:MM:SS）。

二、 DDL：数据库与表结构操作

1. 数据库管理

-- 创建数据库
CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4;

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

-- 使用数据库
USE mydb;

-- 删除数据库
DROP DATABASE mydb;

2. 数据表管理

-- 创建数据表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT DEFAULT 18,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESCRIBE users;

-- 修改表结构
ALTER TABLE users ADD COLUMN phone VARCHAR(20); -- 添加字段
ALTER TABLE users DROP COLUMN age;             -- 删除字段

-- 删除表
DROP TABLE users;

三、 DML：数据增删改

-- 插入数据
INSERT INTO users (username, email) VALUES ('alice', '[email protected]');
INSERT INTO users (username, email) VALUES ('bob', '[email protected]'), ('charlie', '[email protected]');

-- 更新数据（务必带上 WHERE 条件）
UPDATE users SET email = '[email protected]' WHERE username = 'alice';

-- 删除数据（务必带上 WHERE 条件）
DELETE FROM users WHERE id = 3;

四、 DQL：单表查询基础（重点）

SQL 查询语句的执行逻辑顺序一般为：FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ HAVING $\rightarrow$ SELECT $\rightarrow$ ORDER BY $\rightarrow$ LIMIT。

-- 基本查询
SELECT id, username FROM users;

-- 条件过滤 (WHERE)
SELECT * FROM users WHERE age >= 18 AND username LIKE 'a%'; -- LIKE 'a%' 匹配以a开头

-- 排序 (ORDER BY)
SELECT * FROM users ORDER BY created_at DESC; -- DESC 降序, ASC 升序

-- 分页 (LIMIT offset, count)
SELECT * FROM users LIMIT 0, 10; -- 获取前10条数据

-- 分组与聚合函数 (COUNT, SUM, AVG, MAX, MIN)
SELECT age, COUNT(*) as user_count 
FROM users 
GROUP BY age 
HAVING user_count > 1; -- HAVING 用于过滤分组后的数据

五、 多表关联查询（JOIN）

假设有 users 表（用户）和 orders 表（订单，含 user_id 字段）：

• INNER JOIN（内连接）： 仅返回两表匹配成功的记录。
• LEFT JOIN（左外连接）： 返回左表所有记录及右表匹配的记录（未匹配显示 NULL）。

-- 内连接查询：获取购买过商品的用户及其订单信息
SELECT u.username, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左连接查询：获取所有用户及其订单（即使没有订单的用户也会列出）
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

六、 索引与事务（进阶基础）

1. 索引（Index）
索引类似书本的目录，能显著提高查询效率，但会略微降低写入速度。

-- 在 username 字段创建索引
CREATE INDEX idx_username ON users(username);

-- 删除索引
DROP INDEX idx_username ON users;

2. 事务（Transaction）
事务保证一组 SQL 操作要么全部成功，要么全部失败（遵循 ACID 特性）。

START TRANSACTION; -- 开启事务

UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

COMMIT; -- 提交事务（若出错则使用 ROLLBACK 撤销）

MySQL 用户与权限管理是保证数据库安全的核心环节。MySQL 中的用户身份由 用户名 (User) 和 允许连接的主机 (Host) 共同决定（格式为 'username'@'host'）。

一、用户管理（增删改查）

1. 查看现有用户

SELECT User, Host, plugin FROM mysql.user;

2. 创建用户 (CREATE USER)

* 仅限本地连接（localhost）：

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

* 允许任意远程主机连接 (%)：

CREATE USER 'remote_user'@'%' IDENTIFIED BY 'StrongPassword123!';

* 限定特定 IP / 网段连接：

CREATE USER 'office_user'@'192.168.1.100' IDENTIFIED BY 'StrongPassword123!';

3. 修改用户密码 (ALTER USER)

ALTER USER 'dev_user'@'localhost' IDENTIFIED BY 'NewPassword456!';

4. 删除用户 (DROP USER)

DROP USER 'dev_user'@'localhost';

二、权限管理（授权与撤销）

权限控制遵循最小权限原则，常用的权限包括 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP 和 ALL PRIVILEGES。

1. 授予权限 (GRANT)

* 授予特定数据库的所有权限：

GRANT ALL PRIVILEGES ON app_db.* TO 'dev_user'@'localhost';

* 授予特定数据库的只读权限：

GRANT SELECT ON app_db.* TO 'read_user'@'%';

* 仅授予某张表的特定操作权限：

GRANT SELECT, INSERT ON app_db.orders TO 'sales_user'@'localhost';

2. 查看用户权限 (SHOW GRANTS)

SHOW GRANTS FOR 'dev_user'@'localhost';

3. 撤销权限 (REVOKE)

-- 撤销插入权限
REVOKE INSERT ON app_db.* FROM 'dev_user'@'localhost';

-- 撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'dev_user'@'localhost';

4. 刷新权限 (FLUSH PRIVILEGES)

使用 GRANT 或 REVOKE 语句后，通常需要执行刷新令其立即生效：

FLUSH PRIVILEGES;

三、MySQL 8.0 核心安全特性与最佳实践