多表查询两种方法、可视化软件navicat、python操作mysql、pymysql模块
多表查询的思路
表数据准备
表数据准备
create table dep(
id int primary key auto_increment,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
select * from emp,dep; 会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据
多表查询的两种方法
方式一：连表操作
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 right join dep on emo.dep_id=dep.id;
以左右表为基准 展示所有的数据 各自没有的全部NULL填充
学会了连表操作之后也就可以连接N多张表
思路：将拼接之后的表起别名当成一张表再去与其他表拼接 在起别名 在于其他表拼接 其次往复即可
方式二：子查询
将一条SQL语句括起来当成另外一条SQL语句的查询条件
题目：求姓名是jason的员工部门名称
子查询类似我们日常生活中解决问题的方式：分步操作
步骤一：先根据jason获取部门名称
select dep_id from emp where name='jason';
步骤二：再根据部门编号获取部门名称
# select name from dep where id=200;
总结：select name from dep where id=(select dep_id from emp where name='jason');
'''
很多时候多表查询需要结合实际情况判断用哪种 更多时候甚至是相互配合使用
'''
小知识点补充数说明
-
concat与concat_ws
concat用于分组之前的字段拼接操作 select concat(name,'$',sex) from emp; concat_ws拼接多个字段并且中间的连接符一致 select concat_ws('|',name,sex,age,dep_id) from emp;
select concat(name,'$',sex) from emp;
select concat_ws('|',name,sex,age,dep_id) from emp;
-
exists
sql1 exists sql2 sql2有结果的情况下才会执行qsl1 否则不执行sql1 返回空数据
- 表相关SQL补充
# 修改表名
alter table 表名 rename 新表名;
# 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件;
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
# 修改字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
# 修改字段类型
lter table 表名 modify 字段名 新字段类型(数字) 约束条件;
# 删除字段
alter table 表名 drop 字段名;
可视化软件Navicat
第三方开发的用来充当数据库客户端的简单快捷的操作界面
无论第三方软件有多么的花里胡哨 底层的本质还是SQL
能够操作数据库的第三方可视化软件有很多 其中针对MySQL最出名的就是Navicat
1.浏览器搜索Navicat直接下载
版本很多、能够充当的数据库客户端也很多
2.破解方式
先试用在破解、直接下载破解版(老版本)、修改试用日期
3.常用操作
有些功能可能需要自己修改SQL预览
创建库、表、记录、外键
逆向数据库到模型、模型创建
新建查询可以编写SQL语句并自带提示功能
SQL语句注释语法
--、#、\**\
运行、转储SQL文件
安装教程
正版教程
官网地址：http://www.navicat.com.cn/
步骤一：
进入官网点击上方的产品
步骤二：
如果想要先使用可以点击免费使用，如果想要直接购买的可以点击购买，试用满十四天后就需要收费了。
步骤三：
参考破解版的安装教程
破解版教程
文件获取和简单教程可以参考这两个网站。
https://www.jb51.net/database/803226.html
https://www.jb51.net/softjc/823874.html
步骤一：
获取文件，下载后解压。然后双击运行navicat161_premium_cs_x64.exe
步骤二：
点击下一步，并同意规定，然后选择安装路径（安装路径后面要用到）
步骤三：
然后一路下一步一路到安装成功。
步骤四：
解压NavicatCracker.rar，然后打开解压得到的文件夹中的NavicatCracker v16.0.7.0 .exe
步骤五：
出现窗口后点击patch边上的两个点，把目录选择到navicat的安装目录
步骤六：
接着点击patch，如果成功了会有一个如下的弹窗，然后点击是
步骤七：
接着断网，然后点击generate获得激活码
步骤八：
然后打开navicat软件，点击注册，在激活码处输入获得的激活码
步骤九：
点击激活后会出现一个弹窗，点击手动激活，这时候会再出现一个弹窗
步骤十：
把新出现的弹窗中的请求码复制，然后粘贴到request code的窗口中
步骤十一：
点击Generate Activation Code按钮，这时候会在Activation Code中生成一串字符，把他复制然后粘贴到请求码的窗口下方
步骤十二：
点击激活就可以永久激活，但是不支持更新。
数据库常用操作
1.连接数据库
步骤一：
打开软件后点击左上角的连接然后点击MySQL
步骤二：
输入用户名和密码，然后点击测试连接，试试看能不能连上，如果可以就点击确定创建连接
步骤三：
接下去会出现如下界面，这就表示连接数据库成功了
2.创建库
前置条件：连接数据库
步骤一：
选中连接，右键点击新建数据库，这时候会出现一个弹窗
步骤二：
填写数据库名称和字符集（就是字符编码类型，建议选utf8或utf8mb4），排序规则不要动，让他空着
步骤三：
点击确定就可以在左边看到新创的数据库了
3.创建表
步骤一：
双击一个数据库
步骤二：
点击表，然后右键点击新建表
步骤三：
这时候右边窗口会出现创建表的窗口，我们在内部可以更简单的创建表，创建表的时候需要设置上主键（使用tab可以换到下一行）
步骤四：
设置好了之后点击保存就可以创建出这个表了，我们可以在左边查看到表的信
4.往表中添加记录
在左边双击表，就能开始添加表的记录
5.设置外键
需要在创建表的时候就进行设置
ps：还有很多实用的功能，可以自行探索，比如编写sql代码会出现提示可以用Tab补全，类似pycharm。
运行、转储SQL文件
在左侧选中一个数据库，然后右键点击运行/转储SQL文件就可以导入或是到处数据库文件
多表查询练习题
编写复杂的SQL不要想着一口气写完 一定要先明确思路 然后一步步查一步步补
- 查询所有与的课程的名称以及对应的任课老师姓名
- 查询平均成绩大于八十分得同学的姓名和平均成绩、
- 查询没有报李平老师课的学生姓名
- 查询没有同时选修物理课程和体育课程的学生姓名
- 查询挂科超过两门(包括两门)的学生姓名和班级
-- 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='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:注意三者都有类似于文件光标移动的特性
2.增删改查
autocommit=True # 针对增删 该自动确认(直接配置)
conn.commit() # 针对 增 删 改 需要二次确认
Non-grouping报错
针对error 1046(42000) Non-grouping field 'avg_num' is used in HAVING clause报错
将only_full_group_by从配置文件中去掉
练习
查询所有的课程的名称以及对应的任课老师姓名
查询学生表中男女生各有多少人
查询物理成绩等于100的学生的姓名
查询平均成绩大于八十分的同学的姓名和平均成绩
查询所有学生的学号，姓名，选课数，总成绩
查询姓李老师的个数
查询没有报李平老师课的学生姓名
查询物理课程比生物课程高的学生的学号
查询没有同时选修物理课程和体育课程的学生姓名
查询挂科超过两门(包括两门)的学生姓名和班级
查询选修了所有课程的学生姓名
查询李平老师教的课程的所有成绩记录
查询全部学生都选修了的课程号和课程名
查询每门课程被选修的次数
查询之选修了一门课程的学生姓名和学号
查询所有学生考出的成绩并按从高到低排序（成绩去重）
查询平均成绩大于85的学生姓名和平均成绩
查询生物成绩不及格的学生姓名和对应生物分数
查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
查询每门课程成绩最好的前两名学生姓名
查询不同课程但成绩相同的学号，课程号，成绩
查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
任课最多的老师中学生单科成绩最高的学生姓名
