过滤数据
使用WHERE子句
1、数据库表一般包含大量的数据,很少需要检索表中的所有数据。通常会根据特定操作或报告的需要提取表数据的子集。只检索所需数据,因此需要指定搜索条件,搜索条件也称为过滤条件
2、在SELECT语句中,数据根据WHERE子句中指定的搜索条件进行进行过滤,WHERE子句在表名(FROM子句)之后给出
例1:
mysql> use demo;
Database changed
mysql> SELECT level,career FROM roleinfo WHERE career = 2;
+-------+--------+
| level | career |
+-------+--------+
| 107 | 2 |
| 101 | 2 |
+-------+--------+
注:
1、这句SQL语句的意思为:从roleinfo表中检索两个列,但不返回所有行,只返回career = 2的行。
2、WHERE子句的位置:在同时使用ORDER BY 和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误
例2:
mysql> SELECT platformName FROM roleinfo WHERE career = 2 ORDER BY platformName;
+---------------+
| platformName |
+---------------+
| ainjigame_app |
| xinjigame_app |
+---------------+
where子句操作符
在根据条件过滤数据时,一般会有以下的一些过滤操作符
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的两个值之间 |
is null | 值为null |
is not null | 值不为null |
例3:
mysql> SELECT level,career FROM roleinfo WHERE career BETWEEN 2 AND 3;
+-------+--------+
| level | career |
+-------+--------+
| 107 | 2 |
| 106 | 3 |
| 101 | 2 |
+-------+--------+
例3_1:
注:
1、BETWEEN同AND一起搭配使用,表示两个值之间
2、通常value1 应该小于value2。当BETWEEN前面加上NOT运算符时,表示与BETWEEN相反的意思,即选取这个范围之外的值。
3、除了数值类型外,BETWEEN也支持字符串范围,当条件为字符串类型时是不区分大小写的
4、几乎所有的数据库都支持BETWEEN...AND运算符,但不同的数据库对该语法处理方式是有差异的。在MySQL中,BETWEEN包含了value1和value2边界值,而有的数据库则不包含边界值的
5、BETWEEN子句后面跟的数值类型必须要与表中对应值得数据类型一致
空值检查
1、在创建表时,表设计人员可以指定其中的列是否可以不包含值。一个列不包含值时,称其包含空值NULL
2、NULL:无值,它与字段包含0、空字符串或仅仅包含空格不同;可以使用IS NULL子句来检查表中是否具有NULL值。或IS NOT NULL 来检查是否不为NULL
例4:
mysql> SELECT platformName FROM roleinfo WHERE createTime IS NULL;
Empty set (0.00 sec)
/* 无返回值表示表中该列中无NULL值 */
例4_1:
注:
在通过过滤选择出不具有特定值的行数时,可能希望返回具有NULL值的行。但是,不行。因为未知具有特殊含义,数据库不知道它们是否匹配,所以在匹配或不匹配过滤时不反回它们。因此,在过滤数据时,一定要验证返回数据中确实给出了被过滤列具有NULL的行
组合WHERE子句
1、为了进行更强的过滤控制,MYSQL允许给出多个WHERE子句,这些子句可以以两种方式使用:以AND子句的方式或OR子句的方式
2、操作符:用来联结或改变WHERE子句中的子句的关键字,也被称为逻辑操作符
3、存在多个过滤条件时就可以使用逻辑操作符来进行过滤
AND操作符
1、AND操作符表示并列关系:多个条件必须同时成立
例5:
mysql> SELECT platformName FROM roleinfo WHERE career = 2 AND level = 101;
+---------------+
| platformName |
+---------------+
| ainjigame_app |
+---------------+
注:
1、AND:用在WHERE子句中的关键字,用来检索满足所有给定条件的行(进一步限制返回结果)。可以添加多个过滤条件,每添加一个就要使用一个AND
2、上面的例子例子的意思为:从roleinfo检索platformName列,只返回career列等于2且level列等于101的行。
OR操作符
1、OR操作符与AND操作符不同,它指示MYSQL检索匹配任一条件的行。为"或"的关系,其中一个条件成立即可
例6:
mysql> SELECT platformName FROM roleinfo WHERE level = 107 OR level = 101;
+---------------+
| platformName |
+---------------+
| xinjigame_app |
| ainjigame_app |
+---------------+
注:
1、OR:WHERE子句中使用的关键字,用来检索匹配任一条件的行
2、上面的例子例子的意思为:从roleinfo检索platformName列,只返回level列等于107或level列等于101的行。
计算次序
1、在过滤条件中同时存在and和or操作符时,会先计算AND"且"再计算OR"或"。(先且后或)
2、需要规定计算优先级时,可以使用圆括号来标记
例7:
mysql> SELECT platformName FROM roleinfo WHERE career = 2 OR sex = 1 AND camp = 3;
+---------------+
| platformName |
+---------------+
| xinjigame_app |
| ainjigame_app |
+---------------+
该SQL语句的意思为:从roleinfo检索platformName列,只返回sex=1且camp=3或career=2的列(先且后或)
例7_1:
mysql> SELECT platformName FROM roleinfo WHERE (career = 2 OR sex = 1) AND camp = 3;
+---------------+
| platformName |
+---------------+
| ainjigame_app |
+---------------+
该SQL语句的意思为:从roleinfo检索platformName列,只返回career=2或sex=1且camp=3的列(先或后且)
注:
1、SQL(像多数语言一样),在处理OR操作符前会优先处理AND操作符,即AND操作符的优先级更高。所以上面两个例子的意思完全不一样
2、此问题的解决方法是使用圆括号明确地分组相应的操作符,DBMS会首先处理圆括号内的过滤条件
3、任何时候使用具有AND和OR操作符的WHERE子句时,都应该使用圆括号明确地分组操作符,不要过分的依赖默认计算次序。
IN操作符
圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN取得合法值是由逗号分隔的清单,全都括在圆括号中
例8:
mysql> SELECT platformName FROM roleinfo WHERE camp IN(1,2) ORDER BY platformName;
+---------------+
| platformName |
+---------------+
| cinjigame |
| xinjigame_app |
+---------------+
注:
1、上面的SQL语句的意思是:从roleinfo检索platformName列,只返回camp值为1或2的行(包含两个边界值)。操作符后跟由逗号分隔的合法清单,整个清单必须括在圆括号中
2、IN操作完成与OR相同的功能
3、当IN前面加上NOT运算符时,表示与IN相反的意思,即不在这些列表项内选择。
使用IN操作符的优点
1、在使用长的合法选项清单时,IN操作符的语法更加清楚且更直观
2、在使用IN操作符时,计算的次序更容易管理(因为使用的操作符更少)
3、IN操作符一般比OR操作符执行得更快
4、IN操作符最大的有优点是可以包含其他SELECT语句,使得能更动态的建立WHERE子句
例8_1:
mysql> SELECT platformName FROM roleinfo WHERE camp IN (SELECT career FROM roleinfo WHERE sex = 1 OR sex = 0);
+---------------+
| platformName |
+---------------+
| xinjigame_app |
| ainjigame_app |
+---------------+
注:
1、更多情况下IN列表项的值是不明确的,而可能是通过一个子查询得到的,然后将查询结果作为IN的列表项以实现最终的查询结果,注意在子查询中返回的结果必须是一个字段列表项。
2、IN列表项不仅支持数字,也支持字符甚至时间日期类型等,并且可以将这些不同类型的数据项混合排列
3、一个IN只能对一个字段进行范围比对,如果要指定更多字段,可以使用AND或OR逻辑运算符
例8_2:
mysql> SELECT platformName FROM roleinfo WHERE camp IN(3,4) OR career IN(1,2);
+---------------+
| platformName |
+---------------+
| xinjigame_app |
| ainjigame_app |
+---------------+
NOT操作符
WHERE子句中的NOT操作符有且只有一个功能,那就是否定它之后跟的任何条件
例9:
mysql> SELECT platformName FROM roleinfo WHERE camp NOT IN (1,2);
+---------------+
| platformName |
+---------------+
| ainjigame_app |
+---------------+
注:
MYSQL中支持使用NOT对IN、BETWEEN和EXISTS子句取反,这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。
用通配符进行过滤
LIKE操作符
前面介绍的所有操作符都是针对已知值进行过滤的,但是这种过滤方式并不是在任何时候都是好用的。例如,怎么搜索产品名称中包含文本anvil的所有产品?使用简单的比较操作符肯定是不行的,此时就必须使用通配符。利用通配符可创建比较特定数据的搜索模式。在这个例子中,就可以构造一个通配符搜索模式,找出产品中任何位置出现anvil的产品
1、通配符:用来匹配值的一部分的特殊字符。其本身实际上就是SQL的WHERE子句中有特殊含义的字符
2、搜索模式:由字面值,通配符或两者组合成的搜索条件
3、为在搜索子句中使用通配符,就必须使用LIKE操作符。LIKE指示MYSQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较
百分号(%)通配符
最常使用的通配符是百分号(%)。在所搜串中,%表示任何字符出现任意次数。
例10:返回channelName值为"g"开头的值
mysql> SELECT platformName,channelName FROM roleinfo WHERE channelName LIKE "g%";
+---------------+---------------+
| platformName | channelName |
+---------------+---------------+
| ainjigame_app | ginjigame_app |
+---------------+---------------+
/*表示将检索任意以"g"开头的词,%告诉MYSQL接受"g"之后的任意字符*/
例10_1:返回channelName值为"APP"结尾的值
mysql> SELECT platformName,channelName FROM roleinfo WHERE channelName LIKE "%APP";
+---------------+---------------+
| platformName | channelName |
+---------------+---------------+
| xinjigame_app | xinjigame_app |
| ainjigame_app | ginjigame_app |
+---------------+---------------+
/*不区分大小写*/
例10_2:返回channelName值为包含"e"的值
mysql> SELECT platformName,channelName FROM roleinfo WHERE channelName LIKE "%e%";
+---------------+---------------+
| platformName | channelName |
+---------------+---------------+
| xinjigame_app | xinjigame_app |
| cinjigame | binjigame |
| ainjigame_app | ginjigame_app |
+---------------+---------------+
例10_3:返回channelName值为以"b"开头以"e"结尾的值
mysql> SELECT platformName,channelName FROM roleinfo WHERE channelName LIKE "b%e";
+--------------+-------------+
| platformName | channelName |
+--------------+-------------+
| cinjigame | binjigame |
+--------------+-------------+
例10_4:返回channelName值为不以"APP"结尾的值
mysql> SELECT platformName,channelName FROM roleinfo WHERE channelName NOT LIKE "%app";
+--------------+-------------+
| platformName | channelName |
+--------------+-------------+
| cinjigame | binjigame |
+--------------+-------------+
注:
1、%还能匹配0个字符。%表示搜索模式中给定位置的0个、1个或多个字符
2、尾空格可能会干扰通配符的匹配(会无法匹配到对应的值,如WHERE channelName LIKE "%app "),解决这个问题的办法是在搜索模式最后附加一个%,还有就是使用函数
3、虽然似乎%通配符可以匹配任何东西,但有一个例外,即NULL。无法匹配值为NULL的行
4、MySQL允许将NOT运算符与LIKE运算符组合,以找到不匹配特定模式的字符串
下划线(_)通配符
另一种通配符是下划线(_)。下划线的用途与%一样,但是下划线只匹配单个字符而不是多个字符(只能是下划线处不一致)
例11:查找以T开头以m结尾的值
mysql> SELECT
employeeNumber, lastName, firstName
FROM
employees
WHERE
firstname LIKE 'T_m';
+----------------+----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+----------+-----------+
| 1619 | King | Tom |
+----------------+----------+-----------+
例12:查找以b_n开头的值
mysql> SELECT platformName,channelName FROM roleinfo WHERE channelName LIKE "b_n%";
+---------------+---------------+
| platformName | channelName |
+---------------+---------------+
| cinjigame | binjigame |
| ainjigame_app | bonjigame_bpp |
+---------------+---------------+
总结:
1、百分比(%)通配符允许匹配任何字符串的零个或多个字符。
2、下划线(_)通配符允许匹配任何单个字符。
3、在使用下划线(_)通配符时也需要使用%来指定位置
MySQL LIKE与ESCAPE子句
有时想要匹配的模式包含通配符,例如10%,_20等这样的字符串时。在这种情况下,您可以使用ESCAPE子句指定转义字符,以便MySQL将通配符解释为文字字符。如果未明确指定转义字符,则反斜杠字符\是默认转义字符。
例13:查找包含"_pp"的值
mysql> SELECT platformName,channelName FROM roleinfo WHERE channelName LIKE "%\__pp%";
+---------------+---------------+
| platformName | channelName |
+---------------+---------------+
| xinjigame_app | xinjigame_app |
| ainjigame_app | bonjigame_bpp |
+---------------+---------------+
例13_1:
mysql> SELECT
-> platformName,channelName
-> FROM
-> roleinfo
-> WHERE
-> channelName LIKE "%$__PP%" ESCAPE "$";
+---------------+---------------+
| platformName | channelName |
+---------------+---------------+
| xinjigame_app | xinjigame_app |
| ainjigame_app | bonjigame_bpp |
+---------------+---------------+
注:
LIKE操作符强制MySQL扫描整个表以找到匹配的行记录,因此,它不允许数据库引擎使用索引进行快速搜索。因此,当要从具有大量行的表查询数据时,使用LIKE运算符来查询数据的性能会大幅降低。