29. Python 删除 更改 索引数据

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

1.删除数据
需求:课程的及格率太低,最后名最差的5名老师将会被开除

import codecs
import MySQLdb

def connect_mysql():
    db_config = {
        'host': '192.168.48.128',
        'port': 3306,
        'user': 'xiang',
        'passwd': '123456',
        '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在上表中的子查询中。

总结:理解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 )

2.更改数据
需求:把分数低于5分的成绩所有都加60分。

import codecs
import MySQLdb

def connect_mysql():
    db_config = {
        'host': '192.168.48.128',
        'port': 3306,
        'user': 'xiang',
        'passwd': '123456',
        '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 #低于5分的成绩已经没有了
解释:
1.刚开始,我们可以查到分数小于5分的总个数有321个
2.把所有的成绩小于5的都列出来,然后最后加一列分数加60分的结果。

select *, (grade+60) as newGrade from Score where Grade <5;

3.把分数小于5的所有成绩都加60分

update Score set grade = grade + 60 where grade < 5;

4.最后在检查分数小于5的个数为0,说明所有低于5分的分数都发生了改变。

3.索引查询
MySQL索引的概念
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
索引类别
1.普通索引
普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE column =)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。
2.唯一索引
普通索引允许被索引的数据列包含重复的值。比如说,因为人有可能同名,所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。
如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE 把它定义为一个唯一索引。这么做的好处:一是简化了 MySQL 对这个索引的管理工作,这个索引也因此而变得更有效率;二是 MySQL 会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了;如果是,MySQL 将拒绝插入那条新记录。也就是说,唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。
3.主索引
在前面已经反复多次强调过:必须为主键字段创建一个索引,这个索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是:前者在定义时使用的关键字是 PRIMARY 而不是 UNIQUE。
4.外键索引
如果为某个外键字段定义了一个外键约束条件,MySQL 就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。
5.复合索引
索引可以覆盖多个数据列,如像 INDEX (columnA, columnB) 索引。这种索引的特点是 MySQL 可以有选择地使用一个这样的索引。如果查询操作只需要用到 columnA 数据列上的一个索引,就可以使用复合索引 INDEX(columnA, columnB)。不过,这种用法仅适用于在复合索引中排列在前的数据列组合。比如说,INDEX (A,B,C) 可以当做 A 或 (A,B) 的索引来使用,但不能当做 B、C 或 (B,C) 的索引来使用。

mysql主键和索引的区别:

  1. 主键一定是唯一性索引,唯一性索引并不一定就是主键。 
    所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引。
    因为主键可以唯一标识某一行记录,所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。
    主键除了上述作用外,常常与外键构成参照完整性约束,防止出现数据不一致。数据库在设计时,主键起到了很重要的作用。 
    主键可以保证记录的唯一和主键域非空,数据库管理系统对于主键自动生成唯一索引,所以主键也是一个特殊的索引。

  2. 一个表中可以有多个唯一性索引,但只能有一个主键。

  3. 主键列不允许空值,而唯一性索引列允许空值。

4.索引可以提高查询的速度。

创建Course的 CouID 的字段为主键 Score的 SID 字段为主键 Student的 StdID 字段为主键 Teacher的 TID 字段为主键:

import MySQLdb
def connect_mysql():
    db_config = {
        'host': '192.168.48.128',
        'port': 3306,
        'user': 'xiang',
        'passwd': '123456',
        '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的客户端上执行sql7,得到如下的结果:
执行语句:

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|
+----+---------------+-------+------+-----------------------+----------------------+-----------+--------+-------+-------+

显示说明,我们在搜索StdID的时候,是走了idx_StdID_CouID索引的。


本文转自 听丶飞鸟说 51CTO博客,原文链接:http://blog.51cto.com/286577399/2043700


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10天前
|
数据采集 数据可视化 Python
Python分析香港26281套在售二手房数据
Python分析香港26281套在售二手房数据
|
11天前
|
机器学习/深度学习 数据处理 Python
如何利用Python实现高效的数据清理与预处理
数据清理和预处理是数据科学家和分析师工作中不可或缺的一环,而Python作为一门强大的编程语言,可以使这个过程变得更加高效和便捷。本文将介绍一些常见的数据清理和预处理技术,并演示如何使用Python来实现这些技术。
|
1天前
|
算法 NoSQL Python
开山之作!Python数据与算法分析手册,登顶GitHub!
若把编写代码比作行军打仗,那么要想称霸沙场,不能仅靠手中的利刃,还需深谙兵法。 Python是一把利刃,数据结构与算法则是兵法。只有熟读兵法,才能使利刃所向披靡。只有洞彻数据结构与算法,才能真正精通Python
|
1天前
|
存储 数据处理 Python
Python中一二维数据的格式化和处理技术
Python中一二维数据的格式化和处理技术
|
2天前
|
存储 数据采集 JavaScript
基于Python的Web数据与解析
基于Python的Web数据与解析
11 0
|
11天前
|
数据采集 存储 数据挖掘
Python DataFrame初学者指南:轻松上手构建数据表格
【5月更文挑战第19天】本文是针对初学者的Pandas DataFrame指南,介绍如何安装Pandas、创建DataFrame(从字典或CSV文件)、查看数据(`head()`, `info()`, `describe()`)、选择与操作数据(列、行、缺失值处理、数据类型转换、排序、分组聚合)以及保存DataFrame到CSV文件。通过学习这些基础,你将能轻松开始数据科学之旅。
|
11天前
|
数据挖掘 数据处理 Python
【Python DataFrame 专栏】Python DataFrame 入门指南:从零开始构建数据表格
【5月更文挑战第19天】本文介绍了Python数据分析中的核心概念——DataFrame,通过导入`pandas`库创建并操作DataFrame。示例展示了如何构建数据字典并转换为DataFrame,以及进行数据选择、添加修改列、计算统计量、筛选和排序等操作。DataFrame适用于处理各种规模的表格数据,是数据分析的得力工具。掌握其基础和应用是数据分析之旅的重要起点。
【Python DataFrame 专栏】Python DataFrame 入门指南:从零开始构建数据表格
|
14天前
|
JSON JavaScript 数据格式
利用 python 分析基金,合理分析数据让赚钱赢在起跑线!(1)
利用 python 分析基金,合理分析数据让赚钱赢在起跑线!(1)
|
15天前
|
存储 JSON 数据格式
Python知识点——高维数据的格式化
Python知识点——高维数据的格式化
11 0
|
15天前
|
Python
Python知识点——文件和数据格式化
Python知识点——文件和数据格式化
17 0