首页 > 数据库 >进入python的世界_day40_数据库——多表查询补充、Navicat的使用、多表查询实操、Python使用mysql初学

进入python的世界_day40_数据库——多表查询补充、Navicat的使用、多表查询实操、Python使用mysql初学

时间：2022-11-28 20:56:51浏览次数：47  
标签：info 多表 Python class 查询 student WHERE id SELECT

一、多表查询

方式一：关键字链接

  • inner join
  • left join
  • right join
  • union
# 数据准备————随便建立两表
CREATE TABLE `class_info` (
  `id` int(11) NOT NULL DEFAULT '0',
  `class_name` varchar(255) DEFAULT NULL,
  `teacher_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `class_info` VALUES (1, '一年级一班', '李华');
INSERT INTO `class_info` VALUES (2, '一年级二班', '张红');
INSERT INTO `class_info` VALUES (3, '二年级一班', '米老师');
INSERT INTO `class_info` VALUES (4, '二年级二班', '刘老师');
INSERT INTO `class_info` VALUES (5, '三年级一班', '王老师');
INSERT INTO `class_info` VALUES (6, '三年级二班', '范老师');
___________________________________________

CREATE TABLE `student_info` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `class` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `student_info` VALUES (0, '小刘', 15, '1');
INSERT INTO `student_info` VALUES (2, '小王', 14, '1');
INSERT INTO `student_info` VALUES (3, '小红', 16, '2');
INSERT INTO `student_info` VALUES (4, '小李', 16, '3');
INSERT INTO `student_info` VALUES (5, '小肖', 17, '4');
INSERT INTO `student_info` VALUES (6, '小风', 18, '5');
INSERT INTO `student_info` VALUES (7, '小双', 18, '6');
INSERT INTO `student_info` VALUES (8, '小哲', 15, '2');
INSERT INTO `student_info` VALUES (9, '小智', 17, '3');
INSERT INTO `student_info` VALUES (10, '小霍', 16, '5');

# 1.inner join ____ on ____  内连接 只拼接双表共有的数据
SELECT student_info.id as '学号',student_info.`name` as '姓名', class_info.class_name as '班级', class_info.teacher_name as '班主任' from student_info INNER JOIN class_info on student_info.class = class_info.id;



# 2.left join 左连接 ，以关键字左边的表为基准，展示左表所有的数据
# 先去给class_info底下加一行，但是学生表不动，我们拿班级表放在左边为例
SELECT student_info.id as '学号',student_info.`name` as '姓名', class_info.class_name as '班级', class_info.teacher_name as '班主任' from class_info LEFT JOIN student_info on student_info.class = class_info.id;



# 3. right join 和left join 同理，就不再描述了

# 4. union 全链接
# 以左右表为基准 展示所有的数据 各自没有的全部NULL填充

方式二：子查询

​ 将一张表的查询结果括号括起来当作另外一条SQL语句的条件

​ 类似我们处理事情先处理一步，然后基于第一步的结果进行第二步

# 还是拿上面的两表做例子
# 比如说查询小智的班级的班主任名字
1.先获取小智的班级id
SELECT class from student_info WHERE name = '小智'; # 3
2.根据班级id获取班级的班主任名字
SELECT teacher_name FROM class_info WHERE id = 3;  # 米老师

子查询：# 合并
SELECT teacher_name FROM class_info WHERE  id = (SELECT class from student_info WHERE name = '小智');  # 米老师

# 注意，不要把1的分号也复制到子查询中哦~

二、第三方可视化SQL管理软件——navicat

​ 可以充当很多数据库软件的客户端 最主要的用于MySQL

​ 该软件也支持MariaDB、MongoDB等

​ 从官网下载 然后网上找破解工具

# 推荐使用注册机的方法 可以使用最新版16
https://cmsblogs.cn/3902.html
# 使用参考
https://www.bilibili.com/video/BV1yA41147Vi/?from=search&seid=14234973416271175041&spm_id_from=333.337.0.0&vd_source=f92dcdbc952254e0ee168b365a4b444e

  • 注意 MYSQL 的端口号3306

三、多表查询练习题

  • 前提：把结构和数据的文件通过navicat ——库——右键——运行SQL文件——选中执行，再刷新一下库刷新一下表，准备好练习的素材
# 注意：做多表查询的题，一定一步步来，先确定需要用到几张表，去喽一眼这些表的数据长啥样，然后思考数据间的逻辑分步来
1.查询所有的课程的名称以及对应的任课老师姓名
# 第一步：要用到课程表，老师表，去看下数据长啥样
# 第二步：连表，取两个字段，用join类方法好一些
# 第三步：课程表做左表，老师表做右表，inner join ...on ...链接
SELECT
	cname AS '课程名',
	teacher.tname AS '任课老师' 
FROM
	course
	INNER JOIN teacher ON course.teacher_id = teacher.tid;
———————————————————————————————————————————————————————————    
2.查询平均成绩大于八十分的同学的姓名和平均成绩
# 第一步，要用到学生表，成绩表，去看下数据长啥样
# 第二步，连表，取两个字段，用join类方法好一些
# 第三步，先去按学生id分组课程表，拿到每个学生的Id和平均分
SELECT student_id,AVG(num) FROM score GROUP BY student_id;
# 第四步，拼接学生表，学生表做左表，基于第三步的表做右表，inner join ...on ...链接
SELECT
	student.sname AS '姓名',
	t.a AS '平均分' 
FROM
	student
	INNER JOIN ( SELECT student_id, AVG( num ) AS a FROM score GROUP BY student_id ) AS t ON student.sid = t.student_id;
———————————————————————————————————————————————————————————     
3.查询没有报李平老师课的学生姓名
# 第一步，通过看库的逆向模型 直接的要用到很多表，学生表，课程表，老师表，分数表
# 第二步，先去拿李平老师的老师ID号
SELECT tid FROM teacher WHERE tname = '李平老师';
# 第三步，根据第二步的ID号去课程表拿到这个ID教了几门课的课程ID号
SELECT cid FROM course WHERE teacher_id =  (SELECT tid FROM teacher WHERE tname = '李平老师');
# 第四步，根据第三步的课程号，去筛选成绩表中报了这个课程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 = '李平老师'));
# 这里就很简单了，基于第四步的学生ID号去学生表拿到姓名即可，然后取反
SELECT student.sname as '姓名' 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.查询没有同时选修物理课程和体育课程的学生姓名(只要选了其中一门的 两门都选和都没选的都不要)
# 第一步，要用到三张表，学生表，课程表，成绩表
# 第二步，学生表做最后一表，课程表做最内一表
# 第三步，先去课程表拿到物理、体育的课程ID号
SELECT cid FROM course WHERE cname = '体育' or cname = '物理';
# 第四步，拿着第三步的课程ID号去成绩表，找选了这些课的学生ID
SELECT  * FROM score WHERE score.course_id in (SELECT cid FROM course WHERE cname = '体育' or cname = '物理');
# 第五步，根据题意，只要选这两门课数为1的学生ID号，以学生ID分组
SELECT  student_id FROM score WHERE score.course_id in (SELECT cid FROM course WHERE cname = '体育' or cname = '物理') GROUP BY student_id HAVING COUNT(course_id) = 1;
# 第六步，很简答了，不用连表，子查询使用
SELECT sname FROM student WHERE sid in (SELECT  student_id FROM score WHERE score.course_id in (SELECT cid FROM course WHERE cname = '体育' or cname = '物理') GROUP BY student_id HAVING COUNT(course_id) = 1);
# 最后再点击一键美化
——————————————————————————————————————————————————————————————
5.查询挂科超过两门(包括两门)的学生姓名和班级
# 第一步，要用到学生表，班级表，成绩表
# 第二步，去成绩表找出成绩<60的数据
select * from score where num < 60;
# 第三步，按照学生ID分组，计数挂科>=2的
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 第四步，需要两个字字段，用连表方式，inner join ...on ...链接班级表和学生表
select  from class inner join student on class.cid = student.class_id;
# 第五步，把第三步的数据添加到筛选条件即可
SELECT student.sname as '学生名', class.caption as '班级名' FROM class INNER JOIN student on class.cid = student.class_id WHERE student.sid in( select student_id from score where num < 60 group by student_id having count(course_id) >= 2);

四、python操作mysql

1.先下载第三方模块，pymysql

2.代码实操

#1.先连接mysql
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='15679',
    db='db2',
    charset='utf8mb4',
    autocommit=True  # 自动二次验证增删改查
)

#2.产生游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 上面这句的括号内，不填参数是返回元组，加参数会反字典，K是表的字段名
#3.编写sql语句

sql = 'select * from score;'

#4.发送sql语句

cursor.execute(sql)

# 5.获取
# res = cursor.fetchall() # 这个是拿所有的返回数据
res = cursor.fetchmany(2)  # many可以自己指定多少条
						# one是第一个数据
print(res)
conn.close()

标签：info,多表,Python,class,查询,student,WHERE,id,SELECT
From： https://www.cnblogs.com/wznn125ml/p/16933577.html

相关文章

  • 复杂mysql/多表查询
    目录多表查询的两种方法sql语句基础语法补充concat/existe/表字段增加修改删除复杂sql练习题多表查询的两种方法方式1:连表操作innerjoin内连接select*fro......
  • MYSQL 多表查询练习
    多表查询的两种方法#方法一：连表操作#1.内连接innerjoinselect*fromempinnerjoindeponemp.dep_id=dep.id;"""只会连接两张表中公有的数据......
  • python-爬取网页
    爬虫简介网络蜘蛛,网络机器人,抓取网络数据的程序其实就是用Python程序模仿人点击浏览器并访问网站,而且模仿的越逼真越好目的公司业务所需数据公司......
  • python中value和only，以及exclude和defer的区别？
    一、value和only的区别rows=User.objects.values('id','username','age','re_password')，这里得到的是一个queryset，里面存放的是字典，  但是如果想要在查他的......
  • 连表操作join 子查询 SQL补充 数据库软件navicat pymysql模块
    目录多表查询的两种方法方式1:连表操作方式2:子查询SQL补充知识点1.分组之前字段拼接concatconcat_ws2.SQL执行判断条件exists3.表相关SQL补充修改表名altertable.........
  • python爬取精美壁纸图片（阴阳师）
    一、requests获取数据importrequestsifnotos.path.exists('02-heng'):os.makedirs('02-heng')url="https://yys.163.com/media/picture.html"headers={......
  • spl多表查询
    1.多表查询概念：从多张表查询数据2.分类：连接查询和子查询。有两张表A、B连接查询：内连接相当于查询A、B交集数据例1：select*fromemp,deptwhereemp.dep_id=dept.id;......
  • MySQL数据库——多表查询
    MySQL数据库——多表查询一、多表查询的两种方式1.1连表操作'''innerjoin 内连接只连接两张表中公有的数据部分'''select*fromempinnerjoindeponemp.dep_i......
  • 多表查询的两种方法、小知识点补充说明、可视化软件Navicat、多表查询练习题、python
    目录多表查询的两种方法小知识点补充说明可视化软件Navicat多表查询练习题python操作MySQLpymysql补充说明多表查询的两种方法两张表方式1:连表操作 innerjoin 内......
  • 多表查询的方法与第三方模块pymysql
    多表查询的两种方法方式一:连表操作内连接：关键字:innerjoinselect*fromempinnerjoindeponemp.dep_id=dep.id;只连接两张表中公有的数据部分左连接:......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99