#!/usr/bin/env python # -*- coding:utf-8 -*- # @time: 2017/11/23 23:10 # Author: caicai # @File: demon9.py import codecs import MySQLdb def connect_mysql(): db_config = { 'host': '192.168.1.5', 'port': 3306, 'user': 'wxp', 'passwd': '1qazXSW@', 'db': 'python', 'charset': 'utf8' } cnx = MySQLdb.connect(**db_config) return cnx if __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 < 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 ); ''' try: cus = cnx.cursor() cus.execute(sql) result = cus.fetchall() cus.close() cnx.commit() except Exception as e: cnx.rollback() print('error') raise e finally: cnx.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在上表中的子查询中。
import codecs import MySQLdb def connect_mysql(): db_config = { 'host': '192.168.1.5', 'port': 3306, 'user': 'wxp', 'passwd': '1qazXSW@', 'db': 'python', 'charset': 'utf8' } cnx = MySQLdb.connect(**db_config) return cnx if __name__ == '__main__': cnx = connect_mysql() sql = '''select *, (grade+60) as newGrade from Score where Grade <5;''' update = '''update Score set grade = grade + 60 where grade < 5; ''' try: cus_start = cnx.cursor() cus_start.execute(sql) result1 = cus_start.fetchall() print(len(result1)) cus_start.close() cus_update = cnx.cursor() cus_update.execute(update) cus_update.close() cus_end = cnx.cursor() cus_end.execute(sql) result2 = cus_end.fetchall() print(len(result2)) cus_end.close() cnx.commit() except Exception as e: cnx.rollback() print('error') raise e finally: cnx.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分的分数都发生了改变。
索引 索引可以提高查询的速度。 创建Course的CouID的字段为主键 Score的SID字段为主键 Student的StdID字段为主键 Teacher的TID字段为主键, import codecs import MySQLdb def connect_mysql(): db_config = { 'host': '192.168.1.5', 'port': 3306, 'user': 'wxp', 'passwd': '1qazXSW@', 'db': 'python', 'charset': 'utf8' } cnx = MySQLdb.connect(**db_config) return cnx if __name__ == '__main__': cnx = connect_mysql() sql1 = '''alter table Teacher add primary key(TID);''' sql2 = '''alter table Student add primary key(StdID);''' sql3 = '''alter table Score add primary key(SID);''' sql4 = '''alter table Course add primary key(CouID);''' sql5 = '''alter table Score add index idx_StdID_CouID(StdID, CouID);''' # sql6 = '''alter table Score drop index idx_StdID_CouID;''' 删除索引 sql7 = '''explain select * from Score where StdID = 16213;''' try: cus = cnx.cursor() cus.execute(sql1) cus.close() cus = cnx.cursor() cus.execute(sql2) cus.close() cus = cnx.cursor() cus.execute(sql3) cus.close() cus = cnx.cursor() cus.execute(sql4) cus.close() cus = cnx.cursor() cus.execute(sql5) cus.close() cus = cnx.cursor() cus.execute(sql7) result = cus.fetchall() print(result) cus.close() cnx.commit() except Exception as e: cnx.rollback() print('error') raise e finally: cnx.close() 结果: ((1L, u'SIMPLE', u'Score', u'ref', u'idx_StdID_CouID', u'idx_StdID_CouID', u'4', u'const', 4L, None),) 解释: Sql1, sql2, sql3, sql4是添加主键,sql5是增加一个索引,我们也可以在mysql的客户端上执行sq7,得到如下的结果: mysql> explain select * from Score where StdID = 16213; +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+ | 1 | SIMPLE | Score | ref | idx_StdID_CouID | idx_StdID_CouID | 4 | const | 4 | NULL | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------+ 1 row in set (0.00 sec) 这个说明,我们在搜索StdID的时候,是走了idx_StdID_CouID索引的。