MySQL 数据库 之 多表查询 & navicat & pymysql & 事务
内容概要
- 多表查询
- 子查询
- 联表查询
- inner join
- left join
- right join
- union
- navicat 可视化软件
- 多表查询练习题
- python 操作 MySQL 模块 （pymysql）
- 事务
- 特性：ACID
- start transcation
- scrollback
- commit
内容详细
多表查询
在一些复杂的查询需求中，MySQL 的单表查询远远不能满足业务要求
所以提供了两种多表查询的思路
数据准备：
#建表
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),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
注意：涉及到多表查询的时候 字段名称容易冲突 需要使用表名点字段的方式区分
1、子查询
将SQL语句查询的结果括号括起来当做另外一条SQL语句的条件
采取分步骤操作的方式，是我们日常生活中解决问题的方式
# 查询elijah所在的部门名称
涉及到SQL查询题目 一定要先明确到底需要几张表
1.先查询jason所在的部门编号
select dep_id from emp where name='elijah';
2.根据部门编号查询部门名称
select name from dep where id=(select dep_id from emp where name='elijah');
"""一条SQL语句的查询结果既可以看成是一张表也可以看成是查询条件"""
2、连表操作
先将需要使用到的表拼接成一张大表 之后基于单表查询完成
连接主要有以下四种情况：
- INNER JOIN（内连接）：如果表中有至少一个匹配，则返回行 【在语法中可以省略INNER关键字】
- LEFT JOIN（左连接）：从左表返回所有的行，如果右表中没有匹配，对应的列返回Null
- RIGHT JOIN（右连接）：从右表返回所有的行 ，如果左表中没有匹配，对应的列返回Null
- FULL JOIN（全连接）：只要其中一个表中存在匹配，则返回行（即结合左连接和右连接的结果）
# 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;
"""
疑问:上述操作一次只能连接两张表 如何做到多张表?
将两张表的拼接结果当成一张表与跟另外一张表做拼接
依次往复 即可拼接多张表
"""
navicat可视化软件
内部封装了很多SQL的操作 用户只需要鼠标点点点 自动构建SQL语句并执行
navicat可以看成是很多数据库软件的客户端
下载后破解即可：https://shimo.im/docs/g9qK9rpcTGWX6Vgh
使用：
多表查询练习题
问题：
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名（运用计数函数count）
9、查询挂科超过两门(包括两门)的学生姓名和班级
注意：编写SQL不要想着一次性写完 可以边写边看
1、查询所有的课程的名称以及对应的任课老师姓名
-- SELECT
-- teacher.tname,
-- course.cname
-- FROM
-- teacher
-- INNER JOIN course ON teacher.tid = course.teacher_id;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要使用到的表
# 2.在思考多表查询的方式
# 第一步先查询成绩表中 平均成绩大于80的学生编号
# 1.1 按照学生id分组并获取平均成绩
-- select student_id,avg(num) from score group by student_id;
# 1.2 筛选出平均成绩大于80的数据 (针对聚合函数的字段结果 最好起别名防止冲突)
-- select student_id,avg(num) as avg_num from score group by student_id having avg(num) > 80;
# 1.3 将上述SQL的结果与student表拼接
-- 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.先查询李平老师教授的课程编号
-- select course.cid from course where teacher_id =
-- (select tid from teacher where tname ='李平老师');
# 2.根据课程id号筛选出所有报了的学生id号
-- select distinct score.student_id from score where course_id in (select course.cid from course where teacher_id =
-- (select tid from teacher where tname ='李平老师'));
# 3.去学生表中根据id号取反筛选学生姓名
-- SELECT
-- student.sname
-- FROM
-- student
-- WHERE
-- sid NOT IN (
-- SELECT DISTINCT
-- score.student_id
-- FROM
-- score
-- WHERE
-- course_id IN ( SELECT course.cid FROM course WHERE teacher_id = ( SELECT tid FROM teacher WHERE tname = '李平老师' ) )
-- );
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(只要了报了一门的 两门和一门没报的都不要)
# 1.先获取两门课程的id号
-- select course.cid from course where cname in ('物理','体育');
# 2.再去分数表中先筛选出所有报了物理和体育的学生id(两门 一门)
-- select * from score where course_id in (select course.cid from course where cname in ('物理','体育'));
# 3.如何筛选出只报了一门的学生id 按照学生id分组 然后计数 并过滤出计数结果为1的数据
-- select score.student_id from score where course_id in (select course.cid from course where cname in ('物理','体育'))
-- group by score.student_id
-- having count(score.course_id) = 1
-- ;
# 4.根据学生id号去student表中筛选学生姓名
-- SELECT
-- student.sname
-- FROM
-- student
-- WHERE
-- sid IN (
-- SELECT
-- score.student_id
-- FROM
-- score
-- WHERE
-- course_id IN ( SELECT course.cid FROM course WHERE cname IN ( '物理', '体育' ) )
-- GROUP BY
-- score.student_id
-- HAVING
-- count( score.course_id ) = 1
-- );
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先筛选出小于60分的数据
-- select * from score where num < 60;
# 2.按照学生id分组 然后统计挂科数量
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 3.筛选出挂科超过两门的学生id
-- select student_id from score where num < 60 group by student_id
-- having count(course_id) >=2;
# 4.先将上述结果放在一边 去连接student和class表
SELECT
student.sname,
class.caption
FROM
class
INNER JOIN student ON class.cid = student.class_id
WHERE
student.sid IN ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id HAVING count( course_id ) >= 2 );
**更多练习: **
https://www.cnblogs.com/Dominic-Ji/p/10875493.html
python 操作 MySQL 模块 （pymysql）
MuSQL 数据库是可以充当多种客户端的服务端，python是其中一种客户端
python想要操作MySQL数据库需要用到一个模块: pymysql
实例：
import pymysql
# 连接MySQL服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='db8_2',
charset='utf8'
)
# 产生一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 编写SQL语句
sql = 'select * from teacher'
affect_rows = cursor.execute(sql)
print(affect_rows) # 执行该语句会影响到的行数
# 获取执行结果
print(cursor.fetchall()) # 获取所有得到的数据
SQL注入问题
早期MySQL的漏洞
import pymysql
# 连接MySQL服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='db8_3',
charset='utf8',
autocommit=True # 针对增 改 删自动二次确认
)
# 产生一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 编写SQL语句
username = input('username>>>:').strip()
password = input('password>>>:').strip()
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql,(username,password))
data = cursor.fetchall()
if data:
print(data)
print('登录成功')
else:
print('用户名或密码错误')
# 1.只需要用户名也可以登录
# 2.不需要用户名和密码也可以登录
"""
SQL注入的原因 是由于特殊符号的组合会产生特殊的效果
实际生活中 尤其是在注册用户名的时候 会非常明显的提示你很多特殊符号不能用
原因也是一样的
结论:设计到敏感数据部分 不要自己拼接 交给现成的方法拼接即可
"""
# sql = 'insert into userinfo(name,pwd) values("jason","123"),("kevin","321")'
# res = cursor.execute(sql)
# print(res)
"""
在使用代码进行数据操作的时候 不同操作的级别是不一样的
针对查无所谓
针对增 改 删都需要二次确认
conn.commit()
"""
事务
特性：ACID
A:原子性
C:一致性
I:隔离性
D:持久性
- 原子性（atomicity）
一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。 - 一致性（consistency）
事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。 - 隔离性（isolation）
一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。 - 持久性（durability）
持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响
事务的作用： 可以保证数据操作的安全性
相关操作：
start transcation; # 开启事务
rollback # 回滚到操作之前的状态
commit # 确认事务操作 之后不能回滚
数据准备：
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);
具体操作：
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
rollback;
else:
commit;
练习题目
1、查询所有的课程的名称以及对应的任课老师姓名
2、查询学生表中男女生各有多少人
3、查询物理成绩等于100的学生的姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
5、查询所有学生的学号，姓名，选课数，总成绩
6、 查询姓李老师的个数
7、 查询没有报李平老师课的学生姓名
8、 查询物理课程比生物课程高的学生的学号
9、 查询没有同时选修物理课程和体育课程的学生姓名
10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名
12、查询李平老师教的课程的所有成绩记录
13、查询全部学生都选修了的课程号和课程名
14、查询每门课程被选修的次数
15、查询之选修了一门课程的学生姓名和学号
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
17、查询平均成绩大于85的学生姓名和平均成绩
18、查询生物成绩不及格的学生姓名和对应生物分数
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
20、查询每门课程成绩最好的前两名学生姓名
21、查询不同课程但成绩相同的学号，课程号，成绩
22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
24、任课最多的老师中学生单科成绩最高的学生姓名
参考答案：
#1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
#2、查询学生表中男女生各有多少人
SELECT
gender 性别,
count(1) 人数
FROM
student
GROUP BY
gender;
#3、查询物理成绩等于100的学生的姓名
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
INNER JOIN course ON score.course_id = course.cid
WHERE
course.cname = '物理'
AND score.num = 100
);
#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;
#5、查询所有学生的学号，姓名，选课数，总成绩(注意：对于那些没有选修任何课程的学生也算在内)
SELECT
student.sid,
student.sname,
t1.course_num,
t1.total_num
FROM
student
LEFT JOIN (
SELECT
student_id,
COUNT(course_id) course_num,
sum(num) total_num
FROM
score
GROUP BY
student_id
) AS t1 ON student.sid = t1.student_id;
#6、 查询姓李老师的个数
SELECT
count(tid)
FROM
teacher
WHERE
tname LIKE '李%';
#7、 查询没有报李平老师课的学生姓名(找出报名李平老师课程的学生，然后取反就可以)
SELECT
student.sname
FROM
student
WHERE
sid NOT IN (
SELECT DISTINCT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '李平老师'
)
);
#8、 查询物理课程比生物课程高的学生的学号(分别得到物理成绩表与生物成绩表，然后连表即可)
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、 查询没有同时选修物理课程和体育课程的学生姓名(没有同时选修指的是选修了一门的，思路是得到物理+体育课程的学生信息表，然后基于学生分组，统计count(课程)=1)
SELECT
student.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) = 1
);
#10、查询挂科超过两门(包括两门)的学生姓名和班级(求出<60的表，然后对学生进行分组，统计课程数目>=2)
SELECT
student.sname,
class.caption
FROM
student
INNER JOIN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) >= 2
) AS t1
INNER JOIN class ON student.sid = t1.student_id
AND student.class_id = class.cid;
#11、查询选修了所有课程的学生姓名(先从course表统计课程的总数，然后基于score表按照student_id分组，统计课程数据等于课程总数即可)
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
COUNT(course_id) = (SELECT count(cid) FROM course)
);
#12、查询李平老师教的课程的所有成绩记录
SELECT
*
FROM
score
WHERE
course_id IN (
SELECT
cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '李平老师'
);
#13、查询全部学生都选修了的课程号和课程名(取所有学生数，然后基于score表的课程分组，找出count(student_id)等于学生数即可)
SELECT
cid,
cname
FROM
course
WHERE
cid IN (
SELECT
course_id
FROM
score
GROUP BY
course_id
HAVING
COUNT(student_id) = (
SELECT
COUNT(sid)
FROM
student
)
);
#14、查询每门课程被选修的次数
SELECT
course_id,
COUNT(student_id)
FROM
score
GROUP BY
course_id;
#15、查询之选修了一门课程的学生姓名和学号
SELECT
sid,
sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
GROUP BY
student_id
HAVING
COUNT(course_id) = 1
);
#16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
SELECT DISTINCT
num
FROM
score
ORDER BY
num DESC;
#17、查询平均成绩大于85的学生姓名和平均成绩
SELECT
sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) avg_num
FROM
score
GROUP BY
student_id
HAVING
AVG(num) > 85
) t1 ON student.sid = t1.student_id;
#18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT
sname 姓名,
num 生物成绩
FROM
score
LEFT JOIN course ON score.course_id = course.cid
LEFT JOIN student ON score.student_id = student.sid
WHERE
course.cname = '生物'
AND score.num < 60;
#19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT
sname
FROM
student
WHERE
sid = (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.cid
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid
WHERE
teacher.tname = '李平老师'
)
GROUP BY
student_id
ORDER BY
AVG(num) DESC
LIMIT 1
);
#20、查询每门课程成绩最好的前两名学生姓名
#查看每门课程按照分数排序的信息，为下列查找正确与否提供依据
SELECT
*
FROM
score
ORDER BY
course_id,
num DESC;
#表1：求出每门课程的课程course_id,与最高分数first_num
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id;
#表2：去掉最高分，再按照课程分组，取得的最高分，就是第二高的分数second_num
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id;
#将表1和表2联合到一起，得到一张表t3，包含课程course_id与该们课程的first_num与second_num
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id;
#查询前两名的学生（有可能出现并列第一或者并列第二的情况）
SELECT
score.student_id,
t3.course_id,
t3.first_num,
t3.second_num
FROM
score
INNER JOIN (
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
score.num >= t3.second_num
AND score.num <= t3.first_num;
#排序后可以看的明显点
SELECT
score.student_id,
t3.course_id,
t3.first_num,
t3.second_num
FROM
score
INNER JOIN (
SELECT
t1.course_id,
t1.first_num,
t2.second_num
FROM
(
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t1
INNER JOIN (
SELECT
score.course_id,
max(num) second_num
FROM
score
INNER JOIN (
SELECT
course_id,
max(num) first_num
FROM
score
GROUP BY
course_id
) AS t ON score.course_id = t.course_id
WHERE
score.num < t.first_num
GROUP BY
course_id
) AS t2 ON t1.course_id = t2.course_id
) AS t3 ON score.course_id = t3.course_id
WHERE
score.num >= t3.second_num
AND score.num <= t3.first_num
ORDER BY
course_id;
#可以用以下命令验证上述查询的正确性
SELECT
*
FROM
score
ORDER BY
course_id,
num DESC;
-- 21、查询不同课程但成绩相同的学号，课程号，成绩
-- 22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
-- 23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
-- 24、任课最多的老师中学生单科成绩最高的学生姓名
参考答案
