MySQL思维导图
2021-06-29 22:07:07 0 举报
为你推荐
查看更多
Mysql思维导图
作者其他创作
大纲/内容
MySQL
表于表之间的关系
外键
一对多关系
foreign key\t1 一对多表关系 外键字段建在多的一方 2 在创建表的时候 一定要先建被关联表 3 在录入数据的时候 也必须先录入被关联表
级联更新、级联删除
# 修改dep表里面的id字段update dep set id=200 where id=2; 不行# 删除dep表里面的数据delete from dep; 不行
# 1 先删除教学部对应的员工数据 之后再删除部门\t操作太过繁琐 # 2 真正做到数据之间有关系\t更新就同步更新 删除就同步删除
\"\"\"级联更新 >>> 同步更新级联删除 >>> 同步删除\"\"\
多对多关系
一对一
修改表
# MySQL对大小写是不敏感的\"\"\"1 修改表名\talter table 表名 rename 新表名;2 增加字段\talter table 表名 add 字段名 字段类型(宽度) 约束条件;\talter table 表名 add 字段名 字段类型(宽度) 约束条件 first;\talter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;3 删除字段\talter table 表名 drop 字段名;4 修改字段\talter table 表名 modify 字段名 字段类型(宽度) 约束条件;\t\talter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;\t\"\"\"
复制表
\"\"\"我们sql语句查询的结果其实也是一张虚拟表\"\"\"create table 表名 select * from 旧表; 不能复制主键 外键 ...create table new_dep2 select * from dep where id>3;
表查询
前期表准备
where筛选条件
group by分组
# 分组实际应用场景 分组应用场景非常的多\t男女比例 部门平均薪资 部门秃头率 国家之间数据统计# 1\t按照部门分组select * from emp group by post;\"\"\"分组之后 最小可操作单位应该是组 还不再是组内的单个数据\t上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)\t如果设置了严格模式 那么上述命令会直接报错 \"\"\
分组注意事项
having分组之后的筛选条件
\"\"\"having的语法根where是一致的只不过having是在分组之后进行的过滤操作即having是可以直接使用聚合函数的\"\"\
distinct去重
\"\"\
order排序
select * from emp order by salary;select * from emp order by salary asc;select * from emp order by salary desc;\"\"\"order by默认是升序 asc 该asc可以省略不写也可以修改为降序 desc\"\"\
limit限制展示条数
select * from emp;\"\"\"针对数据过多的情况 我们通常都是做分页处理\"\"\
正则
mysql也支持正则
select * from emp where name regexp '^j.*(n|y)$';
多表操作
多表查询
子查询
\"\"\"子查询就是我们平时解决问题的思路\t分步骤解决问题\t\t第一步\t\t第二步\t\t...将一个查询语句的结果当做另外一个查询语句的条件去用\"\"\
总结
表的查询结果可以作为其他表的查询条件也可以通过起别名的方式把它作为一个张虚拟表根其他表关联\"\"\"多表查询就两种方式\t先拼接表再查询\t子查询 一步一步来\"\"\"
navicat软件
练习题
\"\"\"课下一定要把握上课将的这几道题全部自己独立的理解并写出来在解决sql查询问题的时候 不要慌一步一步慢慢来 最终能够东拼西凑出来就过关了！！！\"\"\
sql注入
\"\"\"利用一些语法的特性 书写一些特点的语句实现固定的语法MySQL利用的是MySQL的注释语法select * from user where name='jason' -- jhsadklsajdkla' and password=''select * from user where name='xxx' or 1=1 -- sakjdkljakldjasl' and password=''\"\"\
数据库介绍
数据库的本质：本质其实就是一款基于网络通信的应用程序
关系型数据库
1. 数据之间彼此有关系或约束
2. 存储数据的变现形式通常是以表格存储
非关系性数据库
存储数据通常是以k，v键值对的形式
MySQL的安装
网站：www.mysql.com
目录结构
客户端：bin目录下的mysql.exe
服务端：bin目录下的mysqld.exe
启动
以管理员身份运行，因为部分命令仅管理员身份有效
服务端：先切换到mysqld所在目录下，然后输入mysqld即可启动服务端
客户端：保留原来窗口，重新打开cmd窗口客户端链接服务端完整命令：
mysql -h 127.0.0.1 -P 3306 -uroot -p
环境变量配置
将bin目录添加到环境变量中
将mysql服务端制作成系统服务（开机自启）
安装mysql服务：mysqld --install
卸载mysql服务：mysqld --remove
统一编码配置
my-default.ini 文件是默认配置模板，程序启动会先夹在配置文件中的配置之后才真正的启动
[mysqld] # 一旦服务端启动立刻加载这下面的配置[mysql] # 一旦客户端启动立刻加载这下面的配置[client] # 其他客户端
新建一个my.ini配置文件，然后添加配置，修改配置文件后一定要重启服务才能生效
[mysqld]character-set-server=utf8collation-server=utf8_general_ci[client]default-character-set=utf8[mysql]user=\"root\" # 此是直接将用户名密码添加到配置文件中，使启动mysql时候，直接以root账号登录password=123456default-character-set=utf8
SQL语句初识
1. MySQl中的sql语句是以分号作为结束的标志
2. 基本命令
查看所有库名：show databases;
3. 连接服务的的命令简写：mysql -uroot -p（默认本地，默认端口形式连接）
4. 当你输入的命令不对，又不想让服务端执行并返回错误信息是，可以用 /c 来取消
5. 客户端退出：退出命令加不加分好都可以执行
quit
exit
6. 当你在连接服务端的时候，只输入mysql也能链接，但此时不是管理员身份，而是一个游客身份
7. 设置密码：
mysqladmin -uroot -p原密码 password 新密码
示例：mysqladmin -uroot -p123 password 123456
8. 重置密码：
1. 先关闭当前mysql服务端；然后以命令行方式启动服务端（让mysql跳过用户名密码验证功能）
mysqld --skip-grant-tables
2. 以无密码方式连接
mysql -uroot -p 直接回车
3. 修改当前用户的密码：
update mysql.user set password=password(123456) where user='root' and host='localhost';
4. 立刻将修改数据刷到硬盘
flush privileges;
5. 关闭当前服务端，然后以正常校验授权表的形式启动
针对库的增删改查（文件夹）
增
create database db1;
新增库，并制定编码格式：cerate database db2 charset='gbk';
查
查所有库：show databases;
查单个库，且查询的是创建库的语句：show create database db1;
改
alter database db2 charset='utf8';
删
drop database db2;
针对表的增删改查（文件）
在操作表（文件）的时候，需要指定所在的库（文件夹）
查看当前所在库的名字
select database();
切换库
use db1;
也可以用绝对路径的形式操作不同的库
create table db2.t1(id int);
查看当前库下所有的表名：show tables;
查看某个表的创建语句：show create table t1;
查看表结构：describe t1;简写：desc t1;
alter table t1 modify name char(16);
drop table t1;
针对数据的增删改查（一行行数据）
一定要先有库，有表，才能操作记录
当数据量特别大的时候，不建议使用该命令select * from t1;
查询该表中，所有的name字段select name from t1;
update t1 set name='dsb' where id > 1;
delete from t1 where id > 1;
delete from t1 where name='jason';
将表所有的数据清空
delete from t1;
存储引擎
介绍
innodb
是MySQL5.5版本及之后默认的存储引擎数据存储更加安全
myisam
是MySQL5.5版本之前默认的存储引擎速度要比innodb快，但是我们更加注重的是数据的安全
memory
内存引擎（数据全部存放在内存中）断电数据丢失
blackhole
无论什么时候，都立刻消失（黑洞）
使用
查看所有的存储引擎
show engines;
不同的存储引擎在存储表的时候，异同点
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;
基本数据类型
创建表的完整语法
语法
注意
1. 同一张表中字段名不能重复
2. 宽度和约束条件是可选的（可写可不写）而字段名和字段类型是必须的约束条件写的话，也支持写多个
3. 最后一行不能有逗号
补充
宽度
一般情况下指的是对存储数据的限制
create table t7(name char); # 默认宽度是1 insert into t7 values('jason'); # 会自动截取 insert into t7 values(null); # 会存关键字NULL
5.6版本默认没有开启严格模式，规定只能存一个字符，你给了多个字符，那么MySQL会自动帮你截取
5.7版本及以上或者开启了严格模式，那么规定只能存几个，就不能超，一旦超出范围，会立刻报错 Data too long for ...
严格模式到底开不开呢？
5.7以后的版本默认都是开启严格模式的
使用数据库的准则：能尽量少的让数据库干活就尽量少，不要给数据库增加额外的压力
约束条件 null not null 不能插入null
总结：宽度和约束条件到底是什么关系
宽度是用来限制数据的存储约束条件是在宽度的基础之上增加的额外的约束
严格模式
查看严格模式
show variables like '%mode';
'%mode'模糊匹配/查询 %:匹配任意多个字符 _:匹配任意单个字符
修改严格模式
set session # 只在当前窗口有效set global # 全局有效set global sql_mode = 'STRICT_TRANS_TABLES';修改完之后，冲新进入服务端即可
整型
分类tinyint smallint meduimnt int bigint
作用：存储年龄、等级、id、号码等
以tinyint为例
是否有符号默认情况下是带符号的
超出会如何：超出限制只存最大可接受值
约束条件之unsigned。无符号
create table t10(id tinyint unsigned);
int 也是默认带符号的，整型默认情况下都是带有符号的
只有整形括号里面的数字不是表示限制位数id int(8) 如果数字没有超出8位，那么默认用空格填充至8位 如果数字超出了8位，那么有几位就存几位(但是还是要遵守最大范围)create table t13(id int(8) unsigned zerofill); # 用0填充至8位针对整型字段，括号内无需指定宽度，因为它默认的宽度（11位）足够显示所有的数据了
浮点型
分类：FLOAT DOUBLE DECIMAL
作用：身高、体重、薪资
float < double < decimal# 要结合实际应用场景 三者都能使用
字符类型
char 定长 char(4) 数据超过4个字符直接报错，不够4个字符空格不全varchar 变长 varchar(4) 数据超过4个字符直接报错，不够有几个存几个
create table t18(name char(4));create table t19(name varchar(4));
char_length统计字段长度
select char_length(name) form t18;
char与varchar对比
char 缺点：浪费空间 优点：存取都很简单 直接按照固定字符存储数据即可 存按照5个字符存，取也直接按照5个字符取，速度就会相对varchar快
varchar 优点：节省空间 缺点：存取较为麻烦 存的时候需要制作报头 取的时候也要先读取报头，之后才能读取真实数据
时间类型
date：年月日 2021-6-20
datetime：年月日时分秒 2021-6-20 11:11:11
time：时分秒 11:11:11
Year：2021
枚举与集合类型
分类： 枚举（enum）：多选一 集合（set）：多选多
约束条件
default默认值
unique唯一
# 联合唯一\"\"\"ip和port单个都可以重复 但是加载一起必须是唯一的\"\"\
primary key主键
# 2 如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它 就无法提示查询速度
# 3 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键以后我们在创建表的时候id字段一定要加primary key
# 单个字段主键create table t5( id int primary key\tname char(16));
auth_increment自增
delete from t1 删除表中数据后 主键的自增不会停止truncate t1 清空表数据并且重置主键
结论
以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候id int primary key auto_increment
视图
什么是视图
视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用其实视图也是表
为什么要用视图
如果要频繁的操作一张虚拟表(拼表组成的)，你就可以制作成视图 后续直接操作
如何操作
# 固定语法create view 表名 as 虚拟表的查询sql语句# 具体操作create view teacher2course asselect * from teacher INNER JOIN courseon teacher.tid = course.teacher_id;
注意：
1 创建视图在硬盘上只会有表结构 没有表数据(数据还是来自于之前的表)2 视图一般只用来查询 里面的数据不要继续修改 可能会影响真正的表3 视图基本上不用不好维护
触发器
概念
在满足对表数据进行增、删、改的情况下，自动触发的功能使用触发器可以帮助我们实现监控、日志...触发器可以在六种情况下自动触发 增前 增后 删前删后 改前改后
语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名for each rowbegin\tsql语句end
# 具体使用 针对触发器的名字 我们通常需要做到见名知意# 针对增create trigger tri_before_insert_t1 before insert on t1for each rowbegin\tsql语句endcreate trigger tri_after_insert_t1 after insert on t1for each rowbegin\tsql语句end\"\"\"针对删除和修改 书写格式一致\"\"\"ps:修改MySQL默认的语句结束符 只作用于当前窗口\tdelimiter $$ 将默认的结束符号由;改为$$ delimiter ;
案例
建表
当cmd表中的记录succes字段是no那么就触发触发器的执行去errlog表中插入数据NEW指代的就是一条条数据对象
写触发器
朝cmd表插入数据
删除触发器
drop trigger tri_after_insert_cmd;
事务
什么是事务
开启一个事务可以包含多条sql语句 这些sql语句要么同时成功要么一个都别想成功 称之为事务的原子性
事务的作用
\"\"\"保证了对数据操作的安全性\"\"\"eg:还钱的例子 egon用银行卡给我的支付宝转账1000 \t1 将egon银行卡账户的数据减1000块 2 将jason支付宝账户的数据加1000块 \t你在操作多条数据的时候可能会出现某几条操作不成功的情况
事务的四大特性
\"\"\"ACIDA:原子性\t一个事务是一个不可分割的单位，事务中包含的诸多操作\t要么同时成功要么同时失败C:一致性\t事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态\t一致性跟原子性是密切相关的I:隔离性\t一个事务的执行不能被其他事务干扰\t（即一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰的）D:持久性\t也叫\"永久性\"\t一个事务一旦提交成功执行成功 那么它对数据库中数据的修改应该是永久的\t接下来的其他操作或者故障不应该对其有任何的影响\"\"\"
如何使用事务
# 事务相关的关键字# 1 开启事务start transaction;# 2 回滚(回到事务执行之前的状态)rollback;# 3 确认(确认之后就无法回滚了)commit;
\"\"\"模拟转账功能\"\"\
存储过程
存储过程就类似于python中的自定义函数它的内部包含了一系列可以执行的sql语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部sql语句的执行
基本使用
存储过程演示
pymysql模块中如何调用存储过程
函数
跟存储过程是有区别的，存储过程是自定义函数，函数就类似于是内置函数
自行了解
流程控制
# if判断delimiter //CREATE PROCEDURE proc_if ()BEGIN declare i int default 0; if i = 1 THEN SELECT 1; ELSEIF i = 2 THEN SELECT 2; ELSE SELECT 7; END IF;END //delimiter ;# while循环delimiter //CREATE PROCEDURE proc_while ()BEGIN DECLARE num INT ; SET num = 0 ; WHILE num < 10 DO SELECT num ; SET num = num + 1 ; END WHILE ;
索引
\"\"\"1 当表中有大量数据存在的前提下 创建索引速度会很慢2 在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低\"\"\"索引不要随意的创建！！！
b+树
\"\"\"只有叶子节点存放的是真实的数据 其他节点存放的是虚拟数据 仅仅是用来指路的树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)一个磁盘块存储是有限制的为什么建议你将id字段作为索引\t占得空间少 一个磁盘块能够存储的数据多\t那么久降低了树的高度 从而减少查询次数\"\"\"
聚集索引（primary key）
\"\"\"聚集索引指的就是主键 Innodb 只有两个文件 直接将主键存放在了idb表中 MyIsam 三个文件 单独将索引存在一个文件\"\"\"
\"\"\"叶子节点存放的是数据对应的主键值\t先按照辅助索引拿到数据的主键值\t之后还是需要去主键的聚集索引里面查询数据\"\"\"
覆盖索引
在辅助索引的叶子节点就已经拿到了需要的数据
# 给name设置辅助索引select name from user where name='jason';# 非覆盖索引select age from user where name='jason';
0 条评论
回复 删除
下一页
