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)