常用 MySQL 命令的实用示例:
一、基础 CRUD 操作
-- 增 Insert
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
-- 删 Delete
DELETE FROM users WHERE id = 5;
-- 条件删除
DELETE FROM orders WHERE order_date < '2023-01-01';
-- 改 Update
UPDATE products SET price = 19.99 WHERE product_id = 1001;
-- 多字段更新
UPDATE employees SET salary = salary*1.1, department = 'IT' WHERE emp_id = 42;
-- 查 Select
SELECT * FROM customers WHERE country = 'USA';
-- 复杂查询
SELECT name, AVG(score) as avg_score
FROM students
WHERE class = 'A'
GROUP BY name
HAVING avg_score > 80
ORDER BY avg_score DESC
LIMIT 10;
二、用户管理
-- 创建用户
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
-- 修改密码(MySQL 8.0+)
ALTER USER 'existing_user'@'%' IDENTIFIED BY 'NewSecurePassword456!';
-- 删除用户
DROP USER 'old_user'@'localhost';
三、权限管理
-- 授予完整权限(慎用)
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%' WITH GRANT OPTION;
-- 授予数据库级权限
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_user'@'localhost';
-- 授予表级权限
GRANT SELECT (id, name), UPDATE (email) ON company.employees TO 'hr_user'@'%';
-- 撤销权限
REVOKE DELETE ON inventory.* FROM 'warehouse_user'@'10.0.0.%';
-- 查看权限
SHOW GRANTS FOR 'report_user'@'localhost';
四、其他重要操作
-- 创建数据库时指定字符集
CREATE DATABASE blog_db
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 创建带索引的表
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_title (title),
FULLTEXT idx_content (content)
) ENGINE=InnoDB;
-- 事务处理示例
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;
注意事项:
- 权限分配应遵循最小权限原则
- 生产环境避免使用 ‘% 主机通配符
- 密码复杂度要求:至少12位,包含大小写字母、数字和特殊字符
- 执行权限变更后需刷新权限:
FLUSH PRIVILEGES;
- 重要操作前建议开启事务测试
- 删除操作建议先做备份
可通过以下命令查看用户列表:
SELECT user, host FROM mysql.user;
建议配合使用 mysqldump 进行定期备份:
mysqldump -u root -p --all-databases > full_backup.sql