【MySQL】表的内连外连和索引、事务及视图

我不是女神ヾ 2022-05-22 06:53 194阅读 0赞

### 表的内连和外连 ###

##### 内连接(inner join on) #####

内连接就是多表查询中where子句对笛卡尔积的筛选，我们前面学习的都是内连接。  
**语法：**`select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;`  
例如：显示SMITH的名字和部门名称  
![这里写图片描述][70]

##### 外连接 #####

**左外连接**：联合查询中，左侧的表完全显示，就是左外连接。  
**语法：**`select 字段 from 表名1 left join 表名2 on 连接条件;`  
例如：首先创建两张表，学生表和成绩表  
　　![这里写图片描述][70 1]  
　　查询所有学生的成绩，即使这个学生没有成绩，也要将学生的个人信息显示出来  
　　　![这里写图片描述][70 2]  
**右外连接**：与左外连接相反，右侧的表完全显示  
**语法：**`select 字段 from 表名1 right join 表名2 on 连接条件;`  
例如：将所有的成绩都显示出来，即使这个成绩没有学生与之对应，也需要显示出来  
　　　![这里写图片描述][70 3]

--------------------

### 索引 ###

当数据很多的时候，我们查找的时候，会消耗很多的时间，尤其是在实际项目中，同时又很多人并发查询，还有可能导致死机，所以，我们需要创建索引。  
常见的索引分为**主键索引，唯一索引，普通索引和全文索引**。

##### 索引的基本原理 #####

1.  进行整表扫描，添加索引后，会形成一颗二叉树，从而，降低查找次数；
2.  索引会占用磁盘空间；
3.  添加一条记录，除了添加到表中，还要维护二叉树，因此，我们提高查询速度是以插入、更新、删除的速度为代价的；
4.  当添加一个索引后，不能解决所有的查询问题，需要分别给字段建立索引；
5.  索引是以空间换取时间。

##### 创建索引 #####

**主键索引：**即将某一列设置为主键；  
**唯一索引：**即将某一列设置为唯一键；  
**普通索引：**  
第一种，在创建表时，在表的最后一句加上index：

create table stu(
        id int,
        name varchar(20),
        index(name)
        );

第二种：创建完表以后指定某列为普通索引：

alter table 表名 add index(字段);

**全文索引：**  
当文章字段或有大量文字的字段进行检索时，会用到全文索引。  
![这里写图片描述][70 4]  
例如，在上表中，查找带有database关键字的记录  
使用普通方法查询：  
![这里写图片描述][70 5]  
使用索引查询：  
![这里写图片描述][70 6]

##### 查询索引 #####

1. show keys from 表名;
    2. show index from 表名;
    3. desc 表名;

##### 删除索引 #####

1. drop index 索引名 on 表名;
    2. alter table 表名 drop index 索引名;

##### 创建索引的基本原则 #####

1.  比较频繁查询的字段应该创建索引来提高查找效率；
2.  唯一性太差的字段不适合单独创建索引；
3.  更新非常频繁的字段不适合创建索引；
4.  字段根本不会出现在where子句中，该字段不应该创建索引。

--------------------

### 事务 ###

##### 事务的基本概念 #####

事务就是一组dml语句组成，这一组语句要么全部成功，要么全部失败，是一个整体。  
开始一个事务：`start transaction;`  
做一个保存点：`savepoint aaa;`  
进行各种操作

insert into account values(1,'test',100.01);
    select * from account;

如果有需要，可以回到保存点：`rollback to aaa;`  
提交事务：`commit;`  
注意：  
1. 如果没有设置保存点，也可以回滚，这是回滚到事务的开始，直接使用rollback  
2. 如果一个事务被提交了，则不可以回退；  
3. InnoDB支持事务，MyISAM不支持事务；

##### 事务的隔离级别 #####

1.  脏读:在一个事物1还未提交时，另一个事务2能看到事务1对数据库的更改
2.  不可重复读:在一个事务1提交时对数据库做的修改或者删除操作，未提交的事务2能看到
3.  幻读：在一个事务1提交时对数据库做的增加操作，未提交的事务2能看到

事务的隔离级别有：读而未交(read uncommitted)， 读已提交(read committed)， 可重复读(repeatable read)， 可串行化(serializable)  
设置隔离级别：`set session transaction isolation level read uncommitted;`  
查看当前事务的隔离级别：`select @@tx_isolation;`

##### 事务的ACID特性 #####

1.  原子性A：事务是一个不可分割的工作单位，一个事物中的操作要么圈发生，要么都不发生
2.  一致性C：事务必须使数据库从一个一致性状态变到另一个一致性状态
3.  隔离性I：多个用户并发访问数据库时，数据库为每一个用户开启的事物，不能被其他事物的操作数据所干扰。  
    多个事物间互相隔离。
4.  持久性D：事务一旦提交，事务对数据库中数据的修改是永久性的，不可以回滚

--------------------

### 视图 ###

视图是一个虚表，其内容由查询定义，视图的数据变化会影响到基表，基表的数据变化也会影响到视图。  
创建视图：

create view 视图名
         as
         select 语句

删除视图：`drop view 视图名;`

**视图和真实数据表的区别：**

1.  表占用磁盘空间的，是系统一个具体的文件，视图不需要占用磁盘空间
2.  视图不能添加索引；
3.  使用视图可以简化查询（多表查询），提高安全性

[70]: /images/20220522/8733188a6b354782997b38a33401ebcb.png
[70 1]: /images/20220522/69f7ab77ae0b4c31845f4184d4fbe795.png
[70 2]: /images/20220522/8a75418cf1a641e5a7ee0a84833d71ed.png
[70 3]: /images/20220522/b7336d87a3374f51860f7c4cb85dae4a.png
[70 4]: /images/20220522/6e20fc148f6046c7870dcda74145a544.png
[70 5]: /images/20220522/53cc649ecaa745ee82af9fe61cac088f.png
[70 6]: /images/20220522/0ec07c64519d4d84bf0b3f0d2e253964.png