较复杂的数据库查询语句总结
在项⽬开发中遇到⽐较多的是多表连接查询所需要的较复杂查询语句，这⾥总结了⼀些⽤的⽐较多的查询语句的⽰例：
1.使⽤ like 关键字进⾏模糊查询，使⽤%%将要模糊查询的字符包裹：
⽰例：select * from student where name like %张%
2.使⽤ distinct 关键字来去重查询，将重复记录合并：
⽰例：select distinct age from student
3.使⽤ group by 关键字进⾏分组查询，常⽤作统计数据使⽤：
⽰例：select sex,count(*) from student group by sex
4.使⽤ group by 关键字进⾏分组查询，常⽤作统计数据使⽤，也可以多列分组查询：
⽰例1：select sex,count(*) from student group by sex
⽰例2：select sex,count(*) from student group by sex,age
5.使⽤ having关键字对分组查询结果进⾏帅选，也可以加上where进⾏筛选，where要放在前⾯：
⽰例：select sex,count(*) from student group by sex having count(*) > 3
⽰例：Select clzid from student where clzid > 1 group by clzid
6.使⽤inner join关键字内连接查询两表共有的部分：
⽰例：Select * from student inner join clazz on student.clzid = clazz.id
⽰例：Select * from student,clazz where student.clzid = clazz.id
7.使⽤left join左连接，先把左边的表全部查询出来，再查出来右表共同的部分数据：
⽰例：Select * from student left join clazz on student.clzid = clazz.id
⽰例：Select * from student left join clazz on student.clzid = clazz.id where clazz.cid is null
8.使⽤right join左连接查出左表除共同部分以外的数据，右表数据为空：
⽰例：Select * from student right join clazz on student.clzid = clazz.id
⽰例：Select * from student right join clazz on student.clzid = clazz.id where clazz.cid is null多表left join
9.多表联合查询第三⾏语句就是对前两张表查询完的⼀个结果进⾏左连接：
⽰例：Select * from student
inner join clazz on student.clzid = clazz.id
Left join subject on student.id = subject.id
10.⼦查询：把⼀张表的查询结果当成⼀个条件：
⽰例：Select * from student where clzid = (Select cid from clazz where name =’王⽼师’)
⽰例：Select * from student where clzid in (select cid from clazz)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。