PostgreSQL9.5.9学习篇布尔类型操作符select查询

前端之家收集整理的这篇文章主要介绍了PostgreSQL9.5.9学习篇布尔类型操作符select查询前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

介绍:

布尔类型的操作符:逻辑操作符和比较操作符

逻辑操作符:and,or,not

需要注意的是:false and null结果为false

比较运算符:is

is true

is false

is not true

is not false

is unknown

is not unknown

is null

is not null


登陆测试数据库创建测试测试表插入数据来演示:

1.登陆库testdb1:

[postgres@localhost ~]$ psql -Utestwjw -h 127.0.0.1 -d testdb1 -p 36985

Password for user testwjw: 输入密码:558996

psql.bin (9.5.9)

Type "help" for help.

2.创建表:

boolean的状态要么是true要么是false,如果是unknown,用NULL表示。

boolean在sql中可以用不带引号的TRUE和FALSE表示,也可以用更多的表示真假的带引号的字符表示,如'true','false','yes','no','1','0'等

testdb1=> create table t (id int,nan boolean,note text);

CREATE TABLE

3.查看库中所有的表:

testdb1=> \dt

List of relations

Schema | Name | Type | Owner

--------+-------+-------+---------

public | t | table | testwjw

public | tlb01 | table | testwjw


testdb1=>

4.t表中插入数据:

testdb1=> insert into t values(1,TRUE,'TRUE');

INSERT 0 1

testdb1=> insert into t values(2,FALSE,'FALSE');

INSERT 0 1

testdb1=> insert into t values(3,tRue,'tRue')

testdb1-> ;

INSERT 0 1

testdb1=> insert into t values(4,fAlse,'fAlse');

INSERT 0 1

给t表中插入空数值NULL:

testdb1=> insert into t values(11,null,'null');

INSERT 0 1

testdb1=> insert into t values(11,NULL,'NULL');

INSERT 0 1

5.查看表中的数值:

testdb1=> select * from t;

id | nan | note

----+-----+-------

1 | t | TRUE

2 | f | FALSE

3 | t | tRue

4 | f | fAlse

11 | | null

11 | | NULL

(6 rows)

select * from t where nan='t';

id | nan | note

----+-----+------

1 | t | TRUE

3 | t | tRue

(2 rows)

testdb1=> select * from t where nan; 特殊的查询方式:

id | nan | note

----+-----+------

1 | t | TRUE

3 | t | tRue

(2 rows)

testdb1=>

testdb1=> select * from t where nan<>'t';

id | nan | note

----+-----+-------

2 | f | FALSE

4 | f | fAlse

(2 rows)

testdb1=>

testdb1=> select * from t where not nan;

id | nan | note

----+-----+-------

2 | f | FALSE

4 | f | fAlse

(2 rows)

testdb1=>

testdb1=> select * from t where nan or not nan;

id | nan | note

----+-----+-------

1 | t | TRUE

2 | f | FALSE

3 | t | tRue

4 | f | fAlse

(4 rows)

testdb1=> select * from t where nan and not nan;

id | nan | note

----+-----+------

(0 rows)


testdb1=> select * from t where nan is null;

id | nan | note

----+-----+------

11 | | null

11 | | NULL

(2 rows)


testdb1=> select * from t where nan is unknown; ####神奇

id | nan | note

----+-----+------

11 | | null

11 | | NULL

(2 rows)

testdb1=>

testdb1=> select * from t where nan is not null;

id | nan | note

----+-----+-------

1 | t | TRUE

2 | f | FALSE

3 | t | tRue

4 | f | fAlse

(4 rows)

testdb1=>

插入数值:

testdb1=> insert into t values(7,'t','''t''');

INSERT 0 1

testdb1=> insert into t values(8,'f','''f''');

INSERT 0 1

testdb1=> insert into t values(9,'''yes''');

INSERT 0 1

testdb1=> insert into t values(10,'0','''0''');

INSERT 0 1

testdb1=> select * from t;

id | nan | note

----+-----+-------

1 | t | TRUE

2 | f | FALSE

3 | t | tRue

4 | f | fAlse

11 | | null

11 | | NULL

7 | t | 't'

8 | f | 'f'

9 | t | 'yes'

10 | f | '0'

(10 rows)


testdb1=> select * from t where nan is not true ;

id | nan | note

----+-----+-------

2 | f | FALSE

4 | f | fAlse

11 | | null

11 | | NULL

8 | f | 'f'

10 | f | '0'

(6 rows)


testdb1=> select * from t where nan is true ;

id | nan | note

----+-----+-------

1 | t | TRUE

3 | t | tRue

7 | t | 't'

9 | t | 'yes'

(4 rows)


testdb1=> select * from t where nan is not false ;

id | nan | note

----+-----+-------

1 | t | TRUE

3 | t | tRue

11 | | null

11 | | NULL

7 | t | 't'

9 | t | 'yes'

(6 rows)

猜你在找的Postgre SQL相关文章