Hive3：常用查询语句整理

一、数据准备

建库

CREATE DATABASE itheima;
USE itheima;

订单表

CREATE TABLE itheima.orders (orderId bigint COMMENT '订单id',orderNo string COMMENT '订单编号',shopId bigint COMMENT '门店id',userId bigint COMMENT '用户id',orderStatus tinyint COMMENT '订单状态 -3:用户拒收 -2:未付款的订单 -1：用户取消 0:待发货 1:配送中 2:用户确认收货',goodsMoney double COMMENT '商品金额',deliverMoney double COMMENT '运费',totalMoney double COMMENT '订单金额（包括运费）',realTotalMoney double COMMENT '实际订单金额（折扣后金额）',payType tinyint COMMENT '支付方式,0:未知;1:支付宝，2：微信;3、现金；4、其他',isPay tinyint COMMENT '是否支付 0:未支付 1:已支付',userName string COMMENT '收件人姓名',userAddress string COMMENT '收件人地址',userPhone string COMMENT '收件人电话',createTime timestamp COMMENT '下单时间',payTime timestamp COMMENT '支付时间',totalPayFee int COMMENT '总支付金额'
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';LOAD DATA LOCAL INPATH '/home/atguigu/itheima_orders.txt' INTO TABLE itheima.orders;

用户表

CREATE TABLE itheima.users (userId int,loginName string,loginSecret int,loginPwd string,userSex tinyint,userName string,trueName string,brithday date,userPhoto string,userQQ string,userPhone string,userScore int,userTotalScore int,userFrom tinyint,userMoney double,lockMoney double,createTime timestamp,payPwd string,rechargeMoney double
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';LOAD DATA LOCAL INPATH '/home/atguigu/itheima_users.txt' INTO TABLE itheima.users;

二、基本查询

查询数据量

SELECT COUNT(*) FROM itheima.orders;

过滤广东省订单

SELECT * FROM itheima.orders WHERE useraddress LIKE '%广东%';

找出广东省单笔营业额最大的订单

SELECT * FROM itheima.orders WHERE useraddress like '%广东%' ORDER BY totalmoney DESC LIMIT 1;

三、分组、聚合

统计未支付、已支付各自的人数

SELECT ispay, COUNT(*) AS cnt FROM itheima.orders GROUP BY ispay;

在已付款订单中，统计每个用户最高的一笔消费金额

SELECT userid, MAX(totalmoney) AS max_money FROM itheima.orders WHERE ispay = 1 GROUP BY userid;

统计每个用户的平均订单消费额

SELECT userid, AVG(totalmoney) FROM itheima.orders GROUP BY userid;

统计每个用户的平均订单消费额，过滤大于10000的数据

SELECT userid, AVG(totalmoney) AS avg_money FROM itheima.orders GROUP BY userid HAVING avg_money > 10000;

四、JOIN

JOIN订单表和用户表，找出用户名

SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o JOIN itheima.users u ON o.userid = u.userid;

左外关联，订单表和用户表，找出用户名

SELECT o.orderid, o.userid, u.username, o.totalmoney, o.useraddress, o.paytime FROM itheima.orders o LEFT JOIN itheima.users u ON o.userid = u.userid;

五、UNIO

去重联合的查询结果

SELECT * FROM course
UNION 
SELECT * FROM course

不去重联合的查询结果

SELECT * FROM courseUNION ALL 
SELECT * FROM course

六、RLIKE正则查询

常用匹配规则
在这里插入图片描述

-- 查找广东省数据
SELECT * FROM itheima.orders WHERE useraddress RLIKE '.*广东.*';
-- 查找用户地址是：xx省 xx市 xx区
SELECT * FROM itheima.orders WHERE useraddress RLIKE '..省 ..市 ..区';
-- 查找用户姓为：张、王、邓
SELECT * FROM itheima.orders WHERE username RLIKE '[张王邓]\\S+';
-- 查找手机号符合：188****0*** 规则
SELECT * FROM itheima.orders WHERE userphone RLIKE '188\\S{4}0[0-9]{3}';