【BUPT数据库】GaussDB(for openGauss)数据库实验

发布于 2022-10-21  93 次阅读


GaussDB(for openGauss)数据库实验

Contents

实验一GaussDB(for openGauss)数据库创建与维护实验

实验步骤

  1. 熟悉GaussDB(for openGauss)数据库的创建过程;
  2. 创建一个名为“疫情数据”的数据库;
  3. 删除“疫情数据”数据库。
创建一个名为“疫情数据”的数据库
CREATE TABLE "疫情数据" (
    "病例号" INT,
    "省" VARCHAR(30),
    "市" VARCHAR(30),
    "区" VARCHAR(30),
    "日期" DATE,
    "性别" CHAR(3),
    "年龄" INT,
    "患者信息" VARCHAR(1024),
    "其它信息" VARCHAR(1024),
    "信息来源" VARCHAR(30)
);

image-20221021145254222

删除“疫情数据”数据库
DROP TABLE "疫情数据" ;

image-20221014083541916

实验二 数据库表的创建与维护实验

实验步骤

  1. 熟悉课程实验背景知识;
  2. 使用GAUSSDB(FOR OPENGAUSS)数据库软件创建相应的表;
  3. 将提供的数据导入各表,掌握GAUSSDB(FOR OPENGAUSS)数据库数据导入的方法;
    注意:
    (1) 表中空列的处理;
    (2) 表结构与数据类型的匹配。
  4. 修改 “病例基本信息”表数据,增加名为“备注”的列,数据类型为vachar()型;
  5. 修改 “病例基本信息”表数据,将 “备注”列的数据类型改为int;
  6. 修改 “病例基本信息”表数据,删除“备注”列;
    删除“病例基本信息”数据表。
将提供的数据导入各表

image-20221014084845034

修改 “病例基本信息”表数据,增加名为“备注”的列,数据类型为vachar()型
alter table "病例基本信息" add "备注" varchar(1024);

image-20221014085339307

修改 “病例基本信息”表数据,将 “备注”列的数据类型改为int
alter table "病例基本信息" MODIFY "备注"  int;

image-20221014085620356

修改 “病例基本信息”表数据,删除“备注”列
alter table "病例基本信息" drop "备注";

image-20221014085754717

删除“病例基本信息”数据表
drop table "病例基本信息"

image-20221021145322140

实验三 数据查询实验

一、 实验目的

通过对实验二建立的数据库关系表的各种查询的操作,加深对SQL语言和PostgreSQL查询语言的了解,掌握相关查询语句的语法及使用方法。

二、 实验内容

  1. 单表查询

    1-1 查询国内确诊病例基本信息的所有信息来源。

    1-2 给出河南省、西藏自治区、台湾省的英文名称和人口数。

    1-3 查询2021年1月20日各省现有确诊病例数据,按现有确诊病例数降序排列输出。

    1-4 顺义区中风险地区的数量。

    1-5 计算截至2021年1月20日全国累计确诊病例数。

    1-6 查询1005号病例确诊后,其所在市新增的所有确诊病例。

    1-7 在“病例基本信息表”中查询石家庄市在2021年1月11日当天以及之前的所有60岁以上的患者信息。

    1-8 统计截止到2020年12月30日美国累计确诊病例数最多的10个州。

    1-9 统计截至2021年1月20号中国发病率最高的人群(人群按照年龄划分,儿童<18,18<=青壮年<60,老年>=60)。

  2. 多表查询:

    2-1 借助病例行程信息粗略查询曾去过“源升品质生活坊”的所有患者的基本信息。

    2-2 根据病例行程信息表和病例基本信息表,查询行程信息中存在“家庭聚餐”的病例被确诊的日期。

    2-3 对比中美两国累计确诊病例数,输出格式为(日期,中国累计确诊,美国累计确诊)。

    2-4 计算截止到2021年1月20日,美国有些县的累计确诊是同一个州的其他县的2倍或以上,列出这些县,以及他们所在的州和他们的累计确诊。

    2-5 计算世界上人口数排名前10位的国家地区。

    2-6 列出美国人口超千万的大州中,截至2021年1月20日新冠肺炎疫情死亡率超过2%的州。

    2-7 截至2021年1月20日,河北省哪些区出现了新冠确诊病例但不属于中高风险地区。

    2-8 在病例行程信息表的基础上根据病例基本信息表,查询河北省病例的全部信息。

  3. 嵌套查询:

    3-1 查询披露的确诊患者信息中年龄最大的患者,输出其基本信息。(未注明年龄的患者不进行比较)。

    3-2 查询2020年12月份新增确诊患者最多的城市。

    3-3 结合“全国各省参考信息表”和“病例基本信息表”给出没有新增确诊病例或未披露病例信息的省份。

    3-4 2021年1月20日全国中高风险地区所在省中,哪些省在1月20日没有新增确诊信息披露。

    3-5 根据病例基本信息表查询一月份国内新增患者病例最多的城市。

    3-6 查询除中美两国以外的其余国家中,进入2021年以来单日新增确诊病例始终不低于一万例的国家。

1-1 查询国内确诊病例基本信息的所有信息来源。

方法一:

SELECT "信息来源" FROM "病例基本信息" GROUP BY "信息来源";

结果:

image-20221014090356966

新京报
澎湃新闻
青冈县疾病预防控制中心
大众网
今晚报
中国新闻网
广西新闻网
辽宁日报
黑龙江卫健委网站
河北日报
黑龙江省齐齐哈尔市疾控中心
黑龙江省黑河市政府新闻办
北京青年报
上海本地宝
松原发布
光明网
望奎县新冠肺炎疫情防控工作指挥部
北京市疾病预防控制中心
哈尔滨市人民政府网站
安达市疾控中心
环球网
健康北京
浙江省卫健委
哈尔滨市疾病预防控制中心
黑龙江卫健委
央视新闻客户端
北京日报
大连发布
健康成都
央视新闻
津云
界面新闻
河北新闻网
齐齐哈尔市卫生健康委员会
北京都市
中国快讯
黑龙江省大庆市疾病预防控制中心
黑河市应对新型冠状病毒感染肺炎疫情工作领导小组指挥部
沈阳卫健委
人民日报
大连卫健委
吉林省卫生健康委
黑龙江省卫健委
四川日报

方法二:

SELECT DISTINCT "信息来源" FROM "病例基本信息";

结果同上:

image-20221021104107097

1-2 给出河南省、西藏自治区、台湾省的英文名称和人口数。

创建表"全国各省参考信息":

CREATE TABLE "全国各省参考信息" (
    "中文名称" VARCHAR(1024),
    "英文名称" VARCHAR(1024),
    "组合码" VARCHAR(1024),
    "人口数" BIGINT,
    "省会城市" VARCHAR(1024),
    "纬度" DECIMAL,
    "经度" DECIMAL
);

image-20221014091238939

image-20221014094848930

查询:

SELECT "中文名称", "英文名称", "人口数"
FROM "全国各省参考信息"
WHERE "中文名称" = '河南省'
    OR "中文名称" = '西藏自治区'
    OR "中文名称" = '台湾省';

结果:

image-20221014095103368

1-3 查询2021年1月20日各省现有确诊病例数据,按现有确诊病例数降序排列输出。

查询:现有确诊 = 累计确诊 - 累计治愈

select 省, 累计确诊-累计治愈 as 现有确诊
from 全国各省累计数据统计
where 日期='2021-01-20'
order by 现有确诊 desc

结果:

image-20221020002245938

1-4 顺义区中风险地区的数量。

创建表"全国城市风险等级":

CREATE TABLE "全国城市风险等级" (
    省 VARCHAR(1024),
    市 VARCHAR(1024),
    区 VARCHAR(1024),
    地址详情 VARCHAR(1024),
    风险等级 VARCHAR(1024)
);

image-20221014163622685

查询:

SELECT COUNT(*)
FROM "全国城市风险等级"
WHERE "区" = '顺义区'
    AND "风险等级" = '中风险地区';

结果:4

image-20221014164042111

1-5 计算截至2021年1月20日全国累计确诊病例数。

创建表"全国各省累计数据统计":

CREATE TABLE "全国各省累计数据统计" (
    日期 DATE,
    省 VARCHAR(1024),
    累计确诊 INT,
    累计治愈 INT,
    累计死亡 INT
);

image-20221014164540239

查询:

SELECT SUM("累计确诊")
FROM "全国各省累计数据统计"
WHERE "日期" = '2021/1/20';

结果:98545

image-20221014165427832

1-6 查询1005号病例确诊后,其所在市新增的所有确诊病例。

方法一:

注:由于不能确定与1005号病例同天确诊的病例是在1005号病例前或后确诊,所以仅查询在1005号确诊日期之后的病例。

SELECT "病例号"
FROM "病例基本信息"
WHERE 市 = (
        SELECT "市"
        FROM "病例基本信息"
        WHERE "病例号" = 1005
    )
    AND "日期" > (
        select 日期 
        from 病例基本信息 
        where 病例号 = 1005
    );

结果:982

image-20221020003009528

方法二:

select 病例号 
from 病例基本信息, (select 市, 日期 from 病例基本信息 where 病例号 = 1005) as 目标病人
where 病例基本信息.市 = 目标病人.市 and 病例基本信息.日期>目标病人.日期

结果:982

image-20221021105753000

1-7 在“病例基本信息表”中查询石家庄市在2021年1月11日当天以及之前的所有60岁以上的患者信息。

查询:

SELECT "患者信息"
FROM "病例基本信息"
WHERE "日期" <= '2021/1/11'
    AND "年龄" > 60
    AND "市" = '石家庄市';

结果:

image-20221021101146687

患者信息
1 女,65岁,井陉县秋树坡村人。
2 确诊病例6:女,78岁,藁城区增村镇南桥寨村人。
3 确诊病例8:女,75岁,藁城区增村镇小果庄村人。
4 确诊病例9:女,68岁,藁城区增村镇小果庄村人。
5 确诊病例11:女,77岁,藁城区增村镇南桥寨村人。
6 确诊病例12:男,70岁,藁城区增村镇南桥寨村人。
7 确诊病例16:女,67岁,藁城区增村镇南桥寨村人。
8 确诊病例20:女,68岁,藁城区增村镇小果庄村人,现住长安区丰收路红珊湾小区。
9 确诊病例22:女,68岁,藁城区增村镇南桥寨村人。
10 确诊病例23:女,70岁,藁城区增村镇北桥寨村人。
11 确诊病例24:女,73岁,藁城区常安镇北楼村人。
12 确诊病例26:女,64岁,藁城区增村镇小果庄村人。
13 确诊病例28:男,66岁,藁城区增村镇小果庄村人。在村中开办诊所。
14 确诊病例39:女,71岁,新乐市马头铺镇张家庄村人。
15 确诊病例40:男,67岁,藁城区增村镇刘家佐村人。
16 确诊病例46:男,66岁,藁城区增村镇小果庄村人。
17 确诊病例47:女,68岁,藁城区增村镇南桥寨村人。
18 确诊病例48:男,64岁,藁城区增村镇南桥寨村人。
19 确诊病例52:男,67岁,藁城区增村镇杨马村人。
20 确诊病例53:女,78岁,藁城区增村镇南桥寨村人。
21 确诊病例58:女,63岁,藁城区增村镇小果庄村人。
22 确诊病例61:男,67岁,藁城区增村镇东桥寨村人。
23 确诊病例64:女,72岁,藁城区增村镇南桥寨村人。
24 确诊病例66:女,85岁,藁城区增村镇南桥寨村人。
25 确诊病例67:女,68岁,藁城区增村镇南桥寨村人。
26 确诊病例68:男,70岁,新乐市协神乡牛家庄村人。
27 确诊病例69:女,67岁,藁城区增村镇小果庄村人。
28 确诊病例71:男,72岁,藁城区岗上镇杜村人。
29 确诊病例74:女,62岁,新乐市长寿街道东长寿村人,村内经营棋牌室。
30 确诊病例1:女,69岁,藁城区增村镇小果庄村人。
31 确诊病例4:女,65岁,藁城区增村镇南桥寨村人。
32 确诊病例10:女,64岁,藁城区张家庄镇鲍家庄村人。
33 确诊病例11:女,62岁,藁城区增村镇小果庄村人。
34 确诊病例16:女,82岁,藁城区增村镇小果庄村人。
35 确诊病例19:男,61岁,藁城区增村镇东桥寨村人。
36 确诊病例29:女,71岁,藁城区增村镇小果庄村人。
37 确诊病例30:男,62岁,新乐市长寿街道东长寿村人。
38 确诊病例33:男,68岁,藁城区增村镇小果庄村人。
39 确诊病例35:女,71岁,藁城区增村镇杨马村人。
40 确诊病例39:男,79岁,新乐市马头铺镇张家庄村人,是第38号确诊病例的父亲。
41 确诊病例4:女,70岁,藁城区增村镇小果庄村人。
42 确诊病例5:男,68岁,现住鹿泉区上庄镇观峰嘉邸小区。
43 确诊病例11:女,65岁,藁城区增村镇牛家庄村人。
44 女,66岁,藁城区增村镇小果庄村人。
45 女,66岁,藁城区增村镇小果庄村人。
46 女,86岁,藁城区增村镇小果庄村人。
47 男,75岁,新乐市邯邰镇坚固村人。
48 女,72岁,藁城区增村镇刘家佐村人。
49 女,62岁,藁城区增村镇小果庄村人。
50 女,65岁,藁城区增村镇刘家佐村人。
1-8 统计截止到2020年12月30日美国累计确诊病例数最多的10个州。

创建表"美国各州县确诊与死亡数统计":

CREATE TABLE "美国各州县确诊与死亡数统计" (
    日期 DATE,
    州 VARCHAR(1024),
    县 VARCHAR(1024),
    累计确诊 INT,
    累计死亡 INT
);

image-20221014171433007

查询:

SELECT "州"
FROM "美国各州县确诊与死亡数统计"
WHERE "日期" = '2020/12/30'
group by 州
ORDER BY sum(累计确诊) DESC 
LIMIT 10;

结果:

1 California
2 Texas
3 Florida
4 New York
5 Illinois
6 Ohio
7 Georgia
8 Pennsylvania
9 Tennessee
10 North Carolina

image-20221020003501183

1-9 统计截至2021年1月20号中国发病率最高的人群(人群按照年龄划分,儿童<18,18<=青壮年<60,老年>=60)。

方法一:

select 人群 from 
(SELECT  CASE 
WHEN 年龄<18 THEN '儿童'
WHEN 年龄>=18 and 年龄 <60 THEN '青壮年'
WHEN 年龄>60 THEN '老年' 
ELSE '年龄未知'
END AS 人群
FROM 病例基本信息
WHERE 日期 <= '2021-1-20')
group by 人群
order by count(*) desc
limit 1

结果:青壮年

image-20221020140801541

方法二:使用三次子查询,分别在where中实现年龄段的划分。每次子查询得到包含“数量”与“年龄段”两列的一条记录,用union将三次次查询的结果集合并。最后用“数量”字段倒序排序,得到发病率最高的“年龄段”

注:由于不知道每个年龄段的总人数,因此本题中的发病率仅按发病人数统计

SELECT 总表.年龄段 FROM 
((SELECT COUNT(*) as 数量, '儿童' as 年龄段
FROM "病例基本信息"
WHERE "日期" <= '2021/1/20'
AND "年龄" < 18) UNION
(SELECT COUNT(*) as 数量, '青壮年' as 年龄段
FROM "病例基本信息"
WHERE "日期" <= '2021/1/20'
AND "年龄" >= 18 AND "年龄" < 60) UNION
(SELECT COUNT(*) as 数量, '老年' as 年龄段
FROM "病例基本信息"
WHERE "日期" <= '2021/1/20'
AND "年龄" >= 60))as 总表
order by 数量 desc
limit 1

结果:青壮年

image-20221021101320732

2-1 借助病例行程信息粗略查询曾去过“源升品质生活坊”的所有患者的基本信息。

创建表"病例行程信息":

CREATE TABLE 病例行程信息 (
    行程号 INT,
    病例号 INT,
    日期信息 DATE,
    行程信息 varchar(1024)
);

image-20221014184528987

查询:使用like模糊查询。使用where-in查询属于子查询结果集的病例。

注:假设题目中的患者基本信息的含义是病例基本信息的”患者信息“字段

SELECT 患者信息 FROM 病例基本信息 WHERE 病例号 IN (SELECT 病例号 FROM 病例行程信息 WHERE 行程信息 LIKE '%源升品质生活坊%');

结果:

image-20221021102725332

患者信息
1 确诊病例2:女,1948年出生
2 无症状感染者9:女,1945年出生
3 无症状感染者13:男,1943年出生
4 无症状感染者3:男,1976年出生
2-2 根据病例行程信息表和病例基本信息表,查询行程信息中存在“家庭聚餐”的病例被确诊的日期。

查询:

select 日期 from 病例基本信息 where 病例号 in (select 病例号 from 病例行程信息 where 行程信息 like '%家庭聚餐%')

结果:

日期
1 2021-01-13
2 2021-01-13
3 2021-01-05
4 2021-01-04

image-20221019105815591

2-3 对比中美两国累计确诊病例数,输出格式为(日期,中国累计确诊,美国累计确诊)。

创建表"各国疫情数据统计":

create table 各国疫情数据统计 (
    日期  date,
    国家  varchar(1024),
    省州  varchar(1024),
    累计确诊    int,
    累计治愈    int,
    累计死亡    int
)

image-20221019110937673

查询:使用natual join,按照日期合并两个子查询的结果

-- 思路
create view 美国 as select 日期, sum(累计确诊) from 各国疫情数据统计 where 国家='US' group by 日期

create view 中国 as select 日期, sum(累计确诊) from 全国各省累计数据统计 group by 日期


-- 最终的sql
select 日期, 中国累计确诊, 美国累计确诊 
from (select 日期, sum(累计确诊) as 美国累计确诊 from 各国疫情数据统计 where 国家='US' group by 日期) as 美国
natural join (select 日期, sum(累计确诊) as 中国累计确诊 from 全国各省累计数据统计 group by 日期) as 中国 
order by 日期

结果:

image-20221021103012968

日期 中国累计确诊 美国累计确诊
1 2020-11-22 92117 12347905
2 2020-11-23 92212 12521898
3 2020-11-24 92297 12697001
4 2020-11-25 92403 12879677
5 2020-11-26 92489 12991818
6 2020-11-27 92587 13199752
7 2020-11-28 92682 13355018
8 2020-11-29 92815 13495104
9 2020-11-30 92903 13655220
10 2020-12-01 92994 13842652
11 2020-12-02 93114 14044625
12 2020-12-03 93222 14267421
13 2020-12-04 93349 14499637
14 2020-12-05 93468 14714853
15 2020-12-06 93578 14895691
16 2020-12-07 93671 15089952
17 2020-12-08 93783 15313819
18 2020-12-09 93899 15535463
19 2020-12-10 94026 15766130
20 2020-12-11 94125 16004659
21 2020-12-12 94218 16221125
22 2020-12-13 94329 16408428
23 2020-12-14 94428 16601499
24 2020-12-15 94538 16810792
25 2020-12-16 94627 17056440
26 2020-12-17 94735 17295460
27 2020-12-18 94822 17546621
28 2020-12-19 94954 17738237
29 2020-12-20 95051 17925587
30 2020-12-21 95151 18123968
31 2020-12-22 95229 18321157
32 2020-12-23 95299 18550002
33 2020-12-24 95384 18743916
34 2020-12-25 95461 18841414
35 2020-12-26 95542 19067574
36 2020-12-27 95633 19222891
37 2020-12-28 95721 19396787
38 2020-12-29 95798 19595354
39 2020-12-30 95877 19827770
40 2020-12-31 95964 20061903
41 2021-01-01 96024 20215297
42 2021-01-02 96087 20515083
43 2021-01-03 96161 20723157
44 2021-01-04 96247 20906563
45 2021-01-05 96311 21140217
46 2021-01-06 96399 21394210
47 2021-01-07 96485 21671278
48 2021-01-08 96563 21963383
49 2021-01-09 96691 22225720
50 2021-01-10 96825 22438881
51 2021-01-11 96921 22653070
52 2021-01-12 97096 22879069
53 2021-01-13 97276 23108826
54 2021-01-14 97449 23343727
55 2021-01-15 97617 23584671
56 2021-01-16 97776 23785679
57 2021-01-17 97940 23962788
58 2021-01-18 98165 24105873
59 2021-01-19 98324 24281531
60 2021-01-20 98545 24464045
2-4 计算截止到2021年1月20日,美国有些县的累计确诊是同一个州的其他县的2倍或以上,列出这些县,以及他们所在的州和他们的累计确诊。

查询:子查询查出每个州最低的累计确诊数,用natual join将子查询的结果表与“美国各州县确诊与死亡数统计”表链接,则可以得到“美国各州县确诊与死亡数统计”表中每个县所在州的最低确诊人数。筛选累计确诊 >= 最低累计确诊*2即可。

注:只要一个州中有一个县的累计确诊为0,这个州中所有的县都会进入结果集。观察到表中存在如下的记录:

日期 累计确诊 累计死亡
2020/11/22 New Mexico Out of NM 0 0
2020/11/22 Oregon Unassigned 0 0
…… …… …… …… ……

所以应当在子查询中排除掉累计确诊为0的记录。

select 县, 州, 累计确诊 
from 美国各州县确诊与死亡数统计 
natural join (select 州, min(累计确诊) as 最低累计确诊 from 美国各州县确诊与死亡数统计 where 累计确诊 != 0 group by 州) 
where 累计确诊 >= 最低累计确诊*2

结果:

image-20221021113142741

2-5 计算世界上人口数排名前10位的国家地区。

创建表"参考信息":

create table 参考信息 (
    组合码 varchar(1024),
    国家  varchar(1024),
    省州  varchar(1024),
    市县  varchar(1024),
    纬度  decimal,
    经度  decimal,
    人口数 int8
);

alter table 参考信息 modify 人口数 bigint;

image-20221019150940621

查询:从“参考信息”表中查询除中国以外的国家的人口数,从“全国各省参考信息”表中查询中国人口数,每个子查询得到“国家”、“人口数”两列信息,用union合并两个子查询的结果集。

注:

  1. 由于“参考信息”表中有两个虚构的国家,用人口数 is not null将这两个国家排除掉。
  2. 经过观察,“参考信息”表中,若一个省州有多个市县,则会有一行统计省州的总人口;若一个国家有多个省州,则会有一行统计国家的总人口。因此不能按照group by 国家来处理,应该按照组合码 = 国家来处理。

查询:

select 国家, 人口数 
from (select 国家, 人口数 as 人口数 from 参考信息 where 人口数 is not null and 组合码 = 国家)
union (select 'China' as 国家, sum(人口数) as 人口数 from 全国各省参考信息) 
order by 人口数 desc 
limit 10

结果:

image-20221021140401336

国家 人口数
1 China 1428493105
2 India 1380004385
3 US 329466283
4 Indonesia 273523621
5 Pakistan 220892331
6 Brazil 212559409
7 Nigeria 206139587
8 Bangladesh 164689383
9 Russia 145934460
10 Mexico 127792286
2-6 列出美国人口超千万的大州中,截至2021年1月20日新冠肺炎疫情死亡率超过2%的州。

思路:

create view 美国大州 as
select 省州 as 州, sum(人口数) as 人口数 from 参考信息 where 国家='US' group by 省州 having sum(人口数)>=10000000

create view 州累计死亡 as
select 州, sum(累计死亡) as 累计死亡 from 美国各州县确诊与死亡数统计 where 日期 <='2021/1/20' group by 州

select 州 from 州累计死亡 natural join 美国大州 where 累计死亡>(人口数*0.02)

最终的sql语句:

select 州
from (select 州, sum(累计死亡) as 累计死亡 from 美国各州县确诊与死亡数统计 where 日期 <='2021/1/20' group by 州) as 州累计死亡
natural join (select 省州 as 州, sum(人口数) as 人口数 from 参考信息 where 国家='US' group by 省州 having sum(人口数)>=10000000) as 美国大州
where 累计死亡>(人口数*0.02)

结果:

image-20221019161246516

1 Massachusetts
2 Wisconsin
3 Ohio
4 South Carolina
5 Georgia
6 New Jersey
7 Florida
8 Maryland
9 Pennsylvania
10 Tennessee
11 New York
12 Indiana
13 Minnesota
14 Colorado
15 Arizona
16 Texas
17 Michigan
18 Missouri
19 Illinois
2-7 截至2021年1月20日,河北省哪些区出现了新冠确诊病例但不属于中高风险地区。

解决思路:

select distinct 区 from 病例基本信息 where 省='河北省' and 日期<='2021/1/20' and 区 is not null -- 这一天前所有出现病例的河北省的区

select distinct 区 from 全国城市风险等级 where 省='河北省' -- 中高风险区

image-20221019170040134

最终的sql语句:

select A.区 
from (select distinct 区 from 病例基本信息 where 省='河北省' and 日期<='2021/1/20' and 区 != 'null') as A
left join(select distinct 区 from 全国城市风险等级 where 省='河北省') as B 
on A.区 = B.区
where B.区 is null

结果:

image-20221019165958005

1 栾城
2 井陉县
3 裕华市
4 正定
2-8 在病例行程信息表的基础上根据病例基本信息表,查询河北省病例的全部信息。

查询:由于题目要求在病例行程信息表的基础上,所以使用natural left outer join

select * from 病例行程信息 natural left outer join 病例基本信息 where 省 = '河北省'

结果:

image-20221020005155019

由于信息过多,仅列出几条供参考

病例号 行程号 日期信息 行程信息 日期 性别 年龄 患者信息 其它信息 信息来源
282 116 755 2020年12月26日至27日 居家无外出 河北省 石家庄市 藁城区 2021-01-18 14 确诊病例32:男,14岁,藁城区增村镇增村人。 暂无 河北日报
283 116 756 12月28日 下午乘坐511路公交车(从增村站到刘家佐北站)到藁城区第七中学 河北省 石家庄市 藁城区 2021-01-18 14 确诊病例32:男,14岁,藁城区增村镇增村人。 暂无 河北日报
284 116 757 1月15日 诊断为无症状感染者 河北省 石家庄市 藁城区 2021-01-18 14 确诊病例32:男,14岁,藁城区增村镇增村人。 暂无 河北日报
3-1 查询披露的确诊患者信息中年龄最大的患者,输出其基本信息。(未注明年龄的患者不进行比较)。

由于我不太理解题目中输出其基本信息是指输出病例基本信息中的全部信息,还是”患者信息”一列,所以都做了查询。

查询一:假设题目中所述基本信息代表“病例基本信息”表中的全部属性

select * from 病例基本信息 where 年龄 = (select max(年龄) from 病例基本信息 where 年龄 is not null)

结果:

image-20221019172211000

1103
辽宁省
沈阳市
皇姑区
2020-12-29
女
95
张某某,女,95岁
退休人员,家庭住址为沈阳市皇姑区昆山西路塔湾新城小区。
沈阳卫健委

查询二:假设题目中所述基本信息代表“病例基本信息”表中的“患者信息”

select 患者信息 from 病例基本信息 where 年龄 = (select max(年龄) from 病例基本信息)

结果:张某某,女,95岁

image-20221019172315727

3-2 查询2020年12月份新增确诊患者最多的城市。

思路:

select 省, 累计确诊 from 全国各省累计数据统计 where 日期='2020-12-31' as A

select 省, 累计确诊 from 全国各省累计数据统计 where 日期='2020-11-30' as B

select A.省, A.累计确诊-B.累计确诊 from A join B on A.省 = B.省 order by A.累计确诊-B.累计确诊 desc limit 1

最终的sql语句:

select A.省, A.累计确诊-B.累计确诊 as 新增确诊 
from (select 省, 累计确诊 from 全国各省累计数据统计 where 日期='2020-12-31') as A 
join (select 省, 累计确诊 from 全国各省累计数据统计 where 日期='2020-11-30') as B 
on A.省 = B.省
order by A.累计确诊-B.累计确诊 desc
limit 1

结果:香港特别行政区

image-20221019174321442

3-3 结合“全国各省参考信息表”和“病例基本信息表”给出没有新增确诊病例或未披露病例信息的省份。
-- 思路
select 省 from 病例基本信息 group by 省 having 省 is not null as A

select 中文名称 from 全国各省参考信息 
left join A
on 全国各省参考信息.中文名称 = A.省
where A.省 is NULL

-- 最终的sql语句
select 中文名称 from 全国各省参考信息 
left join (select 省 from 病例基本信息 group by 省 having 省 is not null) as A
on 全国各省参考信息.中文名称 = A.省
where A.省 is NULL

结果:

image-20221021120357565

中文名称
1 安徽省
2 重庆市
3 福建省
4 甘肃省
5 广东省
6 广西省
7 贵州省
8 海南省
9 河南省
10 湖北省
11 湖南省
12 江苏省
13 江西省
14 宁夏省
15 青海省
16 陕西省
17 台湾省
18 西藏自治区
19 新疆维吾尔族自治区
20 云南省
21 浙江省
22 香港特别行政区
23 澳门特别行政区
3-4 2021年1月20日全国中高风险地区所在省中,哪些省在1月20日没有新增确诊信息披露。

查询:最后使用distinct来去重

select distinct 全国城市风险等级.省 
from 全国城市风险等级 
left join (select 省 from 病例基本信息 where 日期='2021-01-20' group by 省) as A 
on 全国城市风险等级.省 = A.省
where A.省 is null

结果:

image-20221019185513100

1 吉林省
2 辽宁省
3-5 根据病例基本信息表查询一月份国内新增患者病例最多的城市。

方法一:新增 = 最高新增

```sql
-- 思路
select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市 as A

select max(新增) as 最高新增 from (select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市) as B

select 市 from A where 新增 = 最高新增

-- 最终的sql语句
select 市
from (select 市, count(<em>) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市)
where 新增 = (select max(新增) as 最高新增 from (select 市, count(</em>) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市))

-- 使用with简化
with subtable as (select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市)
select 市
from subtable
where 新增 = (select max(新增) as 最高新增 from subtable)

```

结果:石家庄市(截图分别为不使用with与使用with)

image-20221019201527917

image-20221021142203460

方法二:新增 >= all

select 市 
from (select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市)
where 新增 >= all(select count(*) from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市)

结果:石家庄市

image-20221021132410391

方法三(简单但效率较低):排序取第一

select 市, count(*) as 新增 from 病例基本信息 where 日期>='2021-1-1' and 日期<'2021-2-1' group by 市 order by 新增 desc limit 1

结果:石家庄市

image-20221019201824990

3-6 查询除中美两国以外的其余国家中,进入2021年以来单日新增确诊病例始终不低于一万例的国家。

查询:select 国家, 日期, sum(累计确诊) from 各国疫情数据统计 group by 国家,日期查询每个国家每一天的累计确诊。为了得到单日新增,将这个子查询复制两份,分别为A表与B表,按照国家做连接。A.日期-B.日期 = 1的含义是把A表当做每一天,B表当做每一天的前一天,按照国家分组后,A.sum(累计确诊) - B.sum(累计确诊)即可得到每天的新增,min(A.sum - B.sum) >= 10000即可筛选得到单日新增不低于一万的国家。

select A.国家
from (select 国家, 日期, sum(累计确诊) from 各国疫情数据统计 group by 国家,日期) as A 
join (select 国家, 日期, sum(累计确诊) from 各国疫情数据统计 group by 国家,日期) as B 
on A.国家 = B.国家 and A.日期-B.日期 = 1 
where A.国家 != 'US' and A.日期 >= '2021-01-01'
group by A.国家
having min(A.sum - B.sum) >= 10000

-- 使用with简化
with subtable as (select 国家, 日期, sum(累计确诊) from 各国疫情数据统计 group by 国家,日期)
select A.国家
from subtable as A join subtable as B 
on A.国家 = B.国家 and A.日期-B.日期 = 1 
where A.国家 != 'US' and A.日期 >= '2021-01-01'
group by A.国家
having min(A.sum - B.sum) >= 10000

结果:(截图分别为不使用with与使用with)

image-20221020143022516

image-20221021141727575

国家
1 Russia
2 United Kingdom
3 Brazil