Python9 数据库进阶

Python9 ---- 数据库进阶

Python爬虫

数据库进阶

tip: 通过 desc 表名命令查看当前表信息

tip2: as 关键字可以为我们的字段或者表取别名

Mysql约束

顾名思义就是对表中的数据进行限定, 目的是保证数据的正确性, 有效性和完整性

主键约束primary key
- 什么是主键
  关系表中记录的唯一标识(不能为null, 不可重复)
  
  选取和业务无关的字段, 常用的主键就是自增id
  
  数据库引擎会通过主键建立索引, 索引是一个有序结构, 我们希望当前主键的值不要改变, 避免重新建立索引, 影响性能.
- 添加主键
  - 建表时添加
```
CREATE TABLE store_perf(
id int PRIMARY KEY auto_increment,
store_name varchar(255)
)
```
  - 通过可视化界面操作(建议)
- 主键的长度限制
  767bytes, 对应的长度为767/4=191
  
  bytes表示字节, 一个utf8mb4按四个字节算, 所以长度最大为191
- 自增键必须为主键
```
Incorrect table definition; there can be only one auto column and it must be defined as a key
```
- 联合主键
  主键其实只能有一个, 如果在图形界面当中选择多个字段作为主键, 那么程序会自动帮我们创建联合主键
- 修改主键
  - 添加主键
```
ALTER TABLE store_perf ADD PRIMARY KEY (`id`)
```
  - 移除主键
```
ALTER TABLE store_perf DROP PRIMARY KEY
```
  - 添加联合主键
```
ALTER TABLE store_perf ADD PRIMARY KEY (`id`, `store_name`)
```
非空约束(空白和null是两个概念)
NOT NULL

只能约束程序层面上没有操作该表中的某个字段, 不能约束人行为上对其赋值为空白字符.
唯一约束
UNIQUE

NULL可以重复

比如统计店铺业绩, 该店铺不可以重复, 那就可以使用唯一约束
外键约束foreign key
- 外键的作用
  保持数据的一致性和完整性, 通过外键来表达主表和从表的关系, 避免冗余字段.
- 为什么现在不用外键了?
  - 性能问题
    插数据需要校验
  - 并发问题
    在高并发的事务场景下, 使用外键容易造成死锁.
  - 扩展性问题
    - 外键就相当于把对象之间的关系交给数据库来维护, 如果遇到分库分表, 外键是不生效的.
    - 作数据迁移时, 触发器, 存储过程和外键都很难迁移, 增加了维护成本.
  - 维护成本
    - 需要有专业DBA来维护庞大的数据库关系
default
默认约束, 默认会填充当前字段.

如果我们没有给一个有默认约束的字段create_time值, 那么该字段会默认填充CURRENT_TIMESTAMP

分组查询`GROUP BY`

将数据按某个字段进行分组, 配合SUM, AVG, COUNT, MAX, MIN等聚合函数做统计使用

INSERT INTO store_perf(store_name, amount, department) values ("男装_店铺1", 34543123.23, "男装事业部"), ("男装_店铺2", 41232373.12, "男装事业部"),("男装_店铺3", 62135632.22, "男装事业部"), ("男装_店铺4", 612356123.26, "男装事业部"), ("女装_店铺1", 78716237854.3, "女装事业部"), ("女装_店铺2", 89123565741.23, "女装事业部"), ("女装_店铺3", 831235769.23, "女装事业部"), ("女装_店铺4", 1912312345.13, "女装事业部"), ("女装_店铺5", 87324234712.92, "女装事业部"), ("女装_店铺6", 12754547457.93, "女装事业部")

SELECT count(*) as 店铺数, 
FORMAT(AVG(amount), 2) as 平均业绩,
SUM(amount) as 总业绩,
MAX(amount) as 最大业绩,
MIN(amount) as 最小业绩,
department as 部门 
FROM store_perf GROUP BY department

课后作业

简单使用Mysql约束, 并举例说明使用场景.
练习使用分组查询.

变量声明

服务器系统变量

通过@@来调用系统变量

# 列出mysql所有系统变量
SHOW VARIABLES
SELECT @@date_format

用户变量

通过@来调用用户变量

# 输出变量yesterday
SELECT @yesterday

# 对变量yesterday进行赋值
SET @yesterday=subdate(CURRENT_DATE, 1);

# 日期的格式化
SET @yesterday=DATE_FORMAT(@yesterday, "%Y/%m/%d");

# 小数的格式化
SET @amount=0.4;
SET @amount=CAST(@amount as DECIMAL(15, 3));
SELECT @amount

局部变量
- 不需要@前缀
- 需要事先进行变量类型的声明和初始化

存储过程

简单地认为是SQL中的函数

声明一个存储过程

创建存储过程

每一句语句结束之后都要添加分号;

CREATE PROCEDURE stat_store_perf(days INT)
BEGIN
	DECLARE t_date VARCHAR(255);
	set t_date = DATE_FORMAT(subdate(CURRENT_DATE, days), '%Y-%m-%d');
	SELECT count(*) as 店铺数, 
	FORMAT(AVG(amount), 2) as 平均业绩,
	SUM(amount) as 总业绩,
	MAX(amount) as 最大业绩,
	MIN(amount) as 最小业绩,
	department as 部门 
	FROM store_perf WHERE sta_date=t_date
	GROUP BY department;
END

调用存储过程
```
CALL stat_store_perf(1)
```
删除存储过程
```
DROP PROCEDURE stat_store_perf
```

触发器

和存储过程一样, 都是嵌入到mysql中的一段程序, 区别就是存储过程需要显式调用, 而触发器式根据对表的相关操作自动激活执行.

创建触发器

CREATE TRIGGER 触发器名
BEFORE[AFTER] [INSERT, UPDATE, DELETE]

CREATE TRIGGER check_department
BEFORE INSERT
ON store_perf
FOR each row
BEGIN
	IF new.department not in ("男装事业部", "女装事业部") THEN
	SET new.department 'unknow';
	END IF;
END

触发器的应用
多用来检查字段
查看触发器

建议通过客户端删除
```
SHOW TRIGGERS FROM store_perf
```
删除触发器

建议通过客户端删除
```
DROP TRIGGERS check_department
```

课后作业

重点掌握用户变量, 练习变量的赋值和使用
练习存储过程(能自行完整地写出一个函数)
练习触发器(只要求操作几次)

用python连接数据库

pymysql

pip install pymysql

#如果让你装vs环境, 执行以下命令升级pip即可
python -m pip install --upgrade pip

连接数据库

数据库设置

MYSQL_CONF = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "qwe369",
    "db": "test"
}

连接

# 连接数据库
mysql_con = pymysql.connect(**MYSQL_CONF)

# 简单理解真正执行语句的线程
mysql_cursor = mysql_con.cursor()

执行SQL语句

单条执行

执行时间15.31s

@clock_it_deco
def insert_one():
    for i in range(10**4):
        store_name = f"店铺_{i}"
        amount = format(random.uniform(10**3, 10**6), '.2f')
        department = f"事业部_{random.randint(1, 10)}"
        sta_date = time.strftime("%Y-%m-%d")

        SQL = f"""INSERT INTO store_perf(store_name, amount, department, sta_date)
                  VALUES ('{store_name}', {amount}, '{department}', '{sta_date}')"""
        print(SQL)
        mysql_cursor.execute(SQL)

        # 显示执行commit, 避免cursor执行, 但是数据库未收到的执行指令的情况
        mysql_con.commit()

多条执行

如果其中有一条记录报错, 那么整个values插入都会失败

@clock_it_deco
def insert_many():
    values = []
    for i in range(10**4):
        store_name = f"店铺_{i}"
        amount = format(random.uniform(10**3, 10**6), '.2f')
        department = f"事业部_{random.randint(1, 10)}"
        sta_date = time.strftime("%Y-%m-%d")
        values.append((store_name, amount, department, sta_date))

    SQL = """INSERT INTO store_perf(store_name, amount, department, sta_date)
             VALUES (%s, %s, %s, %s)"""
    print(values)
    mysql_cursor.executemany(SQL, values)
    mysql_con.commit()

获取返回值

def get_shops():
    SQL = "select store_name, amount, sta_date from store_perf where department='事业部_1' LIMIT 2"
    mysql_cursor.execute(SQL)
    # 获取返回值
    query_set = mysql_cursor.fetchall()
    print(query_set)

返回的是一个元组, 元组中表示记录的也是一个元组

(('店铺_1', Decimal('823471.56'), '2021-02-06'), ('店铺_6', Decimal('726927.02'), '2021-02-06'))

索引和优化

创建索引

未建索引: 0.287s

建了索引后: 0.016s
- 设置索引的字段不可以超过191个字符长度, 也就是767个bytes
```
CREATE INDEX 索引名 ON 表名(字段1, 字段2...)

CREATE INDEX store_name_index ON store_perf(store_name)
```

创建联合索引

CREATE INDEX store_name_sta_date_department_index ON store_perf(store_name, sta_date, department)

查看索引
```
SHOW INDEX FROM store_perf
```

删除索引

ALTER TABLE store_perf DROP INDEX  store_name_index

查看当前查询语句有没有命中索引
- EXPLAIN语句查看当前语句执行性能
- 如果key有值, 说明命中了索引, 且key值为索引名
```
EXPLAIN SELECT * from store_perf WHERE store_name = "店铺_224123"
```
单个字段可以命中联合索引吗?
联合索引涉及到一个叫左缀查询的规则

如果想命中索引, 查询语句中涉及到字段必须是联合索引创建时从左到右顺序
- 原理
  在a_b_c_index这样一个联合索引当中, 实质执行中是先查出a的结果集, 然后再查b或c的结果集(第一个字段匹配，才匹配后面的子字段)
索引的实现原理
B+树, 一种特殊的链表, 用来实现二分查找.

如何优化mysql

合理地建立索引
- 频繁作为查询条件的字段应该建立索引
- 唯一性太差的字段不适合单独建立索引
- 更新非常频繁的字段不适合建立索引
- 避免不经过索引的操作
  - not in, !=等反向逻辑
  - BETWEEN范围查找
  - or逻辑两边都必须命中索引才会走索引
  - 联合索引, 不按左缀查询规则

加缓存

数据库缓存
```
show VARIABLES LIKE '%query_cache%'
```

用redis做缓存

请求 -> redis -> 未命中 -> mysql -> 返回

换固态硬盘

事务

事务的提交, 回滚

START TRANSACTION;
INSERT INTO store_perf(store_name, amount, department, sta_date) VALUES ('店铺_1764', 753294.41, '事业部_8', '2021-02-05');
ROLLBACK;
COMMIT;

业务中使用事务

将class_2中的同学转移到class_1, 如果SQL_2报错, 会导致class_2中的同学丢失.

def transaction():
    try:
        SQL = "DELETE FROM class_2 where name='name_13'"
        mysql_cursor.execute(SQL)
        SQL_2 = "INSERT INTO class_1 VALUES(name)"
        mysql_cursor.execute(SQL_2)

    except Exception as e:
        print("raise Exceptions", e.args[0])
        print("rollback")
        mysql_con.rollback()
    finally:
        mysql_con.commit()

课后作业

练习pymysql的使用, 报错单语句执行, 多语句执行, 查询语句, 事务的使用

import pymysql
import random
import time
from base.fun.time_deco import time_deco

# 定义MYSQL连接参数
MYSQL_CONF = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "1",
    "db": "test"
}

# mysql_con =pymysql.connect(host='127.1',user='root')
# 通过字典的方式传参
# 连接数据库
mysql_con = pymysql.connect(**MYSQL_CONF)

# 真实执行语句的线程
mysql_cursor = mysql_con.cursor()


@time_deco
def create_table():
    SQL = f"""CREATE TABLE if not exists `store_perf`  (
                  `id` int(11) NOT NULL AUTO_INCREMENT,
                  `store_name` varchar(191) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
                  `department` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
                  `amount` decimal(15, 2) NULL DEFAULT NULL,
                  `sta_date` varchar(191) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,
                  `create_time` datetime(0) NOT NULL DEFAULT current_timestamp(),
                  PRIMARY KEY (`id`) USING BTREE
              ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
            """
    mysql_cursor.execute(SQL)
    mysql_con.commit()


def delete_table():
    SQL = '''truncate table store_perf;'''


# 单条SQL插入，单条执行
@time_deco
def insert_one():
    for i in range(10 ** 2):
        store_name = f"店铺_{i}"
        # 生成资金
        amount = format(random.uniform(10 ** 3, 10 ** 6), '.2f')
        department = f"事业部_{random.randint(1, 10)}"
        sta_date = time.strftime("%Y-%m-%d")

        SQL = f"""INSERT INTO store_perf(store_name, amount, department, sta_date)
                  VALUES ('{store_name}', {amount}, '{department}', '{sta_date}')"""
        # print(SQL)
        mysql_cursor.execute(SQL)

        # 显示执行commit, 避免cursor执行, 但是数据库未收到的执行指令的情况
        mysql_con.commit()


# 多条SQL插入，如果其中有一条记录报错, 那么整个values插入都会失败
@time_deco
def insert_many():
    values = []
    for i in iter(range(10 ** 2)):
        store_name = f"店铺_{i}"
        amount = format(random.uniform(10 ** 3, 10 ** 6), '.2f')
        department = f"事业部_{random.randint(1, 10)}"
        sta_date = time.strftime("%Y-%m-%d")
        values.append((store_name, amount, department, sta_date))

    SQL = """INSERT INTO store_perf(store_name, amount, department, sta_date)
             VALUES (%s, %s, %s, %s)"""
    # print(values)
    # print(SQL, values)
    mysql_cursor.executemany(SQL, values)
    mysql_con.commit()


# 获取返回值
# 返回的是一个元组, 元组中表示记录的也是一个元组
def get_shops():
    SQL = "select store_name, amount, sta_date from store_perf where department='事业部_1' LIMIT 2"
    mysql_cursor.execute(SQL)
    # 获取返回值
    query_set = mysql_cursor.fetchall()
    print(query_set)


if __name__ == '__main__':
    # create_table()
    # delete_table()
    # insert_one()
    # insert_many()
    get_shops()

练习索引的创建
将索引的内容背诵

Python9 数据库进阶

Python9 ---- 数据库进阶

数据库进阶

Mysql约束

分组查询GROUP BY

课后作业

变量声明

存储过程

触发器

课后作业

用python连接数据库

索引和优化

如何优化mysql

事务

课后作业

分组查询`GROUP BY`