前言
在这里我只用过postgresql, 对于其他的数据库,暂时没用过,但是后期会学习的。用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'
上面的基础就不多解释了
jion: 这里就是将表关联起了,我的理解是横向关联
左关联:这个主要以左边的表为主表,右边的表为副表,不管右边的表有没有可以关联上的数据,左边的表的数据一定有。
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 <> ''
目前就先写到这儿了,很简单的,还有好多呢,有空了剩下的sql我再做分享 ,主要太饿了