第四章：数据库-4

第四章：数据库-4

外键：

· 一个表的主键A，在另外一个表B中出现，A就是表B的外键

-- 使用外键
use jing_dong;
alter table goods 
add foreign key (cate_id) 
references good_cates(id);
-- 插入新数据
-- 如果添加了cate_id的外键则无法生效
insert into goods (name,cate_id) values ("梨子笔记本",10);
-- 如果频繁的插入，不建议使用外键，因为效率很低
alter table goods drop foreign key goods_ibfk_1;
-- 看一下创建的表结构
show create table goods;

视图：

· 简化复杂操作，本质是一张虚拟的表，增强复用性
· 如果视图引用的表改变了，识图也会跟着改变

-- 创建视图
create view VIEWNAME as select 语句;
-- 查看视图
show tables ;
-- 使用视图
select * from VIEWNAME;
-- 删除视图
drop view VIEWNAME;

use python_test_1;
create view v_students as
select s.id,s.name,s.age,s.gender,c.name as cls_name
from students as s 
inner join classes as c 
on s.cls_id=c.id;
-- 改基本表的信息，视图也会跟着改变
update students
set age = 40
where name="小明";

事务（Transaction）：

· 作为一个基本单元执行的一系列sql语句的操作，要么完全执行，要么完全的不执行
· 执行失败后，直接回滚

---

ACID特性：
    原子性（Atomicity）：一个事务被视为不可分割的最小工作单元，只有所有操作都执行才算成功
    一致性（consistency：数据库总是从一个一致性的状态到另外一个一致性的状态
    隔离性（isolation）：一个事务最终提交前，其他事务不可见，需要排队等待
    持久性（durablity）：一旦事务提交，就是永久修改

过程：
    start transaction;
    语句1;
    语句2;
    语句3;
    commit;

---

begin 或者 start transsaction：
    开始事务
commit：
    提交事务
rollback：
    回滚事务

-- ACID验证
use python_test_1;
select * from students;
begin;
update students 
set age = 100
where name="小明";
commit ;

索引：

· 是一种特殊的文件，包含着对数据表里所有记录的位置信息
· 主键（primary key）本身就是有索引的

---

show index from 表名
    · 查看已有索引
alter table TABLENAME add index 索引名 (字段名……)
    · 添加索引
drop index 索引名称 on 表名
    · 删除索引

-- 无索引
use python_test_1;
set profiling=1;
select *
from test_index where title="ha-99999";
show profiles ;

-- 有索引
use python_test_1;
set profiling=1;
alter table test_index add index (title);
select *
from test_index where title="ha-99999";
show profiles ;

数据库设计三范式：

一范式：
    · 强调字段的原子性，即一个字段不可再分
二范式：
    · 满足一范式基础外
    · 表必须有一个主键
    · 非主键必须完全依赖于主键，而不能只依赖主键的一部分
    · 主键可以由多个字段共同组成
三范式：
    · 满足二范式基础外
    · 非主键字段必须直接依赖于主键，不能存在传递依赖
    · 即不能存在，非主键字段A依赖于非主键字段B，非主键字段B依赖于主键的情况

E-R模型和表间关系：

· 实体-关系模型
· 一对一，一对多，多对多

Python连接数据库：

· 使用pymysql模块

---

使用方法：
    1.导入pymysql包
    2.创建连接对象
    3.获取游标对象
    4.pymysql完成数据的增删查改操作
    5.关闭游标和连接

· 注意pycharm连接默认开启事务机制，因此需要commit

# 导入模块
import pymysql
# 创建连接对象
conn = pymysql.connect(
    host="localhost",
    port=3306,
    user='root',
    password='你的密码',
    database='mysql',
    charset='utf8'
)
# 操作
conn.rollback()
conn.commit()
conn.close()
# 要执行sql语句
# 创建游标
cur = conn.cursor()
cur.execute("use BASENAME;")
cur.execute("select * from TABLENAME;")
# 游标获取数据
# 返回一个
cur.fetchone()
# 返回全部
cur.fetchall()
cur.close()
# 注意要先关闭游标，再关闭connect对象

SQL语句参数化：

· 防止sql注入