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

  • 数据准备

  • 多表查询的两种方式

  • 小知识点补充

  • 可视化软件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),
('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;  # 这个就是将数据对应起来把表连接到一起

'''
笛卡尔积底层效率太低 连接的表越多就会出现冗长的数据 远离了我们的初衷 我们只需要把想要的数据连接在一起拿到想要的表数据就可以了
'''

# 内连接(只连接两张表中公有的数据部分)
inner join

select * from emp inner join dep on emp.dep_id=dep.id;

image

 # 左连接(以左表为基准 展示左表所有的数据 如果没有对应项则用NULL填充)
left join

select * from emp left join dep on emp.dep_id=dep.id;

image

# 右连接(以右表为基准 展示右表所有的数据 如果没有对应项则用NULL填充)
right join	

select * from emp right join dep on emp.dep_id=dep.id;

image

# 全连接(以左右表为基准 展示所有数据 各自没有的全部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;

image

'''
学会了连表操作之后也就可以连接N多张表
思路:将连接之后的表起别名当成一张表再去与其他表拼接 再起别名当一张表 再去其他表拼接 其次往复即可
'''
  • 子查询
子查询类似于我们日常生活中解决问题的方式>>>:分步操作
将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件

题目:求姓名是jason的员工部门名称
    步骤1:先根据jason获取部门编号
        select dep_id from emp where name='jason';
    步骤2:再根据部门编号获取部门编号
        select name from dep where id=200;
    步骤3:根据部门编号去查询部门表中查找部门名称
        select name from dep where id=(select dep_id from emp where name='jason');
'''
很多时候表查询需要结合实际情况判断用哪种 更多时候甚至是相互配合使用
'''

image

小知识点补充说明

1.concat与concat_ws
# concat用于分组之前的字段拼接操作
  select concat(name,'$',sex) from emp;

image

# concat_ws拼接多个字段并且中间的连接符一致
  select concat_ws('|',name,sex,age,dep_id) from emp;

image

2.exists
	SQL语句1 where exists (SQL语句2);
SQL语句2有结果才会执行SQL语句1 否则不执行SQL语句1 返回空数据

select * from dep where exists(select * from emp where emp.id>100); # SQL2无结果时

image

select * from dep where exists(select * from emp where emp.id>1); # SQL2有结果时

image

3.表相关SQL补充

# 修改表名
alter table 表名 rename 新表名;
# 添加字段(默认尾部追加)
alter table 表名 add 字段名 字段类型(数字) 约束条件;
# 选择位置添加字段(已有字段下添加)
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;

# 添加字段 add
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;

#修改字段 change
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件; # (也可以修改字段类型)
# 只想改字段类型而不改字段名可以用modify
alter table 表名 modify 字段名 新字段类型(数字) 约束条件;

# 删除字段 drop
alter table 表名 drop 字段名;

可视化软件Navicat

Navicat可以充当很多数据库软件的客户端 提供了图形化界面能够让我们更加快速的操作数据库
# 下载
  navicat有很多版本 并且默认都是收费使用
  正版可以免费体验14天 
  针对这种图形化软件 版本越新越好(不同版本图标颜色不一样 但是主题功能是一样的)
# 使用
  内部封装了SQL语句 用户只需要鼠标点点点就可以快速操作
  连接数据库  创建库和表 录入数据 操作数据
  外键 SQL文件 逆向数据库到模型 查询(自己写SQL语句)
  有些功能可能需要自己修改SQL预览
  # 使用navicat编写SQL 如果自动补全语句 那么关键字都会变大写

SQL语句注释语法
--、#、\**\
运行、转储SQL文件

多表查询练习题

"""
编写复杂的SQL不要想着一口气写完
	一定要先明确思路 然后一步步写一步步查一步步补
"""
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级

1、查询所有的课程的名称以及对应的任课老师姓名
# 1.这里我们判断需要课程表和教师表 浏览表中数据 做到心中有数
-- select * from course;
-- select * from teacher;
# 2.确定多表联查思路 连表 子查询 混合使用  这道题我们选择连表
select * from course inner join teacher on teacher_id = teacher.tid; # 我们只需要课程的名字和对应老师的名字 把*号改为teacher.tname,course.cname
# 3.得出结果
select teacher.tname,course.cname from course inner join teacher on teacher_id=teacher.tid;
# 4.最后我们可以美化一下代码
SELECT
	teacher.tname,
	course.cname 
FROM
	course
	INNER JOIN teacher ON teacher_id = teacher.tid;

   
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;


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.查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 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
	) 
    
    
9.查询挂科超过两门(包括两门)的学生姓名和班级
# 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

python中支持操作mysql的模块很多，其中最常见的当属'pymysql'

# 属于第三方模块
pip3 install pymysql
# 基本使用
import pymysql
# 1.链接服务端
conn_obj = pymysql.connect(
    host='127.0.0.1',  # MySQL服务端的ip地址
    port=3306,  # MySQL默认的port地址(也就是端口号)
    user='root',  # 用户名
    password='123',  # 密码,也可以简写passwd
    database='db1',  # 使用库名，简写为'db'
    charset='utf8mp4',  # 字符编码，不要加杠
)

# 2.产生获取命令的游标对象
cursor = conn_obj.cursor(
    cursor=pymysql.cursors.DictCursor
)  # 括号里不写参数，数据是元组要元组，不够精确，添加一个参数则会将数据处理成字典

# 3.编写SQL语句
# sql = 'show tables;'
sql = 'select * from dep;'

# 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 @   Super小赵  阅读(170)  评论(0)  编辑  收藏  举报
相关博文：
·  12月4周报
·  MySQL查询关键字
·  python基础(数据库、可视化软件Navicat、python操作MySQL)
·  mysql多表查询
·  多表查询、Navicat、python连接MySQL
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现
****************************************** 页脚Html代码 ******************************************
点击右上角即可分享
微信分享提示
AI IDE Trae