MYSQL之——复杂查询

查找某个字段的特定位置有特定的字符

方式一：select * from `userinfo` where substring(`describe`,6,2)='美貌';　　//_匹配一个字符，那么n个下划线指定n个位置之后出现某个字符，%匹配任意一个字符,维护和阅读非常困难。

方式二：select * from `userinfo` where `describe` like '_____美貌%';　　//使用substring(`字段`,截取开始的位置，截取长度)获取结果中的子串，然后再进行匹配。

 再使用explain关键字查看SQL语句的性能

笛卡尔积：多表一一进行组合

如：select * from `teacher`,`student`;

内连接（笛卡尔积）可以直接并列多张表完成或者使用inner join 关键字表示内连接。限制条件用where添加，更规范的是使用on来加上连接条件。

如：select * from `teacher` inner join`course` where teacher.tid=course.tid

　　select * from `teacher` inner join`course` on teacher.tid=course.tid

左连接：select * from `teacher` left join`course` on teacher.tid=course.tid

右连接：select * from `teacher` right join`course` on teacher.tid=course.tid

 

聚合（统计）函数：avg sum count max min
聚合函数，需要指定统计的范围，如果不使用group by，就以整个表中的所有数据进行统计。

group by 表示按照某个字段进行分组，在使用的时候，如果不是统计结果，那么会显示各组第一条数据的内容。
如果查询非统计结果，会出现only_full_group_by的报错。
这时是因为sql的查询模式限制，必须使用强校验的group by，去修改my.cnf或者my.ini里面的sql_mode参数，去掉only_full_group_by，或者直接添加sql_mode='STRICT_TRANS_TABLES'。
可以在数据库中用 show variables like '%sql_mode%'进行查看。

 

查询男女学员的平均成绩

1）查询所有成员的平均成绩

如：select avg(`score`) FROM `student` LEFT JOIN `score` on student.sid=score.sid;

2）对学生性别进行分组

如：select avg(`score`),ssex FROM `student` LEFT JOIN `score` on student.sid=score.sid GROUP BY student.ssex;

 

查询每位学员的平均成绩

如：select SUM(score/4),sname FROM `student` LEFT JOIN `score` on student.sid=score.sid GROUP BY student.sname;

 

查询所有同学的学号，姓名，选课数，总成绩

如：select student.sid as '学号',student.sname as '姓名',count(score.cid) as '选课数',sum(score) as '总成绩' from student LEFT JOIN score on student.sid=score.sid GROUP BY student.sid;

 

查询每一门课程的任课老师，选课学员数，以及平均成绩，平均年龄

如：

select
course.cid as '课程号',
course.cname as '课程名',
teacher.tname as '任课老师',
count(score.sid) as '学员数',
avg(student.sage) as '平均年龄',
avg(score.score) as '平均成绩'
FROM
course
LEFT JOIN teacher on teacher.tid=course.tid
LEFT JOIN score on course.cid=score.cid
LEFT JOIN student on score.sid=student.sid
GROUP BY course.cid

 

查询没门课程的平均成绩（子查询）

如：

select
*
FROM
(
select score.cid,avg(score.score) as avgscore FROM score GROUP BY score.cid
)
as avgscore
RIGHT JOIN course on course.cid=avgscore.cid;