SQL AND和OR运算符
SQL 中的 AND 和 OR 运算符用来连接多个查询条件,以缩小返回的结果集,它们被称为连接符。
AND 运算符
AND 运算符用于连接 WHERE 子句中的多个查询条件,只有当这些查询条件都被满足时,数据行(记录)才会被选取。语法
WHERE 子句中 AND 运算符的基本语法如下:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];
示例
例如有如下的 website 表:+----+----------------+----------------------------+-----+-------+---------+---------+ | id | name | url | age | alexa | uv | country | +----+----------------+----------------------------+-----+-------+---------+---------+ | 1 | 百度 | https://www.baidu.com/ | 21 | 4 | 5010.5 | CN | | 2 | 淘宝 | https://www.taobao.com/ | 17 | 8 | 3996.75 | CN | | 3 | C语言中文网 | http://c.biancheng.net/ | 12 | 7923 | 11.62 | CN | | 4 | Google | https://www.google.com/ | 23 | 1 | 36474 | US | | 5 | GitHub | https://github.com/ | 13 | 95 | 216.3 | US | | 6 | Stack Overflow | https://stackoverflow.com/ | 16 | 48 | 592.2 | US | | 7 | Yandex | http://www.yandex.ru/ | 11 | 53 | 591.82 | RU | | 8 | VK | https://vk.com/ | 23 | 23 | 1206 | RU | +----+----------------+----------------------------+-----+-------+---------+---------+下面的代码,将从 website 表中选取出 id、name、url 和 uv 四个字段,并且满足日访问量(uv)大于 500 万、年龄(age)小于 20 两个条件:
SELECT id, name, url, uv FROM website WHERE uv>500 AND age<20;执行结果如下:
+----+----------------+----------------------------+---------+ | id | name | url | uv | +----+----------------+----------------------------+---------+ | 2 | 淘宝 | https://www.taobao.com/ | 3996.75 | | 6 | Stack Overflow | https://stackoverflow.com/ | 592.2 | | 7 | Yandex | http://www.yandex.ru/ | 591.82 | +----+----------------+----------------------------+---------+
OR 运算符
OR 运算符用于连接 WHERE 子句中的多个查询条件,只要满足其中一个条件,数据行(记录)就能被选取。语法
WHERE 子句中 OR 运算符的基本语法如下:
SELECT column1, column2, columnN
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]
示例
仍然使用上面的 website 表,从 CUSTOMERS 表中选取出 id、name、url 和 uv 四个字段,并且日访问量(uv)小于 500 万,或者年龄(age)大于 20:SELECT id, name, url, uv FROM website WHERE uv<500 OR age>20;执行结果如下:
+----+-------------+-------------------------+--------+ | id | name | url | uv | +----+-------------+-------------------------+--------+ | 1 | 百度 | https://www.baidu.com/ | 5010.5 | | 3 | C语言中文网 | http://c.biancheng.net/ | 11.62 | | 4 | Google | https://www.google.com/ | 36474 | | 5 | GitHub | https://github.com/ | 216.3 | | 8 | VK | https://vk.com/ | 1206 | +----+-------------+-------------------------+--------+