where 条件使用
更新时间 2025-02-05 09:36:28
最近更新时间: 2025-02-05 09:36:28
本文为您介绍如何在使用SELECT语法时添加where条件。
单条件查询
teledb=# select * from teledb_pg where id =1;
id | nickname
----+------------
1 | teledb
1 | hello,pgxc
(2 rows)
多条件and
teledb=# select * from teledb_pg where id =1 and nickname like '%h%' ;
id | nickname
----+------------
1 | hello,pgxc
(1 row)
多条件or
teledb=# select * from teledb_pg where id =1 or nickname like '%p%' ;
id | nickname
----+------------
1 | teledb
1 | hello,pgxc
3 | pg
(3 rows)
ilike 不区分大小写匹配
teledb=# insert into teledb_pg values(2,'TELEDB');
INSERT 0 1
teledb=# select * from teledb_pg where nickname ilike '%te%';
id | nickname
----+----------
1 | teledb
2 | TELEDB
(2 rows)
where 条件也能支持子查询
teledb=# select * from teledb_pg where id=(select (random())::integer from teledb_pg order by random() limit 1);
id | nickname
----+----------
(0 rows)
teledb=# select * from teledb_pg where id=(select (random())::integer from teledb_pg order by random() limit 1);
id | nickname
----+------------
1 | teledb
1 | hello,pgxc
(2 rows)
null 值查询方法
teledb=# select * from teledb_pg where nickname is null;
id | nickname
----+----------
4 |
(1 row)
teledb=# select * from teledb_pg where nickname is not null;
id | nickname
----+------------
1 | teledb
3 | pg
1 | hello,pgxc
2 | TELEDB
(4 rows)
exists 只要有记录返回就为真
teledb=# select * from teledb_pg where exists(select * from person where person.id = teledb_pg.id);
id | nickname
----+------------
1 | teledb
1 | hello,pgxc
2 | TELEDB
3 | pg
(4 rows)
exists 等价写法
teledb=# select teledb_pg.* from teledb_pg, (select distinct id from person) as t where t.id = teledb_pg.id;
id | nickname
----+------------
1 | teledb
1 | hello,pgxc
2 | TELEDB
3 | pg
(4 rows)
