MySQL学习第四天

第一部分

1、MySQL数值类型

1.1数值类型

数值类型分为整数型和浮点数类型

int 整数

bigint 大整数

decimal 浮点数

1.2日期类型

1.3字符串类型

char 和varchar数据类型区别：

char（长度）是定长字符串类型，括号内写多少长度，就实际占据多少

varchar（长度）是不定长的字符串类型 ，占用空间由实际传入的值决定

2、数据库设计

数据库设计主要用于在创建项目的时候，通过一些特定的范式、关系去完成项目中表格的创建

2.1设计范式

设计范式主要指的是在设计表的字段的时候，需要遵循一些规则

设计范式分为：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、BC范式等，主要需要遵守三大范式，也就是前三个范式

第一范式：每个字段是原子单元，字段信息不可再分

比如：地址需要拆分成省、市、区/县、街道 联系方式要拆分成：qq联系和电话联系

第二范式：在第一范式的基础上，非主键属性要完全依赖主键（非空且唯一）属性

第三范式：满足第一、第二范式的基础上，非主键属性和主键属性间不存在传递依赖

2.2表和表的关系

表和表之间的关系指的是将来在设计不同的表的时候，怎么让表产生关联

一对一：一张表中的数据的一条数据和另一张表中的一条数据是一一对应的关系

实现：设计表的时候，将一张表的数据直接放入另一张表

一对多：一条数据对应了另一张表中的多条数据

比如：一个部门有多个员工、一个班级有多个学生...

实现方案：设计表的时候，在多的数据的一方建立外键，指向一的一方的主键

多对多：一张表中的一条数据可以对应另一张表B中的多表数据，表B中的一条数据也可以对应表A中的多条数据

比如：一个学生可以选修多门课，一门课可以被多个学生选修

一件商品可以被多个用户购买，一个用户可以购买多个商品

实现：创建一个中间表，最少有两个字段

2.3数据库的ER图

ER图也成为实体模型图，将实体关系转化为图形形式的一种概念

矩形=>实体（表）

椭圆=>属性（字段）

菱形=>关系

示例图：

例子：用户表、商品表、商品分类、地址

2.3数据库设计的补充说明：

①实际上在做数据库设计的时候，不一定会完全遵守三范式

②有的时候会通过多余字段数据来提高数据库查询的性能

③数据库分表，在表数据库里非常大的时候会采取的一种优化方案

-----垂直分表：把一张表的不同字段拆分到多个独立表中，每张表共用主键关联（比如说：用户表中经常使用的字段是用户名和密码，用户信息就可以拆到一个信息表里面）

-----水平分表：按行拆分，拆分完的表的字段完全一样的（比如一个学校的学生有很多，可以按照专业拆分成每个专业的学生表；订单表中可以按照生成订单的月份）

④表设计一定要考虑将来的实际的业务量以及实际读（查询）写（增删改）业务，了解表的使用频次，千万级别以下的不考虑优化

3、视图view

视图是由查询定义的一个虚拟的表，可以像表一样被查询、做DML操作

视图的操作语法和表完全相同,后期可以把新创建的视图当做一个表来使用，视图也可以实现增删改，DML最终执行的是原来的表。

如果视图中出现了：分组函数，distinct，group by等会将表数据分组的结果不能使用增删改

视图特点：

①视图是虚拟表，不占用磁盘物理空间

②视图可以将一些表中敏感数据隐藏，留下基本数据使用

③视图一般用来将复杂的SQL语句做包装，可以减少复杂SQL语句的重复执行

创建视图语法:create[or replace] view 表名 as select 语句（中括号里面的内容可以省略）

----创建视图，子查询统计每个学生总分，筛选出总分高于全体学生平均总分的学生； create view v_stusummark as select sid,sum(cmark) summk from group by sid; -- 创建一个每个人的总分视图 create view v_stuSumMark as SELECT sid,sum(cmark) summk FROM mark GROUP BY sid; -- 把视图当作表直接使用 SELECT sid FROM v_stuSumMark WHERE summk > (SELECT avg(summk) FROM v_stuSumMark); -- 创建一个视图，包含学生学号、姓名、课程名、分数 create view v_stuInfo as SELECT s.sid , s.sname , c.cname , m.cmark FROM student s join mark m on s.sid = m.sid join course c on m.cid = c.cid; -- 查询学生姓名和平均分 SELECT sname , avg(cmark) from v_stuInfo group by sid,sname;

4、索引（重要）

默认情况下，表中如果没有索引，都是执行全表扫描（有多少数据扫描多少次）

基本概念：索引是一种用来加快查询的数据结构，可以看做是书籍的目录，通过目录可以快速的检索到想要查找的数据

4.1索引的优缺点（背）

优点：

加快查询、连接、排序、分组效率；

唯一索引可以保证数据的唯一性；

减少磁盘IO（输入、输出），有些索引可以避免回表

缺点：

占用储存空间

降低DML（增删改）写入性能

存在失效场景，无法百分百生效

小表格创建索引没有太大用

4.2索引的分类（背）

（1）按照索引功能

①主键索引创建主键的时候自动生成索引，主键索引是最常用的索引

②唯一索引创建唯一约束的时候自动生成唯一索引，保证数据的唯一性

create unique index 索引名字 on 表名(字段名)

③普通索引index按照自己的需求，去给不同的字段加上普通索引

create index 索引名称 on 表名(字段名)

④联合索引(复合索引)把多个字段放在一起创建索引

create index 索引名称 on 表名(字段1，字段2，字段3......)

查看索引命令：show index from 表名

删除索引：alter table 表名 drop index 索引名；

（2）按照索引的存放位置分类

①聚簇索引（主键索引、效率高）

----索引列和数据捆绑在一起存放的，一个索引对应了一行数据；

----一张表只能有一个聚簇索引

----默认情况下是把主键作为聚簇索引，没有主键找唯一，没有唯一系统会生成隐式的rowid

②非聚簇索引（二级索引、非主键、会造成回表）

----普通索引、唯一索引、联合索引都属于非聚簇索引，索引创建好之后只存索引列+主键，将来查询全部的完整数据需要回表

特点：索引和数据是分开存放的，一张表可以创建多个非聚簇索引，查询的时候会回表查询

（回表：查询通过普通索引找到主键，还要拿着主键去聚簇索引找完整的数据，多一次IO，这个过程就叫做回表）

（3）按照存储结构结构分类

①B+树索引

----大部分的业务索引都是B+树结构，主键、普通、联合都是B+树。

----叶子结点存放数据，形成有序的链表，范围查询、排序、分组性能好

②hash索引（哈希计算）

----基于哈希表，使用哈希计算，如果是等值查询效率比较快，不支持范围查询、排序

----可以使用 = 或 in 去查询，不能使用 < > between like

③全文索引full text

----专门用来给长文本模糊查询的一种结构，可以解决 like%关键字% 不能走索引问题

4.3索引创建原则（背）

（1）按需创建：只为频繁查询的列创建索引

一般只要给where、join、order by等使用较多的列创建索引，如果表只做增加，几乎没有查询，也不需要创建索引

（2）高选择性的字段可以创建索引（唯一值里面筛掉重名）

选择性=唯一值数量/总行数量，越接近1越好

比如说：手机号、身份证号码、用户id都比较合适，但是性别、状态码只有两三个值不合适

（3）避免过度创建索引，降低写的效率

一般情况下，一张表中的普通索引不超过5个

如果要大批量导入数据，可以先临时删除索引，导入之后再重建索引

（4）复合索引遵循最左前缀匹配原则(要从最左边开始，否则就会失效)

复合索引（a，b，c）给a b c三个字段创建索引

索引生效：where a=?,where a=? and b=?,where a=? and b=? and c=?

索引失效：where b=?,where b=? and c=?

（5）优先使用覆盖索引消除回表

索引中包含SQL查询的全部字段，无需回到主键索引读取完整数据行

比如：select id，name from user where phone='xxxx'那么在这个句子中可以给phone 和 那name 创建一个联合索引 index_phone_name(phone,name),可以避免回表

（6）如果表比较小的话，可以不用创建索引

如果表的大小小于5M的话，一般可以不使用索引

5M=5000kb=5000*1000b=5000000b / 100b=50000条

4.4索引失效的场景

创建了索引，但是由于在编写SQL的时候使用某些语法，导致索引没有生效

查询索引是否工作：explain

写法：explain select ...查看type、key等字段

Explain select*from student where sname='张三'；

key字段有值，就表示使用索引，key 为null ，索引失败，全表扫描

type 的值如果是all，表示全表扫描

失败情况

1、索引列使用了函数

EXPLAIN select * from student where UPPER(sname) = '张三'; -- 无效 EXPLAIN select * from student where sname = '张三'; -- 有效

2、索引列存在隐式的转换（字符串和数字混用）

值如果是数字，写一个字符串作为条件，或者字段是一个字符串，写一个数字作为条件，都会导致索引失效

3、使用or连接无索引的字段

or的两侧任意一列没有索引，整个索引都会失效

EXPLAIN select * from student where sname = '张三' or sid = 10004; -- 索引生效 EXPLAIN select * from student where sname = '张三' or sclass = '1班'; -- 索引失效

4、like模糊查询通配符开头

EXPLAIN SELECT * from student where sname like '%小%'; EXPLAIN SELECT * from student where sname like '徐%';

5、复合索引不遵循给最左前缀原则

create index index_name_smajor on student(sname,snativeplace,smajor) EXPLAIN SELECT * from student where sname = '张三' and snativeplace = '江苏' ; -- 有效 EXPLAIN SELECT * from student where snativeplace = '江苏' and smajor = '计算机' ; -- 无效

6、字段数据分布极度不均匀

使用低选择性的字段加了索引

7、查询结果集占表数据量较大

筛选后的数据超过了表总量的30%，MySQL认为回表开销较大，会放弃索引

8、索引列参与了数学计算

explain select * from student where sid + 1 = 10002; -- 无效

9、使用了！=/not / not in / <> 几个字符的也会导致索引失败

4.5MySQL索引数据结构

数据结构指的是数据存放的一种结构模式

MySQL中默认采用的是B+树结构，Oracle中默认采用的是B树

B树和B+树的区别是：

①B树中的非叶子，叶子节点，存放的是完整的数据+索引键，一个节点中不能存放太多条数据，B+树中的非叶子节点只存索引键+子节点指针，不存数据，一个节点能存更多的索引

相比来说，将来B+树的高度会更低，IO次数会更少

②B树可以支持等值查询、范围查询、节点内数据有序支持排序

B+树所有数据都存在叶子节点，且形成链表，所以更有利于范围查询

5、事务（重要）

事务指的是在数据库中对数据执行的一系列的增删改操作，事务一般指的是一个操作中，包含多个增删改业务，这些业务要么全部执行，要么全部不执行，不能部分执行。

转账

A->B转账

A账户减钱 update xx set money =money -1000 where xxx

B账户加钱 update xx set money =money +1000 where xxx

这两个操作属于一个事务

5.1MySQL中事务的操作

commit 提交事务：操作完成后把所有的操作同意提交（归档，永久生效）

rollback 回滚事务：操作过程发生异常，把操作回滚（撤销所有操作）

MySQL中的事务，默认是自动提交的，只要执行了增删改语句，都会自动把数据提交，所以想要事务生效，都需要手动开启事务

开启事务的命令：start transaction;

一个事物从 start transaction 开始，到 commit 或者 rollback 结束

insert into `user` values (null,'jack','123123','2000-10-10','南京')； insert into `user` values (null,'tom','123456','2001-11-11','南京')； start transaction ;-----开启事务 insert into `user` values (null,'lucy','666666','2001-11-11','南京')； update `user` set upassword='666666' where username='jack';-----修改 delect from `user` where username='tom';-----删除 commit;-----提交 rollback;-----回滚

5.2事物的特性

①原子性：一个事物可以看成是一个原子单元，一个事务中的所有操作不可能拆分，要么一起提交，要么一起回滚

②一致性：事务执行前后数据保持一致，比如转账业务的前后

③隔离性：多个事务之间相互隔离、互不影响

④持久性：指的是事务一旦提交或者回滚，数据就会持久化存储，不会改变，除非重新开启一个新事务去操作数据

5.3并发（隔离级别）

将来多个事务同时操作，称为并发，事务并发就可能出现一些问题，将来可以设置数据库中的隔离级别，控制问题的发生。

脏读：指的是一个事物读取到另一个事务未提交的数据，脏读在实际开发中不允许出现

不可重复读:一个事务在开始的时候，查询一条数据，另一个事务B修改了这条数据并提交，事务A查询数据前后不一致了，实际开发中会根据需求确定是否可以出现不可重复读

幻读：一个事务A在做操作的时候，另一个事务B添加了一条数据，但是事务A这个时候查询不到这条数据，事务A也去添加同一条数据，会报错或者事务A修改这条数据可以修改成功，就像产生幻觉一样，称为幻读，幻读也是允许出现的

read uncommited 读未提交 会出现脏读 、不可重复读、幻读

read commited 读已提交 不会出现脏读，会出现不可重复度和幻读

repeatable read 可重复读 不会出现脏读、不可重复读，会出现幻读

serializable 可串行化 不会出现脏读 、不可重复读、幻读

mysql 中默认的隔离级别是 repeatable read

oracle 中默认的隔离级别是read committed

设置语法 ：

查询mysql全局事务隔离级别：

select @@global.transaction_isolation;

select @@session.transaction_isolation;

MySQL开始事务之前需要加上 ：

START TRANSACTION -----(开启事务)

设置当前会话隔离级别为读未提交：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

设置当前会话隔离级别为读已提交：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

设置当前会话隔离级别为可重复读：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

设置当前会话隔离级别为串行化（级别高、限制高）：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

测试脏读、不可重复读和幻读数据步骤