王权个人博客
0%

mysql必知必会

发表于 分类于 ，
本文字数： 24k 阅读时长 ≈ 21 分钟

本文将整理mysql必知必会-知识点整理。

常用函数与操作符

UUID()函数

UUID是由32位小写的16进制数字组成，如下

aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee

在mysql中UUID函数，前三组数字由时间戳生成。第四组数字暂时保持时间戳的唯一性，第五组数字是一个IEEE 802节点标点值，保证空间唯一。使用UUID()函数可以生成时间、空间都独一无二的值。

在mysql中的使用语法

1
select uuid();

结果如下

image-20220325112747319

我们可以使用替换函数把中间的-去掉，语句如下 

1
select replace(uuid(),"-","");

结果如下

image-20220325112915766

limit

SQL语句

1
2
select *from products
limit 5;

表示检索出的数据小于等于5行，而SQL语句

1
2
select *from products
limit 5,5;

指示SQL语句返回从第5行开始的5行，所以limit arg1,arg2,arg1表示从第几行开始，arg2表示返回几行。

order

order语句如下

1
2
select *from table_name
order by column_name [desc|asc]

order语句默认按升序排序，desc为升序，asc为降序，order可以对多个列进行排序。

where

where子句操作符

操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between a and b ab之间，包括ab

IN操作符

有SQL语句

1
2
3
select *from products
where vend_id in (1002,1003)
order by prod_name

此select语句检索供应商1002和1003制造的所有产品

IN操作符一般比OR操作符执行更快

NOT操作符

有SQL语句

1
2
3
select * from products
where vend_id not in (1002,1003)
order by prod_name

匹配1002,1003之外的vend_id制造的所有产品。

LIKE操作符

百分号%通配符

%表示任何字符出现的次数

有SQL语句

1
2
select *from products
where prod_name like 'jet%'

匹配产品名以jet开始的产品名。

下划线_通配符

下划线_只匹配单个字符 

1
2
select *from products
where prod_name like '_ton anvil'

匹配产品名为aton anvil ，bton anvil 诸如此类，

正则表达式

基本字符匹配

1
2
3
select *from products
where prod_name REGEXP '1000'
order by prod_name

REGEXP表示告诉mysql作为正则表达式进行处理。该SQL语句表示检索列prod_name包含文本1000的所有行。

1
2
3
select *from from produts
where prod_name REGEXP '.000'
order by prod_name

.是正则表达语句的一个特殊字符，它表示匹配任意一个字符，因此该SQL语句表示匹配，诸如01000,2000,..诸如此类的文本。

REGEXP与like的区别 
1
2
select name from test 
where name like '_w'
1
2
select  name from test
where name REGEXP '.w'

后一条语句会返回

lwq

lwq

这是因为正则表达式是在文本内匹配任意位置

前一条语句什么都不返回

因为LIke操作符位置固定，除非有数据ww或者lw诸如此类的数据。

进行OR匹配

1
2
3
select *from products
where prod_name REGEXP '1000|2000'
order by prod_name

|为正则表达式的OR操作符。它表示匹配其中之一。

image-20220326180048541

匹配几个字符之一

1
2
3
select prod_name from products
where prod_name REGEXP '[123] Ton'
order by prod_name

image-20220326180402863

用了正则表达式[123] Ton。 [123]定义一组字符，它的意思是匹配1或2或3，因此， 1 ton和2 ton都匹配且返回（没有3 ton）

匹配范围

集合可用来定义要匹配的一个或多个字符，其中匹配数字0-9可简写成[0-9]，匹配字母可简写成[a-z]

1
2
3
select prod_name from products
where prod_name REGEXP '[1-5] Ton'
order by prod_name

image-20220326180719178

匹配特殊字符

为了匹配特殊字符，必须用\\为前导。 \-表示查找-， \.表示查找. 。

1
2
3
select vend_name from vendors
where vend_name REGEXP '\\.'
order by vend_name

image-20220326180933032

匹配字符类

类 说明
[:alnum:] 任意字母和数字（同[a-zA-Z0-9]）
[:alpha:] 任意字符（同[a-zA-Z]）
[:blank:] 空格和制表（同[\\t]）
[:cntrl:] ASCII控制字符（ ASCII 0到31和127）
[:digit:] 任意数字（同[0-9]）
[:graph:] 与[:print:]相同，但不包括空格
[:lower:] 任意小写字母（同[a-z]）
[:print:] 任意可打印字符
[:punct:] 既不在[:alnum:]又不在[:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符（同[\\f\\n\\r\\t\\v]）
[:upper:] 任意大写字母（同[A-Z]）
[:xdigit:] 任意十六进制数字（同[a-fA-F0-9]）

匹配多个例子

元字符 说明
* 0个或多个匹配
+ 1个或多个匹配（等于{1,}）
? 0个或1个匹配（等于{0,1}）
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围（ m不超过255） 
1
2
3
select prod_name from products
where prod_name REGEXP '\\([0-9] sticks?\\)'
order by prod_name

image-20220326181845451

双斜杠(表示匹配左括号(,[0-9]表示匹配任意数字,sticks(s后的?使得s可选)

1
2
3
select prod_name from products
where prod_name REGEXP '[[:digit:]]{4}'
order by prod_name

image-20220326182258136

[:digit:]匹配任意数字，因而它为数字的一个集合。 {4}确切地要求它前面的字符（任意数字）出现4次，所以[[:digit:]]{4}匹配连在一起的任意4位数字。

定位符

目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要使用定位符。

定位元字符

元字符 说明
^ 文本开始
$ 文本结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾 
1
2
3
select prod_name
from products
where prod_name REGEXP '^[0-9\\.]'

image-20220326182647819

^匹配串的开始 ，^[0-9\.]只在.或任意数字为串中第一个字符时才匹配它们。

拼接字段

concat函数

1
2
3
select concat(vend_name,'(',vend_country,')')
from vendors
order by vend_name

image-20220326191058867

Concat()拼接串，即把多个串连接起来形成一个较长的串。

Concat()需要一个或多个指定的串，各个串之间用逗号分隔。

Rtrim函数

1
2
select concat(Rtrim(vend_name),'(',Rtrim(vend_country),')') from vendors
order by vend_name

RTrim()函数去掉值右边的所有空格。 LTrim()（去掉串左边的空格）以及Trim()（去掉串左右两边的空格）。

数据处理函数

文本处理函数

使用upper函数

1
2
select vend_name,upper(vend_name) as vend_name_upcase from vendors
order by vend_name

image-20220326191901387

upper函数将文本转换成大写。

常用的文本处理函数

函数 说明
left(str,length) 返回串左边的length字符
length() 返回串的长度
locate(substr,str) 返回substr在字符串str 的第一个出现的位置
locate(substr,str,pos) 返回字符串substr在字符串str，从pos处开始的第一次出现的位置，如果substr不在str中，则返回值为0
Lower(str) 将串str转换为小写
LTrim(str) 去掉串str左边的空格
Right(str,legth) 返回串右边的legth字符
RTrim(str) 去掉串str右边的空格
Soundex(str) 返回串str的SOUNDEX值
SubString(str,posi) 返回从串str的位置posi开始的子字符 串
Upper(str) 将串str转换为大写

假设customers表中有一个顾客Coyote Inc.,其联系名为Y.LEE但如果这是输入错误，此联系名实际应该是Y.Lie，怎么办?，我们将使用Soundex()函数进行搜索，它匹配所有发音类似于Y.Lie的联系名： 

1
2
select cust_name,cust_contact from customers
where Soundex(cust_contact)=Soundex('Y Lie')

image-20220326194005184

WHERE子句使用Soundex()函数来转换cust_contact列值和搜索串为它们的SOUNDEX值。因为Y.Lee和Y.Lie发音相似，所以它们的SOUNDEX值匹配，因此WHERE子句正确地过滤出了所需的数据。

日期和时间处理函数

常用日期和时间处理函数

函数 说明
AddDate() 增加一个日期（天、周等）
AddTime() 增加一个时间（时、分等）
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期，返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分

数值处理函数

常用数据处理函数

函数 说明
Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

分组数据

group by

1
2
3
select vend_id ,count(*) as num_prods
from products
group by vend_id

image-20220327091235617

上面SQL语句，近按vend_id进行分组，并求它的产品数量。

过滤分组

1
2
3
4
select cust_id,count(*) as orders
from orders
group by cust_id
having count(*)>=2

image-20220327091623570

Having与where差别：where在数据分组前过滤，having在数据分组后进行过滤 
1
2
3
4
5
select vend_id,count(*) as num_prods
from products
where prod_price>=10
group by vend_id
having count(*)>=2

image-20220327091927047

WHERE子句过滤所有prod_price至少为10的行。然后按vend_id分组数据， HAVING子句过滤计数为2或2以上的分组 。

分组与排序

order by 与group by

order by group by
排序产生的输出 分组行，但输出可能不是分组的顺序
任意列都可以使用 只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要 如果与聚集函数一起使用列（或表达式），则必须使用

我们发现用group by分组的数据是以分组顺序进行输出的，但情况并不总是这样，此外，用户也可能会要求以不同于分组的顺序排序。仅因为你以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。应该提供明确的ORDER BY子句。

一般在使用GROUP BY子句时，应该也给 出ORDER BY子句。这是保证数据正确排序的唯一方法。千万 不要仅依赖GROUP BY排序数据。

子查询

假如需要列出订购物品TNT2的所有客户，应该怎样检索？

(1) 检索包含物品TNT2的所有订单的编号。
(2) 检索具有前一步骤列出的订单编号的所有客户的ID。
(3) 检索前一步骤返回的所有客户ID的客户信息。 

1
2
3
4
5
6
7
select cust_name,cust_contact　//根据客户ID检索出客户信息
from customers
where cust_id in (
select cust_id from orders //检索订购TNT２的客户ID
where order_num in(
select order_num from orderitems //检索物品ＴＮＴ２的所有客户
where prod_id='TNT2'))

假如需要显示customers表中每个客户的订单总数。订单与相应的客户ID存储在orders表中。

为了执行这个操作，遵循下面的步骤。
(1) 从customers表中检索客户列表。
(2) 对于检索出的每个客户，统计其在orders表中的订单数目。 

1
2
3
4
5
select cust_name,cust_state,(
select count(*) from orders
where orders.cust_id=customers.cust_id) as orders
from customers
order by cust_name

联结表

1
2
3
4
select vend_name,prod_name,prod_price
from vendors,products
where vendors.vend_id=products.vend_id
order by vend_name,prod_name

该语句为两个表的联结。

1
2
3
select vend_name,prod_name,prod_price
from vendors,products
order by vend_name,prod_name

该语句因为没有过滤条件，所以会执行笛卡尔积。

1
2
3
4
5
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 =2005

image-20220327103926728

使用and操作符对多表联合查询进行条件过滤。

AS的优势 1)缩短SQL语句 2)允许在单条select语句中多次使用相同的表

自连接

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。 

1
2
3
4
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'

该语句为表自身的连接。

image-20220327104725852

内连接

表A
a_id a_name a_part
1 老潘 技术部
2 老王 秘书部
3 老张 设计部
4 老李 运营部
表B
b_id b_name b_part
2 老王 秘书部
3 老张 设计部
5 老刘 人事部
6 老黄 生产部

对表A与表B进行字段Id内连接就有

表C
a_id a_name a_part b_id b_name b_part
2 老王 秘书部 2 老王 秘书部
3 老张 设计部 3 老张 设计部

sql语句有

1
2
select *from 
 A inner join B on A.a_id=B.b_id

左连接

我们表A和表B，我们对表A进行左连接于表B，则有

表D
a_id a_name a_part b_id b_name b_part
2 老王 秘书部 2 老王 秘书部
3 老张 设计部 3 老张 设计部
1 老潘 总裁部 null null null
4 老李 运营部 null null null

有SQL语句

1
select *from A left join B on A.a_id=B.b_id

左(外)连接，左表A的记录将会全部表示出来，而右B只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

右连接

我们表A和表B，我们对表A进行右连接于表B，则有

表E
a_id a_name a_part b_id b_name b_par
2 老王 秘书部 2 老王 秘书部
3 老张 设计部 3 老张 设计部
null null null 5 老刘 人事部
null null null 6 老黄 生产部

自然连接

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列)。

自然联结排除多次出现，使每个列只返回一次 ，也就是说，自然连接是去掉重复列的等值连接.
关系R
A B C
a1 b1 5
a1 b1 6
a2 b2 8
a2 b2 12
关系S
B D
b1 3
b2 7
b3 10
b3 2
b4 5

下面自然连接关系R$\infty$S

R$\infty$S

A B C D
a1 b1 5 3
a1 b1 6 3
a2 b2 8 7
a2 b2 12 7

有SQL语法

1
2
3
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'

组合查询

union

假如需要价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品（不考虑价格）。 

1
2
3
4
5
6
7
select vend_id,prod_id,prod_price
from products
where prod_price <=5
union 
select vend_id,prod_id,prod_price
from products
where vend_id in (1001,1002)

image-20220327155055270

我们这里也有使用OR来进行连接，而产生的查询语句

1
2
3
select vend_id,prod_id,prod_price
from products
where prod_price <=5 or vend_id in (1001,1002)
注意

我们需要对比是使用union与or、and等操作符进行分析，看再场景下哪个操作更加高效。

UNION中的每个查询必须包含相同的列、表达式或聚集函数 。

union all

union操作符会默认，取消重复的行，而使用union all 都返回所有匹配的行，包括重复的行。

全文本搜索

全文本搜索相较于like关键字与正则表达式有都以下三点优势

1)性能更加高效

2)能明确控制匹配什么词，不匹配什么词

3)智能化结果。

启用全文本搜索支持

全文本搜索支持需要在创建表时启用，有SQL语句

1
2
3
4
5
6
7
8
9
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)//note_text列启用全文本搜索支持
)

为了进行全文本搜索，MySQL根据子句FULLTEXT(note_text)的指示对它进行索引。这里的FULLTEXT索引单个列，如果需要也可以指定多个列。

在定义之后， MySQL自动维护该索引。在增加、更新或删除行时，索引随之自动更新 。

进行全文本搜索

在索引之后，使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列， Against()指定要使用的搜索表达式 。

1
2
3
select note_text
from productnotes
where Match(note_text) Against('rabbit')

image-20220327163015273

此SELECT语句检索单个列note_text。由于WHERE子句，一个全文本搜索被执行。 Match(note_text)指示MySQL针对指定的列进行搜索， Against(‘rabbit’)指定词rabbit作为搜索文本。由于有两行包含词rabbit，这两个行被返回。 

1
2
3
select note_text
from productnotes
where note_text like '%rabbit'

image-20220327163409474

注意

上两条SQL语句检索的内容相同，但是其有本质的区别，

后者（使用LIKE）以不特别有用的顺序返回数据。前者（使用全文本搜索）返回以文本匹配 的良好程度排序的数据。两个行都包含词rabbit，但包含词rabbit作为第3个词的行的等级比作为第20个词的行高。 全文本搜索的一
个重要部分就是对结果排序。具有较高等级的行先返回（因为这些行很可能是你真正想要的行）。

使用查询扩展

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

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

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

2)其次，MySql检查这些匹配行并选择所有有用的词。

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

下面语句，首先进行一个简单的全文本搜索，没有查询扩展。

1
2
3
select note_text
from productnotes
where match(note_text) Against('anvils')

image-20220327164606338

只有一行包含词anvils，因此只返回一行。

使用查询扩展的SQL语句

1
2
3
select note_text
from productnotes
where match(note_text) against('anvils' with query expension)

image-20220327164817158

这次返回了7行。第一行包含词anvils，因此等级最高。第二行与anvils无关，但因为它包含第一行中的两个词（ customer和recommend），所以也被检索出来。第3行也包含这两个相同的词，但它们在文本中的位置更靠后且分开得更远，因此也包含这一行，但等级为第三。第三行确实也没有涉及anvils（按它们的产品名。

表中的行越多（这些行中的文本就越多），使用查询扩展返回的结果越好。

布尔文本搜索

以布尔方式，可以提供关于如下内容

1、要匹配的词；
2、要排斥的词（如果某行包含这个词，则不返回该行，即使它包含其他指定的词也是如此）；
3、排列提示（指定某些词比其他词更重要，更重要的词等级更高）；
4、表达式分组；
5、另外一些内容。

布尔文本搜索，即 使 没 有 定 义 FULLTEXT索引，也可以使用它。 
1
2
3
select note_text
from productnotes
where match(note_text) against('heavy' in boolean mode )

image-20220327170414219

此全文本搜索检索包含词heavy的所有行（有两行）。其中使用了关键字IN BOOLEAN MODE。

为了匹配包含heavy但不包含任意以rope开始的词的行，可以使用以下查询 

1
2
3
select note_text 
from productnotes
where match(note_text) against('heavy -rope*' in boolean mode)

image-20220327181137210

全文本布尔操作符
布尔操作符 说明
+ 包含，词必须存在
- 排除，词必须不出现
> 包含，而且增加等级值
< 包含，且减少等级值
() 把词组成子表达式（允许这些子表达式作为一个组被包含、 排除、排列等）
~ 取消一个词的排序值
* 词尾的通配符
“ “ 定义一个短语（与单个词的列表不一样，它匹配整个短语以 便包含或排除这个短语） 
1
2
3
select note_text
from productnotes
where match(note_text) against('+rabbit +bait' in boolean mode)

搜索匹配包含词rabbit和bait的行。 

1
2
3
select note_text 
from productnotes
where match(note_text) against('rabbit bait' in boolean mode)

搜索匹配包含rabbit和bait中的至少一个词的行 。

1
2
3
select note_text
from productnotes
where match(note_text) against('"rabbit bait"' in boolean mode)

搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait。 

1
2
3
select note_text 
from productnotes
where match(note_text) against('>rabbit <carrot' in boolean mode)

匹配rabbit和carrot，增加前者的等级，降低后者的等级。

1
2
3
select note_text
from productnotes
where match(note_text) against('+safe +(<combination)' in boolean mode)

搜索匹配词safe和combination，降低后者的等级 。

插入数据

省略列

省略列必须满足以下某个条件

1)该列定义为允许NULL值(无值或空值)

2)在表定义中给出默认值，表示如果不给出值，则将使用默认值。

提高整体性能

数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MySQL的任务。 INSERT操作可能很耗时（特别是有很多索引需要更新时），而且它可能降低等待处理的SELECT语句的性能。

如果数据检索是最重要的，则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，有SQL代码

1
insert LOW_PRIORITY INTO

该代码表示降低插入数据的级别，update与delete语句也适用。

若每条的insert语句中的列名(和次序)相同，可如下组合各语句

1
insert into customers(cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country) values('Pep E.LawPew','100 Main Street','Los Angeles','CA','90046','USA'),('M. Martian','42 Galaxy Way','New York','NY','112213','USA')

更新与删除数据

IGNORE关键字

如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消
（错误发生前更新的所有行被恢复到它们原来的值）。为了即使是发生错误，也继续进行更新，可使用IGNORE关键字。

1
update ignore customers ....

为了删除某列的值，可以设置它为NULL(若表定义允许设置为NULL值)

1
2
3
update customers
set cust_email=NULL
where cust_id=1005

其中NULL用来去除cust_email列中的值。

删除表的内容而不是表

DELETE语句从表中删除行，甚至是删除表中所有行。但是， DELETE不删除表本身。

更快的删除

如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快（ TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

创建与操纵表

理解NULL

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

覆盖Auto_increment

如果一个列被指定为AUTO_INCREMENT，则它需要使用特殊的值吗？

可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。

确定auto_increment值

让MySQL生成（通过自动增量）主键的一个缺点是你不知道这些值都是谁 。

考虑这个场景：你正在增加一个新订单。这要求在orders表中创建一行， 然后在orderitms表中对订购的每项物品创建一行。 order_num在orderitems表中与订单细节一起存储。这就是为什么orders表和orderitems表为相互关联的表的原因。这显然要求你在插入orders行之后，插入orderitems行之前知道生成的order_num。

可使用last_insert_id()函数获得这个值。

1
select last_insert_id()

此语句返回最后一个AUTO_INCREMENT值。

更新表

1
2
alter table vendors
add vend_phone char(20)

这条语句给vendors表增加一个名为vend_phone的列，必须明确其数据类型。 

1
2
alter table vendors
drop column vend_phone

删除刚刚添加的列。

1
2
3
alter table orderitems
add constraint fk_orderitems_orders
foreign key (order_num) references orders(order_num)

添加外键。

删除表

1
drop table customers2

删除表customers_2表

重命名表

1
rename table customers2 to customers

重命名表

1
2
3
rename table backup_customers to customers,
			 backup_vendors to vendors,
			 backup_products to products

多个表重命名。

视图

注意

1)视图用create view 语句来创建

2)使用show create view viewname，来查看创建视图

3)用drop删除视图，语法为drop view viewname

4)更新视图时，可以先使用drop再使用create，也可以直接使用create or replace view ，如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

创建视图语法

1
2
3
4
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=order.order_num

这条语句创建一个名为productcustomers的视图， 它联结三个表，以返回已订购了任意产品的所有客户的列表。

更新视图

通常，视图是可更新的（即，可以对它们使用INSERT、 UPDATE和DELETE）。更新一个视图将更新其基表，如果你对视图增加或删除行，实际上是对其基表增加或删除行。

但是，并非所有视图都是可更新的。基本上可以说，如果MySQL不能正确地确定被更新的基数据，则不允许更新（包括插入和删除）。这实际上意味着，如果视图定义中有以下操作，则不能进行视图的更新：

1)分组（使用GROUP BY和HAVING）；
2)联结；
3)子查询；
4)并；
5)聚集函数（ Min()、 Count()、 Sum()等）；

6)DISTINCT；
7)导出（计算）列。

存储过程

执行存储过程

1
call productpricing(@pricelow,@pricehigh,@priceaverage)

执行名为productpricing的存储过程。

创建存储过程

1
2
3
4
5
create procedure productpricing()
begin
select avg(prod_price) as priceaverage
from products;
end;

一个返回产品平均价格的存储过程。 

1
call productpricing();

image-20220328120319499

删除存储过程

1
drop procedure productpricing;

这条语句删除刚创建的存储过程。请注意没有使用后面的()，只给出存储过程名。

使用参数

1
2
3
4
5
6
7
8
9
10
11
12
13
create procedure productpricing(
out p1 decimal(8,2),
out ph decimal(8,2),
out pa decimal(8,2)
)
begin
 select min(prod_price) into p1
 from products;
 select max(prod_price) into ph
 from products;
 select avg(prod_price) into pa
 from products;
 end;

此存储过程接受3个参数： pl存储产品最低价格， ph存储产品最高价格， pa存储产品平均价格。每个参数必须具有指定的类型，这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者）。 MySQL支持IN（传递给存储过程）、 OUT（从存储过程传出，如这里所用）和INOUT（对存储过程传入和传出）类型的参数。

考虑这个场景。你需要获得与以前一样的订单合计，但需要对合计增加营业税，不过只针对某些顾客（或许是你所在州中那些顾客）。那么，你需要做下面几件事情：

  1. 获得合计
  2. 把营业税有条件地添加到合计
  3. 返回合计
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
create procedure ordertotal(
in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)
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
--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;

检查存储过程

1
show create procedure ordertotal;

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

为了获得包括何时、由谁创建等详细信息的存储过程列表，使用show procedure status。

限制过程状态结果

show procedure status列出所有存储过程，为限制其输出，可使用like指定一个过滤模式，例如：

1
show procedure status like 'ordertotal'

游标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create procedure processorders()
BEGIN
--declare local variables
DECLARE o int;
DECLARE done boolean default 0;

--declare ordernumbers 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;
select o;
--end of loop 
until done end repeat;
--close the cursor
close ordernumbers;
end;

image-20220328165207698

例子中的FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTILdone END REPEAT;规定）。

这条语句定义了一个CONTINUE HANDLER，它是在条件出现时被执行的代码。 这里， 它指出当SQLSTATE ‘02000’出现时， SET done=1。 SQLSTATE’02000’是一个未找到条件， 当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件 。

触发器

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

  1. delete
  2. insert
  3. update

创建触发器

1
2
create trigger newproduct after insert on products
for each row select 'product added';

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。这个触发器还指定FOREACH ROW，因此代码对每个插入行执行。在这个例子中，文本Productadded将对每个插入的行显示一次。

触发器仅支持表

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

每个表最多支持6个触发器（每条INSERT、 UPDATE和DELETE的之前和之后）。

触发器失败

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

删除触发器

1
drop trigger newproduct;

触发器不能更新或覆盖。为了修改一个触发器，必须先删除它，然后再重新创建。

insert触发器

INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：

  1. 在insert触发器代码内，可引用一个名为new的虚拟表，访问被 插入的行
  2. 在before insert触发器中，new中的值也可以被更新(允许更改被插入的值)；
  3. 对于auto_increment列，new在insert执行之前包含0,在insert执行之后包含新的自动生成值。
1
2
3
declare t decimal(8,2);
create trigger neworder after insert on orders
for each row set @t=NEW.order_num;

此代码创建一个名为neworder的触发器，它按照AFTER INSERTON orders执行。在插入一个新订单到orders表时， MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并赋值给t。此触发器必须按照AFTER INSERT执行，因为在BEFOREINSERT语句执行之前，新order_num还没有生成。 

1
2
insert into orders(order_date,cust_id) values(Now(),10001);
select @t;

进行插入而后触发触发器，我们再通过select把值给打印出来。

delete触发器

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点：

  1. 在delete触发器代码内你可以引用一个名为OLD的虚拟表，访问被删除的行；
  2. OLD中的值全都是只读的，不能更新。
1
2
3
4
5
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的存档表中。

update触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。需要知道以下几点：

  1. 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（ UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值。
  2. 在BEFORE UPDATE触发器中， NEW中的值可能也被更新。
  3. OLD中的值全都是只读的，不能更新 。
1
2
3
create trigger updatevendor before update on vendors
for each row 
set NEW.vend_state=Upper(NEW.vend_state);

管理事务处理

  1. 事务指一组SQL语句。
  2. 回退指撤销指定SQL语句的过程。
  3. 提交指将未存储的SQL语句结果写入数据库表。
  4. 保留点指事务处理中设置的临时占位符，可以对它发布回退。

控制事务处理

1
start transaction

用来标识事务的开始。

使用ROLLBACK

1
2
3
4
5
6
select *from ordertotals;
start transaction;
delete from ordertotals;
select*from ordertotals;
rollback;
select *from ordertotals;

删除ordertotals表中的内容，然后进行事务回退。

使用commit

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

1
2
3
4
5
6
start transaction;
delete from orderitems
where order_num=20010;
delete from orders
where order_num=20010;
commit;

在这个例子中，从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderItems，所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用，但第二条失败，则DELETE不会提交（实际上，它是被自动撤销的）。

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分提交或回退。

例如，前面描述的添加订单的过程为一个事务处理。如果发生错误，只需要返回到添加orders行之前即可，不需要回退customers表（如果存在的话）。

为了支持回退部分事务处理，必须能在事务处理块中合适的位置放
置占位符。这样，如果需要回退，可以回退到某个占位符。

这些占位符称为保留点。为了创建占位符，可如下使用SAVEPOINT
语句： 

1
savepoint delete1;

每个保留点都取标识它的唯一名字，以便在回退时， MySQL知道要
回退到何处。为了回退到本例给出的保留点，可如下进行 

1
rollback to delete1;
1
2
3
4
5
6
7
8
9
10
-- start transaction
start transaction;
-- save point
SAVEPOINT point1;
delete from orders
where order_num=20007;
-- rollback
rollback to point1;
commit;
select *from orders;

保留点与回滚到点须在事务开始与提交之间。

保留点越多越好

保留点越多，你就越能按自己的意愿灵活地进行回退。

释放保留点

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

更改默认的提交行为

默认的MySQL行为是自动提交所有更改。换句话说，任何时候你执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。为指示MySQL不自动提交更改，需要使用以下句： 

1
set autocommit=0;

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

标志为连接专用

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

全球化与本地化

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

校对为什么重要

排序英文正文很容易，对吗？或许不。考虑词APE、 apex和Apple。它们处于正确的排序顺序吗？这有赖于你是否想区分大小写。使用区分大小写的校对顺序，这些词有一种排序方式，使用不区分大小写的校对顺序有另外一种排序方式。这不仅影响排序（如用ORDER BY排序数据），还影响搜索。

使用字符集与校对顺序

1
show character set;

这条语句显示所有可用的字符集以及每个字符集的描述和默认校对。 

1
show collation;

此语句显示所有可用的校对，以及它们适用的字符集。

在创建数据库时，指定默认的字符集和校对。为了确定所用的字符集和校对，可以使用以下语句： 

1
2
show variables like 'character%';
show variables like 'collation%';
1
2
3
4
5
create table mytable
(
column1 int,
column2 varchar(10)
)default character set hebrew collate hebrew_general_ci;

此语句创建一个包含两列的表，并且指定一个字符集和一个校对顺序。

1
2
3
4
5
6
7
create table mytable
(
column1 int,
column2 varchar(10),
column3 varchar(10) character set lain1 collate
hebrew_general_ci;
)

这里对整个表以及一个特定的列指定了CHARACTER SET和COLLATE。

如果你需要用与创建表时不同的校对顺序排序特定的SELECT语句，可以在SELECT语句自身中进行 

1
2
select *from customers
order by lastname,firstname collate laint1_general_cs;

安全管理

查看用户表

1
2
use mysql;
select user from user;

该语句会列出所有mysql中的所有用户。

创建用户账号

1
create user ben identified by '123456'

CREATE USER创建一个新用户账号。

重命名账号

1
rename user ben to bforta;

删除账号

1
drop user bforta;

设置访问权限

1
show grants for bforta;

image-20220329163543533

输出结果显示用户bforta有一个权限USAGE ON .。 USAGE表示根本没有权限。

1
grant select on crashcourse.* to bforta;

此GRANT允许用户在crashcourse.*（ crashcourse数据库的所有表）上使用SELECT。 通过只授予SELECT访问权限，用户bforta对crashcourse数据库中的所有数据具有只读访问权限。

show grants反映这个更改。

1
show grants for bforta;

image-20220329163636393

每个GRANT添加（或更新）用户的一个权限。 

1
revoke select on crashcourse.* from bforta;

这条REVOKE语句取消刚赋予用户bforta的SELECT访问权限。 被撤销的访问权限必须存在，否则会出错 。

grant和revoke可在几个层次上控制访问权限
  1. 整个服务器，使用grant all 和revoke all；
  2. 整个数据库，使用on database.*；
  3. 特定的表，使用on database.table；
  4. 特定的列；
  5. 特定的存储过程；
权限
权限 说明
ALL 除GRANT OPTION外的所有权限
ALTER 使用ALTER TABLE
ALTER ROUTINE 使用ALTER PROCEDURE和DROP PROCEDURE
CREATE 使用CREATE TABLE
CREATE ROUTINE 使用CREATE PROCEDURE
CREATE TEMPORARY TABLES 使用CREATE TEMPORARY TABLE
CREATE USER 使用CREATE USER、 DROP USER、 RENAME USER和REVOKE ALL PRIVILEGES
CREATE VIEW 使用CREATE VIEW
DELETE 使用DELETE
DROP 使用DROP TABLE
EXECUTE 使用CALL和存储过程
FILE 使用SELECT INTO OUTFILE和LOAD DATA INFILE
GRANT OPTION 使用GRANT和REVOKE
INDEX 使用CREATE INDEX和DROP INDEX
INSERT 使用INSERT
LOCK TABLES 使用LOCK TABLES
PROCESS 使用SHOW FULL PROCESSLIST
RELOAD 使用FLUSH
REPLICATION CLIENT 服务器位置的访问
REPLICATION SLAVE 由复制从属使用
SELECT 使用SELECT
SHOW DATABASES 使用SHOW DATABASES
SHOW VIEW 使用SHOW CREATE VIEW
SHUTDOWN 使用mysqladmin shutdown（用来关闭MySQL）
SUPER 使用CHANGE MASTER、 KILL、 LOGS、 PURGE、 MASTER 和SET GLOBAL。还允许mysqladmin调试登录
UPDATE 使用UPDATE
USAGE 无访问权限

更改口令

1
set password for bforta=password('123456');

SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。

SET PASSWORD还可以用来设置你自己的口令： 

1
set password=Password('awertfg');

在不指定用户名时， SET PASSWORD更新当前登录用户的口令。

数据库维护

备份数据

像所有数据一样， MySQL的数据也必须经常备份。由于MySQL数据库是基于磁盘的文件，普通的备份系统和例程就能备份MySQL的数据。但是，由于这些文件总是处于打开和使用状态，普通的文件副本备份不一定总是有效。

下面列出这个问题的可能解决方案。

  1. 使用命令行实用程序mysqldump转储所有数据库内容到某个外部
    文件。在进行常规备份前这个实用程序应该正常运行，以便能正
    确地备份转储文件。
  2. 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据
    （并非所有数据库引擎都支持这个实用程序)。
  3. 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所
    有数据到某个外部文件。这两条语句都接受将要创建的系统文件
    名，此系统文件必须不存在，否则会出错。数据可以用RESTORE
    TABLE来复原
首先刷新未写数据

为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。

进行数据库维护

为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句。

  1. ANALYZE TABLE，用来检查表键是否正确。 ANALYZE TABLE返回如下所示的状态信息： 

    1
    analyze table orders;

    image-20220329171225225

  2. CHECK TABLE用来针对许多问题对表进行检查。 在MyISAM表上还对索引进行检查。 CHECK TABLE支持一系列的用于MyISAM表的方式。CHANGED检查自最后一次检查以来改动过的表。 EXTENDED执行最彻底的检查， FAST只检查未正常关闭的表， MEDIUM检查所有被删除的链接并进行键检验， QUICK只进行快速扫描。如下所示， CHECKTABLE发现和修复问题：

    1
    check table orders,orderitems;

    image-20220329171338109

  3. 如果MyISAM表访问产生不正确和不一致的结果，可能需要用
    REPAIR TABLE来修复相应的表。这条语句不应该经常使用，如果
    需要经常使用，可能会有更大的问题要解决。

  4. 如果从一个表中删除大量数据，应该使用OPTIMIZE TABLE来收回所用的空间，从而优化表的性能。

诊断启动问题

服务器启动问题通常在对MySQL配置或服务器本身进行更改时出
现。 MySQL在这个问题发生时报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能看不到。

在排除系统启动问题时，首先应该尽量用手动启动服务器。 MySQL
服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld命令行选项：

  1. —help显示帮助—一个选项列表；
  2. —safe-mode装载减去某些最佳配置的服务器；
  3. —verbose显示全文本消息（为获得更详细的帮助消息与—help联合使用）；
  4. —version显示版本信息然后退出

查看日志文件

MySQL维护管理员依赖的一系列日志文件。主要的日志文件有以下
几种。

  1. 错误日志。它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用—log-error命令行选项更改。
  2. 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用—log命令行选项更改 。
  3. 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用—log-bin命令行选项更改。注意， 这个日志文件是MySQL5中添加的，以前的MySQL版本中使用的是更新日志。
  4. 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log ， 位 于 data 目 录 中 。 此 名 字 可 以 用—log-slow-queries命令行选项更改。

在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

改善性能

  1. MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOWSTATUS;。）
  2. MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用 KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
  3. 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。
  4. 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT索引），然后在导入完成后再重建它们。
  5. 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，你能看到极大的性能改进。
  6. LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。