删除数据
条件:
有些老师不好好上次,导致课程的及格率太低,最后名最差的5名老师将会被开除。
#!/usr/bin/python
# -*- coding:utf-8 -*-
# @Time : 2017/11/23 0023 09:49
# @Author : liaochao
# @File : del_demo.py
import codecs
import MySQLdb
from DBUtils.PooledDB import PooledDB
db_config = {
'host': '192.168.1.204',
'port': 3306,
'user': 'root',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
pool = PooledDB(MySQLdb,10,**db_config )
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 < 60 and course.couid = score.couid
left join teacher on course.tid = teacher.tid
group by course.tid
order by count_teacher desc
limit 5) as test )
'''
if __name__ == '__main__':
conn = pool.connection()
cur = conn.cursor()
try:
cur.execute(sql)
result = cur.fetchall()
cur.close()
conn.commit()
except Exception as e:
cur.rollback()
print('error')
raise e
finally:
cur.close()
解释:
1. 先查询出Course表中的Course.TID和Course.TID
2. 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在上表中的子查询中。
更改数据
我们把分数低于5分的成绩所有都加60分
#!/usr/bin/python
# -*- coding:utf-8 -*-
# @Time : 2017/11/23 0023 09:49
# @Author : liaochao
# @File : del_demo.py
import codecs
import MySQLdb
from DBUtils.PooledDB import PooledDB
db_config = {
'host': '192.168.1.204',
'port': 3306,
'user': 'root',
'passwd': '123456',
'db': 'python',
'charset': 'utf8'
}
pool = PooledDB(MySQLdb,10,**db_config )
#将分数少于5分的加60分
sql = '''
select * from score where grade<5;
'''
sql_update='''
update score set grade = grade+60 where grade < 5;
'''
if __name__ == '__main__':
try:
conn = pool.connection()
cur = conn.cursor()
cur.execute(sql)
result = cur.fetchall()
print (len(result))
cur.close()
cur_update = conn.cursor()
cur_update.execute(sql_update)
cur_update.close()
cur_end = conn.cursor()
cur_end.execute(sql)
cur_end.close()
conn.commit()
except Exception as e:
conn.rollback()
print('error')
raise e
finally:
conn.close()
结果:
321
0
解释:
1. 刚开始,我们可以查到分数小于5分的总个数有321个
2. select *, (grade+60) as newGrade from Score where Grade <5;这个sql是把所有的成绩小于5的都列出来,然后最后加一列分数加60分的结果。
3. update Score set grade = grade + 60 where grade < 5;是把分数小于5的所有成绩都加60分
4. 最后在检查分数小于5的个数为0,说明所有低于5分的分数都发生了改变。