MySql02-CRUD|索引事务

一：CRUD2.0

1.1：插入

--插入操作  将查询返回的数据插入到另外一张表
insert into table_name select from table_name 2 where ... order bu ... limit;
	--复制表场景:提前准备一些统计的数据(统计的sql一般关联很多表，条件很复杂，执行效率不高)，在凌晨不忙时，定时运行任务，将这些数据存入新表中

	--新表
CREATE TABLE new_score (
	id INT PRIMARY KEY AUTO_INCREMENT,
	student_id INT,
	student_name VARCHAR(20),
	course_id INT,
	course_name VARCHAR(20),
	score DECIMAL
);
  -- 将查询结果返回的数据插入到新表中
insert into new_score (student_id,student_name,course_id,course_name,score)  
	select 
	student_id,student_name,course_id,course_name,score
	from exam_score;

1.2：查询

1.2.1：聚合查询

1.2.1.1：聚合函数

函数	说明
COUNT	返回查询到的数据的 数量
SUM	返回查询到的数据的 总和，不是数字没有意义
AVG	返回查询到的数据的 平均值，不是数字没有意义
MAX	返回查询到的数据的 最大值，不是数字没有意义
MIN	返回查询到的数据的 最小值，不是数字没有意义

1.2.1.2：GROUP BY

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

可以多字段聚合。

select column1, sum(column2), .. from table group by column1,column3;

SELECT SUM(score) FROM exam_score GROUP BY student_id; 
SELECT SUM(score) FROM exam_score GROUP BY student_id,course_id; 
SELECT role,MAX(salary),MIN(salary),AVG(salary) FROM emp GROUP BY role;

1.2.1.3：HAVING

GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING。

SELECT SUM(score) FROM exam_score GROUP BY student_id having sum(score)>66; 
SELECT SUM(score) as 总分 FROM exam_score GROUP BY student_id,course_id having 总分>66; 
SELECT SUM(score) FROM exam_score GROUP BY student_id HAVING SUM(score)<100  ORDER BY  SUM(score) ASC LIMIT 1,1;
--查询重复数据
SELECT 	`name` , class_id ,COUNT(0) FROM student GROUP BY `name`,class_id HAVING COUNT(0)>1;

1.2.2：联合查询

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积，注意关联查询可以对关联表使用别名。

SELECT * FROM student , course;
SELECT student.* , course.* FROM student , course;

1.2.2.1：内连接

--内连接
select 字段 from 表1 as 别名  [inner] join 表2 as 别名 on 连接条件 and 其他条件
select 字段 from 表1 as 别名  [inner] ， 表2 as 别名 where 连接条件 and 其他条件
SELECT DISTINCT stu.`name` , cla.`name` FROM student AS stu,classes  AS cla WHERE stu.`class_id`=cla.`id`;
SELECT cla.`name` , COUNT(DISTINCT stu.`name`) AS 人数 FROM student AS stu,classes  AS cla WHERE stu.`class_id`=cla.`id` GROUP BY cla.`name`;

--各个班级语文成绩平均分
SELECT  c.`name` AS 班级名称 , cou.`name` AS 课程 , AVG(e.`score`) AS 平均成绩
FROM student AS s , classes AS c , exam_score AS e , course  AS cou
WHERE s.`class_id`=c.`id`
AND s.`id`=e.`student_id`
AND e.`course_id`=cou.id
AND e.`course_id`=1
GROUP BY c.`name`;
--各个班级的各个课程的平均分
SELECT  c.`name` AS 班级名称 , cou.`name` AS 课程 , AVG(e.`score`) AS 平均成绩
FROM student AS s , classes AS c , exam_score AS e , course  AS cou
WHERE s.`class_id`=c.`id`
AND s.`id`=e.`student_id`
AND e.`course_id`=cou.`id`
GROUP BY c.`name`, cou.`name`;

SELECT  c.`name` AS 班级名称 , cou.`name` AS 课程 , AVG(e.`score`) AS 平均成绩
FROM exam_score AS e 
JOIN student AS s ON  s.`id`=e.`student_id`
JOIN classes AS c ON  s.`class_id`=c.`id`
JOIN course  AS cou ON e.`course_id`=cou.`id`
GROUP BY c.`name`, cou.`name`;

1.2.2.2：外连接

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

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

如果某张表存在空值却需要显示时，需要通过left/right显示空值。left/right表示存在空值的表的位置。

--内外连接区别 关联学生和班级
INSERT INTO classes (NAME) VALUES ('计算机三班');
  --内连接
   SELECT  s.`id`, s.`name` , c.`id`, c.`name` 
   FROM student AS s
   JOIN classes AS c ON s.`class_id`=c.`id`;
   --无学生班级不显示
   SELECT c.`name` AS 班级名称 , COUNT(s.`id`) AS 班级人数
   FROM student AS s
   JOIN classes AS c ON s.`class_id`=c.`id`
   GROUP BY c.`id`;
  --外连接
   SELECT c.`name` AS 班级名称 , COUNT(s.`id`) AS 班级人数
   FROM classes AS c
   LEFT JOIN student AS s
   ON s.`class_id`=c.`id`
   GROUP BY c.`id`;

   SELECT s.`name` , c.`name`
   FROM student AS s
   RIGHT JOIN classes AS c
   ON s.`class_id`=c.`id`;

内连接：必须满足连接条件和其它条件才能返回。

外连接：满足连接条件和其它条件，或者满足其它条件但是外表存在，也可以返回。

1.2.2.3：自连接

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

  --查询语文成绩比英语成绩高的学生
SELECT  e1.`student_id` AS 学号 , e1.`course_id` AS 课程 , e1.`score` AS 成绩
FROM exam_score AS e1 , exam_score AS e2 
WHERE e1.`student_id`=e2.`student_id`
AND e1.`course_id`=1
AND e2.`course_id`=2
AND e1.`score`> e2.`score`;


SELECT  e1.`student_id` AS 学号 , e1.`course_id` AS 课程 , e1.`score` AS 成绩
FROM exam_score AS e1
JOIN exam_score AS e2 
ON e1.`student_id`=e2.`student_id`
AND e1.`course_id`=1
AND e2.`course_id`=2
AND e1.`score`> e2.`score`;

SELECT s.`id` AS 学号 , s.`name`  AS 姓名 , cla.`name` AS 班级 , e1.`score` AS 语文 ,e2.`score` AS 英语 
FROM exam_score AS e1 
JOIN exam_score AS e2 
ON e2.`student_id`=e1.`student_id`
JOIN student AS s 
ON e1.`student_id`=s.`id`
JOIN course AS c1
ON e1.`course_id`=c1.`id`
JOIN course AS c2
ON e2.`course_id`=c2.`id`
JOIN classes AS cla
ON cla.`id`=s.`class_id`
AND e1.`score`  > e2.`score`
AND c1.`name`='语文'
AND c2.`name`='英语'

1.2.2.4：子查询

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

1：单行子查询

SELECT NAME FROM student 
WHERE class_id=(
    SELECT class_id FROM student WHERE NAME='张三' ) 
AND NAME<>'张三';

2：多行子查询

IN关键字：in(子查询，返回多行数据)

1. 一个字段in(子查询，返回多行数据但是一个字段)
2. 多个字段in(子查询，返回多行数据，但返回字段和in前面的字段一致)

--in关键字
--多行单个字段
SELECT NAME FROM student WHERE class_id=(SELECT class_id FROM student WHERE NAME='张三' ) AND NAME<>'张三';
--多行多个字段
UPDATE exam_score AS e SET e.`score`=e.`score`+10 WHERE (e.`student_id`,e.`course_id`) IN(
SELECT s.`id`,c.`id`
FROM student AS s ,course AS c
WHERE s.`name`='张三' AND c.`name`='英语');

--插入成绩
INSERT INTO exam_score  (score,student_id,course_id) VALUES (66,6,1),(88,6,2);
--查询重复成绩
SELECT * FROM exam_score AS e WHERE(score,course_id) IN (
	SELECT score,course_id FROM exam_score  GROUP BY score,course_id HAVING COUNT(0) > 1
); 

[not]exists关键字：

--exists关键字
--查询英语二班以外的学生 去掉not为查询英语二班的学生
SELECT s.`name` 
FROM student AS s WHERE [NOT]  EXISTS (
	SELECT 1 FROM  classes AS c WHERE 
	s.`class_id`=c.`id`
	AND c.`name`='英语二班'
);

1.2.2.5：合并查询

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

union：

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

union all：

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

--合并查询
--union 查询结果的并集 去掉重复
SELECT * FROM exam_score AS e WHERE  e.`score` > 60 
UNION 
SELECT * FROM exam_score AS e WHERE e.`course_id`=1;
--union all 查询结果的并集 不去重复
SELECT * FROM exam_score AS e WHERE  e.`score` > 60 
UNION ALL 
SELECT * FROM exam_score AS e WHERE e.`course_id`=1;

二：索引和事务

2.1：索引

2.1.1：概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。（具体细节在后续的数据库原理课程讲解）

索引：使用一定的数据结构，保存索引字段对应的数据，以后根据索引字段来检索，提高检索效率。

2.1.2：作业

1. 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
2. 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
3. 索引对于提高数据库的性能有很大的帮助。

2.1.3：使用场景

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

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

2.1.4：深入

数据库保存数据的基本单位： page(page含有一个或者多个数据)

目的： 硬盘读取文件到内存的IO操作是耗时的，读取数据最好能最少次读取到结果集。

索引划分：

1. 存储结构：B Tree索引，B + Tree索引，Hash索引，FULLTEXT全文索引，R Tree索引。
2. 应用层次：普通索引，唯一索引，主键索引，复合索引。
3. 键值类型：主键索引，辅助索引(二级索引)。
4. 数据存储和索引键值逻辑关系划分：聚集索引，非聚集索引。
5. 索引列数量划分：单列索引，多列索引。

创建的某个索引可能是以上多个类型

每一个节点都是一个Page。

B树： 所有节点既有数据，还有Page指针，索引。

B+树： 只有叶子节点有数据和Page指针，索引其它节点只有索引和Page指针，且叶子节点相连接。

主键索引：默认为B+树，聚簇索引，在B+树的叶子节点存放主键字段值。

非聚簇索引(非主键索引，可以使用多种类型的索引比如B+树和Hash索引等)：B+树叶子节点存放的不是主键字段值，而是索引字段值和主键值。先通过索引字段找到叶子节点的主键值，再通过主键值找整条数据(回表操作)。依赖于有序数据，且可能需要回表操作。更新代缴小于聚簇索引。

聚簇索引：查询速度快，但依赖于有序数据，更新代价大，主键最好使用整形，字段名需要洁简。

覆盖索引：查询的字段正好是索引的字段，直接根据索引找到数据，无需回表操作。

符合索引最左匹配原则：查询的where顺序无所谓，关键是索引创建的字段顺序需要符合最左匹配原则。

优化原则：

1. 索引字段尽量不存在null值，查询使用大于等于或者小于等于，使用is[not]null 会走全表扫描不会走索引，like的操作可能不走索引尽量查询like开头而不是结尾。
2. 频繁查询的字段建立索引，考虑最左匹配原则。
3. 为经常用于连接的字段创建索引。
4. 频繁更新的是字段慎用索引。
5. 尽可能创建联合索引，而不是单列索引。
6. 数据库量不大而无需索引。

2.2：事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

2.2.1：事务特性：

1：原子性：Atomicity

事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生

2：一致性：Consistency

事务必须使得数据库从一个一致性状态变化到另一个一致性状态

3：隔离性：Isolation

事物的隔离性是指一个事务的执行不能被其他的事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行各个事务之间不能相互干扰

4：持久性：Durability

持久性是指任何一个事务一旦被提交，它对数据库中数据的改变是永久性的，接下来的其他操作和数据库故障不应该对其产生影响

1. 开启事务：start transaction。
2. 执行多条SQL语句。
3. 回滚或提交：rollback/commit 说明：rollback即是全部失败，commit即是全部成功。

-- 开启事务
start transaction;
update accout set money=money-2000 where name = 'u1';
update accout set money=money+2000 where name = 'u2';
-- 提交事务
commit;

2.2.2：问题

对于同时运行多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题。

1. 脏读：对于事务T1，T2，当T1读取了被T2更新但还未提交的字段，之后若T2回滚，T1读取的内容就是临时且无效的。

2. 不可重复读：对于两个事务T1，T2，当T1读取了一个字段，然后T2更新了该字段，之后，T1再次读取同个字段，值就不一样了。

3. 幻读：对于两个事务T1,T2，当T1从一个表中读取了一个字段，然后T2在改表中插入了一些新的行，之后如果T1再次读取同一张表就会多出几行。

2.2.3：隔离级别

1. READ UNCOMMITTED（读未提交数据）：在事务A操作时，事务B可读可写。

2. READ COMMITED（读已提交数据）：在事务A操作时，事务B可读可写，但只能读被A提交的数据。

3. REPEATABLE READ（可重复读）: 在事务A操作时，事务B不可修改，但可以读和添加。

4. SERIALIZABLE(串行化)：在事务A操作时，事务B，不可读不可写。

5. 在Oracle数据库中，只支持两种，READ COMMITED（默认），SERIALIZABLE。

6. 在mysql数据库中，支持四种隔离，默认REPEATABLE READ