前几天参加IT面试,里面提到mysql语句的编写,今天就在此对mysql进行温习,说的有不足的地方希望大家不吝赐教。参考链接。
简单数据库如下,有四张表 S(S#,SNAME,AGE,SEX),SC(S#,C#,score),C(C#,CNAME,T#), T(T#,TNAME,TITLE);
首先要了解聚合函数和group by,having的概念和关系,首先百度百科对聚合函数的定义是:“SQL基本函数包括SUM(),MAX(),MIN(),COUNT(),AVG()等,聚合函数对一组值执行计算,并返回单个值,也被称为组函数。 聚合函数经常与 SELECT 语句的 GROUP BY 子句的HAVING一同使用。”因此聚合函数得到的是一个值,跟group by 分组取得一列或单个值的结果一直,having 相当于 where 对group by 进行条件限制。因此聚合函数多于group by配合。
#1,查询每个老师的课程平均分 (对老师的姓名进行分组,对SC表的score进行聚合求值,连接 T和 C,C表再连接 SC)
SELECT T.TNAME,AVG(SC.SCORE)AS '平均分' FROM T JOIN C ON T.`T#`=C.`T#` JOIN SC ON C.`C#`=SC.`C#` GROUP BY T.TNAME;
#2,查询每个课程的最高分,最低分,平均分(对课程名进行分组,只需要连接2张表就可)
SELECT C.CNAME,MAX(SC.SCORE) AS '最高分',MIN(sc.SCORE) AS '最低分',AVG(sc.SCORE)AS'平均分' FROM c JOIN sc ON c.`C#`=sc.`C#` GROUP BY c.CNAME;
#3,查询平均分高于80分的同学的信息(对于#2只加了条件,用having判断即可)
SELECT s.*,AVG(sc.SCORE)AS'平均分' FROM s JOIN sc on s.`S#`=sc.`S#` GROUP BY s.SNAME HAVING AVG(sc.SCORE)>80;
#4,查询各学生都选了多少门课(用count根据学号计算行数,也可根据课程号,分数 注:在此用左连接LEFT JOIN(以s表为基准)把没修课程的同学也列举出来,用join则无法列举)
SELECT s.SNAME,COUNT(sc.`S#`) FROM s LEFT JOIN sc ON s.`S#`=sc.`S#` GROUP BY s.SNAME;
#5,查询没有选‘马老师’课的同学的信息
一开始我是使用这种方法编写 :
SELECT s.*
FROM s JOIN sc ON s.`S#`=sc.`S#`
JOIN c ON sc.`C#`=c.`C#`
JOIN t ON c.`T#`=t.`T#`
WHERE t.`T#`NOT IN (SELECT t.`T#` FROM t WHERE t.TNAME='马老师');
却没有得到想要的结果:
解析:为什么会出现这种结果呢? SELECT t.`T#` FROM t WHERE t.TNAME='马老师'得到的T#是30,not in 把30排除,剩下的有 10,20,得到的课程号有1,2,3.因此小军选了 1,2,3有三门,因此出现三次。 因此不能子查询中查询T#,应该查询S#,在主查询中排除查询到的S#,这样才能得到正确的结果
SELECT s.*
FROM s WHERE s.`S#` not in (SELECT s.`S#`
FROM s JOIN sc ON s.`S#`=sc.`S#`
JOIN c ON sc.`C#`=c.`C#`
JOIN t ON c.`T#`=t.`T#` WHERE t.TNAME='马老师');
#6,查询既选了java或者选了c++的同学(并集,UNION 去不重复并集,UNION ALL 去重复并集)
(SELECT s.SNAME
FROM s JOIN sc ON s.`S#`=sc.`S#`
JOIN c on sc.`C#`=c.`C#`
WHERE c.CNAME='C++' )
UNION
(SELECT s.SNAME
FROM s JOIN sc ON s.`S#`=sc.`S#`
JOIN c on sc.`C#`=c.`C#`
WHERE c.CNAME='JAVA');
#7查询既选了java和选了c++的同学(交集,在此不能用INTERSECT,在Oracle可以直接使用,而mysql不能使用,因此使用INNER JOIN 代替)
SELECT a.* FROM (SELECT s.* FROM s JOIN sc ON s.`S#`=sc.`S#` JOIN c on sc.`C#`=c.`C#` WHERE c.CNAME='C++')AS a INNER JOIN
(SELECT s.* FROM s JOIN sc ON s.`S#`=sc.`S#` JOIN c on sc.`C#`=c.`C#` WHERE c.CNAME='JAVA')AS b WHERE a.SNAME=b.SNAME;
以上就是mysql常见的sql语句,若有不足的地方希望大家指教,若有不对的地方请指出,嘴下留情,谢谢大家。