mysql数据库sql语句集锦

心已赠人 2024-02-18 16:41 47阅读 0赞

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、提高数据库性能的配置，启用懒加载：

在mybatis-config.xml中配置

详见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;