hive 去掉字符串第一个字符

转载

码海航行侠 2024-10-05 14:25:05

文章标签 hive 去掉字符串第一个字符 mysql DML 基础查询数据 文章分类 Hive 大数据

1. 写在前面

最近需要用到HQL语句做一些数据分析的工作，又发现了自己的短板， HQL这个东西用起来并不是很流畅，虽然建表，查询的基本语句还能应付(这还是吃的大学的SQL基础老本)，但涉及到一些复杂的查询等就不行了，每次都得谷歌，但谷歌完了并没有留下记录，记不住，导致下一次再用到还得再继续谷歌，这中间就浪费了很多搜索的时间。效率非常低。所以就想再突击一遍SQL, 之所以复杂的查询不会，是因为之前的SQL基础忘光了，而基于hive的查询和SQL语句实在是太像了，并且来到公司之后发现，不管是开发还是算法， SQL应该都是必备技能了。所以借着这个机会，重新走一遍SQL，这次的参考资料是Datawhale的组队学习资料SQL基础教程学习笔记。真的是非常感谢组织伙伴们的辛苦整理和分享，接下来，就大约用两天的时间，快速过一遍，而这个系列，就是整理在这个过程中常用到的一些操作和知识点。这样，以后再用到别的高级知识的时候，就有相应的地方整理啦，也方便回看回练。学习新知识的时候，先俯瞰下整个森林，再走近森林，去看树木，这样不容易迷失 😉

今天是第二篇，整理常用的DML(Data Manipulation Language, 数据操纵语言), 用来查询或者变更表中的记录，主要包括

INSERT: 向表中插入新数据
UPDATE: 更新表中的数据
SELECT: 查询表中的数据
DELETE: 删除表中的数据

首先，是往表中插入数据的语法。

2. 插入数据

基本语法：

INSERT INTO <表名> (列1, 列2, 列3, ……) VALUES (值1, 值2, 值3, ……);

对表进行全列 INSERT 时，可以省略表名后的列清单。这时 VALUES子句的值会默认按照从左到右的顺序赋给每一列。

-- 包含列清单
INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
-- 省略列清单
INSERT INTO productins VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

多行插入：

-- 多行INSERT （ DB2、SQL、SQL Server、 PostgreSQL 和 MySQL多行插入）
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
                              ('0003', '运动T恤', '衣服', 4000, 2800, NULL),
                              ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。想要插入 NULL 的列一定不能设置 NOT NULL 约束。

INSERT INTO productins (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');

还可以向表中插入默认值（初始值）。可以通过在创建表的CREATE TABLE 语句中设置DEFAULT约束来设定默认值。

CREATE TABLE productins
(product_id CHAR(4) NOT NULL,
（略）
sale_price INTEGER
（略）	DEFAULT 0, -- 销售单价的默认值设定为0;
PRIMARY KEY (product_id));

可以使用INSERT … SELECT 语句从其他表复制数据。

-- 将商品表中的数据复制到商品复制表中
INSERT INTO productcopy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
  FROM Product;

3. 数据的更新

基本语法：

UPDATE <表名>
   SET <列名> = <表达式> [, <列名2>=<表达式2>...];  
 WHERE <条件>;  -- 可选，非常重要。
 ORDER BY 子句;  --可选
 LIMIT 子句; --可选

使用 update 时要注意添加 where 条件，否则将会将所有的行按照语句修改。
例子：

-- 修改所有的注册时间
UPDATE product
   SET regist_date = '2009-10-10';  
-- 仅修改部分商品的单价
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = '厨房用具';

使用 UPDATE 也可以将列更新为 NULL（该更新俗称为NULL清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。

-- 将商品编号为0008的数据（圆珠笔）的登记日期更新为NULL  
UPDATE product
   SET regist_date = NULL
 WHERE product_id = '0008';

和 INSERT 语句一样， UPDATE 语句也可以将 NULL 作为一个值来使用。 但是，只有未设置 NOT NULL 约束和主键约束的列才可以清空为NULL。如果将设置了上述约束的列更新为 NULL，就会出错，这点与INSERT 语句相同。

UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

-- 合并后的写法
UPDATE product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
       .....
 WHERE product_type = '厨房用具';

3. SELEST查询语句(巨重要)

3.1 基础查询语句规则

通过SELECT语句查询并选取必要数据的过程称为匹配或查询。常规语句：

SELECT <列名>, ……
  FROM <表名>
 WHERE <条件表达式>;

3.2 运算符

3.2.1 算术运算符和比较运算符

算术运算符： +、-、*、/ 比较运算符: =、<>、>=、>、<=、<

常用法则：

SELECT子句中可以使用常数或者表达式。
使用比较运算符时一定要注意不等号和等号的位置。
字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

代码相关例子：

-- SQL语句中也可以使用运算表达式
SELECT product_name, sale_price, sale_price * 2 AS "sale_price x2"
  FROM product;

-- WHERE子句的条件表达式中也可以使用计算表达式
SELECT product_name, sale_price, purchase_price
  FROM product
 WHERE sale_price-purchase_price >= 500;

/* 对字符串使用不等号
首先创建chars并插入数据
选取出大于‘2’的SELECT语句*/
-- DDL：创建表
CREATE TABLE chars
（chr CHAR（3）NOT NULL, 
PRIMARY KEY（chr））;

-- 选取出大于'2'的数据的SELECT语句('2'为字符串)
SELECT chr
  FROM chars
 WHERE chr > '2';

-- 选取NULL的记录
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price IS NULL;

-- 选取不为NULL的记录
SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price IS NOT NULL;

3.2.2 逻辑运算符

NOT运算符：想要表示“不是……”时，除了前文的<>运算符外，还存在另外一个表示否定、使用范围更广的运算符：NOT。 NOT不能单独使用：

-- 选取出销售单价大于等于1000日元的记录
SELECT product_name, product_type, sale_price
  FROM product
 WHERE sale_price >= 1000;
-- 向代码清单2-30的查询条件中添加NOT运算符
SELECT product_name, product_type, sale_price
  FROM product
 WHERE NOT sale_price >= 1000;

同时使用多个查询条件：

AND：并且
OR：或者

-- “商品种类为办公用品”并且“登记日期是 2009 年 9 月 11 日或者 2009 年 9 月 20 日” 理想结果为“打孔器”
-- 通过使用括号让OR运算符先于AND运算符执行
SELECT product_name, product_type, regist_date
  FROM product
 WHERE product_type = '办公用品'
   AND ( regist_date = '2009-09-11'
        OR regist_date = '2009-09-20');

4. 中级查询

4.1 聚合查询

SQL中用于汇总的函数叫做聚合函数。以下五个是最常用的聚合函数：

COUNT：计算表中的记录数（行数）
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值

例子：

-- 计算全部数据的行数（包含NULL）
SELECT COUNT(*)
  FROM product;

-- 计算NULL以外数据的行数
SELECT COUNT(purchase_price)
  FROM product;

-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price) 
  FROM product;

-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
  FROM product;

-- MAX和MIN也可用于非数值型数据
SELECT MAX(regist_date), MIN(regist_date)
  FROM product;

使用聚合函数删除重复值

-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
 FROM product;
-- 是否使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
 FROM product;

常用法则：

COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。
聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL。
MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
在聚合函数的参数中使用DISTINCT，可以删除重复数据。

4.2 分组查询

用于分组汇总的场景：

SELECT <列名1>,<列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

例子：

-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;

在 GROUP BY 子句中指定的列称为聚合键或者分组列。聚合键包含NULL时，此时会将NULL作为一组特殊数据进行处理。

GROUP BY的子句书写顺序有严格要求，不按要求会导致SQL无法正常执行，目前出现过的子句顺序为：

1 SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

其中前三项用于筛选数据，GROUP BY对筛选出的数据进行处理。

常见错误：

在聚合函数的SELECT子句中写了聚合健以外的列
使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键），下面这个会报错：

-- 不含GROUP BY
SELECT product_type, COUNT(*)
FROM product;

在GROUP BY子句中使用列的别名
SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。执行顺序 $hive 去掉字符串第一个字符_hive 去掉字符串第一个字符$ , group by的时候，还没有具体拿到数据，根本不知道别名的存在。
在WHERE中使用聚合函数
原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

为聚合结果指定条件 - HAVING得到特定分组

WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件（例如，“数据行数为 2 行”或者“平均值为 500”等），如果想指定组的条件，此时可以用HAVING。

HAVING子句用于对分组进行过滤，可以使用数字、聚合函数和GROUP BY中指定的列名（聚合键）。

-- 数字
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING COUNT(*) = 2;

-- 错误形式（因为product_name不包含在GROUP BY聚合键中）
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
HAVING product_name = '圆珠笔';

4.3 排序

查询结果排序使用order by

SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 ORDER BY <排序基准列1>, <排序基准列2>, ……  # 这个句子一般放到最后面

默认为升序排列，降序排列为DESC

-- 降序排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price DESC;
 
-- 多个排序键
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY sale_price, product_id;

-- 当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。
SELECT product_id, product_name, sale_price, purchase_price
  FROM product
 ORDER BY purchase_price;

ORDER BY中列名可使用别名，而GROUP BY中不行，为啥？

这是因为SQL在使用 HAVING 子句时 SELECT 语句的顺序为：新知识

$hive 去掉字符串第一个字符_数据_02$

其中SELECT的执行顺序在 GROUP BY 子句之后，ORDER BY 子句之前。也就是说，当在ORDER BY中使用别名时，已经知道了SELECT设置的别名存在，但是在GROUP BY中使用别名时还不知道别名的存在，所以不能在ORDER BY中可以使用别名，但是在GROUP BY中不能使用别名。

5. DELETE删除

DELETE 语句删除记录：

DELETE FROM 表名称 WHERE 列名称 = 值

可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

DELETE FROM table_name

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：Android 修改屏幕分辨率默认为479

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯