Mysql知识点-日常更新
一、left join 中on和where条件过滤介绍
简介:数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。 在使用left jion时,on和where条件的区别如下:
1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
二、limit1,1 和limit 1 offset 1区别
limit begin,number:从(包含)begin数据开始查询number条数据。
limit number offset begin:从begin后一条数据开始查询number条数据。
三、IFNULL(column,null)
如果查询的字段为空可以设置成null当然你也可以设置成0或者其他的信息。
四、根据数值字段进行排序,可并列但是要连续
sql语句:
select s.Score,
(select count(distinct Score) from Scores where Score>=s.Score) as Rank from Scores s
order by s.Score desc
解释:通过cout(distinct score)来计算比Score大于等于的个数对应的也就是名次,因为order by Score排序了。
五、mybatis
@Deprecated
@Select("select id,mockexam_section as section,id as sectionId"
+ " from t_p_qb_mockexam_section"
+ " where mockexam_charpter_id = #{charpterId} and is_delete = 0"
+ " order by mockexam_section_idx asc")
@Results({
@Result(property = "questionList",column = "sectionId",
many = @Many(select = "com.zikaoshu.baseinfo.mapper.BaseinfoQuestionMapper.listQuestionResDto"))
})
List<SectionQuestionDto> listSectionQuestionDto(@Param("charpterId") Integer charpterId);
/**
* 根据模考章节部分id查询该章节下面所有试题
*/
@Deprecated
@Select("select id,type,discuss_title as discussTitle,stem1,material,a,b,c,d,e,answer,analysis,mockeaxm_section_id as sectionId"
+ " from t_p_qb_question_mockexam"
+ " where mockeaxm_section_id = #{id} and is_delete = 0"
+ " order by q_sequence,gmt_create asc")
List<QuestionResDto> listQuestionResDto(@Param("id") Integer id);
六、根据A表字段判断是查询B表还是C表
SELECT a.id, a.type, b.question , c.content
FROM table_a as a
LEFT JOIN table_b as b on a.id= b.aId and a.type = 2
LEFT JOIN table_c as c on a.id= c.aId and a.type = 3
七、MySQL表根据一个或多个字段去重
// 根据以下SQL查询出来重复的数据,然后手动全选手动删除
select * from tableA a
where (a.col1,a.col2,a.col3,a.col4) in (select col1,col2,col3,col4 from tableA group by col1,col2,col3,col4 having count(*) > 1)
and id not in (select min(id) from tableA group by col1,col2,col3,col4 having count(*)>1)
八、MAX(case when end) 将行转为列_2020-11-06
我要查询每个name对应的course的最高score。
select name, MAX(
CASE when course='语文' then score end
) as '语文',
MAX(
CASE when course='数学' then score end
) as '数学',
MAX(
CASE when course='英语' then score end
) as '英语'
from c_score GROUP BY name;
九、聚合函数和group by的使用
select a,MAX(b) from table where a in (#value) group by a;