sql知识，不定时更新

整理些自己工作以来遇到的sql相关知识，不定时更新......

sql优化：

1、show status 查看状态

2、show processlist 查看进程列表

3、explain分析低效sql执行计划

确定问题，采取相应的应对措施

1、建索引 handler_read_key

2、 定期分析表 analyze table； check table；checksum table；

3、使用optimize table

4、大批量插入数据时，关闭唯一性校验，插入后再打开；插入数据按主键顺序排列；关闭自动提交

5、group by时禁止排序（order by null）

6、设置操作的优先级

7、生成临时结果集（select sql_buffer_results ...）

8、通过拆分，提高表的访问效率

9、某些情况下需要考虑逆范式

10、使用冗余统计表

11、使用连接池

12、减少对mysql的访问

关于索引：

查看索引：

show index from test;

创建主键：

alter table test add primary key(id);

创建一般索引：

alter table test add index index_name(id);

创建唯一索引：

alter table test add unique(id);

创建多列索引：

alter table test add index index_name(id,name,age);

删除主键索引：

alter table test drop index key id;

删除一般索引：

drop index index_name on test;

或者

alter table test drop index index_name ;

删除外键唯一索引：

alter table test drop foreign key index_name；

sql语句积累

1、

左联接：left join on 返回包括左表中的所有记录和右表中联结字段相等的记录 

右联接)：right join on 返回包括右表中的所有记录和左表中联结字段相等的记录

等值连接：inner join on 只返回两个表中联结字段相等的行

2、查询库中所有表名：

select table_name from information_schema.tables where TABLE_SCHEMA = '库名'

3、查询表字段信息

select * from information_schema.columns where column_name = 'brand_id';

4、字段更新

例如：将字段A1501更新为a1501：

UPDATE 表名 SET 字段名 = REPLACE(字段名,'A','a')

5、exists 和 in ：

这条语句适用于a表比b表大的情况

select * from table_a where id in(select id from table_b);

这条语句适用于b表比a表大的情况

select * from table_a where id exists(select id from table_b where a.cat_id = b.cat_id);

6、联合查询

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。 如果允许重复的值，请使用 UNION ALL。

(select a.mc,a.exits,b.notExits from

(select 'rong' mc,count(1)exits from area )a,

(select 'rong' mc,count(1)notExits from areainfo )b)

union

(select a.mc,a.exits,b.notExits from

(select 'baidu' mc,count(1)exits from cachedata.etl_b_baidu )a,

(select 'baidu' mc,count(1)notExits from cachedata.etl_b_breaklaw)b)

7、查询重复数据条数：

select count(*),l.detailurl from rongdetailurl l inner join rongproduct p on l.detailurl = p.detailurl group by 2 having count(*)>1;

8、复制表：

create table abc select * from spi where substr(code,1,2) in (33,34,36)

insert into abc select * from spi where code like '36%'

9、 SUBSTR函数是用来截取数据库某一列字段中的一部分

substr(字段 from pos for len)

substring_index(str,delim,count)

select substring_index(msgtail,'_',1) as type,
substring_index(substring_index(msgtail,'_',2),'_',-1) as source,
substring_index(substring_index(msgtail,'_',4),'_',-1) as logContent,
dated
 from log4j
where levele='info'and msgtail like '%\_%' and Message like '%com.audaque.task.spider%'

存储过程

1、

begin 
(select now() as 查询时间, a.c as 百度成功抓取, b.c as 百度不存在, 
c.cc as 裁判成功抓取, d.c as 裁判不存在,e.c as 失信成功抓取, f.c as 失信不存在,
g.c as 招聘成功抓取, h.c as 招聘不存在,i.c as 11315成功抓取, j.c as 11315不存在
from 
(select  count(1) as c  from cachedata.etl_b_baidudetail) a,
(select  count(1) as c from cachedata.etl_b_baiduisnull)b,
(select count(1) as cc from cachedata.etl_b_cpwslist)c,
(select count(1) as c from cachedata.etl_b_ncpws)d, 
(select count(1) as c from cachedata.etl_b_dishonesty_all)e, 
(select count(1) as c from cachedata.dishonestyisnull)f,  
(select count(1) as c from cachedata.etl_b_recruit)g,
(select count(1) as c from cachedata.spi_u_recruitInfoNotexist)h, 
(select count(1) as c from cachedata.etl_b_baseinfo_11315)i,  
(select count(1) as c from cachedata.etl_b_11315isnull)j); 
end

2、

begin 
select a.gettime as 抓取时间,a.counts as 抓取成功,b.counts as 公司不存在 from 
(select 
count(1) as counts,SUBSTR(spiderTime,1,13) as gettime from cachedata.etl_b_dishonesty_all where 
date_format(spiderTime,'%Y-%m%-%d') = tjsj group by gettime) a 
left join 
(select 
count(1) as counts,SUBSTR(spiderTime,1,13) as gettime from cachedata.spi_u_dishonestyisnull where
 date_format(spiderTime,'%Y-%m%-%d') = tjsj  group by gettime) b
 on a.gettime = b.gettime
union 
select b.gettime as 抓取时间,a.counts as 抓取成功,b.counts as 公司不存在 from 
(select 
count(1) as counts,SUBSTR(spiderTime,1,13) as gettime from cachedata.etl_b_dishonesty_all where 
date_format(spiderTime,'%Y-%m%-%d') = tjsj group by gettime) a 
right join 
(select 
count(1) as counts,SUBSTR(spiderTime,1,13) as gettime from cachedata.spi_u_dishonestyisnull where 
date_format(spiderTime,'%Y-%m%-%d') = tjsj group by gettime) b
 on a.gettime = b.gettime;
end