非常用、但有用的SQL

阿章的学习日记

于 2024-07-22 16:56:33 发布

阅读量215

点赞数 9

文章标签： sql 数据库

本文链接：https://blog.csdn.net/weixin_41899860/article/details/140038337

版权

核心技术堆栈：SQL

一：MySQL
- 1、用户初始化相关
- 2、慢日志、事务、锁
二：postgre

一：MySQL

1、用户初始化相关

创建用户

CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123456';

查看用户和用户使用的地址
```
select user,host from mysql.user;
```

更新域属性，'%'表示允许任意IP地址访问

 update user set host='%' where user ='root';
 flush privileges;

授权

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

客户端与服务器之间的身份验证插件不匹配，则需要更改用户的身份验证插件，通常8.0版本以上出现问题
```
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';  
```

查看密码政策

SHOW variables LIKE 'validate_password%';

修改密码强度为低(其他密码项的设置同理)
```
set global validate_password.policy=LOW;
```

修改密码

ALTER USER 'lisi'@'localhost' IDENTIFIED BY '123456';

linux中配置mysql的策略

如果不需要密码策略，添加my.cnf文件中追加如下配置禁用即可：
- 配置默认编码为utf8
- 关闭客户端dns反解

echo -e "validate_password = off\ncharacter_set_server=utf8\ninit_connect='SET NAMES utf8'\nskip-name-resolve\n" >> /etc/my.cnf 
```bash

echo -e "character_set_server=utf8mb4\ninit_connect='SET NAMES utf8mb4'\nskip-name-resolve\n" >> /etc/my.cnf 
# 重启MySQL
systemctl restart mysqld

2、慢日志、事务、锁

慢sql记录

SELECT * FROM mysql.slow_log WHERE start_time > NOW() - INTERVAL 1 DAY;

最大连接数
```
SHOW VARIABLES LIKE 'max_connections';
```
事务、锁等情况
```
SHOW ENGINE INNODB STATUS;
```
查看正在执行的查询
```
SHOW FULL PROCESSLIST;
```

查看各个数据库（库）占用的空间

SELECT   
    table_schema AS `Database`,  
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Size in MB`  
FROM   
    information_schema.TABLES  
GROUP BY   
    table_schema;

二：postgre

1、查数据库大小

--查看单个数据库大小
SELECT pg_size_pretty(pg_database_size('dbname')) AS size;
-- 查表大小
select pg_relation_size('table_name');
--查询每张表的大小
SELECT relname,pg_size_pretty (pg_total_relation_size(relid)) AS tootal_size
FROM pg_catalog.pg_statio_user_tables ORDER BY relname;

SELECT relname,(pg_total_relation_size(relid)/(1024*1024))::NUMERIC AS size_mb 
FROM pg_catalog.pg_statio_user_tables ORDER BY size_mb desc

2、查数据库表锁状态

-- pg_locks 视图:
-- pg_locks 视图提供了关于当前锁的信息。你可以查询这个视图来查看是否有任何锁与你的表相关。
SELECT relation::regclass AS locked_table, mode, granted, pg_locks.transactionid, pg_stat_activity.query FROM pg_locks 
JOIN 
	pg_class ON relation = pg_class.oid   
LEFT JOIN 
	pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid   
WHERE NOT GRANTED 
	AND relation IS NOT NULL -- 只显示表锁，不包括其他类型的锁，如关系、事务等  
	AND relation::regclass::text = 'table_name'; -- 替换为你的表名
	
-- 使用 pg_stat_activity 和 pg_locks 一起:
--这两个视图可以结合起来，以提供更详细的关于哪些查询正在持有或等待锁的信息。
SELECT  sa.datname, sa.usename, sa.query, sa.pid, sa.wait_event_type, sa.wait_event, l.mode, l.relation::regclass AS locked_table  
FROM   
    pg_stat_activity sa  
JOIN   
    pg_locks l ON sa.pid = l.pid  
WHERE   
    NOT l.GRANTED  
    AND l.relation IS NOT NULL -- 只显示表锁  
    AND l.relation::regclass::text = 'table_name'; -- 替换为你的表名
    
-- pg_blocking_pids 函数（如果可用）:
-- 在某些 PostgreSQL 版本中，pg_blocking_pids 函数可以帮助你找到哪些进程正在阻塞其他进程。这个函数在 pg_catalog 模式下定义，并返回一个 setof integer，表示阻塞指定 PID 的进程 ID。--
SELECT  pg_blocking_pids(pid) AS blocking_pids, pg_stat_activity.query, pg_stat_activity.pid   
FROM   
    pg_stat_activity   
WHERE   
    pid IN (SELECT pid FROM pg_locks WHERE NOT GRANTED AND relation::regclass::text = 'table_name');

3、查数据

查询jsonb数据

查询字符串的长度

	-- 查询数据长度
		SELECT length(kind::text) FROM table_name limit 1
	-- 查询数据字符串长度>180的
		SELECT * FROM table_name WHERE length(kind::text)>180

当kind字段中数据类型是jsonb，数据是数组[{“value”:123},{“value”:456}]

		-- 查询数组长度大于2的数据：
			SELECT * FROM table_name WHERE jsonb_array_length(kind) > 2;
		-- 查询kind数组中,条件:第一个的元素的value字段的值为3的数据：
			SELECT * FROM table_name WHERE kind->0 ->>'VALUE'='3'
		-- 查询kind数组中,取值：第一个的元素的value字段的值：
			SELECT *,kind-> 0 -> 'VALUE' as value FROM table_name
	-- concat_admin int4[],数据：{110115}
	-- 查询concat_admin 字段中数据为空的数据
	SELECT * FROM table_name WHERE concat_admin::text='{}'