MySQL数据库(补充)

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 事务的使用

1. 开启事务： start TRANSACTION
2. 提交事务： commit
3. 回滚事务： 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，且一个表必须有一个主键，非主键字段必须完全依赖于主键¹，而不能只依赖于主键的一部分

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()

---

1. 主键：能够确定唯一的一行记录的特殊字段。可以是多个字段。 ↩︎