PostgreSQL9.5.9 learning the Boolean type operator select query

Introduced:

Operators of Boolean types: logical operators and comparison operators

Logical operator: And,or,not

Note that: false and null result is False

Comparison operator: is

is True

is False

is not true

is not false

is unknown

is not unknown

Is null

is NOT NULL

Log in to test database Create test test table Insert data to demonstrate:

1. Login Library TESTDB1:

[Email protected] ~]$ psql-utestwjw-h 127.0.0.1-d testdb1-p 36985

Password for user testwjw: Enter password: 558996

Psql.bin (9.5.9)

Type ' help ' for help.

2. Create a table:

The Boolean state is either True or False if it is unknown, denoted by null.

Boolean can be represented by true and false without quotation marks in SQL, or with more quoted characters representing True and false, such as ' true ', ' false ', ' yes ', ' no ', ' 1 ', ' 0 ', etc.

Testdb1=> CREATE TABLE t (id int, Nan boolean, note text);

CREATE TABLE

3. View all the tables in the library:

Testdb1=> \dt

List of relations

Schema | Name |  Type | Owner

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

Public | T | Table | Testwjw

Public | TLB01 | Table | Testwjw

Testdb1=>

Inserting data into the 4.t table:

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

To insert an empty value into the T table null:

Testdb1=> INSERT into t values (11,null, ' null ');

INSERT 0 1

Testdb1=> INSERT into t values (11,null, ' NULL ');

INSERT 0 1

5. View the values in the table:

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; Special Query method:

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 is 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 the nan is null;

ID | Nan | Note

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

11 | | Null

11 | | Null

(2 rows)

Testdb1=> select * from T where the 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=>

Insert Value:

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 ', ' ' 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 '

(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 the 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)

This article is from the "10931853" blog, please be sure to keep this source http://wujianwei.blog.51cto.com/10931853/1970348

PostgreSQL9.5.9 learning the Boolean type operator select query