mysql查询每个老师对应的所有学生_practice02_数据库02----查询所有学生的学号, 姓名, 选课数, 总成绩...

1. 首先建立数据表

建立四张数据表(, 成绩表(score))

1. 学生表(students)

1.学生id  ----  s_id

2. 学生姓名  ----  s_name

3. 学生生日 ----  s_birth

4. 学生性别 ----  s_sex

2. 教师表(teacher)

1. 教师id ---- t_id

2. 教师姓名  ---- t_name

3. 课程表(course)

1.课程id ---- c_id

2. 课程名 ----  c_name

3. 教师id   -----   t_id(与教师表对应, 方便建立数据库连接)

4. 成绩表(score)

1. 学生id ---- s_id(与学生表对应, 方便建立数据库连接)

2. 课程id ---- c_id(与课程表对应, 方便建立数据库连接)

3. 学生成绩  -----  s_score

============   表的建立代码:

1 CREATE TABLE students(

2 s_id VARCHAR(20) PRIMARY KEY,

3 s_name VARCHAR(20) NOT NULL DEFAULT '',

4 s_birth VARCHAR(20) NOT NULL DEFAULT '',

5 s_sex VARCHAR(10) NOT NULL DEFAULT ''

6 );

7

8 CREATE TABLE course(

9 c_id VARCHAR(20) PRIMARY KEY,

10 c_name VARCHAR(20) NOT NULL DEFAULT '',

11 t_id VARCHAR(20) NOT NULL

12 );

13

14 CREATE TABLE teacher(

15 t_id VARCHAR(20) PRIMARY KEY,

16 t_name VARCHAR(20) NOT NULL DEFAULT ''

17 );

18

19 CREATE TABLE score(

20 s_id VARCHAR(20),

21 c_id VARCHAR(20),

22 s_score INT(3),

23 PRIMARY(s_id, c_id)

24 );

2. 导入数据

导入数据信息:insert into ... values(信息)

============数据导入代码

1 INSERT INTO students VALUES('01', '赵雷', '1990-01-01', '男');

2 INSERT INTO students VALUES('02', '钱电', '1990-12-21', '男');

3 INSERT INTO students VALUES('03', '孙风', '1990-05-20', '男');

4 INSERT INTO students VALUES('04', '李云', '1990-12-01', '男');

5 INSERT INTO students VALUES('05', '周梅', '1991-12-01', '女');

6 INSERT INTO students VALUES('06', '吴兰', '1992-03-01', '女');

7 INSERT INTO students VALUES('07', '郑竹', '1989-07-01', '女');

8 INSERT INTO students VALUES('08', '王菊', '1990-01-20', '女');

9

10 INSERT INTO course VALUES('01', '语文', '02');

11 INSERT INTO course VALUES('02', '数学', '01');

12 INSERT INTO course VALUES('03', '英语', '03');

13

14 INSERT INTO teacher VALUES('01', '张三');

15 INSERT INTO teacher VALUES('02', '李四');

16 INSERT INTO teacher VALUES('03', '王五');

17

18 INSERT INTO score VALUES('01', '01', 80);

19 INSERT INTO score VALUES('01', '02', 90);

20 INSERT INTO score VALUES('01', '03', 99);

21 INSERT INTO score VALUES('02', '01', 70);

22 INSERT INTO score VALUES('02', '02', 60);

23 INSERT INTO score VALUES('02', '03', 80);

24 INSERT INTO score VALUES('03', '01', 80);

25 INSERT INTO score VALUES('03', '02', 80);

26 INSERT INTO score VALUES('03', '03', 80);

27 INSERT INTO score VALUES('04', '01', 50);

28 INSERT INTO score VALUES('04', '02', 30);

29 INSERT INTO score VALUES('04', '03', 20);

30 INSERT INTO score VALUES('05', '01', 76);

31 INSERT INTO score VALUES('05', '02', 87);

32 INSERT INTO score VALUES('06', '03', 31);

33 INSERT INTO score VALUES('06', '01', 34);

34 INSERT INTO score VALUES('07', '02', 89);

35 INSERT INTO score VALUES('07', '03', 98);

效果图:

course表:

score表:

teacher表:

students表:

查询所有学生的学号, 姓名, 选课数, 总成绩

1. 思路: 想要查询, 首先我们需要建立一张如下的表, 方便我们查询

想要达到该目标, 我们就需要使用group by(一般做统计我们往往会用group by)

课程是求个数, 使用count()函数

总成绩求总和, 使用sum()函数

这里我们会用到students表和Cource表

2. 第一步, 我们需要将students表与score表做关联

使用左连接的方式 ---- (inner取的是交际, 如果8个学生只有7个参加考试, 则未参加考试的学生统计不到, 所以得向学生表做左连接)

select * from students as a left join score as b on a.s_id = b.s_id;

这样就得到了学生的关联所有信息

3. 第二步, 做统计, 用学生编号做统计(group by), 拿到学生编号, 学生课程数和学生总成绩

使用ifnull将空成绩定义为0

select a.s_id, count(b.c_id), sum(ifnull(b.s_score, 0)) from students as a left join score as b on a.s_id = b.s_id group by a.s_id;

4. 第三步, 我们再添加上学生姓名信息

为了防止以后解决多对多的现象, 我们可以再添加一段按s_name分组

select a.s_id, a.s_name, count(b.c_id), sum(ifnull(b.s_score, 0)) from students as a left join score as b on a.s_id = b.s_id group by a.s_id, a.s_name;

5. 拓展, 解决空数据问题, 我们还可以使用case when语句解决, 相当于ifnull()函数

select a.s_id, count(b.c_id), sum(case when b.s_score is null then 0 else b.s_score end) from students as a left join score as b on a.s_id = b.s_id group by a.s_id;

内容来源于网络如有侵权请私信删除