【MySQL】MySQL 学习

一、E-R 模型

1.1 概述

E-R模型主要用于描述数据库中实体之间的关系。当前物理的数据库都是按照 E-R 模型进行设计的。

E(entry)即实体，一个实体对应着数据库中的一张表，R(relationship)则代表各个实体之间的关系。

 

1.2 关系类型

关系描述两个实体之间的对应规则，包括：

• 一对一
• 一对多
• 多对多

 

二、三范式

经过研究和问题总结，人们对于设计数据库提出了一些规范，这些规范被称为范式。

主要我们要遵循的三个范式为：

● 第一范式：列不可拆分

即当前的列在业务实际情况下，不能再进行拆分了。如对于人的姓名，在学生系统中只要存姓名就可以了，而在公安系统中，可能需要分别存姓和名，要根据实际情况拆分

● 第二范式：唯一标识

唯一标识即可以通过一个属性唯一地找到一个对象(一个对象即一条数据)

● 第三范式：引用主键

在多表中引用，必须引用表的主键

 

三、约束

3.1 唯一约束（unique）

在建表时可以添加唯一约束

create table tea(num int unique,name varchar(11));

可以使用命令查看表结构

desc tea;

 

3.2 非空约束（not null）

让插入的数据在设为not null的字段不能为空，由于每一个null都不同，故唯一约束下可能会有多个null

create table tea2(num int not null,name varchar(11));

可以使用命令查看表结构

desc tea2;

 

3.3 主键约束（primary key）

主键约束就是： 非空约束 + 唯一约束

create table tea2(num int primary key,name varchar(11));

可以使用命令查看表结构

desc tea2;

# 主键自动增长(auto_increment)

若数据库中的表存储了很多数据，从外部插入组件是很容易出错的，因为难以避免主键重复，故可以采用

MySQL提供的自动增长的方式。但若已增长过的值，不会被重新覆盖，即一直以系统记录的最大值增长

# 创建教师表

create table tea4(num int primary key auto_increment,name varchar(11));

# 可使用命令查看表结构

desc tea2;

# 建完表后插入多条数据

insert into tea4(name) values('zhanghua');

 

# 查看表数据内容，发现num自增长了

select * from tea4;

 

3.4 外键约束

外键必须是另一个表的主键，否则无法建立关联。使用 foreign key 关键字实现

将不同的表数据变为关联，例如有学生表和班级表，要实现不能把学生加入到数据库中没有的班级中

# 创建班级表，由于学生是依赖于班级的，要先创建班级表，并将班级num设为主键

create table class(num int primary key auto_increment,name varchar(11));

# 创建学生表，并建立外键，使其与班级表关联

create table student(num int primary key auto_increment,name varcher(11),classnum int,foreign key(classnum) references class(num));

# 查看学生表

desc student;

# 插入数据

# 先给class表插入班级数据，否则学生表会由于没有对应班级而无法插入数据

# num 字段已经设了自增长，只要传入null即可

insert into class value(null,'one'); 

# 可以看到 class 表中成功插入了 num 为 1 的班级数据，此时尝试给学生表插入数据

# 先插入班级号为 1 的学生

insert into student values(null,'xiaozhang',1); 

# 插入班级号为 2 的学生，此时数据库提示插入失败，提示添加或更新行失败，有外键

insert into student values(null,'wangmei',2);  

 

四、索引

4.1 什么是索引

索引就类似于书的目录，能够让数据库更快地找到对应的数据。

 

4.2 索引解决了什么问题

● 当没有索引时

如果没有索引，执行查询时  mysql 必须从第一个记录开始扫描整个表的所有记录，直到找到符合要求的记录，表里的记录数量越多，这个操作的代价就越高

● 当存在索引时

如果作为搜索条件的列上已经创建了索引， mysql 无需扫描任何记录即可迅速得到记录所在的位置，若表中有一千条记录，通过索引查找记录至少要比顺序扫描记录快100倍，所以对于现在的各种大型数据库来说，索引可以大大提高数据库的性能

 

 

4.3 索引的类型

● 普通索引

一个索引内只包含单个列，一个表可以有多个单值索引

● 主键索引

必须为主键字段创建一个索引，这个Mysql索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE

● 唯一索引

索引列的值必须唯一，但允许有空值。Mysql会在有新纪录插入数据表时，自动检查新纪录的这个字段的值是否已经在某个记录的这个字段里出现过了。如果是，mysql将拒绝插入那条新纪录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复

● 复合索引

复合索引就是将多个字段联合在一起组成一个索引，其创建方式与创建单一索引的方式完全一样，但在数据库操作期间所需的开销更小，可以代替单一索引。在表的行数远远大于索引建的数目时，使用复合索引可以明显加快表的查询速度

例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 ,当最左侧字段是常量引用时，索引就十分有效。

● 全文索引

只有在 innobb、myisam 引擎上才能使用，只能在 char/varchar/text 类型的字段上使用全文索引

全文索引支持将内容分词，并使用索引对这些内容进行查找，速度优于 where... like

● 其他

当表定义了主键时，会自动创建一个唯一索引，即主键索引

当表定义了外键时，会添加一个普通索引

 

4.4 索引的优缺点

● 优点

通过建立唯一索引或主键索引，保证数据库表中每一行数据的唯一性

提高检索效率，减少表的检索行数

给表连接的连接条件建索引，可以加速表与表之间的连接

在分组和排序进行数据检索时，可以减少查询时间总分组和排序时所消耗的时间

● 缺点

创建和维护索引会耗费时间，且耗费的时间随着数量的增加而增加

索引会占用物理空间，即除了数据库所需要占用的物理空间外，每一个索引还会占用一定的物理空间

当对表数据进行 insert、update、delete 操作时，索引也要动态维护，会降低响应速度

 

4.5 索引的使用

# 创建普通索引

alter table emp add index(empno);

# 创建主键索引(primary key)

alter table emp add primary key (empno);

# 创建唯一索引 (unique)

alter table emp add unique (empno);

# 创建全文索引

alter table emp add fulltext(ename);

# 创建多列索引

alter table emp add index index_name(empno, ename)

# 删除索引

alter table emp drop index(id);

# 查看索引

show index from tom2;

 

4.6 索引的使用场景

转载自： https://juejin.im/post/5de99dd2518825125e1ba49d

● 不适合使用索引的场景

数据量较少的不适合家索引，由于搜索是先搜索索引，再去搜索表数据，数据量小的时候直接全表搜索更快

建立索引的目的就是查询，若不需要查询，则无需索引

若表经常更新则不适合做索引，表更新会导致索引也频繁更新，降低写的效率

唯一性差的字段不适合创建索引(如性别)

 

● 索引失效的场景

查询条件包含or，则要 or 左右的字段都包含索引，否则会失效

若索引的字段类型为字符串，where 时必须用引号括起来，否则索引失效

like 通配符，数据左边不能带有%，否则是个不确定的数据，会导致索引失效

在索引列上使用mysql的内置函数，索引失效(如 DATE_ADD)

使用联合索引时，查询条件不满足最左匹配原则(即第一个必须是联合索引的第一列)，索引失效

对索引运算(如,+、-、*、/)，索引失效

索引字段上使用(!= 或者 <>, not in)时，索引失效

索引字段上使用 is null, is not null，可能导致索引失效

当左连接查询或右连接查询关联的字段编码格式不一致时，可能导致索引失效

当 mysql 估计使用全表扫描要比使用索引快时，不使用索引

 

五、视图

5.1 什么是视图

视图即一张虚拟的表，可以将select的结果保存在其中

 

5.2 视图的使用

# 创建视图 (create view xxx as)

将员工表里工资大于2000的员工，保存到视图 emp_sal_view 中

create view emp_sal_view as (select empno,ename,sal from emp where sal > 2000);

 

# 查看视图结构 (describe)

describe emp_sal_view;

 

# 查看当前的所有视图

show table status where comment='view' \G

 

# 查看视图

视图是一张虚拟表，有哪张表衍生的那张表就是视图的基表

select * from emp_sal_view;

 

# 修改视图 (create or replace view)

create or replace view emp_sal_view as (select * from emp);

 

# 删除视图(drop)

drop view emp_sal_view;

 

五、存储过程

一、什么是存储过程

        存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

 

 

二、存储过程的使用

 

delimiter 新执行符号  可以是%或//

 

语法：

create procedure 过程名（参数1，参数2....）

begin

sql语句;

end

 

# 先修改分隔符为%

delimiter %

# 创建存储过程

mysql> create procedure query()

    -> begin

    -> select * from emp;

    -> end%

Query OK, 0 rows affected (0.00 sec)

# 修改回分隔符

delimiter ;

# 调用存储过程

call query;

 

# 查看所有存储过程

show procedure status \G;

 

# 查看某个具体的存储过程

show create procedure query \G

 

# 删除存储过程

drop procedure query;

 

六、触发器

一、触发器是什么

触发器是一种特殊的存储过程，会在插入、删除或修改特定表中的数据时触发执行。

 

二、触发器的使用

# 创建触发器

 

语法：

create  trigger 触发器名称  触发的时机  触发的动作  on 表名 for each row 触发器状态。

参数说明：

触发器名称：自己定义

触发的时机：before /after  在执行动作之前还是之后

触发的动作：指的激发触发程序的语句类型<insert ,update,delete>

each row：操作每一行我都监控着

 

 

触发器创建语法四要素：

1.监视地点(table)

2.监视事件(insert/update/delete)

3.触发时间(after/before)

4.触发事件(insert/update/delete)

例：当category表中，删除一个bTypeid=3的图书分类时，books表中也要删除对应分类的图书信息（类似级联删除）

create trigger deletetable after delete on emp for each row delete from dept;

 

# 删除触发器

drop trigger deletetable;

 

# 查看触发器

show triggers\G;

# 查看某个触发器的详细信息

show create trigger deletetable \G