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表的分布键

SELECT
    aaa.nspname AS "模式名",
    aaa.relname AS "表名",
    aaa.table_comment AS "中文表明",
    ccc.attname AS "分布键"
FROM
    (
        SELECT
            aa.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.nspname
        FROM
            pg_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 --继承表  
        WHERE
        hh.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
WHERE
    ccc.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 $$ 
declare
  v_qty		int;
begin
	SELECT count(*) into v_qty FROM information_schema.COLUMNS WHERE TABLE_NAME = 'test01'  AND COLUMN_NAME = 'age2';
	if (v_qty = 0) then
		alter 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        
-------------------
 桂BT7956
 00:30:44:1d:10:b6
 桂B583D1
 桂BG5393
(4 rows)