mysql经历
-
limit M:返回 M 条记录
limit N,M : （第一个参数是跳过的数量）从第 N+1 条记录开始, 返回 M 条记录SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
另外，不能直接用limit N-1，因为limit和offset字段后面只接受非负整数（意味着负数、小数都不行）或者单一变量（意味着不能用表达式） -
mybatis查询没有数据时返回的list是空集合，即size=0， 并不是null
-
select 显示列 from 表 。其中显示列可以直接进行数学计算（字段需要是数字型） 。*可以获取所有列，但是影响性能。
-
数据为非数字型需要加'' ，大多数数据库都支持单引号和双引号的互换，mysql是支持的
-
数据库所有关键字（包括表名，字段名，库名）不区分大小写，但数据区分大小写（Mysql中数据也不区分大小写）。
-
数字可以用“<,>,<=,>=”,字符串也可以使用（效果是按照字典顺序排列大小）。
-
group by会造成其他数据消失.如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将原结果集缩小为分组数.这意味着 其他的数据在分组后丢弃.
-
聚合函数常与GROUP BY---HAVING一同使用；另外，聚合函数可以应用于查询语句的SELECT中，但不可用于WHERE语句中，因为WHERE是对逐条的行记录进行筛选，聚合函数针对组进行操作，没有group就把所有数据当成一个大组.
mysql中，SELECT id, premium FROM renewal_details WHERE channel_code = 'T00_A'会查出100条数据；但在select后面加上一个SUM(premium)，变为
SELECT id, SUM(premium) FROM renewal_details WHERE channel_code = 'T00_A'就只有一条数据。count同理。
-
数据库索引，由关键字KEY或INDEX定义。
普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件(WHERE column = ...)或排序条件(ORDER BY column)中的数据列创建索引。 -
多表关联时，用了一个允许为空的字段去关联其他表，导致报错
-
加limit分页会加快查询的效率，测试案例如下
- 加limit 0,10 ，查询时间67秒
- 不加limit，查询时间104秒
常说的Limit的执行效率高，是对于一种特定条件下来说的：即数据库的数量很大，但是只需要查询一部分数据的情况。高效率的原理是：避免全表扫描，提高查询效率。比如：每个用户的email是唯一的，如果用户使用email作为用户名登陆的话，就需要查询出email对应的一条记录。
SELECT * FROM t_user WHERE email=?;上面的语句实现了查询email对应的一条用户信息，但是由于email这一列没有加索引，会导致全表扫描，效率会很低。
SELECT * FROM t_user WHERE email=? LIMIT 1;加上LIMIT 1，只要找到了对应的一条记录，就不会继续向下扫描了【数据库扫描原理未知】，效率会大大提高。
-
mysql会自动进行隐式转换，但是会造成性能问题。 隐式转换举例：一个varchar字段status取值为{'1','2'};sql中写成status=1也能执行， 在写SQL时一定要养成良好的习惯，查询的字段是什么类型，等号右边的条件就写成对应的类型。特别当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描。
-
mysql 的cast函数：CAST(expression AS TYPE)
CAST()函数将任何类型的值转换为具有指定类型的值。目标类型可以是以下类型之一：BINARY，CHAR，DATE，DATETIME，TIME，DECIMAL，SIGNED，UNSIGNED
ps：我使用CAST('1' AS INTEGER)报错，后改为DECIMAL好使 -
对于开发者而言，对MySQL的调优重点一般是在「开发规范」、「数据库索引」又或者说解决线上慢查询上。规范上只要有查询需求都应该建索引。【坑】
-
是否能使用覆盖索引，减少回表【坑】所消耗的时间。意味着，我们在select 的时候，一定要**指明对应的列，而不是select ***。
-
考虑是否组建联合索引，如果组建联合索引，尽量将区分度最高的放在最左边，并且需要考虑最左匹配原则。【坑】
-
对索引进行函数操作或者表达式计算会导致索引失效
-
利用子查询优化超多分页场景。比如 limit offset , n 在MySQL是获取 offset + n的记录，再返回n条。而利用子查询则是查出n条，通过ID检索对应的记录出来，提高查询效率。
-
通过explain命令来查看SQL的执行计划，看看自己写的SQL是否走了索引，走了什么索引。通过show profile 来查看SQL对系统资源的损耗情况（不过一般还是比较少用到的）
-
在开启事务后，在事务内尽可能只操作数据库，并有意识地减少锁的持有时间（比如在事务内需要插入&&修改数据，那可以先插入后修改。因为修改是更新操作，会加行锁。如果先更新，那并发下可能会导致多个事务的请求等待行锁释放）
-
-
在MySQL中，可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。EXPLAIN命令是查看优化器如何决定执行查询的主要方法。EXPLAIN命令虽然没有提供任何优化建议，但它能够提供重要的信息有助于调优决策。EXPLAIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。
- type：对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
性能依次由好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，all。
除了all之外，其他的type都可以使用到索引。除了index_merge之外，其他的type只可以用到一个索引。 - rows：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
- id：包含一组数字，表示查询中执行select子句或操作表的顺序【坑】。
- id相同，执行顺序由上至下。
- 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
- id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
- type：对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
-
mysql数据库中unsigned表面含义是'无符号'的意思,unsigned即为非负数，unsigned属性只针对整型，而binary属性只用于char和varchar。（BINARY不是函数，是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写）
-
case when：无中生有一个新的字段（维度），解决了分组两次（创造一个维度并用它分组）的难题
-
简单Case函数语法格式：
case 列名
when 条件值1 then 选项1
when 条件值2 then 选项2
.......
else 默认值 end
-
Case搜索函数语法格式：
case
when 列名= 条件值1 then 选项1
when 列名=条件值2 then 选项2
.......
else 默认值 end
-
-
分组两次（group by后面两个参数）//人分男人女人，在这两个组里再分成有房和没房
-
去重两个参数（distinct 后面两个参数）
SELECT DISTINCT state, city from customers WHERE state IS NOT NULL;//对state，city都相同的结果才去重
-
在left join语句中，左表过滤必须放where条件中，右表过滤必须放on条件中，这样结果才能不多不少，刚刚好。
-
sql查询执行顺序：
- FROM 和 JOIN
FROM 或 JOIN会第一个执行，确定一个整体的数据范围. 如果要JOIN不同表，可能会生成一个临时Table来用于 下面的过程。总之第一步可以简单理解为确定一个数据源表（含临时表) - WHERE
确定了数据来源，WHERE 语句就将在这个数据源中按要求进行数据筛选，并丢弃不符合要求的数据行，所有的筛选col属性 只能来自FROM圈定的表. AS别名还不能在这个阶段使用，因为可能别名是一个还没执行的表达式 - GROUP BY
如果你用了 GROUP BY 分组，那GROUP BY 将对之前的数据进行分组，统计等，并将原结果集缩小为分组数.这意味着 其他的数据在分组后丢弃. - HAVING
如果你用了 GROUP BY 分组, HAVING 会在分组完成后对结果集再次筛选。AS别名也不能在这个阶段使用. - SELECT
确定结果之后，SELECT用来对结果col简单筛选或计算，决定输出什么数据. - DISTINCT
如果数据行有重复DISTINCT 将负责排重. - ORDER BY
在结果集确定的情况下，ORDER BY 对结果做排序。因为SELECT中的表达式已经执行完了，此时可以用AS别名. - LIMIT / OFFSET
最后 LIMIT 和 OFFSET 从排序的结果中截取部分数据.
- FROM 和 JOIN
-
创建数据库时，库名 表名 使用反引号``，这么加反引号只是作一个保险，这也是一个良好的sql建表习惯。
反引号：它是为了区分MYSQL的保留字与普通字符而引入的符号。注意划重点：有MYSQL保留字作为字段的，必须加上反引号来区分！！！所谓的保留字就是select database insert 这一类数据库的sql指令，当我们不得已要拿他们来做表名和字段名的时候 我们必须要加反引号来避免编译器把这部分认为是保留字而产生错误。
务必要记住：保留字既不能作为表名，也不能作为字段名，如果非要这么操作，请记住要增加反引号！
-
IFNULL(expression_1,expression_2);如果
expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。这个函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
-
IF(expr1,expr2,expr3);如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL), 则 IF() 的返回值为 expr2；否则返回值则为 expr3
-
mysql日期格式化函数 :DATE_FORMAT(d,f), 按照表达式f的要求显示日期d。
如**%Y-%m-%d %H:%i:%s **会将日期d转为"yyyy-MM-dd HH:mm:ss"格式
e.g DATE_FORMAT(GIT_COUNT_DATE, '%Y-%m') -
MySQL中concat函数用法：CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
-
like操作符:LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较；但如果like后面没出现通配符，则在SQL执行优化时将 like 默认为 “=”执行。
"%" 百分号通配符: 表示任何字符出现任意次数 (可以是0次)。
"" 下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符。当然，也可以like "陈___"，数量不限。
-
在xml文件里写sql的模糊查询用：concat('%', #{abbreviation},'%')
-
外键：将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)，此时，第二个表中保存这些值的属性称为外键(foreign key)。
-
啥是InnoDB？
InnoDB，是MySQL的数据库引擎之一，现为MySQL的默认存储引擎。与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能。- ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。
- InnoDB是，事务型数据库的首选引擎，支持ACID事务，支持行级锁定。InnoDB是为处理巨大数据量时的最大性能设计。
- InnoDB 给 MySQL 提供了具有事务(transaction)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)、多版本并发控制(multi-versioned concurrency control)的事务安全(transaction-safe (ACID compliant))型表。
-
事务在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。
-
mysql的事务四个特性：
- 原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。
因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。 - 一致性（Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。一致性与原子性是密切相关的 - 隔离性（Isolation）
隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，
不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别。 - 持久性（Durability）
持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务已经正确提交，
即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成。
否则的话就会造成我们虽然看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。这是不允许的。
- 原子性（Atomicity）
-
触发器（trigger）是个特殊的存储过程，是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发：比如当对一个表进行操作（insert，delete，update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。
-
举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。
-
CREATE TRIGGER 触发器的名称 触发时机(BEFORE或者AFTER) 触发事件(INSERT、DELETE或者UPDATE) ON 表名(在哪张表上建立触发器) FOR EACH ROW 触发器程序体(可以是一条SQL语句或者是用BEGIN和END包含的多条语句)-
BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后
-
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器
-
所以可以说MySQL创建以下六种触发器：
BEFORE INSERT , BEFORE DELETE , BEFORE UPDATE
AFTER INSERT , AFTER DELETE , AFTER UPDATE
-
-
create trigger addOne before insert on class_seven for each row begin 执行语句列表 end其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开
tips：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突
为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成 ||
当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;
-
load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary 或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。
-
根据以上的表格，可以使用以下格式来使用相应的数据：
NEW.columnname：新增行的某列数据 OLD.columnname：删除行的某列数据 -
写一个触发器
delimiter $ create trigger addOne before insert on class_seven for each row begin insert into basket_team(name) values (NEW.name); end$ delimiter ; -
SHOW TRIGGERS语句查看触发器信息
-
触发器会有以下两种限制：
- 触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过OUT或者INOUT类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
- 不能再触发器中使用以显示或隐式方式开始或结束事务的语句，如START TRANS-ACTION,COMMIT或ROLLBACK
-
MySQL的触发器是按照BEFORE触发器、行操作、AFTER触发器的顺序执行的，其中任何一步发生错误都不会继续执行剩下的操作，如果对事务表进行的操作，如果出现错误，那么将会被回滚，如果是对非事务表进行操作，那么就无法回滚了，数据可能会出错。
-
触发器是基于行触发的，所以删除、新增或者修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过得的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑
-
-
mysql的索引
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。正确合理的使用索引可以大大提高MySQL的检索速度，但过多的使用索引将会造成滥用。索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
-
索引分单列索引和组合索引。
单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
-
主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。【坑】
-
FULLTEXT即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。
全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。
-
由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。
HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
-
BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
-
RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。
-
-
索引种类：
普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个组合索引【坑】：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（索引合并：使用多个单列索引组合搜索）
-
操作索引：
-
创建索引
#创建普通索引 CREATE INDEX index_name ON table_name(col_name); #创建唯一索引 CREATE UNIQUE INDEX index_name ON table_name(col_name); #创建普通组合索引 CREATE INDEX index_name ON table_name(col_name_1,col_name_2); #创建唯一组合索引 CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2); -
通过修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name); -
创建表时直接指定索引
CREATE TABLE table_name ( ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name) ); -
删除索引
#直接删除索引 DROP INDEX index_name ON table_name; #修改表结构删除索引 ALTER TABLE table_name DROP INDEX index_name;
-
-
创建索引的时机：
一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：
SELECT t.Name FROM mytable_t t LEFT JOIN mytable_m m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;此时就需要对city和age建立索引，由于mytable_m表的userame也出现在了JOIN子句中，也有对它建立索引的必要。刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。
-
命中索引:
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。错误举例：
#like '%xx'【已验证】 select * from tb1 where name like '%cn'; #使用函数【已验证】 select * from tb1 where reverse(name) = 'wupeiqi'; #or 【已验证】 select * from tb1 where nid = 1 or email = 'seven@live.com'; # 特别的：当or条件中有未建立索引的列才失效，以下会走索引 select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex' #类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... 【已验证】 select * from tb1 where name = 999; #>【坑】 select * from tb1 where name > 'alex' # 特别的：如果是主键或索引是整数类型，则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123 #order by【坑】 select email from tb1 order by name desc; # 当根据索引排序时候，选择的映射如果不是索引，则不走索引 # 特别的：如果对主键排序，则还是走索引： select * from tb1 order by nid desc; #组合索引最左前缀 如果组合索引为：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引 -
其他注意事项：
- 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar - 表的字段顺序固定长度的字段优先【坑】 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引【坑】 - 使用连接（JOIN）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值（重复多）不适合建索引，例：性别不适合 -
limit分页：
若需求是每页显示10条数据，如何建立分页？
使用LIMIT尝试：
# 第一页 SELECT * FROM table_name LIMIT 0,10; # 第二页 SELECT * FROM table_name LIMIT 10,10; # 第三页 SELECT * FROM table_name LIMIT 20,10;但是这样做有如下弊端：
- 每一条select语句都会从1遍历至当前位置，若跳转到第100页，则会遍历1000条记录
- 若记录的id不连续，则会出错【坑】
改善：
若已知每页的max_id和min_id，则可以通过主键索引来快速定位:
#下一页 SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10); #上一页 SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10); #当前页之后的某一页【坑】 SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (页数差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC); #当前页之前的某一页【坑】 SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (页数差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P) ORDER BY id ASC;.
-
-
索引失效的情形:【挨个创建表尝试】
- 当查询的字段是字符串时，等号右边的条件一定要用引号引起来标明这是一个字符串，否则会造成索引失效触发全表扫描，这是类型转换的一个例子。【已验证】
- 不要在索引列上做任何的操作【坑，我的测试只是在where条件里面生效，不是在select后面】，包括计算、函数、自动或者手动类型的转换，这样都会导致索引失效【已验证】
