expr ::= expr binary-op expr |
expr [NOT] like-op expr [ESCAPE expr] |
unary-op expr |
( expr ) |
column-name |
table-name . column-name |
database-name . table-name . column-name |
literal-value |
parameter |
function-name ( expr-list | * ) |
expr ISNULL |
expr NOTNULL |
expr [NOT] BETWEEN expr AND expr |
expr [NOT] IN ( value-list ) |
expr [NOT] IN ( select-statement ) |
expr [NOT] IN [database-name .] table-name |
[EXISTS] ( select-statement ) |
CASE [expr] ( WHEN expr THEN expr )+ [ELSE expr] END |
CAST ( expr AS type ) |
expr COLLATE collation-name
like-op ::= LIKE | GLOB | REGEXP | MATCH
[測試版本] 2.8.16, 包括vstory為7051, 和無vstory值者.
1. 用 = 和 <> 查詢: (後者連=0都查不到)
sqlite> select * from hmsong where vstory=7051 limit 5;
80000|脫掉|杜德偉|||1|||||||||||||7051|||C ||2
80001|１２３木頭人|BMG歌手|||3|||||||||||||7051|||C ||6
80002|１７歲的異想世界|丁文琪|||2|||||||||||||7051|||C ||8
80003|２０００愛我|吳宗憲|||1|||||||||||||7051|||C ||6
80004|２９９９年的聖誕節|光良|||1|||||||||||||7051|||C ||9
sqlite> select * from hmsong where vstory<>7051;
2. 用 notnull 和 isnull查詢:
sqlite> select * from hmsong where vstory notnull limit 5;
80000|脫掉|杜德偉|||1|||||||||||||7051|||C ||2
80001|１２３木頭人|BMG歌手|||3|||||||||||||7051|||C ||6
80002|１７歲的異想世界|丁文琪|||2|||||||||||||7051|||C ||8
80003|２０００愛我|吳宗憲|||1|||||||||||||7051|||C ||6
80004|２９９９年的聖誕節|光良|||1|||||||||||||7051|||C ||9
sqlite> select * from hmsong where vstory isnull limit 5;
700|長頭髮|張宇|||1||||||||||||||||T ||3
701|甘講你嘸知|黃乙玲|||2||||||||||||||||T ||5
702|一分情半分是假|謝雷|||1||||||||||||||||T ||7
703|大頭仔兄弟|高向鵬.傅振輝|||3||||||||||||||||T ||5
705|手下留情|孫淑媚|||2||||||||||||||||T ||4
參考網頁: http://www.sqlite.org/lang_expr.html
