数据库运用01--MySql浅谈运用入门

最新推荐文章于 2023-06-10 16:41:38 发布

程序学者

最新推荐文章于 2023-06-10 16:41:38 发布

阅读量138

点赞数

本文链接：https://blog.csdn.net/qq_34536604/article/details/106997837

版权

简介

主要内容：mysql常用命令、锁、索引、隔离、事物、事件、触发器

更新：对其中的一些知识进行修改和添加，有任何问题可在下方留言或者右下角直接联系我

sql 基础语法

创建学生数据库: create database Student;
使用学生数据库: use Student;
创建成绩表，字段后加not null 则表示不能为空，在查看表结构时为no，default可设置默认值
```
create table score(id int(20) not null auto_increment,
			   name varchar(10) default 'aa',
			   score_num int(100)，
			   primary key (id)   
				);
```
null:代表未定义的值，不能使用在语句中，但可以通过is null判断某一个字段是否为空

主键和外键的设置在创建字段之后设置

auto_increment:从1递增一个表只能添加一个递增，且该列必须为整数而且不能包含null

查看表结构: desc score;
删除成绩表: drop table score;
插入数据到成绩表中: insert into score(id,name) values(1212,'a');
查看成绩表所有的数据: select * from score;
查看成绩表中id为1的所有数据信息: select * from score where id = 1;
这些类型使用单引号：

char/varchar/date/datetime/time/timestamp/blob

dec/int:不使用引号

': 处理字段中单引号的出现
查看成绩表中ID为1的名字: select name from score where id = 1;
查询成绩表中ID为1 姓名为a的名字:

select name from score where id=1 and name =a;
运算符：

<> 不等于 = 等于 < <= > >= (也可以对字符进行比较) or and

select id from score where name > a; 查询成绩表中名字首字母在a之后的所有数据的id

select * from score where name like ‘%ca’; 查询成绩表中所有名字以’ca’结尾的数据

%：任意数量的未知字符
_: 一个未知字符
查询成绩表中成绩在60到100之间的所有信息

select * from score where score_num between 60 and 101;
查询成绩表中名字首字母在’a’到’b’之间的所有信息

select * from score where name between ‘a’ and ‘c’;

查询成绩表中名字在（’a’,’b’）中的所有信息反过来：not in

select * from score where name in(‘a’,’b’);

not:紧跟在where后 not in是特例，同时可以与and /or /is null 搭配使用位置在and/or后紧跟，
is null 之前
删除成绩表中名字为’a’的数据所有用法与select一样

delete from score where name=’a’;

注意删除时若无约束条件则会删除全部删除只能删除一行或多行，无法删除某一个字段或值
修改成绩表中ID为1的名字为’b’成绩为50 无则不修改任何字段

update score set name=’b’,score=50 where id=1;
查看创建表的语句: show create table score;
为score表创建一个不为空的整型自增的字段并放于表首列，设置其为主键

alter table score add column student_id int not null auto_increment first,
add primary key (student_id);
数据原子性：同一列中不会存储多个类型相同的数据，也不会用多个列存储相同的数据

第一范式：每一个数据行均需包含原子性数据值，且每一个数据行都存在唯一的识别方法

alter table score add column phone varchar(10) after id; 为score表创建
一个电话号码字段并放于id列字段之后
alter…change:同时改变现有列的名称和数据类型

alter table score change column id stu_id int not null auto_increment,

add primary key (stu_id);

将score表中ID改为stu_id并将其设置成自增和主键,必须重新命名该列数据类型
alter…modify:修改现有列的数据类型或位置

alter table score modify column id varchar(120);

修改score表的id类型为varchar(120)

alter…add:在当前表中添加一行

alter…drop：删除表中某列

注意：可能造成数据丢失

alter table score rename to score_stu;将表score的名字改为score_stu

alter table score drop primary key; 删除score表的主键
选出name列中从右侧开始选取的2个字符左侧开始为left

select right(name, 2) from score;

select substring_index(name,’,’,1) from score;

选取name列中以第一个逗号隔开之前的部分，若为2,则为第二个之前的所有部分

其它字符串函数：

substring('aaadsasd',4,3);截取‘aaadsasd'中从位置4开始长度为3
upper('aa');字符串转为大写  lower('CC')；字符串转为小写
reverse('cvs');反转字符串
ltrim('  aaaasdf  '); rtrim('  asaadd  ');清除多余空格
length('asadsad')

运用：update contacts set state = right(location,2);取出contacts 表location字
段中右边两字符并放入到state字段中进行遍历

字符串函数可以和select,update,delete搭配使用

mysql复杂语句

case…end

update movie_table 
set category = 
case 
	when drama = 'T' then 'drama'  
//相当于：update movie_table set category = 'drama' 
								where drama = 'T' 
	when comedy = 'T' and sciti = 'T' then 'comedy' 
	else 'misc'
end;

//找到符合when语句时则直接执行该语句并跳到end

order by :排序

select title, category from movie_table 
where
category = 'family'
order by title,purcheased

desc:倒序

sum avg min max count（属于函数） distinct(去重不一样的值属于关键字)
函数需要加括号关键字不需要

select distinct sale_date
from cookie_eales
order by sale_date;

group by :分组(与3搭配使用属于关键字)


select first_name,sum(sales)
from cookie_eales
group by first_name
order by sum(sales) desc;

limit 查询结果的数量

select first_name,sum(sales)
from cookie_eales
group by first_name
order by sum(sales) desc;
limit 0,2;  //从0开始1结束 一共两行数据

in,between in表示在其中的数据,between表示在两个数之间的数据

select * from atable where column beteen min and max //表示在min 和max之间的数据

elect * from atable where column in(n1,n2,n3) //表示数据是 n1或者n2或者n3

or,and 表示在数据满足一个或者全部都满足
运算符,可以使用运算符表示数据大于小于等于等情况,
like 模糊查询,使用like语句表示查询的时候匹配查询, %表示0，1或者多个字符的占位符, _ 表示一个字符的占位符

having表示筛选和where不同点在于having后面可以跟上聚合函数

SELECT region, SUM(population), SUM(area)
FROM bbc
GROUP BY region
HAVING SUM(area)>1000000

多表查询

创建带有外键的表

create table intereste(
int_id int not null auto_increment primary key,
interest varchar(20) not null,
contact_id int not null,

constraint my_contacts_contact_id 
foreign key (contact_id) references my_contacts (contact_id)
);

内连接(笛卡儿积)

select * from t1 inner join t2;	
select t1.*,t2.* from t1 inner join t2;
加入筛选条件：on
select t1.*,t2.* from t1 join t2 on t1.i1=t2.i2;
别名：
select a.i1,b.i2 from t1 as a join t2 as b on a.i1=b.i2;

外连接

外联结除了显示同样的匹配结果，还可以把其中一个数据表在另一个数据表里没有匹配的数据行也显示出来。外联结分左联结和右联结两种。

左连接

select a.i1,b.i2 from t1 as a left join t2 as b on a.i1=b.i2;

右连接

elect a.i1,b.i2 from t1 as a right join t2 as b on a.i1=b.i2;

用处：

select a.i1,b.i2 from t1 as a right join t2 as b on a.i1=b.i2

where a.i1 is null;

some any

some和any会帮助我们筛选出最小的一个数来作为条件

select * from salary_table where salary > some (select salary from salary_table
where position = ‘Python’);
all

all 会筛选出满足所有条件的选项

select * from salary_table where salary > all (select salary from salary_table
where position = ‘java’);
in (=some any)

SELECT * FROM salary_table WHERE salary IN (SELECT salary FROM salary_table
WHERE
position = ‘Python’);

SELECT * FROM salary_table WHERE salary =some (SELECT salary FROM salary_table
WHERE
position = ‘Python’);
exists 会判断数据是否存在如果不存在则不会筛选数据

select * from salary_table
where exists(SELECT * from salary_table where id = 1)

索引与优化

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构

索引优缺点

优点：

1.所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引

2.大大加快数据的查询速度
缺点：

1.创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加

2.索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值

3.当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。
使用

1.对经常更新的表就避免对其进行过多的索引，对经常用于查询的字段应该创建索引

2.数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。

3.在一同值少的列上(字段上)不要建立索引，比如在学生表的”性别”字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

索引类型

索引的类型

1.普通索引 index(key)： MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

2.唯一索引 unique：索引列中的值必须是唯一的，但是允许为空值

3.主键索引 primary key：是一种特殊的唯一索引，不允许有空值

4.全文索引 fulltext: 只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有”好人，二货 …”

5.空间索引 spatial: 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。

存储数据结构

HASH 只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。

(1)由于索引仅包含hash code和记录指针，所以，MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的，不会对性造成太大的影响。

(2)不能使用hash索引排序。

(3)Hash索引不支持键的部分匹配，因为是通过整个索引值来计算hash值的。

(4)Hash索引只支持等值比较，例如使用=，IN( )和<=>。对于WHERE price>100并不能加速查询。
b-tree BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

单列索引和组合索引

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。这里不要搞混淆了。

普通索引: MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快
一点。

唯一索引：索引列中的值必须是唯一的，但是允许为空值

主键索引：是一种特殊的唯一索引，不允许有空值。
组合索引(复合索引)

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索
引时遵循最左前缀集合。
```
1.需要加索引的字段，要在where条件中
2.数据量少的字段不需要加索引
3.如果where条件中是OR关系，加索引不起作用
4.符合最左原则
```
对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。

索引语句

1.创建索引
以User表的username字段(类型为VARCHAR(50))为例，使用username字段的6个字符前缀来创建索引。
CREATE INDEX idx_user_username ON user (username(6));

CREATE INDEX可对表增加普通索引或UNIQUE索引
--例,只能添加这两种索引;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

2.删除索引

ALTER TABLE table_name DROP INDEX index_name;

3.修改索引：先删除再创建同名索引

--先删除
ALTER TABLE user DROP INDEX idx_user_username;
--再以修改后的内容创建同名索引
CREATE INDEX idx_user_username ON user (username(8));

4.查看索引

--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上db_name.前缀
SHOW INDEX FROM [db_name.]table_name

5.查看是否使用了索引进行查询
EXPLAIN SELECT * FROM book WHERE year_publication = 1990;

6.添加索引
ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));

引擎

概述

存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。存储引擎的概念是MySQl的特点，而且是一个插入式的存储引擎概念。这就决定了MySQl数据库中的表可以使用不同的存储方式存储。用户可以根据自己的不同要求，选择不同的存储方式、是否进行事务处理等。

引擎分类

InnoDB存储引擎

InnoDB是Mysql数据库的一种存储引擎。InnoDB给Mysql的表提供了事务、回滚、崩溃修复能力、多版本并发控制的事务安全、间隙锁（可以有效的防止幻读的出现）、支持辅助索引、聚簇索引、自适应hash索引、支持热备、行级锁。还有InnoDB是Mysql上唯一一个提供了外键约束的引擎。

InnoDB存储引擎中，创建的表的表结构是单独存储的并且存储在.frm文件中。数据和索引存储在一起的并且存储在表空间中。但是默认情况下mysql会将数据库的所有InnoDB表存储在一个表空间中的。

其实这种方式管理起来非常的不方便而且还不支持高级功能所以建议每个表存储为一个表空间实现方式为：使用服务器变量innodb_file_per_table = 1。如果需要频繁的进行更新、删除操作的数据库也可选择InnoDB存储引擎。因为该存储引擎可以实现事务提交和回滚。
MyISAM存储引擎

MyISAM存储引擎是Mysql中常见的存储引擎，MyISAM存储引擎是基于ISAM存储引擎发展起来的。MyISAM支持全文索引、压缩存放、空间索引（空间函数）、表级锁、延迟更新索引键。但是MyISAM不支持事务、行级锁、更无法忍受的是崩溃后不能保证完全恢复（只能手动修复）。

MyISAM存储引擎的表存储成3个文件。文件的名字和表的名字相同。扩展名包含frm、MYD、MYI。其中frm为扩展名的

文件存储表的结构；MYD为扩展名的文件存储数据，其是MYData的缩写；MYI为扩展名的文件存储索引，其为MYIndex
的缩写。

MyISAM存储引擎的插入数据很快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择
MyISAM存储引擎能够实现处理的高效率。如果应用的完整性、并发性要求很低，也可以选择MyISAM存储引擎。
MEMORY存储引擎

MEMORY存储引擎是Mysql中的一类特殊的存储引擎。其使用存储在内存中的内存来创建表，而且所有数据保存在内存
中。数据安全性很低，但是查找和插入速度很快。如果内存出现异常就会影响到数据的完整性，如果重启或关机，表中
的所有数据就会丢失，因此基于MEMORY存储引擎的表的生命周期很短，一般都是一次性的。适用于某些特殊场景像查
找和映射，缓存周期性的聚合数据等等。
NDB 集群引擎

作为sql和NDB元素协议之间的接口,用于mysql,ndb集群存储引擎,分布式等

优化建议

1.尽可能让所有的数据检索都通过索引来完成

2.合理设计索引，使索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；

3.尽量控制事务的大小，减少锁定的资源量和锁定时间长度

4.尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录

5.尽可能按照相同的访问顺序来访问，防止产生死锁

6.尽可能做到一次锁定所需要的所有资源，减少死锁产生概率

锁

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

读写锁

共享锁：对某一资源加共享锁，自身可以读该资源，其他人也可以读该资源（也可以再继续加共享锁，即共享锁可多个共存），但无法修改。要想修改就必须等所有共享锁都释放完之后。

语法为：select * from table lock in share mode
排他锁：对某一资源加排他锁，自身可以进行增删改查，其他人无法进行任何操作。

语法为：select * from table for update –增删改自动加了排他锁
数据库规定同一资源上不能同时共存共享锁和排他锁

命令窗1:select * from table lock in share mode

命令窗2:update table set column1=’hello’

在1未完成前 2无法运行：T2 之所以要等，是因为 T2 在执行 update 前，试图对 table 表加一个排他锁，而数据库规定同一资源上不能同时共存共享锁和排他锁。所以 T2 必须等 T1 执行完，释放了全部的共享锁，才能加上排他锁，然后才能开始执行 update 语句。

锁的大小（颗粒度）

表级锁定（table-level）

表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。
行级锁定（row-level）

行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。使用行级锁定的主要是InnoDB存储引擎。

页级锁定（page-level）

页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。使用页级锁定的主要是BerkeleyDB存储引擎。

myisam的锁

由于MyISAM存储引擎使用的锁定机制完全是由MySQL提供的表级锁定实现，所以我们以MyISAM存储引擎作为示例存储引擎。

MySQL表级锁的锁模式

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
锁模式的兼容性：

1.对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

2.对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；

3.MyISAM表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

innodb的锁

行级锁定不是MySQL自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的InnoDB存储引擎，以及MySQL的分布式存储引擎NDBCluster等都是实现了行级锁定。

InnoDB锁定模式及实现机制

InnoDB的锁定机制和Oracle数据库有不少相似之处。InnoDB的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。

共享锁s 排他锁x 意向共享锁is 意向拍他锁
S 兼容冲突兼容冲突
x 冲突冲突冲突冲突
IS 兼容冲突兼容兼容
IX 冲突冲突兼容兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要
等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排
他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

共享锁（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE … FOR UPDATE

	共享锁s	排他锁x	意向共享锁is	意向拍他锁
S	兼容	冲突	兼容	冲突
x	冲突	冲突	冲突	冲突
IS	兼容	冲突	兼容	兼容
IX	冲突	冲突	兼容	兼容

InnoDB行锁实现方式 InnoDB行锁是通过给索引上的索引项加锁来实现的，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

1.在不通过索引条件查询的时候，InnoDB确实使用的是表锁，而不是行锁。

2.由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的
索引键，是会出现锁冲突的。

3.当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普
通索引，InnoDB都会使用行锁来对数据加锁。

4.即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果
MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行
锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引

乐观锁 vs 悲观锁

乐观锁

总是认为不会产生并发问题，每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新
时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现。

version方式：一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到
的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

update table set x=x+1, version=version+1 where id=#{id} and version=#{version};

CAS操作方式：即compare and swap 或者 compare and set，涉及到三个操作数，数据所在的内存值，预期值，新值。当需要更新时，判断当前内存值与之前取到的值是否相等，若相等，则用新值更新，若失败则重试，一般情况下是一个自旋操作，即不断的重试。
悲观锁

总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁（读锁、写锁、行锁等），当其他线程想要访问数据时，都需要阻塞挂起。可以依靠数据库实现，如行锁、读锁和写锁等，都是在操作之前加锁. 所以说我们上面所说的读锁,写锁都是属于悲观锁
优缺点

两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。

gap和record lock

Gap lock 间隙锁

在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并
不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的：防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他
事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；
Record lock

单条索引记录上加锁，record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
Next-Key

就是Record lock和gap lock的结合，即除了锁住记录本身，还要再锁住索引之间的间隙。

死锁

概述

所谓死锁DeadLock: 是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等带的进程称为死锁进程.表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。那么对应的解决死锁问题的关键就是：让不同的
session加锁有次序

加锁方法

select .. from  
不加任何类型的锁

select...from lock in share mode
共享锁
select...from lock for update
排它锁

update..where   
delete from..where 
排他锁

mysql隔离级别

隔离级别是mysql进行事务提交的时候,对数据的一种表现形式

事务的基本要素（ACID）

原子性（Atomicity）：

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
一致性（Consistency）：

事务开始前和结束后，数据库的完整性约束没有被破坏。比如A向B转账，不可能A扣了钱，B却没收到。
隔离性（Isolation）：

同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
持久性（Durability）：

事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的操作方式

提交使用commit 回滚使用 rollback

begin

'''''语句快

commit

事务的并发问题

脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

|事物隔离级别|胀读|不可重复读|幻读|
|:-:|:-:|:-:|:-:|
|读未提交（read-uncommitted）|是|是|是
|不可重复读（read-committed）    |否|    是|    是
|可重复读（repeatable-read）    |否|    否|    是
|串行化（serializable）    |否|    否|    否

语句实现

	
说明: 我们使用a,b两个端 a负责开启事务,并且修改数据。b负责开启事务,并且在a执行事务的时候不断去获取数
	  据。以此研究数据的影响.a先执行,因为a需要去修改数据,所以a要首先获取到锁才行

1.脏读:
首先在b中去设置隔离级别
a客户端
	start transaction
	update `Employee` set Name='test2' where Id=1
b客户端
	set session transaction isolation level read uncommitted
	start transaction
	select * from `Employee`
可以看到a中修改的数据,b中已经可以查看到,但是a的数据并没有commit,b读取到了a还没有提交上
去的脏数据,称为脏读

2.不可复读:
a客户端
	start transaction
	update `Employee` set Name='test2' where Id=1
b客户端
	set session transaction isolation level read committed
	start transaction
	select * from `Employee`
这时候再去查看数据就不会出现脏读的情况,但是在a提交之后,再使用b去查看的话就会有一定的问题

a客户端
	commit
b客户端
	select * from `Employee`
这时候b的中表的数据就变了,虽然没有脏读的情况,但是b中的数据两次不一样, 所以称之为不可复读

3.幻读:
a客户端
	start transaction
	update `Employee` set Name='test2' where Id=1
b客户端
	set session transaction isolation level REPEATABLE read
	start transaction
	select * from `Employee`
这个时候,b中查看的数据没有没有问题,他看到的一直都是老数据。这里使用了mvvc技术

a客户端
	commit
b客户端
	select * from `Employee`
但是有一个问题,当a表去Insert一条数据数据并且commit了之后,b去select的时候就会看到a表中新加的数据,就像幻读
一样,多出了一条数据来

a
	start transaction
	insert into `Employee` (name,id)value('test',10
	commit
为了避免这个问题,b可以设置
	set session transaction isolation level serializable
在a操作事务的时候,b操作事务会一直卡住,直到a表操作完成b才能去select操作，这就是串行化

4. mysql默认的级别是不可复读

不可重复读和幻读

不可复读是针对同一条数据两次读取会有变化幻读是说当select到数据id=9的时候,这个时候我们可以去插入id=10的数据,但是其他的事务已经插入了id=10的记录,这个时候就会报错。但是从本事务的观点来看id为10的东西是不存在的。所以就想有幻觉一样,称为幻读。

其实对于幻读, MySQL的InnoDB引擎默认的RR级别已经通过MVCC自动帮我们(部分)解决了。因为当其他事务增加一条数据的时候。我们两次执行查询语句结果都是一样的。这是使用了mvcc模式来实现的,在RR模式下面，事务每次读取的都是一个快照。同一个事务中每次都读取同一份快照。所以就算数据更新了。读的也是老数据

触发器和事件

概述

1.存储函数（stored function）。返回一个计算结果，该结果可以用在表达式里。
2.存储过程（stored procedure）。不直接返回一个结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户。
3.触发器（trigger）。与数据表相关联，当那个数据表被INSERT、DELETE或UPDATE语句修改时，触发器将自动执行。
4.事件（event）。根据时间表在预定时刻自动执行。

存储过程

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

使用存储过程的情况主要有两种：

（1）只需通过运算来实现某种效果或动作而无需返回一个值，

（2）运算会返回多个结果集（函数做不到这一点）。这只是些指导性建议，不是硬性规定。存储函数要用CREATE FUNCTION语句来创建，存储过程要用CREATE PROCEDURE语句来创建，为了与数据表或数据列的名字有所区别，给参数起名字时将使用p_前缀。
分隔符,由于mysql默认分隔符是 ; 如果我们使用了;表示存储过程已经结束,但是mysql默认语句也是;结束,如果我们想要在存储过程中多加几个sql语句怎么办？我们需要先修改分割符,然后写sql语句和存储过程然后恢复到默认的分隔符。

delimiter命令把mysql程序的语句分隔符重定义为另一个字符或字符串，它必须是在存储例程的定义里没有出现过的。

创建存储过程：
delimiter $
create procedure show_born()
begin
select 1;
select 2;
end$
delimiter ;
存储过程调用：
call show_born
存储过程参数传入：
create procedure p1 (p_id INT) begin select p_id; end$

创建存储函数：
delimiter $
create function show()
begin
select 1;
select 2;
end$
delimiter ;

触发器

触发器是与特定数据表相关联的存储过程，当相应的数据表被INSERT、DELETE或UPDATE语句修改时，触发器将自动执行。
触发器可以被设置成在这几种语句处理每个数据行之前或之后触发。触发器的定义包括一条将在触发器被触发时执行的语句。

创建触发器

每次创建新值的时候,新的数据列test2都会变成999:

create trigger test1_t before insert on test1 for each row begin set NEW.test2=999; end$

事件

我们可以把数据库操作安排在预定时间执行。事件是与一个时间表相关联的存储程序，时间表用来定义事件发生的时间、次数以及何时消失。事件非常适合用来执行各种无人值守的系统管理任务，如定期更新汇总报告、清理过期失效的数据、对日志数据表进行轮转等。
默认条件下，事件不会执行，需要启动事件调度器：

把以下语句添加到一个选项文件中（服务器在启动时将读取）： [mysqld] event_scheduler=ON
查看事件调度器状态：show variables like ‘event_scheduler’
创建事件：

create event e1 on schedule every 5 second do insert into test1 (test1)values(‘8888’);

do 定义语句部分
事件禁用和激活

事件禁用：

alter event e1 disable;

事件激活：

alter event e1 enable;

程序学者

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库运用01--MySql浅谈运用入门

简介主要内容：mysql常用命令、锁、索引、隔离、事物、事件、触发器更新：对其中的一些知识进行修改和添加，有任何问题可在下方留言或者右下角直接联系我sql 基础语法创建学生数据库: create database Student;使用学生数据库: use Student;创建成绩表，字段后加not null 则表示不...
复制链接

扫一扫