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

2018-05-15 本文已影响37人 DragonFangQy

联表查询（多表查询）

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