MySQL与PostgreSQL 的一些SQL
MySQL
1、MYSQL输出重定向
将SQL内容输出到文件
nohup mysql -h127.0.0.1 -uroot -ppassword -Ne "sql语句;" > /home/mysql/data/xxxxx.txt &
2、时间格式转换
时间转换，转10位时间戳
select UNIX_TIMESTAMP('2021-02-27 00:00:00')SELECT FROM_UNIXTIME(1614408000)
3、查看没有主键的表
查看哪些表没有主键，mysql的主键很重要，需要指定好主键
select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES a where not EXISTS (select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLE_CONSTRAINTS b where a.TABLE_NAME=b.TABLE_NAME and b.CONSTRAINT_NAME='PRIMARY') and a.TABLE_SCHEMA not in ('information_schema','performance_schema','mysql','sys');
4、mysql的递归查询
先了解一个函数：find_in_set(string1,string2) 用来查询目标字符在后面字符中的位置，如果不存在，就返回0 mysql> select find_in_set('1','2,3');
+------------------------+
| find_in_set('1','2,3') |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)mysql> select find_in_set('1','1,2,3');
+--------------------------+
| find_in_set('1','1,2,3') |
+--------------------------+
| 1 |
+--------------------------+select id from (select * from test01 where pid is not null and is_del != 1) a, (select @pid:='06') pd where find_in_set(pid,@pid)>0 and @pid:= CONCAT(@pid,',',id)其中@pid 是一个变量字段。 其中id字段是 当前id， pid为对于id的父id
Greenplum/PostgreSQL
1、查看表空间大小
普通表：
select pg_size_pretty(pg_total_relation_size('public.tablename'));分区表：
SELECT tablename,pg_size_pretty(sum(pg_total_relation_size(partitiontablename))::bigint) total_size,pg_size_pretty(sum(pg_relation_size(partitiontablename))::bigint) table_size from pg_partitions where schemaname = 'public' and tablename='表名' group by tablename;
2、查看Greenplum表的分布键
SELECTaaa.nspname AS "模式名",aaa.relname AS "表名",aaa.table_comment AS "中文表明",ccc.attname AS "分布键"
FROM(SELECTaa.oid,obj_description (aa.oid) AS table_comment,aa.relname,bb.localoid,bb.attrnums,regexp_split_to_table(array_to_string(bb.attrnums, ','),',') att,dd.nspnameFROMpg_class aa --原数据信息 最重要的表！LEFT JOIN gp_distribution_policy bb ON bb.localoid = aa.oid --分布键表 LEFT JOIN pg_namespace dd ON dd.oid = aa.relnamespace --模式 LEFT JOIN pg_inherits hh ON aa.oid = hh.inhrelid --继承表 WHEREhh.inhrelid IS NULL and lower(aa.relname) = lower('base_addpart_config')) aaa
LEFT JOIN pg_attribute ccc ON ccc.attrelid::text = aaa.oid::text
AND ccc.attnum::text = aaa.att::text
WHEREccc.attnum > 0 ;
华为的Libra/GaussDB 直接提供了一个函数
postgres=> select getdistributekey('test01');getdistributekey
------------------id
(1 row)
3、开窗函数分组排序
根据一个或多个字段分组，再根据一个或多个字段排序
select T.id,T.name from (select select ROW_NUMBER( ) OVER (PARTITION BY id ORDER BY age DESC) rowNum,id,name from test01) T where T.rowNum = 1;
4、类似oracle的declare
多个SQL捆绑执行，当多个SQL无法分事务执行时，可以使用declare 将其绑定一起， 其中也可以写分支或者循环
如下：给test01 加字段， 先判断这个表的该字段是否存在，如果没有再添加
do $$
declarev_qty int;
beginSELECT count(*) into v_qty FROM information_schema.COLUMNS WHERE TABLE_NAME = 'test01' AND COLUMN_NAME = 'age2';if (v_qty = 0) thenalter table test01 add COLUMN age2 text;end if;
end;
$$ LANGUAGE plpgsql;
5、删除Greenplum表中重复信息
gp的每个节点会有一个gp_segment_id 是唯一的，每个节点的每一行会有一个ctid，是节点层面唯一的， 所以可以根据 这2个字段，确认一个集群中唯一的行，即使它们的数据是完全重复的
下面的PARTITION BY id，就是选择重复信息的粒度， 写id就是id重复就删，如果完全一样再删，就得将字段都写上
delete from test01 where (gp_segment_id, ctid) not in (select T.gp_segment_id,T.ctid from (select ROW_NUMBER () OVER (PARTITION BY id)AS rowId,gp_segment_id,ctid from test01) T where T.rowId =1);
6、多行转换为1行
利用数组函数array_agg 进行组合
SELECT array_to_string(array_agg(table_name),',') from base_addpart_config;
7、字符串去重
postgres=# select regexp_replace('abcabcabc','(.)(\1)+','\1','g');regexp_replace
----------------abc
(1 row)postgres=# select regexp_replace('北京北京北京上海','(.)(\1)+','\1','g');regexp_replace
----------------北京上海
(1 row)
8、去除字符串中某个中间的值
postgres=# SELECT case when substr(name,0,3) = '桂B' then substr(name,0,3)||substr(name,4,length(name)) else name end from test01;name
-------------------桂BT795600:30:44:1d:10:b6桂B583D1桂BG5393
(4 rows)