可视化Navicat，多表查询

可视化软件Navicat

第三方开发的 一种操作数据库的 数据库客户端。 
优点 ：简单快捷 
底层： 其实无论操作多么的简单，对于底层原理来说我们每一步的操作它都在底层变成了sql代码执行。
针对 MySQL最出名的就是 Navicat

该软件内的注释语句：
--、#、\**\

下载方法

百度搜索官网
下载地址：https://www.navicat.com.cn/download/navicat-premium
根据当前使用电脑的系统选择 版本进行下载

安装好后可以试用15天， 可以在百度搜索 一些注册机。或者注册方法进行注册。 

多表查询的两种方法

多表查询思路

表数据准备
create table dep(
  id int primary key auto_increment,
  name varchar(20) 
);

create table emp(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);


select * from emp,dep;
会将两张表数据对应一遍 拼在一起  称为'笛卡尔积'
但是 他把每一个人对应了所有的部门数据，不合理
应该将有关系的对应到一起才合理

如果我们需要查两张表联合在一起的数据但是两张表的字段可能会起冲突，那么我们只需在   字段名前面加上表名.来指定该字段。
select * from emp,dep where emp.dep_id = dep.id;
我们使用筛选 emp的字段dep_id的值与 dep表的id相同 展示。 

连表操作

inner join 内连接
select * from emp inner join dep on emp.dep_id=dep.id;
# 连接两张表中公有的部分数据 不是公有的不展示

left join 左连接
select * from emp left join dep on emp dep_id =dep.id;
# 以左边表为准 展示左表所有数据 右边没有对应的数据用NULL表示

right join 右连接
select * from emp right join dep on emp dep_id=dep.id;
# 以右边表为准 展示右表所有数据 左边没有对应的数据用NULL表示

union 全连接
# 以所有表为准 展示所有数据 没有相对应数据则用NULL表示
select * from emp left join dep on emp dep_id =dep.ip
union
select * from emp right join dep on emp dep_id=dep.id;
"""连表操作可以帮助我们连接N多张表，可以把语句的结果理解成一张新的表，我们可以使用起别名的方式 再别的地方与其他表在进行拼接或者 按照该表数据进行操做 如此反复"""

子查询

将一段SQL语句用括号括起来当成一个结果 给另外一条SQL语句进行查询的条件。
 练习题：	查到名字是jason的员工部门名称
    
    步骤1： 根据名字获取对应的部门编号
    select dep_id from emp where name ='jason';
    
    步骤2；根据部门编号获取部门名称
    select name from dep where id=(select dep_id from emp where name ='jason');
 '''
很多时候多表查询需要结合实际情况判断用哪种  更多时候甚至是相互配合使用
'''   

多表查询练习题

1、查询所有的课程的名称以及对应的任课老师姓名
分解步骤
	"关键在于理清表与表之间的关系"
# 1.先确定需要用到几张表 
# 2.预览表中数据
select * from course;
select * from teacher;
# 3.确定多表查询的思路 选择对应的操作方法
select * FROM course INNER JOIN teacher on course.teacher_id= teacher.tid;
"拼接两张表 设置条件(外键与主键的关系) "
select course.cname,teacher.tname INNER JOIN teacher on course.teacher_id = teacher.tid; 
"拿到拼接后的表 (可以理解成上一段sql代码就是拼接后的表) 获取该表里的课程名称字段与老师名字字段 "

 2、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表 学生表 分数表
# 2.预览表中的数据
SELECT * FROM score;
# 3.根据已知条件大于80分 选择切入点 分数表 按照student_id分组 然后avg求num即可
SELECT student_id,avg(num) FROM score GROUP BY student_id HAVING avg(num) > 80;
#   确定最终的结果需要几张表 需要两张表 采用连表更加合适 
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;

3.查询没有报李平老师课的学生姓名
# # 1.先确定需要用到几张表  老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 获取 李平老师的编号
SELECT * FROM teacher;
SELECT tid FROM teacher WHERE tname = '李平老师';
# 根据李平老师的编号获取与他所有关联的课程编号
select * from course;
SELECT cid FROM  course where teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师');
# 根据课程编号获取 报了这门课的学生编号并去重(因为有相同的学生报了李平老师的多门课)
SELECT * from score;
select distinct student_id FROM score WHERE course_id in (SELECT cid FROM course where teacher_id = (SELECT tid FROM teacher WHERE tname = '李平老师')) ;
# 根据学生编号取反获取没有报名李平老师的学生名
SELECT * from student;
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 = '李平老师')));

4、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表	分数表 学生表 班级表
# 2.预览表数据
select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT student.sname,class.caption FROM student INNER JOIN class ON student.class_id = class.cid where student.sid IN  (SELECT student_id FROM score where num < 60 GROUP BY student_id HAVING COUNT(course_id) >= 2);

5、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表  学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 筛选出课程表里的物理与体育id
SELECT cid from course WHERE cname in ('物理','体育');
# 根据课程id筛选出所有跟物理 体育相关的学生id 统计每个学生报了的课程数 筛选出等于1的
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;
# 子查询获取学生姓名即可

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