【MySQL】# 用户权限操作、查询的同时更新一张表、greatest()和least()函数、日期时间操作函数、解决主键自动增长2、Navicat查看数据库密码

张锐藻

2023-12-01

1. 用户权限操作

1.1 创建用户

只允许从本机访问

CREATE USER 'demo'@'localhost' IDENTIFIED BY '12345'

允许从任意主机访问（也可指定IP）
```
CREATE USER 'demo'@'%' IDENTIFIED BY '12345'
```

创建完成后，刷新授权信息：flush privileges;

修改密码

ALTER USER 'demo'@'%' IDENTIFIED BY '新密码'

删除用户
```
DROP USER 'demo'@'%';
```

1.2 权限操作

授予权限（给 demo用户授予 test库t1表的只读权限）

GRANT SELECT ON `test`.`t1` TO 'demo'@'%' WITH GRANT OPTION;
-- WITH GRANT OPTION  表示该用户可给其它用户赋予权限，但不能超过该用户已有的权限

撤销权限

REVOKE ALL PRIVILEGES ON `test`.`t1` FROM 'demo'@'%';
-- 删除 demo 用户的所有权限

操作完成后，刷新授权信息：flush privileges;

查看用户授权信息
```
SHOW GRANTS FOR 'demo'@'%';
```

1.3 修改用户名后，无法使用视图

默认情况下，视图的安全验证方式为 definer’，因此，可以修改视图的安全验证方式解决问题

definer：调用视图(或存储过程)的用户必须有此视图(存储过程)的EXECUTE权限，并且 definer指定的用户必须存在mysql.user表中
invoker：这种方式不会检查视图或者存储过程指定的用户，只要调用者有权限即可调用

视图的创建语法：

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

2. 查询的同时更新表

例如有 student 表，现在要执行的操作为：把 id=1的 age值，更新为id=2的 age值

通常情况下，我们可能会想到用以下语句来实现这个需求：

UPDATE student 
SET age = ( SELECT age FROM student WHERE id = 1 ) 
WHERE
	id = 2

但是却报错，报错信息为：You can't specify target table 'student' for update in FROM clause（不能在同一语句中update, select同一张表）

解决思路：既然不能是同一张表，那就将select 的结果再嵌套一层，变为一个临时的中间表

UPDATE student 
SET age = (
	SELECT
		tmp.age 
	FROM
	( SELECT age FROM student WHERE id = 1 ) tmp) 
WHERE
	id = 2

3. greatest()和least()函数

greatest()，least()函数都使用 N个参数，并分别返回最大值和最小值，参数可能具有混合数据类型。

如果参数中有 NULL，那么两个函数都立即返回 NULL，而不进行任何比较
如果在 INT 或 REAL 上下文中使用函数，或者所有的参数都是整数值或 REAL 值，那么它们将分别作为 INT 和 REAL 来比较
如果参数由数字和字符串组成，则都视为数字进行比较
如果至少一个参数是非二进制(字符)字符串，则都视为非二进制字符串进行比较
在所有其他情况下，都视为二进制字符串进行比较

SELECT
	GREATEST( 10, 23, 15, 26, 76 ) maxNum,  -- 76
	least( 10, 23, 15, 26, 76 ) minNum      -- 10


SELECT
	GREATEST( 10, 23, NULL, 26, 76 ) maxNum,  -- NULL    可以使用 IFNULL函数，将null置为 0
	least( 10, NULL, 15, 26, 76 ) minNum      -- NULL

4. 日期时间操作函数

4.1 获取时间

# 获取当前日期时间
SELECT NOW();       -- 2022-10-28 18:34:23

# 获取当前日期
SELECT CURDATE();   -- 2022-10-28

# 获取当前时间
SELECT CURTIME();   -- 18:34:23

# 对于时间 2022-10-28 18:34:23，分别获取年、月、日等
SELECT EXTRACT(YEAR FROM NOW()); -- 2022

4.2 日期增加、减少

# 时间减少 1小时
SELECT DATE_SUB( NOW(), INTERVAL 1 HOUR ) 

# 时间增加 1小时
SELECT DATE_ADD( NOW(), INTERVAL 1 HOUR )

-- 其他间隔为类似的操作  INTERVAL

4.3 日期格式化、字符串转日期

# 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')

# 字符串转日期
SELECT STR_TO_DATE( '2022-10-28 10:37:14', '%Y-%m-%d %H:%i:%s' );

5. 解决主键自动增长2

查看配置：SHOW VARIABLES LIKE '%increment%'

如果 auto_increment_increment = 2，执行 SET @@GLOBAL.auto_increment_increment = 1; SET @@auto_increment_increment = 1;

如果 auto_increment_offset = 2，执行 SET @@GLOBAL.auto_increment_offset = 1; SET @@auto_increment_offset = 1;

6. Navicat查看数据库密码

使用 Navicat导出连接（文件 —> 导出连接），要勾选 导出密码
在导出的 ncx文件中，找到 password

在Java 的maven项目中，引入 hutool

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.3.8</version>
</dependency>

import cn.hutool.core.util.HexUtil;
import cn.hutool.crypto.Mode;
import cn.hutool.crypto.Padding;
import cn.hutool.crypto.symmetric.AES;
import org.junit.jupiter.api.Test;

public class DecodeTest {
    @Test
    public void decode() {
        String aesKey = "libcckeylibcckey";
        String aesIv = "libcciv libcciv ";
        String str = "503AA930968F877F04770B47DD731DC0";  // 待解密的密码
        str = str.toLowerCase();
        byte[] tmp = HexUtil.decodeHex(str);
        // 11（低版本的navicat导出的）用 Mode.ECB
        // 12（较高版本导出的）用 Mode.CBC
        AES aes = new AES(Mode.CBC, Padding.PKCS5Padding, aesKey.getBytes(), aesIv.getBytes());
        String rs = aes.decryptStr(tmp);
        System.out.println(rs);
    }
}