本文将详细介绍 SQL 中的四种子句 WHERE / ORDER BY / GROUP BY / HAVING,以及基本运算 / 特殊条件表达式 / 函数 / 聚集函数。
数据过滤 WHERE
基本格式
SELECT 语句可以使用 WHERE 进行数据过滤。格式为:
1
{SELECT 语句} WHERE {条件表达式};
1
SELECT*FROM MovieStar WHERE birthDATE >= "19700101";
1 2 3 4 5 6 7 8
+--------------+-------------+--------+------------+ | name | address | gender | birthDate | +--------------+-------------+--------+------------+ | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | | Debra Winger | A way | F | 1978-05-06 | | Jane Fonda | Turner Av. | F | 1977-07-07 | +--------------+-------------+--------+------------+
其中条件表达式同样支持常用编程语言中的基本运算。
运算符
基本运算符
语义
补充
= / <> / < / > / <= / >=
比较运算符
注意 = 而非 == ,部分 SQL 支持 !=
NOT / AND / OR
逻辑运算符
优先级 NOT > AND > OR
SQL 支持基本的运算符,如四则运算与括号等,此处略去。
特殊运算符
语义
示例
补充说明
IN
多值查找
WHERE movieType IN ("drama", "sciFic")
常用于子查询,将在其他章节介绍。
EXISTS
集合非空
WHERE EXISTS {子查询}
检测子查询返回是否为空,将在其他章节介绍。
BETWEEN AND
区间比较
WHERE length BETWEEN 120 AND 150
等价于 WHERE 120 <= length AND length <= 150
LIKE
模式匹配
WHERE title LIKE "star %"
Mysql 的模式匹配仅支持 % 匹配 ≥0 个字符,_ 匹配 1 个字符
IS NULL
为空
WHERE length IS NULL
由于 NULL 在运算中的特殊性,必要时使用 IS NULL
使用运算符进行组合筛选的示例:
1 2 3 4 5 6 7 8
-- 语句过长使用换行可以更加直观 SELECT * FROM Movies WHERE title LIKE "star %" AND (year<=2000OR studioName = "Paramount");
1 2 3 4 5 6 7
+--------------------+------+--------+-----------+------------+-----------+ | title | year | length | movieType | studioName | producerC | +--------------------+------+--------+-----------+------------+-----------+ | Star Trek | 1979 | NULL | sciFic | Paramount | 444 | | Star Trek: Nemesis | 2002 | 116 | sciFic | Paramount | 321 | | Star Wars | 1977 | 124 | sciFic | Fox | 555 | +--------------------+------+--------+-----------+------------+-----------+
SELECT *, -- 依然可以使用 * CONCAT(studioName, " ", movieType, " ", year) AS description FROM Movies;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
+--------------------------+----------------------------+ | title | description | +--------------------------+----------------------------+ | Empire Strikes Back | Fox drama 1980 | | My people,My country | huaxia feature 2019 | | Gone With the Wind | MGM drama 1938 | | Logan's run | MGM drama 1977 | | Pretty Woman | Disney drama 1990 | | Star Trek | Paramount sciFic 1979 | | Star Trek: Nemesis | Paramount sciFic 2002 | | Star Wars | Fox sciFic 1977 | | Terms of Endearment | MGM drama 1983 | | The Man Who Wasn't There | USA Entertainm comedy 2001 | | The Usual Suspects | MGM drama 1995 | +--------------------------+----------------------------+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
+--------------------------+-----------------------------------------------+ | title | CONCAT(studioName, " ", movieType, " ", year) | +--------------------------+-----------------------------------------------+ | Empire Strikes Back | Fox drama 1980 | | My people,My country | huaxia feature 2019 | | Gone With the Wind | MGM drama 1938 | | Logan's run | MGM drama 1977 | | Pretty Woman | Disney drama 1990 | | Star Trek | Paramount sciFic 1979 | | Star Trek: Nemesis | Paramount sciFic 2002 | | Star Wars | Fox sciFic 1977 | | Terms of Endearment | MGM drama 1983 | | The Man Who Wasn't There | USA Entertainm comedy 2001 | | The Usual Suspects | MGM drama 1995 | +--------------------------+-----------------------------------------------+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
+--------------------------+------+--------+-----------+----------------+-----------+----------------------------+ | title | year | length | movieType | studioName | producerC | description | +--------------------------+------+--------+-----------+----------------+-----------+----------------------------+ | Empire Strikes Back | 1980 | 111 | drama | Fox | 555 | Fox drama 1980 | | My people,My country | 2019 | 158 | feature | huaxia | 100 | huaxia feature 2019 | | Gone With the Wind | 1938 | 238 | drama | MGM | 123 | MGM drama 1938 | | Logan's run | 1977 | 120 | drama | MGM | 888 | MGM drama 1977 | | Pretty Woman | 1990 | 119 | drama | Disney | 999 | Disney drama 1990 | | Star Trek | 1979 | NULL | sciFic | Paramount | 444 | Paramount sciFic 1979 | | Star Trek: Nemesis | 2002 | 116 | sciFic | Paramount | 321 | Paramount sciFic 2002 | | Star Wars | 1977 | 124 | sciFic | Fox | 555 | Fox sciFic 1977 | | Terms of Endearment | 1983 | 132 | drama | MGM | 123 | MGM drama 1983 | | The Man Who Wasn't There | 2001 | 116 | comedy | USA Entertainm | 777 | USA Entertainm comedy 2001 | | The Usual Suspects | 1995 | 106 | drama | MGM | 999 | MGM drama 1995 | +--------------------------+------+--------+-----------+----------------+-----------+----------------------------+
SELECT*FROM Movies ORDERBY length * producerC; -- 可以是计算字段
1 2 3 4 5 6 7 8 9 10
+----------------+-------------+--------+------------+ | name | address | gender | birthDate | +----------------+-------------+--------+------------+ | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | | Debra Winger | A way | F | 1978-05-06 | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Jack Nicholson | X path | M | 1949-05-05 | | Jane Fonda | Turner Av. | F | 1977-07-07 | +----------------+-------------+--------+------------+
1 2 3 4 5 6 7 8 9 10
+----------------+-------------+--------+------------+ | name | address | gender | birthDate | +----------------+-------------+--------+------------+ | Jack Nicholson | X path | M | 1949-05-05 | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | | Jane Fonda | Turner Av. | F | 1977-07-07 | | Debra Winger | A way | F | 1978-05-06 | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | +----------------+-------------+--------+------------+
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
+--------------------------+------+--------+-----------+----------------+-----------+ | title | year | length | movieType | studioName | producerC | +--------------------------+------+--------+-----------+----------------+-----------+ | Star Trek | 1979 | NULL | sciFic | Paramount | 444 | | My people,My country | 2019 | 158 | feature | huaxia | 100 | | Terms of Endearment | 1983 | 132 | drama | MGM | 123 | | Gone With the Wind | 1938 | 238 | drama | MGM | 123 | | Star Trek: Nemesis | 2002 | 116 | sciFic | Paramount | 321 | | Empire Strikes Back | 1980 | 111 | drama | Fox | 555 | | Star Wars | 1977 | 124 | sciFic | Fox | 555 | | The Man Who Wasn't There | 2001 | 116 | comedy | USA Entertainm | 777 | | The Usual Suspects | 1995 | 106 | drama | MGM | 999 | | Logan's run | 1977 | 120 | drama | MGM | 888 | | Pretty Woman | 1990 | 119 | drama | Disney | 999 | +--------------------------+------+--------+-----------+----------------+-----------+
升序与降序
ORDER BY 可以使用 ASC(默认)/ DESC 表示升序和降序,将其放在排序关键字后面。
1
SELECT*FROM MovieStar ORDERBY birthDate ASC;
1
SELECT*FROM MovieStar ORDERBY birthDate DESC;
1 2 3 4 5 6 7 8 9 10
+----------------+-------------+--------+------------+ | name | address | gender | birthDate | +----------------+-------------+--------+------------+ | Jack Nicholson | X path | M | 1949-05-05 | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | | Jane Fonda | Turner Av. | F | 1977-07-07 | | Debra Winger | A way | F | 1978-05-06 | | Kim Basinger | Baldwin Av. | F | 1979-05-07 | +----------------+-------------+--------+------------+
1 2 3 4 5 6 7 8 9 10
+----------------+-------------+--------+------------+ | name | address | gender | birthDate | +----------------+-------------+--------+------------+ | Kim Basinger | Baldwin Av. | F | 1979-05-07 | | Debra Winger | A way | F | 1978-05-06 | | Jane Fonda | Turner Av. | F | 1977-07-07 | | Alec Baldwin | Baldwin Av. | M | 1977-06-07 | | Harrison Ford | Prefect Rd. | M | 1955-05-05 | | Jack Nicholson | X path | M | 1949-05-05 | +----------------+-------------+--------+------------+
注释: 聚集函数的对象为分组,分组使用下一章节中的 GROUP BY 创建,是一些具有相同属性的行的集合。未创建分组的,全部行为一个分组,因此在目前的示例中我们都只能看到一行输出。
1
SELECTMIN(year), MAX(year), COUNT(length), SUM(producerC), AVG(producerC) FROM Movies;
1 2 3 4 5 6 7 8
SELECT MIN(year) AS minYear, MAX(year) AS maxYear, COUNT(length) AS cntLength, SUM(producerC) AS sumProducerC, AVG(producerC) AS avgProducerC FROM Movies;
与 WHERE 不同的是,HAVING 过滤的对象是分组,而 WHERE 的过滤对象为行。HAVING 通常与 GROUP BY 一同使用,必须放在 GROUP BY 之后。如果你无法很好的区分 WHERE 和 HAVING 只需要记住,在 GROUP BY 之前进行的筛选是 WHERE,在 GROUP BY 之后进行的筛选是 HAVING。
1
{SELECT 语句} GROUPBY {分组关键字} HAVING {条件表达式}
由于 HAVING 过滤的对象是分组。在 HAVING 条件表达式中,可以使用聚集函数;而在 WHERE 条件表达式中,不可使用聚集函数。但 HAVING 条件表达式中的引用具有与前面提到的 GROUP BY 相同的约束,详见 分组聚集 GROUP BY - 注意事项。
1 2 3 4 5 6 7 8 9 10
SELECT studioName, COUNT(*) FROM Movies GROUPBY studioName HAVING COUNT(*) >1AND LENGTH(studioName) >3;