分表操作

为什么要分表，一个表不好吗？

这要从范式说起。

数据库系统概论里面是这样说的：

  • 第一范式（1NF）：字段不可分；
  • 第二范式（2NF）：有主键，非主键字段依赖主键；
  • 第三范式（3NF）：非主键字段不能相互依赖。

可能是我太菜了，看不懂，那就自己总结一下。

1NF(原子性)==> 所有字段不可再分

比如：

姓名 个人信息
张三 12岁，5年级，二班，男

这种就是不符合第一范式的，起码要将年级，班级，年龄，性别分为几个不同的字段。

2NF(唯一性)==> 一个表只能说明一个事物

比如：

学号 姓名 年龄 课程名称 成绩 学分

这个表就不符合第二范式，因为这个表明显说明了两个事务：学生信息， 课程信息。

  • 这个不符合第二范式的表会导致如下问题：
    • 数据冗余：每条记录都含有相同信息，比如课程名称相同，需要占用很多存储空间
    • 删除异常：删除所有学生成绩，就把课程信息全删除了，这种就很难受
    • 插入异常：学生未选课，数据库中的数据不完整会导致插入不进去
    • 更新异常：调整课程学分，所有行都调整

3NF ==> 每列都与主键有直接关系，不存在传递依赖

学号 姓名 年龄 所在学院 学院联系电话

不符合第三范式，因为学院联系电话依赖于所在学院，而所在学院又依赖于学号

而其中关键字为单一关键字”学号”。存在依赖传递:：(学号) → (所在学院) → (学院联系电话)

会导致如下问题

  • 数据冗余：每条记录都含有相同信息
  • 删除异常：删除所有学生信息，就把院校信息删除了

什么是分表？

假设有这样一个表（goods-商品表）：

id name category_name brand_name
1 x3250 m4机架式服务器 服务器/工作站 ibm
2 x550cc 15.6英寸笔记本 笔记本 华硕
3 r510vc 15.6英寸笔记本 笔记本 华硕

可以看到category_name和brand_name中有冗余的数据。

可不可以用数字id表示category_name和brand_name呢，答案是肯定的。

数据准备：goods表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(150) not null,
    cate_name varchar(40) not null,
    brand_name varchar(40) not null,
    price decimal(10,3) not null default 0,
    is_show bit not null default 1,
    is_saleoff bit not null default 0
);

-- 向goods表中插入数据

insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); 
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); 
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default); 
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default); 
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default); 
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default); 
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default); 
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); 
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); 
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default); 
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default); 
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); 
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

分表之创建商品种类表 goods_cates

四步走：

  • 第一步 创建 “商品品牌表” 表(两个字段id,name)
  • 第二步 插入数据 brand_name到商品种类表 goods_cates中
  • 第三步 同步 商品表 数据 通过 goods_cates 数据表来更新 goods 表
  • 第四步 修改 goods 表结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# 第一步	创建表 (商品种类表 goods_cates)
CREATE TABLE goods_cates
(
    id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL
);


# 第二步	同步 商品分类表 数据 将商品的所有 (种类信息) 写入到 (商品种类表) 中
# 按照 分组 的方式查询 goods 表中的所有 种类(cate_name)
# 这里插入时候插入的数据是一个查询语句的时候不用写values

select distinct cate_name from goods;
insert into goods_cates(name) (select distinct cate_name from goods);

# 第三步 同步 商品表 数据 通过 goods_cates 数据表来更新 goods 表
# 因为要通过 goods_cates表 更新 goods 表 所以要把两个表连接起来
# 把 商品表 goods 中的 cate_name 全部替换成 商品分类表中的 商品id ( update ... set )
update (goods inner join goods_cates on goods.cate_name=goods_cates.name) 
set goods.cate_name=goods_cates.id;


# 第四步 修改表结构
#  查看表结构(注意 两个表中的 外键类型需要一致)
#  修改表结构 alter table 字段名字不同 change,把 cate_name 改成 cate_id int unsigned not null

alter table goods change cate_name cate_id int unsigned not null ;

分表之创建商品品牌表 goods_brands

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 创建 商品品牌表 goods_brands
# 第一步 创建 "商品品牌表" 表
create table goods_brands (
    id int unsigned primary key auto_increment,
    name varchar(40) not null);
	
# 插入数据 brand_name(分组)
# 按照 分组 的方式查询 goods 表中的所有 种类(brand_name)
insert into goods_brands(name) (select brand_name from goods group by brand_name);
--(注意) 把查询出来的 结果 写入 goods_brands 表里去 ( insert into ) 只插入name
		
# 第二步 同步数据
# 通过goods_brands数据表来更新goods数据表 g.brand_name=b.id
update (goods inner join goods_brands on goods.brand_name=goods_brands.name) set goods.brand_name=goods_brands.id;

# 第三部 修改表结构
# 通过alter table语句修改表结构 brand_id int unsigned not null
alter table goods change brand_name brand_id int unsigned not null;

Python操作数据库

首先要下载pymyql依赖包，然后import pymysql导包

使用Python操作数据库要分几步

1，创建连接 conn = pymysql.connect(主机，端口，用户，密码，数据库，字符集)
2，创建游标 cur = conn.cursor()
3，书写sql 直接写
4，执行SQL cur.execute(sql)**

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
import pymysql
"""
1，创建连接  conn = pymysql.connect(主机，端口，用户，密码，数据库，字符集)
2，创建游标  cur = conn.cursor()
3，书写sql   直接写
4，执行SQL   cur.execute(sql)
"""
conn = pymysql.connect(
    host='127.0.0.1',
    port = 3306,
    user ='root',
    password = '123456',
    database = 'bigdata_db',
    charset ='utf8'
)

cur = conn.cursor()  # 创建游标

sql = "insert into demo(id,name) values(2,'测试')"
sql1 = "select * from demo"
sql2 = ""
for i in range(10):
    cur.execute(sql)
for i in range(30):
    cur.execute(sql1)

for i in cur.fetchall():
    print(i)

cur.close()  # 先关闭游标再关连接
conn.commit()  # 提交修改数据库
conn.close()

SQL之CASE_WHEN_ELSE_END

数据准备的SQL文件可以去我的博客园下载，下载链接

直接解压将sql文件拖动到DataGrip中，然后生成如下几张表，其中的关系如下： p9tPlW9.png

各个表的含义如下

  1. employees 员工表 记录了Northwind所有员工信息.
  2. customers 客户表，记录了客户相关信息.
  3. products 记录了商品信息.
  4. categories 记录了商品类别信息.
  5. suppliers 记录了商品供应商信息.
  6. orders 记录了Northwind的顾客下的订单.
  7. order_items 记录了订单中的每一件商品明细.

题目摘要

练习5(多表关联提取订单编号为10250的订单详情)

练习8(时间的用法)统计2013年入职的员工数量，统计字段起别名 number_of_employees

练习15(CASE-WHEN-THEN-END 的使用)需求： 创建一个报表，统计员工的经验水平

练习25(CASE-WHEN-THEN-ELSE-END 的使用)需求：创建报表将所有产品划分为素食和非素食两类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
# 练习5(多表关联提取订单编号为10250的订单详情)
# 提取订单编号为10250的订单详情，显示如下信息：
# product_name, 产品名
# quantity, 重量
# unit_price （order_items 表) 单价
# discount 折扣
# order_date 订单日期
# 按商品名字排序

# 表:
# order_items: 订单详情表
# products : 商品表
# orders : 订单表
select
       product_name,
       quantity,
       order_items.unit_price,
       discount,
       order_date
from
     order_items join products on order_items.product_id=products.product_id
join orders on orders.order_id=order_items.order_id
where order_items.order_id='10250' order by product_name;

# 练习8(时间的用法)
# 统计2013年入职的员工数量，统计字段起别名 number_of_employees
# 需要注意SQL中的日期总是放在单引号内，格式通常为“ YYYY-MM-DD”（年-月-日）
# 字段: hire_date 雇佣日期
# 表: employees 雇员表
select
       count(*) as number_of_employees
from employees where hire_date>'2013-01-01' and hire_date<'2014-01-01';

# 练习11(多字段分组)
# 统计每个员工处理的订单总数
# 结果包含员工ID employee_id，姓名first_name 和 last_name，处理的订单总数(别名 orders_count)
# 字段:
#   e.employee_id, 员工id
#   e.first_name, 名
#   e.last_name, 姓
# 表:
#  orders : 订单
#  employees : 员工
select
       orders.employee_id,
       first_name,
       last_name,
       count(*)
from orders
    inner join employees on orders.employee_id = employees.employee_id
group by employee_id, first_name, last_name
order by employee_id;

# 练习15(CASE-WHEN-THEN-END 的使用)
# 需求： 创建一个报表，统计员工的经验水平
# 显示字段：first_name, last_name, hire_date, 和 experience
# 经验字段（experience ）：
# hire_date: 雇佣时间
# 'junior' 2014年1月1日以后雇用的员工
# 'middle' 在2013年1月1日之后至2014年1月1日之前雇用的员工
# 'senior' 2013年1月1日或之前雇用的员工
# 表: employees
select
       first_name,
       last_name,
       hire_date,
       case
            when hire_date>'2014-01-01' then '初级'
            when hire_date<='2014-01-01' and hire_date >'2013-01-01' then '中级'
            when hire_date<='2013-01-01' then '高级' end
           as experience
from employees;

# 练习25(CASE-WHEN-THEN-ELSE-END 的使用)
# 需求：创建报表将所有产品划分为素食和非素食两类
# 报表中包含如下字段：
# 产品名字 product_name
# 类别名称 category_name
# 膳食类型 diet_type:
# 非素食 'Non-vegetarian' 商品类别字段的值为 'Meat/Poultry' 和 'Seafood'.
# 其余为 素食 'vegetarian'
# category_id : 种类id
# 表:
# categories c : 种类
# products p : 产品
select * from categories;
select
       product_name,
       category_name,
       products.category_id,
       case
            when categories.category_name='Meat/Poultry' or categories.category_name='Seafood'then '非素食'
            else '素食'
            end as diet_type
from products inner join categories on products.category_id=categories.category_id;

# 练习28(COUNT和CASE-WHEN-THEN-END的使用)
# 需求：统计客户的contact_title 字段值为Owner的客户数量
# 查询结果有两个字段：represented_by_owner 和 not_represented_by_owner
# 主键: customer_id
# 表: customers

-- 我的写法
select * from customers;
select
       count(*),
       case
           when contact_title='Owner' then 'represented_by_owner'
           else 'not_represented_by_owner'
           end as e
from customers group by e;

-- 标准答案
SELECT
    count(CASE WHEN contact_title='Owner' THEN customer_id END) AS represented_by_owner,
    count(CASE WHEN contact_title!='Owner' THEN customer_id END) AS not_represented_by_owner
from customers;


# 练习30(COUNT和CASE-WHEN-THEN-END-GROUP-BY的使用)
# 需求：创建报表，统计不同类别产品的库存量，将库存量分成两类 >30 和 <=30 两档分别统计数量
# 报表包含三个字段
# 类别名称 category_name
# 库存量   units_in_stock
# 库存充足 high_availability
# 库存紧张 low_availability
# 主键: category_id
# 表:
# products: 商品
# categories: 种类
SELECT
    category_name,
    count(CASE WHEN units_in_stock > 30 THEN product_id END) AS high_availability,
    count(CASE WHEN units_in_stock <= 30 THEN product_id END) AS low_availability
from
    products
JOIN
    categories
ON
    products.category_id=categories.category_id
GROUP BY
    categories.category_id,categories.category_name;