mysql、oracle日程总结

问题发现记录：使用case when 语法时，需要考虑这个字段的类型比如：

 case when sum(greate) is null then 0  else sum(greate) end grades

then 0是因为这个greate是因为他是int类型的，sql查询没有问题，但在java获取时，如果写成

then '0' java获取这个值就是一个对象，无法解析

一、MyISAM 和 InnoDB 的区别

1、InnoDB支持事务，MyISAM不支持事务

2、InnoDB支持外键，MyISAM不支持外键

3、InnoDB是聚集索引，也叫聚簇索引（使用B+Tree作为索引结构，数据文件和主键索引绑定在一起的，它必须有主键），

MyISAM是非聚集索引（使用B+Tree作为索引结构，数据文件和索引是分离的，可以没有主键）

(eg:一个简单的语法操作，注意顺义，记录备忘）

select  name  from demo  where name like'%%' and id = '1'  order by age desc  limit 2

二、怎么判断索引是否创建成功

MYSQL

1.show index from 表名

2.explain select * from demo(查看执行计划)

id:有几个子查询就有几个id,并且id的顺序是按照id出现的顺序递增的

select_type:查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询(simple是简单查询)

table：表名

type:访问类型，sql查询优化中一个很重要的指标，结果值依次从最优到最差分别为：

system >const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL,一般来说，好的sql查询至少达到range级别，最好能达到ref

(all,表示为全表扫描,range：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取)

possible_keys:这一列显示查询可能使用哪些索引来查找。 

 key,这一列显示mysql实际采用哪个索引来优化对该表的访问

Extra,不适合在其他字段中显示，但是十分重要的额外信息

having和聚合函数使用

select s.name,avg(sc.scores) from student s join score sc on s.sid=sc.sid group by s.sid  having avg(scores) >80

ORACLE

1、explain plan for +     目标SQL

2 、select * from     table(dbms_xplan.display)

三、mysql给表的字段加索引

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址，在数据十分庞大的时候，索引可以大大加快查询的速度，这是因为使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。”

1、索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够

2、创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

什么时候要使用索引？

• 主键自动建立唯一索引；
• 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
• 作为排序的列要建立索引；
• 查询中与其他表关联的字段，外键关系建立索引
• 高并发条件下倾向组合索引；
• 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不要使用索引？

• 经常增删改的列不要建立索引；
• 有大量重复的列不建立索引；
• 表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

索引失效的情况：

• 在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
• 在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
• LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
• 在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
• 在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括&lt;符号、>符号和！，如果占总记录的比例很小的话，也不会失效）
• 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
• 字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email='99999'。
• 在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
• 如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
• 尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；

索引：就是帮助数据库高效获取数据的数据结构；

简历索引会占用内存空间，一般数据量少的，频繁更新的（不但要修改对应的值，还要去修改索引数据结构中对应的值，所以索引会提升查询速率但是会降低增删改的效率），很少使用的不建议设置索引

1、添加普通索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

 (项目场景：一个七表关联的语句，执行三十秒，发现表都没有加上索引，从where条件考虑，排除让索引失效的情况，在条件中加上普通索引，时间缩减到1秒以内，思路：先通过explain观察sql执行情况，发现有些表使用了全文检索，并且没有使用任何索引，索引考虑给这些表某些字段加上索引)

eg:同上数据库表：alter table demo add index index_test(name) 

2、添加主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

 3、添加唯一索引 （UNIQUE）

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

4、全文索引 （FULLTEXT）

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5、多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

删除索引：

drop index 索引名 on 表名

查询索引：

show index from 表名

ORACLE 中的分页：

select * from ( select rownum rn,a.* from table_name a where rownum <= x //结束行，x = startPage*pageSize ) where rn >= y; //起始行，y = (startPage-1)*pageSize+1

MYSQL中的分页 limit(m,n)

select * from stu limit 2,4;

即：取stu表中第3至第6条，共4条记录。

select * from stu limit 5;

即：取stu表中前5条，共5条记录。

select 语句的嵌套使用，

select c.*,a.*,b.time from 
(select * from OAHT_RESERVEPROJECT) c, 

(select gid,COUNT(gid) sum from OAHT_BUSINESSCOMMINFO where gid in (select id from OAHT_RESERVEPROJECT) GROUP BY gid) a,

(select  gid,max(UPDATEAFTERTIME) time from OAHT_BUSINESSCOMMINFO GROUP BY gid) b where a.gid=b.gid and b.gid=c.id

嵌套+分页

eg:

select * from
(select rownum rn,d.* from
(select  c.*, a.*,b.time,t.commtime,t.COMMDESC,h.telname,h.tel ,case when instr(c.businessperson,  '张三')>0 then '1' else '0' end PERSONFLAG  from 
(select * from OAHT_RESERVEPROJECT) c, 
(select gid,COUNT(gid) sum from OAHT_BUSINESSCOMMINFO where gid in (select id from OAHT_RESERVEPROJECT) GROUP BY gid) a,
(select  gid,max(COMMTIME) time from OAHT_BUSINESSCOMMINFO GROUP BY gid) b,
(select TELPERSONNAME as telName, tel,gid from OAHT_TELNAMEINFO where id in (select min(id) id  from OAHT_TELNAMEINFO GROUP BY gid)) h,
(select  f.* from OAHT_BUSINESSCOMMINFO f, (select  gid,max(COMMTIME) time from OAHT_BUSINESSCOMMINFO GROUP BY gid) g where f.COMMTIME=g.time) t
where a.gid=b.gid and b.gid=c.id and t.gid=c.id and h.gid=c.id) d 
where rownum <= 10 ) where rn >=1

Oracle中遇到的一些有意思的写法sql:

1、select (select max(wid) from eap_vw_work where instanceid=c.INSTANCEID) instanceId,c.title, c.startTime,c.comments from EAP_VW_DONE c where  actor = 'anhuili'

2、decode函数

举例说明：

现定义一table名为output，其中定义两个column分别为monthid（var型）和sale（number型），若sale值=1000时翻译为D，=2000时翻译为C，=3000时翻译为B，=4000时翻译为A，如是其他值则翻译为Other；

SQL如下：

Select monthid , decode (sale,1000,'D',2000,'C',3000,'B',4000,'A',’Other’) sale from outpu

3|

4、NVL

5、把一个表中的字段聚合在一起

select listagg(SUPPLYMAINBUSINESS,';') within group (order by SUPPLYMAINBUSINESS) SUPPLYTYPE from OAHT_RECORD_PROCESS