PgSQL·常用操作语法

PostGroeSQL·常用操作语法

共性:值使用单引号,字段使用双引号,字段名区分大小写

一、查询

1】查询列表

查询全部

SELECT * FROM "weixin"."life_Menu" -- 说明 SELECT * FROM "模式库名"."表名" LIMIT 查询数量

搜索存在内容

SELECT "id","name","text" FROM "life_Menu" WHERE "intShow" < 0 AND "name" LIKE '%搜索内容%' ORDER BY "id" DESC LIMIT 1000 -- 搜索存在内容

开始位置:

SELECT * FROM "weixin"."life_Menu" LIMIT 10 OFFSET 1 -- 说明 SELECT * FROM "模式库名"."表名" LIMIT 查询数 OFFSET 开始位置

倒序排序:(倒序desc 顺序asc

SELECT * FROM "weixin"."life_Menu" ORDER BY "id" desc limit 1000
-- 说明 SELECT * FROM "模式库名"."表名" 根据id 倒序 limit 返回数量
SELECT * FROM "life_Menu" order by "id" asc
-- 说明 SELECT * FROM "表名" 根据id 顺序 返回全部

分页查询:

SELECT * FROM "life_Menu" ORDER BY "id" desc OFFSET 1 limit 10 -- 说明 SELECT * FROM "表名" 根据id 倒序 OFFSET开始位置 LIMIT查询数
SELECT * FROM "life_Menu" WHERE "id" > 1 ORDER BY "id" desc limit 10 -- 说明 SELECT * FROM "表名" 条件 order根据id 倒序 LIMIT 查询10条

查询不重复:(DISTINCT ON (id)

SELECT DISTINCT ON ("user_id") "id","date","name" FROM "表模式名"."表名" WHERE "id"=999 --不排序

SELECT DISTINCT ON ("user_id") ROW_NUMBER() OVER (order by "id" DESC ) "id","date","name" FROM "表模式名"."表名" WHERE "id"=999
--排序 取最大id

字段查询:(字段名 使用双引号

SELECT "id","intShow","name" FROM "life_Menu" WHERE "intShow" > 1 ORDER BY "id" desc limit 10 -- 说明 字段名需要用双引号

条件查询:(匹配值 使用单引号

SELECT "id","intShow","name" FROM "life_Menu" WHERE "intShow" > 0 AND "text" = '345' OR "text" = '123' ORDER BY "id" desc limit 100 -- 注意: 匹配值 使用单引号 AND和值 OR或值

返回新字段名称:(原字段名使用 双引号

SELECT "id","intShow","name", "text" AS "AAA" FROM "life_Menu" -- 注意: 返回自定义字段名 “原字段名” AS “新字段名”

返回自定义字段和自定义值:(自定义值使用 单引号

SELECT "id","intShow","name", 'div' AS "AAA" FROM "life_Menu" -- 注意: 返回自定义字段和自定义值:(自定义值使用 单引号) ‘自定义值’ AS “自定义字段名”

统计某内容 数量(值使用单引号%包裹

SELECT count(0) FROM "life_Menu" WHERE "text" LIKE '%1%' -- 说明:查询表单统计 某字段存在某内容 的数量

统计某内容等于某值 数量(值使用单引号

SELECT count(0) FROM "life_Menu" WHERE "text" = '101' -- 说明:查询表单统计 某字段内容等于某值 的数量

查询附判断条件

SELECT "id","name",(CASE when "intShow"> 0 THEN '可见' when "intShow"< 2 THEN '不可见' ELSE '未知' END) AS "show" FROM "life_Menu" -- 说明:查询附判断条件

查询全部数据库

qSELECT * FROM pg_database -- 查询全部数据库
-- 文件存储位置 /www/server/pgsql/data/base

2】多表查询

多表独链

SELECT AA."id",AA."name",BB."blok" FROM "life_Menu" AS AA ,"mall_Classify" AS BB WHERE AA."id"=1 AND BB."id"=2 

多表内联查询()

SELECT AA."id",AA."name",AA."text",BB."blok" FROM "life_Menu" AS AA INNER JOIN "mall_Classify" AS BB ON AA."id"= BB."id" -- 说明:多表查询 内联查询 INNER JOIN 链接

多表内联条件查询

SELECT AA."id",AA."name",AA."type",AA."text",BB."blok" FROM "life_Menu" AS AA INNER JOIN "mall_Classify" AS BB ON AA."id"= BB."id" WHERE AA."intShow"> 1 AND BB."intShow"> 1
-- 说明:多表查条件询 内联查询 INNER JOIN 链接,结尾使用 WHERE 设置条件

多表全联结查询

SELECT * FROM "life_Menu" FULL OUTER JOIN "mall_Classify" ON "life_Menu"."id"= "mall_Classify"."id" -- 说明:多表查询表 全联结查询 FULL OUTER JOIN 链接

多表查询 附判断条件 内联查询

SELECT AA."id",AA."name",BB."blok",(CASE when AA."intShow"> 0 THEN '可见' when AA."intShow"< 2 THEN '不可见' ELSE '未知' END) AS "show" FROM "life_Menu" AS AA INNER JOIN "mall_Classify" AS BB ON AA."id"=BB."id" -- 说明:多表查询 附判断条件 内联查询,条件括号(CASE when END) INNER JOIN 链接,结尾使用 WHERE 设置条件

多表同字段查询

(SELECT id,blok,issue FROM "表1"  ORDER BY "id" DESC LIMIT 10) union all
(SELECT id,blok,issue FROM "表2"  ORDER BY "id" DESC LIMIT 10) union all
(SELECT id,blok,issue FROM "表3"  ORDER BY "id" DESC LIMIT 10) union all
(SELECT id,blok,issue FROM "表4"  ORDER BY "id" DESC LIMIT 10)  --使用union all链接,语句使用括号包裹

三表联查例子:

SELECT 
A."id",
A."date",
A."time",
A."article_id",
A."article_user_id",
A."text",
A."img",
A."video",
A."address",

B."id" AS "user_id",
B."name" AS "user_name",
B."img" AS "user_img",
B."tfReal" AS "user_tfReal",
B."tfCom" AS "user_tfCom",

C."id" AS "user2_id",
C."name" AS "user2_name",
C."img" AS "user2_img",
C."tfReal" AS "user2_tfReal",
C."tfCom" AS "user2_tfCom"
FROM
"weixin"."life_comment" AS A 
-- INNER JOIN "user"."user_info" AS B ON A."comment_user_id" = B."id" --绝对存在值才有查询结果(内联)
-- INNER JOIN "user"."user_info" AS C ON A."reply_user_id" = C."id" --绝对存在值才有查询结果(内联)
    -- RIGHT JOIN "user"."user_info" AS B ON A."comment_user_id"= B."id" --相对存在值即可查到结果(外右联)
-- RIGHT JOIN "user"."user_info" AS C ON A."reply_user_id"= C."id" --相对存在值即可查到结果(外右联)
LEFT JOIN "user"."user_info" AS B ON A."comment_user_id"= B."id" --相对存在值即可查到结果(外左联)
LEFT JOIN "user"."user_info" AS C ON A."reply_user_id"= C."id" --相对存在值即可查到结果(外左联)
 
WHERE
A."intShow"> 1 AND A."article_id"=2 
ORDER BY
A."id" DESC 

二、操作

1】存储

插入新数据 (返回id)

INSERT INTO "life_Menu" ("id","name","text") VALUES (6,'家政服务66','101') RETURNING "id" -- 插入新数据 RETURNING "id" 返回id编号

插入多数据

INSERT INTO "life_Menu" ("name","text") VALUES ('服务1','内容1'),('服务2','内容2'),('服务3','内容3') RETURNING "id" -- 插入多个数据 RETURNING "id" 返回id编号

更新数据无则创建

INSERT INTO "life_Menu" (id,name,text) VALUES (2,'名称1','内容1') ON CONFLICT(id) DO UPDATE SET "name"='名称1',"text"='内容1' RETURNING "id"; -- 更新数据,没有就创建 CONFLICT包裹id条件,RETURNING "id" 返回id编号

条件更新数据

UPDATE "life_Menu" SET "name"='名称1',"text"='内容2' WHERE "id"=1;

条件替换存在内容

UPDATE "life_Menu" SET "name"=replace("name",'名称1','名称111') WHERE "id" = 1 -- 条件替换存在的内容

条件修改某字段内容

UPDATE "life_Menu" SET "intShow" = '1234' WHERE "id" = 1 -- 条件修改某字段内容

条件某字段累加值

UPDATE "life_Menu" SET "intShow" = "intShow" + 1 WHERE "id" = 1 -- 条件某字段累加值

创建表

CREATE TABLE "life_menu" (
  "id" int8 NOT NULL DEFAULT nextval('id_add'::regclass), -- NOT NULL不是空值 DEFAULT默认自增
  "blok" varchar(20) DEFAULT ''::character varying, -- 默认=空白
  "intSorting" int2 DEFAULT 0,  -- 默认=0
  "tfShow" bool DEFAULT true,  -- 默认=真
  "type" varchar(255)  DEFAULT ''::character varying  -- 默认=空白
);

2】删除

条件删除数据

DELETE FROM "life_Menu" WHERE "id" = 0 -- 条件删除数据

其他

字段自动添加时间日期

# 设置字段默认值为当前年度时间(在字段默认值添加)
to_char((CURRENT_DATE)::timestamp with time zone, 'yyyy'::text)
to_char((CURRENT_DATE)::timestamp with time zone, 'yyyy-MM-dd'::text)
to_char((CURRENT_DATE)::timestamp with time zone, 'yyyy-MM-dd hh:mi:ss'::text)
to_char((CURRENT_DATE)::timestamp with time zone, 'yyyy-MM-dd hh:mi:ss'::text)
to_char(LOCALTIMESTAMP::timestamp with time zone, 'yyyy-MM-dd hh24:mi:ss'::text)

设置id自增

a、先设置 数据库表下 序列下 创建对象

b、在查询运行命令 或 在表中设计 id 添加默认规则 nextval(‘表名.id_add’) 格式一定要表名+一个点 关联

CREATE SEQUENCE id_add START 1; -- 创建自增序列对象 
ALTER TABLE "life_Menu" ALTER COLUMN "id" SET DEFAULT nextval('id_add');-- 设置自增对象

避免死锁的建议

(1)如果平台中存在大事务,尽量将其拆分为小事务。因为大事务一般操作的数据库表或者数据都比较多,因此造成死锁或者阻塞的概率就会相对较大。

(2)为数据库表设计合理的索引,尽量避免数据查询时索引未覆盖或者索引失效的情况,因为全表扫描会会导致给表中的数据行上锁,大大增加了数据库产生死锁的概率。

(3)如果业务允许,我们可以尝试将隔离级别调低,比如将隔离级别从RR调整为RC,可以避免掉很多因为gap锁造成的死锁。

(4)在我们自己的代码中,尽量以一致的顺序获取对象上的锁,避免事务中SQL交互执行,从而降低死锁发生的概率。

962 Views
分享你的喜爱
linwute
linwute

我要像梦一样自由,像大地一样宽容;
在艰辛放逐的路上,点亮生命的光芒;
我要像梦一样自由,像天空一样坚强;
在曲折蜿蜒的路上,体验生命的意义;

留下评论

您的电子邮箱地址不会被公开。 必填项已用*标注