Mysql必知必会

我用漂亮的押韵形容被掠夺一空的爱情

已于 2024-05-12 20:30:11 修改

阅读量261

点赞数

分类专栏：数据库文章标签： mysql

于 2020-07-09 18:15:31 首次发布

本文链接：https://blog.csdn.net/weixin_43854141/article/details/105068427

版权

数据库专栏收录该内容

24 篇文章 0 订阅

订阅专栏

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了解数据库和表

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

SHOW DATABASES;

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

SHOW TABLES;

显示表列

SHOW COLUMNS FROM TABLEName
快捷方式 DESCRIBE TABLEName;

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

SHOW STATUS;

显示授予用户的安全权限

SHOW GRANTS;

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

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;
限制结果

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

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

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

第五章排序检索数据

排序数据

order by columnName

按多个列排序

order by columnName1,columnName2

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

第六章过滤数据

使用where 子句
where子句操作符

操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于

|大于
=|大于等于
BETWEEN|两值之间

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

第七章

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

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

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

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

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

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

like 操作符

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

百分号% 通配符，区分大小写
注意尾空格
注意NULL，%不匹配NULL
下划线（_）通配符
用途与%类似，但只匹配单个字符

使用通配符技巧

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

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

基本字符匹配

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

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

正则表达式不区分大小写

进行OR匹配
where name REGEXP ‘1000|1003’
匹配几个字符之一
where name REGEXP ‘[abc] jeck’
[abc] 定义一组字符，匹配a或b或c jeck,与or类似
匹配范围
where name REGEXP ‘[1-5] Jeck’
匹配特殊字符
where name REGEXP ‘.’

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

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

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

匹配字符类
匹配多个实例

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:]]

定位符

第十章创建计算字段

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

第十一章使用函数处理

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

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

文本函数

concat(last_name,'_',first_name)

upper('join');
lower('Tom');

#字符串截取
substr('姑姑介绍李莫愁给杨过',1,2);

# 返回字符串第一次出现的索引，找不到返回0
instr('姑姑介绍李莫愁给杨过','莫愁')  as   out_put;

# 去除指定字符
select   trim ('aa'  from   'aaaa周aaaaaaa伯aaaaaaaaaaa通') as out_put;

#用左填充指定长度  LPAD     RPAD
select  LPAD('郭襄',10, '*')  as  out_put;

# replace   替换
select   replace('茉莉花',  '茉莉',    '玫瑰')as  out_put;

日期和时间处理函数

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

# now   返会当前系统日期  +  时间
select   NOW();

# curdate   返回当前系统日期，不包含时间
select  curdate();

#curtime   返回当前时间，不包含日期
select    curtime();

#获取指定的部分，年、月、日、小时、分钟、秒
# str_to _date  将日期格式字符串转换成指定格式的日期
select STR_TO_DATE('2000-01-01 12:12:12',  '%Y-%m-%d %H:%i:%s')   as out_put;

#date_format  将日期转换成字符串
select DATE_FORMAT(NOW(),'%y-%c-%d %h:%i:%s')  as out_put;

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

#四舍五入   ROUND
select   ROUND(-1.55);    --    (-2)
select   ROUND(1.567,   2);   --   (1.57)

#向上取整,返回>=该参数的最小整数  CEIL
select    CEIL(1.00);   --  (1)
select    CEIL(1.01);   --  (2)
select    CEIL(-1.01);   --  (-1)

#向下取整   FLOOR  ,返回<=该参数的最大整数
select   floor(-9.99);     --    (-10)

#截断 ,截取小数点后多少位   truncate
select    truncate(1.33339,  1) ;   --  (1.3)

# 取余   mod    ,正负看被除数
#mod(a,b)   :   a-a/b*b
select   mod(10,3);    -- (1)
select   mod(10,-3);    -- (1)
select   mod(-10,3);    -- (-1)
select   mod(-10,-3);    -- (-1)

流程控制函数

if()

case  要判断的字段或表达式
when    常量1    then     要显示的值或语句1;
when    常量2   then     要显示的值或语句2;
...
else   要显示的值或语句;
end

其他函数

select  version();
select  database();
select user();

第十二章汇总数据

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、子查询

即嵌套在其他查询中的查询
分类：
按子查询出现的位置

select 后面
from 后面
where 或 having 后面
exists 后面
按结果集的行列数不同
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

特点：

放小括号中
子查询一般放条件的右侧
标量子查询一般搭配着单行操作符使用 > < >= <= <>
列子查询一般搭配 in any /some all
2、利用子查询进行过滤
3、作为计算字段使用子查询

select   exists(select   name  from  user where id = 666);    --  0/1

select  bo.*
from boys bo
where   not  exists(
      select  boyfriend_id
      from beauty  b
      where   bo.id  =  b.boyfriend_id
);

#列子查询
#查询员工姓名，要求部门号是1列表中的某一个
select  last_name
from employees
where department_id   IN(
          select  distinct    department_id
          from  departments
          where   location_id  IN(1400,  1700)
);


#行子查询
#查询员工编号最小并且工资最高的员工信息
select  *
from   employees
where   (employee_id,salary)=(
          select  min(employee_id),max(salary)
          from   employees
)

第15章联结表

1、联结

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

（1）关系表

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

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

2、创建联结

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

where 联结
内部联结

返回和前面帘子完全相同的数据，用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、使用不同类型的联结

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

左外联结 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、使用联结和联结条件

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

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

补充：

等值连接 a.id = b.id
非等值连接 salary between lower_salary and hight_salary
自连接 from employees a, employees b
外连接 left join right join
全外连接(oracle) FULL OUTER JOIN = 内连接+表1有但表2没有的+表2有但表1没有的
交叉连接 CROSS JOIN 笛卡尔积

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

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

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

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

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

UNION中的么个查询必须包含相同的列，表达式或聚集函数（各个列的次序可以不同）
列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含的转换的类型
UNION会自动包含或取消重复的行，使用UNION ALL就是不取消重复的行
对组合查询结果排序，用UNION组合查询时，只能使用一条order by子句

第18章全文本搜索

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

1、使用全文本搜索

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

启用全文本搜索支持

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

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

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

进行全文本搜索

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

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

传递给Match()的值必须与FULLTEXT()定义中的相同，而且数量和次序正确，
搜索不区分大小写，除非BINARY
与LIKE相比，这种方式有良好程度的排序

使用查询扩展

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

布尔文本搜索

第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 = ‘elmer@fudd.com’ where cust_id = 10005;

更新多个列

update customers set cust_emailn = ‘elmer@fudd.com’, 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_gender  char(1)  check(gender='男'  or  gender = ‘女’),  #检查
  
  cust_seat  int  unique,  #唯一

  cust_country 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 字段名/表名注释
UNIQUE 唯一，可以为空

与大多数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}]

7、主键和唯一的区别

区别项	primary key(主键)	unique（唯一键约束）
唯一性	可以	可以
是否可以为空	不可以	可以
允许个数	只能有1个	允许多个
是否允许多列组合	允许	允许

8、外键

要求在从表设置外键关系
从表的外键列要和主表关联列类型一致或兼容，名称无要求
主表的关联列必须是一个key(一般是主键或唯一)
先插入主表，再插入从表；先删从表，再删主表

#修改表时添加约束
# 表级约束
ALTER   TABLE  stuinfo  ADD  PRIMARY  KEY(id);

#列级约束
ALTER  TABLE   stuinfo  MODIFY   COLUMN  seat  INT  UNIQUE;

#添加外键
ALTER  TABLE  stuinfo   
ADD  CONSTRAINT  fk_stuinfo_majior  FOREIGN  KEY(majorid)  REFERENCES  major(id);  

#修改表时删除约束
...

第22章使用视图

1、视图

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

为什么使用视图

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

视图的规则和限制

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

2、使用视图

create view 语句创建
使用show create view viewname; 查看创建视图的语句
用Drop删除视图， DROP VIEW VIEWNAME;
更新视图，可以先删再建，也可以 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的视图，它联结三个表，以返回已订购了任意产品的所有客户的列表。

检索订购了产品TNT2的客户

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

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

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

select * from vendorlocations;

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

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

select * from customeremaillist;

使用视图与计算字段

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;

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

# 方式一： 
create  or replace  view   视图名
as
查询语句;

#方式二：
alter  view  视图名
as
查询语句;

删除视图

drop view  视图名1，视图名2

查看视图

DESC  myv3;

show  create  view  myv3;

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

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

小插曲-变量

1、系统变量

全局变量
会话变量

# 查看所有全局/会话变量
show global variables;
show  session  variables;

#查看满足条件的部分全局/会话变量
show global variables like  '关键字符';
show  session  variables like  '关键字符';

#查看指定的某个全局/会话变量值
select  @@global.系统变量名;
select  @@session.系统变量名;

#为某个系统变量赋值
set  global /session  系统变量名 = 值;
set  @@global /session  系统变量名 = 值;

2、自定义变量

用户变量
局部变量

第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 $$

执行存储过程

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

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

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

DELIMITER  $$;    重新设置存储过程结尾标识，避免与存储过程体语句分号冲突
create  procedure productpricing()
begin
    select avg(prod_price) as priceaverage
    from products;
end $$

使用这个存储过程

call productpricing();

删除存储过程
drop procedure p1;
使用参数

DELIMITER  $$;    重新设置存储过程结尾标识，避免与存储过程体语句分号冲突
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对存储过程传入和传出

检查存储过程

show create procedure myp1; 存储过程名; 查看指定存储过程
show procedure status; 查看所有的存储过程
创建带in模式的存储过程

# 创建存储过程实现，用户是否登录成功
DELIMITER  $$;    重新设置存储过程结尾标识，避免与存储过程体语句分号冲突
create  procedure   myp4(IN username   VARCHAR(20),IN  password  VARCHAR(20))
BEGIN
         DECLARE  result  INT DEFAULT   0;  #声明并初始化一个变量
         
         SELECT  COUNT(*)  INTO   result   #赋值结果给变量
         FROM    admin
         WHERE   admin.username  =   username
         AND    admin.password   =  password;
                                                                        
        select  IF(result>0,'成功','失败') ;      #利用函数判断，使用
END  $$
                                                                        
# 调用
CALL   myp4('张飞','666')  $$

带OUT模式的存储过程

#  案例 ：根据女神名，返回对应的男神名
DELIMITER  $$;    重新设置存储过程结尾标识，避免与存储过程体语句分号冲突
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$      # 查看结果

#  案例 ：根据女神名，返回对应的男神名和男神魅力值
DELIMITER  $$;    重新设置存储过程结尾标识，避免与存储过程体语句分号冲突
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  都翻倍并返回
DELIMITER  $$;    重新设置存储过程结尾标识，避免与存储过程体语句分号冲突
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语句
一旦声明，必须打开游标供使用。
对于填有数据的游标，根据需要取出（检索）各行
在结束游标使用时，必须关闭游标。

创建游标

create procedure processorders()
begin
         declare ordernumbers cursor
         for
         select order_num from orders;
         open ordernumbers;
         close ordernumbers;
end;

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

打开和关闭游标
open ordernumbers;

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

使用游标数据
用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章管理事务处理

事务：

一个或一组sql语句组成一个执行单元，这个执行单元要么全执行，要么全不执行
事务的创建：
隐式事务：事务没有明显的开启和关闭标记，如：insert、update、delete语句
显示事务：事务具有明显的开启和结束的标记，前提：必须设置自动提交功能禁用set autocommit = 0;

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不自动提交更改,只针对每次连接设置，而不是服务器

3、事务的特性

原子性：事务里面的操作单元不可切割，要么全成功，要么全不成功
一致性：事务执行前后，业务状态和其他业务状态保持一致
隔离性：一个事务执行的时候最好不要受到其他事务的影响
持久性：一但事务提交或回滚，这个状态都要持久化到数据库中

4、事务的并发问题
不考虑隔离性会出现的读问题
概念：

脏读：一个事务读到另外一个事务还没有提交的数据
事务A读取了事务B中尚未提交的数据。如果事务B回滚，则A读取使用了错误的数据。
不可重复读:：在一个事务中,两次查询的结果不一致(针对的update操作)
不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
虚读(幻读)：在一个事务中,两次查询的结果不一致(针对的insert、delete操作)
在事务A多次读取构成中，事务B对数据进行了新增操作，导致事务A多次读取的数据不一致。幻读和不可重复读的区别在于，不可重复是针对记录的update操作，只要在记录上加写锁，就可避免；幻读是对记录的insert操作

5、事务的隔离级别
事务隔离五种级别：

TRANSACTION_NONE 不使用事务。
TRANSACTION_READ_UNCOMMITTED 允许脏读。
TRANSACTION_READ_COMMITTED 防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别
TRANSACTION_REPEATABLE_READ 可以防止脏读和不可重复读，
TRANSACTION_SERIALIZABLE 可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率

6、事务丢失
第一类事务丢失：（称为回滚丢失）

对于第一类事务丢失，就是比如A和B同时在执行一个数据，然后B事务已经提交了，然后A事务回滚了，这样B事务的操作就因A事务回滚而丢失了。

第二类事务丢失：（提交覆盖丢失）

对于第二类事务丢失，也称为覆盖丢失，就是A和B一起执行一个数据，两个同时取到一个数据，然后B事务首先提交，但是A事务接下来又提交，这样就覆盖了B事务，称为第二类事务丢失，覆盖丢失。

7、隔离级别控制，从低到高

1.未提交读(Read uncommitted)。（写加锁，读不加锁）
写操作加写锁，读操作不加锁。禁止第一类丢失更新，但是会出现所有其他数据并发问题。
2.提交读(Read committed)。（写加锁，读加锁）
写操作加写锁，读操作加读锁。禁止第一类丢失更新和脏读。

就是你已经开始读了数据，然后一个事物开始写，然后写的事物不提交的话，是不能进行读的事物，避免了脏读。

oracle支持Read committed和Serializable，默认隔离级别Read committed

3.可重复读(Read repeatable)。（写加锁，读加锁）
对于读操作加读锁到事务结束，其他事务的更新操作只能等到事务结束之后进行。和提交读的区别在于，
提交读的读操作是加读锁到本次读操作结束，可重复读的锁粒度更大。禁止两类丢失更新，禁止脏读和不可重复度，但是可能出现幻读.

一个事物读的时候，我们把两次读看成整体，在读的过程中，不允许写的操作，这样就可以禁止不可重复读。就是两次读操作不允许其他事物。

这是大部分关系数据库的默认隔离级别。Mysql支持4种，默认隔离级别Read repeatable

4.序列化(Serializable)。（对表级读写加锁）
读操作加表级读锁至事务结束。可以禁止幻读。

8、设置查看数据库隔离级别

每启动一个mysql程序，就会获得一个单独的数据库连接，每个数据库连接都有一个全局变量@@tx_isolation,表示当前的事务隔离级别

# 查看数据库隔离级别
select  @@tx_isolation;

# 设置会话数据库隔离级别
set  session   transaction  isolation  level  read  uncommitted;

#设置数据库系统全局的隔离级别
set global   transaction   isolation  level   read   committed;

第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 'p@$$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 p@$$w0rd';  


-- 如果不指定用户名，直接修改当前登录用户的口令 
set password = 'n3w p@$$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、查看日志文件

我用漂亮的押韵形容被掠夺一空的爱情

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
Mysql必知必会

Mysql必知必会第一章1.1数据库基础数据库（database）保存有组织的数据的容器表（table）某种特定类型数据的结构化清单模式（schema）关于数据库和表的布局及特性的信息列（column）表中的一个字段。所有表都是由一个或多个列组成的数据类型（datatype）所容许的数据的类型。每个表列都有相应的数据类型。行（row）表中的一个记录（亦称数据库记录record ）...
复制链接

扫一扫