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