数据库"/>
【笔记】MySQL数据库
MySQL笔记
本笔记用于快速查阅和复习MySQL相关的知识和技巧。
附件提供了一些如关键词保留字、用户权限、MySQL下载地址的链接,主要来自官方文档和一些其他的中译
学习来源: b站’CodeWithMosh’中译
学习参考:MySQL官方文档、w3school
推荐书籍:MySQL必知必会,数据库系统概念
文章目录
- *MySQL笔记*
- 第一章: 引言
- 有关SQL
- 数据库(Database)
- 数据库管理系统(DBMS)
- SQL(Structured Query Language)
- 数据库语言
- 用于操作关系型 DBMS
- 快速概览
- 第二章: 数据库操作
- use使用数据库
- select选择语句
- like,regexp运算符
- 第三章: 数据库连接
- 内连接
- 跨数据库连接
- 自连接
- 多表连接
- 复合连接条件
- 隐式连接条件
- 外连接
- 多表外连接
- 自外连接
- using子句
- 自然连接
- union | 联合
- 第四章: 数据操作
- 列属性
- Insert插入行
- 插入分层行
- 创建表复制
- 更新行
- 从表中删除
- 恢复数据库
- 第五章: 聚合和分组
- 聚合函数
- group by子句
- hving子句
- rollop运算符
- 第六章: 子查询和条件
- 子查询
- in运算符
- distinct关键字
- 子查询vs连接
- all,any关键字
- 相关子查询(好用废时)
- exists运算符
- 子句中的子查询
- 第七章: 函数
- 数值函数
- 字符串函数
- 日期和时间函数
- 日期和时间格式化
- 时间日期计算
- ifnull和coalesce函数
- if函数
- case分支函数
- 第八章: 视图
- 创建视图
- 更改或删除视图
- 可更新视图
- with option check子句
- 视图的优点
- 第九章: 存储过程和函数
- 什么是存储过程
- 创建和调用存储过程
- MySQL工作台创建存储过程
- 删除存储过程
- 带参数的存储过程
- 参数验证,错误代码
- 输出参数
- 变量
- 函数
- 其他约定
- 第十章: 触发器和事件
- 触发器
- 查看触发器
- 删除触发器
- 使用触发器进行审计
- 事件和查看系统变量
- 创建、查看、删除和修改事件
- 第十一章: 事务和并发
- 事务
- 创建事务
- 并发
- 事务隔离等级
- 死锁
- 第十二章: 数据类型
- 字符串类型(String Types)
- 整数类型(Numeric Types)
- 定点数和浮点数类型(Date and Time Types)
- 布尔类型(Boolean Types)
- 枚举和集合类型(Enum and Set Types)
- 时间和日期类型(Date and Time Types)
- 二进制类型(Blob Types)
- json类型(Spatial Types)
- 第十三章: 数据库设计和建模
- 设计数据库
- 数据建模
- 概念模型
- 逻辑模型
- 实体模型
- 主键
- 外键
- 外键约束
- 标准化和范式
- 第一范式
- 第二范式
- 第三范式
- 关于标准化的实用建议
- 不要对什么都建模
- 模型的正向工程
- 数据库同步模型
- 模型的逆向工程
- 创建和删除数据库
- 创建、更改和删除表
- 创建关系
- 字符集和排序规则
- 存储引擎
- 第十四章: 索引
- 索引
- 创建索引
- 查看索引
- 前缀索引
- 全文索引
- 复合索引
- 复合索引中的列顺序
- 当索引无效时
- 使用索引排序
- 覆盖索引
- 维护索引
- 第十五章: 用户和权限
- 创建用户
- 查看用户
- 删除用户
- 更改密码
- 授予权限
- 查看权限
- 增加、撤销权限
- 附件*
- 列属性关键词
- MySQL与其他DBMS的一些语法差别
- 用户权限
- MySQL学习链接和MySQL下载与文档
- MySQL函数官方文档
- 关键字和保留字
第一章: 引言
有关SQL
数据库(Database)
存储数据
数据库用于持久性地存储和管理数据。这些数据可以是结构化或非结构化的,具体取决于数据库类型。
数据库管理系统(DBMS)
数据库操作系统(Database Management System)
DBMS 是一种软件,用于创建、维护和管理数据库。它允许用户与数据库进行互动,提供了数据的存储、检索、更新和删除等功能。
计算机连接 DBMS 发送指令
通过 SQL 或其他查询语言,用户或应用程序可以向 DBMS 发送指令(查询或命令),DBMS 会执行这些指令并返回结果。
DBMS 分类
DBMS 主要分为关系型和非关系型两大类。
-
关系型(Relational)
- 基于表格和行的结构。
- 使用 SQL 作为查询语言。
- 常见的有:MySQL, SQL Server, Oracle, PostgreSQL 等。
-
非关系型(NoSQL)
- 不仅仅基于表格结构,可能是键值对、文档或图形。
- 查询语言因数据库而异。
- 非关系型数据库分类:
- 键值数据库:如 Redis, Memcached, Riak
- 列族数据库:如 Bigtable, HBase, Cassandra
- 文档数据库:如 MongoDB, CouchDB, MarkLogic
- 图形数据库:如 Neo4j, InfoGrid
SQL(Structured Query Language)
数据库语言
SQL 是用于操作关系型数据库的标准语言。它允许你执行各种数据库操作,如数据查询、更新、插入和删除等。
用于操作关系型 DBMS
SQL 主要用于关系型数据库,而非关系型数据库通常有自己的专用查询语言或 API。
快速概览
- 前七章所有基本SQL技能,包括增删改查,汇总数据,子查询,内置函数
- 第八、九章创建视图(View),存储过程和函数
- 第十、十一章高阶内容,如触发器和事件,事务和并发
- 最后几章设计数据库,索引,保护数据库分配用户权限
第二章: 数据库操作
use使用数据库
use
数据库名称
select选择语句
select
查看的列
- from选择子句
from
表名称 (别名)- where选择子句
where
条件- 与或非运算符
and
,or
,not
- in,between运算符
in
列表
between
条件and
条件 ( ps:闭区间 )
like,regexp运算符
一般用在where选择子句
where
一般是列名称like
‘匹配字符’
%
匹配任意字符,_
匹配一个字符如
where name like '_Aming%'
表示为匹配任意字符开头紧接Aming后面任意字符的名字
where
一般是列名称regexp
‘匹配字符’
- 正则表达式(regular expression),不多bb,及其强大
- is NULL运算符
where
列名称 is (not) NULL- 查看列名字中为(非)空的项
- order by子句
order by
列名称- 按列排序
- limit子句
limit
偏移量,数字- 如limtit 6,3就是只显示选择结果中第6条开始后面3条
第三章: 数据库连接
内连接
(inner) join
表on
条件
- inner默认有,一般不打
跨数据库连接
增加数据库前缀,
db.table
自连接
自己连接自己,比如员工的上级也是员工,如
join 表 on 汇报人 = 员工
多表连接
顾名思义可以连接多表,但尽量不要超过3张,影响查询效率
复合连接条件
一般用于有两个主键
隐式连接条件
from
表1,表2
where
条件
- 最好不要用这个,因为一但忘记where就会交叉连接(笛卡尔积)
ps:做笔记的时候就忘了…
外连接
left join
表名on
条件
select * from 表1
left join 表2 on 条件
- 相当于 表1 + (表1 ∩ 表2)
- 表1都有 加上 表1和表2的交集
right join
表2on
条件
- 同上,相当与 交集 加 表2
多表外连接
和多表内连接一样,没啥好说的
自外连接
自连接有时会少数据,此时用外连接
using子句
join
表using(列名)
- 当连接的列名一样时用using
自然连接
natural join
表
- mysql自动连接,不需要条件了,基于共同列名连接
- ps:教程不太建议使用,因为不能控制可能出现意料之外的结果
union | 联合
选择语句
union
选择语句
- 可以将两段选择语句联合,但是列数一定要一样,不然会报错
第四章: 数据操作
列属性
-
打开表的设计模式,MySQL是表旁边的工具图标
- Column 列名称 - Datatype(...) 数据类型(字节数)int:整数类型varchar:可变字符,varchar(50)存5个字节就是5个字节空间 char:字符,char(50)存5个字节也占用了50个字节空间 - PK(Primay Key) 主键 - NN(NOT NULL) 非空 - UN(UNIQUE) 唯一 - AI(Auto_Increment) 自动递增,通常用于主键 - Default/Expression 默认值
Insert插入行
insert into
表(列名)
value(
每列的值)
- 列名可选,非空列必选,不选不用加括号填入全部数据
- 插入一行
value(
每列的值)
,
(
每列的值)
,
(
每列的值)
- 插入多行
插入分层行
LAST_INSERT_ID()
- 返回上一条插入语句MySQL生成的id,可以用这条函数获取id再写一条插入语句插入与之有关联的表
- 如下列语句就是先向订单表(oreders)插入一行,再利用订单id向订单项目表(order_items)插入两行
insert orders(customer_id, order_date, status)
values(1, '2019-01-02', 1);
insert order_items
values( LAST_INSERT_ID() , 1, 1, 2.95),
( LAST_INSERT_ID(), 2, 1, 3.95)
创建表复制
use
数据库名;
create table
表名as
选择子句
- 将选择语句展示的数据复制一个表出来
更新行
update
表名
set
列名 = 值(多选逗号分隔)
where
条件
- 更新单行条件具体唯一,更改多行条件要更通用或用子查询
在MySQL使用多条件需要更改设置,因为MySQL默认在安全更新模式下运行,别的DBMS不会出问题
Windows:Edit(编辑) 或 Mac:Workbentch(菜单) ->Preferencesth(偏好) ->
SQL Editor(编辑器)-> Other ->把这个取消
- Safe Updates (rejects UPDATEs and DELETEs with no restrictions)
安全更新(拒绝更新和删除,没有限制)
- Safe Updates (rejects UPDATEs and DELETEs with no restrictions)
从表中删除
delete from
表
where
条件
- 要批量删除用复杂条件或选择子句
恢复数据库
一般用SQL script(脚本)
第五章: 聚合和分组
聚合函数
如max()最大值,min()最小值,avg()平均数,sum()总计,count()计数
- 聚合函数只运行非空值,影响count(),此时要用count(*)
group by子句
group by
列名
- 以列名进行分组,多列逗号隔开
hving子句
having
条件
- 和where差不多,不过是筛选聚合数据
rollop运算符
group by
列名with rollup
- 自动汇总整个结果集,多个列队每一一组汇总
- ps:应用及其广泛但不是一个标准的SQL语言,只有MySQL里有,不过别的DBMS一定也有类似的运算符
第六章: 子查询和条件
子查询
where
列名 运算符( 选择语句 )
- 这里的选择语句作为子语句进行判断,以此编写复杂查询
in运算符
where
列名in
列表
- 判断是否在一个列表
distinct关键字
一般用于选择语句,不重复显示
- 如
select distinct
列名
子查询vs连接
select * from clients
where client_id not in (
select distinct client_id
from invoices
)
select *from clients
left join invoices using(client id)
where invotce_id is NULL
all,any关键字
没啥卵用的两个关键字
where 列名 运算符 all(列表)
- 满足所有条件,相当于max()
where 列名 运算符 any(列表)
- 满足任一条件,相当于min()
相关子查询(好用废时)
select *
from employees e
where salary > (
select avg(salary)
from employees
where office_id = e.office_id
)
where office_id = e.office_id
- 这段查询会在主查询的每一行的层面执行
- 因此十分费时废内存费空间,但尽管如此,相关子查询很强大,在现实生活有很多应用
exists运算符
where exists
- 判断是否存在
子句中的子查询
select
(选择子句)
from (选择子句) as 别名
- from一般不用,用视图更好用
第七章: 函数
全部函数表见附件
数值函数
round(num,params)
– 四舍五入num,params可选参数,保留的小数位数
truncate(num,params)
– 截断num,params可选参数,截断的小数位数
ceiling(num)
– 返回大于等于num的最小整数
floor(num)
– 返回小于等于num的最大整数
abs(num)
– 返回num的绝对值
rand(num)
– 返回0-1的随机浮点数(最有用)
字符串函数
length(str)
– 返回字符串的字符数
upper(str)
– 大写所有字符
lower(str)
– 小写所有字符
ltrim(str)
– left trim(左修正)的缩写,清空前导空格
rtrim(str)
– right trim(右修正)的缩写,清空后导空格
left(str,value)
– 获取字符串左边value个字符
right(str,value)
– 获取字符串右边value个字符
substring(str,begin,len)
– 获取字符串第begin个字符开始len长度的字符串,len为可选
locate(str1,str2)
– 查找str1是否在str2里,在返回第一个查看到的位置
– 不分大小写,找不到返回0(大部分编程软件返回-1,不要混淆)
replace(arg,str1,str2)
– 将arg里的str1替换成str2
concat(str1,str2)
– 将str1和str2串联在一起
日期和时间函数
now()
--返回当前时间
year(date)
--返回date的年份,如year(now())返回今年
mouth(date)
--返回date的月
day(date)
--返回日
hour(date)
--返回时
minute(date)
--返回分
second(date)
--返回秒
dayname(date)
--返回字符串格式的星期数
monthname(date)
--返回字符串格式的月份
extract(year/day/... from date)
extract(year from now())
–这条语句将会返回当前时间的年份
–sql标准函数,想把代码在别的DBMS也能使用最好用这个
日期和时间格式化
date_format(date,format)
--日期格式化
time_format(date,format)
--时间格式化如
date_format(now(),'%Y-%m-%d')
返回 2003-05-05(假设)
时间日期计算
date_add(date,exp)
--返回date和exp表达式相加的值如
select date_add(now(),interval 1 day)
就是显示现在的日期加一天的结果
date_sub(date,exp)
--返回date和exp表达式相减的值
datediff(date1,date2)
--返回date1减date2的值(不考虑时间只算天)
time_to_sec(time)
--返回time的秒数
ifnull和coalesce函数
IFNULL(列,str)
--如果列值为空返回str
coalesce(列,列2,str)
--如果列1值为空,看列2,如果列2有则返回列2的值,如果也为空返回str
if函数
if(exp,str1,str1)
- 如果exp表达式为真返回str1否则返回str2
case分支函数
case
when
条件than
str
when
条件than
str
else
str
end
从上往下执行,比如
case
when money > 1000 than "wo"
when money> 3000 than "wa!"
else "low"
end
当money为4000时会返回wo,也就是说后面条件永远不会触发
第八章: 视图
创建视图
create view
viewas
选择子句
- 将为创建一个名为view的视图,内容是选择子句的结果
更改或删除视图
drop
视图名
- 删除视图,再执行创建达到更新目的
create or replace view
视图名
- 更新视图,不需要先删除
ps:把视图保存到sql文件,放到sql源码控制是很常见的做法,别人可以在他们的电脑上重构这个数据库
可更新视图
如果视图没有以下东西可以当正常表使用
distinct关键字
任何聚合函数,如max,sum等
group by或having子句
– 通常和聚合函数一起
union运算符
with option check子句
create or replace view
视图名as
选择子句
with check option
- 这条语句会防止update或delete语句将行从视图中删除
视图的优点
- 视图可以帮助我们简化查询
- 减小数据库设计的改动
- 可以使用视图限制基础表访问
- 加强数据的安全性
第九章: 存储过程和函数
什么是存储过程
存储过程是一个包含一堆SQL代码的数据库对象
- 应用代码和SQL代码在一起会难以维护,将SQL代码保存在存储过程或函数中更好调用
- 使用存储过程保存SQL代码好处
- 大部分DBMS可以优化存储过程的代码,执行起来可能更快
- 和视图一样,能加强数据安全性,例如我们可以取消所有表的直接访问权限,
在存储过程操作,然后可以指定能够执行特定存储过程的一人
创建和调用存储过程
delimiter $$
create procedure
name()
begin
选择子句;
end $$
delimiter ;
- 上列语句就是创建了一个名为name的存储过程,记得有括号,可以传参
- 在MySQL中每条语句都要加分号,但是我们需要把选择子句和创造存储过程打包发送给MySQL执行而不是分开执行,所以需要将默认分隔符;改成别的,
delimiter $$
就是在做这件事,改成$$是国际标准当然也可以改成别的,当然最后别忘了改回来delimiter ;
ps:在别的DBMS可能不需要做这件事
call name()
- 这条语句可以调用名为name的存储过程,是SQL代码,不过通常是用应用代码调用接口,如python
MySQL工作台创建存储过程
选择数据库 -> Stored Procedures(存储过程) -> 右击create Stored Procedures -> 编写选择语句后应用
- 如果你觉得写SQL代码每次都要更改分隔符十分麻烦可以这么创建,可以只编写存储过程的名字和选择子句
删除存储过程
drop procedure
proceudre_name
- 删除名为proceudre_name存储过程,如果没有会报错
更安全的方法是drop procedure if exists proceudre_name
带参数的存储过程
create procedure
name(param1 varchar, param2 varchar)
- 这条语句创建了一个名为name的存储过程,需要传入param1和param2两个参数,varchar类型
完整SQL代码见创建和调用存储过程create procedure name (param1 varchar, param2 varchar)
begin
if
paramis NULL than
set
param=
default
end if
选择子句;
end $$
- 这是传入一个参数的情况下,高亮的是判断为空就设为默认值,加粗是变量
where table_param1 = ifNULL(param1,table_param1) and
table_param2 = ifNULL(param2,table_param2)
- 这种方式更为常见,用MySQL自带的ifnull函数判断是否为空
如果传递的值非空就用传递的值,为空设为表格参数,也就是返回全部值
参数验证,错误代码
create procedure
name(变量 类型)
begin
if
金额< 0 than
signal sqlstate
‘错误代码’
set message_text ;
提示信息
选择子句;
end $$
- signal sqlstate '22003’用于验证参数,比如错误代码22003就是数值超范围的标准代码,记得错误代码是个字符串
- set message_text 提示信息用于提示发生了什么错误,可以不填
- 记得后面要加分号结束语句
输出参数
create procedure
name
(变量1 类型
out 变量2 类型 )
#输出函数
begin
select 列2
#将选择的列2赋值给变量2
into 变量2
end $$
变量
设置一个叫var的全局变量,当退出MySQL释放,也叫用户或会话变量
set @var = 0
定义了一个叫var的本地变量,类型是decimal(9,2)就是最多9位数,小数点后两位
default可选m用于设置默认值
declare var decimal(9,2) default 0;
函数
用法和普通函数一样,下列属性可选,也可多选
create function
name()
#创建了一个名为name的函数()里可以传参
returns integer
#和存储过程主要的区别,明确了返回值的类型
deterministic
#属性:确定性,如果给两个一样的值,返回的值也一样
reads SQL DATA
#属性:读取SQL数据,函数会配置选择语句读取数据
modifies SQL DATA
#属性:修改SQL数据,函数会更新,插入,删除数据
begin
选择语句;
return
i; #返回i
end
其他约定
命名形式每个人的喜好各不相同
- 函数有人喜欢在前面加fn_类似fn_name
- 存储库有人喜欢proc_name,有人喜欢驼峰式procName也就是除第一个单词首字母大写
- 分隔符有人喜欢delimiter $$ 有人喜欢delimiter //
- 触发器大部分都是类似table_after_insert,先说明表,再说明在插入后触发
- 事件最好以类似于yearly_delete_stala_audits_rows,每年删除过期的审计行
- 索引以idx_column命名,用idx_前缀加上列名
第十章: 触发器和事件
触发器
样例代码
delimiter $$ #更改分割符为$$create trigger payments_after_delete #创建一个名为payments_after_delete的触发器after delete on payments #说明条件,表示在payments表删除时触发for each row #说明条件,表示对每一行都触发
begin #触发后执行的操作update invoices #三行选择子句,更新invoices表set payment_total = payment_total - old.amount #更新invoices表中payment_total等于来的金额减payments表删除前的amountwhere invoice_id = old.invoice_id; #筛选条件,对两张表invoice_id一样的数据操作
end $$ delimiter ; #更改分隔符为;
- old关键字获取更改前的数据,new获取更改后的数据,加.获取具体属性
- 注意有更改器的表不能更改,不然触发器会自燃
查看触发器
查看所有触发器
show triggers
查看匹配搜索到的触发器
show triggers like 'payments%'
删除触发器
删除名为trigger_name触发器
drop triggers trigger_name;
如果存在名为trigger_name的触发器则删除
drop triggers if exists trigger_name;
使用触发器进行审计
首先我们要有一个保存操作的表,没有先创建一个
样例代码
USE sql_invoicing; #使用sql_invoicing数据库CREATE TABLE payments_audit #创建payments_audit表
(client_id INT NOT NULL, date DATE NOT NULL,amount DECIMAL(9, 2) NOT NULL,action_type VARCHAR(50) NOT NULL,action_date DATETIME NOT NULL
) #定义每列的名字,类型,设置为非空
下列语句穿插到触发器里(end之前),详见触发器
insert into payment_audit
value(old.client_id,old.date, old.amount, 'delect', NOW());
第一行代表插入payment_audit表,第二行把更改前的用户id、日期、金额、操作、时间写入
事件和查看系统变量
事件是按计划执行的任务或一堆SQL代码,比如每天0点或一月一次,通过时间我们能更好的自动化管理MySQL,比如删除过期数据,汇总财务报告等等,非常有用.
在开始事件之前,我们需要开启MySQL事件调度器,它在后台每时每刻寻找需要执行的事件
可以用以下命令查看全部系统变量
show variables
可以用like筛选来找到事件调度器,如
show variables like 'event%'
可以用set设置事件调度器,如果不需要用到事件可以关闭以节省资源,这相当于关闭了一个无时无刻在后台运行的后台程序,如
show variables like 'event%'
set global event_scheduler = off
创建、查看、删除和修改事件
创建一个事件
样例代码
delimiter $$ #更改分隔符create event yearly_delelt_state_audit_rows #创建一个每年删除过期审计行的事件
on schedule #设定计划-- at '2019-05-01' #只执行一次用atevery 1 year #每年执行一次,重复执行用everyevery 1 year start '2023-01-01' end '2033-01-01' #起始时间和结束时间,可选
do begin #执行的操作delete from payments_auditwhere action_date< NOW() - interval 1 year; #筛选日期小于现在时间减去一年的行,下面两个等同于这句-- where action_date< dateadd( NOW(), - interval 1 year)-- where action_date< datesub( NOW(), interval 1 year)
end $$delimiter ; #更改分隔符
用于查看所有事件
show events
用于筛选事件
show events like '_early%'
如果存在yearly_delelt_state_audit_rows事件则删除
drop event if exists yearly_delelt_state_audit_rows
修改时间,把创建事件的代码中的create更改为alter就行
alter event yearly_delelt_state_audit_rows
将yearly_delelt_state_audit_rows事件禁用或启用
alter event yearly_delelt_state_audit_rows disable|enable
第十一章: 事务和并发
事务
事务是代表单个工作单元的一组SQL语句,所有语句执行完成事务才会成功,如果失败则退回已经完成的语句。
比如我给朋友转了100块,朋友没有收到则退回,我们希望把两个操作合并为一个单元一起成功一起失败
事务的特性(称为ACID)
- 原子性 --事务像原子一样,牢不可破,要么都成功,要么被退回
- 一致性 --数据库会始终一致,不会出现类似有订单没项目的情况
- 隔离性 --事务之间相互隔离,当有相同数据被更改时各受到保护,当多个事务修改一个行,这个行会被锁定,其他事务必须等那个事务结束
- 持续性 --事务一旦提交就是永久的,就算停电也不会丢失
创建事务
比如下列代码如果插入语句一执行成功后插入语句二没有成功,比如客户端崩溃,网络脱机,服务端崩溃,则语句一将会被退回
样例代码
use sql_store; #使用数据库start transaction; 开始事务insert into orders(customer_id, order_date, status)
values(1, '2019-01-01', 1); #插入语句一insert into order_items #插入语句二
values(last_insert_id(), 1, 1, 1);commit; #结束事务
- 在某些情况下,我们可能想进行错误检查,
想退回事务可以将commit;
改成rollback;
- MySQL会将事务里的语句检查,如果有update,insert,delete会先装进事务然后自动提交
这由一个自动提交的变量管理的,可以用shwo vairable like 'autocommit
找到
并发
- 当多个事务更改同一行,MySQL默认解决并发方式是锁定那一行,等待正在操作的事务完成或退回
并发问题
- 丢失 --如果没有上锁,事务并发,后执行的事务会覆盖先执行的事务
- 脏读 --如果一个事务的更改还没结束,另一个事务可能会读取错误的信息,可以设置事务隔离等级为读已更改
- 不可重复读 --事务A读取了一个值,事务B在事务A的过程中更改了这个值,事务A后面又读了这个值,可以设置事务隔离等级为可重复读
- 幻读 --事务A查询积分大于10的客户,而事务B更改了一名客户满足条件,但事务A没读到,可以设置事务隔离等级为序列化
事务隔离等级
丢失更新 | 脏读 | 不可重复读 | 幻读 | 设置代码 | |
---|---|---|---|---|---|
读未提交 | set transaction isolation level read uncommitted; | ||||
读已提交 | ✅ | set transaction isolation level read committed; | |||
可重复读 | ✅ | ✅ | ✅ | set transaction isolation level repeatable read; | |
可序列化 | ✅ | ✅ | ✅ | ✅ | set transaction isolation level serializable; |
- 可以用
show variables like 'transaction_isolation';
查看隔离等级,默认是可重复读 - 用
set transaction isolation level serializable;
更改隔离等级,这将为下一个事务设置可序列化隔离等级 - 用
set session transaction isolation level serializable;
将会话后面所有的事务设置 - 用
set global transaction isolation level serializable;
将所有会话后面所有的事务设置 - 当开发时,用别的应用接口连接到MySQL,在执行事务前,只修改那个会话的隔离级别,然后在执行,这样数据库的其他事务就不会受影响
死锁
当两个事务互相占用会触发死锁报错并退回一个
例
事务一
1.use sql_store;
2.start transaction;
3.update orders set status = 1 where order_id = 1;
4.update customers set state = 'va' where customer_id = 1;
5mit;
事务二
1.use sql_store;
2.start transaction;
3.update customers set state = 'va' where customer_id = 1;
4.update orders set status = 1 where order_id = 1;
5mit;
如上诉代码事务二和事务一的区别是第三行和第四行互换,当两个事务同时进行到第三行,再往下会触发死锁,两个事务分别锁定了各自第四行代码的行
第十二章: 数据类型
字符串类型(String Types)
- char(x) --储存x字节的字符串
- varchar(x) --可变字节,不会浪费空间可以统一x,比如短一点的类似电话就varchar(50)长一点的类似地址就(255),最大取值不能超过65535(~64kb)
- mediumtext --最多存储1600,万个字符(16MB)
- longtext – 最多存储4GB
以上是比较常用的类型,其他类型:
- tinytext --最多 255个字节
- text --最多64kb和varchar一样大,但是varchar能用索引
整数类型(Numeric Types)
搜索mysql integer types查看
关键词 | 大小 | 范围 | 备注 | 无符号最大值(前面加unsigned) |
---|---|---|---|---|
tinyint | 1b | [-128,127] | 微整形 | 255 |
smallint | 2b | [-32768,32767] | 小整型 | 65535 |
mediumint | 3b | [-8388608,8388607] | 中整型 | 16777215 |
int | 4b | [-2147483648,2147483647] | 整型 | 4294967295 |
bigint | 8b | [-263,263-1] | 大整型 | 264-1 |
定点数和浮点数类型(Date and Time Types)
- decimal(p,s)定点数,p确定位数,s确定小数点后面位数,例
decimal(9,2)最长为1234567.89
dec、numeric、fixed和这个一个意思,不过一般用decimal浮点型,浮点型都是取近似值
- float 4b 单精度
- double 8b 双精度
布尔类型(Boolean Types)
- bool或者boolean 布尔类型
True = 1,False = 0
枚举和集合类型(Enum and Set Types)
不好用,建一个规格表会更好
- eunm(‘小’,‘中’,‘大’) 只允许三个规格的值可以用枚举
- set(…) 和枚举差不多
时间和日期类型(Date and Time Types)
- date 日期类型
- time 时间类型
- datetime 8b 日期时间值
- timestamp 4b 时间戳(最多到2038,被称为2038问题)
- year 储存年份
二进制类型(Blob Types)
名字 | 大小 |
---|---|
tinyblob | 255B |
blob | 65KB |
mediumblob | 16MB |
longblob | 4Gb |
最好不要在数据库存储二进制文件,如果在数据库存储二进制文件
- 你的数据库大小会迅速增加
- 弱化数据备份功能
- 性能问题,比文件系统慢的多
- 还得额外写代码
json类型(Spatial Types)
首先要确保MySQL版本大于8.0.15
用法1
set 列 = {'hight':10,'list' :[1,2,3]
}
用法二
SET properties = JSON_OBJECT('weight', 10,'list', JSONIARRAY(1, 2, 3),'manufacturer', JSON_OBJECT('name', 'sony')
)
提取MySQLjson格式中某个键的详细值
select product_id,json_extract(properties, '$.weight')
from products
where product_id = 1
------------------------------------------
json_extract(properties, '$.weight')
更简短的写法:
properties -> '$.weight'
->被称为列路径运算符
->>可以去掉双引号
- 这段代码从products表提取了product_id为1的product_id和properties(json格式)中weight的值
json_extract
获取json格式,第一个参数是列,$
符号获取json文档,.
获取json中的属性,$.weight
就是获取json中键为weight的值
更改或删除json的值
- 用
json.set
增加或更改值,如下列代码有weight键则更新值,没有age键则增加键值对
update products
set properties = json_set(properties, #拾取一个json对象'$.weight', 20, #有这个属性所以更改'$.age', 10 #没有这属性所以增加
)
where product_id = 1;
- 用
json.remove
删除一个或多个值,如下列代码有weight键则更新值,没有age键则增加键值对
update products
set properties = json_remove(properties, #拾取一个json对象'$.age', 10 #删除该键值对
)
where product_id = 1;
第十三章: 数据库设计和建模
设计数据库
- 设计良好的数据库可以很轻松的围绕它进行开发,来支持新的业务需求,可以很轻松查询提取有用的信息,并且查询会快速执行
- 设计糟糕的数据库需要大量的维护,并且成本会随时间推移,最终无法开展新业务
数据建模
为数据库的数据创建模型的过程,包括四个步骤:
- 理解和分析业务需求(最重要的一步)
- 概念模型:创建业务的模型,包括业务中的实体,事物,概念之间的关系
- 逻辑模型:对数据的抽象,确定数据类型
- 实体模型:实际建立模型,完善表之间的关系
概念模型
找到业务中实体,事务,概念之间的关系,用可视化方式观察
一般有两种方式,用实体关系图或UML图(标准建模语言),UML功能远超需要功能
只用画出实体,和实体的参数
实体关系图可以用微软visio 或在线网站draw.io(外网)及LucidCharts等
逻辑模型
在概念模型的基础上完善,
增加数据类型
(只用表示为如string,而不是varchar这样的实现细节),表之间的关系(多对多一般转成两个一对多
),独立于数据库技术
- 多对多关系一般建立新实体来转换为两个一对多关系
实体模型
实体模型是在逻辑模型的基础上用特定数据库技术实现的具体细节,
增加主键外键,设定具体数据类型
- 在MySQL中实现为
File
->new Model
->把physical schems中默认的mydb右键编辑改成自己的数据库名
->用add diagram增加表
主键
主键是唯一标识给定表里每条纪录的列(索引以主键为主,不要太大)
- 如果表里没有合适当主键的列,一般新增列id标识
- 也可以使用复合主键,但复合主键作为这个表中子表的外键也必须出现
外键
在两个表,如果表2没有表1就不行,则称表1为父表,表2为子表,在MySQL选择一对多关系时先选择外键表(子表)再选择主键表(父表)
外键约束
表的设计模式中,在
foreign keys
(外键约束)设定当主键操作时外键应该执行什么操作,在这里可以看到外键默认命名为fk_外键表_主键表
及,后面可能会跟上数字,这是MySQL防止重名,如果确认不会重名可以双击更改名字,
- 一般设置是
on update
设置为cascade
(级联),也就是一起更新,on delete
设置为no action
(不操作)或者restrict
(限制),这两个效果一样,但是具体还是要看需求
标准化和范式
在生成表之前要确保我们的设计是最优的,不存在冗余,因为冗余会增加数据库大小,也会是操作更复杂
- 基本上有七条规则,也被称为七范式,每条规则都假设我们已经遵守了前几条规则
- 对于99%的场景只要运用前三条范式
第一范式
要求一行的每个单元格都应该有单一值,且不能出现重复列
- 如果出现一列需要多个值,应该单开表,然后用中间表(基本只带两个表的主键)链接起来。
- 比如课程需要标签,标签有多个,标签应该新开表,但标签表和课程表不好链接关系,就需要一个课程标签表,里面包含课程id和标签id的外键,用两个一对多关系链接起来
第二范式
- 要求符合第一范式
- 每个表都应该有一个单一目的,也就是不应该出现无关这个表的属性,每一列都应该用来描述那个实体
第三范式
- 要求符合第二范式
- 表中的列不应该派生自其他列
- 比如有金额和付款列,就不应该有结余列
关于标准化的实用建议
- 没必要记住第几范式,除非是要考试
- 当实际项目式只要关注消除冗余就行,没人会关心是第几范式—
不要对什么都建模
- 不要对未来不太可能出现的问题建模,不要无视项目范围,项目背景,业务需求
- 我们应该尽可能简化模型,在未来需要的时候再回来改
模型的正向工程
将实体模型创建为真正的数据库
Databases -> Forward Engineer
- 选择连接的主机,输入用户密码,本地学习默认,点击下一步
- 选择要生成的脚本的选项,大多数默认就行,除非你知道在干嘛,点击下一步
- 可以选择编写脚本的对象类型,表,视图,存储过程,触发器及用户对象,也可以设置过滤器,下一步
- 然后会显示数据库脚本,
- 点击保存文件可以生成脚本文件,可以录入源码控制资源库。
- 或者复制到粘贴板,然后在工作台粘贴执行
- 点击继续执行生成数据库
数据库同步模型
在实际生产环境中可能会有多个环境,如生产环境,测试环境等等。所以我们做出修改时应该确保所以服务器能同步修改
- 在模型中做出更改后点击
Databases -> Synchronize Model(同步模型)
模型的逆向工程
对数据库进行反向工程来创建一个模型,然后用该模型用于任何未来更改
- 首先需要关闭别的模型(非常重要,如果没关,MySQL会把数据库添加到那个模型上,虽然没大碍,但还是单独配置模型好)
- 我们在一个模型中可以包含多个数据库,但这些数据库之间最好高度相关,并且想在一个模型中使用
- 选择
Database -> Reverse Engineer(逆向工程)
- 链接数据库后,下一步选择连接的数据库,再下一步筛选表(一般不用),执行后会有个新图在模型中
创建和删除数据库
创建一个叫name的数据库
create database name;
创建一个叫name的数据库如果不存在的话create database if not exists name;
删除一个叫name的数据库如果存在的话drop database if exists name;
创建、更改和删除表
列表属性见附件-列属性关键词
创建表
create database if not exists name; -- 创建一个名为name的数据库如果存在的话
use name; -- 使用name数据库
create table if not exists table_name( -- 创建一个名为table_name的表包含括号里的列及属性custmoer_id int primary key auto_increment, -- 名为custmoer_id的列,数据类型为int,设置为主键,递增first_name varchar(50) not null, -- 名为first_name的列,数据类型为varchar,设置为非空points int not null default 0, -- 名为points的列,数据类型为int,设置为非空,默认为0email varchar(255) not null unique -- 名为email的列,数据类型为varchar,设置为非空,唯一值
);
修改表
跳转创建关系
alter table table_name -- 更改table_name表add last_name varchar(50) NOT NULL after `first_name`, -- 在first_name列后面增 last_name列和属性,反引号可以不要,在有空格的情况下要(取名的陋习)add city varchar(50), -- 在最后增加city列modify column first_name varchar(55) default '', -- 更改first_name列,column可写可不写drop points, -- 删除points列,column可写可不写add primary key (id), -- 将本表中id列设为主键,多个用逗号分隔drop primary key, -- 删除主键,删除时不用写列名drop foreign key fk_name2_name1, -- 删除fk_name2_name1外键add foreign key fk_name2_name1 -- 增加fk_name2_name1外键references ... -- 后面写法与创建关系写法一致;
删除表
drop table if exists name;
-- 如果name表存在则删除(注意如果name表是某个表的父表(主键表),则首先得删除子表(外键表))
创建关系
创建语句见创建表
外键及更新删除模式见外键
创建语句 -- 假设创建了一个name1的表里面有id列
create table name2(..., -- 一些列,详细见创建语句-- 创建名为fk_name2_name1的外键,作用列是name2的id列foreign key fk_name2_name1(id) references name1(id) -- 主键来自name1表的id列on update cascade -- 设置更新模式为级联on delete no action -- 设置删除模式为不操作-- on delete set null -- 设置删除模式为设为空
)
字符集和排序规则
使用show charset
查看当前版本的MySQL支持的所有字符集
- 在Default collation(默认排序规则),决定了某类字符集排序规则
例如:UTF-8字符集的默认排序规则是utf8_general_ci- ci 是 case-insensitive(不区分大小写)的缩写,意思是MySQL在排序时给予大小写同等优先级
99%的情况下这已经能满足我们的要求
- ci 是 case-insensitive(不区分大小写)的缩写,意思是MySQL在排序时给予大小写同等优先级
- Maxlen(最大字节数),一般不用改,不过在某些情况可能需要更改来减少数据库大小
比如我们确定只用拉丁文就可以更改以节省空间
-- 创建数据库时设置为latin1(拉丁)字符集
create database namecharacter set latin1
-- 更改数据库为latin1(拉丁)字符集
alter database namecharacter set latin1
-- 创建表时设置为latin1(拉丁)字符集
create table name(...
)
character set latin1
-- 更改表为latin1(拉丁)字符集
alter table name(...
)
character set latin1
-- 也可以为单独的列设置
create table name(column varchar character set latin1
)
存储引擎
使用show engines
查看当前版本的MySQL支持的所有存储引擎
最常用的是MyISAM和InnoDB
- MyISAM是一个旧的引擎,在5.5版本前流行
- InnoDB随着版本更替,称为更高级的引擎,它能支持事务等功能
MyISAM已经过时了,如果遇到老的版本可能会用这个引擎
-
在表的设计模式中可以更换引擎
-
或者用代码更改
alter table name engine = InnoDB
只需要记住更改存储引擎可能会花费很多时间,因为MySQL必须重建这张表,在这期间无法访问表,所以不要在生产阶段更改,除非你要定期维护
第十四章: 索引
索引
在大数据大流量网站,索引非常重要,因为它们可以显著提高查询的性能
但使用索引也有代价
- 它们会增加数据库大小
- 当我们更改数据库时,MySQL必须更新对应索引,影响我们正常查询的性能
所以我们应该为性能关键的查询保留索引
创建索引
查看查询的开销
explain 选择语句;
主要关注type(类型)和rows(行)
以table表的state列创建一个名为idx_state的索引
craeate index idx_state on table (state);
查看索引
查看表中的索引
-
show indexes in table_name;
第一个是主键,也被称为聚集索引
column_name可以看到是哪个列
Collation中A代表升序,D表示降序
Cardinality代表索引中唯一值的估计数量,不是真实数值- 要想获得更精确的值可以使用
analyze table table_name;
后再查看
index_type存储形式,btree是二进制数的意思
- 要想获得更精确的值可以使用
创建的索引是二级索引,是基于主键索引的
前缀索引
当我们创建索引的列是个字符时可能会占用大量的空间
所以索引字符串列的时候只想包含几个前缀
craeate index idx_state on table (state(5));
要尽量达到性价比最高,也就是用尽量少的字符得到尽量多的唯一值
全文索引
在创建应用程序或网页时特别有用
create fulltext index idx_title_boby on table_name(title,boby)
创建一个名为idx_title_boby的全文索引在table_name表中的title,boby两列
查找时用两个内置函数来支持全文索引
select *
from table_name
where match(title,boby) against ('react redux');
-- against中包含若干个关键词的短语
用select match(title,boby) against ('react redux');
可以查看相关性得分
筛选可以加上 in boolean mode 比如
where match(title,boby) against ('react -redux +from' in boolean mode)
意思是排除文章中含有redux的文章,且文章中必须含有from
复合索引
创建复合索引
create index idx_column1_column2 on table(column1,column2);
复合索引中的列顺序
有两条基本规则
- 我们应该对列排序,让更频繁使用的列排在前面
- 我们应该吧基数更高的放在前面
这不是一定的,要根据实际情况,比如对于
where state = 'NA' AND last_name like 'A%';
对于这个查询来说,=
的约束性更强,而名字是范围更自由,所以把州放前面效率更高
可以用use index(idx_name)
强制使用某个索引
当索引无效时
有些情况下,你拥有索引也可能会出现性能问题,比如
select * from table
where state = 'CA' or points >1000;
这个语句执行的是索引查找,但会扫描全部的行,因为我们用的是or
优化:把这个查询裁成两个更小的查询
select * from table
where state = 'CA' ;
union
select * from table
where points >1000;
列最好不要有运算,比如
wher points - 10 > 2000
可以改成
wher points > 2000 + 10
使用索引排序
select id from table
order by first_name
如果first_name列的索引,则会使用外部排序,是很耗费的操作
如非必要,不要排序
就算排序,如果有两列,也不要一个升序一个降序
可以用show status;
查看MySQL服务器使用的变量
用show status like 'last_query_cost';
查看上一次查询的开销
覆盖索引
- select选择的列,order by聚合的列要覆盖索引中有的列,否则可能会全表扫描
维护索引
处理索引时,要注意重复索引和多余索引
- 重复索引:同一个表同样的列同样的顺序,MySQL不会阻止创建
所以在创建新索引之前检查现有的索引 - 多余索引:比如创建了复合索引(A,B),再创建(A)则为多余索引
而(B,A)或者(B)则不算
索引维护非常重要,确保要删除重复索引,多余索引和未使用的索引
第十五章: 用户和权限
创建用户
创建一个名为user_name
的用户,@
后面接ip或主机名指定连接的计算机
create user user_name@127.0.0.1
或create user user_name@localhost
也可以指定域名比如create user user_name@codewithmosh
这代表了用户可以从该域中的任何计算机连接,但无法从子网域连接
要设定子网域可以使用create user user_name@"%.codewithmosh"
加上%.
表示任何子网域,但是要给整个域加上单引号或者双引号
也可以用create user user_name
创建一个没有连接限制的用户
用create user user_name identified by '123456';
为用户创建密码
查看用户
有两种方式查看用户
- 用
select * from mysql.user;
查看所有用户,有三个用户账户是供mysql使用 - 在导航栏
Administration -> users and privileges
查看
删除用户
create user bob@codewithmosh identified by '123456'; #创建
drop user bob@codewithmosh; #删除
更改密码
两种方法
- 用
set password for bob = '1234'
为bob更改密码
用set password = '1234'
为当前用户更改密码 - 可以查看用户,在查看用户界面可以直接更改用户密码
也可以用Expire password让用户下次登录需要更改密码
授予权限
所有权限见附件
一般有两种情况
- 我们有一个网页或桌面应用,只想让它读写数据
create user moon_app identified by '1234'; #创建moon_app用户(仅为示例,实际一定要用强密码)grant select,insert,update,delete,execute #给予增删查改,执行存储过程的功能
on sql_name.* #在sql_name数据库的所有表,也可以指定单独表,超出这个范围不允许操作
to moon_app; #给moon_app用户这些权限,如果创建时指定了域名这里也需要加上
- 管理员
grant all #给予所有权限
on *.* #所有数据库
to Aming; #给Aming用户
查看权限
有两种方式
- 用
show grants from user;
查看user的权限
用show grants;
查看当前用户的权限 - 在查看用户里点击
Administrative Roles
可以查看权限
在Schema Privileges
可以查看用户特定数据库的权限
更改权限后点击应用就行
增加、撤销权限
增加权限
-- 给moon_app在sql_name中所有表的create view权限
grant create view
on sql_name
to moon_app;
撤销权限
-- 给moon_app在sql_name中所有表的create view权限
revoke create view
on sql_name
from moon_app;
附件*
列属性关键词
关键字 | 意思 |
---|---|
Primay Key | 主键 |
NOT NULL | 非空 |
unique | 唯一 |
Auto_Increment | 自动递增 |
Default | 默认值 |
MySQL与其他DBMS的一些语法差别
描述 | 链接 |
---|---|
在MySQL更新多行需要更改设置 | 更新行 |
自动按列汇总聚合函数的值 | rollop运算符 |
用户权限
描述 | 链接 |
---|---|
MySQL官方文档 | .7/en/privileges-provided.html |
MySQL学习链接和MySQL下载与文档
描述 | 链接 |
---|---|
b站’CodeWithMosh’中译: | |
MySQL下载链接: | / |
MySQL函数官方文档
描述 | 链接 |
---|---|
MySQL数值函数文档(8.0) --mysql numeric funtion: | .0/en/numeric-functions.html |
MySQL字符串函数文档(8.0) --mysql string functions: | .0/en/string-functions.html |
日期时间格式化文档(8.0) --mysql date format function: | .0/en/date-and-time-functions.html |
日期时间格式化w3school文档(中译) --mysql date format function of w3s: | .asp |
关键字和保留字
描述 | 链接 |
---|---|
本地文档 | 本地markdown(8.0) |
MySQL 8.0关键词文档: | .0/en/keywords.html |
MySQL 5.7关键词文档: | .7/en/keywords.html |
更多推荐
【笔记】MySQL数据库
发布评论