MySQL查询技巧与陷阱-优快云博客

1.查询每个班级的前三名

DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

 
insert into sc values (1,'badkano','一年一班',100);
insert into sc values (2,'百度知道团长','一年一班',99);
insert into sc values (3,'du小短','一年一班',95);
insert into sc values (4,'du小小动','一年一班',97);
insert into sc values (5,'du小智','一年一班',80);
insert into sc values (6,'吕布','一年二班',67);
insert into sc values (7,'赵云','一年二班',90);
insert into sc values (8,'典韦','一年二班',89);
insert into sc values (9,'关羽','一年二班',70);
insert into sc values (10,'马超','一年二班',98);

result:

select 
    * 
from sc t
where 
    (select count(1)+1 from sc where class=t.class and score>t.score)
    <= 3

SELECT *
FROM sc a
WHERE 3 > (
SELECT COUNT( * )
FROM sc
WHERE class = a.class
AND score > a.score )
ORDER BY a.class , a.score DESC

2.关于null的查询

查询：

select * from t_category where description<>'325'

select * from t_category where description='325'

这两条查询都只有一条记录，并没有查询出为null的记录

select sum(user_id) as ids from t_category where description='aaaaa'

结果不是为0，是null

3.mysql中逗号字符串与数字比较

       select 5=5;  -- true
        
        select cast(5 as char(20))=5;  -- true
        
        select cast(55 as char(20))=5;  -- false
        
        select cast('5,2' as char(20))=5;  -- true
        
        select '5,2,4'=5; -- true
        
        select 5='5,4';   -- true
        
        select 5='5^4';  -- true

        select 5='5|4';  -- true
            
        select 5='5.4';  -- false

结果莫名其妙，除了点号以外，其他符号间隔的第一个符号与数字匹配就为true,看不懂，以后遇到要注意。

4.mysql中find_in_set使用

        select find_in_set('b','a,b,c,d,e,f');   -- 2
        
        select find_in_set('d','a,b,c,d,e,f');   -- 4

mysql字符串函数 find_in_set(str1,str2)函数是返回str2中str1所在的位置索引，str2必须以","分割开。

like是广泛的模糊匹配，字符串中没有分隔符，Find_IN_SET 是精确匹配，字段值以英文”,”分隔，Find_IN_SET查询的结果要小于like查询的结果。

https://zhidao.baidu.com/question/117048828.html

https://blog.youkuaiyun.com/zoujian1993/article/details/48243139

http://www.manongjc.com/article/1081.html

https://my.oschina.net/u/1032146/blog/149300

Mysql相关问题收集