mysql知识点

mysql

用 @rownum := @rownum+1 实现 rownum 功能

SELECT @rownum := @rownum+1 AS rownum, 
               table1.* 
  FROM (SELECT @rownum:=0) r ，
       table1;

分组排序取样

按分组取最小的两个(N个)val

select a.* from tb a where
2 > (select count(1) from tb where 
name = a.name  /*按哪些分组就哪些相等*/
and val < a.val ) /*找个分组内大部分不相等的来做比较*/
order by a.name,a.val

select a.* from tb a where exists 
(select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) 
order by a.name

根据注释来查询表名：

Select table_name 表名,TABLE_COMMENT '媒资' from
INFORMATION_SCHEMA.TABLES Where  TABLE_COMMENT  LIKE '%媒资%';

HAVING子句示例

1、查询重复的行

select id,name from student where name in (select name from student 
group by name having (count(*) > 1)) order by name;

查询student表中重名的学生，结果包含id和name，按name,id升序

2、查询分组中特定要求的行

select sid,avg(score) as avg_score from student_course group by sid 
having (avg_score < 60);

在student_course表中查询平均分不及格的学生，列出学生id和平均分

3、显示每个地区的总人口数和总面积．仅显示那些面积超过1000000的地区。

select region,sum(population),sum(area) from china group by region 
having (sum(area) > 1000000);

强制索引使用

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 
ORDER BY id DESC;