mysql--sql练习2（子句、函数、join技术的基础使用）

衔接上一篇sql练习，同样使用创建的四张表格。
在这篇文章中，使用了子句，函数，和join技术等，完成对数据的复杂与较为精准的操作：
首先，我们知道：
1、检索表中所有数据
select * from 表名
2、检索表中部分字段
select 字段1,字段2,... from 表名;
3、列重命名(建议保留as。增强可读性)
select 字段名 [as] ‘重命名’ from 表名;
4、查询结果集排序（order by）

select 字段列表 from 表名 order by 字段1 [ASC(升序)默认][DESC(降序)] , 字段2  [ASC(升序)默认][DESC(降序)] , ...;
a:两种排序方式（asc，desc）
b:可根据多个字段排序，每个字段可分别定义排序方式，优先级按sql语句书写的顺序
c:order by 子句位于where子句之后

例子：
1、数字排序
select * from Teachers  order by tAge desc, tNo desc;
2、字符排序
一般情况下a-z
select * from  Courses   orderby  cName;
#因为编码问题可能达不到想要的结果
#因为当前数据库语言设置为utf8，使用中文字段排序则须进行编码更改
select * from Courses order by convert (cName using gbk) ;

5、取数据条数

limit子句（SQLserver中为top子句）
取前n条数据：
select * from student limit n ;
从第n条开始取m条：注意，mysql计数从0开始（常用于分页数据读取）
select * from student limit n m;

#检索年龄最小的2位男同学
select * from student where sgender='男' ORDER BY sage ,sbirthday desc  limit 2;
-- 了解上面各子句的执行顺序where ... order by ...limit

6、聚合函数（max，min，avg，sum，count）
select COUNT(*),COUNT(1),COUNT(sid),COUNT(stel) from student;

– 注意:
– 1、max(字段)、min(字段)、avg(字段)、sum(字段)、count (非主键字段)在统计时
– 自动排除字段值中的nu1l值
– 2、count(*)、count(1)、 count (主键字段)返回表中所有符合条件的信息数目
– 3、avg(字段)、sum(字段)在统计字符字段时，返回值为0

7、去重（distinct,位于字段前）

-- a:单个字段
select distinct sage from student ;
-- b:多个字段(去除字段组合中，重复的项)
select distinct sage,sgender from student ;

8、子查询

#a:in子查询
-- 语法：select 字段列表 from 表1 where 表1.字段 in （select 表2.字段 from 表2）；
#b:exists 子查询
-- select 字段列表 from 表1 where exists（select 主键字段 from 表2 where 表2.字段=表1.字段）
#注意：表1字段与表2字段有主外键关联为佳

-- 例：查询没有任何选修记录的学生的姓名
select * from students;
select * from elogs;
-- 方法一：
select sName from students WHERE sNO in(select distinct sID from elogs);
-- 方法二
select sName from students WHERE exists (select sid from elogs where sid =sno ); 
--ps:通过 explain 或desc 查看执行的优劣
desc select sName from students WHERE exists (select sid from elogs where sid =sno ); 

-- 总结：（查询优劣）
-- 当有子查询时，若外部数据大于内部数据，使用in
-- 内部数据大于外部数据是，使用exists

9、合并结果集union：默认将结果集中的重复值取消
– union all ：保留重复项

10、表连接（join …on）

-- A:内连接
-- a:inner join 
-- select 字段列表 from 表1 inner join 表2  on 表1.主键字段=表2.外键字段（理想情况下） [where等条件]
-- B：外连接
-- a:左连接：left/right join 
-- select 字段列表 from 表1 left join 表2  on 表1.主键字段=表2.外键字段（理想情况下） [where等条件]

11、分组group by

#select 字段列表 from 表名 [where][group by ... having (分组后筛选条件)] [order by][limit]
#注意：where子句后面不能直接使用聚合函数(count,max,min,sum,avg等)，但是having后面可以使用

练习：

-- 1、统计《C语言》这门课程有多少学生考试及格.
select count(*) from elogs e left join courses c on e.cID=c.cNo WHERE e.elScore>=60  && c.cName='C语言' GROUP BY e.cID ; 

-- 2、统计不同性别老师的平均年龄及人数，输出效果如下图所示:。
-- 性别  平均年龄  人数
-- 男    30.7500    4
-- 女    21.0000   1
select t.tGender as '性别', AVG(t.tAge) '平均年龄',COUNT(*) '人数' from teachers as t GROUP BY t.tGender;

-- 3、查询哪些课程已有学生选修。
SELECT cName from courses where cNo in (SELECT distinct cID FROM elogs) ;
SELECT distinct c.cName from courses c INNER JOIN elogs e on c.cNo=e.cID ;#不建议使用，可能存在同名的课程

-- 4、统计选修学分( courses.cCredit)达到十分及以上的同学有哪些
SELECT sName from students s RIGHT JOIN elogs e on s.sNo=e.sID LEFT JOIN courses c ON e.cID=c.cNo GROUP BY e.sID HAVING SUM(c.cCredit)>=10;

-- 5、查询在2018年里姓王的同学选修考试成绩最高的分数是多少.
select MAX(e.elScore) from elogs e LEFT JOIN students s ON e.sID=s.sNo WHERE s.sName LIKE '王%' && YEAR(e.elDate)=2018;

-- 6、统计教师不同姓氏的人数分别有多少? (不考虑复姓)。
select LEFT(`tName`,1) '姓氏',count(*) '人数' from teachers GROUP BY LEFT(`tName`,1);

-- 7、统计《C语言》这门课程选修成绩中最高成绩与最低成绩的分值差
SELECT MAX(e.elScore)-MIN(e.elScore) '分值差' from elogs e INNER JOIN courses c ON e.cID=c.cNo WHERE c.cName='C语言';

-- 8、查看王军老师(teachers)所授课程(courses)的考试成绩(elogs)属于良的同学(students)有哪些(考试成绩在75与85之间，但不包含85为良)。
select s.sName,e.elScore from students s RIGHT JOIN elogs e on s.sNo=e.sID INNER JOIN courses c on e.cID=c.cNo INNER JOIN teachers t ON c.tID=t.tNo WHERE t.tName="老李" && e.elScore>=75 and e.elScore<85;