MySQL

┌─────────────────────────────────────┐
│         连接层(Connection Layer)      │
│  - 连接管理、认证授权、线程池           │
│  - 处理客户端连接请求                  │
└─────────────────────────────────────┘
              ↓
┌─────────────────────────────────────┐
│         服务层(Server Layer)          │
│  - SQL 解析器(Parser)                │
│  - 查询优化器(Optimizer)              │
│  - 查询缓存(Query Cache,8.0已移除)    │
│  - 执行器(Executor)                  │
└─────────────────────────────────────┘
              ↓
┌─────────────────────────────────────┐
│      存储引擎层(Storage Engine)       │
│  - InnoDB(默认)                      │
│  - MyISAM                           │
│  - Memory                           │
│  - 其他插件式引擎                     │
└─────────────────────────────────────┘
              ↓
┌─────────────────────────────────────┐
│        存储层(Storage Layer)          │
│  - 数据文件(.ibd, .frm)              │
│  - 日志文件(redo log, undo log)      │
│  - 配置文件(my.cnf/my.ini)           │
└─────────────────────────────────────┘

# CentOS/RHEL
sudo yum install mysql-server

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

# 启动 MySQL 服务
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 获取临时密码(MySQL 5.7+)
sudo grep 'temporary password' /var/log/mysqld.log

# 登录 MySQL
mysql -u root -p

# 下载 MySQL 官方 Yum 源
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

# 安装 Yum 源
sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm

# 安装 MySQL
sudo yum install mysql-server

# 启动 MySQL
sudo systemctl start mysqld
sudo systemctl enable mysqld

# 查看初始密码
sudo grep 'temporary password' /var/log/mysqld.log

# 修改 root 密码
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword123!';

# 使用 Homebrew 安装
brew install mysql

# 启动 MySQL 服务
brew services start mysql

# 初始化安全设置
mysql_secure_installation

# 登录 MySQL
mysql -u root -p

[mysqld]
# 基础配置
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /tmp/mysql.sock

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

# 存储引擎
default-storage-engine = InnoDB

# 连接配置
max_connections = 200
max_connect_errors = 100

# 缓冲池配置(InnoDB)
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 4

# 日志配置
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# 二进制日志(主从复制)
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

[client]
default-character-set = utf8mb4
port = 3306
socket = /tmp/mysql.sock

[mysql]
default-character-set = utf8mb4

-- 创建数据库(指定字符集和排序规则)
CREATE DATABASE shop
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

-- 查看数据库
SHOW DATABASES;

-- 查看数据库创建语句
SHOW CREATE DATABASE shop;

-- 选择数据库
USE shop;

-- 删除数据库
DROP DATABASE IF EXISTS shop;

-- 创建用户表
CREATE TABLE user_account (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
    username VARCHAR(64) NOT NULL COMMENT '用户名',
    email VARCHAR(128) NOT NULL COMMENT '邮箱',
    phone VARCHAR(20) COMMENT '手机号',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态:1正常,0禁用',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 唯一约束
    UNIQUE KEY uk_email (email),
    UNIQUE KEY uk_phone (phone),
    -- 索引
    KEY idx_status_created_at (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户账户表';

-- 查看表结构
DESC user_account;

-- 查看表创建语句
SHOW CREATE TABLE user_account;

-- 查看表索引
SHOW INDEX FROM user_account;

-- 删除表
DROP TABLE IF EXISTS user_account;

-- 插入单条数据
INSERT INTO user_account (username, email, phone, status)
VALUES ('zhangsan', 'zhangsan@example.com', '13800138000', 1);

-- 插入多条数据
INSERT INTO user_account (username, email, phone, status) VALUES
('lisi', 'lisi@example.com', '13900139000', 1),
('wangwu', 'wangwu@example.com', '13700137000', 1);

-- 从查询结果插入
INSERT INTO user_account_backup (username, email, phone)
SELECT username, email, phone FROM user_account WHERE status = 1;

-- 查询所有数据
SELECT * FROM user_account;

-- 查询指定字段
SELECT id, username, email FROM user_account;

-- 条件查询
SELECT * FROM user_account WHERE status = 1;

-- 排序查询
SELECT * FROM user_account ORDER BY created_at DESC;

-- 分页查询
SELECT * FROM user_account LIMIT 0, 10;  -- 跳过0条,取10条
SELECT * FROM user_account LIMIT 10 OFFSET 0;  -- MySQL 8.x 语法

-- 聚合查询
SELECT status, COUNT(*) as count FROM user_account GROUP BY status;

-- 连接查询
SELECT u.username, o.order_no
FROM user_account u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

-- 更新单个字段
UPDATE user_account SET status = 0 WHERE id = 1;

-- 更新多个字段
UPDATE user_account
SET status = 0, updated_at = NOW()
WHERE id = 1;

-- 条件更新
UPDATE user_account
SET status = 0
WHERE created_at < '2020-01-01' AND status = 1;

-- 删除指定数据
DELETE FROM user_account WHERE id = 1;

-- 条件删除
DELETE FROM user_account WHERE status = 0;

-- 删除所有数据(保留表结构)
DELETE FROM user_account;

-- 清空表(重置自增ID)
TRUNCATE TABLE user_account;

-- 创建用户
CREATE USER 'appuser'@'%' IDENTIFIED BY 'Password123!';

-- 创建用户并指定认证插件
CREATE USER 'appuser'@'%' IDENTIFIED WITH mysql_native_password BY 'Password123!';

-- 修改用户密码
ALTER USER 'appuser'@'%' IDENTIFIED BY 'NewPassword456!';

-- 删除用户
DROP USER 'appuser'@'%';

-- 授权
GRANT ALL PRIVILEGES ON shop.* TO 'appuser'@'%';

-- 授予特定权限
GRANT SELECT, INSERT, UPDATE ON shop.* TO 'appuser'@'%';

-- 授予所有数据库权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看用户权限
SHOW GRANTS FOR 'appuser'@'%';

-- 回收权限
REVOKE INSERT, UPDATE ON shop.* FROM 'appuser'@'%';

-- 回收所有权限
REVOKE ALL PRIVILEGES ON shop.* FROM 'appuser'@'%';

EXPLAIN SELECT * FROM user_account WHERE status = 1;

-- × 不推荐:utf8 可能导致 emoji 存储失败
CREATE TABLE user (name VARCHAR(100)) CHARSET=utf8;

-- √ 推荐:使用 utf8mb4
CREATE TABLE user (name VARCHAR(100)) CHARSET=utf8mb4;

# 登录 MySQL
mysql -u root -p

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

# 执行 SQL 文件
mysql -u root -p < backup.sql

# 导出数据库
mysqldump -u root -p shop > shop_backup.sql

# 导出表
mysqldump -u root -p shop user_account > user_account.sql

# 导入数据库
mysql -u root -p shop < shop_backup.sql