1. 外键、视图、索引、事务
1.1 外键
一个表的主键A,在另外一个表B中出现,我们说A是表B的一个外键。通常情况下不建议在数据库中使用外键,外键的操作都在应用层上去解决。
1.1.1 创建外键
例如: f_property 表中的 f_proid,当做外键添加到f_host_info 表中,外键的字段为f_proid_new
alter table f_host_info add foreign key(f_proid) references f_property(f_proid);
1.1.2 查看外键
show create table f_host_info;
1.1.3 删除外键
alter table 表名 drop foreign key 外键名;
alter table f_host_info drop foreign key f_host_info_ibfk_1;
1.2 视图
1.2.1 定义
视图是一个能够把复杂的SQL语句的功能封装起来的一个虚表。若基本表的数据发生变化,视图也会跟着发生变化。
好处就是:方便操作,可读性和复用性比较高。
1.2.2 创建视图
create view 视图名 as select 语句
例如:我想查询每个户主在银行有多少钱
CREATE VIEW host_money AS SELECT
e.f_host_id,
e.f_person_id,
e.p_name,
f.f_entry_name,
e.f_money
FROM
(
SELECT
c.*,
d.p_name
FROM
(
SELECT
a.f_host_id,
a.f_person_id,
b.f_bank_type,
b.f_money
FROM
f_host_info a
LEFT JOIN f_property b ON a.f_proid = b.f_proid
) c
LEFT JOIN f_person d ON c.f_person_id = d.p_id
) e
LEFT JOIN f_dict_entry f ON e.f_bank_type = f.f_entry_id
1.2.3 使用视图
-- 使用视图,视图也是一种表,只不过是虚拟的
select * from host_money;
1.2.4 删除视图
drop view 视图名
drop view host_money;
1.3 索引
若数据库是一本字典,那么索引就相当于目录。
1.3.1 创建索引
alter table 表名 add index 索引名 (字段名,…)
alter table f_host_info add index hostid (f_host_id);
1.3.2 删除索引
drop index 索引名称 on 表名
drop index hostid on f_host_info
1.3.3 查看已有索引
show index from 表名
show index from f_host_info
1.3.4 优缺点
优点: 加快查询速度
缺点: 占用磁盘空间,且数据量越多耗费的时间越多
使用原则:
1.经常发生数据更新的表避免使用太多索引
2.小表不建议使用索引
3.大表且数据不频繁更改,可以使用索引
1.4 事务
1.4.1 事务的定义
事务是指作为一个基本工作单元执行的一系列SQL语句的操作,要么完全的执行,要么完全地都不执行。
简单的说即:事务就是要完成一件事情,其中包含很多步骤,比如:做饭,要先去买菜,然后洗菜,然后才可以做饭。
如果没有买菜,那么也就做不成饭了,所以以上三个步骤要么都做,要么都不做,这就是事务的需求。
1.4.2 事务的四大特性: ACID
1.4.2.1 原子性 Atomic
一个事务必须被视为一个不可分割的最小单元,整个事务中的所有操作,要么全部成功,要么全部失败回滚。不能单独执行其中的一部分。
1.4.2.2 一致性 Consistency
事务必须使得数据库从一个一致性状态变到另一个一致性状态
1.4.2.3 隔离性 Isolation
一个事务在最终提交前,所做的修改是不可见的,不会影响其他的事务。
1.4.2.4 持久性 Durability
一旦事务提交,则其所做的修改会永久保存到数据库中。
1.4.3 事务的使用
- 开启事务: start TRANSACTION
- 提交事务: commit
- 回滚事务: rollback
start TRANSACTION;
select f_proid from f_host_info;
update .....
update .....
commit;
2. 范式
2.1 数据冗余
数据冗余是指数据之间的重复,即同一数据存储在不同数据文件中的现象。
例如:age表: 有三个字段 id name age
sex表:有三个字段 id name sex
即 id 和 name 重复,完全可以合成一个表 info 字段:id name age sex
2.2 范式
关系型数据库,目前有六种范式:第一范式1NF、第二范式2NF、第三范式3NF、巴斯-科德范式BCNF、第四范式4NF、第五范式5NF(又被称为完美范式)
2.2.1 1NF
字段的原子性,即一个字段不能再分
2.2.2 2NF
满足1NF,且一个表必须有一个主键,非主键字段必须完全依赖于主键1,而不能只依赖于主键的一部分
2.2.3 3NF
满足2NF,非主键字段必须直接依赖于主键,不能存在传递依赖。
例如:order表 包含:orderid orderdate custid custname custage 字段.其中,custname custage 直接依赖的是custid,而不是orderid 所以不符合3NF
3. Python连接MySQL
3.1 PyMySQL模块
使用PyMySQL模块可以连接MySQL数据库。
使用步骤如下:
1.导入pymysql包
2.创建连接对象
conn = pymysql.connect(host="localhost", port=3306, user="test", password="123456", database="testbase", charset="utf8")
3.获取游标对象
cs = conn.cursor()
4.pymysql完成数据的增删改查操作
# 数据操作
sql = "select * from host_money"
# 获取sql影响的行数
row = cs.execute(sql)
print(row)
# 获取一条数据
content = cs.fetchone()
print(content)
# 获取所有数据
print(cs.fetchall())
5.关闭游标和连接
cs.close()
conn.close()
3.2 数据操作
fetchall () 方法返回的数据是一个元组。
使用pycharm去连接数据库的时候,默认是开启事务的,此时如果执行增删改操作后,数据库中是不会发生变化的,需要进一步在代码中添加提交操作。
sql = "update f_property set f_money = 3000 where f_proid = 9006"
cs.execute(sql)
# 提交修改
conn.commit()
3.3 SQL语句参数化
为了防止SQL注入,建议使用参数列表传递参数
# 构造参数列表
params = [input("请输入编号")]
sql = "update f_property set f_money = 3000 where f_proid = %s"
cs.execute(sql, params)
# 提交修改
conn.commit()
- 主键:能够确定唯一的一行记录的特殊字段。可以是多个字段。 ↩︎