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),'
');
把一个表中的数据插入到另一个表中
--把一个表中的数据插入到另一个表中
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，就转为空字符串