PgSql基础练习

• 基本语法

--分页offset 和limit 位置可以互换 不影响结果
select * from jinxudong.t_student offset 0 limit 2;
select * from jinxudong.t_student limit 2 offset 0;

--表明加别名 用as 不用as 都可以
select * from jinxudong.t_student as a  where a.id<23 limit 2 offset 0;
select * from jinxudong.t_student  a  where a.id<23 limit 2 offset 0;

--列明别名用as 和不用as 都可以 且都是小写
select a.id as D,a.address dizhi from jinxudong.t_student a where a.id<33
and address='beijing'
limit 10;

--查看执行计划
explain
select a.id as D,a.address dizhi from jinxudong.t_student a where
a.id>42
and address like '%张%'
limit 10 offset 0;
--创建索引
create index index_address on jinxudong.t_student(address);

-- 查看索引 直接表名 不用加schemas
select * from pg_indexes where tablename='t_student';

-- 删除索引 必须有schemas
drop index jinxudong.i_address;

pgsql可以在Btree索引上指定操作符：text_pattern_ops、varchar_pattern_ops和 bpchar_pattern_ops，它们分别对应字段类型text、varchar和 char，官方解释为“它们与默认操作符类的区别是值的比较是严格按照字符进行而不是根据区域相关的排序规则。这使得这些操作符类适合于当一个数据库没有使用标准“C”区域时，被使用在涉及模式匹配表达式（LIKE或POSIX正则表达式）的查询中

--创建索引
create index index_address on jinxudong.t_student(address varchar_pattern_ops);

--再查执行计划就会走索引
explain
select a.id as D,a.address dizhi from jinxudong.t_student a where
a.id>42
and address like '张%'
limit 10 offset 0;


-- 年
select to_char(to_timestamp(1608018517000 / 1000),'yyyy');
-- 年-月
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM');
-- 年-月-日
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd');
-- 年-月-日 时
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd HH24'); 对应JAVA 的“yyyy-MM-dd HH”
-- 年-月-日 时:分
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd HH24:MI');对应JAVA 的“yyyy-MM-dd HH:mm”
-- 年-月-日 时:分:秒
select to_char(to_timestamp(1608018517000 / 1000),'yyyy-MM-dd HH24:MI:SS');对应JAVA 的“yyyy-MM-dd HH:mm:ss”

--查询某一天日期 不走索引（字段带函数）
explain
select * from jinxudong.t_student where
to_char(create_time,'yyyy-MM-dd')='2022-08-22';
--当前时间加一天
SELECT now()::timestamp + '1 day';
--某天字符串加一天
SELECT to_timestamp('20220823','yyyyMMdd') + '1 day';
--查询某一天走索引，小于明天大于今天

--查询某一天走索引，小于明天大于等于今天
explain
select * from jinxudong.t_student where
create_time <(to_timestamp('20220822','yyyyMMdd') + '1 day') and
create_time >=to_timestamp('20220822','yyyyMMdd');


--批量写入
INSERT INTO jinxudong.movie(
name)
VALUES ('杨戬'),('沉香'),('西游记');

--do update set 插入更新
-- upsert on conflict(id) 主键或者唯一索引相同 存在则执行update
--excluded 为虚拟表 接受传过来的新值
INSERT INTO jinxudong.movie(id,
name)
VALUES (7,'悟空1'),(8,'白龙马2'),(9,'郭靖')
on conflict(id)
do update set
name=excluded.name;

-- do nothing 不做任何事情
INSERT INTO jinxudong.movie(id,
name)
VALUES (7,'悟空1'),(8,'白龙马2'),(9,'郭靖')
on conflict(id)
do nothing;