引言
上一节笔记中,我们回顾了数据库的一些基础知识,同时认识了 MySQL DBMS。上节笔记中给出如何在 Ubuntu 16.04 x64 系统上安装 MySQL 的方法,并且讲解了如何连接和登录服务器,同时介绍了几个简单的命令作为使用 MySQL 开端。从本节实践笔记开始,就要开始学习一些真正的 MySQL 数据库使用技巧了,干货多多,值得总结与回顾。:-)
准备工作
为了方便,在后面的学习实践中,我们使用的数据表来自《MySQL 必知必会》一书提供的样例表;当然你也可以使用其它自定义的表。
下面给出如何创建并填充这些样例表的步骤:
- 请戳 这里 下载相关脚本压缩包;
- 将压缩包解压,并记住脚本所在路径:
/path/to/create.sql
和/path/to/populate.sql
; - 连接并登录 MySQL 服务器,然后创建名为
demo
的数据库:CREATE DATABASE demo;
; - 选择新创建的数据库
demo
:USE demo;
- 执行脚本,创建样例表:
SOURCE /path/to/create.sql;
; - 填充样例表:
SOURCE /path/to/populate.sql;
; - 使用命令
SHOW TABLES
,我们可以看到已经创建并填充完毕的样例表:
检索
SELECT
语句用于从表中检索一个或者多个数据列,使用该命令,我们至少需要提供两点信息:选择什么以及从哪儿选择。
检索单列
- 语句:
SELECT cust_name FROM customers;
; - 说明:表示从表
customers
中检索名为cust_name
的列; 结果:
注意:输出结果的顺序可能在不同情况下有所不同,这是因为我们并没有在检索同时进行排序。但是输出的行数在不同情况下一定相同。
检索多列
- 语句:
SELECT cust_name, cust_city FROM customers;
; - 说明:表示从表中检索两列数据;
- 结果:
检索所有列
- 语句:
SELECT * FROM customers;
; - 说明:使用通配符
*
代表表中所有的列,这样就可以检索出表中所有列数据; 输出:
注意:输出列的顺序一般是列在表定义时出现的顺序,但是有时候也会因为表的模式变化(删除、添加等)而导致顺序变化。一般情况下,如非必要,尽量不要使用通配符,否则会因为检索不必要的列而降低检索和程序的性能。
检索不同行
- 语句:
SELECT DISTINCT vend_id FROM products;
; - 说明:添加
DISTINCT
关键字,可以自动过滤掉重复的结果数据,只显示不同的检索结果。 输出:
注意:我们在应用关键字
DISTINCT
时,需要注意它是应用于所有列,而非前置它的列。比如SELECT DISTINCT vend_id, prod_price FROM products;
语句,除非两个列都不同,否则所有行都会被检索到。
限制行
- 语句:
SELECT vend_id FROM products LIMIT 6;
; - 说明:使用
LIMIT
子句限定检索的行。 输出:
补充:
LIMIT num
:表示从第一行(索引为 0)开始,一共返回num
行;LIMIT n, m
:表示从第n
行开始,一共返回m
行;LIMIT m OFFSET n
:与上一子句的含义相同,此为 MySQL 5 支持的新的替代语法,主要是为了避免上述语法带来的困扰。
完全限定表名
- 语句:
SELECT customers.cust_name FROM customers;
; - 说明:其实与直接指定列名效果一样,但是在有些情况下,我们需要使用到完全限定标名,所以需要记住该语法。
排序
前面只是介绍了如何检索数据,但是有时候我们需要得到排序好的检索结果。本小节就介绍排序的方法,关键是学习 ORDER BY
子句以及关键字 ASC(ENDING)
和 DESC(ENDING)
得到升序或降序结果。
子句(clause):SQL 语句由子句构成,子句有些是可选的,有些是必需的。一个子句通常由一个关键字和所提供的数据组成。在编写 SQL 语句时,需要注意子句、关键字使用顺序,使用不当,会出现错误。
按单列排序
- 语句:
SELECT cust_name FROM customers ORDER BY cust_name;
- 说明:表示输出的检索结果是安装
cust_name
列字母顺序排列的(默认是升序)。当然,我们也可以根据其它非检索列排序。 - 结果:
按多列排序
- 语句:
SELECT cust_id, cust_name FROM customers ORDER BY cust_id, cust_name;
- 说明:表示首先安装 id 排序,然后按照名称排序。需要注意的是,只有在多行
cust_id
相同时,才会按照cust_name
排序。 - 结果:
排序方向
- 语句:
SELECT prod_id, prod_name, prod_price FROM products ORDER BY prod_price DESC, prod_id;
- 说明:使用
DESC
关键字来指定某列按照降序排列,注意该关键字只应用于其前面的列,后面没有指定该关键字的列全部按照标准升序排列。 结果:
技巧:使用
LIMIT
与ORDER BY
组合可以获得最值。
过滤
关于过滤,就是要在检索时提供搜索条件(search criteria),也叫做过滤条件(filter condition),我们将要学习 WHERE
子句的使用,以及 IN
和 NOT
等逻辑操作符;此外,还会介绍使用通配符进行过滤。
WEHRE 子句初探
简单使用:
- 语句:
SELECT prod_id, prod_name FROM products WHERE prod_price = 10.00;
- 说明:表示检索出产品价格为 10.00 的结果。
- 结果:
- 语句:
操作符:
=
:等于;<>
:不等于;!=
:不等于;<
:小于;>
:大于;<=
:小于等于;>=
:大于等于;BETWEEN
:两者之间。
单值检查:
- 语句:
SELECT prod_id, prod_name FROM products WHERE prod_price >= 10.00 ORDER BY prod_price;
- 说明:表示过滤出所有价格大于等于 10.00 的产品,并将结果安装价格排序输出。
- 结果:
- 语句:
不匹配检查:
- 语句:
SELECT prod_id, prod_name FROM products WHERE prod_price != 10.00 ORDER BY prod_price;
- 说明:表示过滤出所有价格不等于 10.00 的产品,并排序结果。
- 结果:
- 语句:
范围检查:
- 语句:
SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price BETWEEN 10 AND 30 ORDER BY prod_price;
- 说明:表示过滤出价格在 10.00 到 30.00 之间的记录,并排序结果。除此之外,
BETWEEN...AND...
操作符还可限定日期范围。 - 结果:
- 语句:
空值检查:
- 语句:
SELECT cust_name FROM customers WHERE cust_email IS NULL;
- 说明:表示过滤出电子邮件为
NULL
的用户名称。 - 结果:
- 语句:
逻辑操作符
AND
用于组合多个(使用多个 AND)过滤条件,使用方法如下:
- 语句:
SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price <= 30.00 AND vend_id = 1003 ORDER BY prod_price;
- 说明:表示检索的结果要同时满足两个条件:价格小于等于 30,同时 vend_id 等于 1003,最终结果按照价格排序。
- 结果:
OR
- 语句:
SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price <= 30.00 OR vend_id = 1003 ORDER BY prod_price;
- 说明:表示检索出的结果只需要满足给定的任何一个条件即可,所得结果按照价格排序。
结果:
注意,在任何时候使用具有 AND 和 OR 操作符的语句中,都应当使用括号,这样可以清晰地标定出计算优先级,避免歧义。
IN
- 语句:
SELECT vend_id, prod_name, prod_price FROM products
WHERE vend_id IN (1003, 1005)
ORDER BY prod_price;
- 说明:表示 vend_id 应当在指定的范围中,范围在圆括号中给出。其实也可以使用
OR
操作符实现。 结果:
补充:
- 对于长的选项清单,使用
IN
操作符会更加清晰; - 计算次序容易管理,使用操作符更少;
IN
操作符一般比OR
执行更快;- 可以包含其它
SELECT
语句,从而更加动态地创建WHERE
子句。
- 对于长的选项清单,使用
NOT
用于否定其后的所有条件。
- 语句:
SELECT vend_id, prod_name, prod_price FROM products
WHERE vend_id NOT IN (1003, 1005)
ORDER BY prod_price;
- 说明:表示检索的结果,vend_id 不在给定的范围中。
结果:
注意:MySQL 支持使用 NOT 对 IN, BETWEEN 和 EXISTS 取反,这与其它 DBMS 运行使用 NOT 对各种条件取反有很大差别。
通配符过滤
在对数据进行相对复杂过滤时,我们可以借助 LIKE
操作符结合通配符完成相应任务。
通配符(wildcard):用于匹配值的一部分的特殊字符。
搜索模式(search pattern):由字面值、通配符或二者组合构成的搜索条件。
LIKE
操作符表示后面接的搜索条件使用的是通配符匹配。
% 通配符
%
通配符表示匹配任何字符出现任何次数(包括 0 次)。常见用法:
- 通配符位于尾部:
SELECT prod_name FROM products WHERE prod_name LIKE 'Jet%';
- 通配符位于开头:
SELECT prod_name FROM products WHERE prod_name LIKE '%anvil';
- 通配符位于两端:
SELECT prod_name FROM products WHERE prod_name LIKE '%anvil%';
- 通配符位于中间:
SELECT prod_name FROM products WHERE prod_name LIKE '%to%';
- 通配符位于尾部:
注意 NULL,并不能用
%
匹配 NULL,而应当使用IS NULL
来完成检查。
_ 通配符
_
通配符用于匹配单个字符,不能匹配多于一个字符或者 0 个字符的情况。- 示例用法:
SELECT prod_name FROM products WHERE prod_name LIKE '_ar%';
注意点
使用通配符虽然很便捷,但是代价时搜索时间更长。所以,不应当过度使用它们,而应当在需要的时候细心使用。
版权声明
- 本文由 Christopher L 发表,采用 知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议 进行许可。请确保你已了解许可协议,并在 转载 时声明。
- 本文固定链接:http://blog.chriscabin.com/database/mysql/1457.html。