MySQL多表查询&python操作MySQL
1. 多表查询思路
# 涉及到SQL查询题目，一定要先明确到底需要几张表。
"""
多表查询的思路
1. 子查询
将SQL语句的查询结果括号括起来，当作另外一条SQL。
就是日常生活中解决问题的方式，分布操作。
2. 连表操作（重要）
先将需要使用的表拼成一张大表，之后基于单表查询完成。
inner join 内连接
left join 左外连接
right join 右连接
union 全连接
"""
# 涉及到多表查询的时候，字段名容易冲突，需要使用表名点字段的方法避免冲突。
"inner join"
# 只拼接两张表有关系的部分--两张表共有的！！
select * from emp inner join dep on dep.id=emp.dep_id;
"left join"
# 以左表为基准，展示所有的内容，没有的NULL填充。
select * from emp left join dep on dep.id=emp.dep_id;
"right join"
# 以右表为基准，没有的NULL填充。
select * from emp right join dep on dep.id=emp.dep_id;
"union"
# 左右表所有的数据都在，没有的NULL填充
select * from emp left join dep on dep.id=emp.dep_id
union
select * from emp right join dep on dep.id=emp.dep_id;
"""
疑问：上述操作一次只能连接两张表，如何做到多张表？
将两张表的拼接结果当成一张表与另外一张表做拼接
以此往复，连接多张表。
"""
2. navicat 可视化软件
内部封装了很多SQL操作，用户用鼠标即可构建SQL语句并执行。
3. 多表查询练习
2. 查询平均成绩大于80分的同学的姓名和平均成绩
SELECT
student.sname,
avg( num )
FROM
score
LEFT JOIN student ON score.student_id = student.sid
GROUP BY
student_id
HAVING
avg( num )> 80;
3. 查询没有报李平老师课的学生姓名
SELECT
student.sid
FROM
student
WHERE
student.sid NOT IN (
SELECT
student.sid
FROM
teacher
LEFT JOIN course ON teacher.tid = course.teacher_id
LEFT JOIN score ON course.cid = score.course_id
LEFT JOIN student ON score.student_id = student.sid
WHERE
teacher.tname = "李平老师"
);
4. 查询没有同时选修物理课程和体育课程的学生姓名
SELECT
student.sname
FROM
student
WHERE
student.sname NOT IN (
SELECT
student.sname
FROM
student
LEFT JOIN score ON student.sid = score.student_id
LEFT JOIN course ON score.course_id = course.cid
WHERE
course.cname IN ( "物理", "体育" )
GROUP BY
student_id
HAVING
count( student_id )= 1
);
5. 查询挂科超过两门(包括两名)的学生姓名和班级
SELECT
student.sname,
class.caption
FROM
score
LEFT JOIN student ON score.student_id = student.sid
LEFT JOIN class ON class.cid = student.class_id
WHERE
num < 60
GROUP BY
student_id
HAVING
count( course_id )>= 2;
4. python操作mysql
4.1 基本使用
pip3 install pymysql
# 1. 连接MySQL服务端
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="123",
db="dbtest",
charset="utf8"
)
# 2. 产生一个游标对象
cursor = conn.cursor(cursor=pymysql.cursor.DictCursor)
# 3. 编写SQL语句
sql = "select * from teacher"
affect_rows = cursor.execute(sql)
print(affect_rows)
# 4. 获取执行结果
print(cursor.fetchall()) # 获取所有
print(cursor.fetchall()) # 类似于文件光标，全获取完了，没了
print(cursor.fetchone()) # 获取单个
print(cursor.fetchmany(3)) # 想获取几个
cursor.scroll(1,'relative') # 也可以控制光标的移动
cursor.scroll(1,'absolute')
4.2 SQL注入
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="123",
db="数据库名",
charset="utf8",
autocommit=True,
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql = 'insert into userinfo(name,pwd) values("joshua","123")'
cursor.execute()
"""
在使用代码进行数据操作的时候，不同操作的级别是不一样的，
针对查无所谓
针对 增 改 删 都需要二次确认
commit
"""
conn.commit() # 可以设置自动提交
"""
SQL注入的原因 是由于特殊符号的组合会产生特殊的效果
实际生活中尤其是在注册用户名的时候，会非常明显的提示你很多特殊符号不能用
原因也是一样的
"""
# 针对SQL注入可以这样做
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql,(username,password))
5. MySQL其他理论补充
"事务(重要)"
ACID
A:原子性
C:一致性
I:隔离性
D:持久性
# 原子性（Atomicity）
原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。
# 一致性（Consistency）
执行的前后数据的完整性保持一致。
# 隔离性（Isolation）
一个事务执行的过程中，不应该受到其他事务的干扰。
# 持久性（Durability）
事务一旦结束，数据就持久到数据库
"MySQL客户端开启事务"
begin; # 开启事务 start transaction;
执行操作
执行操作
commit; # 提交事务 rollback;(表示要回滚，回滚到开启事务之后)
python代码：
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="123",
db="数据库名",
charset="utf8",
autocommit=True,
)
# 开启事务
conn.begin()
try:
cursor.execute("update users set amount=1 where id=1")
int('123')
cursor.execute("update tran set amount=2 where id=2")
except Exception as e:
# 回滚
print("回滚")
conn.rollback()
else:
# 提交
print("提交")
conn.commit()
cursor.close()
conn.close()
