MySQL工作常用语句

目录

1.REGEXP_REPLACE、REGEXP_EXTRACT、REGEXP_EXTRACT_ALL用法与区别

2.COLLECT_LIST函数

3.FROM_JSON函数

1.REGEXP_REPLACE、REGEXP_EXTRACT、REGEXP_EXTRACT_ALL用法与区别

REGEXP_REPLACE是一种用于在字符串中搜索并替换正则表达式模式的函数。它的语法如下：

REGEXP_REPLACE(string, pattern, replacement)

其中，string是要搜索的字符串，pattern是要匹配的正则表达式模式，replacement是要替换匹配的部分的字符串。

例如，下面的语句将在字符串"Hello world!"中查找单词"world"并将其替换为"SQL"：

SELECT REGEXP_REPLACE('Hello world!', 'world', 'SQL');

结果为："Hello SQL!"

例如，下面的语句将所有的数字替换为"#"：

SELECT REGEXP_REPLACE('123abc456', '[0-9]', '#');

结果为："###abc###"

REGEXP_EXTRACT_ALL和REGEXP_EXTRACT函数都是正则表达式函数，但它们的功能略有不同。

REGEXP_EXTRACT函数用于从一个字符串中提取匹配一个正则表达式的子串。它只返回匹配到的第一个子串，如果没有匹配到，则返回空字符串。语法如下：

REGEXP_EXTRACT(string, pattern[, index])

其中，string表示要匹配的字符串，pattern表示要匹配的正则表达式，index表示要提取的子串在匹配到的子串列表中的索引，默认为1。

REGEXP_EXTRACT_ALL函数用于从一个字符串中提取匹配一个正则表达式的所有子串，并将它们存储在一个数组中。语法如下：

REGEXP_EXTRACT_ALL(string, pattern)

其中，string表示要匹配的字符串，pattern表示要匹配的正则表达式。

举个例子，假设我们有一个字符串"abc123def456ghi789"，现在想要提取其中所有的数字。使用REGEXP_EXTRACT函数，我们可以这样写：

SELECT REGEXP_EXTRACT('abc123def456ghi789', '\d+');

执行结果为"123"，也就是只提取了第一个匹配的数字。而使用REGEXP_EXTRACT_ALL函数，则可以这样写：

SELECT REGEXP_EXTRACT_ALL('abc123def456ghi789', '\d+');

执行结果为一个包含所有匹配到的数字的数组，即["123", "456", "789"]。

因此，二者的区别在于返回值的形式不同：REGEXP_EXTRACT函数返回一个字符串，而REGEXP_EXTRACT_ALL函数返回一个数组。

2.COLLECT_LIST函数

COLLECT_LIST是Hive中的一个聚合函数，可以将一个列中的所有值收集到一个数组中。

使用方法如下：

SELECT COLLECT_LIST(column_name) FROM table_name;

其中，column_name是需要进行操作的列的名称，table_name是需要进行操作的表的名称。

例如，我们有一个表employee，列名为name和age，需要将name列中的所有值收集到一个数组中，可以执行以下命令：

SELECT COLLECT_LIST(name) FROM employee;

执行结果将返回一个包含name列中所有值的数组。

3.FROM_JSON函数

FROM_JSON函数是SQL Server 2016引入的新功能，它可以将JSON格式的数据转换为关系型数据。FROM_JSON函数接受一个JSON字符串作为参数，并将其转换为类似表格的形式，以便进行查询、过滤和操作。

SELECT  FROM_JSON(stringJson,'struct<name:String,age:String,address:struct<postcode:bigint,city:String>>')
FROM    tablename

输出结果是把stringJson参照struct的格式进行json序列化，取值方式如下

SELECT  FROM_JSON(stringJson,'struct<name:String,age:String,address:struct<postcode:bigint,city:String>>').name AS name
,FROM_JSON(stringJson,'struct<name:String,age:String,address:struct<postcode:bigint,city:String>>').address.postcode AS postcode
FROM    tablename