PgSQL·常用操作语法

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 开始位置

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条

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

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 

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  -- 默认=空白
);

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)

to_char((CURRENT_DATE)::timestamp with time zone, 'yyyy'::text)::INTEGER //转整型
to_char(LOCALTIMESTAMP::timestamp with time zone, 'hh24:mi:ss'::text)
to_char(LOCALTIMESTAMP::timestamp with time zone, 'yyyy-MM-dd hh24:mi:ss "Dy"'::text) //带星期
to_char(LOCALTIMESTAMP, 'Day') //星期 1表星期日 2表示星期一
(to_char(CURRENT_DATE::timestamp with time zone, 'D'::text)::integer - 2)

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