数据库实训

一

任务描述

湖南人口hnpeople数据表结构如图所示，各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人)

本关任务： 1.在湖南人口数据表(hnpeople)中增加老龄比例llbl字段 decimal类型,长度为5 小数位数为2.

 alter table hnpeople add llbl decimal(5,2);

二

任务描述

湖南人口hnpeople数据表如下所示

各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人) 本关任务： 4.删除数据记录 删除hnpeople数据表中城市cs为长沙市的数据记录

delete from hnpeople where cs='长沙市';

三

任务描述

打开人口普查rkpc数据库，湖南人口hnpeople数据表结构如图所示，各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人)

本关任务： 将数据表字段人口rk改名为人口数量rksl字段,数据类型仍为整形int。

 alter table hnpeople change rk rksl int;

四 

任务描述

湖南人口hnpeople数据表如下所示

各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人) 本关任务： 3.修改湖南人口数据记录： 修改老年比例lnbl字段的值为老年old除以人口rk，再乘以100

UPDATE hnpeople

SET llbl = (old / rk) * 100;

五

任务描述

湖南人口hnpeople数据表如下所示

各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人) 本关任务： 5.查询城市cs为长沙市和株洲市，人口(rk)在500000到1000000之间的城市cs、区县qx、人口rk字段的信息，按人口从高到低排列

select cs,qx,rk from hnpeople where ((cs='长沙市' or cs='株洲市')and( rk>500000 and rk<1000000)) order by rk desc;

六 

任务描述

湖南人口hnpeople数据表如下所示

各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人)

本关任务： 6.查询湖南人口hnpeople数据表中各城市的名称(cs)、总人口zrk、老年比率llbv(老年人数量/所有人数量乘以100)和性别比xbb(男性人口/女性人口)。注意：老年比率、性别比需要保留两位小数。按总人口降序排列。

select cs,sum(rk) as zrk,

round(sum(old)/sum(rk)*100,2) as llbv,

round(sum(man)/sum(woman),2) as xbb

from hnpeople group by cs order by zrk desc;

七 

任务描述

本关任务： 人口普查rkpc数据库中hnpeople和hnyzbm两个数据表 湖南人口hnpeople数据表有城市cs、区县qx、人口rk等字段 数据如下所示

湖南邮政编码hnyzbm数据表有城市cs、区县qx、区县编码qxbm字段如下所示

本关任务： 1.连接两个数据表，查询长沙市的区县qx、区县编码qxbm和人口rk三个字段的值。

 select a.qx,b.qxbm,a.rk from hnpeople a,hnyzbm b where (a.qx=b.qx and a.cs=b.cs and a.cs='长沙市');

八 

任务描述

本关任务： 湖南人口hnpeople数据表有城市cs、区县qx、人口rk等字段 数据如下所示

本关任务： 8.查询hnpeople数据表中，人口多于区县平均人口的城市、区县和人口，按人口降序排列。

 select cs,qx,rk from hnpeople where rk>(select avg(rk) from hnpeople) order by rk desc; 

九 

任务描述

湖南人口hnpeople数据表结构如图所示，各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人) hnpeople数据表结构

本关任务： 9.在湖南人口数据表(hnpeople)中根据城市和区县两个字段建立主索引csqx。

alter table hnpeople add primary key csqx (cs,qx); 

十 

任务描述

湖南人口hnpeople数据表如下所示

各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人) 本关任务： 10.建立视图view1，显示区县人口大于100w的城市cs、区县qx和人口rk。 修改view1,将浏阳市的人口修改为1200000.

update hnpeople set rk=1200000 where qx='浏阳市';

CREATE VIEW view1 as

select cs,qx,rk

from hnpeople

where rk>1000000;

十一 

任务描述

湖南人口hnpeople数据表如下所示

各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人) 本关任务：

12.建立视图view2，显示城市总人口大于500w的城市cs和总人口zrk，按总人口的降序排列。

create view view2 as

select cs, sum(rk) as zrk

from hnpeople

group by cs

having sum(rk)>5000000 order by zrk desc;

十二 

任务描述

湖南人口hnpeople数据表结构如图所示，各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人)

本关任务： 12.建立用户user1,可以在所有机器上查询hnpeople数据表的所有字段。

create user 'user1'@'%';

grant select on rkpc.hnpeople to 'user1'@'%';

十三 

任务描述

湖南人口hnpeople数据表如下所示

各字段含义如下 cs（城市)、qx(区县)、rk(人口)、man(男)、woman(女)、child(儿童)、adult(成人)、old(老人) 本关任务： 13.建立存储过程xbcx，输入城市名称，返回该城市的男性人口百分比，女性人口百分比，要求保留两位小数。 调用该存储过程，将结果赋给变量@lx和@nx

delimiter //

create procedure xbcx(in city_name varchar(20), out lx decimal(5,2), out nx decimal(5,2))

begin

    -- 计算男性人口百分比

    select sum(man) * 100.0 / sum(man + woman) into lx

    from hnpeople

    where cs = city_name;

    -- 计算女性人口百分比

    select sum(woman) * 100.0 / sum(man + woman) into nx

    from hnpeople

    where cs = city_name;

end //

delimiter ;

call xbcx('长沙市', @lx, @nx);

十四 

任务描述

数据库人口普查rkpc有数据表qgrk,有省份sf、名称name、人口rk字段，数据如下所示

有数据表qgqh,有省份sf、城市city、区县qx、代码dm字段，数据如下所示

本关任务： 建立存储过程sfcx(in sfmc varchar(15)) 根据输入的省份名称，建立数据表sfxx,将该省的省份名称sf、城市city、区县qx\代码dm、人口rk各字段的信息存入到数据表数据表sfxx。

delimiter //

create procedure sfcx(in sfmc varchar(15))

begin

    -- 首先，创建新表sfxx，如果表不存在的话

    create table if not exists sfxx (

                                      sf varchar(15),

                                      cs varchar(50),

                                      qx varchar(50),

                                      dm varchar(50),

                                      rk int

                                  );

    -- 接着，向新表中插入数据

    insert into sfxx (sf, cs, qx, dm, rk)

    select qgrk.sf, qgqh.cs, qgqh.qx, qgqh.dm, qgrk.rk

    from qgrk

    join qgqh on qgrk.sf = qgqh.sf

    where qgrk.sf = '湖南省' and qgrk.name=qgqh.qx;

end //

delimiter ;