一、排序检索数据
1. 排序数据
- 正如前一篇所述,下面的SQL语句返回某个数据库表的单个列。但请
看其输出,并没有特定的顺序。 - 输入:SELECT prod_name FROM products;
- 输出:
- 其实,检索出的数据并不是以纯粹的随机顺序显示的。如果不排序,数据一般将以它在底层表中出现的顺序显示。如果不明确控制的话,不能(也不应该)依赖该排序顺序。关系数据库设计理论认为,如果不明确规定排序顺序,则不应该假定检索出的数据的顺序有意义。
- 子句(clause): SQL语句由子句构成,有些子句是必需的,而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。子句的例子有SELECT语句的FROM子句,我们在前一章看到过这个子句。
- 为了明确地排序用SELECT语句检索出的数据,可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字,据此对输出进行排序。请看下面的例子:
- 输入:SELECT prod_name FROM products ORDER BY prod_name;
- 输出:
- 分析:这条语句除了指示MySQL对 prod_name列 以字母顺序排序数据 的ORDER BY子句外,其余与前面的语句相同。
- 注意! 通过非选择列进行排序:通常,ORDER BY子句中使用的列为检索显示所选择的列。但是,实际上并不一定要这样,用非检索的列排序数据是完全合法的。 如下:
- 输入:SELECT prod_name FROM products ORDER BY prod_price;
- 输出:
- 分析:上图中我们想要检索显示的列为 prod_name列,但我们是以未检索的列prod_price列为依据排序的,显而易见,输出的顺序与刚才的不同。当然,作为排序依据的列必须要存在于检索的表中,不然就会出错了。
2. 按多个列排序
- 经常需要按不止一个列进行数据排序。例如,如果要显示雇员清单,可能希望按姓和名排序(首先按姓排序,然后在每个姓中再按名排序)。如果多个雇员具有相同的姓,这样做很有用。
- 为了按多个列排序,只要指定列名,列名之间用逗号分开即可(就像选择多个列时所做的那样)。
- 下面的代码检索3个列,并按其中两个列对结果进行排序——首先按价格,然后再按名称排序。
- 输入:SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
- 输出:
- 分析:重要的是理解在按多个列排序时,排序完全按所规定的顺序进行。 换句话说,对于上述例子中的输出,仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的,则不会按prod_name排序。
3. 指定排序方向
- 数据排序不限于升序排序(从A到Z),这只是默认的排序顺序。 还可以使用ORDER BY子句以降序(从Z到A)顺序排序。为了进行降序排序,必须指定DESC关键字。
1. 下面的例子按价格以降序排序产品(最贵的排在最前面):
- 输入:
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC; - 输出:
2. 如果打算用多个列排序怎么办?下面的例子以降序排序产品(最贵的在最前面),然后再对产品名排序: - 输入:
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC, prod_name; - 输出:
- DESC关键字只应用到直接位于其前面的列名。 在上例中,只对prod_price列指定DESC,对prod_name列不指定。因此,prod_price列以降序排序,而prod_name列(在每个相同价格内)仍然按标准的升序排序。
- 因此,如果想在多个列上进行降序排序,必须对每个列指定DESC关键字。
- 与DESC相反的关键字是ASC(ASCENDING),在升序排序时可以指定它。但实际上,ASC没有多大用处,因为升序是默认的(如果既不指定ASC也不指定DESC,则假定为ASC)。
3. 使用ORDER BY和LIMIT的组合,能够找出一个列中最高或最低的值。下面的例子演示如何找出最昂贵物品的值:
- 输入:
SELECT prod_price,prod_name
FROM products
ORDER BY prod_price DESC
LIMIT 1; - 输出:
- 分析:prod_price DESC保证行是按照由最昂贵到最便宜检索的,而 LIMIT 1告诉MySQL仅返回一行。
- 注意!!ORDER BY子句的位置:在给出ORDER BY子句时,应该保证它位于FROM子句之后。如果使用LIMIT子句,它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息。
二、过滤数据
1. 使用WHERE子句
- 数据库表一般包含大量的数据,很少需要检索表中所有行。通常只会根据特定操作或报告的需要提取表数据的子集。
- 只检索所需数据需要指定搜索条件(search criteria),搜索条件也称为过滤条件(filter condition)。 SELECT语句中,数据根据WHERE子句中指定的搜索条件进行过滤。 WHERE子句在表名(FROM子句)之后给出 ,如下所示:
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE prod_price = 2.50; - 输出:
- 分析:这条语句从products表中检索两个列,但不返回所有行,只返回 prod_price值为2.50的行。
- 这个例子采用了简单的相等测试:它检查一个列是否具有指定的值,据此进行过滤。但是SQL允许做的事情不仅仅是相等测试。
- 注意!WHERE子句的位置:在同时使用ORDER BY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误。
2. WHERE子句操作符
- 在上例中,我们在关于相等的测试时看到了第一个WHERE子句,它确定一个列是否包含特定的值。MySQL支持下列条件操作符。
2.1 检查单个值
1. 我们已经看到了测试相等的例子。再来看一个类似的例子:
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE prod_name = ‘fuses’; - 输出:
- 检查WHERE prod_name=‘fuses’ 语句,它返回prod_name 的值为Fuses的一行。MySQL在执行匹配时默认不区分大小写,所以’fuses’与’Fuses’匹配。
2. 现在来看几个使用其他操作符的例子。
- 第一个例子是列出价格小于10美元的所有产品:
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE prod_price < 10; - 输出:
- 第二个例子检索价格小于等于10美元的所有产品(输出的结果比第一个例子输出的结果多两种产品):
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE prod_price <= 10; - 输出:
2.2 不匹配检查
- 以下例子列出不是由供应商1003 制造的所有产品:
- 输入:
SELECT vend_id, prod_name
FROM products
WHERE vend_id <> 1003;
*输出:
- 下面是相同的例子,其中使用 != 而不是<>操作符:
- 输入:
SELECT vend_id, prod_name
FROM products
WHERE vend_id != 1003; - 输出:
- 由上面可知 ,!= 操作符和 <>操作符作用是相同的。
- 注意!何时使用引号: 如果仔细观察上述WHERE子句中使用的条件,会看到有的值括在单引号内(如前面使用的’fuses’),而有的值未括起来。单引号是用来限定字符串的。如果将值与字符串类型的列进行比较,则需要使用限定引号(‘’)。而用来与数值类型的列进行比较的值不用引号。
2.3 范围值检查
- 为了检查某个范围的值,可使用BETWEEN操作符。其语法与其他WHERE子句的操作符稍有不同,因为它需要两个值,即范围的开始值和结束值。
- 例如,BETWEEN操作符可用来检索价格在5美元和10美元之间或日期在指定的开始日期和结束日期之间的所有产品。下面的例子说明如何使用BETWEEN操作符,它检索价格在5美元和10美元之间的所有产品:
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10; - 输出:
- 分析:从这个例子中可以看到,在使用BETWEEN时,必须指定两个值,即所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值,包括指定的开始值和结束值。
2.4 空值检查
- 在创建表时,表设计人员可以指定其中的列是否可以不包含任何值。在一个列不包含任何值时,则称其为包含空值 NULL。
- NULL :无值(no value),它与列包含 0、空字符串或 仅仅包含空格不同。
- SELECT语句有一个特殊的WHERE子句,可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句。其语法如下:
- 输入:
SELECT prod_name
FROM products
WHERE prod_price IS NULL; - 这条语句返回没有价格(空prod_price字段,不是价格为0)的所有产品,由于表中没有这样的行,所以没有返回数据。
- 但是,在customers 表中包含有具有空值的列,先看看customers 表中的两列数据:
- 如果在文件中没有某位顾客的电子邮件地址,则cust_email 列将包含NULL值,如下:
- 输入:
SELECT cust_id ,cust_email
FROM customers
WHERE cust_email IS NULL; - 输出:
- 注意!NULL与不匹配: 在通过过滤选择出不具有特定值的行时,你可能希望返回具有NULL值的行。但是,不行。因为NULL 具有特殊的含义,数据库不知道它们是否匹配,所以在匹配过滤或不匹配过滤时都不返回它们。 因此,在过滤数据时,一定要验证返回数据中确实给出了被过滤列具有NULL的行。
- 还是以customers 表为例,若想要过滤 'cust_email ’ <> ‘ylee@coyote.com’ 的值:
- 输入:
SELECT cust_id ,cust_email
FROM customers
WHERE cust_email <> ‘ylee@coyote.com’; - 输出:
- 可见,NULL值的行未返回。
三、数据过滤
1. 组合WHERE子句
- 上面介绍的所有WHERE子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制,MySQL允许给出多个WHERE子句。这些子句可以两种方式使用:以 AND子句的方式或 OR子句的方式。
- 操作符(operator):用来联结或改变 WHERE子句中的子句的关键字。也称为逻辑操作符(logical operator)。
1.1 AND操作符
- 为了通过不止一个列进行过滤,可使用AND操作符给WHERE子句附加条件。下面的代码给出了一个例子:
- 输入:
SELECT prod_id, prod_price , prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10; - 输出:
- 分析:此SQL语句检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格。这条SELECT语句中的WHERE子句包含两个过滤条件,并且用AND关键字联结它们。
- AND 是用在WHERE子句中的关键字,用来指示检索满足所有给定条件的行。 上述例子中使用了只包含一个关键字AND的语句,把两个过滤条件组
合在一起。还可以添加多个过滤条件,每添加一条就要使用一个AND。
1.2 OR操作符
- OR: WHERE子句中使用的关键字,与AND操作符不同,它指示MySQL检索匹配任一过滤条件的行。
- 请看如下的SELECT语句:
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003; - 输出:
- 分析:此SQL语句检索由任一个指定供应商制造的所有产品的产品名和价格。OR操作符告诉DBMS匹配任意一个过滤条件而不是同时匹配两个过滤条件。
- 如果这里使用的是AND操作符,则没有数据返回(此时创建的WHERE子句不会检索到匹配的产品)。
1.3 计算次序
- WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。
- 但是,组合AND和OR带来了一个有趣的问题。为了说明这个问题,来看一个例子。假如需要列出价格为10美元(含)以上且由供应商1002或1003制造的所有产品。下面的SELECT语句使用AND和OR操作符的组合建立了一个WHERE子句:
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10; - 输出:
- 分析:请看上面的结果。返回的行中有两行价格小于10美元,显然,返回的行未按预期的进行过滤。为什么会这样呢?原因在于计算的次序。SQL(像多数语言一样)在处理OR操作符前,优先处理AND操作符。 当SQL看到上述WHERE子句时,它理解为由供应商1003制造的任何价格为10美元(含)以上的产品,或者由供应商1002制造的任何产品,而不管其价格如何。换句话说,由于AND在计算次序中优先级更高,操作符被错误地组合了。
- 此问题的解决方法是使用圆括号明确地分组相应的操作符。 请看下面的SELECT语句及输出:
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10; - 输出:
- 分析:因为圆括号() 具有较AND或OR操作符高的计算次序,DBMS首先过滤圆括号内的OR条件。这时,SQL语句变成了选择由供应商1002或1003制造的且价格都在10美元(含)以上的任何产品,这正是我们所希望的。
- 注意!在WHERE子句中使用圆括号 任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确地分组操作符。 不要过分依赖默认计算次序,即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处,它能消除歧义。
2. IN操作符
- 圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定过滤条件的范围,范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单,全都括在圆括号中。注意:IN操作符完成了与OR相同的功能。 下面的例子说明了这个操作符:
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name; - 输出:
- 分析:此SELECT语句检索供应商1002和1003制造的所有产品。IN操作符后跟由逗号分隔的合法值清单,整个清单必须括在圆括号中。
- 为什么要使用IN操作符?其优点具体如下。
- 在使用长的合法选项清单时,IN操作符的语法更清楚且更直观。
- 在使用IN时,计算的次序更容易管理(因为使用的操作符更少)。
- IN操作符一般比OR操作符清单执行更快。
- IN的最大优点是可以包含其他SELECT语句,使得能够更动态地建立WHERE子句。
3. NOT操作符
- WHERE子句中的NOT操作符有且只有一个功能,那就是否定它之后所跟的任何条件。
- NOT :WHERE子句中用来否定后跟条件的关键字。
- 下面的例子说明NOT的使用。为了列出除1002和1003之外的所有供应
商制造的产品,可编写如下的代码: - 输入:
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name; - 输出:
- 这里的NOT否定跟在它之后的条件,因此,MySQL不是匹配1002和 1003 的 vend_id ,而是匹配 1002 和 1003 之外供应商的vend_id。
- 为什么使用NOT?对于简单的WHERE子句,使用NOT确实没有什么优势。但在更复杂的子句中,NOT是非常有用的。例如,在与IN操作符联合使用时,NOT使找出与条件列表不匹配的行非常简单。
- 注意!MySQL中的NOT MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反,这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。
四、使用通配符进行过滤
1.LIKE操作符
- 通配符(wildcard):用来匹配值的一部分的特殊字符。
- 搜索模式(search pattern):由字面值、通配符或两者组合构成的搜索条件。
- 通配符本身实际是SQL的WHERE子句中有特殊含义的字符,SQL支持几种通配符。
- 为在搜索子句中使用通配符,必须使用LIKE操作符。LIKE指示MySQL,后跟的搜索模式利用通配符匹配而不是直接相等匹配(=)进行比较。
1.1 百分号(%)通配符
- 最常使用的通配符是百分号(%)。在搜索串中,(%)表示任何字符出现任意次数。(%)代表搜索模式中给定位置的0个、1个或多个字符。 例如,为了找出所有以词 ‘jet’ 起头的产品,可使用以下SELECT语句:
- 输入:
SELECT prod_name, prod_price
FROM products
WHERE prod_name LIKE ‘jet%’; - 输出:
- 分析:此例子使用了搜索模式 ‘jet%’。在执行这条子句时,将检索任意以jet 起头的词。(%)告诉MySQL接受 jet 之后的任意字符,不管它有多少字符。
- 区分大小写: 根据MySQL的配置方式,搜索可以是区分大小写的。如果区分大小写,‘jet%’ 与JetPack 1000将不匹配。
- 通配符可在搜索模式中任意位置使用,并且可以使用多个通配符。 下面的例子使用两个通配符,它们位于模式的两端:
- 输入:
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘%anvil%’; - 输出:
- 分析:搜索模式’%anvil%'表示匹配任何位置包含文本anvil的值,而不论它之前或之后出现什么字符。
- 注意NULL: 虽然似乎(%)通配符可以匹配任何东西,但有一个例外,即NULL。即使是WHERE prod_name LIKE ‘%’ 也不能匹配用NULL值作为产品名的行。
1.2 下划线(_)通配符
- 另一个有用的通配符是下划线(_)。下划线的用途与%一样,但下划线只匹配单个字符而不是多个字符。
- 举一个例子:
- 输入:
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘_ ton anvil’; - 输出:
- 此WHERE子句中的搜索模式给出了后面跟有文本的两个通配符。结果只显示匹配搜索模式的行:第一行中下划线匹配1,第二行中匹配2。‘.5 ton anvil’ 产品没有匹配,因为搜索模式要求匹配一个通配符而不是两个。
- 对照一下,下面的SELECT语句使用(%)通配符,返回三行产品:
- 输入:
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE ‘%anvil%’; - 输出:
- 与(%)能匹配0个字符不一样,(_)总是匹配一个字符,不能多也不能少。
2. 使用通配符的技巧
- 正如所见,MySQL的通配符很有用。但这种功能是有代价的:通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。
- 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
- 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
- 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。
五、总结
- 本篇文章学习了如何用SELECT语句的ORDER BY子句对检索出的数据进行排序。这个子句必须是SELECT语句中的最后一条子句。可根据需要,利用它在一个或多个列上对数据进行排序。
- 介绍了如何用SELECT语句的WHERE子句过滤返回的数据。我们学习了如何对相等、不相等、大于、小于、值的范围以及NULL值等进行测试。
- 介绍如何用AND和OR操作符组合成WHERE子句,而且还讲授了如何明确地管理计算的次序,如何使用IN和NOT操作符。
- 介绍了什么是通配符以及如何在WHERE子句中使用SQL通配符,并且还说明了通配符应该细心使用,不要过度使用。
- 最后,如果本文有不足之处,欢迎各位大佬批评指正。若本文对您有帮助的话,就给小白博主点个赞吧!欢迎在评论区一起交流学习哦!我们下次再见!!