(1)
-- 创建图书表book
CREATE TABLE test_sql.book(book_id string,
`SORT` string,
book_name string,
writer string,
OUTPUT string,
price decimal(10,2));
INSERT INTO TABLE test_sql.book VALUES ('001','TP391','信息处理','author1','机械工业出版社','20');
INSERT INTO TABLE test_sql.book VALUES ('002','TP392','数据库','author12','科学出版社','15');
INSERT INTO TABLE test_sql.book VALUES ('003','TP393','计算机网络','author3','机械工业出版社','29');
INSERT INTO TABLE test_sql.book VALUES ('004','TP399','微机原理','author4','科学出版社','39');
INSERT INTO TABLE test_sql.book VALUES ('005','C931','管理信息系统','author5','机械工业出版社','40');
INSERT INTO TABLE test_sql.book VALUES ('006','C932','运筹学','author6','科学出版社','55');
-- 创建读者表reader
CREATE TABLE test_sql.reader (reader_id string,
company string,
name string,
sex string,
grade string,
addr string);
INSERT INTO TABLE test_sql.reader VALUES ('0001','阿里巴巴','jack','男','vp','addr1');
INSERT INTO TABLE test_sql.reader VALUES ('0002','百度','robin','男','vp','addr2');
INSERT INTO TABLE test_sql.reader VALUES ('0003','腾讯','tony','男','vp','addr3');
INSERT INTO TABLE test_sql.reader VALUES ('0004','京东','jasper','男','cfo','addr4');
INSERT INTO TABLE test_sql.reader VALUES ('0005','网易','zhangsan','女','ceo','addr5');
INSERT INTO TABLE test_sql.reader VALUES ('0006','搜狐','lisi','女','ceo','addr6');
-- 创建借阅记录表borrow_log
CREATE TABLE test_sql.borrow_log(reader_id string,
book_id string,
borrow_date string);
INSERT INTO TABLE test_sql.borrow_log VALUES ('0001','002','2019-10-14');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0002','001','2019-10-13');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0003','005','2019-09-14');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0004','006','2019-08-15');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0005','003','2019-10-10');
INSERT INTO TABLE test_sql.borrow_log VALUES ('0006','004','2019-17-13');
(2)
SELECT name,
company
FROM test_sql.reader
WHERE name LIKE '李%';
(3)
SELECT book_name,
price
FROM test_sql.book
WHERE OUTPUT = "高等教育出版社"
ORDER BY price DESC;
(4)
SELECT sort,
output,
price
FROM test_sql.book
WHERE price >= 10 and price <= 20
ORDER BY output,price ;
(5)
SELECT b.name,
b.company
FROM test_sql.borrow_log a
JOIN test_sql.reader b ON a.reader_id = b.reader_id;
(6)
SELECT max(price),
min(price),
avg(price)
FROM test_sql.book
WHERE OUTPUT = '科学出版社';
(7)
SELECT b.name,
b.company
FROM
(SELECT reader_id
FROM test_sql.borrow_log
GROUP BY reader_id
HAVING count(*) >= 2) a
JOIN test_sql.reader b ON a.reader_id = b.reader_id;
(8)
CREATE TABLE test_sql.borrow_log_bak AS
SELECT *
FROM test_sql.borrow_log;
(9)
CREATE TABLE book_hive (
book_id string,
SORT string,
book_name string,
writer string,
OUTPUT string,
price DECIMAL ( 10, 2 ) )
partitioned BY ( month_part string, day_part string )
ROW format delimited FIELDS TERMINATED BY '\\|' stored AS textfile;
(10)
方式1：配置hive支持事务操作，分桶表，orc存储格式
方式2：第一步找到要更新的数据，将要更改的字段替换为新的值，第二步找到不需要更新的数据，第三步将上两步的数据插入一张新表中。
|