思路清晰的MySQL常用语句进阶

数据库中插入随机数

update city set score=FLOOR(60 + (RAND() * 40)) where id < 6000

FLOOR(60 + (RAND() * 40)) 表示随机生成60到99的数字（不是60到100的数字，表示从60开始，总共40个数字，即最后一个为99）

删除表中重复的数据且仅保留一条
注：为了演示SQL语句下面以product表为例，删除重复name字段。

DELETE FROM product WHERE name IN
( SELECT * FROM ( SELECT name FROM product GROUP BY name HAVING count( name ) > 1 ) a )
AND id NOT IN
( SELECT * FROM ( SELECT min( id ) AS id FROM product GROUP BY name HAVING count( name ) > 1 ) b )

对于这个sql语句拆分开来看

– 查找所有name相同的数据
SELECT name FROM product GROUP BY name HAVING count( name ) > 1
– 查找表中相同name中id最小的数据
SELECT min( id ) AS id FROM product GROUP BY name HAVING count( name ) > 1

因为在mysql中不能够同时对单表进行查询和更改操作，所以加入了 ( SELECT * FROM ( 条件 ) a ) 这种格式进行整合。
而在Oracle中看起来就会比较清晰（如下）

DELETE FROM product WHERE name IN
( SELECT name FROM product GROUP BY name HAVING count( name ) > 1 )
AND id NOT IN
( SELECT min( id ) AS id FROM product GROUP BY name HAVING count( name ) > 1 )

当查找数值返回结果可能为null时报BindingException异常
注：使用student表单，score和name字段演示

SELECT IF(AVG(score) IS NULL,0, score) as score From student where name = ‘张三’

使用SELECT IF(AVG(字段) IS NULL,0, 字段) as 重命名 From xxx 可以在返回结果可能为null的情况下返回数字0，避免不必要的错误出现。

查询分值排名前n的数据
注：student表单中的score字段查询

– 返回成绩排名前两名学生成绩
SELECT score FROM student s1
WHERE ( SELECT count( 1 ) FROM student s2 WHERE s1.score < s2.score )< 2
ORDER BY score DESC

除法函数
注：student表单中对应classclass班级分值不超过46的占比

– 计算结果以%形式返回并且保留一位小数（如：23.1%）
SELECT CONVERT ( ((select count(id) from student where class=23 and
(select * from (select score from student where class=23 and score = 46) a) > score ) / count( id )), DECIMAL ( 15, 1 ), “%”) as score
FROM student WHERE class=23
– 计算结果以小数形式返回并且保留3位小数（如：0.231）
SELECT CONVERT ( ((select count(id) from student where class=23 and
(select * from (select score from student where class=23 and score = 46) a) > score ) / count( id )), DECIMAL ( 15, 1 )) as score FROM student WHERE class=23

查询结果乱序显示

使用rand（）对排序结果打乱显示
select id from city where id < 20 order by rand()

近期遇到的高频之前没有用过的SQL语句整理出来，希望能够帮到需要帮助的小伙伴！！！