MySql索引事物联查

1.索引

1.1 概念
索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现

1.2 作用
可用于快速定位，检索数据

1.3 使用场景
要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

1.4 使用
创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

• 查看索引

show index from 表名;

• 创建索引
  对于非主键、非唯一约束、非外键的字段，可以创建普通索引

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

• 删除索引

drop index 索引名 on 表名;

事物

2.1使用事物的原因
例如转账,

update accout set money=money-2000 where name = 'a';
update accout set money=money+2000 where name = 'b';

如果在执行第一句时出现网络错误或者数据库出现什么问题,a的钱会减少,但b的钱不会增加
用事物来解决,保证以上两句要么都成功,要么都失败
2.2事物的概念
事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。
在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。
2.3 使用
（1）开启事务：start transaction;
（2）执行多条SQL语句
（3）回滚或提交：rollback/commit;

start transaction;
update accout set money=money-2000 where name = 'a';
update accout set money=money+2000 where name = 'b';
commit;

事务的隔离级别	特点
Read Uncommitted	最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。
Read Committed	只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题
Repeated Read	在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读。
Serialization	事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题通常，在工程实践中，为了性能的考虑会对隔离性进行折中。

隔离性为了解决的问题主要有三个：

    1、 脏读(Drity Read)：事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。

Read Committed可以解决脏读问题，但仍存在以下两种问题。

     2、不可重复读(Non-repeatable read) : 在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这导致锁竞争加剧，影响性能。

Repeated Read可以解决不可重复读问题和脏读问题，但仍无法解决下面的问题。

     3、幻读(Phantom Read) : 在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读仅指由于并发事务增加记录导致的问题，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。

Serialization解决了以上所有问题，但是性能效率较低。
通常来说，事务隔离级别越低，所需持有锁的时间也就越短，并发性能也就越好。

联查

3.1内连接
基本语法

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件

3.2 外连接
外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
基本语法

-- 左外连接，表1完全显示
select 字段名  from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

3.3自连接
是指在同一张表连接自身进行查询

3.4 子查询
是指嵌入在其他sql语句中的select语句,也叫嵌套查询

• 单行子查询:返回一行记录的子查询

select * from stu where classes_id=(select classes_id from stu where name = 'a');

• 多行子查询
  返回多行记录的子查询

1.[NOT] IN关键字

select * from score where course_id in (select id from course where name='语文' or name='英文');

-- 使用 NOT IN
select * from score where course_id not in (select id from course where
name!='语文' and name!='英文');

2.[NOT] EXISTS关键字

-- 使用 EXISTS
select * from score sco where exists (select sco.id from course cou 
where (name='语文' or name='英文') and cou.id = sco.course_id);
-- 使用 NOT EXISTS
select * from score sco where not exists (select sco.id from course cou 
where (name!='语文' and name!='英文') and cou.id = sco.course_id);

• 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用

-- 获取“中文系2019级3班”的平均分，将其看作临时表
SELECT
 avg( sco.score ) score 
FROM
 score sco
 JOIN student stu ON sco.student_id = stu.id
 JOIN classes cls ON stu.classes_id = cls.id
WHERE
 cls.NAME = '中文系2019级3班';

查询成绩表中，比以上临时表平均分高的成绩：

SELECT
 * 
FROM
 score sco,
 (
 SELECT
 avg( sco.score ) score 
 FROM
 score sco
 JOIN student stu ON sco.student_id = stu.id
 JOIN classes cls ON stu.classes_id = cls.id
 WHERE cls.NAME = '中文系2019级3班'
 ) tmp 
WHERE
 sco.score > tmp.score;

3.5合并查询
在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

• union
  该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

select * from course where id<3
union
select * from course where name='英文';
-- 或者使用or来实现
select * from course where id<3 or name = '英文';

• union all
  该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

select * from course where id<3
union all
select * from course where name='英文';

Tip:
SQL查询中各个关键字的执行先后顺序： from > on> join > where > group by > with > having >select > distinct > order by > limit