多表查询与可视化软件navicat
多表查询
子查询思路
就相当于是我们日常生活中解决问题的方式(一步步解决)
将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询条件
eg:以昨天的员工表和部门表为例 查询jason所在的部门名称
子查询的步骤
1.先查jason所在的部门编号
2.根据部门编号去部门表中查找部门名称
连表操作
先将多张表拼接到一起 形成一张大表 然后基于单表查询获取数据
eg:以昨天的员工表和部门表为例 查询jason所在的部门名称
连表操作
1.先将员工表和部门表按照某个字段拼接到一起
2.基于单表查询
多表查询操作
表的创建与数据写入
create table dep(
id int primary key auto_increment,
name varchar(32)
);
create table emp(
id int primary key auto_increment,
name varchar(32),
gender enum('male','female','others') default 'male',
age int,
dep_id int
);
insert into dep values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营'),(205,'安保');
insert into emp(name,age,dep_id) values('jason',18,200),('tony',28,201),('oscar',38,201),('jerry',29,202),('kevin',39,203),('jack',48,204);
子查询
# 使用子查询 获取jason所在的部门名称
# 1.先获取jason的部门编号
select dep_id from emp where name='jason';
# 2.将结果加括号作为查询条件
select name from dep where id=(select dep_id from emp where name='jason');
连表操作
# 使用连表操作 获取jason所在的部门名称
笛卡尔积(了解知识)
select * from emp,dep; # 会讲所有的数据全部对应一遍
select * from emp,dep where emp.dep_id=dep.id; # 效率低下
"""
1.一条SQL语句的查询结果 我们也可以看成是一张虚拟表
2.如果一条SQL语句中设计到多张表的字段名称编写 建议使用表名前缀做区分
"""
连表操作有四个关键字
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 全连接
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;
'''左右两表数据全部展示 没有对应项则用NULL填充'''
答案求解
select dep.name from emp inner join dep on emp.dep_id=dep.id where emp.name='jason'
;
"""
了解
我们学会了连表操作之后 其实就可以将N多张表拼接到一起
思路:我们可以将两张表拼接之后的结果起别名当做一张表使用
然后再去跟另外一张表拼接
select * from emp inner join (select emp.id as epd,emp.name,dep.id from emp inner join dep on emp.dep_id=dep.id) as t1 on emp.id=t1.epd;
"""
可视化软件Navicat
Navicat 简介
Navicat可以充当很多数据库软件的客户端，提供了图形化界面能够让我们更加快速的操作数据库。
Navicat 下载
navicat有很多版本，并且默认都是收费使用，正版可以免费体验14天。针对这种图形化软件， 版本越新越好(不同版本图标颜色不一样 但是主题功能是一样的)。
Navicat 破解版下载与破解教程
Navicat 使用
简单操作说明
连接数据库
初次使用在此界面输入连接名或者主机口端口号连接数据库，或者直接使用默认，输入用户名密码即可连接本地数据库
创建库和表
-
创建库
在已经连接的数据库的库名上直接鼠标右键点击
-
创建表
类似创建库的操作，只需要在库内的表格直接鼠标右键即可
-
创建外键
选中表的名字后选择设计表
然后选择外键点击创建外键依次填入信息
-
外联SQL文件
sql文件的生成
外联sql文件
-
逆向到数据库模型
多表查询练习
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级
-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先明确需要几张表 course表 teacher表
# 2.大致查找一些表中的数据情况
# 3.既然是多表查询 那么查询思路 子查询 连表操作(复杂的SQL需要两者配合使用)
# 4.编写完成后 使用美化功能 将SQL语句规范化
-- SELECT
-- course.cname,
-- teacher.tname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先明确需要用到几张表 student score
# 2.大致查看一下两张表里面的数据
# 3.先获取平均成绩大于80分的学生信息(按照student_id分组)
-- select score.student_id,avg(num) as avg_num from score group by score.student_id having avg_num>80;
# 4.结果需要从两个表里面的获取 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;
-- 7、查询没有报李平老师课的学生姓名
# 此题有两种思路 第一种是正向查询 第二种是反向查询(先查所有报了李平老师课程的学生id 之后取反即可)
# 1.先明确需要用到几张表 四张表
# 2.先查询李平老师的编号
-- select tid from teacher where tname='李平老师'
# 3.再查李平老师教授的课程编号
-- select cid from course where teacher_id=(select tid from teacher where tname='李平老师')
# 4.根据课程编号 去score表中筛选出所有选了课程的学生编号
-- select distinct student_id from score where course_id in (select cid from course where teacher_id=(select tid from teacher where tname='李平老师'));
# 5.根据学生编号去学生表中反向筛选出没有报李平老师课程的学生姓名
-- 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 = '李平老师' ) ) )
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(两门都选了和一门都没选的 都不要 只要选了一门)
# 1.先明确需要用到几张表 三张
# 2.先获取物理课程和体育课程的编号
-- select cid from course where cname in ('物理','体育');
# 3.再去分数表中筛选出选了物理和体育的数据(包含了选了一门和两门 没有选的就已经被排除了)
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 4.如何剔除选了两门的数据(按照学生id分组 然后对课程计数即可)
-- 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;
# 5.根据上述学生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
-- )
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先明确需要几张表 三张表
# 2.先去score表中筛选出所有不及格的数据
-- select * from score where num < 60;
# 3.如何筛选每个学生挂科的门数(按照学生id分组 对学科计数即可)
-- select student_id from score where num < 60 group by student_id
-- HAVING count(course_id) >= 2;
# 4.由于最终的结果需要取自两张表 所以应该拼接
-- select student.sname,class.caption from class inner join student on class.cid=student.class_id;
# 5.使用步骤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 );
