SQL、MySQL、Hive&Presto的使用区别ing_会mysql就会hivesql吗

1 查询结果前10条数据

【SQL】

SELECT TOP 10 * FROM TB1;
1

【MySQL】

SELECT * FROM TB LIMIT 10
select * from info limit 3 offset 2;	-- 从位置2开始，向后获取前3数据  3,4,5
1
2

• 注意：limit不能参与计算，否则会报错
  【HiveSQL】

SELECT * FROM TB LIMIT 10
1

【Oracle】

select * from tbl where rownum<=10;
1

2 返回是周几的函数

【SQL】

SELECT datepart(wk,order_time) FROM TB1;
-- 国际标准每周日是每星期的第一天，周六则是每星期的最后一天,星期五则返回6
1
2

【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;
-- 星期一是星期的第一天
1
2
3
4
5
6
7
8
9
10

【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
1
2
3
4
5

3 查看表结构数量

【SQL】

-- 查看系统内所有数据库
SELECT name, database_id, create_date  FROM sys.databases  ;
-- 查询数据库内所有表
select * from sysobjects where xtype= 'U'  ;
-- 显示表结构
sp_help/sp_columns 表名;
1
2
3
4
5
6

【MySQL】

-- 查看系统内所有数据库
show databases；
-- 查询数据库内所有表
show tables;
-- 显示表结构
desc 表名;
1
2
3
4
5
6

4 获取当前时间

【SQL】

getdate()
1

【MySQL】

now()
1

5 从数据库定位到某张表

【SQL】
库名.dbo.表名
库名…表名

select password from Info.dbo.users where userName='boss'
-- 或者
select password from Info..users where userName='boss'
1
2
3

【MySQL】
库名.表名

select password from Info.users where userName='boss'
1

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;
1
2

9 HiveSQL不支持等值连接以及相应的多表连接

【SQL】

select * from dual a,dual b where a.key = b.key;
1

【MySQL】

SELECT
	*
FROM
	teacher_table t
LEFT JOIN student_table s ON t.id = s.teacherId;
--  左连接查询的结果是：两个表满足连接条件的记录以及左表的剩余记录，其中左表的剩余记录中属于左表的字段的值为实际的值，属于右表的字段的值为null，注意不是对应字段的默认值。
1
2
3
4
5
6

【HiveSQL】

select * from dual a join dual b on a.key = b.key;
1

10 判断表是否存在，如果存在删除的方法不同

【SQL】

IF (
	SELECT
		COUNT (1)
	FROM
		sysobjects
	WHERE
		name = 'teacher_table'
) = 1 DROP TABLE teacher_table ;
1
2
3
4
5
6
7
8

【MySQL】

DROP TABLE
IF EXISTS `teacher_table`;
1
2

11 创建表时自增长的标识字段不同

1. mysql中用AUTO_INCREMENT，sqlserver中用IDENTITY。
2. .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;
1
2
3
4
5
6

【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)
);
1
2
3
4
5
6

12 MySQL可以使用单引号与双引号,而SQLServer只支持单引号

13 都不严格区分大小写

14 正则表达式

[\u4e00-\u9fa5]匹配所有的中文字符
【presto】

1. regexp_extract_all（字符串，模式）->数组（varchar ）由正则表达式匹配的返回（一个或多个）pattern 中string：

SELECT regexp_extract_all('1a 2b 14m', '\d+'); -- [1, 2, 14]
1

2. 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'
1
2

3. regexp_like（字符串，模式） →布尔值:计算正则表达式pattern并确定它是否包含在中string。

SELECT regexp_like('1a 2b 14m', '\d+b'); -- true
1

4. regexp_replace（字符串，模式） →varchar pattern从中删除与正则表达式匹配的子字符串的每个实例 string：

SELECT regexp_replace('1a 2b 14m', '\d+[ab] '); -- '14m'
1

5. regexp_split（字符串，模式）->数组（varchar ）:string使用正则表达式拆分pattern并返回一个数组。尾随的空字符串被保留：

SELECT regexp_split('1a 2b 14m', '\s*[a-z]+\s*'); -- [1, 2, 14, ]
1

-- 【表达违规率-长度违规】仅允许整数或者小数，小数点前最大长度：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})?$')
1
2

6. 【表达违规率-内容违规】不能是纯数字和符号的组合，应有含义说明
7. 在对字段进行重命名的过程中，不能使用中文,并且重命名不能加引号（all of this）
8. 在进行除法运算时，不能分子和分母都是整形，至少有一个是浮点型。解决办法：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
1

【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 '小白';
1
2

第一条的查询结果是name值等于’小白‘的数据，而第二条的查询结果是name中包含’小白’的数据，也可以用like（模糊查询+通配符）来实现regexp查询结果。

• like：模糊搜索
• 已用于数量少的搜索，数据量大的时候需要用组件

select 字段 from 表 where 某字段 like 条件
1

1. % 模式：表示任意0个或者多个字符。可以匹配任意类型和长度的字符，有些情况下如果是中文，则使用两个百分号（%%）表示

select * from info where name like "武%";-- 匹配姓氏：武所有的个案
1

2. _ 模式：表示任意单个字符，匹配单个单一字符，它常用来限制表达式的字符长度语句：

select * from info where email like "__peiqi@live.com"; -- 匹配所有以peiqi@live.com结尾的邮箱
select * from info where email like "__peiqi_live.co_"
1
2

3. 模式：表示括号内所列字符的一个（类似正则表达式）。指定一个字符、字符串或者范围，要求[]对象为它们中的一个

select * from student where s_name like '[ABG]X'; 
-- 找出学生姓名为：AX、BX、GX的所有信息
1
2

4. [^ ] :表示不在括号所列之内的单个字符，取值和[ ]相同，但它要求所匹配的对象为指定字符以外的任意一个字符。

select * from student where s_name like '[^ABG]X'; 
-- 找出学生姓名不是：AX、BX、GX的所有信息
1
2

• 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'
1
2
3
4
5
6
7

元字符表：

select * from t1 where name regexp '[0-9]*';--匹配名字包含或者不包含数字的记录
1

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"}
1
2
3
4
5
6
7

【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]');
1
2
3
4
5
6

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
1
2
3
4

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】

1. date_add(“date”,-1,时间)
2. now()
3. date_diff(“date”,开始时间，结束时间)
4. array_join不能再hive中使用
5. 不支持date_sub
6. 不支持to_date

【presto】

1. date_add(时间，-1)
2. current_date()
3. date_diff(结束时间，开始时间)
4. array_join
5. date_sub