《MySQL数据操作与查询》- 综合项目 - 学生管理系统
《MySQL数据操作与查询》综合项目需求
（1）系统整体功能
维护学生信息、老师信息和成绩信息。
支持按多种条件组合查询学生信息和成绩信息。
（2）系统的信息需求一个班级有一个讲师一个班主任。
一个班级有多名学生。
一个学生有多门课程，一门课程也有多名学生。
（3）表结构
学生表【student】
班级表【class】
课程表【course】
教师表【teacher】
成绩表【score】
（1）表结构实现DDL：
/*
Navicat MySQL Data Transfer
Source Server : test
Source Server Version : 50646
Source Host : localhost:3306
Source Database : studentsys
Target Server Type : MYSQL
Target Server Version : 50646
File Encoding : 65001
Date: 2020-04-17 15:48:10
*/
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(11) DEFAULT NULL,
`class_teacher_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_class_teacher_id` (`class_teacher_id`),
KEY `fk_lecturer_id` (`lecturer_id`),
CONSTRAINT `fk_class_teacher_id` FOREIGN KEY (`class_teacher_id`) REFERENCES `teacher` (`id`),
CONSTRAINT `fk_lecturer_id` FOREIGN KEY (`lecturer_id`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`credit` decimal(3,1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id``` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) DEFAULT NULL,
`course_id` int(11) DEFAULT NULL,
`test_score` decimal(5,2) DEFAULT NULL,
`project_score` decimal(5,2) DEFAULT NULL,
`usual_score` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`id```),
KEY `fk_student_id` (`student_id`),
KEY `course_id` (`course_id`),
CONSTRAINT `course_id` FOREIGN KEY (`course_id`) REFERENCES `score` (`id```),
CONSTRAINT `fk_student_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=334 DEFAULT CHARSET=utf8;
-- ----------------------------
-- 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,
`no` varchar(30) NOT NULL,
`name` varchar(10) NOT NULL,
`sex` varchar(4) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_class_id` (`class_id`),
CONSTRAINT `fk_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=112 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`no` varchar(30) NOT NULL,
`name` varchar(10) NOT NULL,
`sex` varchar(4) DEFAULT NULL,
`phone` varchar(20) DEFAULT NULL,
`type` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
（2）数据导入
-- ----------------------------
-- 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');
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '计算机应用基础', '2.0');
INSERT INTO `course` VALUES ('2', 'Photoshop UI设计', '3.0');
INSERT INTO `course` VALUES ('3', 'HTML5网页设计', '4.0');
INSERT INTO `course` VALUES ('4', 'Java程序设计基础', '4.0');
INSERT INTO `course` VALUES ('5', 'C#程序设计基础', '4.0');
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '95.00', '84.00', '92.55');
INSERT INTO `score` VALUES ('2', '2', '1', '94.00', '85.00', '92.20');
INSERT INTO `score` VALUES ('3', '3', '1', '87.00', '84.29', '91.30');
INSERT INTO `score` VALUES ('4', '4', '1', '94.00', '83.59', '92.30');
INSERT INTO `score` VALUES ('5', '5', '1', '99.00', '91.75', '93.25');
INSERT INTO `score` VALUES ('6', '6', '1', '96.00', '88.17', '92.25');
INSERT INTO `score` VALUES ('7', '7', '1', '92.00', '84.29', '95.45');
INSERT INTO `score` VALUES ('8', '8', '1', '97.00', '85.00', '96.40');
INSERT INTO `score` VALUES ('9', '9', '1', '90.00', '88.25', '89.55');
INSERT INTO `score` VALUES ('10', '10', '1', '98.00', '84.00', '95.70');
INSERT INTO `score` VALUES ('11', '11', '1', '93.00', '92.75', '97.35');
INSERT INTO `score` VALUES ('12', '12', '1', '74.00', '81.75', '92.50');
INSERT INTO `score` VALUES ('13', '13', '1', '78.00', '82.63', '88.30');
INSERT INTO `score` VALUES ('14', '14', '1', '90.00', '86.67', '96.00');
INSERT INTO `score` VALUES ('15', '15', '1', '97.00', '83.50', '89.50');
INSERT INTO `score` VALUES ('16', '16', '1', '98.00', '82.00', '90.70');
INSERT INTO `score` VALUES ('17', '17', '1', '84.00', '86.13', '95.75');
INSERT INTO `score` VALUES ('18', '18', '1', '94.00', '92.25', '93.60');
INSERT INTO `score` VALUES ('19', '19', '1', '96.00', '84.29', '89.00');
INSERT INTO `score` VALUES ('20', '20', '1', '96.00', '88.13', '89.85');
INSERT INTO `score` VALUES ('21', '21', '1', '93.00', '85.09', '92.40');
INSERT INTO `score` VALUES ('22', '22', '1', '95.00', '87.54', '90.95');
INSERT INTO `score` VALUES ('23', '23', '1', '95.00', '87.75', '92.50');
INSERT INTO `score` VALUES ('24', '24', '1', '86.00', '84.04', '93.20');
INSERT INTO `score` VALUES ('25', '25', '1', '99.00', '87.09', '96.70');
INSERT INTO `score` VALUES ('26', '26', '1', '94.00', '87.17', '92.85');
INSERT INTO `score` VALUES ('27', '27', '1', '93.00', '87.17', '90.45');
INSERT INTO `score` VALUES ('28', '28', '1', '88.00', '83.63', '91.10');
INSERT INTO `score` VALUES ('29', '29', '1', '76.00', '81.25', '89.85');
INSERT INTO `score` VALUES ('30', '30', '1', '86.00', '87.04', '92.95');
INSERT INTO `score` VALUES ('31', '31', '1', '85.00', '84.50', '89.65');
INSERT INTO `score` VALUES ('32', '32', '1', '87.00', '86.59', '90.05');
INSERT INTO `score` VALUES ('33', '33', '1', '91.00', '85.25', '96.80');
INSERT INTO `score` VALUES ('34', '34', '1', '93.50', '89.54', '92.05');
INSERT INTO `score` VALUES ('35', '35', '1', '95.00', '87.50', '92.10');
INSERT INTO `score` VALUES ('36', '36', '1', '98.00', '84.79', '89.85');
INSERT INTO `score` VALUES ('37', '37', '1', '93.00', '91.25', '89.55');
INSERT INTO `score` VALUES ('38', '38', '1', '90.00', '85.50', '92.80');
INSERT INTO `score` VALUES ('39', '39', '1', '60.00', '81.40', '90.65');
INSERT INTO `score` VALUES ('40', '40', '1', '97.00', '84.00', '95.25');
INSERT INTO `score` VALUES ('41', '41', '1', '81.00', '85.64', '91.85');
INSERT INTO `score` VALUES ('42', '42', '1', '65.00', '82.80', '88.85');
INSERT INTO `score` VALUES ('43', '43', '1', '95.00', '83.84', '90.70');
INSERT INTO `score` VALUES ('44', '44', '1', '88.00', '89.60', '94.05');
INSERT INTO `score` VALUES ('45', '45', '1', '98.00', '91.00', '91.85');
INSERT INTO `score` VALUES ('46', '46', '1', '91.00', '80.36', '89.60');
INSERT INTO `score` VALUES ('47', '47', '1', '96.00', '87.40', '93.75');
INSERT INTO `score` VALUES ('48', '48', '1', '90.00', '89.80', '96.85');
INSERT INTO `score` VALUES ('49', '49', '1', '98.00', '83.20', '93.30');
INSERT INTO `score` VALUES ('50', '50', '1', '93.00', '85.20', '90.90');
INSERT INTO `score` VALUES ('51', '51', '1', '98.00', '86.36', '93.50');
INSERT INTO `score` VALUES ('52', '52', '1', '99.00', '87.56', '96.65');
INSERT INTO `score` VALUES ('53', '53', '1', '86.00', '93.60', '96.10');
INSERT INTO `score` VALUES ('54', '54', '1', '94.00', '86.60', '96.70');
INSERT INTO `score` VALUES ('55', '55', '1', '61.00', '85.20', '88.90');
INSERT INTO `score` VALUES ('56', '56', '1', '91.00', '80.36', '92.90');
INSERT INTO `score` VALUES ('57', '57', '1', '86.00', '87.80', '88.20');
INSERT INTO `score` VALUES ('58', '58', '1', '84.00', '85.40', '85.65');
INSERT INTO `score` VALUES ('59', '59', '1', '81.00', '87.00', '91.85');
INSERT INTO `score` VALUES ('60', '60', '1', '98.00', '89.73', '92.95');
INSERT INTO `score` VALUES ('61', '61', '1', '94.00', '83.60', '87.80');
INSERT INTO `score` VALUES ('62', '62', '1', '91.00', '80.60', '90.30');
INSERT INTO `score` VALUES ('63', '63', '1', '96.00', '80.40', '88.50');
INSERT INTO `score` VALUES ('64', '64', '1', '85.00', '89.24', '93.15');
INSERT INTO `score` VALUES ('65', '65', '1', '96.00', '86.00', '91.20');
INSERT INTO `score` VALUES ('66', '66', '1', '91.00', '80.60', '91.05');
INSERT INTO `score` VALUES ('67', '67', '1', '98.00', '92.73', '96.80');
INSERT INTO `score` VALUES ('68', '68', '1', '98.00', '90.40', '92.00');
INSERT INTO `score` VALUES ('69', '69', '1', '92.00', '89.13', '87.90');
INSERT INTO `score` VALUES ('70', '70', '1', '97.00', '86.20', '94.85');
INSERT INTO `score` VALUES ('71', '71', '1', '94.00', '86.73', '92.50');
INSERT INTO `score` VALUES ('72', '72', '1', '93.00', '87.40', '93.45');
INSERT INTO `score` VALUES ('73', '73', '1', '82.00', '83.84', '86.55');
INSERT INTO `score` VALUES ('74', '74', '1', '94.00', '87.00', '91.35');
INSERT INTO `score` VALUES ('75', '1', '2', '65.00', '90.31', '91.56');
INSERT INTO `score` VALUES ('76', '2', '2', '62.00', '85.32', '91.02');
INSERT INTO `score` VALUES ('77', '3', '2', '65.00', '90.26', '92.31');
INSERT INTO `score` VALUES ('78', '4', '2', '70.00', '91.14', '92.54');
INSERT INTO `score` VALUES ('79', '5', '2', '81.00', '93.66', '93.06');
INSERT INTO `score` VALUES ('80', '6', '2', '65.00', '90.26', '91.41');
INSERT INTO `score` VALUES ('81', '7', '2', '61.00', '84.73', '94.33');
INSERT INTO `score` VALUES ('82', '8', '2', '70.00', '86.65', '94.20');
INSERT INTO `score` VALUES ('83', '9', '2', '75.00', '87.91', '91.11');
INSERT INTO `score` VALUES ('84', '10', '2', '78.00', '91.96', '96.46');
INSERT INTO `score` VALUES ('85', '11', '2', '77.00', '90.88', '96.88');
INSERT INTO `score` VALUES ('86', '12', '2', '69.00', '90.83', '92.28');
INSERT INTO `score` VALUES ('87', '13', '2', '61.00', '81.61', '90.56');
INSERT INTO `score` VALUES ('88', '14', '2', '86.00', '94.80', '96.95');
INSERT INTO `score` VALUES ('89', '15', '2', '74.00', '87.69', '88.29');
INSERT INTO `score` VALUES ('90', '16', '2', '52.00', '84.57', '89.82');
INSERT INTO `score` VALUES ('91', '17', '2', '74.00', '90.76', '96.36');
INSERT INTO `score` VALUES ('92', '18', '2', '82.00', '90.88', '92.53');
INSERT INTO `score` VALUES ('93', '19', '2', '66.00', '88.17', '91.82');
INSERT INTO `score` VALUES ('94', '20', '2', '49.00', '92.09', '90.14');
INSERT INTO `score` VALUES ('95', '21', '2', '61.00', '85.63', '90.53');
INSERT INTO `score` VALUES ('96', '22', '2', '71.00', '85.82', '90.57');
INSERT INTO `score` VALUES ('97', '23', '2', '70.00', '92.90', '92.35');
INSERT INTO `score` VALUES ('98', '24', '2', '61.00', '82.85', '94.10');
INSERT INTO `score` VALUES ('99', '25', '2', '85.00', '91.76', '96.96');
INSERT INTO `score` VALUES ('100', '26', '2', '86.00', '91.19', '91.89');
INSERT INTO `score` VALUES ('101', '27', '2', '60.00', '84.99', '90.74');
INSERT INTO `score` VALUES ('102', '28', '2', '70.00', '87.36', '91.21');
INSERT INTO `score` VALUES ('103', '29', '2', '63.00', '81.73', '88.78');
INSERT INTO `score` VALUES ('104', '30', '2', '83.00', '90.70', '91.75');
INSERT INTO `score` VALUES ('105', '31', '2', '53.00', '83.54', '90.14');
INSERT INTO `score` VALUES ('106', '32', '2', '60.00', '88.67', '91.47');
INSERT INTO `score` VALUES ('107', '33', '2', '71.00', '92.71', '96.21');
INSERT INTO `score` VALUES ('108', '34', '2', '80.00', '89.31', '91.51');
INSERT INTO `score` VALUES ('109', '35', '2', '78.00', '89.93', '90.78');
INSERT INTO `score` VALUES ('110', '36', '2', '64.00', '85.18', '90.68');
INSERT INTO `score` VALUES ('111', '37', '2', '71.00', '85.06', '88.91');
INSERT INTO `score` VALUES ('112', '38', '2', '60.00', '87.08', '90.08');
INSERT INTO `score` VALUES ('113', '39', '2', '52.00', '85.98', '91.48');
INSERT INTO `score` VALUES ('114', '40', '2', '64.00', '90.69', '96.14');
INSERT INTO `score` VALUES ('115', '41', '2', '60.00', '87.84', '82.74');
INSERT INTO `score` VALUES ('116', '42', '2', '60.00', '87.98', '91.28');
INSERT INTO `score` VALUES ('117', '43', '2', '64.00', '85.82', '90.32');
INSERT INTO `score` VALUES ('118', '44', '2', '67.00', '86.39', '80.54');
INSERT INTO `score` VALUES ('119', '45', '2', '79.00', '94.04', '93.74');
INSERT INTO `score` VALUES ('120', '46', '2', '61.00', '85.87', '85.12');
INSERT INTO `score` VALUES ('121', '47', '2', '78.00', '95.75', '94.50');
INSERT INTO `score` VALUES ('122', '48', '2', '77.00', '87.91', '94.66');
INSERT INTO `score` VALUES ('123', '49', '2', '88.00', '92.90', '92.10');
INSERT INTO `score` VALUES ('124', '50', '2', '63.00', '89.43', '92.18');
INSERT INTO `score` VALUES ('125', '51', '2', '85.00', '92.33', '92.78');
INSERT INTO `score` VALUES ('126', '52', '2', '86.00', '94.61', '97.06');
INSERT INTO `score` VALUES ('127', '53', '2', '68.00', '90.07', '96.22');
INSERT INTO `score` VALUES ('128', '54', '2', '64.00', '89.62', '96.12');
INSERT INTO `score` VALUES ('129', '55', '2', '62.00', '87.27', '89.22');
INSERT INTO `score` VALUES ('130', '56', '2', '74.00', '87.77', '90.82');
INSERT INTO `score` VALUES ('131', '57', '2', '51.00', '87.84', '90.74');
INSERT INTO `score` VALUES ('132', '58', '2', '50.00', '86.13', '90.78');
INSERT INTO `score` VALUES ('133', '59', '2', '71.00', '89.88', '93.28');
INSERT INTO `score` VALUES ('134', '60', '2', '71.00', '92.78', '92.38');
INSERT INTO `score` VALUES ('135', '61', '2', '68.00', '87.15', '90.70');
INSERT INTO `score` VALUES ('136', '62', '2', '73.00', '89.74', '91.94');
INSERT INTO `score` VALUES ('137', '63', '2', '63.00', '84.11', '90.36');
INSERT INTO `score` VALUES ('138', '64', '2', '64.00', '88.10', '93.80');
INSERT INTO `score` VALUES ('139', '65', '2', '76.00', '89.62', '91.12');
INSERT INTO `score` VALUES ('140', '66', '2', '64.00', '90.50', '91.50');
INSERT INTO `score` VALUES ('141', '67', '2', '78.00', '92.40', '96.90');
INSERT INTO `score` VALUES ('142', '68', '2', '66.00', '88.03', '89.18');
INSERT INTO `score` VALUES ('143', '69', '2', '76.00', '88.98', '91.58');
INSERT INTO `score` VALUES ('144', '70', '2', '60.00', '87.98', '96.18');
INSERT INTO `score` VALUES ('145', '71', '2', '69.00', '92.02', '92.02');
INSERT INTO `score` VALUES ('146', '72', '2', '73.00', '88.53', '85.58');
INSERT INTO `score` VALUES ('147', '73', '2', '74.00', '87.72', '91.02');
INSERT INTO `score` VALUES ('148', '74', '2', '78.00', '92.47', '90.62');
INSERT INTO `score` VALUES ('149', '1', '3', '91.00', '89.75', '92.25');
INSERT INTO `score` VALUES ('150', '2', '3', '91.00', '89.75', '89.85');
INSERT INTO `score` VALUES ('151', '3', '3', '79.00', '87.50', '92.70');
INSERT INTO `score` VALUES ('152', '4', '3', '89.00', '96.00', '93.50');
INSERT INTO `score` VALUES ('153', '5', '3', '94.00', '90.50', '96.60');
INSERT INTO `score` VALUES ('154', '6', '3', '82.00', '90.00', '93.10');
INSERT INTO `score` VALUES ('155', '7', '3', '64.00', '86.75', '94.75');
INSERT INTO `score` VALUES ('156', '8', '3', '82.00', '96.00', '93.80');
INSERT INTO `score` VALUES ('157', '9', '3', '90.00', '92.50', '93.80');
INSERT INTO `score` VALUES ('158', '10', '3', '90.00', '85.00', '95.80');
INSERT INTO `score` VALUES ('159', '11', '3', '88.00', '88.50', '92.00');
INSERT INTO `score` VALUES ('160', '12', '3', '75.00', '84.25', '96.05');
INSERT INTO `score` VALUES ('161', '13', '3', '75.00', '84.75', '92.05');
INSERT INTO `score` VALUES ('162', '14', '3', '77.00', '93.75', '96.95');
INSERT INTO `score` VALUES ('163', '15', '3', '86.00', '78.50', '88.20');
INSERT INTO `score` VALUES ('164', '16', '3', '74.00', '87.50', '90.00');
INSERT INTO `score` VALUES ('165', '17', '3', '91.00', '90.50', '96.70');
INSERT INTO `score` VALUES ('166', '18', '3', '91.00', '90.50', '91.10');
INSERT INTO `score` VALUES ('167', '19', '3', '83.00', '86.25', '91.45');
INSERT INTO `score` VALUES ('168', '20', '3', '70.00', '79.50', '87.90');
INSERT INTO `score` VALUES ('169', '21', '3', '84.00', '91.50', '96.70');
INSERT INTO `score` VALUES ('170', '22', '3', '87.00', '85.50', '87.10');
INSERT INTO `score` VALUES ('171', '23', '3', '89.00', '96.50', '94.40');
INSERT INTO `score` VALUES ('172', '24', '3', '85.00', '86.25', '96.05');
INSERT INTO `score` VALUES ('173', '25', '3', '88.00', '91.00', '92.60');
INSERT INTO `score` VALUES ('174', '26', '3', '78.00', '83.00', '90.40');
INSERT INTO `score` VALUES ('175', '27', '3', '71.00', '85.00', '92.20');
INSERT INTO `score` VALUES ('176', '28', '3', '79.00', '85.00', '91.20');
INSERT INTO `score` VALUES ('177', '29', '3', '60.00', '82.25', '91.45');
INSERT INTO `score` VALUES ('178', '30', '3', '87.00', '85.50', '91.60');
INSERT INTO `score` VALUES ('179', '31', '3', '72.00', '82.50', '90.80');
INSERT INTO `score` VALUES ('180', '32', '3', '75.00', '88.75', '91.35');
INSERT INTO `score` VALUES ('181', '33', '3', '88.00', '96.25', '94.15');
INSERT INTO `score` VALUES ('182', '34', '3', '80.00', '81.50', '89.00');
INSERT INTO `score` VALUES ('183', '35', '3', '90.00', '94.00', '93.80');
INSERT INTO `score` VALUES ('184', '36', '3', '81.00', '85.50', '92.00');
INSERT INTO `score` VALUES ('185', '37', '3', '66.00', '84.00', '87.00');
INSERT INTO `score` VALUES ('186', '38', '3', '74.00', '79.75', '93.05');
INSERT INTO `score` VALUES ('187', '39', '3', '80.00', '91.00', '92.10');
INSERT INTO `score` VALUES ('188', '40', '3', '72.00', '94.00', '97.10');
INSERT INTO `score` VALUES ('189', '41', '3', '75.00', '86.60', '90.52');
INSERT INTO `score` VALUES ('190', '42', '3', '61.00', '79.00', '90.00');
INSERT INTO `score` VALUES ('191', '43', '3', '80.00', '94.00', '93.20');
INSERT INTO `score` VALUES ('192', '44', '3', '68.00', '86.20', '95.04');
INSERT INTO `score` VALUES ('193', '45', '3', '93.00', '95.00', '93.90');
INSERT INTO `score` VALUES ('194', '46', '3', '82.00', '94.60', '93.82');
INSERT INTO `score` VALUES ('195', '47', '3', '83.00', '97.00', '93.80');
INSERT INTO `score` VALUES ('196', '48', '3', '90.00', '95.80', '97.96');
INSERT INTO `score` VALUES ('197', '49', '3', '91.00', '92.20', '92.74');
INSERT INTO `score` VALUES ('198', '50', '3', '61.00', '89.60', '92.32');
INSERT INTO `score` VALUES ('199', '51', '3', '80.00', '90.60', '95.42');
INSERT INTO `score` VALUES ('200', '52', '3', '90.00', '94.00', '93.40');
INSERT INTO `score` VALUES ('201', '53', '3', '93.00', '86.00', '94.80');
INSERT INTO `score` VALUES ('202', '54', '3', '77.00', '83.80', '95.86');
INSERT INTO `score` VALUES ('203', '55', '3', '48.00', '84.60', '87.82');
INSERT INTO `score` VALUES ('204', '56', '3', '71.00', '89.00', '92.40');
INSERT INTO `score` VALUES ('205', '57', '3', '69.00', '72.00', '88.10');
INSERT INTO `score` VALUES ('206', '58', '3', '60.00', '81.60', '91.52');
INSERT INTO `score` VALUES ('207', '59', '3', '85.00', '76.00', '90.50');
INSERT INTO `score` VALUES ('208', '60', '3', '70.00', '95.00', '93.70');
INSERT INTO `score` VALUES ('209', '61', '3', '79.00', '90.00', '93.20');
INSERT INTO `score` VALUES ('210', '62', '3', '51.00', '88.20', '96.54');
INSERT INTO `score` VALUES ('211', '63', '3', '52.00', '85.00', '91.30');
INSERT INTO `score` VALUES ('212', '64', '3', '72.00', '87.80', '92.76');
INSERT INTO `score` VALUES ('213', '65', '3', '74.00', '88.60', '92.42');
INSERT INTO `score` VALUES ('214', '66', '3', '61.00', '84.00', '90.70');
INSERT INTO `score` VALUES ('215', '67', '3', '89.00', '84.00', '95.60');
INSERT INTO `score` VALUES ('216', '68', '3', '74.00', '89.20', '92.84');
INSERT INTO `score` VALUES ('217', '69', '3', '73.00', '91.00', '93.00');
INSERT INTO `score` VALUES ('218', '70', '3', '65.00', '82.60', '93.12');
INSERT INTO `score` VALUES ('219', '71', '3', '83.00', '82.40', '91.38');
INSERT INTO `score` VALUES ('220', '72', '3', '61.00', '90.00', '93.60');
INSERT INTO `score` VALUES ('221', '73', '3', '65.00', '91.00', '93.20');
INSERT INTO `score` VALUES ('222', '74', '3', '84.00', '89.60', '91.62');
INSERT INTO `score` VALUES ('223', '1', '4', '97.00', '87.00', '92.32');
INSERT INTO `score` VALUES ('224', '2', '4', '90.00', '84.00', '88.02');
INSERT INTO `score` VALUES ('225', '3', '4', '87.00', '86.00', '92.10');
INSERT INTO `score` VALUES ('226', '4', '4', '93.00', '88.00', '92.78');
INSERT INTO `score` VALUES ('227', '5', '4', '99.00', '84.00', '94.94');
INSERT INTO `score` VALUES ('228', '6', '4', '93.00', '86.00', '92.25');
INSERT INTO `score` VALUES ('229', '7', '4', '90.00', '80.00', '95.44');
INSERT INTO `score` VALUES ('230', '8', '4', '83.00', '85.00', '94.80');
INSERT INTO `score` VALUES ('231', '9', '4', '90.00', '81.00', '91.49');
INSERT INTO `score` VALUES ('232', '10', '4', '93.00', '85.00', '95.99');
INSERT INTO `score` VALUES ('233', '11', '4', '97.00', '88.00', '95.41');
INSERT INTO `score` VALUES ('234', '12', '4', '86.00', '85.00', '95.31');
INSERT INTO `score` VALUES ('235', '13', '4', '63.00', '78.00', '90.30');
INSERT INTO `score` VALUES ('236', '14', '4', '85.00', '83.00', '94.55');
INSERT INTO `score` VALUES ('237', '15', '4', '94.00', '82.00', '84.66');
INSERT INTO `score` VALUES ('238', '16', '4', '87.00', '70.00', '90.77');
INSERT INTO `score` VALUES ('239', '17', '4', '96.00', '85.00', '96.27');
INSERT INTO `score` VALUES ('240', '18', '4', '98.00', '84.00', '93.01');
INSERT INTO `score` VALUES ('241', '19', '4', '87.00', '86.00', '90.76');
INSERT INTO `score` VALUES ('242', '20', '4', '85.00', '80.00', '89.50');
INSERT INTO `score` VALUES ('243', '21', '4', '94.00', '86.00', '93.21');
INSERT INTO `score` VALUES ('244', '22', '4', '87.00', '80.00', '78.94');
INSERT INTO `score` VALUES ('245', '23', '4', '79.00', '82.00', '93.08');
INSERT INTO `score` VALUES ('246', '24', '4', '89.00', '87.00', '94.45');
INSERT INTO `score` VALUES ('247', '25', '4', '99.00', '87.00', '95.42');
INSERT INTO `score` VALUES ('248', '26', '4', '91.00', '80.00', '92.11');
INSERT INTO `score` VALUES ('249', '27', '4', '85.00', '81.00', '91.13');
INSERT INTO `score` VALUES ('250', '28', '4', '85.00', '80.00', '91.17');
INSERT INTO `score` VALUES ('251', '29', '4', '62.00', '82.00', '89.03');
INSERT INTO `score` VALUES ('252', '30', '4', '88.00', '86.00', '92.10');
INSERT INTO `score` VALUES ('253', '31', '4', '81.00', '84.00', '90.20');
INSERT INTO `score` VALUES ('254', '32', '4', '85.00', '87.00', '91.56');
INSERT INTO `score` VALUES ('255', '33', '4', '86.00', '80.00', '94.72');
INSERT INTO `score` VALUES ('256', '34', '4', '71.00', '85.00', '91.45');
INSERT INTO `score` VALUES ('257', '35', '4', '46.00', '85.00', '92.23');
INSERT INTO `score` VALUES ('258', '36', '4', '71.00', '81.00', '90.84');
INSERT INTO `score` VALUES ('259', '37', '4', '80.00', '82.00', '83.29');
INSERT INTO `score` VALUES ('260', '38', '4', '85.00', '83.00', '94.41');
INSERT INTO `score` VALUES ('261', '39', '4', '74.00', '75.00', '91.41');
INSERT INTO `score` VALUES ('262', '40', '4', '84.00', '83.00', '96.16');
INSERT INTO `score` VALUES ('263', '41', '4', '83.00', '85.00', '76.64');
INSERT INTO `score` VALUES ('264', '42', '4', '88.00', '85.00', '90.04');
INSERT INTO `score` VALUES ('265', '43', '4', '94.00', '96.00', '91.41');
INSERT INTO `score` VALUES ('266', '44', '4', '81.00', '80.00', '94.54');
INSERT INTO `score` VALUES ('267', '45', '4', '99.00', '86.00', '89.16');
INSERT INTO `score` VALUES ('268', '46', '4', '98.00', '87.00', '91.51');
INSERT INTO `score` VALUES ('269', '47', '4', '98.00', '86.00', '94.22');
INSERT INTO `score` VALUES ('270', '48', '4', '94.00', '85.00', '96.49');
INSERT INTO `score` VALUES ('271', '49', '4', '91.00', '86.00', '92.71');
INSERT INTO `score` VALUES ('272', '50', '4', '93.00', '82.00', '90.80');
INSERT INTO `score` VALUES ('273', '51', '4', '98.00', '90.00', '94.50');
INSERT INTO `score` VALUES ('274', '52', '4', '98.00', '88.00', '95.70');
INSERT INTO `score` VALUES ('275', '53', '4', '98.00', '83.00', '95.91');
INSERT INTO `score` VALUES ('276', '54', '4', '89.00', '87.00', '96.23');
INSERT INTO `score` VALUES ('277', '55', '4', '75.00', '70.00', '90.51');
INSERT INTO `score` VALUES ('278', '56', '4', '95.00', '85.00', '92.04');
INSERT INTO `score` VALUES ('279', '57', '4', '91.00', '81.00', '89.21');
INSERT INTO `score` VALUES ('280', '58', '4', '83.00', '77.00', '89.32');
INSERT INTO `score` VALUES ('281', '59', '4', '91.00', '85.00', '91.88');
INSERT INTO `score` VALUES ('282', '60', '4', '96.00', '84.00', '93.01');
INSERT INTO `score` VALUES ('283', '61', '4', '93.00', '88.00', '93.90');
INSERT INTO `score` VALUES ('284', '62', '4', '92.00', '84.00', '92.93');
INSERT INTO `score` VALUES ('285', '63', '4', '94.00', '61.00', '90.05');
INSERT INTO `score` VALUES ('286', '64', '4', '93.00', '88.00', '93.90');
INSERT INTO `score` VALUES ('287', '65', '4', '98.00', '87.00', '90.58');
INSERT INTO `score` VALUES ('288', '66', '4', '89.00', '83.00', '91.28');
INSERT INTO `score` VALUES ('289', '67', '4', '94.00', '84.00', '96.43');
INSERT INTO `score` VALUES ('290', '68', '4', '94.00', '85.00', '92.01');
INSERT INTO `score` VALUES ('291', '69', '4', '92.00', '83.00', '90.83');
INSERT INTO `score` VALUES ('292', '70', '4', '89.00', '85.00', '95.52');
INSERT INTO `score` VALUES ('293', '71', '4', '87.00', '74.00', '91.97');
INSERT INTO `score` VALUES ('294', '72', '4', '96.00', '86.00', '92.88');
INSERT INTO `score` VALUES ('295', '73', '4', '87.00', '85.00', '90.26');
INSERT INTO `score` VALUES ('296', '74', '4', '96.00', '82.00', '90.06');
INSERT INTO `score` VALUES ('297', '75', '1', '64.00', '62.00', '60.00');
INSERT INTO `score` VALUES ('298', '76', '1', '98.00', '79.00', '66.00');
INSERT INTO `score` VALUES ('299', '77', '1', '83.00', '65.00', '65.00');
INSERT INTO `score` VALUES ('300', '78', '1', '62.00', '60.00', '62.00');
INSERT INTO `score` VALUES ('301', '79', '1', '68.00', '86.00', '85.00');
INSERT INTO `score` VALUES ('302', '80', '1', '66.00', '95.00', '63.00');
INSERT INTO `score` VALUES ('303', '81', '1', '62.00', '93.00', '67.00');
INSERT INTO `score` VALUES ('304', '82', '1', '91.00', '69.00', '68.00');
INSERT INTO `score` VALUES ('305', '83', '1', '87.00', '84.00', '70.00');
INSERT INTO `score` VALUES ('306', '84', '1', '71.00', '65.00', '78.00');
INSERT INTO `score` VALUES ('307', '85', '1', '96.00', '80.00', '68.00');
INSERT INTO `score` VALUES ('308', '86', '1', '77.00', '60.00', '62.00');
INSERT INTO `score` VALUES ('309', '87', '1', '90.00', '87.00', '69.00');
INSERT INTO `score` VALUES ('310', '88', '1', '62.00', '82.00', '79.00');
INSERT INTO `score` VALUES ('311', '89', '1', '78.00', '98.00', '97.00');
INSERT INTO `score` VALUES ('312', '90', '1', '71.00', '65.00', '99.00');
INSERT INTO `score` VALUES ('313', '91', '1', '87.00', '70.00', '72.00');
INSERT INTO `score` VALUES ('314', '92', '1', '95.00', '66.00', '77.00');
INSERT INTO `score` VALUES ('315', '93', '1', '65.00', '82.00', '94.00');
INSERT INTO `score` VALUES ('316', '94', '1', '84.00', '63.00', '95.00');
INSERT INTO `score` VALUES ('317', '95', '1', '90.00', '67.00', '74.00');
INSERT INTO `score` VALUES ('318', '96', '1', '73.00', '96.00', '95.00');
INSERT INTO `score` VALUES ('319', '97', '1', '85.00', '76.00', '69.00');
INSERT INTO `score` VALUES ('320', '98', '1', '83.00', '80.00', '93.00');
INSERT INTO `score` VALUES ('321', '99', '1', '92.00', '90.00', '80.00');
INSERT INTO `score` VALUES ('322', '100', '1', '98.00', '80.00', '71.00');
INSERT INTO `score` VALUES ('323', '101', '1', '66.00', '67.00', '87.00');
INSERT INTO `score` VALUES ('324', '102', '1', '75.00', '71.00', '81.00');
INSERT INTO `score` VALUES ('325', '103', '1', '81.00', '90.00', '66.00');
INSERT INTO `score` VALUES ('326', '104', '1', '75.00', '95.00', '94.00');
INSERT INTO `score` VALUES ('327', '105', '1', '84.00', '92.00', '83.00');
INSERT INTO `score` VALUES ('328', '106', '1', '72.00', '74.00', '61.00');
INSERT INTO `score` VALUES ('329', '107', '1', '88.00', '92.00', '90.00');
INSERT INTO `score` VALUES ('330', '108', '1', '73.00', '99.00', '81.00');
INSERT INTO `score` VALUES ('331', '109', '1', '86.00', '99.00', '61.00');
INSERT INTO `score` VALUES ('332', '110', '1', '60.00', '65.00', '83.00');
INSERT INTO `score` VALUES ('333', '111', '1', '84.00', '73.00', '95.00');
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '1', '193610202301', '罗皓钟', '女', '17');
INSERT INTO `student` VALUES ('2', '1', '193610202302', '徐保健', '男', '19');
INSERT INTO `student` VALUES ('3', '1', '193610202304', '何镕钦', '女', '17');
INSERT INTO `student` VALUES ('4', '1', '193610202305', '孔维粉', '女', '20');
INSERT INTO `student` VALUES ('5', '1', '193610202306', '周婷', '男', '17');
INSERT INTO `student` VALUES ('6', '1', '193610202307', '杨彦斌', '男', '18');
INSERT INTO `student` VALUES ('7', '1', '193610202308', '谢俊清', '男', '20');
INSERT INTO `student` VALUES ('8', '1', '193610202310', '杨艳', '男', '20');
INSERT INTO `student` VALUES ('9', '1', '193610202311', '陈永康', '男', '20');
INSERT INTO `student` VALUES ('10', '1', '193610202312', '李雄波', '女', '19');
INSERT INTO `student` VALUES ('11', '1', '193610202313', '虎月香', '女', '17');
INSERT INTO `student` VALUES ('12', '1', '193610202314', '李娟', '女', '19');
INSERT INTO `student` VALUES ('13', '1', '193610202315', '闪正', '女', '20');
INSERT INTO `student` VALUES ('14', '1', '193610202316', '王龙', '女', '18');
INSERT INTO `student` VALUES ('15', '1', '193610202317', '杨勇飞', '男', '18');
INSERT INTO `student` VALUES ('16', '1', '193610202318', '徐浩文', '男', '18');
INSERT INTO `student` VALUES ('17', '1', '193610202320', '聂跃庆', '男', '18');
INSERT INTO `student` VALUES ('18', '1', '193610202321', '王云锋', '女', '18');
INSERT INTO `student` VALUES ('19', '1', '193610202322', '李子龙', '男', '18');
INSERT INTO `student` VALUES ('20', '1', '193610202323', '董康明', '男', '17');
INSERT INTO `student` VALUES ('21', '1', '193610202324', '韩蓉', '男', '18');
INSERT INTO `student` VALUES ('22', '1', '193610202325', '李冬余', '男', '20');
INSERT INTO `student` VALUES ('23', '1', '193610202326', '田彬', '男', '17');
INSERT INTO `student` VALUES ('24', '1', '193610202327', '陈李泉', '男', '18');
INSERT INTO `student` VALUES ('25', '1', '193610202328', '陈释韬', '男', '20');
INSERT INTO `student` VALUES ('26', '1', '193610202329', '杨坤', '男', '18');
INSERT INTO `student` VALUES ('27', '1', '193610202331', '陈燕', '女', '19');
INSERT INTO `student` VALUES ('28', '1', '193610202332', '王秀英', '男', '18');
INSERT INTO `student` VALUES ('29', '1', '193610202333', '唐斌', '女', '18');
INSERT INTO `student` VALUES ('30', '1', '193610202334', '卢军', '男', '19');
INSERT INTO `student` VALUES ('31', '1', '193610202335', '朱绍东', '男', '20');
INSERT INTO `student` VALUES ('32', '1', '193610202336', '田孟珊', '女', '17');
INSERT INTO `student` VALUES ('33', '1', '193610202338', '张玉苗', '男', '17');
INSERT INTO `student` VALUES ('34', '1', '193610202340', '钱黄芬', '女', '20');
INSERT INTO `student` VALUES ('35', '1', '193610202341', '高源', '男', '17');
INSERT INTO `student` VALUES ('36', '1', '193610202342', '刘春香', '男', '18');
INSERT INTO `student` VALUES ('37', '1', '193610202343', '刀新明', '男', '20');
INSERT INTO `student` VALUES ('38', '2', '193610202623', '卢冉念', '女', '19');
INSERT INTO `student` VALUES ('39', '2', '193610202401', '岩奔', '男', '19');
INSERT INTO `student` VALUES ('40', '2', '193610202402', '肖明安', '男', '18');
INSERT INTO `student` VALUES ('41', '2', '193610202404', '羊禹飞', '男', '18');
INSERT INTO `student` VALUES ('42', '2', '193610202405', '朱洪春', '男', '17');
INSERT INTO `student` VALUES ('43', '2', '193610202407', '罗天飞', '男', '17');
INSERT INTO `student` VALUES ('44', '2', '193610202409', '罗建英', '男', '20');
INSERT INTO `student` VALUES ('45', '2', '193610202410', '杨春明', '男', '20');
INSERT INTO `student` VALUES ('46', '2', '193610202411', '冯润泽', '女', '17');
INSERT INTO `student` VALUES ('47', '2', '193610202412', '白简者', '男', '20');
INSERT INTO `student` VALUES ('48', '2', '193610202413', '张子鹏', '男', '17');
INSERT INTO `student` VALUES ('49', '2', '193610202414', '王娇', '女', '20');
INSERT INTO `student` VALUES ('50', '2', '193610202415', '银源福', '女', '20');
INSERT INTO `student` VALUES ('51', '2', '193610202416', '饶文丽', '男', '20');
INSERT INTO `student` VALUES ('52', '2', '193610202418', '郭超', '男', '19');
INSERT INTO `student` VALUES ('53', '2', '193610202419', '黄继国', '男', '17');
INSERT INTO `student` VALUES ('54', '2', '193610202420', '李鸿媛', '女', '19');
INSERT INTO `student` VALUES ('55', '2', '193610202421', '冷旭涵', '女', '20');
INSERT INTO `student` VALUES ('56', '2', '193610202422', '黄安芮', '女', '18');
INSERT INTO `student` VALUES ('57', '2', '193610202423', '颜权昌', '女', '18');
INSERT INTO `student` VALUES ('58', '2', '193610202424', '王丽英', '男', '18');
INSERT INTO `student` VALUES ('59', '2', '193610202426', '杨海红', '男', '17');
INSERT INTO `student` VALUES ('60', '2', '193610202429', '樊潞泓', '女', '20');
INSERT INTO `student` VALUES ('61', '2', '193610202430', '苏丽霜', '女', '20');
INSERT INTO `student` VALUES ('62', '2', '193610202431', '李正勤', '男', '18');
INSERT INTO `student` VALUES ('63', '2', '193610202432', '龙诚', '男', '18');
INSERT INTO `student` VALUES ('64', '2', '193610202433', '徐贵刚', '男', '17');
INSERT INTO `student` VALUES ('65', '2', '193610202435', '贺光玲', '男', '18');
INSERT INTO `student` VALUES ('66', '2', '193610202436', '丁宇', '男', '19');
INSERT INTO `student` VALUES ('67', '2', '193610202437', '杨卓', '男', '19');
INSERT INTO `student` VALUES ('68', '2', '193610202438', '何涛', '女', '18');
INSERT INTO `student` VALUES ('69', '2', '193610202439', '王强', '男', '17');
INSERT INTO `student` VALUES ('70', '2', '193610202440', '李韬', '男', '17');
INSERT INTO `student` VALUES ('71', '2', '193610202441', '邓宏恩', '男', '20');
INSERT INTO `student` VALUES ('72', '2', '193610202442', '杨明金', '女', '17');
INSERT INTO `student` VALUES ('73', '2', '193610202443', '邓梓林', '女', '18');
INSERT INTO `student` VALUES ('74', '3', '193610202618', '宝清龙', '男', '17');
INSERT INTO `student` VALUES ('75', '3', '193610202502', '陈凤', '女', '20');
INSERT INTO `student` VALUES ('76', '3', '193610202503', '陈刘珂雯', '男', '20');
INSERT INTO `student` VALUES ('77', '3', '193610202504', '程瑞', '男', '17');
INSERT INTO `student` VALUES ('78', '3', '193610202506', '代涛', '女', '20');
INSERT INTO `student` VALUES ('79', '3', '193610202507', '邓国飞', '男', '17');
INSERT INTO `student` VALUES ('80', '3', '193610202508', '邓礼鹏', '女', '18');
INSERT INTO `student` VALUES ('81', '3', '193610202511', '杜云聪', '男', '20');
INSERT INTO `student` VALUES ('82', '3', '193610202512', '高元芬', '男', '20');
INSERT INTO `student` VALUES ('83', '3', '193610202513', '顾太香', '男', '19');
INSERT INTO `student` VALUES ('84', '3', '193610202514', '郭秀芬', '男', '18');
INSERT INTO `student` VALUES ('85', '3', '193610202515', '蒋嘉慧', '女', '20');
INSERT INTO `student` VALUES ('86', '3', '193610202516', '李思民', '女', '17');
INSERT INTO `student` VALUES ('87', '3', '193610202517', '李宗阳', '女', '18');
INSERT INTO `student` VALUES ('88', '3', '193610202519', '廖中燕', '男', '19');
INSERT INTO `student` VALUES ('89', '3', '193610202522', '廖祖涵', '男', '17');
INSERT INTO `student` VALUES ('90', '3', '193610202523', '刘泽龙', '男', '20');
INSERT INTO `student` VALUES ('91', '3', '193610202526', '陆玉宽', '男', '19');
INSERT INTO `student` VALUES ('92', '3', '193610202528', '马梦威', '女', '20');
INSERT INTO `student` VALUES ('93', '3', '193610202529', '马士颖', '男', '18');
INSERT INTO `student` VALUES ('94', '3', '193610202531', '马爽', '男', '20');
INSERT INTO `student` VALUES ('95', '3', '193610202534', '彭利磊', '女', '20');
INSERT INTO `student` VALUES ('96', '3', '193610202535', '全勇', '女', '18');
INSERT INTO `student` VALUES ('97', '3', '193610202536', '唐靖焱', '男', '20');
INSERT INTO `student` VALUES ('98', '3', '193610202537', '王浩', '男', '18');
INSERT INTO `student` VALUES ('99', '3', '193610202539', '王陆兵', '女', '17');
INSERT INTO `student` VALUES ('100', '3', '193610202540', '王有骅', '男', '20');
INSERT INTO `student` VALUES ('101', '3', '193610202541', '向世文', '男', '17');
INSERT INTO `student` VALUES ('102', '3', '193610202608', '熊梧淞', '男', '17');
INSERT INTO `student` VALUES ('103', '3', '193610202609', '徐苓棚', '女', '17');
INSERT INTO `student` VALUES ('104', '3', '193610202613', '杨肖', '男', '18');
INSERT INTO `student` VALUES ('105', '3', '193610202615', '杨雨青', '男', '19');
INSERT INTO `student` VALUES ('106', '3', '193610202617', '张健', '女', '20');
INSERT INTO `student` VALUES ('107', '3', '193610202619', '张瑾', '男', '20');
INSERT INTO `student` VALUES ('108', '3', '193610202621', '张菊', '男', '20');
INSERT INTO `student` VALUES ('109', '3', '193610202624', '张龙', '男', '19');
INSERT INTO `student` VALUES ('110', '3', '193610202603', '周行', '男', '17');
INSERT INTO `student` VALUES ('111', '3', '193610202605', '朱兴尚', '女', '18');
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '1500077', '王威', '男', '159********', '讲师');
INSERT INTO `teacher` VALUES ('2', '1500044', '鲁卫秀', '女', '183********', '班主任');
INSERT INTO `teacher` VALUES ('3', '1500027', '李松龄', '女', '184********', '班主任');
2、查询自己的信息，显示的内容包括：学号、姓名、性别、年龄。
SELECT no 学号,name 姓名,sex 性别,age 年龄 FROM student WHERE name='杨明金'
3、查询自己所在班级的学生信息，显示内容包括：班级、学号、姓名、性别、年龄。
SELECT c.name 班级,no 学号,s.name 姓名,sex 性别,age 年龄 FROM student s,class c
WHERE s.class_id = c.id
AND c.name='19计算机网络技术二班'
4、查询所有讲师的带班信息，显示内容包括：讲师姓名、班级、班级人数。
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 type='讲师'
GROUP BY c.name
5、查询每个讲师带了多少学生，显示内容包括：讲师姓名，学生数。
SELECT t.name 讲师姓名,COUNT(*) 学生数 FROM student s,class c,teacher t
WHERE s.class_id=c.id AND c.lecturer_id = t.id
AND type='讲师'
6、在教师表（teacher）中插入一条新的记录：
INSERT INTO teacher VALUES(8,'1500086','王威','男','142********','讲师')
7、将教师“王威”的工号修改为“1500098”。
UPDATE teacher SET no='1500098'
WHERE NAME='王威'
8、在教师表（teacher）中删除姓名为“王威”的记录。
DELETE FROM teacher WHERE name='王威'
如果有外键约束则提示：
Cannot delete or update a parent row: a foreign key constraint fails (`studentsys`.`class`, CONSTRAINT `fk_lecturer_id` FOREIGN KEY (`lecturer_id`) REFERENCES `teacher` (`id`))
有外键约束则，需要先删除外键约束。
ALTER TABLE class DROP FOREIGN KEY fk_lecturer_id
删除后在添加上
ALTER TABLE class ADD CONSTRAINT fk_lecturer_id FOREIGN KEY(lecturer_id) REFERENCES teacher(id)
9、查询各班“HTML5网页设计”的机试成绩，显示内容包括：班级名称、最高分、最低分、平均分。
SELECT 班级表.name 班级名称,MAX(test_score) 最高分,MIN(test_score) 最低分,SUM(test_score)
FROM class 班级表, score 成绩表,student 学生表,course 课程表
WHERE 成绩表.course_id=课程表.id AND 成绩表.student_id=学生表.id AND 学生表.class_id=班级表.id
AND 课程表.name='HTML5网页设计'
GROUP BY 班级名称
10、查询自己的各科成绩，显示内容包括：学号、姓名、课程名称、机试成绩、项目成绩、平时成绩、综合成绩。其中，综合成绩=机试成绩*40%+平时成绩*40%。
SELECT 学生表.no 学号,学生表.name 姓名,课程表.name 课程名称,成绩表.test_score 机试成绩,成绩表.project_score 项目成绩
,成绩表.usual_score 平时成绩,成绩表.project_score*0.4+成绩表.usual_score*0.4 综合成绩
FROM course 课程表, score 成绩表,student 学生表
WHERE 成绩表.course_id=课程表.id AND 成绩表.student_id=学生表.id
AND 学生表.name='杨明金'
11、在学生表（student）中截取你所在班级所有学生的学号后四位作为每位学生的新编号，显示内容包括：新编号、姓名。
SELECT RIGHT(no,4) 新编号,s.name 姓名 FROM student s,class c
WHERE s.class_id=c.id AND c.name='19计算机网络技术二班'
12、查询自己所有成绩的平均分，显示内容包括：学号、姓名、机试平均分、项目平均分、平时平均分。
SELECT no 学号,st.name 姓名,avg(test_score) 机式平均分,avg(project_score) 项目平均分,avg(usual_score) 平时平均分
FROM student st,score sc
WHERE sc.student_id=st.id AND st.name='杨明金'
13、查询得到所有学生中各姓氏的人数，并按照人数降序排序，显示内容包括：姓氏、人数。
SELECT LEFT(name,1) 姓氏,COUNT(*) 人数 FROM student
GROUP BY 姓氏
ORDER BY 人数 DESC
14、在学生表（student）中将性别为“无”的学生的性别改为“男”。
UPDATE student SET sex='男'
WHERE sexNULL
15、查询得到所有学生中姓氏为“杨”、“李”、“王”的男生和女生的人数，并按照姓氏升序排序。
SELECT LEFT(name,1) 姓氏,sex 性别,COUNT(*) 人数 FROM student
WHERE (name LIKE '杨%') OR (name LIKE '李%') OR (name LIKE '王%')
GROUP BY 性别,姓氏
ORDER BY 姓氏
16、查询所有学生中总共有多少个姓氏。
SELECT COUNT(DISTINCT LEFT(name,1)) 姓氏数量 FROM student
17、查询所有学生的成绩信息，显示内容包括：学号、姓名、班级名称、讲师、课程名称、机试成绩、项目成绩、平时成绩、综合成绩，并按照综合成绩降序排序。
SELECT 学生表.no 学号,学生表.name 姓名,班级表.name 班级名称,老师表.name 讲师,成绩表.test_score 机试成绩
,成绩表.project_score 项目成绩,成绩表.usual_score 平时成绩,成绩表.project_score*0.4+成绩表.usual_score*0.4 综合成绩
FROM course 课程表, score 成绩表,student 学生表,class 班级表,teacher 老师表
WHERE 课程表.id=成绩表.course_id AND 成绩表.student_id=学生表.id AND 学生表.class_id=班级表.id AND 班级表.lecturer_id=老师表.id
ORDER BY 综合成绩 DESC
18、查询各讲师各门课程的平均分，显示内容包括：讲师、课程名称、机试成绩平均分、项目成绩平均分、平时成绩平均分、综合成绩平均分,并按照课程名称升序排序，综合成绩平均分降序排序。
SELECT 老师表.name 讲师,课程表.name 课程名称,ROUND(AVG(成绩表.test_score),2)机试成绩平均分,ROUND(AVG(成绩表.project_score),2)项目成绩平均分
,ROUND(AVG(成绩表.usual_score),2)平时成绩平均分,ROUND(AVG(成绩表.project_score*0.4+成绩表.usual_score*0.4),2)综合成绩平均分
FROM course 课程表, score 成绩表,student 学生表,class 班级表,teacher 老师表
WHERE 课程表.id=成绩表.course_id AND 成绩表.student_id=学生表.id AND 学生表.class_id=班级表.id AND 班级表.lecturer_id=老师表.id
GROUP BY 讲师,课程名称
ORDER BY 课程名称 ASC,综合成绩平均分 DESC
19、查询各讲师各班级各门课程的平均分，显示内容包括：讲师、班级名称、课程名称、机试成绩平均分、项目成绩平均分、平时成绩平均分、综合成绩平均分,并按班级名称降序排序。
SELECT 老师表.name 讲师,班级表.name 班级名称,课程表.name 课程名称,ROUND(AVG(成绩表.test_score),2)机试成绩平均分,ROUND(AVG(成绩表.project_score),2)项目成绩平均分
,ROUND(AVG(成绩表.usual_score),2)平时成绩平均分,ROUND(AVG(成绩表.project_score*0.4+成绩表.usual_score*0.4),2)综合成绩平均分
FROM course 课程表, score 成绩表,student 学生表,class 班级表,teacher 老师表
WHERE 课程表.id=成绩表.course_id AND 成绩表.student_id=学生表.id AND 学生表.class_id=班级表.id AND 班级表.lecturer_id=老师表.id
GROUP BY 讲师,班级名称,课程名称
ORDER BY 班级名称 DESC
作者：明金同学
相关文章
Chynna
2021-04-13
Kamilia
2020-01-12
Olga
2023-07-20
Elizabeth
2023-07-20
Ianthe
2023-07-20
Valora
2023-07-20
Phemia
2023-07-20
Tia
2023-07-20
Summer
2023-07-20
Fredrica
2023-07-20
Bianca
2023-07-20
Jacuqeline
2023-07-20
Maha
2023-07-21
Valentina
2023-07-21
Edie
2023-07-21
Bonnie
2023-07-21
Laila
2023-07-22
Ilona
2023-07-22
Xylona
2023-07-22