删除数据背景:有些老师不好好上次,导致课程的及格率太低,最后名最差的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