主机地址：rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com
端口号：3306
用户名：fanmao65
密码：abc@fanmao65
数据库：fanmao65_hm

student表

course科目表

score成绩表

teacher老师表

如果使用的是自己的数据库，可以自己创建表。

下面是建表语句；

drop TABLE if EXISTS student;
create table student(
`sno` int(5) not null,
`sname` varchar(3),
`ssex` varchar(1),`sbirthday` datetime not null,
`class` VARCHAR(10)
);
insert into student(`sno`,`sname`,`ssex`,`sbirthday`,`class`) 
values 
("108","曾华","男","1990/9/1","20033"),
("107","匡明","男","1989/8/9","20031"),
("101","王丽","女","1992/8/9","20033"),
("109","李军","男","1991/7/8","20033"),
("103","王芳","女","1993/7/9","20031"),
("105","陆君","男","1990/7/8","20031");
drop table if EXISTS course;
create table  course( 
`cno`  VARCHAR(11) not null, 
`cname` varchar(255) not null,
`tno` int(11)not null,
primary key (`cno`)
);
insert into course
(`cno`,`cname`,`tno`)
VALUES
("3-105","计算机导论","825"),
("3-245","操作系统","804"),
("6-166","数字电路","856"),
("9-888","高等数学","831");
DROP TABLE if EXISTS score;
create table score(
`sno` VARCHAR(10) not null,
`cno` varchar(20) not null,
`degree` varchar(15) not NULL
);    
INSERT INTO `score`
(`Sno`, `Cno`,`Degree`)
values
("103","3-245","86"),
("105","3-245","75"),
("109","3-245","68"),
("103","3-105","92"),
("105","3-105","88"),
("109","3-105","76"),
("101","3-105","64"),
("107","3-105","91"),
("108","3-105","78"),
("101","6-166","85"),
("107","6-166","79"),
("108","6-166","81");
DROP TABLE if EXISTS teacher;
create table teacher
(`tno` VARCHAR(11) not null,
`tname` varchar(50) not null,
`tsex` varchar(1) null,
`tbirthday` datetime(6) not null,
`prof` varchar(50) not null ,
`depart` varchar(50) not null)
;
insert into `teacher`
(`tno`,`tname`,`tsex`,`tbirthday`,`prof`,`depart`)
values 
('804','李成','男','1958/12/1','副教授','计算机系'),
('856','张翔','男','1969/3/12','讲师','电子工程系'),
('825','汪萍','女','1972/5/5','助教','计算机系'),
('831','柳冰','女','1977/8/14','助教','电子工程系');

升级版数据 也可以直接导入这个文件。
fanmao65_hm.sql

day-01

1. 统计 student学生表中 1990年-2000年出生的人数。 ```json — 统计 student学生表中 1990年-2000年出生的人数。

SELECT COUNT(sno) from student WHERE sbirthday BETWEEN “1990-01-01” and “2000-01-01”;

2. 查询 学生姓名，性别，年龄，班级。
```json
 SELECT sname, ssex, DATEDIFF(NOW(),sbirthday)/365 as age, class FROM student;

select sname,ssex,TIMESTAMPDIFF(year,sbirthday,NOW()) as age ,class from student

day-02

1. 每门课都及格的同学 姓名 ,班级。

   
   -- 每门课目 成绩都大于 60 
   
   --  也就是说 所有科目中最小成绩大于 60分 即可。
   
   -- 从成绩表中 统计每个同学的最小成绩
   
   SELECT sno, min(degree) from score
   GROUP BY sno HAVING MIN(degree) >= 60;
   
   -- 
   select a.sno,sname,class from student as a
   WHERE sno in (
     -- 最小成绩大于60的学号
     SELECT sno from score
   GROUP BY sno HAVING MIN(degree) >= 60
   )
   

2. 每个班级的同学人数。

   select class, count(class) from student
   group by class;
   

day-03

1. 统计每个学生的考试成绩总分，没有考试成绩的话 总分为0， 输出 学号，姓名，总分；

主要考察两个知识点：

1. left join 以学生表为主，统计所有学生表的数据。没有与之对应的数据则显示为Null
2. ifnull 函数，如果为空则显示 为0. ```sql

select a.sno,sname,ifnull(sum(degree),”0”) from student a LEFT join score b on a.sno=b.sno group by a.sno,sname order by sum(degree)

2. 统计每个班的平均分，   输出班级，平均分；
```python
SELECT student.class,AVG(score.degree) FROM student INNER JOIN score on student.sno=score.sno GROUP BY student.class;

day-04

select R.name,sum(S.price*num) from S
INNER JOIN R on S.id=R.id
WHERE date BETWEEN "2021-01-01" and "2021-06-30"
GROUP BY R.name having sum(S.price*num)>20000

day-05

1. 每个老师对应有多少个同学。 输出老师名字，对应学生数量。

   SELECT teacher.tname,count(score.sno) from teacher
   INNER JOIN course
   on teacher.tno=course.tno
   INNER JOIN score
   on course.cno=score.cno
   GROUP BY teacher.tname;
   

   SELECT tname,数量 FROM 
   (SELECT c.tno,COUNT(b.sno) as 数量 from course as a
   INNER JOIN score as b
   ON a.cno = b.cno
   INNER JOIN teacher as c
   ON a.tno = c.tno
   GROUP BY c.tno) as tmp
   INNER JOIN teacher
   ON tmp.tno = teacher.tno;
   

2. 输出老师名字，教授的课程名字。 ```bash — 1. 每个老师对应有多少个同学。 输出老师名字，对应学生数量。 SELECT teacher.tname,COUNT(sname) FROM teacher INNER JOIN course ON teacher.tno = course.tno INNER JOIN score on course.cno = score.cno INNER JOIN student on score.sno = student.sno GROUP BY teacher.tname

— 2. 输出老师名字，教授的课程名字。 SELECT teacher.tname,course.cname FROM teacher INNER JOIN course on teacher.tno = course.tno ```

day-06