六、多表查询1. 多表关系的产生在实际业务中数据通常不会全部存在一张表里而是根据业务规则拆分到多张表中。这样可以减少数据冗余提高维护效率。表与表之间存在三种常见关系· 一对多多对一例如一个部门有多个员工一个员工只能属于一个部门。这是最常用的关系。· 多对多例如一个学生可以选择多门课程一门课程也可以被多名学生选择。需要借助中间表实现。· 一对一例如一个人只有一个身份证一个身份证只能对应一个人。相对较少使用。当我们需要同时查询多张表中的数据时比如查询员工姓名及其所属部门名称就必须使用多表查询。2. 笛卡尔积的概念与消除方法笛卡尔积假设有两个集合 A 和 BA 有 m 条记录B 有 n 条记录如果不加任何条件地将它们相乘会得到 m × n 条记录。这些记录中大部分是无意义的。例如· A学生表张三、李四· B课程表语文、数学· 笛卡尔积结果张三-语文、张三-数学、李四-语文、李四-数学在多表查询中如果我们直接写 SELECT * FROM emp, dept就会产生“每个员工匹配所有部门”的无效数据。多表查询的核心就是用连接条件消除无用的笛卡尔积记录。为了后续讲解我们先准备两张表-- 部门表结构 CREATE TABLE dept ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL ); -- 部门数据初始化 INSERT INTO dept(name) VALUES (开发部), (市场部), (财务部); -- 员工表结构 CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10) NOT NULL, gender CHAR(1) NOT NULL, salary DECIMAL(10,2) NOT NULL, join_date DATE NOT NULL, dept_id INT NOT NULL, FOREIGN KEY (dept_id) REFERENCES dept(id) ); -- 员工数据初始化 INSERT INTO emp(name, gender, salary, join_date, dept_id) VALUES (孙悟空, 男, 7200.00, 2013-02-24, 1), (猪八戒, 男, 3600.00, 2010-12-02, 2), (唐僧, 男, 9000.00, 2008-08-08, 2), (白骨精, 女, 5000.00, 2015-10-07, 3), (蜘蛛精, 女, 4500.00, 2011-03-14, 1);3. 内连接查询内连接返回两张表中连接条件匹配的记录不匹配的记录不会出现在结果中。隐式内连接使用 WHERE 子句来指定连接条件消除笛卡尔积。-- 查询员工及其所属部门信息 SELECT * FROM emp JOIN dept ON emp.dept_id dept.id; -- 使用表别名查询员工姓名、性别及部门名称 SELECT e.name, e.gender, d.name AS dept_name FROM emp e JOIN dept d ON e.dept_id d.id;输出NAMEgenderdept_name孙悟空男开发部猪八戒男市场部唐僧男市场部白骨精女财务部蜘蛛精女开发部显式内连接使用 INNER JOIN ... ON 语法结构更清晰推荐使用。-- 标准内连接语法 SELECT * FROM emp INNER JOIN dept ON emp.dept_id dept.id; -- 简写形式INNER关键字可省略 SELECT * FROM emp JOIN dept ON emp.dept_id dept.id;显式内连接和隐式内连接在性能上没有区别但显式语法更容易阅读特别是多表连接时。4. 外连接查询内连接只返回匹配的数据而外连接会返回一张表的全部记录另一张表没有匹配时用 NULL 填充。左外连接LEFT JOIN返回左表所有记录 右表中匹配的记录。如果右表没有匹配对应字段为 NULL。SELECT e.NAME, e.salary, d.NAME AS dept_name FROM emp e LEFT JOIN dept d ON e.dept_id d.id;适用场景以左表为主需要显示左表的所有记录即使右表没有对应数据。例如如果我们想查看所有员工包括尚未分配部门的员工左外连接可以保证员工全部显示。右外连接RIGHT JOIN返回右表所有记录 左表中匹配的记录。SELECT e.NAME, d.NAME AS dept_name FROM emp e RIGHT JOIN dept d ON e.dept_id d.id;适用场景以右表为主需要显示右表的所有记录。例如显示所有部门即使某个部门没有员工。实际开发中左外连接使用更加频繁因为我们可以通过调整表顺序用左外连接实现右外连接的效果。通常习惯使用 LEFT JOIN。5. 子查询子查询是指在一个查询中嵌套另一个查询SELECT 语句。子查询可以出现在 WHERE、FROM、SELECT 等子句中。将一次查询的结果作为另一个查询的条件或数据源。例如查询工资最高的员工信息。-- 分步查询方法 -- 1. 首先查询最高工资 SELECT MAX(salary) FROM emp; -- 结果为9000 -- 2. 然后查询工资等于最高值的员工信息 SELECT * FROM emp WHERE salary 9000; -- 使用子查询的简洁写法单条SQL实现 SELECT * FROM emp WHERE salary (SELECT MAX(salary) FROM emp);子查询结果作为条件单行单列结果配合 、、、、、 等运算符。-- 查询薪资低于公司平均水平的员工信息 SELECT * FROM emp WHERE salary (SELECT AVG(salary) FROM emp);多行单列结果配合 IN、NOT IN、ANY、ALL 等。-- 查询财务部和市场部的所有员工信息 -- 首先获取财务部和市场部的部门ID SELECT id FROM dept WHERE name IN (财务部, 市场部); -- 返回结果2,3 -- 根据部门ID查询对应员工 SELECT * FROM emp WHERE dept_id IN ( SELECT id FROM dept WHERE name IN (财务部, 市场部) );子查询结果作为临时表多行多列结果将子查询的结果当作一张虚拟表与其他表进行连接查询。-- 查询入职日期在 2011-01-01 之后的员工及其部门信息 -- 方法1子查询作为临时表 SELECT * FROM dept d JOIN (SELECT * FROM emp WHERE join_date 2011-01-01) e ON d.id e.dept_id; -- 方法2普通内连接更简洁 SELECT * FROM emp e, dept d WHERE e.dept_id d.id AND e.join_date 2011-01-01;子查询作为临时表时必须为子查询指定别名例如上面的 e。6.5 表的拼接当需要将两个结构完全相同的查询结果合并成一个结果集时可以使用 UNION 或 UNION ALL。· UNION合并后自动去重按所有列比较。· UNION ALL合并后保留所有行不去重效率更高。拼接条件两个查询的列数必须相同对应列的数据类型必须兼容通常要求完全一致。-- 查询工资大于7000 或 性别为女 的员工会去重 SELECT * FROM emp WHERE salary 7000 UNION SELECT * FROM emp WHERE gender 女; -- 使用 UNION ALL 不去重效率高 SELECT * FROM emp WHERE salary 7000 UNION ALL SELECT * FROM emp WHERE gender 女;注意UNION 会对整个结果集进行排序去重数据量大时性能较差。如果确定没有重复或可以接受重复优先使用 UNION ALL。七、事务管理1. 事务的基本概念事务Transaction是一组逻辑操作单元要么全部成功要么全部失败。例如银行转账从A账户扣款和向B账户加款必须同时成功不能只执行一半。如果中间出现错误整个操作应该回滚到最初状态。事务的基本操作MySQL 中使用以下命令管理事务命令作用START TRANSACTION开启事务也可用BEGIN替代COMMIT提交事务使当前事务中的所有修改永久生效ROLLBACK回滚事务撤销当前事务中的所有未提交修改SAVEPOINT 名称设置保存点允许后续通过ROLLBACK TO SAVEPOINT回滚到指定保存点状态经典案例转账操作-- 创建账户表 CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(10), balance DOUBLE ); -- 插入测试数据 INSERT INTO account(NAME, balance) VALUES (zhangsan, 1000), (lisi, 1000); -- 张三给李四转账500元 START TRANSACTION; -- 开启事务 UPDATE account SET balance balance - 500 WHERE NAME zhangsan; UPDATE account SET balance balance 500 WHERE NAME lisi; -- 假设这里程序报错则执行 ROLLBACK -- 一切正常执行 COMMIT COMMIT; -- 如果中途发现错误可以回滚 -- ROLLBACK;设置保存点示例START TRANSACTION; UPDATE account SET balance balance - 100 WHERE NAME zhangsan; SAVEPOINT sp1; UPDATE account SET balance balance 100 WHERE NAME lisi; -- 发现第二个更新有问题回滚到保存点 ROLLBACK TO SAVEPOINT sp1; COMMIT; -- 只保留了第一个更新事务提交方式· MySQL 默认自动提交每条 DML增删改语句执行后自动 COMMIT。· 手动提交需要先 START TRANSACTION再手动 COMMIT / ROLLBACK。· 查看自动提交状态SELECT autocommit;1代表自动提交0代表手动· 修改自动提交SET autocommit 0;Oracle 数据库默认是手动提交而 MySQL 默认自动提交这点需要注意。2. 事务的四大特性ACID事务必须同时满足以下四个特性缺一不可。特性英文说明原子性Atomicity事务中的所有操作是一个不可分割的整体要么全部执行要么全部不执行一致性Consistency事务执行前后数据库从一个一致状态变为另一个一致状态。如转账前后总金额不变隔离性Isolation多个事务并发执行时相互隔离互不干扰。一个事务看不到其他事务未提交的中间状态持久性Durability事务一旦提交对数据库的改变是永久的即使系统故障也不会丢失示例解释一致性张三和李四共有2000元无论转账多少次、金额多少只要事务成功提交两人余额之和永远为2000元。3. 事务的隔离级别当多个事务同时操作同一批数据时如果隔离性做得不够会引发一些问题。设置不同的隔离级别可以解决这些问题。并发事务的三大问题问题类型英文术语描述示例脏读Dirty Read事务读取了另一个未提交事务修改的数据事务B修改数据但未提交事务A读取该数据若事务B回滚事务A读取的数据将无效不可重复读Non-repeatable Read同一事务内两次读取同一数据结果不一致因中间有其他已提交事务修改事务A首次查询张三余额1000元事务B修改余额为900元并提交事务A再次查询得到900元结果不一致幻读Phantom Read同一事务内两次查询返回的记录数不同因中间有其他事务插入或删除数据事务A查询显示5名员工事务B新增1名员工并提交事务A再次查询显示6名员工如同出现幻觉MySQL 的四种隔离级别了解隔离级别脏读不可重复读幻读并发性能READ UNCOMMITTED读未提交可能可能可能最高READ COMMITTED读已提交不会可能可能较高REPEATABLE READ可重复读MySQL默认不会不会可能*较低SERIALIZABLE串行化不会不会不会最低*MySQL 的 REPEATABLE READ 通过 MVCC多版本并发控制解决了幻读问题所以实际使用中不会出现幻读。各级别说明· READ UNCOMMITTED最低级别任何问题都无法避免几乎不用。· READ COMMITTED只能读取已提交的数据避免脏读。Oracle 默认级别。· REPEATABLE READ保证同一事务内多次读取同一数据结果一致避免脏读和不可重复读。MySQL 默认级别。· SERIALIZABLE强制事务串行执行最高安全但效率极低通常不用。隔离级别配置查看当前隔离级别-- MySQL 5.x 版本查询事务隔离级别 SELECT tx_isolation; -- MySQL 8.x 版本查询事务隔离级别 SELECT transaction_isolation;设置隔离级别-- 配置全局事务隔离级别对所有新建连接生效 SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 配置当前会话隔离级别仅影响当前连接 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;示例修改默认隔离级别为 READ COMMITTEDSET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;修改全局隔离级别后已存在的连接不受影响新连接才会使用新的级别。八、索引1. 索引概述索引是数据库中用于提高数据检索速度的一种数据结构类似于书籍的目录。通过对表中一列或多列的值进行排序并存储MySQL 可以快速定位到目标数据而无需扫描整个表。索引的作用主要包括· 大幅提升查询速度对于 WHERE、ORDER BY、GROUP BY 子句中的列索引可以显著减少扫描行数。· 保证数据唯一性唯一索引可以防止重复数据。· 加速表连接在多表查询时对连接列建立索引可提升 JOIN 性能。索引的优势与代价优势· 提高 SELECT 查询效率尤其是大表。· 对 ORDER BY 和 GROUP BY 也有优化作用。代价· 占用存储空间索引本身也是一张表会占用额外的磁盘空间。· 降低 DML 性能对表进行 INSERT、UPDATE、DELETE 时MySQL 需要同步更新索引因此增删改操作会变慢。· 索引维护成本不合理的索引可能导致优化器选错索引反而降低性能。经验法则对查询频繁的字段、需要排序或分组的字段建立索引避免在小表或更新非常频繁的表上建立过多索引。2. 普通索引普通索引INDEX是最基本的索引类型没有任何限制允许重复值和 NULL 值。-- 基本语法 CREATE INDEX index_name ON table_name (column_name); -- 示例在员工表的姓名字段上创建索引 CREATE INDEX idx_emp_name ON emp(name);修改表添加索引-- 为指定表添加索引 ALTER TABLE table_name ADD INDEX index_name (column_name); -- 示例为员工表添加薪资字段索引 ALTER TABLE emp ADD INDEX idx_salary (salary);创建表时直接指定索引CREATE TABLE mytable ( id INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX (username) -- 创建username字段的索引 );删除索引-- 删除指定表上的索引 DROP INDEX indexName ON table_name; -- 示例删除emp表的idx_name索引 DROP INDEX idx_name ON emp;3. 唯一索引唯一索引UNIQUE INDEX与普通索引类似但要求索引列的值必须唯一允许 NULL 值多个 NULL 不视为重复。唯一索引既保证数据唯一性也加速查询。-- 创建唯一索引语法 CREATE UNIQUE INDEX index_name ON table_name(column_name); -- 示例为用户表的手机号字段创建唯一索引 CREATE UNIQUE INDEX idx_user_phone ON user(phone);修改表添加唯一索引ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);创建表时指定唯一索引CREATE TABLE user ( id INT PRIMARY KEY, email VARCHAR(100), UNIQUE INDEX idx_email (email) );注意如果表中已有重复数据创建唯一索引会失败。需要先清理重复值。4. ALTER TABLE 管理索引使用 ALTER TABLE 命令可以方便地添加或删除各种类型的索引。添加主键索引:主键索引是一种特殊的唯一索引要求列值非空且唯一。一张表只能有一个主键。-- 添加主键要求列已设为 NOT NULL ALTER TABLE mytable ADD PRIMARY KEY (id);添加唯一索引ALTER TABLE mytable ADD CONSTRAINT index_name UNIQUE (column_list);添加普通索引ALTER TABLE mytable ADD INDEX index_name (column_list);添加全文索引全文索引FULLTEXT用于对文本内容进行高效搜索如 MATCH AGAINST。ALTER TABLE mytable ADD FULLTEXT INDEX index_name (column_list);删除索引-- 删除普通索引或唯一索引需指定索引名称 ALTER TABLE mytable DROP INDEX index_name; -- 删除主键索引无需指定名称 ALTER TABLE mytable DROP PRIMARY KEY;删除主键时如果主键列设置了 AUTO_INCREMENT需要先移除自增长属性。5. 查看索引信息使用 SHOW INDEX 命令可以查看表中所有索引的详细信息。-- 查看指定表的所有索引信息 SHOW INDEX FROM table_name; -- 示例查询emp表的索引情况 SHOW INDEX FROM emp;输出关键字段说明字段名说明Table当前索引所属的表名Non_unique索引唯一性标识0表示唯一索引1表示非唯一索引Key_name索引名称Seq_in_index联合索引中的字段顺序编号从1开始计数Column_name被索引的列名Collation排序规则A表示升序NULL表示不可排序Cardinality索引唯一值估算数数值越大查询优化器越倾向于使用该索引Index_type索引存储类型如BTREE、HASH等为了获得更好的可读性可以添加 \G 选项按行显示SHOW INDEX FROM emp\G九、视图1. 视图概述视图View是基于 SQL 语句结果集的可视化虚拟表。它不存储实际数据只保存查询的定义。视图可以包含表的全部或部分记录也可以由一张或多张表创建。当查询视图时MySQL 会动态执行视图定义中的 SELECT 语句并返回结果。简单理解视图就像一个“预定义的查询”我们可以像操作普通表一样对它进行查询。-- 创建简化版员工视图仅展示基本信息 CREATE VIEW v_emp_simple AS SELECT id, name, gender, dept_id FROM emp;视图在实际开发中有三个主要价值价值说明简化查询将复杂的多表连接和子查询封装为视图用户只需执行SELECT * FROM view即可获取数据无需重复编写复杂SQL语句数据安全通过视图隐藏敏感字段如工资、密码等仅向特定用户开放必要的列确保数据安全性数据独立当源表结构变更如新增列或修改列名时只需调整视图定义使用视图的应用程序无需修改代码例如员工表中有工资字段普通查询不应看到。可以创建一个不含工资的视图给人事部门查询使用。2. 视图的基本操作基本语法CREATE VIEW view_name AS SELECT query_statement;完整语法了解即可CREATE [OR REPLACE] [ALGORITHM {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER user] [SQL SECURITY {DEFINER | INVOKER}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]· OR REPLACE如果视图已存在则替换· ALGORITHM视图处理算法MERGE / TEMPTABLE· WITH CHECK OPTION限制通过视图更新数据时需满足视图条件示例创建员工及其部门信息的视图CREATE VIEW v_emp_dept AS SELECT e.NAME, e.gender, e.salary, d.NAME AS dept_name FROM emp e LEFT JOIN dept d ON e.dept_id d.id;使用视图查询-- 查询薪资超过5000的员工部门信息 SELECT * FROM v_emp_dept WHERE salary 5000;修改视图ALTER VIEW 视图名 AS SELECT 新的查询语句; -- 示例修改员工部门视图移除薪资信息 ALTER VIEW v_emp_dept AS SELECT e.name, e.gender, d.name AS dept_name FROM emp e LEFT JOIN dept d ON e.dept_id d.id;删除视图-- 删除指定视图 DROP VIEW 视图名; -- 安全删除视图如果存在 DROP VIEW IF EXISTS v_emp_dept;3. 视图的可更新性可更新视图的条件通过视图可以对基表进行 INSERT、UPDATE、DELETE 操作但必须保证视图是可更新的。可更新视图要求视图中的行与基表中的行具有一对一的关系。不可更新的视图结构如果视图包含以下任何一种结构则为不可更新只读· LIMIT 关键字· 聚合函数SUM()、COUNT() 等· DISTINCT 关键字· GROUP BY 子句· HAVING 子句· UNION 运算符· ORDER BY部分情况· 位于选择列表中的子查询· FROM 子句中包含多个表但某些多表视图仍可能可更新受限· 引用了不可更新的视图· ALGORITHM TEMPTABLE一般不建议对视图执行 DML 操作因为视图可能对应复杂的多表连接更新操作的实际效果难以预测。即使视图是可更新的也容易破坏数据完整性如更新了不该改的字段。视图的初衷是查询简化和安全而不是数据写入。最佳实践视图仅用于 SELECT 查询数据变更直接操作基表。十、数据库设计1. 表之间的关系设计在关系型数据库中表与表之间存在三种基本关系。正确的关系设计可以减少数据冗余保证数据一致性。一对多多对一最常见的关系。例如部门和员工。一个部门有多个员工一个员工只属于一个部门。实现方式在“多”的一方员工表添加外键指向“一”的一方部门表的主键。-- 部门表主表 CREATE TABLE dept ( id INT PRIMARY KEY, name VARCHAR(20) ); -- 员工表从表包含外键关联部门表 CREATE TABLE emp ( id INT PRIMARY KEY, name VARCHAR(20), dept_id INT, FOREIGN KEY (dept_id) REFERENCES dept(id) );多对多例如学生和课程。一个学生可以选修多门课程一门课程也可以被多名学生选修。实现方式需要借助第三张中间表也称为关联表。中间表至少包含两个外键分别指向两张表的主键。通常还将这两个外键组合作为联合主键。-- 学生信息表 CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR(20) ); -- 课程信息表 CREATE TABLE course ( id INT PRIMARY KEY, name VARCHAR(20) ); -- 学生选课关联表 CREATE TABLE student_course ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id), -- 设置联合主键 FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (course_id) REFERENCES course(id) );一对一例如人和身份证。一个人只有一个身份证一个身份证只能对应一个人。实现方式在任意一方添加外键并设置为唯一约束UNIQUE指向另一方的主键。也可以将两张表共用同一个主键。-- 方案一在身份证表中设置 person_id 作为唯一外键 CREATE TABLE person ( id INT PRIMARY KEY, name VARCHAR(20) ); CREATE TABLE id_card ( id INT PRIMARY KEY, card_no VARCHAR(18), person_id INT UNIQUE, -- 通过唯一约束确保一对一关系 FOREIGN KEY (person_id) REFERENCES person(id) ); -- 方案二采用主键共享方式身份证表主键同时作为外键 CREATE TABLE id_card ( id INT PRIMARY KEY, card_no VARCHAR(18), FOREIGN KEY (id) REFERENCES person(id) );2. 三大范式设计理论范式Normal Form是数据库设计时遵循的规范目的是减少数据冗余、避免更新异常。要满足后一级范式必须先满足前一级所有范式。第一范式1NF列原子性要求表中的每个字段都是不可分割的原子值即不能有“复合字段”或“多值字段”。反例地址字段包含省份、城市、街道如果经常按城市查询应拆分。id name address1 张三 广东省深圳市南山区科技园正例拆分为省份、城市、详细地址。id name province city detail第二范式2NF消除部分依赖在 1NF 的基础上要求表中的每一列都与主键完全相关针对联合主键。即不能出现只依赖联合主键中某一列的情况。反例订单详情表使用 (订单编号, 商品编号) 作为联合主键但“商品名称”和“商品单价”只依赖商品编号不依赖订单编号违反 2NF。订单编号 商品编号 商品名称 单价 数量正例拆分为三张表· 订单表订单编号· 商品表商品编号商品名称单价· 订单明细表订单编号商品编号数量第三范式3NF消除传递依赖在 2NF 的基础上要求表中的每一列都与主键直接相关而不是间接相关。即不能有非主键列依赖另一个非主键列。反例员工表中部门名称依赖于部门编号而部门编号依赖于员工ID传递依赖。员工ID 员工姓名 部门编号 部门名称正例拆分为两张表· 员工表员工ID员工姓名部门编号· 部门表部门编号部门名称范式的理解与实际应用建议严格遵循 3NF 的设计通常是最“规范”的但实际开发中不必死守范式。因为过度规范化会导致查询时需要大量 JOIN影响性能。常见的做法是· 以 3NF 为基准设计确保没有明显的冗余和更新异常。· 适当反规范化为了查询性能可以故意增加冗余字段如订单表中冗余客户姓名以空间换时间。· 根据业务场景权衡OLTP在线事务处理系统适当遵守范式OLAP在线分析处理和数据仓库系统常常反规范化以减少 JOIN。十一、DCL用户与权限管理DCLData Control Language数据控制语言用于管理数据库用户和控制用户对数据库对象的访问权限。通过DCL数据库管理员可以创建用户、分配权限、回收权限从而保障数据安全。1. 用户管理MySQL 将用户信息存储在系统数据库 mysql 的 user 表中。每个用户由 用户名 和 主机名 联合标识例如 zhangsanlocalhost。基本语法CREATE USER 用户名主机名 IDENTIFIED BY 密码;主机名设置用户允许连接的来源主机localhost仅限本地连接 · %允许所有主机连接通配符 ·192.168.1.%限定特定IP网段连接-- 创建本地用户zhangsan并设置密码为123 CREATE USER zhangsanlocalhost IDENTIFIED BY 123; -- 创建允许从任意主机登录的用户lisi并设置密码为123 CREATE USER lisi% IDENTIFIED BY 123;如果MySQL启用了密码强度验证插件如 validate_password可能需要先降低验证策略否则密码需要满足复杂度要求-- 禁用密码复杂度验证 SET GLOBAL validate_password_policy 0; SET GLOBAL validate_password_length 1;删除用户-- 删除指定用户 DROP USER 用户名主机名; -- 示例删除允许任意主机访问的base用户 DROP USER base%;删除用户不会影响该用户之前创建的表或数据只是无法再登录。修改用户密码MySQL 5.7 及更早版本支持使用 PASSWORD() 函数修改密码SET PASSWORD FOR 用户名主机名 PASSWORD(新密码); -- 使用示例 SET PASSWORD FOR lisi% PASSWORD(234567);MySQL 8.0 版本建议采用 ALTER USER 语句ALTER USER lisi% IDENTIFIED BY newpassword;查询用户用户信息存储在 mysql.user 表中-- 查询MySQL用户列表 USE mysql; SELECT user, host FROM user;示例输出---------------------- | user | host | ---------------------- | root | localhost | | zhangsan | localhost | | lisi | % | ----------------------主机名中的 % 表示“任意主机”。例如· zhangsan%可从任何IP登录。· zhangsan192.168.1.%可从 192.168.1.x 网段登录。· 若同时存在 zhangsanlocalhost 和 zhangsan%MySQL 会选择匹配更精确的主机。2. 权限管理用户创建后默认没有任何权限需要管理员授予权限才能操作数据库对象。查询用户权限-- 查看指定用户的权限 SHOW GRANTS FOR usernamehostname; -- 实际应用示例 SHOW GRANTS FOR lisi%;执行结果示例------------------------------------------------- | Grants for lisi% | ------------------------------------------------- | GRANT USAGE ON *.* TO lisi% | -------------------------------------------------USAGE 表示没有任何实际操作权限。授予权限语法GRANT 权限列表 ON 数据库名.表名 TO 用户名主机名 [WITH GRANT OPTION];权限列表指定授予的具体权限如 SELECT、INSERT、UPDATE、DELETE 或 ALL PRIVILEGES全部权限多个权限之间用逗号分隔数据库名.表名支持指定特定数据库或表使用.表示授予所有数据库和表的权限WITH GRANTOPTION可选参数授予用户将自己拥有的权限再分配给其他用户的权限示例-- 授予用户 lisi 对 db3 数据库中 account 表的查询、删除和更新权限 GRANT SELECT, DELETE, UPDATE ON db3.account TO lisi%; -- 授予用户 zhangsan 对所有数据库的完全访问权限超级管理员权限 GRANT ALL PRIVILEGES ON *.* TO zhangsanlocalhost; -- 授予用户 lisi 对 school 数据库的查询权限并允许其进一步授权 GRANT SELECT ON school.* TO lisi% WITH GRANT OPTION;撤销权限REVOKE 权限列表 ON 数据库名.表名 FROM 用户名主机名;使用示例-- 撤销用户 lisi 对 db3.account 表的 UPDATE 权限 REVOKE UPDATE ON db3.account FROM lisi%; -- 撤销用户 lisi 在所有数据库和表上的全部权限 REVOKE ALL ON *.* FROM lisi%;撤销权限后用户将不能再执行相应操作但用户本身仍然存在可再次授权。权限更改后可能需要执行 FLUSH PRIVILEGES; 刷新权限表但在使用 GRANT 和 REVOKE 语句时MySQL 会自动刷新。
