MySQL学习总结

数据库的了解

SQL DB DBMS

: DB：DateBase(数据库)，实际上数据库是以文件的形式存在的。

DBMS：DateBase Management System(数据库管理系统)，常见的有：MySQL Oracle DB2 Sybase SqlServer

SQL：结构化查询语言，是一门标准通用的语言。标准的sql适合于所有的数据库产品。sql语句执行的适合也会进行编译，不过它的编译是由DBMS来执行的我们是看不见的。

DBMS负责执行sql语句，通执行sql语句来炒作DB的数据

================================================

什么是表

表

table是数据的基本组成单元，所有数据都以表格的形式组织，目的性是可读性强

一个表包括行和列：

行：被称为数据 / 记录(data)
列：被称为字段(column)

================================================

SQL语句分类

DQL(数据库查询语言)

查询语句，凡是select语句都是DQL

DML(数据库操作语言)

insert delete update，对表中的数据进行增删改

TCL(事务控制语言)

commit 提交事务，rollback 回滚事务。(TCL中的T是Transactiom)

DCL(数据控制语言)

grant 授权，revoke 撤销权限等。

DDL(数据定义语言)

creat drop alter，对表结构的增删改。

当一个文件的扩展名是.sql并且文件中由大量的sql语句，我们称这样的文件为sql脚本。

DQL查询语句

简单查询语句

简单的查询语句的语法格式

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

如果给查询结果的列重命名

select 字段名 as 重命名的名字 from 表名1,表名2...;

如果有中文的话要用单引号括起来 比如：‘姓名’
MySQL是支持双引号的 比如：“姓名” 但是不推荐使用，因为双引号在其他数据库不通用，只有MySQL可以用双引号。

================================================

条件查询语句

条件查询语句语法格式

select 字段,字段 from 表名 where 条件;

按执行顺序：先from 然后where 最后 select

简单条件语句例子

查询工资等级5000的员工

select ename from emp where sal = 5000;

---

查询工资不等于3000的员工

select ename from emp where sal <> 3000;
select ename from emp where sal !- 3000;

注意：
以后运算优先级不确定的时候添加括号()就好了

---

找出工资在1100和3000之间的的员工包括1100和3000

select ename,sal from emp where sal between 1100 and 3000;

between语句

左边是小数据，右边的大数据
between 1100 and 3000 是闭区间[1100~3000]都包括

---

找出工资等于1100 和 3000 的员工

select ename from emp where sal in(1100,3000);

in 语句

in 后面的值是不是区间是值
not in 不在这几个集当中的

================================================

模糊者查询 like

模糊者查询主要掌握%符和_符

%号符表示任意多个字符
_号符表示任意一个字符

找出名字中含有o字母的人

select ename from emp where ename like '%o%';

找出第二个字母是A的人

---

select ename from emp where ename like '_A%';

================================================

排序

排序语法

select 字段名 from 表名 where 条件 order by 字段名 排序顺序;

排序的默认是升序就是后面那个排序顺序不写的话默认是升序
指定升序或降序的话修改排序顺序 desc表示降序 asc表示升序

注意：
越靠前的字段越能起到主导作用，只有当前面的字段结束后面的字段名才会执行

================================================

分组函数

count计数 sum求和 avg平均值 max最大值 min最小值

语法

select 分组函数(字段) from 表名；

所有的分组函数都是对某一组数据进行操作的

分组函数自动忽略null

sql有一个语法规则是分组函数不可直接在where里面使用
原因是group by在where之后执行

count( * )和count(具体某个字段名)的区别
count( * )是统计总记录条数与字段无光
count(具体某个字段名)是统计这个字段的全部记录

---

多行处理函数

特点

分组函数还有一个名字叫做多行处理函数
多行处理函数的特点是输入多行输出一行

单行处理函数

特点

输入一行，输出一行

group by 和 having

group by：是按照某个字段或某些字段进行分组
having：having是对分组之后的字段进行过滤

语法

select 字段 from 表名 where 条件 group by 字段 having 条件 order by 字段 排序顺序;

分组函数一般和group by联合使用这也是为什么它被称为分组函数的原因。并且每一个分组函数都在group by 执行之后才可以执行。当一条sql语句中没有group by的话整张表直成一组。

如果一个语句中有group by的话select后面只能跟分组函数和排序字段别的跟不了
having只为group by服务

================================================

distinct去重

语法

select distinct 字段,字段2 from 表名;

distinct只能出现在所有字段最前面
distinct出现在所有字段最前面表示后面的字段联合起来去重

================================================

连接查询

连接查询就是多张表连接起来查询

连接查询的分类

根据年代来分

SQL92
SQL99

根据表连接分

内连接：等值连接，非等值连接，自连接
外连接：左外连接，右外连接

关于表别名

第一执行效率高
第二可读性好

SQL92语法

select 字段 from 表名,表名 where 条件;

SQL99语法

select 字段 from 表名 join 表名 on 条件;

SQL99的语法比SQL92要好因为表连接条件和后来的where分离了

在表连接的时候有一种现象叫做笛卡尔乘积现象
笛卡尔乘积：当俩张表连接查询的时候，没有任何条件进行限制，最终查询结果条数是两张表的乘积。

避免笛卡尔乘积的方法是叫条件
避免了笛卡尔乘积不会减少匹配次数。只不过过显示的是有效值

---

内连接

内连接

假设A表和B表进行连接，使用内连接，凡是AB表可以匹配上的记录查询出来这就是内连接。内连接两张表是平等的。

语法

select 字段 from 表名 inner join 表名 on 条件;

inner可以省略不写，写上可读性好点

---

内连接之等值连接

等值连接：一张表和一张表连接，条件是一个等值关系叫等值连接

select d.dname,e.ename from emp e inner join dept d on e.deptno = d.deptno;

---

内连接之非等值连接

非等值连接：一张表和一张表连接，条件不是一个等值关系叫非等值连接

select e.ename,e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal;

---

内连接之自连接

自连接：一张表看成两张表。自己连接自己

select e.ename,d.ename from emp e inner join emp d on e.mgr = d.empno;

================================================

外连接

假设A表和B表进行连接，使用外连接的话，AB两张表有一张的主表一张是副表，主要查询主表的，捎带查询副表，当副本中的数据没有和主表的数据匹配上，副本自动模拟null来匹配。

外连接主要特点
主表的数据无条件查询出来

左连接语法

select 字段 from 表名 left join 表名 on 条件;

右连接语法

select 字段 from 表名 right join 表名 on 条件;

================================================

子查询

select语句中嵌套select，被嵌套的select叫做子查询
子查询可以出现在

select
	...(select)
from
	 ...(select)
where
	...(select)

select中使用子查询可以把它当成一个字段
from中使用子查询可以把它当成一张表
where中使用子查询可以把它当成一个值

Union

特点是
让两个没有关系不相关的表拼接到一块。

语法

select 字段名 from 表名 where 条件
union
select 字段名 from 表名 where 条件

================================================

limit

limit是MySQL特有的，不通用。
limit取结果集中的部分数据，这是他的作用
语法机制

limt startindex,length

startindex 表示起始位置
length 表示取几个

完整语法（包括前面的）

select 字段名 from 表名 join 表名 on 条件 where 条件 group by 字段 having 条件 order by 字段 排序条件 limt 起始位置,取几个;

limit有个公式
每页显示pageSize页记录
第pageNo页: (pageNo - 1) * pageSize , pageSize

MySQL中常用的数据类型

int 整数型
bigint 长整形
float 浮点型
char 定长字符串
varchar 可变成长字符串
date 日期类型
BLOB 二进制大对象(储存图片，视频等流媒体信息)
CLOB 字符大对象(储存较大文本，比如可以储存4g字符串)

char和varchar应该怎么选择呢

在实际开发中把如果，某个字符的数据长度已经确定不会发生改变例如：性别，生日等就使用char
如果数据长度不确定例如：姓名，emlia那些啊就使用varchar

DML数据库操作语言

insert delete update，对表中的数据进行增删改
增删改有个术语叫CRUD操作
Create(增) Retrieve(检索) Update(修改) Delete(删除)

---

insert

语法

inser into 表名(字段1,字段2,字段3) values(值1,值2,值3);

要求

字段的数量要和值的数量相等，并且数据类型要相同

insert语句每成功执行一次数据库就多一条记录

update

语法

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

注意
没有条件整张表全部更新

delete

语法

delete from 表名 where 条件;

注意
没有条全部删除

删除大表数据

truncate table 表名;

这个是永久删除，谨慎使用

================================================

DDL数据库定义语言

creat drop alter，对表结构的增删改。
增删改有个术语叫CRUD操作
Create(增) Retrieve(检索) Update(修改) Delete(删除)

对表结构的修改，使用工具完成即可，因为在实际开发中一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行否定，即使需要修改表结构用工具即可。

---

Create

创建表语法

create table 表名(
	字段名1 数据类型,
	字段名2 数据类型,
	字段名3 数据类型,
	...
);

在创建表的时候可以在(字段名，值)后面添加给default 值，来给这个字段一个默认值
注意default后面一定是跟字段名数据类型相匹配的值 没写default默认值NULL

将查询结果当表创建出来的语法

Create table 表名 as select语句;

Drop

当这个表存在的话就删除这个表的语法

drop table if exists 表名;

================================================

约束(Constraint)

在创建表的时候，给表添加相应的约束。添加约束是为了保证表中数据的合法性 有效性 完整性

常见约束

非空约束(not null)
唯一约束(unique)
主键约束(primary key)
外键约束(foreign key)

在列后面添加约束叫

列级约束

单独添加约束叫

表级约束

---

非空约束(not null)

语法

create table 表名(
	字段 数据类型 not null
);

非空约束没有表级约束

---

唯一约束(unique)

唯一性约束修饰的字段具有唯一性，就是不能重复

语法

create table 表名(
	字段 数据类型 unique,
	unique(字段，字段...)
);

---

主键约束(primary key)

被主键约束修饰的字段不能为null，也不能重复

语法

create table 表名(
	字段 数据类型 primary key,
	primary key(字段，字段);
);

主键约束相关术语

主键约束：primary key
主键字段：被主键修饰的字段叫主键字段
主键值：主键字段里面的值叫做主键值

主键作用

表的设计三范式有要求，第一范式要求每一张表都应用有主键
主键的作用是这一行记录的唯一标识(像人的身份证一样)

---

主键分类

根据主键字段的字段数量分类

单一组将（推荐常用）
复合组将 （不推荐使用，违法三范式）

根据主键的性质分类

自然主键： 主键值最好就是跟业务没有挂钩的自然数
业务主键： 主键值和业务挂钩比如：拿这银行卡号做主键，拿着身份证做主键（不推荐使用）因为以后业务需要改变，主键值也需要改变，但有的时候可能没有办法改变，因为主键重复了

主键自增机制

语法

create table 表名(
	字段名 数据类型 primary key auto_increment
);

外键约束

外键约束相关术语

外键约束：foreign key
外键字段：添加有外键约束的字段
外键值：外键约束修饰字段内的值.

外键约束可以为NULL

语法

create table 表名(
	foreign key(字段名) references 表名(字段名)
);

注意
被引用的字段不一定是主键，但至少要有unique约束

储存引擎

什么是储存引擎
储存引擎这个名字只有要MySQL
MySQL支持很多储存引擎每一个储存引擎都对应一种不同的储存方式。每一个储存引擎都有自己的优缺点，需要合适的时机来使用合适的储存方式。

---

Mylsam储存引擎

Myisam是mysq中最常用的储存引擎，但是这种引擎不是默认的

Mylsam采用三给文件组织一张表

XXX.frm(储存格式的文件)
XXX.myd(储存表中数据的文件)
XXX.myi(储存表中索引的文件)

优点

可被压缩，节省储存空间，并且可以转换为只读表，提高检索效率

缺点

不支持事务

---

InnoDB储存引擎

InnoDB mysql默认储存引擎

优点

支持事务，行级锁，外键等。这次储存引擎的数据安全得到保障

缺点

表的结构在tableSpace这样的空间中无法被压缩，无法转换成只读。

这种InnoDB储存引擎在数据库崩溃后提供自动恢复机制
InnoDB支持级联更新和级联删除

---

MEMOKY储存引擎

缺点

不支持事务。数据容易丢失。因为所有数据和索引都是储存在内存当中的。

优点

查询速度快

================================================

事务(Transaction)

事务是一个完整的业务逻辑单元，不可再分。

和事务相关的语句只有：BML（insert delete update）

因为他们这三个语句都是和数据库当中的数据相关的

事务的存在是为了保证数据的完整性，安全性.

事务就是多条DML语句捆绑在一起

---

提交事务

把历史操作一并同步到硬盘当中，同步到硬盘之后清除历史操作

回滚事务

把历史操作清空

历史操作就是执行DML语句的操作

---

事务的特性

事务包括四大特性ACID

A:原子性:事务是最新的工作单元，不可再分
C:一致性:事务必须保证多条DML语句同时执行或同时失败
I:隔离性:事务A与事务B之间有隔离性
D:持久性:持久性说的是最终数据必须久化到硬盘中，事务才算成功。

关于事务之间的隔离性
事务隔离性存在隔离级别，理论上隔离级别包廓4个

第一级别：读未提交(read uncommitted)

对方事务没有提交，我们当前事务可以读取到对方未提交的数据
读未提交存在脏读

第二级别：读已提交(read committed)

这种隔离解决了脏读现象
对方事务提交之后的数据我方可以读取到
读已提交存在的问题是：不可重复读

第三级别：可重复读(repeatable read)

这种隔离级别解决了，不可复读的问题。
这种隔离级别存在的问题是：读取到的数据是幻象

第四级别：序列化读(serializable)

解决了所有问题
效率低。需要排队

MySQL默认情况下是自动提交的
什么是自动提交
执行一条DML语句提交一次
怎么关闭自动提交
start eransaction;

设计事务的全局隔离级别

set global transaction isolation level 隔离级别;

================================================

索引

索引语法

添加索引
create index 索引名字 on 表名(字段名);
删除索引
drop index 索引名字 on 表名;

什么是索引，有什么用

索引就相当于一本书的目录，通过目录可以快速的找到对应的资源

在数据库方面查询一张表有两种扫描方法

第一种 全表扫描
第二种 根据索引检索(效率非常高)

索引为什么可以提高效率呢

其实最根本的原理是缩小了扫描的范围。索引虽然可以提高检索效率，但是不能随意添加索引，因为所有也是数据库当中的，也需要数据库不断的维护，是由维护成本的。比如，表中的数据经常被修改这样就不适合添加索引了，因为数据库一旦修改，索引需要重新排序，进行维护。

什么时候添加索引

数据量大(根据客户需求，根据线上的环境)
该字段很少DML操作(字段进行修改，索引需要维护)
该字段经常出现在where子句中(经常根据哪个字段查询)

索引什么时候失效

模糊查询的时候，第一个通配符使用的是%，这个时候索引失效

索引实现原理

通过B + Tree缩小扫描范围，底层索引进行排序分区，索引会携带数据在表中的物理地址最终，通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率最高。

select ename from emp where ename = 'SMITH';
通过索引
select ename from emp where 物理地址 = 0X3;

索引分类

单一索引 ：给单个字段添加索引
复合索引 ：给多个字段添加索引
主键索引 ：主键上会自动添加索引
唯一索引 ： 有unique约束的字段上会自动添加索引

================================================

视图(View)

视图就是站在不同的角度去看待数据。（同一张表的数据，通过不同的角度去看待）

视图语法

创建视图
create view 视图名字 as select语句;
删除视图
drop view 视图名字

只有DQL语句才能以视图对象的方式创建出来

对视图进行增删改查，会影响到原表数据(通过视图影响不是直接操作员表)
可以对视图进行CRUD操作

视图作用

视图可以隐藏表的实现细节，保密级别较高的系统，数据库对外提交相关的视图。

================================================

DBA命令

将数据库当前中数据导出

在windows的dos命令窗口中执行：

mysqldump 数据库名>绝对路径 -u用户 -p密码

导入数据

create database 数据库名;

================================================

数据库设计三范式

设计范式是设计表的休据，按照这个三范式设计的表不会出现数据冗余

三范式都是哪些

第一范式

任何一张表都应该有主键，并且每一个字段原子性不可以再分

第二范式

建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖，
多对多 三张表 关系表两外键

第三范式

建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。
一对多 两张表 多的表加外键

在实际开发中以满足客户的需求为主，有的时候会拿冗余来换执行速度