初識psql(五)

初識psql (五) 之 where 字句

where

子句的文法：

where search_condition
           

從定義上看我們知道，where後面跟的是一個條件搜尋語句，即它的最後得到的傳回值是布爾類型，

boolean

隻有兩種可能

true

或

false

,就是這個條件搜尋語句執行之後，

true

就傳回我們前面

select

後面的列；

false

就是沒有得到我們想要的資料

我們還是先定義一張表

student

drop table if exists students;

create table if not exists students(
id integer not null,
name varchar(30) unique not null,
salary money not null,
create_at integer  
);

//建立自增主鍵
CREATE SEQUENCE students_id_seq
    INCREMENT 1
    START 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
alter table students alter column id set default nextval('students_id_seq');

insert into students (name,saraly,create_at) values ('liyi',9996.32,1545837392),
('admin',12365.35,1545838392),
('root',2568.365,1545839392),
('test',1236548.25,1545537392);
           

注意=>psql主鍵的建立文法：

CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

-- 測試Sequences函數:
select nextval('students_id_seq');
           

• where ... =

  查詢等于

select * from students where name = 'liyi';
           

• where ... !=

  查詢不等于

select * from students where salary != 9996.32;
           

• where ... >|>=

  查詢大于或大于等于

select * from students where salary > 1000000;
select * from students where salary >= 12365.35;
           

• where ... <|<=

  查詢小于等于

select * from students where salary < 10000;
select * from students where salary <= 12365.35;
           

• where...in|not in

  查詢在 | 不在【…】某一區間

select * from students where name in ('admin','root');
select * from students where name not in ('admin','root');
           

• where ... between|not between

  查詢在 | 不在【…】之間

select * from students where create_at between 1545537390 and 1545838392;
select * from students where create_at not between 1545537390 and 1545838392;
           

• where ... like

  模糊查詢

select * from students where name likke '_iyi';
select * from students where name like '_iy_';
select * from students where name like '%yi';
select * from students where name like '%y%';
           

• where ...in

  後面我們接上select子句也是可以的哦；

我們之前建立過

users

表，這次我們也來使用一下

select * from students where name in (select username from users);
           

以上sql語句全不可以正常運作

學海無涯，跪在堅持，加油