1.查询某个表的某个字段批量新增到另外一个表:
INSERT INTO insertTable (var1,var2,var3,create_time,var4)select field1,field2,constant1,1,now(),NOW(),constant2 from selectTalble;
2.删除名字重复的,如果重复保留id最小的
delete from table where id not in (select minid from (select min(id) as minid from table group by name) b);
3.查看某个数据库的所有数据量
SELECT TABLE_NAME, TABLE_ROWS FROM `information_schema`.`tables` WHERE `table_schema` = 'databaseName'
4.判断某个列是否重复
select col,count(*) as ct from table GROUP BY col HAVING ct>1;
5.字符串拼接用concat
concat('code201803190000',id)
6.需经常查询的字段或关联性比较多的,或区分比较大的字段,经常用的字段推荐建索引.如果ABC经常一起使用,特别是A是必须有的情况下可以建组合索引
7.sql语句关联进来走有索引的字关联,对于数据量大的尽量缩小查询范围,where后面直接跟范围筛选最多,有索引的
8.mybaits中,resultMap一般对应自定义实体类,
resultType对应常用引用类或包装类,如:Integer,Double,String,HashMap,
jdbcType对应的类型是sql的类型,如VARCHAR,TIMESTAMP,BIT,INTEGER都是大写,(而不是实体类里的Date,String)
9.常用的mysql关键字:curdate(),date_add,now(),ifnull(),concat,sum,count,round,max,min
12.对于经常用的且不易改变的,比如字典,可以放入redis缓存
13:多个选择:choose-when-otherwise用于查询条件选择,case when-then-else-end用于字段参数选择
<choose>
<when test='Field == "codeDESC"'>
order by code desc
</when>
<otherwise>
ORDER By name desc
</otherwise>
</choose>
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
14.删除主从表,特别唯一关联的是主表某个唯一字段,
DELETE s.*, main.* FROM sub s
INNER JOIN main m ON s.m_id= m.id
WHERE code=#{code};
DELETE from main WHERE code=#{code};
15.两表更新:
update main m,main_copy mc set m.code=mc.code where m.id =mc.id
16.批量查询list用in或exist
<foreach collection="list" item="item" separator="," index="index" open="(" close=")">
#{item}
</foreach>
17批量新增list
insert into table
(XXXXX,YYYY)
values
<foreach collection="list" item="item" separator="," >
(
#{item.xxxxx},#{item.yyyy}
)
</foreach>
18批量更新list
<foreach collection="list" item="item" separator=";" index="index" open="" close="">
update table XXXXX
</foreach>
19.表复制:
INSERT INTO table1(id,name,age) SELECT id,name,age FROM table2 where XXXXX
20.去除表数据中空格
update table set col=replace(col,' ','');//清除news表中content字段中的空格
21.查询表名
select table_name from information_schema.tables where table_name like'contain%'
22.有就更新,无则新增,需要一个唯一索引
REPLACE INTO table(x,y)values(1,2)
22.新增忽略重复数据
insert IGNORE into XXXX
23.Mybatis select返回多个list,但为啥只有一条记录
在之前的select后面加上主键id
<id column="id" jdbcType="INTEGER" property="id" />
<collection property="historyDiseaseList" ofType="com.domain.enty">
<result column="disease_id" jdbcType="INTEGER" property="diseaseId" />
<result column="time" jdbcType="TIMESTAMP" property="time" />
</collection>
association的几个属性:
property:指定内部对象属性名
javaType:内部映射的对象的类型。
column:要传给select语句的参数,相当于指定外键字段。
select:指定用户查询语句的ID
24.匹配某个字段全部为数字:
regexp '^[0-9]+$'
25.去除最右边的空格:
UPDATE `base_user` SET name = rtrim(name ) WHERE name like '% '
26.4个8:
length(order_num)-length(replace(order_num,'8','')) = 4
27.包含中文:
where length(name) !=char_length(name)
28.查询某个字段包含大写字母
SELECT * FROM table WHERE col REGEXP BINARY '[A-Z]';
BINARY 强制区分大小写,REGEXP 正则表达式
大小写转换:
lower(), upper() 来实现MySQL转换字符串大小写,因为这和其他数据库中函数相兼容
update table set col=LOWER(col) where id=xxx
29.sql语句不能有多个空格多个,可能会识别不到
30.查询右斜杠并替换为左斜杠
select * from table where col like '%\\\%';
替换为左斜杠
update table set col=replace(col,'\\','/') where col like '%\\\%'
31.年龄计算并更新
update table set age= TIMESTAMPDIFF(YEAR, birthday, CURDATE()) where age is null
32.查询最近一条
select t.group_col, t.create_time from table1 t
inner join (select group_col, max(create_time) 'create_time' from table1
group by group_col) maxt on t.group_col = maxt.group_col and t.create_time = maxt.create_time
33.查询倒数第二的数据
SELECT * FROM table order by id desc limit 1,1
34.mysql赋权
本机:LNMP、LAMP环境数据库授权
GRANT all privileges ON ‘test.*’ TO ‘solin’@’localhost’;
应用服务器和数据库服务器不在一个主机上的授权
GRANT all privileges ON ‘test.*’ TO ‘solin’@’192.168.1.%;
严格的授权:重视安全,忽略了方便:
GRANT SELECT,INSERT,UPDATE,DELETE ON ‘test.*’ to ‘solin’@’192.168.1.%’;
(2)生产环境从库(只读)用户的授权:
CRANT SELECT ON ‘test.*’ TO ‘solin’@’192.168.1.%’;
35.查看mysql版本:select version();
36.中文全文检索:mysql版本MySQL 5.7.6以上
vi /etc/my.cnf
增加如下配置,之前的配置别删除,是增加:
[mysqld]
ft_min_word_len = 2
ngram_token_size=2
重启mysql:/etc/init.d/mysql restart
建立全文索引:
ALTER TABLE table ADD FULLTEXT INDEX ft_index (col1,col2,col3,col4) WITH PARSER ngram;
连接符,排除null:CONCAT_WS(separator,str1,str2,...)
CONCAT_WS(',',str1,str2)