Mysql必知必会笔记

最新推荐文章于 2022-10-12 22:58:32 发布

yxyibb

最新推荐文章于 2022-10-12 22:58:32 发布

阅读量1.3k

点赞数

分类专栏：数据库数据分析文章标签： mysql

本文链接：https://blog.csdn.net/u012835414/article/details/113930532

版权

数据分析同时被 2 个专栏收录

19 篇文章 0 订阅

订阅专栏

数据库

11 篇文章 0 订阅

订阅专栏

1 了解SQL

数据库概念

数据库：一个以某种有组织的方式存储的数据集合。
- 数据库：保存有组织的数据的容器(通常是一个文件或一组文件），不直接访问，通过DBMS创建和操纵的容器。
- 数据库软件：DBMS，数据库管理系统
表：某种特定类型数据的结构化清单。存储某种特定类型的数据。表可以保存顾客清单、产品目录,或者其他信息清单。
- 存在表中的数据是一种类型的数据或一个清单。
- 每个表有自己名字，标识自己。名字唯一。
- 模式（schema）关于数据库和表的布局及特性的信息。
列、数据类型
- 列（column）表中的一个字段。所有表由一个或多个列组成。
- 数据库中每个列都有相应的数据类型。数据类型定义列可以存储的数据种类。
- 数据类型（datatype）所容许的数据的类型。每个表列都有相应的数据类型,它限制(或容许)该列中存储的数据。
行：表中的一个记录。
主键（primary key）：一列(或一组列),其值能够唯一区分表中每个行。条件：
- 任意两行都不具有相同的主键值;
- 每个行都必须具有一个主键值(主键列不允许NULL值)
- 主键好习惯：1）不更新主键列中的值;2）不重用主键列的值;3）不在主键列中使用可能会更改的值。
SQL，Structured Query Language，与数据库通信语言查询

2MySQL简介

2.1简介

是一种DBMS，存储、检索、管理和处理数据的数据库软件。免费使用，执行很快，简单使用。不支持其他DBMS提供的功能和特性，但更新的版本正则增加功能。分：

基于共享文件系统的DBMS（Microsoft Access和FileMaker）
基于客户机-服务器的DBMS（MySQL、Oracle以及Microsoft SQL Server）。
1. 服务器负责所有数据访问和处理的一个软件，与数据文件打交道，运行在数据库服务器上；执行数据增删改查请求；
2. 客户机与用户打交道，发送请求。

2.2 MySQL工具

命令行实用程序
MySQL Administration（MySQL管理器）是一个图形交互客户机，用来简化MySQL服务器的管理
MySQL Query Browser，一个图形交互客户机，用来编写和执行MySQL命令

3 使用MySQL

连接-选择-show

连接：登录(主机名，端口，用户名，口令)
选择：use关键字
查表、列、用户等信息，show
- SHOW DATABASES 返回可用数据库的一个列表
- SHOW TABLES 返回当前选择的数据库内可用表的列表
- SHOW COLUMNS FROM customers(DESCRIBE customers)要求给出一个表名 ( 这个例子中的 FROMcustomers ),它对每个字段返回一行
- SHOW STATUS 显示广泛的服务器状态信息
- SHOW CREATE DATABASE和SHOW CREATE TABLE 创建特定数据库或表的MySQL语句
- SHOW GRANTS 显示授予用户(所有用户或特定用户)的安全权限
- SHOW ERRORS 和 SHOW WARNINGS 显示服务器错误或警告消息

4 Select语句

SELECT检索SELECT xx FROM xx(第0行)

从一个或多个表中检索信息
- 想选择什么
- 从什么地方选择
检索单个列
- SELECT prod_name FROM products;(未排序)
- 多条SQL语句必须以分号(;)分隔
- SQL语句不区分大小写
- 处理SQL语句时,其中所有空格都被忽略
检索多个列
- SELECT prod_id, prod_price, prod_name FROM products;
- SQL语句一般返回原始的、无格式的数据
检索所有列
- SELECT * FROM products;
- 一般,除非你确实需要表中的每个列,否则最好别使用 * 通配符。虽然使用通配符可能会使你自己省事,不用明确列出所需列,但检索不需要的列通常会降低检索和应用程序的性能。
检索不同行
- SELECT vend_id FROM products;会列出所有供应商ID，14行（14个产品，4个供应商）
- SELECT DISTINCT vend_id FROM products;只返回不同(唯一)的vend_id 行,因此只返回4行
- 不能部分使用 DISTINCT（应用于所有列而不仅是前置它的列，如果给出 SELECT DISTINCT vend_id,prod_price ,除非指定的两个列都不同,否则所有行都将被检索出来）
限制结果
- SELECT prod_name FROM products LIMIT 5;（LIMIT 限制返回不多于5行）
- SELECT prod_name FROM products LIMIT 5， 5;（返回从行5开始的5行，第一个开始位置，第二个为检索的行数）
- 行 0 检索出来的第一行为行0而不是行1。因此, LIMIT 1, 1将检索出第二行而不是第一行
- 在行数不够时，返回能返回的行数
- LIMIT4 OFFSET 3 意为从行3开始取4行
使用完全限定的表名
- SELECT products.prod_name FROM products;

5 排序检索数据 ORDER BY

排序SELECT xx FROM xx ORDER BY yy (DESC 降序)(LIMIT 1)

子句：一个子句通常由一个关键字和所提供的数据组成。
排序数据，使用 ORDER BY 子句
- SELECT prod_name FROM products ORDER BY prod_name;
- ORDER BY 子句中使用的列将是为显示所选择的列。但是,实际上并不一定要这样,用非检索的列排序数据是完全合法的
按多个列排序，指定列名,列名之间用逗号分开即可
- SELECT prod_name FROM products ORDER BY prod_name;
指定排序方向(降序DESC)
- SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC;
- SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name;(DESC 关键字只应用到直接位于其前面的列名)
- 如果想在多个列上进行降序排序,必须对每个列指定 DESC 关键字
- 默认升序，ASC
- 排序是否区分大小写，取决于数据库如何设置（MySQL等数据库在字典(dictionary)排序顺序中，大小写默认相同）数据库管理员必要时可更改
- 使用 ORDER BY 和 LIMIT 的组合,能够找出一个列中最高或最低的值
- SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1（ORDER BY 子句位于 FROM 子句之后;LIMIT ,它必须位于 ORDER BY之后）

6 过滤数据 WHERE

过滤搜索条件 SELECT xx FROM xx WHERE yy (ORDER BY yy)

WHERE子句，过滤条件
- SELECT prod_name, prod_price FROM products WHERE prod_price=2.50;(只返回 prod_price 值为 2.50 的行)
- 同时使用 ORDER BY 和 WHERE 子句时,应该让 ORDER BY 位于 WHERE 之后
WHERE 子句操作符
检查单个
- 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, vend_id FROM products WHERE vend_id <> 1003;
- 单引号用来限定字符串;将值与串类型的列进行比较,则需要限定引号。用来与数值列进行比较的值不用引号
范围值检查 BETWEEN
- SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
- 必须指定两个值——所需范围的低端值和高端值;必须用 AND 关键字分隔。
空值检查 IS NULL
- NULL 无值（no value）与字段包含 0 、空字符串或仅仅包含空格不同。
- SELECT prod_name FROM products WHERE prod_price IS NULL
- 过滤数据时,一定要验证返回数据中确实给出了被过滤列具有 NULL 的行（未知具有
  特殊的含义,数据库不知道它们是否匹配,所以在匹配过滤或不匹配过滤时不返回它们）。

7 数据过滤组合WHERE, NOT, IN

7.1 高级过滤，SELECT xx FROM xx WHERE yy AND（OR）yy ;

7.2 SELECT xx FROM xx WHERE yy (NOT)IN (BETWEEN \EXISTS) yy

操作符（operator）：联结或改变WHERE子句中的子句的关键字。（逻辑操作符）
AND(每添加一条件就要使用一个 AND)
- SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <=10;
OR(检索匹配任一给定条件的行)
- SELECT prod_price, prod_name FROM products WHERE vend_id = 1002 or vend_id = 1003;
计算次序
- SELECT prod_price, prod_name FROM products WHERE vend_id = 1002 or vend_id = 1003 AND prod_price >=10;(优先处理 AND 操作符，所以返回的行中有两行价格小于10美元；理解为：由供应商 1003 制造的任何价格为10美元(含)以上的产品,或者由供应商 1002 制造的任何产品,而不管其价格如何)
- SELECT prod_price, prod_name FROM products WHERE (vend_id = 1002 or vend_id = 1003) AND prod_price >=10;（括号有更高的计算次序，理解为由供应商 1002 或 1003 制造的且价格都在10美元(含)以上的任何产品）
IN：指定条件范围,范围中的每个条件都可以进行匹配，IN 取合法值的由逗号分隔的清
单,全都括在圆括号中。等价于OR！
- SELECT prod_price, prod_name FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name ;
- 使用长的合法选项清单时, IN 操作符的语法更清楚且更直观
- 计算的次序更容易管理(因为使用的操作符更少）
- IN 操作符一般比 OR 操作符清单执行更快
- IN 的最大优点是可以包含其他 SELECT 语句,使得能够更动态地建立 WHERE 子句
NOT：否定它之后所跟的任何条件
- SELECT prod_price, prod_name FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name ;
- MySQL 支持使用 NOT 对 IN 、 BETWEEN 和EXISTS子句取反

8 通配符进行过滤

8.1 SELECT xx FROM xx WHERE yy LIKE ‘yy%’;(任意字符出现任意次数)；

8.2 … LIKE ‘_ yy’;(单个字符任意次数)；

通配符（wildcard）用来匹配值的一部分的特殊字符，通配符本身实际是SQL的 WHERE 子句中有特殊含义的字符
搜索模式(search pattern)由字面值、通配符或两者组合构成的搜索条件。
LIKE，谓词，在搜索子句中使用通配符,必须使用 LIKE 操作符，利用通配符匹配而不是直接相等匹配进行比较
百分号( % )通配符：任何字符出现任意次数
- SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘JET%’;(使用了搜索模式 ‘jet%’ 。在执行这条子句时,将检索任意以 jet 起头的词。 % 告诉MySQL接受 jet 之后的任意字符,不管它有多少字符)
- 搜索可以配置区分大小写
- SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘%anvil%’;（通配符可在任意位置使用,并且可以使用多个）
- 尾空格可能会干扰通配符匹配。（在搜索模式最后附加一个 % 或者使用函数去掉首尾空格）
- %不能匹配NULL
下划线( _ )通配符：只匹配单个字符而不是多个字符
- SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘_ ton anvil’;
技巧（通配符处理搜索比一般搜索花费时间更长）
- 不要过度使用通配符
- 尽量不要把通配符放在搜索模式的开始处
- 仔细注意通配符的位置

9 用正则表达式进行搜索

9.1 SELECT xx FROM xx WHERE yy REGEXP ‘yy’;

9.2 or |、[]:SELECT xx FROM xx WHERE yy REGEXP ‘1|2|3’; (’[123]ton’)

9.3 匹配范围: SELECT xx FROM xx WHERE yy REGEXP ‘[0-9]’;

9.4 特殊字符,前导：SELECT xx FROM xx WHERE yy REGEXP ‘\.’;

9.5 字符类、重复元字符、定位符

正则表达式是用来匹配文本的特殊的串(字符集合)
基本字符匹配
- SELECT prod_name FROM products WHERE prod_name REGEXP ‘1000’ ORDER BY prod_name;(关键字 LIKE 被 REGEXP 替代,REGEXP 后所跟的东西作为正则表达式(与文字正文 1000 匹配的一个正则表达式)处理)
- SELECT prod_name FROM products WHERE prod_name REGEXP ‘.000’ ORDER BY prod_name;(. 是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符)
- LIKE匹配整个列,如果被匹配的文本在列值中出现, LIKE 将不会找到它,相应的行也不被返回(除非使用通配符);
- REGEXP 在列值内进行匹配,如果被匹配的文本在列值中出现, REGEXP 将会找到它,相应的行将被返回。使用 ^ 和 $ 定位符(anchor)，匹配整个列值，同LIKE
- 不区分大小写：SELECT xx FROM xx WHERE yy REGEXP BINARY ‘yy’;
OR匹配
- 搜索两个串之一，使用‘|’，正则表达式的 OR 操作符，表示匹配其中之一
- 多个 OR 条件可并入单个正则表达式：‘1000 | 2000 | 3000’ 将匹配 1000 或 2000 或 3000
匹配几个字符之一：指定一组用 [ 和 ] 括起来的字符
- SELECT prod_name FROM products WHERE prod_name REGEXP ‘[123 Ton]’ORDER BY prod_name;[123] 定义一组字符,它的意思是匹配 1 或 2 或 3 ,因此, 1 ton 和 2 ton 都匹配且返回(没有 3 ton )
- [] 是另一种形式的 OR 语句;正则表达式 [123]Ton为 [1|2|3]Ton 的缩写,需要用 [] 来定义 OR 语句查找什么!!!
- SELECT prod_name FROM products WHERE prod_name REGEXP ‘1|2|3 Ton’ORDER BY prod_name;把含有1或2或3 ton的其他行检出来
- 字符集合也可以被否定，在集合的开始处放置一个 ^ 即可
匹配范围：使用 - 来定义一个范围。[0-9]、[a-z]
匹配特殊字符:必须用 \ 为前导(转义)。 \- 表示查找 - , \. 表示查找 .
- 正则表达式内具有特殊意义的所有字符都必须以这种方式转义。包括 . 、 | 、 []
  - \ 也用来引用元字符
  - \f 换页
  - \n 换行
  - \r 回车
  - \t 制表
  - \v 纵向制表
- 为了匹配反斜杠( \ )字符本身,需要使用 \\
匹配字符类：使用预定义的字符集
匹配多个实例
- 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;
定位符：匹配特定位置的文本
- SELECT prod_name FROM products WHERE prod_name REGEXP ‘^[0-9\\.]’ ORDER BY prod_name;
- ^[0-9\\.]只在 . 或任意数字为串中第一个字符时才匹配它们
- ^ 有两种用法。在集合中(用 [ 和 ] 定义),用它来否定该集合,否则,用来指串的开始处
- LIKE 匹配整个串而 REGEXP 匹配子串。利用定位符,通过用 ^ 开始每个表达式,用 $ 结束每个表达式,可以使REGEXP 的作用与 LIKE 一样.
- REGEXP 检查总是返回 0 (没有匹配)或 1 (匹配),可以在不使用数据库表的情况下用SELECT 来测试正则表达式:SELECT ‘hello’ REGEXP ‘[0-9]’;返回0.

10创建计算字段

计算字段+引用计算字段:SELECT Concat (xx),(xx) As xx’ FROM xx WHERE yy=zz;

字段（field）：用在计算字段的连接上，列的意思。
计算字段：运行在SELECT语句内创建的。
拼接（concatenate）：将值联结到一起构成单个值。concat()
- SELECT Concat(vend_name, ‘(’,vend_country,’)’) FROM vendors ORDER BY vend_name;
- 删除数据右侧多余空格（第八章）:RTrim()；同时，LTrim()和Trim()
- SELECT Concat (RTrim(vend_name),’(’,RTrim(vend_country),’)’) FROM vendors ORDER BY vend_name;
别名（alias）：一个字段或值的替换名。用AS关键字赋予。
- SELECT Concat (RTrim(vend_name),’(’,RTrim(vend_country),’)’) AS vend_title FROM vendors ORDER BY vend_name;指示SQL创建一个包含
  指定计算的名为 vend_title 的计算字段。
- 在实际的表列名包含不符合规定的字符（如空格）时重新命名它，在原来的名字含混或容易误解时扩充它。
- 列名，也称导出列，derived column
执行算术计算
- SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems WHERE order_num=2005; expanded_price 列为一个计算字段
- SELECT 省略 FROM 子句以便简单地访问和处理表达式，可以测试和试验函数与计算。SELECT 3*2;将返回6

11 使用数据处理函数

文本处理函数+日期和时间处理函数+数值处理函数

函数没有SQL的可移植性强
文本处理函数
- 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_contactt=‘Y.Lie’; 匹配所有发音类似于Y.Lie 的联系名
- SOUNDEX 是一个将任何文本串转换为描述其语音表示的字母数字模式的算法
日期和时间处理函数
- 日期和时间采用相应的数据类型和特殊的格式存储,函数总是被用来读取、统计和处理这些值。
- 总是使用4位数字的年份
- 要日期，用Date():SELECT cust_id, order_num FROM orders WHERE Date(order_date) = ‘2021-01-01’;
- 要时间，用Time()
数值处理函数
返回DBMS正使用的特殊信息（返回用户登录信息，检查版本细节）的系统函数。

12 汇总数据

12.1 聚集函数：AVG+COUNT+MAX+MIN+SUM

12.2 聚集不同值SELECT AVG(DISTINCT xx) AS xx FROM xx WHERE yy;

12.3 组合聚集：ELECT COUNT(*) AS xx, MIN(xx) AS xx, MAX(xx) AS xx FROM xx WHERE yy;

聚集函数(aggregate function) 运行在行组上,计算和返回单个值的函数。
AVG()返回所有列的平均值,也可以用来返回特定列或行的平均值
- SELECT AVG(pro_price) AS avg_price From products; 所以产品均值
- SELECT AVG(pro_price) AS avg_price From products WHERE vend_id=1003; 所有1003产品的均值
- 只用于单个列，列名必须作为函数参数给出。多个列的平均值,必须使用多个 AVG() 函数
- AVG() 函数忽略列值为 NULL 的行
COUNT()确定表中行的数目或符合特定条件的行的数目
- 使用 COUNT(*) 对表中行的数目进行计数,不管表列中包含的是空值( NULL )还是非空值
- SELECT COUNT(*) AS num_cust;返回5,无论行中各列什么值，在num_cust返回。
- 使用 COUNT(column) 对特定列中具有值的行进行计数,忽略NULL 值
- SELECT COUNT(cust_email) AS num_cust FROM customers;返回3。
MAX()返回指定列中的最大值
- 对非数值数据使用 MAX()，用于文本数据时,如果数据按相应的列排序,则 MAX() 返回最后一行。
- MAX() 函数忽略列值为 NULL 的行
MIN()返回指定列的最小值
- 对非数值数据使用 MIN()，用于文本数据时,如果数据按相应的列排序,则 MIN() 返回最前面的行。
- MIN() 函数忽略列值为 NULL 的行。
SUM()返回指定列值的和(总计)，合计计算值
- SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num=20005;
- SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num=20005;
- 所有聚集函数都可用来执行多个列上的计算
- SUM() 函数忽略列值为 NULL 的行
聚集不同值
- 对所有的行执行计算,指定 ALL 参数或不给参数(因为 ALL 是默认行为)
- 只包含不同的值,指定 DISTINCT 参数
- DISTINCT 用于 COUNT()，DISTINCT 必须使用列名,不能用于计算或表达式
- DISTINCT 用于 MIN() 和 MAX()无意义，一个列中的最小值和最大值不管是否包含不同值都是相同的。
组合聚集函数
- 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;
- 在指定别名以包含某个聚集函数的结果时,不应该使用表中实际的列名(便于排除故障)

13 分组数据

13.1 GROUP BY 子句，创建分组

13.2 HAVING 子句，过滤分组，针对分组聚集值（WHERE子句针对行）

13.3 顺序：SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY-LIMIT

数据分组 WHERE子句（针对行）
创建分组，对每个组而不是整个结果集进行聚集
- GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套,为数据分组提供更细致的控制
- 如果在 GROUP BY 子句中嵌套了分组,数据将在最后规定的分组上进行汇总。换句话说,在建立分组时,指定的所有列都一起计算(所以不能从个别的列取回数据)
- GROUP BY 子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)。如果在 SELECT 中使用表达式,则必须在GROUP BY 子句中指定相同的表达式。不能使用别名
- 除聚集计算语句外, SELECT 语句中的每个列都必须在 GROUP BY 子句中给出
- 如果分组列中具有 NULL 值,则 NULL 将作为一个分组返回。如果列中有多行 NULL 值,它们将分为一组
- GROUP BY 子句必须出现在 WHERE 子句之后, ORDER BY 子句之前
- 使用 WITH ROLLUP 关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组)的值
- SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;
过滤分组-HAVING 子句
- HAVING 支持所有 WHERE 操作符
- SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;
- 这里 WHERE 子句不起作用,因为过滤是基于分组聚集值而不是特定行值的
- WHERE 在数据分组前进行过滤, HAVING 在数据分组后进行过滤；同时使用时先用WHERE后HAVING
分组和排序
- 一般在使用 GROUP BY 子句时,应该也给出 ORDER BY 子句。这是保证数据正确排序的唯一方法
- SELECT orfer_num, SUM(quantityitem_price) As ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantityitem_price)>=50;
- SELECT orfer_num, SUM(quantityitem_price) As ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantityitem_price)>=50 ORDER BY ordertotal;
- GROUP BY 子句用来按订单号( order_num 列)分组数据,以便 SUM(*) 函数能够返回总计订单价格。 HAVING 子句过滤数据,使得只返回总计订单价格大于等于 50 的订单。最后,用 ORDERBY 子句排序输出
SELECT 子句顺序 SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY-LIMIT

14 子查询

SELECT语句查询，嵌套多个子查询，作为计算字段使用子查询

查询query，一般SELECT语句；子查询，嵌套在其他查询中的查询。
利用子查询过滤：跨表查询时，把一条SELECT语句返回结果用于另一条。比如：
- 需要列出订购物品 TNT2 的所有客户：(1) 检索包含物品 TNT2 的所有订单的编号。(2) 检索具有前一步骤列出的订单编号的所有客户的ID。(3) 检索前一步骤返回的所有客户ID的客户信息。
- SELECT order_num FROM orderitems WHERE prod_id=‘TNT2’;输出列出两个包含此物品的订(20005,20007)
- SELECT cust_id FROM orders WHERE order_num IN (220005,20007);输出用户ID（10001,10004）
- SELECT cust_name, cust_contact FROM customers WHERE cust_id IN (10001, 10004);输出这些用户信息
- 可以把三个查询组合起来：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’));
- 为了好看，适当分解、缩进
作为计算字段使用子查询
- 显示 customers表中每个客户的订单总数。先从customers表统计客户列表，再统计每个用户在orders表的订单数。
- SELECT COUNT(*) AS orders FROM orders WHERE cust_id=10001;
- SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id =customers.cust_id) AS orders FROM customers ORDER BY cust_name;
- 相关子查询（correlated subquery）涉及外部查询的子查询。列名可能有多义性，就必须使用这种语法（表名和列名由一个句点分隔）
- 逐渐增加子查询来建立查询。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，在确认它正常后才嵌入子查询。

15 联结表-join

15.1 xx.aa ==yy.aa;

15.2 INNER JOIN… ON…

15.3 xx.aa = yy.aa AND mm.bb=nn.bb;

关系表，保证把信息分解成多个表，一类数据一个表。
- 各表通过某些常用的值（即关系设计中的关系（relational））互相关联。设计唯一标示，即主键（primary key）。
- 外键（foreign key）外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。
- 关系数据可以有效地存储和方便地处理，不浪费时间、空间；一个表信息变动，不影响其他表；数据无重复，操作简单；
- 可伸缩性（scale）能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好（scale well）。
- 联结是一种机制，用来在一条 SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。联结由MySQL根据需要建立，它存在于查询的执行当中，非物理实体。
- 使用关系表时，仅在关系列中插入合法的数据非常重要。
创建联结：规定要联结的所有表以及它们如何关联即可。
- SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id ORDER BY vend_name, prod_name;
- 完全限定列名在引用的列可能出现二义性时，必须使用完
  全限定列名（用一个点分隔的表名和列名）。如vendors.vend_id
WHERE子句，构建联结
- 笛卡儿积（cartesian product）由没有联结条件的表关系返回
  的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘
  以第二个表中的行数。
- 保证所有联结都有 WHERE 子句，保证 WHERE 子句的正确性。
- 叉联结（cross join），笛卡儿积的联结类型
内部联结
- 也称等值联结（equijoin），它基于两个表之间的相等测试。
- SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;
- SQL规范首选 INNER JOIN 语法,使用明确的联结语法
联结多个表
- SQL对一条 SELECT 语句中可以联结的表的数目没有限制
- 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;
- MySQL在运行时关联指定的每个表以处理联结,不要联结
  不必要的表。联结的表越多，性能下降越厉害。
- SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id=orders.cust_id AND orders.order_num=orderitems.order_num AND prod_id=‘TNT2’;（同14章嵌套子查询）

16 创建高级联结

16.1 表别名

16.2 自联结+自然联结+外部联结+带聚集函数的联结

表别名，给SQL表起别名。缩短SQL语句，允许在单条SELECT语句中多次使用相同的表。
- SELECT cust_name, cust_contact 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’;
- 表别名不仅能用于 WHERE 子句，它还可以用于 SELECT 的列表、 ORDER BY 子句以及语句的其他部分。
不同类型联结
- 自联结：自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句，查询快一些。
  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';
- 自然联结：标准的联结返回所有数据,甚至相同的列多次出现。自然联结排除多次出现,使每个列只返回一次。对表使用通配符( SELECT * )，对所有其他表的列使用明确的子集来完成。
  SELECT c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price 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';
- 外部联结：联结两个表，包括没有关联行的行。使用 OUTER JOIN 语法时,必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表( RIGHT 指出的是 OUTER JOIN 右边的表,而 LEFT指出的是 OUTER JOIN 左边的表)
  #内部联结，检索所有客户及其订单： SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id; #外部联结，检索所有客户，包括无订单的： SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id;
- MySQL不支持简化字符 = 和 = 的使用
- 存在两种基本的外部联结形式:左外部联结右外部联结，差别是所关联的表的顺序不同。
带聚集函数的联结

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;
SELECT customers.cust_name, customers.cust_id, COUNT(orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id; （两句对比，后句多检索出 Mouse用户，有0个订单）

使用联结和联结条件

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

17 组合查询-UNION

UNION组合多个查询

组合查询：也允许执行多个查询(多条 SELECT 语句),并将结果作为单个查询结果集返回。这些组合查询通常称为并 ( union ) 或复合查询(compound query)。
- 在单个查询中从不同的表返回类似结构的数据，需要组合查询
- 对单个表执行多个查询,按单个查询返回数据，需要组合查询
- 任何具有多个 WHERE 子句的 SELECT 语句都可以作为一个组合查询给出
创建组合查询： UNION 操作符来组合数条SQL查询
- 使用UNION，执行多条 SELECT 语句，并把输出组合成单个查询结果集。（同使用多条WHERE子句）使用多个表检索数据，用UNION可能更简单。
- UNION规则： 1)UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔;2)UNION 中的每个查询必须包含相同的列、表达式或聚集函数;3) 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）
- 包含或取消重复的行:默认UNION 从查询结果集中自动去除了重复的行，若想返回所有匹配行，使用 UNION ALL 而不是 UNION
- 对组合查询结果排序： ORDER BY，必须出现在最后一条 SELECT 语句之后，对最终结果集作用。
- 使用 UNION 可极大地简化复杂的 WHERE 子句，简化从多个表中检索数据的工作

18 全文本搜索

18.1 高级数据查询及选择：SELECT xx FROM xx WHERE Match(yy) Against(yy)

18.2 全文本搜索+使用查询扩展+布尔文本搜索

全文本搜索(引擎MyISAMz支持，InnoDB不支持)
- 性能高：不用查看每行每个词
- 不需特别明确的控制：创建指定列中各词的一个索引，搜索可以针对这些词进行
- 智能
全文本搜索，索引被搜索的列，随着数据的改变不断地重新索引。索引后，与Match()\Against()一起实际搜索。
启用：在创建表时启用全文本搜索，不要在导入数据时使用 FULLTEXT，慢。先导入所有数据，在修改表，定义FULLTEXT。

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;
# CREATE TABLE 语句定义表 productnotes 并列出它所包含的列,根据子句 FULLTEXT(note_text) 的指示对note_text进行索引,MySQL自动维护该索引

进行：索引之后，使用两个函数 Match() 和 Against() 执行全文本搜索，其中 Match() 指定被搜索的列， Against() 指定要使用的搜索表达式。
- 传递给 Match() 的值必须与FULLTEXT() 定义中的相同。如果指定多个列，则必须列出它们（而且次序正确）。
- 搜索不区分大小写除非使用 BINARY 方式（本章中没有介绍），否则全文本搜索不区分大小写。
- 全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回(搜到的文本更靠前)；
- 等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来，文本中词靠前的行的等级值比词靠后的
  行的等级值高；多个搜索词，包含多数匹配词的那些行将具有比包含较少词（或仅有一个匹配）的那些行高的等级值。

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');
# 输出note_text列，包含rabbit的文本。
# 等同于LIKE'%rabbit%'，但全文本搜索提供了简单 LIKE 搜索不能提供的功能。而且，由于数据是索引的，全文本搜索还相当快。

使用查询扩展：设法放宽所返回的全文本搜索结果的范围。MySQL对数据和索引进行两遍扫描来完成搜索：
- 进行一个基本的全文本搜索，找出与搜索条件匹配的所有行
- MySQL检查这些匹配行并选择所有有用的词（我们将会简要地解释MySQL如何断定什么有用，什么无用）
- MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);
# 查询扩展极大地增加了返回的行数，但这样做也增加了实际上并不想要的行的数目。
# 表中的行越多（这些行中的文本就越多），使用查询扩展返回的结果越好

布尔文本搜索，提供：
- 要匹配的词；
- 要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
- 排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
- 表达式分组；
- 另外一些内容;
- 即使没有 FULLTEXT 索引也可以使用,但非常慢

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);
# 匹配包含 heavy 但不包含任意以 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 和 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个或3个以下字符的词（如果需要，这个数目可以更改）。
- MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表。
- 许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%规则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于 IN BOOLEAN MODE 。
- 如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词
  或者不出现，或者至少出现在50%的行中）。
- 忽略词中的单引号。例如， don’t 索引为 dont
- 不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果。
- 如前所述，仅在 MyISAM 数据库引擎中支持全文本搜索

19 插入数据INSERT

INSERT插入数据

数据插入，插入(或添加)行到数据库表的。
- 插入完整行
- 插入行的一部分
- 插入多行
- 插入某些查询的结果
- 可针对每个表或每个用户,利用MySQL的安全机制禁止使用 INSERT 语句
插入完整行，指定表名和被插入到新行中的值；没有输出，INSERT 语句一般不会产生输出。
- 总是使用列的列表
- 不管使用哪种 INSERT 语法，都必须给出VALUES 的正确数目
- 省略列如果表的定义允许，则可以在 INSERT 操作中省略某些列：1）该列定义为允许 NULL 值（无值或空值）2）在表定义中给出默认值。这表示如果不给出值，将使用默认值
- 对表中不允许 NULL 值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功
- 提高整体性能：INSERT 操作可能很耗时，如果数据检索是最重要的（通常是这样），则你可以通过在INSERT 和 INTO 之间添加关键LOW_PRIORITY ，指示MySQL降低 INSERT 语句的优先级

INSERT INTO Customers VALUES(NULL, 'Pep E. LaPew', '100 Main Street','Los Angeles','CA','90046','USA',NULL,NULL);
#某列无值，需要NULL填充
#第一列 cust_id 也为 NULL 。这是因为每次插入一个新行时，该列由MySQL自动增量
#高度依赖次序，不安全
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列表中的相应值填入列表中的对应项;不一定是实际表中的次序

插入多个行：使用多条 INSERT 语句，甚至一次提交它们，每条语句用一个分号结束。
- 每条 INSERT 语句中的列名（和次序）相同，可以给出一次列名，写多条VALUES，中间用（），（）隔开
- 提高性能
插入检索出的数据：将一条 SELECT 语句的结果插入表中，INSERT SELECT

#将一个custnew表数据导入customers表：
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
# SELECT 语句从 custnew 检索出要插入的值，而不是列出它们;
#cust_id,需不重复；若不能保证，直接不导入该列，由MySQL自动生成新值。
#两个表的列名不要求一致
# INSERT SELECT 中 SELECT 语句可包含 WHERE 子句以过滤插入的数据

20更新、删除UPDATE\DELETE

20.1 UPDATE

20.2 DELETE

更新数据：更新表中特定行；更新表中所有行。注意不要省略WHERE子句。
UPDATE 语句由3部分组成：要更新的表；列名和它们的新值；确定要更新行的过滤条件。
- UPDATE customers SET cust_email = ‘elmer@fudd.com’ WHERE cust_id=10005;
- 以要更新的表的名字开始
- 若没有WHERE子句，将更新整个customers表。
- UPDATE customers SET cust_name = ‘The Fudds’, cust_email = ‘elmer@fudd.com’ WHERE cust_id=10005;
- 在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间用逗号分隔
- UPDATE语句中可以使用子查询
- IGNORE关键字：UPDATE IGNORE customers… 即使发生错误，不会取消整个操作。
删除数据DELETE：删除表中特定的行；从表中删除所有行。（注意假删）
- DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句
- 删除表的内容而不是表 DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身
- 删除所有行，可使用TRUNCATE TABLE语句，速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）
更新和删除指导原则
- 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
- 保证每个表都有主键，像WHERE子句那样使用（指定各主键、多个值或值的范围）
- 先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确
- 使用强制实施引用完整性的数据库

21创建、操纵表

CREATE TABLE 主键必唯一（ AUTO_INCREMENT自增）（DEFAULT默认值）

CREATE TABLE用来创建新表，ALTER TABLE用来更改表列（或其他诸如约束或索引等对象），而DROP TABLE用来完整地删除一个表

创建表：
- 使用具有交互式创建和管理表的工具
- 用MySQL语句操纵
基础：必须给出，新表的名字，在关键字CREATE TABLE之后给出；表列的名字和定义，用逗号分隔
- 语句格式化：适当缩进
- 创建新表时，表名必须不存在。防止意外覆盖，先手工删除，再重建。仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS，只是查看表名是否存在，并且仅在表名不存在时创建它

CREATE TABLE customers
(
	cust_id          int       NOT NULL ANTO_INCREMENT,
	cust_name        char(50)  NOT NULL  ,
	cust_address     char(50)  NULL  ,
	cust_city        char(50)  NULL  ,
	cust_state       char(5)   NULL  ,
	cust_zip         char(10)  NULL  ,
	cust_country     char(50)  NULL  ,
	cust_contact     char(50)  NULL  ,
	cust_email       char(255) NULL  ,
	PRIMARY KEY (cust_id)
)ENGINE=InnoDB;

每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定
- NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL
- NULL是没有值
- 空串‘’是一个有效的值，允许在NOT NULL中

CREATE TABLE orders
(
	order_num        int       NOT NULL ANTO_INCREMENT,
	order_date       datetime  NOT NULL  ,
	cust_id          int       NOT NULL  ,
	PRIMARY KEY (order_num)
)ENGINE=InnoDB;
#都含有关键字NOT NULL,插入没有值的列，将返回错误，且插入失败

表中的每个行必须具有唯一的主键值，如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一
- PRIMARY KEY (order_num， order_item)
- 主键可以在创建表时定义，或者在创建表之后定义
- 主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。允许NULL值的列不能作为唯一标识。
AUTO_INCREMENT：本列每当增加一行时自动增量，给该列赋予下一个可用的值，给每个行分配一个唯一的cust_id，做主键。每个表只允许一AUTO_INCREMENT列，而且它必须被索引
- 覆盖AUTO_INCREMENT，在INSERT语句中指定一个值，只要它是唯一的，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。
- 确定AUTO_INCREMENT值，last_insert_id()函数获得这个值：SELECT last_insert_id()，返回最后一个AUTO_INCREMENT值
指定默认值：默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定

CREATE TABLE orderitems
(
	order_num        int       NOT NULL  ,
	order_item       int       NOT NULL  ,
	prod_id          char(10)  NOT NULL  ,
	quantity         int       NOT NULL  DEFAULT 1,
	item_price       decimal(8,2)  NOT NULL  ,
	PRIMARY KEY (order_num, order_item)
)ENGINE=InnoDB;
#quantity列包含订单中每项物品的数量,默认1

引擎类型
- MySQL有具体管理和处理数据的内部引擎，引擎有多种，默认一般是MyISAM
- InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索
- MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
- MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理
- 更多：http://dev.mysql.com/doc/refman/5.0/en/storage_engines.html
- 引擎类型可以混用
- 外键不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键
更新表：ALTER TABLE
- 建议设计好表，不要对表进行大改动
- 需要给出：在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；所做更改的列表。
- ALTER TABLE vendors ADD vend_phone CHAR(20);给vendors表增加一个名为vend_phone的列，必须明确其数据类型
- ALTER TABLE vendors DROP COLUMN vend_phone;
- 常用：定义外键，下例定义表所有的外键：

 ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num);
 ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id) REFERENCES products(prod_id);
   ALTER TABLE orders ADD CONSTRAINT fk_orderitems_customers FOREIGN KEY (cust_id) REFERENCES customers(cust_id);
    ALTER TABLE products ADD CONSTRAINT fk_orderitems_vendors FOREIGN KEY (vend_id) REFERENCES orders (vend_id);
# 更改4个不同的表，使用了4条ALTER TABLE语句

复杂的表结构更改一般需要手动删除过程：

用新的列布局创建一个新表
使用INSERT SELECT语句，从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
检验包含所需数据的新表；
重命名旧表（如果确定，可以删除它）；
用旧表原来的名字重命名新表；
根据需要，重新创建触发器、存储过程、索引和外键。

删除表：DROP TABLE;
- DROP TABLE customers2;
- 删除表没有确认，也不能撤销，执行这条语句将永久删除该表。
重命名表：RENAME TABLE;
- RENAME TABLE customers2 TO customers;一个表
- RENAME TABLE customers2 TO customers， vendors2 TO vendors;多个表

22使用视图

22.1 CREATE VIEW（ SHOW CREATE VIEW viewname ）

22.2 DROP VIEW viewname

22.3更新时，先DROP后CREATE或CREATE OR REPLACE VIEW

视图：虚拟的表，只包含使用时动态检索数据的查询，不包含数据。提供SELECT语句层次封装，简化数据处理及重新格式化基础数据或保护基础数据
- SELECT cust_name, cust_contact FROM customers, orders, orderitems WHERE customers.cust_id=order.cust_id AND orderitems.order_num=orders.order_num AND prod_id=‘TNT2’;
- SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id=‘TNT2’;(productcustomers是一个视图，作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询)
视图常见应用：
- 重用SQL语句
- 简化复杂的SQL操作，编写查询后，可方便的重用它而不必知道基本查询细节。
- 使用表的部分
- 保护数据，给用户授予表的特定部分的访问权限而不是整个表的访问权限
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
- 视图用来查看存储在别处的数据，视图本身不包含数据
- 嵌套复杂视图，性能下降厉害
规则限制
- 与表一样，视图必须唯一命名
- 无限创建视图
- 创建视图需要足够的访问权限
- 视图可以嵌套，利用从其他视图检索数据的查询来构造一个视图
- ORDER BY可在视图中，但从该视图检索数据SELECT也含有ORDERBY，ORDER BY将被覆盖
- 视图不能索引，也不能有关联的触发器或默认值
- 视图可以和表一起使用。例如，编写一条联结表和视图的SELECT语句
使用视图
- 视图用CREATE VIEW语句来创建
- 使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
- 用DROP删除视图，其语法为DROP VIEW viewname;。
- 更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR
  REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图
利用视图简化复杂的联结
- CREATE VIEW productcustomers AS SELECT cust_name, cust_contact, prod_id FROM customers, orders, orderitems WHERE customers.cust_id=order.cust_id AND orderitems.order_num=orders.order_num;
- 创建一个名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表；执行SELECT * FROM productcustomers，将列出订购了任意产品的客户。
- 创建可重用的视图
用视图重新格式化检索出的数据
- CREATE VIEW vendorlocations AS SELECT Concat(RTrim(vend_name),’(’,RTrim(vend_country),’)’) AS vend_title FROM vendors ORDER BY vend_name;
- SELECT * FROM vendorlocations;(检索出以创建所有邮件标签的数据)
用视图过滤不想要的数据
- 过滤没有电子邮件地址的客户:CREATE VIEW customeremaillist AS SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;WHERE子句过滤了cust_email列中具有NULL值的那些行
- SELECT * FROM customeremaillist;
- 如果从视图检索数据时使用了一条WHERE子句，则两组子句（一组在视图中，另一组是传递给视图的）将自动组合
视图可简化计算字段
更新视图：视图是可更新的（即，可以对它们使用INSERT、UPDATE和
DELETE），更新一个视图将更新其基表，对其基表增加或删除行
- 并非所有视图都是可更新的如果MySQL不能正确地确定被更新的基数据，则不允许更新
- 如果视图定义中有以下操作，则不能进行视图的更新：
  - 分组（使用GROUP BY和HAVING）；
  - 联结；
  - 子查询；
  - 并；
  - 聚集函数（Min()、Count()、Sum()等）；
  - DISTINCT；
  - 导出（计算）列
- 视图主要用于数据检索，SELECT语句

23 使用存储过程

存储过程CREATE PROCEDURE xx()BEGIN YY END

为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件
优势：简单安全高性能
- 简化操作
- 不需反复建立处理步骤，保证数据完整性，防止错误
- 简化对变动的管理，安全
- 提高性能
- 更灵活
缺陷：编写复杂，需要安全访问权限。运行使用，不允许创建
执行存储过程：调用CALL，接受存储过程名字及参数;存储过程可以显示结果，也可以不显示结果

CALL productpricing(@pricelow,
					@pricehigh,
					@priceaverage);

创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END;
# 存储过程名为productpricing,()里列参数，没有参数空白；BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句；

#默认的MySQL语句分隔符为;命令行实用程序也使用;作为语句分隔符,最终不会成为存储过程的成分，这会使存储过程中的SQL出现句法错误,解决办法是临时更改命令行实用程序的语句分隔符
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
	FROM products;
END//
DELIMITER;

DELIMITER //告诉命令行实用程序使用//作为新的语句结束分隔符,
CALL productpricing();#执行刚创建的存储过程并返回结果。
#存储过程实际上是一种函数，所以存储过程名后需要有()符号（即使不传递参数也需要）

删除存储过程
- DROP PROCEDURE productpricing;没有使用后面的 () ，只给出存储过程名
- 仅当存在时删除；若不存在删除会产生错误。可使用 DROP PROCEDURE IF EXISTS
使用参数
- 存储过程并不显示结果，而是把结果返回给指定变量。
- 变量varibale，内存中一个特定的位置，用来临时存储数据
- 支持IN（传递给存储过程）、 OUT （从存储过程传出，如这里所用）和 INOUT （对存储过程传入和传出）类型的参数
- 存储过程的代码位于 BEGIN 和 END 语句内
- 参数数据类型，允许与表中使用的相同，不能是记录集，不能通过一个参数返回多个行和列。

CREATE PROCEDURE productpricing(
	OUT pl DECIMAL(8,2),
	OUT ph DECIMAL(8,2),
	OUT pa 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;
#3个参数： pl 存储产品最低价格， ph 存储产品最高价格， pa 存储产品平均价格,每个参数必须具有指定的类型，这里使用十进制值

CALL productpricing(@pricelow,
					@pricehigh,
					@priceaverage
);
#变量名 所有MySQL变量都必须以 @ 开始

SELECT @priceaverage;

SELECT @pricehigh, @pricelow, @priceaverage;

CREATE PROCEDURE ordertotal(
	IN onumber INT,
	OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price * quantity)
	FROM orderitems
	WHERE order_num=onumber
	INTO ototal;
END;  
#为调用这个新存储过程，可使用以下语句：
CALL ordertotal(20005, @total);
#显示合计
SELECT @total;

建立智能存储过程
- 获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客：获得合计（与以前一样）；把营业税有条件地添加到合计；返回合计（带或不带税）

--Name:ordertotal
--Parameters:onumber = order number
			 taxable = 0 if not taxable, 1 if taxable
			 otatal = order total variable
CREATE PROCEDURE ordertotal(
	IN onumber INT,
	IN taxable BOOLEAN,
	OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
	--Declare variable for total
	DECLARE total DECIMAL(8,2);
	--Declare tax percentage
	DECLARE taxrate INT DEFAULT 6;
	--Get the order total
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num=onumber
	INTO total;
	--Is this taxbale?
	IF taxable THEN
		-- Yes, so add taxrate to the total
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
		--And finally, save to out variable
		SELECT total INTO ototal;
	END; 	
#COMMENT 关键字:不是必需的，但如果给出，将在 SHOW PROCEDURE STATUS 的结果中显示
CALL ordertotal(20005, 0, @total);
SELECT @total;

CALL ordertotal(20005, 1, @total);
SELECT @total;
#通过给中间的参数指定 0 或 1 ，可以有条件地将营业税加到订单合计上
#IF 语句还支持 ELSEIF 和 ELSE 子句

检查存储过程
- 为显示用来创建一个存储过程的 CREATE 语句，使用 SHOW CREATE PROCEDURE 语句；
- SHOW CREATE PROCEDURE ordertotal;
- 获得包括何时、由谁创建等详细信息的存储过程列表，使用 SHOW PROCEDURE STATUS(可使用LIKE限制输出)

24 游标

cursor：存储在MySQL服务器上的数据库查询，不是SELECT语句，而是被该语句检索出来的结果集。存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
- 用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。
- 只能用于存储过程不像多数DBMS，MySQL游标只能用于存储过程（和函数）
使用
- 声明，定义要使用的SELECT语句
- 打开游标使用，用前面定义的SELECT语句把数据实际检索出来
- 对填有数据的游标，根据需要取出（检索）各行。
- 结束游标使用，必须关闭
创建DECLARE

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders；
END;
# DECLARE 语句用来定义和命名游标，这里为 ordernumbers 。 存储过程处理完成后，游标就消失（因为它局限于存储过程）;在定义游标之后，可以打开它。

打开关闭游标

OPEN ordernumbers;
#处理 OPEN 语句时执行查询，存储检索出的数据以供浏览和滚动
CLOSE ordernumbers;
#CLOSE 释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。
#若没有重新打开游标，则不能使用。再次使用不用声明，直接打开即可。
#如果你不明确关闭游标，MySQL将会在到达 END 语句时自动关闭它。
#完整过程：
CREATE PROCEDURE processorders()
BEGIN
	--Declare the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders；
	--Open the cursor
	OPEN ordernumbers;
	--Close the cursor
	CLOSE ordernumbers;
END;

使用： FETCH 语句分别访问它的每一行。
- FETCH 指定检索什么数据（所需的列），检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针，使下一条 FETCH 语句检索下一行（不重复读取同一行）。

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
	DECLARE o INT;
	
	--Declare the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders；
	
	--Open the cursor
	OPEN ordernumbers;
	
	-- Get order number
	FETCH ordernumbers INTO o;
	
	--Close the cursor
	CLOSE ordernumbers;
END;
#FETCH 用来检索当前行的 order_num 列（将自动从第一行开始）到一个名为 o 的局部声明的变量中。对检索出的数据不做任何处理

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	
	--Declare the cursor
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders；
	
	--Declare continue handler
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	--Open the cursor
	OPEN ordernumbers;
	
	--Loop through all rows
	REPEAT
	
	-- Get order number
	FETCH ordernumbers INTO o;
	
	--End of loop
	UNTIL done END REAPET;
	
	--Close the cursor
	CLOSE ordernumbers;
END;
# FETCH 是在 REPEAT 内，因此它反复执行直到 done 为真（由 UNTIL done END REPEAT; 规定）;为使它起作用，用一个 DEFAULT 0 （假，不结束）定义变量 done 
#如果调用这个存储过程，它将定义几个变量和一个 CONTINUE HANDLER ，定义并打开一个游标，重复读取所有行，然后关闭游标。
# 可以在循环内放入任意需要的处理（FETCH后，循环结束前）

DECLARE 语句的次序：用 DECLARE 语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。
可用来重复执行代码，直到使用 LEAVE 语句手动退出为止。通常REPEAT 语句的语法使它更适合于对游标进行循

25 使用触发器

触发器：MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于 BEGIN 和 END 语句之间的一组语句）
- DELETE
- INSERT
- UPDATE
创建，需要给出：
- 唯一的触发器名（在每个表中唯一，最好就唯一）
- 触发器关联的表
- 触发器应响应的活动（DELETE\INSERT\UPDATE）
- 触发器何时执行

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
# newproduct
# products
# INSERT
# AFTER 四要素

注意：仅支持表，只有表才支持触发器，视图不支持（临时表也不支持）。
每个表每个事件每次只允许一个触发器。
每个表最多支持6个触发器（每条 INSERT 、 UPDATE和 DELETE 的之前和之后）
单一触发器不能与多个事件或多个表关联
如果 BEFORE 触发器失败，则MySQL将不执行请求的操作；如果 BEFORE 触发器或语句本身失败，MySQL将不执行 AFTER 触发器

删除触发器 DROP TRIGGER
- DROP TRIGGER newproduct
INSERT触发器
- 可引用一个名为 NEW 的虚拟表，访问被插入的行
- 在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新（允许更改被插入的值）
- 对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含 0 ，在 INSERT执行之后包含新的自动生成值
- 一般将 BEFORE 用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;
#AFTER,触发器在插入之后执行，对于orders每次插入使用这个触发器将总是返回新订单号。

DELETE触发器
- 在 DELETE 触发器代码内，可以引用一个名为 OLD 的虚拟表，访问被删除的行
- OLD 中的值全都是只读的，不能更新
- BEFORE DELETE 触发器的优点（相对于 AFTER DELETE 触发器来说）为，如果由于某种原因，订单不能存档， DELETE 本身将被放弃

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;
#在任意订单被删除前将执行此触发器,使用一条 INSERT 语句将 OLD 中的值（要被删除的订单）保存到一个名为 archive_orders 的存档表中（为实际使用这个例子，你需要用与 orders 相同的列创建一个名为 archive_orders 的表）

UPDATE触发器
- 可以引用一个名为 OLD 的虚拟表访问以前（ UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值；
- 在 BEFORE UPDATE 触发器中， NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）；
- OLD 中的值全都是只读的，不能更新。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);
# 数据净化在UPDATE前进行

重点
- MySQL版本中有一些改进和增强触发器支持的计划
- 创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果 INSERT 、 UPDATE 或 DELETE 语句能够执行，则相关的触发器也能执行。
- 应该用触发器来保证数据的一致性（大小写、格式等）；优点是它总是进行这种处理，而且是透明地进行，与客户机应用无关
- 创建审计跟踪，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易
- MySQL触发器中不支持 CALL 语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内

26管理事务处理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
- MyISAM 和 InnoDB 是两种最常使用的引擎。前者不支持明确的事务处理管理，而后者支持
- 如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。
管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退
- 事务（ transaction ）指一组SQL语句
- 回退（ rollback ）指撤销指定SQL语句的过程
- 提交（ commit ）指将未存储的SQL语句结果写入数据库表
- 保留点（ savepoint ）指事务处理中设置的临时占位符（place-
  holder），你可以对它发布回退（与回退整个事务处理不同）
ROLLBACK：只能在一个事务处理内使用（在执行一条 START TRANSACTION 命令之后）回退START TRANSACTION之后的所有语句。
- 不能回退 SELECT 语句
- 不能回退 CREATE 或 DROP 操作
COMMIT
- 一般mysql语句是隐含提交（implicit commit），即提交（写或保存）操作是自动进行的
- 事务处理块，提交不会隐含进行。
- 当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭（将来的更改会隐含提交）。

START TRANSACTION;
DELETE FROM orderitems WHERE order_num=20010;
DELETE FROM orders WHERE order_num=20010;
COMMIT;
#用事务处理块来保证订单不被部分删除;最后的 COMMIT 语句仅在不出错时写出更改

使用保留点：为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。
- 保留点越多越好
- 保留点在事务处理完成（执行一条 ROLLBACK 或COMMIT ）后自动释放。也可以用 RELEASE SAVEPOINT 明确地释放保留点

SAVEPOINT delete1;
#每个保留点都取标识它的唯一名字，以便在回退时，MySQL知道要回退到何处。
ROLLBACK delete1;

更改默认的提交行为:指示MySQL不自动提交更改
- autocommit 标志是针对每个连接而不是服务器的

SET autocommit=0;
#autocommit 标志决定是否自动提交更改，不管有没有 COMMIT语句。设置 autocommit 为 0 （假）指示MySQL不自动提交更改（直到 autocommit 被设置为真为止）

27 全球化和本地化

字符集和校对顺序
- 字符集为字母和符号的集合
- 编码为某个字符集成员的内部表示
- 校对为规定字符如何比较的指令

SHOW CHARACTER SET;
#显示所有可用的字符集以及每个字符集的描述和默认校对
SHOW COLLATION;
#显示所有可用的校对，以及它们适用的字符集

通常系统管理在安装时定义一个默认的字符集和校对。此外，也可以在创建数据库时，指定默认的字符集和校对.
- CHARACTER 可以用于ORDER BY、 GROUP BY 、 HAVING 、聚集
  函数、别名等

#确定所用的字符集和校对
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';
#给表指定字符集和校对，可使用带子句的 CREATE TABLE指定
CREATE TABLE mytable
(
	columnn1    INT,
	columnn2    VARCHAR(10)
	#对每个列设置
	columnn2    VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
)DEFAULT CHARACTER SET hebrew
 COLLATE hebrew_general_ci;
#若需要用与创建表时不同的校对顺序排序特定的 SELECT 语句，可以在 SELECT 语句自身中进行：
SELECT *FROM customers
ORDER BY lastname, firstname COLLATE latin1_general_cs;#使用 COLLATE 指定一个备用的校对顺序，区分大小写，影响排序

28安全管理

安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。换句话说，用户不能对过多的数据具有过多的访问权。
访问控制：管理访问控制需要创建和管理用户账号
- MySQL Administrator提供了一个图形用户界面，可用来管理用户及账号权限
- 访问控制的目的不仅仅是防止用户的恶意企图，防止数据梦魇
- 不要用root
管理用户
- 用多个客户机进行试验试验对用户账号和权限进行更改的最好办法是打开多个数据库客户机
创建：CREATE USER ben IDENTIFIED BY 'p@$$w0rd';
- IDENTIFIED BY 指定的口令为纯文本，MySQL将在保存到 user 表之前对其进行加密.为了作为散列值指定口令，使用 IDENTIFIED BY PASSWORD
- 重命名：RENAME USER ben TO bforta;
删除：DROP USER bforta;
设置访问权限:创建用户账号后，必须接着分配访问权限。否则，只能登录，不能看、操作数据
- 用户定义为 user@host
- 设置权限，用GRANT，需要：1）要授予的权限；2）被授予访问权限的数据库或表；3）用户名
- 反操作REVOKE，撤销特定权限，被撤销的访问权限必须存在，否则会出错
- 在使用 GRANT 和 REVOKE 时，用户账号必须存在
- 简化：通过列出各权限并用逗号分隔，将多条GRANT 语句串在一起GRANT SELECT, INSERT ON crashcourse.* TO bforta;

#看赋予用户账号的权限:
SHOW GRANTS FOR bforta;
# USAGE 表示根本没有权限

GRANT SELECT ON crashcourse.* # crashcourse 数据库的所有表,select,具有只读访问权限

#撤销特定的权限
REVOKE SELECT ON crashcourse.* FROM bforta;

GRANT 和 REVOKE 可在几个层次上控制访问权限：
- 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
- 整个数据库，使用 ON database.*；
- 特定的表，使用 ON database.table；
- 特定的列；
- 特定的存储过程。
更改口令: SET PASSWORD
- SET PASSWORD 更新用户口令。新口令必须传递到 Password() 函数进行加密

SET PASSWORD FOR bforta = Password('n3w p@$$w0rd');
SET PASSWORD = Password('n3w p@$$w0rd');#不指定用户时，更新当前登录用户口令

29 数据库维护

备份数据：MySQL数据库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据
- 使用命令行实用程序 mysqldump 转储所有数据库内容到某个外部文件。
- 用命令行实用程序 mysqlhotcopy 从一个数据库复制所有数据（并非所有数据库引擎都支持这个实用程序）
- 使用MySQL的 BACKUP TABLE 或 SELECT INTO OUTFILE 转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则会出错。数据可以用 RESTORE TABLE 来复原。
- 刷新未写数据：为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用 FLUSH TABLES 语句
维护：
- ANALYZE TABLE ，用来检查表键是否正确
- CHECK TABLE 用来针对许多问题对表进行检查。 CHECK TABLE 支持一系列的用于 MyISAM 表的方式。CHANGED 检查自最后一次检查以来改动过的表。 EXTENDED 执行最彻底的检查， FAST 只检查未正常关闭的表， MEDIUM 检查所有被删除的链接并进行键检验， QUICK 只进行快速扫描。
- 如果 MyISAM 表访问产生不正确和不一致的结果，可能需要用REPAIR TABLE 来修复相应的表。
- 如果从一个表中删除大量数据，应该使用 OPTIMIZE TABLE 来收回所用的空间，从而优化表的性能
诊断启动：在对MySQL配置或服务器本身进行更改时出现。
- 在排除系统启动问题时，首先应该尽量用手动启动服务器。通过在命令行上执行 mysqld 启动：
- –help 显示帮助——一个选项列表；
- –safe-mode 装载减去某些最佳配置的服务器；
- –verbose 显示全文本消息（为获得更详细的帮助消息与 --help
  联合使用）；
- –version 显示版本信息然后退出。
查看日志
- 错误日志，包含启动和关闭问题以及任意关键错误的细节。通常名为 hostname.err ，位于 data 目录中。此日志名可用–log-error 命令行选项更改
- 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为 hostname.log ，位于 data 目录中。此名字可以用–log 命令行选项更改。
- 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为 hostname-bin ，位于 data 目录内。此名字可以用 --log-bin 命令行选项更改
- 缓慢查询日志。此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用，通常名为hostname-slow.log ，位于 data 目录中。此名字可以用–log-slow-queries 命令行选项更改。

30 改善性能

http://dev.mysql.com/doc/
- 首先，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 。
- 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
- 最重要的规则就是，每条规则在某些条件下都会被打破。

yxyibb

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
Mysql必知必会笔记

1 了解SQL1.1基本概念数据库：一个以某种有组织的方式存储的数据集合。数据库：保存有组织的数据的容器(通常是一个文件或一组文件），不直接访问，通过DBMS创建和操纵的容器。数据库软件：DBMS，数据库管理系统表：某种特定类型数据的结构化清单。存储某种特定类型的数据。表可以保存顾客清单、产品目录,或者其他信息清单。存在表中的数据是一种类型的数据或一个清单。每个表有自己名字，标识自己。名字唯一。模式（schema）关于数据库和表的布局及特性的信息。列、数据类型列（colu
复制链接

扫一扫