python四十三课----多表查询的方法，表相关SQL语句补充，navicat可视化软件，多表查询练习题，python操作MySQL
上周内容回顾
-
查询常用关键字
select与from where筛选 逻辑运算符、成员运算、身份运算 模糊查询:like % _ group by分组 按照指定的条件将个体分为整体 便于数学统计 聚合函数:max、min、sum、avg、count 小特性:分组之后默认只可以直接获取分组的字段数据 sql_mode='only_full_group_by' having过滤 本质与where相同 都是筛选数据 只不过 where分组之前 having分组之后 distinct去重 去重的前提是数据一模一样 order by排序 默认是升序(asc)也可以改为降序(desc) limit分页 主要用于数据的限制展示也是优化操作 regexp正则 ps:可以适当回顾正则表达式内容 """ 补充小知识 1.字段起别名 as 2.group_concat() 分组之后获取除分组字段意外的字段数据 并且支持拼接 """ -
多表查询前戏
笛卡尔积:极其浪费资源 select * from emp,dep; ps:涉及到多表操作 字段为了不出现冲突最好在字段前加表名
今日内容概要
- 多表查询的两种方法
- 小知识点补充说明
- 可视化软件Navicat
- 多表查询练习题
- python操作MySQL
今日内容详细
.
.
.
.
.
.
.
.
.
.
昨天的连表操作方式：
select * from emp,dep where emp.dep_id=dep.id;
但这种连表操作方式当 emp与dep里面的数据很多很多时，
由于该方法先会用到是笛卡尔积的方式让emp与dep里面的数据相乘，
很多乘很多时就可能会直接卡死！！！所以用笛卡尔积的方式连表并不合理！！！
---------------------------------------------------------------
.
.
.
.
.
.
.
多表查询的两种方法
方式1 连表操作
关键字：单join 默认就是内连接
关键字： inner join 内连接
select * from emp inner join dep on emp.dep_id=dep.id;
emp inner join dep 讲两个表连接在一起，on后面接的连接的依据！！！
on emp.dep_id=dep.id 按照员工表里面的dep_id字段与部门表里面的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多张表！！！
思路:
将拼接之后的表起别名当成一张表，再去与其他表拼接！！！
再讲拼接后的表起别名当一张表，再去与其他表拼接 ！！ 依次往复即可！！
----------------------------------------------------
内连接
.
左连接 以左表为基准 展示左表所有的数据！！！ 右表如果没有对应项则用NULL填充！！！
.
右连接 以右表为基准 展示右表所有的数据！！！ 如果没有对应项则用NULL填充！！！
.
全连接
.
.
.
连表操作拓展操作 union
union 是对结果的连接，不像join是用条件对两张表进行连接
所以用union连接两张表，可以不用连接条件，只要保证这两张表的每个字段一样，或者字段不一样，
你起别名让字段名一样，都能union
示范：
select * from (
select biz_id,data_type,time,time_num,content from data_raw_0 where data_type="virtural_coil_statistics"
union
select biz_id,data_type,time,time_num,content from data_raw_1 where data_type="virtural_coil_statistics"
union
select biz_id,data_type,time,time_num,content from data_raw_2 where data_type="virtural_coil_statistics"
union
select biz_id,data_type,time,time_num,content from data_raw_3 where data_type="virtural_coil_statistics"
union
select biz_id,data_type,time,time_num,content from data_raw_4 where data_type="virtural_coil_statistics"
)t
order by t.time_num desc limit 10;
# 这里就是把5张表的数据全部连到一起，组成一张表，取别名为t
# 然后对这张表的time_num字段，按降序排序，最后再分页拿前10条
.
.
.
.
.
.
.
.
.
.
方式2: 子查询
方式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括号里面的字段对应的数据拼接到一起！！！
-----------------------------------------------------------
concat_ws拼接多个字段并且中间的连接符一致
select concat_ws('|',name,sex,age,dep_id) from emp;
---------------------------------------------------------
2. where exists
sql1 where exists sql2
sql2有结果的情况下才会执行sql1 否则不执行sql1 返回空数据
注意sql2的语句整体应该用括号括起来，可以理解为where后面的所有条件是（限定查询条件）
sql2有结果的情况下才会执行sql1 ！！！
---------------------------------------------------------
concat
.
concat_ws
.
.
3. 表相关SQL语句补充！！！
3.表相关SQL补充
alter table 表名 rename 新表名; # 修改表名
--------------------------------------------------
alter table 表名 add 字段名 字段类型(数字) 约束条件; # 添加字段名
在原来已有的最后一个字段后面再加一个字段！！！
--------------------------------------------------
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段; # 添加字段名
在after后面指定的字段下面添加一个字段！！！
--------------------------------------------------
alter table 表名 add 字段名 字段类型(数字) 约束条件 first; # 添加字段名
在已有的字段前面再添加一个字段
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--------------------------------------------------------------------------
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件; # 修改字段名
change不仅支持修改字段名也支持在在修改字段名的同时也修改字段类型！！！
change也支持不修改字段名就修改字段类型，这样新字段名就要与旧字段名写一样了！！！
--------------------------------------------------
alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
如果不想改字段名只相改字段类型可以同modify，专门用来改字段类型！！！
--------------------------------------------------
alter table 表名 drop 字段名; # 删除字段名
每次只能删一个字段！！！
删字段要慎重，删一个字段，该字段下的所有数据全没了！！！！！！
--------------------------------------------------
.
添加字段
.
after 添加字段
.
first 添加字段
.
change不仅支持修改字段名也支持在在修改字段名的同时也修改字段类型！！！
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
.
alter table 表名 drop 字段名; 删除字段名
.
.
.
.
可视化软件Navicat
第三方开发的，用来充当数据库客户端的简单快捷的操作界面！！！
------------------------
无论第三方软件有多么的花里胡哨,底层的本质还是SQL语句
能够操作数据库的第三方可视化软件有很多
其中针对MySQL最出名的就是Navicat
-------------------------------------------------
1.浏览器搜索Navicat直接下载
版本很多、能够充当的数据库客户端也很多
2.破解方式
先试用在破解、直接下载破解版(老版本)、修改试用日期
--------------------------------------------------
navicat16 破解版操作详见下
7.打开navicat的注册页面，打开激活工具，点击Generate生成Keygen，点击Copy，粘贴到navicat注册页面中！点击激活
8.点击手动激活
9.按图中步骤操作，即可激活成功
.
.
.
.
.
.
.
.
.
.
Navicat 常用操作
连接数据库
有些功能可能需要自己修改SQL预览！！！
文件---新建连接---mysql
进入新建（mysql）的界面---用户名可以不写---输入用户名与密码---连接测试------
连接成功后点击确定---接连接上了
----------------------------------------
一开始连接的mysql的名字是灰色的，鼠标双击一下名字，就会显示出mysql客户端里面有多少个数据库了！！！同理数据库的图标一开始也是灰色的，双击一下就亮了，并显示出该库里面有多少表！！
---------------------------------------
.
.
.
.
.
.
.
逆向数据库到模型
右击库名-----点击逆向数据库到模型
帮你把目前所在库里面，所有的表，用图表的形式展示出来，
如果两张表之间有关系还会有一根线连着，代表有关系！！！
点击线就能看到两个表里面哪两个字段有关联！！！
甚至创表的时候也可以点击模型按钮，点新建模型按钮，在里面创表！！
---------------------------------------------
.
.
.
.
.
.
.
.
自己写sql查询数据
自己写sql查询数据也可以
新建查询可以编写SQL语句并自带提示功能
点查询---点新建查询---选择对应的库----正常编写sql查询语句就行了
----点击运行就能查询到表里面的数据了！！！
---------------------------------------------
当有多条sql语句的时候可以选择单独的sql语句执行，也可以一起执行
还有当查询的sql语句我们想保留的时候，点击保存按钮，输入对应的保存名称，
就可以在查询的图标下面看到对应保存的sql语句了！！！
---------------------------------------------
.
.
.
.
.
.
.
.
SQL语句注释语法有三种：
Ctrl+？就是黑掉该行代码在该代码前面加--
# 单行注释
/*多行注释*/
---------------------------------------------
.
.
.
当想要一次性把一个库里面的所有的表数据给别人，怎么办？
转储SQL文件
右击库图标-----点击转储sql文件----点击结构和数据----选个路径保存一下----该库里面所有数据就生成了一个sql文件了------就可以发给别人了
.
运行SQL文件
假如别人发了一个sql文件给我-----我就可以在任意一个库下-----右击库名字------点击运行sql文件------选择sql文件再点开始-----再点关闭-----这样别人sql文件里面的所有表数据就粘贴过来了
---------------------------------------------
.
.
.
.
.
连接客户端
.
.
创建库
右击localhost_3306 点击创建新建数据库
填写数据库名,编码一定要选择utf8,排序规则不要选,点确定数据库就创好了！！！
.
.
创建表（按Tab键光标就会自动往下个框里面走！）
双击你要选择的库，在里面右击表的图标，点击新建表。
一般第一个字段设置好是不是主键，如果该字段设置了主键，别忘了左下角默认自动递增的选项勾一下
然后里面正常输入字段名，字段类型，确定是不是设置该字段不能为null,勾上了就代表该字段不能为空，否则默认为该字段可以为空
当所有字段名与字段类型，约束条件写设置好了之后，实际上底层就是在写sql语句
可以点击sql预览看一下，点击保存后，输入要创的该表的表名确定后！！！表就创好了！！！
.
常用的字段类型
.
建外键
先把两张表建好后，才能建外键
右击需要建外键的表的图标，点击设计表，进入编辑表的界面后，点击外键按钮
选择好关联字段、关联的表、关联的字段、以及删除与更新时的动作一般就是CASCADE就行了
最后点个保存就好了。
外键部分的操作 创外键 首先必须先再创一个关联的表！！！
比如创一个关联部门表dep，并往里面添加点数据。
再给userinfo表，建立外键，绑定部门表！！！
右击userinfo表图标，点击设计表后，在原来的字段名下面再加一个外键的字段名，
最后再点击外键按钮，在里面继续设置 ：
字段里面选择把哪个字段设置成外键，参考的库不用选，
被引用的表（老版本叫参考表）要选你要绑定关联的表，被引用的字段（老版本叫参考栏位）要选你要绑定关联表里面的字段名。
最后删除时与更新时选择cascade 就是sql里面的连级跟新与连级删除的操作
都搞好后点击保存！！就好了！！
然后回到userinfo表里面外键字段dep_ip下面就可以通过点选择关联表里面的对应字段名里面的数据了！！而不能随意添加数据了！！！
.
这个时候如果想要把外键改成一对一的话，要再点设计表，点一下索引按钮，把字段与索引类型选择一下，然后再点击sql预览，看有没有这句话 ADD UNIQUE INDEX(外键字段名) 有了就证明一对多的外键被约束成了一对一的外键了！！
.
.
.
修改表
右击userinfo表图标，点击设计表，就可以对当前的表进行修改，比如改字段类型，字段条件或者添加字段删除字段等操作。
.
.
添加表数据
双击表名可以给表添加数据！！
相当于sql语句操作： INSERT INTO 表名 (字段名1, 字段名2, 字段名3) VALUES (数据1, 数据2, 数据3)
最后点击左下角的✔ 这样表里面的数据就添加好了！！
.
.
.
.
.
.
多表查询练习题 重要 面试经常会考！！！！！
该创表的sql文件在python基础day43文件夹里面
-------------------
编写复杂的SQL不要想着一口气写完
一定要先明确思路 然后一步步写一步步查一步步补
-------------------
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询没有同时选修物理课程和体育课程的学生姓名
5、查询挂科超过两门(包括两门)的学生姓名和班级
-------------------------------------------------------
1、查询所有的课程的名称以及对应的任课老师姓名
解题思路：
1.先确定需要用到几张表： 课程表 老师表 要的就是课程名称及对应的老师姓名
----------------------
2.预览表中的数据 做到心中有数
select * from course;
select * from teacher;
----------------------
3.确定多表查询的思路 连表 子查询 混合操作（要的数据来源于两张表，所以连表更合适一点）
SELECT * from course inner join teacher on course.teacher_id = teacher.tid;
----------------------
4.最后再确定select 后面的* 要改成什么
由于我们只要课程名称与老师姓名，所以把*改成course.cnam,teacher.tname 就好了！！！
SELECT course.cnam,teacher.tname from course inner join teacher on course.teacher_id = teacher.tid;
.
.
.
.
第二题
-- 2、查询平均成绩大于八十分的同学的姓名和平均成绩
1.先确定需要用到几张表： 学生表 分数表
2.预览表中的数据 知道了score表里面student_id字段与student表里面sid关联的！！！
select * from student;
select * from score;
3.以 查询平均成绩大于八十分 为切入点 故以分数表为切入点！！！
求平均成绩大于八十分的学生 不就是： 先求每个学生的平均成绩---------然后筛选出大于80的
score表按照student_id分组,然后avg求num
就得出按学生分组后，每个学生所有课程的平均成绩了！！！
select student_id,avg(num) from score group by student_id;
拿到了所有学生的平均成绩了！！！
select student_id,avg(num) from score group by student_id having avg(num)>80;
再加个筛选条件就拿到了: 大于80分的学生id与学生平均成绩了！！的虚拟表了
可以给聚合函数avg(num)起别名，但是有的sql版本不支持！！
---------------------------------
4.确定最终的结果需要来自两张表： 上面的虚拟表 和 学生表
所以采用连表更加合适 虚拟表 和 学生表 进行连接 ！！！
select * 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;
把上面的虚拟表的整体代码用括号括起来，放到join的后面，作为和学生表连接的表，再起个别名，利于拿虚拟表里面的student_id
-------------------------------------
5. 最后把*号改一下，改成 student.sname,t1.student_id
就完成了！！！
.
.
.
.
.
.
第三题
3、查询没有报李平老师课的学生姓名
-- 3、查询没有报李平老师课的学生姓名
1.先确定需要用到几张表： 老师表 课程表 分数表 学生表
2.预览每张表的数据
3.确定思路
思路1:正向筛选：大致思路，老师表筛掉李平姓名，然后课程表与前一个表连接，这样就能得到不是李平教的课程，对应的课程表里的cid 了
然后再用分数表与前一个虚拟表连接，就得到了没有报李平课程的学生id了，然后学生表再和前一个虚拟表连接，就能拿到没报李平课程的学生姓名了！！！
--------------------------------------
思路2:筛选所有报了李平老师课程的学生id,然后取反即可,操作简单点！！！
-----------------------------------
步骤1 获取李平老师教授的课程cid，可以先用子查询拿到老师表里面李平老师对应的tid
select tid from teacher where tname = '李平老师'; # 先获取李平老师教授的课程tid
select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
这样就拿到了 课程表里面 李平老师所教授的课程对应的cid号了
---------------------
步骤2 根据课程id筛选出，分数表里面所有报了李平老师的学生sid
select * from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
这样就拿到课程表里面所有报李平老师课的所有数据了！！！
--------
select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
此时只想要学生表里面的student_id对应的数据就行了，所以把*换掉，
因为里面老师教了两门课，如果学生选了这两门课，所以分数表里面学生的id可能会重复，所以再用distinct去一下重复
----------------------
步骤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.根据给出的条件确定起手的表： 课程表
--------------------------------------------------------
select cid from course where cname in ('物理','体育');
# 4.筛选出课程表里面物理课和体育课对应cid，该cid是与分数表里面的course_id相关联的
----------------
select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 5.根据课程cid筛选出分数表里面所有跟物理与体育相关的学生id（这样就先把物理与体育一门不报的学生先筛掉了）
------------------------------
# 6.统计每个学生报了的课程数量!!! 再按学生分组，再在每一个学生id分组后面利用聚合函数求出该学生所报课程的课程数！！！！！！
select student_id,count(course_id) from score where course_id in (select cid from course where cname in ('物理','体育'))group by student_id;
------------------------------
# 再筛选出，分数表里面，每个学生报了的课程数量等于1的 学生id 这样就把同时报物理和体育两门的学生id给筛掉了 这样该分数表里面的学生id 就都是只报了一门物理或者体育的学生id了
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;
# 最后由于题目要求是只要学生姓名，所以要可以利用到子查询，由于学生表里面的sid是与分数表里面的student——id关联的，所以可以把目前该表中select student_id,count(course_id) 中的count(course_id)字段名直接去了，就只保留student_id字段名！！！
------------------------------
# 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）;
------------------------------
.
.筛选出课程表里面物理课和体育课对应cid，该cid是与分数表里面的course_id相关联的
.
.
.根据课程cid筛选出,分数表里面所有跟物理与体育相关的学生id（这样就先把物理与体育一门不报的学生先筛掉了）
.
.
.
.
.
.
.
.
.
.
第五题
-- 5、查询挂科超过两门(包括两门)的学生姓名和班级
1.先确定涉及到的表 分数表 学生表 班级表
2.预览表数据
select * from class
3.根据条件确定以： 分数表作为起手条件
---------------------------
# 步骤1 先筛选掉大于60的数据
select * from score where num < 60;
-----------
# 步骤2 统计每个学生挂科的次数 就是在分数表里面按student_id分组，然后统计学生组里面每一个学生的挂科数量，
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 方法1：连接班级表与学生表 然后基于学生id筛选即可
# 方法2：或者先用学生表和上面分数表里面筛选出来的挂两门课的student_id 利用子查询的知识
# 先得到学生表里面挂两门课学生的sname和class_id 的数据
# 然后再用课程表和该虚拟表做一个连接操作
# 最后select后面写上要的字段caption与sname 就完成了
select sname,class_id from student where sid in (select student_id from score where num < 60 group by student_id having count(course_id) >= 2);
此处虽然上面表的数据中student_id只有一个数据，where 后面的条件可以用 = 但是如果考虑到后续还有其他的学生数据往分数表里面添加的时候，当出现挂两门课的学生不止一个学生的话 这里用 = 就不对了，所以考虑代码通用性讲 = 改为in 比较合适
-----------------------------------------------
select * from class inner join (select sname,class_id from student where sid in (select student_id from score where num < 60 group by student_id having count(course_id) >= 2)) as t1 on class.cid = t1.class_id;
class表与上面的虚拟表，进行连表操作， 虚拟表起别名为t1
------------------------------------------
select class.caption,t1.sname from class inner join (select sname,class_id from student where sid in (select student_id from score where num < 60 group by student_id having count(course_id) >= 2)) as t1 on class.cid = t1.class_id;
最后去掉多余的字段
.
.步骤1 分数表作为起手条件,先筛选出<60的数据,小于60分视为挂科了！！！
.
.步骤2 统计每个学生挂科的次数
.
.步骤3 筛选次数大于等于2的数据
.
.或者先用学生表和上面分数表里面筛选出来的挂两门课的student_id 利用子查询的知识 先得到学生表里面挂两门课学生的sname和class_id 的数据
.
.然后再用课程表和该虚拟表做一个连接操作 最后select后面写上要的字段caption与sname 就完成了
.
.
.
.
python操作MySQL
pymysql模块
pip3 install pymysql
-------------------------------
最基本代码：
import pymysql
# host='127.0.0.1' 服务端的ip地址 3306端口 自报家门账户与密码后
# 再指定要操作的库 指定编码
# 1.连接MySQL服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db4_03',
charset='utf8mb4'
)
# 2.产生游标对象 cursorn 光标；[计] 游标，指针
# cursor = conn.cursor() # 括号内不填写额外参数,fetchall()的结果数据是一个一个元组 指定性不强 [(),(),()] 不知道每个元组里面的数据是哪个字段下的数据，不方便！！！
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # [{},{}]
# 3.编写SQL语句
sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql) # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows) # 如果不需要返回值结果，可以不接收返回值
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)
.
. 括号内不填写额外参数,fetchall()的结果数据是一个一个元组 指定性不强 [(),(),()] 不知道每个元组里面的数据是哪个字段下的数据，不方便！！！
.
.fetchall()的结果数据以字典的形式展示，键就是字段名，值就是数据
.
.
.
pymysql补充说明
1.获取数据
fetchall()
获取所有的结果 类似于文件光标的概念，执行完一次，再执行fetchall()就获取不到结果了！！！
fetchone() 获取结果集的第一条数据
fetchmany() 获取指定条数的结果集
ps:注意三者都有类似于文件光标移动的特性
------------------------------------------------------------------------
控制光标移动：
cursor.scroll(1,'relative') # 基于当前位置往后移动一位，一条数据就算移动一位
cursor.scroll(0,'absolute') # 基于数据的开头往后移动
------------------------------------------------------
2.增删改查
默认情况看用python是不能对数据库进行增删改的操作的，只有查看数据的权利！！！
如果想要对数据库进行增删改的操作，必须要二次确认！！！
方法一：
需要在原来基础代码的最下面再写一行代码！！！
conn.commit() # 针对 增 删 改 需要二次确认(代码确认)
-------------------------------------------
方法二：
也可以在原来基础代码的第一行，连接MySQL服务端代码conn = pymysql.connect(）的括号里面再加一行配置
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
db='db4_03',
charset='utf8mb4'
autocommit=True
)
---------------------------
autocommit=True # 针对增 删 改 自动确认(直接配置)
.
.
.
.
作业
抽空练习博客内更多练习题(能完成三分之一即可 答案可能有小错)
https://www.cnblogs.com/Dominic-Ji/p/10875493.html
1.整理今日内容及博客
2.练习多表查询练习题
3.尝试使用pymysql编写用户登录注册功能
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT cname,tname from course inner join teacher on course.teacher_id = teacher.tid
-------------------------------------------------------------
2、查询学生表中男生有多少人
SELECT gender,count(sid) from student where gender = '男' GROUP BY gender
-------------------------------------------------------------
3、查询物理成绩等于100的学生的姓名
课程表 分数表 学生表 先拿到课程表物理课对应的cid
SELECT cid from course WHERE cname = '物理'
SELECT student_id from score where course_id = (SELECT cid from course WHERE cname = '物理') and num =100
select sname from student where sid in (SELECT student_id from score where course_id = (SELECT cid from course WHERE cname = '物理') and num =100
)
------------------------------------------------------------
4、查询平均成绩大于八十分的同学的姓名和平均成绩
学生表 成绩表 要两个数据，两个数据又在不同的表里面，必须要用到表连接！！
先拿分数表里面的平均成绩大于八十分的成绩和student_id
select student_id,avg(num) as avg_num from score GROUP BY student_id HAVING avg(num) >80
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
注意这个第一个求平均分的聚合函数必须要起一个别名，虽然在第一个表里面用不到该别名,但是在下面的sql语句里面需要用该别名！！！t1.avg_num 如果直接t1.avg(num)会报错！！！
-----------------------------------------------------------------
5、查询所有学生的学号，姓名，选课数，总成绩
sid sname count() sum()
学生表 分数表 课程表
select sid,sname from student; # 学生的学号，姓名
SELECT student_id,count(course_id),sum(num) from score GROUP BY student_id;
# 对学生表 按学生分组用聚合函数拿总成绩与选课数
SELECT * from (select sid,sname from student) as t1 left join (SELECT student_id,count(course_id) as count_course ,sum(num) as sum_num from score GROUP BY student_id) as t2 on t1.sid = t2.student_id ; # 两表连接
# 再去掉多余字段
SELECT t1.sid,t1.sname,t2.count_course,t2.sum_num from (select sid,sname from student) as t1 left join (SELECT student_id,count(course_id) as count_course ,sum(num) as sum_num from score GROUP BY student_id) as t2 on t1.sid = t2.student_id ;
注意两表连接的时候，用左连接left join，不能用inner join 因为学生表里面有几个学生没有选课，所有分数表里面的的学生id是没有这几个学生对应的id的，所以用inner join 内连接后就看不到这几个学生的姓名
--------------------------------------------------------------
.
.
该题答案写的有问题，要参考看下jason的答案！！！！
6、 查询姓李老师的个数(看似简单，实际有点绕)
SELECT * from teacher where tname like '李%'; # 先看下李姓老师大概有几个
SELECT tname,count(tid) as count_id from teacher where tname like '李%' GROUP BY tname; # 以李姓老师分组，并对每一个李姓老师姓名统计一次次数
SELECT count(t1.tname) as '李姓老师个数' from (SELECT tname,count(tid) as count_id from teacher where tname like '李%' GROUP BY tname) as t1 GROUP BY t1.count_id;
# 对上面的虚拟表起个别名，然后再对该表按count_id进行分组，这样group_concat(t1.tname)里面就全是李姓老师姓名了，同理count(t1.tname) 结果就是李姓老师的个数了！！！
.
7、 查询没有报李平老师课的学生姓名
老师表 课程表 分数表 学生表
SELECT tid from teacher where tname = '李平老师' # 先拿t表里李平的tid
select cid from course WHERE teacher_id = (SELECT tid from teacher where tname = '李平老师') # 再拿c表里面李平课对应的cid
select student_id from score WHERE course_id in (select cid from course WHERE teacher_id = (SELECT tid from teacher where tname = '李平老师'));
# 再拿s表里面的选了李平课对应的student_id
SELECT sname from student where sid not in (select student_id from score WHERE course_id in (select cid from course WHERE teacher_id = (SELECT tid from teacher where tname = '李平老师')));
# 最后拿s表里面 不是选李平课的学生sid对应的sname
---------------------------------------------------------------
8 查询物理课程成绩比生物课程成绩高的学生的学号
select student_id,num from score where course_id = (SELECT cid from course WHERE cname = '物理')
select student_id,num from score where course_id = (SELECT cid from course WHERE cname = '生物')
SELECT * from (select student_id,num from score where course_id = (SELECT cid from course WHERE cname = '物理')) as t1 INNER join (select student_id,num from score where course_id = (SELECT cid from course WHERE cname = '生物')) as t2 on t1.student_id = t2.student_id;
SELECT t1.student_id from (select student_id,num from score where course_id = (SELECT cid from course WHERE cname = '物理')) as t1 INNER join (select student_id,num from score where course_id = (SELECT cid from course WHERE cname = '生物')) as t2 on t1.student_id = t2.student_id where t1.num > t2.num;
----------------------------------------------------------------------
.
9. 查询没有同时选修物理课程和体育课程的学生姓名（严谨一点是：查询只选一门物理或者体育课程的学生姓名，两门都选的与两门都不选的学生都不算！！！）
SELECT * from course;
SELECT cid from course where cname = '物理' or cname = '体育'
select student_id,count(course_id) as count_course from score WHERE course_id in (SELECT cid from course where cname = '物理' or cname = '体育') GROUP BY student_id HAVING count(course_id) < 2;
SELECT sname from student where sid in (select student_id from score WHERE course_id in (SELECT cid from course where cname = '物理' or cname = '体育') GROUP BY student_id HAVING count(course_id) < 2);
10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名
12、查询李平老师教的课程的所有成绩记录
13、查询全部学生都选修了的课程号和课程名
14、查询每门课程被选修的次数
15、查询之选修了一门课程的学生姓名和学号
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
17、查询平均成绩大于85的学生姓名和平均成绩
18、查询生物成绩不及格的学生姓名和对应生物分数
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
20、查询每门课程成绩最好的前两名学生姓名
21、查询不同课程但成绩相同的学号，课程号，成绩
22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
24、任课最多的老师中学生单科成绩最高的学生姓名
多表查询练习题的创建多张表的sql语句的navicat的转储文件
/*
数据导入：
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50624
Source Host : localhost
Source Database : sqlexam
Target Server Type : MySQL
Target Server Version : 50624
File Encoding : utf-8
Date: 10/21/2016 06:46:46 AM
*/
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for `class`
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(32) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `class`
-- ----------------------------
BEGIN;
INSERT INTO `class` VALUES ('1', '三年二班'), ('2', '三年三班'), ('3', '一年二班'), ('4', '二年九班');
COMMIT;
-- ----------------------------
-- Table structure for `course`
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
KEY `fk_course_teacher` (`teacher_id`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `course`
-- ----------------------------
BEGIN;
INSERT INTO `course` VALUES ('1', '生物', '1'), ('2', '物理', '2'), ('3', '体育', '3'), ('4', '美术', '2');
COMMIT;
-- ----------------------------
-- Table structure for `score`
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`num` int(11) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_score_student` (`student_id`),
KEY `fk_score_course` (`course_id`),
CONSTRAINT `fk_score_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
CONSTRAINT `fk_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `score`
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES ('1', '1', '1', '10'), ('2', '1', '2', '9'), ('5', '1', '4', '66'), ('6', '2', '1', '8'), ('8', '2', '3', '68'), ('9', '2', '4', '99'), ('10', '3', '1', '77'), ('11', '3', '2', '66'), ('12', '3', '3', '87'), ('13', '3', '4', '99'), ('14', '4', '1', '79'), ('15', '4', '2', '11'), ('16', '4', '3', '67'), ('17', '4', '4', '100'), ('18', '5', '1', '79'), ('19', '5', '2', '11'), ('20', '5', '3', '67'), ('21', '5', '4', '100'), ('22', '6', '1', '9'), ('23', '6', '2', '100'), ('24', '6', '3', '67'), ('25', '6', '4', '100'), ('26', '7', '1', '9'), ('27', '7', '2', '100'), ('28', '7', '3', '67'), ('29', '7', '4', '88'), ('30', '8', '1', '9'), ('31', '8', '2', '100'), ('32', '8', '3', '67'), ('33', '8', '4', '88'), ('34', '9', '1', '91'), ('35', '9', '2', '88'), ('36', '9', '3', '67'), ('37', '9', '4', '22'), ('38', '10', '1', '90'), ('39', '10', '2', '77'), ('40', '10', '3', '43'), ('41', '10', '4', '87'), ('42', '11', '1', '90'), ('43', '11', '2', '77'), ('44', '11', '3', '43'), ('45', '11', '4', '87'), ('46', '12', '1', '90'), ('47', '12', '2', '77'), ('48', '12', '3', '43'), ('49', '12', '4', '87'), ('52', '13', '3', '87');
COMMIT;
-- ----------------------------
-- Table structure for `student`
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`gender` char(1) NOT NULL,
`class_id` int(11) NOT NULL,
`sname` varchar(32) NOT NULL,
PRIMARY KEY (`sid`),
KEY `fk_class` (`class_id`),
CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `student`
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '男', '1', '理解'), ('2', '女', '1', '钢蛋'), ('3', '男', '1', '张三'), ('4', '男', '1', '张一'), ('5', '女', '1', '张二'), ('6', '男', '1', '张四'), ('7', '女', '2', '铁锤'), ('8', '男', '2', '李三'), ('9', '男', '2', '李一'), ('10', '女', '2', '李二'), ('11', '男', '2', '李四'), ('12', '女', '3', '如花'), ('13', '男', '3', '刘三'), ('14', '男', '3', '刘一'), ('15', '女', '3', '刘二'), ('16', '男', '3', '刘四');
COMMIT;
-- ----------------------------
-- Table structure for `teacher`
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(32) NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `teacher`
-- ----------------------------
BEGIN;
INSERT INTO `teacher` VALUES ('1', '张磊老师'), ('2', '李平老师'), ('3', '刘海燕老师'), ('4', '朱云海老师'), ('5', '李杰老师');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY