pgsql语法

postgreSQL常用语法

1、CRUD增删改查

创建用户角色

 create  user ldc  with password 'ldc-';

创建数据库

 create DATABASE school_info 
 ENCODING  ='utf-8'  --指定字符集    
 TABLESPACE = pg_default 
 owner ldc; --设置数据库所有者
 grant all privileges on database school_info  to ldc;  --将 school_info 数据库的所有权限赋予 ldc

创建表

 -- 创建班级表
 create table class_info
 (
     id                    serial not null
                           constraint class_info_pk
                           primary key,
     name                  varchar,
     write_date            timestamp
 );
 
 comment on table class_info is '班级表';
 comment on column class_info.name is '班级名称';
 comment on column class_info.write_date is '修改时间';
 alter table class_info  owner to ldc;   -- 修改表拥有者为ldc
 
 
 --创建表
 -- 创建学生表，id自增
 -- id      serial not null  表示id自增
 -- id      integer not null  表示id不自增
 create table student
 (
     id                    serial not null
                           constraint student_pk
                           primary key,
     name                  varchar,
     class_id             integer  references "class_info"("id"),
     height                numeric,
     weight                numeric,
     write_date            timestamp
 );
 
 comment on table student is '学生表';
 comment on column student.name is '名称';
 comment on column student.class_id is '班级ID';
 comment on column student.height is '身高';
 comment on column student.weight is '体重';
 comment on column student.write_date is '修改时间';
 alter table student  owner to ldc;   -- 修改表拥有者为ldc

增加记录

 insert into "class_info" (name,write_date) values('高一八班', '2010-09-09 11:33:00');
 
 insert into "student" (name,class_id, height, weight, write_date) 
     values ('小梁',1,160,50, '2010-09-09 12:33:00'),
     ('小文',1,155,50, '2010-10-08 13:33:00'),
     ('小强',1,175,60, '2010-11-12 13:33:00');

删除表

 -- 如果表存在就先删除
 drop table if exists student;

删除记录

 delete from student where name='小梁'

删除字段

 alter table 表名 drop column 列名 ;
 比如
 alter table student drop column sex ;

更新记录

 update student set name='大梁' where id=3

新增或更新

--如果id冲突就更新
insert into student(id, name,class_id) 
	values(1,'小兰',1)
	on conflict(id) 
	do update set name ='小芳';

--如果id冲突就什么也不做
insert into  student(id, name,class_id)
   values(3,'小明',1)
   on conflict(id) do nothing;

联合子集更新

# 联合子集更新，把sale_order_line的name连接换行符，然后按id更新到表a_test中对应的name
update a_test set name=array_to_string(array(select name from sale_order_line where order_id=a_test.id),'<br/>');

把一个表中的数据插入到另一个表中

--把一个表中的数据插入到另一个表中
insert into 目标表名 (column1,column2,columnn) select value1,value2,valuen from  源表名
比如：
insert into student (name, classs_name,create_date) select  student_name as name, class_name, now() from class_table;

增加字段

alter table student add column sex bool;

查看用户角色

select * from pg_roles;

查看当前时间

now()
select now()

查看表所有字段

select * from information_schema.columns where table_schema='public' and table_name='student';

查看数据库所有表名

select tablename from pg_tables where schemaname='public'

2、按条件查询

升降序

-- 对查询结果按id降序显示
select * from student order by id desc

-- 对查询结果按id升序显示
select * from student order by id asc

转义字符

-- 转义字符, 查找name中包含单引号的记录
select * from student where name like  E'%'%'

查看表记录总数

方式一：

select relname as TABLE_NAME, reltuples as rowCounts from pg_class where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='public') order by rowCounts desc;

方式二：

select count(*) from student;

3、常用函数

array:将结果转换为数组

SELECT array(SELECT "name" FROM student);

结果：{小芳,小文,大梁}

array_to_string:将数组合并为字符串

select array_to_string(array[1,2,3], ',');

结果：1,2,3

cast:类型转换

select cast(id as varchar) from student;  --把id 从integer转成varchar

concat:字符串拼接

select 
concat('学生:id=', cast(s.id as varchar), '姓名:',s.name, '班级:',ci.name) 
from student as s
left join class_info as ci on ci.id=s.class_id

结果：学生:id=1姓名:小芳班级:高一八班

concat_ws:多字符串拼接(不用转换类型)

select 
concat_ws('学生:id=', s.id, '姓名:',s.name, '班级:',ci.name) 
from student as s
left join class_info as ci on ci.id=s.class_id

substring:字符截取

select substring('abcd',1,2); -- 表示下标从1开始,截取2个字符

结果：ab

row_number():定义行编号

--对行记录定义行编号，使用函数ROW_NUMBER()
select           
		ROW_NUMBER() OVER (ORDER BY id desc) AS sequence_number,
		id,name
from
		student

array_agg:把表达式变成一个数组

-- 名称降序然后组合成数组
select array_agg(name order by name asc) from student

结果：{大梁,小芳,小文}

unnest:一行变多行

select unnest(array_agg(name order by name asc)) from student

结果：{大梁,小芳,小文} ->

大梁

小芳

小文

array:把结果变成数组类型

select ARRAY(select unnest(array_agg(name order by name asc))) from student

结果：{大梁,小芳,小文}

合并查询同一列的多条记录

# PostgreSQL合并查询同一列的多条记录，针对一对多，多对多字段
比如表：

id   name                  
1    小明                   
1    小红                     id   name
1    小亮        -->          1    小明，小亮，小红
2    小强                     2    小强，小王
2    小王

SELECT 
    id, array_to_string(ARRAY(SELECT unnest(array_agg(name order by name desc))),',') AS all_name
FROM  
    student
GROUP BY id;

to_char：类型转换

select to_char(write_date, 'yyyy-MM-dd hh24:MI:ss') from student

case:枚举

--case语句
select
case 
	when score > 80 and score < 90 then '良'
	when score > 90 then '优秀'
else '中'
end as result
from student;

with ：临时表

--临时表、字符串合并、类型转换、时间格式转换、当前时间
WITH TEMP AS ( 
	SELECT CAST (concat (write_date, '-01' ) AS TIMESTAMP ) 
	   AS account_period_time 
		 FROM student AS s ) 
SELECT
	account_period_time,
	to_char(CURRENT_DATE,'yyyy-MM-dd hh24:MI:ss') as current_date,
	to_char( account_period_time, 'yyyy' ) as year,
	to_char( account_period_time, 'MM' ) as month,
	to_char( account_period_time, 'dd' ) as day 
FROM TEMP

结果：

account_period_time	current_date	    year	month	day
2019-06-01 0:00:00	2020-06-24 00:00:00	2019	 06	     01
2019-06-01 0:00:00	2020-06-24 00:00:00	2019	 06	     01

多个临时表

# 多个临时表
WITH temp_student AS ( SELECT ID, NAME, sex FROM student WHERE sex = TRUE ),
temp_class AS (
	SELECT
		ID,
		NAME,
		student_id,
		teacher_id
	FROM
		the_class 
	),
	temp_teacher AS (
	SELECT 
	  ID,
		NAME,
		age 
	FROM
		teacher 
	) 
	SELECT
	ts.NAME AS student_name,
	tc.NAME AS class_name,
	te.NAME AS teacher_name
	from temp_student as ts
	LEFT JOIN temp_class AS tc ON tc.student_id = ts.ID  
	LEFT JOIN teacher AS te ON te.id = tc.teacher_id

coalesce:返回参数中的第一个非null的值

-- null转成有意义的值
select coalesce(name, '') as name from student;  --name为null，就转为空字符串

4、其它

# 保留重复记录中的最小id的SQL语句
select min(id) as id,co1,co2 from test group by co1,co2

# 使用 interval 时间相加减(+/-)
当前时间 + 10秒，
select to_char(now() + interval '10 second', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;
当前时间 - 10秒
select to_char(now() + interval '-10 second', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;
 
当前时间 + 10分，
select to_char(now() + interval '10 minute', 'yyyy-mm-dd hh24:mi:ss')  as reqDate  from account_period;
 
当前时间 + 10时，
select to_char(now() + interval '10 hour', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;
 
当前时间 + 10天，
select to_char(now() + interval '10 day', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;

当前时间 + 10年，
select to_char(now() + interval '10 year', 'yyyy-mm-dd hh24:mi:ss')  as reqDate from account_period;


# 判断字段是否全为数字
select '1234' ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'
select '1234a' ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'
select * from student where score  ~ '^([0-9]+[.]?[0-9]*|[.][0-9]+)$'