注意：操作前请先备份数据库
数据库操作
创建数据库
CREATE DATABASE dbname;
CREATE DATABASE IF NOT EXISTS dbname; -- 不存在时创建
CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- 指定字符集
查看数据库
SHOW DATABASES; -- 列出所有数据库
SHOW CREATE DATABASE dbname; -- 查看数据库创建语句
修改数据库
ALTER DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
删除数据库
DROP DATABASE dbname;
DROP DATABASE IF EXISTS dbname; -- 存在时删除
表操作
创建表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
查看表
SHOW TABLES; -- 列出所有表
DESCRIBE users; -- 查看表结构
SHOW CREATE TABLE users; -- 查看表创建语句
修改表
ALTER TABLE users ADD COLUMN age INT AFTER email; -- 添加列
ALTER TABLE users MODIFY COLUMN username VARCHAR(100); -- 修改列类型
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(50); -- 重命名列
ALTER TABLE users DROP COLUMN age; -- 删除列
ALTER TABLE users RENAME TO customers; -- 重命名表
删除表
DROP TABLE users;
DROP TABLE IF EXISTS users; -- 存在时删除
数据操作 (CRUD)
插入数据 (Create)
INSERT INTO users (username, email, password)
VALUES ('john_doe', 'john@example.com', 'hashed_password');
-- 批量插入
INSERT INTO users (username, email, password) VALUES
('user1', 'user1@example.com', 'hash1'),
('user2', 'user2@example.com', 'hash2');
查询数据 (Read)
SELECT * FROM users; -- 查询所有数据
SELECT username, email FROM users; -- 查询特定列
SELECT * FROM users WHERE id = 1; -- 条件查询
SELECT * FROM users WHERE email LIKE '%@example.com'; -- 模糊查询
SELECT * FROM users ORDER BY created_at DESC; -- 排序
SELECT * FROM users LIMIT 10 OFFSET 20; -- 分页
更新数据 (Update)
UPDATE users SET email = 'new_email@example.com' WHERE id = 1;
UPDATE users SET password = 'new_hash', updated_at = NOW() WHERE id = 1;
删除数据 (Delete)
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2020-01-01'; -- 删除旧数据
高级查询
聚合函数
SELECT COUNT(*) FROM users; -- 计数
SELECT AVG(age) FROM users; -- 平均值
SELECT SUM(salary) FROM employees; -- 求和
SELECT MAX(salary), MIN(salary) FROM employees; -- 最大最小值
分组查询
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING employee_count > 5;
连接查询
-- 内连接
SELECT u.username, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;
-- 左连接
SELECT u.username, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);
SELECT username FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
索引操作
创建索引
CREATE INDEX idx_email ON users(email); -- 普通索引
CREATE UNIQUE INDEX idx_username ON users(username); -- 唯一索引
查看索引
SHOW INDEX FROM users;
删除索引
DROP INDEX idx_email ON users;
事务控制
START TRANSACTION;
INSERT INTO orders (user_id, amount) VALUES (1, 99.99);
UPDATE accounts SET balance = balance - 99.99 WHERE user_id = 1;
COMMIT; -- 或 ROLLBACK 回滚
用户权限管理
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; -- 创建用户
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost'; -- 授予权限
FLUSH PRIVILEGES; -- 刷新权限
REVOKE ALL PRIVILEGES ON dbname.* FROM 'username'@'localhost'; -- 撤销权限
实用命令
SHOW PROCESSLIST; -- 查看当前连接
KILL process_id; -- 终止查询
`EXPLAIN` SELECT * FROM users WHERE id = 1; -- 分析查询
SET GLOBAL slow_query_log = 'ON'; -- 开启慢查询日志
