MySQL多表查询及pymysql简单使用

目录

• 多表查询的两种方法
• 小知识点补充说明
• 可视化软件Navicat
• 多表查询练习题
• python操作MySQL
• pymysql补充说明

多表查询的两种方法

方式一：连表操作

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填充
"""
学会了连表操作之后也就可以连接N多张表
	思路：将拼接之后的表起别名当成一张表再与其它表拼接 再起别名当一张表 再去与其它表拼接 其次往复即可
ps：涉及到多张表内容时需要使用多表查询，比如需要学生姓名和学生成绩，这样需要多表查询
"""

方式2：子查询

将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件
题目：求姓名是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
	sql1  exists  sql2
		sql2有结果的情况下才会执行sql1 否则不执行sql1 返回空数据

3.表相关SQL补充
	create table t1(id int primary key auto_crement,name varchar(32))；
	alter table 表名 rename 新表名;
	alter table 表名 add 字段名 字段类型(数字) 约束条件  # 添加字段
	alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
	alter table 表名 add 字段名 字段类型(数字) 约束条件 first; # 修改字段
	alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
	alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
	alter table 表名 drop 字段名;  # 删除字段

可视化软件Navicat

第三方开发的用来充当数据库客户端的简单快捷的操作界面
	无论第三方软件有多么的花里胡哨 底层的本质还是SQL
能够操作数据库的第三方可视化软件有很多 其中针对MySQL最出名的就是Navicat

1.浏览器搜索Navicat直接下载
	版本很多、能够充当的数据库客户端也很多
2.破解方式
	先试用在破解、直接下载破解版(老版本)、修改试用日期
3.常用操作
	有些功能可能需要自己修改SQL预览
    	创建库、表、记录、外键
    	逆向数据库到模型、模型创建
    	新建查询可以编写SQL语句并自带提示功能
    	SQL语句注释语法
        --、#、\**\
    	运行、转储SQL文件

多表查询练习题

"""
编写复杂的SQL不要想着一口气写完
	一定要先明确思路 然后一步步写一步步差一步步补
1.先确定需要涉及到几张表
2.大致预览表结构与表数据
3.根据已知条件确定切入点
4.灵活运用所学内容去拼凑
	将复杂的操作流程化 步骤化
ps:一定要自己多练多敲
"""

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、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 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; 
补充说明：select student.sname,t1.avg(num) from student inner join  (select student_id,avg(num) from score group by student_id having avg(num) > 80) as t1 on student.sid=t1.student_id;会报错avg找不到，建议加别名获取，参考上面的最终答案

5、查询所有学生的学号，姓名，选课数，总成绩

6、 查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名
#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='李平老师')))

8、 查询物理课程比生物课程高的学生的学号

9、 查询没有同时选修物理课程和体育课程的学生姓名
#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)

10、查询挂科超过两门(包括两门)的学生姓名和班级
# 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);

11、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录
 
13、查询全部学生都选修了的课程号和课程名

14、查询每门课程被选修的次数

15、查询之选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

20、查询每门课程成绩最好的前两名学生姓名

21、查询不同课程但成绩相同的学号，课程号，成绩

22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；

23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；

24、任课最多的老师中学生单科成绩最高的学生姓名

python操作MySQL

pymysql模块
	pip3 install pymysql

import pymysql

#1 连接MySQL服务端
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password='Abcd1234',
    db='db1',
    charset='utf8mb4'
)

#2. 产生游标
# cursor = conn.cursor() # 括号内不填写额外参数 数据是元组 指定性不强 ((),())
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  #[{},{}]
#3.编写SQL语句
sql1 = 'select * from teacher;'

#4.发送SQL语句
affect_rows = cursor.execute(sql1)  # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)  

#5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)

pymysql补充说明

1.获取数据
    fetchall()  获取所有的结果
    fetchone()	获取结果集的第一个数据
    fetchmany() 获取指定数量的结果集
    ps:注意三者都有类似于文件光标移动的特性

    cursor.scroll(1,'relative')  # 基于当前位置往后移动
    cursor.scroll(0,'absolute')  # 基于数据的开头往后移动
 
2.增删改查
	 autocommit=True  # 针对增 删 改 自动确认(直接配置,在 pymysql.connect里面配置)
  	 conn.commit()  # 针对 增 删 改 需要二次确认(代码确认)