1、mysql中插入一条数据时,条件排重。
SELECT #{netId,jdbcType=INTEGER}, #{parentId,jdbcType=INTEGER}, #{address,jdbcType=VARCHAR}
FROM DUAL
where not exists(select...(子查询->排重条件))
使用 dual 做表名可以让你在 select 语句后面直接跟上要插入字段的值,即使这些值还不存在当前表中。
2、按类型字段分组后,按时间字段取最大值
我以为直接用这个:
select * from 表名 group by 类型 having max(时间);
然而,查询结果并不是我想要的。
网上也有很多错误答案,不加思考就贴出来。
发现一个靠谱的思路:
即是,先分组,用max(时间)查询到最大时间和对应的类型字段的值,再inner join 原表。貌似可行。
写法如同这个(来自:mysql中的处理方式):
-- mysql中的处理方式
SELECT t.stuid,
t.stuname,
t.score,
t.classid
FROM stugrade t
where t.score = (SELECT max(tmp.score) from stugrade tmp where tmp.classid=t.classid)
但是后面再思考,如果分组后,同一组中的数据的时间字段的值如果有重复。那么后面inner join后,就会
出现多个主键不同的最大值数据。如果业务上允许的话。取其中一条。也是可行的。
如果是在oracle或者postgresql可以采用row_number() over(PARTITION BY [分组列] ORDER BY [排序列] ASC/DESC) AS [行号别名] 来处理。
SELECT
...
FROM(
SELECT temp1.* ,
"row_number"() OVER(PARTITION BY [分组列] ORDER BY [排序列] ASC/DESC ) AS "rowNo"
FROM (
...
) temp1
)temp2
WHERE temp2."rowNo"<2
此处还可参考:参考链接
3、mapper文件中的转义字符
mybatis 的映射文件(xxx.xml)中,书写sql语句时,不能出现"<"、">"等会被解析成标签符号的运算符号。这时需要将"<"、">"替换成xml的转义字符。
附:XML转义字符:
< | < | 小于号 |
> | > | 大于号 |
& | & | 和 |
' | ’ | 单引号 |
" | " | 双引号 |
第二种方法是使用<![CDATA[ ]]>符号进行说明,将此类符号不进行解析
你的可以写成这个:
<![CDATA[ when min(starttime)<='12:00' and max(endtime)<='12:00' ]]>
转自:
https://blog.csdn.net/zheng0518/article/details/10449549
4、mysql如何保存boolean类型字段
设置is_del字段类型为tinyInt(1),通过mybatis的逆向工程,生成的projo里面,对应属性isDel的
MySQL没有boolean类型。这也是比较奇怪的现象。例:
create table xs
(
id int primary key,
bl boolean
)
这样是可以创建成功,但查看一下建表后的语句,就会发现,mysql把它替换成tinyint(1)。也就是说mysql把boolean=tinyInt了。
boolean类型
MYSQL保存BOOLEAN值时用1代表TRUE,0代表FALSE,boolean在MySQL里的类型为tinyint(1),
MySQL里有四个常量:true,false,TRUE,FALSE,它们分别代表1,0,1,0
5、索引创建
创建索引:
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
删除索引:
DROP INDEX [indexName] ON mytable;
查看索引:
SHOW INDEX FROM table_name\G
有四种方式来添加数据表的索引:
-- 该语句添加了一个主键,这一位置索引值必须是唯一的,且不能为null
ALTER TABLE tbl_name ADD PRIMARY(column_list);
-- 这条语句创建索引的值必须是唯一的(除了null外,null可能会出现多次)
ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)
-- 添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name(column_list)
-- 该语句指定了索引为FULLTEXT,用于全文索引。
ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)
6、数据的批量更新
参考链接:https://blog.csdn.net/xyjawq1/article/details/74129316
实体类:
/**
* 文件
*
*/
public class RFile {
/**
* 主键
*/
private Long id;
/**
* 下载次数
*/
private Integer downloadCount;
...
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Integer getDownloadCount() {
return downloadCount;
}
public void setDownloadCount(Integer downloadCount) {
this.downloadCount = downloadCount;
}
...
}
mapper接口:
/**
* 批量更新下载次数
*
*/
public int updateDownloadCountByPrimaryKey(@Param("fileList") List<RFile> fileList);
mapper映射文件:
<update id="updateDownloadCountByPrimaryKey" parameterType="java.util.List">
update `r_file`
<trim prefix="set" suffixOverrides=",">
<trim prefix="download_count =case" suffix="end,">
<foreach collection="fileList" item="item" index="index">
<if test="item.downloadCount !=null">
when id=#{item.id} then #{item.downloadCount}
</if>
<if test="item.downloadCount == null or item.downloadCount < 0">
when id=#{item.id} then `r_file`.download_count <!-- 原数据 -->
</if>
</foreach>
</trim>
</trim>
where id in
<foreach collection="fileList" index="index" item="item" separator="," open="(" close=")">
#{item.id,jdbcType=BIGINT}
</foreach>
</update>
参考执行的sql:
update `r_file`
set download_count =
case when id=? then ?
when id=? then ?
when id=? then ?
when id=? then ?
end
where id in ( ? , ? , ? , ? )
7、case when 使用的一些小问题
参考链接:https://www.cnblogs.com/blogxiao/p/7600964.html
8、maven工程中遇到的问题
在maven工程中,在整合mybatis时,如果是maven命令启动,可能会报找不到mapper文件的错误,关键词:...not found ...statement...xxxMapper.XXX...
如果在确认了注解或者其它无误后依然报错。那么,可能是因为mapper.xml文件放到了src/main/java目录下,maven默认是将src/main/resource目录下的文件作为资源文件打包。src/main/java目录下的xml文件被忽略掉了。所以需要在pom文件中标识资源文件的目录。
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
参考链接:https://blog.csdn.net/biren_wang/article/details/78200805
9、group by遇到的问题
需求举例:
一张表示动物(猫)的表:
CREATE TABLE `cat` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`type` int(2) DEFAULT NULL,
`age` int(2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
type字段关联一张类型(颜色)表:
CREATE TABLE `cat_type` (
`id` int(12) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
分别插入测试数据:
cat:
cat_type:
要求统计查询出不同颜色的猫的个数,年龄段作为查询条件。没有为0。
第一次查询(条件:猫年龄>10):
SELECT
ct.`name` '颜色',
COUNT(cat.id) '数量'
FROM
cat_type ct
LEFT JOIN cat ON cat.type = ct.id
WHERE
cat.age > 10
GROUP BY
ct.`name`
结果:
不是想要的结果。
修改后查询:
SELECT
ct.`name` '颜色',
COUNT(temp_.id) '数量'
FROM
cat_type ct
LEFT JOIN (
SELECT * FROM cat WHERE cat.age > 10
) temp_ ON temp_.type = ct.id
GROUP BY
ct.`name`
结果:
第一次查询中,where条件筛选在join之后,而题意是想先条件过滤再进行分组统计。这里where筛掉了需要进行group by统计的数据。