【我和openGauss的故事】openGauss易知易会的几个实用特性

使用openGauss已经有很长一段时间了，本文将介绍几个简单易用的数据库特性。

create table users(id int,name text,email text,deleted_at timestamp,delete boolean);
insert into users values(1,'jerome','chong.peng@enmotech.com',null,false);
insert into users values(2,'sara','lynn.wood@poland.com','2001-09-11',true);
insert into users values(3,'dolores','th000@sky.com',null,false);
insert into users values(4,'evan','rachel.moore@hollywood.com',null,false);

通常我们使用如下语句进行查询

openGauss#  select * from users;
 id |  name   |           email            |     deleted_at      | delete 
----+---------+----------------------------+---------------------+--------
  1 | jerome  | chong.peng@enmotech.com    |                     | f
  2 | sara    | lynn.wood@poland.com       | 2001-09-11 00:00:00 | t
  3 | dolores | th000@sky.com              |                     | f
  4 | evan    | rachel.moore@hollywood.com |                     | f
(4 rows)

也可以使用下面的语句进行查询，尤其是列较多时

openGauss# select users from users;
                         users                         
-------------------------------------------------------
 (1,jerome,chong.peng@enmotech.com,,f)
 (2,sara,lynn.wood@poland.com,"2001-09-11 00:00:00",t)
 (3,dolores,th000@sky.com,,f)
 (4,evan,rachel.moore@hollywood.com,,f)
(4 rows)

上面是将所有列作为行类型返回单列，可以比较简洁的返回数据。

假设我们有以下查询：

select id, name, email
from users
where name = 'dolores'
  and email = 'th000@sky.com';

select id, name, email
from users
where 1=1
  and name = 'dolores'
  and email = 'th000@sky.com';

应用层需要比较方便进行where条件拼接。

select id, name, email
from users
where (name, email) = ('dolores','th000@sky.com');

 id |  name   |     email     
----+---------+---------------
  3 | dolores | th000@sky.com
(1 row)

select id, name, email
from users
where deleted_at is null
and (
     (name = 'dolores' and email = 'th000@sky.com')
     or
     (name = 'evan' and email = 'rachel.moore@hollywood.com')
);

select id, name, email
from users
where deleted_at is null
 and (name, email) 
 in (('dolores','th000@sky.com'),('evan','rachel.moore@hollywood.com'));

select id, name, email
from users
where delete =  false;

select id, name, email
from users
where not delete;

 id |  name   |           email            
----+---------+----------------------------
  1 | jerome  | chong.peng@enmotech.com
  3 | dolores | th000@sky.com
  4 | evan    | rachel.moore@hollywood.com
(3 rows)

select ...
  from  t1 
  join  t2
    on  t1.id = t2.id;

select ...
  from  t1 
  join  t2
 using  (id);

on t1.a = t2.a and t1.b = t2.b

using (a,b);

create domain my_addr varchar(100) not null default 'n/a';

create domain binary_float as float;