SQL基础2

1、注意group by 分组之后，如果需要用分组后的聚合函数结果进行条件限制，那么不能加在where那，而要使用having 写group by之后。
in 表示 某个字段的值在一个结果集中
not 表示否定
select distinct 列名 将查询结果中的内容去重显示。
as 给表或者子查询或者查出来的列名，取个其它的名字

查询平均成绩大于60分的同学的id：
select sid from score where avg(score)>60 group by sid

查询年龄大于平均年龄的同学的信息：
select * from student where sage>(select avg(sage) from student)

(select avg(sage) from student) 叫做子查询

查询参加了课程1但是没有参加课程2的考试的同学的名字。
查询参加了课程1的同学：
select sid from score where cid=1
查询参加了课程2的同学：
select sid from score where cid=2

select sid from score where sid in (select sid from score where cid=1) and sid not in (select sid from score where cid=2);
查出来会有相同的sid结果，去重：使用distinct
select distinct sid from score where sid in (select sid from score where cid=1) and sid not in (select sid from score where cid=2);

2、多表查询：连接查询子查询

查询参加了课程1但是没有参加课程2的考试的同学的名字：
第一种方案：通过使用子查询
select DISTINCT sid,sname from student where sid in (select sid from score where cid=1) and sid not in (select sid from score where cid=2);

第二种方案：连接查询：
select DISTINCT score.sid,student.sname from student INNER JOIN score on student.sid=score.sid where score.sid in (select sid from score where cid=1) and score.sid not in (select sid from score where cid=2);

笛卡尔积：
也称作直积，将两个数据集中的记录一一组合。
select * from teacher,course
select * from teacher inner join course
加上限制条件：
只能用where
select teacher.tname,course.cname from teacher,course where teacher.tid=course.tid
使用inner join的时候，既可以用where 也可以用 on ，建议标准用on
select * from teacher inner join course on teacher.tid=course.tid

外链接：左右外连接：一定是要有连接条件的
左连接：以左表作为基准，左表中的每一条记录都要去进行配对，即使不符合连接条件，而右表中的数据就对应填空。
右连接：以右表作为基准，右表中的每一条记录都要去进行配对，即使不符合连接条件，而左表中的数据就对应填空。

#以course作为基准查询，会显示所有的课程，不管有没有任课老师
select * from course LEFT JOIN teacher on teacher.tid=course.tid;
#以teacher作为基准，显示所有的老师，不管有没有授课
select * from teacher LEFT JOIN course on teacher.tid=course.tid;
#以teacher作为基准，显示所有的老师，不管有没有授课
select cname,tname from course RIGHT JOIN teacher on teacher.tid=course.tid;

3、练习题：
1.先确认基准表，哪个字段是不能缺的。
2.连接需要的其它表（有可能是张临时表）。
3.基于要查询的字段，进行整理。
4.使用group by 和聚合函数来完成最终统计。

1、查询男女学员的平均总成绩
需要两张表 student score 需要先合到一起，然后再分组，必用连接查询
由于平均成绩应该是算总分的平均，所以其实用到的表是子查询的总分统计表。

2、查询所有同学的学号，姓名，选课数，总成绩
需要student表和score表就够了。

3、查询每一门课程的任课老师，选课学员数，以及学员的平均成绩，平均年龄
基准表：course
四张表都得用。

https://blog.csdn.net/qq_44614710/article/details/86714504

4、答案：
#1.查询男女学员的平均成绩
select * from student as s LEFT JOIN (select sid,sum(score) as total from score group by sid) as sc on s.sid=sc.sid;

select ssex,avg(score) as '平均成绩' from student as s LEFT JOIN score as sc on s.sid=sc.sid GROUP BY ssex;

select ssex,avg(total) as '平均成绩' from student as s LEFT JOIN (select sid,sum(score) as total from score group by sid) as sc on s.sid=sc.sid GROUP BY ssex;

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

select s.sid,sname,count(cid) as '选课数',sum(score) as '总成绩' from student as s LEFT JOIN score as sc on s.sid=sc.sid GROUP BY s.sid;

#3、查询每一门课程的任课老师，选课学员数，以及学员的平均成绩，平均年龄
#先查出需要的数据，连接成一张完整表
select c.cid,cname,tname,sc.sid,sname,sage,score from course as c
LEFT JOIN teacher as t on c.tid=t.tid
#必须先连score才能连student
left JOIN score as sc on c.cid=sc.cid
LEFT JOIN student as s on sc.sid=s.sid
ORDER BY cid;

#group by 和 聚合函数进行统计
select c.cid as '编号',c.cname,t.tname,count(s.sname) as '选课人数',avg(s.sage) as '平均年龄',avg(sc.score) as '平均分' from course as c
LEFT JOIN teacher as t on c.tid=t.tid
#必须先连score才能连student
left JOIN score as sc on c.cid=sc.cid
LEFT JOIN student as s on sc.sid=s.sid
GROUP BY c.cid

5、分页查询存储过程：
BEGIN
#Routine body goes here...
#定义一个用于表示起点的变量
DECLARE startpt INT;
#每一页的页码，用limit显示，limit的起点是（page-1）*4
#输入必须是大于等于1的，如果是0或者负数，直接显示第一页
IF(page>0)
THEN
set startpt=(page-1)*4;
ELSE
set startpt=0;
end IF;
select * from score limit startpt,4;
END

调用存储过程的方式：查询语句使用 call 过程名(参数列表)

6、存储过程创建的时候，如果是varchar类型参数，需要指定一下参数的长度。

CREATE DEFINER=`root`@`%` PROCEDURE `login`(IN `logname` varchar(32),IN `logpwd` varchar(32))
BEGIN
#Routine body goes here...
select * from userinfo where username=logname and pwd=logpwd;
END