第七章:数据过滤
7.1组合WHERE子句
7.1.3 计算次序
SQL(像多数语言一样)在处理OR操作符前,优先处理AND操作符。
解决:任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序,即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处,它能消除歧义。
7.2 IN操作符
为什么要使用IN操作符?其优点具体如下。
❑ 在使用长的合法选项清单时,IN操作符的语法更清楚且更直观。
❑ 在使用IN时,计算的次序更容易管理(因为使用的操作符更少)。
❑ IN操作符一般比OR操作符清单执行更快。
❑ IN的最大优点是可以包含其他SELECT语句,使得能够更动态地建立WHERE子句。第14章将对此进行详细介绍。
7.3 NOT操作符
为什么使用NOT?对于简单的WHERE子句,使用NOT确实没有什么优势。但在更复杂的子句中,NOT是非常有用的。例如,在与IN操作符联合使用时,NOT使找出与条件列表不匹配的行非常简单。
第8章 用通配符进行过滤
8.1.1 百分号(%)通配符
注意尾空格: 尾空格可能会干扰通配符匹配。例如,在保存词anvil时,如果它后面有一个或多个空格,则子句WHERE prod_name LIKE '%anvil’将不会匹配它们,因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。一个更好的办法是使用函数(第11章将会介绍)去掉首尾空格。
8.1.2 下划线(_)通配符
与%能匹配0个字符不一样,_总是匹配一个字符,不能多也不能少
8.2 使用通配符的技巧
❑ 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
❑ 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
❑ 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。
第9章 用正则表达式进行搜索
9.2 使用MySQL正则表达式
MySQL用WHERE子句对正则表达式提供了初步的支持,允许你指定正则表达式,过滤SELECT检索出的数据。
9.2.2 进行OR匹配
为搜索两个串之一(或者为这个串,或者为另一个串),使用|,如下所示:
语句中使用了正则表达式1000|2000。|为正则表达式的OR操作符。它表示匹配其中之一,因此1000和2000都匹配并返回。
9.2.3 匹配几个字符之一
这里,使用了正则表达式[123] Ton。[123]定义一组字符,它的意思是匹配1或2或3,因此,1ton和2 ton都匹配且返回(没有3 ton)。
正则表达式[123]Ton为[1|2|3]Ton的缩写
注意使用括号:
这并不是期望的输出。两个要求的行被检索出来,但还检索出了另外3行。之所以这样是由于MySQL假定你的意思是’1’或’2’或’3 ton’。除非把字符|括在一个集合中,否则它将应用于整个串。
9.2.4 匹配范围
这里使用正则表达式[1-5] Ton。[1-5]定义了一个范围,这个表达式意思是匹配1到5,因此返回3个匹配行。由于5 ton匹配,所以返回.5 ton。
9.2.5 匹配特殊字符
我们已经看到.、[]、|和-等,还有其他一些字符。请问,如果你需要匹配这些字符,应该怎么办呢?
为了匹配特殊字符,必须用\\为前导。\\-表示查找-, \\.表示查找.。
匹配\ 为了匹配反斜杠(\)字符本身,需要使用\\。
\或\? 多数正则表达式实现使用单个反斜杠转义特殊字符,以便能使用这些字符本身。但MySQL要求两个反斜杠(MySQL自己解释一个,正则表达式库解释另一个)。
9.2.6 匹配字符类
存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作,可以使用预定义的字符集,称为字符类(character class)
9.2.7 匹配多个实例
正则表达式\\ ([0-9] sticks? \\ )需要解说一下。\\ (匹配), [0-9]匹配任意数字(这个例子中为1和5), sticks?匹配stick和sticks(s后的?使s可选,因为?匹配它前面的任何字符的0次或1次出现), \\ )匹配)。没有?,匹配stick和sticks会非常困难。
[:digit:]匹配任意数字,因而它为数字的一个集合。{4}确切地要求它前面的字符(任意数字)出现4次,所以[[:digit:]]{4}匹配连在一起的任意4位数字。
等价于:
9.2.8 定位符
为了匹配特定位置的文本,需要使用定位符。
如果你想找出以一个数(包括以小数点开始的数)开始的所有产品,怎么办?简单搜索[0-9\\ .](或[[:digit:]\\ .])不行,因为它将在文本内任意位置查找匹配。解决办法是使用^定位符,如下所示:
匹配串的开始。因此,[0-9\.]只在.或任意数字为串中第一个字符时才匹配它们。没有^,则还要多检索出4个别的行(那些中间有数字的行)。
^的双重用途 ^有两种用法。在集合中(用[和]定义),用它来否定该集合,否则,用来指串的开始处。
LIKE和REGEXP的不同在于,LIKE匹配整个串而REGEXP匹配子串。利用定位符,通过用^开始每个表达式,用$结束每个表达式,可以使REGEXP的作用与LIKE一样。
简单的正则表达式测试 可以在不使用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0(没有匹配)或1(匹配)。可以用带文字串的REGEXP来测试表达式,并试验它们。相应的语法如下:
返回0
第10章 创建计算字段
10.1 计算字段
我们需要直接从数据库中检索出转换、计算或格式化过的数据;而不是检索出数据,然后再在客户机应用程序或报告程序中重新格式化。
计算字段并不实际存在于数据库表中。计算字段是运行时在SELECT语句内创建的。
字段(field): 基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。
客户机(如应用程序)的角度来看,计算字段的数据是以与其他列的数据相同的方式返回的
为什么使用计算字段:
在数据库服务器上完成这些操作比在客户机中完成要快得多,因为DBMS是设计来快速有效地完成这种处理的。
10.2 拼接字段
方法:在MySQL的SELECT语句中,可使用Concat()函数来拼接两个列。
MySQL的不同之处
多数DBMS使用+或||来实现拼接,MySQL则使用Concat()函数来实现。当把SQL语句转换成MySQL语句时一定要把这个区别铭记在心。
Concat()拼接串,即把多个串连接起来形成一个较长的串。Concat()需要一个或多个指定的串,各个串之间用逗号分隔。
通过删除数据右侧多余的空格来整理数据,这可以使用MySQL的RTrim()函数来完成:
Trim函数 MySQL除了支持RTrim()(正如刚才所见,它去掉串右边的空格),还支持LTrim()(去掉串左边的空格)以及Trim()(去掉串左右两边的空格)。
导出列:
别名有时也称为导出列(derived column),不管称为什么,它们所代表的都是相同的东西。
10.3 执行算术计算
输出中显示的expanded_price列为一个计算字段,此计算为quantity*item_price。客户机应用现在可以使用这个新计算列,就像使用其他列一样。
如何测试计算: SELECT提供了测试和试验函数与计算的一个很好的办法。虽然SELECT通常用来从表中检索数据,但可以省略FROM子句以便简单地访问和处理表达式。例如,SELECT 3*2;将返回6, SELECT Trim(‘abc’);将返回abc,而SELECT Now()利用Now()函数返回当前日期和时间。通过这些例子,可以明白如何根据需要使用SELECT进行试验。
第11章 使用数据处理函数
11.1 函数
函数一般是在数据上执行的,它给数据的转换和处理提供了方便。
注意:
出于可移植性原因,如果使用函数,应该保证做好代码注释,以便以后你(或其他人)能确切地知道所编写SQL代码的含义。
11.2 使用函数(种类)
文本函数
数值函数
日期和时间函数
系统函数:
(返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节))
11.2.1 文本处理函数
常用的文本处理函数:
批注:SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念,但MySQL(就像多数DBMS一样)都提供对SOUNDEX的支持。
使用SOUNDEX的场景:
假设customers表中有一个顾客Coyote Inc.,其联系名为Y.Lee。但如果这是输入错误,此联系名实际应该是Y.Lie,怎么办?显然,按正确的联系名搜索不会返回数据。
那么可以这样:
WHERE子句使用Soundex()函数来转换cust_contact列值和搜索串为它们的SOUNDEX值。因为Y.Lee和Y.Lie发音相似,所以它们的SOUNDEX值匹配,因此WHERE子句正确地过滤出了所需的数据。
uppper()
11.2.2 日期和时间处理函数
日期和时间采用相应的数据类型和特殊的格式存储,以便能快速和有效地排序或过滤,并且节省物理存储空间。
常用的日期和时间处理函数
首先需要注意的是MySQL使用的日期格式。无论你什么时候指定一个日期,不管是插入或更新表值还是用WHERE子句进行过滤,日期必须为格式yyyy-mm-dd。因此,2005年9月1日,给出为2005-09-01。虽然其他的日期格式可能也行,但这是首选的日期格式,因为它排除了多义性(如,04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或……)
根据确切日期查询:
那如果不知道确切日期呢?
用函数截取:
指示MySQL仅将给出的日期与列中的日期部分进行比较,而不是将给出的日期与整个列值进行比较。 为此,必须使用Date()函数。Date(order_date)指示MySQL仅提取列的日期部分,更可靠的SELECT语句为:
也存在一个Time()函数,在你只想要时间时应该使用它。
11.2.3 数值处理函数
常用的数值处理函数
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-35CVrTTI-1587615034154)(https://note.youdao.com/yws/res/2733/6E576785502F4DD684FA8E1540371F53)]
第12章 汇总数据
12.1 聚集函数
MySQL查询可用于检索数据,以便分析和报表生成:
❑ 确定表中行数(或者满足某个条件或包含某个特定值的行数)。
❑ 获得表中行组的和。
❑ 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。
12.1.2 COUNT()函数
注意:
❑ 使用COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值。
❑ 使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值。
如果指定列名,则指定列的值为空的行被COUNT()函数忽略,但如果COUNT()函数中用的是星号(*),则不忽略。
12.1.3 MAX()函数
对非数值数据使用MAX(): 虽然MAX()一般用来找出最大的数值或日期值,但MySQL允许将它用来返回任意列中的最大值,包括返回文本列中的最大值。在用于文本数据时,如果数据按相应的列排序,则MAX()返回最后一行。
MAX()函数忽略列值为NULL的行。
12.1.4 MIN()函数
对非数值数据使用MIN(): MIN()函数与MAX()函数类似,MySQL允许将它用来返回任意列中的最小值,包括返回文本列中的最小值。在用于文本数据时,如果数据按相应的列排序,则MIN()返回最前面的行。
MIN()函数忽略列值为NULL的行。
12.1.5 SUM()函数
12.2 聚集不同值
在MySQL 4.x中不能正常运行
5个聚集函数都可以如下使用:
❑ 对所有的行执行计算,指定ALL参数或不给参数(因为ALL是默认行为);
❑ 只包含不同的值,指定DISTINCT参数。
如果指定列名,则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*),因此不允许使用COUNT(DISTINCT),否则会产生错误。类似地,DISTINCT必须使用列名,不能用于计算或表达式。
12.3 组合聚集函数
在指定别名以包含某个聚集函数的结果时,不应该使用表中实际的列名。虽然这样做并非不合法,但使用唯一的名字会使你的SQL更易于理解和使用(以及将来容易排除故障)。