目录
0.PostgreSQL的数据类型:
0.1.数值数据类型
0.2.字符串数据类型
0.3.日期/时间数据类型
0.4.布尔类型
0.5.货币类型
0.6.几何类型
1.PostgreSQL创建表
1.1.建表之前,创建自增序列
1.2.创建表,设置id为自增序列
2.夸表数据复制
3.分页处理
3.1.PostgreSQL两种分页方法查询
3.2.mysql 的分页
4.函数使用
5.数据类型转化(针对于PostgreSQL)
6.字段拼接
7.获取当前时间
0.PostgreSQL的数据类型:
0.1.数值数据类型
名称 | 描述 | 存储大小 | 范围 |
---|---|---|---|
smallint | 存储整数,小范围 | 2字节 | -32768 至 +32767 |
integer | 存储整数。使用这个类型可存储典型的整数 | 4字节 | -2147483648 至 +2147483647 |
bigint | 存储整数,大范围。 | 8字节 | -9223372036854775808 至 9223372036854775807 |
decimal [desɪml] | 用户指定的精度,精确 | 变量 | 小数点前最多为131072个数字; 小数点后最多为16383个数字 |
numeric [nuˈmɛrɪk] | 用户指定的精度,精确 | 变量 | 小数点前最多为131072个数字; 小数点后最多为16383个数字。 |
real | 可变精度,不精确 | 4字节 | 6位数字精度 |
double | 可变精度,不精确 | 8字节 | 15位数字精度 |
serial [ˈsɪəriəl] | 自动递增整数 | 4字节 | 1 至 2147483647 |
big serial | 大的自动递增整数 | 8字节 | 1 至 9223372036854775807 |
0.2.字符串数据类型
数据类型 | 描述 |
---|---|
char(size) | 这里size是要存储的字符数。固定长度字符串,右边的空格填充到相等大小的字符。 |
character(size) [ˈkærəktər] | 这里size是要存储的字符数。 固定长度字符串。 右边的空格填充到相等大小的字符 |
varchar(size) | 这里size是要存储的字符数。 可变长度字符串。 |
character varying(size) | 这里size是要存储的字符数。 可变长度字符串。 |
text | 可变长度字符串 |
0.3.日期/时间数据类型
名称 | 描述 |
---|---|
timestamp [ (p) ] [不带时区 ] | 日期和时间(无时区) |
timestamp [ (p) ]带时区 | 包括日期和时间,带时区 |
date | 日期(没有时间) |
time [ (p) ] [ 不带时区 ] | 时间(无日期) |
time [ (p) ] 带时区 | 仅限时间,带时区 |
0.4.布尔类型
名称 | 描述 | 存储大小 |
---|---|---|
boolean | 它指定true或false的状态。 | 1字节 |
0.5.货币类型
名称 | 描述 | 存储大小 | 范围 |
---|---|---|---|
money | 货币金额 | 8字节 | -92233720368547758.08 至 +92233720368547758.07 |
0.6.几何类型
名称 | 存储大小 | 表示 | 描述 |
---|---|---|---|
point | 16字节 | 在一个平面上的点 | (x,y) |
line | 32字节 | 无限线(未完全实现) | ((x1,y1),(x2,y2)) |
lseg | 32字节 | 有限线段 | ((x1,y1),(x2,y2)) |
box | 32字节 | 矩形框 | ((x1,y1),(x2,y2)) |
path | 16+16n字节 | 封闭路径(类似于多边形) | ((x1,y1),…) |
polygon | 40+16n字节 | 多边形(类似于封闭路径) | ((x1,y1),…) |
circle | 24字节 | 圆 | <(x,y),r>(中心点和半径) |
1.PostgreSQL创建表
1.1.建表之前,创建自增序列
创建自增序列
--创建序列 CREATE SEQUENCE "xiang_base"."cloud_new_id_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; --设置丛多少开始自增 SELECT setval('"xiang_base"."cloud_new_id_seq"', 1, true); --给序列指定字段 ALTER SEQUENCE "xiang_base"."cloud_new_id_seq" OWNED BY "xiang_base"."cloud_news"."id"; --给序列指定所有人 ALTER SEQUENCE "xiang_base"."cloud_new_id_seq" OWNER TO "cloud_admin";
1.2.创建表,设置id为自增序列
设置自增序列:nextval('xiang_base.cloud_new_id_seq'::regclass)
CREATE TABLE "xiang_base"."cloud_news" ( "id" int8 NOT NULL DEFAULT nextval('xiang_base.cloud_new_id_seq'::regclass), "title" TEXT COLLATE "pg_catalog"."default", "news_content" TEXT COLLATE "pg_catalog"."default", "news_content_m" TEXT COLLATE "pg_catalog"."default", "pic_path" VARCHAR ( 256 ) COLLATE "pg_catalog"."default", "intro" TEXT COLLATE "pg_catalog"."default", "is_deleted" bool NOT NULL DEFAULT FALSE, "creator" VARCHAR ( 32 ) COLLATE "pg_catalog"."default", "updater" VARCHAR ( 32 ) COLLATE "pg_catalog"."default", "create_time" TIMESTAMP ( 3 ) DEFAULT now( ), "update_time" TIMESTAMP ( 3 ) DEFAULT now( ), "description" TEXT COLLATE "pg_catalog"."default", "keywords" TEXT COLLATE "pg_catalog"."default", CONSTRAINT "cloud_news_pkey1" PRIMARY KEY ( "id" ) ); ALTER TABLE "xiang_base"."cloud_news" OWNER TO "cloud_admin"; COMMENT ON COLUMN "xiang_base"."cloud_news"."title" IS '新闻标题'; COMMENT ON COLUMN "xiang_base"."cloud_news"."news_content" IS '新闻内容'; COMMENT ON COLUMN "xiang_base"."cloud_news"."news_content_m" IS '新闻移动端内容'; COMMENT ON COLUMN "xiang_base"."cloud_news"."pic_path" IS '新闻图片'; COMMENT ON COLUMN "xiang_base"."cloud_news"."intro" IS '介绍'; COMMENT ON COLUMN "xiang_base"."cloud_news"."is_deleted" IS '删除标志:FALSE:未删除,TRUE:已删除'; COMMENT ON COLUMN "xiang_base"."cloud_news"."creator" IS '创建人'; COMMENT ON COLUMN "xiang_base"."cloud_news"."updater" IS '更新人'; COMMENT ON COLUMN "xiang_base"."cloud_news"."create_time" IS '创建时间'; COMMENT ON COLUMN "xiang_base"."cloud_news"."update_time" IS '更新时间'; COMMENT ON COLUMN "xiang_base"."cloud_news"."description" IS '描述'; COMMENT ON COLUMN "xiang_base"."cloud_news"."keywords" IS '关键词';
2.夸表数据复制
--从一张表复制数据到另一张表 INSERT INTO xiang_base.cloud_news ( ID, title, news_content, news_content_m, pic_path, intro, is_deleted, creator, updater, create_time, update_time ) SELECT ID :: int8, title, news_content, news_content_m, pic_path, intro, is_deleted, creator, updater, create_time, update_time FROM xiang_base.cloud_news_bak_20221214_1;
3.分页处理
3.1.PostgreSQL两种分页方法查询
在3000W数据的时候,建议使用第一种
--第一种 SELECT * FROM test_table WHERE i_id>0 limit 100;
--第二种 offset从0开始,offset 0表示从第一行开始获取。 SELECT * FROM test_table limit 100 OFFSET 0;
3.2.mysql 的分页
SELECT * FROM test_table limit 100, 10;
4.函数使用
--时间转字符串 --MySQL: date_format(a.tag_create_date,‘%Y-%m-%d %H:%i:%s’) --PostgreSQL: to_char(a.tag_create_date,‘yyyy-mm-dd HH:MM:SS’)
--字符串转时间: --MySQL: date_format(a.tag_create_date,‘%Y-%m-%d %H:%i:%s’) --PostgreSQL: to_date(a.tag_create_date,‘yyyy-mm-dd HH:MM:SS’)
--IFNULL()函数 --MySQL: IFNULL(a.idm,‘’) --PostgreSQL: COALESCE(a.id,‘’)
-- sysdate()函数 MySQL: SELECT sysdate() PostgreSQL: SELECT now()
-- find_in_set()函数(允许在逗号分隔的字符串列表中查找指定字符串的位置) MySQL: SELECT t.dept_id FROM sys_dept t WHERE find_in_set(‘100’, ancestors) PostgreSQL: SELECT t.dept_id FROM sys_dept t WHERE ‘100’ = ANY (string_to_array(ancestors, ‘,’))
-- group_concat()函数 MySQL: select a.name,group_concat(distinct city)from user_city a group by a.name; PostgreSQL: select a.name,array_to_string(array_agg(distinct a.city),‘,’)from user_city a group by a.name;
-- LIMIT MySQL: select id,name from hospital.ods_user_basic limit 10,2; PostgreSQL: select id,name from hospital.ods_user_basic limit 10 offset 2;
-- DISTINCT MYSQL: select DISTINCT b.id from hospital.ods_user_basic as b PostgreSQL: select DISTINCT ON (b.id) b.* from hospital.ods_user_basic as b
-- MyBatis-Plus整合MySQL、PostgreSQL,LIKE使用 -- LIKE是一般用法,ILIKE匹配时则不区分字符串的大小写 -- MySQL: -- PostgreSQL:
5.数据类型转化(针对于PostgreSQL)
-- 强转(在查询字段上指定数据类型) a.a1 = b.b1::int8 -- 或者 a.a1::varchar = b.b1 -- 或者 CAST(a.a1 AS varchar) MySQL:CAST(a.a1 AS varchar)
6.字段拼接
如果需要对拼接的值去重,可以这样处理:
SELECT string_agg(address,',') FROM student GROUP BY grade; --未去重的情况下: string_agg| ----------| 天河一路,天河一路 | 天河一路,天河一路 | SELECT string_agg(distinct address,',') FROM student GROUP BY grade; string_agg| ----------| 天河一路 | 天河一路 |
如果想对拼接的值做排序,可以在拼接符号后面加order by
SELECT string_agg(name,',' order by name desc) FROM student GROUP BY grade; --结果: string_agg| ----------| 小B,小A | 小D,小C |
7.获取当前时间
MySQL
- CURDATE(), CURTIME(), NOW():可以获取客户端所在时区的当前时间;
- UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP():可以获得当前的UTC时间;
- CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP():就是CURDATE(), CURTIME(), NOW()的别名
mysql> SELECT CURDATE(), CURTIME(), NOW(); +------------+-----------+---------------------+ | CURDATE() | CURTIME() | NOW() | +------------+-----------+---------------------+ | 2022-08-30 | 11:45:47 | 2022-08-30 11:45:47 | +------------+-----------+---------------------+ mysql> SELECT UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP(); +------------+------------+---------------------+ | UTC_DATE() | UTC_TIME() | UTC_TIMESTAMP() | +------------+------------+---------------------+ | 2022-08-30 | 07:46:10 | 2022-08-30 07:46:10 | +------------+------------+---------------------+ mysql> SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(); +----------------+----------------+---------------------+ | CURRENT_DATE() | CURRENT_TIME() | CURRENT_TIMESTAMP() | +----------------+----------------+---------------------+ | 2022-08-30 | 11:46:31 | 2022-08-30 11:46:31 | +----------------+----------------+---------------------+
PGSql
select now(); select CURRENT_DATE; select CURRENT_TIME; select CURRENT_TIMESTAMP; select CURRENT_TIME(precision); select CURRENT_TIMESTAMP(precision); select LOCALTIME; select LOCALTIMESTAMP; select LOCALTIME(precision); select LOCALTIMESTAMP(precision); --示例 SELECT CURRENT_TIME; 结果:14:39:53.662522-05 SELECT CURRENT_DATE; 结果:2001-12-23 SELECT CURRENT_TIMESTAMP; 结果:2001-12-23 14:39:53.662522-05 SELECT CURRENT_TIMESTAMP(2); 结果:2001-12-23 14:39:53.66-05 SELECT LOCALTIMESTAMP; 结果:2001-12-23 14:39:53.662522