初识mysql

复习总结

day05-----------------MySql

1、mysql存储引擎

1. MyISAM
2. InnoDB

查看当前存储引擎：
1. ☆ show variables like '%storage_engine%'---------查看当前存储引擎
2. show engines

2、对比MyISAM和InnoDB

	 对比项		 MyISAM		  Innodb(默认)
1. ☆   主外键			不支持		    支持
2. ☆   事务           不支持        支持
3. ☆   行表锁	        表锁          行锁
4.      缓存           只缓存索引    缓存索引和数据
5.      表空间          小           大
6.     关注点          性能         事务
7.      默认安装        是           是

表锁：即使操作一条记录也锁住整张表，不适合高并发
行锁：操作时只锁一行，适合高并发

3、Mysql性能下降sql慢，执行时间长，等待时间长

1. sql语句写的烂
2. 索引失效
3. 关联查询太多join
4. 服务器调优以及各个参数设置

4、mysql几种连接

4.1 等值连接

select u.* from user u ,address a where u.id = a.user_id;

4.2 内连接

1. inner join： select a.*,b.* from user a inner join address b on a.id = b.user_id;

2. join： select a.*,b.* from user a join address b on a.id = b.user_id;

4.3 左外连接：left join

   select a.*, b.* from user a left join address b on a.id = b.user_id;

4.4 右外连接：right join

   select a.*, b.* from user a right join address b on a.id = b.user_id;

4.5 左连接：left join + where B.key is null

select a.*, b.* from user a left join address b on a.id = b.user_id where b.user_id is null;

4.6 右连接：right join + where A.key is null

select a.*, b.* from user a right join address b on a.id = b.user_id where a.id is null;

4.7 全连接 == 左外 union 右外

select a.*, b.* from user a left join address b on a.id = b.user_id
union
select a.*, b.* from user a right join address b on a.id = b.user_id;

4.8 两张表中都没有出现的数据集 == 左连 union 右连

select a.*, b.* from user a left join address b on a.id = b.user_id where b.user_id is null
union
select a.*, b.* from user a right join address b on a.id = b.user_id where a.id is null;

5、索引

5.1 什么是索引 以及如何查看索引

数据库中一个排好序的数据结构，以实现快速查询、更新数据库中的数据

查看索引：show index from table_name;

5.2 索引的分类

5.2.1. 普通索引

5.2.2. 唯一索引

5.2.3. 主键索引

5.2.4. 组合索引

5.2.5. 全文索引

5.3 索引的优势与缺点

5.3.1 优势

1. 提高了数据检索的效率，降低了数据库的IO成本
2. 降低了数据排序的成本，降低了CPU的消耗

5.3.2 缺点

1. 降低更新表的速度，因为更新时不仅要保存数据，还要保存索引文件
2. 占用空间

5.4 索引失效问题

1. 全值匹配我最爱，最左前缀要遵守；
2. 带头大哥不能死，中间兄弟不能断；
3. 索引列上少计算，范围之后全失效；
4. like百分写最右，覆盖索引不写星；
5. 不等空值还有or，索引失效要少用。

5.5 什么时候需要创建索引，什么时候不需要创建

5.5.1 需要创建

1. 主键自动建立
2. 频繁作为查询条件的字段
3. 

5.5.2 不需要创建

1. 频繁更新的字段
2. where条件里用不到的字段
3. 表记录太少
4. 经常增删改的表
5. 某个数据列包含许多重复的值

5.6 索引的工作原理 以二叉树为例

1. 索引键值key
2. 指向对应数据记录物理地址的指针

5.7 索引优化

1. mysql无法利用range类型后面的字段进行查询
2. 左连接索引加右表，右连接索引加左表
3. 永远使用小结果集驱动大的结果集
4. 优先优化内层循环
5. 保证join语句中被驱动表上join条件字段已经被索引

6.数据库范式

6.1 第一范式(1NF):无重复的列

1. 第一范式是关系型模式的基本要求，不满足第一范式的数据库就不是关系型数据库。

2. 第一范式是指数据库表的每一列都是不可分割的基本数据项，
   实体中的某个属性不能有多个值或者不能有重复的属性。

3. 在第一范式中，表的每一行只能包含一个实例的信息。

4. 简而言之，第一范式就是无重复的列。

6.2 第二范式(2NF):非主属性要完全依赖于主关键字

1. 第二范式是在第一范式的基础上建立起来的，即满足第二范式必须先满足第一范式。

2. 第二范式要求数据库表中每一个实例或行必须被唯一的区分。
   为实现区分通常需要为表加上一个列，以储存各个实例的唯一标识，
   这个唯一属性列被称为主键。

3. 第二范式要求实体的属性完全依赖于主键。
   所谓完全依赖与主键是指不能存在仅依赖主键一部分的属性，
   如果存在，那么这个属性和主关键字的这一部分就应该分离出来形成一个新的实体。

4. 简而言之，第二范式就是非主属性要完全依赖于主关键字。

6.3 第三范式(3NF):属性不依赖于其它非主属性—消除冗余

1. 第三范式必须先满足第二范式。

2. 第三范式要求一个数据表中非主属性不存在传递函数依赖，
   例如员工表中有了部门编号之后，就不要再将部门名称、
   部门简介等与部门相关的属性再添加到员工表里了，
   可以单独建一张部门表，因为这些属性之间存在传递函数依赖。

7、drop,delete与truncate的区别

1. drop：DDL，操作即生效，不能回滚，删除整个表(结构和数据)。

2. delete：DML，只删除数据，但是其占用的存储空间还在，还可以支持回滚。

3. truncate：DDL，操作即生效，不能回滚，只删除数据，会释放数据占用的存储空间。

8、谈一谈锁机制

8.1 什么是锁机制

1. 锁是计算协调多个进程或线程并发访问某一资源的机制

2. 在数据库中，除传统的计算资源的竞争以外，数据也是一种供许多用户共享的资源。

3. 如何保证数据并发访问的一致、有效性是所有数据库必须解决的一个问题，
   锁冲突也是影响数据库并发访问性能的一个重要因素。

8.2 锁的分类

8.2.1 从对数据操作的类型(读/写)分

1. 读锁(共享锁)：针对同一种数据，多个读操作可以同时进行而不会互相影响。

2. 写锁(排它锁)：当前写操作没完成前，会阻断其他读锁和写锁。

8.2.2 从对数据操作的粒度分

1. 表锁：偏向MyIsam存储引擎，开销小，加锁快，
        无死锁，锁定粒度大，发生锁冲突的概率最高，并发粒度低。

2. 行锁：偏向InnoDb存储引擎，开销大，加锁慢，
        有死锁；锁定粒度小，发生锁冲突的概率最低，并发粒度高。

8.3 读锁

1. 终端1给A表加了读锁，终端1和终端2都可以对A表进行查操作。

2. 终端1不可以对其他没有锁定的表进行增删改查操作，
   终端2可以对其他没有锁定的表进行增删改查操作。

3. 终端1对A表进行增删改操作都会报错，
   终端2对A表进行增删改操作需要等待终端1释放读锁。

1. 终端1给A表加了写锁，终端1可以对A表进行增删改查操作，
   终端2对A表进行增删改查操作需要等待终端1释放写锁。

2. 终端1不可以对其他没有锁定的表进行增删改查操作，
   终端2可以对其他没有锁定的表进行增删改查操作。

8.4 写锁

8.5 如何分析表锁定

1. Table_locks_immediate:产生表级锁定的次数，
                         表示可以立即获取锁的查询次数，每立即获取值加1

2. Table_locks_waited   ：出现表级锁定争用而发生等待的次数
						（不能立即获取锁的次数，每等待一次锁值加1），
						 此值高则说明存在较严重的表级锁争用的情况。

9、事务

10、谈一谈SQL优化

10.1 explain

1. 查看执行计划
2. explain + SQL

10.2 谈一谈explain的几个属性

10.2.1 id----表的读取顺序

表示查询中执行select子句或者操作表的顺序 
1. id相同：执行顺序由上往下
2. id不同：id值越大，优先级越高，越先被执行

10.2.2 select_type----数据读取操作的操作类型

1. simple：简单的select语句，不包含子查询或者union
2. primary：查询中若包含任何的子部分，最外层被标记为primary
3. subquery：包含了子查询
4. derived：在from列表中包含了子查询被标记为derived(衍生)
5. union
6. union result

10.2.3 type----查询使用了何种类型---------------非常重要####

从最好到最差依次是：

system>const>eq_ref>ref>range>index>all

1. system：表只有一行记录(等于系统表)，平时基本不会出现

2. const：表示通过索引一次就找到了，只匹配了一行数据。

3. eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。

4. req：非唯一性索引扫描，返回匹配某一个单独值的所有行，本质上也是一种索引访问

5. range：只检索给定范围的行，使用一个索引来选择行

6. index：只遍历索引树，全索引扫描

7. all：全表扫描

10.2.4 possible_keys----可能使用的索引

10.2.5 key----实际使用的索引-------------------非常重要

10.2.6 key_len----索引中使用的字节数

10.2.7 ref----索引中哪一列被使用

一般是一个常数--const

10.2.8 rows

大致计算出找到所需记录需要读取的行数

10.2.9 extra------不适合在其他列中显示，但是十分重要的信息

1. using filesort：说明mysql会对数据使用一个外部的索引排序，
   而无法按照表内的索引顺序进行排序-----------------出现的话尽快优化

2. using temporary：使用了临时表  常见于order by 
   和 group by ---------出现的话尽快优化

3. using index：使用了覆盖索引-------------说明效率不错
   1. 同时出现了using where:表明索引被用来执行查找动作
   2. 没有出现using where：表明索引只是用来读取数据

4. using where

5. using join buffer

6. impossible where：where子句总是false

11、比较exists和in

12、谈一谈慢查询

13、存储过程和函数的区别

1. 函数必须有返回值，存储过程没有
2. 函数的参数只能是IN,存储过程可以是IN、OUT、INOUT
3. 函数可以嵌入SQL语句，存储过程一般是作为一个独立的部分来执行
4. 存储过程在创建时进行了预编译，执行速度比函数快

13.1 函数

13.1.1 定义形式

create function 函数名 (形参1 类型1 ，形参2 类型2 ...)

returns 返回值类型

begin

  #这里写完整的函数语句

  return XX值

end

注意：必须有return语句，且返回的类型要跟设定的类型一致。

13.1.2 调用形式

1. 跟调用内部函数一样：select func1();
2. 在编程语句中：set @v1 = func1();

13.1.3 创建一个函数

函数目标：获取三个数中的最大值

create function getMaxValue(p1 float,p2 float,p3 float)
returns float
begin
	declare result float;
	if(p1 >= p2 and p1 >= p3) then
	begin
		set result = p1;
	end;
	elseif(p2 >= p1 and p2 >= p3) then
	begin
		set result = p2;
	end;
	else
	begin
		set result = p3;
	end;
	end if;
	return result;
end;

13.1.4 删除函数

drop function 函数名

注意：
1. 在函数内部，可以用各种变量和流程控制的使用
2. 在函数内部，也可以由各种增删改语句

13.2 存储过程

其本质还是函数，只是没有返回值

13.2.1 定义形式

create procedure 存储过程名 ([in] [out] [inout] 形参1 类型1，

							[in] [out] [inout] 形参2 类型2...)

begin

	# 这里写完整的过程中语句
	# 其中可以有各种的流程控制
	# 还可以有增删改查等等
	# 其中查询语句(select)会作为存储过程调用的结果，返回结果集
end;

13.2.2 创建一个存储过程

目标：将3个数据写入到列表test,并返回该表中的第一个字段的前3个大值的行

create procedure getDate(p1 int,p2 int,p3 int)
begin
	insert into test(f1,f2,f3) values (p1,p2,p3);
	select * from test order by f1 desc limit 0,3;
end;

调用存储过程：
call 存储过程名(实参1，实参2...)

13.2.3 说明

1. in：用于设定变量，用于来"接收实参数据",即"传入",默认不写就是in
2. out：用于设定变量，用于来"存储存储过程中的数据",即"传出",即存储过程中必须对它赋值
3. inout：是in和out的结合，具有双向作用。
4. ☆ 注意：对于out和inout的设定，对应的实参就必须是一个变量，因为该变量是用于接收传出数据

创建一个存储过程，使用 in out inout

create procedure pro1 (in p1 int,out p2 int,inout p3 int)
begin
	set p2 = p1 * 2;
	set p3 = p3 + p1 * 3;
	insert into test(f1,f2,f3) values (p1,p2,p3)
end;

调用：
call pro1(1,2,3)  XXXX	该存储过程第2、3个参数有out，对应的实参必须为变量，这么调会报错！！！

set @v1 = 1
set @v2 = 2
call pro1(1,@v1,@v2)  √√√√

13.3 触发器

13.3.1 定义形式

create trigger 触发器名 触发时机 触发事件 on 表名
for each row
begin
...
end;

说明：
1. 触发时机： before、after
2. 触发事件： insert、update、delete
3. 即触发器的含义是：在某个表上进行insert/update/delete之前/后，会执行其中写好的语句，即每个表只有6个可能情形会调用该触发器。

13.3.2 写一个触发器

增加：insert

create trigger t1 before insert on emp1
for each row
begin
insert into emp2(name) values (new.name);
end;

修改：update

create trigger t2 before update on dept1
for each row
begin
update dept2 set name = new.name where name = old.name;
end;

删除：delete

create trigger t3 before delete on user1
for each row
begin
delete from user2 where name = old.name;
end;

注意：在触发器内部，有两个关键字
1. new：代表当前正要执行的insert或update的时候，"新行"的数据，他可以获得这一新行数据的任意一个字段的值，形式为new.id、new.name。

2. old：代表当前正要执行的update或delete的时候，"旧行"的数据，他可以获得这一旧行数据的任意一个字段的值，形式为old.id、old.name。

14、mysql变量

14.1 普通变量 ----------------不带@符号

1. 定义形式：declare 变量名 类型名 【default 默认值】
2. 赋值：set 变量名 = 值
3. 使用环境：只能在函数、存储过程、触发器中使用。

14.2 会话变量 ----------------带@符号

1. 定义形式：set @变量名 = 值
2. 使用环境：任何地方

14.3 变量赋值有如下形式

1. 针对普通变量：set 变量名 = 值(表达式); 注：此变量必须先用declare声明
2. 针对会话变量：set @变量名 = 值(表达式); 注：此变量不需要用declare声明
3. 针对会话变量：select @变量名 := 值(表达式); 注：会给该变量赋值的同时，还会作为一个"select"语句输出结果集。
4. 针对会话变量：select  值(表达式) into @变量名; 注：会给该变量赋值的同时，但不会作为一个"select"语句输出结果集。

15 mysql编程

15.1 流程控制语句

15.1.1 if 条件语句

if 条件语句 then
	begin
		语句块
	end;
elseif 条件语句 then
	begin
		语句块
	end;
else
	begin
		语句块
	end;
end if;

15.1.2 case 语句

例如：
select name ,
	case sex 
		when 1 then '男' 
		when 0 then '女' 
	end as '性别' 
from user;

例如：
update user set state = 
		case 
			when state = 1 then 0
			when state = 0 then 1
		end;

15.1.3 loop 语句

[标识符:] loop
	begin
		...
	end;
end loop [标识符]

注意：内部必须有一个"退出循环机制" : 
	if ()  then
		begin
			leave 标识符；
		end;

15.1.4 while 循环

[标识符:] while 条件 do
	...
end while [标识符]	

15.1.5 repeat 循环

[标识符:] repeat
	...
until 条件
end repeat [标识符]

15.1.6 leave 语句

leave 标识符