mysql数据库sql语句集锦

1、mysql查询某个值出现次数超过一定频率的sql语句，很明显，看到这样的字眼，马上想起关键字"having"，用于数据筛选：

SELECT * from tb_ecg WHERE usr_id in( SELECT `usr_id` FROM tb_ecg group by usr_id HAVING COUNT(`usr_id`)>10);

以上sql语句用于查询 tb_ecg表中，哪些用户的数据超过10次。

2、提高数据库性能的配置，启用懒加载：

  <!--lazyLoadingEnabled：设置懒加载，默认为false。如果为false：则所有相关联的都会被初始化加载。
     aggressiveLazyLoading：默认为true。当设置为true时，懒加载的对象可能被任何懒属性全部加载；否则，每个属性按需加载。 -->

在mybatis-config.xml中配置

<settings>
    <!-- 打开延迟加载的开关 -->
    <setting name="lazyLoadingEnabled" value="true" />
    <!-- 将积极加载改为消息加载即按需加载 -->
    <setting name="aggressiveLazyLoading" value="false" />
</settings>

详见https://www.cnblogs.com/zhangzongle/p/6210407.html

mysql给已存在的表添加多个字段：

<span style="color:#4f4f4f">ALTER TABLE ytj1012 ADD duns varchar(36) not null, ADD created_By varchar(64) not null, ADD created_Date datetime not null,ADD updated_By varchar(64),ADD updated_Date datetime,</span>

ADD dataResType varchar(100)not null,ADD sSupplierCode varchar(10)not null,ADD sMachineCode varchar(100)not null,ADD IsSuccess varchar(36),ADD uploadTime datetime,ADD errReason text;

将某列的值赋给同一表的另一列

update table tb1 set tb1.ddsl = (select tb2.sl from table tb2 where tb2.id = tb1.id);

mysql除法运算，四舍五入后保留2位小数：

UPDATE report set ua=ROUND(ua/16.81,2) WHERE ua is not null and ua>1;

分组查询并统计每组的数量：

SELECT idcard_no,count(*) AS counts from tb_ecg WHERE idcard_no like 'VM%' GROUP BY idcard_no ORDER BY counts DESC;

嵌套查询、子查询

select jg,b.`NAME`,b.ADDRESS,b.CREATED_BY,cou from (select count(*) AS cou,duns AS jg from `tb_nibp` group by duns ORDER BY cou DESC LIMIT 11) as a LEFT JOIN tb_jigou b on a.jg=b.DUNS;

将查询结果更新到指定字段：

update tb_ecg ecg left join test_replace rep on ecg.idcard_no=rep.patient_id set ecg.idcard_no = rep.id_card WHERE ecg.idcard_no like 'VM%' AND rep.id_card is not NULL;