MySQL--python关联MySQL、练习题

1、python关联MySQL

pymysql：

　　安装：pip3 install pymysql

　　

1.0：连接到数据库中

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='user',
    autocommit=True,
    charset='utf8'  # 编码的时候千万不要加上- 如果写成了utf-8-->就会直接报错 
)
cursor = conn.cursor(pymysql.cursors.DictCursor)  
# 产生一个游标对象 以字典的形式返回查询出来的数据  
# 键是表的字段  
# 值是表的字段对应的值
sql = "select * from teacher"

cursor.execute(sql)  # 执行传入的sql语句

print(cursor.fetchone())  # 只获取一条数据
cursor.scroll(2, 'absolute')  # 控制光标移动  absolute相对于起始的位置往后移动几位
cursor.scroll(1, 'relative')  # relative相对于当前位置  往后移动几位
print(cursor.fetchall())  # 获取所有的数据  返回的结果只是一个列表

2.0： sql注入的问题

import pymysql

conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='user',
    autocommit=True,  # 这个参数配置后，增删改查都不许呀在手动进行添加conn.commit了
    charset='utf8'  # 编码的时候千万不要加上- 如果写成了utf-8-->就会直接报错
)
cursor = conn.cursor(pymysql.cursors.DictCursor)

user_name = input('user_name:>>').strip()
password = input('password:>>').strip()
sql = "select * from teacher where name=%s and password=%s"

res = cursor.execute(sql, (user_name, password))  # 能够帮你自动过滤特殊符号 避免sql注入的问题
# execute能够自动识别sql语句中的%s  然后帮你完成替换
if res:
    print(cursor.fetchall())
else:
    print("user-name or password error!!!")
    
    
"""
sql注入就是利用注释等具有特殊意义的符号  来完成一些骚操作

后续写的sql语句 不要手动拼接关键性的数据，而是可以选择让execute去完成
"""

增删改查：

# 增
sql = "insert into user(username,password) values(%s,%s)"
rows = cursor.excute(sql,('jason','123'))

# 修改
sql = "update user set username='jasonDSB' where id=1"
rows = cursor.excute(sql)

"""
增和改单单执行excute并不会真正影响到数据，需要再执行conn.commit()才可以完成真正的增改
"""

# 一次插入多行记录
res = cursor,excutemany(sql,[(),(),()]

练习题：　　

　　查询所有的课程的名称以及对应的任课老师姓名

SELECT
    course.cname,
    teacher.tname
FROM
    course
INNER JOIN teacher ON course.teacher_id = teacher.tid;

　　查询平均成绩大于八十分的同学的姓名和平均成绩

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;

　　查询没有报李平老师课的学生姓名

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 = '李平老师'
            )
    );

　　查询没有同时选修物理课程和体育课程的学生姓名

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

　　查询挂科超过两门(包括两门)的学生姓名和班级

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