python 关联表_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

问题：数据表就是一个集合，每一条记录就是集合中的一个值，内联查询就是对两个集合(表)求笛卡尔乘积

简单来说内联查询会得到 每个数据表记录数 乘积 条记录的结果集，如上面的两张表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

如上表，省市县在一张表中，如果要做三级联动

-- 查询山东省的所有城市

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安装

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