联表查询（多表查询）
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
