SQL、MySQL与HiveSQL的使用区别
- 1 查询结果前10条数据
- 2 返回是周几的函数
- 3 查看表结构数量
- 4 获取当前时间
- 5 从数据库定位到某张表
- 6 分号字符；
- 7 IS [NOT] NULL
- 8 Hive不支持将数据插入现有的表或分区中,仅支持重写或覆盖：insert、update、delete
- 9 HiveSQL不支持等值连接以及相应的多表连接
- 10 判断表是否存在，如果存在删除的方法不同
- 11 创建表时自增长的标识字段不同
- 12 MySQL可以使用单引号与双引号,而SQLServer只支持单引号
- 13 都不严格区分大小写
- 14 正则表达式
- 15 [解析json中的字段值](https://blog.csdn.net/lzxlfly/article/details/108552894)
- 16 复杂数据类型转换-一行转多行
- 17 select 除法注意事项
- 18 引入时间参数
- 19 cast(string as )
- 20 字段名相关
- 21. 日期函数相关
1 查询结果前10条数据
【SQL】
SELECT TOP 10 * FROM TB1;
【MySQL】
SELECT * FROM TB LIMIT 10
select * from info limit 3 offset 2; -- 从位置2开始，向后获取前3数据 3,4,5
- 注意：limit不能参与计算，否则会报错
【HiveSQL】
SELECT * FROM TB LIMIT 10
【Oracle】
select * from tbl where rownum<=10;
2 返回是周几的函数
【SQL】
SELECT datepart(wk,order_time) FROM TB1;
-- 国际标准每周日是每星期的第一天，周六则是每星期的最后一天,星期五则返回6
【MySQL】
SELECT DATE_FORMAT(date,%W) FROM TB1;
-- 星期名字（Sunday，……，Saturday）
SELECT DATE_FORMAT(date,%w) FROM TB1;
-- 返回6表示Saturday
-- 一个星期中的天数(0=Sunday ……6=Saturday)
SELECT DATE_FORMAT(date,%U) FROM TB1;
-- 返回值：7 表示在周天是一周的第一天时，表示第几周
-- 星期天是星期的第一天
SELECT DATE_FORMAT(date,%u) FROM TB1;
-- 星期一是星期的第一天
【HiveSQL】
SELECT pmod(datediff（'2019-04-01','1920-01-01'）,-3,7) as week FROM TB;
-- '1920-01-01'周四
-- 计算'2019-04-01'时周几，返回值为“0-6”(“0-6”分别表示“星期日-星期六”）
-- 返回值为“1-7”(“1-7”分别表示“星期一-星期日”）
select IF(pmod(datediff('2019-04-01','1920-01-01')-3,7)='0',7,pmod(datediff('2019-04-01','1920-01-01')-3,7)) as week
3 查看表结构数量
【SQL】
-- 查看系统内所有数据库
SELECT name, database_id, create_date FROM sys.databases ;
-- 查询数据库内所有表
select * from sysobjects where xtype= 'U' ;
-- 显示表结构
sp_help/sp_columns 表名;
【MySQL】
-- 查看系统内所有数据库
show databases；
-- 查询数据库内所有表
show tables;
-- 显示表结构
desc 表名;
4 获取当前时间
【SQL】
getdate()
【MySQL】
now()
5 从数据库定位到某张表
【SQL】
库名.dbo.表名
库名…表名
select password from Info.dbo.users where userName='boss'
-- 或者
select password from Info..users where userName='boss'
【MySQL】
库名.表名
select password from Info.users where userName='boss'
6 分号字符；
- 分号是SQL语句结束标记，在HiveQL中也是，但是在HiveQL中，对分号的识别没有那么智慧，例如：
- select concat(key,concat(‘;’,key)) from dual;
- 但HiveQL在解析语句时提示：
FAILED: Parse Error: line 0:-1 mismatched input ‘’ expecting ) in function specification - 解决的办法是，使用分号的八进制的ASCII码进行转义，那么上述语句应写成：
- select concat(key,concat(‘\073’,key)) from dual;
7 IS [NOT] NULL
- SQL中null代表空值, 值得警惕的是, 在HiveQL中String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False。
8 Hive不支持将数据插入现有的表或分区中,仅支持重写或覆盖：insert、update、delete
【HiveSQL】
INSERT OVERWRITE TABLE t1
SELECT * FROM t2;
9 HiveSQL不支持等值连接以及相应的多表连接
【SQL】
select * from dual a,dual b where a.key = b.key;
【MySQL】
SELECT
*
FROM
teacher_table t
LEFT JOIN student_table s ON t.id = s.teacherId;
-- 左连接查询的结果是：两个表满足连接条件的记录以及左表的剩余记录，其中左表的剩余记录中属于左表的字段的值为实际的值，属于右表的字段的值为null，注意不是对应字段的默认值。
【HiveSQL】
select * from dual a join dual b on a.key = b.key;
10 判断表是否存在，如果存在删除的方法不同
【SQL】
IF (
SELECT
COUNT (1)
FROM
sysobjects
WHERE
name = 'teacher_table'
) = 1 DROP TABLE teacher_table ;
【MySQL】
DROP TABLE
IF EXISTS `teacher_table`;
11 创建表时自增长的标识字段不同
- mysql中用AUTO_INCREMENT，sqlserver中用IDENTITY。
- .mysql数据库在创建表时可以给 int 类型指定列宽，如int(11)，也可以不指定。而sqlserver数据库在创建表时则不能对 int 类型指定列宽。
【SQL】
CREATE TABLE `student_table` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`sName` VARCHAR (20) NOT NULL DEFAULT '学生名字',
`teacherId` INT (11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
【MySQL】
CREATE TABLE student_table (
id INT NOT NULL IDENTITY,
sName VARCHAR (20) NOT NULL DEFAULT '学生名字',
teacherId INT NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);
12 MySQL可以使用单引号与双引号,而SQLServer只支持单引号
13 都不严格区分大小写
14 正则表达式
[\u4e00-\u9fa5]匹配所有的中文字符
【presto】
- regexp_extract_all（字符串，模式）->数组（varchar ）由正则表达式匹配的返回（一个或多个）pattern 中string：
SELECT regexp_extract_all('1a 2b 14m', '\d+'); -- [1, 2, 14]
- regexp_extract（字符串，模式） →varchar:返回由正则表达式匹配的第一个字符串pattern 中string
regexp_extract（字符串，模式，组） →varchar 查找中出现的第一个正则表达式pattern， string并返回捕获组号 group：
SELECT regexp_extract('1a 2b 14m', '\d+'); -- 1
SELECT regexp_extract('1a 2b 14m', '(\d+)([a-z]+)', 2); -- 'a'
- regexp_like（字符串，模式） →布尔值:计算正则表达式pattern并确定它是否包含在中string。
SELECT regexp_like('1a 2b 14m', '\d+b'); -- true
- regexp_replace（字符串，模式） →varchar pattern从中删除与正则表达式匹配的子字符串的每个实例 string：
SELECT regexp_replace('1a 2b 14m', '\d+[ab] '); -- '14m'
- regexp_split（字符串，模式）->数组（varchar ）:string使用正则表达式拆分pattern并返回一个数组。尾随的空字符串被保留：
SELECT regexp_split('1a 2b 14m', '\s*[a-z]+\s*'); -- [1, 2, 14, ]
-- 【表达违规率-长度违规】仅允许整数或者小数，小数点前最大长度：3,小数点后最大长度：3
select distinct a.item_result from vital_signs_record cross join unnest(vital_sign_weight) as a where not regexp_like(a.item_result,'^[0-9]{1,3}(\.[0-9]{1,3})?$')
- 【表达违规率-内容违规】不能是纯数字和符号的组合，应有含义说明
- 在对字段进行重命名的过程中，不能使用中文,并且重命名不能加引号（all of this）
- 在进行除法运算时，不能分子和分母都是整形，至少有一个是浮点型。解决办法：double或decimal或者将一个数值乘以0.001
select count(end_time),count(1),(count(1)-count(end_time))*1.00/count(1) as rate from tbl_sug_infusion_items
【HIVE】
regexp_extract
语法: regexp_extract(string subject, string pattern, int index)
返回值: string
说明： 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
第一参数： 要处理的字段
第二参数: 需要匹配的正则表达式
第三个参数:0是显示与之匹配的整个字符串,1 是显示第一个括号里面的,2 是显示第二个括号里面的字段
【SQL】
REGEXP_LIKE(source_string, pattern[, match_parameter])
ource_string 支持字符数据类型（CHAR、VARCHAR2、CLOB、NCHAR、NVARCHAR2 和 NCLOB，但不包括 LONG）。pattern 参数是正则表达式的另一个名称。match_parameter 允许可选的参数（如处理换行符、保留多行格式化以及提供对区分大小写的控制）。
【MySQL】:
select * from stu_info where name like '小白';
select * from stu_info where name regexp '小白';
第一条的查询结果是name值等于’小白‘的数据，而第二条的查询结果是name中包含’小白’的数据，也可以用like（模糊查询+通配符）来实现regexp查询结果。
- like：模糊搜索
- 已用于数量少的搜索，数据量大的时候需要用组件
select 字段 from 表 where 某字段 like 条件
- % 模式：表示任意0个或者多个字符。可以匹配任意类型和长度的字符，有些情况下如果是中文，则使用两个百分号（%%）表示
select * from info where name like "武%";-- 匹配姓氏：武所有的个案
- _ 模式：表示任意单个字符，匹配单个单一字符，它常用来限制表达式的字符长度语句：
select * from info where email like "__peiqi@live.com"; -- 匹配所有以peiqi@live.com结尾的邮箱
select * from info where email like "__peiqi_live.co_"
- 模式：表示括号内所列字符的一个（类似正则表达式）。指定一个字符、字符串或者范围，要求[]对象为它们中的一个
select * from student where s_name like '[ABG]X';
-- 找出学生姓名为：AX、BX、GX的所有信息
- [^ ] :表示不在括号所列之内的单个字符，取值和[ ]相同，但它要求所匹配的对象为指定字符以外的任意一个字符。
select * from student where s_name like '[^ABG]X';
-- 找出学生姓名不是：AX、BX、GX的所有信息
- regexp：正则表达式
-- 包含所有数字，小写字母，大写字母的所有记录
select * from my_user where name regexp '[0-9a-zA-Z]';
-- 如果包含空格，直接写出即可
-- 查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB：
select patient_id,patient_name,conditions
from Patients
where conditions regexp ' DIAB1|^DIAB1'
元字符表：
select * from t1 where name regexp '[0-9]*';--匹配名字包含或者不包含数字的记录
15 解析json中的字段值
【presto】
json_extract_scalar(string json, string json_path) → varchar
json_extract(string json, string json_path) → json
-- 单层
select json_extract_scalar(json_str, '$.name');
-- 多层
select json_extract_scalar(json_str, '$.item.tabid');
-- get json数组
select json_extract(json_str, '$.item.ass_rule[0]');
-- {"lv1":"ass","lv2":"mpp"}
【HIVE】
get_json_object(string json_string, string path) → varchar
-- 单层
select get_json_object(json_str, '$.name');
-- 双层
select get_json_object(json_str, '$.item.tabid');
-- get_json数组
select get_json_object(json_str, '$.item.ass_rule[0]');
16 复杂数据类型转换-一行转多行
【Hive】
-- lateral view explode(split(复杂字段名,',')) myTable
select content_id, tag_id_list, tag_id
from content
lateral view explode(split(tag_id_list, ',')) myTable as tag_id
17 select 除法注意事项
【SQL】 对于SQL中select除法问题，需要通过cast转换为float,否则真是结果<1的情况下，直接为0
【MySql】：对应在MySQL中的除法不需要通过cast转换
18 引入时间参数
【HIVE】${begindate} ${enddate}
【Presto】不支持
19 cast(string as )
【HIVE】cast( string as string)
【Presto】cast( string as varchar)
20 字段名相关
【hive】 中文字段名 字段名 引用中文 a = “我是中国人”
【presto】 中文字段名 “字段名” 引用中文 a = ‘我是中国人’
21. 日期函数相关
【hive】
- date_add(“date”,-1,时间)
- now()
- date_diff(“date”,开始时间，结束时间)
- array_join不能再hive中使用
- 不支持date_sub
- 不支持to_date
【presto】
- date_add(时间，-1)
- current_date()
- date_diff(结束时间，开始时间)
- array_join
- date_sub
3925
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
