文章目录
MySQL必知必会学习笔记
1.选择数据库
当你最初连接到MySQL的时候，没有任何数据库打开供你使用。在你要操作任意数据库之前，需要先选择一个数据库，为此，可以使用USE关键字
# 输入
use weather;
# 输出
Database changed
2.数据库和表
如果不知道使用的数据库名怎么办，可以使用SHOW
显示库
输入：SHOW DATABASES;
输出：
分析：以上命令返回可用数据库的一个列表，包含MySQL内部使用的数据库
输入：SHOW TABLES;
输出：
分析：以上命令返回当前选择的数据库内可用的表
显示表列
输入：SHOW COLUMNS FROM t_comment;
输出：
分析：以上命令要求给出一个表名，对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息(如：auto_increment)
特别的：
-
自动增量：某些表列需要唯一值，每个行添加到表中时，MySQL可用自动为每个行分配下一个可用编号，不用添加一行时手动分配唯一值
-
DESCRIBE：用本命令代替SHOW [] FROM
3.检索数据
SELECT关键字 检索表数据
# 从hot表中检索名为year的列
SELECT year FROM hot;
从一个表中检索多个列，用都好将列名隔开，最后一个列名不加
检索所有列：在列名位置使用*通配符
SELECT * FROM hot;
检索内容不同的行：从结果上看，year内容重复的有很多，那么可以使用DISTINCT关键字，放在列名前面
SELECT DISTINCT year FROM hot;
可以看到，内容重复的列都被剔除，值得注意的是，DISTINCT只生效一个列名，如果多个列名都要提出内容相同的列，请声明多个
限制结果，使用limit子句
# 返回前5行数据
SELECT year FROM hot LIMIT 5;
# 返回从第5行开始的5行
SELECT year FROM hot limit 4, 5
# 等价写法
LIMIT 4 OFFSET 5
特别的，行号是从0开始，而不是1开始，类似数组下标，如图：
4.排序
排序数据，使用ORDER BY子句，对一个或多个列进行排序，特别的，默认是升序排序，要使用降序还得用DESC，升序使用ASC，但是意义不大
特别的，ORDER BY和DISTINCT会互相影响
# 一个列降序排序，并且去重和限制5条
SELECT DISTINCT year FROM hot ORDER BY year DESC LIMIT 5;
# 检索多个列，先按年排序，相同才按月排序
SELECT year, motn FROM hot OPDER BY year, month DESC LIMIT 5;
# 最值查询，利用LIMIT和ORDER BY
5.过滤数据
搜索条件/过滤条件：使用WHERE子句筛选
# 月等于12
SELECT year, DISTINCT month FROM hot WHERE month = 12 LIMIT 5;
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
# 字符串
SELECT username FROM user WHERE username = 'emma';
字符串要用引号，数值则不用
范围检索使用BETWEEN子句，开始值和终止值用AND连接
# 查询1到12月份
SELECT month FROM hot WHERE month BETWEEN 1 AND 12;
6.数据过滤
前文介绍的WHERE都是单一的条件，接下来介绍逻辑操作符IN、OR和NOT
# 年份2005且月份小于等于5
SELECT year, month FROM hot WHERE year = 2005 AND month <= 5;
# 年份等于2005或者2006，并去重
SELECT DISTINCT year FROM hot WHERE year = 2005 OR year = 2006;
# 范围IN，功能等价于OR，多个数据用逗号分割且数据必须全在小括号里，IN和圆括号之间可以有空格
# 如果数据是字符串，要注意引号
SELECT DISTINCT year FROM hot WHERE year IN (2005, 2006);
SELECT username FROM user WHERE username IN('admin', 'emma');
# NOT 用来否定后面条件的关键字，Mysql可以对IN, BETWEEN EXISTS(是否存在)进行否定
SELECT year FROM hot WHERE year NOT IN (2005, 2006);
# 特别的，SQL语言会优先处理AND然后才是OR，所以要注意次序，可以用小括号
# 查询2005年11或12月，以下是错误示范
SELECT year, month FROM hot WHERE month = 12 OR month = 11 AND year = 2005;
# 正确
SELECT year, month FROM hot WHERE (month = 12 OR month = 11) AND year = 2005;
错误的：可以看到，不仅有2005年的，还有其他年份的
正确的：
所以，任何时候使用IN和OR的WHERE子句，都要加上小括号
通配符过滤
搜索模式：由字面值、通配符或者两者组合构成的搜索条件
通配符，用来匹配值的一部分的特殊字符，实际是SQL的WHERE子句中的特殊含义的字符
| 符号 | 含义 |
|---|---|
| % | 匹配任意个 |
| _ | 匹配一个 |
# 以jet开头的产品
SELECT prod_id, prod_name FROM products WHERE produts WHERE prod_name LIKE '%jet';
# 以jet结尾
SELECT prod_id, prod_name FROM products WHERE produts WHERE prod_name LIKE 'jet%';
# 以s开头，e结尾
SELECT prod_id, prod_name FROM products WHERE produts WHERE prod_name LIKE 's%e';
# 文本中任意位置，含有anvil
SELECT prod_id, prod_name FROM products WHERE produts WHERE prod_name LIKE '%anvil%';
# _
SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_zhangsan';
小结
- 看似%可以匹配任何东西，但是NULL不行
- 不要过度使用，通配符，如果其他操作符也可以达到目的，请使用其他操作符
- 如果不得不使用到通配符，请把通配符放在搜索模式的结尾，因为开始处是最慢的
正则表达式
正则表达式是用来匹配文本的特殊的串，如果你想从一个文本文件中提取电话号码或者查找名字中间有数字的所有文件，都可以使用正则表达式
| 符号 | 作用 | 示例1 | 示例2 | 示例3 |
|---|---|---|---|---|
| . | 匹配任意一个字符 | .00，任意以一个字符开头，且后面是两个00的值，如100，a00 | ||
| | | 或 | a|b，匹配a或b | (ab)|(cd)，匹配ab或cd | |
| [] | 匹配特定字符 | [123]，匹配1或2或3 | [1-9]，匹配1或2或3或4…或8或9 | |
| \\ | 转义字符 | \\.，表示要查找带有.字符的值 | ||
| ？ | 可选字符 | heap?，会匹配结果hea或heap，p变成可选字符 | ||
| ^ | 匹配行首 | 1，以0或1或2…或9开头 | ||
| ^ | 否定(只有和[]一起使用时) | [^0-9]匹配不是以0或1或2…或9 | ||
| $ | 匹配行尾 | [0-9]$，以0或1或2…或9结尾 | ||
计算字段
存储在数据库表中的数据，但是这些数据一般不是应用程序需要的格式，如：
- 想在一个字段中显示公司名和地址，但是者两个信息不在一个表里
- 物品订单表存储物品的价格和数量，但是不需要存储每个物品的总价格，但是为了打印发票，却需要物品的总价格
- 根据表的数据进行某种计算等
特别的：
-
计算字段并不实际存在于数据库表中，而是运行时在SELECT语句内创建
-
字段和列类似，列一般指数据库表中的列，字段一般指运行时创建的不存在的列
假设有如下情况：
vendors表中包含供应商名字和位置信息，假如要生成一个供应商报表，需要在供应商的名字中按照name(loaction)这样的格式列出供应商的位置，此报表需要单个值，需要用括号将vend_country括起来，这些东西没有明确存储在数据库表里
拼接：把值联结到一起构成单个值
Concat()函数
用Concat()函数拼接两个列
# 输入
SELECT Concat(vend_name, '(', vend_contry, ')')
FROM vendors
ORDER BY vend_name;
Trim函数
- Trim()，删掉左右两边的空格
- LTrim()，删掉左边的空格
- RTrim()，删掉右边的空格
# 输入
SELECT Concat('A', Trim(' 123 '), 'B');
SELECT Concat('A', LTrim(' 123 '), 'B');
SELECT Concat('A', RTrim(' 123 '), 'B');
# 输出
A123B
A123 B
A 123B
别名
AS关键字对字段或值赋予别名，也称为导出列
# 输入
SELECT Concat(vend_name, '(', RTrim(vend_conutry), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
算术计算
使用算术符号：
-
-
-
- /
# 汇总物品的价格
SELECT prod_id, quantity, item_price, quantity * item_price AS expanded_price
FROM orederitems
WHERE order_num = 20005;
使用数据处理函数
文本处理函数
| 函数 | 说明 |
|---|---|
| Left() | 返回串左边的字符 |
| Right() | 返回串右边的字符 |
| Length() | 返回串的长度 |
| Locate() | 找出串的一个字串 |
| Upper() | 将串转换为大写 |
| Lower() | 将串转换为小写 |
| Soundex() | 返回串的SOUNDEX值 |
| subString() | 返回字串的字符 |
# 输入，将名字转化成大写
SELECT vend_name, Upper(vend_name), AS vend_name_upcase
FROM vendors
ORDERED BY vend_name;
特别的，SOUNDEX值是一个将任何文本串转化为其语音表示的字母数字模式的算法，使得能够对串进行发音比较而不是字母比较，也就是匹配发音类似的，如：
# 匹配发音类似Y.Lie
SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y.lie');
日期和时间处理函数
| 函数 | 说明 |
|---|---|
| AddDate() | 增加一个日期(天、周等) |
| AddTime() | 增加一个时间(时、分等) |
| CurDate() | 返回当前日期 |
| CurTime() | 返回当前时间 |
| Date() | 返回日期时间的日期部分 |
| DateDiff() | 计算两个日期的时间差 |
| Date Add() | 高度灵活的日期运算函数 |
| Date Format() | 返回一个格式化的日期或者时间串 |
| Day() | 返回一个日期的天数部分 |
续表
| 函数 | 说明 |
|---|---|
| DayOfWeek() | 对于一个日期，返回它是星期几 |
| Hour() | 返回一个时间的小时部分 |
| Minute() | 返回一个时间的分钟部分 |
| Month() | 返回一个时间的月份部分 |
| Now() | 返回当前日期和时间 |
| Second() | 返回一个时间的秒部分 |
| Time() | 返回一个日期时间的时间部分 |
| Year() | 返回一个时间的年份部分 |
若目标order_data(数据类型为datetime)为2005-09-01 11:30:05
WHERE order_date = '2005-09-01' # 匹配失败
WHERE Date(order_date) = '2005-09-01' # 匹配成功
# 检索order_date为2005年9月的所有行
WHERE year(order_date) = 2005 AND Month(order_date) = 9;
# 匹配月份范围
SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
数值处理函数
| 函数 | 说明 |
|---|---|
| Abs() | 返回绝对值 |
| Cos() | 返回一个角度的余弦值 |
| Exp() | 返回一个数值的指数 |
| Mod() | 返回除操作的余数部分 |
| Pi() | 返回圆周率 |
| Rand() | 返回一个随机数 |
| Sin() | 返回一个角度正弦 |
| Start() | 返回一个数值的平方根 |
| Tan() | 返回一个角度的正切值 |
汇总数据
我们经常需要汇总数据而不用实际把它们检索处理，如
- 确定表中的行数
- 获得表中行的和
- 找出表列的最大值、最小值和平均值等
聚集函数
| 函数 | 说明 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值之和 |
AVG函数
对表中行数计数并计算特定列值之和，求得该列的平均值，可以求特定列也可以求所有列
# 返回所有产品的平均价格
SELECT AVG(prod_price) AS avg_price
FROM products;
# 计算特定供应商的产品的价格
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
# 这条语句过滤除id = 1003的产品，因此，求平均值时候只求出该供应商的平均值
COUNT()函数
可以用COUNT含税确定表中行的数目或者特点行的数目
有两种使用方式：
- COUNT(*),对任何数目进行计数，不论是否NULL
- COUNT(column)，对特定列进行计数，忽略NULL
# 统计客户总数
SELECT CUONT(*) AS num_cust
FROM customer;
MAX()函数
返回指定列的最大值
MIN()函数
返回指定列的最小值
SUM()函数
返回指定列值的和
# 返回订单号为20005的总订单金额
SELECT SUM(item_price * quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
聚集不同的值
对以上的函数都可以如下使用：
- 对所有的行执行计算，指定ALL参数或者不给参数，因为ALL是默认行为
- 只包含不同的值，可以使用DISTINCT
组合聚集函数
SELECT CONUT(*) AS num_items,
MIN(prod_price) AS price_min,
MAX(prod_price) AS price_max,
AVG(prod_price) AS price_avg
FROM products;
分组数据
我们现在已经可以返回特定供应商的产品数目，但是如果我们要返回某个供应商的产品数目怎么办？某个10个以上产品的供应商怎么办？
所以需要把数据分为多个逻辑组，对每个组进行逻辑计算
创建分组：使用GROUP BY语句
- 可以包含任意数目的列，可以对分组进行嵌套
- 若进行了嵌套，数据在最后规定的分组上进行汇总
- 每个列都必须是检索列或有效的表达式
- SELECT 语句中的每个列都必须在GROUP BY子句中给出
- 若分组列中有NULL值，NULL将作为一个分组返回
下面看一个例子：
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
上面SELECT子句指定了两个列，vend_id和一个别名列(实际是由CONUT函数创建)，GRPOUP BY子句表示要按照vend_id分组。这表示，对每个分组出来的结果求COUNT，而不是整个表
过滤分组
除了能用GROUP BY分组外，MySQL还支持过滤分组，也就是可以自行决定留下哪些组，排除哪些组。
我们已经学会了WHERE 的作用，但是WHERE 不能过滤分组，只能过滤行，于是就有了HAVING，事实上，目前为止的WHERE都可以用HAVING代替。
唯一的差别就是WHERE过滤的是行，HAVING过滤分组，下面请看例子：
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
这条语句前三行类似前一个例子，最后一行加了HAVING，也就是过滤掉了COUNT(*) >=2的分组
关于差别，还可以这样理解，WHERE是在分组前进行过滤，HAVING是在分组后进行过滤，因此，如果用WHREE排除行，可能会影响HAVING的结果
再看一个例子：
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
本例中，WHERE过滤掉所有prod_price小于10的部分，然后按vend_id分组，对分组结果<2的进行过滤
SELECT子句的顺序
| 函数 | 说明 | 是否必须使用 |
|---|---|---|
| SELECT | 要返回的列或表达式 | 是 |
| FROM | 从检索数据的表 | 仅从表选择数据时使用 |
| WHERE | 行级过滤 | 否 |
| GROUP BY | 分组 | 仅按组计算时候使用 |
| HAVING | 组级过滤 | 否 |
| ORDER BY | 输出排序 | 否 |
| LIMIT | 要检索的行数 | 否 |
子查询
子查询：嵌套在其他查询中的查询(实际使用时由于性能的限制，不能嵌套太多的子查询)
下面看例子：
- 查询包含物品TNT的所有订单的编号
- 查询具有前一步列出的订单编号的客户的id
- 查询前一步客户id对应的客户信息
以上每个步骤都可以单独作为一个查询来执行，可以把一条SELECT语句返回的结果作用于另一条SELECT语句的WHERE子句
因此，有：
# 查询包含物品TNT的所有订单的编号
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT';
# 书中输出，20005和20007
# 查询具有前一步列出的订单编号的客户的id
SELECT cust_id
FROM orders
WHERE order_num IN(20005, 20007);
# 书中返回10001，10004
# 现在把第一个查询变成子查询
SELECT cust_id
FROM orders
WHERE order_num IN
(
SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT'
);
# 输出结果还是10001，10004
所以，可以看出，SELECT 语句中，子查询总是从内向外处理并且，需要保证SELECT返回的语句具有和WHERE子句相同数目的列
使用计算字段作为子查询
假如需要显示客户表中每个用户的订单总数，那么需要：
- 检索客户列表
- 对于查询出的每个客户，统计其在orders表中的订单数目
SELECT cust_name,
cust_state,
(
SELECT COUNT(*)
FROM orders
WHERE orders.id = customers.cust_id
) AS orders
FROM customers
ORDER BY cust_name;
这条SELECT语句对customers中每个客户返回3列，cust_name，cust_state和orders
其中orders是一个计算字段，由圆括号中的子查询建立的
连接表
联结就是把两个或两个以上表的行结合起来
- 内部联结，又称之为等值联结
- 自然联结
- 外部联结
请先看联结的例子：
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name;
这条语句的FROM有两个表，WHERE指定的列一个在vendors表中，另一个在products表中
没有任何联结条件的查询语句将会返回表的笛卡尔积
目前为止使用的联结都是内联结，不过有专门的语法可以指明是内联结：
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
以上效果完全等同于之前的例子，只不过用INNER JOIN指明是内联结，并且条件用ON表示而不是WHERE
那么现在来试试联结多个表，事实上可以联结无数个表，联结的规则也相同，先列出所有表，然后定义表直接的关系，如：
# 从三个表中查询出编号为20005的订单中物品的信息
SELECT prod_name, vend_name, prod_price, quantity
FORM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND order_num = 20005;
创建高级联结
自联结
在单条SELECT中多次使用相同的表
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';
以上语句完全合法，但是对于products不合法，如果不使用别名进行联结，那么就会有两个vend_id和两个prod_id，MySQL就会不知道使用哪一个，所以使用别名来特别指明是哪一个
自然联结
无论何时对表进行联结，应该至少有一个列出现在不止一个表中，甚至相同的列出现多次，自然联结则会排除多次出现，使得每一个列只返回一次
其实这个工作是程序员来完成的，而不是MySQL，也就是这个任务是程序给出的WHERE等过滤条件来筛选出你要的列
外部联结
许多联结将一个表中的行和另一个表中的行想关联，但有时候需要包含没有关联行的那些行，如：
- 对于每个客户下了多少订单进行计数，包括那些至今尚未下单的客户
- 列出所有产品以及订购数量，包括没有人订购的产品
- 计算平均销售规模，包括那些至今尚未下订单的客户
那么，对于每个客户下了多少订单进行计数，包括那些至今尚未下单的客户有：
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;
使用带有聚集函数的联结
# 检索所有客户以及每个客户下的订单数
SELECT customers.cust_name,
customers.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;
组合查询
利用UNION操作符将多条SELECT语句组合成一个结果集
UNION的使用很简单，就是给出每条SELECT语句，在每条语句之间放上关键字UNION
# 需要价格小于等于5的所有物品的一个列表，并且还想包括供应商1001和1002生产的所有物品
# 虽然WHERE也可以实现，但是这次使用UNION
# 先看单条语句，价格小于等于5的所有物品
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
# 供应商1001和1002生产的物品
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
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);
如你所见，UNION就是这么简单，但是需要注意：
- UNION必须由两条或两条以上的SELECT语句组成，语句之间使用UNION分割
- 每个查询必须包含相同的列、表达式或者聚集函数
- 列数类型必须兼容
UNION ALL
返回所有匹配的行，而不是自动取消重复的行
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 products
WHERE vend_id IN (1001, 1002);
特别的
对组合查询结果进行排序的话，只能使用一条ORDER BY语句，并且必须出现在最后一条SELECT语句之后，将会排序所有SELECT 语句返回的结果
18.全文本搜索
截至目前，我们已经学会了通配符匹配文本和正则表达式匹配
尽管它们很好用，但是却存在几个重要的限制
- 性能，通配符和正则表达式通常要求MySQL匹配表中所有行
- 明确控制，使用通配符和正则表达式很难明确的控制匹配什么和不匹配什么
- 智能化结果，它们都不能提供一种智能化的选择结果的方法，如，一个特殊词的搜索将会返回多个包含该词的所有行
幸运的是，以上的问题，全文本搜索都可以结果，只不过仅部分数据库引擎支持罢了
一般在创建表的时候启用全文本搜索
CREATE TABLE priductnotes
(
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;
以上语句创建一个priductnotes表，表中含有note_text列，为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的只是对他进行索引，当然，FULLTEXT可以索引多个列
定义以后，MySQL自动维护该索引，在增加、更新或删除行时，索引也随之更新。
可以在创建表的时候指定FULLTEXT，或者稍后指定
特别的：
不要在导入数据时使用FULLTEXT：应该先导入所有数据，然后再修改表，定义FULLTEXT，有助于更快的导入数据
现在，咱们可以开始全文本搜索了，只要使用**Match()**指定被搜索的列，**Against()**指定要使用的表达式
请看例子：
SELECT note_text
FROM products
WHERE Match(note_text) Against('rabbit');
此SELECT语句检索单个列note_text。Match指定note_text列，Against指定单词rabbit作为搜索文本
特别的：
- 全文本搜索不区分大小写，因此需要区分大小写时，请使用BINARY
- 全文本搜索会进行结果排序
查询扩展
查询扩展用来放宽查询的全文本搜索结果的范围，假设你想查找evil，但是只有一行含有evil，你还想找出和你搜索有关的所有其他行，即使这些行不包含evil
使用查询扩展的时候，MySQL会对数据和索引进行两边扫码来完成搜索
- 首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有的行
- 其次，MySQL检查这些匹配行并选择所有有用的词
- 最后，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词
例如：
SELECT note_text
FROM products
WHERE Match(note_text) Against('evil' WITH QUERY EXPANSION);
布尔文本搜索
以布尔方式进行全文本搜索，可以提供
- 要匹配的词
- 要排斥的词
- 排列提示
- 表达式分组
- 另外一些内容
例如：
# 匹配单词heavy，但是排除rope*
SELECT note_text
FROM priducts
WHERE Match(note_text) Against('heavy-rope*' IN BOOLEAN MODE);
全文本搜索，布尔操作符
| 布尔操作符 | 说明 |
|---|---|
| + | 包含，词必须出现 |
| - | 排除，词必须不出现 |
| > | 包含，而且增加等级值 |
| > | 包含，但是减少等级值 |
| () | 把词组成子表达式(允许这些子表达式作为一个组被包含、排除、排列等) |
| ~ | 取消一个词的排序值 |
| * | 词尾的通配符 |
| “” | 定义一个短语 |
一些例子：
# 搜索匹配包含词rabbit和bait
SELECT note_text
FROM products
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);
# 搜索匹配rabbit和bait至少一个词
SELECT note_text
FROM products
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);
# 匹配短语 rabbit bait而不是匹配两个词
SELECT note_text
FROM products
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);
# 匹配rabbit和carrot，增加前者的等级，降低后者的等级
SELECT note_text
FROM porducts
WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE);
小结
- 再索引全文本数据时，短词且从索引中排除，短词定义为那些具有3或 3个以下字符的词
- MySQL带有一个内建的非用词列表，这些词在索引全文本数据时总是被忽略，如果需要，可以覆盖这个列表
- 许多词出现的频率很高，搜索它们没有用处，因此，MySQL定义了一条50%规则，如果一个词出现50%以上的行中，则将它作为非用词忽略，50%规则不适用布尔全文本搜索
- 如果表中的行数少于3行，则全文本搜索不返回结果
- 忽略词中的单引号，例如don’t会被索引为dont
- 不具有词分割符的语言不能恰当地返回全文本搜索结果
- 尽在MyISAM数据库引擎中支持全文本搜索
19、插入数据
到现在为止，你已经是一个优秀的R boy了(read boy)，向着CRUD继续出发吧~
INSERT用来插入行到数据库表中，可以有几种方式使用：
- 插入完整的行
- 插入行的一部分
- 插入多行
- 插入某些查询的结果
插入完整的行
需要指定表名和被插入到新行中的值
INSERT INTO Customers
VALUES
(
NULL,
'Pep',
'100',
'Los',
'CA',
'90046',
'USA',
NULL,
NULL
);
第一列为NULL是因为MySQL设定了自动增量。虽然这种写法简单，但是并不安全，应该尽量避免使用，因为它高度依赖于表中列的定义次序，即使可以得到这种次序信息，但是不能保证下一次表结构变动后各个列保持完全相同的次序
因此，安全写法如下：
# 会更繁琐一些
# 列的顺序不一定要按表的设计的顺序，只有值和列是一一对应即可
# 所以不管表的结构如何变化，以下SQL语句一直成立(除非删除表的结构)
INSERT INTO customers
(
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
)
VALUES
(
'Pep',
'100',
'Los',
'CA',
'90046',
'USA',
NULL,
NULL
);
特别的，INSERT可能会很耗时，特别是有许多索引需要更新的时候，可能会降低待处理的SELECT语句的性能，因此你可以在INSERT和INTO之间添加关键字LOW_PRIORITY，降低INSERT语句的优先级
插入多个行
第一个办法就是写多个INSERT，更好的办法就是多个VALUES
# 一条INSERT有多组值，没组值用一对圆括号括号括起来，用逗号分割
INSERT INTO customers
(
cust_name,
cust_city,
cust_state,
cust_zip,
cust_country
)
VALUES
(
'Pep',
'100',
'Los',
'CA',
'90046',
'USA',
NULL,
NULL
),
(
'xxx',
'333',
'sss',
'ddd',
'33546',
'UK',
NULL,
NULL
);
插入检索出的数据
顾名思义，就是一条INSERT和SELECT语句组合，如：
# 此时列名并不重要，只会按对应的顺序填充新插入的行
# 这个例子含有cust_id，如果你不能保证cust_id不重复，你可以忽略这，由MySQL自动维护
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;
20、更新和删除数据
现在是你CR boy了，接下来努力当CURD boy吧~
更新UPDATE
使用UPDATE可以做：
- 更新表中特定的行
- 更新所有行
请看例子：
# 为用户10005更新邮箱
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
# 为用户10005更新邮箱和名字，列之间使用逗号分割
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
# 可以用UPDATE删除数据，设置为NULL即可，如
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
特别地：
- 可以在UPDATE语句中使用子查询来更新数据，见第14章：子查询
- 由于更新多行时，可能会发生错误，导致整个UPDATE操作被取消，因此可以使用IGNORE关键字，即使发生错误也可以继续执行，如：
UPDATE IGNORE customer
...
删除数据
使用DELETE语句中表中删除数据，请看例子：
# 删除指定的行
DELETE FROM customers
WHERE cust_id = 10006;
特别的，DELELE是删除行而不是列，要删除列请使用UPDATE，并且DELETE是删除表中的行，而不是删除表本身
如果想快速的删除表中的所有行，可以使用TRUNCATE TABLE语句，实际上是删除原来的表并新建一个表
小结
- 除非却是打算更新和删除每一行，否则绝对不要使用不带WHERE的UPDATE和DELETE
- 保证每个表都有主键，这样可以使用WHERE来指定它
21、创建和操纵表
创建表可以使用CREATE TABLE语句，要注意的是：
- CREATE TABLE要给出新表的名字
- 给出表列的名字和定义，用逗号分割
请看例子：
# 创建表时，必须确保表名不存在
CREATE TABLE customers
(
cust id int NOT NULL AUTO_INCREMENT
cust_name char(50) NOT NULL,
cust_addr char(50) NULL, # 可以为空
PRIMARY KEY (cust_id) # 指定主键
)ENGINE = InnoDB;
# 可以多个列组成组件
PRIMARY KEY (cust_id, cust_name)
# 指定默认值，不支持函数作为默认值，仅支持常量
CREATE TABLE customers
(
cust id int NOT NULL AUTO_INCREMENT
cust_name char(50) NOT NULL DEFAULT '1',
cust_addr char(50) NULL, # 可以为空
PRIMARY KEY (cust_id) # 指定主键
)ENGINE = InnoDB;
# 获得AUTO_INCREMENT值
# 返回最后一个AUTO_INCREMENT的值，然后可以用于后续的MySQL语句
SELECT last_insert_id()
AUTO_INCREMENT是告诉MySQL，每当本列增加一行时候自动增量，给每一行分配一个唯一的值，每个表只允许一个AUTO_INCREMENT，并且它必须被索引
特别的：如果没有指定数据库引擎则使用默认引擎，切记，外键不能跨引擎
| 数据库引擎 | 说明 |
|---|---|
| InnoDB | 可靠的事务处理 |
| MyISAM | 高性能，支持全文本搜索，但不适用事务处理 |
| MEMORY | 功能等同于MyISAM，单数据存储在内存，速度很快，适合临时表 |
更新表
为更新表的定义，可以使用ALTER TABLE，例如：
# 新增列
ALTER TABLE vendors
ADD vend_phone CHAR(20);
# 删除列
ALTER TABLE vendors
DROP COLUMN vend_phone;
删除表
DROP TABLE + 表名
重命名表
RENAME TABLE customers2 TO customers
backup_customers TO customer,
backup_vendors TO vendors;
使用视图
视图虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询
优点：
- 重用SQL语句
- 简化复杂的SQL操作
- 使用表的组成部分而不是整个表
- 保护数据
- 更改数据格式和表示
规则和限制：
- 命名必须唯一
- 对于可以创建的视图数目没有限制
- 为了创建视图，必须具有足够的访问权限
- 视图可以嵌套
- ORDER BY可以用于视图
- 视图不能索引，也不能有关联的触发器或者默认值
- 视图可以和表一起使用
下面请看例子：
# 查询订购了某个特定产品的客户
SELECT cust_name, cust_contact
FROM customers, order, orderitems
WHERE costomers.cust_id = orders.cust_id
AND orderitems.oder_num = orders.order_num
AND prod_id = 'TNT';
# 现在可以把整个查询包装成名为productcustomers的虚拟表，则可以：
SELECT cust_name, cust_contace
FROM productcustomers
WHERE prod_id = 'TNT';
# 使用CREATE VIEW 创建视图
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact
FROM customers, order, orderitems
WHERE costomers.cust_id = orders.cust_id
AND orderitems.oder_num = orders.order_num
# 使用SHOW CREATE VIEW ciewname; 查看创建视图
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT';
# DROP删除视图，语法为：DROP VIEW viewname;
# 更新视图：先DROP后CREATE，也可以之间CREATE OR REPLACE VIEW
# 如果视图不存在，则第二条语句会创建一个视图
# 如果视图存在，则会替换原有视图
使用视图重新格式化检索出的数据
视图的另一个场景用途是重新格式化检索出的数据，请看例子：
# 在单个组合计算列中返回供应商名称和位置
SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
# 假如需要经常使用这个格式的结果，不必每次需要的时候进行联结
# 只需要创建一个视图，每次需要的时候使用它即可
# 也就是创建了临时表
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_d, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
除此之外还可以简化计算字段等，背后的原理就是把经常使用的查询语句的结果做为一个临时表，然后对这个临时表进行查询操作
特别的：
视图是可以更新的，使用INSERT、UPDATE、DELETE
但是这个更新是由条件的，如果视图中定义了一下的东西，则不能更新：
- 分组(GROUP BY、HAVING)
- 联结
- 子查询
- 并
- 聚集函数
- DISTINCT
- 导出计算列
23、使用存储过程
现在你已经是一个合格的CURD boy了
接下来，努力当SQL boy吧~
迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。显然，我们有需要操作多个语句的时候，如：
- 为了处理订单，需要核对以保证库存中有相应的物品
- 关于哪些物品入库(并且可以立即发货)，和哪些物品退订，需要通知相应的客户
存储过程：简单来说就是为以后的使用而保存一条或多条SQL语句的集合
优点：
- 简单
- 安全
- 高性能
缺点：
- 编写复杂
- 可能没有创建存储过程的权限
创建存储过程
使用CREATE PROCEDURE语句，如：
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
该存储过程名为productpricing，用CREATE PROCEDURE productpricing()定义。如果存储过程接受参数，它们将会在()中列举出来，此例存储过程没有参数，但是圆括号是必须的
BEGIN和END限定存储过程体，过程体里仅是一条简单的SELECT语句
MySQL处理这段代码的时候，它创建一个存储过程，没有返回树，因此这段代码并未调用存储过程，这里只是为了以后使用它而创建
特别地，在上述例子中，有两个;号，要直到MySQL语句的分隔符为**;，但是MySQL命令行的分割符也为;**，这会导致语法错误，解决办法如下：
DELIMITER // # 告诉命令行以//作为新的分隔符
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END // # 使用新的分割符
DELIMITER ; # 记得改回来
那么现在的问题就是如何使用存储过程了，使用CALL关键字即可
删除存储过程
存储过程在创建以后，就被保存在服务器上以供使用，直至被删除
删除命令为：
# 注意是仅当存在的时候删除
DROP PROCEDURE productpricing;
使用参数
一般存储过程并不显示结果，而是把结果返回给你指定的变量(变量是指内存中一个特定的位置，用来临时存储数据)
CREATE PROCEDURE productpricing(
# DECIMAL是小数类型，数值共8位，小数点后有2位
OUT pl DECIMAL(8, 2),
OUT ph DECIMAL(8, 2),
OUT oa DECIMAL(8, 2)
)
BEGIN
SELECT Min(prod_price)
INTO pl
FROM products
SELECT Max(prod_price)
INTO ph
FROM products;
SELECT Avg(prod_price)
INTO pa
FROM products;
END;
此存储过程接受三个参数，pl存储最低价格，ph存储产品最高价格，pa存储平均价格
OUT关键字指出相应的参数用来从存储过程中传出一个值，返回给调用者，MySQL支持IN(传递给存储过程)，OUT（从存储过程中传出）以及INOUT从存储过程中传入传出
此时调用
# 在调用的时候，这条语句不显示任何数据
CALL productpricing(@pricelow,
@pricehigh，
@priceaverage
);
# 为了显示检索出的产品的平均价格，可以如下进行：
SELECT @priceaverage;
# 使用IN和OUT参数
# 接受订单编号并返回订单的合计
CREATE PROCEDURE ordertotal(
IN number INT,
OUT ototal DECIMAL(8, 2)
)
BEGIN
SELECT Sum(item+price * quantity)
FROM orderitems
WHERE order_num = onumber
INTO ototal
END;
# 调用
CALL ordertotal(2005, @total);
onumber定义为IN，因为订单号被传入存储过程，ototal定义为OUT，因为要从存储过程中返回合计
SELECT语句使用这两个参数，WHERE子句使用onumber过滤行，INTO使用ototal存储计算出来的合计
只能存储过程
假设如下场景，你需要合计订单，并且增加营业税，不过只针对某些客户，那么你要完成
- 获得合计
- 把营业税有条件的添加到合计
- 返回合计
于是乎：
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8, 2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
# 定义两个变量
DECLARE total percentage
DECLARE taxrate INT DEFAULT 6;
# 查询到的合计有条件的放入total总数中
SELECT Sum(item_price * quantity)
FROM orderitems
WHERE order_num = onumber
INTO total
// 如果增加税为真，那么增加税收到局部变量total中
IF taxble THEN
SELECT total + (total / 100 * taxrate) INTO total;
END IF;
# 不过是否为真，都保存到全局变量ototal中
SELECT total INTO ototal;
END;
# 使用该智能存储过程
CALL ordertotal(20005, 0, @total);
SELECT @total;
CALL ordertotal(20005, 1, @total);
SELECT @total;
最后，检查存储过程，使用SHOW CREATE PROCEDURE，如：
# 列出指定存储过程
SHOW CREATE PROCEDURE ordertotal;
# 列出所有
SHOW PROCEDURE STATUS;
# 过滤
SHOW PROCEDURE STATUS LIKE 'ordertotal';
24、游标
游标：一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集，MySQL游标只能用于存储过程和函数
有时候需要在检索出来的行中前进或后退一行，有了游标以后，应用程序就可以根据需要滚动浏览其中的数据
使用游标
- 必须声明(定义)它
- 一旦声明后，必须打开游标以供使用
- 对于填有数据的游标，根据需要取出各行
- 结束游标使用的时候，必须关闭游标
在声明游标后，可根据需要频繁的打开和关闭游标，游标打开后，可根据需要频繁的执行取操作
特别的，DECLARE与定义的局部变量必须定义在任意游标或者句柄之前，而句柄必须定义在游标之后
创建游标
使用DECLARE语句创建，命名游标并定义相应的SELECT语句，存储过程处理完后，游标就消失了
在一个游标被打开后，可以使用FEYCH语句分别访问它的每一行，它会向前移动游标中的内部行指针，使得下一条FETCH语句检索下一行
如：
CREATE PROCEDURE processorders()
BEGIN
# 定义局部变量o
DECLARE o INT;
# 定义游标
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
# 打开游标
OPEN ordernumbers;
# 获取数据到局部变量o中
# 自动从第一行开始
FETCH ordernumbers INTO o;
# 关闭游标
CLOSE ordernumbers;
END;
循环检索数据
使用REPEAT，它会反复执行直到done为真(由UNTIL done END REPEAT;规定)
DROP PROCEDURE IF EXITS processorders;
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
# 循环变量切记要带有初值
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
# 定义一个游标
DECLARE ordernumbers CURSOR
FOR
SELECT ouder_num FROM orders;
# 定义CONTINUE HANDLER
# 条件出现时被执行的代码
# 也就是当SQLSTATE '02000' 出现时，done = 1
# SQLSTATE '02000'是一个未找到条件，当循环没有更多行供循环而不能继续时候出现这个条件
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
# 打开游标
OPEN ordernumbers;
REPEAT
FETCH ordernumbers INTO o;
SELECT o;
# 直到done为真，才停止循环
UNTIL done END REPEAT;
# 关闭游标，释放游标使用的所有内存资源
CLOSE ordernumbers;
END //
DELIMITER;
一个更复杂的例子
以上例子我们只是取出数据，我们其实可以对数据进行更复杂的操作，请看例子：
DROP PROCEDURE IF EXITS processorders;
DELIMITER //
CREATE PROCEDURE processorders()
BEGIN
# 循环变量切记要带有初值
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
DECLARE t DECIMAL(8, 2);
# 定义一个游标
DECLARE ordernumbers CURSOR
FOR
SELECT ouder_num FROM orders;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
# 创建一个表，用于存储循环查询从数据
CREATE TABLE IF NO EXISTS ordertotals
(
order_num INT,
total DECIMAL(8, 2);
)
# 打开游标
OPEN ordernumbers;
REPEAT
# 循环获取变量，放到o里
FETCH ordernumbers INTO o;
# 调用另一个存储过程(23章定义的)，会把结果返回到t里
CALL prdertotal(o, 1, t);
# 插入
INSERT INTO ordertotals(order_num, total)
VALUES(o, t);
# 直到done为真，才停止循环
UNTIL done END REPEAT;
# 关闭游标，释放游标使用的所有内存资源
CLOSE ordernumbers;
END //
DELIMITER;
使用触发器
假设有如下场景：
- 每当增加一个客户时，都要检查电话号码是否正确
- 订购一个产品时，都要从库存中减去订购的数量
所以，使用触发器的目的，在某条或某些事件发生的时候自动执行
触发器：在MySQL响应以下任意语句而自动执行的一条MySQL语句
- DELETE
- INSERT
- UPDATE
创建触发器
使用CREATE TRIGGER语句
需要给出
- 唯一的触发器名
- 触发器关联的表
- 触发器应该响应的活动
- 触发器何时执行，处理之前还是之后
请看例子：
CREATE TRIGGER newproduct AFTER INSERT ON products;
FOR EACH ROW SELECT 'Product added';
# MySQL5以后不支持触发器返回结果集，要使用INTO
CREATE TRIGGER newproduct AFTER INSERT ON products;
FOR EACH ROW SELECT 'Product added' INTO @message;
以上语句创建了一个名为newprodduct的触发器，由于是AFTER INSERT 所以会在INSERT语句之后执行，这个触发器还指定了FOR EACH ROW，因此对于每个插入都会执行
本例中就是对每次插入操作都会显示字符串’Product added’
一些注意点：
- 只有表支持触发器，视图是不支持的
- 每个表最多支持6个触发器
输出触发器使用DROP TRIGGER + 触发器名，如：
DROP TRIGGER newproduct;
INSERT触发器
INSERT触发器在INSERT语句执行之前或者之后执行，需要直到以下几点：
- 在INSERT触发器代码内，可以使用一个名为NEW的虚拟表，访问被插入的行，这个表类似INSERT表的拷贝
- 在BEFORE INSERT触发器中，可以修改NEW表中的值
- 对于自动递增的列，NEW表中在INSERT之前是0，之后是自动生成的值
请看例子：
CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
此代码创建了一个neworder触发器，在INSERT语句执行之后执行，每次插入时，会有一个新的订单编号(order_num)，触发器用于获取这个编号并且返回
特别的：
- BEFORE触发器通常用于数据的验证和净化
- AFTER触发器通常用于数据的显示
DELETE触发器
DELETE触发器在DELETE语句执行之前或之后执行，需要知道以下两点
- 在DELETE触发器代码内，你可以使用OLD的虚拟表，访问被删除的行
- OLD中的值只读，不可写
下面看一个例子：
CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
INSERT INTO archive_orders(order_num, order_date, cust_id)
VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;
此触发器用于把删除的数据保存到一个叫archive_orders中，前提是这个表已经被创建
特别的，如你所见，BEGIN和END可以容纳多条SQL语句，对于DELETE语句这并不是必须的
UPDATE触发器
UPDATE触发器在UPDATE语句执行之前或者之后执行，需要知道以下几点：
- 在UPDATE触发器中，你可以使用OLD的虚拟表，返回旧值，可以使用NEW虚拟表访问新值
- 在BEFORE语句中，NEW可以被修改
- OLD的值只读，不可写
请看例子：
# 确保字母大写
CREATE TRIGGER uodateevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
26、事务处理
事务处理：一种机制，用来管理成批执行的SQL语句，保证数据库中不含不完整的操作结果
术语：
- 事务：一组SQL语句
- 回退：撤销执行批次的SQL语句的过程
- 提交：将未写入的SQL语句结果写入数据库表
- 保留点：事务处理中设置的临时占位符，可以在此回退
事务处理的关键在于，将SQL语句组逻辑分块，并明确规定数据何时回退
MySQL使用START TRANSACTION标识一个事务的开始
MySQL使用ROLLACK命令回退MySQL语句
下面看一个例子：
SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLACK;
SELECT * FROM ordertotals;
这个例子就是先显示原始数据，然后设置回退点，然后删除数据，显示删除以后的结果，再回退到检查点，然后再显示回退结果
如果正常执行，那么第一次显示的结果应该和第三次是一样的
特别的，事务处理是用来管理INSERT、UPDATE和DELETE的，不能回退SELECT，可以回退CREATE和DROP但是它们不会被撤销，也就相当于不能回退
使用COMMIT
一般的语句是隐含COMMIT的，执行完语句，就发生对应的动作，但是再事务管理这块并不会如此，需要明确的提交，即使用COMMIT
START TRANSACTION
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
这个例子中，从系统中删除订单20010， 因为涉及到两个数据库表的修改，所以使用事务管理保证订单不被部分删除
最后的COMMIT 语句仅仅再不出错的时候成功执行，如果第一条成功，第二条失败，那么不会影响到数据库
特别的：当COMMIT或者ROLLBACK之后，事务会自动关闭，将来的语句又会是隐含提交
使用保留点
简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理，但是只是对简单的事务管理才能这样做，更复杂的事务处理可能需要部分的提交或者回退
为了支持部分回退，需要在事务处理中合适的位置防止占位符，这样需要回退的时候可以回退到某个占位符，这些占位符被称之为保留点，如：
SAVEPOINT delete;
每个保留点都有唯一标识它的语句，以便在回退的时候知道要回退到哪里
于是：
ROLLBACK TO delete;
特别的：
-
保留点越多越好，这样你旧可以灵活的进行回退
-
保留点在事务处理完后自动释放，MySQL5以后可以使用RELEASE SAVEPOINT明确的释放
更改默认的提交行为
默认的MySQL语句都是自动提交所有更改，也就是实时生效，为了不自动提交，可以如下操作：
SET autocommit = 0;
特别的，autocommit是针对连接的，而不是服务器
27、本地化和全球化
字符集：字母和符号的集合
编码：某个字符集成员的内部标识
校对：规定字符如何比较的指令
# 查看MySQL所支持的字符集
SHOW CHARACTER SET;
# 查看所支持校对的完整列表
SHOW COLLATION;
# 确定所用的字符集和校对
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
# 给表指定字符集和校对 使用带子句的CREATE TABLE
# 创建一个包含两个列的表，并指定一个字符集和一个校对顺序
CREATE TABLE mytable
(
columnn1 INT,
columnn2 VARCHAR(10),
columnn3 VARCHAR(10) CHARACTER SET latin1
COLLATE litin1_general_ci #位特定的列指定字符集和校对
)DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci; # 直嘀咕字符集和校对
# 创建表时，不同的校对顺序排序指定的SELECT 语句
SELECT * FROM customers
ORDER BY lastname, firstname COLLATE latin1_gener_CS;
特别的，串可以在字符集直接进行转换，可以使用Cast()或Convert()函数
COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等
28、安全管理
访问控制：
- 防止用户恶意的企图
- 保证用户不能指定不该执行的语句
MySQL账号信息存储在名为mysql的数据库中
# 获取用户账号列表
USE mysql;
SELECT user FROM user;
mysql数据库中有一个名为user的表，保存所有用户
# 创建一个用户
# ben是用户名
# password是用户的密码
CREATE USER ben IDENTIFIED BY 'password';
# 为用户重命名
RENAME USER ben TO bob;
# 删除一个用户账号
DROP USER bob;
# 显示账号的权限
SHOW GRANTS FOR bob;
# 赋予权限
# 允许用户bob在crashcourse这个数据库里所有表进行查询
GRANT SELECT ON crashcourse.* TO bob;
# 回收权限
REVOKE SELECT ON crashcourse.* FROM bob;
# 更改密码
# 可以不指定用户名，这样就是修改当前用户
SET PASSWORD FOR bob = Password('123456');
# MySQL8使用如下命令
ALTER USER bob IDENTIFIED BY '123456';
flush privieges;
29、数据库维护
像所有数据一样，MySQL的数据也必须经常备份，由于MySQL数据库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据，但是由于这些文件总是处于打开和使用状态，普通的文件备份不一定总是有效
备份数据：
- 使用命令行mysqldump转储所有数据库内容到某个外部文件
- 使用命令行mysqlhostcopy从一个数据库复制所有数据
- 使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件，恢复数据则使用RESTORE TABLE
数据库的维护
ANALYZE TABLE检查表键是否正确
ANALYZE TABLE orders;
# CHECK TABLE 针对许多问题对表进行检查
CHECK TABLE orders, orderitems;
一些MySQL命令行选项
- –help 显示帮助文档
- –safe-mode 装载减去某些最佳配置的服务器
- –verbose 显示全文本信息
- –version 显示版本信息
日志文件
-
错误日志：包含启动和关闭问题已经任意关键错误的细节，通常名为hostname.err位于date目录中
-
查询日志：hostname.log
-
二进制日志：hostname-bin
-
缓慢查询日志：此日志记录执行缓慢的任何查询，通常名为hostname-slow.log
-
可以使用FLUSH LOGS刷新和重新开始所有日志文件
30、改善性能
MySQL是一个多用户多线程的DBMS，它经常同时执行多个任务，如果这些任务中的一个执行缓慢，那么所有的请求都会缓慢，可以使用SHOW PROCESSLIST 显示所有活动进程，可以使用KILL终结它
0-9 ↩︎
292
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
