多表查询两种方法，小知识点补充，可视化软件Navicat，多表查询练习题，python操作MySQL
目录
多表查询两种方法，小知识点补充，可视化软件Navicat，多表查询练习题，python操作MySQL
今日内容概要
- 多表查询的两种方法
- 小知识点补充说明
- 可视化软件Navicat
- 多表查询练习题
- python操作MySQL
今日内容详细
多表查询的两种方法
方式一：连表操作
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多张表
思路：将拼接之后的表起别名当成一张表再去与其他表拼接 再起别名当一张表 再去与其他表拼接 其次往复即可
'''
方式二：子查询
将一条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 concat('|',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.查询所有的课程的名称以及对应的任课老师姓名
2.查询平均成绩大于80分的同学的姓名和平均成绩
3.查询没有报李平老师课的学生姓名
4.查询没有同时选修物理课程和体育课程的学生姓名
5.查询挂科超过两门(包括两门)的学生姓名和班级
-- 1.查询所有的课程的名称以及对应的任课老师姓名
-- 1.先确定需要用到几张表，需要课程表和老师表
-- 2.预览表中的数据 做到心中有数
-- SELECT * FROM teacher;
-- SELECT * FROM course;
-- 3.确定多表查询的思路 连表 子查询 混合操作
SELECT
teacher.tname,
course.cname
FROM
teacher
INNER JOIN course ON teacher.tid = course.teacher_id;
-- 2.查询平均成绩大于80分的同学的姓名和平均成绩
-- 需要成绩表，学生表
-- 先按照学生id找到学生平均成绩并保留平均成绩大于80的
-- SELECT student_id,AVG(num) FROM score GROUP BY student_id HAVING AVG(num)>80;
SELECT
student.sname,
s1.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 s1 ON student.sid = s1.student_id;
-- 3.查询没有报李平老师课的学生姓名
-- 需要老师表 课程表 学生表
-- 拿到李平老师的编号
-- SELECT tid FROM teacher WHERE tname='李平老师';
-- 拿到李平老师对应的课程编号
-- SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname='李平老师');
-- 拿到学习该课程的学生id
-- SELECT student.sid FROM student INNER JOIN (SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname='李平老师')) AS c1 ON c1.cid=student.class_id;
-- 根据拿到的学生id取反学生姓名
SELECT sname FROM student WHERE sid NOT in (SELECT student.sid FROM student INNER JOIN (SELECT cid FROM course WHERE teacher_id=(SELECT tid FROM teacher WHERE tname='李平老师')) AS c1 ON c1.cid=student.class_id);
-- 4.查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
-- 需要课程表 学生表 分数表
-- SELECT * FROM student;
-- SELECT * FROM course;
-- 找到物理课程和体育课程的编号
-- SELECT cid FROM course WHERE cname in ('物理','体育');
-- 查询所有学习该课程的学生id
-- SELECT student_id FROM score WHERE course_id in (SELECT cid FROM course WHERE cname in ('物理','体育'));
-- 判断其中课程数为1的学生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;
-- 根据拿到的学生id拿到学生名字
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.查询挂科超过两门(包括两门)的学生姓名和班级
-- 需要成绩表 学生表 班级表
-- SELECT * FROM score;
-- SELECT * FROM student;
-- SELECT * FROM class;
-- 查询所有不及格的成绩
-- SELECT * FROM score WHERE num<60;
-- 拿到学生id对应的挂科次数
-- SELECT student_id,count(course_id) FROM score WHERE num<60 GROUP BY student_id;
-- 根据挂科次数判断处学生id
-- SELECT student_id FROM score WHERE num<60 GROUP BY student_id HAVING count(course_id)>=2;
-- 根据学生id拿到学生信息
SELECT
s1.sname,
class.caption
FROM
class
INNER JOIN (
SELECT
*
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) >= 2
)
) AS s1 ON s1.class_id = class.cid;
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='db1',
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') # 基于数据的开头往后移动，0就是开头不动
2.增删改查
autocommit=True # 针对增 删 改 自动确认(直接配置)
conn.commit() # 针对增 删 改 需要二次确认(代码确认)
