python基础(数据库、可视化软件Navicat、python操作MySQL)

多表查询的两种方法

数据准备：

建表

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),
('tony','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);

方式1:连表操作

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多张表
	思路:将拼接之后的表起别名当成一张表再去与其他表拼接 再起别名当一张表 再去与其他表拼接 其次往复即可
'''

方式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

"""sql2有结果的情况下才会执行sql1 否则不执行sql1 返回空数据"""
sql1 exists sql2

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 字段名;  # 删除字段

可视化软件Navicat

简介

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

1.官网下载地址

版本很多、能够充当的数据库客户端也很多

2.破解教程

3.常用操作

有些功能可能需要自己修改SQL预览

创建库

创建表

逆向数据库到模型
模型创建

新建查询可以编写SQL语句并自带提示功能
SQL语句注释语法

--、#、**
运行、转储SQL文件
接收数据

多表查询练习题

"""
编写复杂的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
	);

python操作MySQL

pymysql模块
	pip3 install pymysql
 
import pymysql

# 1.连接MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    db='db4_03',
    charset='utf8mb4'
)
# 2.产生游标对象
# cursor = conn.cursor()  # 括号内不填写额外参数 数据是元组 指定性不强  [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]
# 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)

pymysql补充说明

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

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

posted @ 2022-11-28 21:21 吴仁耀阅读(70) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· 数据库(部分讲解)

· MySQL多表查询、可视化软件Navicat、python操作MySQL

· python 之路 37 Navicat 可视化软件使用、 pymysql模块使用

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

阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· .NET Core 中如何实现缓存的预热？
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 如何调用 DeepSeek 的自然语言处理 API 接口并集成到在线客服系统
· 【译】Visual Studio 中新的强大生产力特性

公告

昵称：吴仁耀
园龄： 2年5个月
粉丝： 13
关注： 9

+加关注

2025年3月

日

一

二

三

四

五

六

随笔分类 (24)

python基础(24)

江南三月听莺天买酒莫论钱

python基础(数据库、可视化软件Navicat、python操作MySQL)

多表查询的两种方法

数据准备：

方式1:连表操作

方式2:子查询

小知识点补充说明

可视化软件Navicat

简介

1.官网下载地址

2.破解教程

3.常用操作

多表查询练习题

python操作MySQL

pymysql补充说明

公告

搜索

常用链接

最新随笔

积分与排名

随笔分类 (24)

随笔档案 (88)

相册 (1)

阅读排行榜

评论排行榜

推荐排行榜

最新评论

江南三月听莺天 买酒莫论钱

python基础(数据库、可视化软件Navicat、python操作MySQL)

多表查询的两种方法

数据准备：

方式1:连表操作

方式2:子查询

小知识点补充说明

可视化软件Navicat

简介

1.官网下载地址

2.破解教程

3.常用操作

多表查询练习题

python操作MySQL

pymysql补充说明

公告

搜索

常用链接

最新随笔

积分与排名

随笔分类 (24)

随笔档案 (88)

相册 (1)

阅读排行榜

评论排行榜

推荐排行榜

最新评论

江南三月听莺天买酒莫论钱