常用 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;

注意事项:

  1. 权限分配应遵循最小权限原则
  2. 生产环境避免使用 ‘% 主机通配符
  3. 密码复杂度要求:至少12位,包含大小写字母、数字和特殊字符
  4. 执行权限变更后需刷新权限:
    FLUSH PRIVILEGES;
  5. 重要操作前建议开启事务测试
  6. 删除操作建议先做备份

可通过以下命令查看用户列表:

SELECT user, host FROM mysql.user;

建议配合使用 mysqldump 进行定期备份:

mysqldump -u root -p --all-databases > full_backup.sql