MySql摘要

001、批量删除

public void deleteAll(List<Integer> list);//删除

 <delete id="deleteAll" >
         DELETE from qy_exchangepl  
	 WHERE exchangeplid in
	<foreach item="item" index="index" collection="list" 
                       open="(" separator="," close=")">
             #{item}
        </foreach>
     </delete>

 

002 、MySql日期函数

003、MySql 禁用和启用外键约束检查

-- SQL data bulk transfer script generated by the MySQL Migration Toolkit
-- ----------------------------------------------------------------------
-- Disable foreign key checks 禁用
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
-- Re-enable foreign key checks 启用
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
-- End of script

 005、

查询重复记录

/*查找重复记录
	指定重复字段
	按重复字段分组
	count重复字段值>1则重复*/
SELECT *
FROM allintersection b
WHERE b.`Name` 
IN(
	SELECT a.`Name`
	FROM allintersection a
	GROUP BY a.`Name`
	HAVING COUNT(a.`Name`)>1
)

 006、

删除重复记录

按规则删除重复记录	
DELETE FROM allintersection 
WHERE allintersection.ID 
IN(
  SELECT b.maxid
	FROM
	(SELECT MAX(a.ID) as maxid
	FROM allintersection a
	GROUP BY a.`Name`
	HAVING COUNT(a.`Name`)>1 ) b
)

 007、

关于having的几点说明

1、SQL标准要求HAVING必须引用GROUP BY子句中的列或用于总计函数中的列。
   不过，MySQL支持对此工作性质的扩展，并允许HAVING涉及SELECT清单中
   的列和外部子查询中的列。
2、HAVING子句必须位于GROUP BY之后ORDER BY之前。
3、如果HAVING子句引用了一个意义不明确的列，则会出现警告。在下面的语
   句中，col2意义不明确，因为它既作为别名使用，又作为列名使用：
   mysql> SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
   标准SQL工作性质具有优先权，因此如果一个HAVING列名既被用于GROUP BY，又被
   用作输出列清单中的起了别名的列，则优先权被给予GROUP BY列中的列。
4、HAVING子句可以引用总计函数，而WHERE子句不能引用。【这应该是开发者在特
   定的情况下采用HAVING子句的最大原因】
5、不要将HAVING用于应被用于WHERE子句的条目，从我们开头的2条语句来看，这样用
   并没有出错，但是mysql不推荐。而且也没有明确说明原因，但是既然它要求，我
   们遵循就可以了。

 008、按某一字段分组统计

例：qy_rb表--rblx日报类型字段(可能值0、1、2、3、4)，现依据rblx统计一段时间内所有人员发表不同类型日报的数量

 

效果图:

 

局部sql：

SELECT 
SUM(case when rb.rblx='0' then 1 else 0 end) AS rbcount,
SUM(IF (rb.rblx=1,1,0) )AS zbcount,
SUM(IF (rb.rblx=2,1,0)) AS ybcount,
SUM(IF (rb.rblx=3,1,0)) AS jxthcount,
SUM(IF (rb.rblx=4,1,0)) AS jxfscount,
rb.userid
FROM qy_rb	rb
WHERE DATE_FORMAT(rb.tjsj,'%Y-%m-%-d')>=DATE_FORMAT('2012-08-01','%Y-%m-%-d') and DATE_FORMAT(rb.tjsj,'%Y-%m-%-d')<=DATE_FORMAT('2012-12-31','%Y-%m-%-d')
GROUP BY rb.userid

完整sql：

SELECT qyuser.xm,
	IF(ISNULL(t_a.rbcount),0,t_a.rbcount) as rbcount,
	IF(ISNULL(t_a.zbcount),0,t_a.zbcount) as zbcount,
	IF(ISNULL(t_a.ybcount),0,t_a.ybcount) as ybcount,
	IF(ISNULL(t_a.jxthcount),0,t_a.jxthcount) as jxthcount,
	IF(ISNULL(t_a.jxfscount),0,t_a.jxfscount) as jxfscount,
	IF(ISNULL(t_b.excount),0,t_b.excount) as excount,
	bm.bmmc
FROM qy_user qyuser
LEFT JOIN
(SELECT 
SUM(case when rb.rblx='0' then 1 else 0 end) AS rbcount,
SUM(IF (rb.rblx=1,1,0) )AS zbcount,
SUM(IF (rb.rblx=2,1,0)) AS ybcount,
SUM(IF (rb.rblx=3,1,0)) AS jxthcount,
SUM(IF (rb.rblx=4,1,0)) AS jxfscount,
rb.userid
FROM qy_rb	rb
WHERE DATE_FORMAT(rb.tjsj,'%Y-%m-%-d')>=DATE_FORMAT('2012-08-01','%Y-%m-%-d') and DATE_FORMAT(rb.tjsj,'%Y-%m-%-d')<=DATE_FORMAT('2012-12-31','%Y-%m-%-d')
GROUP BY rb.userid) t_a on qyuser.userid=t_a.userid
LEFT JOIN 
(SELECT count(*) as excount,
qyex.userid
FROM qy_exchange qyex 
WHERE DATE_FORMAT(qyex.tjsj,'%Y-%m-%-d')>=DATE_FORMAT('2012-08-01','%Y-%m-%-d') and DATE_FORMAT(qyex.tjsj,'%Y-%m-%-d')<=DATE_FORMAT('2012-12-31','%Y-%m-%-d')
GROUP BY qyex.userid

)t_b ON qyuser.userid=t_b.userid
LEFT JOIN qy_bm bm on qyuser.bmid=bm.bmid
WHERE qyuser.dwid='001'
ORDER BY rbcount AND zbcount and ybcount AND  excount DESC

 

 00、case语句

##方式一：
	SELECT 
		case sal.sfaz
			when 1  then '是' 
			when 2  then '否'
		end sfaz
	FROM saleorder sal
##	方式二：
	SELECT 
		case 
			when sal.sfaz=1  then '是' 
			when sal.sfaz=2  then '否'
		end sfaz
	FROM saleorder sal
##
SELECT 
	CASE
		WHEN a.user_name=null then 'no'
		WHEN a.user_name='' THEN 'no'
		ELSE a.user_name
	END as user_name
FROM game_user a

 

 00、if语句

SELECT 
		IF(sal.sfaz=1,'是','否') as sfaz
	FROM saleorder sal

00、mysql: 将记录值转换为字段统计(成绩表)

/*
select coalesce(a,b,c);  
如果a==null,则选择b；如果b==null,则选择c；如果a!=null,则选择a；
如果a b c 都为null ，则返回为null（没意义）。
*/
SELECT a.username,
    (SELECT COALESCE(t.fenshu,0) from cores t WHERE t.cursorse='数学' and t.username=a.username) as 数学,
    (SELECT IFNULL(t.fenshu,0) from cores t WHERE t.cursorse='语文' and t.username=a.username) as 语文,
   (SELECT IF(ISNULL(t.fenshu),0,t.fenshu) from cores t WHERE t.cursorse='英语' and t.username=a.username) as 英语    
FROM cores a
group by a.username