第十五章:联结表

本章介绍如何编写使用联结的 SELECT语句。

外键（foreign key）外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

为了方便,接下来的数据库都使用MySQL的官方数据集

# 查找每个员工的工资
select distinct e.emp_no,
	concat(e.first_name," " ,e.last_name) as name,s.salary 
	from employees e,salaries s 
	where s.emp_no= e.emp_no;

# 没有联结条件的表关系返回的结果为笛卡儿积。
# 笛卡尔积就是拿第一行和另一个表全部行连接,然后拿第二行和另一个表全部行连接...
select distinct e.emp_no,
	concat(e.first_name," " ,e.last_name) as name,s.salary 
	from employees e,salaries s

# 查找员工编号emp_no对应的职务dept_name(dept_no为职务编号)
select emp_no,dept_name 
    from departments a inner join dept_emp b on a.dept_no=b.dept_no 
    order by emp_no;
# 当然也可以使用where来连接
select b.emp_no,a.dept_name from departments a,dept_emp b
	where a.dept_no = b.dept_no
	order by emp_no;

第十六章:创建高级联结

本章介绍如何对被联结的表使用表别名和聚集函数。
自联结、自然联结和外部联结。

自连接一般用于不知道详细信息的查询.

现在要查找比惠惠消费要高的人,正常的思路就是:

1	select name from table1 where price > 27

但是,在不看表的情况下,我们并不知道惠惠的消费是27,于是使用子查询:

1 2	select name from table1 where (select price from table1 where name = '惠惠')

如果不想使用自连接,这时就可以简化成自连接查询

1 2	select t1.name from table1 t1,table1 t2 where t1.name = '惠惠' and t1.price < t2.price

同理,查找页数比id为258126这本书还有多的书的id

1 2	select t2.id,t2.page from commic_list t1,commic_list t2 where t1.id=258126 and t1.page < t2.page

自然联结
无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。
标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。
自然联结排除多次出现，使每个列只返回一次.

然而MySQL并没有特地支持自然联结,这意味着如果我们要实现自然联结,那么就需要我们手动输出那些列.

1
2
3

# 手动输出要输出的列
select d1.*,d2.emp_no,d2.from_date,d2.to_date 
	from departments d1 inner join dept_emp d2 on d1.dept_no = d2.dept_no;

外部联结:
left join on 和 right join on合成外部联结

使用带聚集函数的联结

# 查询每个职务有多少人
select d1.dept_no,count(d1.dept_no) 
    from departments d1 inner join dept_emp d2 on d1.dept_no = d2.dept_no
    group by d1.dept_no with rollup;
/*
+---------+-------------------+
| dept_no | count(d1.dept_no) |
+---------+-------------------+
| d001    |             20211 |
| d002    |             17346 |
| d003    |             17786 |
| d004    |             73485 |
| d005    |             85707 |
| d006    |             20117 |
| d007    |             52245 |
| d008    |             21126 |
| d009    |             23580 |
| NULL    |            331603 |
+---------+-------------------+
*/

第十七章:组合查询

本章讲述如何利用UNION操作符将多条SELECT语句组合成一个结果集。
组合查询通常称为并（union）或复合查询（compound query）。

任何具有多个WHERE子句的SELECT语句都可以使用组合查询替代.
也就是说,有两种基本情况，其中需要使用组合查询：

在单个查询中从不同的表返回类似结构的数据；
对单个表执行多个查询，按单个查询返回数据。

# 查找员工号emp_no在[10002-10028]或者入职时间(to_date-from_date)大于100天的信息
select * from dept_emp 
    where emp_no between 10002 and 10028 
    or datediff(to_date,from_date) > 100
# 这时可以使用union
select * from dept_emp where emp_no between 10002 and 10028
union
select * from dept_emp where datediff(to_date,from_date) > 100

上面union看起来比较傻,但是union一般是用于多个表查询的,单个表基本不用他.

使用union注意:

UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）
==UNION从查询结果集中自动去除了重复的行==（换句话说，它的行为与单条SELECT语句中使用多个WHERE子句条件一样）。

包含或取消重复的行:
上面说的”UNION从查询结果集中自动去除了重复的行”是union的默认行为,如果需要保留重复的行,可以使用UNION ALL.

# 使用union all
select * from dept_emp where emp_no between 10002 and 10028
union all
select * from dept_emp where datediff(to_date,from_date) > 100

UNION ALL做为UNION的一种形式，它完成 WHERE子句完成不了的工作。
如果确实需要每个条件的匹配行全部出现（包括重复行），则必须使用UNION ALL而不是WHERE

对组合查询结果排序:
select排序要使用order by,但是现在union有多条select,所以只能在最后一个select的后面使用order by

使用组合查询的话,==不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况==,要么就全部升序,要么就全部降序.不能一部分升序一部分降序.

第十八章:全文本搜索

本章学习如何使用MySQL的全文本搜索功能进行高级的数据查询和选择。

通配符和正则表达式的搜索机制的限制:

性能——通配符和正则表达式匹配通常要求MySQL尝试匹配表中所有行（而且这些搜索极少使用表索引）。因此，被搜索行数不断增加，这些搜索可能非常耗时。
明确控制—— 明确地控制匹配什么和不匹配什么。
例如，指定一个词必须匹配，一个词必须不匹配，而一个词仅在第一个词确实匹配的情况下才可以匹配或者才可以不匹配。
智能化的结果——它们都不能提供一种智能化的选择结果的方法。
例如，一个特殊词的搜索将会返回包含该词的所有行，而不区分包含单个匹配的行和包含多个匹配的行。类似的，一个特殊词的搜索将不会找出不包含该词但包含其他相关词的行。

这些限制都可以用全文本搜索来解决。
在使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词。MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行。这样，MySQL可以快速有效地决定哪些词匹配（哪些行包含它们），哪些词不匹配，它们匹配的频率，等等

简单来说:全文本搜索会根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算该行的等级,然后按等级降序返回。

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。
==一般在创建表时启用全文本搜索==。(CREATE TABLE语句（第21章中介绍）接受FULLTEXT子句，它给出被索引列的一个逗号分隔的列表。)

# 在定义表时使用fulltext
creat table product_notes
(
	note_id    int       not null  auto_increment,
	prod_id    char(10)  not null,
	note_date  datetime  not null,
	note_text  text      null,
	primary key (note_id),
	fulltext(note_text)
) engine = MyISAM;
# 在定义之后，MySQL自动维护note_text索引。在增加、更新或删除行时， 索引随之自动更新。

当然,也可以在定义表后再来建立全文本索引.
PS：不要再导入数据时使用fulltext，这样有助于更快的导入数据。

全文本索引的两个函数:

Match()指定被搜索的列
Against()指定要使用的搜索表达式。

使用规则:

传递给match（）的值必须与fulltext（）定义中的相同；
如果指定多个列，则必须列出它们（次序正确）；
除非使用binary方式，否则全文本搜索不区分大小写。

1
2
3

# match(note_text)指示MySQL针对指定的列进行搜索，against('rabbit')指定rabbit作为搜索文本
select note_text from product_notes
	where Match(note_text) Against('rabbit')

得到:

1554879643411

其实上面的全文本搜索可以使用通配符来做:

1	select note_text from product_notes where note_text like '%rabbit%'

得到:

1554879772323

虽然都是找到了两行,但是两者的顺序不一样.
全文本搜索的一个重要部分就是对结果排序。具有较高等级的行先返回
(包含词rabbit作为第3个词的行的等级比作为第20个词的行高。所以在全文本搜索中rabbit作为第三个词的行先返回)

1
2
3

# 测试每一行的等级
select note_text Match(note_text) Aganist('rabbit') AS rank
from product_notes

输出如下:

1554880282009

也就是说,如果某行包含有rabbit这个单词,那么他的rank就高,(不包含rank就为0),rank等级由词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来.

使用查询扩展:
查询扩展用来设法放宽所返回的全文本搜索结果的范围。（MySQL对数据和索引进行两遍扫描来完成搜索）。

用法：where子句中against指定的搜索表达式后跟with query erpansion;

检索过程:

进行一个基本的全文本搜索，找出与搜索条件匹配的所有行；
MySQL检查这些匹配行并选择所有有用的词（将会简要的解释MySQL如何断定什么有用什么无用）；
MySQL再次进行全文本搜索，这次不仅使用原来的条件，还是用所有有用的词。

1
2
3

# 首先使用全文本搜索
select note_text from product_notes
	where Match(note_text) Against('anvils')

得到:

1554880729595

1
2
3

# 然后使用查询扩展
select note_text from product_notes
	where Match(note_text) Against('anvils' with query expansion)

得到:

1554880865262

这次返回了7行。第一行包含词anvils，因此等级最高。
第二行与anvils无关，但因为它包含第一行中的两个词（customer 和recommend），所以也被检索出来

布尔文本搜索 :
全文本搜索的一种,但是没有fulltext索引也可以使用布尔方式（但这种方式很缓慢)
需要提供一下条件：

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

用法:使用关键字in boolean mode

1 2	select note_text from product_notes where Match(note_text) Against('heavy' in boolean mode)

得到:

1554881233044

此全文本搜索检索包含词heavy的所有行（有两行）。

1
2
3

# 匹配包含heavy但不包含以rope开始的词的行
select note_text from product_notes
	where Match(note_text) Against ('heavy -rope*' in boolean mode)

-rope*明确地指示MySQL排除包含rope*,得到:

1554881571442

-排除一个词，而* 是截断操作符（可想象为用于词尾的一个通配符）

所有布尔操作符如下:

# 搜索匹配包含词rabbit和bait的行。 
select note_text from product_notes 
	where Match(note_text) Against('+rabbit +bait' in boolean mode);
	
# 没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行。 
select note_text from product_notes 
	where Match(note_text) Against('rabbit bait' in boolean mode);
	
# 这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和 bait。 
select note_text from product_notes 
	where Match(note_text) Against ('"rabbit bait"' in boolean mode);
	
# 匹配rabbit和carrot，增加前者的等级，降低后者的等级。 
select note_text from product_notes 
	where Match(note_text) Against ('>rabbit <carrot' in boolean mode);
	
# 这个搜索匹配词safe和combination，降低后者的等级。 
select note_text from product_notes 
	where Match(note_text) Against ('+safe + (<combination)' in boolean mode);

注意: 在布尔方式中，不按等级值降序排序返回的行。

全文本搜索使用说明

在索引全文本数据时，短词被忽略且从索引中删除（短词定义为3个或3个以下字符的词：如果需要可以更改）；
MySQL带有一个内建的非用词（stopword）列表，这些词在索引全文本搜索时总被忽略（如果需要，可以覆盖这个列表）；
MySQL50％规则：如果一个词出现在50％以上的行中，则将它作为一个非用词忽略；50％规则不用于in boolean mode；
如果表中的行数少于3行，则全文本搜索不返回结果；
（因为每个词或者不出现，或者至少出现在50%的行中)
忽略词中的单引号，例如don’t索引为dont；
不具有词分隔符的语言不能恰当的返回全文本搜索结果；
仅在MyISAM数据库引擎中支持全文本搜索。

第十九章:插入数据

本章介绍如何利用SQL的INSERT语句将数据插入表中。

# 插入完整的行
# 各个列必须以它们在表定义中出现的次序填充。
insert into less_table values (50219,'hyl','http:xxx.jpg','2019-04-08 14:35','hh')
# 插入完整的行时,必须所有字段都要填入,没有的使用Null
insert into less_table values (50220,null,null,'2019-04-08 14:35','hh')

上面的代码不推荐使用,这种语句高度依赖于表中列的定义次序

# 在表名后的括号里明确地给出了列名
insert into less_table(id,name,img,created_time,text) 
	values (50220,null,null,'2019-04-08 14:35','hh')
# 使用这种语法，还可以省略列。这表示可以只给某些列提供值，给其他列不提供值。
insert into less_table(id,text) values (50221,'hh')

使用省略列需要注意:

该列定义为允许NULL值（无值或空值）。
在表定义中给出默认值。这表示如果不给出值，将使用默认值。

如果对表中不允许NULL值且没有默认值的列不给出值，则 MySQL将产生一条错误消息，并且相应的行插入不成功。

如果想要一次性插入多条数据,就可以组合insert into语句:

insert into less_table(id,text) values
	(50221,'hh'),
	(50222,'xx'),
	(20223,'ww');

可以利用insert into将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT

# 使用insert into从custnew表中的数据导入customers表
insert into customers(cust_id,
	cust_contact,
	cust_email,
	cust_name)
select cust_id,
	cust_contact,
	cust_email,
	cust_name
from custnew
where cust_id > 10000;

对比

1 2	insert into customers(cust_id,cust_contact,cust_email,cust_name) values (50221,'hh','http://xxx.com','hyl')

就知道,insert select不过是把values (50221,'hh','http://xxx.com','hyl')改成了select cust_id,cust_contact,cust_email,cust_name from custnew;

需要注意的是:insert select使用的并不是关键词参数,而是位置参数:
MySQL不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列（不管其列名）将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列.

第二十章:更新和删除数据

本章介绍如何利用UPDATE和DELETE语句进一步操纵表数据。

UPDATE语句由3部分组成:

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

1 2	# 更新特定行 update less_table set name = 'hyl',img = 'http://xx.jpg' where id = 50221

使用ignore关键字,因为数据库的操作是原子性的,一旦发生错误就会回退到之前的状态,如果想忽略错误,继续前进,那么就可以使用ignore关键字

1 2	# 使用ignore关键字 update IGNORE less_table set name = 'hyl',img = 'http://xx.jpg' where id = 50221

delete:

1	delete from less_table where id = 50221

如果想一次性删除所有列,不要使用delete from less_table,可以使用TRUNCATE TABLE,速度更快（TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）

1	truncate less_table

第二十一章:创建和操纵表

本章讲授表的创建、更改和删除的基本知识

creat table product_notes
(
	note_id    int       not null  auto_increment,
	prod_id    char(10)  not null,
	note_date  datetime  not null,
	note_text  text      null,
	primary key (note_id),
	fulltext(note_text)
) engine = InnoDB;

在创建新表时，指定的表名必须不存在。我们可以使用IF NOT EXISTS来避免错误:

creat table product_notes if not exists
(
	note_id    int       not null  auto_increment,
	prod_id    char(10)  not null,
	note_date  datetime  not null,
	note_text  text      null      default 'xxx',
	primary key (note_id),
	fulltext(note_text)
) engine = InnoDB;

注意:
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）。
对于auto_increment列,我们可以手动insert into一个值.但是后续的增量将开始使用该手工插入的值。

我们可以使用last_insert_id()来获取auto_increment列的最后一个值

1	select last_insert_id()

MySQL不允许使用函数作为默认值，它只支持常量.

引擎类型:ENGINE=InnoDB
在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT 语句或进行其他数据库处理时，该引擎在内部处理你的请求。

不同的引擎具有不同的功能和特性.

InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

不同的表可以使用不同的引擎,也就是说数据库允许引擎的混用.
但是外键不能跨引擎,使用一个引擎的表不能引用具有使用不同引擎的表的外键.

更新表:
理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

ALTER TABLE语句:

# 添加新的字段
alter table less_table add new_column char(20)
# 删除字段
alter table less_table drop column new_column

对于alter table,其实上面的添加/删除新的字段是很少用的,最常用的就是定义外键

1
2
3

alter table orderitems 
	add constraint fk_orderitems_orders
	foreign key (order_num) references orders (order_num);

删除表:

1	drop table less_table

重命名表:

# 重命名单个表
rename table less_table to new_table
# 重命名多个表
rename table old_table1 to new_table1
			old_table2 to new_table2
			old_table3 to new_table3

第二十二章:使用视图

本章将介绍视图究竟是什么，它们怎样工作，何时使用它们。如何利用视图简化前面章节中执行的某些SQL操作。

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

select cust_name,cust_contact
from customers,orders,orderitems
where customers.cust_id = orders.cust_id
	and orderitems.order_num = orders.order_num
	and prod_id = 'TNT2'

现在，假如可以把整个查询包装成一个名为product_customers的虚拟表，则可以如下轻松地检索出相同的数据：

1 2	select cust_name,cust_contact from product_custimers where prod_id = 'TNT2'

product_customers就是一个视图.
作为视图，本身并不包含数据。==它包含的是一个SQL查询==（与上面用以正确联结表的相同的查询）
为什么要使用视图？
视图的常见的应用之一是隐藏复杂的SQL，这通常都会涉及联结。

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

使用规则:

视图用CREATE VIEW语句来创建。
使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
用DROP删除视图，其语法为DROP VIEW viewname;。
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图

基表：用来创建视图的表叫做基表base table

利用视图简化复杂的联结

# 连接三个表,找出员工姓名对应的职务
select e.emp_no,concat(e.first_name,' ',e.last_name) as name,d1.dept_name
	from employees e,departments d1,dept_emp d2 
	where e.emp_no = d2.emp_no and d2.dept_no = d1.dept_no 
	order by e.emp_no 
/*
+--------+-------------------+-----------------+
| emp_no | name              | dept_name       |
+--------+-------------------+-----------------+
|  10001 | Georgi Facello    | Development     |
|  10002 | Bezalel Simmel    | Sales           |
|  10003 | Parto Bamford     | Production      |
|  10004 | Chirstian Koblick | Production      |
|  10005 | Kyoichi Maliniak  | Human Resources |
+--------+-------------------+-----------------+
*/

上面的sql语句超级复杂,这时就可以使用创建视图了:

# 用视图重新格式化检索出的数据(就是创建了一个新的name列)
create or replace view name2dept as 
select e.emp_no,concat(e.first_name,' ',e.last_name) as name,d1.dept_name
	from employees e,departments d1,dept_emp d2 
	where e.emp_no = d2.emp_no and d2.dept_no = d1.dept_no 
	order by e.emp_no

接下来就可以复用这个视图了:

select * from name2dept where emp_no < 10005;
/*
+--------+-------------------+-------------+
| emp_no | name              | dept_name   |
+--------+-------------------+-------------+
|  10001 | Georgi Facello    | Development |
|  10002 | Bezalel Simmel    | Sales       |
|  10003 | Parto Bamford     | Production  |
|  10004 | Chirstian Koblick | Production  |
+--------+-------------------+-------------+
*/

用视图过滤不想要的数据:

1
2
3

# 视图嵌套视图,并且过滤
create or replace view name2dept2 as 
select * from name2dept where emp_no < 10005;

使用视图与计算字段 :

1
2
3

# 使用视图来创建并且存储计算字段
create view sumPrice as 
select item_prict*quantity as tot_price from orderitems

如果视图定义中有以下操作，则不能进行视图的更新：

分组（使用GROUP BY和HAVING）；
联结；
子查询；
并；
聚集函数（Min()、Count()、Sum()等）；
DISTINCT；
导出（计算）列。

一般，应该将视图用于检索（SELECT语句）而不用于更新（INSERT、UPDATE和DELETE）。

第二十三章:使用存储过程

本章介绍什么是存储过程，为什么要使用存储过程以及如何使用存储过程，并且介绍创建和使用存储过程的基本语法。

迄今为止，使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一个完整的操作需要多条语句才能完成。

简单来说,存储过程就是数据库语言层面的代码封装与重用。
或者说存储过程就是一组可编程的函数，是为了完成特定功能的SQL语句集

创建存储过程:

# 存储过程就是函数,所以product_pricing()后面的括号就是放参数的地方
# begin和end中间就是函数体,不过这里称为"存储过程体"
create procedure product_pricing()
	begin
	select avg(prod_price) as price_average from products;
	end;

存储过程和函数是差不多的东西:
所以存储过程创建时并不会执行,只有在调用的时候才会执行.

执行存储过程
MySQL执行存储过程的语句为CALL,CALL接受存储过程的名字以及需要传递给它的任意参数。

执行上面定义的存储过程product_pricing:

# 没有参数,所以就不要传入参数了
call product_pricing();
/*
+---------------+
| price_average |
+---------------+
|     16.133571 |
+---------------+
*/

删除存储过程:

drop procedure product_pricing;
# 如果product_pricing不存在的话使用drop就会失败,
# 这时可以用DROP PROCEDURE IF EXISTS
DROP PROCEDURE IF EXISTS product_pricing;

使用参数:
MySQL支持IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）类型的参数。

# 此存储过程接受3个参数：pl存储产品最低价格，ph存储产品最高价格，pa存储产品平均价格
# 每个参数必须具有指定的类型，这里使用十进制值
# 关键字OUT指出相应的参数用来从存储过程传出一个值（返回给调用者） 
create procedure product_pricing(
	out p1 decimal(8,2),
	out ph decimal(8,2),
	out pa decimal(8,2)
)
begin
	# 将select的值放在p1变量里
	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;

注意:==不能通过一个参数返回多个行和列==。

执行:

1 2	# 传入的变量都必须以@开始 call product_pricing(@price_low,@price_high,@price_average)

示例二:

# ordertotal接受订单号并返回该订单的合计
# onumber定义为IN，因此订单号被传入存储过程。ototal定义为OUT，因此要从存储过程返回合计
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);

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

1
2
3

show create procedure ordertotal;
# 获得包括何时、由谁创建等详细信息的存储过程列表
show procedure status

但是注意show procedure status是列出所有的存储过程,我们可以使用like来限制输出:

1	show procedure status like 'ordertotal'

第二十四章:使用游标

本章将讲授什么是游标以及如何使用游标。

有时，需要在检索出来的行中前进或后退一行或多行,这时就可以使用游标了。

游标（cursor）是一个存储在MySQL服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

数据缓冲的思想：游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。
先有数据基础：游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。
类似于指针：游标类似于指向数据结构堆栈中的指针，用来pop出所指向的数据，并且只能每次取一个。

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

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

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

# 定义名为ordernumbers的游标，使用了可以检索所有订单的SELECT语句
create procedure processorders()
begin
	declare ordernumbers cursor
	for
	select order_num from orders
end;

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

打开和关闭游标
游标用OPEN CURSOR语句来打开：

1	open ordernumbers

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

游标处理完成后，使用close来关闭游标：

1	close ordernumbes

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

获得下一行数据:FETCH

# FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。
create procedure processorders()
begin
	# 声明局部变量
	declare o int;
	# 声明一个游标
	declare ordernumbers cursor
	for
	select order_num from orders;
	# 打开游标
	open ordernumbers
	# 获取数据
	fetch ordernumbers into o;
	# 关闭游标
	close ordernumbers;
end;

循环检索数据，从第一行到最后一行：

create procedure processorders()
begin
	# 声明局部变量
	declare done boolean default 0;
	declare o int;

	# 声明一个游标
	declare ordernumbers cursor
	for
	select order_num from orders;

	# 声明一个continue handler
	declare continue handler for sqlstate '02000' set done = 1;

	# 打开游标
	open ordernumbers

	# 循环
	repeat
		# 获取数据
		fetch ordernumbers into o;
	# 结束循环
	until done end repeat;

	# 关闭游标
	close ordernumbers;
end;

FETCH是在REPEAT内，因此它反复执行直到done为真（由UNTIL done END REPEAT;规定）(done一开始就设置为0,即False)

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

进一步修改的版本，这次对取出的数据进行某种实际的处理：

# ordertotal接受订单号并返回该订单的合计
# onumber定义为IN，因此订单号被传入存储过程。ototal定义为OUT，因此要从存储过程返回合计
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;
#########################################################################
create procedure processorders()
begin
	# 声明局部变量
	declare done boolean default 0;
	declare o int;
	# 变量t存储每个订单的合计
	declare t decimal(8,2);

	# 声明一个游标
	declare ordernumbers cursor
	for
	select order_num from orders;

	# 声明一个continue handler
	declare continue handler for sqlstate '02000' set done = 1;
	# 创建一个表ordertotals来存储 过程生成的结果
	create table if not exists ordertotals

	# 打开游标
	open ordernumbers;

	# 循环所有行
	repeat
		# 获取数据
		fetch ordernumbers into o;
		# 调用存储过程ordertotal,来计算每个订单的带税的合计
		call ordertotal(o,1,t);
		# INSERT保存每个订单的订单号和合计。 
		insert into ordertotals(order_num,total) values(0,t);

	# 结束循环
	until done end repeat;

	# 关闭游标
	close ordernumbers;
end;

此存储过程不返回数据，但它能够创建和填充另一个表，可以用一条简单的SELECT语句查看该表：

1	select * from ordertotals

1555030158464

第二十五章:使用触发器

本章介绍创建和使用触发器的语法。

触发器:在某个表发生更改时自动处理。
触发器只支持三种语句:

delete
insert
update

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

唯一的触发器名；
触发器关联的表；
触发器应该响应的活动（DELETE、INSERT或UPDATE）；
触发器何时执行（处理之前或之后）

1
2
3

# 每次成功插入行，显示Product added消息。 
create trigger newproduct after insert on products
for each row select 'Product added';

触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT，所以此触发器将在INSERT语句成功执行后执行。
这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。在这个例子中，文本Product added将对每个插入的行显示一次。

每个表每个事件每次只允许一个触发器。因此，每个表多支持6个触发器（每条INSERT、UPDATE 和DELETE的之前和之后）。
单一触发器不能与多个事件或多个表关联，(简单来说,就是一个触发器只能是insert,delete,update三者之一)

注意:如果before触发器或语句本身失败，MySQL 将不执行AFTER触发器（如果有的话）。通常，将BEFORE用于数据验证和净化。

删除触发器

1	drop trigger newproduct;

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

使用触发器
INSERT触发器

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；
在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改被插入的值）；
对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT 执行之后包含新的自动生成值

1
2
3

# 触发器从NEW. order_num取得这个值并返回它。
create trigger neworder after insert on orders
for each row select new.order_num;

创建一个名为neworder的触发器，它按照AFTER INSERT ON orders执行。在插入一个新订单到orders表时，MySQL生成一个新订单号并保存到order_num中。触发器从NEW. order_num取得这个值并返回它。

DELETE触发器

在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
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;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_ orders的存档表中（为实际使用这个例子，你需要用与orders相同的列创建一个名为archive_orders的表）

使用BEFORE DELETE触发器的优点为，==如果由于某种原因，订单不能存档，DELETE本身将被放弃。==

UPDATE触发器

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

1
2
3

create trigger updatevendor before update on vendors
for each row
set new.vend_state = Upper(new.vand_state);

这个触发器就是用来净化数据的:
每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换。
即:插入前将数据改成大写.

关于触发器的进一步介绍

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

第二十六章:管理事务处理

本章介绍什么是事务处理以及如何利用COMMIT和ROLLBACK语句来管理事务处理。

并非所有引擎都支持事务处理 :
MyISAM和InnoDB是两种最常使用的引擎。前者不支持明确的事务处理管理，而后者支持。

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。
简单来说:事务处理保证操作的原子性

事务不能回退SELECT语句。（这样做也没有什么意义。）也不能回退CREATE或DROP操作。

在使用事务和事务处理时，有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语:

事务（transaction）指一组SQL语句；
回退（rollback）指撤销指定SQL语句的过程；
提交（commit）指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（place- holder），你可以对它发布回退（与回退整个事务处理不同）。

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

1 2	# 标识事务的开始 start transaction

使用ROLLBACK

select * from ordertotals;

start transaction;
delete from ordertotals;
select * from ordertotals;
rollback;

select * from ordertotals;

回忆在scrapy使用mysql的经验:
首先使用start transaction标志事务开始,使用rollback语句回退 START TRANSACTION之后的所有语句.

使用COMMIT:事务确认
一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。

但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句，如下所示

start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;

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

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

# 创建占位符
savepoint delete1;

# 回退到占位符
rollback to delete1

# 释放占位符
release savepoint delete1

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

1	set autocommit = 0;

第二十七章:全球化和本地化

本章介绍MySQL处理不同字符集和语言的基础知识

字符集和校对顺序
重要术语：

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

使用字符集和校对顺序

# 查看支持的字符集完整列表
show character set;

# 查看支持校对的完整列表
show collation;

# 查看所用的字符集
show variables like 'character%';

# 查看所用的校对
show variables like 'collation%';

建表时指定字符集和校对:

create table mytable
(	column1 int,
	column2 varchar(10)
)
default character set hebrew
collate hebrew_general_ci;

MySQL还允许对每个列设置字符集和校对:

# 对整个表以及一个特定的列指定了CHARACTER SET和 COLLATE。 
create table mytable
(
	column1 int,
	column2 varchar(10),
	column3 varchar(10) character set latin1 collate latin1_general_ci
)
default character set hebrew
collate hebrew_general_ci;

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

1 2	select * from customers order by last_name,first_name collate latin1_general_ci;

COLLATE还可以用于GROUP BY、HAVING、聚集函数、别名等。

第二十八章:安全管理

数据库服务器通常包含关键的数据，确保这些数据的安全和完整需要利用访问控制。
本章将学习MySQL的访问控制和用户管理。

管理用户

# 获得所有用户账号列表
use mysql;
select user from user;
/*
+---------------+
| user          |
+---------------+
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
*/

创建用户账号

# 创建一个密码为p@$$w0rd的用户ben
create user ben identified by 'p@$$w0rd';

# 重命名用户
rename user ben to bforta;

# 删除用户帐号
drop user bforta;

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

为看到赋予用户账号的权限，使用SHOW GRANTS FOR

1	show grants for bforta;

为设置权限，使用GRANT语句。GRANT要求你至少给出以下信息：

要授予的权限；
被授予访问权限的数据库或表；
用户名。

GRANT的反操作为REVOKE，用它来撤销特定的权限。

1 2	# 取消用户bforta对crashcourse数据库的SELECT访问权限 revoke select on crashcourse.* from bforta

更改口令
使用SET PASSWORD语句

# SET PASSWORD更新用户口令。新口令必须传递到Password()函数进行加密。 
set password for bforta = Password('n3w p@$w0rd');

# 在不指定用户名时，SET PASSWORD更新当前登录用户的口令。 
set password = Password('n3w p@$$w0rd')

第二十九章:数据库维护

本章学习如何进行常见的数据库维护

备份数据:

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

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

进行数据库维护

ANALYZE TABLE，用来检查表键是否正确。

analyze table less_table;
/*
+----------------------------+---------+----------+----------+
| Table                      | Op      | Msg_type | Msg_text |
+----------------------------+---------+----------+----------+
| iremenberspider.less_table | analyze | status   | OK       |
+----------------------------+---------+----------+----------+
*/

CHECK TABLE用来针对许多问题对表进行检查。

check table less_table,more_table;
/*
+----------------------------+-------+----------+----------+
| Table                      | Op    | Msg_type | Msg_text |
+----------------------------+-------+----------+----------+
| iremenberspider.less_table | check | status   | OK       |
| iremenberspider.more_table | check | status   | OK       |
+----------------------------+-------+----------+----------+
*/

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

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

诊断启动问题

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

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

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

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

第三十章:改善性能

本章将复习与MySQL性能有关的某些要点。

如果你遇到显著的性能不良，可使用SHOW PROCESSLIST 显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）
总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。
使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
一般来说，存储过程执行得比一条一条地执行其中的各条MySQL 语句快。
决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）
有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作
必须索引数据库表以改善数据检索的性能。
SELECT语句不要使用复杂的OR条件,通过使用多条 SELECT语句和连接它们的UNION语句.
LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE

附录:数据类型

1555052329430

1555052347335

1555052366209

1555052379173

MySQL必知必会2