MySQL練習題參考答案

標籤：rom   for   bsp   opened   cap   localhost   nod   技術分享   nsf   

匯出現有資料庫資料：

• mysqldump -u使用者名稱 -p密碼 資料庫名稱 >匯出檔案路徑           # 結構+資料
• mysqldump -u使用者名稱 -p密碼 -d 資料庫名稱 >匯出檔案路徑       # 結構 

匯入現有資料庫資料：

• mysqldump -uroot -p密碼  資料庫名稱 < 檔案路徑  

/* Navicat Premium Data Transfer Source Server         : localhost Source Server Type    : MySQL Source Server Version : 50624 Source Host           : localhost Source Database       : sqlexam Target Server Type    : MySQL Target Server Version : 50624 File Encoding         : utf-8 Date: 10/21/2016 06:46:46 AM*/SET NAMES utf8;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------  Table structure for `class`-- ----------------------------DROP TABLE IF EXISTS `class`;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;-- ------------------------------  Records of `class`-- ----------------------------BEGIN;INSERT INTO `class` VALUES (‘1‘, ‘三年二班‘), (‘2‘, ‘三年三班‘), (‘3‘, ‘一年二班‘), (‘4‘, ‘二年九班‘);COMMIT;-- ------------------------------  Table structure for `course`-- ----------------------------DROP TABLE IF EXISTS `course`;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;-- ------------------------------  Records of `course`-- ----------------------------BEGIN;INSERT INTO `course` VALUES (‘1‘, ‘生物‘, ‘1‘), (‘2‘, ‘物理‘, ‘2‘), (‘3‘, ‘體育‘, ‘3‘), (‘4‘, ‘美術‘, ‘2‘);COMMIT;-- ------------------------------  Table structure for `score`-- ----------------------------DROP TABLE IF EXISTS `score`;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;-- ------------------------------  Records of `score`-- ----------------------------BEGIN;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‘);COMMIT;-- ------------------------------  Table structure for `student`-- ----------------------------DROP TABLE IF EXISTS `student`;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;-- ------------------------------  Records of `student`-- ----------------------------BEGIN;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‘, ‘劉四‘);COMMIT;-- ------------------------------  Table structure for `teacher`-- ----------------------------DROP TABLE IF EXISTS `teacher`;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;-- ------------------------------  Records of `teacher`-- ----------------------------BEGIN;INSERT INTO `teacher` VALUES (‘1‘, ‘張磊老師‘), (‘2‘, ‘李平老師‘), (‘3‘, ‘劉海燕老師‘), (‘4‘, ‘朱雲海老師‘), (‘5‘, ‘李傑老師‘);COMMIT;SET FOREIGN_KEY_CHECKS = 1;表結構和資料

表結構和資料

2、查詢“生物”課程比“物理”課程成績高的所有學生的學號；

select A.student_id from (select student_id,num from score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="生物") as AINNER JOIN(select student_id,num from score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="物理") as BON A.student_id=B.student_id WHERE A.num > B.num

 3、查詢平均成績大於60分的同學的學號、名字和平均成績；

select student.sid,student.sname,B.numname from (select student_id,avg(num) as numname from score GROUP BY student_id HAVING AVG(num)>60) as BLEFT JOIN student on B.student_id=student.sid

4、查詢所有同學的學號、姓名、選課數、總成績；

SELECT student_id,student.sname,COUNT(course_id),SUM(num) from score LEFT JOIN student on score.student_id=student.sid GROUP BY student_id

5、查詢姓“李”的老師的個數；

select * from teacher WHERE tname like "李%"

6、查詢沒學過“李平”老師課的同學的學號、姓名；

SELECT student.sid,student.sname from student WHERE student.sid not in (SELECT student_id from score WHERE score.course_id in (select course.cid from course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE teacher.tname="李平老師") GROUP BY student_id)

 

MySQL練習題參考答案