MYSQL数据库系统第6次实验 数据表查询综合实验

创建新冠疫情期间企业复工复产信息数据库（rwork），其中包含两张表，分别是疫情记录表(ep)、企业复工复产记录表(ew),两张表的结构如表1和表2：

1. 创建rwork数据库，按照表1和表2结构创建ep和ew数据表，要求ew表的外键与ep表的主键字段使用级联更新和级联删除。
   

mysql> create table ep(
    -> pid int primary key not null auto_increment comment'地区编号',
    -> rname varchar(20) not null unique comment'地区名称',
    -> fnum int default 0 comment '现存确诊人数',
    -> rnum int default 0 comment'累计确诊人数',
    -> cnum int default 0 comment'治愈人数',
    -> dnum int default 0 comment'死亡人数',
    -> pdate DateTime default now() comment'发布时间'
    -> );
Query OK, 0 rows affected (1.53 sec)

mysql> create table ew(
    -> wid varchar(20) primary key comment'工商注册号',
    -> ename varchar(100) not null comment'企业名称',
    -> pid int not null comment'所在地区',
    -> tel varchar(11) not null comment'联系电话',
    -> linkman varchar(10) not null comment'联系人',
    -> total int default 0 comment'员工总人数',
    -> rcount int default 0 comment'复工人数',
    -> tcount int default 0 comment'核酸检测人数',
    -> infect int default 0 comment'感染隔离人数',
    -> rdate datetime comment'复查时间',
    -> foreign key(pid) references ep (pid)
    -> on delete cascade
    -> on update cascade
    -> );
Query OK, 0 rows affected (1.49 sec)

2.分别在ep和ew数据表中添加以下记录：
（1）ep表记录
(‘广东’,7,1637,1622,8);
(‘北京’,297,891,585,9);
(‘香港’,95,1197,1095,7);
(‘上海’,24,706,675,7);
(‘甘肃’,21,162,139,2);
(‘四川’,11,589,575,3);
(‘台湾’,5,447,435,7);

注意:我们的评论数据中存在emoji表情，而这些表情是按照四个字节一个单位进行编码的，而我们通常使用的utf-8编码在mysql数据库中默认是按照3个字节一个单位进行编码的，正是这个原因导致将数据存入mysql数据库的时候出现错误。

把一张表里所有的字段编码都改成 utfmb4

alter table `你的表` convert to character set utf8mb4;

mysql>   alter table `ep` convert to character set utf8mb4;
Query OK, 0 rows affected (1.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into ep (rname,fnum,rnum,cnum,dnum)
    -> values ('广东',7,1637,1622,8),
    ->  values ('北京',297,891,585,9),
    -> ('香港',95,1197,1095,7),
    ->  ('上海',24,706,675,7),
    ->  ('甘肃',21,162,139,2),
    ->  ('四川',11,589,575,3),
    ->  ('台湾',5,447,435,7);
Query OK, 6 rows affected (0.10 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from ep;
+-----+-------+------+------+------+------+---------------------+
| pid | rname | fnum | rnum | cnum | dnum | pdate               |
+-----+-------+------+------+------+------+---------------------+
|   1 | 广东  |    7 | 1637 | 1622 |    8 | 2021-06-01 14:23:26 |
|   2 | 北京  |  297 |  891 |  585 |    9 | 2021-06-01 14:25:32 |
|   3 | 香港  |   95 | 1197 | 1095 |    7 | 2021-06-01 14:25:32 |
|   4 | 上海  |   24 |  706 |  675 |    7 | 2021-06-01 14:25:32 |
|   5 | 甘肃  |   21 |  162 |  139 |    2 | 2021-06-01 14:25:32 |
|   6 | 四川  |   11 |  589 |  575 |    3 | 2021-06-01 14:25:32 |
|   7 | 台湾  |    5 |  447 |  435 |    7 | 2021-06-01 14:25:32 |
+-----+-------+------+------+------+------+---------------------+
7 rows in set (0.00 sec)

（2）ew表记录
(‘GZ101’,‘广东金晶科电子股份有限公司’,1,‘13745291034’,‘张长卫’,2345,2342,2123,3,‘2020-6-10’);
(‘GZ102’,‘广州立白企业集团有限公司’,1,‘13524791734’,‘王扶林’,1126,1121,1126,0,‘2020-5-8’);
(‘HZ103’,‘惠州市惠城区创美灯饰加工厂’,1,‘13414687273’,‘刘文忠’,873,842,324,2,‘2020-5-23’);
(‘BJ101’,‘北京暖通空调公司’,2,‘13834577264’,‘吴志强’,727,720,727,0,‘2020-5-28’);
(‘BJ102’,‘北京启晖生物科技有限公司’,2,‘13726747288’,‘张丽楠’,560,534,534,0,‘2020-5-28’);
(‘SH101’,‘上海特斯拉工厂’,4,‘13885231704’,‘王长林’,1234,1130,1130,0,‘2020-2-10’);

mysql> alter table `ew` convert to character set utf8mb4;
Query OK, 0 rows affected (1.86 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql>  insert into ew (wid,ename,pid,tel,linkman,total,rcount,tcount,infect,rdate)
    -> values
    ->  ('GZ101','广东金晶科电子股份有限公司',1,'13745291034','张长卫',2345,2342,2123,3,'2020-6-10'),
    ->  ('GZ102','广州立白企业集团有限公司',1,'13524791734','王扶林',1126,1121,1126,0,'2020-5-8'),
    ->  ('HZ103','惠州市惠城区创美灯饰加工厂',1,'13414687273','刘文忠',873,842,324,2,'2020-5-23'),
    ->  ('BJ101','北京暖通空调公司',2,'13834577264','吴志强',727,720,727,0,'2020-5-28'),
    ->  ('BJ102','北京启晖生物科技有限公司',2,'13726747288','张丽楠',560,534,534,0,'2020-5-28'),
    ->  ('SH101','上海特斯拉工厂',4,'13885231704','王长林',1234,1130,1130,0,'2020-2-10');
Query OK, 6 rows affected (0.88 sec)
Records: 6  Duplicates: 0  Warnings: 0

3、查询已复工的广州企业的全部信息。（说明：企业编号含有‘GZ’字符串的记录即为广州企业）

mysql> select *
    -> from ew
    -> where wid like '%GZ%';

4、查询累计确诊人数大于全部地区平均累计确诊人数的地区名称rname、累计确诊人数rnum、死亡人数dnum；

mysql> select rname 地区人数,rnum 累计确诊人数,dnum 死亡人数
    -> from ep
    -> where rnum>(select avg(rnum) from ep);
+----------+--------------+----------+
| 地区人数 | 累计确诊人数 | 死亡人数 |
+----------+--------------+----------+
| 广东     |         1637 |        8 |
| 北京     |          891 |        9 |
| 香港     |         1197 |        7 |
+----------+--------------+----------+
3 rows in set (0.00 sec)

5、查询复工比例大于90%的企业的名称，所在地区名称rname，企业名称ename，复工率，查询结果按照复工率降序排列，所有查询结果字段用中文别名。（说明：复工率=(复工人数rcount /企业总人数total)>0.9）

mysql>  select ename as 企业名称,rname as 所在地区,(rcount/total)*100 as 复工率
    -> from ep
    -> join ew
    -> on ep.pid=ew.pid
    -> where (rcount/total)>0.9
    -> order by 复工率 desc;
+----------------------------+----------+---------+
| 企业名称                   | 所在地区 | 复工率  |
+----------------------------+----------+---------+
| 广东金晶科电子股份有限公司 | 广东     | 99.8721 |
| 广州立白企业集团有限公司   | 广东     | 99.5560 |
| 北京暖通空调公司           | 北京     | 99.0371 |
| 惠州市惠城区创美灯饰加工厂 | 广东     | 96.4490 |
| 北京启晖生物科技有限公司   | 北京     | 95.3571 |
| 上海特斯拉工厂             | 上海     | 91.5721 |
+----------------------------+----------+---------+
6 rows in set (0.00 sec)

6、查询企业复工人数平均值大于700的地区，结果显示地区编码、地区名称、企业复工人数最大值、最小值、平均值，所有查询结果字段用中文别名；
法1：

mysql>  select ep.pid 地区编码,rname 地区名称,
    -> max(rcount) 企业复工人数平均值,
    -> min(rcount) as 企业复工人数最小值,
    -> avg(rcount) as 企业复工人数平均值
    -> from ep
    -> inner join ew
    -> on ep.pid=ew.pid
    -> group by ep.pid
    -> having avg(rcount)>700;
+----------+----------+--------------------+--------------------+--------------------+
| 地区编码 | 地区名称 | 企业复工人数平均值 | 企业复工人数最小值 | 企业复工人数平均值 |
+----------+----------+--------------------+--------------------+--------------------+
|        1 | 广东     |               2342 |                842 |          1435.0000 |
|        4 | 上海     |               1130 |               1130 |          1130.0000 |
+----------+----------+--------------------+--------------------+--------------------+
2 rows in set (0.00 sec)

法2：

mysql>  select t1.pid as 地区编码,
    -> rname as 地区名,
    -> 企业复工人数平均值,
    -> 企业复工人数最大值,
    -> 企业复工人数最小值
    -> from
    -> (select pid,
    -> avg(rcount) as 企业复工人数平均值,
    -> max(rcount) as 企业复工人数最大值,
    -> min(rcount) as 企业复工人数最小值
    -> from ew
    -> group by pid
    -> having avg(rcount)>700)t1
    -> left join ep t2
    -> on t1.pid=t2.pid;
+----------+--------+--------------------+--------------------+--------------------+
| 地区编码 | 地区名 | 企业复工人数平均值 | 企业复工人数最大值 | 企业复工人数最小值 |
+----------+--------+--------------------+--------------------+--------------------+
|        1 | 广东   |          1435.0000 |               2342 |                842 |
|        4 | 上海   |          1130.0000 |               1130 |               1130 |
+----------+--------+--------------------+--------------------+--------------------+
2 rows in set (0.00 sec)

7、修改广东省治愈人数cnum增加10%，累计确诊人数rnum增加8%。

mysql>  update ep set cnum=cnum+0.1*cnum,
    -> rnum=rnum+0.08*rnum
    -> where rname like '%广东%';
Query OK, 1 row affected (0.87 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>  select * from ep
    -> where ep.pid=1;
+-----+-------+------+------+------+------+---------------------+
| pid | rname | fnum | rnum | cnum | dnum | pdate               |
+-----+-------+------+------+------+------+---------------------+
|   1 | 广东  |    7 | 1768 | 1784 |    8 | 2021-06-01 14:23:26 |
+-----+-------+------+------+------+------+---------------------+
1 row in set (0.00 sec)

8、复制企业复产记录表ew生成新数据表，表名为ew6,然后从ew6表中删除核酸检测率低于50%（即：核酸检测人数tcount / rcount复工人数<0.5）的企业记录。

mysql> create table ew6 as select * from ew;
Query OK, 6 rows affected (1.72 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> delete from ew6
    -> where tcount/rcount<0.5;
Query OK, 1 row affected (0.87 sec)

mysql> select * from ew6;