删除数据背景:有些老师不好好上次,导致课程的及格率太低,最后名最差的5名老师将会被开除。点击(此处)折叠或打开#!/usr/bin/env python# -*- coding:utf-8 -*-# Author :Alvin.xie# @Time :2017-11-23 16:37# @file :delectdata.pyimport MySQLdbdef connect_mysql():    db_config = {        'host': '10.89.1.10',        'port': 3306,        'user': 'demo',        'passwd': '123qaz',        'db': 'python_test',        'charset': 'utf8'    }    cnx = MySQLdb.connect(**db_config)    return cnxif __name__ == '__main__':    cnx = connect_mysql()    sql = '''delete from Teacher where TID in(    select TID from (select Course.CouID, Course.TID, Teacher.TName, count(Teacher.TID) as count_teacher from Course    left join Score on Score.Grade    left join Teacher on Course.TID = Teacher.TID    group by Course.TID    order by count_teacher desc    limit 5) as test )    '''try:    cus = cnx.cursor()    cus.execute(sql)    result = cus.fetchall()    cus.close()    cnx.commit()except Exception as e:    cnx.rollback()    print('error')    raise efinally:    cnx.close()结果:程序正常执行,没有报错解释:1.      先查询出Course表中的Course.TID和Course.TID2.      left join 是关联Score表,查出Score.Grade> 59,并且,课程ID和课程表的CouID要对应上3.      left join Teacher 是关联老师表,课程中的了老师ID和老师表中的老师ID对应上4.      select中加上老师的名字Teacher.Tname和count(Teacher.TID)5.      group by Course.TID,在根据老师的的TID进行分组6.      oder by 最后对count_teacher进行排序,取前5行,7.      在通过套用一个select子查询,把所有的TID搂出来8.      然后delete from Teacher 最后删除TID在上表中的子查询中。
09-17 18:27