大数据第52天—Mysql练习题12道之七-图书管理数据库-杨大伟
现有图书管理数据库的三个数据模型如下:
图书(数据表名:BOOK)
序号 |
字段名称 |
字段描述 |
字段类型 |
1 |
BOOK_ID |
总编号 |
文本 |
2 |
SORT |
分类号 |
文本 |
3 |
BOOK_NAME |
书名 |
文本 |
4 |
WRITER |
作者 |
文本 |
5 |
OUTPUT |
出版单位 |
文本 |
6 |
PRICE |
单价 |
数值(保留小数点后2位) |
读者(数据表名:READER)
序号 |
字段名称 |
字段描述 |
字段类型 |
1 |
READER_ID |
借书证号 |
文本 |
2 |
COMPANY |
单位 |
文本 |
3 |
NAME |
姓名 |
文本 |
4 |
SEX |
性别 |
文本 |
5 |
GRADE |
职称 |
文本 |
6 |
ADDR |
地址 |
文本 |
借阅记录(数据表名:BORROW LOG)
序号 |
字段名称 |
字段描述 |
字段类型 |
1 |
READER_ID |
借书证号 |
文本 |
2 |
BOOK_D |
总编号 |
文本 |
3 |
BORROW_ATE |
借书日期 |
日期 |
(1)创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。
(2)找出姓李的读者姓名(NAME)和所在单位(COMPANY)。
(3)查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE),结果按单价降序排序。
(4)查找价格介于10元和20元之间的图书种类(SORT)出版单位(OUTPUT)和单价(PRICE),结果按出版单位(OUTPUT)和单价(PRICE)升序排序。
(5)查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。
(6)求”科学出版社”图书的最高单价、最低单价、平均单价。
(7)找出当前至少借阅了2本图书(大于等于2本)的读者姓名及其所在单位。
(8)考虑到数据安全的需要,需定时将“借阅记录”中数据进行备份,请使用一条SQL语句,在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK.井且将“借阅记录”中现有数据全部复制到BORROW_1.0G_ BAK中。
(9)现在需要将原Oracle数据库中数据迁移至Hive仓库,请写出“图书”在Hive中的建表语句(Hive实现,提示:列分隔符|;数据表数据需要外部导入:分区分别以month_part、day_part 命名)
(10)Hive中有表A,现在需要将表A的月分区 201505 中 user_id为20000的user_dinner字段更新为bonc8920,其他用户user_dinner字段数据不变,请列出更新的方法步骤。(Hive实现,提示:Hlive中无update语法,请通过其他办法进行数据更新)
1 --(1)创建图书管理库的图书、读者和借阅三个基本表的表结构。请写出建表语句。 2 --图书 3 create table test_seven_BOOK 4 ( 5 BOOK_ID String COMMENT '总编号', 6 SORT String COMMENT '分类号', 7 BOOK_NAME String COMMENT '书名', 8 WRITER String COMMENT '作者', 9 OUTPUT String COMMENT '出版单位', 10 PRICE decimal(10,2) COMMENT '单价' 11 ) 12 row format delimited fields terminated by '\t'; 13 14 --读者 15 create table test_seven_READER 16 ( 17 READER_ID String COMMENT '借书证号', 18 COMPANY String COMMENT '单位', 19 NAME String COMMENT '姓名', 20 SEX String COMMENT '性别', 21 GRADE String COMMENT '职称', 22 ADDR String COMMENT '地址' 23 ) 24 row format delimited fields terminated by '\t'; 25 26 --借阅记录 27 create table test_seven_BORROW_LOG 28 ( 29 READER_ID String COMMENT '借书证号', 30 BOOK_D String COMMENT '总编号', 31 BORROW_ATE date COMMENT '借书日期' 32 ) 33 row format delimited fields terminated by '\t';
1 -- 插入数据 2 insert into table test_seven_book values ('1001','A1','Java','James Gosling','sun','11'); 3 insert into table test_seven_book values ('1002','A2','linux','Linus Benedict Torvalds','sun','22'); 4 insert into table test_seven_book values ('1003','A3','Java3','James Gosling3','sun3','33'); 5 insert into table test_seven_book values ('1004','A4','Java4','James Gosling4','sun4','44'); 6 insert into table test_seven_book values ('1005','B1','Java5','James Gosling5','sun','55'); 7 insert into table test_seven_book values ('1006','C1','Java6','James Gosling6','sun5','66'); 8 insert into table test_seven_book values ('1007','D1','Java7','James Gosling7','sun6','77'); 9 insert into table test_seven_book values ('1008','E1','Java8','James Gosling4','sun3','88'); 10 insert into table test_seven_reader values ('7','buu',decode(binary('李大帅'),'utf-8'),'man','lay1','beijing4'); 11 insert into table test_seven_reader values ('2','buu2','苏大强','man','lay2','beijing2'); 12 insert into table test_seven_reader values ('3','buu2','李二胖','woman','lay3','beijing3'); 13 insert into table test_seven_reader values ('4','buu3','王三涛','man','lay4','beijing4'); 14 insert into table test_seven_reader values ('5','buu4','刘四虎','woman','lay5','beijing1'); 15 insert into table test_seven_reader values ('6','buu','宋冬野','woman','lay6','beijing5'); 16 insert into table test_seven_borrow_log values ('1','1002','2019-06-01'); 17 insert into table test_seven_borrow_log values ('1','1003','2019-06-02'); 18 insert into table test_seven_borrow_log values ('1','1006','2019-06-03'); 19 insert into table test_seven_borrow_log values ('2','1001','2019-06-04'); 20 insert into table test_seven_borrow_log values ('3','1002','2019-06-05'); 21 insert into table test_seven_borrow_log values ('4','1005','2019-06-06'); 22 insert into table test_seven_borrow_log values ('5','1003','2019-06-06'); 23 insert into table test_seven_borrow_log values ('3','1006','2019-06-07'); 24 insert into table test_seven_borrow_log values ('2','1003','2019-06-03'); 25 insert into table test_seven_borrow_log values ('3','1008','2019-06-03'); 26 insert into table test_seven_borrow_log values ('1','1002','2019-06-04');
1 --(2)找出姓李的读者姓名(NAME)和所在单位(COMPANY)。 2 select name,company from test_seven_reader where name like '李%'; 3 --(3)查找“高等教育出版社”的所有图书名称(BOOK_NAME)及单价(PRICE),结果按单价降序排序。 4 select BOOK_NAME,PRICE from test_seven_book order by PRICE desc; 5 --(4)查找价格介于10元和20元之间的图书种类(SORT)出版单位(OUTPUT)和单价(PRICE),结果按出版单位(OUTPUT)和单价(PRICE)升序排序。 6 select SORT,OUTPUT,PRICE from test_seven_book where PRICE between 10 and 20 order by OUTPUT,PRICE asc; 7 8 --(5)查找所有借了书的读者的姓名(NAME)及所在单位(COMPANY)。 9 select 10 rd.name, 11 rd.COMPANY 12 from 13 ( 14 select 15 READER_ID 16 from test_seven_borrow_log 17 group by READER_ID 18 ) t1 19 join 20 test_seven_reader rd 21 on t1.READER_ID = rd.READER_ID; 22 --(6)求”科学出版社”图书的最高单价、最低单价、平均单价。 23 select 24 max(PRICE) max, 25 min(PRICE) min, 26 avg(PRICE) avg 27 from 28 test_seven_book; 29 --(7)找出当前至少借阅了2本图书(大于等于2本)的读者姓名及其所在单位。 30 select 31 rd.READER_ID, 32 rd.name, 33 rd.COMPANY 34 from 35 ( 36 select 37 READER_ID, 38 count(*) num 39 from test_seven_BORROW_LOG 40 group by READER_ID 41 having count(*) >= 2 42 ) t1 43 join 44 test_seven_reader rd 45 on t1.READER_ID = rd.READER_ID; 46 47 --(8)考虑到数据安全的需要,需定时将“借阅记录”中数据进行备份, 48 -- 请使用一条SQL语句,在备份用户bak下创建与“借阅记录”表结构完全一致的数据表BORROW_LOG_BAK. 49 --井且将“借阅记录”中现有数据全部复制到BORROW_l0G_BAK中。 50 create table BORROW_LOG_BAK 51 ( 52 READER_ID String COMMENT '借书证号', 53 BOOK_D String COMMENT '总编号', 54 BORROW_ATE date COMMENT '借书日期' 55 ) 56 as select * from test_seven_BORROW_LOG; 57 58 --(9)现在需要将原Oracle数据库中数据迁移至Hive仓库, 59 --请写出“图书”在Hive中的建表语句(Hive实现,提示:列分隔符|;数据表数据需要外部导入:分区分别以month_part、day_part 命名) 60 create table test_seven_book_oracle ( 61 book_id string COMMENT '总编号', 62 sort string COMMENT '分类号', 63 book_name string COMMENT '书名', 64 writer string COMMENT '作者', 65 output string COMMENT '出版单位', 66 price decimal(10,2) COMMENT '单价' 67 ) 68 PARTITIONED BY (month string,day string) 69 row format delimited fields terminated by '|'; 70 71 --(10)Hive中有表A,现在需要将表A的月分区 201505 中 user_id为20000的user_dinner字段更新为bonc8920, 72 -- 其他用户user_dinner字段数据不变,请列出更新的方法步骤。 73 --(Hive实现,提示:Hlive中无update语法,请通过其他办法进行数据更新) 74 create table tmp_A as select * from A where user_id<>20000 and month_part=201505; 75 insert into table tmp_A partition(month_part=’201505’) values(20000,其他字段,bonc8920); 76 insert overwrite table A partition(month_part=’201505’) select * from tmp_A where month_part=201505;