MySQL基礎知識點整理

# 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 備份方法

1. mysqldump工具

mysqldump -u root -p dbname > backup.sql

1. 導出表數據

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密碼

1. 停止MySQL服務
2. 使用–skip-grant-tables啟動
3. 修改密碼
4. 重啟服務

十二、學習資源

1. 官方文檔：https://dev.mysql.com/doc/
2. MySQL必知必會（書籍）
3. 高性能MySQL（書籍）
4. 在線教程：菜鳥教程、RUNOOB等

本文整理了MySQL的基礎知識點，適合初學者快速了解MySQL的核心概念和基本操作。實際應用中，還需要結合具體業務場景深入學習高級特性和優化技巧。 “`

注：本文約2500字，采用Markdown格式編寫，包含代碼塊、列表、標題等格式。內容涵蓋MySQL安裝、配置、基本操作、CRUD、索引、事務、存儲引擎等核心知識點。