MySQL超详细总结

1、MySQL常用命令

1. 查看mysql中所有的数据库
	show databases;

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

3.删除数据库
	drop database 数据库名;
	
4.使用数据库
	use 数据库名;

5.显示表
	show tables;

6.查看表结构
	desc 表名;

7.查看数据库的版本号
	select version();
	
8.查看当前使用的是哪个数据库
	select database();

2、SQL语句的分类

SQL语言分类	描述
DQL	数据查询语言(凡是带select关键字的都是查询语句)
DML	数据操作语言(凡是对表中的数据进行增删改查的都是DML)
DDL	数据定义语言(凡是带有create、drop、alter的都是DDL)
TCL	事务控制语言(事务提交:commit 事务回滚: rollback)
DCL	数据控制语言(授权grant、撤销权限revoke…等)

3、简单查询

CREATE TABLE `person`(  
  `age` INT(3), 
  `name` VARCHAR(30),
  `weight` INT(5),
  `height` INT(5) 
) ENGINE=INNODB CHARSET=utf8

INSERT INTO person VALUES(15,'张三',140,175),(18,'王东',130,178),(16,'李华',135,180),(20,'刘飞',145,185);

3.1、查询单个字段

select name from person；

3.2、查询多个字段

select name,age,weight,height from person;

3.3、查询全部字段

1.第一种方式把所有的字段名全部都写出来

2.第二种方式(可读性差,效率低)
select * from person;

3.3、起别名as

select name as names from person;

3.4、数学运算

* / + -
字段是数字使用数学运算直接运算
字段是字符串使用数学运算则从新创建新的字段运算
select age*12 from person;

4、条件查询

语法格式:
	select 字段1,字段2,from 表名 where 条件;

=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between…and…	两个值之间 （左小右大）
is null	为空
is not null	不为空
and	并且
or	或者
in	包含
not in	不包含
like	模糊查询,支持%或下划线匹配,%匹配任意多个字符,下划线:一个下划线只匹配一个字符

注意:

1. and的优先级比or高,如果想先执行or小括号括起来
2. like模糊查询,查询的字段里面如果带有_则使用\转义，例如 select name from person where name like ‘%\ _%’;

5、排序

* 数据库默认是升序
语法格式:
	select 字段1,字段2 from 表名 order by 字段; //升序
	select 字段1,字段2 from 表名 order by 字段 desc; //降序
	select 字段1,字段2 from 表名 order by 字段 asc; //升序

多个字段排序
	select select 字段1,字段2 from 表名 order by 字段 asc,字段 asc; //先执行第一个字段,如果第一个字段不满足才会往下继续执行。

6、数据处理函数/单行处理函数

mysql官方文档: https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

lower	转换小写
upper	转换大写
substr	截取字符串
length	区长度
trim	去空格
str_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将null转换成一个具体值

7、分组函数

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

注意:

1. 分组函数在使用的时候必须先进行分组,然后才能用。如果你没有对数据进行分组,整张表默认为一组。
2. 分组函数不能够直接使用在where子句中。

SELECT COUNT(comm) FROM emp -- count(字段) 会忽略所有的null值
SELECT COUNT(*) FROM emp -- count(*) 不会忽略null值,
SELECT COUNT(1) FROM emp -- count(1) 不会忽略所有的null值

SELECT SUM(sal) AS '求和' FROM emp
SELECT AVG(sal) AS '平均值' FROM emp
SELECT MAX(sal) AS '最大值' FROM emp
SELECT MIN(sal) AS '最小值' FROM emp

8、分组查询

--  group by ： 按照某个字段或者某些字段进行分组。
--  having : having是对分组之后的数据进行再次过滤,并且having里面只能写分组函数。

--  注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。

按照工作岗位分组,然后对工资求和
SELECT job,SUM(sal) sal FROM emp GROUP BY job;

找出每个部门最高薪资,要求显示最高薪资大于3000的
SELECT MAX(sal),deptno FROM emp GROUP BY deptno HAVING MAX(sal)>3000;

结论:

• 在一条select语句当中,如果有group by语句的话, select后面的字段只能写: 参与分组的字段和分组函数,其它的一律不可以跟，因为没有意义。

9、去除重复记录distinct

语法格式:
	select distinct 字段 from 表名;

注意:

• 原表数据不会被修改,只是查询结果去重，去重需要使用一个关键字: distinct
• distinct 只能写在字段最前面

10、连接查询

10.1、什么是连接查询

连接查询：将多张表（可以大于2张）进行记录的连接（按照某个指定的条件进行数据拼接）。最终结果：记录数有可能变化，字段数一定会增加（至少两张表的合并）。

10.2、连接查询的分类

根据语法的年代分类:

• SQL92: 1992年的时候出现的语法
• SQL99: 1999年的时候出现的语法
• 重点学习SQL99

根据表连接的方式分类:

• 内连接
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接(左连接)
  • 右外连接(右连接)
• 全连接

10.3、内连接之等值连接

关键字: inner join on
语法: select a.字段,b.字段 from a表 join b表 on a和b的连接条件   --join连接的意思
说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

案例: 查询每个员工所在部门名称,显示员工名和部门名
语句: SELECT e.ename,d.dname FROM emp e JOIN dept d  ON e.deptno = d.deptno;

10.4、内连接之非等值连接

案例: 找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级?
语句: SELECT e.ename,e.sal,s.grade FROM emp e JOIN salgrade s ON e.sal BETWEEN s.`LOSAL` AND s.`HISAL`;

10.5、内连接之自连接

案例: 查询员工的上级领导,要求显示员工名和对应的领导名?
语句: SELECT e.ename '员工',d.ename '领导' FROM emp e JOIN emp d ON e.mgr = d.empno;
说明: 一张表看成两张表,自己连接自己。

10.6、左外连接

关键字: left join on / left outer join on 
语句: select 字段a,字段b from a表 left on b 表 on a和b的连接条件
说明: left join 是 left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
主查左表

10.7、右外连接

关键字: right join on / right outer join on
语法: select a字段,b字段 from a表 right join b 表 on a和b的连接条件
说明: right join是 right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
主查右表

10.8、多表连接

语法: select 字段a,字段b,字段c from a表 join b表 on a和b的连接条件 join c表 on a和c表的连接条件...
一直嵌套使用
一条SQL中内连接和外连接可以混合,都可以使用。
案例: 找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级?
语句: SELECT e.ename,d.dname,e.sal,s.grade FROM emp e JOIN dept d ON e.deptno=d.deptno JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

11、子查询

11.1、什么是子查询

select语句中嵌套select语句,被嵌套的select语句称为子查询。

11.2、子查询都可以出现在哪里呢

select

​ …(select)…

from

​ …(select)…

where

​ …(select)…

11.3、where子句中的子查询

案例: 找出比最低工资高的员工姓名和工资?
语句: SELECT ename,sal FROM emp WHERE sal>(SELECT MIN(sal) FROM emp);

11.4、from子句中的子查询

-- 注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。
案例: 找出每个岗位的平均工资的薪资等级。
语句: SELECT e.job,s.grade FROM (SELECT job,AVG(sal)AS avgsal FROM emp GROUP BY job) e JOIN salgrade s ON e.avgsal BETWEEN s.losal AND s.hisal;

思路:SELECT job,AVG(sal)AS avgsal FROM emp GROUP BY job查询到每个工资岗位的平均工资,看成一张e表,再与薪资等级进行一一匹配。

11.5、select子句中的子查询(了解)

案例: 找出每个员工的部门名称吗,要求显示员工名部门吗
语句: SELECT e.ename,(SELECT d.dname FROM dept d WHERE e.deptno=d.deptno) AS dname FROM emp e;

12、union

作用: 合并两条sql的结果集、

语法: SQL1 union SQL2

案例: 查询工资岗位为SALESMAN和CLERK,要求显示员工名和工作岗位。
语句: SELECT ename,job FROM emp WHERE job='salesman'
	 UNION
	 SELECT ename,job FROM emp WHERE job='clerk';

什么时候才允许使用union

• union在进行结果集合并的时候,要求两个结果集的列数相同，即使字段类型不相同，也可以使用。

13、limit

作用：将查询结果集的一部分取出来，通常使用在分页查询当中。

语法: limit startIndex,length

​ startIndex是起始下标,length是长度。其实下标从0开始。

每页显示3条记录
	第1页: limit 0,3	[0,1,2]
	第2页: limit 3,3	[3,4,5]
	第3页: limit 6,3	[6,7,8]
	第4页: limit 6,3	[9,10,11]

每页显示pageSize条记录
	第pageNo页: limit (pageNo - 1) * pageSize , pageSize

按照薪资降序,取出排名在前五的员工

select ename from emp order by sal desc limit 5;
等于
select ename from emp order by sal desc limit 0,5;

取出工资排名在【3-5】名的员工

select ename from emp order by sal desc limit 2,3;

14、查询的执行顺序

select…from…where…group by… having…orderby…limit…

1. from
2. where
3. group by
4. having
5. select
6. order by
7. limit

15、表

15.1、创建表

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

-- 复制表
-- create table 表名 as 查询语句
-- 将一个查询结果当做一张表新建相当于复制一张表

15.2、数据类型

常用的数据类型

varchar
	可变长度的字符串
	比较智能 节省空间
	会根据实际的数据长度动态分配空间
	优点: 节省空间
    缺点: 需要动态分配空间,速度慢。
    
char
	定长字符串
	不管实际的数据长度是多少
	分配固定长度的空间去存储数据
	使用不恰当的使用,可能会导致空间的浪费
	
int
	数字中的整数型
	
bigint
	数字中的长整型
	
float
	单精度浮点型数据
	
double
	双精度浮点型数据
	
date	
	短日期类型
	
datetime
	长日期类型
	
clob
	字符大对象
	最多可以存储4g的字符串
	比如: 存储一片文章,存储一个说明
	
blob
	二进制大对象
	专门用来存储图片、声音、视频等流媒体数据。

15.3、删除表

语法: drop table 表名
判断表是否存在,如果存在则删除
drop table if exists 表名

15.4、插入数据

语法: insert into 表名(字段1,字段2,字段3) values(值1,值2,值3);
简写:
	insert into 表名 values (值1,值2,值3);
	注意值必须和创建的字段一一对应,类型也是。

15.5、修改数据

语法: update 表名 set 字段名1=值1,字段名2=值2 where 条件;
注意: 没有条件限制会导致所有数据全部更新

15.6、删除数据

语法: delete from 表名 where 条件;
注意: 没有条件,整张表的数据会全部删除
delete语句删除数据的原理
	表中的数据被删除了，但是这个数据在硬盘上的真是存储空间不会被释放
	缺点: 删除效率比较低
	优点: 支持回滚,可以恢复数据。
	
----------------------------------------------------
快速删除表中的数据 truncate
	原理:
		删除效率高,表被一次截断,物理删除。
		缺点:无法恢复,不支持回滚。
		优点: 快速。

语法: truncate table 表名

16、约束

16.1、什么是约束

约束英文：constraint

约束实际上就是表中数据的限制条件

16.2、约束的种类

非空约束	not null
唯一性约束	unique (支持表级约束)
主键约束	primary key(简称PK)（支持表级约束)
外键约束	foreign key(简称FK)
检查约束	check(mysql不支持,oracle支持)

16.3、非空约束

非空约束 not null约束的字段不能为null

创建表,给字段添加非空约束
create table t_user(
	id int(2),
    name varchar(20) not null
);

如果没有插入name字段数据,就会报错
insert into t_user(id) values(1);
ERROR:>Field 'name' doesn't have a default value 

16.4、唯一性约束

unique约束的字段，具有唯一性，不可重复，但可以为null

创建表,给字段添加唯一性约束
CREATE TABLE t_person(
   age INT(2),
   NAME VARCHAR(20) UNIQUE
);

如果为name字段添加重复数据,就会报错
INSERT INTO t_person(age,NAME)VALUES(15,'张三'),(18,'张三');
ERROR:>Duplicate entry '张三' for key 'name'

表级约束,给多个字段联合约束

联合约束,表示两个字段重复才会报错,而一个字段重复另一个字段不重复则不会报错
create table t_animal(
	age int(15),
    name varchar(20),
    description varchar(30),
    unique(name,description)
);
例一: name重复description不重复并不会报错
INSERT INTO t_animal(age,NAME,description) VALUES (10,'馒头','可爱的小狗'),(8,'馒头','可爱的小猫');

例二:name重复description重复则会报错
INSERT INTO t_animal(age,NAME,description) VALUES (10,'豆豆','企鹅'),(8,'豆豆','企鹅');
ERROR:>Duplicate entry '豆豆-企鹅' for key 'name'

16.5、主键约束

表设计时一定要有主键

主键约束的相关术语:

• 主键约束
• 主键字段
• 主键值

primary key约束的字段,不能重复也不能为null

创建表给字段添加主键约束
create table t_vehicle(
	id int primary key,
    type varchar(10),
    name varchar(10)
);

例一: id不写值的情况下会报错
INSERT INTO t_vehicle(TYPE,NAME) VALUES (校车,大巴);
ERROR:>Field 'id' doesn't have a default value

例二: id值重复的情况下报错
INSERT INTO t_vehicle(id,TYPE,NAME) VALUES (1,'校车','大巴'),(1,'私家车','奔驰');
ERROR:>Duplicate entry '1' for key 'PRIMARY'

单一主键(表级定义)

create table t_vehicle(
	id int,
    type varchar(10),
    name varchar(10),
    primary key(id)
);

复合主键(表级定义,开发不建议使用)

create table t_vehicle(
	id int,
    type varchar(10),
    name varchar(10),
    primary key(id,name)
);

在MySQL数据库提供了一个自增的数字，专门用来自动生成主键值，主键值不用用户维护，自动生成，自增数从1开始，以1递增(auto_increment)

create table t_user(
  id int(10) primary key auto_increment,
  name varchar(32) not null
);

16.6、外键约束

1、外键约束涉及到的相关术语:

• 外键约束
• 外键字段
• 外键值

2、什么是外键(foregin key)

若有两个表A、B，id是A的主键，而B中也有id字段，则id就是表B的外键，外键约束主要用来维护两个表之间数据的一致性。

3、分析场景

设计数据库表，用来存储学生和班级信息

两种方案

1.方案一：将学生信息和班级信息存储到一张表

sno     sname      classno      cname
1       jay			100			浙江省第一中学高三1班
2		lucy		100			浙江省第一中学高三1班
3		king		200			浙江省第一中学高三2班

缺点：数据冗余，空间浪费。

2.方案二：将学生信息和班级信息分开两张表存储

班级表

cno(pk)		cname
100			浙江省第一中学高三1班
200			浙江省第一中学高三2班

学生表（添加单一外键）

sno(pk)		sname		classno(fk)
1			jack		100
2			lucy		100
3			king		200

好处:保证数据的完整性和一致性

结论:为了保证学生表中的classno字段中的数据必须来自于班级表中的cno字段中的数据，有必要给学生表中的classno字段添加外键约束

注意点:

• 外键值可以为null

• 有了外键引用之后,表分为父表和字表

  • 班级表: 父表
  • 学生表:子表

• 删除表的顺序

  • 先删子,在删父

• 创建表的顺序

  • 先创建父,在创建子

• 删除数据的顺序

  • 先删子,在删父

• 插入数据的顺序

  • 先插入父,在插入子

案例:存储学生的班级信息

语法:
	foreign key(子表字段) reference 父表(字段)

班级表 父表
create table t_class(
	cno int primary key,
    cname varchar(20)
);

学生表 子表
create table t_student(
	sno int primary key auto_increment,
    sname varchar(20),
    classno int,
    foreign key(classno) references t_class(cno)
);

INSERT INTO t_class(cno,cname) VALUES(100,'aaaaaaxxxxxx');
INSERT INTO t_class(cno,cname) VALUES(200,'oooooopppppp');
INSERT INTO t_student(sname,classno) VALUES('jack',100);
INSERT INTO t_student(sname,classno) VALUES('lucy',100);
INSERT INTO t_student(sname,classno) VALUES('king',200);

子表中的classno只能写父表中cno存在值或null

案例:找出每个学生的班级名称
语句:SELECT s.sname,c.cname FROM t_student s JOIN t_class c ON c.cno=s.classno;

17、存储引擎

17.1、什么是存储引擎

实际上存储引擎是一个表存储/组织数据的方式。

不同的存储引擎,表存储数据的方式不同。

17.2、如何给表添加指定的存储引擎

create table 表名(
	.......
)ENGINE=InnoDB default CHARSET=utf8;

-- mysql默认的引擎是:InnoDB
-- mysql默认的字符编码方式是: utf8

17.3、如何查看mysq支持哪些存储引擎

show engines;

mysq引擎介绍

17.4、mysql常用的存储引擎

1、MyISAM存储引擎

它管理的表具有以下特征:

使用三个文件表示每个表:

• 格式文件 - 存储表结构的定义(mytable.frm)
• 数据文件 - 存储表行的内容(mytable.MYD)
• 索引文件 - 存储表上索引(mytable.MYI)

可被转换为压缩、只读表来节省空间

MyISAM不支持事务机制,安全性低

2、InnoDB存储引擎

mysql默认的存储引擎,同时也是一共轻量级的存储引擎

InnoDB支持事务,支持数据库崩溃后自动恢复机制。

InnoDB存储引擎主要的特点是: 安全

它管理的表具有以下主要特性:

• 每个InnoDB表在数据库目录中以.frm格式文件表示
• InnoDB表空间tablespace被用于存储表的内容
• 提供一组用来记录事务性活动的日志文件
• 用Commit(提交)、SAVEPOINT及ROLLBACK(回滚)支持事务的处理
• 提供全ACID兼容
• 在MySQL服务器崩溃后提供自动恢复
• 多版本(MVCC)和行级锁定
• 支持外键及引用的完整性,包括级联删除和更新

InnoDB最大的特点就是支持事务:

​ 以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，不能很好的节省存储空间。

3、MEMORY存储引擎

使用MEMORY存储引擎的表,其数据存储在内存中,且行的长度固定，这两个特点使得MEMORY存储引擎非常快。

MEMORY存储引擎管理的表具有以下列特征:

• 在数据库目录内,每个表均以.frm 格式的文件表示。
• 表数据及索引被存储在内存中
• 表级锁机制。
• 不能包含TEXT或BLOB字段。
• MEMORY存储引擎以前被成为HEAP引擎。

MEMORY引擎优点: 查询效率是最高的。

MEMORY引擎缺点: 不安全，关机之后数据消失。因为数据和索引都在内存当中。

详细介绍三种引擎

18、事务

18.1、什么是事务

• 事务是逻辑上的一组操作，组成这组操作的各个单元，要不全都成功要不全都失败，这个特性就是事务
• 事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同
• 一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成

18.2、关于事务的一些术语

• 开启事务：Start Transaction
• 事务结束：End Transaction
• 提交事务：Commit Transaction
• 回滚事务：Rollback Transaction

18.3、如何提交事务和回滚事务

提交事务: commit;

回滚事务: rollback; (回滚永远都是只能回滚到上一次提交点)

mysql默认情况下是支持自动提交事务的,也就是说，只要执行一条DML语句就开启了事务，并且提交了事务

如何将mysql的自动提交机制关闭掉
	1.执行命令: start transaction;

回滚操作

mysql> start transaction; 开启手动提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(1,'zs');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(2,'ls');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | zs   |
|    2 | ls   |
+------+------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
Empty set (0.00 sec)

提交事务

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into user values(1,'zs');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(2,'ls');
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | zs   |
|    2 | ls   |
+------+------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+------+------+
| id   | name |
+------+------+
|    1 | zs   |
|    2 | ls   |
+------+------+
2 rows in set (0.00 sec)

18.4、事务的4个特性

• A、原子性
  • 事务是最小的工作单元，不可再分。
• C、一致性
  • 所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。
• I、隔离性
  • A事务和B事务之间具有一定的隔离。
• D、持久性
  • 事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不会丢失。

18.5、事务四大特性之一 隔离性

1. 事务A和事务B之间具有一定的隔离性
2. 隔离性有隔离级别(4个)
   1. 读未提交：read uncommitted
   2. 读已提交：read committed
   3. 可重复读：repeatable read
   4. 串行化：serializable

1、read uncommitted

- 事务A和事物B，事物A未提交的数据，事务B可以读取到
- 这里读取到的数据叫做“脏数据”
- 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

2、read committed

- 事务A和事务B，事务A提交的数据，事务B才能读取到
- 这种隔离级别高于读未提交
- 换句话说，对方事务提交之后的数据，我当前事务才能读取到
- 这种级别可以避免“脏数据”
- 这种隔离级别会导致“不可重复读取”
- Oracle默认隔离级别

3、repeatable read

- 事务A和事务B，事务A提交之后的数据，事务B读取不到
- 事务B是可重复读取数据
- 这种隔离级别高于读已提交
- 换句话说，对方提交之后的数据，我还是读取不到
- 这种隔离级别可以避免“不可重复读取”，达到可重复读取
- 比如1点和2点读到数据是同一个
- MySQL默认级别
- 虽然可以达到可重复读取，但是会导致“幻像读”

4、serializable

- 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
- 这种隔离级别很少使用，吞吐量太低，用户体验差
- 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

5、设置事务隔离级别

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
  	其中的<isolation-level>可以是：
–	READ UNCOMMITTED
–	READ COMMITTED
–	REPEATABLE READ
–	SERIALIZABLE
•	例如： SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

19、索引

19.1、什么是索引

• 索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
• 一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引
• 索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

19.2、索引的实现原理

在mysql当中,索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中,索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中.在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个数的形式存在。(自平衡二叉树: B+Tree)

索引原理总结

19.3、什么时候添加索引

• 数据量庞大
• 字段经常出现在where的后面,以条件的形式存在，也就是说这个字段总是被扫描。
• 字段很少的DML(增、删、改)操作。

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

建议通过主键或者unique约束的字段进行查询，效率是比较高的。

19.4、索引的分类

• 普通索引(INDEX)：最基本的索引，没有任何限制
• 唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
• 主键索引(PRIMARY key)：它 是一种特殊的唯一索引，不允许有空值,值也不能重复。
• 全文索引(FULLTEXT key)：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。

19.5、如何创建/删除索引

-- 创建
语法: create 索引 索引名 on 表名(字段名);
例子: create index emp_ename_index on emp(ename);

-- 删除
语法: drop 索引 索引名 on 表名;
例子: drop index emp_ename_index on emp;

索引的创建删除详细总结

19.6、查看SQL语句中的索引

语法: explain 查询语句
例子: explain select * from emp;

20、视图

20.1、什么是视图

视图是由数据库中的一个表或多个表导出的虚拟表，是一种虚拟存在的表，方便用户对数据的操作。

20.2、视图的作用

• 对视图表的数据进行增删改,会导致原表被操作。
• 假设有一条非常复杂的SQL语句,而这条SQL语句需要在不同的位置上反复使用。每一次使用这个SQL语句的时候都需要重新编写,很长,很麻烦,怎么办?
  • 可以把这条复杂的SQL语句以视图的形式新建，在需要编写这条sql语句的位置直接使用视图对象，可以大大简化开发，并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。
• 视图的作用

20.3、如何创建/删除视图

-- 创建
语法: create view 视图名 as 查询语句;
例子: create view emp_view as select * from emp;

-- 删除
语法: drop view 视图名;

21、备份

-- 在windows的dos命令窗口中: 
导出数据: mysqldump 数据库名>物理磁盘位置:\数据库文件名.sql -uroot -p
例子: mysqldump bjpowernode>D:\shule.sql -uroot -p

导入数据: source 数据库名.sql

22、数据库设计三范式

22.1、什么是数据库设计范式

数据库表的设计依据，教你怎么进行数据库表的设计。

22.2、数据库设计范式一共有三个范式

1. 第一范式: 要求任何一张表必须有主键，每一个字段原子性不可再分。
2. 第二范式: 建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
3. 第三范式: 建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

22.3、第一范式

最核心，最重要的范式，所有表的设计都需要满足。

必须有主键，并且没一个字段都是原子性不可再分。

学生编号	学生姓名	联系方式
1001	张三	zs@qq.com,123333333
1002	李四	ls@qq.com,456666666
1001	王五	ww@qq.com,78999999

• 以上是学生表,满足第一范式吗?
  • 不满足,第一: 没有主键 第二: 联系方式可以分为邮箱地址和电话

学生编号(pk)	学生姓名	邮箱地址	联系电话
1001	张三	zs@qq.com	123333333
1002	李四	ls@qq.com	456666666
1003	王五	ww@qq.com	78999999

22.4、第二范式

建立在第一个范式的基础之上，

要求所有非主键字段必须完全依赖主键，不要产生部分依赖。

学生编号	学生姓名	教师编号	教师姓名
1001	张三	001	王老师
1002	李四	002	赵老师
1003	王五	001	王老师
1001	张三	002	赵老师

• 以上表满足第一范式吗?
  • 不满足，没有主键

学生编号+	教师编号(pk)	学生姓名	教师姓名
1001	001	张三	王老师
1002	002	李四	赵老师
1003	001	王五	王老师
1001	002	张三	赵老师

• 学生编号，教师编号，两个字段联合做主键，复合主键(pk),经过修改之后，满足了第一范式,那么第二范式呢?
  • 不满足，"张三"依赖1001，"王老师"依赖001，显然产生了部分依赖。
  • 部分依赖的缺点: 数据冗余，空间浪费。

为了让以上的表满足第二范式,你需要这样设计:
	使用三张表来表示多对多的关系
	学生表
	学生编号(pk)  学生名字
	------------------------
	1001		 张三
	1002		 李四
	1003		 王五
	
	教师表
	教师编号(pk)  教师姓名
	------------------------
	001			 王老师
	002			 赵老师
	
	学生教师关系表
	id(pk)		 学生编号(fk)		教师编号(fk)
	------------------------------------------
	1			 1001				001
	2			 1002				002
	3			 1003				001

22.5、第三范式

建立在第二范式的基础之上

要求所有非主键字段必须直接依赖主键，不要产生传递依赖。

学生编号(pk)	学生姓名	班级编号	班级名称
1001	张三	01	一年一班
1002	李四	02	一年二班
1003	王五	03	一年三班
1004	赵六	03	一年三班

• 以上表满足第一范式吗?

  • 满足,有主键

• 以上表满足第二范式吗?

  • 满足,因为主键不是复合主键，没有产生部分依赖。

• 以上表满足第三范式吗?

  • 不满足: 一年一班依赖01,01依赖1001，产生传递依赖。不符合第三范式的要求，产生了数据的冗余

  ​

为了让以上的表满足第三范式,你需要这样设计:
	班级表
	班级编号(pk)		班级名称
	-----------------------------
	01				   一年一班
	02				   一年二班	
	03				   一年三班
	
	学生表
	学生编号(pk)		学生姓名		班级编号(fk)
	--------------------------------------------
	1001			   张三			01
	1002			   李四			02
	1003			   王五			03
	1004			   赵六			03

22.6、总结

• 一对一: 外键唯一
• 多对多: 三张表，关系表两个外键
• 一对多: 两张表，多的表加外键