綜合專案需求

一、系統整體功能

系統需支持以下功能：

維護學生資訊、老師資訊和成績資訊

支持按多種條件組合查詢學生資訊和成績資訊

學生 Student（id,班級id，學號，姓名，性別，電話，地址，出生日期，民族，家長電話1，家長電話2）

老師 Teacher（id,工號，姓名，性別，電話，型別（班主任、講師））

班級 Class（id，班級名稱，講師id，班主任id）

課程 Subject（id，課程名稱、課程簡介）

成績 Score（id，stu_id,sub_id,卷面成績，專案成績，平時成績，綜合成績）

二、系統的資訊需求

一個班級有一個講師一個班主任

一個班級有多名學生

一個學生有多門課程，一門課程也有多名學生

三、表結構

【學生表 Student】

【教師表 Teacher】

【班級表Class】

【課程表Subject】

【成績表Score】

四、資料維護

（1）查詢自己的資訊，顯示學號，姓名，性別，電話，地址，出生日期，民族，家長電話1，家長電話2

SELECT nostudent 學號,name 姓名,sex 性別,phone 電話,address 地址,birthday 出生日期,nation 名族,parent_phone_1 家長電話1,parent_phone_2 家長電話2 
FROM student 
WHERE id=1;

（2）查詢自己和所在班級的資訊，顯示班級，學號，姓名，性別，電話，地址，出生日期，民族，家長電話1，家長電話2

SELECT class.name 班級,nostudent 學號,student.name 姓名,sex 性別,phone 電話,address 地址,birthday 出生日期,nation 名族,parent_phone_1 家長電話1,parent_phone_2 家長電話2 
FROM student,class WHERE student.class_id = class.id 
AND class_id=1;

（3）查詢所有講師的帶班資訊，顯示，講師姓名，班級，班級人數

SELECT t.name 講師姓名,c.name 班級,COUNT(*) 班級人數 FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.lecturer_id = t.id
AND teacher_type='講師'
GROUP BY c.name;

（4）查詢班級資訊，顯示班級名稱、講師姓名、班主任姓名，班級人數

SELECT c.name 班級名稱,t.name 講師姓名,COUNT(*) 班級人數 FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.lecturer_id = t.id AND teacher_type='講師'
GROUP BY c.name;

SELECT c.name 班級名稱,t.name 講師姓名,COUNT(*) 班級人數 FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.class_teacher_id = t.id AND teacher_type='班主任'
GROUP BY c.name;

（5）查詢我們班的成績資訊，要求顯示，姓名，卷面成績，專案成績，平時成績，綜合成績,按綜合成績降序排序

SELECT st.name 姓名,exam 卷面成績,project 專案成績,daily 平時成績,result 綜合成績
FROM student st,score sc
WHERE sc.stu_id=st.id AND st.class_id=1;

（6）查詢每個講師帶了多少學生，顯示講師姓名，學生數

SELECT t.name 講師姓名,COUNT(s.class_id) 學生數 FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.lecturer_id = t.id
AND teacher_type='講師'
GROUP BY teacher_type;

（7）查詢各班H5卷面成績，顯示班級、平均分，最高分，最低分

SELECT c.name 班級,AVG(exam) 平均成績,MAX(exam) 最高成績,MIN(exam) 最低成績 FROM class c,score s,student st 
WHERE c.id=st.class_id AND s.stu_id=st.id AND s.sub_id=3;

（8）查詢你自己各科成績，顯示，姓名、卷面成績，專案成績，平時成績，綜合成績

SELECT st.name 姓名,exam 卷面成績,project 專案成績,daily 平時成績,result 綜合成績 FROM student st,score sc,`subject` su
WHERE sc.stu_id=st.id AND sc.sub_id=su.id
AND st.name='楊明金';

（9）查詢各地州的學生人數，顯示，地址州，學生人數，按人數排序

SELECT address 地址州,COUNT(name) 學生人數 FROM student 
GROUP BY address
ORDER BY COUNT(name)

（10）查詢自己所有成績的平均分，顯示姓名 卷面成績，專案成績，平時成績，綜合成績

SELECT st.name 姓名,AVG(exam) 卷面成績,AVG(project) 專案成績,AVG(daily) 平時成績,AVG(result) 綜合成績 FROM student st,score sc,`subject` su
WHERE sc.stu_id=st.id AND sc.sub_id=su.id
AND st.name='楊明金';

（11）查詢各地州的學生平均成績，顯示，地址州，學生人數，平均卷面成績，平均專案成績，平均平時成績，平均綜合成績，按綜合成績排序從高到低排序

SELECT address 地址州,COUNT(st.name) 學生人數,AVG(exam) 卷面成績,AVG(project) 專案成績,AVG(daily) 平時成績,AVG(result) 綜合成績 FROM student st,score sc,`subject` su
WHERE sc.stu_id=st.id AND sc.sub_id=su.id
GROUP BY address

（12）查詢所有學生的成績資訊，顯示，學生姓名，班級，講師，班主任，課程名，卷面成績，專案成績，平時成績，綜合成績，按卷面成績排序從高到低排序

SELECT st.name 學生姓名,cl.name 班級名稱,te.name 老師姓名,su.name 課程名稱,exam 卷面成績,project 專案成績,daily 平時成績, result 綜合成績
FROM score sc,`subject` su,student st,class cl,teacher te
WHERE sc.sub_id=su.id AND sc.stu_id=st.id AND cl.id=st.class_id AND te.id=cl.lecturer_id  
ORDER BY exam DESC

（13）查詢各講師各科目的平均分，顯示講師，科目，卷面成績，專案成績，平時成績，綜合成績，選擇合適的排序欄位和排序方式

SELECT te.name 講師,su.name 科目,exam 卷面成績,project 專案成績,daily 平時成績,result 綜合成績
FROM score sc,`subject` su,student st,class cl,teacher te
WHERE sc.sub_id=su.id AND sc.stu_id=st.id AND cl.id=st.class_id AND te.id=cl.lecturer_id
ORDER BY result DESC

（14）查詢各講師各班各科目的平均分，顯示講師，班級，科目，卷面成績，專案成績，平時成績，綜合成績, 選擇合適的排序欄位和排序方式

SELECT te.name 講師,cl.name 班級, su.name 科目,AVG(exam) 卷面成績,AVG(project) 專案成績,AVG(daily) 平時成績,AVG(result) 綜合成績 
FROM score sc,`subject` su,student st,class cl,teacher te
WHERE sc.sub_id=su.id AND sc.stu_id=st.id AND cl.id=st.class_id AND te.id=cl.lecturer_id
GROUP BY te.name,cl.name
ORDER BY result DESC

五、資料庫匯入

/*
Navicat MySQL Data Transfer

Source Server         : test
Source Server Version : 50646
Source Host           : localhost:3306
Source Database       : schools

Target Server Type    : MYSQL
Target Server Version : 50646
File Encoding         : 65001

Date: 2020-09-11 16:31:56
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `lecturer_id` int(1) DEFAULT NULL,
  `class_teacher_id` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', '19計算機網路技術一班', '1', '2');
INSERT INTO `class` VALUES ('2', '19計算機網路技術二班', '1', '2');
INSERT INTO `class` VALUES ('3', '19大資料技術與應用一班', '1', '3');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(20) DEFAULT NULL,
  `sub_id` int(1) DEFAULT NULL,
  `exam` decimal(18,1) DEFAULT NULL,
  `project` decimal(18,1) DEFAULT NULL,
  `daily` decimal(18,1) DEFAULT NULL,
  `result` decimal(18,1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '50.0', '70.0', '90.0', '230.0');
INSERT INTO `score` VALUES ('2', '1', '3', '85.0', '60.0', '50.0', '603.0');
INSERT INTO `score` VALUES ('3', '2', '4', '50.0', '90.0', '80.0', '656.0');
INSERT INTO `score` VALUES ('4', '4', '5', '65.0', '86.0', '56.0', '455.0');
INSERT INTO `score` VALUES ('5', '2', '6', '45.0', '48.0', '65.0', '915.0');
INSERT INTO `score` VALUES ('6', '3', '5', '45.0', '46.0', '45.0', '564.0');

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `class_id` int(11) DEFAULT NULL,
  `nostudent` varchar(30) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(200) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `nation` varchar(20) DEFAULT NULL,
  `parent_phone_1` varchar(20) DEFAULT NULL,
  `parent_phone_2` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '1', '193610202629', '楊明金', '男', '18214217246', '云南昭通', '2020-09-11', '漢族', '13587757775', '5454874545');
INSERT INTO `student` VALUES ('2', '1', '195656546544', '肖明安', '女', '154345343535', '云南昆明', '2020-10-21', '藏族', '26565656565', '5656565655');
INSERT INTO `student` VALUES ('3', '2', '235656565555', '閃正', '女', '1534565654', '四川眉州', '2020-09-08', '漢族', '15645878787', '1546787645');
INSERT INTO `student` VALUES ('4', '2', '156487867557', '巖奔', '男', '12345454886', '云南文山', '2020-09-16', '白族', '15647867878', '5413434543');
INSERT INTO `student` VALUES ('5', '3', '156986564565', '劉澤龍', '女', '15649878978', '云南曲靖', '2020-09-06', '布依族', '15645484777', '1565456475');

-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of subject
-- ----------------------------
INSERT INTO `subject` VALUES ('1', '計算機應用基礎', '介紹');
INSERT INTO `subject` VALUES ('2', 'Photoshop UI設計', '介紹');
INSERT INTO `subject` VALUES ('3', 'HTML5網頁設計', '簡介');
INSERT INTO `subject` VALUES ('4', 'Java程式設計基礎', '這是簡介');
INSERT INTO `subject` VALUES ('5', 'C#程式設計基礎', '這是介紹');

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nonumber` varchar(30) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` char(2) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `Teacher_type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '1500077', '王威', '男', '159********', '講師');
INSERT INTO `teacher` VALUES ('2', '1500044', '魯衛秀', '女', '183********', '班主任');
INSERT INTO `teacher` VALUES ('3', '1500027', '李松齡', '女', '184********', '班主任');