MySQL第八次作业

身份设定：你的父亲是厂长，在毕业后计划继承家业

随着“互联网+”的不断深入，经济、社会发展加速，各位厂长副厂长会议决定传统的工厂管理方法需要革新，要紧跟时代潮流数字化的管理工厂。他们一致认为你作为厂长接班人，又是计算机相关专业的大专生有义务也有能力做好这件事。

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

什么是数据库？

储存数据的仓库

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

可以进行新增删减

什么是数据库管理系统？

是一个操纵和管理数据库的大型软件

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

无数个

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

无数个

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

无数条

你解释了这么多他们终于明白了点基础知识，这时他们又问道，这个数据库管理系统这么强大，你要用什么来操作呢？你说要使用SQL语言来操作数据库，他们觉得你好厉害，居然会一种他们从没听过的“外语”，强烈要求你演示一下

你打开了控制台，输入指令登录MySQL

mysql -u root -p110853

查看所有数据库

show databases;

告诉他们在学校学习了很多内容，自己也创建了很多个数据库，并一一指给他们看

看完后随手创建了一个新的数据库，名为 temp_姓名全拼

Create database temp_liufeng;

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

Alter database temp_liufeng default character set gbk;

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

Show create database  temp_liufeng

删除 temp_姓名全拼 数据库

Drop table  temp_liufeng;

退出MySQL

/q

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

之后根据会议内容整理出每张表的字段信息，并开始创建表

车间表 姓名全拼_workshop

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

create table liufeng__workshop(

 NO CHAR(10) PRIMARY KEY comment"车间号",

ADDRESS TEXT comment"地址",

OPEN_DATE DATE comment"开设时间",

NAME VARCHAR(10) NOT NULL comment"主任姓名"）；

 

职工表 姓名全拼_worker

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

mysql> create table liufeng_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 liufeng_workshop(No));

 

产品表 姓名全拼_product

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

 create table liufeng_product(

    -> NO CHAR(10) PRIMARY KEY comment"产品编号",

    -> WORK_NO CHAR(10) comment"工号",

    -> WEIGHT DOUBLE comment"重量",

-> constraint fk_WORK_NO foreign key(WORK_NO) references liufeng_worker(No));

 

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

show tables;

 

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

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

show create table liufeng_workshop;

 

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

create table liufeng_worker ；

 

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

show create table liufeng_product;

 

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

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

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

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

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

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

 

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

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

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

 

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

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

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

 

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

desc liufeng_workshop;

 

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

desc liufeng_worker;

 

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

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

 

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

desc liufeng_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 liufeng_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 liufeng_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 liufeng_worker change work work_type varchar(30)
default “common” comment"工种";

 

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

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

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

 

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

show create table liufeng_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 liufeng_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 liufeng_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 liufeng_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 liufeng_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 liufeng_worker;

 

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

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

 

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

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

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

 

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

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

 

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

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

 

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

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

 

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

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

 

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

update liufeng_worker set phone=“18529723749” where name=“Jared”;

 

update liufeng_worker set phone=“17697397496” where name=“Moore”;

 

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

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

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

 

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

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

 

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

select * from liufeng_worker;

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

select * from liufeng_workshop;

 

看到成果非常满意，拿电脑来到老爸办公室，他让你展示下这个数据库怎么用，你告诉他可以告诉他任何他想知道的关于车间及职工的信息。他问你WS2车间的在职员工有哪几位？

你用查询语句查询了职工表中车间号为WS2并且是否在职字段为1的工号、车间号、职工姓名、在职状态字段数据

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

 

他要求你查询不是今年入职的所有非普通员工，你很想怼他说数据库把并没有入职时间字段

但是想到有工龄字段，你查询了职工表中工龄不等于0并且工种不是common的工号、车间号、职工姓名、工种、工龄字段数据

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

 

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

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

 

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

select * from liufeng_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 liufeng_product;

 

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

alter table liufeng_product drop weight;

 

添加了是否合格字段

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

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

 

观察表中数据发现产品编号应该用整数类型，并且设置自动增加，所以你修改了产品编号字段的数据类型，并查看了产品表的字段信息

字段名	数据类型	长度	其他约束	备注信息
NO	INT	5	自动增加	产品编号

alter table liufeng_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 liufeng_product(work_no)

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

 

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

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

select * from liufeng_worker where shop_no=“WS2”;

 

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

 

select phone from liufeng_workshop where no=“WS2”;

 

电话沟通得知W8是今天新入职的员工，信息如下

工号	车间号	职工姓名	联系电话	工种	工龄	是否在职
W15	WS2	Jack	13397693245	Common	0	1

你看到工种、工龄、是否在职都与表中设置的默认值相同，故使用指定字段添加数据的方式插入了这条数据，只指定了工号、车间号、职工姓名、联系方式字段

insert into liufeng_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 liufeng_product(work_no) values(“W15”);

 

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

select * from liufeng_product;

 

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

truncate liufeng_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 liufeng_product(work_no)

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

 

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

select * from liufeng_product;

 

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

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

 

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

select * from liufeng_product;