MySQL必知必会
2020-09-01 10:56:44 5 举报AI智能生成
《MySQL必知必会》是一本关于MySQL数据库的入门教程，旨在帮助读者快速掌握MySQL的基本概念、操作方法和实际应用技巧。本书内容涵盖了MySQL的安装与配置、SQL语言基础、表的创建与管理、数据查询与过滤、数据更新与删除、索引与性能优化、存储过程与触发器、事务处理与并发控制等多个方面。通过阅读本书，读者可以迅速建立起对MySQL数据库的全面认识，为进一步深入学习和应用打下坚实的基础。无论是初学者还是有一定经验的开发者，都可以从本书中获得宝贵的知识和实践经验。
作者其他创作
大纲/内容
MySQL必知必会
第一章 了解SQL
数据库（database）
保存有组织的数据的容器（通常是一个文件或一组文件）。
表（table）
某种特定类型数据的结构化清单。
表名
表名的唯一性取决于多个因素，如数据库名和表名等的结合。这表示，虽然在相同数据库中不能两次使用相同的表名，但在不同的数据库中却可以使用相同的表名。
模式（schema）
关于数据库和表的布局及特性的信息。
列（column）
表中的一个字段。所有表都是由一个或多个列组成的。
数据类型（datatype）
所容许的数据的类型。每个表列都有相应的数据类型，它限制（或容许）该列中存储的数据。
定义好数据类型，对优化磁盘和排序数据有帮助
行（row）
表中的一个记录。
主键（primary key）
一列（或一组列），其值能够唯一区分表中每个行。
应该总是定义主键
虽然并不总是都需要主键，但大多数数据库设计人员都应保证他们创建的每个表具有一个主键，以便于以后的数据操纵和管理。
主键条件
任意两行都不具有相同的主键值；
每个行都必须具有一个主键值（主键列不允许NULL值）。
主键的最好习惯
不更新主键列中的值；
不重用主键列的值；
不在主键列中使用可能会更改的值。
备注
可以使用多个列作为主键
所有列值的组合必须是唯一的
SQL
结构化查询语言（Structured Query Language）的缩写
一种专门用来与数据库通信的语言。
SQL特点
SQL不是某个特定数据库供应商专有的语言。几乎所有重要的DBMS都支持SQL，所以，学习此语言使你几乎能与所有数据库打交道。
SQL简单易学。它的语句全都是由描述性很强的英语单词组成，而且这些单词的数目不多。
SQL尽管看上去很简单，但它实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。
第二章 MySQL简介
MySQL
数据库软件
通常由服务器操作
MySQL优点
成本——MySQL是开放源代码的，一般可以免费使用（甚至可以免费修改）。
性能——MySQL执行很快（非常快）。
可信赖——某些非常重要和声望很高的公司、站点使用MySQL，这些公司和站点都用MySQL来处理自己的重要数据。
简单——MySQL很容易安装和使用。
MySQL实用工具
命令行实用程序
随安装包自带
MySQL Administrator
需要自己下载
官网不支持了
MySQL Query Browser
MySQL WorkBench
官网支持
第3章 使用MySQL
选择数据库
use xxxxx;
显示所有数据库
show databases;
显示数据库中所有表
show tables;
显示表中所有列
show columns from table1;
describe table1;
显示帮助信息
help show;
第4章 检索数据
SELECT语句
SELECT DISTINCT guid from TABLE1；
显示某一列中不同的行
select * from table1 limit 5
显示查询结果为5行
select * from table1 limit 5，6
显示查询结果为从5开始的6行数据
select table1.guid from tlbbdb.table1;
完全限定名
第5章 排序检索数据
OrderBy升序排列
先按照id排序，若是id相同，则按照name排序
desc降序排列
先按照id降序排序，若是id相同，则按照name排序
第6章 过滤数据
where
=
!=不等于
<>不等于与!=一样
<
<=
>
>=
BETWEEN
空值检查
第7章 数据过滤
AND
用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。
OR
WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行。
圆括号()
任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。使用圆括号没有什么坏处，它能消除歧义。
IN
WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。
NOT
WHERE子句中用来否定后跟条件的关键字。
MySQL中的NOT
MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。
第8章 用通配符进行过滤
LIKE操作符
通配符（wildcard） 用来匹配值的一部分的特殊字符。
搜索模式（search pattern）[插图]由字面值、通配符或两者组合构成的搜索条件。
百分号（%）通配符
包含la和zy的name
%不能匹配NULL
下划线（_）通配符
同%通配符，但只可匹配一个字符
注意事项
不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数据。
第9章 用正则表达式进行搜索
基本字符匹配
匹配1000的行
将REGEXP换成LIKE将不能匹配，因为LIKE不能匹配相同值的行
匹配以000结尾的行
.代表匹配任意字符
匹配大小写
进行OR匹配
|可以连接多个OR关系的表达式
匹配几个字符之一
匹配包含1sky、2sky或3sky的name
不匹配包含1sky、2sky或3sky的name
[]括起来的123相当于1|2|3，必须有[]，否则会是另一种解释
匹配范围
span style=\"font-size: inherit;\
[0123456789]==[0-9]
字母同数字
匹配特殊字符
若要匹配特殊字符，需要在特殊字符前面加\\\\
匹配元字符
\\\\f
换页
\\\
换行
回车
\\\\t
制表
\\\\v
纵向制表
匹配字符类
预定义的字符集
[:alnum:]
任意字母和数字（同[a-zA-Z0-9]）
[:alpha:]
任意字符（同[a-zA-Z]）
[:blank:]
空格和制表符（同[\\\\t]）
[:cntrl:]
ASCII控制字符（ASCII0-31和127）
[:digit:]
任意数字（同[0-9]）
[:graph:]
与[:print:]相同，但不包括空格
[:lower:]
任意小写字母（同[a-z]）
[:print:]
任意可打印字符
[:punct:]
既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:]
包括空格在内的任意空白字符（同[\\\\f\\\\\\\\\\t\\\\v]）
[:upper:]
任意大写字母（同[A-Z]）
[:xdigit:]
任意十六进制数字（同[a-fA-F0-9]）
匹配多个实例
重复元字符
*
0个或多个匹配
+
？
{n}
指定数目的匹配
不少于指定数目的匹配
匹配数目的范围（m不超过255）
例1
匹配得到了TNT(1 stick)和TNT（5 sticks）
\\\\(匹配(
[0-9]匹配1和5
stick?匹配stick和sticks
?代表前面匹配0个或1个字符
\\\\)匹配)
例2
匹配连在一起的四位数字
{4}要求它前面的数字出现4次
定位符
^
文本的开始
$
文本的结尾
[[:<:]]
词的开始
[[:>:]]
词的结尾
匹配.5 ton和1 ton
^的作用是匹配字符串的开始，也就是检查首个字符是0-9和.的字符串
正则表达式检查
SELECT 'hello' REGEXP '[0-9]';
第10章 创建计算字段
Concat
连接字符串，字符串之间用逗号分隔
Trim
去掉串左右两边的空格
LTrim
去掉串左边空格
RTrim
去掉串右边空格
AS 别名
命名一个别名
例子1
例子2
检查计算字段
SELECT 2*3;
计算表达式
SELECT Trim(' name ');
消除两边空格
SELECT Now();
返回当前日期和时间
第11章 使用数据处理函数
文本处理函数
Left()
返回串左边的字符
Length()
返回串的长度
Locate()
找出串的一个子串
Lower()
将串转换为小写
LTrim()
去掉串左边的空格
Right()
返回串右边的字符
RTrim()
去掉串右边的空格
Soundex()
返回串的SOUNDEX值
英文发音匹配
SubString()
返回子串的字符
Upper()
将串转换为大写
日期和时间处理函数
AddDate()
增加一个日期（天、周等）
AddTime()
增加一个时间（时、分等）
CurDate()
返回当前日期
CurTime()
返回当前时间
Date()
返回日期时间的日期部分
DateDiff()
计算两个日期之差
Date_Add()
高度灵活的日期计算函数
Date_Format()
返回一个格式化的日期或时间串
Day()
返回一个日期的天数部分
DayOfWeek()
对于一个日期，返回对应的星期几
Hour()
返回一个时间的小时部分
Minute()
返回一个时间的分钟部分
Month()
返回一个日期的月份部分
Now()
返回当前的日期和时间
Second()
返回一个时间的秒数部分
Time()
返回一个日期时间的时间部分
Year()
返回一个日期的年份部分
数值处理函数
Abs()
返回一个数的绝对值
Cos()
返回一个角度的余弦
Exp()
返回一个数的指数值
Mod()
返回除操作的余数
Pi()
返回圆周率
Rand()
返回一个随机数
Sin()
返回一个角度的正弦
Sqrt()
返回一个数的平方根
Tan()
返回一个角度的正切
第12章 汇总数据
聚集函数
AVG()
返回某列的平均值
SELECT AVG(prod_price) AS avg_price FROM products where vend_id=1003;
只用于单个列
AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
NULL值
AVG()函数忽略列值为NULL的行。
COUNT()
返回某列的行数
SELECT COUNT(*) AS num_cust FROM customers;
使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。
MAX()
SELECT MAX(prod_price) AS max_price FROM products;
对非数值数据使用MAX()
虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。
MIN()
返回某列的最小值
SELECT MIN(prod_price) AS max_price FROM products;
对非数值数据使用MIN()
MIN()函数与MAX()函数类似，MySQL允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。
SUM()
返回某列值之和
SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num=20005;
在多个列上进行计算
利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。
SUM()函数忽略列值为NULL的行。
组合聚集函数
实际上SELECT语句可根据需要包含多个聚集函数
SELECT COUNT(*) AS num_cust，SUM(quantity) AS items_ordered FROM orderitems WHERE order_num=20005;
在指定别名以包含某个聚集函数的结果时，不应该使用表中实际的列名。虽然这样做并非不合法，但使用唯一的名字会使你的SQL更易于理解和使用（以及将来容易排除故障）。
第13章 分组数据
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关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值
HAVING子句
HAVING支持所有WHERE操作符
HAVING支持过滤分组，而WHERE只支持过滤行
WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组
输出每一个cust_id对应的大于等于2的订单计数
输出每一个prod_price大于等于10的vend_id对应的大于等于2的订单计数
分组和排序
GROUP BY
分组行。但输出可能不是分组的顺序
只可能使用选择列或表达式列，而且必须使用每个选择列表达式
如果与聚集函数一起使用列（或表达式），则必须使用
ORDER BY
排序产生的输出
任意列都可以使用（甚至非选择的列也可以使用）
不一定需要
SELECT子句顺序
子句
说明
是否必须使用
SELECT
要返回的列或表达式
是
FROM
从中检索数据的表
仅在从表选择数据时使用
WHERE
行级过滤
否
分组说明
仅在按组计算聚集时使用
HAVING
组级过滤
输出排序顺序
LIMIT
要检索的行数
第14章 使用子查询
列必须匹配
在WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。
子查询和性能
这里给出的代码有效并获得所需的结果。但是，使用子查询并不总是执行这种类型的数据检索的最有效的方法。更多的论述，请参阅第15章，其中将再次给出这个例子。
第15章 联结表
外键（foreign key）
外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
可伸缩性（scale）
能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）。
创建联结:就是从多个表中检索数据
不要忘了WHERE子句
应该保证所有联结都有WHERE子句，否则MySQL将返回比想要的数据多得多的数据。同理，应该保证WHERE子句的正确性。不正确的过滤条件将导致MySQL返回不正确的数据。
叉联结
有时我们会听到返回称为叉联结（cross join）的笛卡儿积的联结类型。
使用哪种语法
ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。
性能考虑
MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结不必要的表。联结的表越多，性能下降越厉害。
14章的嵌套表例子修改
多做实验
正如所见，为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法。
第16章 创建高级联结
使用表别名
使用不同类型的联结
自联结
自然联结
外部联结
使用带聚集函数的联结
使用联结和联结条件
注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
保证使用正确的联结条件，否则将返回不正确的数据。
应该总是提供联结条件，否则会得出笛卡儿积。
在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。
第17章 组合查询
组合查询
在单个查询中从不同的表返回类似结构的数据；
对单个表执行多个查询，按单个查询返回数据。
多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。
换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出
创建组合查询
使用UNION
UNION规则
UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）。
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）。
包含或取消重复的行
UNION
消除重复行
UNION ALL
不消除重复行
对组合查询结果排序
允许使用多条ORDER BY子句
在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后
第18章 全文本搜索
LIKE和正则表达式问题
性能
通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，由于被搜索行数不断增加，这些搜索可能非常耗时。
明确控制
使用通配符和正则表达式匹配，很难（而且并不总是能）明确地控制匹配什么和不匹配什么。例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。
智能化的结果
虽然基于通配符和正则表达式的搜索提供了非常灵活的搜索，但它们都不能提供一种智能化的选择结果的方法。例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行（按照可能是更好的匹配来排列它们）。类似，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。
不要在导入数据时使用FULLTEXT
Match()指定被搜索的列，Against()指定要使用的搜索表达式
SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
全文本搜索出的数据会智能化排序，比如查询一个词，那么词在句子中出现约靠前，排序越靠前
使用查询扩展
首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
其次，MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）。
再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
布尔文本搜索
要匹配的词；
要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
排列提示（指定某些词比其他词更重要，更重要的词等级更高）;
表达式分组；
另外一些内容。
即使没有FULLTEXT索引也可以使用
布尔方式不同于迄今为止使用的全文本搜索语法的地方在于，即使没有定义FULLTEXT索引，也可以使用它。但这是一种非常缓慢的操作（其性能将随着数据量的增加而降低）。
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);
没有效果
SELECT note_text FROM productnotes WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
过滤掉以rope开头的数据
-排除一个词
*截断操作符
全文本布尔操作符
包含，词必须存在
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和carrot，增加前者的等级，降低后者的等级
()
把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）
SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);
这个搜索匹配词safe和combination，降低后者的等级
~
取消一个词的排序值
词尾的通配符
\"\"
定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）
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和bait中的至少一个词的行
全文本搜索使用说明
在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）。
MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表（请参阅MySQL文档以了解如何完成此工作）。
许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE。
如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）。
忽略词中的单引号。例如，don't索引为dont。
不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
如前所述，仅在MyISAM数据库引擎中支持全文本搜索。
第19章 插入数据
Insert
插入完整的行
自动增量的列，虽有系统管理，但不能忽略，可以填写NULL
插入行的一部分
插入多行
此技术可以提高数据库处理的性能，因为MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快。
插入某些查询的结果
font color=\"#c41230\
这个例子在INSERT和SELECT语句中使用了相同的列名
但是，不一定要求列名匹配。
事实上，MySQL甚至不关心SELECT返回的列名。
它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。
INSERT语句一般不会产生输出
省略列
如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件。
该列定义为允许NULL值（无值或空值）。
在表定义中给出默认值。这表示如果不给出值，将使用默认值。
如果对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功。
INSERT LOW_PRIORITY_INTO
降低INSERT语句的优先级
适用于UPDATE和DELETE
第20章 更新和删除数据
更新数据
UPDATE customers SET cust_email='elmer@fudd.com' WHERE cust_id = 10005;
删除数据
DELETE FROM customers WHERE cust_id=10006;
DELETE语句从表中删除行，甚至是所有行，但不删除表本身
TRUNCATE TABLE可以删除所有行，比DELETE 快
指导原则
除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）
在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。
MySQL没有撤销（undo）按钮。应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。
第21章 创建和操纵表
创建表
交互式创建和管理表的工具
直接用MySQL语句操纵
创建表customers
在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。
使用NULL值
NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。
多个主键
LAST_INSERT_ID()
LAST_INSERT_ID()是MySQL中的一个函数，自动返回最后一个INSERT或UPDATE查询中AUTO_INCREMENT列设置的第一个表发生的值。
指定默认值
默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定
与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。
引擎类型
指定引擎ENGINE=InnoDB
InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
使用一个引擎的表不能引用具有使用不同引擎的表的外键。
更新表
添加一个列
ALTER TABLE vendors ADD vend_phone CHAR(20);
删除一个列
ALTER TABLE vendors DROP COLUMN vend_phone;
定义外键
ALTER TABLE orderitems ADD CONSTRAIN fk_orderitems_orders FOREIGN KEY(order_num) REFERENCES orders(order_num);
备注：
使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份（模式和数据的备份）。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。
删除表
DROP TABLE customers2;
最好不用这条指令
重命名表
第22章 使用视图
视图
虚拟的表
重用SQL语句。
简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节。
使用表的组成部分而不是整个表。
保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
规则和限制
与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）。
对于可以创建的视图数目没有限制。
为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图。
ORDER BY可以用在视图中，但如果从该视图检索数据的SELECT语句中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
视图不能索引，也不能有关联的触发器或默认值。
视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句。
使用视图
基础规则
视图用CREATE VIEW语句来创建。
使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
用DROP删除视图，其语法为DROP VIEW viewname;。
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE ORREPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。
简化复杂联结
创建视图，简化表查询
使用视图，得到查询结果
格式化检索出的数据
SELECT* FROM vendorlocations;
过滤不想要的数据
SELECT* FROM customeremaillist;
使用视图与计算字段
SELECT * FROM orderitemsexpanded WHERE order_num=20005;
视图更新
可更新
主要用于数据检索
不可更新
分组、联结、子查询、并、聚集函数、DISTINCT、导出（计算）列
一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）。
第23章 使用存储过程
什么是存储过程？
为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。
为什么要使用存储过程
优点
通过把处理封装在容易使用的单元中，简化复杂的操作
由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一（试验和测试）存储过程，则所使用的代码都是相同的。
简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
提高性能。因为使用存储过程比使用单独的SQL语句要快。
存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码
缺点
存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
你可能没有创建存储过程的安全访问权限。许多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许他们创建存储过程。
使用存储过程
执行
创建
CREATE PROCEDURE productpricing()BEGIN SELECT Avg(prod_price) AS priceaverage FROM products;END;
DELIMITER //
定义//为语句结束
//也可以替换为别的符号，只要可以区别;即可
删除
DROP PROCEDURE productpricing;
如果该存储过程不存在，会报错
DROP PROCEDURE productpricing IF EXISTS;
如果该存储过程不存在，不会报错
使用参数
样例
所有MySQL变量都必须以@开始
SELECT @priceaverage;
参数作用
传递给存储过程
OUT
从存储过程传出
INOUT
对存储过程传入和传出
其他关键字
--
注释
DECLARE
声明变量名和数据类型
COMMENT
评论，将在SHOW PROCEDURE STATUS的结果中显示
IF
ELSE
THEN
ELSEIF
检查存储过程
SHOW CREATE PROCEDURE ordertotal；
显示用来创建一个存储过程的CREATE语句
SHOW PROCEDURE STATUS;
获得存储过程详细信息
第24章 使用游标
游标
有时需要在检索出来的行中前进或后退一行或多行，这就需要用到游标cursor
只可作用于存储过程
使用游标
在能够使用游标前，必须声明（定义）它。这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。
一旦声明后，必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
对于填有数据的游标，根据需要取出（检索）各行。
在结束游标使用时，必须关闭游标。
创建游标
CREATE PROCEDURE processorders()BEGIN DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;END;
打开游标
OPEN ordernumber；
声明游标后，不需要再次声明。需要用到时打开即可
关闭游标
CLOSE ordernumbers;
隐式关闭
如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它
FETCH
在一个游标被打开后，可以使用FETCH语句分别访问它的每一行
第25章 使用触发器
触发器
创建触发器
四条信息
唯一的触发器名
在每个表中唯一，但不是在每个数据库中唯一，不过建议还是在整个数据库中唯一
触发器关联的表
触发器何时执行（处理之前或之后）
BEFORE和AFTER
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';
备注信息
只有表才支持触发器，视图不支持（临时表也不支持）
删除触发器
DROP TRIGGER newproduct;
触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。
使用触发器
INSERT触发器
在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行
在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）
对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;
每次向orders表中插入一行，就会返回order_num的值，这个值是orders中的自增值
DELETE触发器
在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
OLD中的值全都是只读的，不能更新。
BEGIN和END代表这是一个触发器体，可以容纳多条SQL语句
UPDATE触发器
在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；
在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);
重点
与其他DBMS相比，MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。
应该用触发器来保证数据的一致性（大小写、格式等）。在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关。
触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。
遗憾的是，MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。
第26章 管理事务处理
事务处理
事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
也就是说，对于一种操作，若是因为异常原因中断，那么可以回滚到之前的状态
事务（transaction）指一组SQL语句；
回退（rollback）指撤销指定SQL语句的过程；
提交（commit）指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。
控制事务处理
事务的开始
START TRANSACTION
使用ROLLBACK
SELECT * FROM ordertotals;START TRANSACTION;DELETE FROM ordertotals;SELECT * FROM ordertotals;ROLLBACK;SELECT * FROM ordertotals;
ROLLBACK不能回退CREATE和DROP操作
使用COMMIT
START TRANSACTION;DELETE FROM orderitems WHERE order_num=20010;DELETE FROM orders WHERE order_num=20010;COMMIT;
事务的关闭
当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。
使用保留点
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。
设置保留点
SAVEPOINT delete1;
回退到保留点
ROLLBACK TO delete1;
保留点自动释放，也可以使用RELEASE SAVEPOINT主动回退
更改默认的提交行为
SET autocommit=0;
MySQL在进行SQL语句执行时，是自动提交的。若如此设置，将会用COMMIT主动提交
第27章 全球化和本地化
使用字符集和校对顺序
SHOW CHARACTER SET;
查看所支持的字符集完整列表
SHOW COLLATION;
查看所支持校对的完整列表
SHOW VARIABLES LIKE 'character%';SHOW VARIABLES LIKE 'collation%';
第28章 安全管理
访问控制
应该严肃对待root登录的使用。仅在绝对需要时使用它（或许在你不能登录其他管理账号时使用）。不应该在日常的MySQL操作中使用root。
数据库要为特定的人设置好权限
管理用户
USE mysql;SELECT user FROM user;
创建用户账号
CREATE USER ben IDENTIFIED BY 'p@$$w0rd'
新创建的用户账号没有访问权限，需要主动设置权限
删除用户账号
DROP USER bforta;
设置访问权限
查看权限
SHOW GRANTS FOR bforta;
设置权限
GRANT SELECT ON crashcourse.* TO bforta;
为用户bforta给予crashcourse数据库所有表的SELECT权限，也就是只读权限
取消权限
REVOKE SELECT ON crashcourse.* FROM bforta;
为用户bforta取消crashcourse数据库所有表的SELECT权限
简化多次授权
权限层次
整个服务器，使用GRANT ALL 和 REVOKE ALL
整个数据库，使用ON database.*
特定的表，使用ON database.table
特定的列
特定的存储过程
所有权限
更改口令
SET PASSWORD FOR bforta=Password('n3w_p@$$wOrd')
为用户bforta更改密码
SET PASSWORD = Password('n3wsde#$s')
为当前登录用户更改口令
第29章 数据库维护
备份数据
使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件。在进行常规备份前这个实用程序应该正常运行，以便能正确地备份转储文件。
可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）。
可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用RESTORE TABLE来复原。
数据库维护
检查表键是否正确
ANALYZE TABLE orders;
CHECK TABLE支持一系列的用于MylSAM表的方式
CHANGED检查自最后一次检查以来改动过的表
EXTENDED执行最彻底的检查，FAST只检查未正常关闭的表
MEDIUM检查所含有被删除的链接并进行键检验
QUICK只进行快速扫描
REPAIR TABLE 修复相应的表
OPTIMIZE TABLE 收回所用的空间，从而优化表性能
诊断启动问题
命令行执行mysqld手动启动服务器
重要命令行选项
--help显示帮助——一个选项列表
--safe-mode装载减去某些最佳配置的服务器
--verbose显示全文本消息（为获得更详细的帮助消息与--help联合使用）
--version显示版本信息然后退出
查看日志文件
错误日志。
它包含启动和关闭问题以及任意关键错误的细节
此日志通常名为hostname.err，位于data目录中。
此日志名可用--log-error命令行选项更改。
查询日志。
它记录所有MySQL活动，在诊断问题时非常有用。
此日志文件可能会很快地变得非常大，因此不应该长期使用它。
此日志通常名为hostname.log，位于data目录中。
此名字可以用--log命令行选项更改。
二进制日志。
它记录更新过数据（或者可能更新过数据）的所有语句。
此日志通常名为hostname-bin，位于data目录内。
此名字可以用--log-bin命令行选项更改。
注意，这个日志文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日志。
缓慢查询日志。
顾名思义，此日志记录执行缓慢的任何查询。
这个日志在确定数据库何处需要优化很有用。
此日志通常名为hostname-slow.log，位于data目录中。
此名字可以用--log-slow-queries命令行选项更改。
第30章 改善性能
改善性能
首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。
一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
MySQL是用一系列的默认设置预先配置的，这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES；和SHOW STATUS;。）
MySQL是一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。
使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。
应该总是使用正确的数据类型。
决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）。
有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT索引），然后在导入完成后再重建它们。
必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，你能看到极大的性能改进。
索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）
LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
最重要的规则就是，每条规则在某些条件下都会被打破。
0 条评论
回复 删除
下一页
