多表查询、Navicat、python连接MySQL

多表查询的两种方法

方法1：连表操作
inner join 
# 内连接，连接两张表共有的数据部分
select * from emp inner join dep on emp.dep_id=dep.id;

left join 
# 左连接，展示左表所有的数据，没有对应项则用NULL填充
select * from emp left join dep on emp.dep_id=dep.id

right join
# 右连接，展示右表所有的数据，没有对应项则用NULL填充
select * from emp right join dep on emp.dep_id=dep.id

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

"""
	学会连表操作之后可以连接多张表。
思路：
	将拼接之后的表起别名当成一张表再去与其他表拼接，再另起名当成一张表与其他表拼接，依次往复。	
"""

方法2：子查询
	将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件
例：
	题目：求姓名是sun的员工所在的部门名称
解析：
子查询类似于我们日常生活中解决问题的方式>>>:分步操作
    步骤1：先根据sun获取部门编号
    select dep_id from emp where name='sun'
    步骤2：再根据部门编号获取部门名称
    select name from dep where id=200;
    总结：
    select name from dep where id=(select dep_id from emp where name='sun');
    """
    很多时候多表查询需要结合实际情况判断用哪种，更多时候甚至是相互配合使用
    """

小知识点补充说明

concat与concat_ws
    # concat 用于分组之前的字段拼接操作
    select concat(name,'&',sex)from emp;
    # concat_ws拼接多个字段并且中间的连接符一致
    select concat_ws('|',name,sex,age,dep_id)from emp;

exists
	SQL1 exists SQL2
    SQL1与SQL2是两条SQL语句，SQL2有结果的情况下才会执行SQL1，否则不执行SQL1返回空数据。
    
表相关SQL补充
	alter table 表名 rename 新表名；
    alter table name rename username；
    # 修改表名
    
    alter table 表名 add 字段名 字段类型（数字）约束条件；
    alter table dep add age int；
    # 添加字段
    
    alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
    alter table dep add age int after name；
    # 已有字段的添加字段
    
    alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
    alter table dep add UID int first；
    # 添加字段到最前面
    
  	alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
    alter table dep change name username varchar(64);
    # 修改字段名
 	
    alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
    alter table dep modify username char(32);
    # 指定修改字段名或字段类型
    
 	alter table 表名 drop 字段名;  
    alter table dep drop gender;
    # 删除字段

可视化软件Navicat

Navicat是一款充当数据库客户端简单快捷的操作界面的App，底层本质还是SQL，针对Mysql最出名。

1.网址
http://www.navicat.com.cn
正版收费，可以百度破解版

Navicat主要功能介绍

连接

可以选择数据库软件，如MySQL、SQL server……等

新建查询

可以用SQL语句获取数据

库

逆向数据库到模型

直观看到库里表与表之间的关系

选中表 >>> 右键点击

打开表

可以修改数据值

设计表

可以修改字段

多表查询练习题

题目：
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询平均成绩大于八十分的同学的姓名和平均成绩
3、查询没有报李平老师课的学生姓名
4、查询没有同时选修物理课程和体育课程的学生姓名
5、查询挂科超过两门(包括两门)的学生姓名和班级

"""
编写复杂的SQL不要想着一口气写完
	一定要先明确思路 然后一步步写一步步查一步步补
"""

题目解析

1.查询所有的课程的名称以及对应的任课老师姓名
#首先确定要用到几张表，什么表
课程表、教师表
#预览数据
select * from course;
select * from teacher;
#确定多表查询的思路
select teacher.tname,course.cname from course INNER JOIN course.teacher_id=teacher.tid;

2.查询平均成绩大于八十分的同学的姓名和平均成绩
#首先确定要用到几张表，什么表
成绩表、学生表
#预览数据
select * from course;
select * from student;
#确定多表查询的思路
select student_id,avg(num) as avg_num from score group by student_id having avg_num >80


SELECT
	sname,
	stu_avg.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 stu_avg ON student.sid = stu_avg.student_id
    
3、查询没有报李平老师课的学生姓名
#首先确定要用到几张表，什么表
教师表、学生表、课程表、分数表
#确定思路 
#思路1:正向筛选 
#思路2:筛选所有报了李平老师课程的学生id 然后取反即可
先获取李平老师教授的课程id
select tid from teacher where tname = '李平老师';
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
	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.查询没有同时选修物理课程和体育课程的学生姓名
# 先确定需要的表  
	学生表、分数表、课程表
# 根据给出的条件确定起手的表
根据物理和体育筛选课程id
select cid from course where cname in ('物理','体育');

根据课程id筛选出所有跟物理、体育相关的学生id
select * from score where course_id in (select cid from course where cname in ('物理','体育'))

统计每个学生报了的课程数，筛选出等于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;

子查询获取学生姓名即可
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 
	)
    
5、查询挂科超过两门(包括两门)的学生姓名和班级
# 先确定涉及到的表	
	分数表 学生表 班级表
# 根据条件确定以分数表作为起手条件
先筛选掉大于60的数据
select * from score where num < 60;

统计每个学生挂科的次数
select student_id,count(course_id) from score where num < 60 group by student_id;

筛选次数大于等于2的数据
select student_id from score where num < 60 group by student_id having count(course_id) >= 2;

连接班级表与学生表 然后基于学生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='123',
    db='db4_03',
    charset='utf8mb4'
)

# 2.产生游标对象
# cursor = conn.cursor()  
# 括号内不填写额外参数 数据是元组 指定性不强  [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]

# 3.编写SQL语句
# sql = 'select * from teacher;'
sql = 'select * from score;'

# 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 @ 2022-11-28 21:21 知了了了了阅读(365) 评论(0) 收藏举报

刷新页面返回顶部

zhiliaowang

多表查询、Navicat、python连接MySQL

多表查询、Navicat、python连接MySQL

多表查询的两种方法

小知识点补充说明

可视化软件Navicat

Navicat主要功能介绍

连接

新建查询

库

多表查询练习题

题目解析

python操作MySQL

pymysql补充说明

公告