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;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/228021.html

標籤：其他

上一篇：httpcanary小黃鳥上的一些問題求助一下大佬

下一篇：practice02_資料庫02----查詢所有學生的學號, 姓名, 選課數, 總成績