MySQL基础(四):navicat工具使用说明,高难度SQL查询练习题

数据库(MySQL) 专栏收录该内容
16 篇文章 2 订阅

下面是小凰凰的简介,看下吧!
💗人生态度:珍惜时间,渴望学习,热爱音乐,把握命运,享受生活
💗学习技能:网络 -> 云计算运维 -> python全栈( 当前正在学习中)
💗您的点赞、收藏、关注是对博主创作的最大鼓励,在此谢过!
有相关技能问题可以写在下方评论区,我们一起学习,一起进步。
后期会不断更新python全栈学习笔记,秉着质量博文为原则,写好每一篇博文。

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 );
相关推荐
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值