MySQL必知必会阅读笔记(1-20章)

作者: _Cappuccino_ | 来源:发表于2019-07-23 16:07 被阅读0次

第1章了解SQL

1.1 数据库基础

1.1.1 什么是数据库

数据库(database) 保存有组织的数据的容器(通常是一个文件或一组文件).

1.1.2 表

表(table) 某种特定类型数据的结构化清单.
表名表名的唯一性取决于多个因素,如数据库名和表名等的结合.这表示,虽然在相同数据库中不能两次使用相同的表名, 但在不同的数据库中却可以使用相同的表名.

模式(schema) 关于数据库和表的布局及特性的信息.

1.1.3 列和数据类型

列(column) 表中的一个字段.所有表都是由一个或多个列组成的.
数据类型(datatype) 所容许的数据的类型.每个表列都有相应的数据类型,它限制(或容许)该列中存储的数据.

1.1.4 行

行(row) 表中的一个记录.

1.1.5 主键

主键(primary key)一列(或一组列),其值能够唯一区分表中每个行.
唯一标识表中每行的这个列(或这组列)称为主键.主键用来表示一个特定的行.没有主键,更新或删除表中特定行很困难,因为没有安全的方法保证只涉及相关的行.
应该总是定义主键

表中的任何列都可以作为主键,只要它满足以下条件:

任意两行都不具有相同的主键值;
每个行都必须具有一个主键值(主键列不允许NULL值).

主键的最好习惯除MySQL强制实施的规则外,应该坚持的几个普遍认可的最好习惯为:

不更新主键列中的值;
不重用主键列的值;
不在主键列中使用可能会更改的值.

1.2 什么是SQL

SQL(发音为字母S-Q-L或sequel)是结构化查询语言(Structured Query Language)的缩写.SQL是一种专门用来与数据库通信的语言.

SQL有如下的优点.

SQL不是某个特定数据库供应商专有的语言.几乎所有重要的DBMS都支持SQL,所以,学习此语言使你几乎能与所有数据库打交道.
SQL简单易学.它的语句全都是由描述性很强的英语单词组成,而且这些单词的数目不多.
SQL尽管看上去很简单,但它实际上是一种强有力的语言,灵活使用其语言元素,可以进行非常复杂和高级的数据库操作.

1.3 动手实践

1.4 小结

第2章 MySQL简介

2.1 什么是MySQL

数据的所有存储、检索、管理和处理实际上是由数据库软件——DBMS(数据库管理系统)完成的.MySQL是一种DBMS,即它是一种数据库软件.
MySQL已经存在很久了,它在世界范围内得到了广泛的安装和使用. 为什么有那么多的公司和开发人员使用MySQL?以下列出其原因.

成本——MySQL是开放源代码的,一般可以免费使用(甚至可以免费修改).
性能——MySQL执行很快(非常快).
可信赖——某些非常重要和声望很高的公司、站点使用MySQL,这些公司和站点都用MySQL来处理自己的重要数据.
简单——MySQL很容易安装和使用.

2.1.1 客户机—服务器软件

DBMS可分为两类:一类为基于共享文件系统的DBMS,另一类为基于客户机—服务器的DBMS.

2.1.2 MySQL版本

2.2 MySQL工具

2.2.1 mysql命令行实用程序

每个MySQL安装都有一个名为mysql的简单命令行实用程序.

Mysql命令行中的一些注意事项和规则

命令输入在mysql>之后;
命令用;或\g结束,换句话说,仅按Enter不执行命令;
输入help或\h获得帮助,也可以输入更多的文本获得特定命令的帮助(如,输入help select获得使用SELECT语句的帮助);
输入quit或exit退出命令行实用程序.

2.2.2 MySQL Administrator

MySQL Administrator(MySQL管理器)是一个图形交互客户机,用来简化MySQL服务器的管理.

MySQL Administrator提示输入服务器和登录信息(并且允许你保存服务器定义供以后选择),然后显示允许选择不同视图的图标.其中:

Server Information(服务器信息)显示客户机和被连接的服务器的状态和版本信息;
Service Control(服务控制)允许停止和启动MySQL以及指定服务器特性;
User Administration(用户管理)用来定义MySQL用户、登录和权限;
Catalogs(目录)列出可用的数据库并允许创建数据库和表.

2.2.3 MySQL Query Browser

MySQL Query Browser为一个图形交互客户机,用来编写和执行 MySQL命令.

MySQL Query Browser要求输入服务器和登录信息(在MySQL Query Browser和MySQL Administrator之间共享保存的定义),然后显示应用界面.注意下面几点.

输入MySQL命令到屏幕顶上的窗口中.在输入语句后,单击 Execute按钮把它提交给MySQL处理.
结果(如果有)显示在屏幕左边的大区域网格中.
多条语句和结果显示在它们自己的标签中,并且允许快速切换. * 屏幕右边是一个标签,它列出所有可能的数据源(这里称为大纲),展开任一数据源查看它的表,展开任一个表查看它的列. 你还可以选择表和列让MySQL Query Browser为你编写MySQL语
句.
Schemata(大纲)标签的右边是一个History(历史)标签,它保持MySQL语句的执行历史.在需要测试不同版本的MySQL语句时,它非常有用.
关于MySQL语法、函数等的帮助可在屏幕右下角得到.

2.3 小结

第3章使用MySQL

3.1 连接

MySQL与所有客户机—服务器DBMS一样,要求在能执行命令之前登录到DBMS.登录名可以与网络登录名不相同(假定你使用网络).MySQL 在内部保存自己的用户列表,并且把每个用户与各种权限关联起来.

为了连接到MySQL,需要以下信息:

主机名(计算机名)——如果连接到本地MySQL服务器,为localhost;
端口(如果使用默认端口3306之外的端口);
一个合法的用户名;
用户口令(如果需要).
在连接之后,你就可以访问你的登录名能够访问的任意数据库和表了.

3.2 选择数据库

关键字(key word) 作为MySQL语言组成部分的一个保留字.决不要用关键字命名一个表或列.

为了使用crashcourse数据库,应该输入以下内容: use crashcourse

记住,必须先使用USE打开数据库,才能读取其中的数据.

3.3 了解数据库和表

SHOW DATABASES;返回可用数据库的一个列表.
SHOW TABLES;返回当前选择的数据库内可用表的列表.
SHOW COLUMNS要求给出一个表名(这个例子中的FROM customers),它对每个字段返回一行,行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息(如字段cust_id 的auto_increment).
DESCRIBE语句 MySQL支持用DESCRIBE作为SHOW COLUMNS FROM的一种快捷方式.换句话说,DESCRIBE customers;是 SHOW COLUMNS FROM customers;的一种快捷方式.
SHOW STATUS,用于显示广泛的服务器状态信息;
SHOW CREATE DATABASE和SHOW CREATE TABLE,分别用来显示创建特定数据库或表的MySQL语句;
SHOW GRANTS,用来显示授予用户(所有用户或特定用户)的安全权限;
SHOW ERRORS和SHOW WARNINGS,用来显示服务器错误或警告消息.
HELP SHOW:显示允许的SHOW语句.

3.4 小结

第4章检索数据

4.1 SELECT语句

SELECT语句:它的用途是从一个或多个表中检索信息.
为了使用SELECT检索表数据,必须至少给出两条信息——想选择什么,以及从什么地方选择.

4.2 检索单个列

结束SQL语句
多条SQL语句必须以分号(;)分隔.MySQL 如同多数DBMS一样,不需要在单条SQL语句后加分号.但特定的DBMS可能必须在单条SQL语句后加上分号.当然,如果愿意可以总是加上分号.事实上,即使不一定需要,但加上分号肯定没有坏处.如果你使用的是mysql命令行,必须加上分号来结束SQL语句.

SQL语句和大小写
请注意,SQL语句不区分大小写,因此 SELECT与select是相同的.同样,写成Select也没有关系. 许多SQL开发人员喜欢对所有SQL关键字使用大写,而对所有列和表名使用小写,这样做使代码更易于阅读和调试.

使用空格
在处理SQL语句时,其中所有空格都被忽略.SQL 语句可以在一行上给出,也可以分成许多行.多数SQL开发人员认为将SQL语句分成多行更容易阅读和调试.

例子: SELECT prod_name FROM products;

4.3 检索多个列

在SELECT关键字后给出多个列名,列名之间必须以逗号分隔.

例子: SELECT prod_name, prod_price FROM products;

4.4 检索所有列

SELECT语句还可以检索所有的列而不必逐个列出它们.这可以通过在实际列名的位置使用星号(*)通配符来达到

例子: SELECT * FROM products;

如果给定一个通配符(*),则返回表中所有列.列的顺序一般是列在表定义中出现的顺序.

使用通配符一般,除非你确实需要表中的每个列,否则最好别使用*通配符.

4.5 检索不同的行

DISTINCT 指示MySQL 只返回不同的值.

例子: SELECT DISTINCT vend_id FROM products;

不能部分使用DISTINCT DISTINCT关键字应用于所有列而不仅是前置它的列.
如果给出SELECT DISTINCT vend_id, prod_price,除非指定的两个列都不同,否则所有行都将被检索出来.

4.6 限制结果

LIMIT 返回第一行或前几行

例子:SELECT prod_name FROM products LIMIT 5;
LIMIT 5指示MySQL返回不多于5行.

例子:SELECT prod_name FROM products LIMIT 5,5;
LIMIT 5, 5指示MySQL返回从行5开始的5行.第一个数为开始位置,第二个数为要检索的行数.

行0 检索出来的第一行为行0而不是行1.因此,LIMIT1,1 将检索出第二行而不是第一行.

在行数不够时
LIMIT中指定要检索的行数为检索的最大行数.如果没有足够的行(例如,给出LIMIT 10, 5,但只有13 行),MySQL将只返回它能返回的那么多行.

知识点
MySQL 5的LIMIT语法 LIMIT 3, 4的含义是从行4开始的3 行还是从行3开始的4行?如前所述,它的意思是从行3开始的4 行,这容易把人搞糊涂. 由于这个原因,MySQL 5支持LIMIT的另一种替代语法.LIMIT 4 OFFSET 3意为从行3开始取4行,就像LIMIT 3, 4一样.

4.7 使用完全限定的表名

只是限定列名
例子:SELECT products.prod_name FROM products;

限定表明
例子:SELECT products.prod_name FROM crashcourse.products;

4.8 小结

第5章排序检索数据

5.1 排序数据

子句(clause) SQL语句由子句构成,有些子句是必需的,而有的是可选的.一个子句通常由一个关键字和所提供的数据组成.

为了明确地排序用SELECT语句检索出的数据,可使用ORDER BY子句. ORDER BY子句取一个或多个列的名字,据此对输出进行排序.

例子:SELECT prod_name FROM products ORDER BY prod_name;

通过非选择列进行排序通常,ORDERBY子句中使用的列将是为显示所选择的列.但是,实际上并不一定要这样,用非检索的列排序数据是完全合法的.

5.2 按多个列排序

为了按多个列排序,只要指定列名,列名之间用逗号分开即可.

例子: SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;

5.3 指定排序方向

数据排序不限于升序排序(从A到Z).这只是默认的排序顺序,还可以使用ORDER BY子句以降序(从Z到A)顺序排序.为了进行降序排序, 必须指定DESC关键字.与DESC相反的关键字是ASC(ASCENDING),在升序排序时可以指定它.

在多个列上降序排序 如果想在多个列上进行降序排序,必须对每个列指定DESC关键字.

例子: SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESE;
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESE prod_name ASC;

ORDERBY子句的位置
在给出ORDERBY子句时,应该保证它位于FROM子句之后.如果使用LIMIT,它必须位于ORDER BY 之后.使用子句的次序不对将产生错误消息.

5.4 小结

第6章过滤数据

6.1 使用WHERE子句

数据库表一般包含大量的数据,很少需要检索表中所有行.通常只会根据特定操作或报告的需要提取表数据的子集.只检索所需数据需要指定搜索条件(search criteria),搜索条件也称为过滤条件(filter condition).

在SELECT语句中,数据根据WHERE子句中指定的搜索条件进行过滤. WHERE子句在表名(FROM子句)之后给出

例子: SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;

WHERE子句的位置
在同时使用ORDERBY和WHERE子句时,应该让ORDER BY位于WHERE之后,否则将会产生错误

6.2 WHERE子句操作符

条件操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

6.2.1 检查单个值

例子:
SELECT prod_name, prod_price FROM products where prod_name='fuses';
SELECT prod_name, prod_price FROM products where prod_price<10;
SELECT prod_name, prod_price FROM products where prod_price<=10;

6.2.2 不匹配检查

<> 和 != 是等价的
例子:
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;
SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;

6.2.3 范围值检查

BETWEEN操作符:需要两个值,即范围的开始值和结束值.这两个值必须用AND关键字分隔.
BETWEEN匹配范围中所有的值,包括指定的开始值和结束值.
例子:
SELECT prod_name, prod_price FROM products where prod_price BETWEEN 5 AND 10;

6.2.4 空值检查

NULL 无值(no value),它与字段包含0、空字符串或仅仅包含空格不同.

SELECT语句有一个特殊的WHERE子句,可用来检查具有NULL值的列.这个WHERE子句就是IS NULL子句.

例子:
SELECT prod_name FROM products WHERE prod_price IS NULL;
SELECT cust_id FROM customers WHERE cust_email IS NULL;

NULL与不匹配
在通过过滤选择出不具有特定值的行时,你可能希望返回具有NULL值的行.但是,不行.因为未知具有特殊的含义,数据库不知道它们是否匹配,所以在匹配过滤或不匹配过滤时不返回它们.
因此,在过滤数据时,一定要验证返回数据中确实给出了被过滤列具有NULL的行.

6.3 小结

第7章数据过滤

7.1 组合WHERE子句

之前的所有WHERE子句在过滤数据时使用的都是单一的条件.为了进行更强的过滤控制,MySQL允许给出多个WHERE子句.这些子句可以两种方式使用:以AND子句的方式或OR子句的方式使用.

操作符(operator) 用来联结或改变WHERE子句中的子句的关键字.也称为逻辑操作符(logical operator).

7.1.1 AND操作符

为了通过不止一个列进行过滤,可使用AND操作符给WHERE子句附加条件.
添加多个过滤条件,每添加一条就要使用一个AND.

例子:
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id=1003 AND prod_price<=10;

7.1.2 OR操作符

OR操作符与AND操作符不同,它指示MySQL检索匹配任一条件的行.
OR WHERE子句中使用的关键字,用来表示检索匹配任一给定条件的行.

例子:
SELECT prod_name, prod_price FROM products WHERE vend_id=1002 OR vend_id=1003;

7.1.3 计算次序

WHERE可包含任意数目的AND和OR操作符.允许两者结合以进行复杂和高级的过滤.
SQL(像多数语言一样)在处理OR操作符前,优先处理AND操作符.

在WHERE子句中使用圆括号
因为圆括号具有较AND或OR操作符高的计算次序,DBMS首先过滤圆括号内的OR条件.
任何时候使用具有AND和OR操作符的WHERE子句,都应该使用圆括号明确地分组操作符.不要过分依赖默认计算次序,即使它确实是你想要的东西也是如此.使用圆括号没有什么坏处,它能消除歧义.

例子:
以下两个语句执行的结果是不同的

SELECT prod_name, prod_price FROM products WHERE vend_id=1002 OR vend_id=1003 AND prod_price >= 10;
意思是:由供应商1003制造的任何价格为10美元(含)以上的产品,或者由供应商1002制造的任何产品

SELECT prod_name, prod_price FROM products WHERE (vend_id=1002 OR vend_id=1003) AND prod_price >= 10;
意思是:由供应商1002或1003制造的且价格都在10美元(含)以上的任何产品

7.2 IN操作符

IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配.IN取合法值的由逗号分隔的清单,全都括在圆括号中.
IN操作符与OR的功能是相同的.

IN WHERE子句中用来指定要匹配值的清单的关键字,功能与OR相当.

为什么要使用IN操作符?其优点具体如下.

在使用长的合法选项清单时,IN操作符的语法更清楚且更直观.
在使用IN时,计算的次序更容易管理(因为使用的操作符更少).
IN操作符一般比OR操作符清单执行更快.
IN的最大优点是可以包含其他SELECT语句,使得能够更动态地建立WHERE子句.

例子:
以下两个语句是等价的
SELECT prod_name, prod_price FROM products where vend_id IN (1002, 1003) ORDER BY prod_name;

SELECT prod_name, prod_price FROM products where vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;

7.3 NOT操作符

NOT操作符有且只有一个功能,那就是否定它之后所跟的任何条件.
NOT WHERE子句中用来否定后跟条件的关键字.

例子:
SELECT prod_name, prod_price FROM products where vend_id NOT IN (1002, 1003) ORDER BY prod_name;

为什么使用NOT?
对于简单的WHERE子句,使用NOT确实没有什么优势.但在更复杂的子句中,NOT是非常有用的.
例如,在与IN操作符联合使用时,NOT使找出与条件列表不匹配的行非常简单.

MySQL中的NOT
MySQL支持使用NOT对IN、BETWEEN和 EXISTS子句取反,这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别.

7.4 小结

第8章用通配符进行过滤

8.1 LIKE操作符

通配符(wildcard) 用来匹配值的一部分的特殊字符.
搜索模式(search pattern) 由字面值、通配符或两者组合构成的搜索条件.

为在搜索子句中使用通配符,必须使用LIKE操作符.
LIKE指示MySQL,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较.

谓词操作符何时不是操作符?答案是在它作为谓词(predi-cate)时.
从技术上说,LIKE是谓词而不是操作符.虽然最终的结果是相同的,但应该对此术语有所了解,以免在SQL文档中遇到此术语时不知道.

8.1.1 百分号(%)通配符

区分大小写根据MySQL的配置方式,搜索可以是区分大小写的.

最常使用的通配符是百分号(%).在搜索串中,%表示任何字符出现任意次数.
% 代表搜索模式中给定位置的0个、1个或多个字符.

通配符可在搜索模式中任意位置使用,并且可以使用多个通配符.

例子:
找出所有以词jet起头的产品
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%';
找出所有包含anvi词的产品
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '%anvil%';
找出以s起头以e结尾的所有产品
select prod_name FROM products LIKE 's%e';

注意NULL
虽然似乎%通配符可以匹配任何东西,但有一个例外,即NULL.
即使是WHERE prod_name LIKE '%'也不能匹配用值NULL作为产品名的行.

8.1.2 下划线(_)通配符

另一个有用的通配符是下划线().
下划线的用途与%一样,但下划线只匹配单个字符而不是多个字符.
与%能匹配0个字符不一样,总是匹配一个字符,不能多也不能少.

例子:
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ton anvil';

8.2 使用通配符的技巧

用通配符要记住的技巧.

不要过度使用通配符.如果其他操作符能达到相同的目的,应该使用其他操作符.
在确实需要使用通配符时,除非绝对有必要,否则不要把它们用在搜索模式的开始处.把通配符置于搜索模式的开始处,搜索起来是最慢的.
仔细注意通配符的位置.如果放错地方,可能不会返回想要的数据.
总之,通配符是一种极重要和有用的搜索工具,以后我们经常会用到它.

8.3 小结

第9章用正则表达式进行搜索

9.1 正则表达式介绍

正则表达式是用来匹配文本的特殊的串(字符集合).
正则表达式用正则表达式语言来建立,正则表达式语言是用来完成刚讨论的所有工作以及更多工作的一种特殊语言.与任意语言一样,正则表达式具有你必须学习的特殊的语法和指令.

9.2 使用MySQL正则表达式

MySQL仅支持多数正则表达式实现的一个很小的子集.

9.2.1 基本字符匹配

例子:
检索列prod_name包含文本1000的所有行
SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;

LIKE与REGEXP
除关键字LIKE被REGEXP替代外,这条语句看上去非常像使用LIKE的语句
LIKE与REGEXP 在LIKE和REGEXP之间有一个重要的差别.请看以下两条语句:
LIKE方式:SELECT prod_name FROM products WHERE prod_name LIKE '1000' ORDER BY prod_name;
REGEXP方式:SELECT prod_name FROM products WHERE prod_name REGEXP '1000' ORDER BY prod_name;
如果执行上述两条语句,会发现第一条语句不返回数据,而第二条语句返回一行.为什么?
正如第8章所述,LIKE匹配整个列.如果被匹配的文本在列值中出现,LIKE将不会找到它,相应的行也不被返回(除非使用通配符).而REGEXP在列值内进行匹配,如果被匹配的文本在列值中出现,REGEXP将会找到它,相应的行将被返回.这是一个非常重要的差别.
那么,REGEXP能不能用来匹配整个列值(从而起与LIKE相同的作用)?答案是肯定的,使用^和$定位符(anchor)即可.

9.2.2 进行OR匹配

使用|从功能上类似于在SELECT语句中使用OR语句,多个OR条件可并入单个正则表达式.

两个以上的OR条件
可以给出两个以上的OR条件.例如, '1000 | 2000 | 3000'将匹配1000或2000或3000.

例子:
SELECT prod_name FROM products WHERE prod_name REGEXP '1000 | 2000' ORDER BY prod_name;

9.2.3 匹配几个字符之一

[]是另一种形式的OR语句.事实上,正则表达式[123]Ton 为[1|2|3]Ton的缩写,也可以使用后者.但是,需要用[]来定义OR语句查找什么.

字符集合也可以被否定,即,它们将匹配除指定字符外的任何东西. 为否定一个字符集,在集合的开始处放置一个^即可.因此,尽管[123] 匹配字符1、2或3,但[^123]却匹配除这些字符外的任何东西.

例子:
匹配1 Ton或者2 Ton或者3 Ton
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;
匹配1或者2或者3 Ton
SELECT prod_name FROM products WHERE prod_name REGEXP '1|2|3 Ton' ORDER BY prod_name;

9.2.4 匹配范围

集合可用来定义要匹配的一个或多个字符.
数字集合:[0-9]
字符集合:[a-z]

例子:
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name;

9.2.5 匹配特殊字符

为了匹配特殊字符,必须用\\为前导.这种处理就是所谓的转义(escaping),正则表达式内具有特殊意义的所有字符都必须以这种方式转义.这包括.、|、[]以及迄今为止使用过的其他特殊字符.

空白元字符

元字符	说明
\f	换页
\n	换行
\r	回车
\t	制表
\v	纵向制表

匹配\ 为了匹配反斜杠()字符本身,需要使用\.

9.2.6 匹配字符类

预定义的字符集,称为字符类

字符类

类	说明
[:alnum:]	任意字母和数字(同[a-zA-Z0-9])
[:alpha:]	任意字符(同[a-zA-Z])
[:blank:]	空格和制表(同[\t])
[:cntrl:]	ASCII控制字符(ASCII 0到31和127)
[:digit:]	任意数字(同[0-9])
[:graph:]	与[:print:]相同,但不包括空格
[:lower:]	任意小写字母(同[a-z])
[:print:]	任意可打印字符
[:punct:]	既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]	包括空格在内的任意空白字符(同[\f\n\r\t\v])
[:upper:]	任意大写字母(同[A-Z])
[:xdigit:]	任意十六进制数字(同[a-fA-F0-9])

9.2.7 匹配多个实例

目前为止使用的所有正则表达式都试图匹配单次出现.如果存在一个匹配,该行被检索出来,如果不存在,检索不出任何行.但有时需要对匹配的数目进行更强的控制.

重复元字符

字符	说明
*	0个或多个匹配
+	1个或多个匹配(等于{1,})
?	0个或1个匹配(等于{0,1})
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	匹配数目的范围(m不超过255)

例子:
SELECT prod_name FROM products WHERE prod_name REGEXP '$[0-9] sticks?$' ORDER BY prod_name;

SELECT prod_name FROM products WHERE prod_name REGEXP '[[:digit:]]{4}' ORDER BY prod_name;

SELECT prod_name FROM products WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]' ORDER BY prod_name;

9.2.8 定位符

定位元字符

元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

例子:
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9\']' ORDER BY prod_name;

^的双重用途 ^有两种用法.在集合中(用[和]定义),用它来否定该集合,否则,用来指串的开始处.

使REGEXP起类似LIKE的作用
本章前面说过,LIKE和REGEXP 的不同在于,LIKE匹配整个串而REGEXP匹配子串.
利用定位符,通过用^开始每个表达式,用$结束每个表达式,可以使REGEXP的作用与LIKE一样.

简单的正则表达式测试可以在不使用数据库表的情况下用 SELECT来测试正则表达式.REGEXP检查总是返回0(没有匹配) 或1(匹配).可以用带文字串的REGEXP来测试表达式,并试验它们.
相应的语法如下:SELECT 'hello REGEXP '[0-9]';
这个例子显然将返回0(因为文本hello中没有数字).

9.3 小结

第10章创建计算字段

10.1 计算字段

字段(field) 基本上与列(column)的意思相同,经常互换使用,不过数据库列一般称为列,而术语字段通常用在计算字段的连接上.

客户机与服务器的格式
可在SQL语句内完成的许多转换和格式化工作都可以直接在客户机应用程序内完成.但一般来说,在数据库服务器上完成这些操作比在客户机中完成要快得多,因为DBMS是设计来快速有效地完成这种处理的.

10.2 拼接字段

拼接(concatenate) 将值联结到一起构成单个值.

在MySQL的SELECT语句中,可使用Concat()函数来拼接两个列.

Concat()拼接串,即把多个串连接起来形成一个较长的串.

Concat()需要一个或多个指定的串,各个串之间用逗号分隔.

MySQL的不同之处
多数DBMS使用+或||来实现拼接, MySQL则使用Concat()函数来实现.当把SQL语句转换成 MySQL语句时一定要把这个区别铭记在心.

例子:
SELECT Concat(vend_name, '(', vend_country, ')') FROM vendors ORDER BY vend_name;

RTrim()函数去掉值右边的所有空格.通过使用RTrim(),各个列都进行了整理.

Trim函数 MySQL除了支持RTrim()(正如刚才所见,它去掉串右边的空格),还支持LTrim()(去掉串左边的空格)以及 Trim()(去掉串左右两边的空格).

例子:
SELECT Concat(Rtrim(vend_name, '(', Rtrim(vend_coutry), ')') FROM vendors ORDER BY vend_name;

别名(alias)是一个字段或值的替换名.别名用AS关键字赋予.

导出列别名有时也称为导出列(derivedcolumn),不管称为什么,它们所代表的都是相同的东西.

别名的其他用途
别名还有其他用途.常见的用途包括在实际的表列名包含不符合规定的字符(如空格)时重新命名它,在原来的名字含混或容易误解时扩充它,等等.

例子:
SELECT Concat(Rtrim(vend_name, '(', Rtrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

10.3 执行算术计算

例子:
检索订单号20005中的所有物品
SELECT prod_id, quantity, item_price FROM orderitems WHERE order_num=2005;
汇总物品的价格(单价乘以订购数量):
SELECR prod_id, quantity, item_price quatity*item_price AS expanded_price FROM orderitems WHERE order_num=20005;

MySQL算术操作符

操作符	说明
+	加
-	减
*	乘
/	除

10.4 小结

第11章使用数据处理函数

11.1 函数

与其他大多数计算机语言一样,SQL支持利用函数来处理数据.函数一般是在数据上执行的,它给数据的转换和处理提供了方便.

函数没有SQL的可移植性强
能运行在多个系统上的代码称为可移植的(portable).相对来说,多数SQL语句是可移植的, 在SQL实现之间有差异时,这些差异通常不那么难处理.而函数的可移植性却不强.几乎每种主要的DBMS的实现都支持其他实现不支持的函数,而且有时差异还很大.
为了代码的可移植,许多SQL程序员不赞成使用特殊实现的功能.虽然这样做很有好处,但不总是利于应用程序的性能.如果不使用这些函数,编写某些应用程序代码会很艰难.必须利用其他方法来实现DBMS非常有效地完成的工作.
如果你决定使用函数,应该保证做好代码注释,以便以后你(或其他人)能确切地知道所编写SQL代码的含义.

11.2 使用函数

大多数SQL实现支持以下类型的函数.

用于处理文本串(如删除或填充值,转换值为大写或小写)的文本函数.
用于在数值数据上进行算术操作(如返回绝对值,进行代数运算)的数值函数.
用于处理日期和时间值并从这些值中提取特定成分(例如,返回两个日期之差,检查日期有效性等)的日期和时间函数.
返回DBMS正使用的特殊信息(如返回用户登录信息,检查版本细节)的系统函数.

11.2.1 文本处理函数

常用的文本处理函数

函数	说明
Left()	返回串左边的字符
Length()	返回串的长度
Locate()	找出串的一个子串
Lower()	将串转换为小写
LTrim()	去掉串左边的空格
Right()	返回串右边的字符
RTrim()	去掉串右边的空格
Soundex()	返回串的SOUNDEX值
SubString()	返回子串的字符
Upper()	将串转换为大写

SOUNDEX的解释
SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法.SOUNDEX考虑了类似的发音字符和音节,使得能对串进行发音比较而不是字母比较.虽然SOUNDEX不是SQL概念,但MySQL(就像多数DBMS一样)都提供对 SOUNDEX的支持.

例子:
SELECT vend_name, Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

SELECT cust_name, cust_contact FROM customers WHERE cust_contact = 'Y. Lie';

SELECT cust_name, cust_contact FROM customers WHERE Soundex(cust_contact) = Soundex('Y Lie');

11.2.2 日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储,以便能快速和有效地排序或过滤,并且节省物理存储空间.
一般,应用程序不使用用来存储日期和时间的格式,因此日期和间函数总是被用来读取、统计和处理这些值.由于这个原因,日期和时间函数在MySQL语言中具有重要的作用.

常用日期和时间处理函数

函数	说明
AddDate()	增加一个日期(天、周等)
AddTime()	增加一个时间(时、分等)
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期时间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Format()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部
DayOfWeek()	对于一个日期,返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个日期的月份部分
Now()	返回当前日期和时间
Second()	返回一个时间的秒部分
Time()	返回一个日期时间的时间部分
Year()	返回一个日期的年份部分

应该总是使用4位数字的年份
支持2位数字的年份,MySQL处理00-69为2000-2069,处理70-99为1970-1999.虽然它们可能是打算要的年份,但使用完整的4位数字年份更可靠,因为MySQL不必做出任何假定.

如果要的是日期,请使用Date()
如果你想要的仅是日期, 则使用Date()是一个良好的习惯,即使你知道相应的列只包含日期也是如此.这样,如果由于某种原因表中以后有日期和时间值,你的SQL代码也不用改变.当然,也存在一个Time() 函数,在你只想要时间时应该使用它.

例子:
SELECT cust_id, order_num FROM orders WHERE order_data='2005-09-01';
SELECT cust_id, order_num FROM orders WHERE Date(order_data)='2005-09-01';

SELECT cust_id, order_num FROM orders WHERE Date(order_data) BETWEEN '2005-09-01' AND '2005-09-30';

SELECT cust_id, order_num FROM orders WHERE Year(order_data)=2005 AND Month(order_date)=9;

11.2.3 数值处理函数

值处理函数仅处理数值数据.这些函数一般主要用于代数、三角或几何运算,因此没有串或日期—时间处理函数的使用那么频繁. 具有讽刺意味的是,在主要DBMS的函数中,数值函数是最一致最统一的函数.

常用数值处理函数

函数	说明
Abs()	返回一个数的绝对值
Cos()	返回一个角度的余弦
Exp()	返回一个数的指数值
Mod()	返回除操作的余数
Pi()	返回圆周率
Rand()	返回一个随机数
Sin()	返回一个角度的正弦
Sqrt()	返回一个数的平方根
Tan()	返回一个角度的正切

11.3 小结

第12章汇总数据

12.1 聚集函数

我们经常需要汇总数据而不用把它们实际检索出来,为此MySQL提供了专门的函数.使用这些函数,MySQL查询可用于检索数据,以便分析和报表生成.这种类型的检索例子有以下几种.
􏰀* 确定表中行数(或者满足某个条件或包含某个特定值的行数). 􏰀

获得表中行组的和.
􏰀找出表列(或所有行或某些特定的行)的最大值、最小值和平均值.

聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数.

SQL聚集函数

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

标准偏差 MySQL还支持一系列的标准偏差聚集函数,但本书并未涉及这些内容.

12.1.1 AVG()函数

AVG()通过对表中行数计数并计算特定列值之和,求得该列的平均值.AVG()可用来返回所有列的平均值,也可以用来返回特定列或行的平均值.
AVG()也可以用来确定特定列或行的平均值.
只用于单个列 AVG()只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出.为了获得多个列的平均值, 必须使用多个AVG()函数.
NULL值 AVG()函数忽略列值为NULL的行.

例子:
返回products表中所有产品的平均价格
SELECT AVG(prod_price) AS avg_price FROM products;
SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;

12.1.2 COUNT()函数

COUNT()函数进行计数.可利用COUNT()确定表中行的数目或符合特定条件的行的数目.

COUNT()函数有两种使用方式.
􏰀* 使用COUNT()对表中行的数目进行计数,不管表列中包含的是空值(NULL)还是非空值.
􏰀 使用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值.

NULL值
如果指定列名,则指定列的值为空的行被COUNT()函数忽略,但如果COUNT()函数中用的是星号(*),则不忽略.

例子:
返回customers表中客户的总数
SELECT COUNT(*) AS num_cust FROM customers;
对具有电子邮件地址的客户计数
SELECT COUNT(cust_email) AS num_cust FROM customers;

12.1.3 MAX()函数

MAX()返回指定列中的最大值.要求指定列名

对非数值数据使用MAX()
虽然MAX()一般用来找出最大的数值或日期值,但MySQL允许将它用来返回任意列中的最大值,包括返回文本列中的最大值.在用于文本数据时,如果数据按相应的列排序,则MAX()返回最后一行.
NULL值 MAX()函数忽略列值为NULL的行.

例子:
返回products表中最贵的物品的价格
SELECT MAX(prod_price) AS max_price FROM products;

12.1.4 MIN()函数

MIN()的功能正好与MAX()功能相反,它返回指定列的最小值.与MAX()一样,MIN()要求指定列名

对非数值数据使用MIN()
MIN()函数与MAX()函数类似, MySQL允许将它用来返回任意列中的最小值,包括返回文本列中的最小值.在用于文本数据时,如果数据按相应的列排序, 则MIN()返回最前面的行.
NULL值 MIN()函数忽略列值为NULL的行.

例子:
返回products表中最便宜物品的价格
SELECT MIN(prod_price) AS min_price FROM products;

12.1.5 SUM()函数

SUM()用来返回指定列值的和(总计).
SUM()也可以用来合计计算值.
在多个列上进行计算
如本例所示,利用标准的算术操作符, 所有聚集函数都可用来执行多个列上的计算.
NULL值 SUM()函数忽略列值为NULL的行.

例子:
检索所订购物品的总数(所有quantity值之和)
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num=2005;
合计每项物品的item_pricequantity,得出总的订单金额
SELECT SUM(item_pricequantity) AS total_price FROM orderitems WHERE order_num=2005;

12.2 聚集不同值

以上5个聚集函数都可以如下使用:
􏰀* 对所有的行执行计算,指定ALL参数或不给参数(因为ALL是默认行为);
􏰀* 只包含不同的值,指定DISTINCT参数.
ALL为默认 ALL参数不需要指定,因为它是默认行为.如果不指定DISTINCT,则假定为ALL.

注意
如果指定列名,则DISTINCT只能用于COUNT().DISTINCT不能用于COUNT(*),因此不允许使用COUNT(DISTINCT), 否则会产生错误.类似地,DISTINCT必须使用列名,不能用于计算或表达式.

将DISTINCT用于MIN()和MAX()
虽然DISTINCT从技术上可用于MIN()和MAX(),但这样做实际上没有价值.一个列中的最小值和最大值不管是否包含不同值都是相同的.

例子:
使用AVG()函数返回特定供应商提供的产品的平均价格
SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id=1003;

12.3 组合聚集函数

目前为止的所有聚集函数例子都只涉及单个函数.但实际上SELECT语句可根据需要包含多个聚集函数.

取别名
在指定别名以包含某个聚集函数的结果时,不应该使用表中实际的列名.虽然这样做并非不合法,但使用唯一的名字会使你的SQL更易于理解和使用(以及将来容易排除故障).

例子:
SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;

12.4 小结

第13章分组数据

13.1 数据分组

例子:
返回供应商1003提供的产品数目
SELECT COUNT(*) AS num_prods FROM products WHERE vend_id=1003;

13.2 创建分组

分组是在SELECT语句的GROUP BY子句中建立的.

在具体使用GROUP BY子句前,需要知道一些重要的规定.

GROUP BY子句可以包含任意数目的列.这使得能对分组进行嵌套, 为数据分组提供更细致的控制.
如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总.换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据).
GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数).如果在SELECT中使用表达式,则必须在 GROUP BY子句中指定相同的表达式.不能使用别名.
除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出.
如果分组列中具有NULL值,则NULL将作为一个分组返回.如果列中有多行NULL值,它们将分为一组.
GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前.

使用ROLLUP 使用WITHROLLUP关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组)的值,如下所示:
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

例子:
SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

13.3 过滤分组

除了能用GROUP BY分组数据外,MySQL还允许过滤分组,规定包括哪些分组,排除哪些分组.
那就是使用HAVING子句.HAVING非常类似于WHERE.事实上,目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代.唯一的差别是 WHERE过滤行,而HAVING过滤分组.

HAVING支持所有WHERE操作符
有关WHERE的所有这些技术和选项都适用于HAVING.它们的句法是相同的,只是关键字有差别.

HAVING和WHERE的差别
这里有另一种理解方法,WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤.这是一个重要的区别,WHERE排除的行不包括在分组中.这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组.

例子:
SELECT cust_id, COUNT() AS orders FROM orders GROUP BY cust_id HAVING COUNT()>=2;
返回过去12个月内具有两个以上订单的顾客
SELECT vend_id COUNT() AS num_prods FROM products WHERE prod_price>=10 ROUP BY vend_id HAVING COUNT()>=2;

SELECT vend_id COUNT() AS num_prods FROM products ORDER BY vend_id HAVING COUNT()>=2;

13.4 分组和排序

虽然GROUP BY和ORDER BY经常完成相同的工作,但它们是非常不同的.
ORDERBY与GROUPBY

ORDER BY	GROUP BY
排序产生的输出	分组行.但输出可能不是分组的顺序
任意列都可以使用(甚至非选择的列也可以使用)	只可能使用选择列或表达式列,而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列(或表达式),则必须使用

不要忘记ORDER BY
一般在使用GROUP BY子句时,应该也给出ORDER BY子句.这是保证数据正确排序的唯一方法.千万不要仅依赖GROUP BY排序数据.

例子:
检索总计订单价格大于等于50的订单的订单号和总计订单价格
SELECT order_num, SUM(quantityitem_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantityitem_price)>=50;

为按总计订单价格排序输出,需要添加ORDER BY子句
SELECT order_num, SUM(quantityitem_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantityitem_price)>=50 ORDER BY ordertotal;

13.5 SELECT子句顺序

SELECT子句及其顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

13.6 小结

第14章使用子查询

14.1 子查询

查询(query) 任何SQL语句都是查询.但此术语一般指SELECT 语句.
SELECT语句是SQL的查询.迄今为止我们所看到的所有SELECT语句都是简单查询,即从单个数据库表中检索数据的单条语句.
SQL还允许创建子查询(subquery),即嵌套在其他查询中的查询.

14.2 利用子查询进行过滤

格式化SQL
包含子查询的SELECT语句难以阅读和调试,特别是它们较为复杂时更是如此.如上所示把子查询分解为多行并且适当地进行缩进,能极大地简化子查询的使用.

例子:
分步查询：
第一步：对于prod_id为TNT2的所有订单物品,它检索其order_num列
SELECT order_nam FROM orderitems WHRE prod_id='TNT2';
第二步：具有订单20005和20007的客户ID
SELECT cust_id FROM orders WHERE order_num IN (20005, 20007);

等同于以下的嵌套子查询
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_nam FROM orderitems WHRE prod_id='TNT2');

SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2'));
为了执行上述SELECT语句,MySQL实际上必须执行3条SELECT
语句.最里边的子查询返回订单号列表,此列表用于其外面的子查询的WHERE子句.外面的子查询返回客户ID列表,此客户ID列表用于最外层查询的WHERE子句.最外层查询确实返回所需的数据.

可见,在WHERE子句中使用子查询能够编写出功能很强并且很灵活的 SQL语句.对于能嵌套的子查询的数目没有限制,不过在实际使用时由于性能的限制,不能嵌套太多的子查询.

列必须匹配
在WHERE子句中使用子查询(如这里所示),应该保证SELECT语句具有与WHERE子句中相同数目的列.通常,子查询将返回单个列并且与单个列匹配,但如果需要也可以使用多个列.

虽然子查询一般与IN操作符结合使用,但也可以用于测试等于(=)、不等于(<>)等.

子查询和性能
这里给出的代码有效并获得所需的结果.但是,使用子查询并不总是执行这种类型的数据检索的最有效的方法.

14.3 作为计算字段使用子查询

需要显示customers表中每个客户的订单总数.
为了执行这个操作,遵循下面的步骤.
(1) 从customers表中检索客户列表.
(2) 对于检索出的每个客户,统计其在orders表中的订单数目.
例子:
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE order.cust_id=customers.cust_id) AS orders FROM customers ORDER BY cust_name;

相关子查询(correlated subquery) 涉及外部查询的子查询.

虽然子查询在构造这种SELECT语句时极有用,但必须注意限制有歧义性的列名.

逐渐增加子查询来建立查询
用子查询测试和调试查询很有技巧性,特别是在这些语句的复杂性不断增加的情况下更是如此.用子查询建立(和测试)查询的最可靠的方法是逐渐进行, 这与MySQL处理它们的方法非常相同.首先,建立和测试最内层的查询.然后,用硬编码数据建立和测试外层查询,并且仅在确认它正常后才嵌入子查询.这时,再次测试它.对于要增加的每个查询,重复这些步骤.这样做仅给构造查询增加了一点点时间,但节省了以后(找出查询为什么不正常)的大量时间,并且极大地提高了查询一开始就正常工作的可能性.

14.4 小结

第15章联结表

15.1 联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表.联结是利用SQL的SELECT能执行的最重要的操作,很好地理解联结及其语法是学习SQL的一个极为重要的组成部分.

15.1.1 关系表

外键(foreignkey) 外键为某个表中的一列,它包含另一个表的主键值,定义了两个表之间的关系.
这样做的好处如下:

供应商信息不重复,从而不浪费时间和空间;
如果供应商信息变动,可以只更新vendors表中的单个记录,相关表中的数据不用改动;
由于数据无重复,显然数据是一致的,这使得处理数据更简单.
总之,关系数据可以有效地存储和方便地处理.因此,关系数据库的可伸缩性远比非关系数据库要好.

可伸缩性(scale) 能够适应不断增加的工作量而不失败.设计良好的数据库或应用程序称之为可伸缩性好(scale well).

15.1.2 为什么要使用联结

正如所述,分解数据为多个表能更有效地存储,更方便地处理,并且具有更大的可伸缩性.但这些好处是有代价的.
如果数据存储在多个表中,怎样用单条SELECT语句检索出数据?
答案是使用联结.简单地说,联结是一种机制,用来在一条SELECT语句中关联表,因此称之为联结.使用特殊的语法,可以联结多个表返回一组输出,联结在运行时关联表中正确的行.

维护引用完整性
重要的是,要理解联结不是物理实体.换句话说,它在实际的数据库表中不存在.联结由MySQL根据需要建立,它存在于查询的执行当中.
在使用关系表时,仅在关系列中插入合法的数据非常重要.回到这里的例子,如果在products表中插入拥有非法供应商ID (即没有在vendors表中出现)的供应商生产的产品,则这些产品是不可访问的,因为它们没有关联到某个供应商.
为防止这种情况发生,可指示MySQL只允许在products表的供应商ID列中出现合法值(即出现在vendors表中的供应商). 这就是维护引用完整性,它是通过在表的定义中指定主键和外键来实现的.

15.2 创建联结

例子:
这条语句的FROM
子句列出了两个表,分别是vendors和products.它们就是这条SELECT语句联结的两个表的名字.这两个表用WHERE子句正确联结,WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id.
SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id=products.vend_id ORDER BY vend_name, prod_name;

完全限定列名
在引用的列可能出现二义性时,必须使用完全限定列名(用一个点分隔的表名和列名).如果引用一个没有用表名限制的具有二义性的列名,MySQL将返回错误.

15.2.1 WHERE子句的重要性

笛卡儿积(cartesian product)
由没有联结条件的表关系返回的结果为笛卡儿积.检索出的行的数目将是第一个表中的行数乘以第二个表中的行数.

不要忘了WHERE子句
应该保证所有联结都有WHERE子句,否则MySQL将返回比想要的数据多得多的数据.同理,应该保证WHERE子句的正确性.不正确的过滤条件将导致MySQL返回不正确的数据.

叉联结有时我们会听到返回称为叉联结(crossjoin)的笛卡儿积的联结类型.

例子:
SELECT vend_name, prod_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name;

15.2.2 内部联结

目前为止所用的联结称为等值联结(equijoin),它基于两个表之间的相等测试.这种联结也称为内部联结.其实,对于这种联结可以使用稍微不同的语法来明确指定联结的类型.

使用哪种语法
ANSI SQL规范首选INNER JOIN语法.此外, 尽管使用WHERE子句定义联结的确比较简单,但是使用明确的联结语法能够确保不会忘记联结条件,有时候这样做也能影响性能.

例子:
SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;

15.2.3 联结多个表

SQL对一条SELECT语句中可以联结的表的数目没有限制.创建联结的基本规则也相同.首先列出所有表,然后定义表之间的关系.

性能考虑 MySQL在运行时关联指定的每个表以处理联结.这种处理可能是非常耗费资源的,因此应该仔细,不要联结不必要的表.联结的表越多,性能下降越厉害.

例子:
显示编号为20005的订单中的物品
SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id=vendors.vend_id AND orderitems.prod_id=products.prod_id AND order_num=20005;

返回订购产品TNT2的客户列表
SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2'));
等同于以下的查询:
SELECt cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id=order.cust_id AND orderitems.order_num=order.order_num AND prod_id='TNT2';

多做实验
正如所见,为执行任一给定的SQL操作,一般存在不止一种方法.很少有绝对正确或绝对错误的方法.性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响.因此,有必要对不同的选择机制进行实验,以找出最适合具体情况的方法.

15.3 小结

第16章创建高级联结

16.1 使用表别名

SQL还允许给表名起别名.这样做有两个主要理由:

缩短SQL语句;
允许在单条SELECT语句中多次使用相同的表.

应该注意,表别名只在查询执行中使用.与列别名不一样,表别名不返回到客户机.

例子:
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;

SELECT cust_name, cust_conntact FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id='TNT2';

16.2 使用不同类型的联结

迄今为止,我们使用的只是称为内部联结或等值联结(equijoin)的简单联结.现在来看3种其他联结,它们分别是自联结、自然联结和外部联结.

16.2.1 自联结

用自联结而不用子查询
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句.虽然最终的结果是相同的,但有时候处理联结远比处理子查询快得多.应该试一下两种方法,以确定哪一种的性能更好.

例子:
SELECT prod_id, prod_name FROM products WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id='DTNTR');
等用于以下的自联结
SELECT p1.prod_id, p1.prod_name FROM products AS p1, products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id='DTNTR';

16.2.2 自然联结

无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列).标准的联结(前一章中介绍的内部联结)返回所有数据,甚至相同的列多次出现.自然联结排除多次出现,使每个列只返回一次.

事实上,迄今为止我们建立的每个内部联结都是自然联结,很可能我们永远都不会用到不是自然联结的内部联结.

例子:
SELECT c.*, o.order_num, o.order_data, oi.prod_id, oi.quantity, oi.item_prict FROM customers AS c, orders AS o, orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id='FB';

16.2.3 外部联结

联结包含了那些在相关表中没有关联行的行.这种类型的联结称为外部联结.

没有=操作符
MySQL不支持简化字符=和=*的使用,这两种操作符在其他DBMS中是很流行的.

外部联结的类型
存在两种基本的外部联结形式:左外部联结和右外部联结.
它们之间的唯一差别是所关联的表的顺序不同.
换句话说,左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结.
因此,两种类型的外部联结可互换使用,而究竟使用哪一种纯粹是根据方便而定.

例子:
检索所有客户及其订单:
SELECT customers.cust_id, order.order_num FROM customers INNER JOIN orders ON customers.cust_id=order.cust_id;

检索所有客户,包括那些没有订单的客户
SELECT customers.cust_id, order.order_num FROM customers LEFT OUTER JOIN oders ON customers.cust_id=order.cust_id;

SELECT customers.cust_id, order.order_num FROM customers RIGHT OUTER JOIN orders ON order.cust_id=cusiomers.cust_id;

16.3 使用带聚集函数的联结

例子:
要检索所有客户及每个客户所下的订单数
SELECT coustomer.cust_name, customer.cust_id, COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id;

SELECT customer.cust_name, customers.cust_id, COUNT(order.order_num) AS num_ord FROM customers LEFT OUTER JOIN order ON customers.cust_id=order.cust_id GROUP BY customers.cust_id;

16.4 使用联结和联结条件

在总结关于联结的这两章前,有必要汇总一下关于联结及其使用的某些要点.

注意所使用的联结类型.一般我们使用内部联结,但使用外部联结也是有效的.
保证使用正确的联结条件,否则将返回不正确的数据.
应该总是提供联结条件,否则会得出笛卡儿积.
在一个联结中可以包含多个表,甚至对于每个联结可以采用不同的联结类型.虽然这样做是合法的,一般也很有用,但应该在一起测试它们前,分别测试每个联结.这将使故障排除更为简单.

16.5 小结

第17章组合查询

17.1 组合查询

多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句.MySQL也允许执行多个查询(多条SELECT语句),并将结果作为单个查询结果集返回.这些组合查询通常称为并(union)或复合查询(compound query).

有两种基本情况,其中需要使用组合查询:

在单个查询中从不同的表返回类似结构的数据;
对单个表执行多个查询,按单个查询返回数据.

组合查询和多个WHERE条件
多数情况下,组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同.换句话说,任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出,在以下段落中可以看到这一点.这两种技术在不同的查询中性能也不同.因此,应该试一下这两种技术,以确定对特定的查询哪一种性能更好.

17.2 创建组合查询

可用UNION操作符来组合数条SQL查询.利用UNION,可给出多条SELECT语句,将它们的结果组合成单个结果集.

17.2.1 使用UNION

UNION的使用很简单.所需做的只是给出每条SELECT语句,在各条语句之间放上关键字UNION.

例子:
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5;
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
进行联合,UNION指示MySQL执行两条SELECT语句,并把输出组合成单个查询结果集.
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);
等同于以下的查询
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001, 1002);
在这个简单的例子中,使用UNION可能比使用WHERE子句更为复杂.但对于更复杂的过滤条件,或者从多个表(而不是单个表)中检索数据的情形,使用UNION可能会使处理更简单.

17.2.2 UNION规则

正如所见,并是非常容易使用的.但在进行并时有几条规则需要注意.

UNION必须由两条或两条以上的SELECT语句组成,语句之间用关键字UNION分隔(因此,如果组合4条SELECT语句,将要使用3个UNION关键字).
UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过各个列不需要以相同的次序列出).
列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含地转换的类型(例如,不同的数值类型或不同的日期类型). 如果遵守了这些基本规则或限制,则可以将并用于任何数据检索任务.

17.2.3 包含或取消重复的行

UNION从查询结果集中自动去除了重复的行(换句话说,它的行为与单条SELECT语句中使用多个WHERE子句条件一样).
这是UNION的默认行为,但是如果需要,可以改变它.事实上,如果想返回所有匹配行,可使用UNION ALL而不是UNION.
使用UNION ALL,MySQL不取消重复的行.

UNION与WHERE
本章开始时说过,UNION几乎总是完成与多个WHERE条件相同的工作.UNION ALL为UNION的一种形式,它完成WHERE子句完成不了的工作.如果确实需要每个条件的匹配行全部出现(包括重复行),则必须使用UNION ALL而不是WHERE.

例子:
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION ALL
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002);

17.2.4 对组合查询结果排序

在用UNION组合查询时,只能使用一条ORDER BY子句,它必须出现在最后一条SELECT语句之后.对于结果集,不存在用一种方式排序一部分,而又用另一种方式排序另一部分的情况,因此不允许使用多条ORDER BY子句.

组合不同的表
为使表述比较简单,本章例子中的组合查询使用的均是相同的表.但是其中使用UNION的组合查询可以应用不同的表.

例子:
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5 UNION
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001, 1002) ORDER BY vend_id, prod_price;

17.3 小结

第18章全文本搜索

18.1 理解全文本搜索

并非所有引擎都支持全文本搜索
正如第21章所述,MySQL支持几种基本的数据库引擎.并非所有的引擎都支持本书所描述的全文本搜索.两个最常使用的引擎为MyISAM和InnoDB,前者支持全文本搜索,而后者不支持.这就是为什么虽然本书中创建的多数样例表使用InnoDB,而有一个样例表(productnotes表)却使用MyISAM的原因.如果你的应用中需要全文本搜索功能,应该记住这一点.

LIKE关键字以及正则表达式的限制:

性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行(而且这些搜索极少使用表索引).因此,由于被搜索行数不断增加,这些搜索可能非常耗时.
明确控制——使用通配符和正则表达式匹配,很难(而且并不总是能)明确地控制匹配什么和不匹配什么.例如,指定一个词必须匹配,一个词必须不匹配,而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配.
智能化的结果——虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索,但它们都不能提供一种智能化的选择结果的方法.例如,一个特殊词的搜索将会返回包含该词的所有行,而不区分包含单个匹配的行和包含多个匹配的行(按照可能是更好的匹配来排列它们).类似,一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行.

所有这些限制以及更多的限制都可以用全文本搜索来解决.在使用全文本搜索时,MySQL不需要分别查看每个行,不需要分别分析和处理每个词.MySQL创建指定列中各词的一个索引,搜索可以针对这些词进行.这样,MySQL可以快速有效地决定哪些词匹配(哪些行包含它们),哪些词不匹配,它们匹配的频率,等等.

18.2 使用全文本搜索

为了进行全文本搜索,必须索引被搜索的列,而且要随着数据的改变不断地重新索引.在对表列进行适当设计后,MySQL会自动进行所有的索引和重新索引.
在索引之后,SELECT可与Match()和Against()一起使用以实际执行搜索.

18.2.1 启用全文本搜索支持

一般在创建表时启用全文本搜索.CREATE TABLE语句接受FULLTEXT子句,它给出被索引列的一个逗号分隔的列表.
在定义之后,MySQL自动维护该索引.在增加、更新或删除行时,索引随之自动更新.
可以在创建表时指定FULLTEXT,或者在稍后指定(在这种情况下所有已有数据必须立即索引).

不要在导入数据时使用FULLTEXT
更新索引要花时间,虽然不是很多,但毕竟要花时间.如果正在导入数据到一个新表,此时不应该启用FULLTEXT索引.应该首先导入所有数据,然后再修改表,定义FULLTEXT.这样有助于更快地导入数据(而且使索引数据的总时间小于在导入每行时分别进行索引所需的总时间).

18.2.2 进行全文本搜索

在索引之后,使用两个函数Match()和Against()执行全文本搜索,其中Match()指定被搜索的列,Against()指定要使用的搜索表达式.
使用完整的Match()说明
传递给Match()的值必须与 FULLTEXT()定义中的相同.如果指定多个列,则必须列出它们(而且次序正确).

搜索不区分大小写除非使用BINARY方式,否则全文本搜索不区分大小写.

排序多个搜索项如果指定多个搜索项,则包含多数匹配词的那些行将具有比包含较少词(或仅有一个匹配)的那些行高的等级值.

例子:
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
此SELECT语句检索单个列note_text.由于WHERE子句,一个全文本搜索被执行.Match(note_text)指示MySQL针对指定的列进行搜索,Against('rabbit')指定词rabbit作为搜索文本.由于有两行包含词rabbit的行被返回.

等同于下面的语句：
SELECT note_text FROM productnotes WHERE note_text LIKE '%rabbit%';

上述两条SELECT语句都不包含ORDER BY子句.后者(使用LIKE)以不特别有用的顺序返回数据.前者(使用全文本搜索)返回以文本匹配的良好程度排序的数据.两个行都包含词rabbit,但包含词rabbit作为第3个词的行的等级比作为第20个词的行高.这很重要.全文本搜索的一个重要部分就是对结果排序.具有较高等级的行先返回.

SELECT note_text, Match(note_text) Against('rabbit') AS rank FROM productnotes;
Match()和Against()用来建立一个计算列(别名为rank),此列包含全文本搜索计算出的等级值.等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来.正如所见,不包含词rabbit的行等级为0(因此不被前一例子中的WHERE子句选择).确实包含词rabbit的两个行每行都有一个等级值,文本中词靠前的行的等级值比词靠后的行的等级值高.

正如所见,全文本搜索提供了简单LIKE搜索不能提供的功能.而且,由于数据是索引的,全文本搜索还相当快.

18.2.3 使用查询扩展

在使用查询扩展时,MySQL对数据和索引进行两遍扫描来完成搜索:

首先,进行一个基本的全文本搜索,找出与搜索条件匹配的所有行;
其次,MySQL检查这些匹配行并选择所有有用的词(我们将会简要地解释MySQL如何断定什么有用,什么无用).
再其次,MySQL再次进行全文本搜索,这次不仅使用原来的条件,而且还使用所有有用的词.
利用查询扩展,能找出可能相关的结果,即使它们并不精确包含所查找的词.

行越多越好表中的行越多(这些行中的文本就越多),使用查询扩展返回的结果越好.

例子:
SELECT note_text FROM productnotes WHERE Match(note_next) Against('anvils');
下面的语句使用查询扩展
SELECT note_text FROM productnotes WHERE Match(note_next) Against('anvils' WITH QUERY EXPANSION);
查询扩展极大地增加了返回的行数,但这样做也增加了你实际上并不想要的行的数目.

18.2.4 布尔文本搜索

MySQL支持全文本搜索的另外一种形式,称为布尔方式(boolean mode).
以布尔方式,可以提供关于如下内容的细节:

要匹配的词;
要排斥的词(如果某行包含这个词,则不返回该行,即使它包含其他指定的词也是如此);
排列提示(指定某些词比其他词更重要,更重要的词等级更高);
表达式分组;
另外一些内容.

即使没有FULLTEXT索引也可以使用
布尔方式不同于迄今为止使用的全文本搜索语法的地方在于,即使没有定义FULLTEXT索引,也可以使用它.但这是一种非常缓慢的操作(其性能将随着数据量的增加而降低).

IN BOOLEAN MODE的行为差异
虽然这个例子的结果与没有IN BOOLEAN MODE的相同,但其行为有一个重要的差别

排列而不排序 在布尔方式中,不按等级值降序排序返回的行.

文本布尔操作符

布尔操作符	说明
+	包含,词必须存在
-	排除,词必须不出现
>	包含,而且增加等级值
<	包含,且减少等级值把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等)
~	取消一个词的排序值
*	词尾的通配符
""	定义一个短语(与单个词的列表不一样,它匹配整个短语以便包含或排除这个短语)

例子:
全文本搜索检索包含词heavy的所有行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

匹配包含heavy但不包含任意以rope开始的词的行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

搜索匹配包含词rabbit和bait的行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);

包含rabbit和bait中的至少一个词的行
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);

搜索匹配短语rabbit bait而不是匹配两个词rabbit和 bait
SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);

匹配rabbit和carrot,增加前者的等级,降低后者的等级
SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);

匹配词safe和combination,降低后者的等级
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);

18.2.5 全文本搜索的使用说明

全文本搜索的某些重要的说明.

在索引全文本数据时,短词被忽略且从索引中排除.短词定义为那些具有3个或3个以下字符的词(如果需要,这个数目可以更改).
MySQL带有一个内建的非用词(stopword)列表,这些词在索引全文本数据时总是被忽略.如果需要,可以覆盖这个列表(请参阅MySQL文档以了解如何完成此工作).
许多词出现的频率很高,搜索它们没有用处(返回太多的结果).因此,MySQL规定了一条50%规则,如果一个词出现在50%以上的行中,则将它作为一个非用词忽略.50%规则不用于IN BOOLEAN MODE.
如果表中的行数少于3行,则全文本搜索不返回结果(因为每个词或者不出现,或者至少出现在50%的行中).
忽略词中的单引号.例如,don't索引为dont.
不具有词分隔符(包括日语和汉语)的语言不能恰当地返回全文本搜索结果.
如前所述,仅在MyISAM数据库引擎中支持全文本搜索.

没有邻近操作符
邻近搜索是许多全文本搜索支持的一个特性,它能搜索相邻的词(在相同的句子中、相同的段落中或者在特定数目的词的部分中,等等).MySQL全文本搜索现在还不支持邻近操作符,不过未来的版本有支持这种操作符的计划.

18.3 小结

第19章插入数据

19.1 数据插入

INSERT是用来插入(或添加)行到数据库表的.插入可以用几种方式使用:

插入完整的行;
插入行的一部分;
插入多行;
插入某些查询的结果.

插入及系统安全
可针对每个表或每个用户,利用MySQL的安全机制禁止使用INSERT语句

19.2 插入完整的行

把数据插入表中的最简单的方法是使用基本的INSERT语法,它要求指定表名和被插入到新行中的值.

没有输出 INSERT语句一般不会产生输出.

总是使用列的列表
一般不要使用没有明确给出列的列表的INSERT语句.使用列的列表能使SQL代码继续发挥作用,即使表结构发生了变化.

仔细地给出值
不管使用哪种INSERT语法,都必须给出VALUES的正确数目.如果不提供列名,则必须给每个表列提供一个值.如果提供列名,则必须对每个列出的列给出一个值. 如果不这样,将产生一条错误消息,相应的行插入不成功.

省略列
如果表的定义允许,则可以在INSERT操作中省略某些列.省略的列必须满足以下某个条件.

该列定义为允许NULL值(无值或空值).
在表定义中给出默认值.这表示如果不给出值,将使用默认值.
如果对表中不允许NULL值且没有默认值的列不给出值,则 MySQL将产生一条错误消息,并且相应的行插入不成功.

例子:
INSERT INTO Customers VALUES(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
这种语法很简单,但并不安全,应该尽量避免使用.上面的SQL语句高度依赖于表中列的定义次序,并且还依赖于其次序容易获得的信息.即使可得到这种次序信息,也不能保证下一次表结构变动后各个列保持完全相同的次序.因此,编写依赖于特定列次序的SQL语句是很不安全的.如果这样做,有时难免会出问题.

INSERT INTO Customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) VALUES('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);
在插入行时,MySQL将用VALUES列表中的相应值填入列表中的对应项.VALUES中的第一个值对应于第一个指定的列名.第二个值对应于第二个列名,如此等等.

INSERT INTO Customers(cust_name, cust_contact, cust_email, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('Pep E. LaPew', NULL, NULL, '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA');

提高整体性能
数据库经常被多个客户访问,对处理什么请求以及用什么次序处理进行管理是MySQL的任务.INSERT操作可能很耗时(特别是有很多索引需要更新时),而且它可能降低等待处理的SELECT语句的性能.
如果数据检索是最重要的(通常是这样),则你可以通过在 INSERT和INTO之间添加关键字LOW_PRIORITY,指示MySQL 降低INSERT语句的优先级,如下所示:INSERT LOW_PRIORITY INFO
顺便说一下,这也适用于下一章介绍的UPDATE和DELETE语句.

19.3 插入多个行

其中单条INSERT语句有多组值,每组值用一对圆括号括起来,用逗号分隔.
提高INSERT的性能此技术可以提高数据库处理的性能,因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT 语句快.

例子:
INSERT INFO customers(cust_name. cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA');

INSERT INFO customers(cust_name. cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

合并
INSERT INFO customers(cust_name. cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA'), ('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

19.4 插入检索出的数据

INSERT一般用来给表插入一个指定列值的行.但是,INSERT还存在另一种形式,可以利用它将一条SELECT语句的结果插入表中.这就是所谓的INSERT SELECT,顾名思义,它是由一条INSERT语句和一条SELECT语句组成的.

INSERTSELECT中的列名
为简单起见,这个例子在INSERT和 SELECT语句中使用了相同的列名.但是,不一定要求列名匹配.事实上,MySQL甚至不关心SELECT返回的列名.它使用的是列的位置,因此SELECT中的第一列(不管其列名)将用来填充表列中指定的第一个列,第二列将用来填充表列中指定的第二个列,如此等等.这对于从使用不同列名的表中导入数据是非常有用的.

INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据.

例子:
使用INSERT SELECT从custnew中将所有数据导入customers
INSERT INTO customers(cust_id, cust_contact, cust_email, cust_name, cust_address, cust_sity, cust_state, cust_zip, cust_country) SELECT cust_id, cust_contact, cust_email, cust_name, cust_address, cust_sity, cust_state, cust_zip, cust_country FROM custnew;

19.5 小结

第20章更新和删除数据

20.1 更新数据

为了更新(修改)表中的数据,可使用UPDATE语句.
可采用两种方式使用UPDATE:

更新表中特定行;
更新表中所有行.

不要省略WHERE子句在使用UPDATE时一定要注意细心.因为稍不注意,就会更新表中所有行.

UPDATE语句非常容易使用,甚至可以说是太容易使用了.
基本的UPDATE语句由3部分组成,分别是:

要更新的表;
列名和它们的新值;
确定要更新行的过滤条件.
UPDATE语句总是以要更新的表的名字开始.

在更新多个列时,只需要使用单个SET命令,每个“列=值”对之间用逗号分隔(最后一列之后不用逗号).

在UPDATE语句中使用子查询 UPDATE语句中可以使用子查询,使得能用SELECT语句检索出的数据更新列数据.

IGNORE关键字
如果用UPDATE语句更新多行,并且在更新这些行中的一行或多行时出一个现错误,则整个UPDATE操作被取消 (错误发生前更新的所有行被恢复到它们原来的值).为即使是发生错误,也继续进行更新,可使用IGNORE关键字,
如下所示: UPDATE IGNORE customers...

为了删除某个列的值,可设置它为NULL(假如表定义允许NULL值).

例子:
UPDATE customers SET cust_email='elmer@fudd.com' WHERE cust_id=10005;
UPDATE customers SET cust_name='The Fudds', cust_email='elmer@fudd.com' WHERE cust_id=10005;

UPDATE customers SET cust_email=NULL WHERE cust_id=10005;

20.2 删除数据

为了从一个表中删除(去掉)数据,使用DELETE语句.
可以两种方式使用DELETE:

从表中删除特定的行;
从表中删除所有行.

不要省略WHERE子句在使用DELETE时一定要注意细心.因为稍不注意,就会错误地删除表中所有行.

UPDATE非常容易使用,而DELETE更容易使用.

DELETE FROM要求指定从中删除数据的表名. WHERE子句过滤要删除的行.

DELETE不需要列名或通配符.DELETE删除整行而不是删除列.为了删除指定的列,请使用UPDATE语句.

删除表的内容而不是表 DELETE语句从表中删除行,甚至是删除表中所有行.但是,DELETE不删除表本身.

更快的删除如果想从表中删除所有行,不要使用DELETE. 可使用TRUNCATE TABLE语句,它完成相同的工作,但速度更快(TRUNCATE实际是删除原来的表并重新创建一个表,而不是逐行删除表中的数据).

例子:
DELETE FROM customers WHERE cust_id=10006;

20.3 更新和删除的指导原则

前一节中使用的UPDATE和DELETE语句全都具有WHERE子句,这样做的理由很充分.如果省略了WHERE子句,则UPDATE或DELETE将被应用到表中所有的行.换句话说,如果执行UPDATE而不带WHERE子句,则表中每个行都将用新值更新.类似地,如果执行DELETE语句而不带WHERE子句,表的所有数据都将被删除.

下面是许多SQL程序员使用UPDATE或DELETE时所遵循的习惯.

除非确实打算更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句.
保证每个表都有主键(如果忘记这个内容,请参阅第15章),尽可能像WHERE子句那样使用它(可以指定各主键、多个值或值的范围).
在对UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防编写的WHERE子句不正确.
使用强制实施引用完整性的数据库(关于这个内容,请参阅第15章),这样MySQL将不允许删除具有与其他表相关联的数据的行.

小心使用 MySQL没有撤销(undo)按钮.应该非常小心地使用UPDATE和DELETE,否则你会发现自己更新或删除了错误的数据.