Mysql/Oracle一些语法

以下均是自己学习时的一些总结或者遇到问题的解决办法

in和exists的区别

//in
select * from A where id in(select id from B)

以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.
它的查询过程类似于以下过程
List resultSet=[];
Array A=(select * from A);	//把A表数据查询出来
Array B=(select id from B);	//把B表符合要求的数据查出来
//遍历A数据，和B查出来的数据一个一个对比,符合要求的结果集就是需要的
for(int i=0;i<A.length;i++) {
   for(int j=0;j<B.length;j++) {
      if(A[i].id==B[j].id) {
         resultSet.add(A[i]);
         break;
      }
   }
}
return resultSet;

in适合B表比A表数据小的情况

exists

select a.* from A a where exists(select 1 from B b where a.id=b.id)

以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,
因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.
它的查询过程类似于以下过程
List resultSet=[];
Array A=(select * from A)	//查询A表所有数据
for(int i=0;i<A.length;i++) {
   if(exists(A[i].id) {    //执行select 1 from B b where b.id=a.id是否有记录返回
       resultSet.add(A[i]);
   }
}
return resultSet;

exists适合B表比A表数据大的情况

结论：in先计算子查询的结果，然后以该结果集为源头和主语句发生关联关系；
exists先计算主语句的结果集，在和子查询寻发生关联关系
所以：子查询结果集小，in更适用；反之，若主查询结果集小，则用exists

union和union all

union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;（连接两个结果集，并且去重）
union all: 对两个结果集进行并集操作, 包括重复知行, 即所有的结果全部显示, 不管是不是重复;（连接两个结果集，不去重）
union内部的select语句必须拥有相同数量的列，列也必须拥有相似的数据类型，同时，每条select语句中列的顺序必须相同

关键字作为列名

这几天改了个bug，公司项目是mysql5版本的，但现在有用mysql8版本的，发生了sql报错，主要是因为部分列名在mysql5不是关键字，mysql8是关键字，导致sql执行报错；具体解决方式，在执行的sql中，给关键字加上反引号``(ESC下面的那个键),比如：

//此语句在mysql5中执行时没问题的，因为id和groups均不是关键字，
//但在mysql8中groups是关键字，执行就会报错
insert into t_table (id,groups) values(1,'test');

//mysql8中执行时，给groups加上反引号
insert into t_table (id,`groups`) values(1,'test');

注意：select * from t_table是不会报错的，但只要带groups时就会报错

mysql组合索引

组合索引，是在按一定顺序的多个字段上创建的索引。多个字段的排序不同，索引的使用不同。即 index_name （a, b）和 index_name2 (b, a)是不同的索引。

组合唯一索引也遵循组合索引的使用规则

组合索引的使用规则-最佳左匹配规则

举例说明，假设有如下一个MySQL表：

create table tb_person_info (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`name` varchar(255) NOT NULL COMMENT '姓名',
`age` int DEFAULT '0' COMMENT '年龄',
`birthday` date DEFAULT NULL COMMENT '出生日期',
`sex` varchar(10) DEFAULT NULL COMMENT '性别',
`id_card` char(18) DEFAULT NULL COMMENT '身份证号',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后更新时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_card` (`name`, `age`, `id_card`),
KEY `idx_id_card` (`id_card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='人员信息';

在该表上有创建三个索引，分别是：

• 主键索引id
• 组合索引idx_name_age_card
• 普通索引idx_id_card

下面展示一些SQL语句，并标明是否用到索引，以及用到了哪个索引

# 用到索引idx_name_age_carcd
select * from tb_person_info where name = '张伟';
# 没有用到索引
select * from tb_person_info where age = 25;
# 用到索引idx_id_card
select * from tb_person_info where id_card like '4110812022%';
# 用到索引idx_name_age_card
select * from tb_person_info where name = '张伟' and age = 25;
# 用到索引idx_name_age_card
select * from tb_person_info where name = '张伟' and id_card like '411081%';
# 用到索引idx_id_card
select * from tb_person_info where age = 25 and id_card like '411081%';
# 用到索引idx_name_age_card
select age, id_card from tb_person_info where age = 25 and id_card like '411081%';

总结以上SQL语句可以得到一下结论：

• 查询字段有组合索引之外的字段时，查询条件必须包含组合索引中的第一个字段，才会用到该索引

• 查询字段只限于组合索引内的字段时，查询条件只要有组合索引中的字段，就会用到该索引

当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！但如果是ba、ca也是会用到索引的，这是mysql查询优化器，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划，也就是说，即使写的顺序是ba/ca，但最后真正执行的顺序还是ab/ac；ab是用到了a和b两个索引，ac只用到了a索引。见下图：

索引失效的情况：

1. like查询以“%”开头；
2. or语句前后没有同时使用索引；
3. 组合索引中不是使用第一列索引；
4. 在索引列上使用“IS NULL”或“IS NOT NULL”操作；
5. 在索引字段上使用“not”，“<>”，“!=”等等。

mysql sql语句执行顺序

from-->on-->join-->where-->group by-->avg,sum.... -->having-->select-->distinct-->order by-->limit

mysql的分页查询(limit、offset)

好久没写过源生的mysql分页查询了，之前都是使用的封装好的代码，最近又要写mapper.xml了，涉及到分页了，结果忘记了limit的使用了，下面记录一下

1. 当 limit后面跟一个参数的时候，该参数表示要取的数据的数量(limit number：获取前number条数据)
   例如 select* from user limit 3 表示直接取前三条数据
2. 当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量(limit numbser1,number2：从number1后面获取number2条数据)
   例如 select * from user limit 1,3;
   就是跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据
3. 当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量(limit number1 offset number2：从number2后面获取number1条数据 )
   例如select * from user limit 3 offset 1;表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

这里在顺带提一嘴，当在mapper.xml中写sql进行分页查询时，无论是使用第2种还是第3种，都会将number1和number2作为参数传入，但一般情况下，传的参数都是第N页的M条数据，所以使用时要提前在代码中对offset做处理，一般offset = (页数-1) * M