MySQL | 学习笔记

更新: 4/20/2025 字数: 0 字时长: 0 分钟

MySQL 是一个开源的关系型数据库管理系统，由瑞典 MySQL AB 公司开发，现在属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一，尤其是在 Web 应用程序领域。

特点：

开源免费
性能高效
可靠性好
易于使用
跨平台支持

MySQL 基础

安装与配置

在 Windows 上安装

从官网下载 MySQL 安装包
运行安装程序，按照向导完成安装
配置 root 密码和其他选项

在 Linux 上安装

bash

# Ubuntu/Debian
sudo apt update
sudo apt install mysql-server

# CentOS/RHEL
sudo yum install mysql-server
sudo systemctl start mysqld

连接 MySQL

bash

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

# 指定主机和端口
mysql -h hostname -u username -p -P port

数据库基本操作

创建和管理数据库

sql

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

-- 创建数据库
CREATE DATABASE db_name;

-- 使用数据库
USE db_name;

-- 删除数据库
DROP DATABASE db_name;

创建和管理表

sql

-- 创建表
CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESCRIBE table_name;
SHOW CREATE TABLE table_name;

-- 删除表
DROP TABLE table_name;

-- 修改表
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name MODIFY column_name new_datatype;
ALTER TABLE table_name DROP column_name;

数据类型

数值类型

INT：整数类型
TINYINT：小整数类型
BIGINT：大整数类型
FLOAT：单精度浮点数
DOUBLE：双精度浮点数
DECIMAL：精确小数

字符串类型

CHAR：固定长度字符串
VARCHAR：可变长度字符串
TEXT：长文本
ENUM：枚举类型
SET：集合类型

日期和时间类型

DATE：日期
TIME：时间
DATETIME：日期和时间
TIMESTAMP：时间戳
YEAR：年份

二进制类型

BINARY：固定长度二进制字符串
VARBINARY：可变长度二进制字符串
BLOB：二进制大对象

SQL 语句

数据操作语言 (DML)

插入数据

sql

-- 插入单行
INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

-- 插入多行
INSERT INTO table_name (column1, column2, ...) 
VALUES 
    (value1_1, value1_2, ...),
    (value2_1, value2_2, ...),
    ...;

查询数据

sql

-- 基本查询
SELECT column1, column2, ... FROM table_name;

-- 使用条件
SELECT * FROM table_name WHERE condition;

-- 排序
SELECT * FROM table_name ORDER BY column1 [ASC|DESC];

-- 分组
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

-- 限制结果数量
SELECT * FROM table_name LIMIT offset, count;

更新数据

sql

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

删除数据

sql

DELETE FROM table_name WHERE condition;

数据定义语言 (DDL)

主键

sql

-- 创建表时定义主键
CREATE TABLE table_name (
    id INT PRIMARY KEY,
    ...
);

-- 添加主键
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

外键

sql

-- 创建表时定义外键
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    ...
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 添加外键
ALTER TABLE table_name ADD CONSTRAINT fk_name
FOREIGN KEY (column_name) REFERENCES ref_table(ref_column);

索引

sql

-- 创建索引
CREATE INDEX index_name ON table_name (column1, column2, ...);

-- 唯一索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

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

视图

sql

-- 创建视图
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

-- 使用视图
SELECT * FROM view_name;

-- 删除视图
DROP VIEW view_name;

事务控制

sql

-- 开始事务
START TRANSACTION;
-- 或
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

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

高级特性

函数与操作符

字符串函数

sql

-- 连接字符串
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- 获取子字符串
SELECT SUBSTRING(name, 1, 3) FROM products;

-- 字符串长度
SELECT LENGTH(description) FROM products;

数值函数

sql

-- 四舍五入
SELECT ROUND(price, 2) FROM products;

-- 向上/向下取整
SELECT CEILING(value), FLOOR(value) FROM table_name;

日期和时间函数

sql

-- 当前日期和时间
SELECT NOW(), CURDATE(), CURTIME();

-- 格式化日期
SELECT DATE_FORMAT(date_column, '%Y-%m-%d') FROM table_name;

-- 日期计算
SELECT DATE_ADD(date_column, INTERVAL 1 DAY) FROM table_name;

聚合函数

sql

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

-- 求和
SELECT SUM(column_name) FROM table_name;

-- 平均值
SELECT AVG(column_name) FROM table_name;

-- 最大/最小值
SELECT MAX(column_name), MIN(column_name) FROM table_name;

连接查询

内连接（INNER JOIN）

sql

SELECT a.column1, b.column2
FROM table_a a
INNER JOIN table_b b ON a.common_field = b.common_field;

左连接（LEFT JOIN）

sql

SELECT a.column1, b.column2
FROM table_a a
LEFT JOIN table_b b ON a.common_field = b.common_field;

右连接（RIGHT JOIN）

sql

SELECT a.column1, b.column2
FROM table_a a
RIGHT JOIN table_b b ON a.common_field = b.common_field;

全连接（模拟 FULL JOIN）

sql

SELECT a.column1, b.column2
FROM table_a a
LEFT JOIN table_b b ON a.common_field = b.common_field
UNION
SELECT a.column1, b.column2
FROM table_a a
RIGHT JOIN table_b b ON a.common_field = b.common_field
WHERE a.common_field IS NULL;

子查询

sql

-- 在 WHERE 子句中使用子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 在 FROM 子句中使用子查询
SELECT t.category, AVG(t.price) as avg_price
FROM (SELECT * FROM products WHERE price > 100) t
GROUP BY t.category;

-- EXISTS 子查询
SELECT customer_id, name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.customer_id
);

性能优化

索引优化

为经常用于查询的列创建索引
对于复合索引，注意列的顺序
避免在频繁更新的列上创建索引
定期检查和优化索引

sql

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

-- 优化表
OPTIMIZE TABLE table_name;

查询优化

只查询需要的列，避免 SELECT *
使用 EXPLAIN 分析查询执行计划
合理使用 LIMIT 限制结果集大小
优化 JOIN 查询顺序和方式

sql

-- 分析查询
EXPLAIN SELECT * FROM table_name WHERE condition;

配置优化

常见的 MySQL 配置参数：

innodb_buffer_pool_size：InnoDB 缓冲池大小
key_buffer_size：MyISAM 键缓存大小
max_connections：最大连接数
query_cache_size：查询缓存大小（MySQL 8.0+ 已移除）
tmp_table_size：临时表大小
innodb_flush_log_at_trx_commit：事务提交时日志刷新方式

高可用和扩展性

备份与恢复

备份数据

bash

# 使用 mysqldump 备份
mysqldump -u username -p database_name > backup.sql

# 备份多个数据库
mysqldump -u username -p --databases db1 db2 > backup.sql

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

恢复数据

bash

# 恢复数据
mysql -u username -p database_name < backup.sql

复制

主从复制：

在主服务器配置中启用二进制日志

ini

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW

在从服务器上配置

ini

[mysqld]
server-id = 2
relay_log = mysql-relay-bin

在从服务器上设置复制

sql

CHANGE MASTER TO
  MASTER_HOST='master_host_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=0;

START SLAVE;

分片与分区

分区表

sql

-- 范围分区
CREATE TABLE sales (
    id INT, 
    amount DECIMAL(10,2),
    sale_date DATE
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- 列表分区
CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(20)
)
PARTITION BY LIST (department) (
    PARTITION p_sales VALUES IN ('Sales', 'Marketing'),
    PARTITION p_tech VALUES IN ('IT', 'Development'),
    PARTITION p_admin VALUES IN ('HR', 'Admin', 'Finance')
);

安全性

用户和权限管理

创建用户

sql

-- 创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password';

-- 授予权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'host';
GRANT SELECT, INSERT ON database_name.table_name TO 'username'@'host';

-- 撤销权限
REVOKE privilege ON database_name.* FROM 'username'@'host';

-- 删除用户
DROP USER 'username'@'host';

-- 刷新权限
FLUSH PRIVILEGES;

安全最佳实践

定期更新 MySQL 版本
删除不必要的默认数据库和账户
设置强密码策略
限制连接访问
启用 SSL 加密连接
定期审计数据库活动
限制用户权限遵循最小权限原则

实际应用案例

电子商务数据库设计

sql

-- 创建客户表
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    phone VARCHAR(20),
    registration_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 创建产品表
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    category VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_amount DECIMAL(12, 2) NOT NULL,
    shipping_address VARCHAR(255) NOT NULL,
    payment_method VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- 创建订单明细表
CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

常见查询示例

获取销售报表

sql

SELECT 
    p.category,
    SUM(oi.quantity) as total_sold,
    SUM(oi.quantity * oi.price) as total_revenue
FROM 
    order_items oi
JOIN 
    products p ON oi.product_id = p.product_id
JOIN 
    orders o ON oi.order_id = o.order_id
WHERE 
    o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY 
    p.category
ORDER BY 
    total_revenue DESC;

查找热门产品

sql

SELECT 
    p.product_id, 
    p.name,
    SUM(oi.quantity) as total_ordered
FROM 
    products p
JOIN 
    order_items oi ON p.product_id = oi.product_id
GROUP BY 
    p.product_id, p.name
ORDER BY 
    total_ordered DESC
LIMIT 10;

计算客户生命周期价值

sql

SELECT 
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) as customer_name,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(o.total_amount) as total_spent,
    MIN(o.order_date) as first_order,
    MAX(o.order_date) as last_order,
    DATEDIFF(MAX(o.order_date), MIN(o.order_date)) as days_as_customer
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
GROUP BY 
    c.customer_id, customer_name
ORDER BY 
    total_spent DESC;

贡献者

wkwbk

页面历史

最后编辑于 3 天前

查看完整历史

MySQL | 学习笔记 ​

MySQL 基础 ​

安装与配置 ​

数据库基本操作 ​

数据类型 ​

SQL 语句 ​

数据操作语言 (DML) ​

数据定义语言 (DDL) ​

事务控制 ​

高级特性 ​

函数与操作符 ​

连接查询 ​

子查询 ​

性能优化 ​

索引优化 ​

查询优化 ​

配置优化 ​

高可用和扩展性 ​

备份与恢复 ​

复制 ​

分片与分区 ​

安全性 ​

用户和权限管理 ​

安全最佳实践 ​

实际应用案例 ​

电子商务数据库设计 ​

常见查询示例 ​

贡献者

页面历史

MySQL | 学习笔记

MySQL 基础

安装与配置

数据库基本操作

数据类型

SQL 语句

数据操作语言 (DML)

数据定义语言 (DDL)

事务控制

高级特性

函数与操作符

连接查询

子查询

性能优化

索引优化

查询优化

配置优化

高可用和扩展性

备份与恢复

复制

分片与分区

安全性

用户和权限管理

安全最佳实践

实际应用案例

电子商务数据库设计

常见查询示例