MySQL必知必会思维导图模板_ProcessOn思维导图、流程图

术语

数据库 database

保存有组织的数据的容器（通常是一个文件或一组文件）

数据库管理系统 DBMS

数据库软件。数据库是通过DBMS创建和操纵的容器

表 table

表是一种结构化的文件，可用来存储某种特定类型的数据。

数据库中的每个表都有一个名字，用来标识自己。此名字是唯一的。

模式 schema

表具有一些特性，这些特性定义了数据在表中如何存储，如可以存储什么样的数据，数据如何分解，各部分信息如何命名，等等。 描述表的这组信息就是所谓的模式。

模式可以用来描述数据库中特定的表以及整个数据库（和其中表的关系）

列 column

表中的一个字段

数据库中每个列都有相应的数据类型

数据类型 datatype

所容许的数据的类型。

每个表列都有相应的数据类型，它限制（容许）该列中存储的数据。

数据类型还帮助正确地排序数据，并在优化磁盘使用方面起重要的作用。

行 row

表中的数据是按行存储的，所保存的每个记录存储在自己的行内。

你可能听到用户在提到行row时称其为数据库记录record。在很大程度上，这两个术语是可以互相替代的，但从技术上说，行才是正确的术语。

主键 primary key

一列或一组列，其值能够唯一区分表中每个行

没有主键，更新或删除表中特定行很困难，因为没有安全的办法保证只涉及相关的行。

主键需要满足以下条件

任意两行都不具有相同的主键值

每个行都必须具有一个主键值（主键列不允许NULL值）

SQL Structured Query Language

SQL是一种专门用来与数据库通信的语言

子句 clause

SQL语句由子句构成，有些子句是必需的，而有的是可选的。

一个子句通常由一个关键字和所提供的数据组成

操作符 operator

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

通配符 wildcard

用来匹配值的一部分的特殊字符。 百分号(%)通配符  下划线（_）通配符

通配符本身实际是SQL的WHERE子句中有特殊含义的字符。

搜索模式 search pattern

由字面值、通配符或两者组合构成的搜索条件

字段 field

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

拼接 concatenate

将值联结到一起构成单个值

计算字段

计算字段并不实际存在数据库表中。计算字段是运行时在SELECT语句内创建的。

常用指令

mysql -uroot -p123456

use

describe

describe tbl_name;

show

show databases;

show tables;

show columns from tbl_name;

show status;

show create database db_name;

show create table tbl_name;

show [ global | session ] status [ like 'pattern' | where expr ];

show grants;

show errors;

show warnings

show processlist

检索数据

单表检索

检索单个列

SELECT col_name FROM tbl_name;

检索多个列

SELECT col_name1,col_name2,col_name3 FROM tbl_name;

检索所有列

SELECT * FROM tbl_name;

检索不同的行

SELECT DISTINCT col_name FROM tbl_name;

限制结果

SELECT col_name FROM tbl_name LIMIT 5;

SELECT col_name FROM tbl_name LIMIT 5,5;

SELECT col_name FROM tbl_name LIMIT 4 OFFSET 3;

使用完全限定的表名

SELECT tbl_name.col_name FROM  dat_name.tbl_name;

多表检索

插入任务管理器，标注需求优先级

插入备注，记下备忘事件

排序检索数据

按单列排序

SELECT prod_name FROM products ORDER BY prod_name;

通过非选择列进行排序

通常，ORDER BY子句中使用的列将是为显示所选择的列。 但是实际上并不一定要这样，用非检索的列排序数据是完全合法的。

按多个列排序

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

指定排序方向

使用关键字ACE可进行升序排序。MySQL默认是升序排序

使用DESC关键字可进行降序排序。

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

DESC 和 ACE 关键字只应用到直接位于其前面的列名。 如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

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

区分大小写和排序顺序

使用ORDER BY 和 LIMIT 的组合，能够找出一个列中最高或最低的值。

SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

ORDER BY子句必须是SELECT语句中的最后一条子句。

过滤数据

使用WHERE子句

WHERE子句操作符

检查单个值

SELECT prod_name,prod_price FROM products WHERE prod_price=2.50;

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

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

不匹配检索

SELECT vend_id,prod_name FROM products WHERE vend_id <>1003;

SELECT vend_id,prod_name FROM products WHERE vend_id !=1003;

范围值检查

为了检查某个范围的值，可使用BETWEEN操作符。 其语法与其他WHERE子句的操作符稍有不同，因为它需要两个值。即范围的开始值和结束值。这两个值必须用关键字AND连接。

BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

空值检查

空值NULL 

在创建表时，表设计人员可以指定其中的列是否可以不包含值，一个列不包含值时，称其为包含空值NULL。

null它与字段包含0，空字符串或仅仅包含空格不同。

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

SELECT prod_name FROM products WHERE prod_price IS NULL;

组合WHERE子句

AND操作符

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

OR操作符

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

计算次序

WHERE子句可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。

SQL像多数语言一样，在处理OR操作符前，优先处理AND操作符。

（）圆括号具有较AND或OR操作符更高的计算次序。

IN操作符

圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。

IN操作符后跟由逗号分隔的合法值清单，整个清单必须括在圆括号中。

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

IN操作符完成与OR操作符相同的功能。

为什么要使用IN操作符？

在使用长的合法选项清单时，IN操作符的语法更清楚且直观。

在使用IN时，计算次序更容易管理。（因为使用的操作符更少）

IN操作符一般比OR操作符清单执行的更快。

IN的最大优点是可以包含其他SELECT语句，使得能够更动态地建立WHERE子句。

NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后跟的任何条件。

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

MySQL支持使用NOT 对 IN、BETWEEN 和 EXISTS 子句取反。

用通配符进行过滤

LIKE 操作符

为在搜索子句中使用通配符，必须使用LIKE操作符。

LIKE指示MySQL,后跟的搜索模式利用通配符而不是直接相等匹配进行比较。

通配符 wildcard

根据MySQL的配置方式，使用通配符进行搜索是可以区分大小写的。 如果区分大小写，'jet%'与JetPack将不匹配。

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

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '%anvil%';

通配符也可以出现在搜索模式中间，虽然这样做不太有用。

SELECT prod_name FROM products WHERE prod_name LIKE 's%e';

注意尾空格

尾空格可能会干扰通配符匹配。 例如，在保存词anvil时，如果它后面有一个或多个空格。则子句WHERE prod_name LIKE '%anvil'将不会匹配它们。 因为在最后的l后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。 一个更好的办法是使用函数去掉尾空格。

百分号% 通配符

在搜索串中，%表示任何字符出现任意次数（代表搜索模式中给定位置的0个、1个或多个字符）

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE ‘jet%';

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

下划线（_）通配符

下划线（_）通配符只匹配单个字符而不是多个字符。

与%能匹配0个字符不一样，_总是匹配一个字符，不能多也不能少。

使用通配符的技巧

不要过度使用通配符。如果其他操作符也能达到相同的目的，应该使用其他操作符。

在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。 把通配符置于搜索模式的开始处，搜索起来是最慢的。

MySQL正则表达式

基本字符匹配

使用REGEXP关键字，它告诉MySQL: REGEXP后所跟的东西为正则表达式。

LIKE 与 REGEXP之间有一个重要的差别。 LILE匹配整个列，如果被匹配的文本在列值中出现，LIKE将不会找到它，相应的行也不被返回（除非使用通配符）。 而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行被返回。

使用定位符^ 和 $ ，REGEXP也可以用来匹配整个列值，从而起到与LIKE相同的作用。

. 是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符。

进行OR匹配

为搜索两个串之一（或者为这个串，或者为另外一个串），使用 | 

使用 | 从功能上类似于在SELECT语句中使用OR语句，多个OR条件可并入单个正则表达式。 例如：'1000 | 2000 | 3000'将匹配1000或2000或3000

匹配几个字符之一

如果想要匹配特定的字符，可以通过指定一组用 [ ] 括起来的字符来完成。

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

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

匹配范围

集合可用来定义要匹配的一个或多个字符。例如，[0123456789]将匹配数字0到9 为简化这种类型的集合，可使用 - 来定义一个范围。[0-9] 等同于 [0123456789]

范围不限于完整的集合，[1-3] 和 [6-9]也是合法的范围。 此外，范围不一定只是数值的，[a-z]匹配任意字母字符。

匹配特殊字符

为了匹配特殊字符，必须用\\ 为前导。 \\- 表示查找 -， \\. 表示查找 .。 这种处理就是所谓的转义（escaping）。正则表达式内具有特殊意义的所有字符都必须以这种方式转义。

\\也用来引用元字符（具有特殊含义的字符）

匹配字符类

[:character_class:]

匹配多个实例

重复元字符

*

0个或多个匹配

+

1个或多个匹配 等于{1，}

？

0个或1个匹配 等于{0，1}

{n}

指定数目的匹配

{n,}

不少于指定数目的匹配

{n,m}

匹配数目的范围，m不超过255

定位符

为了匹配特定位置的文本，需要使用定位符。 如没有使用定位符，则是匹配一个串中任意位置的文本。

定位元字符

^   文本的开始

^有两个用法。在集合中（用[ ] 定义），用它来否定该集合。 否则，用来指串的开始处。

$   文本的结尾

[[:<:]]   词的开始

[[]:>:]  词的结尾

简单的正则表达式测试

可以在不使用数据库表的情况下用SELECT来测试正则表达式。 REGEXP检查决是返回0（没有匹配）或1(匹配)。 可以用带文字串的REGEXP来测试表达式，相应语法如下： SELECT 'hello' REGEXP '[0-9]'

函数

文本处理函数

CONCAT()

mysql> SELECT CONCAT('My', 'S', 'QL');         -> 'MySQL' mysql> SELECT CONCAT('My', NULL, 'QL');         -> NULL mysql> SELECT CONCAT(14.3);         -> '14.3'

TRIM()

RTRIM()

LTRIM()

TRIM( [ {BOTH | LEADING | TRAILING } [remstr] FROM ] str ) TRIM( [remstr FROM] str)

mysql>SELECT TRIM('  bar   ');     ->'bar'

mysql>SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');     ->'barxxx'

mysql>SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');     ->'bar'

mysql>SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');     ->'barx'

LEFT(str,len)

SELECT LEFT('foobarbar',5); ->'fooba'

RIGHT(str，)

SELECT RIGHT('foobarbar',4); ->'rbar'

LENGTH(str)

SELECT LENGTH('text'); ->4

SELECT LENGTH('汉'); ->3

LOCATE(substr,str) LOCATE(substr,str,pos)

SELECT LOCATE('bar','foobarbar'); ->4

SELECT LOCATE('xbar','foobarbar'); ->0

SELECT LOCATE('bar','foobarbar'); ->7

SELECT LOCATE('bar','fooBarbar'); ->4

UPPER(str)

SELECT UPPER('Hej'); ->'HEJ'

LOWER(str)

SELECT LOWER('QUADRATICALLY'); ->'quadratically'

SUBSTR(str,pos),SUBSTR(str FROM pos),SUBSTR(str,pos,len),SUBSTR(str FROM pos FOR len) SUBSTR() is a synonym for SUBSTRING() SUBSTRING(str,pos),SUBSTRING(str FROM pos),SUBSTRING(str,pos,len),SUBSTRING(str FROM pos FOR len)

mysql> SELECT SUBSTRING('Quadratically',5);         -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4);         -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6);         -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3);         -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3);         -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);         -> 'ki'

日期和时间处理函数

ADDDATE(date,INTERVAL expr unit)

mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);         -> '2008-02-02' mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);         -> '2008-02-02'

DATE_ADD(date,INTERVAL expr unit) DATE_SUB(date,INTERVAL expr unit)

mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);         -> '2018-05-02' mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);         -> '2017-05-01' mysql> SELECT DATE_ADD('2020-12-31 23:59:59',     ->                 INTERVAL 1 SECOND);         -> '2021-01-01 00:00:00' mysql> SELECT DATE_ADD('2018-12-31 23:59:59',     ->                 INTERVAL 1 DAY);         -> '2019-01-01 23:59:59' mysql> SELECT DATE_ADD('2100-12-31 23:59:59',     ->                 INTERVAL '1:1' MINUTE_SECOND);         -> '2101-01-01 00:01:00' mysql> SELECT DATE_SUB('2025-01-01 00:00:00',     ->                 INTERVAL '1 1:1:1' DAY_SECOND);         -> '2024-12-30 22:58:59' mysql> SELECT DATE_ADD('1900-01-01 00:00:00',     ->                 INTERVAL '-1 10' DAY_HOUR);         -> '1899-12-30 14:00:00' mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);         -> '1997-12-02' mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',     ->            INTERVAL '1.999999' SECOND_MICROSECOND);         -> '1993-01-01 00:00:01.000001'

ADDDATE(date,days)

mysql> SELECT ADDDATE('2008-01-02', 31);         -> '2008-02-02'

ADDTIME(expr1,expr2) ADDTIME()

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');         -> '2008-01-02 01:01:01.000001' mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');         -> '03:00:01.999997'

CURDATE() CURRENT_DATE CURRENT_DATE()

mysql> SELECT CURDATE();         -> '2008-06-13' mysql> SELECT CURDATE() + 0;         -> 20080613

CURTIME([fsp]) CURRENT_TIME CURRENT_TIME([fsp])

mysql> SELECT CURTIME(); +-----------+ | CURTIME() | +-----------+ | 19:25:37  | +-----------+ mysql> SELECT CURTIME() + 0; +---------------+ | CURTIME() + 0 | +---------------+ |        192537 | +---------------+ mysql> SELECT CURTIME(3); +--------------+ | CURTIME(3)   | +--------------+ | 19:25:37.840 | +--------------+

NOW() CURRENT_TIMESTAMP CURRENT_TIMESTAMP([fsp]) LOCALTIME LOCALTIME([fsp]) LOCALTIMESTAMP LOCALTIMESTAMP([fsp])

mysql> SELECT NOW();         -> '2007-12-15 23:50:26' mysql> SELECT NOW() + 0;         -> 20071215235026.000000

DATE(expr)

mysql> SELECT DATE('2003-12-31 01:02:03');         -> '2003-12-31'

DATEDIFF(expr1,expr2)

mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');         -> 1 mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');         -> -31

DATE_FORMAT(date,format)

数值处理函数

算术处理函数

% MOD

*

mysql> SELECT 3*5;         -> 15 mysql> SELECT 18014398509481984*18014398509481984.0;         -> 324518553658426726783156020576256.0 mysql> SELECT 18014398509481984*18014398509481984;         -> out-of-range error

+

子主题

-

mysql> SELECT 3-5;         -> -2

-

mysql> SELECT - 2;         -> -2

/

mysql> SELECT 3/5;         -> 0.60 mysql> SELECT 102/(1-1);         -> NULL

DIV

mysql> SELECT 5 DIV 2, -5 DIV 2, 5 DIV -2, -5 DIV -2;         -> 2, -2, -2, 2

数学处理函数

ABS()

mysql> SELECT ABS(2);         -> 2 mysql> SELECT ABS(-32);         -> 32

MOD(N,M) N % M N MOD M

mysql> SELECT MOD(234, 10);         -> 4 mysql> SELECT 253 % 7;         -> 1 mysql> SELECT MOD(29,9);         -> 2 mysql> SELECT 29 MOD 9;         -> 2

mysql> SELECT MOD(34.5,3);         -> 1.5

CEIL(X) CEILING(X)

mysql> SELECT CEILING(1.23);         -> 2 mysql> SELECT CEILING(-1.23);         -> -1

FLOOR(X)

mysql> SELECT FLOOR(1.23), FLOOR(-1.23);         -> 1, -2

RAND([N])

子主题

ROUND(X) ROUND(X,D)

mysql> SELECT ROUND(-1.23);         -> -1 mysql> SELECT ROUND(-1.58);         -> -2 mysql> SELECT ROUND(1.58);         -> 2 mysql> SELECT ROUND(1.298, 1);         -> 1.3 mysql> SELECT ROUND(1.298, 0);         -> 1 mysql> SELECT ROUND(23.298, -1);         -> 20 mysql> SELECT ROUND(.12345678901234567890123456789012345, 35);         -> 0.123456789012345678901234567890

mysql> SELECT ROUND(150.000,2), ROUND(150,2); +------------------+--------------+ | ROUND(150.000,2) | ROUND(150,2) | +------------------+--------------+ |           150.00 |          150 | +------------------+--------------+

TRUNCATE(X,D)

mysql> SELECT TRUNCATE(1.223,1);         -> 1.2 mysql> SELECT TRUNCATE(1.999,1);         -> 1.9 mysql> SELECT TRUNCATE(1.999,0);         -> 1 mysql> SELECT TRUNCATE(-1.999,1);         -> -1.9 mysql> SELECT TRUNCATE(122,-2);        -> 100 mysql> SELECT TRUNCATE(10.28*100,0);        -> 1028

系统处理函数

聚集函数

AVG([DISTINCT] expr) [over_clause]

mysql> SELECT student_name, AVG(test_score)        FROM student        GROUP BY student_name;

COUNT(expr)[over_clause]

COUNT(DISTINCT expr,[expr...])

mysql> SELECT COUNT(DISTINCT results) FROM student;

GROUP_CONCAT(expr)

GROUP_CONCAT([DISTINCT] expr [expr...]              [ORDER BY {unsigned_integer | col_name | expr}                 [ASC | DESC] [,col_name...] ]               [SEPARATOR str_val])

mysql> SELECT student_name,          GROUP_CONCAT(test_score)        FROM student        GROUP BY student_name;

mysql> SELECT student_name,          GROUP_CONCAT(DISTINCT test_score                       ORDER BY test_score DESC SEPARATOR ' ')        FROM student        GROUP BY student_name;

JSON_ARRAYAGG(col_or_expr)[over_clause]

mysql> SELECT o_id, attribute, value FROM t3; +------+-----------+-------+ | o_id | attribute | value | +------+-----------+-------+ |    2 | color     | red   | |    2 | fabric    | silk  | |    3 | color     | green | |    3 | shape     | square| +------+-----------+-------+ 4 rows in set (0.00 sec) mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes     -> FROM t3 GROUP BY o_id; +------+---------------------+ | o_id | attributes          | +------+---------------------+ |    2 | ["color", "fabric"] | |    3 | ["color", "shape"]  | +------+---------------------+ 2 rows in set (0.00 sec)

JSON_OBJECTAGG(key,value)[over_clause]

mysql> SELECT o_id, attribute, value FROM t3; +------+-----------+-------+ | o_id | attribute | value | +------+-----------+-------+ |    2 | color     | red   | |    2 | fabric    | silk  | |    3 | color     | green | |    3 | shape     | square| +------+-----------+-------+ 4 rows in set (0.00 sec) mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)     -> FROM t3 GROUP BY o_id; +------+---------------------------------------+ | o_id | JSON_OBJECTAGG(attribute, value)      | +------+---------------------------------------+ |    2 | {"color": "red", "fabric": "silk"}    | |    3 | {"color": "green", "shape": "square"} | +------+---------------------------------------+ 2 rows in set (0.00 sec)

MAX([DISTINCT] expr) [over_clause]

mysql> SELECT student_name, MIN(test_score), MAX(test_score)        FROM student        GROUP BY student_name;

MIN([DISTINCT] expr) [over_clause]

mysql> SELECT student_name, MIN(test_score), MAX(test_score)        FROM student        GROUP BY student_name;

SUM([DISTINCT] expr) [over_clause]

窗口函数 Window Function

分组数据

创建分组

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

GROUP BY 子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

GROUP BY 子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。

如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。

GROUP BY子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚集函数。 如果在SELECT中使用表达式，则必须在GROUP BY 子句中指定相同的表过式。不能使用别名。

除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。

如果分组列中具有NULL值，则NULL将作为一个分组返回，如果有多行NULL值，它们将分为一组

GROUP BY 子句必须出现在WHERE子句后，ORDER BY子句之前。

ROLLUP

使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值

mysql> SELECT year, SUM(profit) AS profit        FROM sales        GROUP BY year WITH ROLLUP; +------+--------+ | year | profit | +------+--------+ | 2000 |   4525 | | 2001 |   3010 | | NULL |   7535 | +------+--------+

过滤分组

除了能用GROUP BY 分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。

WHERE过滤指定的是行而不是分组。

MySQL提供了HAVING子句用来过滤分组

WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中， 这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组

子查询

SQL 允许创建子查询subquery，即嵌套在其他查询中的查询。 为什么要这么做呢？

利用子查询进行过滤

SELECT cust_name,cust_contact  FROM customers  WHERE cust_id IN (  SELECT cust_id FROM orders WHERE order_num IN   (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2'))

作为计算字段使用子查询

SELECT cust_name,cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders FROM customers ORDERY BY cust_name;

相关子查询

联结表

创建联结

SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name;

SELECT prod_name,vend_name,prod_price,quantity FROM products,vendors,orderitems WHERE products.vend_id=vendors.vend_id AND orderitems.prod_id=products.prod_id AND order_num=20005;

笛卡儿积   cartesian product

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

叉联结

内部联结 等值联结

内部联结也称为等值联结，它基于两个表之间的相等测试。

SELECT vend_name,prod_name,prod_price  FROM vendors INNER JOIN products on vendors.vend_id=products.vend_id;

自联结

使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商的其他物品是否也在这些问题。

SELECT prod_id,prod_name,FROM products WHERE vend_id=( SELECT vend_id FROM products WHERE prod_id='DTNTR');

SELECT p1.prod_id,p1.prod_name FROM products AS p1，products AS p2 WHERE P1.vend_id=p2.vend_id AND p2.vend_id='DTNTR';

用自联结而不用子查询

自然联结

标准的联结（内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次

怎么完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。

自然联结是这样一种联结，其中你只能选择那些唯一的列，这一般是通过对第一个表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。

外部联结

左外联结

SELECT  customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id;

右外联结

使用带聚集函数的联结

性能考虑

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

组合查询

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

有两种情况需要使用组合查询

在单个查询中从不同的表返回类似结构的数据

对单个表执行多个查询，按单个查询返回数据

组合查询和多个WHERE条件

多数情况下，组合相同表的两个查询完成的工作与具有多个WHERE子句条件的单条查询完成的工作相同。 换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询给出。

创建组合查询

可用UNION操作符来组合数条SQL查询。

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(1002,1001);

UNION规则

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔

UNION中的每个查询必须包含相同的列，表达式或聚集函数（不过各个列不需要以相同的次序列出）

列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型

包含或取消重复的行

UNION从查询结果集中自动去除了重复的行，换句话说，它的行为与单条SELECT语句中使用多个where子句条件一样。

如果想返回所有匹配行，可使用UNION ALL

对组合查询结果排序

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后。

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price<=5  UNION  SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id in(1002,1001) ORDER BY vend_id,prod_price;

全文本搜索

启用全文本搜索

CREATE TABLE `productnotes` (   `note_id` int NOT NULL AUTO_INCREMENT,   `prod_id` char(10) NOT NULL,   `note_date` datetime NOT NULL,   `note_text` text,   PRIMARY KEY (`note_id`),   FULLTEXT KEY `note_text` (`note_text`) ) ENGINE=MyISAM

不要在导入数据时使用FULLTEXT

进行全文本搜索

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

使用完整的Match（）说明

搜索不区分大小写

全文本搜索的一个重要部分是对结果排序。具有较高等级的行先返回。

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

排序多个搜索项

使用查询扩展

查询扩展用来设法放宽所返回的全文本搜索结果的范围。

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

首先，进行一个基本的全文本搜索，找出与搜索条件匹配的所有行

其次，MySQL检查这些匹配行并选择所有有用的词

再其次，MySQL再次进行全文本搜索，这次不仅使用原来的条件，而且还使用所有有用的词

表中的行越多，使用查询扩展返回的结果越好

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

布尔文本搜索

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

要匹配的词

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)

排列提示（指定某些词比其他词更重要，更重要的词等级更高）

表达式分组

另外一些内容

全文本布尔操作符

+

包含，词必须存在

SELECT note_text FROM productnotes WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE)

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE)

-

排除，词必须不出现

>

包含，而且增加等级值

SELECT note_text FROM productnotes WHERE Match(note_text) Against('>rabbit <carrot' IN BOOLEAN MODE)

<

包含，而且减少等级值

（）

把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等）

SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE)

~

取消一个词的排序值

*

词尾的通配符

“”

定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语）

SELECT note_text FROM productnotes WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE)

排列而不排序

全文本搜索的使用说明

在索引全文本数据时，短词被忽略且从索引中排除。短词定义为那些具有3个或3个以下字符的词（如果需要，这个数目可以更改）

MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本数据时总是被忽略。如果需要，可以覆盖这个列表。

许多词出现的频率很高，搜索它们没有用处（返回太多的结果）。因此，MySQL规定了一条50%的规则， 如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%规则不用于IN BOOLEAN MODE

如果表中的行数少于3行，则全文本搜索不返回结果（因为每个词或者不出现，或者至少出现在50%的行中）

忽略词中的单引号。例如，don't索引为dont

不具有词分隔符（包括日语和汉语）的语言不能恰当地返回全文本搜索结果

仅在MyISAM数据库引擎中支持全文本搜索。

插入数据

插入完整的行

INSERT INTO customers VALUES(NULL,'Peep E. LaPew','100 Main Street','Los Angeles','CA','90046','USA',NULL,NULL)

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

插入行的一部分

插入多行

使用多条INSERT语句，一次提交它们，每条语句用一个分号结束。

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

如果每条INSERT语句中的列名和次序相同，则可以使用如下组合语句。 其中单条INSERT语句有多组值，每组值用一对圆括号括起来。

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

插入某些查询结果

INSERT INTO customers(cust_id,cust_contact,cust_emai,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;

更新和删除数据

更新数据

更新表中特定的行

UPDATE custtomers SET cust_email='elmer@fudd.com' WHERE cust_id=10005;

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

更新表中所有行

在UPDATE语句中使用子查询

IGNORE关键字

UPDATE IGNORE customers……

删除数据

从表中删除特定行

DELETE FROM customers WHERE cust_id='10005';

从表中删除所有行

更快的删除 TRUNCATE

更新和删除的指导原则

除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句

保证每个表都有主键，尽可能像WHERE子句那样使用它

在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。

使用强制实施引用完整性的数据，这样MySQL将不允许删除具有与其他表相关联的数据的行。

创建和操纵表

创建表

表创建基础

CREATE TABLE customers ( cust_id int NOT NULL AUTO_INCREMENT, cust_name char(50) NOT NULL, cust_addrese 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(50) NULL, PRIMARY KEY(cust_id) )ENGINE=InnoDB;

使用NULL值

NULL值就是没有值或缺值。允许NULL值的列也允许在插入行时不给出该列的值。 不允许NULL值的列，在插入或更新行时，该列必须有值。

每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。

NULL为默认设置，如果不指定NOT NULL,则认为指定的是NULL.

不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。 如果指定‘’（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。

主键再介绍

主键值必须唯一。即，表中的每个行必须具有唯一的主键值。 如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

CREATE TABLE orderitems  ( order_num int NOT NULL, order_item int NOT NULL, prod_id char(10)NOT NULL, quantity int NOT NULL, item_price decimal(8,2) NOT NULL, PRIMARY KEY(order_item,order_num) )ENGINE=InnoDB;

使用AUTO_INCREMENT

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引

覆盖AUTO_INCREMENT，如果一个列被指定为AUTO_INCREMENT,则它需要使用特殊的值吗？ 你可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。 后续的增量将开始使用该手工插入的值。

指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

不允许函数与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。

使用默认值而不是NULL值；许多数据库开发人员使用默认值而不是NULL，特别是对用于计算或数据分组的列更是如此。

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;

引擎类型

InnoDB 是一个可靠的事务处理引擎，它不支持全文本搜索

MEMORY在功能等同于MyISAM，但由于数据存储在内存（而不是磁盘中，速度很快（特别适合于临时表）

MyISAM 是一个性能极高的引擎，它支持全文本搜索。但是不支持事务

外键不能跨引擎

更新表

ALTER TABLE vendors ADD vend_phone char(20);

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语句，每个更改用逗号分隔

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

用新的列布局创建一个新表

使用INSERT SELECT语句，从旧表复制数据到新表。如果有必要可使用转换函数和计算字段

检验包含所需数据的新表

重命名旧表（如果确定，可以删除它）

用旧表原来的名字重命名新表

根据需要，重新创建触发器、存储过程、索引和外键

删除表

DROP TABLE customers2

重命名表

RENAME TABLE customers2 to customers;

RENAME TABLE backup_customers to customers,              backup_vendors to vendors,              backup_products to products;

使用视图

什么是视图

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

为什么使用视图

重用SQL语句

简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道它的基本查询细节

使用表的组成部分而不是整个表

保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限

更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的规则和限制

与表一样，视图必须唯一命名（不能给视图取与别的视图或表相同的名字）

对于可以创建的视图数目没有限制

为了创建视图，必须具有足够的访问权限。这些限制通常由数据库管理人员授予。

视图可以嵌套，即可以利用从其他视图中检索数据的查询来构造一个视图

ORDER BY 也可以用在视图中，但如果从该视图检索数据的SELECT语句也包含有ORDER BY，那么该视图中的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=orders.cust_id  AND orderitems.order_num=orders.order_num;

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

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

用视图过滤不想要的数据

使用视图与计算字段

更新视图

通常，视图是可更新的（即可对它们使用INSERT、UPDATE和DELETE）。 更新一个视图将更新其基表。

并非所有的视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新。 如果视图定义中有以下操作，则不能进行视图的更新：

分组（使用GROUP BY和HAVING）

联结

子查询

并

聚集函数（MIN()、COUNT()、SUM()等）

DISTINCT

导出（计算）列

存储过程

什么是存储过程

简单来说存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合。

为什么要使用存储过程

通过把处理封装在容易使用的单元中，简化复杂的操作。

由于不要求反复建立一系列处理步骤，这保证了数据的完整性。

简化对变动的管理。 如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码。

提高性能。 因为使用存储过程比使用单独的SQL语句要快。

存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写更强更灵活的代码。

缺陷

一般来说，存储过程的编写比基本的SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。

可能没有创建存储过程的安全访问权限。

使用存储过程

执行存储过程

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

创建存储过程

CREATE PROCEDURE productpricing() BEGING      SELECT Avg(prod_price) AS priceaveage FROM products; END;

更改命令行实用程序的语句分隔符

DELIMITER // CREATE PROCEDURE productpricing() BEGIN      SELECT Avg(prod_price) AS priceaveage FROM products; END // DELIMITER ;

删除存储过程

DROP PROCEDURE productpricing;

使用参数

示例

--创建存储过程  此存储过程接受3个参数，每个参数必须具有指定的数据类型。 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个参数，因此必须正好传递3个参数，不多也不少。 --在调用时，这条语句并不显示任何数据。它返回以后可以显示（或在其他处理中使用）的变量。 CALL productpricing(         @pricelow,         @pricehigh,         @priceaverage); --为了显示检索出的变量值需使用SELECT语句。 SELECT @pricelow;

--创建存储过程 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;

MySQL支持的参数类型

IN

传递给存储过程

OUT

从存储过程传出

INOUT

对存储过程传入和传出

参数的数据类型

存储过程的参数允许的数据类型与表中使用的数据类型相同。

记录集不是允许的类型。因此，不能通过一个参数返回多个行和列。

建立智能存储过程

考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税。 不过只针对某些顾客。那么你需要做下面几件事： <ul><li>获得合计（与以前一样）；</li><li>把营业税有条件地添加到合计；</li><li>返回合计（带或不带税）；</li></ul>

存储过程的完整工作如下： --Name:ordertotal --Parameters:onumber=order number --           taxable=0 if not taxable,1 if taxable --           ototal=order total variable CREATE PROCEDURE ordertotal(      IN onumber INT,      IN taxable BOOLEAN,      OUT ototal DICIMAL(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 taxable? IF taxable THEN  SELECT total+(total/100*taxrate) INTO total; END IF; --And finally,save to out variable SELECT total INTO ototal; END; --调用 CALL ordertotal(20005,0,@total); SELECT @total;

检查存储过程

为显示用来创建一个存储过程的CREATE语句，使用SHOW CREATE PROCEDURE语句。

为了获得包括何时，由谁创建等详细信息的存储过程列表，使用SHOW PROCEDURE STATUS. SHOW PROCEDURE STATUS列出所有存储过程，为限制其输出，可使用LIKE指定一个过滤模式。 例如： SHOW PROCEDURE STATUS LIKE 'ordertotal';

说明

--  用来注释

DECLARE  用来声明变量，要求指定变量名和数据类型，它也支持可选的默认值。

COMMENT  它不是必需的，如果给出，将在SHOW PROCEDURE STATUS的结果中显示。

所有MySQL变量都必须以@开始

游标

什么是游标

游标cursor是一个存储在MySQL服务器上的数据库查询， 它不是一条SELECT语句，而是被该语句检索出来的结果集。 在存储游标之后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

不像多数DBMS，MySQL游标只能用于存储过程和函数

使用游标

使用游标涉及几个明确的步骤：

在能够使用游标前，必须声明（定义）它。 这个过程实际上没有检索数据，它只是定义要使用的SELECT语句。

一旦声明后，必须打开游标以供使用。 这个过程用前面定义的SELECT语句把数据实际检索出来。

对于填有数据的游标，根据需要取出（检索）各行。

在结束游标使用时，必须关闭游标。

创建游标

游标用DECLARE语句创建。 DECLARE命名游标，并定义相应的SELECT语句，根据需要带WHERE和其他子句。

CREATE PROCEDURE processorders() BEGIN --下面的语句定义了名为ordernumbers的游标      DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; END;

打开和关闭游标

OPEN ordernumbers;

在处理OPEN语句时执行查询，存储检索出的数据以供浏览和滚动。

CLOSE ordernumbers;

CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭。

如果你不明确关闭游标，MySQL将会在到达END语句时自动关闭它。

一个游标关闭后，如果没有重新打开，则不能使用它。 但是，使用声明过的游标不需要再次声明，用OPEN语句打开它就可以了。

使用游标数据

在一个游标被打开后，可以使用FETCH语句分别访问它的每一行。 FETCH指定检索什么数据（所需的列），检索出来的数据存储在什么地方。 它还向前移动游标中的内部行指针，使下一条FETCH语句检索下一行。

--示例1，从游标中检索单个行（第一行） 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; --Fetch用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。 FETCH ordernumbers INTO O; --Close the cursor CLOSE ordernumbers; END;

--示例2，循环检索数据，从第一行到最后一行 CREATE PROCEDURE processorders() BEGIN --声明局部变量 DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; --定义游标 DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders; --这条语句定义了一个CONTINE HANDLER,它是在条件出现时被执行的代码。 --这里，它指出当SQLSTATE ’02000’出现时，SET done=1。 --SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多行供循环而不能继续时，出现这个条件。 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; --打开游标 OPEN ordernumbers; --循环 Loop through all rows REPEAT --Get order number     FETCH ordernumbers INTO o; --可以在循环内放入任意需要的处理（在FETCH语句之后，循环结束之前） --结束循环 End of loop UNTIL done END REPEAT; --关闭游标 CLOSE ordernumbers; END;

--示例3 CREATE PROCEDURE processorders() BEGIN --定义局部变量 DECLARE done BOOLEAN DEFAULT 0; DECLARE o INT; DECLARE t DECIMAL(8,2); --定义游标 DECLARE ordernumber CURSOR FOR SELECT order_num FROM orders; --定义句柄 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; OPEN ordernumbers; REPEAT    FETCH ordernumbers INTO o;    --调用其他存储过程    CALL ordertotal(o,1,t);    INSERT INTO ordertotals(order_num,total) VALUES(o,t); UNTIL done END REPEAT; CLOSE ordernumbers; END;

DECLARE语句的次序

DECLARE语句的发布存在特定的次序。 用DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义， 而句柄必须在游标之后定义。

触发器

什么是触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，如果你想要某条语句（或某些语句）在事件发生时自动执行，怎么办呢？ 例如： <ul><li>每当增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，州的缩写是否为大写。</li><li>每当订购一个产品时，都从库存数量中减去订购的数量。</li><li>无论何时删除一行，都在某个存档表中保留一个副本。 所有这些例子的共同之处是它们都需要在某个表发生更改时自动处理。这确切地说就是触发器。</li></ul>

 触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于BEGIN和END之间的一组语句）

DELETE

INSERT

UPDATE

只有表才支持触发器，视图不支持，临时表也不支持。

触发器按每个表每个事件每次地定义，每个表每次事伯每次只允许一个触发器。 因此，每个表最多支持6个触发器。（每条INSERT、UPDATE和DELETE的之前和之后） 单一触发器不能与多个事件或多个表关联

如果BEFORE触发器失败，则MySQL将不执行请求的操作。 此外，如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）

创建触发器

在创建触发器时，需要给出4条信息

唯一的触发器名

触发器关联的表

触发器应该响应的活动（DELETE、INSERT、UPDATE）

触发器何时执行（处理之前或之后）

触发器用CREATE TRIGGER语句创建

CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added' INTO @e;

删除触发器

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 INTO @e; INSERT INTO orders ( order_date, cust_id ) VALUES( now(), 10001 ); SELECT @e;

UPDATE触发器

在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); //每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper（NEW.vend_state）替换

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、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。

应该用触发器来保证数据的一致性（大小写、格式等）。 在触发器中执行这种类型的处理的优点是它总是进行这种处理，而且是透明的进行，与客户机应用无关。

触发器的一种非常有意义的使用是创建审计跟踪。 使用触发器，把更改（如果需要，甚至还有之前和之后的状态）记录到另一个表非常容易。

遗憾的是，MySQL触发器中不支持CALL语句。 这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

管理事务处理

什么是事务处理

事务处理是一种机制，用来管理必须成批执行的MySQL操作，以保证数据库不包含不完整的操作结果。 利用事务处理，可以保证一组操作不会中途停止，它们或者作为整体执行，或者完全不执行（除非明确指示）。 如果没有错误发生，整组语句提交给（写到）数据库表。 如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

事务处理相关术语

事务   transaction 指一组SQL语句

回退   rollback 指撤销指定SQL语句的过程

提交   commit  指将未存储的SQL语句结果写入数据库表

保留点    savepoint  指事务处理中设置的临时占位符(place-holder)，你可以对它发布回退（与回退整个事务处理不同）

控制事务处理

使用rollback

示例

SELECT * FROM ordertotals; START TRANSACTION; DELETE FROM ordertotals; SELECT * FROM ordertotals; ROLLBACK; SELECT * FROM ordertotals;

哪些语句可以回退

事务处理用来管理INSERT、UPDATE和DELETE语句。你不能回退SELECT语句（这样做也没有意义）。 不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

使用commit

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。 但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句。

隐含事务关闭  当commit或rollback语句执行后，事务会自动关闭（将来的更改会隐含提交）

START TRANSACTION; DELETE FROM orderitems WHERE order_num=20010; DELETE FROM orders WHERE order_num=20010; COMMIT;

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。 但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。 为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符。 这样，如果需要回退，可以回退到某个占位符。这些占位符称为保留点。为了创建占位符，可使用SAVEPOINT语句

//创建保留点  每个保留点都取标识它的唯一名字 SAVEPOINT delete1; //回退到保留点 ROLLBACK TO delete1;

保留点越多越好    可以在MySQL代码中设置任意多的保留点，越多越好。 为什么呢？因为保留点越多，你就越能按自己的意愿灵活的进行回退。

释放保留点   保留点在事务处理完成（执行一条ROLLBACK或COMMIT）后自动释放。 MySQL5以来，也可以用RELEASE SAVEPOINT明确地释放保留点。

更改默认的提交行为

默认的MySQL行为是自动提交所有更改。 换句话说，任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。 为指示MySQL不自动提交更改，需要使用以下语句： SET AUTOCOMMIT=0; autocommit标志决定是否自动提交更改，不管有没有commit语句，设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。

autocommit标志是针对每个连接而不是服务器的。

全球化和本地化

字符集和校对顺序

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同的方式存储和检索。 因此，MySQL需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法。