前几天参加IT面试,里面提到mysql语句的编写,今天就在此对mysql进行温习,说的有不足的地方希望大家不吝赐教。参考链接。

简单数据库如下,有四张表 S(S#,SNAME,AGE,SEX),SC(S#,C#,score),C(C#,CNAME,T#), T(T#,TNAME,TITLE);

mysql里解析html标签_mysql里解析html标签

mysql里解析html标签_mysql_02

mysql里解析html标签_聚合函数_03

mysql里解析html标签_并集_04

首先要了解聚合函数和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;

mysql里解析html标签_mysql里解析html标签_05

#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;

mysql里解析html标签_sql_06

#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;

mysql里解析html标签_聚合函数_07

#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;

mysql里解析html标签_mysql_08

#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='马老师');

却没有得到想要的结果:

mysql里解析html标签_并集_09

解析:为什么会出现这种结果呢?  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='马老师');

mysql里解析html标签_聚合函数_10

#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');

mysql里解析html标签_sql_11

#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里解析html标签_sql_12

 

以上就是mysql常见的sql语句,若有不足的地方希望大家指教,若有不对的地方请指出,嘴下留情,谢谢大家。