python大数据基础班MySQL学习总结——day05_拓展知识

笨小孩124

已于 2023-10-29 16:14:37 修改

阅读量26

点赞数

分类专栏： MySQL 学习总结文章标签： python 大数据 mysql

于 2023-10-29 11:22:09 首次发布

本文链接：https://blog.csdn.net/weixin_46010015/article/details/134100338

版权

学习总结同时被 2 个专栏收录

32 篇文章 0 订阅

订阅专栏

MySQL

6 篇文章 0 订阅

订阅专栏

MySQL拓展知识

day05_拓展知识

day05_拓展知识

特殊类型

enum类型：类似单选，每次只能插入已经定义好的数据，而且一次只能插入一个值·

set类型：类似多选，每次只能插入已经定义好的数据，而且一次可以插入多个值

# 3.特殊类型
# 建表
create table student(
    id int ,
    name varchar(100),
    age int,
    #enum()类型和set()类型的使用
    gender enum('男','女'),  # 类似单选框
    hobby set('吃饭','学习','打豆豆'), # 类似多选框
    birthday date
);
# 查看表结构
desc student;
# 演示enum类型特点: 类似单选,插入的值必须是已经定义好的
insert into student (gender) values ('妖'); -- 报错,因为妖没有提前设置
insert into student (gender) values ('男'); -- 成功
insert into student (gender) values ('女'); -- 成功
insert into student (gender) values ('男,女'); -- 失败,不能插入多个

# 演示set类型特点: 类似多选,插入的值必须是已经定义好的
insert into student (hobby) values ('睡觉'); -- 报错,因为妖没有提前设置
insert into student (hobby) values ('吃饭'); -- 成功
insert into student (hobby) values ('学习'); -- 成功
insert into student (hobby) values ('吃饭,学习'); -- 成功,可以插入多个

ER建模

ER模型图

在这里插入图片描述

E-R图由实体、属性、实体之间的联系构成，主要用来描述数据库中表结构。

实体型(Entity):具有相同属性的实体具有相同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体;在E-R图中用矩形表示，矩形框内写明实体名；比如
电商购物系统中用户、购物车、订单等都是实体。

属性(Attribute):实体所具有的某一特性，一个实体可由若干个属性来刻画。在E-R图中用椭圆形表示属性，并用无向边将其与相应的实体连接起来；比如用户的ID、用户名、密码、昵称、身份证号码都是属性。

联系(Relationship): 实体彼此之间相互连接的方式称为联系，也称为关系。在E-R图中用菱形表示，并标注关系名称.

数据库范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。（多余的重复或啰嗦内容变少，简称去重）
根据数据库冗余的大小,目前关系型数据库有六种范式,各种范式呈递次规范，越高的范式数据库冗余越小。
第一范式（1NF）
第二范式（2NF）
第三范式（3NF）
巴斯-科德范式（BCNF）
第四范式 ( 4NF）
第五范式（5NF，又称完美范式）

注意: 范式就是设计数据库的通用规范,一般遵循前三种范式即可
况。
第一范式:（1NF)强调是列的原子性，即列不能够再分成其他几列。
第二范式：(2NF)满足1NF的基础上，另外包含两部分内容
    一是表必须有一个主键；
    二是非主键字段必须间接或直接依赖于主键
第三范式：(3NF)满足2NF的基础上，另外包含
    非主键列必须直接依赖于主键，不能存在传递依赖。
     即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

存储引擎

查看存储引擎(default处就是默认存储引擎): show engines;


在MySQL中最常用存储引擎的是InnoDB和MyISAM

InnoDB和MyISAM的区别是:
   1、innodb支持事务，而myisam不支持事务。
   2、innodb支持外键约束，而myisam不支持外键约束。
   3、innodb默认行锁，而myisam是表锁（每次增加删除更新都会锁住表）。
  4、innodb是聚集索引，而myisam是非聚集索引(索引和数据是分离的)。
   5、innodb不存储表的总行数，select count( * ) from 表名;的时候会全表查询，执行相对较慢。
  而myisam会提前存放表的总行数，所以select count(*）from 表名 ;的时候会查的很快。

事务

特性和分类

事务(Transaction): 就是指逻辑上的一组sql语句操作，组成这组操作的各个sql语句，执行时要么全成功要么全失败。

事务应该具有4个特性:== 原子性、一致性、隔离性、持久性==。这四个属性通常称为ACID特性。
原子性（Atomicity）: 一个事务是一个不可分割的工作单位，事务中包括的操作要么都做，要么都不做。
一致性（Consistency）: 事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（Isolation）: 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）: 一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

事务的分类主要是两种: 隐式事务和显式事务
隐式事务:该事务没有明显的开启和结束标记，它们都具有自动提交事务的功能；我们的DML语句（insert、update、delete）就是隐式事务。
显式事务:该事务具有明显的开启和结束标记。使用显式事务的前提是你得先把自动提交事务的功能给禁用,禁用自动提交功能就是设置autocommit变量值为0（0:禁用 1:开启）

查看自动提交事务状态: select @@autocommit;
禁用自动提交事务的方式: set autocommit = 0;

# 查看事务提交状态(0:禁用 1:开启)
select @@autocommit; # 第一次查询,默认结果是1
# 隐式事务: 平常默认增删改操作都是隐式事务,也就是自动开启和提交事务
# 创建数据库并使用它
create database binzi;
use binzi;
# 创建账目表
create table binzi_account(
    id int,
    name varchar(100),
    money double
);
# 插入数据查看效果
insert into binzi_account values(1,'阿三',10000);
insert into binzi_account values(2,'斌子',10000);


# 显式事务: 增删改操作需要手动开启和提交事务
# 临时禁用自动提交功能(0:禁用  1:开启)
set autocommit = 0;
# 查看事务提交状态(0:禁用 1:开启)
select @@autocommit; # 修改后查看是0


# 需求: 阿三给斌子转账1000
# 1.演示正常事务流程
# 开启事务
start transaction;
# 阿三账户先减1000
update binzi_account set money = money-1000 where name='阿三';
# 斌子账户再加1000
update binzi_account set money = money+1000 where name='斌子';
# 提交事务
commit ;


# 2.演示异常事务流程
# 需求: 阿三给斌子转账2000
# 开启事务
start transaction;
# 阿三账户先减2000
update binzi_account set money = money-2000 where name='阿三';
# 假设中间程序中断,导致后续添加失败,此时需要回滚事务
# 回滚事务: 注意只要提交了,就不能回滚
rollback;
# 斌子账户添加失败
# update binzi_account set money = money+2000 where name='斌子';
# 提交事务
commit ;

并发问题和隔离级别

并发问题:脏读, 不可重复读 , 幻读
脏读：对于两个事务T1,T2，T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取的内容就是临时且无效的
不可重复读：对于两个事务T1,T2，T1读取了一个字段，然后T2更新了该字段之后，T1在读取同一个字段，值就不同了
幻读：对于两个事务T1,T2，T1在A表中读取了一个字段，然后T2又在A表中插入了一些新的数据时，T1再读取该表时，就会发现神不知鬼不觉的多出几行了…
隔离级别: mysql默认隔离级别repeatable read mysql中的四种事务隔离级别如下：
1. read uncommitted（读未提交数据）：允许事务读取未被其他事务提交的变更。（脏读、不可重复读和幻读的问题都会出现）。
2. read committed（读已提交数据）：只允许事务读取已经被其他事务提交的变更（可以避免脏读，但不可重复读和幻读的问题仍然可能出现）
3. repeatable read（可重复读）：确保事务可以多次从一个字段中读取相同的值，在这个事务持续期间，禁止其他事务对这个字段进行更新(update)。（可以避免脏读和不可重复读，但幻读仍然存在）
4. serializable（串行化）：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作，所有并发问题都可避免，但性能十分低下（因为你不完成就都不可以弄，效率太低）

查看mysql的默认事务隔离级别: select @@transaction_isolation;

注意: oracle默认隔离级别read committed

索引

索引:
MySQL官方对索引的定义是：索引（Index）是帮助MySQL高效获取数据的数据结构。索引最形象的比喻就是图书的目录。注意只有在大量数据中查询时索引才显得有意义。

MySQL常见的索引分类：
按数据结构分类:B+tree索引、Full-text索引、Hash索引。
按物理存储分类:聚集索引、非聚集索引（也叫二级索引、辅助索引）。
按字段个数分类:单列索引、联合索引（也叫复合索引、组合索引）。
按字段特性分类:主键索引(PRIMARY KEY)、唯一索引(UNIQUE INDEX)、普通索引(INDEX)、全文索引(FULLTEXT)

主键索引(PRIMARY KEY): 建立在主键上的索引被称为主键索引，一张数据表只能有一个主键索引，索引列值不允许有空值，不允许重复,通常在创建表时一起创建。
唯一索引(UNIQUE INDEX):建立在UNIQUE字段上的索引被称为唯一索引，一张表可以有多个唯一索引，索引列值允许为空，不允许重复,注意:列值中出现多个空值不会发生重复冲突。
普通索引(INDEX): 建立在普通字段上的索引被称为普通索引。
全文索引(FULLTEXT INDEX): MyISAM存储引擎支持Full-text索引，用于查找文本中的关键词，而不是直接比较是否相等。Full-text索引一般使用倒排索引实现，它记录着关键词到其所在文档的映射。

MySQL 5.6 以前的版本:只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本:MyISAM 和 InnoDB 存储引擎均支持全文索引;
注意: 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

# 创建表
create table student_index(
    id int,
    name varchar(100) ,
    age int ,
    gender enum('男','女','妖'),  # 单选框
    hobby set('吃饭','学习','打豆豆'), # 多选框
    birthday datetime
);
# 查看所有索引
show index from student_index;

# 添加主键索引
alter table student_index add primary key(id);
# 添加唯一索引
create unique index name_index on student_index(name);
# 添加普通索引
create index age_index on student_index(age);
# 添加全文索引(注意:字段类型只能是字符串相关的)
create fulltext index full_index on student_index(name);
# 添加组合索引(注意:查询的时候必须遵循最左前缀原则)
create index mul_index on student_index(gender,hobby);
create index mul2_index on student_index(gender,hobby,birthday);

# 查看所有索引
show index from student_index;

# 删除组合索引
drop index mul_index on student_index;
drop index mul2_index on student_index;
# 删除全文索引
drop index full_index on student_index;
# 删除普通索引
drop index age_index on student_index;
# 删除唯一索引
drop index name_index on student_index;
# 删除主键索引
alter table student_index drop primary key;


# 查看所有索引
show index from student_index;

视图

视图: （view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。

注意: 数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。也就是说原表数据改变会影响视图数据

# 查看c002分类的商品
select * from day04.products where category_id = 'c002';
# 如何存储查询后的临时结果集?
# 方式1: 建新表存储(占用本地空间,从原表中把对应数据存储到新表中)
create table product_c2 as select * from day04.products where category_id = 'c002';
# 方式2: 建视图存储(只存储了定义,实际数据还在原表中存存储)
create view product_c3 as select * from day04.products where category_id = 'c003';


# 需求: 创建视图,存储products和category两表内连接后的结果集
create VIEW product_detail
    as
select *
from day04.products p join day04.category c on p.category_id = c.cid;

# 演示视图的好处以及应用
# 因为刚刚product_detail已经存储了products和category内连接结果
# 现在查询数据可以直接使用product_detail视图
# 需求2: 查询化妆品的所有商品信息
select * from product_detail where cname = '化妆品';


# 删除product_c3视图
drop view product_c3;

# 修改视图数据
alter view product_detail as select * from day04.products;
# 修改视图名(虚拟表名)
rename table product_detail to products;

# 查看表/视图和对应类型
show full tables;

# 查看视图信息
desc products;