MySQL第八次作业

身份设置:你父亲是厂长，计划毕业后继承家业

互联网 随着经济和社会发展的加快，厂长副厂长会议决定，传统的工厂管理方法需要创新，数字管理工厂应跟上时代潮流。他们同意，作为厂长的继任者，计算机相关专业的大学生有义务和能力做好这件事。

你告诉你，你最近在学习MySQL作为70/80后的数据库，他们不知道什么是MySQL，你知道他们不懂解释，但因为面子还是说了MySQL这是一个数据库管理系统。他们看到你愿意解释，所以他们争先恐后地问，问题越来越多……

什么是数据库？

仓库存储数据

用户可以执行什么操作数据库？

可增删查改操作

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

您的数据库管理系统能管理多少个数据库？
无数个

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

每个表能有很多数据吗？
无数条

你解释了这么多，他们终于明白了一些基本知识，然后他们问，这个数据库管理系统如此强大，你想用什么来操作？你说你想用它SQL语言操作数据库，他们认为你很强大，实际上会是一种他们从未听说过的外语，强烈要求你演示
您打开控制台，输入指令登录MySQL
mysql –u root –p数据库密码

查看所有数据库
show databases;

告诉他们在学校学到了很多东西，他们也创建了很多数据库，并逐一指向他们
看完之后，我创建了一个新的数据库，叫做 temp_姓名全拼

create database temp_linjinzheng;

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

alter database temp_linjinzheng default character set gbk;

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

show create database temp_linjinzheng;

删除 temp_姓名全拼 数据库

drop database temp_linjinzheng;

退出MySQL \q

看到你的展示后，他们坚信你会完成工厂数据库的创建。您将与您讨论数据库创建所需的内容。讨论结果是创建车间、员工和产品三个表。会后，您创建并使用数据库 姓名全拼_factory

create database lingjingzheng_factory;

然后根据会议内容整理每个表的字段信息，开始创建表格

车间表 姓名全拼_workshop

字段名	数据类型	长度	主、外键	其他约束	备注信息
NO	CHAR	10	主键		车间号
ADDRESS	TEXT				地址
OPEN_DATE	DATE				开设时间
NAME	VARCHAR	10		非空，唯一	主任姓名

职工表 姓名全拼_worker

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

产品表 姓名全拼_product

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

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

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

首先查看车间表的创建信息

 

之后查看职工表的创建信息  ,最后查看产品表的创建信息

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

老爸看后非常满意，立即通知秘书召开厂长会议

在会上你讲解了数据库的搭建过程以及目前成果，获得了极大的好评，同时收到了3条建议

1.职工表需要添加工龄字段，在之后计算工资以及年终奖会用到

字段名	数据类型	长度	主、外键	其他约束	备注信息
SENIORITY	TINYINT	2			工龄

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

 

 

 

 

2.车间表需要添加主任的联系电话字段，方便之后管理车间

字段名	数据类型	长度	主、外键	其他约束	备注信息
PHONE	VARCHAR	20			主任联系电话

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

 

3.同理职工表也需要添加联系方式字段方便管理

字段名	数据类型	长度	主、外键	其他约束	备注信息
PHONE	VARCHAR	20			联系电话

 alter table lingjinzheng_worker add PHONE VARCHAR(20) comment "联系电话";

 

 

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

desc lingjinzheng_workshop;

 

 

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

desc  lingjinzheng_worker;

 

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

alter table lingjinzheng_worker modify PHONE varchar(20) after NAME;

 

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

正当对自己的修改成果满意之时，老爸秘书发来文件，打开看到是公司现有的车间及员工信息。首先给车间表所有字段批量插入数据

车间号	地址	开设时间	主任姓名	联系电话
WS1	Jone Street	2018-03-06	Frank	13852374989
WS2		2018-05-27	Alan
WS3	Gray Street		Peter	17074120081
WS4		2018-09-15	Clare	18182330376

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

select * from lingjinzheng_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 lingjinzheng_worker change WORK WORK_TYPE VARCHAR(30) COMMENT "工种";

之后用修改字段数据类型的语句将职工表的SENIORITY字段修改如下表所示：

字段名	数据类型	长度	默认	备注信息
SENIORITY	INT	2	0	工龄

alter table lingjinzheng_worker change SENIORITY SENIORITY INT(2) DEFAULT"0" COMMENT "工龄";

 

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

SHOW CREATE LINGJINZHENG_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

之后使用指定字段批量插入数据的方式开始往职工表新增这些被标红的八条数据，在新增时不用指定工种、工龄字段

 

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

 

看到结果非常满意，一边说着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

看来看去好像没有可以使用技术手段节省代码量的方式了，你只好使用所有字段批量新增的方式往职工表里新增了这些数据

 

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

SELECT * FROM lingjinzheng_worker;

 

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

SELECT * FROM lingjinzheng_workshop where no in ("ws1","ws2");

 

 

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

首先修改职工表中Asa的联系方式为17349275853，工龄为3年

update lingjinzheng_worker set phone = "17349275853", work_type = "3" where name = "asa";

 

 

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

update lingjinzheng_worker set phone = "18934783924", work_type = "3" where name = "Tyler";

 

 

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

update lingjinzheng_worker set phone = "15597239204", work_type = "3" where name = "august";

 

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

select name,phone from lingjinzheng_workshop where no in ("ws1");

 

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

 update lingjinzheng_worker set address="Bleecker Street",phone="17792847923" where no="WS2";

 

 

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

 

 

车间主任还告诉你Moore因为家庭原因已经离职，这时你才发现自己在设计职工表时并没有字段来记录职工的在离职状态，这简直是一大败笔，细想这个字段只有在职和不在职两种值，所以准备用1和0来表示以节省数据库空间，你在职工表中添加了一个字段来记录职工是否在职

字段名	数据类型	长度	主、外键	其他约束	默认	备注信息
IS_ONWORK	ENUM(“0”,”1”)				“1”	是否在职 1是 0否

alter table lingjinzheng_worker add IS_ONWORK ENUM("0","1") default"1" comment "是否在职1是0否";

 

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

update lingjinzheng_worker set IS_ONWORK = "0",where name = "moore";

 

 

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

 

select * from LINGJINZHENG_worker;

 

 

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

SELECT * FROM lingjinzheng_workshop;

 

看到成果非常满意，拿