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

多表查询的两种方法

方式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_ws('|',name,sex,age,dep_id)from emp;

2.exists

  sql1 exists sql2

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

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.浏览器搜索Navicat直接下载

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

2.破解方式

   先试用再破解、直接下载破解版(老版本)、修改试用日期

3.常用操作

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

        创建库、表、记录、外键

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

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

        SQL语句注释语法

        --、#、\**\

        运行、转储SQL文件

多表查询练习题

"""

编写复杂的SQL不要想着一口气写完

   一定要先明确思路 然后一步步写 一步步查 一步步补

"""

1、查询所有的课程的名称以及对应的任课老师姓名

4、查询平均成绩大于八十分的同学的姓名和平均 成绩

7、查询没有报李平老师课得学生姓名

8、查询没有同时选项物理课程和体育课程的学生姓名

9、查询挂科超过两门(包括两门)的学生姓名和班级

--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;

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

pymysql模块

    pip3  install  pymysql

import pymysql

# 1.连接MySQL服务端

conn = pymysql.connect(

    host='127.0.0.1',

    port=3306,

    user='root',  # 数据库用户名

    password='222',  # 数据库密码

    db='db04_02',   # 数据库名

    charset='utf8mb4'  # 编码

    autocommit=True # 针对增删改 自动确认

)

# 2.产生游标对象

# cursor = conn.cursor()   # 括号内不填写额外参数  数据是元组 指定行性不强 [(),()]

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)   # [{},{}]

# 3.编写SQL语句

# sql = 'select * from teacher;'

sql = 'select * from score;'

# 4.发送SQL语句

# cursor.execute(sql)  # execute 也有返回值 接收的是SQL语句影响的行数

affect_rows = cursor.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()  # 针对增 删 改 需要二次确认(代码确认)

巴特西

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

多表查询的两种方法

小知识点补充说明

可视化软件Navicat

多表查询练习题

python操作MySQL

pymysql补充说明

最新文章

热门文章