mysql统计查询和索引练习

课程数据表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;

--------------------视图-------------------------------------------------------