Mysql必知必会

Mysql必知必会

第一章

1.1数据库基础

• 数据库（database）保存有组织的数据的容器
• 表（table）某种特定类型数据的结构化清单
• 模式（schema）关于数据库和表的布局及特性的信息
• 列（column）表中的一个字段。所有表都是由一个或多个列组成的
• 数据类型（datatype）所容许的数据的类型。每个表列都有相应的数据类型。
• 行（row）表中的一个记录（亦称数据库记录record ）
• 主键（primary key）一列（或一组列），其值能够唯一的区分表中的每个行

1.2 什么是SQL

SQL(Structured Query Language)结构化的查询语言

• DDL:数据定义语言
  * create,alter,drop…
• DML:数据操纵语言
  * update,insert,delete
• DCL:数据控制语言
  * grant,if…
• DQL:数据查询语言
  * select

第二章MySQL简介

2.1什么是MySQL

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展。

• 一种DBMS,一种数据库软件
• 使用原因（源码开放、MYSQL执行快、可信赖、简单）
• 客户机-服务器软件
• mysql版本
  4------InnoDB引擎，增加事务处理，改进全文检索
  5------存储过程、触发器、游标、视图
  5.7-----默认使用InnoDB引擎，高并发性能更佳

2.2 MySQL工具

第三章使用MySQL

3.1 连接

• 主机名，如果本地为localhost
• 端口，默认3306
• 一个合法的用户名
• 用户口令

3.2 选择数据库

• use DatabaseName;
  use语句并不返回任何结果。使用use打开数据库，才能读取其中的数据

3.3了解数据库和表

1. 显示数据库信息，返回可用数据库的一个列表

• SHOW DATABASES;

2. 返回当前选择的数据库内可用表的 列表

• SHOW TABLES;

3. 显示表列

• SHOW COLUMNS FROM TABLEName
  快捷方式 DESCRIBE TABLEName;

4. 显示广泛的服务器状态信息

• SHOW STATUS;

5. 显示授予用户的安全权限

• SHOW GRANTS;

6. 显示服务器错误或警告消息

• SHOW ERRORS; SHOW WARNINGS;
  可以执行 HELP SHOW; 命令查询允许的show 命令

自动增量：自动为每行分配下一个可用的编号

第四章 检索数据

• SELECT 语句
• 检索单列 select columnName form tableName;
• 检索多列 select columnName1， columnName2， form tableName;
• 检索所有列 select * from tableName;
  此处* 为通配符，能检索出名字未知的 列
• 检索不同的行 select distinc columnName from tableName;
• 限制结果

1. select distinc columnName from tableName limit 5; 返回不多于5行
2. select distinc columnName from tableName limit 3,5; 从第3行开始的5行

检索出来的第一行行号是0不是1
limit 中从指定行返回行数时，若结果行数不够，则返回能查到的所有
limit 3,4 释义：从行3开始的4行

• 使用完全限定的表名 select tableName.columnName from tableName ;

第五章 排序检索数据

1. 排序数据

• order by columnName

2. 按多个列排序

• order by columnName1,columnName2

3. 指定排序方向
   DESC 降序排列
   ASC 升序排列（默认）

第六章 过滤数据

1. 使用where 子句
2. where子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	两值之间

3. 空值检查
   select name from user where name is null;

第七章

1. 组合where 子句
   操作符：用来联结或改变where子句中的子句的关键字，也称逻辑操作符

• and 操作符
• or 操作符
• 计算次序 圆括号有较高于and 或or 操作符的计算次序
  select name,age from user where (id=1001 or id = 1003) and sex = ‘男’

2. IN操作符
   select name,age from user where id in(1001,1003);

• IN操作符更清晰
• 计算次序更易管理
• IN操作符比OR操作符执行更快
• 可以包含其他select语句

3. NOT 操作符
   MySQL 支持 Not 对IN 、between和 exists 子句取反

第八章 使用通配符进行过滤

1. like 操作符

搜索模式：由字面值、通配符或两者组合构成的搜索条件

• 百分号% 通配符 ，区分大小写
  注意尾空格
  注意NULL，%不匹配NULL

• 下划线（_）通配符
  用途与%类似，但只匹配单个字符

2. 使用通配符技巧

• 不要过度使用通配符
• 不要把通配符置于搜索模式的开始处，搜索起来最慢
• 注意通配符的位置，放错可能得不到想要的结果

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

1. 基本字符匹配

• 匹配 name 包含 文本周字的
  where name REGEXP ‘周’
• 匹配任意字符
  where name REGEXP ‘.000’
  这里 正则表达式 .000。. 是正则表达式语言中的一个特殊的字符。类似like

like与 REGEXP区别：like匹配的文本在列值中出现，like将不会找到他，相应的行不会返回，除非用通配符。REGEXP则会返回

正则表达式不区分大小写

2. 进行OR匹配
   where name REGEXP ‘1000|1003’

3. 匹配几个字符之一
   where name REGEXP ‘[abc] jeck’
   [abc] 定义一组字符，匹配a或b或c jeck,与or类似

4. 匹配范围
   where name REGEXP ‘[1-5] Jeck’

5. 匹配特殊字符
   where name REGEXP ‘.’

• .匹配任意字符，因此每个行都被检索出来
• 为了匹配特殊字符，必须用\为前导。
• \-表示查找-，\.表示查找.
• \也用来引用元字符（具有特殊含义的字符）

元字符	说明
\f	换页
\n	换行
\r	回车
\t	制表
\v	纵向制表

*为了匹配反斜杠()字符本身，就需要用\*

6. 匹配字符类
   

7. 匹配多个实例

elect prod_name from products where prod_name regexp ‘\([0-9) sticks?\)’ order by prod_name;

正则表达式\([0-9) sticks?\)，[0-9]匹配任意字符，sticks？匹配stick和sticks（s后的？使s可选）

select prod_name from products where prod_name regexp ‘[[:digit:]]{4}’ order by prod_name;

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

8. 定位符

第十章 创建计算字段

1. 计算字段
   字段：基本上与列的意思相同
2. 拼接字段
   Concat()
   Rtrim()
   使用别名： as
3. 执行算术计算
   加减乘除

第十一章 使用函数处理

1. 函数
   函数没有SQL的可移植性强：不同数据库函数略有差异

2. 使用函数

• 处理文本串的文本函数
• 在数据上进行算术操作的数值函数
• 处理日期和时间并从中提取特定成分的日期和时间函数
• 返回DBMS正使用的特殊信息的系统函数

1. 文本函数
   

2. 日期和时间处理函数
   

*如果要的是日期，请使用Date()

3. 数值处理函数
   一般用于代数、三角函数或几何运算

第十二章 汇总数据

1聚集函数

运行在行组上，计算和返回单个值的函数

• AVG() 函数只用于单列

• COUNT() 函数
  ** count(*) :对表中的行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值 **
  ** count(column)对特定列中具有值的行进行计数，忽略Null值 **

• MAX()函数 可以返回任意列的最大值，包括返回文本列中的最大值，忽略列值为NULL的行

• MIN() 函数 可以返回任意列的最小值，包括返回文本列中的最小值，忽略列值为NULL的行

• SUM() 可以执行多个列上的计算，忽略列值为NULL的行

2聚集不同值

ALL为默认，只包含不同值，指定DISTINCT

3组合聚集函数

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章 分组数据

1、数据分组

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算

2、创建分组

• group by子句可以包含任意数目的列

• 如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总

• group by子句列出每个列都必须是检索列或有效表达式（不能是聚集函数）

• 如果有null，null作为一个分组返回

• group by子句必须出现在where子句之后，order by子句之前

• 除聚集计算语句外，select语句中的每个列都必须在group by 子句中给出

3、过滤分组

• having子句
  HAVING和WHERE的区别：where在分组前过滤，HAVING 在分组后过滤

4 、 分组和排序
一般使用GROUP BY 子句时也给出ORDER BY子句

5 、select子句顺序

• select from where group by having order by limit

第14章 使用子查询

1、子查询

即嵌套在其他查询中的查询

2、利用子查询进行过滤

3、作为计算字段使用子查询

第15章 联结表

1、联结

联结表就是能在数据检索查询的执行中联结表

（1）关系表

• 关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值互相关联
• 外键：外键为某个表中的一列，它包含了另一个表的主键值，定义了两个表之间的关系

可伸缩性scale：能够适应不断增加的工作量而不失败。

2、创建联结

笛卡尔积：由没有联结条件的表关系返回为结果为笛卡尔积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

1. where 联结
2. 内部联结

返回和前面帘子完全相同的数据，用inner join指定
3) 联结多个表

select  p_name,v_name,p_price,quantity
from  a,b,c
where a.id = b.id
and  b.id = c.id 
and  order_num  = 2005;

性能考虑：这种处理可能非常的耗性能，因此不要联结不必要的表，联结越多性能下降越厉害

第16章 创建高级联结

1、使用表别名，缩短SQL语句，允许在单挑select语句中多次使用相同的表

应当注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机

2、使用不同类型的联结

1. 自联结
2. 外部联结
   用来包含没有关联行的那些行

• 左外联结 left outer join
• 右外联结 right outer join

3、使用带聚集函数的联结

select customers.cust_name, 
custome.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;

4、使用联结和联结条件

关于联结及其使用的要点：

• 注意使用的联结类型，一般用内部联结，但使用外部联结也是有效的
• 使用正确的联结条件
• 应该总是提供联结条件，否则会得出笛卡尔积
• 一个联结中 可以包含多个表。需测试性能

####第17章 组合查询
1、组合查询

允许执行多个查询（多条select语句），并将结果作为单个查询结果集返回，通常称作并（union）或复合查询(compound query)

有两种基本情况，其中需要使用组合查询：

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

2、创建组合查询
UNION规则：

• UNION中的么个查询必须包含相同的列，表达式或聚集函数（各个列的次序可以不同）

• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换的类型

• UNION会自动包含或取消重复的行，使用UNION ALL就是不取消重复的行

• 对组合查询结果排序，用UNION组合查询时，只能使用一条order by子句

第18章 全文本搜索

并非所有引擎都支持全文本搜索：最常使用的引擎MyISAM和InnoDB,前者支持后者不支持。

1、使用全文本搜索

• 为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断的重新索引。
• Match()和Against()一起使用以实际执行搜索

1. 启用全文本搜索支持

create  table   aaaa(
name   varchar(20)   not  null  auto_increment,
age      
)

• 一般在创建表时启用全文本搜索,create table 接受FULLTEXT 子句
• mysql 会自动维护该索引
• 可以在创建表的时候指定FULLTEXT，或者稍后指定

不要在导入数据时使用FULLTEXT：更新索引要花时间

2. 进行全文本搜索

• 其中Match()指定被搜索的列，Against()指定要使用的搜索表达式

select note_text from productnotes where Match(note_text) Against('rabbit');

• 传递给Match()的值必须与FULLTEXT()定义中的相同，而且数量和次序正确，

• 搜索不区分大小写，除非BINARY

• 与LIKE相比，这种方式有良好程度的排序

3. 使用查询扩展

select note_text from productnotes where Match(note_text) Against('anvils' with query expansion);

4. 布尔文本搜索

第19章 插入数据

• 插入完整的行
  这种写法每个列必须提供一个值，没有的应使用NUL
  虽然简单，但不安全，应避免使用

insert into customers values(null, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', null, null);

完整的insert语句，输入对应的列名

insert into customers(cust_name, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) 
values('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', null, null);

如果列中允许null或者给出默认值，可以省略列

• 插入多行

insert into customers(cust_name, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email) 

values('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', null, null),
           ('Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', null, null);

• 插入检索出的数据
  insert的另一种形式，利用它将一条select语句的结果插入表中
  例如：
  insert into customers(cust_contact, cust_email, cust_name, cust_adress, cust_city, cust_state, cust_zip, cust_country)

select cust_id, cust_email, cust_name, cust_adress, cust_city, cust_state, cust_zip, cust_country from custnew;

第20章 更新和删除数据

1、更新数据UPDATE

• 更新表中特定行 使用where限定行
• 更新表中所有的行

客户10005现在有了电子邮件地址，需要更新他的记录

update customers set cust_emailn = ‘[e-mail]’ where cust_id = 10005;

更新多个列

update customers set cust_emailn = ‘[e-mail]’, cust_name = ‘The Fudds’

where cust_id = 10005;

如果要删除某一列的值，可设置它成为null（表定义允许为null）

2、删除数据

• 从表中删除特定的行 where限定行

• 从表中删除所有行

只删除客户10006

delete from customers where cust_id = 10006;

delete语句从表中删除行，甚至所有行，而不是表本身

更快的删除可以使用truncate table语句，实际是删除原来的表并重建创建一个表，而不是逐行删除表中数据

3、更新和删除的指导原则

• 除非确定更新或修改所有行，否则不要使用不带where子句的update 或 delete 语句
• 保证每个表都有主键
• 对update 或delete 语句使用where子句前应该先使用select 测试，保证过滤的正确性
• 谨慎使用

第21章 创建和操纵表

1、创建表

表创建基础

create table  if not exists customers(
  cust_id  int  not null auto_increment  comment  '主键id',
    
  cust_name char(50) not null,

  cust_adress char(50) not null,

  cust_city char(50) not null,

  cust_state char(5) not null,

  cust_zip char(10) not null,

  cust_country char(50) not null,

  cust_contact char(50) not null,

  cust_email char(255) default  null  comment  '邮箱',

  primary key(cust_id) USING BTREE

)Enging = InnoDB auto_increment=1  default  charset=utf-8  comment='客户表';

• if not exists 判断表是否存在，存在则不创建
• 使用null值的合理性，不要混淆null 与 空串
• 使用auto_increment自增长，自增长值可用select last_insert_id()函数获取
• default 指定默认值
• primary key 指定主键
• Enging 引擎设置 InnoDB是事务引擎（不支持全文本搜索），MyISAM 性能极高，支持全文本搜索，不支持事务
• auto_increment=1 指定起始的***值
• USING BTREE MYSQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关；你说的这个是用BTREE来创建索引，提高查询效率
• charset=utf-8 设置表utf-8编码
• comment 字段名/表名注释

与大多数DBMS不一样，Mysql 不允许使用函数作为默认值
外键不能跨引擎

2、更新表

• 更改表结构，添加一个列
  alter table vendors add vend_phone char(20);

• ALTER TABLE 一种常用的用途是定义外键

alert table 表名 add constraint FK_ID foreign key(外键字段名) references 外表表名(主键字段名)

3、删除表

drop table customers2;

4、重命名表
rename table customers2 to customers;

5、 删除外键约束
alter table 表名 drop foreign key 外键名;

6、外键关联表联合删除

建立外键是为了保证数据的完整和统一性，但如果主表中的数据被删除或修改，从表中的数据该怎么办？很明显应该删除，否则数据库中会存在很多无意义的垃圾数据。为此，MySQL可以在建立外键时添加ON DELETE或ON UPDATE子句来告诉数据库，怎样避免垃圾数据的产生。

alter table 表名 add constraint FK_ID foreign key (外键字段名) references 外表表名 (主键字段名)
[on delete {cascade | set null | no action| restrict}]
[on update {cascade | set null | no action| restrict}]

第22章 使用视图

1、视图

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

1. 为什么使用视图

• 重用SQL
• 简化复杂的sql
• 使用表的组成部分而不是整个表
• 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限
• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据

2. 视图的规则和限制

• 与表名一样，视图必须唯一命名
• 可创建的视图数目没有限制
• 必须有足够的权限
• 视图可嵌套
• order by 可用在视图，但如果从该视图检索数据select中也含有order by,那视图的将被覆盖
• 视图不能索引，也不能有关联的触发器或默认值
• 视图可以和表一起使用

2、使用视图

• create view 语句创建
• 使用show create view viewname; 查看创建视图的语句
• 用Drop删除视图， DROP VIEW VIEWNAME;
• 更新视图，可以先删再建，也可以 create or replace view

1. 利用视图简化复杂的联结

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的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。

检索订购了产品TNT2的客户

select cust_name, cust_contact from productcustomers where prod_id = 'TNT2';

2. 用视图重新格式化检索出的数据
   视图的另一种常见用途是重新格式化检索出的数据

create view vendorlocations as 
select contact(RTrim(vend_name), '(', RTrim(vend_country), ')') as vend_title from vendors order by vend_name;

select * from vendorlocations;

3. 用视图过滤不想要的数据
   过滤没有电子邮件地址的客户

create view customeremaillist as
select cust_id, cust_name, cust_email from customers where cust_email is not null;
select * from customeremaillist;

4. 使用视图与计算字段

create view orderitemsexpanded as 

select order_num prod_id, quantity, item_price, quantity*item_price as expanded_price from orderitems;

select * from orderitemsexpanded where order_num = 20005;

5. 更新视图
   视图是可以更新的，可以对它们使用insert、update和delete

但是不是所有视图都可以更新的，如果视图定义中有以下操作，则不能进行视图的更新

• 分组（使用group by 和 having）
• 联结
• 子查询
• 并
• 聚集函数
• distinct
• 导出列

第23章 使用存储过程

1、存储过程

存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合，可将其视为批文件，虽然它们的作用不仅限于批处理

2、为什么要使用存储过程

• 简化复杂操作
• 不要求反复建立一系列处理步骤，确保了数据的完整性
• 简化变动的管理
• 提高性能

存储过程不仅仅适用于大型项目，对于中小型项目，使用存储过程也是非常有必要的。其威力和优势主要体现在：

• 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

• 当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。

• 存储过程可以重复使用,可减少数据库开发人员的工作量。

• 安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

存储过程的缺点

• 调试麻烦

• 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。

• 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。

• 如果在一个程序系统中大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的。维护起来更加麻烦！

3、使用存储过程
语法 ：

create  procedure   存储过程名(参数列表)
BEGIN

      存储过程体（一组合法有效的sql语句）

END

注意：
1、参数列表包含三部分
参数模式 参数名 参数类型
举例：
IN stuname VARCHAR(20)

参数模式：
IN ：该参数可以作为输入，即该参数需要调用方传入值
OUT ：该参数可以作为输出，即该参数可以作为返回值
INOUT ：该参数可以作为输入和输出，即该参数需要调用方传入值和返回接收的值

2、如果存储过程体仅仅只有一句话，BEGIN END 就可以省略

• 存储过程体中的每条SQL语句的结尾要求必须加分号
• 存储过程的结尾可以使用 DELIMITER 重新设置，语法： DELIMITER $$

1. 执行存储过程

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

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

2. 创建存储过程
   一个返回平均价格的存储过程

create  procedure productpricing()
begin
         select avg(prod_price) as priceaverage
         from products;
end;

使用这个存储过程

call productpricing();

3. 删除存储过程
   drop procedure p1;

4. 使用参数

create procedure productpricing(

        out pl declimal(8,2),

        out ph declimal(8,2),

        out pa declimal(8,2)
)

begin

         select min(prod_price)  into pl  from products;

         select max(prod_price)  into ph  from products; 

         select avg(prod_price)  into pa  from products;

end;

in传递给存储过程、out从存储过程传出和inout对存储过程传入和传出

5. 检查存储过程

• show create procedure myp1; 存储过程名; 查看指定存储过程
• show procedure status; 查看所有的存储过程

-带IN模式的存储过程

• 带OUT模式的存储过程

#  案例 ：根据女神名，返回对应的男神名
CREATE PROCEDURE   myp5(IN  beautyName   VARCHAR(20), OUT  boyName   VARCHAR(20))
BEGIN
        SELECT  bo.boyName   INTO    boyName
        FROM    boys   bo
        INTER  JOIN   beauty   b  ON   bo.id  =  b.boyfriend_id
        WHERE   b.name = beautyName;
END  $$ 

#调用
--  set   @bName$$   # 设置接收变量
CALL   myp5('小昭',@bName)$$     #变量也可以不定义直接使用
SELECT   @bName$      # 查看结果

#  案例 ：根据女神名，返回对应的男神名和男神魅力值
CREATE PROCEDURE   myp6(IN  beautyName   VARCHAR(20), OUT  boyName   VARCHAR(20),OUT   userCP   INT)
BEGIN
        SELECT  bo.boyName  , bo.userCP    INTO    boyName,  userCP  # 注意多返回值语法
        FROM    boys   bo
        INTER  JOIN   beauty   b  ON   bo.id  =  b.boyfriend_id
        WHERE   b.name = beautyName;
END  $$ 

#调用
--  set   @bName$$   # 设置接收变量
CALL   myp5('小昭',@bName,@userCP)$$     #变量也可以不定义直接使用
SELECT   @bName,@userCP$$      # 查看结果

• 带INOUT模式的存储过程

#案例：传入a和b两个值，最终a和b  都翻倍并返回
CREATE   PROCEDURE  myp8(INOUT  a  INT ,  INOUT   b   INT )
BEGIN
          SET  a=a*2;
          SET  b=b*2;

END  $$

#  定义传入的变量
set    @m=10$$
set    @n=20$$

#调用
call   myp8(@m, @n)$$
#查看
select   @m,@n$$

第24章 使用游标

1、游标
有时、需要在检索出来的行中前进或后腿一行或多行，这就需要使用游标。游标（cursor）是一个存储在MySQL服务器上的数据库查询，他不是一条select语句 ,而是被该语句检索出来的结果集。在存储了游标后，应用程序可以根据需要滚动或浏览其中的数据。

游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改。

MySQL游标只能用于存储过程（和函数）

2、使用游标

• 使用前必须声明。这个过程其实没有检索数据，只是定义要使用的select语句
• 一旦声明，必须打开游标供使用。
• 对于填有数据的游标，根据需要取出（检索）各行
• 在结束游标使用时，必须关闭游标。

1. 创建游标

create procedure processorders()

begin

         declare ordernumbers cursor

         for

         select order_num from orders;

         open ordernumbers;

         close ordernumbers;

end;

## decalre语句用来定义和命名游标，这里为  ordernumbers
## 存错过程处理完成后，游标就消失了（因为它局限于存储过程）

2. 打开和关闭游标
   open ordernumbers;

• 处理open 语句时，存储检索出的数据供浏览和滚动
  close ordernumbers;
• close 释放游标使用的所有内部内存和资源，光标不用就关闭，如果不明确关闭，mysql在到达END语句时会自动关闭

3. 使用游标数据
   用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;

第25章 使用触发器

MySQL语句在需要时被执行，存储过程也是如此。但是，如果想要某条语句在事件发生时自动执行，需要使用触发器

2、创建触发器

创建触发器时需给出信息：

• 唯一的触发器名
• 触发器关联的表
• 触发器应该响应的活动（delete/insert或update）
• 触发器何时执行

create   trigger   newproduct   after   insert  on  products
for  each   row   select   'Product   added';

注意：

• 触发可以在操作之前或之后
• 只有表才支持触发器，视图不支持，临时表也不支持
• 每个表每个事件每次只允许一个触发

3、删除触发器
drop trigger newproduct;

4、使用触发器

• 在insert触发器代码中，可以引用一个名为NEW的虚拟表

create trigger neworder after insert on orders for each row select new.order_num into @order_num;
insert into orders(order_date,cust_id) values (now(),10001);
select @order_num;

• 在delete触发器代码内，可以引用一个名为OLD的虚拟表

# 使用OLD保存将要被删除的行到一个存档表中 
delimiter //
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); # 引用一个名为OLD的虚拟表，访问被删除的行
end //
delimiter ;

• update触发器

# 在更新vendors表中的vend_state值时，插入前先修改为大写格式 
create trigger updatevendor before update on vendors 
for each row set new.vend_state = upper(new.vend_state);
# 更新1001供应商的州为china
update vendors set vend_state = 'china' where vend_id =1001;
# 查看update后数据，1001供应商对应的vend_state自动更新为大写的CHINA
select * from vendors;

第26章 管理事务处理

1、事务处理

并非所有引擎都支持事务处理

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

2、控制事务处理

• 使用rollback

# 开始事务及回退 
select * from ordertotals;   # 查看ordertotals表显示不为空
start transaction;           # 开始事务处理 
delete from ordertotals;     # 删除ordertotals表中所有行
select * from ordertotals;   # 查看ordertotals表显示 为空
rollback;                     # rollback语句回退 
select * from ordertotals;   # rollback后，再次查看ordertotals表显示不为空

• 使用commit
  隐含提交：提交（写或保存）操作是自动进行的，但在事务处理中，提交不会隐含的进行，需要commit语句提交

start transaction;
delete from orderitems where order_num = 20010;
delete from orders where order_num = 20010;
commit;   # 仅在上述两条语句不出错时写出更改 

• 使用保留点
  rollback 和commit 可以写入或撤销整个事务处理。更复杂的事务处理可能需要部分提交或回退

# savepoint 保留点 
# 创建保留点
savepoint delete1;
# 回退到保留点 
rollback to delete1;
# 释放保留点 
release savepoint delete1;

##mysql默认是自动提交所有更改
## 更改默认的提交行为 
set autocommit = 0;  # 设置autocommit为0（假）指示MySQL不自动提交更改,只针对每次连接设置，而不是服务器

第27章 全球化和本地化

1、字符集和校对顺序
不同的语言和字符集需要以不同的方式存储和检索。

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

# 查看所支持的字符集完整列表
show character set;
# 查看所支持校对的完整列表,以及它们适用的字符集
show collation;
# 确定所用系统的字符集和校对
show variables like 'character%';
show variables like 'collation%';
# 使用带子句的CREATE TABLE，给表指定字符集和校对
create table mytable
(
    column1 int,
    column2 varchar(10)
) default character set hebrew 
  collate hebrew_general_ci;
# 除了能指定字符集和校对的表范围外，MySQL还允许对每个列设置它们
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;
# 校对collate在对用ORDER BY子句排序时起重要的作用
# 如果要用与创建表时不同的校对顺序排序,可在SELECT语句中说明 
select * from customers order by lastname,firstname collate latin1_general_cs;

第28章 安全管理

1、访问控制
尽可能创建一系列不同用途的账号供用户使用，不要轻易使用root账户

2、管理用户
Mysql用户账号信息存储在名为mysql的数据库中

-- 创建用户账号 
# 使用create user
create user ben identified by '[e-mail]$$w0rd';
# 重命名一个用户账号
rename user ben to bforta;
# 删除用户账号 
drop user bforta;
# 查看赋予用户账号的权限
show grants for bforta;
# 允许用户在（crashcourse数据库的所有表）上使用SELECT，只读
grant select on crashcourse.* to bforta;
# 重新查看赋予用户账号的权限，发生变化 
show grants for bforta;
# 撤销特定的权限
revoke select on crashcourse.* from bforta;
# 简化多次授权
grant select,insert on crashcourse.* to bforta;

-- 更改口令

# 原来课本中使用的password()加密函数，在8.0版本中已经移除 
# password() :This function was removed in MySQL 8.0.11.
set password for bforta = 'n3w [e-mail]$$w0rd';  


-- 如果不指定用户名，直接修改当前登录用户的口令 
set password = 'n3w [e-mail]$$w0rd';

grant和revoke可在几个层次上控制访问权限

• 整个服务器，使用grant all和revoke all;
• 整个数据库，使用on database.*;
• 特定的表，使用on database.table;
• 特定的列
• 特定的存储过程

第29章 数据库维护

# 分析表 键状态是否正确
analyze table orders;
# 检查表是否存在错误 
check table orders,orderitems;
check table orders,orderitems quick; # QUICK只进行快速扫描
# 优化表OPTIMIZE TABLE，消除删除和更新造成的磁盘碎片，从而减少空间的浪费
optimize table orders;

1、备份数据
2、数据库维护
3、诊断启动问题
4、查看日志文件