mysql

查询关键字的使用顺序

1.查询中用到的关键词主要包含六个，并且他们的顺序依次为 select–from–where–group by–having–order by

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序 与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行

from:需要从哪个数据表检索数据

where:过滤表中数据的条件

group by:如何将上面过滤出的数据分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果

*order by 😗按照什么样的顺序来查看返回的数据

触发器

触发器是一种特殊的存储过程，主要是通过事件来触发而被执行的。

它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。

可以联级运算

触发器创建

1.创建table1,table2 表：
create table table1(name char(30),age int)charset="utf8";
create table table2(name char(30),age int)charset="utf8";
2.创建触发器：
DELIMITER ||
create trigger tb2_auto_add after insert on table1
for each row
begin
insert into table2(name,age)value("老旧",33);
end
DELIMITER ;
insert into table1(name,age)value("老刘"，23)；

视图

简化复杂的sql操作

是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改会影响基本表。它使得我们获取数据更容易，相比多表查询。 游标

是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要

在视图里面进行数据的修改之后，对应在正式表里面的数据也会被修改

drop和truncate和delete的区别

drop是进行删除表格  　　　　truncate是删除所有表中的数据并删除表数据缓存   delete是删除数据

count（*）和count（column）和count（）的区别

count()对行的数目进行计算,包含NULL

count(column)对特定的列的值具有的行数进行计算,不包含NULL值

count()还有一种使用方式,count(1)这个用法和count()的结果是一样的

count(1): 便利整张表,但不取值,对于返回的数据,放入1进去.然后累加

本地临时表（表明带一个#）

 

CREATE TABLE #Products (
ProductID INT PRIMARY KEY,
ProductName varchar
);

 

全局临时表（表明带有二个##）

CREATE TABLE ##Products (
ProductID INT PRIMARY KEY,
ProductName varchar
);

从临时表插入和查询数据

INSERT #Products (ProductID, ProductName, ProductDescription)
VALUES (1, 'The temporary time leap', 'A novel about temporary time leaping.');

 

通配符

%百分号通配符:表示任何字符出现任意次数(可以是0次)

_下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.

%通配符的使用

%通配符使用:

匹配以"yves"开头的记录:(包括记录"yves") SELECT *FROM products WHERE products.prod_name like ‘yves%’;

匹配包含"yves"的记录(包括记录"yves") * SELECT FROM products WHERE products.prod_name like ‘%yves%’;

匹配以"yves"结尾的记录(包括记录"yves",不包括记录"yves ",也就是yves后面有空格的记录,这里需要注意) SELECT * FROM products WHERE products.prod_name like ‘%yves’;

*通配符使用:

匹配结果为: 像"yyves"这样记录 SELECT FROM products WHERE products.prod_name like ‘_yves’;

匹配结果为: 像"yvesHe"这样的记录.(一个下划线只能匹配一个字符,不能多也不能少) SELECT FROM products WHERE products.prod*name like ‘yves**’;

 sql优化

1、添加索引

2、in代替or，或者between代替in

3、禁用select *

4、where 判断null（对null判断会扫描全表）

5、where后面添加表达式

6、inner join  代替左右连接

7、禁用%开头的模糊查询

8、避免锁

没有索引进行变更时会有表锁，有索引是进行变更会有行锁

innodb引擎使用update时,会有行锁/表锁两种模式, 如果where 字段没有索引的时候会升级成表锁

 

update table set xx=1 where name=xx (name没有索引,此时是表锁)
update table set xx=1 where id=xx (id有索引,此时是行锁)

索引

单列索引

以表的单个字段创建的索引

联合索引

以表的多个字段创建的索引

普通索引

非主键非唯一性的索引

主键索引

唯一索引

索引失效

1、模糊查询

2、大于小于不等于

3.函数

4、模糊查询（以%开头的like）

5、not  in  ，not  exist

5，or

6、is  not   null  

连接类型

内连接

只获取连接匹配的值

左连接

返回左表的全部数据和右边相等的数据

右连接

返回全部的右表数据和左表相等的数据

mysql的sql语句的执行流程

1、客户端发送一条查询给服务器

2、查询缓存，如果没有缓存就进入下一阶段

3、服务端进行sql解析，预处理，再由优化器生成对应的执行计划

4、mysql根据优化器生成的执行计划，调用引擎的Api来执行查询

5、数据返回

mysql的事务特性

原子性  一致性  隔离性 持久性

事务的并发问题

脏独

读取到未提交的数据或操作

幻读

A，B同时在修改一条数据的事务里面，A将数据1改为二并提交显示数据为2，同时B将2改为了1，并结果显示为1，等A返回发现结果为1，这是A就出现了幻读 

不可重复读

 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致

事务的隔离级别

读未提交：另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据脏读

不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果因此本事务先后两次读到的数据结果会不一致。

可重复读：在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象

串行化：最高的隔离级别，在这个隔离级别下，不会产生任何异常。并发的事务，就像事务是在一个个按照顺序执行一样

mysql的事务的传播行为

1、需要事务

没有事务就创建一个事务，有事务就加入该事务

2.支持事务

有事务就按照事务走，没有就按照非事务走

3、创建事务

无论是否有事务都创建事务

4、不支持事务进行

有事务就挂起，非事务就执行

5、非事务

有事务就抛出异常

vchar和char的区别

char 是定长   vchar是可变的

char定长不够时会使用空格进行代替存储  ，空间换取效率

mysql的读写锁

锁的本质就是为Id打上标记

表锁和行锁

表锁是MySQL中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表，一个用户在对表进行写操作前需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获取读锁，读锁之间不相互阻塞

行锁

行锁可以最大程度地支持并发，同时也带来了最大开销。InnoDB 和 XtraDB 以及一些其他存储引擎实现了行锁。行锁只在存储引擎层实现，而服务器层没有实现

添加行锁的方法

使用select ......where ..语法时where后面必须时索引，然后在后面加for update

行锁分为共享锁和排它锁

在进行 insert ，delete   ，update时会自动加行锁

乐观锁

用数据版本Version记录机制实现，这是乐观锁最常用的一种实现方式

数据版本，即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 version字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据

写法  ：

取数据时一起取出version

update  table1  set name=‘1’ ，version=version+1 where id=1  and version =#{version}

mybatis的缓存

一级缓存

本地缓存

session级别的缓存相同的sql会从session里面进行取值，容易产生脏数据

二级缓存

全局缓存  基于nameSpace级别的缓存，多个session共享，mybatis里面的二级缓存默认开启

mysql函数

GROUP_CONCAT 将所查询的字段，可能涉及的多个值，并列在一个框里面

 

max最大值

 

min最小值

 

avg 平均值

 

sum 总数

 

random 随机数 ： select random() as random; 函数返回一个介于-9223372036854775808 和 +9223372036854775807 之间的伪随机整数。

 

abs 绝对值

 

upper 把该字段内容进行全部大写

 

lower 把该字段内容进行全部小写

 

length 返回内容字符串的长度 SELECT name, length(name) FROM COMPANY

%取余、mod函数取余

floor(x):取小于等于x的最大整数， 向下取整函数 ceiling(x):取大于等于x的最小整数，向上取整函数