where 條件使用
更新時間 2025-02-14 10:21:51
最近更新時間: 2025-02-14 10:21:51
分享文章
本文為您介紹如何在使用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)
