MySQL基础（四）：navicat工具使用说明，高难度SQL查询练习题一点一滴铺就人生-

1、navicat使用说明

（1）navicat连接云上数据库

先建立ssh连接到云主机上：

（2）navicat简单使用

其他功能自己慢慢摸索吧！！

2、准备表

注意：下面表的生成，需要用到navicat工具！navicat就是一个数据库的可视化工具。让你不用再写复杂的SQL语句，只是鼠标点点点，实现各种数据库的骚操作！

导出的sql语句代码：

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; 

拷贝上述代码，新建一个.sql文件，保存到桌面 打开navicat新建数据库db1，选中新建的数据库鼠标右键选择运行SQL文件 弹出文件框，选中刚刚保存到桌面的.sql文件即可 

运行完上述sql文件就会生成以下几个表：

3、模型图

4、查询题目

（1）查询所有的课程的名称以及对应的任课老师姓名

SELECT  course.cname,  teacher.tname  FROM  course  INNER JOIN teacher ON course.teacher_id = teacher.tid 

（2）查询平均成绩大于八十分的同学的姓名和平均成绩

SELECT  student.sname, AVG(score.num) FROM  student  INNER JOIN score ON student.sid = score.student_id  GROUP BY student.sid HAVING AVG(score.num) > 80; 

（3）查询没有报李平老师课的学生姓名

1. 选出李平老师所教的课程的课程号 SELECT     course.cid FROM     course INNER JOIN teacher ON course.teacher_id = teacher.tid WHERE     teacher.tname = '李平老师' 2. 根据李平老师所教的课程的课程号，找出报李平老师课的学生id 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 = ‘李平老师‘     ) 3. 根据得到的报李平老师课的学生id，取反得到没报李平老师课的学生id，并根据查询其名字 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 = ‘李平老师‘             ) ); 

（4）查询没有同时选修物理课程和体育课程的学生姓名

1. 查询出'体育'、'物理'两门课程的课程号 SELECT     cid FROM     course WHERE     cname = '物理' OR cname = '体育' 2. 过滤出选修这两门课程最少其中一门的所有人，然后按学号分组，count计数，把count计数等于2的学生号选出来 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) = 2 3. 根据学生号查询学生姓名 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) = 2 ); 

（5）查询挂科超过两门(包括两门)的学生姓名和班级名

注意：caption字段里面存的就是班级名！

# 两次内连接 1. 先把挂科两门及以上的学生的姓名和班级名选出来 SELECT  student.sname,  student.class_id  FROM  student INNER JOIN score ON student.sid = score.student_id  WHERE score.num < 60 GROUP BY score.student_id HAVING COUNT( score.student_id ) >= 2 2. 把上面得到的挂科学生姓名与班级名的表取别名为t1，然后将class和t1进行内连接，然后取得sname和caption SELECT  t1.sname,  class.caption  FROM  class  INNER JOIN ( SELECT   student.sname,   student.class_id   FROM   student INNER JOIN score ON student.sid = score.student_id   WHERE  score.num < 60 GROUP BY score.student_id HAVING COUNT( score.student_id ) >= 2 ) AS t1 ON t1.class_id = class.cid; 

# 内连接+子查询 # 比上面简单一点的方法 SELECT  student.sname,  class.caption  FROM  class  INNER JOIN student ON class.cid = student.class_id  WHERE  student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING COUNT( course_id ) >= 2 );