单条件查询

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)