pgSQL笔记03-函数和索引

【PostgreSQL函数】数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数

《一、数学函数》

6.2.1-绝对值函数ABS(x) 和 圆周率函数PI()

select ABS(2),ABS(-3.3),pi();

6.2.2-平方根函数SQRT(x) 和 求余函数MOD(x,y)

select sqrt(9),sqrt(40),mod(31,8),mod(45.5,6);

6.2.3-获取整数的函数 CEIL(x)、CEILING(x)、FLOOR(x)

select ceil(-3.35),ceiling (3.35),floor(-3.35),floor(3.35);

6.2.4-四舍五入函数 ROUND(x) 和 ROUND(x,y)

select round(-1.14),round(-1.67),round(1.14),round(1.66),round(1.38,1),round(1.38,0),round(232.38,-1),round(232.38,-2);

6.2.5-符号函数 SIGN(x)

select sign(-21),sign(0),sign(21);

6.2.6-幂运算函数 POW(x,y) 、 POWER(x,y) 、 EXP(x)

select pow(2,2),power(2,2),pow(2,-2),power(2,-2),exp(3),exp(-3),exp(0);

6.2.7-对数运算函数 LOG(x)

select log(3);

6.2.8-角度与弧度相互转换的函数 RADIANS(x) 和 DEGREES(x)

select radians(90),radians(180),degrees(pi()),degrees(pi()/2);

6.2.9-正弦函数 SIN(x) 和反正弦函数 ASIN(x)

select sin(1),round(sin(pi())),asin(0.841470984807897);

select asin(3); /*会报错*/

6.2.10-余弦函数 COS(x) 和 反余弦函数 ACOS(x)

select cos(0),cos(pi()),cos(1),acos(1),acos(0),acos(0.54030230586814);

6.2.11-正切函数 TAN(x)、反正切函数 ATAN(x)、余切 函数 COT(x)

select tan(0.3),round(tan(pi()/4)),atan(0.309336249609623),atan(1),cot(0.3),1/tan(0.3),cot(pi()/4);

《二、字符串函数》

6.3.1-计算字符串字符数 char_lenth(str) 和字符串长度 length(str)

select char_length('date1'),char_length('汉字'),length('date1'),length('汉字');

（为毛我测试的汉字的length也是2？不解！！）

6.3.2-合并字符串函数 concat(s1,s2,...) 和 concat_ws(x,s1,s2,...)

select concat('PostgreSQL','9.15'),concat('Postgre',NULL,'SQL');

select concat_ws('-','1st','2nd','3rd'),concat_ws('*','1st',NULL,'3rd');

6.3.3-获取指定长度的字符串 left(s,n) 和 right(s,n)

s

select left('football',5),right('football',4);

6.3.4-填充字符串的函数 lpad(s1,len,s2) 和 rpad(s1,len,s2)

select lpad('hello',4,'?'),lpad('hello',10,'?'),rpad('hello',4,'?'),rpad('hello',10,'?');

6.3.5-删除空格的函数 ltrim(s) 、 rtrim(s) 和 trim(s)

select ltrim(' book '),rtrim(' book '),trim(' book ');

 

6.3.6-删除指定字符串的函数 trim(s1 FROM s)

select trim('xy' from 'xyboxyokxyxy');

6.3.7-重复生成字符串的函数 repeat(s,n)

select repeat('cd-',3),repeat('cd-',-2),repeat('cd-',NULL);

6.3.8-字符串替换函数 replace(s,s1,s2)

select replace('xxx.baidu.com','x','w');

6.3.9-获取子串的函数 substring(s,n,len)

select substring('breakfast',5),substring('breakfast',5,3),substring('breakfast',-3);

6.3.10-匹配子串开始位置的函数 position(str1 IN str)

select position('ball' in 'football');

6.3.11-字符串反转的函数 reverse(s)

select reverse('abcde');

 

《三、日期和时间函数》

6.4.1-获取当前日期current_date和获取当前时间current_time、localtime

select current_date,current_time,localtime;

6.4.2-获取当前日期和时间的函数current_timestamp、localtimestamp和now()

select current_timestamp,localtimestamp,now();

6.4.3-获取日期指定值extract(type FROM d)

/*提取年份、月份、日期*/

select extract(year from timestamp '2015-12-20 10:23:49'),extract(month from now()),extract(day from now());

/*一年中的第几天、一周中的星期几(0-6)、一年中的第几季度(1-4)*/

select extract(doy from now()),extract(dow from now()),extract(quarter from now());

6.4.4-日期和时间的运算操作（加减乘除）

select date '2012-09-28' + integer '10';

select date '2012-09-28' + interval '3 hour';

select date '2012-09-28' + time '06:00';

select timestamp '2012-09-28 02:00:00' + interval '10 hours';

select date '2012-11-01' - date '2012-09-10';

select date '2012-09-28' - integer '10';

select 15 * interval '2 day';

select 50 * interval '2 second';

select interval '1 hour' / integer '2';

 

《四、条件判断函数》

又叫：控制流程函数。CASE...WHEN...THEN...END

select case 2 when 1 then 'one' when 2 then 'two' else 'more' end;

select case when 1<0 then '正确' else '错误' end;

 

《五、系统信息函数》

6.6.1-获取PostgreSQL版本号 VERSION()

select version();

6.6.2-获取用户名 USER和CURRENT_USER

select user,current_user;

 

《六、加密函数》

6.7.1-加密函数 MD5(str)

select md5('mypwd');

6.7.2-加密函数 ENCODE(str,pswd_str)

select encode('secret','hex'),length(encode('secret','hex'));

6.7.3-解密函数 DECODE(crypt_str,pswd_str)

select decode('736563726574','hex');

 

《七、改变数据类型的函数CAST》

CAST(x AS type)

select cast(100 as char(2));

 

【PostgreSQL索引】

1. 索引简介

分类：B-tree（常用）、Hash（性能较弱，不建议使用）、GiST、GIN

2.创建索引 CREATE INDEX

create table book(

bookid int not null,

bookname varchar(255) not null,

authors varchar(255) not null,

info varchar(255) null,

comment varchar(255) null,

year_publication date not null

);

 

（1）创建普通索引 create index

/*创建普通索引*/

CREATE INDEX bknameidx ON book(bookname);

（2）创建唯一索引 create unique index

/*创建唯一索引*/

CREATE UNIQUE INDEX uniqididx ON book (bookid);

（3）创建单列索引

/*创建单列索引*/

CREATE INDEX bkcmtidx ON book (comment);

（4）创建组合索引

/*创建组合索引*/

CREATE INDEX bkauandinfoidx ON book (authors,info);

 

2.重命名索引 ALTER INDEX ... RENAME TO ...

/*重命名索引*/

ALTER INDEX bkauandinfoidx RENAME TO abcd;

 

3.删除索引 DROP INDEX

/*删除索引*/

DROP INDEX abcd;