工作中常用到的sql语句

--  将获取到的数据重新组合
-- INSERT into keyword(keyword) select keyword from keyword_copy1 GROUP BY keyword
-- INSERT into keyword(keyword) select keyword from infos GROUP BY keyword
-- INSERT into result7_info(keyword,title,info,createtime) select keyword,title,info,createtime  from result7_info_03_2

-- insert into coreched_infos_uni(keyword,title,info,createdtime)  select keyword,title,info,createdtime  from coreched_infos  GROUP BY title 



-- 查看重复性的数据 和 分组情况
-- select title,count(*) as count from 0910info group by title having count>1
-- select count(*) from infos
-- select id from (Select MIN(id) AS ID from 0910info Group By title) A
-- Select GROUP_CONCAT(id)    from hxjq_keyword_country_info_01 Group By title having count(*)>1


-- 查看有多少个关键词
-- select count(*) from (select *  from bgelateriagambrinus_infos  GROUP BY keyword ) as a
-- SELECT count(*) from coreched_infos_uni GROUP BY keyword

-- DELETE from bgelateriagambrinus_infos_00_09 where id <72
-- INSERT into infos(keyword,title,info,createdtime) VALUES(1,1,1,'2020-09-14 10:55:56')
-- show index from keyword
-- Select MAX(id) from 0910info Group By title having count(title)>1
select count(*) as num ,keyword from infos GROUP BY keyword HAVING num<2
--连表查询
select sum(a_table.cd) as 总数, sum(a_table.cc) as 部分 from(select count(1) as cd,0 as cc from kf_manager union all  SELECT 0,count(1) as cc  from kf_manager where qx='米易') a_table
-- 删除重复性的title
-- DELETE FROM  infos where id  not in ( select id from (Select MIN(id) AS ID from infos Group By title ) a  ) 
-- select count(*) as sum from infos
-- select id from (Select MIN(id) AS ID from infos Group By title) a
-- DELETE FROM  infos_copy2 where LENGTH(info)<30
-- Select MIN(id) from infos_copy2 Group By info having  COUNT(*)>1
-- Select * from infos_copy2 Group By keyword having  COUNT(info)>1
-- Select GROUP_CONCAT(id) from infos_copy2 Group By keyword having  COUNT(info)>1
-- Select GROUP_CONCAT(id) from infos_copy2 Group By keyword ,title having count(*)>1
-- select GROUP_CONCAT(id) from (select * from infos where keyword = '1 2 0mm zirkonia keramik mill ball in czechrepublic') a GROUP BY title having count(*) >1
-- select count(*) as sum from infos where info ='' or title =''
-- DELETE from infos where title ='' or info=''
-- 
DELETE from infos  where keyword in ( select keyword from ( SELECT keyword from infos  GROUP BY keyword HAVING count(*)<2 ) as a)
DELETE from keyword where keyword not in ( select keyword from ( SELECT keyword from infos  GROUP BY keyword ) as a)
-- TRUNCATE keyword
-- 
-- alter table info add index keyword(keyword)
-- alter table product_city_info_00 drop index keyword
-- show index from product_city_info_00

-- infos转换成keyword   工作的最后程序
-- INSERT into keyword(keyword) select keyword from infos GROUP BY keyword
-- INSERT into infos(keyword,title,info,createdtime) select keyword,title,info,createdtime as createdtime from coreched_infos
-- INSERT into infos(keyword,title,info) select keyword,title,info from keyword_copy1

-- 随机查询产品的列表
-- SELECT a.*,c.catdir,c.catname ,c.proname as catproname from product as a JOIN category as c on a.catid = c.id ORDER BY rand() limit 6

-- 判断是否含有中文符号
-- SELECT * FROM `keyword` WHERE `keyword` REGEXP  '[吖-座]' 
-- select GROUP_CONCAT(id) as str from infos where keyword='crusher mounted on excavator indian manufacturing company' GROUP BY keyword

-- 向category表中添加数据
-- insert into category(catname,catdir,moduleid,module) VALUES('blog','blogs',0,'blog')


-- 关键词有特殊符号查询语句
-- UPDATE infos set status =0 where status=1
-- TRUNCATE keyword
-- select COUNT(*) as num from infos where status=0

联合更新数据库
update infos  LEFT JOIN  keyword  b ON infos.keyword=b.keyword  set infos.keyword_id=b.id where infos.keyword_id=0 

select name,sum(score<60) as gk,sum(score) as scores from student GROUP BY name HAVING gk>=2 order by scores desc