首页  思维导图  详情

MySQL必知必会

2023-06-05 20:34:55   1  举报





AI智能生成

为你推荐

查看更多



电商微服务技术架构图--产品经理必知必会系列架构师必知必会系列

Oracle PL SQL必知必会

Mysql 必知必会总结

《JSON必知必会》-Lindsay Bassett

MySQL必知必会笔记

作者其他创作

大纲/内容

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

数据库 database

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

数据库管理系统DBMS

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

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

表 table

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

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

模式 schema

表中的一个字段

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

列 column

所容许的数据的类型。

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

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

数据类型 datatype

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

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

行 row

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

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

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

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

主键需要满足以下条件

主键 primary key

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

SQLStructured Query Language

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

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

子句 clause

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

操作符 operator

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

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

通配符 wildcard

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

搜索模式 search pattern

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

字段 field

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

拼接 concatenate

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

计算字段

术语

mysql -uroot -p123456

use

describe tbl_name;

describe

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

show

常用指令

SELECT col_name FROM tbl_name;

检索单个列

微信图片_20190813205230.jpg

检索多个列

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 4 OFFSET 3;

限制结果

SELECT tbl_name.col_name FROM dat_name.tbl_name;

使用完全限定的表名

单表检索

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

插入备注，记下备忘事件

多表检索

URL

检索数据

SELECT prod_name FROM products ORDER BY prod_name;

按单列排序

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

通过非选择列进行排序

按多个列排序

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

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

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

指定排序方向

区分大小写和排序顺序

SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 1;

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

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

排序检索数据

使用WHERE子句

检查单个值

不匹配检索

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

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

范围值检查

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

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

空值NULL

SELECT prod_name FROM products WHERE prod_price IS NULL;

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

空值检查

WHERE子句操作符

AND操作符

OR操作符

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

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

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

计算次序

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

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

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

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

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

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

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

为什么要使用IN操作符？

IN操作符

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

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

NOT操作符

组合WHERE子句

为了进行更强的过滤控制，MySQL允许给出多个WHERE子句。这些子句可以用两种方式使用：以AND子句的方式或 OR子句的方式。

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

LIKE 操作符

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

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

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

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

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

注意尾空格

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

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

百分号% 通配符

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

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

下划线（_）通配符

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

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

使用通配符的技巧

用通配符进行过滤

过滤数据

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

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

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

基本字符匹配

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

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

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

进行OR匹配

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

[ ] 是另一种形式的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}

不少于指定数目的匹配

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

重复元字符

匹配多个实例

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

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

^ 文本的开始

$ 文本的结尾

[[:<:]] 词的开始

[[]:>:] 词的结尾

定位元字符

定位符

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

简单的正则表达式测试

MySQL正则表达式

CONCAT()

TRIM()

RTRIM()

LTRIM()

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'

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

RIGHT(str，)

SELECT LENGTH('text');->4

SELECT LENGTH('汉');->3

LENGTH(str)

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

UPPER(str)

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

LOWER(str)

文本处理函数

当应用于二进制字符串（二进制、变二进制、BLOB）时，UPPER()和LOWER()是无效的。要执行二进制字符串的大小写转换，首先要将其转换为非二进制字符串

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

CURDATE()CURRENT_DATECURRENT_DATE()

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

CURTIME([fsp])CURRENT_TIMECURRENT_TIME([fsp])

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

NOW()CURRENT_TIMESTAMPCURRENT_TIMESTAMP([fsp])LOCALTIMELOCALTIME([fsp])LOCALTIMESTAMPLOCALTIMESTAMP([fsp])

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

DATE(expr)

日期和时间处理函数

%MOD

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

子主题

mysql> SELECT 3-5; -> -2

mysql> SELECT - 2; -> -2

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

DIV

算术处理函数

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

ABS()

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

CEIL(X)CEILING(X)

FLOOR(X)

RAND([N])

数学处理函数

数值处理函数

系统处理函数

AVG([DISTINCT] expr) [over_clause]

COUNT(expr)[over_clause]

mysql> SELECT COUNT(DISTINCT results) FROM student;

GROUP_CONCAT(expr)

JSON_ARRAYAGG(col_or_expr)[over_clause]

MAX([DISTINCT] expr) [over_clause]

MIN([DISTINCT] expr) [over_clause]

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子句之前。

创建分组

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

ROLLUP

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

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

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

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

过滤分组

分组数据

利用子查询进行过滤

作为计算字段使用子查询

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

相关子查询

子查询

创建联结

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

笛卡儿积 cartesian product

叉联结

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

内部联结等值联结

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

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

用自联结而不用子查询

自联结

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

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

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

自然联结

左外联结

右外联结

使用带聚集函数的联结

外部联结

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

性能考虑

联结表

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

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

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

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

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

组合查询和多个WHERE条件

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

创建组合查询

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

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

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

UNION规则

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

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

包含或取消重复的行

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

对组合查询结果排序

组合查询

启用全文本搜索

不要在导入数据时使用FULLTEXT

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

使用完整的Match（）说明

搜索不区分大小写

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

排序多个搜索项

进行全文本搜索

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

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

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

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

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

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

SELECT note_textFROM productnotesWHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION)

使用查询扩展

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)

要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）

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

表达式分组

另外一些内容

MySQL支持全文本搜索的另外一种形式，称为布尔方式（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语句，一次提交它们，每条语句用一个分号结束。

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

插入多行

插入某些查询结果

插入数据

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

更新表中特定的行

更新表中所有行

在UPDATE语句中使用子查询

UPDATE IGNORE customers……

IGNORE关键字

更新数据

DELETE FROM customers WHERE cust_id='10005';

从表中删除特定行

从表中删除所有行

更快的删除 TRUNCATE

删除数据

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

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

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

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

更新和删除的指导原则

更新和删除数据

表创建基础

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

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

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

使用NULL值

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

主键再介绍

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

使用AUTO_INCREMENT

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

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

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

指定默认值

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

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

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

外键不能跨引擎

引擎类型

创建表

ALTER TABLE vendorsADD 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;

重命名表

创建和操纵表

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

什么是视图

重用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条更新语句会替换原有视图。

视图的创建

利用视图简化复杂的联结

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

用视图过滤不想要的数据

使用视图与计算字段

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

分组（使用GROUP BY和HAVING）

联结

并

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

DISTINCT

导出（计算）列

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

更新视图

使用视图

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

什么是存储过程

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

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

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

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

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

为什么要使用存储过程

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

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

缺陷

执行存储过程

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个参数，font color=\"#e74f4c\

--font face=\"阿里巴巴普惠\

示例

传递给存储过程

从存储过程传出

OUT

对存储过程传入和传出

INOUT

MySQL支持的参数类型

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

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

参数的数据类型

使用参数

font face=\"苍耳与墨\

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

建立智能存储过程

为显示用来创建一个存储过程的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语句把数据实际检索出来。

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

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

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

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

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

创建游标

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

OPEN ordernumbers;

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

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

CLOSE ordernumbers;

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

打开和关闭游标

--示例1，从游标中检索单个行（第一行）CREATE PROCEDURE processorders()BEGIN--Declare local variablesDECLARE o INT;--Declare the cursorDECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;--Open the cursorOPEN ordernumbers;--Fetch用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。FETCH ordernumbers INTO O;--Close the cursorCLOSE ordernumbers;END;

--示例2，循环检索数据，从第一行到最后一行CREATE PROCEDURE processorders()BEGIN--声明局部变量DECLARE done BOOLEAN DEFAULT 0;DECLARE o INT;--定义游标DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;--font face=\"苍耳与墨\" style=\"\

--示例3CREATE PROCEDURE processorders()BEGIN--font face=\"苍耳与墨\

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

使用游标数据

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

DECLARE语句的次序

使用游标

游标

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

DELETE

INSERT

UPDATE

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

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

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

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

什么是触发器

唯一的触发器名

触发器关联的表

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

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

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

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

触发器用CREATE TRIGGER语句创建

创建触发器

DROP TRIGGER newproduct;

删除触发器

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；

对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

INSERT触发器

在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（update语句前）的值，引用一个名为NEW的虚拟表访问新更新的值。

在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值。）

OLD中的值全部都是只读的，不能更新。

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendorsFOR EACH ROW SET NEW.vend_state=Upper(NEW.vend_state);//每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper（NEW.vend_state）替换

示例：

UPDATE触发器

在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；

OLD中的值全都是只读的，不能更新；

使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说），如果由于某种原因，订单不能存档，DELETE本身将被放弃。

DELETE触发器

创建触发器可能需要特殊的安全访问权限，但是，触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行，则相关的触发器也能执行。

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

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

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

关于触发器的进一步介绍

使用触发器

BEFORE 或 AFTER?通常，将BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）

触发器

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

什么是事务处理

事务 transaction 指一组SQL语句

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

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

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

事务处理相关术语

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

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

哪些语句可以回退

使用rollback

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

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

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

使用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需要适应不同的字符集（不同的字母和字符），适应不同的排序和检索数据的方法。

为字母和符号的集合

字符集

为某个字符集成员的内部表示

编码

为规定字符如何比较的指令

校对