MySQL笔记

本笔记用于快速查阅和复习MySQL相关的知识和技巧。

（第一次用markdown写笔记写的有点不规范，比如代码没放到代码块里）

附件提供了一些如关键词保留字、用户权限、MySQL下载地址的链接，主要来自官方文档和一些其他的中译

学习来源： b站'CodeWithMosh'中译

学习参考：MySQL官方文档、w3school

第一章: 引言

有关SQL

DB 数据库(Database)
```
存储数据
```
DBMS 数据库操作系统(Database Management System)
```
计算机连接DBMS发送指令,DBMS返回结果
```
DBMS分为关系型(Relational)和非关系型(NoSQL) 常见关系型:MySQL,SQL server,Oracle,PostgreSQL... 非关系型分类 键值数据库：Redis、Memcached、Riak 列族数据库：Bigtable、HBase、Cassandra 文档数据库：MongoDB、CouchDB、MarkLogic 图形数据库：Neo4j、InfoGrid

SQL(SQUEL) 数据库语言(Structured Query Language)

用于操作关系型DBMS,非关系型无法读取

快速概览

- 前七章所有基本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 表2 on 条件

同上,相当与交集加表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()

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 view as 选择子句

将为创建一个名为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 param is 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表删除前的amount
   where  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'   #只执行一次用at
	every 1 year         #每年执行一次,重复执行用every
	every 1 year start '2023-01-01' end '2033-01-01'   #起始时间和结束时间,可选
do begin    #执行的操作
   delete from payments_audit
   where 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;
5.commit;
事务二
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;
5.commit;

如上诉代码事务二和事务一的区别是第三行和第四行互换,当两个事务同时进行到第三行,再往下会触发死锁,两个事务分别锁定了各自第四行代码的行

第十二章: 数据类型

字符串类型(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	[-2⁶³,2⁶³-1]	大整型	2⁶⁴-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
选择连接的主机,输入用户密码,本地学习默认,点击下一步
选择要生成的脚本的选项,大多数默认就行,除非你知道在干嘛,点击下一步
可以选择编写脚本的对象类型,表,视图,存储过程,触发器及用户对象,也可以设置过滤器,下一步
然后会显示数据库脚本,
1. 点击保存文件可以生成脚本文件,可以录入源码控制资源库。
2. 或者复制到粘贴板,然后在工作台粘贴执行
3. 点击继续执行生成数据库

数据库同步模型

在实际生产环境中可能会有多个环境,如生产环境,测试环境等等。所以我们做出修改时应该确保所以服务器能同步修改

在模型中做出更改后点击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,设置为非空,默认为0
   email       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%的情况下这已经能满足我们的要求
Maxlen(最大字节数),一般不用改,不过在某些情况可能需要更改来减少数据库大小比如我们确定只用拉丁文就可以更改以节省空间

-- 创建数据库时设置为latin1(拉丁)字符集
create database name
   character set latin1
-- 更改数据库为latin1(拉丁)字符集
alter database name
   character 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.com 这代表了用户可以从该域中的任何计算机连接，但无法从子网域连接

要设定子网域可以使用create user user_name@"%.codewithmosh.com" 加上%.表示任何子网域，但是要给整个域加上单引号或者双引号

也可以用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.com;  #删除

更改密码

两种方法

用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官方文档	dev.mysql.com/doc/refman/…

MySQL学习链接和MySQL下载与文档

描述	链接
b站'CodeWithMosh'中译:	www.bilibili.com/video/BV1UE…
MySQL下载链接:	dev.mysql.com/downloads/

MySQL函数官方文档

描述	链接
MySQL数值函数文档(8.0) --mysql numeric funtion:	dev.mysql.com/doc/refman/…
MySQL字符串函数文档(8.0) --mysql string functions:	dev.mysql.com/doc/refman/…
日期时间格式化文档(8.0) --mysql date format function:	dev.mysql.com/doc/refman/…
日期时间格式化w3school文档(中译) --mysql date format function of w3s:	www.w3school.com.cn/sql/func_da…

关键字和保留字

描述	链接
本地文档	本地markdown(8.0)
MySQL 8.0关键词文档:	dev.mysql.com/doc/refman/…
MySQL 5.7关键词文档:	dev.mysql.com/doc/refman/…

MySQL笔记

MySQL笔记

第一章: 引言

有关SQL

快速概览

第二章: 数据库操作

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工作台创建存储过程

删除存储过程

带参数的存储过程

参数验证,错误代码

输出参数

变量

函数

其他约定

第十章: 触发器和事件

触发器

查看触发器

删除触发器

使用触发器进行审计

事件和查看系统变量

创建、查看、删除和修改事件

第十一章: 事务和并发

事务

创建事务