postgresql中常用小语法
postgresql中常用小语法1. PG中 类型转换有时候在postgresql中需要对值的类型进行转换，pg中提供的方法example : select '33':: integerexample2: select case('33' as integer)2. pg中的行号 （类似于oracle中的 rownum）example : select row_number() over() , * from XXXX3. pg 中查询中的列转数组example : select array_agg(AAAAA) FROM XXXXX4. pg 中字符串函数 ：函数：string || string说明：String concatenation 字符串连接操作例子：'Post' || 'greSQL' ＝ PostgreSQL函数：string || non-string or non-string || string说明：String concatenation with one non-string input 字符串与非字符串类型进行连接操作例子：'Value: ' || 42 = Value: 42函数：bit_length(string)说明：Number of bits in string 计算字符串的位数例子：bit_length('jose') = 32函数：char_length(string) or character_length(string)说明：Number of characters in string 计算字符串中字符个数例子：char_length('jose') = 4函数：lower(string)说明：Convert string to lower case 转换字符串为小写例子：bit_length('jose') = 32函数：octet_length(string)说明：Number of bytes in string 计算字符串的字节数例子：octet_length('jose') = 4函数：overlay(string placing string from int [for int])说明：Replace substring 替换字符串中任意长度的子字串为新字符串例子：overlay('Txxxxas' placing 'hom' from 2 for 4) = 4函数：position(substring in string)说明：Location of specified substring 子串在一字符串中的位置例子：position('om' in 'Thomas') = 3函数：substring(string [from int] [for int])说明：Extract substring 截取任意长度的子字符串例子：substring('Thomas' from 2 for 3) = hom函数：substring(string from pattern)说明：Extract substring matching POSIX regular expression. See Section 9.7 for more information on pattern matching. 利用正则表达式对一字符串进行任意长度的字串的截取例子：substring('Thomas' from '...$') = mas函数：substring(string from pattern for escape)说明：Extract substring matching SQL regular expression. See Section 9.7 for more information on pattern matching. 利于正则表达式对某类字符进行删除，以得到子字符串例子：trim(both 'x' from 'xTomxx') = Tom函数：trim([leading | trailing | both] [characters] from string)说明：Remove the longest string containing only the characters (a space by default) from the start/end/both ends of the string 去除尽可能长开始，结束或者两边的某类字符，默认为去除空白字符，当然可以自己指定，可同时指定多个要删除的字符串例子：trim(both 'x' from 'xTomxx') = Tom函数：upper(string)说明：Convert string to uppercase 将字符串转换为大写例子：upper('tom') = TOM函数：ascii(string)说明：ASCII code of the first character of the argument. For UTF8 returns the Unicode code point of the character. For other multibyte encodings. the argument must be a strictly ASCII character. 得到某一个字符的Assii值例子：ascii('x') = 120函数：btrim(string text [, characters text])说明：Remove the longest string consisting only of characters in characters (a space by default) from the start and end of string 去除字符串两边的所有指定的字符，可同时指定多个字符例子：btrim('xyxtrimyyx', 'xy') = trim函数：chr(int)说明：Character with the given code. For UTF8 the argument is treated as a Unicode code point. For other multibyte encodings the argument must designate a strictly ASCII character. The NULL (0) character is not allowed because text data types cannot store such bytes. 得到某ACSII值对应的字符例子：chr(65) = A函数：convert(string bytea, src_encoding name, dest_encoding name)说明：Convert string to dest_encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. Conversions can be defined by CREATE CONVERSION. Also there are some predefined conversions. See Table 9-7 for available conversions. 转换字符串编码，指定源编码与目标编码例子：convert('text_in_utf8', 'UTF8', 'LATIN1') = text_in_utf8 represented in ISO 8859-1 encoding函数：convert_from(string bytea, src_encoding name)说明：Convert string to the database encoding. The original encoding is specified by src_encoding. The string must be valid in this encoding. 转换字符串编码，自己要指定源编码,目标编码默认为数据库指定编码，例子：convert_from('text_in_utf8', 'UTF8') = text_in_utf8 represented in the current database encoding函数：convert_to(string text, dest_encoding name)说明：Convert string to dest_encoding.转换字符串编码，源编码默认为数据库指定编码，自己要指定目标编码,例子：convert_to('some text', 'UTF8') = some text represented in the UTF8 encoding函数：decode(string text, type text)说明：Decode binary data from string previously encoded with encode. Parameter type is same as in encode. 对字符串按指定的类型进行解码例子：decode('MTIzAAE=', 'base64') = 123\000\001函数：encode(data bytea, type text)说明：Encode binary data to different representation. Supported types are: base64, hex, escape. Escape merely outputs null bytes as \000 and doubles backslashes. 与decode相反，对字符串按指定类型进行编码例子：encode(E'123\\000\\001', 'base64') = MTIzAAE=函数：initcap(string)说明：Convert the first letter of each word to uppercase and the rest to lowercase. Words are sequences of alphanumeric characters separated by non-alphanumeric characters. 将字符串所有的单词进行格式化，首字母大写，其它为小写例子：initcap('hi THOMAS') = Hi Thomas函数：length(string)说明：Number of characters in string 讲算字符串长度例子：length('jose') = 4函数：length(stringbytea, encoding name )说明：Number of characters in string in the given encoding. The string must be valid in this encoding. 计算字符串长度，指定字符串使用的编码例子：length('jose', 'UTF8') = 4函数：lpad(string text, length int [, fill text])说明：Fill up the string to length length by prepending the characters fill (a space by default). If the string is already longer than length then it is truncated (on the right). 对字符串左边进行某类字符自动填充，即不足某一长度，则在左边自动补上指定的字符串，直至达到指定长度，可同时指定多个自动填充的字符例子：lpad('hi', 5, 'xy') = xyxhi上一个：Redis简单使用
下一个：mongodb将查询结果导出到文件
- 更多SQLServer疑问解答：
- 配置MSSQL复制指定快照文件夹提示：不是有效的路径或文件名
- 详细解读varchar和Nvarchar区别
- SQL SERVER 2005 同步复制技术
- 进程未能大容量复制到表 解决方法
- MSSql实例教程:MSSql数据库同步
- SQLServer2000同步复制技术实现（分发和订阅）
- sqlserver
- SQLSERVER
- 测试 sqlserver 最大用户数连接
- 写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID作为主键,注意：ID可能不是连续的
- sqlserver2008的安装问题。
- 为什么安装的SQL server 2008我的没有MSSQLSERVER协议啊？
- 怎样把exel表里的数据复制到sqlserver表里
- sqlserver数据库主键和外键问题
- sqlserver 中的左表连接查询和右表连接查询有啥不同？有什么用？
