MySQL 数据库基本操作指南

MySQL 作为世界上最流行的开源关系型数据库,掌握其核心操作是每一位开发者和DBA的必备技能。本文将系统地介绍 MySQL 的权限管理、数据操作和核心对象创建,并厘清一些关键概念。

一、权限管理:角色与用户

自 MySQL 8.0 版本起,正式引入了“角色”的概念,使得权限管理变得更加高效和规范。

1. 创建角色并授予权限

角色是权限的集合,可以批量地授予给用户,简化管理。

-- 1.1 创建角色(例如:创建只读角色和读写角色)
CREATE ROLE 'read_only_role', 'read_write_role';

-- 1.2 为角色授予权限
-- 授予 read_only_role 对特定数据库(mydb)所有表的只读权限
GRANT SELECT ON mydb.* TO 'read_only_role';

-- 授予 read_write_role 对特定数据库的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'read_write_role';

-- 也可以授予所有数据库的权限(慎用)
-- GRANT ALL PRIVILEGES ON *.* TO 'super_admin_role';

2. 创建用户并授予角色

用户是用来连接数据库的实体,我们可以将定义好的角色授予给用户。

-- 2.1 创建用户
CREATE USER 'zhangsan'@'%' IDENTIFIED BY 'StrongPassword123!'; -- 允许从任何主机连接
CREATE USER 'lisi'@'localhost' IDENTIFIED BY 'AnotherPassword456!'; -- 只允许本地连接

-- 2.2 将角色授予用户
GRANT 'read_only_role' TO 'zhangsan'@'%';
GRANT 'read_write_role' TO 'lisi'@'localhost';

-- 2.3 重要:授予角色后,需要激活角色权限
-- 方式一:为用户设置默认角色(用户下次登录时自动激活)
SET DEFAULT ROLE 'read_write_role' TO 'lisi'@'localhost';

-- 方式二:在当前会话中激活所有已授予的角色
SET ROLE ALL;

-- 查看当前激活的角色
SELECT CURRENT_ROLE();

3. 回收权限/角色

当需要撤销用户的权限时,可以使用 REVOKE 命令。

-- 3.1 从角色中回收特定权限
REVOKE INSERT, UPDATE ON mydb.* FROM 'read_write_role';

-- 3.2 从用户手中回收角色
REVOKE 'read_write_role' FROM 'lisi'@'localhost';

-- 3.3 直接回收用户的特定权限(不通过角色)
REVOKE DELETE ON mydb.employees FROM 'zhangsan'@'%';

二、基本数据操作:增删改查 (CRUD)

以一张简单的 employees 表为例。

-- 创建示例表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

1. 增 (Create – INSERT)

INSERT INTO employees (name, department, salary) VALUES
('张三', '研发部', 15000.00),
('李四', '市场部', 12000.00);

2. 查 (Read – SELECT)

-- 查询所有数据
SELECT * FROM employees;

-- 条件查询
SELECT name, salary FROM employees WHERE department = '研发部' AND salary > 10000;

-- 排序和限制
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

3. 改 (Update – UPDATE)

-- 为研发部员工加薪10%
UPDATE employees SET salary = salary * 1.1 WHERE department = '研发部';

-- 更新特定记录
UPDATE employees SET department = '人事部' WHERE id = 2;

4. 删 (Delete – DELETE)

-- 删除特定记录(务必谨慎!)
DELETE FROM employees WHERE id = 2;

-- 清空表(更高效,但无法回滚)
TRUNCATE TABLE employees;

三、创建视图 (View)

视图是一个虚拟表,其内容由查询定义。它可以简化复杂查询、隐藏数据复杂性并提供额外的安全层。

-- 创建一个视图,只显示研发部员工的名字和工资,隐藏敏感信息
CREATE VIEW v_rd_employees AS
SELECT id, name, salary
FROM employees
WHERE department = '研发部';

-- 之后可以像查询普通表一样查询视图
SELECT * FROM v_rd_employees WHERE salary > 12000;

四、创建存储过程 (Stored Procedure)

存储过程是一组为了完成特定功能的 SQL 语句集,经编译后存储在数据库中。

-- 修改语句分隔符,以便在过程中使用分号
DELIMITER //

-- 创建一个存储过程,用于提高某个部门全体员工的工资
CREATE PROCEDURE raise_salary(IN dept_name VARCHAR(50), IN raise_rate DECIMAL(3,2))
BEGIN
    UPDATE employees
    SET salary = salary * (1 + raise_rate)
    WHERE department = dept_name;
END //

-- 将分隔符改回分号
DELIMITER ;

-- 调用存储过程,给市场部加薪5%
CALL raise_salary('市场部', 0.05);

五、关键概念辨析:模式、实例、数据库

这些概念容易混淆,但在 MySQL 的语境下可以这样理解:

概念英文解释
实例Instance指正在运行的 MySQL 服务器进程。它是位于内存中的一套程序和数据结构,负责管理数据库。你可以在同一台物理服务器上运行多个 MySQL 实例,它们使用不同的端口、socket 和数据目录。
数据库Database在 MySQL 中,“数据库”“模式”同义词CREATE DATABASECREATE SCHEMA 命令是等价的。它是一组命名对象(如表、视图、存储过程等)的逻辑容器,用于隔离不同应用程序的数据。
模式Schema同上。在 MySQL 中,模式就是数据库。但在更广义的数据库理论中,“模式”有时指数据库的结构(表、列、关系等)。

简单比喻:

  • 实例 就像是一个银行总行,它是一个正在运营的机构。
  • 数据库/模式 就像是总行下属的不同分行(如个人贷款部、对公业务部)。它们在同一个机构(实例)下,但管理着不同的业务和数据。
  • 表、视图 等就是分行里的保险柜、文件柜,是实际存放数据的地方。

总结

本文涵盖了 MySQL 的核心操作流程:

  1. 权限控制:通过角色(Role)批量管理权限,再授予用户(User),实现了灵活且安全的访问控制。
  2. 数据操作:使用基本的 CRUD 语句对数据进行管理。
  3. 高级对象:利用视图简化查询和增强安全,使用存储过程封装复杂业务逻辑。
  4. 概念辨析:明确了 MySQL 中实例(运行的服务)、数据库/模式(逻辑容器)的区别。

掌握这些操作,您就具备了进行 MySQL 数据库日常开发和管理工作的坚实基础。

This entry was posted in 应用. Bookmark the permalink.