• 多表查询、Navicat软件、PyMySQL模块

    目录

    多表查询、Navicat软件、PyMySQL模块

    一、多表查询的两种方法

    1.准备工作

    python
    1.创建数据库
create databases db4
2.使用数据库
use db4
3.表数据准备
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
);

4.插入数据
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);

    2.第一种:连表操作

    python
    *****1. inner join  # 内链接 只链接两张表共有的部分
    select * from emp inner join dep on emp.dep_id=dep.id
    
2. left join  # 左链接 以左表为基准展示所有的数据若没有数据用NULL补充
   select * from emp left join dep on emp.dep_id=dep.id
    
3. right jion  # 右链接 以右表为基准展示所有的数据若没有数据用NULL补充
   select * from emp right join dep on emp.dep_id=dep.id
    
    
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

    3.第二种:子查询

    python
    将一条SQL语句用括号当成另一条SQL语句的查询条件 子查询类似于日常生活中的解决问题的思路
    题目:求姓名是某某的员工部门名称
    思路：
        步骤一:先根据员工姓名获取部门编号
            select dep_id from emp where name='jason'
        步骤二:再根据部门编号获取部门名称
            select name from dep where id=200
            
            
    真正的步骤:
         select name from dep where id=(select dep_id from emp where name='jason');

    总结与结论:

    连接表操作之后可以实现连续连接N多张表 大概思路就是将拼接之后的表起别名当成第一张表与其他表连接 以此类推
    实际操作过程中 大部分情况下两种方法配合着使用可能性更多一些 但是具体用的时候根据实际需求再选择方法即可。

    二、多表查询练习题

    1.课堂多表查询练习题

    python
    1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询没有同时选修物理课程和体育课程的学生姓名
5、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)

    2.以上练习题对应的SQL语句详细

    python
    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;
    python
    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;
    python
    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 = '李平老师'
-- 					)
-- 			)
-- 	)
    python
    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
-- 	)
    python
    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
    1.concat与cancat_ws
concat用于分组之前
select concat(name,'|',sex) from emp;
concat_ws拼接多个字段并且中间的连接符一致
select concat('|',name,sex,age,dep_id) from emp;
2.表相关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

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

1.软件下载
	直接在浏览器下载即可 但是这款软件是收费的 要么花钱买 要么破解
    当然会直接选择破解 哈哈哈哈

2.破解方式
	详细破解过程 戳这里>>>https://www.bilibili.com/read/cv16884052
        
3.常用操作
    有些功能可能需要自己修改SQL预览
    创建库、表、记录、外键
    逆向数据库到模型、模型创建
    新建查询可以编写SQL语句并自带提示功能
    SQL语句注释语法
    --、#、\**\
    运行、转储SQL文件

    1.连接数据库操作
    image
    2.创建数据库操作
    image
    3.创建表操作
    image
    4.转储数据保存操作
    image

    五、python操作MySQL

    1.pycharm安装第三方模块pymysql的详细步骤

    python
    1.先点击file 再点击settings
2.先点击project:项目名 再点击PythonInterpreter
3.左键双击第三方模块列表框调出模块搜索对话框
4.输入PyMySQL模块名
5.给Spscify version打对勾
6.点击 Install Package 就可以

    2.python操作MySQL的基本操作

    python
    import pymysql

# 1.链接MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='1998',
    db='db4',
    charset='utf8mb4',
    autocommit=True
)
# 2.产生游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 括号内步写参数 结果是元组 写了之后[{},{}]
# 3.遍写SQL语句
sql = 'select * from emp;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql)
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)

    3.pymysql模块 知识点补充说明

    python
    1.获取数据
    fetchall()  # 获取所有的结果
    fetchone() # 获取结果集的第一个数据
    fetchmany(5)  # 获取指定数量的结果集
    cursor.scroll(1,'relative')  # 基于当前位置往后移动
    cursor.scroll(0,'absolute')  # 基于数据的开头往后移动

2.增删改查
    autocommit=True  # 直接配置(自动确认) 强烈推荐 这样就安心进行增删改查了
    conn.commit()  # 需要写代码(二次确认) 不太推荐 因为容易忘记

    __EOF__

  • 本文作者： The Road of Learning 阿丽米热
  • 本文链接： https://www.cnblogs.com/almira998/p/16934127.html
  • 关于博主： 评论和私信会在第一时间回复。或者直接私信我。
  • 版权声明： 本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
  • 声援博主： 如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。
  • 相关阅读:
    （五）编译中出现的向后兼容问题
    PMI 为什么不公布 PMP 题目和 PMP 考试答案
    3. 栈基本概念、【顺序栈、共享栈 和 链栈】代码实现
    “SRP模型+”多技术融合在生态环境脆弱性评价模型构建、时空格局演变分析与RSEI 指数的生态质量评价及拓展应用
    ue影片渲染队列渲染出来的png序列有些带有光斑和黑斑
    Spring Boot Auto-Configuration
    测试大老都是怎么理解cookie&session的?
    UOS/Linux的root权限下启动一个带界面的程序
    Python数据容器：set(集合)
    kubernetes集群之Pod优雅重启
  • 原文地址：https://www.cnblogs.com/almira998/p/16934127.html