可视化软件navicat

目录

• 多表查询思路
• 可视化软件navicat
• 多表查询练习题

内容 多表查询思路

多表查询的思路总共就两种：

白嫖两个表

获取jason所在的部门名称

子查询

相当于是我们日常生活中解决问题的方式（一步步解决）

将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件

先获取jason的部门编号

将结果加括号作为查询条件

连表操作

先将多张表拼接到一起，形成一张大表，然后基于单表查询获取数据

连表操作有四个关键字：

• inner join 内连接（只连接两张表中有对应关系的数据）

• left join 左连接（以左表为基准，展示所有的数据，没有对应项则用NULL填充）

• right join 右连接（以右表为基准，展示所有的数据，没有对应项则用NULL填充）

• union 全连接（左右两表数据全部展示，没有对应项则用NULL填充）

获取jason的部门编号

笛卡尔积

会将所有的数据全部对应一遍，效率低下

一条SQL语句的查询结果，我们也可以看成是一张虚拟表

如果一条SQL语句中涉及到多张表的字段名称编写，建议使用表名前缀做区分

 

学会了连表操作之后，可以将N多张表拼接到一起（将两张表拼接之后的结果起别名当做一张表使用，然后再去跟另外一张表拼接）

可视化软件之Navicat

Navicat可以充当很多数据库软件的客户端，提供了图形化界面能够让我们更加快速的操作数据库

使用navicat编写SQL，如果自动补全语句，那么关键字都会变大写

SQL语句注释语法：快捷键与pycharm中的一致

多表查询练习题

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

1. 先明确需要几张表，course表，teacher表
2. 大致查找一些表中的数据情况
3. 既然是多表查询，那么查询思路，子查询，连表操作(复杂的SQL需要两者配合使用)
4. 编写完成后，使用美化功能，将SQL语句规范化

-- SELECT

-- course.cname,

-- teacher.tname

-- FROM

-- course

-- INNER JOIN teacher ON course.teacher_id = teacher.tid;

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

1. 先明确需要用到几张表 student score
2. 大致查看一下两张表里面的数据
3. 先获取平均成绩大于80分的学生信息（按照student_id分组）
4. select score.student_id,avg(num) as avg_num from score group by score.student_id having avg_num>80;
5. 结果需要从两个表里面的获取 ，student ，SQL语句执行之后的虚拟表

-- SELECT

-- student.sname,

-- t1.avg_num

-- FROM

-- student

-- INNER JOIN ( SELECT student_id, avg( num ) AS avg_num FROM score GROUP BY score.student_id HAVING avg_num > 80 ) AS t1 ON student.sid = t1.student_id;

• 查询没有报李平老师课的学生姓名

此题有两种思路：第一种是正向查询，第二种是反向查询（先查所有报了李平老师课程的学生id 之后取反即可）

1. 先明确需要用到几张表，四张表
2. 先查询李平老师的编号
3. select tid from teacher where tname='李平老师'
4. 再查李平老师教授的课程编号
5. select cid from course where teacher_id=(select tid from teacher where tname='李平老师')
6. 根据课程编号，去score表中筛选出所有选了课程的学生编号
7. select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));
8. 根据学生编号去学生表中反向筛选出没有报李平老师课程的学生姓名

-- SELECT

-- sname

-- FROM

-- student

-- WHERE

-- sid NOT IN ( SELECT DISTINCT student_id FROM score WHERE course_id IN ( SELECT cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) ) )

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

1. 先明确需要用到几张表 三张
2. 先获取物理课程和体育课程的编号
3. select cid from course where cname in ('物理','体育');
4. 再去分数表中筛选出选了物理和体育的数据(包含了选了一门和两门 没有选的就已经被排除了)
5. select * from score where course_id in (select cid from course where cname in ('物理','体育'))
6. 如何剔除选了两门的数据(按照学生id分组 然后对课程计数即可)
7. select student_id from score where course_id in (select cid from course where cname in ('物理','体育')) -- group by student_id HAVING count(course_id) = 1;
8. 根据上述学生id号筛选出学生姓名

-- SELECT

-- sname

-- FROM

-- student

-- WHERE

-- sid IN (

-- SELECT -

- student_id

-- FROM

-- score

-- WHERE

-- course_id IN ( SELECT cid FROM course WHERE cname IN ( '物理', '体育' ) )

-- GROUP BY

-- student_id

-- HAVING

-- count( course_id ) = 1

-- )

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

1. 先明确需要几张表 三张表
2. 先去score表中筛选出所有不及格的数据
3. select * from score where num < 60;
4. 如何筛选每个学生挂科的门数（按照学生id分组 对学科计数即可）
5. select student_id from score where num < 60 group by student_id -- HAVING count(course_id) >= 2;
6. 由于最终的结果需要取自两张表，所以应该拼接
7. select student.sname,class.caption from class inner join student on class.cid=student.class_id;
8. 使用步骤3获取到的学生编号，对步骤4的表结果筛选数据

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 );