python之資料庫

標籤：python   mysql   

# 作業內容‘‘‘1、查詢所有的課程的名稱以及對應的任課老師姓名2、查詢學生表中男女生各有多少人3、查詢物理成績等於100的學生的姓名4、查詢平均成績大於八十分的同學的姓名和平均成績5、查詢所有學生的學號，姓名，選課數，總成績6、 查詢姓李老師的個數7、 查詢沒有報李平老師課的學生姓名8、 查詢物理課程比生物課程高的學生的學號9、 查詢沒有同時選修物理課程和體育課程的學生姓名10、查詢掛科超過兩門(包括兩門)的學生姓名和班級‘‘‘# 建立庫和庫表，並插入資料‘‘‘班級表：class課程表：course成績表：score學生表：student老師表：teacher‘‘‘mysql> create database sqlexam;Query OK, 1 row affected (0.01 sec)mysql> use sqlexamDatabase changedmysql>mysql> SET NAMES utf8;Query OK, 0 rows affected (0.01 sec)mysql> SET FOREIGN_KEY_CHECKS = 0;Query OK, 0 rows affected (0.00 sec)mysql> DROP TABLE IF EXISTS `class`;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE TABLE `class` ( -> `cid` int(11) NOT NULL AUTO_INCREMENT, -> `caption` varchar(32) NOT NULL, -> PRIMARY KEY (`cid`) -> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO `class` VALUES (‘1‘, ‘三年二班‘), (‘2‘, ‘三年三班‘), (‘3‘, ‘ 一年二班‘), (‘4‘, ‘二年九班‘);Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> DROP TABLE IF EXISTS `course`;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE TABLE `course` ( -> `cid` int(11) NOT NULL AUTO_INCREMENT, -> `cname` varchar(32) NOT NULL, -> `teacher_id` int(11) NOT NULL, -> PRIMARY KEY (`cid`), -> KEY `fk_course_teacher` (`teacher_id`), -> CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`) -> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.04 sec)mysql> INSERT INTO `course` VALUES (‘1‘, ‘生物‘, ‘1‘), (‘2‘, ‘物理‘, ‘2‘), (‘3‘, ‘體育‘, ‘3‘), (‘4‘, ‘美術‘, ‘2‘);Query OK, 4 rows affected (0.01 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> DROP TABLE IF EXISTS `score`;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE TABLE `score` ( -> `sid` int(11) NOT NULL AUTO_INCREMENT, -> `student_id` int(11) NOT NULL, -> `course_id` int(11) NOT NULL, -> `num` int(11) NOT NULL, -> PRIMARY KEY (`sid`), -> KEY `fk_score_student` (`student_id`), -> KEY `fk_score_course` (`course_id`), -> CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`), -> CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`) -> ) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO `score` VALUES (‘1‘, ‘1‘, ‘1‘, ‘10‘), (‘2‘, ‘1‘, ‘2‘, ‘9‘), (‘5‘, ‘1‘, ‘4‘, ‘66‘), (‘6‘, ‘2‘, ‘1‘, ‘8‘), (‘8‘, ‘2‘, ‘3‘, ‘68‘), (‘9‘, ‘2‘, ‘4‘, ‘99‘), (‘10‘, ‘3‘, ‘1‘, ‘77‘), (‘11‘, ‘3‘, ‘2‘, ‘66‘), (‘12‘, ‘3‘, ‘3‘, ‘87‘), (‘13‘, ‘3‘, ‘4‘, ‘99‘), (‘14‘, ‘4‘, ‘1‘, ‘79‘), (‘15‘, ‘4‘, ‘2‘, ‘11‘), (‘16‘, ‘4‘, ‘3‘, ‘67‘), (‘17‘, ‘4‘, ‘4‘, ‘100‘), (‘18‘, ‘5‘, ‘1‘, ‘79‘), (‘19‘, ‘5‘, ‘2‘, ‘11‘), (‘20‘, ‘5‘, ‘3‘, ‘67‘), (‘21‘, ‘5‘, ‘4‘, ‘100‘), (‘22‘, ‘6‘, ‘1‘, ‘9‘), (‘23‘, ‘6‘, ‘2‘, ‘100‘), (‘24‘, ‘6‘, ‘3‘, ‘67‘), (‘25‘, ‘6‘, ‘4‘, ‘100‘), (‘26‘, ‘7‘, ‘1‘, ‘9‘), (‘27‘, ‘7‘, ‘2‘, ‘100‘), (‘28‘, ‘7‘, ‘3‘, ‘67‘), (‘29‘, ‘7‘, ‘4‘, ‘88‘), (‘30‘, ‘8‘, ‘1‘, ‘9‘), (‘31‘, ‘8‘, ‘2‘, ‘100‘), (‘32‘, ‘8‘, ‘3‘, ‘67‘), (‘33‘, ‘8‘, ‘4‘, ‘88‘), (‘34‘, ‘9‘, ‘1‘, ‘91‘), (‘35‘, ‘9‘, ‘2‘, ‘88‘), (‘36‘, ‘9‘, ‘3‘, ‘67‘), (‘37‘, ‘9‘, ‘4‘, ‘22‘), (‘38‘, ‘10‘, ‘1‘, ‘90‘), (‘39‘, ‘10‘, ‘2‘, ‘77‘), (‘40‘, ‘10‘, ‘3‘, ‘43‘), (‘41‘, ‘10‘, ‘4‘, ‘87‘), (‘42‘, ‘11‘, ‘1‘, ‘90‘), (‘43‘, ‘11‘, ‘2‘, ‘77‘), (‘44‘, ‘11‘, ‘3‘, ‘43‘), (‘45‘, ‘11‘, ‘4‘, ‘87‘), (‘46‘, ‘12‘, ‘1‘, ‘90‘), (‘47‘, ‘12‘, ‘2‘, ‘77‘), (‘48‘, ‘12‘, ‘3‘, ‘43‘), (‘49‘, ‘12‘, ‘4‘, ‘87‘), (‘52‘, ‘13‘, ‘3‘, ‘87‘);Query OK, 47 rows affected (0.01 sec)Records: 47 Duplicates: 0 Warnings: 0mysql> DROP TABLE IF EXISTS `student`;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE TABLE `student` ( -> `sid` int(11) NOT NULL AUTO_INCREMENT, -> `gender` char(1) NOT NULL, -> `class_id` int(11) NOT NULL, -> `sname` varchar(32) NOT NULL, -> PRIMARY KEY (`sid`), -> KEY `fk_class` (`class_id`), -> CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`) -> ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO `student` VALUES (‘1‘, ‘男‘, ‘1‘, ‘理解‘), (‘2‘, ‘女‘, ‘1‘, ‘鋼蛋‘), (‘3‘, ‘男‘, ‘1‘, ‘張三‘), (‘4‘, ‘男‘, ‘1‘, ‘張一‘), (‘5‘, ‘女‘, ‘1‘, ‘張二‘), (‘6‘, ‘男‘, ‘1‘, ‘張四‘), (‘7‘, ‘女‘, ‘2‘, ‘鐵鎚‘), (‘8‘, ‘男‘, ‘2‘, ‘李三‘), (‘9‘, ‘男‘, ‘2‘, ‘李一‘), (‘10‘, ‘女‘, ‘2‘, ‘李二‘), (‘11‘, ‘男‘, ‘2‘, ‘李四‘), (‘12‘, ‘女‘, ‘3‘, ‘如花‘), (‘13‘, ‘男‘, ‘3‘, ‘劉三‘), (‘14‘, ‘男‘, ‘3‘, ‘劉 一‘), (‘15‘, ‘女‘, ‘3‘, ‘劉二‘), (‘16‘, ‘男‘, ‘3‘, ‘劉四‘);Query OK, 16 rows affected (0.01 sec)Records: 16 Duplicates: 0 Warnings: 0mysql> DROP TABLE IF EXISTS `teacher`;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> CREATE TABLE `teacher` ( -> `tid` int(11) NOT NULL AUTO_INCREMENT, -> `tname` varchar(32) NOT NULL, -> PRIMARY KEY (`tid`) -> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO `teacher` VALUES (‘1‘, ‘張磊老師‘), (‘2‘, ‘李平老師‘), (‘3‘, ‘劉海燕老師‘), (‘4‘, ‘朱雲海老師‘), (‘5‘, ‘李傑老師‘);Query OK, 5 rows affected (0.00 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> SET FOREIGN_KEY_CHECKS = 1;Query OK, 0 rows affected (0.00 sec)mysql> show tables;+-------------------+| Tables_in_sqlexam |+-------------------+| class || course || score || student || teacher |+-------------------+5 rows in set (0.00 sec)# 1、查詢所有的課程的名稱以及對應的任課老師姓名mysql> SELECT -> course.cname, -> teacher.tname -> FROM -> course -> INNER JOIN teacher ON course.teacher_id = teacher.tid;+--------+-----------------+| cname | tname |+--------+-----------------+| 生物 | 張磊老師 || 物理 | 李平老師 || 美術 | 李平老師 || 體育 | 劉海燕老師 |+--------+-----------------+4 rows in set (0.01 sec)# 2、查詢學生表中男女生各有多少人mysql> SELECT -> gender 性別, -> count(1) 人數 -> FROM -> student -> GROUP BY -> gender;+--------+--------+| 性別 | 人數 |+--------+--------+| 女 | 6 || 男 | 10 |+--------+--------+2 rows in set (0.00 sec)# 3、查詢物理成績等於100的學生的姓名mysql> SELECT -> student.sname -> FROM -> student -> WHERE -> sid IN ( -> SELECT -> student_id -> FROM -> score -> INNER JOIN course ON score.course_id = course.cid -> WHERE -> course.cname = ‘物理‘ -> AND score.num = 100 -> );+--------+| sname |+--------+| 張四 || 鐵鎚 || 李三 |+--------+3 rows in set (0.00 sec)# 4、查詢平均成績大於八十分的同學的姓名和平均成績mysql> SELECT -> student.sname, -> t1.avg_num -> FROM -> student -> INNER JOIN ( -> SELECT -> student_id, -> avg(num) AS avg_num -> FROM -> score -> GROUP BY -> student_id -> HAVING -> avg(num) > 80 -> ) AS t1 ON student.sid = t1.student_id;+--------+---------+| sname | avg_num |+--------+---------+| 張三 | 82.2500 || 劉三 | 87.0000 |+--------+---------+2 rows in set (0.00 sec)# 5、查詢所有學生的學號，姓名，選課數，總成績mysql> SELECT -> student.sid, -> student.sname, -> t1.course_num, -> t1.total_num -> FROM -> student -> LEFT JOIN ( -> SELECT -> student_id, -> COUNT(course_id) course_num, -> sum(num) total_num -> FROM -> score -> GROUP BY -> student_id -> ) AS t1 ON student.sid = t1.student_id;+-----+--------+------------+-----------+| sid | sname | course_num | total_num |+-----+--------+------------+-----------+| 1 | 理解 | 3 | 85 || 2 | 鋼蛋 | 3 | 175 || 3 | 張三 | 4 | 329 || 4 | 張一 | 4 | 257 || 5 | 張二 | 4 | 257 || 6 | 張四 | 4 | 276 || 7 | 鐵鎚 | 4 | 264 || 8 | 李三 | 4 | 264 || 9 | 李一 | 4 | 268 || 10 | 李二 | 4 | 297 || 11 | 李四 | 4 | 297 || 12 | 如花 | 4 | 297 || 13 | 劉三 | 1 | 87 || 14 | 劉一 | NULL | NULL || 15 | 劉二 | NULL | NULL || 16 | 劉四 | NULL | NULL |+-----+--------+------------+-----------+16 rows in set (0.00 sec)# 6、 查詢姓李老師的個數mysql> SELECT -> count(tid) -> FROM -> teacher -> WHERE -> tname LIKE ‘李%‘;+------------+| count(tid) |+------------+| 2 |+------------+1 row in set (0.00 sec)# 7、 查詢沒有報李平老師課的學生姓名mysql> SELECT -> student.sname -> FROM -> student -> WHERE -> sid NOT IN ( -> SELECT DISTINCT -> student_id -> FROM -> score -> WHERE -> course_id IN ( -> SELECT -> course.cid -> FROM -> course -> INNER JOIN teacher ON course.teacher_id = teacher.tid -> WHERE -> teacher.tname = ‘李平老師‘ -> ) -> );+--------+| sname |+--------+| 劉三 || 劉一 || 劉二 || 劉四 |+--------+4 rows in set (0.00 sec)# 8、 查詢物理課程比生物課程高的學生的學號mysql> SELECT -> t1.student_id -> FROM -> ( -> SELECT -> student_id, -> num -> FROM -> score -> WHERE -> course_id = ( -> SELECT -> cid -> FROM -> course -> WHERE -> cname = ‘物理‘ -> ) -> ) AS t1 -> INNER JOIN ( -> SELECT -> student_id, -> num -> FROM -> score -> WHERE -> course_id = ( -> SELECT -> cid -> FROM -> course -> WHERE -> cname = ‘生物‘ -> ) -> ) AS t2 ON t1.student_id = t2.student_id -> WHERE -> t1.num > t2.num;+------------+| student_id |+------------+| 6 || 7 || 8 |+------------+3 rows in set (0.00 sec)# 9、 查詢沒有同時選修物理課程和體育課程的學生姓名mysql> SELECT -> student.sname -> FROM -> student -> WHERE -> sid IN ( -> SELECT -> student_id -> FROM -> score -> WHERE -> course_id IN ( -> SELECT -> cid -> FROM -> course -> WHERE -> cname = ‘物理‘ -> OR cname = ‘體育‘ -> ) -> GROUP BY -> student_id -> HAVING -> COUNT(course_id) = 1 -> );+--------+| sname |+--------+| 理解 || 鋼蛋 || 劉三 |+--------+3 rows in set (0.00 sec)# 10、查詢掛科超過兩門(包括兩門)的學生姓名和班級mysql> SELECT -> student.sname, -> class.caption -> FROM -> student -> INNER JOIN ( -> SELECT -> student_id -> FROM -> score -> WHERE -> num < 60 -> GROUP BY -> student_id -> HAVING -> count(course_id) >= 2 -> ) AS t1 -> INNER JOIN class ON student.sid = t1.student_id -> AND student.class_id = class.cid;+--------+--------------+| sname | caption |+--------+--------------+| 理解 | 三年二班 |+--------+--------------+1 row in set (0.00 sec)

python之資料庫