注意：操作前请先备份数据库

数据库操作

创建数据库

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';  -- 开启慢查询日志