MySQL 速查表

本章提供 MySQL 常用命令和操作的快速参考。

连接和管理

连接命令

# 本地连接
mysql -u root -p

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

# 指定数据库
mysql -u root -p mydb

# 执行 SQL 后退出
mysql -u root -p -e "SELECT VERSION();"

# 导入 SQL 文件
mysql -u root -p mydb < backup.sql

服务管理

# Windows
net start MySQL
net stop MySQL

# Linux
sudo systemctl start mysqld
sudo systemctl stop mysqld
sudo systemctl restart mysqld
sudo systemctl status mysqld

数据库操作

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

-- 查看数据库
SHOW DATABASES;
SHOW CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

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

表操作

-- 创建表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 查看表结构
DESCRIBE users;
DESC users;
SHOW CREATE TABLE users;

-- 修改表
ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);
ALTER TABLE users CHANGE COLUMN name username VARCHAR(50);
ALTER TABLE users ADD INDEX idx_email (email);

-- 删除表
DROP TABLE IF EXISTS users;

-- 清空表
TRUNCATE TABLE users;

数据操作

-- 插入数据
INSERT INTO users (name, email) VALUES ('张三', '[email protected]');
INSERT INTO users (name, email) VALUES 
    ('张三', '[email protected]'),
    ('李四', '[email protected]');

-- 查询数据
SELECT * FROM users;
SELECT name, email FROM users WHERE id = 1;
SELECT DISTINCT name FROM users;
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 更新数据
UPDATE users SET name = '王五' WHERE id = 1;
UPDATE users SET name = '王五', email = '[email protected]' WHERE id = 1;

-- 删除数据
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2024-01-01';

查询语句

SELECT 基础

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

-- 条件查询
SELECT * FROM table_name WHERE condition;

-- 排序
SELECT * FROM table_name ORDER BY column ASC|DESC;

-- 分页
SELECT * FROM table_name LIMIT offset, count;
SELECT * FROM table_name LIMIT count OFFSET offset;

-- 去重
SELECT DISTINCT column FROM table_name;

-- 别名
SELECT column AS alias FROM table_name;
SELECT column alias FROM table_name;

WHERE 条件

-- 比较运算
WHERE column = value
WHERE column != value
WHERE column > value
WHERE column >= value
WHERE column < value
WHERE column <= value

-- 逻辑运算
WHERE condition1 AND condition2
WHERE condition1 OR condition2
WHERE NOT condition

-- 范围查询
WHERE column BETWEEN value1 AND value2
WHERE column IN (value1, value2, value3)
WHERE column NOT IN (value1, value2, value3)

-- 模糊查询
WHERE column LIKE '张%'      -- 以"张"开头
WHERE column LIKE '%张%'     -- 包含"张"
WHERE column LIKE '_张%'     -- 第二个字是"张"

-- NULL 判断
WHERE column IS NULL
WHERE column IS NOT NULL

聚合函数

SELECT COUNT(*) FROM users;                  -- 总行数
SELECT COUNT(column) FROM users;             -- 非NULL行数
SELECT SUM(amount) FROM orders;              -- 求和
SELECT AVG(price) FROM products;             -- 平均值
SELECT MAX(price) FROM products;             -- 最大值
SELECT MIN(price) FROM products;             -- 最小值

-- 分组聚合
SELECT category, COUNT(*) FROM products GROUP BY category;
SELECT category, AVG(price) FROM products GROUP BY category HAVING AVG(price) > 100;

JOIN 连接

-- 内连接
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id;

-- 左连接
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.table1_id;

-- 右连接
SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.table1_id;

-- 自连接
SELECT a.name, b.name FROM users a JOIN users b ON a.manager_id = b.id;

子查询

-- WHERE 子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- FROM 子查询
SELECT * FROM (SELECT id, name FROM users) AS t;

-- EXISTS 子查询
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

索引操作

-- 创建索引
CREATE INDEX idx_name ON users(name);
CREATE UNIQUE INDEX idx_email ON users(email);
CREATE INDEX idx_composite ON users(last_name, first_name);

-- 查看索引
SHOW INDEX FROM users;

-- 删除索引
DROP INDEX idx_name ON users;
ALTER TABLE users DROP INDEX idx_name;

-- 强制使用索引
SELECT * FROM users FORCE INDEX (idx_name) WHERE name = '张三';

用户和权限

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

-- 授权
GRANT ALL PRIVILEGES ON mydb.* TO 'user'@'localhost';
GRANT SELECT, INSERT ON mydb.users TO 'user'@'localhost';

-- 查看权限
SHOW GRANTS FOR 'user'@'localhost';

-- 撤销权限
REVOKE INSERT ON mydb.* FROM 'user'@'localhost';

-- 修改密码
ALTER USER 'user'@'localhost' IDENTIFIED BY 'new_password';

-- 删除用户
DROP USER 'user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

事务

-- 开始事务
START TRANSACTION;
BEGIN;

-- 提交
COMMIT;

-- 回滚
ROLLBACK;

-- 保存点
SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT savepoint_name;
RELEASE SAVEPOINT savepoint_name;

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

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

备份和恢复

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

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

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

# 只备份表结构
mysqldump -u root -p --no-data mydb > schema.sql

# 只备份数据
mysqldump -u root -p --no-create-info mydb > data.sql

# 恢复
mysql -u root -p mydb < backup.sql

性能分析

-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 详细执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';

-- 查看表状态
SHOW TABLE STATUS LIKE 'users';

-- 查看索引使用情况
SELECT * FROM sys.schema_index_statistics 
WHERE table_schema = 'mydb';

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

系统信息

-- 版本信息
SELECT VERSION();
SELECT @@version;

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

-- 当前用户
SELECT USER();
SELECT CURRENT_USER();

-- 字符集
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

-- 存储引擎
SHOW ENGINES;

-- 状态信息
SHOW STATUS;
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Innodb%';

-- 系统变量
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connections';

常用函数

字符串函数

CONCAT('a', 'b', 'c')           -- 'abc'
CONCAT_WS('-', 'a', 'b', 'c')   -- 'a-b-c'
SUBSTRING('abcdef', 1, 3)       -- 'abc'
LEFT('abcdef', 3)               -- 'abc'
RIGHT('abcdef', 3)              -- 'def'
LENGTH('abcdef')                -- 6
CHAR_LENGTH('你好')             -- 2
LOWER('ABC')                    -- 'abc'
UPPER('abc')                    -- 'ABC'
TRIM('  abc  ')                 -- 'abc'
REPLACE('abc', 'b', 'x')        -- 'axc'
LPAD('abc', 5, '0')             -- '00abc'
RPAD('abc', 5, '0')             -- 'abc00'

数值函数

ABS(-5)                         -- 5
CEIL(1.5)                       -- 2
FLOOR(1.5)                      -- 1
ROUND(1.567, 2)                 -- 1.57
TRUNCATE(1.567, 2)              -- 1.56
MOD(10, 3)                      -- 1
POWER(2, 3)                     -- 8
SQRT(16)                        -- 4
RAND()                          -- 0~1 随机数

日期函数

NOW()                           -- 当前日期时间
CURDATE()                       -- 当前日期
CURTIME()                       -- 当前时间
YEAR('2024-01-15')              -- 2024
MONTH('2024-01-15')             -- 1
DAY('2024-01-15')               -- 15
DATE_FORMAT(NOW(), '%Y-%m-%d')  -- 格式化日期
DATE_ADD(NOW(), INTERVAL 7 DAY) -- 加 7 天
DATE_SUB(NOW(), INTERVAL 1 MONTH) -- 减 1 月
DATEDIFF('2024-01-20', '2024-01-01') -- 19 (天数差)
TIMESTAMPDIFF(DAY, '2024-01-01', '2024-01-20') -- 19

流程控制

-- IF
SELECT IF(score >= 60, '及格', '不及格') FROM students;

-- CASE
SELECT 
    CASE 
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        WHEN score >= 60 THEN '及格'
        ELSE '不及格'
    END AS grade
FROM students;

-- NULL 处理
SELECT IFNULL(column, default_value) FROM table;
SELECT COALESCE(column1, column2, default_value) FROM table;
SELECT NULLIF(column1, column2) FROM table;

小结

本章提供了 MySQL 常用命令和操作的快速参考，包括：

1. 连接和服务管理
2. 数据库和表操作
3. 数据查询和操作
4. 索引和用户管理
5. 事务和备份恢复
6. 性能分析和常用函数

建议将本页面收藏，作为日常开发的快速参考。