MySQL第八次作业-复习作业（工厂项目）

身份设定：你的父亲是厂长，在毕业后计划继承家业
随着“互联网+”的不断深入，经济、社会发展加速，各位厂长副厂长会议决定传统的工厂管理方法需要革新，要紧跟时代潮流数字化的管理工厂。他们一致认为你作为厂长接班人，又是计算机相关专业的大专生有义务也有能力做好这件事。
你告诉大家最近正在学习MySQL数据库，作为70/80后他们不知道什么是MySQL，你深知解释了他们也不懂，但碍于面子还是说了MySQL是一种数据库管理系统。他们看你愿意解释所以争先恐后的问，问题越来越多……
什么是数据库？
存储数据的仓库

用户可以对数据库执行什么操作？
可以进行增删查改操作

什么是数据库管理系统？
是一个操纵和管理数据库的大型软件

你个数据库管理系统可以管理多少个数据库？
无数个

那一个数据库有多少张表呢？
无数个

每张表是否可以有很多条数据呢？
无数条

你解释了这么多他们终于明白了点基础知识，这时他们又问道，这个数据库管理系统这么强大，你要用什么来操作呢？你说要使用SQL语言来操作数据库，他们觉得你好厉害，居然会一种他们从没听过的“外语”，强烈要求你演示一下
你打开了控制台，输入指令登录MySQL
mysql –u root –p数据库密码

查看所有数据库
show databases;

告诉他们在学校学习了很多内容，自己也创建了很多个数据库，并一一指给他们看
看完后随手创建了一个新的数据库，名为 temp_姓名全拼

CREATE DATABASE temp_zhongzheng;

将 temp_姓名全拼 数据库的默认字符集改成了 gbk

alter database temp_zhongzheng default character set gbk;

查看 temp_姓名全拼 数据库创建信息，告诉大家刚才的修改已经生效了

status;

删除 temp_姓名全拼 数据库

drop database temp_zhongzheng;

退出MySQL

\q

他们看到你的展示后更坚信要你来完成工厂数据库创建，你就数据库创建所需内容和大家讨论，讨论结果是需要创建车间、职工、产品三张表，会后你创建并使用了数据库 姓名全拼_factory

CREATE DATABASE zhongzheng_factory;

之后根据会议内容整理出每张表的字段信息，并开始创建表
车间表 姓名全拼_workshop
字段名 数据类型 长度 主、外键 其他约束 备注信息
NO CHAR 10 主键 车间号
ADDRESS TEXT 地址
OPEN_DATE DATE 开设时间
NAME VARCHAR 10 非空，唯一 主任姓名

create table zhongzheng_workshop( NO CHAR(10) comment"车间号" primary
key, ADDRESS TEXT comment"地址", OPEN_DATE DATE comment"开设时间", NAME
VARCHAR(10) not null unique comment"主任姓名" );

职工表 姓名全拼_worker
字段名 数据类型 长度 主、外键 其他约束 备注信息
NO CHAR 10 主键 工号
SHOP_NO CHAR 10 外键workshop (no) 车间号
NAME VARCHAR 10 非空，唯一 姓名
WORK VARCHAR 30 工种

create table zhongzheng_worker( NO CHAR(10) primary key comment"工号",
SHOP_NO CHAR(10) comment"车间号", NAME VARCHAR(10) not null unique
comment"姓名", WORK VARCHAR(30) comment"工种", constraint fk_SHOP_NO
foreign key(SHOP_NO) references zhongzheng_workshop(No) );

产品表 姓名全拼_product
字段名 数据类型 长度 主、外键 其他约束 备注信息
NO CHAR 10 主键 产品编号
WORK_NO CHAR 10 外键worker(no) 工号
WEIGHT DOUBLE 重量

create table zhongzheng_product( NO CHAR(10)primary key comment"产品编号",
WORK_NO CHAR(10) comment"工号", WEIGHT DOUBLE comment"重量", constraint
fk_WORK_NO foreign key(WORK_NO) references zhongzheng_worker(No) );

创建完三张表后你查看数据库中所有表

show tables;

看到了所有自己创建的表非常有成就感，继续逐个查看表的创建信息
首先查看车间表的创建信息

show create table zhongzheng_workshop;

之后查看职工表的创建信息

create table zhongzheng_worker ；

最后查看产品表的创建信息

show create table zhongzheng_product;

心满意足的拿给老爸（厂长）看，没想到他说你这个我也看不懂啊，受到吐槽的你内心非常不爽，想到可以画ER图给他看，打开https://www.processon.com/网站（可直接微信登陆），画好了ER图

老爸看后非常满意，立即通知秘书召开厂长会议
在会上你讲解了数据库的搭建过程以及目前成果，获得了极大的好评，同时收到了3条建议
1.职工表需要添加工龄字段，在之后计算工资以及年终奖会用到
字段名 数据类型 长度 主、外键 其他约束 备注信息
SENIORITY TINYINT 2 工龄

alter table zhongzheng_worker add SENIORITY TINYINT(2) comment"工龄";

2.车间表需要添加主任的联系电话字段，方便之后管理车间
字段名 数据类型 长度 主、外键 其他约束 备注信息
PHONE VARCHAR 20 主任联系电话

alter table zhongzheng_workshop add PHONE VARCHAR(20) comment"主任联系电话";

3.同理职工表也需要添加联系方式字段方便管理
字段名 数据类型 长度 主、外键 其他约束 备注信息
PHONE VARCHAR 20 联系电话

alter table zhongzheng_worker add PHONE VARCHAR(20) comment"主任联系电话";

加好后查看车间表的字段信息

desc zhongzheng_workshop;

感觉非常赞，之后查看职工表的字段信息

desc zhongzheng_worker;

发现联系方式被加在了最后，追求完美主义的你把联系方式的字段位置修改到了姓名之后

alter table zhongzheng_worker modify phone varchar(20) after name;

修改后再次查看职工表的字段信息

desc zhongzheng_worker;

正当对自己的修改成果满意之时，老爸秘书发来文件，打开看到是公司现有的车间及员工信息。首先给车间表所有字段批量插入数据
车间号 地址 开设时间 主任姓名 联系电话
WS1 Jone Street 2018-03-06 Frank 13852374989
WS2 2018-05-27 Alan
WS3 Gray Street Peter 17074120081
WS4 2018-09-15 Clare 18182330376

insert into zhongzheng_workshop values
(“ws1”,“jone_street”,“2018-03-06”,“frank”,“13852374989”),
(“ws2”,null,“2018-05-27”,“alan”,null),
(“ws3”,“gray_street”,null,“peter”,“17074120081”),
(“ws4”,“null”,“2018-09-15”,“clare”,“18182330376”);

插入成功后查看车间表所有字段数据

select * from zhongzheng_workshop;

接下来打开员工信息表
工号 车间号 职工姓名 联系电话 工种 工龄
W1 WS1 Chisel 13024390423 special 5
W2 WS1 Asa expert 3
W3 WS1 Parker 15754183291 common 1
W4 WS1 Ron 18283957359 common 0
W5 WS1 Tyler common 0
W6 WS1 Tony 17683934639 common 0
W7 WS1 August common 0
W8 WS2 Sam 13186396347 special 5
W9 WS2 Klee 13792694095 expert 3
W10 WS2 Webb 17288936395 common 1
W11 WS2 Jared common 0
W12 WS2 Moore common 0
W13 WS2 June 18086473924 common 0
W14 WS2 Reg 13087843989 common 0
你第一次发现原来厂里有这么多人，心里默默为老爸竖了个大拇指，但转念一想自己要新增这么多数据进去真是头痛，你开始想办法，想看看怎么做可以少打点字，观察发现大多数的工种都是common，大多数工龄都是0，所以你想重新创建一个有默认值的职工表，这样之后新增的记录里工种工龄和默认值相同就不用打了，会省事不少
所以你先用修改字段名的语句将职工表的WORK字段修改如下表所示：
旧字段名 新字段名 数据类型 长度 默认 备注信息
WORK WORK_TYPE VARCHAR 30 common 工种

alter table zhongzheng_worker change work work_type varchar(30)
default “common” comment"工种";

之后用修改字段数据类型的语句将职工表的SENIORITY字段修改如下表所示：
字段名 数据类型 长度 默认 备注信息
SENIORITY INT 2 0 工龄

alter table zhongzheng_worker change SENIORITY SENIORITY int(2)
default “0” comment"工领";

修改成功后查看职工表的创建信息

show create table zhongzheng_worker;

看到默认值已经设置成功了，你便把工种、工龄都和默认值相同的记录标红
工号 车间号 职工姓名 联系电话 工种 工龄
W1 WS1 Chisel 13024390423 special 5
W2 WS1 Asa expert
W3 WS1 Parker 15754183291 common 1
W4 WS1 Ron 18283957359 common 0
W5 WS1 Tyler common 0
W6 WS1 Tony 17683934639 common 0
W7 WS1 August common 0
W8 WS2 Sam 13186396347 special 5
W9 WS2 Klee 13792694095 expert 3
W10 WS2 Webb 17288936395 common 1
W11 WS2 Jared common 0
W12 WS2 Moore common 0
W13 WS2 June 18086473924 common 0
W14 WS2 Reg 13087843989 common 0
之后使用指定字段批量插入数据的方式开始往职工表新增这些被标红的八条数据，在新增时不用指定工种、工龄字段

insert into zhongzheng_worker (no,shop_no,name,phone) values
(“w4”,“ws1”,“ron”,“18283957395”), (“w5”,“ws1”,“tyler”,null),
(“w6”,“ws1”,“tony”,“174723724898”), (“w7”,“ws1”,“august”,null);

insert into zhongzheng_worker (no,shop_no,name,phone) values
(“w11”,“ws2”,“Jared”,null), (“w12”,“ws2”," Moore “,null),
(“w13”,“ws2”,” June “,“18086473924”), (“w14”,“ws2”,” Reg ",”
13087843989”);

因为没有指定工种、工龄字段也就不用频繁的写“common”和0，这次新增下来你感觉自己轻松了不少，但有点担心数据库有没有自动写入默认值，所以你用查询语句查看职工表中的工号、工种、工龄字段数据

select * from zhongzheng_worker;

看到结果非常满意，一边说着MySQL真靠谱一边整理出了还没有添加进表的所有数据
工号 车间号 职工姓名 联系电话 工种 工龄
W1 WS1 Chisel 13024390423 special 5
W2 WS1 Asa expert
W3 WS1 Parker 15754183291 common 1
W8 WS2 Sam 13186396347 special 5
W9 WS2 Klee 13792694095 expert 3
W10 WS2 Webb 17288936395 common 1
看来看去好像没有可以使用技术手段节省代码量的方式了，你只好使用所有字段批量新增的方式往职工表里新增了这些数据

insert into zhongzheng_worker values
(“w1”,“ws1”,“Chisel”,“13024390423”,“special”,“5”),
(“w2”,“ws1”,“Asa”,null,“expert”,null),
(“w3”,“ws1”,“Parker”,“15754183291”,“commom”,“1”),
(“w8”,“ws2”,“sam”,“13186396347”,“special”,“5”),
(“w9”,“ws2”,“klee”,“13792694095”,“expert”,“3”),
(“w10”,“ws2”,“webb”,“17288936395”,“common”,“1”);

新增完成后查看职工表中所有数据

select * from zhongzheng_worker;

发现WS1，WS2车间均有职工联系方式不明确，这不利于管理，需要联系他们的车间主任来拿到这些员工的联系方式。所以你查看车间表中车间号在WS1，WS2里的所有字段信息

select * from zhongzheng_workshop where no in (“ws1”,“ws2”);

发现WS2车间地址、车间主任的联系方式都没有，顿时火大，打电话给厂长秘书要WS2车间地址及主任联系方式，她说问道告诉你。之后你打给WS1车间主任问到了WS1车间所有缺少的信息，你一一对数据库中的数据进行了修改
首先修改职工表中Asa的联系方式为17349275853，工龄为3年

update zhongzheng_worker set phone = “17349275853”,seniority=”3” where
name = “asa”;

接着修改Tyler的联系方式为18934783924

update zhongzheng_worker set phone = “18934783924” where name =
“tyler”;

最后修改August的联系方式为15597239204

update zhongzheng_worker set phone = “15597239204” where name =
“august”;

之后查询车间表中WS1车间的职工姓名、联系方式、工龄字段信息

select name,phone from zhongzheng_workshop where no in (“ws1”);

此时秘书打来电话说WS2车间的地址在Bleecker Street，主任的联系方式是17792847923，你修改了车间表中WS2车间的地址和主任联系方式

update zhongzheng_workshop set address=“Bleecker
Street”,phone=“17792847923” where no=“WS2”;

打电话给WS2车间主任，得知Jared和Moore的联系方式分别是18529723749和17697397496，你写了两条update语句来改这两条数据

update zhongzheng_worker set phone=“18529723749” where name=“Jared”;
update zhongzheng_worker set phone=“17697397496” where name=“Moore”;

车间主任还告诉你Moore因为家庭原因已经离职，这时你才发现自己在设计职工表时并没有字段来记录职工的在离职状态，这简直是一大败笔，细想这个字段只有在职和不在职两种值，所以准备用1和0来表示以节省数据库空间，你在职工表中添加了一个字段来记录职工是否在职
字段名 数据类型 长度 主、外键 其他约束 默认 备注信息
IS_ONWORK ENUM(“0”,”1”) “1” 是否在职 1是 0否

alter table zhongzheng_worker add IS_ONWORK enum(“0”,“1”) default “1”
comment"是否在职 1是 0否";

新增好字段后，再将Moore的是否在职字段修改为“0”，表示他已经离职了

update zhongzheng_worker set IS_ONWORK=“0” where name=" Moore ";

修改完成后查看职工表中所有数据

select * from zhongzheng_worker;

发现所有的空都被填满了，之后查看车间表中的所有数据

select * from zhongzheng_workshop;

看到成果非常满意，拿电脑来到老爸办公室，他让你展示下这个数据库怎么用，你告诉他可以告诉他任何他想知道的关于车间及职工的信息。他问你WS2车间的在职员工有哪几位？
你用查询语句查询了职工表中车间号为WS2并且是否在职字段为1的工号、车间号、职工姓名、在职状态字段数据

select no,shop_no,name,is_onwork from zhongzheng_worker where
shop_no=“WS2” and is_onwork!=“0”;

他要求你查询不是今年入职的所有非普通员工，你很想怼他说数据库把并没有入职时间字段
但是想到有工龄字段，你查询了职工表中工龄不等于0并且工种不是common的工号、车间号、职工姓名、工种、工龄字段数据

select no,shop_no,name,work_type from zhongzheng_worker where
is_onwork!=“0” and work_type!=“common”;

他感叹着数据库真强大，这是他的手机响了，手机号是以177开头7923结尾，他问你这是我们公司的员工吗？你在职工表中查看联系方式以177开头7923结尾的所有字段数据

select * from zhongzheng_worker where phone like"177%7923";

发现并不存在这样的手机号，又去车间表查看主任联系方式以177开头7923结尾的所有字段数据

select * from zhongzheng_workshop where phone like"177%7923";

告诉老爸这是WS2车间主任的联系方式
他接完电话告诉你WS2车间已经发来了昨天的产品生产情况
产品编号 工号 是否合格
1 W8 合格
2 W9 合格
3 W10 合格
4 W11 合格
5 W12 合格
6 W8 不合格
7 W13 合格
8 W14 合格
9 W9 不合格
10 W15 合格
11 W10 合格
12 W11 合格
13 W12 合格
14 W13 合格
15 W14 合格
16 W8 合格
17 W15 合格
看到数据你感觉不妙，因为在记忆中你创建的产品表并没有字段来记录产品是否合格
你查看了产品表的字段信息

desc zhongzheng_product;

你删除了表中不需要的重量字段

alter table zhongzheng_product drop weight;

添加了是否合格字段
字段名 数据类型 长度 主、外键 其他约束 默认 备注信息
IS_QUALIFIED ENUM(0,1) 非空 1 是否合格 1是 0否

alter table zhongzheng_product add IS_QUALIFIED enum(“0”,“1”) not null
default “1” comment"是否合格1是0否";

观察表中数据发现产品编号应该用整数类型，并且设置自动增加，所以你修改了产品编号字段的数据类型，并查看了产品表的字段信息
字段名 数据类型 长度 其他约束 备注信息
NO INT 5 自动增加 产品编号

alter table zhongzheng_product change no no int(5) comment"产品编号"
auto_increment;

设置好了产品编号的自增、是否合格的默认，你发现在新增时不用指定产品编号、是否合格字段，只需要工号这一个字段，简直太赞了，你用指定字段批量新增数据的方式插入了所有数据
产品编号 工号 是否合格
1 W8 合格
2 W9 合格
3 W10 合格
4 W11 合格
5 W12 合格
6 W8 不合格
7 W13 合格
8 W14 合格
9 W9 不合格
10 W15 合格
11 W10 合格
12 W11 合格
13 W12 合格
14 W13 合格
15 W14 合格
16 W8 合格
17 W15 合格

insert into zhongzheng_product(work_no)
values(“W8”),(“W9”),(“W10”),(“W11”),(“W12”),(“W8”),(“W13”),(“W14”),(“W9”),(“W10”),(“W11”),(“W12”),(“W13”),(“W14”),(“W8”);

这时发现W15这条数据新增报错，和外键“工号”相关

你查看了职工表中车间号为WS2的所有字段数据

select * from zhongzheng_worker where shop_no=“WS2”;

发现果真没有W15这个员工，你准备打电话给WS2的车间主任核实职工信息，所以查看了车间表中车间号为WS2的主任联系方式字段数据

select phone from zhongzheng_workshop where no=“WS2”;

电话沟通得知W8是今天新入职的员工，信息如下
工号 车间号 职工姓名 联系电话 工种 工龄 是否在职
W15 WS2 Jack 13397693245 Common 0 1
你看到工种、工龄、是否在职都与表中设置的默认值相同，故使用指定字段添加数据的方式插入了这条数据，只指定了工号、车间号、职工姓名、联系方式字段

insert into zhongzheng_worker(no,shop_no,name,phone)
values(“W15”,“WS2”,“Jack”,“13397693245”);

新入职职工加入职工表后你又开始执行之前往产品表新增数据的语句
你还是用指定字段批量新增数据的方式插入了所有数据，只指定了工号字段
产品编号 工号 是否合格
1 W8 合格
2 W9 合格
3 W10 合格
4 W11 合格
5 W12 合格
6 W8 不合格
7 W13 合格
8 W14 合格
9 W9 不合格
10 W15 合格
11 W10 合格
12 W11 合格
13 W12 合格
14 W13 合格
15 W14 合格
16 W8 合格
17 W15 合格

insert into zhongzheng_product(work_no) values(“W15”);

插入成功后你查看产品表中的所有字段数据

select * from zhongzheng_product;

发现no并没有从1开始自动增加而是从18开始，你在想是不是自己之前新增了17条数据又删除掉的原因，但是这里老师还没有讲过所以你只好自己百度，百度发现truncate关键词可以在删除表中数据后让自动增加的默认初始值重新从1开始，所以你准备使用 truncate表名; 语句删除产品表所有数据

truncate zhongzheng_product;

再次用指定字段批量新增数据的方式插入产品表所有数据，只指定工号字段
产品编号 工号 是否合格
1 W8 合格
2 W9 合格
3 W10 合格
4 W11 合格
5 W12 合格
6 W8 不合格
7 W13 合格
8 W14 合格
9 W9 不合格
10 W15 合格
11 W10 合格
12 W11 合格
13 W12 合格
14 W13 合格
15 W14 合格
16 W8 合格
17 W15 合格

insert into zhongzheng_product(work_no)

values(“W8”),(“W9”),(“W10”),(“W11”),(“W12”),(“W8”),(“W13”),(“W14”),(“W9”),(“W15”),(“W10”),(“W11”),(“W12”),(“W13”),(“W14”),(“W8”),(“W15”);

插入成功后你再次查看产品表中的所有字段数据

select * from zhongzheng_product;

发现产品编号为6和9的两条记录产品应该是不合格的，你把产品表中产品编号在6，9里的是否合格状态改成了0

update zhongzheng_product set IS_QUALIFIED=“0” where no=“6”;

修改后再次查看产品表所有字段数据

select * from zhongzheng_product;