MySQL思维导图详解版
需积分: 21 83 浏览量
2022-08-08
10:26:42
上传
评论
收藏 1.6MB PDF 举报
MySQL
MySQL基础
数据库基本操作
显示数据库
show databases;显示当前服务所含有的数据库(数
据集合)
创建数据库
create database [if not exists] 数据库名 [字符
集，校验规则] 括号中的部分可以不写
[if not exists] 当该数据库已经存在时不会报错不会
真实创建，当该数据库不存在时才会真实创建
数据库名不能是关键字，如果非要用关键字作为数据
库名，需要用反引号引起来
字符集，不同字符集所能表示的字符种类数不一样。
MySQL默认的是拉丁文，是不能表示中文字符的
设定字符集有两种方式
1，在创建数据库的时候手动指定，create
database xxx Character set utf8;这种指定只是限
制当前这个数据库的字符集
2，修改配置文件，一劳永逸
占到配置文件进行修改
重启mysql服务，注意不是重启客户端
创建新的库，因为原来的库是不会受到影响的
校验规则，就是指定按照什么标准来进行字符串的比
较
默认的校验规则是utf8_ general_ ci，注意这里是以
ci结尾的，这种校验规则下字符串比较是不分区大小
写的
相似的这种 utf8_ general_ cs,以cs结尾的这种比较
是区分大小写的
使用数据库
在想对一个数据库进行操作的时候，必须要先选中数
据库 use 数据库名;
像这个语句后面是可以不用加分号的，但是我们建议
我们在写指令的时候还是都把分号加上，这样也不用
特意记住哪些是可以不加的，知道有这种不加也正确
的情况存在就行
删除数据库 drop database [if not exists] 数据库名
if not exists 当数据库不存在时，执行不会报错
注意！！！！ 数据库的删除是一种很危险的操作，
一旦删除，数据基本就不太可能能找回来的
常用数据类型
数值类型
整形
BIT[M]，其中M是指定位数，默认是1，表示的是二
进制数，范围从1~64.所能表示的数值范围也就是2^
M-1
tinyint 1字节
smallint 2字节
int 4字节
bigint 8字节
最常用的是这两个，所能表示的数值范围更大
浮点型
float(M,D) 4字节
double(M,D) 8字节
decimal(M,D)
numeric(M,D)
M表示的是有效数字个数，D是小数点后个数。这两
个都是遵循IEEE754规则的，所以表示小数肯定是表
示不准确的。对于像表示钱这种类型的数据就不能用
这两个
两个作用一样，一般用decimal就好。可以精确的表
示小数
字符串类型
varchar(size)
可变长度字符串，其中size表示最多能放几个字符，
注意不是字节数，一个字符占多少个字节这个是取决
于你的字符编码的
text 长文本数据
mediumtext 中等长文本数据，比text更长
blob 二进制形式的长文本数据
一般这两个用的比较多
日期类型
datatime 8字节 最多可以表示到9999年的时间，一般都用datatime
timestamp 4字节 只能最多表示到2038年的时间
这种数据类型插入的时候表示形式是以字符串的形式
表示 "2020-08-02 120000" 。比较时间的大小还是
用 > < =
如果是想要插入当前时间，可以用函数now(),
insert into 表名 values(now());
表的操作
创建表
create table 表名(列名 类型 [comment “这是注释
”],列名 类型.......);
注意是类型在后面，comment是注释，这个只能在
创建表的时候用, -- 两个横线加一个空格是在任何时
候都能用的注释方法。表名也不能是关键字，非要用
用反引号引起来
查看表结构 desc 表名;
删除表 drop table [if not exists] 表名; 危险操作
展示该数据库里面的所有表 show tables;
MySQL 表的增删改查
初阶
新增(create)
insert into 表名 values(xxx,xxx...)，values()括号
里面的内容要和表的列的类型，个数对应起来
几种形式的插入
单行数据，全列插入 eg: insert into person values("张三",19);
多行数据，全列插入
eg: insert into person values ("李四",20),("王
五",22);
多行或单行数据，指定列插入
eg: insert into person (name) values ("xxx"),("
vvv");
查询(retrieve)
全列查询 select * from 表名
这其实也是一个危险操作，我们在查询的时候，服务
器回去读取磁盘，并把读取到的数据通过网卡写回到
客户端。如果数据量过大，全列查询的时间就会很
长，甚至于把磁盘IO和网卡带宽直接占满
指定列查询 select 列名,列名..... from 表名
这样就只会查询出你指定列的结果。注意，没显示的
列不是说数据库就删了，我们所有的查询结果只是根
据你的要求而生成的一个临时表，和数据库里面的表
是两回事
查询字段为表达式(列和列进行计算)
select 表达式 from 表名 表达式是根据表中的列写
出来的
eg: select name,chinese+math+english from
exam_result;
可以给表达式取别名 select 表达式 as 别名 from 表名
eg: select name,chinese+math+english as
total from exam_result;
可能表达式运算出的结果是不符合我们的数据库的列
的类型要求的，可能会超范围，这是无所谓的，还是
那句话，这是一个临时表，和数据库的表没关系
去重 关键字 distinct select distinct 列名,... from 表名
可以是单列去重 eg: select distinct math from exam_result;
也可以是多列去重
eg: select distinct chinese,math from exam_
result;
多列去重的话就不是单单看一列了，是两列对应位置
上都是重复的才算是重复的，才会被去掉
排序
注意：我们在查询数据的时候，可能会看到有的列就
是有序的，但实际上我们的数据库是没有做出过有序
的承诺的，可能只是这个时候默认他就是这么排的，
或者你就是这么插入进去的，换个环境可能就不满足
了。要想查询的结果是有序的，务必使用order by
select 列名,...... from 表名 order by 列名 asc/
desc
排序的时候默认是升序的，asc是可以不写的
降序的是desc,是必须指明的。注意这个desc和描述
表结构的那个desc是两个单词的缩写
使用别名和表达式进行排序
eg : select name,chinese+math+english as
total from exam_result order by total desc;
可以指定多个列进行排序，放在前面的比较优先级
高,当前面的相同无法比较的时候就要看后面列的顺
序了
eg: select name,math,chinese from exam_
result order by math,chinese;
多个列也可以各自指定升序或者是降序 eg:
select * from exam_result order by math desc,
chinese asc;
对于null的排序，它视为比任何值都要小
条件查询
运算符
比较运算符
> < >= <=
= <=>
!= <>
between and
in
is null
is not null
like
模糊查询，其中— 能够匹配任意一个字符，%能够匹
配任意个字符包括0个,在进行匹配的时候，是否区分
大小写是要看我们的校验规则的
逻辑运算符
and
or
not
and的优先级高于or，运用的时候要考虑二者的执行
顺序的关系
里面主要注意和null的关系比较
= != <>
= 不能用来比较null的关系，当是null = null的时候
返回值是null，相当于
false。 != <> 他们表示不相等，
与=意思相反，但是也是不能和null比较，有一方是
null结果就是null
<=> is null is not null
这三个都是能够用来比较和null的关系的，当然也适
用于基本类型
where条件里面不能使用别名
首先理解条件查询的过程：首先会根据你的条件遍历
数据表选出符合条件的记录，然后再执行前半部分的
select语句，选出哪些列是你要进行展示的
所以既然where的部分先执行，但是你的别名是在前
面定义的，所以在where看来我根本就不认识这个变
量
错误示范 ： mysql> select name,chinese+math+
english as total from exam_result where
total > 200;
ERROR 1054 (42S22): Unknown column 'total'
in 'where clause'
分页查询 关键字 limit
select * from 表名 limit n
查询前n条记录
select * from 表名 limit n offset m
从m开始查询n条记录，m从0开始
这些所有的查询都是可以混合起来用的
eg ：select name,chinese+math+english as
total from exam_result where name != "猪悟
能" order by total limit 3 offset 1; 注意后面各个
部分的顺序，where是紧挨着from,limit一般在最后,
order by 在中间
修改(update)
update 表名 set 列值 = 值 where/order by ....
eg : update exam_result set math = math + 10
order by math+chinese+english asc limit 3;
update也是危险操作，这个不像上面查询的是临时
表，这个修改修改的是真正服务器中的表的数据
所谓更新就是更新修改哪个表的哪些行的哪些列修改
为哪些值
删除(delete) delete from 表名 where..../order by../limit..
当你如果后面不跟条件的时候，就会把整张表的记录
删除，有点类似于删表操作。但是也不完全一样，删
表之后表是不存在的，这里只是把所有记录删除了，
表还是存在的
进阶
数据库的约束
约束是干啥的？ 约束就是用来限制列的数据类型，
强制让编译器来对数据库中的记录进行检查
约束类型
not null 非空约束，指定列不能是空
unique 保证指定列的值必须唯一
每次插入的时候都会先遍历表看看这个值是否已经存
在，如果存在，就是不能插入的
default 规定该列没有赋值的时候的默认值
primary key 主键,针对每一条记录进行一个身份标
识
主键具有唯一性，并且不为空，一个表的主键只能有
一个
为了更好生成不重复的主键，而不用每次手动赋值，
mysql提供了自增主键
create table student(
-> id int primary key auto_increment,
-> name varchar(
20));
其中auto_increment就是自增主键
设置了自增主键之后，插入记录的时候，我们可以自
己指定，如果不自己指定，那就把相应位置写成
null，mysql会根据你当前记录里面主键的最大值的
基础上去自增
当前这个自增主键，对于全都在一台服务器上的数据
是没有问题的。但是当数据量过大等原因，往往会有
数据库的分布式部署，也就是将数据分散到多个服务
器上，这样就只是逻辑上是一张表了，数据都分散到
了不同的服务器，那这个时候自增主键就失效了，因
为不能保证不同服务器之间的主键的唯一性，那要怎
么解决呢？（面试点！！！！）
现在就需要我们自己去设计一个能适用于分布式系统
上的主键生成算法：(时间戳 + 机房编号,主机编
号 + 随即因子)--->计算一个哈希值，得到唯一的主
键编号。在实际工程上，其中随即因子重复的可能性
很小很小，所以就直接忽略不计了，只能说理论上是
有重复的可能的。
foreign key 外键，把两张表关联起来
一旦设置了外键，那么在子表进行插入更新数据的时
候，父表就对子表有了约束，要求必须是父表中存在
的
同样的，因为子表里面用着父表里的数据，所以子表
对于父表也是有约束的，比如在子表存在的情况下，
你是删除不了父表，也删除修改不了某些记录
扩展：外键的逻辑删除
场景：有两个表，一个是商品表，一个是订单表。假
设现在商家上下架一款商品，但是订单表中还有这个
商品的记录，那我们要怎么办？因为直接把商品表中
的商品记录删除是删不掉的，子表还在用。所以这就
有了逻辑删除，用一个列的值来标识这个记录是否有
效，不是真的删除，只是将其标记为无效。下次插入
的时候检测到为无效，就不能进行插入
check
表的设计 数据库的设计
根据需求，确定出实体（表）
梳理出实体与实体之间的关系（表与表之间的关系）
一对一
一对多
多对多
没有任何关系
新增 可以将一个表里面的查询结果直接插入到另一个表中
eg： insert into student1 select * from
student;
只不过要注意，我们插入也是可以指定列的，后面的
选择也是可以指定列输出。所以不管怎么样，前后的
列的个数，类型必须要对应，才能完成这种操作
查询
聚合查询
聚合查询就是查询出的同一列的行与行之间进行计
算。之前的查询带表达式是同一行的列与列之间的计
算
聚合函数
count() 返回查询到的数据的数量，也就是行数
count(*) 即使是空值也会被记录 select count(*)
from exam_result 【where..】;
count(列名) 如果是空值则不会进行记录 select
count(id) from exam_result 【where..】;
sum() 将查询到的行的值进行总和
select sum(chinese) from exam_result 【
where...】;
avg() 将查询到的值求平均
select avg(chinese) from exam_result 【
where...】;
max() 选出查询结果中的最大值
select max(math) from exam_result【
where...】;
min() 选出查询结果中的最小值
select min(math) from exam_result 【
where...】;
这几个运算都只针对数字，不是数字没有任何意义，
遇到null也会跳过
group by子句
根据你的查询结果进行分组，把值相同的分成一组，
然后对于每一组，我们都可以进行聚合
注意，在进行分组查询的时候，只有用来分组的这一
列或者是宏观上的关于组的信息，它是可以直接进行
输出的，其他组内的一些具体信息你是输出不全的，
只有搭配聚合函数使用
分组查询
先查询再分组
先分组再查询 分组之后的筛选只能用having子句，不能用where
having子句
联合查询
联合查询就是多张表进行笛卡尔积然后筛选出你要的
结果的过程
不过注意，当表比较大，数据量很多的情况下，笛卡
尔积的效率会很低，甚至说成为一个危险操作
联合查询 = 笛卡尔积 + 连接条件 + 其他条件(看具体
需求)
笛卡尔积的写法
表名,表名 条件跟在where后
表名 join 表名 条件跟在on后
多表之间可能列名之间会冲突，所以经常会使用表名
做前缀
内连接 join/inner join/表名,表名
外连接
left join 左外连接
左外连接会把左侧表的信息都给展示完全，当右侧表
中没有对应的关系的时候，会把右侧表对应列填充为
null
right join 右外连接 与左外连接相反
扩充：如果左右不对应，又想把两边的信息都给展示
完全，那还有一种操作叫做全外连接，只不过这个操
作在MySQL里面是不支持的
自连接 自己和自己做笛卡尔积，把行关系转换为列关系
运行开销比较大
可读性差
子查询
本质就是把多个查询语句组合成一个查询语句，运用
一个查询的临时表结果，发起另一组查询
合并查询 把多个查询的结果集合并成一个
union 合并的时候如果有重复的元素是会去重的
union all 合并的时候如果有重复的元素不会去重
如果说左右两个表的数据都是一一对应的时候，其实
内连接与外连接是没有什么区别的。但是如果说其中
一张表的数据在另一张表里面不存在，不是一一对应
关系的时候，内连接与外连接的区别就会很大
评论0