12、MySQL必知必会

目录[-]

MYSQL必知必会（Teach Yourself SQL in 10 Minutes）

https://cdn.mysql.com//Downloads/MySQLInstaller/mysql-installer-community-5.7.25.0.msi Mysql安装包

创建第6章节开始起的

第一章了解数据库

1.1.1 什么是数据库（database）

数据库是一个以某种有组织的方式存储的数据集合，使用它的是数据库软件DBMS（数据库管理系统）。

Create Datase lesson;

1.1.2 表（table）

比如数据库像是一个抽屉，表就类似于一个个不同标签的文件夹，而文件夹里可以存储数据，表是某种特定类型结构化清单

而一张表，应该存储一类数据，比如订单信息、账号信息、商品信息就应该分开三个表记录。

数据库中，每张表的名字是唯一的，在同一个数据库中不能出现重名，但不同数据库却可以。

1.1.3 列和数据类型（column）

表由列组成，所有的表都是由列组成，列是竖着显示的，如姓名、分数、科目这样的形式。

%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;%SYSTEMROOT%\System32\WindowsPowerShell\v1.0\;C:/mysql/bin;C:\Users\Administrator\AppData\Local\Programs\Python\Python39\Scripts;C:\Users\Administrator\AppData\Local\Programs\Python\Python39

1.1.4 行（row）

例如，列记录了每个客人的各种属性，而行就是展示单独一个客人的全部属性。

1.1.5 主键（primary key）

表中，每一行都应该有一个可以唯一标识自己的一列（或一组列），一个顾客可以通过编号查找，订单表可以通过订单编号查找，以此类推。

任意两行都不可以存在相同的主键
每个行都必须有一个主键值（主键不允许出现NULL值）通常使用id代表主键

主键的好习惯

不更新主键的值
不重用主键列的值
不在主键列使用可能更改的值（比如不能用姓名来做主键，姓名有更改的风险）

1.2 什么是SQL（Structured Query Language）

SQL是结构化查询语言的缩写，专门用来和数据库通信的语言。

SQL有以下优点

SQL不是某个特定数据库的专用语言，几乎所有DBMS都支持SQL。
SQL语法比较简单，语句都是由英语单词组成。
SQL搭配其语言元素，可以处理非常复杂和高级的数据库操作。

另一个好习惯，大写显示MySQL关键词，小写显示列、名、判断语句等

第三章使用MySQL

3.1 连接

为了连接MySQL，需要以下信息：

主机名（计算机名）——如果连接到本地MySQL，为localhost
端口（如果不是默认3306，需手动填写）
一个合法的用户名
用户口令（默认账号是root，密码也是root，拥有全部权限）

3.2 选择数据库

在刚打开MySQL数据库时，没有任何数据库打开供人使用，需选择一个数据库，使用USE关键字。

例如，有一个数据库liang，使用时，输入USE liang(表名)

3.3 了解数据库和表

数据库、表、列、用户、权限等信息被存储在数据库和表中，使用SHOW命令来显示这些信息，如SHOW DATABASES;

而显示这个数据库下，有哪些表，使用SHOW TABLES;

还可以显示表中的列名，SHOW columns FROM student(表名);

第四章检索数据

4.1 SELECT语句

最简单的就是查询语句，比如student表。

公式：SELECT 列 FROM 表——可单独输入一个/多个列名，或是输入通配符*表示搜索全部的列

参考：SELECT name,id FROM student

4.5 检索不同的行

公式：SELECT DISTINCT 列 FROM 表

SELECT DISTINCT name FROM student

如果筛选出来的结果（行）有相同的，需要distinct关键字来区分重复项（但只能筛选出一部分值）

4.6 限制结果（limit）

公式：SELECT 列 FROM 表 LIMIT 数字

SELECT*FROM student LIMIT 3

SELECT*FROM student LIMIT 5,5

可以控制返回值有几行，比如只想看到前几行数据，默认是写的行数，两个值如5,5 —表示从第5行往下再数5行。

4.7 使用完全限定的表名

有一些情形需要完全限定表名，这样会让语句可读性更高，比如：

SELECT student.name FROM student

第五章排序检索数据

5.1 排序数据（order by）

公式：SELECT 列 FROM 表 order by 列 asc（desc）——默认升序排列asc

SELECT*FROM student ORDER BY score DESC（根据学生分数，降序排列）

SELECT*FROM student ORDER BY score ASC LIMIT 1(找出分数最低的那个人)

多个条件排序，只不过order by后面可以多个列，比如先根据价格，再根据姓名做排序。

第六章过滤数据

6.1 使用WHERE语句

where条件判定：

SELECT*FROM student WHERE score>=90

SELECT*FROM student WHERE name="张三"

操作符between

SELECT*FROM student WHERE score between 80 AND 90

操作符OR和IN

SELECT*FROM student WHERE score>90 OR score<60

——OR操作符，匹配两个条件中的任意一个

SELECT*FROM student WHERE score IN(80,90) ORDER BY id

——IN操作符，范围内每个条件都可以匹配，IN 和OR完成的筛选结果一样，但是使用IN有的时候更清晰。

空值检查(NULL)

SELECT prod_name FROM products WHERE prod_price IS NULL;

第七章数据过滤

7.1 组合WHERE字句

上一章的子句只是单个字段查询，下面进行组合查询，使用OR、AND等方式。

7.1.1 AND操作符

SELECT*FROM products WHERE prod_id="TNT3" AND vend_id=3;

只有同时符合，才可以查询出来；每添加一个新的筛选条件，就多加一个AND

7.1.2 OR操作符

SELECT*FROM products WHERE prod_id="TNT3" OR vend_id=4;

只要其中一个筛选项满足，就可以显示查询结果

7.1.3 计算次序

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

SELECT*FROM products WHERE (prod_id="TNT3" OR vend_id=4) AND prod_price>5;

使用圆括号进行分隔，优先执行括号内的语句，后执行括号外的；否则优先执行AND，其次OR

7.2 IN操作符

SELECT vend_id,prod_name FROM products WHERE vend_id IN(1,2,3,4)

IN里的合法值，由逗号分隔，IN的好处，可以包含其他SELECT语句，速度比OR快，且更加直观

7.3 NOT操作符

否定它之后的所有条件关键字，在与IN同时使用时，效果很好

第八章用通配符进行过滤

8.1 LIKE操作符和通配符

下划线：_表示任意字符的一次匹配

下划线_通配符(匹配单个字符):

SELECT*FROM student WHERE name LIKE "张_" ——第一个字匹配张，第二个字任意

百分号：%表示任意字符的任意次数，但不能匹配NULL

百分比%通配符(匹配全部字符):

SELECT*FROM student WHERE name LIKE "%%" ——筛选出所有内容

SELECT*FROM student WHERE name LIKE "%辽" ——前面匹配任意字符，最后一个字匹配辽

SELECT*FROM student WHERE name LIKE "张%" ——第一个字匹配张，后面匹配任意字符

第九章正则表达式进行搜索（P62）

9.1 正则表达式介绍

正则表达式是匹配一段字符串中特殊的内容，比如一段文字中提取电话号码，找到重复的单词，替换URL为HTML链接等等

SELECT*FROM zhengze WHERE description REGEXP '7983'

——REGEXP，包含的意思

LIKE和REGEXP的区别，LIKE是匹配整个列，但REGEXP匹配的是列值，后面会用^等关键字。另外LIKE是要全部匹配，REGEXP匹配一部分即可。

SELECT*FROM zhengze WHERE description REGEXP '群众|作家|党员'

——类似关键字OR

SELECT*FROM zhengze WHERE description REGEXP '[群众作家党员]'

——中括号内进行匹配

SELECT*FROM zhengze WHERE description REGEXP '[0-9a-z]'

——匹配包含数字英文的内容

SELECT*FROM zhengze WHERE description REGEXP ' \\.'

——双斜杠为前置，匹配特殊字符，如果要匹配反斜杠\，那就需要写三个\\\

匹配一个手机号码：

1[0-9]{10} ——首数字1，中间任意数字，除了1外有10位长度，加起来11位

匹配QQ号码：

[1-9]{4,10} ——首数字不为0，总长度5-11（很奇怪，匹配到的值超过这个数目）

匹配A类IP地址：

[1-9].[0-9].[0-9].[0-9] ——首数字不为0，每个之间句号分隔，总共4个字节

匹配多个实例

定位元字符

SELECT*FROM zhengze WHERE description REGEXP '^[是].*[员]'

——^文本的开始，.*匹配是和员两个字中间的任意多个字符，原句子：是一位演员

第10章创建计算字段（P73）

10.1 计算字段

如果想在一个字段既显示公司名、也显示地址，但这两个通常在不同的列
城市、洲和邮政编码存储在不同的列，但邮件需要把它们组合起来检索
列数据是大小写混合的，但报表程序需要把所有数据都按照大写显示
物品订单表存储价格和数量，打印发票时，需计算物品的总价格/平均价格或其他计算

10.2 拼接字段

将两个列拼接起来，可使用Concat(str1,str2)函数来拼接

SELECT CONCAT(name,country) FROM vendors ——直接拼接即可

SELECT CONCAT(name,' ',course) FROM student ——可以用字符做分隔，比如这里用了空格分隔开两个合并的字符

10.3 执行算数计算

SELECT prod_id,quantity,item_price,quantity*item_price AS price_all FROM orders

——筛选出的结果，price_all这个字段显示4个字段，第4个就是乘积

第11章使用数据处理函数(P80)

11.1 函数

用于处理文本串(如删除或填充值，转换值为大写或小写)。
用于在数值数据上进行算术操作(如返回绝对值，进行代数运算的数值函数)。
用户处理日期和时间值，并从这些值中提取特定成分(如两个日期之差，检查日期有效性等)的日期和时间函数。
返回DBMS正使用的特殊信息(如返回用户登录信息，检查版本细节)的系统函数。

11.2.1 文本处理函数

UPPER(str)——将小写字母变为大写字母

LOWER(str)——转为小写

LENGTH(str)——返回串的长度

LOCATE(substr,str)——返回子串的位置，substr是子串，str是母串

RTRIM(str)——去掉字符串右边的空格

LTRIM(str)——去掉字符串左边的空格

11.2.2 日期处理函数

ADDDATE(expr,day)

——增加一个日期(天)，如select AddDate('2020-04-08',3)

ADDTIME(expr1,expr2)

——expr1是时间表达式，也可以像上面那样，但加的是秒，不会自己换算

CURDATE()

——返回当前日期

CURTIME()

——返回当前时间

DATE(expr)

——返回日期（不显示具体时间）

DATEDIFF(expr1,expr2)

——返回两个日期之差，如select DATEDIFF('2020-04-08','2020-04-09')

DATE_ADD((date,INTERVAL expr unit))

——日期运算函数，如select DATE_ADD('2020-04-04 01:00:03',INTERVAL 3 HOUR)

DATE_FORMAT(date,format)

——返回一格式化的日期或时间串，如select DATE_FORMAT('2020-04-04 01:00:03','%W %M %Y') %W返回星期几如Monday，%M返回月份如April，%Y返回年如2020

Day(date)

——返回一个日期的天数，如select DAY('2020-04-30')，返回30（天），同理Hour、Month、Minute、Now、Second、Time、Year是同样的原理

11.2.3 数值处理函数

主要用于代数、三角或几何运算，因此使用不频繁

ABS(x)——返回绝对值，如select Abs(-30)

COS(x)——返回角度余弦，如select Cos(1)，同理Sin和Tan

EXP(x)——返回指数值

MOD(x,y)——返回除操作的余数

PI()——返回3.141593

RAND()——返回1以下的随机小数，与order by共同使用

SQRT(x)——返回平方根

第12章汇总数据(P86)

12.1 聚集函数

确定表中行数，或满足某个条件的行数
获得表中行组的和
找出表列（或所有行或某些特定的行）最大值、最小值和平均值

AVG()——返回某列平均值，如select AVG(quantity) from orders

COUNT()——返回某列的行数，count(*)则是对所有行计数

MAX()——返回某列的最大值

SUM()——返回某列值总和

DISTINCT()——只包含不同的值，select avg(DISTINCT item_price) from orders

12.3 组合聚集函数

实际应用中，更适合叠在一起使用

select

count(*) as shuliang,

min(quantity) as min_quantity,

max(quantity) as max_quantity,

avg(item_price) as avg_price

from orders

第13章分组数据(P94)

13.1 数据分组

如果想知道每个供应商提供的产品总数、或者只显示单个供应商提供的产品之类的问题，就需要分组查询

13.2 创建分组

group by指示MySQL分组数据，这里显示的是，统计分组后，每个prop_id对应的数量，很明显这里FB出现了两次

select prod_id,count(*) as number_prod from orders

GROUP BY prod_id

显示结果如图

13.3 过滤分组

Having子句，类似Where，也可替代Where

Having过滤分组，Where过滤行

例如：想要列出至少有两个订单的所有顾客，为得到这种数据，必须基于完整的分组而不是个别的行进行过滤。

-- having这里如果用where就报错了，因为group by之后已经转成分组，而where不能搜索分组

select name,course,score

from student

group by course

having score>1

order by score asc

具体操作：先用group by做第一次分组排序，再用order by做第二次分行排序

Group by和Order by的区别

比如说，我要做一个排序，显示分数/单价的排序，用order by合适；

如果要排序每个科目的学生姓名，group by合适。

第14章使用子查询(P101)

嵌套在查询语句里面的查询语句，就是子查询

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

orderitems表

第1步，找到订单编号

select order_num from orderitems where prod_id="TNT2"

orders表

第2步，通过第1步的order_num，找到对应的cust_id

select cust_id

from orders

where order_num in(select order_num from orderitems where prod_id="TNT2")

customers表

第3步，通过第2步的cust_id，找到对应的全部信息

select*from customers

where cust_id=(select cust_id

from orders

where order_num in(select order_num

from orderitems

where prod_id="TNT2"))

14.3 作为计算字段使用子查询

使用子查询的另一个方法是创建字段，假如需要显示customers表中，每个客户的订单总数，订单和客户ID存储在orders表中。

从customers表中检索客户列表。
对于检索出的每个客户，统计其在orders表中的订单数目。

第一步

select cust_name,cust_address

from customers

第二步

select cust_name,cust_address,(select count(*)

from orders

where orders.cust_id=customers.cust_id)

from customers

--嵌套时，select查询的参数，带上表的名字来强化

第15章联结表(108)

15.1 联结

主键：要求主键列数据唯一，并且不允许为空。

外键：用于在两个表之间建立联系，需要指定引用主表的那一列。

比如说，用户id属性，在表1和表2都有，它可以是表1的主键，那么就可以说它也是表2的外键

联结：只存在于Select查询当中，并不是真实的表，用于通过一个相同的列的值，查询两个不同表的数据，From使用两个表，而且相同id这一个数据，需要指定从哪一个表取这个数据。

Where字句很重要，两个表的联结是用笛卡尔积的方式，如果不用，会产出很多错误数据。

Select vendors.vend_id,vend_name,prod_name,prod_price

From vendors,products

Where products.vend_id=vendors.vend_id

语法略微有不同，下面是内部联结语句，Inner Join的用法，上面的叫做等值连接，二者是一样。

Select vendors.vend_id,vend_name,prod_name,prod_price

From vendors inner join products

On vendors.vend_id=products.vend_id

对照第14章的子查询，两种方法都可以得出想要的结果，

select*from customers

where cust_id=(select cust_id

from orders

where order_num in(select order_num

from orderitems

where prod_id="TNT2"))

另一种方法：

select cust_name,cust_cust_contact

from orderitems,orders,customers

where orderitems.order_num=orders.order_num

and orders.cust_id=customers.cust_id

and prod_id='TNT2'

第16章创建高级联结(P117)

16.1 使用表别名

就是给表起个简单的名字，比如orders起名叫o，products起名叫p，把长的名字转化成短的名字，方便书写，缩短SQL语句

Select o_I.prod_id,cust_name,quantity

From orderitems as o_i,orders as o,customers as c

Where o_i.order_num=o.order_num

and o.cust_id=c.cust_id and prod_id='TNT2'

16.2.1 自联结

比如products表里面，有一个物品DTNTR是损坏的，我需要知道在同一张表里，生产者个DTNTR的供应商，又生产了其他什么物品，那就是同一张表，做了两次查询

子查询

select prod_id,vend_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.prod_id='DTNTR'

相比起来，联结可能更简洁一些，二者都比较容易理解

16.2.2 自然联结（目前没看懂）

16.2.3 外部联结

内部联结是将两个表里，都含有的字段做关联，但如果想要知道没有关联的行，就要用外部联结。

对每个客户下了多少订单进行计数，包括那些至今还没有下单的客户
列出所有产品以及订购数量，包括没人订购的产品
计算平均销售规模，包括那些至今未下订单的客户

先用一个内部联结的例子，检索orders和customers表，用户id及订单的关系

select o.cust_id,o.order_num,c.cust_name

from orders as o INNER JOIN customers as c

on o.cust_id=c.cust_id

输出是

select o.cust_id,o.order_num,c.cust_name

from customers as c LEFT JOIN orders as o

on o.cust_id=c.cust_id

输出是

16.3 使用带聚集函数的联结

查询每个客户对应的订单总数

select o.cust_id,c.cust_name,count(o.order_num) as num_order

from customers as c INNER JOIN orders as o

on o.cust_id=c.cust_id

group by c.cust_id

注意事项：

联结类型，一般使用内部联结，但外部联结也是可用的
保证正确的联结条件，否则将返回不正确的数据
提供联结条件，否则会得出笛卡尔积

实战演习：

问题1：得出每一门学科对应的最高分数

select course,max(grade)

from student

group by course

问题2：接上一步，得出每门科目最高分数的人是谁

select b.course,b.Max_grade,a.name

from (select course,max(grade) as Max_grade from student group by course)as b,student as a

where b.Max_grade=a.grade and b.course=a.course

问题3：SQL写出每一位同学的总成绩

select distinct name,sum(grade) as all_grade

from student

group by name

第二步：其中张三，李四植树总分各加了100分，这里的排列就是先把张三、李四不加入排列，再加入排列（再次加入时，已经给加分了）

select name,SUM(grade)

from student

where name not in('李四','张三')

GROUP BY name

Union

select name,SUM(grade)+100

from student

where name in('李四','张三')

GROUP BY name

第17章组合查询(P124)

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

使用条件

在单个查询中从不同表返回类似结构的数据，
对单个表执行多个查询，按单个查询返回数据。
自己记录一条，两个select筛选的列名应该一致，不然多出的字段会排成行而不是列
只允许一个order by/group by

第18章全文本搜索(P130)

18.1 理解全文本搜索

第8章、第9章分别使用了通配符（Like）和regexp（正则表达式）来匹配，但存在限制。

性能——通配符和正则匹配通常要尝试访问所有行，非常耗时。
明确控制——使用通配符和正则匹配很难。
智能化的结果——例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个还是包含多个匹配的行。选择更好的排序方式来展示它们。

18.2 使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。

在索引之后，SELECT可与Match()和Against()一起使用以实际进行搜索。

18.2.1 启用全文本搜索支持

创建表时启用全文本搜索，CREATE TABLE里，加一行FULLTEXT(note_text)，这里的索引是这一列，也可以选择多个列。

在定义之后，MySQL自动维护该索引，在增加、更新或删除时，索引随之自动更新。也可以创建表之后，定义索引。

18.2.2 进行全文本搜索

在索引之后，使用Match()和Against()，Match()指定被搜索的列，Against()指定要使用的搜索表达式。可实际使用后，发现并查不到数据，实际做下去，发现不如Like容易。

select name from student

where Match(name) Against("梁")

第19章插入数据(P142)

19.1 数据插入

INSERT是用来插入(或添加)行到数据库表的，插入可以用几种方式使用：

插入完整的行
插入行的一部分
插入多行
插入某些查询的结果

19.2 第一种：插入完整的行

Insert Into 表名

Values(10,'梁君','家庭住址是XXXOOO',NULL)

Values中每个值对应数据库结构而定，每个列必须有值，没有值的使用NULL，第一个主键可以使用NULL，前提是这个主键支持自动增量，这样可以不必输入最前面的一列值。

Insert Into student(id,name,course,grade)

Values(NULL,'梁大','物理',30)

如果把表中具体的参数标出来，那样更具体，并且在表结构变动时，不会出错。

提升整体性能，可在Insert和Values之间加入Low_Priority，指示MySQL降低Insert语句的优先级。这个Low_Priority也支持后面的Update和Delete。

19.3 第二种：插入多行

简单一点的方式，就在Values后面加个逗号，再加一些数据即可。

19.4 第四种：插入检索出的数据

该场景参考

Insert Into student(id,name,course,grade)

Select id,name,course,grade from student

报错了，原因主键id的值不能相同，如果不能保证两张表的数据，可以不写主键(也就是id列)

第20章更新和删除数据(P150)

20.1 更新数据

更新(修改)表中的数据，可使用Update语句，可采用两种方式使用：

更新表中特定行
更新表中所有行

Update语句非常容易使用，由3部分组成，分别是：

要更新的表
列名和它们的新值
确定要更新行的过滤条件

Update student

Set name='梁君改',grade=99

Where id=1

如果不使用Where作为筛选和限制，则会更改所有列的值，支持多行修改

20.2 删除数据

从一个表中删除(去掉)数据，使用DELETE语句，可以用两种方式使用DELETE：

从表中删除特定的行
从表中删除所有行

20.3 更新和删除的指导原则

需要遵守的规则：

尽量不用Update或Delete语句，如果使用，需要加入Where筛选条件
保证每个表都有主键（主键是用来保证数据完整性）
在使用Update或Delete语句之前，使用Select查询，防止更新/删除错误
使用强制实施引用完整性的数据库

Delete from 表 Where name="梁君"

第21章创建和操纵表(P155)

21.1 创建表

使用交互式创建和管理表的工具(MySQL或是其他的数据库软件)、MySQL语句操纵

21.1.1 表创建基础

为利用Create Table创建表，必须给出下列信息

新表的名字，在关键字Create Table之后
表列的名字和定义，逗号分隔

Create table 表

(

id int not null Auto_Increment,

name char(50) not null,

course char(50) not null,

score int not null,

address char(255) null,

primary key(id)

)Engine=InnoDB;

上面的Auto_Increment是自动增量， primary key是主键，Engine=InnoDB是数据库引擎。

注意事项：

MySQL语句忽略空格，可以在一行中输入，也可以分行输入，互不影响
处理现有的表，创建表格不是覆盖，需删除旧表，再创建新表

21.1.2 使用NULL值

在创建表时，需规定哪一列允许NULL值还是不允许NULL值，主键通常都需要值。

NULL值和空串不一样，空串是''，实际上是有值，而NULL就是无值。

21.1.3 主键再介绍

主键值必须唯一，每个行必须要有唯一的主键值。如果主键使用单个列，那它的值必须唯一。如果使用多个列，这些列的组合值必须唯一。

21.1.4 Auto_Increment

自动增量，每个表只允许一个Auto_Increment列，而且它必须被索引(比如，使它成为主键)

21.1.5 指定默认值

Create table student4

(

id int not null Auto_Increment,

name char(50) not null,

course char(50) not null DEFAULT '语文',

score decimal(8,2) not null,

Primary Key(id)

)Engine=InnoDB;

这里的decimal(8,2)指的是类型、长度、小数，其实和上面的char(50)意思是一样的

而DEFAULT的值，就是默认值

21.1.6 引擎类型

前面几个创建表，使用的都是Engine=InnoDB引擎，如果不写，可能默认使用MyISAM引擎，但不是所有语句都能使用它。所以，人为规定了InnoDB引擎。

InnoDB不支持全文搜索。
Memory在功能等同于MyISAM，但由于数据存储在内存(不是硬盘)中，速度很快(适合临时表)。
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务管理。
混用引擎，不能作用于外键，使用一个引擎的表不能引用使用不同引擎的表的外键。

21.2 更新表

更新表定义，可使用ALTER TABLE语句，必须给出以下信息：

在ALTER TABLE之后给出要更改的表名(该表必须存在，否则出错)
所做更改的列表

Alter Table vendors

Add vend_phone char(20);

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

Alter Table vendors

Drop Column vend_phone ——Drop Column删除列

Alter Table另一个用途是定义外键，下面是外键的代码

Alter Table orderitems

Add CONSTRAINT fk_orderitems_orders

FOREIGN Key(order_num) REFERENCES orders(orders_num);

21.3 删除表(P164)

删除一整张表

Drop Table 表名

21.4 重命名表

Rename Table 旧表名 to 新表名 ——可以更改多个表名，一排一排的修改即可

第22章使用视图(P166)

22.1 视图

视图是虚拟的表，视图也是一个查询语句，提供的不是数据而是根据检索数据的查询。视图提供了一种MySQL的Select语句层次的封装，可以用来简化数据处理以及重新格式化基础数据或保护基础数据。

视图一般用于检索Select，而不用更新Insert、Update、Delete。

视图的特点：

重用SQL语句。
简化复杂的SQL操作，在编写查询后，可以重用而不需要知道细节。
使用表的部分而不是全部。
保护数据，可以让用户查看一部分数据而不是全部。
更改数据格式和表示，视图可返回与底层表的表示和格式不同的数据。

22.1.2 视图的规则和限制

与表一样，视图必须唯一命名（视图和表的名字不能相同）。
对于可创建的视图数量无规定。
为了创建视图，必须有足够权限。
视图可以嵌套，可以从其他视图的检索数据，来构造视图。
ORDER BY可以用在视图，但如果检索数据中也有ORDER BY，则会被覆盖。
视图不能索引，也不能有关联触发器或默认值。
视图可以和表一起使用，例如，编写一条联结表和视图的SELECT语句。

22.2 使用视图

视图用Create View语句创建
使用Show create new 视图名；来查看创建视图的语句
用Drop删除视图，语法为Drop view 视图名
更新视图时，先用Drop再同Create，也可以Create or replace view，如果要更新的视图不存在，会创建一个；如果已存在，会更新视图

22.2.1 利用视图简化复杂的联结

创建视图，后面是一整块查询语句，前面这一行就是创建一个视图

Create View 视图名 as

Select s.id,s.name,s.course,s.grade,info.address,info.beizhu

From info,student as s

Where info.name=s.name

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

22.2.3 用视图过滤不想要的数据（也就是在检索中不检索部分数据）

22.2.4 使用视图与计算字段

22.5 更新视图

视图是可更新的，可以用Insert、Update和Delete，视图本身没有数据，实际上是对基表增加或删除行。但是，并非所有视图都可更新，如果MySQL不能确定被更新的基数据，则不允许更新。

分组(使用Group By和Having)
联结Union
子查询
并
聚集函数(Min()、Count()、Sum()等)
Distinct
导出(计算)列

第23章存储过程(P174)

23.1 存储过程

如果有多种情况：

为了处理订单，需要核对保证库存中有相应的物品。
如果库存有物品，这些物品需要预定，不卖给别人。
库存中没有的物品需要订购，需要与供应商沟通。
关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应客户。

存储过程，简单的说，就是为以后的使用而保存的一条或多条MySQL语句的集合，可将其看成批处理，它们的作用不止是批处理。而保存后的SQL语句，后续可持续使用。

23.2 为什么使用存储过程

一些主要理由：

通过把处理封装在容易使用的单元中，简化复杂操作。
由于不要求反复建立一系列处理步骤，这保证了数据的完整性，如果所有人都使用这个存储过程，则使用的代码都是相同的。这一点的延伸就是防止错误，需要执行的步骤越多，越容易出现错误。
简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。动用它的人员甚至可以不知道这些变化。

换句话说，使用存储过程有3个主要好处，简单、安全、高性能。

难点1:存储过程编写复杂 2.权限相关，有可能没有对应权限。

23.3 使用存储过程

23.3.1 执行存储过程

MySQL称存储过程的执行为调用，使用Call，Call接受存储过程的名字以及需要传递给它的任意参数。

Call productprice(@pricelow,

@pricehigh,

@priceaverage)

其中，执行名为procutproce的存储过程，它计算并返回产品最低、最高和平均价格。

存储过程可以显示结果，也可以不显示。

存储过程实际上也是一种函数。

23.3.2 创建存储过程

Create Procedure aaa()

Begin

select course,max(grade)as MAX_grade

from student

group by course;

End;

Begin和End语句用来限定存储过程体，过程体本身仅是一个简单的Select语句。

23.3.3 删除存储过程

Drop Procedure aaa;

这里就不用写函数，而直接写过程体名字就行。

23.3.4 使用参数

存储过程，只简单的显示Select语句的结果。一般情况，存储过程并不显示结果，而且把结果放到指定的变量(内存中一个特定的位置，用来临时存储数据)。

第一步：创建带参数的存储过程

Create Procedure abc(

Out avg decimal(8,2),

Out max decimal(8,2)

)

BEGIN

Select avg(grade) Into avg from student;

Select max(grade) Into max from student;

END;

第二步：调用存储过程(存储过程是一个函数，形参必须都传递进去)

Call abc(@avg,@max)

第三步：查询变量值(可以查询一个，可以查询多个)

Select @avg,@max

MySQL支持IN(传递给存储过程)、OUT(从存储过程中传出，还有一种INOUT(书中未提及)。

复杂的存储过程

-- 新建

Create Procedure tax(

In onumber INT,

In taxable Boolean,

Out final_total decimal(8,2))

Begin

-- 定义总钱数和税率

Declare total decimal(8,2);

Declare taxrate INT DEFAULT 6;

-- 先计算一个总钱数，这句话就是说，输入的订单号等于表中已有的订单号

select sum(item_price*quantity) from orderitems where order_num=onumber

Into total;

-- 判断是否需要税率

If taxable Then

Select total+(total/100*taxrate)

Into total;

End if;

-- 最后，保存计算后数据

select total into final_total;

End

调用时，使用

Call tax(20005,0,@total);

Select @total;

第二个参数，输入0，不走税率；输入1，走税率计算。而第三个就是计算结果。

23.3.6 检查存储过程

显示用来创建一个存储过程的Create语句，可以用下面显示。

Show Create Procedure tax;

获得何时、由谁创建等详细的存储过程列表，使用下面语句。

Show Procedure Status;

第24章使用游标(P185)

24.1 游标

有时，需要在检索出来的行中前进或后退一行或多行，这就是使用游标的原因。

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

24.2 使用游标

使用前，必须声明(定义)，这个过程没有检索数据，只是定义要使用的Select语句。
一旦声明后，必须打开游标以供使用，这个过程用前面定义的Select语句把数据实际检索出来。
对于填有数据的游标，根据需要取出(检索)各行。
在结束游标使用时，必须关闭游标。

24.2.1 创建游标

Declare创建游标，Declare命名游标，并定义相应的Select语句，根据需要带Where和其他子句。

Create Procedure processorders()

Begin

Declare ordernumbers cursor

For

Select order_num From orders;

End;

这个存储过程并没有做很多事情，Declare语句用来定义和命名游标，这里的游标名字就是ordernumbers。存储过程处理完成后，游标会消失。

定义游标之后，可以打开它。

24.2.2 打开和关闭游标

游标使用Open cursor语句来打开，比如Open ordernumbers

处理完成后，使用Close ordernumbers进行关闭，End语句也会自动关闭游标。

后面的以后补充吧，实在看不下去了 ——2020年4月15日

第25章使用触发器(P192)

25.1 触发器

比如当执行了某条/某些语句，在事件发生时自动执行，应该怎么做:

每当增加一个顾客到某个数据库表时，都检查电话格式是否正确，州的缩写是否大写。
每当订购一个产品，都从库存数量中减去订购的数量。
无论何时删除一行，都在某个存档表中保留一个副本。

所有这些例子，都是它们需要在某个表发生更改时自动处理，这就是触发器。

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句，或位于Begin和End之间的一组语句。

DELETE
INSERT
UPDATE

其他MySQL语句不支持触发器。

25.2 创建触发器

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

唯一的触发器名；
触发器关联的表；
触发器应该响应的活动(Delete、Insert和Update)
触发器何时执行(处理之前或之后)

Create Trigger newproduct After Insert to products

For Each Row Select "一二三四";

Create Trigger：创建名为newproduct的触发器

After Insert to：在Insert语句成功执行后执行

For Each Row：代码对每个插入行执行

文本"一二三四"：对每个插入的行显示一次

25.3 删除触发器

Drop Trigger newproduct;

25.4 使用触发器

25.4.1 Insert触发器

Insert触发器再Insert语句执行之前或之后执行。

在Insert触发器代码内，可引用一个名为New的虚拟表，访问被插入的行
在Before Insert触发器中，New中的值也可以被更新(允许修改被插入的值)
对于Auto_Increment列，New在Insert执行之前包含0，在Insert执行之后包含新的自动生成值。

一个更好的，在插入数据后赋予新id的方法：

Create Trigger neworder After Insert On orders

For Each Row Select NEW.order_num;

创建了一个名为neworder的触发器，它在After Insert On orders执行，orders是数据库表，在插入一条新数据后，触发器从NEW.order_num得到这个值并返回。

Insert Into orders(order_date,cust_id)

Values(Now(),10001)

这里输入语句，只输入了两个字段，返回值会自动更新前面的order_num字段。

再补充一个完整的插入步骤

1.在设计表界面，可以看到这个表有没有触发器

2.在orders表，插入一条新数据之后，再插入一条虚拟表格new的新数据到archive_orders表，而为什么用new而不是old，思考一下，如果是删除，就用old。

注意，这里的after，尝试过改成before，返回的插入结果，id是0，所以还是用after。

Create Trigger neworders222 after insert on orders for each row

begin

Insert Into archive_orders(order_num,order_date,cust_id)

Values(new.order_num,new.order_date,new.cust_id);

end

3.删除触发器

drop trigger neworders222

25.4.2 Delete触发器

Delete触发器在Delete语句执行之前/之后执行。

在Delete触发器代码内，可以引用一个叫做Old的虚拟表，访问被删除的行
Old中的值全都是只读的，不能更新

Create Trigger deleteorder Before Delete On orders For Each Row

Begin

Insert Into archive_orders(order_num,order_date,cust_id)

Values(Old.order_num,Old.order_date,Old.cust_id);

End

使用Before Delete触发器的优点，先存档，再删除，更加稳妥。

arcive_orders这个表用来存储被删除的内容，而被删除的内容，暂时是存储在Old表中，就相当于把Old里的缓存放到真正的表中。

25.4.3 Update触发器(P196)

在Update触发器代码中，可以引用Old的虚拟表访问以前(Update语句前)的值，引用一个名为New的虚拟表访问新更新的值。
在Before Update触发器中，New中的值可能也被更新(允许更改将要用于Update语句中的值)。
Old中的值全部是只读，不能更新。

Create Trigger updatevendor before update on vendors

for each row set New.列名=Upper(New.列名);

New.列名=Upper(New.列名)

25.4.4 关于触发器的进一步介绍

创建触发器可能需要特殊的安全访问权限，但是，触发器是自动的。如果Insert、Update、Delete语句能够运行，则相关触发器也能执行。
触发器一种很有意义的使用是创建审计跟踪。
触发器不支持Call语句，存储过程代码需要复制到触发器内。

第26章管理事务处理(P199)

26.1 事务处理

并非所有引擎都支持事务处理，比如默认的MyISAM不支持明确的事务处理管理，而InnoDB支持，所以全篇都在用InnoDB引擎。

事务处理可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

假如多个表之间有关联，有同一个字段的关联如订单编号，当出现某种数据库故障(如超出磁盘空间、安全限制、表锁等)阻止了这个过程，数据库可能就会出现空白订单编号的情况。

这就是为什么使用事务处理。

事务(transcation)指一组SQL语句
回退(rollback)指撤销指定SQL语句的过程
提交(commit)指将为存储的SQL语句结果写入数据库表
保留点(savepoint)指事务处理中设置的临时占位符，可以对它发布回退

26.2 控制事务处理

管理事务处理的关键在于将SQL语句组分解为逻辑块，并明确规定数据何时应该回退，何时不应该回退。

Start Transcation ——标识事务的开始

26.2.1 使用Rollback

MySQL的Rollback命令用来回退(撤销)MySQL语句，执行下来，这个表删除又回来了。rollback只能在一个事务处理内使用，在执行Start Transaction命令之后生效。

Select*from info

Start Transaction

-- 删除掉这个表

Delete from info

-- 回滚

Rollback

Select*from info

26.2.2 使用Commit

一般的MySQL语句是直接针对数据库表执行和编写的，称为隐含提交，即提交(写/保存)是自动进行的。但是在事务处理块总，提交不会隐含进行，需要使用Commit语句。

执行几次后的结果，两个Delete命令，保证系统中两个表完全删除掉这条记录。也就是说，start transaction之后，第一次执行delete命令后，是不会真正删除的，如果再输入一个commit或重新执行一次delete语句，才会执行。

Start Transaction;

Delete From orders where order_num=10002;

Delete From orderitems where order_num=10002;

Commit;

26.2.3 使用保留点

简单的Rollback和commit语句就可以写入或撤销整个事务处理。但是更复杂的事务处理需要部分提交或回退。

保留点，使用Savepoint语句，每个保留点有唯一名字，可创建多个保留点。

Savepoint delete1;

而回退到保留点的语句，就是

Rollback to delete1;

如何释放保留点？

执行一条Rollback或Commit后自动释放
Releasesavepoint也可以释放

26.2.4 更改默认的提交行为

默认MySQL行为是自动提交所有更改，而且立即生效。

Set autocommit=0;

——标志决定是否自动提交更改，0(假)指示MySQL不自动提交更改，直到autocommit设置为真为止。

第27章全球化和本地化（P204)

27.1 字符集和校对顺序

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

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

在MySQL的正常数据库活动(Select、Insert等)，不需要操心太多。

27.2 使用字符集和校对顺序

查看所支持的字符集完整列表，使用以下语句：

Show Character set;

查看所支持校对的完整列表，使用以下语句：

Show Collation;

创建一个完整的

Create Table mytable

(column1 int,

column2 varchar(10)

)default character set hebrew

collate hebrew_general_ci;

第28章安全管理(P208)

28.1 访问控制

MySQL服务器的安全基础是：用户应该对他们需要的数据具有适当的访问权，既不能多也不能少。

MySQL可以通过可视化手段，添加一个用户，并且可以设置应有的权限

28.2 管理用户

MySQL用户账号和信息存储在名为mysql的MySQL数据库中。一般不需要直接访问mysql数据库和表，但有时需要直接访问。比如想要知道这个数据库有哪些用户时，就需要用到。

Use mysql;

Select user from user; ——返回的两个都是用户，拥有不同权限

28.2.1 创建用户账号

为了作为散列值指定口令，使用identified by，不然这个口令就是加密后的文本。

Create User ben identified by 'a213456'

更改名称

Rename User ben to ben222;

28.2.2 删除用户账号

为了删除一个用户账号(以及相关的权限)，使用Drop User语句

Drop User ben222;

28.2.3 设置访问权限

新创建的用户没有访问权限，能登录MySQL，但看不到数据，不能执行任何操作。

Show Grants For ben

修改权限，crashcourse是全部权限

Grant Select On crashcourse.* To ben;

如果取消，则用Revoke，其他语句完全一样。

28.2.4 更改口令

Set Password=Password('n3w p@$$wOrd');

——这个先别用了，用过一次把我root的密码给改没了，再找回来浪费很多时间

第29章数据库维护(P216)

29.1 备份数据

使用命令行实用程序mysqldump转储所有数据库内容到外部文件。在进行常规备份前这个实用程序应该正常工作，以便正确地备份转储文件。
可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据。
可以使用MySQL的backup table或Select into outfile转储所有数据到某个外部文件。这两条语句都接受将要创建的系统文件名，此系统文件必须不存在，否则出错。数据可以通过Restore Table复原。

29.2 进行数据库维护

Analyze Table 表名，用来检查表键是否正确。

Check Table 表名，可用于多个表检查，发现和修复问题。在MyISAM表上对索引进行检查。
Changed检查自最后一次检查以来改动过的表。
Extended执行最彻底的检查。
Fast只检查未正常关闭的表。
Medium检查所有被删除链接并进行键检验。
Quick只进行快速扫描。
如果MyISAM表访问产生不正确，可能需要Repair table来修复相应的表，如果经常使用，可能会有更大的问题要解决。
如果从一个表中删除大量数据，应使用Optimize Table来收回所用的空间，优化性能。

29.3 诊断启动问题

在排除系统启动问题时，首先尽量使用手动启动服务器。在命令行中执行Mysqld启动。

--help显示帮助：一个选项列表
--safe-mode：装载减去某些最佳配置的服务器
--verbose：显示全文本消息(与--help联合使用)
--version：显示版本信息然后退出。

29.4 查看日志文件(P218)

错误日志。

第30章改善性能

30.1 改善性能

在诊断应用的滞缓现象和性能问题时，性能不良的数据库(以及数据库查询)是最常见的原因

MySQL使用show variables和show status查看配置
MySQL一个多用户多线程的DBMS，经常执行多个任务。如果其中一个执行缓慢，则所有请求都执行缓慢。使用Show processlist显示所有活动进程(以及它们的线程ID和执行时间)。还可以用Kill命令终结某个特定的进程
有不止一个方法编写同一条Select语句，联结、并、子查询等，找到最佳方法
使用Explain语句让MySQL解释它将如何执行一条Select语句
存储过程执行得比一条一条地执行其中的各条MySQL语句块
应该总是使用正确的数据类型
不要检索比需求还要多的数据，比如Select* 除非真的要每一个列
Select语句有多条OR条件，不如分开来用Union联合，会有极大的性能改进
索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果不经常搜索，可以不用索引
Like很慢，所以最好使用Fulltext而不是Like
数据库是不断变化的实体，由于表的使用和内容的更改，优化和配置可能改变

MySQL语句的语法

C.1 Alter Table

用来更新已存在表的模式，创建新表用Create Table。详情第21章。

Alter Table 表名

(

Add column datatype null/not null,

Change column columns datatype null/not null,

Drop column,

...);

Alter Table student

add column other char(30) not null;

C.2 Commit

用来将事务处理写到数据库。详情第26章。可以理解为将事务立即执行。

Commit;

C.3 Create Index

用于在一个或多个列上创建索引。详情第21章。

Create Index 索引名 Create Index 索引名

On 表名(列 asc/desc,...) On 表名(id asc)

C.4 Create Procedure

创建存储过程，是可复用的代码块，可供后续使用和修改，详情第23章。

Create Procedure 存储过程名([参数])

Begin

...

End;

C.5 Create Table

创建数据库表，详情第21章。注意是表，不是库

Create Table 表名

(

column datatype null/not null,

...

id int not null auto_increment,

name varchar(30) not null,

primary key(id)

);engine=InnoDB

C.6 Create User

为系统添加新的用户账户。

Create User username[@hostname]

[identified by 密码 'password']

Create User ben identified by 'a213456'

C.7 Create View

创建一个或多个表上的新视图，视图就是一个比较直观化的表格，不能再编辑sql代码，只能查看。详情第22章。

Create View 视图名 as

Select s.id,s.name,s.course,s.grade,info.address,info.beizhu

From info,student as s

Where info.name=s.name

C.8 Delete

从表中删除一行或多行。详情第20章。

Delete from 表名

[Where ...];

C.9 Drop

永久地删除数据库对象(表、视图、索引等)，详情见第21、22、23、24章。

itemname;

C.10 Insert

给表插入一行，详情第19章。

Insert into 表名[(column,...)]

values(values1,values2,...);

C.11 Insert Select

插入Select的结果到一个表，详情见第19章。

Insert into tablename

select columns ...from tablename..

C.12 Rollback

撤销一个事务处理块，详情见第26章。

Rollback[to savepointname]

C.13 Savepoint

保留点，详情见第26章。

Savepoint sp1;

C.14 Select

Select用于从一个或多个表(视图)中检索数据，更多信息参考第4-17章节。

Select columnname,...

From tablename,...

Where

Group by

Having

Order by

C.15 Start Transaction

新的事务处理块的开始，详情见第26章。

Start Transaction;

C.16 Update

更新表中的一行或多行，详情见第20章。

Update student(表名)

Set name(列名)='小红改'

Where id=10

C.17 触发器trigger

在插入/删除/更新某个/些字段后，触发器可以会写入一些数据到新的表。

Create Trigger newproduct After Insert to products

For Each Row Select "一二三四";

删除触发器

Drop Trigger newproduct;

更新内容

Create Trigger updatevendor before update on vendors

for each row set New.列名=Upper(New.列名);

New.列名=Upper(New.列名)

C.18 Like和Regexp区别

比如原有的表结构如下

Select*from student where name Like "君" ——返回空白

Select*from student where name regexp "君" ——返回所有文字内带有君的列，也就是1、2、3、11、12，一共5列

也就是说，Like匹配整个列，而不是匹配列里面的值。但是如果使用通配符，就可以匹配里面的值。

Select*from student where name Like "%君" ——返回梁君、梁再改改改君。匹配前面全部，后面只匹配一个君字。

"%君%"如果用这个，前后都有%，返回1、2、3、11、12，一共5列

Select*from student where name Regexp "^.*君" ——使用占位符，shift+数字6，句号代表匹配任意，乘号匹配多个

Select*from student where name Regexp "^君" ——但如果使用这样的匹配，只返回君梁啊123

12、MySQL必知必会

我要留言 / 展开表单