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()