《MySQL必知必会》知识点总结

本篇博客内容主要来源于**Ben Forta著作《MySQL必知必会》**的学习笔记，如有不正确，欢迎指正。博客中所有SQL语句均来自 《MySQL必知必会》样例表

1. 初识MySQL

在学习MySQL之前，先了解一下数据库和SQL，它们是学习MySQL的先决条件

什么是数据库

数据库

数据库是一个以某种有组织的方式存储的数据集合。简单理解就是将其想象为一个文件柜，此文件柜是一个存放数据的物理位置，不管数据是什么以及如何组织的。

数据库 ：保存有组织的数据的容器

表

在你将资料访问文件柜的时候，不会随意扔，而是创建文件，将相关的资料放入特定的文件中。在数据库领域中，这种文件叫做表。

表：某种特定类型的数据的结构化清单

表的关键在于，存储在表中的数据是一种类型的数据或一个清单。

列和数据类型

表由列组成。列中存储着表中某部分的信息。

列 表中的一个字段。所有表都是由一个或多个列组成。

可以将数据库想象为一个excel文件，里面有多个sheet对应不同的表，每个sheet中都有表头一行，定义了下面一列的信息。如姓名、年龄、性别、喜好等。

数据类型：每一列在创建表的时候定义了数据类型，在存储的时候只能添加这种类型的数据。

行

每一行对应一条信息。如第一行是张三的姓名、年龄、性别、喜好，第二行是李四的。

什么是SQL

SQL是结构化查询语言(Structured Query Language)的缩写，SQL是一种专门用来与数据库通信的语言。

SQL的优点：

1. 不是某个特定数据库供应商转悠的语言，几乎所有的数据库都支持SQL
2. SQL简单易学，所有语句都是由描述性很强的英语单词组成。
3. SQL灵活，可以进行非常复杂和高级的数据库操作。

什么是MySQL

数据的所有存储、检索、管理和处理实际上都是由数据库软件——DBMS(数据库管理系统)完成的，MySQL是一种DBMS，也就是一种数据库软件。

MySQL是当前最流行的数据库软件之一：

1. MySQL是开源的，可以免费使用
2. 执行很快
3. 可信赖，很多公司和站点都在使用MySQL
4. 容易安装和使用。(Ubuntu18.04安装MySQL并设置远程登录)

学习MySQL推荐安装Navicat——强大的数据库管理和设计工具。

2.检索数据

检索数据

检索单个列

SELECT是最常使用的SQL语句了，它的用于是从一个表或者多个表中检索信息。

-- 从products表中选取prod_name列
SELECT prod_name FROM products

单条SQL语句结束可以加或者不加分号(; )，多条SQL语句之间一定要加分号。如果使用的是mysql命令行，必须加上分号。

SQL不分大小写。一般会使用SQL关键字大写，所有的列和表名小写，这样容易阅读和调试。

检索多个列

多列在SELECT关键字后面给出多个列名即可。

SELECT prod_id, prod_name, prod_price FROM products

在选择多个列时，列名之间要加逗号，最后一个列名后不加。

检索所有列

SELECT * FROM products;

一般不使用*通配符，因为检索出不需要的列时会降低检索和应用程序性能，当数据量越大检索效率越低。

检索不同的行

SELECT返回所有的行，你不需要出现重复的结果。比如只需要所有提供商的ID：

SELECT DISTINCT vend_id FROM products;

不能部分使用DISTINCT，它应用于所有列而不是前置它的列。如果给出SELECT DISTINCT vend_id, prod_price，除非两列都相同，否则所有行都被检索出来。

限制结果

SELECT 语句返回所有的结果，为了返回第一行或者前几行，可以使用LIMIT子句。

SELECT prod_name FROM products LIMIT 5;

返回的结果超过五条则只显示五条。

LIMIT 5,5 指示MySQL返回从行5开始的5行。第一个数为开始位置，第二个数为要检索的行数。

检索出来的第一行为行0，而不是行1。LIMIT 1,1是检索第二行而不是第一行

LIMIT 4 OFFSET 3，从第三行开始取第四行 等同于LIMIT 3,4

使用完全限定的表名

SELECT products.prod_name FROM crashcourse.products;

-- 等同于SELECT prod_name FROM products;
-- 等同于SELECT prod_name FROM crashcourse.products;

排序检索数据

排序数据

SELECT prod_name FROM products 
ORDER BY prod_name;

这条语句的搜索结果根据prod_name字段的字母顺序排序。当然也可以使用非选择列进行排序。

按照多个排序

经常需要按不止一个列排序。

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price, prod_name;

检索三个列，并按照价格和名字进行排序——首先按照价格排序，价格相同的时候按照名称排序。也就是说当价格都不一样的时候，不会按照名字排序。

指定排序方向

数据排序默认是升序（A-Z)，还可以使用ORDER BY子句以降序（Z-A）排序，使用指定关键字DESC。

SELECT prod_id, prod_price, prod_name
FROM products
ORDER BY prod_price DESC;

DESC只应用在其前面的列名。

如果想在多个列上降序排序：对每个列指定DESC关键字。

3.数据过滤

使用WHERE子句

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在FROM子句之后给出。

SELECT prod_name, prod_price
FROM products
WHERE prod_price=2.50;

输出：

在同时使用ORDER BY 和 WHERE子句时，应该让ORDER BY位于WHERE之后，否则将产生错误。

WHERE子句操作符

MYSQL支持的操作符有

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

检测单个值

SELECT prod_name, prod_price
FROM products
WHERE prod_name='fuses';

输出：

MySQL在执行匹配时默认不区分大小写

SELECT prod_name, prod_price
FROM products
WHERE prod_price<=10;

输出：

单引号用赖限定字符串，如果将值与字符串类型的列比较，需要限定引号。用来与数值列进行比较的值不用引号。

输入：

SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

输出：

BETWEEN必须指定两个值，并用AND关键字分隔，BETWEEN匹配范围内所有的值，包括指定的开始值和结束值。

空值检查用 IS NULL;

组合WHERE子句

AND操作符

为了通过不止一个列进行过滤，可使用AND操作符给WHERE子句附加条件。

SELECT
	prod_name,
	prod_price 
FROM
	products 
WHERE
	vend_id = 1003 
	AND prod_price <= 10;

此SQL语句检索由供应商1003制造且价格小于等于10美元的所有产品的名称和价格。

每多一个过滤条件，多添加一个AND

OR操作符

OR操作符和AND操作符不同，它指示MySQL匹配检索任一条件的行。

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

此SQL语句检索任由一个指定供应商制造的所有产品的产品名和价格。

WHERE可包含任意数目的AND和OR操作符，允许两者结合进行复杂或高级过滤。SQL优先处理AND操作符，再处理OR操作符。可以使用圆括号明确分组相应操作符。

IN操作符

IN操作符配合圆括号，用来指定条件范围，在范围的每个条件都可以进行匹配。IN取合法值由逗号隔开，全都括在圆括号中。

SELECT
	prod_name,
	prod_price 
FROM
	products 
WHERE
	vend_id IN ( 1002, 1003 ) 
ORDER BY
	prod_name;

IN操作符和OR操作符有相同的功能。但是IN更具有优势：

• IN操作符的语法更清楚且更直观
• IN操作符一般比OR操作符清单执行更快。
• IN最大优点可以包含其他SELECT语句，使得更动态建立WHERE子句

NOT操作符

NOT是用来否定后跟条件的关键字。

LIKE操作符

前面介绍的都是对已知值的过滤，那么对于未知值的过滤，使用LIKE配合通配符。

百分号(%)通配符

%表示任意字符出现任意次数。

SELECT
	prod_name,
	prod_price 
FROM
	products 
WHERE
	prod_name LIKE 'jet%';

输出：

%可以出现多次，也可以表达匹配到0次。

下划线(_)通配符

用法同%， 但_总是匹配一个字符，不同多也不能少。

不要过度使用通配符。如果其他操作符能达到相同目的，应该使用其他操作符。如果绝非必要，不要放在搜索模式的起始处，会匹配每一行，搜索起来是最慢的。

4.正则表达式

正则表达式是用来匹配文本的特殊的串(字符集合)

MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定正则表达式，过滤SELECT检索出的数据。

基本字符匹配

在MySQL的WHERE子句中使用REGEXP来使用正则表达式：

SELECT
	prod_name 
FROM
	products 
WHERE
	prod_name REGEXP '1000' 
ORDER BY
	prod_name;

输出结果：

REGEXP是在列值中进行匹配，如果被匹配的文本在列值中出现，REGEXP会找到它。LIKE则是匹配整个列，需要完全匹配。

匹配是不区分大小写，如果想要区分大小写可以使用BINARY关键字。WHERE prod_name REGEXP BINARY ‘JetPak .000’。

常用的匹配字符

字符	功能
|	相当于OR，表示匹配其中之一
[]	是另一种形式的OR，[123]相当于1 | 2 | 3
[1-9]	范围匹配，表示[12345789]，不局限于数值，也可以是字母
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

反斜杠()字符本身，需要使用\\

特殊字符，前面加\是为了转义。再加一个\，是MySQL自己转义。

重复元字符

元字符	说明
*	0个或多个匹配
+	1个或多个匹配
?	0个或1个匹配
{n}	指定数目匹配
{n,}	不少于指定数目匹配
{n,m}	匹配数目匹配

定位元字符

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

5. 数据处理函数

拼接字段

拼接：将值连接到一起构成单个值

输入：

SELECT
	CONCAT(vend_name,'   (',vend_country,')')
FROM
	vendors
ORDER BY
	vend_name;

输出：

使用别名
从前面的输出中可以看到，已经很好将两个字段拼接到一起。但是列名却是未命名的额，使用别名可以赋予，使用AS关键字：

执行算数计算

常用到对检索出的数据进行算数计算。

文本处理函数

常用的文本处理函数

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

关于SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

简单说就是SOUNDEX是根据发音来模糊检索。如通过Soundex(Y lie)可以检索到Y lee的结果

日期和时间处理函数

函数	说明
addDate()	增加一个日期（天、周等）
addTime()	增加一个时间（时、分等）
CurDate()	返回当前日期
CurTime()	返回当前时间
Date()	返回日期间的日期部分
DateDiff()	计算两个日期之差
Date_Add()	高度灵活的日期运算函数
Date_Fromat()	返回一个格式化的日期或时间串
Day()	返回一个日期的天数部分
DayOfWeek()	对于一个日期，返回对应的星期几
Hour()	返回一个时间的小时部分
Minute()	返回一个时间的分钟部分
Month()	返回一个时间的月份部分
Now()	返回串左边的字符
Second()	返回串左边的字符
Time()	返回串左边的字符
Yead()	返回串左边的字符

提一个需求：检索2005年9月的所有订单

SELECT
	cust_id,
	order_num 
FROM
	orders 
WHERE
	date( order_date ) BETWEEN '2005-09-01' 
	AND '2005-09-30'

或者

SELECT
	cust_id,
	order_num 
FROM
	orders 
WHERE
	YEAR ( order_date ) = 2005 
	AND MONTH ( order_date ) = 9

数值处理函数

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

6. 汇总数据

本章介绍什么是SQL聚集函数以及如何利用它们汇总表的数据

聚集函数

聚集函数运行在行组上，计算和返回单个值的函数。

SQL聚集函数

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

1. AVG():只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。AVG()函数忽略列值为NULL的行。
2. Count(*)是对表中的行的数目进行计数，包括NULL值。COUNT(column)对特定列具有的值进行计数，不包括NULL值
3. MAX()和MIN()忽略为NULL的行，如果用于文本数据，MAX返回最后一行，MIN返回第一行。
4. 简单记忆：除了COUNT(*)不忽略NULL值，其它均忽略。

聚集不同的值

在MySQL5.0.3及以上的版本

• 对所有的行执行计算，指定ALL参数或不给参数（ALL是默认行为）
• 只包含不同的值，指定DISTINCT参数

DISTINCT只能用于COUNT(column),不能用于COUNT(*)，会报错。另外，DISTINCT用于MAX()和MIN()函数可行，但没有意义。

组合聚集函数

一个SELECT可根据需要包含多个聚集函数。

7. 组合查询

本章介绍如何利用UNION操作符将多条SELECT语句组合成一个结果集

组合查询

多数的SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条Select）语句，并将结果作为单个查询结果集返回。

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

• 在单个查询中从不同的表返回类似结构的数据
• 在单个表执行多个查询，按单个查询返回数

任何具有WHERE子句的SELECT语句都可以作为一个组合查询给出

使用UNION

UNION使用很简单。给出每条SELECT语句，在各条语句之间放上关键字UNION。举一个例子：假如需要价格小于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）。先看看单条语句：

查看价格小于5的

-- sql1
SELECT
	vend_id,
	prod_id,
	prod_price 
FROM
	products 
WHERE
	prod_price <= 5;

查看供应商是1001和1002生产的所有物品：

-- sql2
SELECT
	vend_id,
	prod_id,
	prod_price 
FROM
	products 
WHERE
	vend_id IN ( 1001, 1002 );

再看看使用UNION组合这两条语句：

-- sql3
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 );

同理可以用where子句执行相同的查询：

-- sql4
SELECT
	vend_id,
	prod_id,
	prod_price 
FROM
	products 
WHERE
	prod_price <= 5 
	OR vend_id IN ( 1001, 1002 );

在这个例子中，使用UNION可能比WHERE子句更复杂。但对于更为复杂的过滤条件，或者从多个表中检索数据的情形，使用UNION可能会使处理更简单。

UNION规则

UNION使用简单，但有几条规则需要注意：

• UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
• UNION的每个查询必须包含相同的列、表达式或聚集函数
• 列的类型不变完全相同，但必须是可以隐式转换的

包含或取消重复的行

在上面的例子中，执行后可以知道sql1执行结果为4行，第二条sql2执行结果为5行，第三条sql3因为UNION组合两条SELECT语句后，只返回了8行而不是9行。

UNION从查询结果集中自动去除了重复行，因为供应商1002生产的一种物品的价格也低于5，sql1和sql2都返回该行。在使用UNION时，重复的行被自动取消。

这是UNION默认行为，如果不想取消重复行，可以使用UNION ALL。

-- sql5
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 );

返回结果为9行，结果请读者自行验证。

对组合查询结果排序

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

输入：

-- sql6
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;

在最后一条SELECT使用了ORDER BY子句，实际上MySQL将用它来排序所有SELECT语句返回的所有结果。

8.全文本搜索

本篇介绍使用MySQL全文本搜索功能进行高级的数据查询和选择。

理解全文搜索

并非所有引擎都支持全文本搜索。MySQL支持几种基本的数据库引擎，其中使用最多的是MyISAM和InnoDB，前者支持全文本搜索，而后者不支持。

前面的博客有介绍到LIKE关键字和进一步在MySQL中使用正则表达式，这些搜索机制非常有用，但是有几个重要的限制。

• 性能——通配符和正则表达式要求MySQL匹配所有行，这样搜索非常耗时。
• 明确控制——通配符和正则表达式比较困难控制匹配什么和不匹配什么。例如，要求第一个词匹配，第二个词不匹配，而第三个词要求第一个词匹配的情况下才可以匹配。（1.通配符和正则能做到，但是难。2.看不明白的话，先继续往下）
• 智能化结果——通配符和正则的结果不会进行区分。也就是搜索到该行包括一个还是多个关键字，搜索结果不会加以区分。

全文本搜索解决以上的限制。使用全文本搜索时，MySQL不需要分别查看每行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配，哪些词不匹配，它们匹配频率相关度等，等等。

启用全文本搜索

一般在创建表的时候启用全文本搜索。示例：

CREATE TABLE productnotes (
	note_id INT NOT NULL AUTO_INCREMENT,
	prod_id CHAR ( 10 ) NOT NULL,
	note_date datetime NOT NULL,
	note_text text NULL,
	PRIMARY KEY ( note_id ),
FULLTEXT ( note_text ) 
) ENGINE = MyISAM;

为支持note_text进行全文本搜索，必须加上FULLTEXT(note_text)进行索引。在定义之后，MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新。

不要再导入数据时使用FULLTEXT。导完数据后，再修改表，定义FULLTEXT，可以缩短总耗时。

进行全文本搜索

使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式。

SELECT
	note_text 
FROM
	productnotes 
WHERE
	MATCH ( note_text ) Against ( 'rabbit' );

match()的值必须与FULLTEXT()定义中的相同。搜索不区分大小写，可以使用BINARY关键字指定大小写

上面的搜索可以用LIKE子句完成：

SELECT
	note_text 
FROM
	productnotes 
WHERE
	note_text LIKE '%rabbit%';

但是Like在数据量大的时候搜索效率低，还有就是不能对结果排序。全文本搜索可以使得较高等级的行先返回：

SELECT
	note_text,
	MATCH ( note_text ) Against ( 'rabbit' ) AS ranks 
FROM
	productnotes;

输出为：

这个例子有助于说明全文本搜索如何排除行（排除那些等级为0的行），如何排序结果（按等级以降序排序）。

使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围。比如你想找到包含’anvils’的搜索结果，只有一个注释里面包含’anvils’，但你还想找出可能与你的搜索有关的其它所有行，即使它们不包含词anvils。

SELECT
	note_text 
FROM
	productnotes 
WHERE
	MATCH ( note_text ) Against ( 'anvils' )

这条搜索只能返回一个结果，匹配上anvils。使用查询扩展：

SELECT
	note_text 
FROM
	productnotes 
WHERE
	MATCH ( note_text ) Against ( 'anvils' WITH QUERY EXPANSION );

这次返回了七行，第一行包含anvils,优先级最高第一条展示。第二行及以后与anvils无关，但是通过相关词customer和recommend，所以也能被检索出来。

它的执行顺序为：

1. 首先进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
2. MySQL检查这些匹配行并选择有用的词（MySQL是如何判断的？）
3. 最后，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用步骤二中有用的词。

这样扩展搜索能够增加返回的行数，但是也增加了不想要的行数目。

布尔文本搜索

布尔方式是全文本搜索的另外一种形式，可以提供如下细节：

• 要匹配的词
• 要排斥的词（某行包含该词，则不返回该行，即便它包含其他指定词）
• 排列提示（指定词的优先级）
• 表达式分组

布尔方式即便没有FULLTEXT索引也可以使用，但是一种非常缓慢的操作，尽量少用，尤其在数据量大的时候。

全文布尔索引操作符

布尔操作符	说明
+	包含，词必须存在
-	排除，词必须不存在
>	包含，而且增加等级值
<	包含，且减少等级值
()	把词组成子表达式
~	取消一个词的排序值
*	词尾的通配符
“”	定义一个短语

下面举几个例子，说明某些操作符的使用：

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 而不是同时匹配两个词

SELECT
	note_text 
FROM
	productnotes
WHERE
	MATCH ( note_text ) Against ( '>rabbit <carrot' IN BOOLEAN MODE );
-- 这个搜索匹配rabbit和carrot,增加前者的等级，降低后者的等级

SELECT
	note_text 
FROM
	productnotes
WHERE
	MATCH ( note_text ) Against ( '+safe +(<combination)' IN BOOLEAN MODE );
	
-- 搜索同时匹配词safe和combination，降低后者的等级

全文本搜索的使用说明

有几点全文本搜索的重要说明。

• 在索引全文本数据时，默认短词被忽略且从索引中排除。（3个或以下字符为短词，可以更改）
• MySQL自带一个内建的非用词(stopword)列表，在全文本搜索数据时总是被忽略。（可以自定义覆盖该列表）
• 许多词出现频率过高，一个词出现出现在50%以上的行中，将它作为一个非用词忽略。（在布尔方式中不生效）
• 如果表行数少于3行，全文本搜索不返回结果（要么不出现，出现就大于等于50%）
• 忽略词中的单引号。例如，don’t索引为dont

9.插入数据

本章介绍如何利用SQL的INSERT语句将数据插入到表中

数据插入

数据库的增删改查，其中查是用的最多的（非DBA）。另外也要了解一下其他三种SQL语句。第一个是INSERT，如其字面含义，INSERT是用来插入行到数据库表的。插入可以用几种方式使用：

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

插入完整的行

INSERT语句要求指定表名和被插入到新行中的值。下面举例：

INSERT INTO customers
VALUES
	( NULL, 'Pep', '100 Main Street', 'Los Angels', 'CA', '90046', 'USA', NULL, NULL );

插入成功，会返回Affected rows:1.表示插入成功。

该例子表示插入一个新客户到customers表。存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值，应该使用NULL（假设表对该列允许为空）。每个列必须在表定义中出现次序填充。第一列cust_id为Null，是因为建表的时候cust_id设置为自增长。

这种写法高度依赖于表中列的定义次序，而且以后表结构变动，SQL也会失效。正常开发中更多使用以下SQL：

INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
VALUES
	( 'Pep', '100 Main Street', 'Los Angels', 'CA', '90046', 'USA', NULL, NULL );

这两条SQL完成相同的工作，但是后者在表名后明确的给出了列名。插入时，会将VALUES响应值对应到列表中的对应项。

这样values的值取决于表后的列名顺序，这样更改表结构，INSERT语句依然正常工作。

values必须和列名数量一一对应，数据类型也要兼容。可以省略部分列，但必须满足两个条件之一：一是该列定义为NULL值，二是表定义中给出了默认值。如cust_id是自增长，上面sql中省略了。

插入多个行

方案一：
可以写多个INSERT语句，每条语句用分号隔开，然后一次提交执行。

方案二：

INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
VALUES
	( 'Pep', '100 Main Street', 'Los Angels', 'CA', '90046', 'USA', NULL, NULL ),
	( 'Martin', '42 Galaxy Way', 'New York', 'NY', '11234', 'USA');

单条INSERT有多组值，每组值用一对圆括号括起来，用逗号隔开。这样执行速度比多个单条INSERT语句插入块。

插入检索的数据

INSERT存在另一种形式，可以利用它将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT

假如你想从另一张表中合并客户列表到你的customers表，不需要每次读取一行，利用INSERT插入。

INSERT INTO customers ( cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email )
SELECT cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email 
FROM custnew;

INSERT SELECT的列名不需要完全匹配，只要数据类型能够兼容即可。另外可以使用WHERE子句过滤插入的数据。

10. 更新和删除数据

更新数据

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

• 更新表中的特定行
• 更新表中所有行

UPDATE语句非常容易使用，基本的UPDATE语句由三部分组成：

1. 要更新的表
2. 列名和它们的新值
3. 确定要更新行的过滤条件

UPDATE customers 
SET cust_email = 'eamon@163.com' 
WHERE
	cust_id = 1005;

UPDATE语句总是以要更新的表名开始
SET命令是用新值赋给被更新的列
WHERE限定哪一行，否则会更新这张表的所有选中列

在更新多个列时，每个“列=值”用逗号分隔，最后一列不用逗号。

UPDATE customers 
SET cust_email = 'eamon@163.com',
    cust_name = 'Berry'
WHERE
	cust_id = 1005;

IGNORE关键字，如果UPDATE语句更新多行，并且在一行或者多行时出现一个错误，则整个UPDATE操作被取消。可以使用IGNORE关键字：UPDATE INGORE customers…

删除数据

从一个表中删除数据，使用DELETE语句。可以使用两种方式使用DELETE：

• 从表中删除特定行
• 从表中删除所有行

DELETE 
FROM
	customers 
WHERE
	cust_id = 10006;

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

DELETE不需要列名或通配符，它删除的是整行而不是删除列。如果要删除列，可以使用UPDATE，设置为NULL。

DELETE删除的是表的内容而不是表，如果不加WHERE限定，删除表中的所有行，所以一定要加限定条件WHERE。

更快地删除
当真的需要删除整个表内容，使用TRUNCATE TABLE语句，它完成相同工作，但速度更快。（TRUNCATE实际是删除原来的表，并重新创建一个表，而不是逐行删除表数据。）

更新和删除的指导原则

1. 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句
2. 保证每个表都有主键，尽可能像WHERE子句那样使用它
3. 在对UPDATE或DELETE语句使用WHERE子句前，可以使用SELECT进行测试，保证过滤的是正确的记录，以防编写的WHERE子句不正确。
4. 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有其他表相关联的数据的行。

11.使用视图

本篇介绍视图究竟是什么，它们怎样工作，何时使用它们。视图适用于MySQL5及以后逇版本

视图

视图是虚拟的表。与包含数据的表不一样，视图只包含使用时动态检索数据的查询。

例如检索订购了某个特定产品的客户：

SELECT
	cust_name,
	cust_contact 
FROM
	customers,
	orders,
	orderitems 
WHERE
	customers.cust_id = orders.cust_id 》
	AND orderitems.order_num = orders.order_num 
	AND prod_id = 'TNT2'

假如可以把整个查询包装成一个名为productcustomers的虚拟表，则可以进行轻松检索：

SELECT
	cust_name,
	cust_contact 
FROM
    productcustomers
WHERE
    prod_id='TNT2';

这就是视图的作用。productcustomers是一个视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询。

为什么使用视图

下面是视图的一些常见应用：

• 重用SQL语句
• 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节
• 使用表的组成部分而不是整个表
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图创建之后，可以与表基本相同的方式利用它们。可以执行SELECT操作，过滤和排序数据；也可以添加和更新数据（不建议用）。视图本身不包含数据，因此它们返回的数据是从其他表中检索出来的。

视图的规则和限制

下面是关于视图创建和使用的最常见规则和限制

• 与表一样，视图必须唯一命名
• 对于可以创建的视图数目没有限制
• 为了创建视图，必须具有足够的访问权限。
• 视图可以嵌套，即可以从其他视图中检索数据的查询来构造一个视图（可能会影响性能，使用前测试）
• ORDER BY可以用在视图中，视图中检索数据的SELECT语句中也包含有ORDER BY，那么该视图中的ORDER BY会被覆盖。
• 视图不能索引，也不能有关联的触发器或默认值
• 视图可以和表一起使用

使用视图

在理解什么是视图后，看一下视图的创建

• 视图用CREATE VIEW语句来创建
• 使用SHOW CREATE VIEW viewname，来查看创建视图的语句
• 用DROP删除视图，DROP VIEW viewname
• 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW

利用视图简化复杂的联结

视图最常用的方式就是隐藏复杂的SQL

CREATE VIEW productcustomers AS SELECT
cust_name,
cust_contact,
prod_id 
FROM
	customers,
	orders,
	orderitems 
WHERE
	customers.cust_id = orders.cust_id 
	AND orderitems.order_num = orders.order_num;

上面这条语句创建了视图，接下来可以使用

可以看出，视图极大简化了复杂SQL语句的使用。利用视图，可一次性编写基础的SQL，然后根据需要多次使用。

视图重新格式化检索出的数据

创建视图

CREATE VIEW vendorlocations AS SELECT
CONCAT( Rtrim( vend_name ), '   (', Rtrim( vend_country ), ')' ) AS vend_title 
FROM
	vendors 
ORDER BY
	vend_name;

使用视图

视图过滤不想要的数据

视图对于应用普通的WHERE子句也很有用。过滤没有电子邮件地址的用户

CREATE VIEW customeremaillist AS SELECT
cust_id,
cust_name,
cust_email 
FROM
	customers 
WHERE
	cust_email IS NOT NULL;

使用视图：

另外使用视图还有计算字段等

更新视图

上面视图例子都是和SELECT语句使用的，实际场景中也是最常使用的。视图同样可以用来更新（Insert Update Delete），但是存在着限制，视图中有以下操作，则不能进行视图的更新：

• 分组
• 联结
• 子查询
• 并
• 聚集函数(MIN()、COUNT()、Sum()等)
• DISTINCT
• 计算列

视图主要还是用于检索。