目录
第1章 了解SQL
第1章最值得注意的就是关于数据库的相关概念。
- 数据库(Database):保存有组织的数据的容器。也可以理解成保存有数据的仓库。
注意:数据库Database和数据库管理系统DBMS是有区别的,但人们常用数据库这个术语来表示他们所用的数据库软件(也就是数据库管理系统)。例如我们常说我们使用的是MySQL数据库,这其实是不严谨的,因为MySQL是数据库软件。数据库是通过DBMS创建和操作的容器,但其实数据库是什么不重要,因为我们不能直接访问,需要通过DBMS间接访问到数据库。
- 表(Table):是某种特定类型数据的结构化清单。例如顾客清单,学校的所有学生,公司的所有员工等。
注意:所谓表的特定类型数据即学校的学生和公司的员工不应该存储在同一张数据库表中,这会使得以后的访问很困难,应该创建两张表即学生表和员工表。数据库中的每个表有唯一的一个名字,但在不同的数据库中又可以有相同的表名。例如:
- 列(Column):是表中的一个字段,所有表都是一个或多个列组成的。
注意:理解列的最好方式就是将数据库表想象称为一个网格,网格中每一列都存储着一条特定的信息。例如用户表的列有id列、姓名列、密码列、生日列等,如密码列这一列都存储的是密码信息。
- 数据类型(DataType):说到列就必须说到数据类型,即每列都有相应的数据类型,它限制该列中所存储的数据。
注意:数据类型是用来限制存储的数据格式的,例如是生日列,那么该列应该用日期类型,而不是整数类型。又可以用来防止用户恶意录入数据,比如在生日列录入他的名字。数据类型还可以帮助正确地排序数据,优化磁盘使用。例如:
- 行(Row):表中的一条记录。如果把表格想象成网格,那么网格中垂直的列为表列,水平的行为表行。
注意:其实行和记录两个术语是可以相互替代的。如学生表的每一行都存储着一个学生的信息,而学生表的总行数表示共有多少个学生。例如:
- 主键(Primary Key):一列(或一组列),其值能够唯一区分表中的每个行。
注意:唯一标识表中的每行的这个列(或这组列)称为主键,如果没有主键那么更新或删除表中的特定行就会很困难。例如我国人民的身份证号就可以当主键,因为他们都是唯一的,但会说为什么不用人的姓名当主键呢,因为全国人口那么多,重名的人数相当多,如果要更新某个人的信息,通过姓名会找出许多条记录,无法完成操作。通常我们会设定一个id来作为主键,如:
表中的任何列都可以作为主键,但需要满足如下两个条件:
(1)任意两行都不具有相同的主键值。如用户名列就不可以作为主键,因为可能重名。
(2)每行都必须具有一个主键值,主键列不允许为NULL。
主键通常定义在表的一列上,但这并不是必需的,也可以一起使用多个列作为主键。在使用多列作为主键时,上述条件必须应用到构成主键的所有列,所有列值的组合必须是唯一的(但单个列的值可以不唯一)。
- SQL(Structured Query Language):即结构化查询语言,是一种专门用来与数据库通信的语言。
注意:所谓的与数据库通信就是对数据库中的表和行进行读写操作。SQL是一种标准,但不是所有的DBMS都遵循这种标准,本书中是针对MySQL所讲的。
SQL的优点如下:
(1)SQL不是某个特定数据库供应商专有的语言。几乎所有重要的DBMS(如MySQL、Oracle、SQL server等)都支持SQL,所以,学习此语言使你几乎能与所有数据库打交道。
(2)SQL简单易学。它的语句全都是由描述性很强的英语单词组成,而且这些单词的数目不多。
(3)SQL尽管看上去很简单,但它实际上是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作。
第2章 MySQL简介
MySQL:是一种数据库管理系统(DBMS),即它是一种数据库软件,用来管理数据的。
MySQL的优点:
- 成本——MySQL是开放源代码的,一般可以免费使用(甚至可以免费修改)。
- 性能——MySQL执行很快(非常快)。
- 可信赖——某些非常重要和声望很高的公司、站点使用MySQL,这些公司和站点都用MySQL来处理自己的重要数据。
- 简单——MySQL很容易安装和使用。
MySQL命令行实用程序:即在操作系统命令提示符下输入mysql(或mysql -uroot -p)后出现的界面。
注意事项:
- 命令输入在 mysql> 之后。
- 命令用分号 ; 或 \g 结束,换句话说,仅按Enter不执行命令。
- 输入 help 或 \h 可以查看帮助,也可以输入特定的命令查看特定帮助(如:help select查看select语句的帮助)
- 输入 quit 或 exit 可以退出命令行使用程序。
图形化工具:关于MySQL的图形化工具推荐Navicat for MySQL等。
第3章 使用MySQL
3.1 连接数据库
连接MySQL,需要如下几个信息:
- 主机名(计算机名)——如果连接到本地MySQL服务器,为 localhost;
- 端口(如果使用默认端口3306之外的端口);
- 一个合法的用户名;
- 用户口令(也就是密码,如果需要)。
命令如下:
mysql -u 用户名 -h 主机名 -P 端口号 -p 密码
3.2 选择数据库
这是必须的,因为MySQL可能管理着多个数据库,而不清楚你要用哪个,所以必须用USE关键字选择数据库。语法:
# 选择数据库
USE 数据库名;
# 查询当前处于哪个数据库下
SELECT DATABASE();
3.3 显示数据库和表
可以通过下面的命令来查看有哪些数据库,数据库中有哪些表。
# 获取MySQL内部的所有数据库,以列表显示返回
SHOW DATABASES;
# 获取当前数据库中的所有表,以列表显示返回
SHOW TABLES;
# 显示指定数据库中的所有表,以列表显示返回
SHOW TABLES FROM 数据库名;
# 显示某张表的列信息
SHOW COLUMNS FROM 表名;
# 同上条语句功能一样,也是显示表的信息
DESCRIBE 表名;
DESC 表名;
3.4 其他SHOW语句
所支持的其他 SHOW 语句还有(下面这些不常用,了解即可):
# 显示广泛的服务器状态信息
SHOW STATUS;
# 显示创建特定的数据库
SHOW CREATE DATABASE 数据库名;
# 显示创建特定的表
SHOW CREATE TABLE 表名;
# 显示授权用户的安全权限
SHOW GRANTS;
# 显示服务器错误信息
SHOW ERRORS;
# 显示服务器警告信息
SHOW WARNINGS;
第4章 检索数据
4.1 SELECT语句
SELECT是关键字,每条SQL语句由一个或多个关键字构成。
SELECT语句是最常用的SQL语句,用于从一个表或多个表中检索数据。
为了能够使用SELECT检索数据,需要至少两个关键信息:选择什么字段、从什么地方选择。
4.2 检索单个列
语法:
SELECT 字段名 FROM 表名;
注意,查询出来的数据如果没有明确排序查询,那么返回的数据的顺序没有特殊意义,返回数据的顺序可能是数据被添加到表中的顺序,也可能不是,只要返回相同数目的行,那么就是正确的。
结束SQL语句:多条SQL语句必须以分号分隔,单条SQL语句不一定需要加,但推荐加上。如果使用mysql命令行,必须加上分号。
SQL语句和大小写:SQL语句不区分大小写,如select和SELECT和Select效果是一样的。
使用空格:在处理SQL语句时,其中所有空格都会被忽略。SQL语句可以全部写在一行,也可以分多行书写,但推荐分多行,便于调试和阅读。
4.3 检索多个列
语法:
SELECT 字段名,字段名,字段名... FROM 表名;
注意,多个列名之间通过逗号进行分隔,但最后一个列名不加逗号,否则发生错误。
4.4 检索所有列
语法:
SELECT * FROM 表名;
注意:给定一个通配符(*),则返回表中的所有列,列的顺序一般是列在表中定义出现的顺序。但如果添加或删除列可能会导致顺序变化。除此之外,还可以列出该表中所有列字段来达到检索所有列的目的。
使用通配符:一般,除非你确实需要表中的每个列,否则最好别使用*通配符。虽然使用通配符可能会使你自己省事,不用明确列出所需列,但检索不需要的列通常会降低检索和应用程序的性能。
检索未知列:使用通配符有一个大优点。由于不明确指定列名(因为星号检索每个列),所以能检索出名字未知的列。
4.5 检索不同的行(去重)
语法:
SELECT DISTINCT 字段名 FROM 表名;
注意:使用distinct关键字,可以达到对字段去重的目的,但必须直接放在列名的前面。
不能部分使用DISTINCT:DISTINCT 关键字应用于所有列而不仅是前置它的列。如果给出SELECT DISTINCT id,name则是根据id和name两个字段去重的。如:
4.6 限制结果
所谓的限制结果,即使用LIMIT关键字来限定返回几行。LIMIT 5表示返回前5行,LIMIT 5,5表示从行5开始的5行返回。
语法如下:
LIMIT n; #返回前n行
LIMIT i,n; #将从行i(从0开始)开始的n行数据返回。第一个i表示开始位置,第二个n表示要检索的行数
行0:也就是说LIMIT i,n中的i是从0开始的,而不是从1开始,就像Java中数组的索引也是从0开始的而不是从1开始的,LIMIT 1,1检索出来的是第二行,LIMIT 0,1检索出来的才是第一行。
在行数不够时:行数不够时,有多少行就返回多少行。
4.7 使用完全限定名
所谓的完全限定名就是同时使用表名和列名来锁定某一列。
语法:
SELECT 表名.列名 FROM 表名;
# 示例
select user.id,user.age from users;#返回user表中的id字段和age字段
注意:表名也可以使用完全限定名,即数据库名.表名。
第5章 排序检索数据
5.1 排序数据
为什么要排序:检索出的数据并不是以纯粹的随机顺序出现的,如果不排序,则数据一般以在底层出现的顺序显示,可能是数据最初添加在表中的顺序。但是,如果数据后来进行过更新或删除,则此顺序将会受到MySQL重用回收存储空间的影响。
子句(clause) SQL语句由子句构成,有些子句是必需的,而有的是可选的。一个子句通常由一个关键字和所提供的数据组成。
排序使用ORDER BY子句,后面跟着一个或多个列名。语法:
SELECT 查询列表 FROM 表名 ORDER BY 列名,列名,..
5.2 按多个列排序
经常需要按多个字段进行排序,比如姓名先按照姓排序,如果遇到相同的姓则按照名进行排序。
为了按多个列排序,只要指定列名,列名之间用逗号分开即可(就像选择多个列时所做的那样)。语法同上。
在按多个列排序时,比如ORDER BY age,name,只有当多个行具有相同的age值时才会对name进行排序,如果age列中所有的值都是唯一的,那么则不会对name进行排序。
5.3 指定排序方向
所谓的指定排序方向就是按照升序或降序。默认是升序。降序需要指定DESC关键字。
语法:
# 单列指定排序方向
SELECT 查询列表 FROM 表名 ORDER BY 字段名 [DESC|ASC]
# 多列指定排序方向
SELECT 查询列表 FROM 表名 ORDER BY 字段名 [DESC|ASC], 字段名 [DESC|ASC], ...
在多个列上降序排序 如果想在多个列上进行降序排序,必须对每个列指定 DESC 关键字。
ASC关键字用于表示升序,但是默认排序就是升序的;DESC表示降序,需要指定。
组合应用:使用ORDER BY和LIMIT的组合,能够找出一个列中最高或最低的值。
# 语法
SELECT 查询列表 FROM 表名 ORDER BY 字段名 [DESC|ASC] LIMIT 1;
# 示例
select prod_price from products order by prod_price desc limit;#找出最昂贵物品的值
ORDER BY 子句的位置 在给出 ORDER BY 子句时,应该保证它位于 FROM 子句之后。如果使用 LIMIT ,它必须位于 ORDER BY之后。使用子句的次序不对将产生错误消息。
第6章 过滤数据
6.1 使用WHERE子句
在SELECT语句中,数据可以根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名(FROM子句)之后给出,语法如下:
SELECT 查询条件 FROM 表名 WHERE 筛选条件
WHERE子句的位置:在同时使用 ORDER BY 和 WHERE 子句时,应该让 ORDER BY 位于 WHERE 之后,否则将会产生错误。
6.2 WHERE子句操作符
WHERE子句支持的操作符如下:
何时使用引号:单引号用来限定字符串。如果将值与串类型(如varchar、char等)的列进行比较,则需要限定引号。用来与数值列(int、float等)进行比较的值不用引号。
注意:BETWEEN使用时必须指定两个值,即所需范围的低端值和高端值,比如age BETWEEN 18 AND 24,即年龄在18到24之间的所有记录。 BETWEEN 匹配范围中所有的值,包括指定的开始值和结束值。
IS NULL子句:SELECT 语句有一个特殊的 WHERE 子句,可用来检查具有 NULL 值的列。语法如下:
SELECT 查询列表 FROM 表名 WHERE 字段名 IS NULL;#查询某字段是否为NULL值的记录
SELECT 查询列表 FROM 表名 WHERE 字段名 IS NOT NULL;#查询某字段是否不为NULL值的记录
第7章 数据过滤
7.1 组合WHERE子句
即多条件过滤数据,MySQL允许给出多个WHERE子句,这些子句有两种使用方式:AND子句或OR子句。
7.1.1 AND操作符
表示不止一个条件进行过滤,语法如下:
SELECT 查询列表 FROM 表名 WHERE 筛选条件 AND 筛选条件 ....
必须同时满足AND左右两侧的所有筛选条件。
7.1.2 OR操作符
OR操作符表示检索匹配任一条件的行,即只要有一个条件成立就可以检索需要的数据。
SELECT 查询列表 FROM 表名 WHERE 筛选条件 OR 筛选条件 ....
只需要满足OR左右两侧的任一条件。
7.1.3 计算次序
WHERE 可包含任意数目的 AND 和 OR 操作符。允许两者结合以进行复杂和高级的过滤。
引出问题:同时存在AND和OR操作符时,该如何查询?
问题原因:SQL(像多数语言一样)在处理 OR 操作符前,优先处理 AND 操作符。
解决问题:此问题的解决方法是使用圆括号明确地分组相应的操作符。示例如下:
SELECT 查询列表 FROM 表名 WHERE (筛选条件 OR 筛选条件) AND 筛选条件
因为圆括号具有较 AND 或 OR 操作符高的计算次序,DBMS首先过滤圆括号内的 OR 条件。
在WHERE子句中使用圆括号:任何时候使用具有 AND 和 OR 操作符的 WHERE 子句,都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序,即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处,它能消除歧义。
7.2 IN操作符
圆括号在 WHERE 子句中还有另外一种用法。 IN 操作符用来指定条件范围,范围中的每个条件都可以进行匹配。 IN 取合法值的由逗号分隔的清单,全都括在圆括号中。
SELECT 查询列表 FROM 表名 WHERE 字段名 IN (值1,值2,...)
其实等价于OR操作符的功能:
SELECT 查询列表 FROM 表名 WHERE 字段名=值1 OR 字段名=值2 OR ...
# 注意,这里的字段名是同一个字段
为什么要使用 IN 操作符?其优点具体如下。
- 在使用长的合法选项清单时, IN 操作符的语法更清楚且更直观。
- 在使用 IN 时,计算的次序更容易管理(因为使用的操作符更少)。
- IN 操作符一般比 OR 操作符清单执行更快。
- IN 的最大优点是可以包含其他 SELECT 语句,使得能够更动态地建立 WHERE 子句。
7.3 NOT操作符
WHERE 子句中的 NOT 操作符有且只有一个功能,那就是否定它之后所跟的任何条件。
例如:
# 查询不在IN列表里的所有记录
SELECT 查询列表 FROM 表名 WHERE 字段名 NOT IN (值1,值2,...)
为什么使用 NOT ?对于简单的 WHERE 子句,使用 NOT 确实没有什么优势。但在更复杂的子句中, NOT 是非常有用的。例如,在与 IN 操作符联合使用时, NOT 使找出与条件列表不匹配的行非常简单。
MySQL中的NOT :MySQL支持使用 NOT 对 IN 、 BETWEEN 和EXISTS子句取反,这与多数其他 DBMS允许使用 NOT 对各种条件取反有很大的差别。
- NOT IN:表示不在IN列表中的所有记录
- NOT BETWEEN:表示不在某两个值范围之内的所有记录
- NOT EXISTS:表示不存在某条件的记录
第8章 用通配符进行过滤
8.1 LIKE操作符
问题提出:前面过滤数据的方法条件值都是已知的,如果要查找某字符串中包含"abc"的所有记录,就需要用到通配符了。
通配符(wildcard) 用来匹配值的一部分的特殊字符。
搜索模式(search pattern)由字面值、通配符或两者组合构成的搜索条件。
通配符实际上是SQL的WHERE子句中具有特殊含义的字符。
为了能够在搜索子句中使用通配符,必须使用LIKE操作符。
8.1.1 百分号(%)通配符
最常使用的通配符是百分号( % )。在搜索串中, % 表示任何字符出现任意次数。
语法如下:
SELECT 查询列表 FROM 表名 WHERE 字段名 LIKE '%关键字%';
区分大小写 根据MySQL的配置方式,搜索可以是区分大小写的。如果区分大小写, 'jet%' 与 JetPack 1000 将不匹配。
通配符可在搜索模式中任意位置使用,并且可以使用多个通配符。
除了一个或多个字符外, % 还能匹配0个字符。 %代表搜索模式中给定位置的0个、1个或多个字符。
注意NULL 虽然似乎 % 通配符可以匹配任何东西,但有一个例外,即 NULL 。即使是 WHERE prod_name LIKE '%' 也不能匹配用值 NULL 作为产品名的行。
8.1.2 下划线(_)通配符
另一个有用的通配符是下划线( _ )。下划线的用途与 % 一样,但下划线只匹配单个字符而不是多个字符。
SELECT 查询列表 FROM 表名 WHERE 字段名 LIKE '_关键字';
与 % 能匹配0个字符不一样,_总是匹配一个字符,不能多也不能少。
8.2 使用通配符的技巧
使用通配符的技巧:
- 不要过度使用通配符。如果其他操作符能达到相同的目的,应该使用其他操作符。
- 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处(如'%关键字')。把通配符置于搜索模式的开始处,搜索起来是最慢的。
- 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。
第9章 用正则表达式进行搜索
9.1 正则表达式介绍
正则表达式是用来匹配文本的特殊的串(字符集合)。
- 如果你想从一个文本文件中提取电话号码,可以使用正则表达式。
- 如果你需要查找名字中间有数字的所有文件,可以使用一个正则表达式。
- 如果你想在一个文本块中找到所有重复的单词,可以使用一个正则表达式。
- 如果你想替换一个页面中的所有URL为这些URL的实际HTML链接,也可以使用一个正则表达式。
9.2 使用MySQL正则表达式
9.2.1 基本字符匹配
语法:
SELECT 查询列表 FROM 表名 WHERE 字段名 REGEXP '正则表达式';
示例:
SELECT * from user WHERE username REGEXP '.三';
# 匹配username字段中所有第二个字符是'三'的记录
. 是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符。
LIKE与REGEXP的区别:
SELECT * FROM user WHERE username LIKE '三';
SELECT * FROM user WHERE username REGEXP '三';
如果执行上述两条语句,会发现第一条语句不返回数据,而第二条语句返回所匹配的记录。为什么?
LIKE 匹配整个列。如果被匹配的文本在列值中出现, LIKE 将不会找到它,相应的行也不被返回(除非使用通配符)。而 REGEXP 在列值内进行匹配,如果被匹配的文本在列值中出现, REGEXP 将会找到它,相应的行将被返回。这是一个非常重要的差别。
匹配不区分大小写 MySQL中的正则表达式匹配(自版本3.23.4后)不区分大小写(即,大写和小写都匹配)。为区分大小写,可使用 BINARY 关键字,如 WHERE prod_name REGEXP BINARY 'JetPack .000' 。
9.2.2 进行OR匹配
为搜索两个串之一(或者为这个串,或者为另一个串),使用 |。语法如下:
SELECT 查询列表 FROM 表名 WHERE 字段名 REGEXP '值1|值2|...';
示例:
SELECT * FROM user WHERE username REGEXP '三|四';
# 检索username字段中待有'三'或'四'的记录
使用 | 从功能上类似于在 SELECT 语句中使用 OR 语句,多个 OR 条件可并入单个正则表达式。
两个以上的 OR 条件 可以给出两个以上的 OR 条件。例如,'1000 | 2000 | 3000' 将匹配 1000 或 2000 或 3000 。
9.2.3 匹配几个字符之一
匹配任何单一字符。但是,如果你只想匹配特定的字符,怎么办?可通过指定一组用 [ 和 ] 括起来的字符来完成。
SELECT 查询列表 FROM 表名 WHERE 字段名 REGEXP '[值1值2值3...]关键字';
示例:
SELECT * FROM user WHERE username REGEXP '[张李]三';
# 匹配username中第一个字符是'张'或'李',第二个字符是'三'的所有记录
# 等价于
SELECT * FROM user WHERE username REGEXP '[张|李]三';
这里使用了正则表达式[张李]三。其中[张李]定义一组字符,它的意思是匹配'张'或'李',因为'张三'和'李三'都匹配且返回,而且[ ]组中几个值不需要用逗号进行分隔开。
正如所见, [ ] 是另一种形式的 OR 语句。事实上,正则表达式 [123]Ton为 [1|2|3]Ton 的缩写,也可以使用后者。
但如果使用REGEXP '1|2|3 Ton',那么匹配的是'1'或'2'或'3 Ton'。
因此除非把字符 | 括在一个集合中,否则它将应用于整个串。
字符集合也可以被否定,即,它们将匹配除指定字符外的任何东西。为否定一个字符集,在集合的开始处放置一个 ^ 即可。因此,尽管 [123]匹配字符 1 、 2 或 3 ,但 [^123] 却匹配除这些字符外的任何东西。
9.2.4 匹配范围
集合可用来定义要匹配的一个或多个字符。例如,下面的集合将匹配数字0到9:[0123456789]。
为简化这种类型的集合,可使用 - 来定义一个范围。下面的式子功能上等同于上述数字列表:[0-9]
范围不限于完整的集合, [1-3] 和 [6-9] 也是合法的范围。此外,范围不一定只是数值的, [a-z] 匹配任意字母字符。
例如:
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton';
9.2.5 匹配特殊字符
如果要匹配如下这些特殊字符该怎么办呢?
. [ ] | -
select * from user where username REGEXP '.';的语句是无效的。
为了匹配特殊字符,必须用 \\ 为前导。 \\- 表示查找 - , \\. 表示查找 . 。
select * from user where username REGEXP '\\.';这样的语句才能查找成功。
这种处理就是所谓的转义(escaping),正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括 . 、 | 、 [] 以及迄今为止使用过的其他特殊字符。
\\也可以用来引用元字符(即具有特殊含义的字符)
匹配 \ 为了匹配反斜杠( \ )字符本身,需要使用 \\\\ 。
\ 或 \\? 多数正则表达式实现使用单个反斜杠转义特殊字符,以便能使用这些字符本身。但MySQL要求两个反斜杠(MySQL自己解释一个,正则表达式库解释另一个)。
9.2.6 匹配字符类
存在找出你自己经常使用的数字、所有字母字符或所有数字字母字符等的匹配。为更方便工作,可以使用预定义的字符集,称为字符类(character class)。
9.2.7 匹配多个实例
目前为止使用的所有正则表达式都试图匹配单次出现。如果存在一个匹配,该行被检索出来,如果不存在,检索不出任何行。但有时需要对匹配的数目进行更强的控制。例如,你可能需要寻找所有的数,不管数中包含多少数字,或者你可能想寻找一个单词并且还能够适应一个尾随的 s (如果存在),等等。
这可以用表9-3列出的正则表达式重复元字符来完成。
例如正则表达式 \\([0-9] sticks?\\) 需要解说一下。 \\( 匹配 ),[0-9] 匹配任意数字(这个例子中为1和5), sticks? 匹配 stick和 sticks ( s 后的 ? 使 s 可选,因为 ? 匹配它前面的任何字符的0次或1次出现), \\) 匹配 ) 。没有 ? ,匹配 stick 和 sticks 会非常困难。
select * from user where username REGEXP '[[:digit:]]{4}';表示匹配连在一起的4个数字。如前所述, [:digit:] 匹配任意数字,因而它为数字的一个集合。 {4} 确切地要求它前面的字符(任意数字)出现4次,所以[[:digit:]]{4} 匹配连在一起的任意4位数字。
需要注意的是,在使用正则表达式时,编写某个特殊的表达式几乎总是有不止一种方法。上面的例子也可以如下编写:REGEXP '[0-9][0-9][0-9][0-9]'。
9.2.8 定位符
目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本,需要使用表9-4列出的定位符。
例如,如果你想找出以一个数(包括以小数点开始的数)开始的所有产品,怎么办?简单搜索 [0-9\\.] (或 [[:digit:]\\.] )不行,因为它将在文本内任意位置查找匹配。解决办法是使用 ^ 定位符,如下所示:REGEXP '^[0-9\\.]'。^ 匹配串的开始。因此, ^[0-9\\.] 只在 . 或任意数字为串中第
一个字符时才匹配它们。
^ 的双重用途 ^: 有两种用法。在集合中(用 [ 和 ] 定义),用它来否定该集合,否则,用来指串的开始处。
使 REGEXP 起类似 LIKE 的作用 : LIKE 和 REGEXP的不同在于, LIKE 匹配整个串而 REGEXP 匹配子串。利用定位符,通过用 ^ 开始每个表达式,用 $ 结束每个表达式,可以使
REGEXP 的作用与 LIKE 一样。
简单的正则表达式测试 可以在不使用数据库表的情况下用SELECT 来测试正则表达式。 REGEXP 检查总是返回 0 (没有匹配)或 1 (匹配)。可以用带文字串的 REGEXP 来测试表达式,并试
验它们。相应的语法如下:SELECT 'hello' REGEXP '[0-9]';。这个例子显然将返回 0 (因为文本 hello 中没有数字)。
第10章 创建计算字段
10.1 计算字段
存储在数据库表中的数据一般不是应用程序所需要的格式。下面举几个例子。
- 如果想在一个字段中既显示公司名,又显示公司的地址,但这两个信息一般包含在不同的表列中。
- 城市、州和邮政编码存储在不同的列中(应该这样),但邮件标签打印程序却需要把它们作为一个恰当格式的字段检索出来。
- 列数据是大小写混合的,但报表程序需要把所有数据按大写表示出来。
- 物品订单表存储物品的价格和数量,但不需要存储每个物品的总价格(用价格乘以数量即可)。为打印发票,需要物品的总价格。
- 需要根据表数据进行总数、平均数计算或其他计算。
在上述每个例子中,存储在表中的数据都不是应用程序所需要的。我们需要直接从数据库中检索出转换、计算或格式化过的数据;而不是检索出数据,然后再在客户机应用程序或报告程序中重新格式化。
计算字段并不实际存在于数据库表中。计算字段是运行时在 SELECT 语句内创建的。
字段(field): 基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上。
10.2 拼接字段
拼接(concatenate) 将值联结到一起构成单个值。比如姓和名连接到一起就是姓名。
在MySQL的 SELECT 语句中,可使用Concat() 函数来拼接两个列。
MySQL的不同之处 多数DBMS使用 + 或 || 来实现拼接,MySQL则使用 Concat() 函数来实现。当把SQL语句转换成MySQL语句时一定要把这个区别铭记在心。
SELECT CONCAT(字段或字符串, 字段或字符串, ...) FROM 表名 WHERE 筛选条件;
Trim 函数 MySQL除了支持 RTrim() (正如刚才所见,它去掉串右边的空格),还支持 LTrim() (去掉串左边的空格)以及Trim() (去掉串左右两边的空格)。
别名(alias)是一个字段或值的替换名。别名用 AS 关键字赋予。
SELECT 字段名 AS 别名 FROM 表名;
别名的其他用途 别名还有其他用途。常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它,在原来的名字含混或容易误解时扩充它,等等。
导出列 别名有时也称为导出列(derived column),不管称为什么,它们所代表的都是相同的东西。
10.3 执行算术运算
计算字段的另一常见用途是对检索出的数据进行算术计算。如加减乘除等。
MySQL支持表10-1中列出的基本算术操作符。此外,圆括号可用来区分优先顺序。
如何测试计算 SELECT :提供了测试和试验函数与计算的一个很好的办法。虽然 SELECT 通常用来从表中检索数据,但可以省略 FROM 子句以便简单地访问和处理表达式。例如, SELECT 3*2; 将返回 6 , SELECT Trim('abc'); 将返回 abc ,而 SELECT Now() 利用 Now() 函数返回当前日期和时间。通过这些例子,可以明白如何根据需要使用 SELECT 进行试验。
第11章 使用数据处理函数
11.1 函数
如TRIM()函数去除空格。
函数没有SQL的可移植性强 因为不同的DBMS支持其他DBMS不支持的函数,有时差异还很大,所以移植性差。
11.2 使用函数
大多数SQL实现支持以下类型的函数:
- 用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数。
- 用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数。
- 用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性等)的日期和时间函数。
- 返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数。
11.2.1 文本处理函数
SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。 SOUNDEX 考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较。虽然SOUNDEX 不是SQL概念,但MySQL(就像多数DBMS一样)都提供对SOUNDEX 的支持。如下:
11.2.2 日期和时间处理函数
应该总是使用4位数字的年份 支持2位数字的年份,MySQL处理00-69为2000-2069,处理70-99为1970-1999。虽然它们可能是打算要的年份,但使用完整的4位数字年份更可靠,因为MySQL不必做出任何假定。
11.2.3 数值处理函数
数值处理函数仅处理数值数据。这些函数一般主要用于代数、三角或几何运算,因此没有串或日期 — 时间处理函数的使用那么频繁。
第12章 汇总数据
12.1 聚集函数
我们经常需要汇总数据而不用把它们实际检索出来,为此MySQL提供了专门的函数。使用这些函数,MySQL查询可用于检索数据,以便分析和报表生成。这种类型的检索例子有以下几种。
- 确定表中行数(或者满足某个条件或包含某个特定值的行数)。
- 获得表中行组的和。
- 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值。
聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数。
12.1.1 AVG()函数
AVG() 通过对表中行数计数并计算特定列值之和,求得该列的平均值。 AVG() 可用来返回所有列的平均值,也可以用来返回特定列或行的平均值。
SELECT AVG(列名) FROM 表名 WHERE 筛选条件;
# 例如:select AVG(price) from products where id=100;
只用于单个列 AVG()只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出。为了获得多个列的平均值,必须使用多个 AVG() 函数。
NULL值 AVG() 函数忽略列值为 NULL 的行。
12.1.2 COUNT()函数
COUNT() 函数进行计数。可利用 COUNT() 确定表中行的数目或符合特定条件的行的数目。COUNT() 函数有两种使用方式。
- 使用 COUNT(*) 对表中行的数目进行计数,不管表列中包含的是空值( NULL )还是非空值。
- 使用 COUNT(column) 对特定列中具有值的行进行计数,忽略NULL 值。
NULL 值 如果指定列名,则指定列的值为空的行被 COUNT()函数忽略,但如果 COUNT() 函数中用的是星号( * ),则不忽略。
12.1.3 MAX()函数
MAX() 返回指定列中的最大值。 MAX() 要求指定列名。
对非数值数据使用 MAX() 虽然 MAX() 一般用来找出最大的数值或日期值,但MySQL允许将它用来返回任意列中的最大值,包括返回文本列中的最大值。在用于文本数据时,如果数据按相应的列排序,则 MAX() 返回最后一行。
NULL值 MAX() 函数忽略列值为 NULL 的行。
12.1.4 MIN()函数
MIN() 的功能正好与 MAX() 功能相反,它返回指定列的最小值。与MAX() 一样, MIN() 要求指定列名。
对非数值数据使用MIN() MIN()函数与 MAX() 函数类似,MySQL允许将它用来返回任意列中的最小值,包括返回文本列中的最小值。在用于文本数据时,如果数据按相应的列排序,则 MIN() 返回最前面的行。
NULL值 MIN() 函数忽略列值为 NULL 的行。
12.1.5 SUM()函数
SUM() 用来返回指定列值的和(总计)。SUM() 也可以用来合计计算值。
在多个列上进行计算 利用标准的算术操作符,所有聚集函数都可用来执行多个列上的计算。
NULL 值 SUM() 函数忽略列值为 NULL 的行。
12.2 聚集不同值
以上5个聚集函数都可以如下使用:
- 对所有的行执行计算,指定 ALL 参数或不给参数(因为 ALL 是默认行为);
- 只包含不同的值,指定 DISTINCT 参数。
ALL 为默认 ALL 参数不需要指定,因为它是默认行为。如果不指定 DISTINCT ,则假定为 ALL 。
SELECT 聚集函数(DISTINCT 列名) FROM 表名 WHERE 筛选条件;
# 例如:select avg(distinct price) from products;
注意 如果指定列名,则 DISTINCT 只能用于 COUNT() 。 DISTINCT不能用于 COUNT(*),因此不允许使用COUNT(DISTINCT),否则会产生错误 。类似地, DISTINCT 必须使用列名,不能用于计算或表达式。
将 DISTINCT 用于 MIN() 和 MAX() 虽然 DISTINCT 从技术上可用于 MIN() 和 MAX() ,但这样做实际上没有价值。一个列中的最小值和最大值不管是否包含不同值都是相同的。
12.3 组合聚集函数
实际上 SELECT语句可根据需要包含多个聚集函数。例如:
SELECT COUNT(*) AS num_items,
MIN(price) AS price_min,
MAX(price) AS price_max,
AVG(price) AS price_avg
FROM products;
取别名 在指定别名以包含某个聚集函数的结果时,不应该使用表中实际的列名。虽然这样做并非不合法,但使用唯一的名字会使你的SQL更易于理解和使用(以及将来容易排除故障)。
第13章 分组数据
13.1 分组
例如要计算每个班的总分,就需要按班分组了。
分组允许把数据分为多个逻辑组,以便能对每个组进行聚集计算。
13.2 创建分组
分组是在 SELECT 语句的 GROUP BY 子句中建立的。例如:
SELECT SUM(score) FROM classes GROUP BY class_id;# 计算每个班的总分
在具体使用 GROUP BY 子句前,需要知道一些重要的规定:
- GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制。
- 如果在 GROUP BY 子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)。
- GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT 中使用表达式,则必须在GROUP BY 子句中指定相同的表达式。不能使用别名。
- 除聚集计算语句外, SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
- 如果分组列中具有 NULL 值,则 NULL 将作为一个分组返回。如果列中有多行 NULL 值,它们将分为一组。
- GROUP BY 子句必须出现在 WHERE 子句之后, ORDER BY 子句之前。
使用 ROLLUP 使用 WITH ROLLUP 关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组)的值,如下所示:
13.3 过滤分组
除了能用 GROUP BY 分组数据外,MySQL还允许过滤分组,规定包括哪些分组,排除哪些分组。
比如列出总分在100以上的班级。
WHERE 过滤指定的是行而不是分组。事实上, WHERE 没有分组的概念。
MySQL为此目的提供了另外的子句,那就是 HAVING 子句。 HAVING 非常类似于 WHERE 。事实上,目前为止所学过的所有类型的 WHERE 子句都可以用 HAVING 来替代。唯一的差别是WHERE 过滤行,而 HAVING 过滤分组。
总结:WHERE是分组前筛选;HAVING是分组后筛选。
HAVING 支持所有 WHERE 操作符 所学过的有关 WHERE 的所有这些技术和选项都适用于HAVING 。它们的句法是相同的,只是关键字有差别。
例如:
select sum(score),class_id from user group by class_id having sum(score)>100;
# 查看总分在100以上的班级
HAVING 和 WHERE 的差别 这里有另一种理解方法, WHERE 在数据分组前进行过滤, HAVING 在数据分组后进行过滤。这是一个重要的区别, WHERE 排除的行不包括在分组中。这可能会改变计算值,从而影响 HAVING 子句中基于这些值过滤掉的分组。
注意是可以同时使用WHERE和HAVING语句的。
SELECT 查询列表,聚合函数 FROM 表名 WHERE 筛选条件 GROUP BY 分组字段 HAVING 分组后的筛选条件;
13.4 分组和排序
虽然 GROUP BY 和 ORDER BY 经常完成相同的工作,但它们是非常不同的。表13-1汇总了它们之间的差别。
不要忘记 ORDER BY 一般在使用 GROUP BY 子句时,应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法。千万不要仅依赖 GROUP BY 排序数据。
SELECT 查询列表,聚合函数 FROM 表名 GROUP BY 分组字段 HAVING 分组后的筛选条件 ORDER BY 排序字段;
13.5 SELECT子句顺序
表13-2以在 SELECT 语句中使用时必须遵循的次序,列出迄今为止所学过的子句。
例如:
SELECT 查询列表,聚合函数
FROM 表名
WHERE 分组前的筛选条件
GROUP BY 分组字段
HAVING 分组后的筛选条件
ORDER BY 排序字段
LIMIT 起始索引,显示条数;
第14章 使用子查询
14.1 子查询
SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询。
14.2 利用子查询进行过滤
例如:
可见,在 WHERE 子句中使用子查询能够编写出功能很强并且很灵活的SQL语句。对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询。
虽然子查询一般与 IN 操作符结合使用,但也可以用于测试等于( = )、不等于( <> )等。
14.3 作为计算字段使用子查询
使用子查询的另一方法是创建计算字段。即使用聚合函数如COUNT等。
注意,这个子查询是在SELECT后面的,即是查询列表的一部分,而且使用到了WHERE orders.cust_id=customers.cust_id,这种涉及到外部查询的子查询。
相关子查询(correlated subquery) 涉及外部查询的子查询。这种类型的子查询称为相关子查询。任何时候只要列名可能有多义性,就必须使用这种语法(表名和列名由一个句点分隔)。如果不使用完全限定列名则查询失败:
逐渐增加子查询来建立查询 用子查询测试和调试查询很有技巧性,特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立(和测试)查询的最可靠的方法是逐渐进行,这与MySQL处理它们的方法非常相同。首先,建立和测试最内层的查询。然后,用硬编码数据建立和测试外层查询,并且仅在确认它正常后才嵌入子查询。这时,再次测试它。对于要增加的每个查询,重复这些步骤。这样做仅给构造查询增加了一点点时间,但节省了以后(找出查询为什么不正常)的大量时间,并且极大地提高了查询一开始就正常工作的可能性。
第15章 联结表
15.1 联结
SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表。
所谓的联结就是多表查询。
15.1.1 关系表
例如产品信息和供应商信息,应该用两张表来存储,而不是一张表。关系表的设计就是要保证把信息分解成多个表,一类数据一个表。各表通过某些常用的值(即关系设计中的关系(relational))互相关联。
外键(foreign key) 外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系。
关系数据库的可伸缩性远比非关系数据库要好。
可伸缩性(scale) 能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好(scale well)。
15.1.2 为什么要使用联结
如果数据存储在多个表中,怎样用单条 SELECT 语句检索出数据?
答案是使用联结。简单地说,联结是一种机制,用来在一条 SELECT语句中关联表,因此称之为联结。使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行。
总结:为了能够使用一条SQL语句完成多张表的查询。
15.2 创建联结
语法如下:
SELECT 查询列表 FROM 表1,表2 WHERE 联结条件;
例如:
完全限定列名 在引用的列可能出现二义性时,必须使用完全限定列名(用一个点分隔的表名和列名)。如果引用一个没有用表名限制的具有二义性的列名,MySQL将返回错误。
15.2.1 WHERE子句的重要性
利用 WHERE 子句建立联结关系似乎有点奇怪,但实际上,有一个很充分的理由。请记住,在一条 SELECT 语句中联结几个表时,相应的关系是在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情。在联结两个表时,你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。 WHERE 子句作为过滤条件,它只包含那些匹配给定条件(这里是联结条件)的行。没有WHERE 子句,第一个表中的每个行将与第二个表中的每个行配对,而不管它们逻辑上是否可以配在一起。
总之,WHERE子句是为了阻止笛卡儿积结果的产生,即不能让第一个表的每一行匹配第二个表的所有行。
笛卡儿积(cartesian product) 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
不要忘了 WHERE 子句 应该保证所有联结都有 WHERE 子句,否则MySQL将返回比想要的数据多得多的数据。同理,应该保证 WHERE 子句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据。
15.2.2 内部联结
目前为止所用的联结称为等值联结(equijoin),它基于两个表之间的相等测试。这种联结也称为内部联结。其实,对于这种联结可以使用稍微不同的语法来明确指定联结的类型。如下语法:
SELECT 查询列表 FROM 表1 INNER JOIN 表2 ON 联结条件;
使用哪种语法 ANSI SQL规范首选 INNER JOIN 语法。此外,尽管使用 WHERE 子句定义联结的确比较简单,但是使用明确的联结语法能够确保不会忘记联结条件,有时候这样做也能影响性能。推荐使用INNER JOIN...ON...
15.2.3 联结多个表
SQL对一条 SELECT 语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有表,然后定义表之间的关系。
SELECT 查询列表 FROM 表1,表2,表3,.. WHERE 联结条件1 AND 联结条件2 AND ...
性能考虑 MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的,因此应该仔细,不要联结不必要的表。联结的表越多,性能下降越厉害。
多做实验 正如所见,为执行任一给定的SQL操作,一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此,有必要对不同的选择机制进行实验,以找出最适合具体情况的方法。