1、删除数据库里字段内容相同的记录且只保存一条:
delete from 表名 where (唯一标识字段 not in
(select 唯一标识字段 from (SELECT 唯一标识字段 FROM 表名 GROUP BY USER_NAME ) s) )
and REGISTER_TIME<='2017-02-16 21:32:19'
2、删除sql语句写法:
1
int delRoleMenuByRoleIds(List<String> roleIds);
1
<delete id="delRoleMenuByRoleIds">
2
DELETE FROM SYS_ROLE_MENU where SYS_ROLE_ID IN
3
<foreach collection="list" item="item" open="(" close=")" separator=",">
4
#{item}
5
</foreach>
6
</delete>
3、查询每个类型的前三条记录:
1
SELECT * FROM topic AS T
2
WHERE 3>(SELECT COUNT(*) topic_id FROM topic WHERE user_Id=T.userId AND id>T.id)
4、改表sql语句写法:
1
-- 表test的name字段后面添加一个字段CITY_CODE
2
3
ALTER TABLE `test` ADD COLUMN `CITY_CODE` VARCHAR(20) COMMENT '城市编码' AFTER NAME;
5、获取日期里的年月日时分秒:
1
SELECT YEAR(CURRENT_DATE);
2
--单独获取年
6、更改表字段类型:
1
alter table ADS_PERIODICATION modify `IS_SALE` tinyint
2
3
alter table 表名 modify 字段名 字段类型
7、给表某字段后面再添加列:
1
ALTER TABLE `ADS_PERIODICATION` ADD COLUMN `IS_SALE` tinyint NULL
2
AFTER `AGENCY_ID`;
8、插入sql语句的写法:
1
<insert id="insertAppUser" parameterType="cn.net.cnp.framework.pojo.AppUser">
2
</insert>
3
注:有几个字段写几个字段,可以不用写全
9、获取一周前的日期:
1
SELECT DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
10、查找字段里是否包含某值(ableApplication存的值里是否包含app这三个字符):
1
"select ut.* from ycb_mcs_user_t ut ,ycb_mcs_t t where t.id=ut.tid and FIND_IN_SET('app',t.ableApplication) and uid=#{id} limit 1") (
2
UserT isFirstGetApp( ("id") Long id);