益阳营销网站建设,洛阳西工区做网站哪家好,wordpress网站加速工具,django网站开发视频教程下载文章目录 MySQL数据库学习1 表1.1 表中的数据类型1.2 表的创建1.3 表的删除1.4 default设置字段默认值1.5 表结构的修改1.5.1 表名的修改1.5.2 字段名的修改1.5.3 修改字段类型1.5.4 添加字段1.5.5 删除字段1.5.6 表的复制 1.6 表的约束1.6.1 什么是约束#xff1f;1.6.2 not … 文章目录 MySQL数据库学习1 表1.1 表中的数据类型1.2 表的创建1.3 表的删除1.4 default设置字段默认值1.5 表结构的修改1.5.1 表名的修改1.5.2 字段名的修改1.5.3 修改字段类型1.5.4 添加字段1.5.5 删除字段1.5.6 表的复制 1.6 表的约束1.6.1 什么是约束1.6.2 not null 非空约束1.6.3 unique 唯一性约束1.6.4 primary key 主键约束单一主键复合主键自然主键业务主键auto increment 自动递增删除主键约束 1.6.5 foreign key 外键约束外键的缺点 1.6.6 约束的增删改 2 insert 数据插入DML2.1 insert into 的基本用法2.2 insert into 插入日期与前一部分相同2.2.1 str_to_date(必须严格按照标准输出)2.2.2 data_format 3 delete 删除数据4 update 更新数据5 存储引擎3.1 什么是存储引擎3.2 怎么给表指定 存储引擎 3.3 Mysql有哪些存储引擎3.4 常用的存储引擎 6 transaction事务非常重要4.1什么是事务4.2 事务的特征ACID4.3 事务的原理4.4 事务的提交和回滚4.5 事物的隔离性4.6 隔离级别的验证验证read uncommitted 读未提交验证read committed 读已提交验证repeatable read 可重复读验证serializable 序列化 7 index索引7.1 什么是索引7.2 索引的实现原理7.3 怎么判断是否创建索引单列索引/复合索引的选择?不需要创建索引的情况 7.4 索引的创建与删除7.5 查看是否有索引7.6 索引的失效重要% 开头or使用复合索引时没有使用左侧的列进行查找索引失效where中索引列参加了运算where中使用了函数其它情况 8 view视图8.1 视图是什么8.2 视图的创建和删除8.3 视图的使用 9 DBA常用命令9.1 数据的导入和导出导出导入授权等操作 10 三大范式重要10.1 什么是三大范式10.2 三大范式第一范式第二范式第三范式 10.3 表设计的总结 MySQL数据库学习
1 表
对于表的操作属于DDLDDL为Data Definition Language 数据定义语言
DDL 数据定义语言Data Definition Languagecreate 创建drop 删除表、数据库、约束、触发程序等alter 修改Truncate语句清空表里的数据。1.1 表中的数据类型
以下是一些常见的数据类型
标识符特点varchar可变长度字符串节省空间根据实际的数据长度动态分配空间优点节省空间缺点需要动态分配空间速度慢variable:可变的varchar最大长度为255char定长字符串不管数据长度为多少分配固定的空间优点不需要动态分配空间速度快缺点使用不恰当的话会导致空间的浪费char最大长度为255int整型数字等同于java中的intint最大长度为11bigint长整型等同于java中的longfloat单精度浮点数double双精度浮点数date短日期类型datetime厂日期类型clob字符大对象最多可以存储4gb的字符串例如一篇文章等。超过255个字符的数据通常使用clobCharacter Large Obejct :CLOBblo二进制大对象Binary Large Object:BLOB一般用来存储图片、声音、视频等流媒体数据。往Blog类型的字段上插入数据的时候需要使用IO流。
1.2 表的创建
create table 表名(字段名 数据类型,字段名 数据类型...,字段名 数据类型
); 注意分号结尾注意
建表时尽量以 t_ 或者 tbl_ 开始可读性强字段名最好是见名知意且意思清除明确
表名和字段名都属于标识符
//不同编码中字符和字节的对应关系不同。UTF-8编码中一个英文字符等于一个字节一个中文含繁体等于三个字节。
ASCII码中一个英文字母占一个字节的空间一个中文汉字占两个字节的空间。
Unicode编码中一个英文和一个汉字均占两个字节。4.0版本以下varchar(100)指的是100字节如果存放UTF8汉字时只能存33个每个汉字3字节 5.0版本以上varchar(100)指的是100字符无论存放的是数字、字母还是UTF8汉字每个汉字3字节都可以存放100个。一个长度代表一个字符一个中文字符占一个长度一个英文字母也占一个长度。
#创建一个学生表
#学号、姓名、年龄、邮箱地址
create table t_student(stu_no int(11),stu_name varchar(20),stu_sex char(1),stu_age int(3),stu_email varchar(255)
);1.3 表的删除
drop table 表名
drop table if exists 表名;drop table t_student;
#当这张表t_student不存在时会报错
drop table if exists t_student;
#当这张表不存在时也不会报错mysql drop table if exists t_student;
Query OK, 0 rows affected (0.04 sec)1.4 default设置字段默认值
#同样的一个表我们给每个字段添加对应的默认值
drop table if exists t_student;
create table t_student(stu_no int(11),stu_name varchar(20),stu_sex char(1) default 男,stu_age int(3),stu_email varchar(255)
);
#然后插入一条数据
mysql insert into t_student(stu_no)values(001);
Query OK, 1 row affected (0.02 sec)
#查看表内信息
mysql select * from t_student;
-----------------------------------------------
| stu_no | stu_name | stu_sex | stu_age | stu_email |
-----------------------------------------------
| 1 | NULL | 男 | NULL | NULL |
-----------------------------------------------
1 row in set (0.00 sec)
#default可以指定对应的默认值但是默认也要满足对应匹配的条件1.5 表结构的修改
什么是表结构的修改
添加、删除、修改某个字段。
#第一实际的开发中需求一旦确定之后表一旦设计好之后很少会对结构进行修改。修改 表的结构成本比较高对应的基于表的代码就会需要大量的修改。
#第二可以借助工具1.5.1 表名的修改
alter table 原来的表名 rename to 新的表名;1.5.2 字段名的修改
# data_type: 字段类型
alter table 表名 change 旧字段名 新字段名 数据类型;
# 注意后面的字段类型必须跟1.5.3 修改字段类型
# tb_name表名称
# filed_name字段名
# data_type: 字段类型
alter table tb_name modify filed_name data_type;1.5.4 添加字段
# data_type: 字段类型
# tb_name表名称
# filed_name字段名
# name_1: 某个字段
# 默认加在尾部
alter table tb_name add field_name data_type;
# 指定加在开头
alter table tb_name add field_name data_type first;
# 加到中间指定位置后面 ---- 加到指定的name_1 字段后面
alter table tb_name add field_name data_type after name_1;1.5.5 删除字段
# tb_name表名称
# filed_name字段名
alter table tb_name drop field_name;1.5.6 表的复制
create table 新表表名 as select * from 已有的表;
例如
create table dept2 as select * from dept;1.6 表的约束
1.6.1 什么是约束
#约束constraint
在创建表的时候我们对表中的字段加上一些约束保证表中数据的完整性有效性。#常见的约束
-非空约束 not null
-唯一约束 unique
-主键约束 primary key
-外键约束 foreign key
-检查约束 check MySql中不支持mysql所有的存储引擎均不支持check约束MySQL会对check子句进行分析但是在插入数据时会忽略因此check并不起作用因此实现对数据约束有两种方法
1.在mysql种约束如使用enum类型或者触发器等。
2.在应用程序里面对数据进行检查再插入。1.6.2 not null 非空约束
被not null指定的字段在插入数据时不能为null也就是必须有数据
#一个表可以由多个非空约束和唯一约束
drop table if exists t_vip;
create table t_vip(id int(10),name varchar(10) not null
);insert into t_vip values(00,makabaka);
insert into t_vip values(01,wuxidixi);
insert into t_vip values(10,tomblido);insert into t_vip(id) values(11);
#出错因为没有对not null字段进行赋值
ERROR 1364 (HY000): Field name doesnt have a default value#修改非空约束
alter table orders
alter column customer_number varchar(20) not nullalter table orders
alter column order_number varchar(20) not nullalter table orders
alter column order_line int not null1.6.3 unique 唯一性约束
被唯一性约束 unique 约束的字段其值不能重复但是可以为NULL。
#一个表可以由多个非空约束和唯一约束
drop table if exists t_vip;
create table t_vip(id int,name varchar(10) unique,email varchar(30)
);
insert into t_vip(id,name,email) values(1,zhangsan,123123.com);
insert into t_vip(id,name,email) values(2,null,123234.com);
insert into t_vip(id,name,email) values(2,null,123234.com);
#name字段虽然被unique约束了但是可以多个为nullinsert into t_vip(id,name,email) values(3,zhangsan,123123.com);
#出错因为已经有了zhangsan字段。
ERROR 1062 (23000): Duplicate entry zhangsan for key t_vip.name
#Duplicate:复制完全一样的重复的
#entry条目参与加入如何让 name 和 email 两个字段联合起来具有唯一性unique的另一种写法
drop table if exists t_vip;
create table t_vip(id int,name varchar(10) unique,email varchar(30) unique
);
#以上代码不符合需求表示各自唯一drop table if exists t_vip;
create table t_vip(id int,name varchar(10),email varchar(30),unique(name,email)
);
#unique(name,email) 表示name和email两个字段联合起来唯一
insert into t_vip(id,name,email) values(4,null,123234.com);
insert into t_vip(id,name,email) values(5,null1,123234.com);#约束直接添加到字段后面的是对字段进行约束属于列级约束
#约束添加到建表的最后的采用unique(a,b)可以对多个字段进行联合唯一性约束属于表级约束
什么时候使用标记约束
给多个数据添加联合约束时。
在mysql中某个字段被not null 和 unique 两个关键字进行约束就会成为主键primary key oracle中不一样 #用法
drop table if exists t_vip;
create table t_vip(id int not null unique,name varchar(10),email varchar(30)
);
mysql desc t_vip;
-----------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------
| id | int | NO | PRI | NULL | |新增了primary key
| name | varchar(10) | YES | | NULL | |
| email | varchar(30) | YES | | NULL | |
-----------------------------------------------
3 rows in set (0.00 sec)
1.6.4 primary key 主键约束
#主键约束
#主键字段被添加了primary key 关键字的字段。
#主键值主键字段中的每一个值都是主键值。
#每个表中可以有多个非空和唯一约束但是主键约束只能有一个单一或者复合什么是主键
主键值是每一行记录的唯一标识符。
没有主键的表会被某些工具标为无效。
一般在不容易重复的字段上进行标记如身份证号ID等。
主键的特征not null unique 主键不能为空且不能重复
单一主键
drop table if exists t_vip;
create table t_vip(id int primary key,name varchar(10)
);
insert into t_vip values(1,name1);
insert into t_vip values(2,name1);insert into t_vip values(1,name2);
#错误
ERROR 1062 (23000): Duplicate entry 1 for key t_vip.PRIMARY主键也可以使用表级约束的形式如下
drop table if exists t_vip;
create table t_vip(id int,name varchar(10),primary key(id)
);复合主键
多个字段联合起来作为主键
drop table if exists t_vip;
create table t_vip(id int,name varchar(10),primary key(id,name)
);
insert into t_vip values(1,name1);
insert into t_vip values(1,name2);
#上面两条SQL语句可以成功执行虽然1重复了但是name1和name2没有重复所以可以成功插入。实际开发中无特殊需求建议使用单一主键。
主键值一般是定长的。
主键值建议使用int 、 bigint 、 char 、等类型不建议使用 varchar 。
自然主键
主键值是一个自然数和业务没有关系
业务主键
主键值和业务紧密关联例如银行卡账号作为主键值。
在实际的开发中自然主键使用比较多因为主键一旦和业务挂钩当业务发生变动的时候就可能会影响到主键值所以建议使用自然主键。
auto increment 自动递增
在mysql中有一种机制可以帮助我们自动维护一个主键值。
drop table if exists t_vip;
create table t_vip(id int primary key auto_increment,name varchar(10)
);
insert into t_vip(name) values(name1);
insert into t_vip(name) values(name2);#auto_increment 从1开始递增mysql select * from t_vip;
-----------
| id | name |
-----------
| 1 | name1 |
| 2 | name2 |
-----------
2 rows in set (0.00 sec)#实现了自动增长删除主键约束
alter table orders
drop constraint pk_od1.6.5 foreign key 外键约束
#外键约束一种约束foreign key
#外键字段字段上添加了外键约束
#外键值外键约束中的每一个值drop table if exists t_student;
drop table if exists t_class;
create table t_class(class_no int primary key,class_name varchar(20)
);
create table t_student(student_no int primary key auto_increment,student_name varchar(10),class_no int,foreign key(class_no) references t_class(class_no)
);
#oreign key(class_no) references t_class(class_no)insert into t_class values(100,成都市);
insert into t_class values(101,绵阳市);insert into t_student(student_name,class_no) values(玛卡巴卡,100);
insert into t_student(student_name,class_no) values(无息地西,101);insert into t_student(student_name,class_no) values(无息地西,102);
#错误因为没有102的班级
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (sqlstudy.t_student, CONSTRAINT t_student_ibfk_1 FOREIGN KEY (class_no) REFERENCES t_class (class_no))子表中的外键引用了父表中的某个字段被引用的这个字段必须是主键吗
不一定是主键但至少具有unique约束具有唯一性外键可以为NULL吗
可以为null删除表的顺序删除数据的顺序
先删除子表再删除父级表先删除子表数据再删除父表数据
创建表的顺序插入数据的顺序
先创建父级表再创建子级表先插入父级表再插入子级表
外键的缺点
一、级联问题
阿里巴巴的开发手册中就曾指出强制要求不允许使用外键一切外键概念必须在应用层解决。 因为每次级联delete或update的时候都要级联操作相关的外键表不论有没有这个必要由其在高并发的场景下这会导致性能瓶颈
二、增加数据库压力
外键等于把数据的一致性事务实现全部交给数据库服务器完成并且有了外键当做一些涉及外键字段的增删更新操作之后需要触发相关操作去检查而不得不消耗资源
三、死锁问题
若是高并发大流量事务场景使用外键还可能容易造成死锁
四、开发不方便
有外键时无论开发还是维护需要手工维护数据时都不太方便要考虑级联因素
总结
一、如是单机且低并发也不需要性能调优再或者不能用程序保证数据的一致性完整性可以使用外键
二、如果为了高并发分布式使系统性能更优以及更好维护则一定不能使用外键
1.6.6 约束的增删改
没多大用又多又杂…干脆放个链接在这以后用得着的时候看看就行。
http://t.zoukankan.com/howie-we-p-12075456.html2 insert 数据插入DML
2.1 insert into 的基本用法
#语法格式
insert into 表名(字段名1, 字段名2, 字段名3...,所有的字段名) values (值1,值2,值3...,字段对应的值)#value后面括号中的值是和字段一一对应的且数据类型要一一对应#以上面的表为例插入一条数据
insert into t_student(stu_no,stu_name,stu_sex,stu_age,stu_email)values(001,张三,男,22,abcqq.com);
#数据值和类型必须匹配insert into t_student(stu_no)values(002);
#插入之后若没有给其它字段指定值其它值会变为null
mysql select * from t_student;
------------------------------------------------
| stu_no | stu_name | stu_sex | stu_age | stu_email |
------------------------------------------------
| 1 | 张三 | 男 | 22 | abcqq.com |
| 2 | NULL | NULL | NULL | NULL |
------------------------------------------------
2 rows in set (0.00 sec)#insert into语句只要执行成功就会增加一条数据可以省略前面的字段名吗
可以
#原语句
insert into t_student(stu_no,stu_name,stu_sex,stu_age,stu_email)values(001,张三,男,22,abcqq.com);
#省略后
insert into t_student values(001,张三,男,22,abcqq.com);
#结果
mysql select * from t_student;
------------------------------------------------
| stu_no | stu_name | stu_sex | stu_age | stu_email |
------------------------------------------------
| 1 | NULL | 男 | NULL | NULL |
| 1 | 张三 | 男 | 22 | abcqq.com |
------------------------------------------------
2 rows in set (0.00 sec)
#注意
#省略了字段名后相当于填写了所有的字段所以值内必须匹配表内所有的字段值才能插入成功2.2 insert into 插入日期与前一部分相同
2.2.1 str_to_date(必须严格按照标准输出)
把字符串转为日期 select * from emp where hiredatestr_to_date(1981-02-20);drop table if exists t_user;create table if not exists t_user(user_id int,user_name varchar(30),user_birth date);#插入数据时insert into t_user values(1,makabaka,1990-10-1); #执行成功....Query OK, 1 row affected (0.01 sec)#但是如果插入时没有按照对应的以 年-月-日 来插入如下insert into t_user values(1,makabaka,10-1-1990); #结果ERROR 1292 (22007): Incorrect date value: 10-1-1990 for column user_birth at row 1#执行错误因为19990-10-1不是一个date类型的数据语法格式str_to_date(日期的字符串,日期的格式)mysql 的日期格式%Y 年%m 月%d 日%h 时%i 分%s 秒insert into t_user values(2,wuxidixi,str_to_date(10-1-1990,%m-%d-%Y)); #此时Query OK, 1 row affected (0.00 sec)#执行成功str_to_date函数可以把字符串varchar转为日期date类型的数据
通常使用在insert into数据插入方面因为某些字段会被定义为date类型的数据
需要通过该函数将字符串转为date类型
但是若插入时字符串匹配了 [ 年-月- 日 ] 格式就不用使用该函数。
了解一下
#java中的日期格式
yyyy-HH-dd HH:mm:ss SSS2.2.2 data_format
将date类型转换成一定格式的varchar类型字符串
用法
date_format(日期类型的数据,需要显示的日期格式)
一般用在查询日期方面设置展示的日期格式。select user_id as 编号,user_name as 姓名,date_format(user_birth,%m/%d/%Y) as 生日 from t_user;
#结果
----------------------------
| 编号 | 姓名 | 生日 |
----------------------------
| 1 | makabaka | 10/01/1990 |
| 2 | wuxidixi | 10/01/1990 |
----------------------------
2 rows in set (0.00 sec)#select查询date型的数据时会自动将date转为varchar类型显示在dos命令窗口中并且采用磨人的mysql日期格式%Y-%m-%d3 delete 删除数据
DELETE FROM 表名称 WHERE 列名称 值4 update 更新数据
UPDATE 表名称 SET 列名称 新值 WHERE 列名称 某值5 存储引擎
3.1 什么是存储引擎
存储引擎是MySQL中特有的一个术语其他数据库中没有。Oracle中是另外一个名字。
存储引擎是一个表存储/组织数据的方式。
不同的引擎存储数据的方式也不同。3.2 怎么给表指定 存储引擎
#使用 show create table 表名
#可以看到创建表时的SQL语句如下drop table if exists t_user;
create table if not exists t_user(user_id int primary key auto_increment,user_name varchar(30)
);mysql show create table t_user;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t_user | CREATE TABLE t_user (user_id int NOT NULL AUTO_INCREMENT,user_name varchar(30) DEFAULT NULL,PRIMARY KEY (user_id)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)#可以看到上面有一个ENGINEInnoDB
#可以在建立表的时候可以在小括号后面指定存储引擎和字符编码方式#mysql默认的存储引擎是InnoDB
#mysql默认的字符编码方式是utf8MySQL在5.5.3之后增加了这个utf8mb4的编码,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode。而Mysql的utf8编码最多三个字节。 好在utf8mb4是utf8的超集,除了将编码改为utf8mb4外不需要做其他转换。通俗点讲就是,utf8mb4作为utf8的super set,完全向下兼容,所以不用担心字符的兼容性问题。当然,为了节省空间,一般情况下使用utf8也就够了。drop table if exists t_user;
create table if not exists t_user(user_id int primary key auto_increment,user_name varchar(30)
)engine innoDB default charset gbk;#创建表时需指定引擎可以使用ENGINE语句如果省略则使用默认引擎很可能是MyISAM#引擎类型可以混用一个数据库中不同表可以使用不同引擎#混用引擎类型有一个缺陷外键不能跨引擎即使用了一个引擎的表不能引用具有使用不同引擎的表的外键3.3 Mysql有哪些存储引擎
#使用 show engines \G 命令可以查看mysql所有的引擎
#MySQL支持的引擎由版本号的不同来控制
#select version();查看版本号
mysql select version();
-----------
| version() |
-----------
| 8.0.28 |
-----------
1 row in set (0.00 sec)mysql show engines \G
*************************** 1. row ***************************Engine: MEMORYSupport: YES #支持Comment: Hash based, stored in memory, useful for temporary tables#基于哈希的存储在内存中对临时表有用
Transactions: NO #事务处理XA: NO #分布式事务协议Savepoints: NO #保存点
*************************** 2. row ***************************Engine: MRG_MYISAMSupport: YESComment: Collection of identical MyISAM tables#相同MyISAM表的集合
Transactions: NOXA: NOSavepoints: NO
*************************** 3. row ***************************Engine: CSVSupport: YESComment: CSV storage engine#CSV存储引擎
Transactions: NOXA: NOSavepoints: NO
*************************** 4. row ***************************Engine: FEDERATED Support: NO #不支持Comment: Federated MySQL storage engine#联合MySQL存储引擎
Transactions: NULLXA: NULLSavepoints: NULL
*************************** 5. row ***************************Engine: PERFORMANCE_SCHEMASupport: YESComment: Performance Schema#性能模式
Transactions: NOXA: NOSavepoints: NO
*************************** 6. row ***************************Engine: MyISAMSupport: YESComment: MyISAM storage engine#MyISAM存储引擎
Transactions: NOXA: NOSavepoints: NO
*************************** 7. row ***************************Engine: InnoDBSupport: DEFAULTComment: Supports transactions, row-level locking, and foreign keys#支持事务、行级锁定和外键
Transactions: YESXA: YESSavepoints: YES
*************************** 8. row ***************************Engine: BLACKHOLESupport: YESComment: /dev/null storage engine (anything you write to it disappears)#/开发/空存储引擎您向其写入的任何内容都会消失
Transactions: NOXA: NOSavepoints: NO
*************************** 9. row ***************************Engine: ARCHIVESupport: YESComment: Archive storage engine#存档存储引擎
Transactions: NOXA: NOSavepoints: NO
9 rows in set (0.00 sec)[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Lr2YKaDS-1690357396085)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220801101643121.png)]
https://blog.csdn.net/flycp/article/details/108064705在选择存储引擎时应该根据应用系统的特点选择合适的存储引擎。
对于复杂的应用系统还可以根据实际情况选择多种存储引擎进行组合。3.4 常用的存储引擎
MyISAM存储引擎
使用三个文件表示每个表格式文件-存储表结构的定义mytable.frm)数据文件-存储表行的内容mytable.MYD索引文件-存储表上的索引mytable.MYI#索引可以缩小扫描范围提高检索效率#对于一张表来说只要是主键或者是unique约束的字段都会自动添加索引。灵活的Auto_increment字段处理
可被转换为压缩、只读来节省空间
#安全性低InnoDB存储引擎 mysql默认的存储引擎同时也是一个重量级的存储引擎支持事务支持数据库崩溃后自动恢复的机制安全每个InnoDB表在数据库目录中以.frm格式文件表示InnoDB 表空间 tablespace 被用于存储表的内容表空间是一个逻辑名称表空间存储数据索引提供一组用来记录事务性活动的日志文件用 commit\savepoint\rollback 支持事务处理提供全ACID兼容多版本NVCC和行级锁定支持外键及引用的完整性也包括级联删除和更新#InnoDB最大的特点就是支持事务以保证数据的安全性但效率不是特别高#不能很好的节省存储空间MEMORY存储引擎
#使用memory存储引擎的表其数据存储在内存中且行的长度固定。
#处理速度非常快#memory引擎的特点
数据库目录内每个表安.frm格式文件表示
表数据和索引存储在内存中
表级锁机制
不能包含text 或者 blob 字段
memory 存储引擎以前被称为HEAP 引擎
#heap:堆#memory 查询效率最高。
#但是不安全关机之后数据消失。6 transaction事务非常重要
4.1什么是事务 事务就是用户定义的一系列数据库操作这些操作可以视为一个完成的逻辑处理工作单元要么全部执行要么全部不执行是不可分割的工作单元。 如假设A账户向B账户转账100
则A账户里的钱应该减去100B账户里的前应该增加100。
以上的两个操作要么同时成功要么同时失败一致性才能保证业务的正常运行。
4.2 事务的特征ACID
(1) 原子性(Atomicity)#“要么全部执行要不都不执行”
(2)一致性(Consistency)#必须满足数据库的完整性约束且事务执行完毕后会将数据库由一个一致性的状态变为另一个一致性的状态。与原子性紧密相关。
(3)隔离性(Isolation)#事务之间是彼此独立的隔离的。及一个事务的执行不可以被其他事务干扰。锁
(4)持续性(Durability)#是指一个事务一旦提交它对数据库的改变将是永久性的因为数据刷进了物理磁盘了其他操作将不会对它产生任何影响。insert
delete
update
只有以上三个语句和事务有关。
只要操作设计数据的增删改那么就需要考虑安全问题。
4.3 事务的原理
https://blog.csdn.net/qq_37362891/article/details/123070843
redo log 和 undo log事务是怎么多条DML语句同时成功和同时失败的呢
InnoDB存储引擎提供一组用来记录事务性活动的日志文件
提交事务
清空事务性活动的日志文件将数据全部彻底持久化到数据库表中
提交事务标志着事务的结束并且是一种全部成功的结束
回滚事务
将之前的DML操作全部撤销并且清空事务性活动的日志文件
回滚事务标志着事务的结束并且表示所有语句全都失败的结束
4.4 事务的提交和回滚
提交事务commit
回滚事务rollback
事务transaction
MySQL 在默认情况下是支持自动提交事务的也就是每执行一条DML提交一次
#如何关闭事务自动提交
start transaction;
#提交
commit;
#回滚
rollback;#创建一个新的表
drop table if exists t_user;
create table if not exists t_user(user_id int primary key auto_increment,user_name varchar(30)
);#此时表内数据为空
mysql select * from t_user;
Empty set (0.00 sec)#开启事务
mysql start transaction;
Query OK, 0 rows affected (0.00 sec)#插入数据
insert into t_user(user_name)values(makabaka);
insert into t_user(user_name)values(wuxidixi);#表中有两条数据
mysql select * from t_user;
--------------------
| user_id | user_name |
--------------------
| 4 | makabaka |
| 5 | wuxidixi |
--------------------
2 rows in set (0.00 sec)#此时进行提交
mysql commit;
Query OK, 0 rows affected (0.00 sec)#再插入一条数据
#提交或者回滚都意为一个事务的结束所以要重新打开一个事务
start transaction;
insert into t_user(user_name)values(tom);#共插入了三条数据
mysql select * from t_user;
--------------------
| user_id | user_name |
--------------------
| 1 | makabaka |
| 2 | wuxidixi |
| 3 | tom |
--------------------
3 rows in set (0.00 sec)#此时进行事务回滚
mysql rollback;
Query OK, 0 rows affected (0.00 sec)#再查看表内
mysql select * from t_user;
--------------------
| user_id | user_name |
--------------------
| 1 | makabaka |
| 2 | wuxidixi |
--------------------
2 rows in set (0.00 sec)#未提交的数据被回滚了4.5 事物的隔离性
事务与事务之间的隔离级别有哪些呢
#读未提交read uncomitted最低的隔离级别什么是读未提交事务A可以读取到事务B未提交的数据。这种隔离级别存在的问题就是Dirty Read 脏读现象读到了脏数据这种隔离级别都试了理论上的大多数的数据库隔离级别都是在第二个读已提交之后的。#读已提交read committed什么是读已提交事务A只能读到事务B提交之后的数据。这种隔离级别解决了脏读现象。不可重复读数据。事务在开启之后第一次和第二次读取的数据可能不会相同所以不可重复读。这种隔离级别是比较真实的数据每次读取的都是绝对的真实数据。oracle数据库默认的隔离级别就是 read committed#可重复读repeatable read什么是可重复读事务开启之后无论多久在事务A中读取到的数据都是一致的即使事务B已经将数据修改且提交了但是事务A读取到的数据还是不会发生改变这就是可重复读。解决了不可重复读数据的问题。但是会产生幻影读每一次读取的数据都是幻象不够真实。#序列化/串行化serializable (最高的隔离级别)什么是序列化是最高级别的隔离效率最低解决了所有的问题。这种隔离级别表示事务排队不能并发。synchronize线程同步事务同步每一次读取到的数据都是最真实的且效率最低。4.6 隔离级别的验证
#查看隔离级别
select transaction_isolation;
#结果
mysql select transaction_isolation;
-------------------------
| transaction_isolation |
-------------------------
| REPEATABLE-READ |#可重复读
-------------------------
1 row in set (0.00 sec)#设置全局的隔离级别
set global transaction isolation level read uncommitted;
#设置完成后记得输入exit退出重新登陆mysql#创建一个新的表
drop table if exists t_user;
create table if not exists t_user(user_id int primary key auto_increment,user_name varchar(30)
);验证read uncommitted 读未提交
set global transaction isolation level read uncommitted;事务A事务Buse sqlstudy;use sqlstudy;start transaction;select * from t_user;start transaction;insert into t_user(name)values(‘张三’);此语句未提交select * from t_user;(可以查询到张三)
验证read committed 读已提交
set global transaction isolation level read committed;事务A事务Buse sqlstudy;use sqlstudy;start transaction;start transaction;select * from t_user;什么都读不到insert into t_user(name)values(‘张三’);此语句未提交select * from t_user;(查询不到张三)commit;select * from t_user;(可以查询到张三)
验证repeatable read 可重复读
set global transaction isolation level read committed;事务A事务Buse sqlstudy;use sqlstudy;start transaction;start transaction;select * from t_user;什么都没有insert into t_user(name)values(‘张三’);此语句未提交insert into t_user(name)values(‘李四’);此语句未提交commit;提交了以上语句select * from t_user;(可以查询到张三、李四)select * from t_user;(什么都读取不到)select * from t_user;(什么都读取不到)select * from t_user;(什么都读取不到)
左边结束事务后再进行读取就能读取到了
验证serializable 序列化
set global transaction isolation level serializable;事务A食物Buse sqlstudy;use sqlstudy;start transaction;start transaction;select * from t_user;什么都没有insert into t_user(name)values(‘李四’);此语句未提交事务未结束select * from t_user;什么都没有且光标卡住无法执行任何指令commit(事务提交完成)待左边提交完成自动显示上一条select查询语句的结果。
7 index索引
7.1 什么是索引
参考文章
http://c.biancheng.net/sql/indexes.html索引是为了提高检索效率缩小扫描范围的一种机制是在字段上添加的一种关键字。索引可以添加在字段上也可以多个字段联合起来作为索引。例如
字段name中有以下数据[zhangsan,lisi,wanger,zhaowu,laoliu,jack]
若没有对name添加索引我们在寻找where namejack时
则数据库系统会扫描所有的name记录全盘扫描对每个值进行比对效率很低。若有表内有索引则会根据索引进行查找。注意
#跟新华字典的目录一样mysql中的索引也是需要排序的且mysql中的索引跟TreeSet数据结构相同是一个自平衡的二叉树。
mysql中的索引是一个B-Tree数据结构 Binary Tree 二叉树7.2 索引的实现原理
在任何数据库中主键都会自动添加索引对象一个字段上若有unique也会自动创建索引对象。在任何数据库当中任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理地址。#MySQL中索引是一个单独的对象不同的存储引擎以不同的形式存在在MyISAM存储引擎中索引存储在一个.MYI 文件中。在InnoDB存储引擎中索引存储在一个逻辑名叫做 tablespace 的表空间当中。在MEMORY存储引擎中索引被存储在内存中。课程里面讲的是关于二分查找的算法。二分查找的时间复杂度是logn
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7znmWmnW-1690357396090)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220801170731613.png)]
经过二分查找之后取出其中的物理地址然后访问磁盘获得数据。
参考文章为什么使用B数而不使用B-树
https://blog.csdn.net/weixin_53601359/article/details/1155584277.3 怎么判断是否创建索引
1. 数据量庞大
2. 某个字段经常出现在where子句后面经常被作为条件使用
3. 该字段有很少的DMLinsert/delete/update操作因为在DML语句之后索引会被重新排序
4. 多表查询中与其它表进行on关联的字段外键关系单列索引/复合索引的选择?
高并发下倾向于创建复合索引
查询中经常用来排序的字段
查询中经常用来统计或者分组字段不需要创建索引的情况
1. 表记录太少
2. where条件查询中用不到的字段
3. 经常增删改的表: 更新了表索引也得更新才行
4. 如果一张表中重复的记录非常多为它建立索引就没有太大意义建议不要随意添加索引因为索引需要维护 索引太多反而会降低系统的性能
解决方案通过主键或者被unique约束的字段进行查询。
7.4 索引的创建与删除
create index emp_ename on emp(ename);
create index 索引名称 on 表名(需要添加索引的字段名);DROP INDEX 索引名 ON 表名#补充
#DROP PRIMARY KEY表示删除表中的主键。一个表只有一个主键主键也是一个索引。
#DROP INDEX index_name表示删除名称为 index_name 的索引。
#DROP FOREIGN KEY fk_symbol表示删除外键。7.5 查看是否有索引
explain select * from emp where ename king;#结果为
mysql explain select * from emp where ename king;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.01 sec)[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gO6qImXj-1690357396095)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220801173834570.png)]
typeALL 全表扫描没有优化最慢的方式index 索引全扫描range 索引范围扫描常用语between等操作ref 使用非唯一索引扫描或唯一索引前缀扫描返回单条记录常出现在关联查询中eq_ref 类似ref区别在于使用的是唯一索引使用主键的关联查询const 当查询是对主键或者唯一键进行精确查询系统会把匹配行中的其他列作为常数处理null MySQL不访问任何表或索引直接返回结果System 表只有一条记录(实际中基本不存在这个情况)https://blog.csdn.net/weixin_53601359/article/details/115558427性能排行 System const eq_ref ref range index ALL 扫描了14条记录说明没有使用索引。type all
mysql create index emp_ename on emp(ename);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0然后再查看king记录的状态
type ref
mysql explain select * from emp where ename king;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | emp | NULL | ref | emp_ename | emp_ename | 43 | const | 1 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3evtW6LV-1690357396100)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220801175857972.png)]
可以看到前后状态的不同。
了解B树 与 B-树
https://blog.csdn.net/jiang_wang01/article/details/1137392307.6 索引的失效重要
‘%’ 开头
select * from emp where ename like %T;mysql explain select * from emp where ename like %T;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 11.11 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.01 sec)[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tEFPH3e6-1690357396105)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220801183939608.png)]
ename上即使添加了索引也不会走索引为什么
因为模糊匹配使用了%开头系统不知道你所匹配的这个值是多少所以进行了全文比对同理如果用下划线_开头也是。 尽量避免模糊查询时以‘%’开头是一种优化策略。 or
使用or连接条件时要么or两边的字段都要有索引才会根据索引进行查询。
否则若or两边任意一边的字段没有索引那么索引也会失效。 少使用or 也属于优化策略的一部分 使用union见前一部分连接表结果 使用复合索引时没有使用左侧的列进行查找索引失效
什么是复合索引
create index emp_job_sal_index on emp(job,sal);explain select * from emp where job manager;
#结果
mysql create index emp_job_sal_index on emp(job,sal);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql explain select * from emp where job manager;
-----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | emp | NULL | ref | emp_job_sal_index | emp_job_sal_index | 39 | const | 3 | 100.00 | NULL |
-----------------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CAcNwCem-1690357396110)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220801184723993.png)]
若查询时使用右边的字段
explain select * from emp where sal 3000;
#结果
mysql explain select * from emp where sal 3000;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8Fcvvnig-1690357396114)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220801184836095.png)]
where中索引列参加了运算
create index emp_sal_index on emp(sal);
explain select * from emp where sal 800;
#结果
mysql explain select * from emp where sal 800;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | emp | NULL | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | 100.00 | NULL |
---------------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)#typeref说明使用了索引#执行以下语句
explain select * from emp where sal1 800;
mysql explain select * from emp where sal1 800;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.01 sec)#type变成了all 进行了全盘扫描
#如果索引未失效会显示typeref即便是没有找到任何数据[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P06m48vi-1690357396119)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220801185645944.png)]
where中使用了函数
explain select * from emp where lower(ename) smith;
#结果
mysql explain select * from emp where lower(ename) smith;
-----------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 100.00 | Using where |
-----------------------------------------------------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-iDMOtmDx-1690357396123)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220801185928380.png)]
不止以上5中索引失效的情况还有其他的索引失效的情况
其它情况
https://blog.csdn.net/sy_white/article/details/122112440索引优化是数据库优化的重要手段优化的时候需要优先考虑的因素就是索引。
注意唯一性较弱的字段上添加索引用处不大。
8 view视图
8.1 视图是什么
视图就是一张虚拟的表
视图是一个虚拟的表是一个表中的数据经过某种筛选后的显示方式视图由一个预定义的查询select语句组成。
在 SQL 中视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句我们也可以提交数据就像这些来自于某个单一的表。
注释数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
https://blog.csdn.net/zhongkeyuanchongqing/article/details/1179644508.2 视图的创建和删除
create view 视图名 as (一个select语句);create table dept2 as select * from dept;#复制一个dept表
#创建视图
create view dept2_view as select * from dept2;
#删除视图
drop view dept2_view;8.3 视图的使用
对视图进行插入
#面向视图对象进行增删改查会导致原表的数据被操作
#查看视图内容
select * from dept2_view;
#结果
mysql select * from dept2_view;
------------------------------
| DEPTNO | DNAME | LOC |
------------------------------
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
------------------------------
4 rows in set (0.00 sec)
#有4条记录#往视图里面插入数据
insert into dept2_view(deptno,dname,loc) values (60,SALES,BeiJing);#然后查询dept2_view视图中的数据
mysql select * from dept2_view;
------------------------------
| DEPTNO | DNAME | LOC |
------------------------------
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 60 | SALES | BeiJing |
------------------------------
5 rows in set (0.00 sec)#视图中插入了一条数据此时我们再查询dept2表会发现
mysql select * from dept2;
------------------------------
| DEPTNO | DNAME | LOC |
------------------------------
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 60 | SALES | BeiJing |
------------------------------
5 rows in set (0.00 sec)#dept2表中也被插入了一条数据对视图里的数据进行删除
#开启一个事务方便等下回滚
start transaction;delete from dept2_view;
#然后查询dept2_view视图
mysql select * from dept2_view;
Empty set (0.00 sec)
#视图中的数据被删除了#再查询dept2表中的数据
mysql select * from dept2;
Empty set (0.00 sec)
#dept2表中的数据也被删除了#此时进行rollback 或者 commit多表联合视图
#建立多表查询
create view emp_dept_view as
selecte.ename,e.sal,d.dname
fromemp e
joindept d
one.deptnod.deptno;start transaction;
#开启事务#查询视图
mysql select * from emp_dept_view;
-----------------------------
| ename | sal | dname |
-----------------------------
| SMITH | 800.00 | RESEARCH |
| ALLEN | 1600.00 | SALES |
| WARD | 1250.00 | SALES |
| JONES | 2975.00 | RESEARCH |
| MARTIN | 1250.00 | SALES |
| BLAKE | 2850.00 | SALES |
| CLARK | 2450.00 | ACCOUNTING |
| SCOTT | 3000.00 | RESEARCH |
| KING | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES |
| ADAMS | 1100.00 | RESEARCH |
| JAMES | 950.00 | SALES |
| FORD | 3000.00 | RESEARCH |
| MILLER | 1300.00 | ACCOUNTING |
-----------------------------
14 rows in set (0.00 sec)#更新视图
update emp_dept_view set sal 1000 where dname accounting;
#执行结果
mysql update emp_dept_view set sal 1000 where dname accounting;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0#然后我们查询原表
mysql select * from emp;
----------------------------------------------------------------------
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
----------------------------------------------------------------------
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 1000.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 1000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1000.00 | NULL | 10 |
----------------------------------------------------------------------
14 rows in set (0.00 sec)mysql select * from dept;
------------------------------
| DEPTNO | DNAME | LOC |
------------------------------
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
------------------------------
4 rows in set (0.00 sec)#可以看到 原表部门号为10的员工工资全都改为了1000
#此时进行 rollback 或者 commit;视图是以文件的形式存储在硬盘上不会消失。
9 DBA常用命令
9.1 数据的导入和导出
导出
新建用户
create user 用户名 identified by 密码 ;#导出
在dos窗口中输入 mysqldump 数据看名称存放的路径\存放的文件名称.sql -u 用户名 -p 密码;
mysql exit
Bye #退出mysql
#导出sqlstudy数据库
#这里我导出的位置和文件名为
##C:\Users\MacieSerenity\Desktop\improveMySelf\study-me\SQLScriptFile\recover\recover.sql##C:\Users\MacieSerenity mysqldump sqlstudy C:\Users\MacieSerenity\Desktop\improveMySelf\study-me\SQLScriptFile\recover\recover.sql -u root -p
Enter password: ************#导出某个表例如ABC
C:\Users\MacieSerenity mysqldump sqlstudy ABC C:\Users\MacieSerenity\Desktop\improveMySelf\study-me\SQLScriptFile\recover\recover.sql -u root -p
Enter password: ************[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DF2jG17U-1690357396128)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802091437976.png)]
导入
1.登录数据库 mysql -u root -p 密码
2.创建数据库 create database 数据库名
3.使用数据库 use 数据库名称
4.初始化数据库source C:\Users\MacieSerenity\Desktop\improveMySelf\study-me\SQLScriptFile\recover\recover.sql授权等操作
https://blog.csdn.net/a8039974/article/details/8498816110 三大范式重要
10.1 什么是三大范式
数据库表的设计依据可以根据设计范式对数据库的表进行设计。
第一范式要求每张表必须有主键且每个字段属性不可再分
第二范式要求所有非主键字段完全依赖主键不要产生部分依赖
第三范式要求所有非主键字段直接依赖主键不要产生传递依赖
只有满足了第一范式才能进行第二范式只有满足第二范式才能使用第三范式
也就是说第二范式建立在第一范式的基础之上第三范式建立在第二范式的基础之上。10.2 三大范式
第一范式
最核心最重要的一个范式所有表的设计都需要满足
必须要有主键且属性不可再分
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jbY9I4FE-1690357396133)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802093404701.png)]
第二范式
建立在第一范式的基础之上要求所有非主键字段必须完全依赖主键不要产生部分依赖。
个人理解主要是看主键是不是复合主键尽量拆开复合主键
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-teU8sU07-1690357396137)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802093656110.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ASc0Jy31-1690357396142)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802093728564.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-k2HWCA9l-1690357396147)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802093923907.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PMQljR7I-1690357396152)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802094208504.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-emJJYGBI-1690357396156)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802094227294.png)]
第三范式
建立在第二范式的基础之上要求所有非主键字段直接依赖主键不要产生传递依赖
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SQ9h2tIr-1690357396161)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802094902900.png)]
满足第三范式吗
不满足因为班级名称依赖班级编号但是班级编号依赖学生编号产生了传递依赖。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6rWFMViB-1690357396165)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802095158404.png)]
10.3 表设计的总结
一对多
一对多两张表多的表加外键
多对多
多对多三张表关系表两外键
一对一
一对一可拆分外键唯一
实际开发中一对一关系的数据量比较庞大尽量拆分为多个表
例如
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WBSr3MhV-1690357396170)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802095731541.png)]
然后在第二表红添加一个字段并加上外键约束
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JvL6FRIP-1690357396174)(MySQL-%E7%AC%AC%E4%BA%8C%E9%83%A8%E5%88%86.assets/image-20220802095838838.png)]
数据库设计三范式是理论上的。
实践和理论是有偏差的。
最终的目的都是满足客户的需求有时候会拿冗余的数据换取执行效率速度。
表之间的连接次数越多效率越低。
有时候会产生冗余但是为了减少表之间的连接次数且减少SQL的编写难度也是一种合理的优化。 文章转载自： http://www.morning.pdtjj.cn.gov.cn.pdtjj.cn http://www.morning.tkchg.cn.gov.cn.tkchg.cn http://www.morning.dytqf.cn.gov.cn.dytqf.cn http://www.morning.kwqqs.cn.gov.cn.kwqqs.cn http://www.morning.xbbrh.cn.gov.cn.xbbrh.cn http://www.morning.rtlth.cn.gov.cn.rtlth.cn http://www.morning.feites.com.gov.cn.feites.com http://www.morning.ymjgx.cn.gov.cn.ymjgx.cn http://www.morning.bpmdn.cn.gov.cn.bpmdn.cn http://www.morning.kdfqx.cn.gov.cn.kdfqx.cn http://www.morning.bmrqz.cn.gov.cn.bmrqz.cn http://www.morning.jhrqn.cn.gov.cn.jhrqn.cn http://www.morning.frsxt.cn.gov.cn.frsxt.cn http://www.morning.nzkkh.cn.gov.cn.nzkkh.cn http://www.morning.shuanga.com.cn.gov.cn.shuanga.com.cn http://www.morning.xqjrg.cn.gov.cn.xqjrg.cn http://www.morning.wanjia-sd.com.gov.cn.wanjia-sd.com http://www.morning.jgzmr.cn.gov.cn.jgzmr.cn http://www.morning.xfyjn.cn.gov.cn.xfyjn.cn http://www.morning.xxhc.cn.gov.cn.xxhc.cn http://www.morning.nkbfc.cn.gov.cn.nkbfc.cn http://www.morning.pphgl.cn.gov.cn.pphgl.cn http://www.morning.drndl.cn.gov.cn.drndl.cn http://www.morning.sqmbb.cn.gov.cn.sqmbb.cn http://www.morning.bhqlj.cn.gov.cn.bhqlj.cn http://www.morning.hlppp.cn.gov.cn.hlppp.cn http://www.morning.bbjw.cn.gov.cn.bbjw.cn http://www.morning.mflhr.cn.gov.cn.mflhr.cn http://www.morning.kryr.cn.gov.cn.kryr.cn http://www.morning.mjats.com.gov.cn.mjats.com http://www.morning.wzdjl.cn.gov.cn.wzdjl.cn http://www.morning.qydgk.cn.gov.cn.qydgk.cn http://www.morning.dgckn.cn.gov.cn.dgckn.cn http://www.morning.lznfl.cn.gov.cn.lznfl.cn http://www.morning.kjyfq.cn.gov.cn.kjyfq.cn http://www.morning.chtnr.cn.gov.cn.chtnr.cn http://www.morning.rckdq.cn.gov.cn.rckdq.cn http://www.morning.qsy40.cn.gov.cn.qsy40.cn http://www.morning.807yy.cn.gov.cn.807yy.cn http://www.morning.zmwzg.cn.gov.cn.zmwzg.cn http://www.morning.wwkdh.cn.gov.cn.wwkdh.cn http://www.morning.qtkfp.cn.gov.cn.qtkfp.cn http://www.morning.pgzgy.cn.gov.cn.pgzgy.cn http://www.morning.snnkt.cn.gov.cn.snnkt.cn http://www.morning.dhyzr.cn.gov.cn.dhyzr.cn http://www.morning.qhmhz.cn.gov.cn.qhmhz.cn http://www.morning.dqgbx.cn.gov.cn.dqgbx.cn http://www.morning.xrhst.cn.gov.cn.xrhst.cn http://www.morning.jqpq.cn.gov.cn.jqpq.cn http://www.morning.xjqrn.cn.gov.cn.xjqrn.cn http://www.morning.fwlch.cn.gov.cn.fwlch.cn http://www.morning.rykmf.cn.gov.cn.rykmf.cn http://www.morning.gygfx.cn.gov.cn.gygfx.cn http://www.morning.fcqlt.cn.gov.cn.fcqlt.cn http://www.morning.fglxh.cn.gov.cn.fglxh.cn http://www.morning.xqwq.cn.gov.cn.xqwq.cn http://www.morning.zzgkk.cn.gov.cn.zzgkk.cn http://www.morning.fkgqn.cn.gov.cn.fkgqn.cn http://www.morning.gblrn.cn.gov.cn.gblrn.cn http://www.morning.mfjfh.cn.gov.cn.mfjfh.cn http://www.morning.wmqrn.cn.gov.cn.wmqrn.cn http://www.morning.rnzbr.cn.gov.cn.rnzbr.cn http://www.morning.pqnkg.cn.gov.cn.pqnkg.cn http://www.morning.bpkqd.cn.gov.cn.bpkqd.cn http://www.morning.wmcng.cn.gov.cn.wmcng.cn http://www.morning.fbjnr.cn.gov.cn.fbjnr.cn http://www.morning.bkxnp.cn.gov.cn.bkxnp.cn http://www.morning.mcmpq.cn.gov.cn.mcmpq.cn http://www.morning.zffn.cn.gov.cn.zffn.cn http://www.morning.wnxqf.cn.gov.cn.wnxqf.cn http://www.morning.txqsm.cn.gov.cn.txqsm.cn http://www.morning.ghlyy.cn.gov.cn.ghlyy.cn http://www.morning.qwdlj.cn.gov.cn.qwdlj.cn http://www.morning.rmfwh.cn.gov.cn.rmfwh.cn http://www.morning.hkpyp.cn.gov.cn.hkpyp.cn http://www.morning.wqcbr.cn.gov.cn.wqcbr.cn http://www.morning.msxhb.cn.gov.cn.msxhb.cn http://www.morning.mbnhr.cn.gov.cn.mbnhr.cn http://www.morning.pgxjl.cn.gov.cn.pgxjl.cn http://www.morning.znnsk.cn.gov.cn.znnsk.cn
