无锡网站建设推广,大专计算机网络技术主要学什么,深圳龙华区房价,网站建设公司做网站需要注意什么PostgreSQL为内置的数据类型提供了大量的函数和运算符#xff0c;用户也可以定义自己的函数和运算符#xff0c;使用psql命令\df和\do可以列出所有可用的函数和运算符
1. 逻辑运算符
常用的逻辑运算符有AND、OR、NOT#xff0c;逻辑系统有三个值true、fase和null#xff…PostgreSQL为内置的数据类型提供了大量的函数和运算符用户也可以定义自己的函数和运算符使用psql命令\df和\do可以列出所有可用的函数和运算符
1. 逻辑运算符
常用的逻辑运算符有AND、OR、NOT逻辑系统有三个值true、fase和null运算关系如下表所示
aba AND ba OR bTRUETRUETRUETRUETRUEFALSEFALSETRUETRUENULLNULLTRUEFALSEFALSEFALSEFALSEFALSENULLFALSENULLNULLNULLNULLNULL
aNOT aTRUEFALSEFALSETRUENULLNULL
2. 比较函数和运算符
2.1. 运算符
常用的比较运算符如下表所示
运算符描述小于大于小于等于大于等于等于不等于!不等于
2.2. 比较谓词
另外还有一些用于比较运算的谓词如下所示
谓词说明示例BETWEEN AND包括范围端点2 BETWEEN 1 AND 3 -- t2 BETWEEN 3 AND 1 -- fNOT BETWEEN AND2 NOT BETWEEN 1 AND 3 -- fBETWEEN SYMMETRIC AND两个端点值排序后比较2 BETWEEN SYMMETRIC 3 AND 1 -- tNOT BETWEEN SYMMETRIC AND2 NOT BETWEEN SYMMETRIC 3 AND 1 -- fIS DISTINCT FROM不相等将null视为可比值1 IS DISTINCT FROM NULL -- tNULL IS DISTINCT FROM NULL -- fIS NOT DISTINCT FROM相等将null视为可比值1 IS NOT DISTINCT FROM NULL -- tNULL IS NOT DISTINCT FROM NULL -- fIS NULL1.5 IS NULL -- fIS NOT NULL‘null’ IS NOT NULL -- tISNULL非标准语法NOTNULLIS TRUEtrue IS TRUE -- tNULL::boolean IS TRUE -- fIS NOT TRUEtrue IS NOT TRUE -- fNULL::boolean IS NOT TRUE -- tIS FALSEtrue IS FALSE -- fNULL::boolean IS FALSE -- fIS NOT FALSEtrue IS NOT FALSE -- fNULL::boolean IS NOT FALSE -- tIS UNKNOWNtrue IS UNKNOWN -- fNULL::boolean IS UNKNOWN -- tIS NOT UNKNOWN布尔值是true或falsetrue IS NOT UNKNOWN -- tNULL::boolean IS NOT UNKNOWN -- f
2.3. 比较函数
常用的比较函数如小表所示
函数说明示例num_nonnulls(VARIADIC “any”)返回非null的个数num_nonnulls(1, NULL, 2) -2num_nulls(VARIADIC “any”)返回null的个数num_nulls(1, NULL, 2) - 1
3. 数学函数和运算符
3.1. 数学运算符
常用的数学运算符如下表所示
符号说明示例加号2 3 - 5正 3.5 - 3.5-减号2 - 3 - -1-负-(-4) - 4*乘2 * 3 - 6/除5.0 / 2 - 2.5%取模5 % 4 - 1^立方2^3 - 8|/平方根|/ 25.0 - 5||/立方根||/ 64.0 - 4绝对值 -5.0 - 5.0与91 15 - 11|或32 | 3 - 35#异或17 # 5 - 20~取反~1 - -2按位向左移1 4 - 16按位向右移8 2 - 2
3.2. 数学函数
常用数学函数如下表所示
函数名说明示例abs(n)取绝对值abs(-17.4) - 17.4cbrt(d)取立方根cbrt(64.0) - 4ceil(n)/ceil(d)向上取整ceil(42.2) - 43ceil(-42.8) - -42ceiling(n)/ceiling(d)向上取整ceiling(95.3) - 96degrees(d)将弧度转为度degrees(0.5) - 28.64788975654116div(y, x)y/x的整数商div(9, 4) - 2erf(d)erf(1.0) - 0.8427007929497149erfc(d)erfc(1.0) - 0.15729920705028513exp(n)/exp(d)指数exp(1.0) - 2.7182818284590452factorial(n)阶乘factorial(5) - 120floor(n)/floor(d)向下取整floor(42.8) - 42floor(-42.8) - -43gcd(n, n)最大公约数gcd(1071, 462) - 21lcm(n, n)最小公倍数lcm(1071, 462) - 23562ln(n)/ln(d)自然对数ln(2.0) - 0.6931471805599453log(n)/log(d)10为底的对数log(100) - 2log10(n)/log10(d)log10(1000) - 3log(b,x)以b为底的x对数log(2.0, 64.0) - 6.000min_scale(n)最小刻度min_scale(8.4100) - 2mod(y, x)y/x的余数mod(9, 4) - 1pi()πpi() - 3.141592653589793power(a, b)a的b次方power(9, 3) - 729radians(d)将度数转为弧度radians(45.0) - 0.7853981633974483round(n)/round(d)四舍五入round(42.4) - 42round(v, s)四舍五入保留s位小数round(42.4382, 2) - 42.44scale(n)/scale(d)获取小数位数scalse(8.4100) - 4sign(n)/sign(d)获取符号(-1, 0, 1)sign(-8.4) - -1sqrt(n)/sqrt(d)平方根sqrt(2) - 1.4142135623730951trim_scale(n)删除小数后的0trim_scale(8.4100) - 8.41trunc(n)/trunc(d)截断为整数trunc(42.8) - 42trunc(v, s)截断v到小数点后的s位trunc(42.4382, 2)width_bucketwidth_bucket(5.35, 0.024, 10.06, 5) - 3width_bucketwidth_bucket(now(), array[‘yesterday’, ‘today’, ‘tomorrow’]::timestamptz[]) - 2
3.3. 随机函数
常用随机函数如下表所示
函数名说明示例random()获取0.0到1.0之间的随机值random() - 0.897124072839091random_normal(mean, stddev)从给定数的正态分布中返回随机数mean默认0.0stddev默认1.0random_normal(0.0, 1.0) - 0.051285419setseed(d)给random()和random_normal()设置调用种子-1.0~1.0setseed(0.12345)
3.4. 三角函数
常用三角函数如下表所示
函数名说明示例acos(d)反余弦返回弧度acos(1) - 0acosd(d)反余弦返回度acosd(0.5) - 60asin(d)反正弦返回弧度asin(1) - 1.5707963267948966asind(d)反正弦返回度asind(0.5) - 30atan(d)反正切返回弧度atan(1) - 0.7853981633974483atand(d)反正切返回度atand(1) - 45atan2(y, x)y/x的反正切返回弧度atan2(1, 0) - 1.5707963267948966atan2d(y, x)y/x的反正切返回度atan2d(1, 0) - 90cos(d)余弦返回弧度cos(0) - 1cosd(d)余弦返回度cosd(60) - 0.5cot(d)余切返回弧度cot(0.5) - 1.830487721712452cotd(d)余切返回度cotd(45) - 1sin(d)正弦返回弧度sin(1) -sind(d)正弦返回度sind(30) - 0.5tan(d)正切返回弧度tan(1) -tand(d)正切返回度tand(45) - 1
3.5. 双曲线函数
常用双曲线函数如下表所示
函数名说明示例sinh(d)双曲正弦sinh(1) - 1.1752011936438014cosh(d)双曲余弦cosh(0) - 1tanh(d)双曲正切tanh(1) - 0.7615941559557649asinh(d)反双曲正弦asinh(1) - 0.881373587019543acosh(d)反双曲余弦acosh(1) - 0atanh(d)反双曲正切atanh(0.5) - .5493061443340548
4. 字符串函数和运算符
常用字符串函数如下表所示
名称说明示例||拼接btrim(s, c)移除指定字符cbtrim(‘xyxtrimyyx’, ‘xy’) - trimIS [NOT] [form] NORMALIZED检查字符串是否为指定的Unicode形式默认NFC还有NFD、NFKC、NFKDU ‘\0061\0308bc’ IS NFD NORMALIZED - tbit_length(text)返回字符串位数bit_length(‘jose’) - 32char_length(text)返回字符串字符数char_length(‘jose’) - 4lower(text)转小写lower(‘TOM’) - tomlpad(s, length, [fill text])填充指定字符lpad(‘hi’, 5, ‘xy’) - xyxhiltrim(str, [char])移除指定字符ltrim(‘zzzytest’, ‘xyz’) - testnormalize(t, [form])normalize(U ‘\0061\0308bc’, NFC) - U ‘\00E4bc’octet_length(t)octet_length(‘jose’) - 5octet_length(char)octet_length(abc ::character(4)) - 4overlay(str PLACING newStr FROM start [FOR count])从头start开始替换count个字符overlay(‘Txxxxas’ placing ‘hom’ from 2 from 4) - Thomasposition(substr IN str)position(’ om’ in ‘Thomas’ - 3)rpad(str, len, [fill])将fill拼接到str后面rpad(‘hi’, 5, ‘xy’) - hixyxrtrim(str, [char])移除含有char的字符rtrim(‘testxxzx’, ‘xyz’) - testsubstring(str [FROM start] [FOR count])从start开始截取count位字符substring(‘Thomas’ from 2 for 3) - homsubstring(str FROM pattern)substring(‘Thomas’ from ‘…$’) - massubstring(str SIMILAR pattern ESCAPE escape)substring(str FROM pattern FOR escape)substring(‘Thomas’ similar ‘%#“o_a#”_’ escape ‘#’) - omatrim([LEADINGTRAILINGBOTH] [char] FROM str)trim([LEADINGTRAILINGBOTH] [FROM] str, [char])upper(text)转大写upper(‘tom’) - TOMtext ^ text第一个字符以第二个字符开头时返回true‘alphabet’ ^ ‘alph’ - trueconcat(val1 “any” [, val2 “any” [, …]])连接不为null的字符concat(‘abcde’, 2, NULL, 22) - abcde222concat_ws(sep text, val1 “any” [, val2 “any” [, …]])用指定分隔符连接不为null的字符concat_ws(‘,’, ‘abcde’, 2, NULL, 22) - abcde, 2, 22format(formatstr text [, formatarg “any” [, …]])格式化字符串format(‘Hello %s, %1$s’, ‘World’) - Hello World, Worldinitcap(text)将单词第一个字母大写其他小写initcap(‘hi THOMAS’) - Hi Thomasleft(string text, n integer)返回前n个字符left(‘abcde’, 2) - ablength(text)返回字符长度length(‘jose’) - 4md5(text)返回md5加密后的值md5(‘abc’) - 900150983cd24fb0d6963f7d28e17f72pg_client_encoding()返回当前编码pg_client_encoding() - UTF8quote_literal(anyelement)将给定的值转换为文本quote_literal(42.5) - ‘42.5’quote_nullable(anyelement)将给定的值转换为文本如果为Null返回nullquote_nullable(42.5) - ‘42.5’repeat(string text, number integer)重复n次repeat(‘Pg’, 4) - PgPgPgreplace(string text, from text, to text)将from替换为toreplace(‘abcdefabcdef’, ‘cd’, XX) - abXXefabXXefreverse(text)反转reverse(‘abcde’) - edcbaright(string text, n integer)返回后n个字符right(‘abcde’, 2) - desplit_part(string text, delimiter text, n integer)split_part(‘abc~~def~~ghi’, ‘~~’, 2) - defstarts_with(string text, prefix text)判断是否已prefix开头starts_with(‘alphabet’, ‘alph’) - truesubstr(string text, start integer [, count integer])从start开始截取count位字符substr(‘alphabet’, 3) - phabetsubstr(‘alphabet’, 3, 2) - phto_hex(integer)将数字转换为十六进制数to_hex(2147483647) - 7fffffff
5. 格式化函数
常用的格式化函数如下表所示
函数描述示例to_char( timestamp, text)根据给定的格式将时间戳转换为字符串to_char(timestamp ‘2024-05-05 17:31:12.66’, ‘HH12:MI:SS’) - 05:31:12to_char(interval, text)根据给定的格式将间隔转换为字符串to_char(interval ‘15h 2m 12s’, ‘HH24:MI:SS’) - 15:02:12to_char(numeric, text)根据给定的格式将数字转换为字符串to_char(125, ‘999’) - 125to_char(125.8::real, ‘999D9’) - 125.8to_char(-125.8, ‘999D99S’) - 125.80-to_date(text, text)根据给定的格式将字符串转换为日期to_date(‘05 Dec 2023’, ‘DD Mon YYYY’) - 2023-12-05to_number(text, text)根据给定的格式将字符串转换为数字to_number(‘12454.8-’, ‘99G999D9S’) - -12454.8to_timestamp(text, text)根据给定的格式将字符串转换为时间戳to_timestamp(‘05 Dec 2023’, ‘DD Mod YYYY’) - 2023-12-05 00:00:00-05
用于数字格式的模板样式如下表所示
符号描述90.,PR括号中的负值S负号L货币符号D小数点G组分隔符MI小于0时减号PL大于0时加号SG加号/减号RN1~3999之间随机数TH or th序列号后缀VEEEE
6. 日期时间函数和运算符
6.1. 日期时间运算符
常用的日期时间运算符如下表所示
运算符描述示例date integer日期加天数date ‘2024-05-05’ 7 - 2024-05-12date interval日期加时间间隔date ‘2024-05-05’ interval ‘1 hour’ - 2024-05-05 01:00:00date time日期加时间date ‘2024-05-05’ time ‘03:00’ - 2024-05-05 03:00:00interval interval时间间隔相加interval ‘1 day’ interval ‘1 hour’ - 1 day 01:00:00timestamp interval时间戳加时间间隔timestamp ‘2024-05-05 01:00’ interval ‘23 hours’ - 2024-05-06 00:00:00time interval时间加时间间隔time ‘01:00’ interval ‘3 hours’ - 04:00:00- interval- interval ‘23 hours’ - -23:00:00date - date日期相隔天数date ‘2024-05-05’ - date ‘2024-05-02’ - 3date - integern天前date ‘2024-05-05’ - 3 - 2024-05-02date - interval日期减时间间隔date ‘2024-05-05’ - interval ‘1 hour’ - 2024-05-04 23:00:00time - time相隔多长时间time ‘05:00’ - time ‘03:00’ - 02:00:00time - interval时间减时间间隔time ‘05:00’ - interval ‘2 hours’ - 03:00:00timestamp - interval时间戳减时间间隔timestamp ‘2024-05-05 23:00’ - interval ‘23 hours’ - 2024-05-05 00:00:00interval - interval时间间隔相减interval ‘1 day’ - interval ‘1 hour’ - 1day -01:00:00interval * double precision时间间隔乘以小数interval ‘1 second’ * 900 - 00:15:00interval ‘1 day’ * 21 - 21 daysinterval ‘1 hour’ * 3.5 - 03:30:00interval / double precision时间间隔除以小数interval ‘1 hour’ / 1.5 - 00:40:00
6.2. 日期时间函数
常用的日期时间函数如下表所示
函数描述示例age(timestamp, timestamp)age(timestamp ‘2024-05-05’, timestamp ‘2010-03-15’) - 14 years 1 mon 21 daysage(timestamp)当前日期相减age(timestamp ‘2010-03-15’) - 14 years 1 mon 24 daysclock_timestamp()当前日期时间带时区clock_timestamp() - 2024-05-08 11:45:59.727 0800current_date当前日期current_date - 2024-05-05current_time当前时间current_time - 14:39:53.66252208current_time(integer)当前时间带时区14:39:53.6608current_timestamp当前日期时间带时区current_timestamp(0) - 2024-05-05 14:39:5308date_add(timestamp with time zone, interval [, text])日期相加date_add(‘2024-05-05 00:00:0008’ :: timestamptz, ‘1 day’ :: interval, ‘Asia/Shanghai’) - 2024-05-06 00:00:0008date_bin(interval, timestamp, timestamp)date_bin(‘15 minutes’, timestamp ‘2024-05-05 09:40:00’, timestamp ‘2024-05-05 09:05:00’) - 2024-05-05 09:35:00.000date_part(text, timestamp)获取小时数date_part(‘hour’, timestamp ‘2024-05-05 09:40:00’) - 09date_part(text, interval)date_part(‘month’, interval ‘2 years 3 months’) - 3date_subtract(timestamp with time zone, intervl [, text])日期相减date_subtract(‘2024-05-05 00:00:0008’::timestamptz, ‘1 day’::interval, ‘Asia/Shanghai’) -date_trunc(text, timestamp)只获取日期和小时date_trunc(‘hour’, timestamp ‘2024-05-05 09:40:00’) - 2024-05-05 09:00:00date_trunc(text, timestamp with time zone, text)获取指定时区的日期date_trunc(‘day’, timestamptz ‘2024-05-05 09:40:00’, ‘Asia/Shanghai’) -date_trunc(text, interval)date_trunc(‘hour’, interval ‘2 days 3 hours 40 minutes’) - 2 days 03:00:00localtime获取当前时间localtime - 09:40:53.662522localtime(integer)获取当前时间localtime(0) - 09:40:53localtimestamp获取当前日期时间localtimestamp - 2024-05-05 09:40:00.662522localtimestamp(integer)获取当前日期时间localtimestamp(2) - 2024-05-05 09:40:00.66make_date(year int, month int, day int)make_date(2024, 05, 05) - 2024-05-05make_interval([years int [, months int [, weeks int [, days int [, hours int [, mins int [, secs double precision ]]]]]]])make_interval(days 10) - 10 daysmake_time(hour int, min int, sec double precision)make_time(8, 15, 23.5) - 08:15:23.5make_timestamp(year int, month int, day int, hour int, min int, sec double precision)make_timestamp(2024, 05, 05, 8, 15, 23.5) - 2024-05-05 08:15:23.5make_timestamptz(year int, month int, day int, hour int, min int, sec double precision [, timezone text])make_timestamptz(2024, 5, 5, 8, 15, 23.5) - 2024-05-05 08:15:23.508make_timestamptz(2024, 5, 5, 8, 15, 23.5, ‘’) - 2024-05-05 08:15:23.508now()获取当前日期时间now() - 2024-05-05 09:40:00.66252208statement_timestamp()当前日期时间statement_timestamp() - 2024-05-05 14:04:32.735 0800timeofday()当前日期时间timeofday() -transaction_timestamp()当前日期时间transaction_timestamp() -to_timestamp(double precision)转换时间戳to_timestamp(23343) -
7. 开窗函数
常用的开窗函数如下表所示
函数描述row_number()返回当前行在其分区内的编号从1开始rank()返回当前行的列组dense_rank()返回当前行的排名percent_rank返回当前行的相对排名即排名-1/总分区行数-1cume_dist()返回累积分布即当前行前面或与当前行对等的分区行数/分区行总数ntile(num integer)返回一个从1到参数值的整数lag(value anycompatible [, offset integer [, default anycompatible]])lead(value anycompatible [, offset integer [, default anycompatible]])first_value(value anyelement)返回在窗口框架的第一行计算的值last_value(value anyelement)返回在窗口框架的最后行计算的值nth_value(value anyelement, n integer)返回在窗口框架的第n行计算的值
