postgreSql 相关经验

·  阅读 233

author: 赵天天 2021-10-15

前言

PostgreSQL 是一个开源的,免费的,同时非常强大的关系型数据管理系统,还是一个面向对象数据库——支持嵌套,及一些其他功能。以下是一些使用postgreSQL的相关经验

查询语句

select * from tablea
复制代码

新增语句

insert into tablea ( key1, key2, key3 ) values ( value1, value2, value3 )
复制代码

删除语句

delete from tablea
复制代码

更新语句

update tablea set key1 = 'hello', key2 = 'world', ke3='nihao'
复制代码

上面的基础就不多解释了

join: 这里就是将表关联起了,横向关联

左关联:这个主要以左边的表为主表,右边的表为副表,不管右边的表有没有可以关联上的数据,左边的表的数据一定有。

select a.name, 
       a.id,
       b.name,
       b.id 
from tablea as a 
left join tableb as b
on a.id = b.id
复制代码

右关联:其实和左关联一个样, 这里就是将右表为主表,左表为副表,当然会了左关联就可以了。

select a.name, 
       a.id,
       b.name,
       b.id 
from tablea as a 
right join tableb as b
on a.id = b.id
复制代码

内关联:就是结果集取得两个表的关联的公共集

select a.name, 
       a.id,
       b.name,
       b.id 
from tablea as a 
inner join tableb as b
on a.id = b.id
复制代码

这里当然也有全关联,不过不建议用,因为他会做笛卡尔积的,会消耗数据库的性能的,

union 与union all:这里就是将两个表的数据连成一个表,我理解就是纵向连接,他们的连接字段名一定得一样,并且字段的类型也一样, 而且字段摆的顺序也得一样。

union 与 union all 的区别是,union 将表连接在一起后会去重的, union all 连接后不会去重的。

-- union all
select  name as key ,
           value as id
from tablea as a
union all 
select key,
       id
from tableb

-- union 
select  name as key ,
           value as id
from tablea as a
union
select key,
       id
from tableb
复制代码

子查询: 嵌套查询

-- 例1
select * from  tableb
where group_id in (select group_id from tablea where id > 100 )

-- 例2
select *,
   '我是子查询' as key222
from  tableb
where group_id > (select group_id from tablea where id > 100 )
复制代码

with 语句, 相当于做一个表,在后面中可以多次用到的,这样会书写方便一点

-- 这是with 简单的语法
with cte as (
  select name,
         age,
         sex
  from  tableb
  where group_id in (select group_id from tablea where id > 100 )
) 
select * from cte

-- 复杂一点的  这里只是为了体现一下那个语法,不要考虑其他逻辑问题哦
with cte as (
  select name,
         age,
         sex
  from  tableb
  where group_id in (select group_id from tablea where id > 100 )
) 
select from (
  select * from cte as a 
  left join tablen as n
  on a.name = n.name ) as st
inner join tablet as t
on st.name = t.name
where id in (select id from cte where sex> 10)
复制代码

returning : 当新增、删除、更新一个数据时想反回该操作的数据

-- 新增
insert into tablea ( key1, key2, key3 ) values ( value1, value2, value3 ) 
returning key1 , key2
-- 删除
delete from tablea returning id
-- 更新
update tablea set key1 = 'hello', key2 = 'world', ke3='nihao' returning id 
复制代码

group by : 是用于分组的, 对一些字段进行归类,然后可以聚合之类的。

select 
    sex,
    sum(id),
    array_agg(name) as names,   -- 这里将聚合出name,将以数组的形式展示
    array_to_string(array_agg(name)) as s_names, 这里将数组转字符串
from table2 group by sex
复制代码

array_agg(name) 输出数组

string_to_array(names,',') 将字符串转数组,并且以‘,’分割

array_to_ string(names) 将数组转字符串

row_to_ json( tablea ) 将表数据转成json, 但是像这样会有转义‘\’的

select row_to_json( tablea ) as a  from tablea  -- 将行数据转成json
复制代码

所以 array_to_json(array_agg(row_to_json(a))) 就可以将行完全转为json, 对于json来说,是有key 值的, 那么转为json后它的key主要有 a表字段名来决定的。

这是我之前写过的一个tree实例,业务逻辑不用管哦 哈哈 ,看语法就成

SELECT  label,
        VALUE,
	children 
FROM
	(
	SELECT
		a.info_sys_name AS label,
		a.info_sys_id || ',' || a.info_sys_name AS VALUE,
		a.agency_id,
		array_to_json (
			array_agg (
			row_to_json ( a ))) AS children 
	FROM
		(
		SELECT DISTINCT
			a.info_sys_id,
			a.agency_id,
			a.info_sys_name,
			b.sys_node_name AS label,
			b.sys_node_id || ',' || b.sys_node_name AS 
		VALUE
			
		FROM
			h_information_system AS a
			INNER JOIN h_system_node AS b ON a.info_sys_id = b.info_sys_id 
			AND a.state = '1' 
			AND b.state = '1' 
			AND b.sys_node_name <> '' 
			AND b.sys_node_id <> '' 
		) AS a
		LEFT JOIN sys_agency_info AS b ON a.agency_id = b.agency_id 
		AND b.state = '1' 
	GROUP BY
		a.info_sys_name,
		a.info_sys_id,
		a.agency_id 
	) AS a 
WHERE
	a.agency_id = '1' 
	AND a.label <> '' 
	AND a.
VALUE
	<> ''
复制代码

with RECURSIVE 递归: 大家都知道递归就是自己调自己,这里主要是通过已知时间获取最近10天的时间, 切记 一定得加limit 限制 要不然会跑死的

WITH RECURSIVE t(static_time) AS (
    SELECT date_trunc('day', CURRENT_DATE)  
    UNION ALL
    SELECT static_time-interval '1 day' FROM t
)

select  * from t limit 10
复制代码

case when :就是sql中的判断, 一般应用于对照表,比如 性别:sex, 姓名:name。如果说需要有多个值判断,则case when 字段名 then 'eee' end ,如果只针对一个字段判断的话,case 字段名 when 'eee' end

-- 多个字段判断
select  case when fonttype = 'name' then '姓名'
             when typenttt = 'sex' then '性别' 
             else '其他' end as  typename
from tablea

-- 一个字段判断 
select  case typename when '张甜甜' then 'ok' 
             when  '赵天天天' then 'no' end as nametype 
from tablea
复制代码

case when 结合聚合函数 sum、max、min 的使用

-- 统计state = 1 和 state = 0 的个数, 这里将state的这个字段分别统计出为1的个数,和为0的个数,并且生成两个字段
select
     sum(case when state = 1 then 1 end) as count1,
     sum(case when state = 0 then 0 end) as count0,  
from tablea
-- 例如 tableb 中 有两个字段,分别是科目(英语、数学、语文),成绩。现在想实现统计出英语、语文、数学分别作为字段的成绩。
就是简单的列转行
select
     max(case when subject = '英语' then score else 0) end as englist,
     max(case when subject = '数学' then score else 0) end as math,   
     max(case when subject = '语文' then score else 0) end as china,   
from tablea
复制代码

COALESCE:也是属于判断的一个方法, 判断如果该字段为null ,则可赋值

select coalesce(name, '赵天天') from tables
复制代码

CURRENT_DATE: 这个主要获取当前日期

select CURRENT_DATE
复制代码

(values(1), (2), (3) , (4)) as t(event_grade) : 这个是自己创建一个临时表作为暂存用的,应用场景有可能是我需要一个已知的表去左关联另个表,这样就可以临时创建一个表

-- 这个表名为t , 字段为riskitem_type, type_index
 select * from (values('新兴','1'),('流行','2') ,('高危','3'), ('攻击','4'),('其他','5')) as t(riskitem_type, type_index) 
复制代码

rank() over(order by score desc): 用于排名,这里通过score进行分组, desc 或者 asc 用于正序还是倒序 , rank() 这里的排名是可以并列排名的, 下一个空出名次

dense_rank() over(order by score desc) 和 rank() 的区别是,这里的排名是可以并列排名的, 下一个不空出名次

row_number() over(order by score desc) 和上面的区别是,不可以并列名次的,都是进行连续排名

select rank() over(order by score desc) as rank  from  tablea
复制代码

array[]: 是数组,我在项目中通常知道有几个值,需要以数组返回

select array['菜单','首页','个人中心'] as msg
复制代码

但是取数组值的时候不能从第0 位取, 只能从第一位开始取

select msg[1] as msg1,
       msg[2] as msg2
from (
  select array['菜单','首页','个人中心'] as msg
) tablea
复制代码

::integer 这个就是强制转换为 integer 类型, :: 加类型 就可以强制转化类型了

-- 字符串转整型
select  '1' :: integer

-- 整型转text

select '5656565656' :: text
复制代码

to_char 就是将时间转化为指定格式的字符串类型

select to_char(CURRENT_DATE, 'yyyy-mm-dd')
复制代码

distinct 就是用于去重,但是distinct 只能放在头部 如: select name, distinct sex from tablea 这样语法不允许

select distinct event_grade from (values(1), (1), (3) , (4)) as t(event_grade) 
复制代码

date_trunc:时间截取函数

select trunc(sysdate) from dual    //返回当前日期  sysdate: 系统时间
select trunc(sysdate,'year') from dual;   //返回本年的第一天
select trunc(sysdate,'month') from dual;   //返回本月的第一天
select trunc(sysdate,'q') from dual;       //返回本季度的第一天
select to_char(trunc(sysdate),'yyyy-mm-dd hh24:mi:ss') from dual;  //获取当天的零时零分零秒
复制代码

||: 用于字符串拼接

select 'a' || 'b'
复制代码

split_part: 用于对字符串的截取 , 一定得从第一位开始

select  split_part( '56-96', '-', 2 )
复制代码

interval:用于对时间的加减

select date '2019-08-31' - interval '1 day' as start_time
复制代码
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改