MySQL学习-第二部分-CSDN博客

本文链接：https://blog.csdn.net/weixin_44673253/article/details/131940831

文章目录

MySQL数据库学习
1 表
2 insert 数据插入（DML）
3 delete 删除数据
4 update 更新数据
5 存储引擎
6 transaction事务（非常重要）
7 index索引
8 view视图
9 DBA常用命令
- 9.1 数据的导入和导出
10 三大范式（重要）

MySQL数据库学习

1 表

对于表的操作属于DDL，DDL为Data Definition Language 数据定义语言

DDL 数据定义语言Data Definition Language
	create 创建
	drop 删除（表、数据库、约束、触发程序等）
	alter 修改
	Truncate语句：清空表里的数据。

1.1 表中的数据类型

以下是一些常见的数据类型

标识符	特点
varchar	可变长度字符串，节省空间，根据实际的数据长度动态分配空间优点：节省空间缺点：需要动态分配空间，速度慢 variable:可变的 varchar最大长度为255
char	定长字符串，不管数据长度为多少，分配固定的空间优点：不需要动态分配空间，速度快缺点：使用不恰当的话会导致空间的浪费 char最大长度为255
int	整型数字，等同于java中的int int最大长度为11
bigint	长整型，等同于java中的long
float	单精度浮点数
double	双精度浮点数
date	短日期类型
datetime	厂日期类型
clob	字符大对象最多可以存储4gb的字符串，例如一篇文章等。超过255个字符的数据，通常使用clob Character Large Obejct :CLOB
blo	二进制大对象 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' doesn't have a default value

#修改非空约束
alter table orders 
alter column customer_number varchar(20) not null

alter table orders 
alter column order_number varchar(20) not null

alter table orders 
alter column order_line int not null

1.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','123@123.com');
insert into t_vip(id,name,email) values(2,null,'123@234.com');
insert into t_vip(id,name,email) values(2,null,'123@234.com');
#name字段虽然被unique约束了，但是可以多个为null

insert into t_vip(id,name,email) values(3,'zhangsan','123@123.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','123@234.com');
insert into t_vip(id,name,email) values(5,'null1','123@234.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_od

1.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.html

2 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,'abc@qq.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 | abc@qq.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,'abc@qq.com');
#省略后：
insert into t_student values(001,'张三','男',22,'abc@qq.com');
#结果
mysql> select * from t_student;
+--------+----------+---------+---------+------------+
| stu_no | stu_name | stu_sex | stu_age | stu_email  |
+--------+----------+---------+---------+------------+
|      1 | NULL     | 男      |    NULL | NULL       |
|      1 | 张三     | 男      |      22 | abc@qq.com |
+--------+----------+---------+---------+------------+
2 rows in set (0.00 sec)
#注意：
#省略了字段名后，相当于填写了所有的字段，所以值内必须匹配表内所有的字段值，才能插入成功

2.2 insert into 插入日期（与前一部分相同）

2.2.1 str_to_date(必须严格按照标准输出)

把字符串转为日期

 select * from emp where hiredate=str_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 SSS

2.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-%d'

3 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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

#可以看到上面有一个ENGINE=InnoDB
#可以在建立表的时候可以在小括号后面指定存储引擎和字符编码方式

#mysql默认的存储引擎是InnoDB
#mysql默认的字符编码方式是utf8

MySQL在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: MEMORY
     Support: YES #支持
     Comment: Hash based, stored in memory, useful for temporary tables
     #基于哈希的，存储在内存中，对临时表有用
Transactions: NO #事务处理
          XA: NO #分布式事务协议
  Savepoints: NO #保存点
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
     #相同MyISAM表的集合
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
     #CSV存储引擎
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: FEDERATED  
     Support: NO #不支持
     Comment: Federated MySQL storage engine
     #联合MySQL存储引擎
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 5. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
     #性能模式
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
     #MyISAM存储引擎
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
     #支持事务、行级锁定和外键
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 8. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
     #/开发/空存储引擎（您向其写入的任何内容都会消失）
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
     #存档存储引擎
Transactions: NO
          XA: NO
  Savepoints: 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账户里的钱应该减去100；B账户里的前应该增加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	事务B
use 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	事务B
use 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	事务B
use 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	食物B
use 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 name='jack'时
则数据库系统会扫描所有的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/115558427

7.3 怎么判断是否创建索引？

1. 数据量庞大
2. 某个字段经常出现在where子句后面，经常被作为条件使用
3. 该字段有很少的DML（insert/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)]

type：
    ALL 全表扫描，没有优化，最慢的方式
    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/113739230

7.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: 0

mysql> 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)

#type=ref，说明使用了索引

#执行以下语句：
explain select * from emp where sal+1 = 800;
mysql> explain select * from emp where sal+1 = 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  进行了全盘扫描
#（如果索引未失效会显示type=ref，即便是没有找到任何数据）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(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/117964450

8.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
select
	e.ename,e.sal,d.dname
from
	emp e
join
	dept d
on
	e.deptno=d.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/84988161

10 三大范式（重要）

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的编写难度，也是一种合理的优化。