1.Select 查询所有列
SELECT * FROM mytable(表名)
2.Select 查询某些属性列(多列用逗号分隔开)的语法:
SELECT column(列名), another_column, …
FROM mytable(表名);
SELECT
查询的 WHERE
子句,WHERE
子句用来描述哪些行应该进入结果,具体就是通过条件限定这些行的属性满足某些具体条件
3.条件查询语法
SELECT column, another_column, …
FROM mytable
WHERE condition AND/OR another_condition AND/OR …
注:这里的 condition 都是描述属性列的,具体会在下面的表格体现。
AND
or OR
这两个关键字来组装多个条件(表示并且,或者)
Operator(关键字) | Condition(意思) | SQL Example(例子) |
=, !=, < <=, >, >= | Standard numerical operators 基础的 大于,等于等比较 | col_name != 4 |
BETWEEN … AND … (between...and...) | Number is within range of two values (inclusive) 在两个数之间 | col_name BETWEEN 1.5 AND 10.5 |
NOT BETWEEN … AND … (not between...and...) | Number is not within range of two values (inclusive) 不在两个数之间 | col_name NOT BETWEEN 1 AND 10 |
IN (…) | Number exists in a list 在一个列表 | col_name IN (2, 4, 6) |
NOT IN (…) | Number does not exist in a list 不在一个列表 | col_name NOT IN (1, 3, 5) |
如果属性是字符串, 我们会用到字符串相关的一些操作符号,其中 LIKE(模糊查询) 和 %(通配符)
Operator(操作符) | Condition(解释) | Example(例子) |
= | Case sensitive exact string comparison (notice the single equals)完全等于 | col_name = "abc" |
!= or <> | Case sensitive exact string inequality comparison 不等于 | col_name != "abcd" |
LIKE like | Case insensitive exact string comparison 没有用通配符等价于 = | col_name LIKE "ABC" |
NOT LIKE | Case insensitive exact string inequality comparison 没有用通配符等价于 != | col_name NOT LIKE "ABCD" |
% | Used anywhere in a string to match a sequence of zero or more characters (only with LIKE or NOT LIKE) 通配符,代表匹配0个以上的字符 | col_name LIKE "%AT%" (matches "AT", "ATTIC", "CAT" or even "BATS") "%AT%" 代表AT 前后可以有任意字符 |
_ | Used anywhere in a string to match a single character (only with LIKE or NOT LIKE) 和% 相似,代表1个字符 | col_name LIKE "AN_" (matches "AND", but not "AN") |
IN (…) | String exists in a list 在列表 | col_name IN ("A", "B", "C") |
NOT IN (…) | String does not exist in a list 不在列表 | col_name NOT IN ("D", "E", "F") |
4.用 DISTINCT
关键字来指定某个或某些属性列唯一返回。
选取出唯一的结果的语法
SELECT DISTINCT column, another_column, …
FROM mytable
WHERE condition(s);
5.结果排序
SELECT column, another_column, …
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC;
LIMIT num_limit OFFSET num_offset;
ORDER BY col_name
这句话的意思就是让结果按照 col_name 列的具体值做 ASC升序 或 DESC 降序,对数字来说就是升序 1,2,3,... 或降序 ... 3,2,1 . 对于文本列,升序和降序指的是按文本的字母序。
注:num_limit 和num_offset是数字
LIMIT
和 OFFSET
子句通常和 ORDER BY
语句一起使用,当我们对整个结果集排序之后,我们可以 LIMIT
来指定只返回多少行结果 ,用 OFFSET
来指定从哪一行开始返回。