大师网-带你快速走向大师之路 解决你在学习过程中的疑惑,带你快速进入大师之门。节省时间,提升效率

Python学习笔记二十(MySQL、SQL、联表查询)

联表查询(多表查询)

create table article(
id int unsigned auto_increment primary key,
title varchar(100) ,
content_path varchar(100),
content_num int unsigned,
author_id int unsigned,
is_del bit default 0
);

create table author(
id int unsigned auto_increment primary key,
name varchar(100),
address varchar(100),
is_del bit default 0
);

01现有数据.png

连接查询

  • inner join 数据的交集
  • left join 数据的交集 并上 左边表的特有数据
  • right join 数据的交集 并上 右边表的特有数据

inner join 内连接或等值连接(相当于两表或者多表的的权重相等)

-- inner join 数据的交集
select * from article inner join author ;
select * from article inner join author on article.author_id = author.id;
02inner join.png

问题:数据表就是一个集合,每一条记录就是集合中的一个值,内联查询就是对两个集合(表)求笛卡尔乘积[1],但是笛卡尔积的结果集并不是我们想要的结果,所以需要使用on 筛选出我们需要的结果集(笛卡尔积结果集的子集)

简单来说内联查询会得到 每个数据表记录数 乘积 条记录的结果集,如上面的两张表article 和 author 分别有20 和 10 条记录,会得到一个有 200(20*10)条记录的结果集

注:

  • on 联表查询时,做条件筛选时的关键字
  • where 单表查询时,做条件筛选时的关键字
  • having 分组时,做条件筛选的关键字

left join 左连接 (左边的权重大于右边,左表为主)

-- 显示公共部分,以及左表特有的部分(author_id  为null 的五条记录)
select * from article left join author on article.author_id = author.id;
03left join.png

right join 右连接 (右边的权重大于左边,右表为主)

-- 显示公共部分,以及右表特有的部分
select * from article right join author on article.author_id = author.id;
04right join.png

自关联

当需要的数据都在一张表中,但普通的查询又不能满足需求时,可以通过自关联的方式得到一张能满足需求的表

05省市县数据表.png

如上表,省市县在一张表中,如果要做三级联动[2]需要自关联查询得到想要的数据。

-- 查询山东省的所有城市
select * from areas as a1 inner join areas as a2 on a1.pid = a2.aid where a2.atitle = "山东省";
06自关联.png

子查询

查询的嵌套 ,将一个查询的结果集作为另一个查询的输入,可以作为条件,也可以作为数据源。

-- 查询山东省的所有城市 
select * from areas where pid = (select aid from areas where atitle = "山东省");
07子查询.png

Python操作数据库

pymysql安装[3]

from pymysql import connect  # 导入模块

if __name__ == '__main__':
    #调用connect() 方法创建 Connection 对象 
    conn = connect(host="localhost", port=3306, user="root", password="dragon",
                   database="fang", charset="utf8")

    cs = conn.cursor()

    for tmp in range(20):
        cs.execute("""insert into article(title,author_id) values(%s,%s); """, ("dragon", 1000))
        conn.commit()

    conn.close()

  • connect() 方法的参数
    • 参数host:连接的mysql主机,如果本机是'localhost'
    • 参数port:连接的mysql主机的端口,默认是3306
    • 参数database:数据库的名称
    • 参数user:连接的用户名
    • 参数password:连接的密码
    • 参数charset:通信采用的编码方式,推荐使用utf8
  • Connection 对象的方法
    • close()关闭连接
    • commit()提交
    • cursor()返回Cursor对象,用于执行sql语句并获得结果
  • Cursor对象
    • 用于执行sql语句,使用频度最高的语句为select、insert、update、delete
    • 获取Cursor对象:调用Connection对象的cursor()方法
      Cursor 对象的方法
    • close()关闭
    • execute(operation [, parameters ])执行语句,返回受影响的行数,主要用于执行insert、update、delete语句,也可以执行create、alter、drop等语句
    • fetchone()执行查询语句时,获取查询结果集的第一个行数据,返回一个元组
    • fetchall()执行查询时,获取结果集的所有行,一行构成一个元组,再将这些元组装入一个元组返回
  • Cursor 对象的属性
    • rowcount只读属性,表示最近一次execute()执行后受影响的行数
    • connection获得当前连接对象

sql注入问题

from pymysql import connect

if __name__ == '__main__':
    conn = connect(host="localhost", port=3306, user="root", password="fangfang",
                   database="fang", charset="utf8")

    article_id = input("请输入id:")
    cs = conn.cursor()

    sql_str = """select * from article where id >%s; """ % article_id
    print(sql_str)
    # for tmp in range(20):
    cs.execute(sql_str)
    conn.commit()

    conn.close()
    
    # 运行结果
    # 请输入id:123
    # select * from article where id >123;
    
    # 假设 我输入
    # 请输入id:123;delete from article 
    # select * from article where id >123;delete from article ;

    # 假设是插入语句
    # """inset into areas(atitle) values ("%s");""" % """a");delete from areas where (aid >"1"""
    # 如下
    # inset into areas(atitle) values ("a");delete from areas where (aid >"1");


08SQL注入 清空表数据.png

解决方案

execute 可以传参

def execute(self, query, args=None):
        """Execute a query

        :param str query: Query to execute.

        :param args: parameters used with query. (optional)
        :type args: tuple, list or dict

        :return: Number of affected rows
        :rtype: int

        If args is a list or tuple, %s can be used as a placeholder in the query.
        If args is a dict, %(name)s can be used as a placeholder in the query.
        """
        while self.nextset():
            pass

        query = self.mogrify(query, args)

        result = self._query(query)
        self._executed = query
        return result

# 实例
# 列表 或 元组传值
# execute("""select * from article where id >%s; """,(123,)) 

# 字典传值
# sql_str = """insert into article(title,author_id) values (%(title)s,%(author_id)s); """
# cs.execute(sql_str, {"author_id": 22,"title": "aaaa"})

事务

  • 事务作用:保证数据修改的完整性
  • 特性:
    数据操作后可以,可以回滚(注意删除表,删除库没办法)
    acid 原子性,一致性,隔离性,持久性
  • 事务的操作
     begin;-- 开启事务
     start transaction;-- 开启事务
     commit; -- 提交事务
     rollback; -- 回滚

索引

  • 有什么用:加快查询的速度
  • 原理:对我们的字段进行分组,如果大并发的情况下,修改数据会变慢
-- 创建
    create index 名字 on 表名(索引的字段);
-- 查看
    show index from 表名;
-- 删除
    drop index 索引名 on 表名; # 必须指定表名

数据库设计

三范式

  • 第一范式(1NF):强调的是列的原子性,即列不能够再分成其他几列。(一个数据一个字段)
  • 第二范式(2NF):首先是 1NF,另外包含两部分内容,一是表必须有一个主键;二是没有包含在主键中的列必须完全依赖于主键,而不能只依赖于主键的一部分。(确定主键,拥有主键)
  • 第三范式(3NF):首先是 2NF,另外非主键列必须直接依赖于主键,不能存在传递依赖。即不能存在:非主键列 A 依赖于非主键列 B,非主键列 B 依赖于主键的情况。(减少重复)

数据库军规


到此结DragonFangQy 2018.5.15


  1. 笛卡尔乘积

  2. 三级联动数据库资源,需要积分

  3. pymysql模块的安装