MySQL必知必会总结

1 了解SQL

• SQL（发音为字母S-Q-L或sequel）是结构化查询语言（Structured Query Language）的缩写， 专门用来数据库通信的语言

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

• 表（table） 某种特定类型数据的结构化清单

• 列（column） 表中的一个字段，列中存储着表中某部分的信息

• 数据类型（datatype） 所容许的数据的类型

• 行（row） 表中的一个记录

• 主键（primary key），一列（或一组列），其值能够唯一区分表中每个行，用来表示一个特定的行

  • 任意两行都不具有相同的主键值
  • 每个行都必须具有一个主键值（主键列不允许NULL值）

2 Mysql简介

数据的所有存储、检索、管理和处理实际上是由数据库软件——DBMS（数据库管理系统）完成的。MySQL是一种DBMS，即它是一种数据库软件。MySQL是一种DBMS，即它是一种数据库软件。特点如下：

• 成本——MySQL是开放源代码的，一般可以免费使用（甚至可以免费修改）
• 性能——MySQL执行很快（非常快）
• 可信赖——某些非常重要和声望很高的公司、站点使用MySQL
• 简单——MySQL很容易安装和使用

3 使用Mysql

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

• 主机名（计算机名）——如果连接到本地MySQL服务器，为localhost
• 端口（如果使用默认端口3306之外的端口）
• 一个合法的用户名
• 用户口令（如果需要）

在执行任意数据库操作前，需要选择一个数据库。为此，可使用USE关键字。相关命令

• use crashcourse 使用crashcourse库
• show database 可用数据库中的列表
• show tables 数据库内可用表的列表
• show columns from customers 显示列
• show status 用于显示广泛的服务器状态信息
• show create database 显示创建特定数据库mysql语句
• show create table 显示创建特定表的MySQL语句
• show grants 显示授予用户（所有用户或特定用户）的安全权限
• show errors 显示服务器错误
• show warnings 显示警告消息

4 检索

select column from table;
select prod_id, prod_name, prod_price from products;
select * from table;
select distinct column from table;
select column from table limit 5;
select column from table 5,5;   #  从行5开始的5行

5 排序检索数据

默认以数据底层表中出现的顺序展示，不应该假定检索出来的顺序有意义。

select column from table order by column;   # 使用非检索的列排序也是合法的
select column from table order by column, column2;  # 多列排序，只有当第一列不同时，才会使用第二列
select column from table order by column desc;  # 降序排列
select column from table order by column desc, column2; # desc 直接作用它前边的列，多列降序需要每个列都指定 desc
select column from table order by column desc limit 1; # limit 放最后，这里找到最大的值

6 过滤数据

where子句操作符： = , != , <, <=, >, >=, BETWEEN

select name, price from products where price=2;
select id, name from products where id <> 1003;  # 列出不是由供应商1003制造
select name, price from products where price is null;  # 空值 NULL 检查
select name, price from products where price BETWEEN 3 and 5;  # 找到3和5之间，包括3和5

7 数据过滤

组合 where 子句。注意 AND 优先级高于 OR，如果必要应该使用括号，尽量都用括号防止歧义。

select prod_name, prod_price from products where (vend_id=1002 or vend_id=1003) and prod_price>=10
select name, price from products where price > 3 and price < 5;
select name, price from products where price in (3,5);  # in 比使用 or 更快, 更清楚
select name, price from products where price not in (3,5);  # 使用 not 否定条件
select Concat(vend_name, '(', vend_country, ')') from vendors order by vend_name;    # 计算字段
select name, price from products where id=1002 or vend_id=1003;

8 用通配符过滤

LIKE操作符(谓词), 如果配置了区分大小写则也需要注意大小写问题。

# %表示任何字符出现任意次数（包括0次），不会匹配 NULL
select prod_id,prod_name from products where prod_name LIKE 'jet%';   # 将检索任
意以jet起头的词, 注意尾空格可能会干扰通配符
select prod_id,prod_name from products where prod_name LIKE '%anvil%';  # 匹配任何位置包含文本anvil的值

# _ 只能匹配单个字符
select prod_id,prod_name from products where prod_name LIKE '_ ton_anvil';

注意：不要过度使用通配符；尽量不要把通配符放在开始位置；

9 使用正则表达式搜索

mysql 支持正则表达式的子集 REGEXP, LIKE匹配整串，‘.’匹配任意一个字符。Like匹配整列，REGEXP是匹配列中；可以通过BINARY关键字区分大小；匹配特殊字符前必须用\\为前导，即转义；

# 搜索prod_name 包含文本 1000 的所有行
select prod_id, prod_name from products where prod_name REGEXP '1000' order by prod_name;
select prod_name from products where prod_name REGEXP '.000' order by prod_name;  # 匹配任意一个字符
select prod_id, prod_name from products where prod_name REGEXP '1000|2000' order by prod_name; # 搜索两个串之一
select prod_name from products where prod_name REGEXP '[123] Ton' order by prod_name;  # []匹配任何单一字符,1或2或3
select prod_name from products where prod_name REGEXP '[1-5] Ton' order by prod_name; # 匹配1到5;
select vend_name from vendors where vend_name REGEXP '\\.' order by vend_name;  # 匹配'.'
select prod_name from products where prod_name REGEXP '\\([0-9] sticks?\\)' order by prod_name;  # 匹配0-9任意数字，?表示其前出现0次或1次；
select prod_name from products where prod_name REGEXP '[[:digit:]][4]' order by prod_name;  # 匹配连在一起的任意4位数字
select prod_name from products where prod_name REGEXP '^[0-9\\.]' order by prod_name;  # ^匹配串的开始，以数字0-9开头的行

10 创建计算字段

拼接字段： concat()

select concat(vend_name, ' (', vend_country, ')') from vendors order by vend_name;
select concat(vend_name, ' (', RTrim(vend_country), ')') from vendors order by vend_name;  # RTrim/LTrim/TRim 去除空格

别名：使用 as 支持列 别名

select Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title from vendors order by vend_name;

算数计算：对检索出的数据进行算数计算

select prod_id,quantity,item_price, quantity*item_price AS expanded_price from orderitems where order_num=20005;

11 使用数据处理函数

文串本、数值计算、日期处理、系统函数等

# 文本：Left, Length, Locate, Lower, LTrim, Right, RTrim, Soundex(替换为描述语音表示的字母数字模式), SubString, Upper
select vned_name,Upper(vend_name) as vend_name_upcase from vendors order by vend_name;
select cust_name, cust_contact from customers where Soundex(cust_contact) = Soundex('Y lie')  # 匹配所有发音类似于 Y.Lie的联系名

# 日期：CurDate, Date, Day, Hour, Minute, Month, Now, Second, Time, Year
select cust_id,order_num from orders where order_date ='2005-09-01';
select cust_id,order_num from orders where Date(order_date) ='2005-09-01';  # use Date，更准确, 由于一些格式不统一问题(yyyy--mm-dd h:m:s)等

select cust_id,order_num from orders where Date(order_date) BETWEEN '2005-09-01' and '2005-09-30';
select cust_id,order_num from orders where Year(order_date) =2005 and Month(order_date) =9;  # Year()是一个从日期（或日期时间）中返回年份的函数，Month()从日期中返回月份

# 数值：Abs, Cos, Exp, Mod, Pi, Rand, Sin, Sqrt, Tan

12 汇总数据

汇总而不是检索数据，确定行数、获取和、找出最大最小平均值。

五个聚集函数（运行在行组上，计算和返回单个值的函数）:

• avg
• count
• max
• min
• sum

# avg
select avg(price) as avg_price from products;  # avg会忽略列值为 NULL 的行

# count
select count(*) as num_cust from customers;   # count(*)对表中的行数计算，不管包含的是 NULL 还是非空
select count(cust_email) as num_cust from customers;   # count(column) 忽略 NULL 的值

# max、min, 忽略 NULL 值
select max(prod_price) as max_price FROM products;

# sum 指定列值的和
select sum(quantity) as items_ordered from orderitems where order_num = 20005; # ignore NULL
select sum(item_price*quantity) as total_price from orderitems where order_num = 20005;

# distince 聚集不同值
select avg(distinct quantity) as items_ordered from orderitems where order_num = 20005; # ignore NULL

# 组合聚集函数
select count(*) as num_items, min(prod_price) as price_min, max(prod_price) as price_max, avg(prod_price) as price_avg from products;

13 分组数据

group by and having，分组允许把数据分为多个逻辑组，以便能够对每个组进行聚集计算。GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。使用with rollup继续对每个分组汇总；

# 分组
select vend_id, count(*) as num_prods from products group by vend_id;

# 分组汇总
select vend_id, count(*) as num_prods from products group by vend_id with roolup;

# 使用 having 过滤分组，where 过滤行，having 支持所有的where子句条件
select cust_id, count(*) as orders from orders group by cust_id having count(*) >= 2;

# having and where 一起用, ，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤
select cust_id, count(*) as orders from orders where prod_price>=10 group by cust_id having count(*) >= 2;

# order by and group by
select order_num, sum(quantity*item_price) as ordertotal from orderitems group by order_num
having sum(quantity*item_price) >= 50
order by ordertotal;

14 使用子查询

子查询： 在SELECT语句中，子查询总是从内向外处理。

# 利用子查询进行过滤。可以把一条 select 语句返回的结果用于另一条 select 语句的 where 子句
select cust_name, cust_contact
from customers
where cust_id in (select cust_id
                  from orders
                  where order_num in (select
                      order_num from orderitems where prod_id='TNT2'));  # 参考15章使用join 处理


# 作为计算字段使用子查询，相关子查询需要限定列名
select cust_name, cust_state, (select count(*) from orders
                               where orders.cust_id=customers.cust_id) as orders
from customers order by cust_name;

15 联结表

where很重要，不加where联结表是笛卡尔积，检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。要保证所有联结都要有where子句，则MySQL将返回比想要的数据多得多的数据。

# 引用的列可能出现二义性时，必须使用完全限定列名
select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id=products.vend_id order by vend_name,prod_name;

# 内部联结（等值联结）
select vend_name, prod_name, prod_price
from vendors
INNER JOIN products
on vendors.vend_id = products.vend_id;  # 此时on等同于where的条件

# 连接多个表，sql 对一条 select 中的连接的表数目没有限制。先列出所有表，然后定义表之间的关系
select prod_name, vend_name, prod_price, quantity
from orderitems, products, vendors
where prodcuts.vend_id = vendors.vend_id
and orderitems.prod_id = products.prod_id
and order_num = 20005

# 14章的例子使用 join 处理
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';

16 创建高级联结

何时使用表别名？允许单条 select 中多次引用相同的表

自连接：用 as 语句别名

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';

外部联结：联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。 与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。

select customers.cust_id, orders.order_num
from customers
left outer join orders
on customers.cust_id = orders.cust_id

复合查询： 多数SQL查询都只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。 也可以用 or 条件实现相同功能。简化复杂 where

带聚集函数的联结：

select customers.cust_name, customers.cust_id, count(orders.order_num) as num_ord
from customers
inner join orders
on customers.cust_id = orders.cust_id
group by customers.cust_id

17 组合查询

可以用 union 操作符来组合多个 SQL 查询，把结果合并成单个结果集。使用 union 可以使用多个 where 条件替换。重复行会被自动取消，如果需要全部，可以使用union all

# union 必须是相同的列，并且返回的是不重复的行。可以使用 union all 返回所有的行(这个 where 无法完成)
select vend_id,prod_id,prod_price from products where prod_price<=5
union
select vend_id,prod_id,prod_price from products wehre vend_id in (1002,1002);

对组合查询结果排序，末尾还是与之前一样加order by

select vend_id, prod_id, prod_price
from products
where prod_price <=5
union
select vend_id, prod_id, prod_price
from vend_id prodcuts
where vend_id in (1001, 1002)
order by vend_id, prod_price;

18 全文本搜索

启动全文本搜索支持FULLTEXT, 如果需要可以指定多列。

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),
    fulltex(note_text)
) engine=MyISAM;

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

select note_text from productnotes
where match(note_text) Against('rabbit')  # 指定列note_text, 从中搜rabbit

select note_text, Match(note_text) Against('rabbit') As rank
from productnotes;  # 对搜索结果排序，等级由MySQL根据行中词的数目、唯一词的数目、整个索引中词的总数以及包含该词的行的数目计算出来

select note_text from productnotes
where Match(note_text) Against('anvils' with query expansion)  # 查询扩展，有该字的行中的其它文字在其它行中也能被检索出来

select note_text
from productnotes
where Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE)  # 这个搜索匹配包含词rabbit和bait的行

select note_text
from productnotes
where Match(note_text) Against('rabbit bait' IN BOOLEAN MODE)  # 没有指定操作符，这个搜索匹配包含rabbit和bait中的至少一个词的行

select note_text
from productnotes
where Match(note_text) Against ('"rabbit bait"' IN BOOLEAN MODE)  # 这个搜索匹配短语rabbit bait而不是匹配两个词rabbit和bait

select note_text
from productnotes
where Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE) # 这个搜索匹配词safe和combination，降低后者的等级

19 插入数据

插入完整的行；插入行的一部分；插入多行；插入某些查询的结果。insert语句一般不会产生输出

insert into Customers
VALUES(NULL,
       'Pep E. LaPew',
       '100 Main Street',
       'Los Angeles',
       'CA',
       '90046',
       'USA',
       NULL,
       NULL
);  # 如果某个列没有值（如上面的cust_contact和cust_email列），应该使用NULL值，该语句高度依赖于表中列的定义次序，尽量避免使用。

insert into customers(
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country,
    cust_contace,
    cust_email
)
VALUES(
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046',
    'USA',
    NULL,
    NULL
);  # 在表名后的括号里明确地给出了列名, 这种操作表结构即使发生变化也能执行成功

INSERT LOW PRIORITY tableName # 通过加关键字LOW_PRIORITY 降低insert语句的优先级，因为比较耗时

insert into customers(
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
VALUES(
    'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046',
    'USA',
), (
    'M. Martinan',
    '42 Galaxy Way',
    'New York',
    'NY',
    '11213',
    'USA',
);  # 单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔，该技术可以提高数据库处理性能

insert into customers(
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
select cust_id, 
	cust_email,
	cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
from custnew;  # 使用INSERT SELECT从custnew中将所有数据导入customers

20 更新和删除数据

更新（修改）表中的数据，可使用UPDATE语句，更新表中特定行，更新表中所有行，操作一定要小心，不要省略where子句；

update customers
set cust_emial = 'elemer@fudd.com'
where cust_id = 10005;  # UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行。没有WHERE子句，MySQL将会用这个电子邮件地址更新customers表中所有行,慎重

update customers
set cust_name = 'The Fudds', cust_email = 'elemer@fudd.com'
where cust_id = 10005;  # 在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间用逗号分隔

update customers
set cust_email = NULL
where cust_id = 10005;   # 为了删除某个列的值，可设置它为NULL

删除数据，可使用DELETE语句，从表中删除特定的行，从表中删除所有行，操作也要小心，不要省略where子句；

delete from customers
where cust_id = 10006;  # 从customers表中删除一行

DELETE语句从表中删除行，甚至是删除表中所有行。但是，DELETE不删除表本身。如果想从表中删除所有行，不要使用DELETE，可使用TRUNCATE TABLE语句（更快，TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）。

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

21 创建和操作表

利用CREATE TABLE创建表， 给出新表的名字， 表列名和定义，用逗号分隔。

create table customers if not exists
(
    cust_id int not null auto_increment,
    cust_name char(50) not null,
    cust_address char(50) null,
    cust_city char(50) null,
    cust_state char(10) null,
    cust_zip char(10) null,
    cust_country char(50) null,
    cust_contact char(50) null,
    cust_emal char(255) null,
    PRIMARY KEY(cust_id)
) ENGINE=InnoDB;  # 表名紧跟在CREATE TABLE关键字后, 每列的定义以列名开始，后跟列的数据类型,有的加comment, 创建表的时候要先确认表不存在，可以加入if not exists

create table orders if not exists
(
    order_num int not null auto_increment,
    order_date datetime not null,
    cust_id int not null default 1,  # 指定默认值， 而不是NULL列
    PRIMARY KEY(order_num)
)ENGINE=InnoDB;  # 每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。对于含有关键字NOT NULL的列会阻止插入没有值的列。

不要把NULL值与空串相混淆。NULL值是没有值，它不是空串。空串是一个有效的值，它不是无值。

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

PRIMARY KEY(vend_id)  # 单列作为主键

PRIMARY KEY(order_num, order_item)  # 由多个列组成的主键

数据库引擎：

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

外键不能跨引擎 混用引擎类型有一个大缺陷。即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

ALTER TABLE更改表结构, 给出要更改的表名，要更改的列表。

ALTER TABLE vendors
ADD vend_phone Char(20);  # 增加一个vend_phone的列

ALTER TABLE vendors
DROP COLUMN vend_phone;  # 删除vend_phone列

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id)
REFERENCES customers(cust_id)  # 设置外键

删除表及重命名表

DROP TABLE custormers2;  # 删除表

RENAME TABLE customers2 TO customers;  # 将customers2重命名customers

RENAME TABLE backup_customers TO customers,
		backup_vendors TO vendors,
		backup_products TO products;  # 重命名多个表

22 使用视图

视图提供了一种MySQL的SELECT语句层次的封装，可用来简化数据处理以及重新格式化基础数据或保护基础数据。 视图用CREATE VIEW语句来创建。使用SHOW CREATE VIEW viewname；来查看创建视图的语句。用DROP删除视图，其语法为DROP VIEW viewname;。更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。

create view productcustomers as 
select cust_name, cust_contact, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num  # 创建名为productcustomers的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表

select cust_name, cust_contact
from productcustomer
where prod_id = 'TNT2'  # 通过WHERE子句从视图中检索特定数据

create view customeremaillist as
select cust_id, cust_name, cust_email
from customers
where cust_email is not null;   # 用视图过滤不想要的数据, 排除没有电子邮件地址的用户

视图一般是可以更新的，可以对它们使用INSERT、UPDATE和DELETE。如果视图定义中有以下操作，则不能进行视图的更新：

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

23 使用存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句为CALL。

CALL productpricing(@pricelow, @pricehigh, @priceaverage);  # 执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

create procedure productpricing()
begin
	select avg(product_price) as priceaverage
	from products;
end;  # 存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义, 括号里可以传参数

CALL productpricing();  # 执行刚创建的存储过程并显示返回的结果

DROP procedure productpricing;  # 删除刚创建的存储过程, 后无括号

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存储产品平均价格

select @priceaverage;
select @pricelow, @pricehigh, @priceaverage;  # 调用

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

CALL ordertotal(20005, @total);  # 必须给ordertotal传递两个参数；第一个参数为订单号，第二个参数为包含计算出来的合计的变量名

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

24 游标

游标（cursor）是一个存储在MySQL服务器上的数据库查询， 在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。使用游标涉及几个明确的步骤：

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

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

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

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

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

create procedure processorders()
begin
	declare ordernumbers cursor
	for
	select order_num from orders
end;  # DECLARE语句用来定义和命名游标ordernumbers

open ordernumbers;  # 打开游标

close ordernumbers; # 游标处理完成后，应当使用如下语句关闭游标

fetch ordernumber INTO o;  # FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明的变量中

repeat
	fetch ordernumbers into o;
until done end repeat;  # 重复读取所有行，然后关闭游标

repeat
	fetch ordernumbers into o;
	call ordertotal(o, 1, t);
	insert into ordertotals(order_num, total)
	values(o, t);
until done end repeat;  # 增加了另一个名为t的变量, 此存储过程还在运行中创建了一个新表，名为ordertotals。这个表将保存存储过程生成的结果;

25 触发器TRIGGER

某条语句（或某些语句）在事件发生时自动执行，例如：

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

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句：

• DELETE
• INSERT
• UPDATE

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

# 创建触发器
create trigger newproduct after insert on products
for each row select 'product added'  # 创建名为newproduct的新触发器, 触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT， 所以此触发器将在INSERT语句成功执行后执行, 对每个插入航显示一次'product added'

drop trigger newproduct;  # 删除触发器

create trigger neworder after insert on orders
for each row select new.order num;  # 创建insert触发器，名为neworder，它按照AFTER INSERT ON orders执行（after）

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;  # 创建delete触发器，在任意订单被删除前将执行此触发器。它使用一条INSERT语句 将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中（before）

create trigger updatevendor before on vendors
for each row set new.vend_state = upper(new.vend_state);   # 创建update触发器， 每次更新一个行时，NEW.vend_state中的值（将用来更新表行的值）都用Upper(NEW.vend_state)替换（before）

---

26 管理事务处理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。如果没有错误发生，整组语句提交给（写到）数据库表。如果发生错误，则进行回退（撤销）以恢复数据库到某个已知且安全的状态。

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

start transaction;  # 事务开始

delete from ordertotals;
rollback;   # 回退，取消上述的删除操作，不能回退的操作语句select，create，drop操作

delete from orderitems where order_num = 20010;
delete from orderitems where order_num = 20010;
commit;  # 最后的commit语句仅在不出错时写出更改

savepoint delete1;  # 创建保留点，可以在代码中设置任意多的保留点，便于灵活回退， 在事务处理完成后自动释放
rollback to delete1;  # 回退到对应的保留点

set autocommit=0;  # 设置autocommit为0（假）指示MySQL不自动提交更改， 针对的是每个连接而不是服务器

---

27 全球化和本地化

数据库表被用来存储和检索数据。不同的语言和字符集需要以不同的方式存储和检索。

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

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

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

create table mytable
(
  column1 INT,
  column2 VARCHAR(10),
  column3 VARCHAR(10) CHARCTER SET latin1 COLLATE latin1_general_ci
) DEFAULT CHARACTER SET hebrew
COLLATE hebrew_general_ci;  # 对整个表以及一个特定的列指定了CHARACTER SET和COLLATE

select * from customers
order by lastname, firstname
COLLATE latin1_general_cs;  # 此SELECT使用COLLATE指定一个备用的校对顺序

---

28 安全管理

多数用户只需要对表进行读和写，但少数用户甚至需要能创建和删除表；

• 某些用户需要读表，但可能不需要更新表；

• 可能想允许用户添加数据，但不允许他们删除数据；

• 某些用户（管理员）可能需要处理用户账号的权限，但多数用户不需要；

• 可能想让用户通过存储过程访问数据，但不允许他们直接访问数据；

• 可能想根据用户登录的地点限制对某些功能的访问；

第一种方法是使用MySQL Administrator ，提供了一个图形用户界面，可用来管理用户及账号权限；

第二种方法是MySQL创建一个名为root的用户账号，它对整个MySQL服务器具有完全的控制权限；

设置权限可以使用GRANT语句：

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

use mysql;
select user from user;  # 获得所有用户账号列表

create user ben IDENTIFIED BY 'p@ssw0rd';  # 创建新用户账户， 用户为ben， 密码为p@ssw0rd

RENAME user ben to btorta;  # 重命名用户

DROP user btorta;  # 删除用户

show grants for btorta;  # 查看用户被赋予的权限

GRANT SELECT on crashcourse.* to btorata;  # 给btorata赋予查看以crashcourse开头的所有表的select权限

REVOKE SELECT on crashcourse.* fron btorata;  # 撤销btorata对crashcourse开头表的select权限，执行前一定该用户一定要具有该权限，否则执行出错

SET PASSWORD for bforta = Password('new p@ssw0rd');  # 更改用户bforta的密码，在不指定用户时，更新的是当前登录用户的密码

---

29 数据库维护

备份数据：

• 使用命令行实用程序mysqldump转储所有数据库内容到某个外部文件;
• 可用命令行实用程序mysqlhotcopy从一个数据库复制所有数据;
• 可以使用MySQL的BACKUP TABLE或SELECT INTO OUTFILE转储所有数据到某个外部文件;

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

数据维护：

• ANALYZE TABLE，用来检查表键是否正确；
• CHECK TABLE用来针对许多问题对表进行检查;

诊断启动问题：

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

查看日志文件：

• 错误日志，包含启动和关闭问题以及任意关键错误的细节，hostname.err ；
• 查询日志，记录所有MySQL活动，hostname.log ；
• 二进制日志， 记录更新过数据的所有语句，hostname-bin ；
• 缓慢查询日志， 记录执行缓慢的任何查询，hostname-slow.log ；

---

30 改善性能

数据库管理员把他们生命中的相当一部份时间花在了调整、试验以改善DBMS性能之上。以下是一些性能优化探讨和建议：

• 关键的生产DBMS应该运行在自己的专用服务器上
• 如果遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程
• 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法
• 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句
• 存储过程执行得比一条一条地执行其中的各条MySQL语句快
• 应该总是使用正确的数据类型
• 决不要检索比需求还要多的数据
• 有的操作（包括INSERT）支持一个可选的DELAYED关键字
• 在导入数据时，应该关闭自动提交
• 必须索引数据库表以改善数据检索的性能
• 复杂的or可以用SELECT语句和连接它们的UNION语句改写
• 索引改善数据检索的性能，但损害数据插入、删除和更新的性能
• LIKE很慢， 一般来说，最好是使用FULLTEXT而不是LIKE
• 每条规则在某些条件下都会被打破

更多建议可以查看网页dev.mysql.com/doc/

---

Mysql语法

• alter tale, 更新表

alter table tablename
(
    add column datatype null,
    drop column,
)

• commit, 将事务处理写到数据库

commit;

• create index, 创建索引

create index indexname
on tablename (column )

• create procedure, 创建存储过程

create procedure procedurename
begin

end;

• create table, 创建表

create table tablename
(
    column1 datatype null,
    column2 datatype null,
)

• create user, 添加用户

create user username
identified by 'password';

• create view 添加视图

create view viewname
as 
select ...;

• delete, 删除一行或多行

delete from table
where... ;

• drop, 永久的删除数据库对象

drop database|index|procedure|table|trigger|user|view
itemname;

• insert, 增加一行

insert into tablename (column1, column2)
values (value1, value2)

• insert select, 插入select的结果到一个表

insert into table name (columns, )
select columns, ... from table name
where...

• rollback, 撤销一个事务处理块

rollback to savepointname;

• savepoint, 为使用ROLLBACK语句设立保留点

savepoint sp1;

• select, 检索数据

select columnname
from tablename
where
union
group by
having
order by 

• start transaction, 新的事务处理块的开始

start transaction

• uodate 更新表中一行或多行

update tablename
set columnname = value
where

---

Mysql 数据类型

• 串数据

• 数值类型

• 日期和时间数据类型

• 二进制数据类型