MySQL基础知识点整理

发布时间：2021-09-13 14:22:54 作者：chen
来源：亿速云阅读：108

# MySQL基础知识点整理

## 一、MySQL简介

### 1.1 什么是MySQL
MySQL是一个开源的关系型数据库管理系统（RDBMS），由瑞典MySQL AB公司开发，现属于Oracle旗下产品。它使用最常用的结构化查询语言SQL进行数据库操作。

主要特点：
- 开源免费（社区版）
- 性能高、可靠性好
- 支持跨平台（Windows/Linux/macOS等）
- 支持多种编程语言接口
- 支持多用户、多线程

### 1.2 MySQL版本
- 社区版（MySQL Community Server）：免费开源
- 企业版（MySQL Enterprise Edition）：收费，提供技术支持
- 集群版（MySQL Cluster）

当前主流版本：MySQL 5.7、MySQL 8.0（推荐使用8.0+版本）

## 二、MySQL安装与配置

### 2.1 Windows安装
1. 下载MySQL安装包（.msi）
2. 运行安装向导
3. 配置root密码
4. 选择服务启动类型
5. 完成安装

### 2.2 Linux安装（以Ubuntu为例）
```bash
# 更新软件包列表
sudo apt update

# 安装MySQL服务器
sudo apt install mysql-server

# 安全配置
sudo mysql_secure_installation

# 启动服务
sudo systemctl start mysql

2.3 基本配置

配置文件位置： - Windows: my.ini - Linux: /etc/mysql/my.cnf

常用配置项：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
character-set-server=utf8mb4
default-storage-engine=INNODB

三、MySQL基础操作

3.1 连接MySQL

mysql -u 用户名 -p

3.2 用户与权限管理

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

-- 授权
GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户权限
SHOW GRANTS FOR 'username'@'host';

-- 撤销权限
REVOKE ALL PRIVILEGES ON database.* FROM 'username'@'host';

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

3.3 数据库操作

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

-- 创建数据库
CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 选择数据库
USE dbname;

-- 删除数据库
DROP DATABASE dbname;

四、表操作

4.1 数据类型

常见数据类型： - 整数：TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT - 浮点数：FLOAT, DOUBLE, DECIMAL - 字符串：CHAR, VARCHAR, TEXT, BLOB - 日期时间：DATE, TIME, DATETIME, TIMESTAMP - 其他：ENUM, SET, JSON

4.2 创建表

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    status TINYINT DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4.3 修改表结构

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

-- 修改列
ALTER TABLE users MODIFY COLUMN phone VARCHAR(15);

-- 删除列
ALTER TABLE users DROP COLUMN phone;

-- 重命名表
RENAME TABLE users TO customers;

4.4 约束

PRIMARY KEY：主键约束
FOREIGN KEY：外键约束
UNIQUE：唯一约束
NOT NULL：非空约束
DEFAULT：默认值约束
CHECK：检查约束（MySQL 8.0+）

五、CRUD操作

5.1 插入数据

-- 单条插入
INSERT INTO users (username, password, email) 
VALUES ('admin', '123456', 'admin@example.com');

-- 批量插入
INSERT INTO users (username, password, email) VALUES 
('user1', '111111', 'user1@example.com'),
('user2', '222222', 'user2@example.com');

5.2 查询数据

-- 基本查询
SELECT * FROM users;

-- 条件查询
SELECT username, email FROM users WHERE status = 1;

-- 排序
SELECT * FROM users ORDER BY created_at DESC;

-- 分页
SELECT * FROM users LIMIT 10 OFFSET 20;  -- 第3页，每页10条

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

-- 连接查询
SELECT u.username, o.order_id 
FROM users u 
JOIN orders o ON u.id = o.user_id;

5.3 更新数据

UPDATE users SET password = 'newpassword', status = 0 WHERE id = 1;

5.4 删除数据

DELETE FROM users WHERE id = 1;

-- 清空表（不可恢复）
TRUNCATE TABLE users;

六、索引

6.1 索引类型

普通索引
唯一索引
主键索引
组合索引
全文索引（FULLTEXT）
空间索引（SPATIAL）

6.2 创建索引

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建组合索引
CREATE INDEX idx_name_status ON users(username, status);

6.3 查看索引

SHOW INDEX FROM users;

6.4 删除索引

DROP INDEX idx_username ON users;

七、事务与锁

7.1 事务特性（ACID）

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

7.2 事务操作

START TRANSACTION;
-- 执行SQL语句
COMMIT;  -- 或 ROLLBACK;

7.3 隔离级别

READ UNCOMMITTED
READ COMMITTED
REPEATABLE READ（MySQL默认）
SERIALIZABLE

设置隔离级别：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

7.4 锁类型

共享锁（S锁）
排他锁（X锁）
意向锁
记录锁
间隙锁
临键锁

八、存储引擎

8.1 常见存储引擎

InnoDB（默认）：支持事务、行级锁、外键
MyISAM：不支持事务，表级锁，适合读多写少
MEMORY：内存表，数据不持久化
ARCHIVE：归档存储，高压缩比

8.2 引擎选择建议

需要事务支持：InnoDB
只读或读多写少：MyISAM
临时数据：MEMORY
归档数据：ARCHIVE

九、备份与恢复

9.1 备份方法

mysqldump工具

mysqldump -u root -p dbname > backup.sql

导出表数据

SELECT * INTO OUTFILE '/tmp/users.csv' 
FIELDS TERMINATED BY ',' 
FROM users;

9.2 恢复数据

mysql -u root -p dbname < backup.sql

十、性能优化

10.1 查询优化

使用EXPLN分析查询
避免SELECT *
合理使用索引
优化JOIN操作
避免使用OR条件

10.2 索引优化

为常用查询条件创建索引
避免过多索引
使用覆盖索引
定期分析表（ANALYZE TABLE）

10.3 配置优化

# 缓冲区设置
innodb_buffer_pool_size = 4G
key_buffer_size = 256M

# 连接数设置
max_connections = 200
thread_cache_size = 10

# 日志设置
slow_query_log = 1
long_query_time = 2

十一、常见问题

11.1 字符集问题

建议使用utf8mb4字符集，支持完整的Unicode（包括emoji）

11.2 时区问题

SET GLOBAL time_zone = '+8:00';

11.3 忘记root密码

停止MySQL服务
使用–skip-grant-tables启动
修改密码
重启服务

十二、学习资源

官方文档：https://dev.mysql.com/doc/
MySQL必知必会（书籍）
高性能MySQL（书籍）
在线教程：菜鸟教程、RUNOOB等

本文整理了MySQL的基础知识点，适合初学者快速了解MySQL的核心概念和基本操作。实际应用中，还需要结合具体业务场景深入学习高级特性和优化技巧。 “`

注：本文约2500字，采用Markdown格式编写，包含代码块、列表、标题等格式。内容涵盖MySQL安装、配置、基本操作、CRUD、索引、事务、存储引擎等核心知识点。