Mysql约束、分组查询

友儿

September 6, 2022

627 views

No comments

2386 words

MYSQL 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`)
```
非空约束
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约束、分组查询