课程数据表course
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for course -- ---------------------------- DROP TABLE IF EXISTS `course`; CREATE TABLE `course` ( `cno` varchar(5) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `cname` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `cperiod` int NULL DEFAULT NULL, `credit` float NULL DEFAULT NULL, `ctno` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, PRIMARY KEY (`cno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of course -- ---------------------------- INSERT INTO `course` VALUES (‘c001‘, ‘文学欣赏‘, 40, 1.5, ‘t001‘); INSERT INTO `course` VALUES (‘c002‘, ‘中国历史文化‘, 60, 2, ‘t003‘); INSERT INTO `course` VALUES (‘c003‘, ‘视频编辑‘, 70, 2.5, ‘t002‘); INSERT INTO `course` VALUES (‘c004‘, ‘音乐欣赏‘, 40, 1.5, ‘t004‘); SET FOREIGN_KEY_CHECKS = 1;
学生选课成绩表elective
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for elective -- ---------------------------- DROP TABLE IF EXISTS `elective`; CREATE TABLE `elective` ( `sno` int NULL DEFAULT NULL, `cno` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `score` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of elective -- ---------------------------- INSERT INTO `elective` VALUES (4, ‘c004‘, ‘80‘); INSERT INTO `elective` VALUES (5, ‘c002‘, ‘60‘); INSERT INTO `elective` VALUES (6, ‘c004‘, ‘99‘); INSERT INTO `elective` VALUES (7, ‘c004‘, ‘89‘); INSERT INTO `elective` VALUES (8, ‘c002‘, ‘59‘); INSERT INTO `elective` VALUES (9, ‘c002‘, ‘40‘); INSERT INTO `elective` VALUES (4, ‘c001‘, ‘88‘); INSERT INTO `elective` VALUES (5, ‘c001‘, ‘78‘); INSERT INTO `elective` VALUES (1, ‘c002‘, ‘88‘); INSERT INTO `elective` VALUES (1, ‘c003‘, ‘79‘); INSERT INTO `elective` VALUES (1, ‘c004‘, ‘80‘); INSERT INTO `elective` VALUES (2, ‘c001‘, ‘99‘); INSERT INTO `elective` VALUES (2, ‘c002‘, ‘97‘); INSERT INTO `elective` VALUES (2, ‘c003‘, ‘98‘); INSERT INTO `elective` VALUES (2, ‘c004‘, ‘95‘); INSERT INTO `elective` VALUES (3, ‘c001‘, ‘82‘); INSERT INTO `elective` VALUES (3, ‘c002‘, ‘87‘); INSERT INTO `elective` VALUES (3, ‘c003‘, ‘96‘); INSERT INTO `elective` VALUES (3, ‘c004‘, ‘78‘); INSERT INTO `elective` VALUES (4, ‘c003‘, ‘89‘); INSERT INTO `elective` VALUES (4, ‘c002‘, ‘67‘); SET FOREIGN_KEY_CHECKS = 1;
学生表studentinfo
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for studentinfo -- ---------------------------- DROP TABLE IF EXISTS `studentinfo`; CREATE TABLE `studentinfo` ( `sno` int NOT NULL, `sname` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `sgender` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `sbirth` date NULL DEFAULT NULL, `sclass` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, PRIMARY KEY (`sno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of studentinfo -- ---------------------------- INSERT INTO `studentinfo` VALUES (1, ‘李丹‘, ‘女‘, ‘1998-07-10‘, ‘网络技术2‘); INSERT INTO `studentinfo` VALUES (2, ‘赵蛋‘, ‘男‘, ‘1990-12-26‘, ‘电子商务2‘); INSERT INTO `studentinfo` VALUES (3, ‘薛兆丰‘, ‘男‘, ‘1999-07-16‘, ‘软件开发1‘); INSERT INTO `studentinfo` VALUES (4, ‘李大嘴‘, ‘男‘, ‘1995-04-08‘, ‘电子商务2‘); INSERT INTO `studentinfo` VALUES (5, ‘李样‘, ‘女‘, ‘1998-02-04‘, ‘网络技术1‘); INSERT INTO `studentinfo` VALUES (6, ‘王撕葱‘, ‘男‘, ‘1991-01-04‘, ‘电子商务2‘); INSERT INTO `studentinfo` VALUES (7, ‘郑板桥‘, ‘女‘, ‘1991-08-08‘, ‘软件开发3‘); INSERT INTO `studentinfo` VALUES (8, ‘马东‘, ‘男‘, ‘1991-08-09‘, ‘软件开发3‘); INSERT INTO `studentinfo` VALUES (9, ‘马晓东‘, ‘男‘, ‘1991-03-04‘, ‘软件开发2‘); SET FOREIGN_KEY_CHECKS = 1;
教师表teacher
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for teacher -- ---------------------------- DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `tno` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `tname` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `tgender` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, `tedu` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL, PRIMARY KEY (`tno`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES (‘t001‘, ‘吴亚飞‘, ‘男‘, ‘本科‘); INSERT INTO `teacher` VALUES (‘t002‘, ‘李琦‘, ‘男‘, ‘硕士‘); INSERT INTO `teacher` VALUES (‘t003‘, ‘王艳红‘, ‘女‘, ‘硕士‘); INSERT INTO `teacher` VALUES (‘t004‘, ‘马志超‘, ‘男‘, ‘博士‘); INSERT INTO `teacher` VALUES (‘t005‘, ‘万丽‘, ‘女‘, ‘硕士‘); SET FOREIGN_KEY_CHECKS = 1;
-- -------------------统计查询------------------------------------------- -- 统计男生 和女生人数 SELECT sgender,COUNT(*) AS 人数 FROM studentinfo GROUP BY sgender -- 查询elective表，统计并输出每个学生所选课程数目及平均分。 SELECT sno,COUNT(cno) AS 选修课程数目, AVG(score) AS 平均分 FROM elective GROUP BY sno -- 查询elective表，统计并输出每门课程所选学生人数及最高分。 SELECT cno , COUNT(sno), MAX(score) FROM elective GROUP BY cno -- 查询elective表中每门课成绩都在70～90分的学生的学号。 SELECT sno FROM elective GROUP BY sno HAVING MIN(score)>=70 AND MAX(score)<=90 -- 查询至少选修了三门课程的学生的学号。 SELECT sno,COUNT(*) AS 选修课程大于等于三 FROM elective GROUP BY sno HAVING COUNT(*)>=3 -- --------------多表查询--------------------------------------------- -- 交叉连接 CROSS JOIN SELECT * FROM course CROSS JOIN teacher -- SELECT * FROM course LEFT JOIN teacher ON course.ctno = teacher.tno -- 查询“学生选课”数据库，输出考试成绩不及格学生的学号、姓名、课程号、成绩。 SELECT st.sno,st.sname,el.cno,el.score FROM studentinfo st JOIN elective el ON st.sno = el.sno WHERE el.score < 60 ; -- 查询“学生选课”数据库，输出考试成绩不及格学生的学号、姓名、课程名、成绩。 SELECT st.sno, st.sname, co.cname, el.score FROM studentinfo AS st JOIN elective AS el ON st.sno = el.sno JOIN course AS co ON el.cno = co.cno WHERE el.score<60 ; -- 或者如下 SELECT st.sno, st.sname, co.cname, el.score FROM studentinfo AS st , elective AS el , course AS co WHERE st.sno = el.sno AND el.cno = co.cno AND el.score<60 -- 查询“学生选课”数据库，输出所有教师教授的课程信息，没有教授课程的教师也要列出。 SELECT * FROM teacher AS t LEFT JOIN course AS c ON t.tno = c.ctno -- 查询和学号为*****的学生在同一个班级的学生的学号和姓名 SELECT s2.sno , s2.sname FROM studentinfo AS s JOIN studentinfo AS s2 ON s.sclass = s2.sclass WHERE s.sno = 8 AND s2.sno != 8 -----------------子查询------------------------------------------- -- 查询“学生选课”数据库，输出选修了“音乐欣赏”这门课的所有学生的学号和成绩。 SELECT e.sno,e.score FROM elective AS e WHERE e.cno = (SELECT cno FROM course WHERE cname = ‘音乐欣赏‘) -- 查询“学生选课”数据库，输出选修了“音乐欣赏”这门课的所有学生的姓名,学号和成绩。 SELECT s.sname,e.sno,e.score FROM elective AS e LEFT JOIN studentinfo AS s ON e.sno = s.sno WHERE e.cno = (SELECT cno FROM course WHERE cname = ‘音乐欣赏‘) -- 查询“学生选课”数据库，输出年龄最大的学生的姓名。 SELECT MIN(sbirth) FROM studentinfo SELECT sname FROM studentinfo WHERE sbirth = ( SELECT MIN(sbirth) FROM studentinfo ) -- 查询“学生选课”数据库，输出“中国历史文化”这门课不及格的学生的姓名。 SELECT cno FROM course WHERE cname = ‘中国历史文化‘ SELECT s.sname AS 中国历史文化成绩不及格名单 FROM elective AS e LEFT JOIN studentinfo s ON s.sno = e.sno WHERE e.score < 60 AND e.cno = (SELECT cno FROM course WHERE cname = ‘中国历史文化‘); -- 查询“学生选课”数据库，输出考试不及格的学生的姓名。 SELECT sname AS 考试成绩不合格 FROM studentinfo WHERE sno IN (SELECT sno FROM elective WHERE score < 60); -- 查看“学生选课”数据库的teacher表,若不存在具有教授职称的教师，则显示所有教师的姓名和职称。 SELECT * FROM teacher WHERE NOT EXISTS(SELECT * FROM teacher WHERE tedu = ‘教授‘); -- 查询“学生选课”数据库的elective表，如果有需要补考的，就显示所有学生的成绩信息。如果没有需要补考的，就不输出任何信息。 SELECT * FROM elective WHERE EXISTS(SELECT * FROM elective WHERE score < 60); -- ------------------------------------索引------------------------------- -- 1.创建表的时候创建索引 -- 2.在已经存在的表上使用CREATE INDEX语句创建索引 -- CREATE [UNIQUE] [FULLTEXT] [SPATIAL] INDEX索引名 ON 表名(字段名[(长度)] [ASC | DESC]) -- 3.在已经存在的表上使用ALTER TABLE语句创建索引 -- ALTER TABLE 表名 ADD [UNIQUE] [FULLTEXT] [SPATIAL] INDEX索引名(字段名[(长度)] [ASC | DESC]) -- 给学生表 的学生姓名添加普通索引 CREATE INDEX stu_name ON studentinfo(sname); EXPLAIN SELECT sname FROM studentinfo SHOW CREATE TABLE studentinfo -- （1）使用DROP INDEX删除索引 DROP INDEX 索引名字 ON 表名 DROP INDEX stu_name ON studentinfo -- (2)使用ALETR TABLE删除索引 ALETR TABLE 表名 DROP INDEX 索引名字 ALETR TABLE studentinfo DROP INDEX stu_name; --------------------视图-------------------------------------------------------