跳至主要內容

MySQL入门教程 - 从零开始学习MySQL数据库

Java突击队大约 19 分钟

MySQL入门教程 - 从零开始学习MySQL数据库

目录

  1. MySQL简介
  2. MySQL安装与配置
  3. MySQL基础操作
  4. 数据类型
  5. 表的创建与管理
  6. 数据操作（DML）
  7. 数据查询（DQL）
  8. 高级查询
  9. 索引
  10. 约束
  11. 事务
  12. 存储过程和函数
  13. 视图
  14. 触发器
  15. 用户和权限管理
  16. 性能优化
  17. 备份与恢复
  18. 总结与进阶

点击这里👇🏻获取：100万QPS短链系统、复杂的商城微服务系统、智能翻译助手AI Agent、SaaS点餐系统、刷题吧小程序、商城系统、秒杀系统、AI项目、代码生成神器、苏三demo项目等 8 个项目的：项目源代码、开发教程和技术答疑

1. MySQL简介

MySQL是一个开源的关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，目前属于Oracle公司。MySQL是最流行的关系型数据库管理系统之一，在Web应用方面，MySQL是最好的RDBMS应用软件之一。

核心特点：

  • ✅ 开源免费：社区版完全免费，商业版提供技术支持

  • ✅ 性能优秀：处理速度快，支持高并发

  • ✅ 易于使用：SQL语法简单，学习成本低

  • ✅ 跨平台：支持Windows、Linux、macOS等多种操作系统

  • ✅ 可扩展性强：支持多种存储引擎

  • ✅ 安全性高：提供完善的权限管理机制

  • ✅ 社区活跃：拥有庞大的用户群体和丰富的资源

  • Web应用：网站后台数据库

  • 数据仓库：大数据存储和分析

  • 内容管理：CMS系统数据库

  • 电商系统：订单、商品、用户数据管理

  • 日志系统：系统日志存储和分析

  • MySQL 5.7：稳定版本，广泛使用

  • MySQL 8.0：最新版本，性能提升，新特性

  • MariaDB：MySQL的分支，完全兼容MySQL

  • 数据库（Database）：数据的集合

  • 表（Table）：数据的二维结构

  • 行（Row）：表中的一条记录

  • 列（Column）：表中的一个字段

  • 主键（Primary Key）：唯一标识一条记录的字段

  • 外键（Foreign Key）：关联其他表的字段

2. MySQL安装与配置

方式一：使用安装包

  1. 下载MySQL

  2. 安装MySQL

    • 运行安装程序
    • 选择"Developer Default"或"Server only"
    • 设置root用户密码
    • 完成安装

  3. 配置环境变量

    • 将MySQL的bin目录添加到系统PATH环境变量
    • 默认路径：C:\Program Files\MySQL\MySQL Server 8.0\bin

方式二：使用XAMPP

  1. 下载XAMPP：https://www.apachefriends.org/
  2. 安装XAMPP（包含MySQL）
  3. 通过XAMPP控制面板启动MySQL

点击这里👇🏻获取：100万QPS短链系统、复杂的商城微服务系统、智能翻译助手AI Agent、SaaS点餐系统、刷题吧小程序、商城系统、秒杀系统、AI项目、代码生成神器、苏三demo项目等 8 个项目的：项目源代码、开发教程和技术答疑

2.2 macOS系统安装

使用Homebrew（推荐）

# 安装MySQL
brew install mysql

# 启动MySQL服务
brew services start mysql

# 设置root密码
mysql_secure_installation

使用安装包

  1. 下载MySQL for macOS安装包
  2. 运行安装程序
  3. 按照向导完成安装

Ubuntu/Debian

# 更新包列表
sudo apt update

# 安装MySQL
sudo apt install mysql-server

# 启动MySQL服务
sudo systemctl start mysql

# 设置开机自启
sudo systemctl enable mysql

# 安全配置
sudo mysql_secure_installation

CentOS/RHEL

# 安装MySQL
sudo yum install mysql-server

# 启动MySQL服务
sudo systemctl start mysqld

# 设置开机自启
sudo systemctl enable mysqld

# 获取临时密码
sudo grep 'temporary password' /var/log/mysqld.log
# 检查MySQL版本
mysql --version

# 登录MySQL
mysql -u root -p
# 输入密码后进入MySQL命令行

修改配置文件

Windows： C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Linux/macOS： /etc/mysql/my.cnf 或 /usr/local/mysql/my.cnf

常用配置项：

[mysqld]
# 端口号
port = 3306

# 数据目录
datadir = /var/lib/mysql

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 最大连接数
max_connections = 200

# 缓冲区大小
innodb_buffer_pool_size = 1G

点击这里👇🏻获取：100万QPS短链系统、复杂的商城微服务系统、智能翻译助手AI Agent、SaaS点餐系统、刷题吧小程序、商城系统、秒杀系统、AI项目、代码生成神器、苏三demo项目等 8 个项目的：项目源代码、开发教程和技术答疑

3. MySQL基础操作

# 命令行连接
mysql -u root -p

# 指定主机和端口
mysql -h localhost -P 3306 -u root -p

# 连接指定数据库
mysql -u root -p database_name

查看数据库

-- 显示所有数据库
SHOW DATABASES;

-- 查看当前数据库
SELECT DATABASE();

创建数据库

-- 创建数据库
CREATE DATABASE mydb;

-- 创建数据库并指定字符集
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 如果不存在则创建
CREATE DATABASE IF NOT EXISTS mydb;

删除数据库

-- 删除数据库
DROP DATABASE mydb;

-- 如果存在则删除
DROP DATABASE IF EXISTS mydb;

使用数据库

-- 选择数据库
USE mydb;

-- 查看当前使用的数据库
SELECT DATABASE();

查看表

-- 显示当前数据库的所有表
SHOW TABLES;

-- 显示表结构
DESCRIBE table_name;
-- 或
DESC table_name;

-- 显示创建表的SQL语句
SHOW CREATE TABLE table_name;

MySQL支持多种数据类型，我们将在第4章详细讲解。

4. 数据类型

整数类型

类型字节有符号范围无符号范围
TINYINT1-128 ~ 1270 ~ 255
SMALLINT2-32768 ~ 327670 ~ 65535
MEDIUMINT3-8388608 ~ 83886070 ~ 16777215
INT/INTEGER4-2147483648 ~ 21474836470 ~ 4294967295
BIGINT8-2^63 ~ 2^63-10 ~ 2^64-1

示例：

CREATE TABLE numbers (
    id INT,
    age TINYINT UNSIGNED,
    count BIGINT
);

浮点类型

类型字节说明
FLOAT4单精度浮点数
DOUBLE8双精度浮点数
DECIMAL(M,D)M+2定点数，M为总位数，D为小数位数

示例：

CREATE TABLE prices (
    price FLOAT,
    amount DOUBLE,
    precise_price DECIMAL(10, 2)  -- 总共10位，小数点后2位
);
类型说明最大长度
CHAR(n)固定长度字符串255字节
VARCHAR(n)可变长度字符串65535字节
TEXT长文本65535字节
MEDIUMTEXT中等文本16777215字节
LONGTEXT超长文本4294967295字节

示例：

CREATE TABLE users (
    username VARCHAR(50),
    description TEXT,
    content LONGTEXT
);
类型格式范围
DATEYYYY-MM-DD1000-01-01 ~ 9999-12-31
TIMEHH:MM:SS-838:59:59 ~ 838:59:59
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 ~ 2038-01-19 03:14:07
YEARYYYY1901 ~ 2155

示例：

CREATE TABLE events (
    event_date DATE,
    event_time TIME,
    created_at DATETIME,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

ENUM类型

CREATE TABLE users (
    status ENUM('active', 'inactive', 'pending')
);

SET类型

CREATE TABLE users (
    hobbies SET('reading', 'music', 'sports', 'travel')
);

JSON类型（MySQL 5.7+）

CREATE TABLE products (
    id INT,
    attributes JSON
);

5. 表的创建与管理

基本语法

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    PRIMARY KEY (column1)
);

实际示例

-- 创建用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    age INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

从查询结果创建表

-- 复制表结构
CREATE TABLE new_table LIKE old_table;

-- 复制表结构和数据
CREATE TABLE new_table AS SELECT * FROM old_table;

-- 复制部分数据
CREATE TABLE new_table AS 
SELECT id, username FROM old_table WHERE age > 18;

添加列

-- 添加单列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加多列
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20),
ADD COLUMN address VARCHAR(200);

-- 在指定位置添加列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

修改列

-- 修改列类型
ALTER TABLE users MODIFY COLUMN age TINYINT;

-- 修改列名和类型
ALTER TABLE users CHANGE COLUMN age user_age INT;

-- 修改列默认值
ALTER TABLE users ALTER COLUMN age SET DEFAULT 0;

删除列

ALTER TABLE users DROP COLUMN phone;

重命名表

ALTER TABLE old_name RENAME TO new_name;
-- 或
RENAME TABLE old_name TO new_name;
-- 删除表
DROP TABLE table_name;

-- 如果存在则删除
DROP TABLE IF EXISTS table_name;

-- 删除多个表
DROP TABLE table1, table2, table3;
-- 清空表数据（保留表结构）
TRUNCATE TABLE users;

-- 删除所有数据（可回滚）
DELETE FROM users;

6. 数据操作（DML）

插入单条记录

-- 插入所有列
INSERT INTO users (username, email, password, age) 
VALUES ('zhangsan', 'zhangsan@example.com', '123456', 25);

-- 插入部分列
INSERT INTO users (username, email, password) 
VALUES ('lisi', 'lisi@example.com', '123456');

-- 省略列名（需要提供所有列的值）
INSERT INTO users 
VALUES (NULL, 'wangwu', 'wangwu@example.com', '123456', 30, NOW(), NOW());

插入多条记录

INSERT INTO users (username, email, password, age) 
VALUES 
    ('user1', 'user1@example.com', '123456', 20),
    ('user2', 'user2@example.com', '123456', 25),
    ('user3', 'user3@example.com', '123456', 30);

从查询结果插入

-- 从其他表插入数据
INSERT INTO users (username, email, password)
SELECT username, email, password FROM old_users WHERE age > 18;

INSERT IGNORE

-- 如果记录已存在则忽略（不报错）
INSERT IGNORE INTO users (username, email, password) 
VALUES ('zhangsan', 'zhangsan@example.com', '123456');

REPLACE INTO

-- 如果记录已存在则替换
REPLACE INTO users (username, email, password) 
VALUES ('zhangsan', 'zhangsan@example.com', '123456');

基本语法

UPDATE table_name 
SET column1 = value1, column2 = value2, ...
WHERE condition;

更新单条记录

-- 更新指定记录
UPDATE users 
SET age = 26, email = 'newemail@example.com' 
WHERE id = 1;

更新多条记录

-- 更新所有满足条件的记录
UPDATE users 
SET status = 'active' 
WHERE age > 18;

使用表达式更新

-- 年龄加1
UPDATE users SET age = age + 1 WHERE id = 1;

-- 价格打8折
UPDATE products SET price = price * 0.8 WHERE category = 'electronics';

注意事项

-- ⚠️ 危险操作：没有WHERE条件的UPDATE会更新所有记录
-- UPDATE users SET status = 'inactive';  -- 不要这样做！

-- 安全做法：先查看要更新的记录
SELECT * FROM users WHERE age > 18;
-- 确认后再更新
UPDATE users SET status = 'active' WHERE age > 18;

基本语法

DELETE FROM table_name WHERE condition;

删除单条记录

-- 删除指定记录
DELETE FROM users WHERE id = 1;

删除多条记录

-- 删除所有满足条件的记录
DELETE FROM users WHERE age < 18;

-- 删除所有记录（危险！）
DELETE FROM users;  -- ⚠️ 会删除所有数据

清空表（更快）

-- TRUNCATE比DELETE更快，但不可回滚
TRUNCATE TABLE users;

删除关联数据

-- 删除用户及其订单
DELETE u, o 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE u.id = 1;

7. 数据查询（DQL）

SELECT column1, column2, ... 
FROM table_name 
WHERE condition 
ORDER BY column 
LIMIT n;
-- 查询所有列
SELECT * FROM users;

-- 查询指定列
SELECT id, username, email FROM users;

比较运算符

-- 等于
SELECT * FROM users WHERE age = 25;

-- 不等于
SELECT * FROM users WHERE age != 25;
-- 或
SELECT * FROM users WHERE age <> 25;

-- 大于、小于
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE age < 65;
SELECT * FROM users WHERE age >= 18;
SELECT * FROM users WHERE age <= 65;

逻辑运算符

-- AND（且）
SELECT * FROM users WHERE age > 18 AND age < 65;

-- OR（或）
SELECT * FROM users WHERE age < 18 OR age > 65;

-- NOT（非）
SELECT * FROM users WHERE NOT age = 25;

IN操作符

-- 在指定值列表中
SELECT * FROM users WHERE age IN (20, 25, 30);

-- 不在指定值列表中
SELECT * FROM users WHERE age NOT IN (20, 25, 30);

BETWEEN操作符

-- 在范围内（包含边界）
SELECT * FROM users WHERE age BETWEEN 18 AND 65;

-- 不在范围内
SELECT * FROM users WHERE age NOT BETWEEN 18 AND 65;

LIKE操作符（模糊查询）

-- % 匹配任意字符
SELECT * FROM users WHERE username LIKE 'zhang%';  -- 以zhang开头
SELECT * FROM users WHERE username LIKE '%san';    -- 以san结尾
SELECT * FROM users WHERE username LIKE '%zhang%'; -- 包含zhang

-- _ 匹配单个字符
SELECT * FROM users WHERE username LIKE 'zhang_';  -- zhang后跟一个字符

-- 转义特殊字符
SELECT * FROM users WHERE username LIKE '100\%';  -- 匹配100%

IS NULL / IS NOT NULL

-- 为空
SELECT * FROM users WHERE email IS NULL;

-- 不为空
SELECT * FROM users WHERE email IS NOT NULL;
-- 升序（默认）
SELECT * FROM users ORDER BY age ASC;

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

-- 多列排序
SELECT * FROM users ORDER BY age DESC, username ASC;

-- 使用列位置排序
SELECT id, username, age FROM users ORDER BY 3 DESC;  -- 按第3列（age）排序
-- 限制返回记录数
SELECT * FROM users LIMIT 10;

-- 跳过前n条，返回m条（分页）
SELECT * FROM users LIMIT 10, 20;  -- 跳过10条，返回20条
-- 或
SELECT * FROM users LIMIT 20 OFFSET 10;
-- 去除重复值
SELECT DISTINCT age FROM users;

-- 多列去重
SELECT DISTINCT age, status FROM users;
-- COUNT：计数
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT age) FROM users;

-- SUM：求和
SELECT SUM(price) FROM orders;

-- AVG：平均值
SELECT AVG(age) FROM users;

-- MAX：最大值
SELECT MAX(age) FROM users;

-- MIN：最小值
SELECT MIN(age) FROM users;
-- 按年龄分组统计
SELECT age, COUNT(*) as count 
FROM users 
GROUP BY age;

-- 多列分组
SELECT age, status, COUNT(*) 
FROM users 
GROUP BY age, status;

-- 使用HAVING过滤分组
SELECT age, COUNT(*) as count 
FROM users 
GROUP BY age 
HAVING count > 5;  -- 只显示数量大于5的分组

WHERE vs HAVING：

  • WHERE：在分组前过滤行
  • HAVING：在分组后过滤组

8. 高级查询

INNER JOIN（内连接）

-- 只返回两表都匹配的记录
SELECT u.username, o.order_no, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

LEFT JOIN（左连接）

-- 返回左表所有记录，右表没有匹配则显示NULL
SELECT u.username, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

RIGHT JOIN（右连接）

-- 返回右表所有记录，左表没有匹配则显示NULL
SELECT u.username, o.order_no
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

FULL OUTER JOIN（全外连接）

-- MySQL不支持FULL OUTER JOIN，可以用UNION实现
SELECT u.username, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.username, o.order_no
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

自连接

-- 员工和经理的关系
SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

标量子查询

-- 返回单个值的子查询
SELECT * FROM users 
WHERE age > (SELECT AVG(age) FROM users);

列子查询

-- 返回一列的子查询
SELECT * FROM users 
WHERE age IN (SELECT age FROM admins);

行子查询

-- 返回一行的子查询
SELECT * FROM users 
WHERE (age, status) = (SELECT age, status FROM admins WHERE id = 1);

EXISTS子查询

-- 检查子查询是否返回结果
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- 合并两个查询结果（去重）
SELECT username FROM users
UNION
SELECT username FROM admins;

-- 合并两个查询结果（不去重）
SELECT username FROM users
UNION ALL
SELECT username FROM admins;

字符串函数

-- 连接字符串
SELECT CONCAT('Hello', ' ', 'World');  -- Hello World

-- 转大写
SELECT UPPER('hello');  -- HELLO

-- 转小写
SELECT LOWER('HELLO');  -- hello

-- 截取字符串
SELECT SUBSTRING('Hello World', 1, 5);  -- Hello

-- 字符串长度
SELECT LENGTH('Hello');  -- 5

-- 去除空格
SELECT TRIM('  Hello  ');  -- Hello
SELECT LTRIM('  Hello');   -- Hello
SELECT RTRIM('Hello  ');   -- Hello

数值函数

-- 四舍五入
SELECT ROUND(3.14159, 2);  -- 3.14

-- 向上取整
SELECT CEIL(3.14);  -- 4

-- 向下取整
SELECT FLOOR(3.14);  -- 3

-- 绝对值
SELECT ABS(-10);  -- 10

-- 随机数
SELECT RAND();

日期函数

-- 当前日期时间
SELECT NOW();        -- 2024-01-15 14:30:45
SELECT CURDATE();    -- 2024-01-15
SELECT CURTIME();    -- 14:30:45

-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

-- 日期计算
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

-- 日期差
SELECT DATEDIFF('2024-01-20', '2024-01-15');  -- 5

-- 提取日期部分
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());

条件函数

-- IF函数
SELECT IF(age > 18, '成年', '未成年') FROM users;

-- CASE WHEN
SELECT 
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age < 65 THEN '成年'
        ELSE '老年'
    END as age_group
FROM users;

9. 索引

索引是数据库中用于快速查找数据的数据结构，类似于书籍的目录。

点击这里👇🏻获取：100万QPS短链系统、复杂的商城微服务系统、智能翻译助手AI Agent、SaaS点餐系统、刷题吧小程序、商城系统、秒杀系统、AI项目、代码生成神器、苏三demo项目等 8 个项目的：项目源代码、开发教程和技术答疑

9.2 索引的类型

主键索引（PRIMARY KEY）

-- 创建表时指定主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);

-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

唯一索引（UNIQUE）

-- 创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- 添加唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

普通索引（INDEX）

-- 创建表时指定
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50),
    INDEX idx_username (username)
);

-- 添加普通索引
CREATE INDEX idx_username ON users(username);

复合索引

-- 多列索引
CREATE INDEX idx_name_age ON users(username, age);

全文索引（FULLTEXT）

-- 全文搜索索引
CREATE FULLTEXT INDEX idx_content ON articles(content);
-- 查看表的所有索引
SHOW INDEX FROM users;

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';
-- 删除索引
DROP INDEX idx_username ON users;

-- 删除主键
ALTER TABLE users DROP PRIMARY KEY;
  1. 经常用于WHERE条件的列
  2. 经常用于JOIN的列
  3. 经常用于ORDER BY的列
  4. 不要在小表上创建索引
  5. 不要创建过多索引
  6. 复合索引遵循最左前缀原则

10. 约束

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50)
);
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);
CREATE TABLE users (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- MySQL 8.0.16+支持
CREATE TABLE users (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 0 AND age <= 150)
);

11. 事务

事务是一组SQL语句的集合，要么全部执行成功，要么全部失败回滚。

  • 原子性（Atomicity）：事务不可分割
  • 一致性（Consistency）：数据保持一致
  • 隔离性（Isolation）：事务之间相互隔离
  • 持久性（Durability）：事务提交后永久保存
-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 执行SQL语句
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;
-- 查看隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 隔离级别：
-- READ UNCOMMITTED：读未提交
-- READ COMMITTED：读已提交
-- REPEATABLE READ：可重复读（MySQL默认）
-- SERIALIZABLE：串行化
-- 查看自动提交状态
SELECT @@autocommit;

-- 关闭自动提交
SET autocommit = 0;

-- 开启自动提交
SET autocommit = 1;

12. 存储过程和函数

创建存储过程

DELIMITER //

CREATE PROCEDURE GetUserById(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //

DELIMITER ;

调用存储过程

CALL GetUserById(1);

带输出参数的存储过程

DELIMITER //

CREATE PROCEDURE GetUserCount(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM users;
END //

DELIMITER ;

-- 调用
CALL GetUserCount(@count);
SELECT @count;
DELIMITER //

CREATE FUNCTION GetUserName(user_id INT)
RETURNS VARCHAR(50)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE user_name VARCHAR(50);
    SELECT username INTO user_name FROM users WHERE id = user_id;
    RETURN user_name;
END //

DELIMITER ;

-- 使用
SELECT GetUserName(1);

13. 视图

-- 创建视图
CREATE VIEW user_orders AS
SELECT u.username, o.order_no, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 查询视图
SELECT * FROM user_orders;
-- 修改视图定义
ALTER VIEW user_orders AS
SELECT u.username, o.order_no, o.total, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
DROP VIEW user_orders;

14. 触发器

-- 在插入前触发
DELIMITER //

CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END //

DELIMITER ;

-- 在更新后触发
DELIMITER //

CREATE TRIGGER after_user_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action, timestamp)
    VALUES (NEW.id, 'UPDATE', NOW());
END //

DELIMITER ;
SHOW TRIGGERS;
DROP TRIGGER before_user_insert;

15. 用户和权限管理

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

-- 创建用户（允许远程连接）
CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
-- 授予所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'newuser'@'localhost';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'newuser'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;
REVOKE ALL PRIVILEGES ON database_name.* FROM 'newuser'@'localhost';
DROP USER 'newuser'@'localhost';
-- 修改当前用户密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

-- 修改其他用户密码
SET PASSWORD FOR 'user'@'localhost' = 'newpassword';

16. 性能优化

  1. 使用索引
  2. **避免SELECT ***
  3. 使用LIMIT限制结果
  4. 优化JOIN查询
  5. 使用EXPLAIN分析查询
-- 分析表
ANALYZE TABLE users;

-- 优化表
OPTIMIZE TABLE users;

-- 查看索引使用情况
EXPLAIN SELECT * FROM users WHERE username = 'zhangsan';
# 增加缓冲区大小
innodb_buffer_pool_size = 1G

# 增加最大连接数
max_connections = 200

# 启用查询缓存（MySQL 5.7及以下）
query_cache_size = 64M

17. 备份与恢复

# 备份整个数据库
mysqldump -u root -p database_name > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

# 备份表结构
mysqldump -u root -p --no-data database_name > structure.sql

# 备份数据
mysqldump -u root -p --no-create-info database_name > data.sql
# 恢复数据库
mysql -u root -p database_name < backup.sql

# 恢复所有数据库
mysql -u root -p < all_backup.sql
# 创建备份脚本
#!/bin/bash
mysqldump -u root -p'password' database_name > /backup/db_$(date +%Y%m%d).sql

# 添加到crontab
# 每天凌晨2点备份
0 2 * * * /path/to/backup.sh

18. 总结与进阶

通过本教程，你已经掌握了：

  • ✅ MySQL的安装和配置
  • ✅ 数据库和表的基本操作
  • ✅ SQL语句的使用（增删改查）
  • ✅ 高级查询（JOIN、子查询）
  • ✅ 索引和约束
  • ✅ 事务处理
  • ✅ 存储过程和函数
  • ✅ 视图和触发器
  • ✅ 用户权限管理
  • ✅ 性能优化基础

  1. MySQL高级特性

    • 分区表
    • 复制和主从同步
    • 集群和高可用
    • 性能调优

  2. 数据库设计

    • 范式理论
    • 数据库设计原则
    • 数据建模

  3. MySQL 8.0新特性

    • 窗口函数
    • CTE（公用表表达式）
    • JSON增强

  4. NoSQL数据库

    • Redis
    • MongoDB

  5. 命名规范：使用有意义的表名和列名

  6. 数据类型：选择合适的数据类型

  7. 索引策略：合理使用索引

  8. SQL优化：编写高效的SQL语句

  9. 备份策略：定期备份数据

  10. 安全措施：设置强密码，限制权限

结语

MySQL是一个功能强大、应用广泛的关系型数据库管理系统。通过本教程的学习，相信你已经掌握了MySQL的基础知识和核心功能。

记住：

  • 多实践：理论结合实践，多写SQL语句
  • 理解原理：理解数据库的工作原理
  • 关注性能：注意查询性能和优化
  • 持续学习：关注MySQL新特性和最佳实践

祝你学习愉快，数据库操作顺利！ 🚀

本教程由Java突击队学习社区编写，如有问题欢迎反馈。

一个可以快速提升Java技术的神奇网站
Copyright © 2025 Java突击队