MySQL必知必会总结

站长

2024年04月07日 09:49 · 阅读数 63

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 过滤数据

MySQL必知必会总结

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开头的行

MySQL必知必会总结

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;

MySQL必知必会总结

11 使用数据处理函数

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

MySQL必知必会总结

# 文本：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 汇总数据

MySQL必知必会总结

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

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

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继续对每个分组汇总；

MySQL必知必会总结

# 分组
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)  # 查询扩展，有该字的行中的其它文字在其它行中也能被检索出来

MySQL必知必会总结

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语句：

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

MySQL必知必会总结

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 数据类型

串数据

MySQL必知必会总结

数值类型

MySQL必知必会总结

日期和时间数据类型

MySQL必知必会总结

二进制数据类型

MySQL必知必会总结

转载自:https://juejin.cn/post/7227029203655508027