多表查询的两种方法
# 方法一：连表操作
# 1.内连接 inner join
select * from emp inner join dep on emp.dep_id=dep.id;
"""只会连接两张表中公有的数据"""
# 2.左连接 left join
select * from emp left join dep on emp.dep_id;
"""以左边的表为基准，展示左表所有的数据，没有的用NULL填充"""
# 3.右连接 right join
select * from emp right join dep on emp.dep_id=dep.id;
"""以右边的表为基准，展示右表所有的数据，没有的用NULL填充"""
# 4.全连接 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填充"""
"""
连表操作连多张表需要两种表连起来起别名再去连别的表
"""
# 方法二：子查询
"""将一条SQL语句用括号括起来当作另一条SQL语句的查询条件"""
# 1.要求：求姓名是jason的员工部门名称
步骤1：先获取jason员工部门编号
select dep_id from emp where name='jason
步骤2：根据部门编号获取部门名称
select name from dep where id=200;
合成一条：
select name from dep where id=(select dep_id from emp where name='jason')
"""
连表操作和子查询许多时候都是相互配合使用
"""
知识点补充
# 1.concat与concat_ws
concat用于分组之前的字段拼接操作
select concat(name, '|',sex) from emp;
concat_ws拼接多个字段并且中间的连接符一致
select concat_ws('|',name,sex,age,dep_id) from emp;
# 2.exists
sql语句1 exists sql语句2
sql语句2有结果的情况下才会执行上sql语句1，否则不执行sql语句1，会返回空数据
# 3.表相关SQL补充
alter table 表名 rename 新表名; # 修改表名
alter table 表名 add 字段名 字段类型(数字) 约束条件; # 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
alter table 表名 add 字段名 字段类型(数字) 约束条件 first; # 修改字段
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
alter table 表名 drop 字段名； # 删除字段
多表查询实操
pymysql模块操作MYSQL
pymysql模块下载：
pip3 install pymysql
pymysql使用：
# 1.连接MYSQL服务端：
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db4_03',
charset='utf8mb4'
)
# 2.产生游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# [{},{}]
# cursor = conn.cursor() # 括号内不填写额外参数数据是元组，指定性不强
# [(),()]
# 3.编写sql语句
sql = 'select * from teacher;'
#sql = 'select * from score;'
# 4.发送sql语句
affect_rows = cursor.execute(sql) # execute也有返回值，接收到的是sql语句影响的行数
print(affect_rows)
# 5.获取sql语句执行之后的结果
res =cursor.fetchall()
print(res)
"""
补充说明：
1.获取数据
fetchall() 获取所有的结果
fetchone() 获取结果集的第一个数据
fetchmany() 获取指定数量的结果集
ps：数据的获取类似于文件光标的特性
cursor.scroll(1,'relative') # 基于当前位置往后移动
cursor.scroll(0,'absolute') # 基于数据的开头往后移动
2.增删改查
autocommit=Ture # 针对增删改自动确认(直接配置)
conn.commit() # 针对增删改需要二次确认(代码确认)
"""
多表查询练习
"""编写难度较高SQL语句要明确思路一步一步进行补充完整"""
1.查询所有的课程的名称以及对应的任课老师姓名
2.查询平均成绩大于八十分的同学的姓名和平均成绩
3.查询没有报李平老师课的学生姓名
4.查询没有同时选修物理课程和体育课程的学生姓名
5.查询挂科超过两门(包括两门)的学生姓名和班级
# 1.查询所有的课程的名称以及对应的任课老师姓名
- 1.先确定需要用到几张表 课程表 分数表
- 2.预览表中的数据 做到心中有数
select * from course;
select * from teacher;
- 3.确定多表查询的思路 连表 子查询 混合操作
SELECT
teacher.tname,
course.cname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
# 2.查询平均成绩大于八十分的同学的姓名和平均成绩
- 1.先确定需要用到几张表 学生表 分数表
- 2.预览表中的数据
select * from student;
select * from score;
- 3.根据已知条件80分 选择切入点 分数表
""" 求每个学生的平均成绩,按照student_id分组,然后avg求num即可"""
select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
- 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
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,然后取反
"""
步骤1: 先获取李平老师教授的课程id
select tid from teacher where tname = '李平老师';
select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
步骤2: 根据课程id筛选出所有报了李平老师的学生id
select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
步骤3: 根据学生id去学生表中取反获取学生姓名
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.预览表数据
- 3.根据给出的条件确定起手的表
- 4.根据物理和体育筛选课程id
select cid from course where cname in ('物理','体育');
- 5.根据课程id筛选出所有跟物理 体育相关的学生id
select * from score where course_id in (select cid from course where cname in ('物理','体育'))
- 6.统计每个学生报了的课程数 筛选出等于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;
- 7.子查询获取学生姓名即可
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
)
# 5.查询挂科超过两门(包括两门)的学生姓名和班级
- 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
);
"""
标签：多表,dep,查询,course,student,MYSQL,where,id,select From： https://www.cnblogs.com/juzijunjun/p/16933586.html