|
postgreSQL下载地址:目前开源的TBase版本是基于postgreSQL10的
|
https://www.enterprisedb.com/downloads/postgres-postgreSQL-downloads
|
|
|
安装教程
|
https://blog.csdn.net/engerla/article/details/127820191
|
|
postgreSQL建表示例：
|
CREATE TABLE "evaluation_records" (
|
"id" int8 NOT NULL,
|
"name" varchar(255) COLLATE "default" DEFAULT NULL::character varying,
|
"evaluation_score" int4,
|
"evaluation_date" date,
|
"evaluation_content" text COLLATE "default",
|
"attendance_probability" float4,
|
"status" int2 DEFAULT 0,
|
"remark" varchar(500) COLLATE "default" DEFAULT NULL::character varying,
|
"creator" varchar(32) COLLATE "default" DEFAULT NULL::character varying,
|
"updater" varchar(32) COLLATE "default" DEFAULT NULL::character varying,
|
"create_time" timestamp(6) DEFAULT CURRENT_TIMESTAMP NOT NULL,
|
"update_time" timestamp(6),
|
PRIMARY KEY ("id")
|
);
|
COMMENT ON TABLE "evaluation_records" IS '评价记录表';
|
|
COMMENT ON COLUMN "evaluation_records"."id" IS '主键';
|
COMMENT ON COLUMN "evaluation_records"."name" IS '姓名';
|
COMMENT ON COLUMN "evaluation_records"."evaluation_score" IS '评价分数';
|
COMMENT ON COLUMN "evaluation_records"."evaluation_date" IS '评价日期';
|
COMMENT ON COLUMN "evaluation_records"."evaluation_content" IS '评价内容：富文本';
|
COMMENT ON COLUMN "evaluation_records"."attendance_probability" IS '出勤率';
|
COMMENT ON COLUMN "evaluation_records"."status" IS '状态: 0-删除,1-有效';
|
COMMENT ON COLUMN "evaluation_records"."remark" IS '备注';
|
COMMENT ON COLUMN "evaluation_records"."creator" IS '创建人';
|
COMMENT ON COLUMN "evaluation_records"."updater" IS '更新人';
|
COMMENT ON COLUMN "evaluation_records"."create_time" IS '创建时间';
|
COMMENT ON COLUMN "evaluation_records"."update_time" IS '更新时间';
|
|
建表注意事项：
|
建议表名和字段名都统一为小写！因为在postgreSQL中查询的时候必须对大写字段添加双引号才能执行，例如：SELECT * from train_toolkit_quartz WHERE "STATE" = 1;
|
两张关联表，关联字段的字段类型一定要保持一致！否则，关联查询的时候postgreSQL会报错！
|
|
常用字段类型对应关系：
|
int8 对应mysql中的 bigint
|
int4 对应mysql中的 int
|
int2 对应mysql中的 smallint
|
longtext 对应mysql中的 text
|
timestamp(6) 对应mysql中的 datetime
|
date 对应mysql中的 date
|
|
mysql建表语句，示例：
|
CREATE TABLE `evaluation_records` (
|
`id` bigint(20) NOT NULL COMMENT '主键',
|
`name` varchar(255) DEFAULT NULL COMMENT '姓名',
|
`evaluation_score` int(3) DEFAULT NULL COMMENT '评价分数',
|
`evaluation_date` date DEFAULT NULL COMMENT '评价日期',
|
`evaluation_content` longtext COMMENT '评价内容：富文本',
|
`attendance_probability` double(2,2) DEFAULT NULL COMMENT '出勤率',
|
`status` smallint(1) NOT NULL DEFAULT '0' COMMENT '状态: 0-删除,1-有效',
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
`creator` varchar(32) DEFAULT NULL COMMENT '创建人',
|
`updater` varchar(32) DEFAULT NULL COMMENT '更新人',
|
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
PRIMARY KEY (`id`)
|
) COMMENT='评价记录表';
|
|
插入数据：
|
-- 通用写法
|
INSERT INTO evaluation_records VALUES ('713503081040800400', '张三', '10', '2023-09-15', '<p>评价依据：XXX</p>', '0.89', '1', NULL, '000018e30cce41eea59765009e413f82', NULL, '2023-09-15 18:45:11', NULL);
|
-- mysql
|
INSERT INTO `evaluation_records` VALUES ('713503081040800400', '张三', '10', '2023-09-15', '<p>评价依据：XXX</p>', '0.89', '1', NULL, '000018e30cce41eea59765009e413f82', NULL, '2023-09-15 18:45:11', NULL);
|
-- postgreSQL
|
INSERT INTO "evaluation_records" VALUES ('713503081040800400', '张三', '10', '2023-09-15', '<p>评价依据：XXX</p>', '0.89', '1', NULL, '000018e30cce41eea59765009e413f82', NULL, '2023-09-15 18:45:11', NULL);
|
|
修改数据
|
-- 通用写法
|
UPDATE evaluation_records SET name='李四' WHERE id='713503081040800400';
|
|
查询数据：
|
-- 通用写法
|
SELECT * FROM evaluation_records WHERE name='李四';
|
|
-- 通用写法：AS关键字
|
SELECT name,evaluation_score AS "evaluationScore" FROM evaluation_records WHERE name='李四';
|
|
-- 通用写法：时间等于
|
SELECT * FROM evaluation_records WHERE create_time = '2023-09-15 18:45:11';
|
|
-- 通用写法：时间区间
|
SELECT * FROM evaluation_records WHERE create_time between '2023-09-15' and '2023-09-16';
|
|
删除数据：
|
-- 通用写法
|
DELETE FROM evaluation_records WHERE id='713503081040800400';
|
|
创建索引：
|
-- 普通索引，通用写法
|
CREATE INDEX idx_name_score ON evaluation_records(name,evaluation_score);
|
-- 唯一索引，通用写法
|
CREATE UNIQUE INDEX idx_status ON evaluation_records(status);
|
|
分页：
|
-- postgreSQL分页
|
SELECT
|
id,
|
type,
|
name,
|
code,
|
pid,
|
remark
|
FROM
|
sys_org
|
WHERE
|
type = 1
|
AND pid = 1323441314737557510
|
ORDER BY state DESC,seq ASC
|
-- 从第0条开始，检索10条
|
OFFSET 0 LIMIT 10
|
|
-- mysql分页
|
SELECT
|
id,
|
type,
|
name,
|
code,
|
pid,
|
remark
|
FROM
|
sys_org
|
WHERE
|
type = 1
|
AND pid = 1323441314737557510
|
ORDER BY state DESC,seq ASC
|
-- 从第0条开始，检索10条
|
LIMIT 0,10
|
|
|
常用函数对应关系：
|
<> 对应mysql中的 !=
|
|
OR 对应mysql中的 ||
|
|
TO_CHAR() 对应mysql中的 DATE_FORMAT()
|
|
yyyy-mm-dd hh24:mi:ss 对应mysql中的 %Y-%m-%d %H:%i:%S
|
|
DATE() 对应mysql中的 DAY()
|
|
CURRENT_DATE 对应mysql中的 CURDATE()
|
|
CASE WHEN type=1 THEN 1 ELSE 0 END 对应mysql中的 IF(type=1,1,0) 备注，type为字段名称
|
|
COALESCE() 对应mysql中的 IFNULL()
|
|
CONCAT(字段1,'分隔符',字段2)) 对应mysql中的 CONCAT(字段1,"分隔符",字段2))
|
|
STRING_AGG(字段名称,',') 对应mysql中的 GROUP_CONCAT(字段名称)
|
|
不支持mysql中的时间差计算函数TIMESTAMPDIFF()
|
|
GROUP BY函数：
|
-- postgreSQL写法
|
SELECT
|
tfs.name,
|
tfs.total_num
|
FROM
|
train_fixed_assets tfs
|
GROUP BY tfs.name,tfs.total_num
|
ORDER BY tfs.total_num DESC
|
|
-- mysql写法
|
SELECT
|
tfs.name,
|
tfs.total_num
|
FROM
|
train_fixed_assets tfs
|
GROUP BY tfs.name
|
ORDER BY tfs.total_num DESC
|
|
|
针对xxMapper.xml文件：
|
-- postgreSQL写法：万万不可省略AS关键字！
|
SELECT name,evaluation_score AS "evaluationScore" FROM evaluation_records
|
<WHERE>
|
status = 1
|
<if test="creator!=null">
|
and creator = #{creator}
|
</if>
|
<if test="startTime!=null">
|
and to_char(create_time,'yyyy-mm-dd hh24:mi:ss') <![CDATA[ >= ]]> #{startTime}
|
</if>
|
<if test="endTime!=null">
|
and to_char(create_time,'yyyy-mm-dd hh24:mi:ss') <![CDATA[ <= ]]> #{endTime}
|
</if>
|
</WHERE>
|
|
-- mysql写法
|
SELECT name,evaluation_score 'evaluationScore' FROM evaluation_records
|
<WHERE>
|
status = 1
|
<if test="creator!=null">
|
and creator = #{creator}
|
</if>
|
<if test="startTime!=null">
|
and DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') <![CDATA[ >= ]]> DATE_FORMAT(#{startTime},'%Y-%m-%d %H:%i:%S')
|
</if>
|
<if test="endTime!=null">
|
and DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') <![CDATA[ <= ]]> DATE_FORMAT(#{endTime},'%Y-%m-%d %H:%i:%S')
|
</if>
|
</WHERE>
|
