MySql学习记录：基础总结

发表于 分类于 阅读次数： 本文字数： 4k 阅读时长 ≈ 15 分钟

  本文将个人在MySql数据库学习中记录的知识点进行了总结，方便日后的复习以及对明年春招实习的准备。

一、基础

启动/停止MySql服务：

1
net start/stop mysql;

MySql登录：

1
2
3
4
5
mysql -u用户 -p密码

此处推荐先不填密码直接回车，随后输入密码。因为上面直接输入密码有密码泄露的风险。
mysql -u用户 -p
Enter password: **********

MySql中数据类型：

1
2
3
4
5
6
7
8
9
10
varchar		可变长度字符串 智能节省空间 但是速度慢
char		不可变长度字符串 速度快 但是不会智能节省空间	
int 		整数型(11)
bigint		长整型
float		单精度浮点数
double		双精度浮点数
date		短日期	年-月-日		 	%Y-%m-%d
datetime	长日期	年-月-日 时:分:秒 	%Y-%m-%d %h:%i:%s
clob		字符大对象 最大4G字符串 超过255字节的都用它
blob		图片、声音、视频的等媒体二进制数据

导入/导出数据库文件：

1
2
3
4
5
#导出数据库
mysqldump 库/表 > 地址.sql -uroot -p密码;

#导入数据库
source 地址;

二、关键字 show / use / describe

1. show

查看所有数据库：

1
show databases;

查看当前库的表：

1
show tables;

查看数据库支持的储存引擎：

1
2
3
4
5
写法一：show engines;
写法二：show engines \g
写法三：show engines \G

写法一和二结果是一样的，写法三是以每一行来显示。因为\g和;都是结束符，所以加一个即可。

查看数据库变量：

1
2
3
show variables like 'have%';

#此句即为模糊搜索have开头的变量。

查看建表语句：

1
2
3
4
show create table 表名;

#其中最下面一行信息为：使用的储存引擎、默认编码方式
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2. use

选择使用数据库：

1
use 数据库名;

3. describe

查看表结构：

1
2
describe 表名;
desc 表名; (缩写)

三、关键字 create / drop / alter

1. create

创建数据库：

1
create database 数据库名;

创建表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#表名推荐 t_ 或 tbl_ 开头  数据类型看最上面
create table 表名( 
	字段1 数据类型1 default 默认值(可选),
	字段2 数据类型2 列级约束(可选),
	字段3 数据类型3,
	...
	表级约束(可选), #约束的内容在后面
	)
	ENGINE=指定引擎 default CHARSET=指定字符编码方式 #此句可选
	;
	
	MySql默认储存引擎为：InnoDB
	默认字符编码方式为：utf8mb4

#将一个select查询结构建立为新表
create table 表名 as select _______;

2. drop

删除数据库：

1
2
drop database 数据库名;
drop database if exists 数据库名; #如果数据库存在则进行删库操作

删除表：

1
2
drop table 表名;
drop table if exists 表名; #如果表存在则进行删表操作

3. alter

改表名：

1
2
alter table 老名字 rename 新名字;
alter table 老名字 rename to 新名字;

改字段名：

1
2
alter table 表名 change 原字段名 新字段名 数据类型;
alter table 表名 change column 原字段名 新字段名 数据类型;

添加字段：

1
2
alter table 表名 add column 字段名 数据类型;
alter table 表名 add column 字段名 数据类型 after 字段名;

删除字段：

1
alter table 表名 drop column 字段名;

四、分组函数

1. 单行处理函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select中函数使用:
select 函数(字段名) from 表名;

lower(数据)									全小写
upper(数据)									全大写
substr(数据,起始下标,长度)					取字符串		#和c++的substr差不多 不过下标是从1开始
length(数据)								取长度
str_to_date(数据,'格式')						字符串转日期 
date_format(数据,'格式')						格式化日期
format(数据,保留小数位数,分隔符位置(可选))	格式化
round(数据)									四舍五入
rand()										取随机数
ifnull(数据,变值)							把null转为具体值
concat(数据1，数据2,...)						拼接字符串
trim(数据)									去前后空格

2. 多行处理函数

1
2
3
4
5
6
7
8
单行处理函数即为：对某一字段一行的数据进行处理
多行处理函数即为：对该字段下所有数据进行处理

count(字段)			计数 即统计数据个数	count(*)为总行数
sum(字段)			求和
avg(字段)			平均值
max(字段)			最大值
min(字段)			最小值

五、关键字 select / distinct 及查询相关

1. select

查看版本号：

1
select version();

查看当前选择的数据库：

1
select database();

查看表:

1
2
3
#查多个字段则逗号隔开
select 字段名(as 别名) from 表名;
select * from 表名; #查看整张表

2. distinct

去重关键字：

1
2
3
select distinct 字段名1,字段名2,... from 表名;

字段内容去重，该关键字只能放在最前面，表示对后面 字段集合 查询结果进行去重。

3. 查询语句顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
语法顺序：
select... 		查询
from...			获取数据
where...		筛选
group by...		分组
having...		分组后筛选
order by...		排序
limit...		取指定条数数据

执行顺序：
from...			获取数据
where...		筛选
group by...		分组
having...		分组后筛选
select...		查询
order by...		排序
limit...		取指定条数数据

4. 条件查询相关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
条件查询关键字：where
例：select 字段 from 表 where 字段 = 值;

<					小于
<=					小于等于
>					大于
>=					大于等于
!= 或 <>			不等于
between...and...	两个值之间，即 >= and <=
is null				为空 
is not null 		不为空
and					并且
or					或者
in(值1,值2,...)		包含
not in(值1,值2,...)	不包含
not					可以取非 主要用于 in 和 is 中
like				模糊搜索 %为多个字符 _为一个字符 例：%z% 即为包含z的字符串

5. 分组查询相关

1
2
3
4
5
6
7
8
9
10
11
12
13
多行处理函数的原理是先对数据进行分组，随后对该组数据进行处理。
所以多行处理函数需要在分组后才能使用(会默认进行分组)。

所以在where语句后面无法使用多行处理函数。
因为此时还没进行分组操作，详情请看上面的执行顺序。

当你需要对函数操作后的数据进行判断时，例如该行数据是否等于此组数据的平均值，就需要使用group by进行分组。
分组后，使用having进行判定。

例：select count,name from t_student group by count,name having count = avg(count);
此句中，对分数与姓名进行分组，当分数等于分数的平均值时，输出学生姓名与分数。

注：group by分组后，select后只能跟参加分组的字段，以及分组函数。

重点： having的效率是偏低的，我们尽量用where进行筛选。where实在实现不了时，再用having进行筛选。

6. 连接查询相关

1
2
3
4
5
当我们需要对多张表的内容进行查询汇总时，例如取表1里的a字段与表2里的b字段，就需要进行表的连接。

表的连接分为内连接和外连接。而连接的机制类似两个集合相乘，所以当无限制连接查询时，会产生笛卡尔积现象。

我们尽量要少进行表的连接，因为过多的连接会大大增大遍历的次数。
  • 内连接
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    inner join即为内连接，内连接查询后的结果不含NULL，只包含符合条件的结果。

    SQL99写法: (推荐)                       	SQL92写法: (不推荐)

    select 字段								select 字段
    from 表1,								from 表1,表2
    (inner)join 表2,						where 条件;
    on 条件(连接条件),
    where 条件(查询条件);

    可以看出，sql99语法将连接条件分离出来，使连接操作更加清晰明了。
  • 外连接
    外连接
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    outer join即为外连接，外连接中分为左外连接与右外连接。
    如上图，左/右即为包含左/右表的全部内容，以及另外一张表里符合条件的内容。

    外连接结果去掉含NULL的结果，即为内连接查询结果。

    左外连接：                				右外连接：    

    select 字段								select 字段
    from 表1,								from 表1,	
    left (outer) join 表2,					right (outer) join 表2,		
    on 条件(连接条件),						on 条件(连接条件),
    where 条件(查询条件);					where 条件(查询条件);

    当查询中不存在主表，只需要符合条件的结果时，使用内连接。
    当存在主表，需要依据主表得出结果时，使用外连接。
      一个连接查询语句中，可以连接多个表，且内连接和外连接可以混合。
  • union 合并
1
2
3
4
5
6
select _________
union
select _________;

将两次查询的结果合并，要求列数相同，数据类型一致。
好处是减少连接，效率高。

7. order by 与 limit

1
2
3
4
order by 字段名 desc; 	降序排序
order by 字段名 asc; 	升序排序(默认)

limit 起始下标,长度;		显示从起始下标开始的指定数量信息。起始下标从0开始。(这个重要)

六、关键字 insert / update / delete

1. insert

插入数据：

1
2
3
4
5
6
7
insert into 表名(字段1,字段2,...) values (值1,值2,...);

#表名后面那一部分可以省略，如省略则等于全写，后面值 得按顺序写全。
insert into 表名 values (值1,值2,...);

#同时插入多条数据
insert into 表名(字段1,字段2,...) values (值1,值2,...),(值1,值2,...),(值1,值2,...);

2. update

修改数据：

1
2
3
update 表名 set 字段1=值1,字段2=值2,... where 条件;

如果无条件，则对整张表进行修改

3. delete 与 truncate

删除数据：

1
2
3
4
5
6
7
#条件删除
delete from 表名 where 条件;
空间不释放，效率低，但是在开启事务时支持回滚。

#此为删除整张表 
truncate table 表名;
物理删除，非常快，但是不支持回滚。

七、约束

1
2
3
4
5
6
7
8
9
约束一共有四种：

非空约束：		not null
唯一性约束：		unique
主键约束：		primary key
外键约束：		foreign key

列级约束：直接create建表时在字段后加。
表级约束：在建表语句后面添加，详情看上面的create关键字相关。
  • not null
    1
    顾名思义，添加此约束后，此字段不能为空。
  • unique
    1
    2
    添加此约束后，该字端内容唯一，不会重复。在尝试insert重复信息后，会报错。
    可以 unique(字段1,字段2,...) 添加联合唯一表级约束。
  • primary key
    1
    2
    3
    4
    主键约束即为 不可重复 且 不为空。
    mysql中同时添加非空约束和唯一约束，则自动添加主键约束。
    primary key auto_increment 自增主键，从1开始。
    主键分为 业务主键 和 自然主键(和业务无关)，一般推荐自然主键，方便日后的修改。
  • foreign key
    1
    2
    3
    4
    外键约束即为将 此字段 与 另外一张表内的指定字段 相关。
    使得该字段内的值，只能为另外一张表内的指定字段的内容。
    foreign key(字段) references 表名(字段) #添加外键约束
    			 子					  父

八、事务

1. transaction

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
事务即为 '一个完整的业务逻辑' ，是最小工作单元。
批量的DML语句 同时成功 或 同时失败，即为事务transaction。
 (即增删改)
 
#首先应该开启事务
start transaction;

#提交事务	相当于存档
commit;

#回滚事务	相当于读档
rollback;

特性：
原子性 ———— 最小工作单元，不可分
一致性 ———— 同时成功/失败
隔离性 ———— 两事务直接存在隔离
持久性 ———— 事务结束的一个保障

2. 隔离级别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
事务隔离级别一共有四种：
1.read uncommited	读未提交
2.read commited		读已提交
3.repeatable read	可重复读(mysql默认隔离级别)
4.serializable		序列化/串行化
以上四种级别，隔离程度由低到高。

#设置级别
set global transaction level 级别;

#查看当前隔离级别
select @@tx_isolation;
select @@transaction_isolation;
以上有一个可用。
  • read uncommited
    1
    2
    3
    事务A可读取到事务B的未提交数据。
    也就是说事务B输入了DML语句，但是没有commit，从事务A中也可以读到没有commit的结果。
    可能造成'脏读'，即读入脏数据。
  • read commited
    1
    2
    事务A只可读取到事务B提交后的数据。
    即事务A中只可以读到事务B commit后的结果。
  • repeatable read
    1
    2
    3
    4
    事务A读取一次数据后，以后每次读取数据都和第一次读到的数据一样(开启事务时的数据)。
    即使事务B commit数据后，事务A读取到的还是一开始的数据。
    应用场景为：事务A只想查询一个时间的数据，随后commit的数据不影响事务A的查询。
    由此可能造成'幻影读'。
  • serializable
    1
    2
    最强的隔离，当事务B未commit时，事务A查不到东西。
    即事务不可并发执行，效率最低，但是隔离性最高。

九、视图

1
2
3
4
5
6
7
8
9
10
11
视图相当于一个'表'，由一个DQL语句查询结果建成。
与表不同的是，在对视图对象进行增删改时，原表上的数据也会发生改变。

所以我们可以面向视图更新原表。
好处是利于维护，简化开发。

#建立视图
create view 视图名 as select ______;

#删除视图
drop view 视图名;

十、索引

1. 关于索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
查询有两种：
1.全表查询
2.根据索引检索

索引index 由B-Tree实现底层，通过地址查询。
主键会自动添加索引，unique也会。

索引分类：
单一索引 	———— 	一个字段上添加索引。
复合索引 	————	两个字段或者更多的字段上添加索引。
主键索引 	————	主键上添加索引。
唯一性索引	————	具有unique约束的字段上添加索引。
注意:唯一性比较弱的字段上添加索引用处不大。

添加索引的前提：
1.数据量庞大
2.经常出现在where后，即字段总是被扫描
3.很少DML操作，即增删改

#创建索引
create index 对象别名 on 表名(字段名);

#删除索引
drop index 对象别名 on 表名;

#查看select执行详情
explain select * from 表名 where 条件;
type栏中，all为无索引，ref为有索引。

2. 索引失效

1
2
3
4
5
6
某些情况下，可能会出现索引失效的情况。
1. 模糊查询，并且以%开头。少用like%
2. or两边字段若有一个没有索引，则索引失效。少用or
3. 复合索引，若没有使用左侧的字段查找，则失效。
4. 索引字段参与运算。例如id是索引字段，'where id+1 = 2' 则索引失效。
5. 索引字段使用函数也会失效。

十一、常用储存引擎

  • MyISAM
    1
    2
    3
    4
    5
    6
    使用三个文件表示每个表：
    1.定义 .frm
    2.内容 .MYD
    3.索引 .MYI
    特点:
    可转换为压缩只读表，从而节省空间。
  • InnoDB 
    1
    2
    3
    4
    5
    6
    分为三部分：
    1.目录 .frm
    2.表空间 tablespace 里面存了表内容和索引
    3.日志文件 处理事务
    特点:
    非常安全，支持事务，支持崩溃后自动恢复机制，但是效率较低。
  • MEMORY
    1
    2
    3
    4
    由一个.frm文件储存。
    特点:
    储存在内存中，查询效率最高。但是不安全，关机后数据消失，数据和索引都在内存中。
    且不支持blob字段。

十二、数据库设计三范式

1
2
3
4
5
6
一：必须有主键，字段原子性不可再分。
二：在一的基础上，所有非主键字段完全依赖主键，不产生部分依赖。
三：在二的基础上，所有非主键字段直接依赖主键，不产生传递依赖。

但是以满足客户需求为主。
有时候不拆表，空间占用大，冗余大，但是查询速度快且开发较为容易。根据情况选择。

小结

  目前数据库基础学习已经告一段落，随后如果再次进行数据库的学习的话，可能就会对底层一点的东西进行研究了，例如索引的底层之类的。
  马上要开始redis的学习了，在redis学习结束后，我预备写一个关于sql和redis的小demo加深印象。本篇笔记的内容日后也会不断完善，希望一切顺利。