改密码

在MySQL中，密码存储在数据库的 mysql.user 表中，但密码通常以散列形式存储，不以明文形式存储。因此，你不能直接查询用户的密码，但你可以通过重置密码的方式来更改密码。

要重置用户 user01 的密码，可以按照以下步骤操作：

1. 使用 root 账号登录MySQL服务器。

2. 连接到 mysql 数据库：

   USE mysql;
   

3. 使用 UPDATE 语句来更改用户的密码。以下是一个示例（版本5.6）以前：

   UPDATE user SET authentication_string = PASSWORD('new_password') WHERE User = 'user01';
   

   在这个示例中，将 new_password 替换为你想要设置的新密码。

   如果你的MySQL版本较新（版本5.7及以后），可能需要使用 ALTER USER 命令来更改密码。以下是示例：

   ALTER USER 'user01'@'localhost' IDENTIFIED BY 'new_password';
   

   这将设置 user01 的新密码为 new_password 。

4. 刷新权限以确保更改生效：

   FLUSH PRIVILEGES;
   

现在，user01 的密码已经被重置为新密码。请确保将 new_password 替换为你要设置的实际密码。这个过程不会向你显示用户的当前密码，因为密码通常以散列形式存储，不能以明文形式检索。

只读账户

MySQL 5.7及以后

在MySQL 8中创建一个只读账号，你需要执行以下步骤：

1. 登录MySQL服务器：

   使用一个具有足够权限的MySQL账号登录到MySQL服务器。通常，这个账号是管理员账号，具有 CREATE USER 和 GRANT 等权限。

2. 创建只读账号：

   使用以下命令创建一个只读账号。在这个示例中，我们将创建一个名为 readonly_user 的只读账号，并将其限制为只能访问特定的数据库：

   CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'your_password';
   

   这将创建一个名为 readonly_user 的账号，密码是 your_password 。 '%' 表示该账号可以从任何主机连接到MySQL服务器。如果你想限制只能从特定主机连接，可以将 '%' 替换为特定主机的IP地址或主机名。

3. 授予只读权限：

   • 单个库：将 SELECT 权限赋予该账号以使其只能读取数据，而不能修改或删除数据。在这个示例中，我们将授予 SELECT 权限给 readonly_user 账号，让它可以访问 database_name 数据库：

     GRANT SELECT ON database_name.* TO 'readonly_user'@'%';
     

     如果需要赋予多个权限，需要执行多条 GRANT 语句

   • 所有数据库：

     GRANT SELECT ON *.* TO 'readonly_user'@'%';
     

4. 刷新权限：

   最后，执行以下命令以刷新MySQL的权限缓存，以确保新的权限立即生效：

   FLUSH PRIVILEGES;
   

MySQL 5.7之前

在MySQL 5.6及更早的版本中，创建只读账户需要使用不同的命令。在这些旧版本中，密码通常以 明文形式 存储在 mysql.user 表的 Password 列中，因此你可以使用以下方式创建只读账户：

1. 使用具有足够权限的MySQL账号（例如，root ）登录到MySQL服务器。

2. 创建只读账户并设置密码：

   GRANT USAGE ON *.* TO 'readonly_user'@'localhost' IDENTIFIED BY 'password';
   

   这将创建一个名为 readonly_user 的账户，并设置密码为 password 。 'localhost' 意味着这个账户只能从本地主机连接到MySQL服务器。如果你想允许从任何主机连接，可以将 'localhost' 替换为 '%'。

3. 授予只读权限：

   接下来，你需要授予只读权限给 readonly_user 账户。以下是一个示例，将授予 SELECT 权限给 readonly_user ，让它可以访问指定数据库（例如，mydatabae_name）中的数据：

   GRANT SELECT ON mydatabae_name.* TO 'readonly_user'@'localhost';
   

   如果你希望该账号只读其他数据库，可以替换 mydatabae_name 为目标数据库的名称。

4. 刷新权限：

   最后，执行以下命令以刷新MySQL的权限缓存，以确保新的权限立即生效：

   FLUSH PRIVILEGES;
   

授予其他权限

除了 SELECT 权限，你还可以使用 GRANT 命令授予MySQL账号其他权限。以下是一些常见的权限以及相应的 GRANT 命令示例：

1. INSERT权限：允许在表中插入新数据。

   GRANT INSERT ON database_name.table_name TO 'username'@'host';
   

2. UPDATE权限：允许更新表中的数据。

   GRANT UPDATE ON database_name.table_name TO 'username'@'host';
   

3. DELETE权限：允许删除表中的数据。

   GRANT DELETE ON database_name.table_name TO 'username'@'host';
   

4. CREATE权限：允许创建新数据库或表。

   GRANT CREATE ON database_name.* TO 'username'@'host';
   

5. ALTER权限：允许修改表结构。

   GRANT ALTER ON database_name.* TO 'username'@'host';
   

6. DROP权限：允许删除数据库或表。

   GRANT DROP ON database_name.* TO 'username'@'host';
   

7. ALL权限：允许执行所有权限（除了GRANT权限以外）。

   GRANT ALL ON database_name.* TO 'username'@'host';
   

-- 同时授予多个权限
-- 为数据库1授予SELECT权限
GRANT SELECT ON database1.* TO 'username'@'localhost';

-- 为数据库2授予INSERT和UPDATE权限
GRANT INSERT, UPDATE ON database2.* TO 'username'@'localhost';

事务与问题详解

MySQL 提供了多种事务隔离级别，每种隔离级别都有不同的特性和影响。事务隔离级别用于控制事务之间的可见性和并发性，不同的隔离级别可能会导致一些并发问题。以下是四种常见的事务隔离级别及其详细解释以及可能导致的问题：

1. 读未提交（Read Uncommitted）：

   • 允许事务读取未提交的修改，可能会看到其他事务尚未提交的数据变化。
   • 可能导致脏读、不可重复读和幻读问题。

2. 读已提交（Read Committed）：

   • 一个事务只能读取已经提交的数据，避免了脏读问题。
   • 但是可能会导致不可重复读和幻读问题。

3. 可重复读（Repeatable Read）：

   • 事务期间，对同一行的读操作会返回相同的结果，避免了不可重复读问题。
   • 但是可能会导致幻读问题，即在一个事务中两次相同的查询得到的结果行数不一致。

4. 串行化（Serializable）：

   • 最高隔离级别，确保了事务的完全隔离，防止脏读、不可重复读和幻读问题。
   • 但是会导致并发性能降低，因为事务需要串行执行。

在选择事务隔离级别时，需要权衡数据的一致性和并发性能。不同的业务场景可能需要不同的隔离级别。通常情况下，读已提交或可重复读是常见的选择，但在一些特殊情况下，可能需要使用更高的隔离级别（如串行化）来保证数据的完全隔离性。

A方法内调用B和C，C不能读到B已经删除的数据

问：现在有一个需求，mysql是默认事务隔离级别，首先有一个删除A方法，A方法需要调用删除B方法紧接着调用查询C方法，C方法一定不能查询到B方法刚才已经删除的数据，@Transactional注解应该怎么加

答：为了满足你的需求，你可以在不同的方法上使用不同的事务传播行为（Propagation）和隔离级别（Isolation）。在 Spring 的 @Transactional 注解中，可以通过 propagation 和 isolation 属性来设置事务的传播行为和隔离级别。

在你的情况下，可以这样处理：

1. 删除A方法：

@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
public void deleteA() {
    // 执行删除操作
    // 调用删除B方法
    deleteB();
    // 调用查询C方法
    queryC();
}

2. 删除B方法：

@Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.READ_COMMITTED)
public void deleteB() {
    // 执行删除操作
}

3. 查询C方法：

@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED)
public void queryC() {
    // 执行查询操作
}

在上面的示例中，我们将删除A方法和查询C方法都设置为 Propagation.REQUIRED 传播行为，并且隔离级别都为 Isolation.READ_COMMITTED，这意味着它们会在同一个事务中执行，而且查询操作只能看到已经提交的数据，不会看到B方法中已经删除的数据。

而删除B方法我们设置为 Propagation.REQUIRES_NEW 传播行为，这将会在一个新的事务中执行，它的隔离级别也为 Isolation.READ_COMMITTED，这样可以确保在删除B方法中的事务完成后，查询C方法不会看到B方法删除的数据。

请根据实际情况调整事务的传播行为和隔离级别，以满足你的需求。

行转列（多行转成列的数据）

定义：将同样key值的多行value数据，转换为使用一key值的多列数据，使每一行数据中，每一个key对应多个value；行转列完成后，视觉上的效果是，表的总行数变少了，但是列数增加了。

示例：同一个学生id每门学科成绩各占一行，转成一行内显示多列学科成绩。

MySQL中的行转列和列转行

列转行（多列转成行的数据）

定义：将表中同一key值对应的多个value列，转换为多行数据，使每一行数据中，保证一个key只对应一个value；列转行完成后，视觉上的效果是，表的总列数变少了，但是行数增加了。

示例：一个学生id在一行内显示多列学科成绩，转成每门学科成绩各占一行。

MySQL四大特性

MySQL是一种关系型数据库管理系统（RDBMS），具有许多特性，其中四个主要特性是：

1. ACID 属性：

   • 原子性（Atomicity）： 事务是一个不可分割的工作单元，要么全部执行成功，要么全部失败。如果其中任何一部分失败，整个事务都会被回滚到起始点，保持数据的一致性。

   • 一致性（Consistency）： 事务执行的结果必须使数据库从一个一致性状态变到另一个一致性状态。事务执行的中间状态不能被其他事务访问。是编程过程中，想要达成的一种目的。保证事务只能从一个正确的状态转移到另一个正确的状态。

     提示

     数据库的“一致性”从底层来说，是一组约束。这组约束可以是约束条件、可以是触发器等，也可以是它们的组合。从更高的层面来说，“一致性”是一种目的，即保持数据库与真实世界之间的正确映射。此时，需要靠各种锁来达成“一致性”。

   • 隔离性（Isolation）： 多个事务同时执行时，每个事务都不会受到其他事务的干扰。隔离性保证每个事务能够在相对于其他事务的隔离环境中执行，从而防止并发事务之间的数据冲突。

   • 持久性（Durability）： 一旦事务被提交，它对数据库的修改就是永久性的，即使系统发生故障也能够保持。

2. 事务支持：

   • MySQL支持事务的概念，允许一组相关的操作作为单个操作单元执行。这保证了数据的完整性和一致性。

3. 关系型数据库管理系统（RDBMS）：

   • MySQL是一个关系型数据库管理系统，采用了表格的形式来存储和管理数据。这意味着它支持事务、数据的完整性和关系型模型的查询语言SQL（Structured Query Language）。

4. 多用户和并发控制：

   • MySQL是一个多用户的数据库系统，多个用户可以同时访问数据库。为了保证数据的一致性，MySQL采用了并发控制机制，通过锁定机制和事务隔离级别来处理多个用户同时访问相同数据的情况，防止数据不一致和冲突。

这些特性使MySQL成为一个强大的数据库管理系统，适用于各种规模的应用程序，从小型网站到大型企业级系统。

事务隔离级别

需要注意的是，随着隔离级别的增加，事务的并发性下降，但数据的一致性得到了保障。选择合适的隔离级别需要根据具体的业务需求和对并发性和一致性的权衡来决定。

引擎

MySQL 主要使用2种执行引擎：

• InnoDB引擎
• MyISAM引擎

MyISAM 不支持事务，MyISAM 中的锁是表级锁；而 InnoDB 支持事务，并且支持行级锁。

——————

增删改查

INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
(value1, value2, value3, ...)，
-- 可以多条
(value1, value2, value3, ...);

-- 直接删除表，不检查是否存在
DROP TABLE table_name ;
或
DROP TABLE [IF EXISTS] table_name;

-- 仅删除数据
DELETE FROM table_name
WHERE condition;

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];

dump备份

https://www.cnblogs.com/letcafe/p/mysqlautodump.html

#MySQLdump常用
mysqldump -u root -p --databases 数据库1 数据库2 > xxx.sql

——————

查询执行顺序

from > where > group（含聚合）> having > order > select

Functions and Operators 函数和运算符

mysql中的内置函数

IFNULL

SELECT `id`, IFNULL(`name`, '无名称') FROM `student`;

CONCAT

CONCAT(IFNULL(C1,''),IFNULL(C2,''))

distinct

去重结果集

SELECT DISTINCT `name`,`age` FROM `student`;

>、<、=

BETWEEN

[NOT] BETWEEN 取值1 AND 取值2

其中：

• NOT：可选参数，表示指定范围之外的值。如果字段值不满足指定范围内的值，则这些记录被返回。
• 取值1：表示范围的起始值（包含）。
• 取值2：表示范围的终止值（包含），必须不小于取值1。
• 闭区间

日期：

between '2020-1-12' and '2020-06-12';
-- 实际执行的是 2020-01-12 00:00:00 AND 2020-06-11 23:59:59
2017-07-25 24:00:00  晚上24点即为下一天00点  2017-07-26 00:00:00，数据库识别不出24点的信息；换成下一天00点即可以查询出正确结果。

• 日期

select count(1) from user where regist_date between '2017-07-25 00:00:00' and  '2017-07-25 24:00:00';
-- 这条sql语句查询出结果为0。实际上数据库有一条符合该查询条件的数据。
-- 错误原因：2017-07-25 24:00:00  晚上24点即为下一天00点  2017-07-26 00:00:00，数据库识别不出24点的信息；换成下一天00点即可以查询出正确结果。

JOIN

SELECT * FROM A AS a 
JOIN B AS b ON a.id = b.id;

连表操作时：先根据查询条件和查询字段确定驱动表，确定驱动表之后就可以开始连表操作了，然后再在缓存结果中根据查询条件找符合条件的数据

INNER JOIN

• 仅包含A,B两表都匹配且都不为空的行

• INNER JOIN 和 , (逗号) 在语义上是等同的

• JOIN是INNER JOIN的简写

LEFT [OUTER] JOIN

• 包含A中所有，B中不存在的填充NULL

RIGHT [OUTER] JOIN

• 包含B中所有，A中不存在的填充NULL

USING()

on a.c1 = b.c1 等同于 using(c1)

LIKE

1. 百分号通配符 %：

% 通配符表示零个或多个字符。例如，'a%' 匹配以字母 'a' 开头的任何字符串。

SELECT * FROM customers WHERE last_name LIKE 'S%';

以上 SQL 语句将选择所有姓氏以 'S' 开头的客户。

2. 下划线通配符 _：

_ 通配符表示一个字符。例如，'_r%' 匹配第二个字母为 'r' 的任何字符串。

SELECT * FROM products WHERE product_name LIKE '_a%';

以上 SQL 语句将选择产品名称的第二个字符为 'a' 的所有产品。

3. 组合使用 % 和 _：

SELECT * FROM users WHERE username LIKE 'a%o_';

以上 SQL 语句将匹配以字母 'a' 开头，然后是零个或多个字符，接着是 'o'，最后是一个任意字符的字符串，如 'aaron'、'apol'。

4. 不区分大小写的匹配：

SELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;

以上 SQL 语句将选择姓氏以 'smi' 开头的所有员工，不区分大小写。

LIKE 子句提供了强大的模糊搜索能力，可以根据不同的模式和需求进行定制。在使用时，请确保理解通配符的含义，并根据实际情况进行匹配。

聚合函数

MAX

SUM

COUNT

• COUNT(*)中包含NULL的数据

• COUNT(常量) 和 COUNT(*) 表示的是直接查询符合条件的数据库表的行数。

• 而COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数。

• **性能：**count(*) > count(1) > count(主键字段) > count(字段)

sum,min,max,avg,count

ORDER BY

MySQL排序时如果用的的字段为字符串型的，排序规则是这样的：如1，10，2，20，3，4，5，这种排序是按照字符从第一个字符开始比较出来的，但不是我想要的，我想要的是：1，2，3，4，5……，10，20这种。

把相应的字段转换成整型，使用CAST函数，如下：

SELECT * FROM `b_datatype` ORDER BY CAST(`c_no` AS UNSIGNED) ASC;

GROUP BY

GROUP_CONCAT

每个人的每门成绩为一条记录，根据姓名分组查询之后，会报错only_full_group_by错误，使用group_concat将分组之后的同一个字段的多条结果合成一条

SELECT 
	`name`,
	group_concat(`class`,':',`score` SEPARATOR ',') as 'class:score'
FROM `t_score_line2column` GROUP BY `name`;

HAVING

HAVING执行顺序在GROUP_BY之后，GROUP_BY和WHERE后不能使用聚合函数，如果需要对查询之后的结果集使用聚合函数，应使用HAVING；例如：

对分组数据再次判断时要用having

select reports，count(*) from employees group by reports having count(*) > 4;
-- 在分组数据上查询`count() > 4`的数据

LIMIT

LIMIT num[, offset]

• 不写 offset ：查询前num条数据

• 写 offset ：从indexnum(包括)开始，查询offset条数据

另一种用法

SELECT * FROM t_student LIMIT 1, 3
# 另一种写法，但意思一样，都代表取第2、3、4条数据
SELECT * FROM t_student LIMIT 3 OFFSET 1

• LIMIT: 查 3 条
• OFFSET: 从第 1 条开始

IN

原理：先执行子查询，然后将子查询的结果集与主查询的结果集做一个笛卡尔乘积，然后通过子查询的结果集去和主查询的c_no匹配

[NOT] IN

select * from `b_datatype` where `c_no` IN ('2','4','6');

• IN 相当于 =any()

select * from `b_datatype` where `c_no` =any (select '2' UNION ALL select '4' UNION ALL select '6');

• NOT IN 相当于 <>all()

EXISTS

原理：先执行主查询，然后根据主查询结果集的以此用每条记录做子查询，如果子查询返回TRUE，则显示该条记录

SQL语句中exists和in的区别 - 白白的白浅 - 博客园 (cnblogs.com)

[NOT] EXISTS

SELECT * FROM `student` AS s EXISTS(
	SELECT id FROM `best_class` AS c WHERE s.id = c.sid
)

CAST

语法

CAST(expression AS datatype)

• expression 是需要转换的表达式或值。
• datatype 是目标数据类型，可以是 MySQL 支持的任何数据类型，如 CHAR, VARCHAR, INT, FLOAT, DATE, TIME 等。

如果有列使用的varchar类型存储的数字，在使用ORDER BY排序时候是：1、10、2、20而不是想要的1、2、3，就要将列使用CAST进行转换。例如：

SELECT * FROM `b_datatype` ORDER BY CAST(`c_no` AS UNSIGNED) ASC;

UNION

• 可以将多个结果集拼接在一起。例如：

SELECT 2 AS num
UNION ALL
SELECT 2 AS num
UNION ALL
SELECT 3 AS num;

• 如果不想保留相同的值，就用UNION而不是UNION ALL

运算符优先级

从最高优先级到最低优先级。一起显示在一行上的运算符具有相同的优先级。

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN, MEMBER OF
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

比较运算符

日期和时间

数学函数

数字函数和运算符

数据库操作语句类型（DQL、DML、DDL、DCL）简介

https://www.cnblogs.com/study-s/p/5287529.html

SQL语言共分为四大类：数据查询语言DQL，数据操纵语言DML，数据定义语言DDL，数据控制语言DCL。

1. 数据查询语言（DQL）

   基本结构是由SELECT子句，FROM子句，WHERE
   子句组成的查询块：

   SELECT <字段名表>
   FROM <表或视图名>
   WHERE <查询条件>
   

2. 数据操纵语言（DML）
   主要有三种形式：

   1. 插入：INSERT
   2. 更新：UPDATE
   3. 删除：DELETE

3. 数据定义语言（DDL）

   用来创建数据库中的各种对象：表、视图、索引、同义词、聚簇等：

   表	视图	索引	同义词	聚簇
   CREATE TABLE	CREATE VIEW	CREATE INDEX	CREATE SYN	CREATE CLUSTER

   重要

   DDL操作是隐性提交的，不能Rollback！

4. 数据控制语言（DCL）

   用来授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。如：

   1. GRANT：授权。

   2. ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
      回滚命令使数据库状态回到上次最后提交的状态。其格式为：SQL>ROLLBACK;

   3. COMMIT [WORK]：提交。

      在数据库的插入、删除和修改操作时，只有当事务在提交到数据
      库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
      到所做的事情，别人只有在最后提交完成后才可以看到。
      提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
      别说明这三种类型。

      1. 显式提交
         用 COMMIT 命令直接完成的提交为显式提交。其格式为：SQL>COMMIT；
      2. 隐式提交
         用SQL命令间接完成的提交为隐式提交。这些命令是：
         ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
         EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
      3. 自动提交
         若把 AUTOCOMMIT 设置为 ON ，则在插入、修改、删除语句执行后，系统将自动进行提交，这就是自动提交。其格式为：SQL>SET AUTOCOMMIT ON；

DB Link

需要操作其他数据库实例的部分表，但又不想系统连接多库。此时我们就需要用到数据表映射。如同Oracle中的DBlink一般。

1.开启FEDERATED引擎

若需要创建FEDERATED引擎表，则目标端实例要开启FEDERATED引擎。

• 在配置文件 [mysqld] 中加入一行：federated，然后重启数据库，FEDERATED引擎就开启了。

2. 创建FEDERATED表

使用CONNECTION创建FEDERATED表

-- 创建一个与源表结构相同的表（推荐与源端结构一致）
CREATE TABLE (......) 
ENGINE =FEDERATED CONNECTION='mysql://username:password@hostname:port/database/tablename';
-- 注意ENGINE=FEDERATED CONNECTION后为源端地址 避免使用带@的密码

3.使用CREATE SERVER

使用 CREATE SERVER 创建FEDERATED表

如果要在同一服务器上创建多个FEDERATED表，或者想简化创建FEDERATED表的过程，则可以使用该 CREATE SERVER 语句定义服务器连接参数，这样多个表可以使用同一个server。

CREATE SERVER创建的格式是：

CREATE SERVER link_name
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', PASSWORD '123456', HOST 'remote_host', PORT 3306, DATABASE 'db_name');

验证：

select * from mysql.servers

之后创建FEDERATED表可采用如下格式：

CREATE TABLE (......) 
ENGINE =FEDERATED CONNECTION='link_name/tablename'

使用总结

• 目标端建表结构可以与源端不一样 推荐与源端结构一致
• 源端DDL语句更改表结构 目标端不会变化
• 源端DML语句目标端查询会同步
• 源端drop表 目标端结构还在但无法查询
• 目标端不能执行DDL语句
• 目标端执行DML语句 源端数据也会变化
• 目标端truncate表 源端表数据也会被清空
• 目标端drop表对源端无影响

非官方使用规范

1. 源端专门创建只读权限的用户来供目标端使用。
2. 目标端建议用CREATE SERVER方式创建FEDERATED表。
3. FEDERATED表不宜太多，迁移时要特别注意。
4. 目标端应该只做查询使用，禁止在目标端更改FEDERATED表。
5. 建议目标端表名及结构和源端保持一致。
6. 源端表结构变更后 目标端要及时删除重建。