LIKE ANY(ARRAY[])如果需要相似任意一个参数, 需要使用这个语法 NOT LIKE ALL(ARRAY[])如果想达到不相似任意一个参数, 需要用这个语法 ILIKE ILIKE是不区分大小写的LIKE ILIKE NOT ILIKE ILIKE ANY(ARRAY[]) NOT ILIKE ALL(ARRAY[])
& array['a', 'd'] select '{"a":1, "b":2, "c":3}'::jsonb ?& array['a', 'b'] 应用: # 模糊查询,extra_info ->> 'blacklistName'表示从json中取出key为blacklistName的元素 SELECT * FROM alarm WHERE is_deleted = 0 AND extra_info ->> 'blacklistName' like '%张三%' # 另...
--查询emp表中员工姓名第三个字符是A的员工信息 select * from emp where ename like '__A%'; like中的转义字符:\ select * from emp where ename like '%\_%' escape '\';把\后紧跟的字符转换成普通字符_,不然_的意思在这代表只能一个任意字符 all、any:后面都跟一个集合或者子查询 --查询工资比...
PostgreSQL中提供了三种实现模式匹配的方法:SQL LIKE操作符,更近一些的SIMILAR TO操作符,和POSIX-风格正则表达式。 1. LIKE: string LIKE pattern [ ESCAPE escape-character ] string NOT LIKE pattern [ ESCAPE escape-character ] 每个pattern定义一个字串的集合。如果该string包含在pattern代表的字串集合里,那么LI...
'abc'LIKE'abc'true'abc'LIKE'a%'true'abc'LIKE'_b_'true'abc'LIKE'c'false LIKE模式匹配总是覆盖整个串。因此,要匹配在串内任何位置的序列,该模式必须以百分号开头和结尾。 要匹配文本的下划线或者百分号,而不是匹配其它字符, 在pattern里相应的字符必须 前导逃逸字符。缺省的逃逸字符是反斜线,但是你可以用...
expression operator ANY (array expression) expression operator SOME (array expression) all(array)表达式 expression operator ALL (array expression) 行与行构造器的比较操作表达式 row_constructor operator row_constructor operator is =, <>, <, <=, > or >= row_constructor IS DISTINCT FROM row_const...
integer||notnull|salary|numeric(8,2)||notnull|bonus|numeric(8,2)|||email|charactervarying(100)||notnull|notes|text||notnull|Checkconstraints:"ck_emp_salary"CHECK(salary>0::numeric)"ck_emp_sex"CHECK(sex::text=ANY(ARRAY['男'::charactervarying,'女'::charactervarying]::text[]))Inherits:...
PostgreSQL: SELECT t.dept_id FROM sys_dept t WHERE ‘100’ = ANY (string_to_array(ancestors, ‘,’)) 5、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),‘,’...
concat(text, anyarray)/concat(anyarray, text)/concat(anyarray, anyarray) 用于拼接任意类型的数据。 SELECT concat('adb4pg', 6666); 返回信息如下: concat --- adb4pg6666 (1 row) SELECT concat(6666, 6666); 返回信息如下: concat --- 66666666 (1 row) SELECT concat(current_date, 6666...
Control flow can be contingent on the outcome of either true or false, like when using the PostgreSQL CASE expression, with various actions resulting from the evaluation. Character This data type consists of strings of characters such as letters or numbers, and it’s used to store text ...