MySQL 思维导图模板_ProcessOn思维导图、流程图

数据库介绍

存储数据的一个仓库，文件系统

数据库管理系统（DBMS）

操作管理数据的程序

常见的数据库

1.MySQL

2.SQL Server

3.Oracle

4.DB2

数据库的数据

数据创建流程

1.进入数据库

2.创建数据库

3.创建表

4.在表里面写入数据

表数据

Java类-->表

Java成员变量-->表字段

Java对象-->表记录

数据库层次两个阶段

数据库的需求分析

1.原始需求

2.将原始需求转换为业务需求

数据库的需求设计

1.将整理好的需求转换为一个业务模型

2.将后端或者其他代码转化为数据库参数

多表之间的关系

一对多（多对一）关系

建表原则：在多的一方设定为从表（外键），在少的一方设定为主表（主键），
然后从表的外键关联了主表的主键

多对多关系

建表原则：多对多需要依靠一个中间表完成，中间表最少具备两个字段，
这两个字段作为外键，分别关联另外两个表

一对一关系

几乎不用，因为一张表即可完成

表设计的五大范式

什么是范式：一个好的数据库设计对存储、性能以及后期的开发维护有着至关重要的作用，
为了建立更加科学合理的数据库流程，有人设计了一套优化数据库存的规则，
这个规则就被称之为范式

第一范式

要求数据库的每一个表字段都是不可分割的，必须具备原子性，不能是数据或者集合

第二范式

在满足第一范式的基础上，要求表中的每一个字段都强烈地依赖于主键
特点：1.一张表只能描述一件事情
2.表中的每一个字段都必须和主键有依赖关系

第三范式

在满足第二范式的基础上，要求表中的每一个字段都直接依赖于主键，
任何非主键列都不能进行任何形式的传递
依赖什么是传递依赖，如果现在有三张表A<--B--<C

表约束

对表的数据进行限制，保证数据的准确性、有效性、完整性
可以有效阻止非法数据的插入

约束的种类

默认约束：default
插入数据的时候，若某个字段没有数据插入，则填充默认值

非空约束：not null
字段没有数据或者是null时，视为非法插入，插入无效

唯一约束：unique
字段在表中不能出现重复数据，但null是允许重复的

主键约束：primary key（pk）
用来标识（区分）数据库数据的每一条记录
1.可以限制字段不为空
2.可以限制字段唯一
3.一张表只能存在一个主键

外键约束：foreign key （fk）
语法：constraint 外键名 foreign key（字段名）references 主表名（字段名）
将从表的字段和主表的字段进行关联

自增长约束：auto_increment
1.只有整数类型才能自增长

2.自增正字段一般为主键字段
3.自增长从0开始

检查约束：check（MySQL不支持）

级联操作

当我们的一张表存在外键的时候，如果修改了主表数据，
且从表会跟随主表联动修改从表数据，就可以使用级联操作

关键字：ON DELETE CASE 级联删除
ON UPDATE CASE 级联更新

MySQL常用字段类型

char

varchar char和varchar的区别：char是固定字符串，占用的空间与数据大小无关，除非数据大小超过默认空间大小
varchar是可变字符串，占用的空间与数据大小密切相关，可以自由缩放

int

date：日期，不包含时间

datetime：日期，包含时间

timestamp：当前时间的时间戳

数据库的四大变量

全局变量
作用域：server

@@global.变量名

会话变量
作用域：当前会话

@@变量名

用户变量
作用域：当前会话

@变量名

局部变量
作用域：当前语句块

变量名

SQL的通用语法

1.sql可以单行写也可以多行写，以分号作为结束

2.使用空格分割关键字，增加可读性

3.sql对大小写不敏感，建议关键字大写，非关键字小写，非关键字命名用反单引号包裹，避免与关键字冲突

4.SQL注册 --注释一行
/**/注释多行
#注释一行（MySQL方言）

数据库的事务

如何操作事务

1.开启事务：start transaction；

2.回滚事务：rollback；

3.提交事务：commit；

默认的事务提交方式

MySQL：自动提交
一条DML语句就是一个事务

Oracle：手动提交
必须要commit，代码才会生效

事务的四大特征

1.原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成
不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）
到事务开始前的状态，就像这个事务从来没有执行过一样。

2.一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、
串联性以及后续数据库可以自发性地完成预定的工作。

3.隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，
隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
事务隔离分为不同级别，
包括读未提交（Read uncommitted）产生脏读、虚读、幻读
Oracle默认读提交（read committed）产生虚读、幻读
MySQL默认可重复读（repeatable read）产生幻读
串行化（Serializable）没有问题

4.持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

sql语言

数据定义语言（DDL）

定义数据库当中的对象（库、表）关键字：create、delete、alter、show

1.创建数据库

1.登录数据库 mysql -u 用户名 -p 密码

2.创建数据库
create database 数据库名；

3.创建指定字符集的数据库
create database 数据库名 charset set 字符集编码；

4.创建一个数据库并判断是否存在，不存在就创建
create database if not exist 数据库名；

5.创建一个数据库并判断是否存在，不存在就创建并且指定字符串编码
create database if not exist 数据库名 charset set 字符集编码；

2.查看数据库

查询所有数据库名称 show database；

查询指定数据库的字符集，并且显示创建语句
show create database 数据库名；

3.删除数据库

删除指定数据库
drop database 数据库名；

删除指定数据库，如果不存在则不删除

drop database if exist；

MySQL数据类型

数值类型型

日期和时间类型

字符串类型

进入指定数据库

use 数据库名；

创建表

create table 表名｛
字段名字段类型
｝；

复制表

create table 新表名 like 被复制的表名；

查询表

查询库里面有哪些表 show table；
查询表的结构 class 表名；
查询指定表的创建语句 show create table 表名；

修改表

1.给表添加（多个）字段

alter table 表名 add column 字段名字段类型（字段名字段类型）；

2.修改表字段的数据类型

alter table 表名 modify column 字段名字段类型；

3.修改表字段的字段名

alter table 表名 change column 旧字段名新字段名字段类型；

4.删除一个字段

alter table 表名 drop column 字段名；

5.修改表名

alter table 表名 rename to 新表名；

数据操作语言（DML）

操作数据（增、删、改）关键字：insert、delete、update

添加数据

指定列名添加

insert into 表名（字段名1、字段名2）values（数值1、数值2）；

全部列的添加

insert into 表名 values（数值1、···、最后一个数值）；

一次性插入多条数据

insert into 表名 values｛数据集｝｛数据集｝｛数据集｝；

删除数据

删除表的全部数据

delete from 表名；

删除表的指定数据

select from 表名 where 字段名 = 字段值；

truncate删除全表数据

truncate table 表名；

drop、truncate、delete三者的区别

drop和truncate都是DDL，drop删除表会使表被抹去
而truncate则会留下表名，只删除表数据和表结构

truncate和delete一个是DDL，一个是DML。truncate会删除表结构和表数据
并且因为维护更少的日志，删除速度快，但恢复数据难
delete只删除表数据，而且要维护更多的日志，导致删除速度慢，但恢复数据比较容易

修改数据

修改全部数据

update 表名 set 字段名=数据值；

修改指定字段数据

update 表名 set 字段名 = 数据值 where 字段名 = 数据值；

同时修改多个字段

update 表名 set 字段名1 = 数据值1，字段名2 = 数据值2 where 字段名 = 数据值；

在基础数据上进行修改

学生表中所有学生的分数减少一分
update 学生表 set 分数 = 分数-1；

数据库查询语言（DQL）

查询数据

常用查询逻辑

书写顺序：SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段 HAVING 分组条件 ORDER BY 排序条件 LIMIT 分页条件
执行顺序： FROM --> WHERE --> GROUP BY --> HAVING --> SELECT --> ORDER BY

查询表中所有数据

select * from 表名；

查询表的指定列

select 列名1，列名2 from 表名；

指定别名查询

select 字段名 as 自定义名字 from 表名；

常量列查询

select 字段名 as 自定义名字，临时常量（例如总分）as 别名 from 表名；

合并列查询

select 字段名 as 自定义名字，（字段1+字段2）as 别名 from 表名；

条件查询（跟在where后面的关键字）

条件运算符
1.小于< 大于> 小于等于<= 大于等于>= 不等于<> 不等于!=(MySQl方言)
2.BETWEEN···AND 在···范围内
3.IN 包括
4.LIKE 模糊查询
5.IS NULL 是否为空
6.AND/&& 且
7.OR/|| 或
8.NOT/！非

模糊查询

select * from 表名 where 字段名 like 查询语法
%表示任意多个字符
_（下划线）表示单个字符

聚合查询

max（）：获取查询后结果的最大值 select max（字段名）from 表名；

min（）：获取查询后结果的最小值 select min（字段名）from 表名；

avg（）：获取查询后结果的平均值 select avg（字段名）from 表名；

sum（）：获取查询后结果的总和 select sum（字段名）from 表名；

count（）：获取查询后结果的总记录数 select count（字段名）from 表名；PS：字段名不建议用*

注意：一般我们在使用统计函数的时候，要注意使用泛查询（*）和使用列查询（id），是有区别的
泛查询会记录空值列（NULL）列查询则不会记录空值列
如果想让列查询也记录空值列要使用IFNULL（） SECELT COUNT（IFNULL（id，0））FROM 表名；
聚合函数可以连串使用 SECELT SUM（字段名）/COUNT（字段名）FROM 表名；

分页查询
关键字：LIMIT（MySQL方言）

select * from 表名 limit 参数1，参数2；
参数1：从第几条数据开始查询（默认从0开始记录）
参数2：一次查询多少条

分页的计算规则
select * from 表名 limit （当前页码-1）*每页条数，每页条数；

排序查询
关键字：ORDER BY
默认是升序ASC，降序DESC

单列排序：针对一个列进行单词排序

组合排序：当第一个列数值一致的时候，可以组合N个列进行第N次排序

分组查询
关键字：GROUP BY

统计每个地区有多少人
select address count（*）from 表名 group by address；
统计地区人数大于2个人的地区
select address count（*）from 表名 group by address having count（*）>=2；

多表查询

交叉连接查询
会产生笛卡尔积问题
解决方法：用主键外键进行限制

select 表1.字段名，表2.字段名 from 表1，表2；

内连接查询：两张表交叉后并且过滤后的数据查询（交集）
关键字：INNER JOIN

select * from a_table a inner join b_table b on a.a_id = b.b_id;

左（外）连接查询：左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录
右表记录不足的地方均为NULL
关键字：LEFT JOIN

SELECT * FROM a_table a left join b_table b ON a.a_id = b.b_id;

右（外）连接查询：左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来
左表记录不足的地方均为NULL
关键字：RIGHT JOIN

SELECT * FROM a_table a right outer join b_table b on a.a_id = b.b_id;

全连接查询：

(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB )

注意事项： 1.通过union连接的SQL它们分别单独取出的列数必须相同；
2.不要求合并的表列名称相同时，以第一个sql 表列名为准；
3.使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；
4.被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；
(select id,name from A order by id) union all (select id,name from B order by id); //没有排序效果
(select id,name from A ) union all (select id,name from B ) order by id; //有排序效果

子查询：将一个SQL语句的查询结果（单列数据）作为另一个SQL语句的查询条件

数据库控制语言（DCL）

存储过程

存储过程是一组SQL组成的逻辑语句，这个存储可以理解为一个对象体，
这个过程会持久的保存在数据库的服务器端，效率非常高

特点：1.存储过程保存在服务器端，只要通过客户端调用一次语句，就可以执行服务器端事先写好的SQL语句
2.依赖DDL
3.存储过程的移植性非常差

create procedure 存储过程名（传输类型参数名参数类型）
begin
SQL语句；
end

call 存储过程名；

传输类型有 IN输入参数 OUT输出参数 INOUT输入输出参数