操作符:用来联结或改变WHERE子句中的子句关键字。也称逻辑操作符。
关键字:WHERE
1、检索全表
SELECT *
FROM colloge;
输出:
2、AND操作符
SELECT colloge_id, colloge_name, order_num
FROM colloge
WHERE colloge_id > 35 AND order_num > 10;
输出:
WHERE子句可以单独使用也可以使用多个AND或者多个OR操作符。
3、OR操作符
SELECT colloge_id, colloge_name, order_num
FROM colloge
WHERE colloge_id = 35 OR order_num > 10;
输出:
OR操作符只需符合任一条件即可,无需同时符合才可输出!
4、计算次序
SELECT colloge_id, colloge_name, order_num
FROM colloge
WHERE colloge_id < 35 OR colloge_id > 35 AND order_num > 10;
输出:
出现以上结果的原因:
SQL在处理OR操作符前,会优先处理AND操作符。当SQL看到上述WHERE子句时,它理解为由colloge_id大于35的任何order_num大于10的数据,或者colloge_id小于35的任意的不管order_num值的数据。
解决这种情况的方法是:加()
输入:
SELECT colloge_id, colloge_name, order_num
FROM colloge
WHERE (colloge_id < 35 OR colloge_id > 35) AND order_num > 10;
输出:
5、IN操作符
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。
与OR操作符很像,但是更推荐使用IN操作符。
输入:
SELECT colloge_id, colloge_name, order_num
FROM colloge
WHERE order_num IN (1,2)
ORDER BY order_num;
输出:
6、 NOT操作符(取反)
输入:
SELECT colloge_id, colloge_name, order_num
FROM colloge
WHERE order_num NOT IN (1,2,4,6,8,9,13,15,16)
ORDER BY order_num;
输出: