笔记

1、MaxCompute SQL 中，使用 not in 后面接子查询，子查询返回的结果是上万级别的数据量，但阿里云中 in 和 not in 后面的子查询返回数量是 1000，那么在 not in 必须使用的情况下，该如何实现此查询？

1、

--正确写法                      
SELECT  *
FROM    dbo.Table_A AS a
WHERE   a.ID NOT IN ( SELECT    b.ID
                      FROM      dbo.Table_B AS b
                      WHERE     b.ID IS NOT NULL ) --排除NULL值参与运算符比较
                      
--建议修改为关联查询方法                                            
--正确写法                   
SELECT  *
FROM    dbo.Table_A AS a
        LEFT OUTER JOIN dbo.Table_B AS b ON a.ID = b.ID
WHERE   b.ID IS NULL

2、select 中有case when的

SELECT a.id
    , a.xx
    , NULL
    , CASE 
        WHEN a.id IN (SELECT b.id
            FROM **** b) THEN 'A'
        ELSE 'B'
    END
FROM **** a
WHERE get_idcard_sex(a.id) = 'M'

改为

SELECT a.xx
    , a.xx
    , NULL
    , '7'
    , CASE 
        WHEN b.xx IS NULL THEN 'A'
        ELSE 'B'
    END
FROM **** a
LEFT JOIN **** b
ON a.id = b.id
WHERE get_idcard_sex(a.id) = 'M'

2、join or 代替方法

1、group by 方法

2、子查询方法

SELECT *
FROM (
    SELECT *
    FROM **** a
    LEFT OUTER JOIN **** b
    ON a.id1 = b.id
    WHERE b.id IS NULL
) c
LEFT OUTER JOIN **** d
ON c.id2 = d.id
WHERE d.id IS NULL

3、去重

SELECT *
FROM (
    SELECT *
        , ROW_NUMBER() OVER (PARTITION BY t.去重字段 ORDER BY t.去重字段 DESC) AS rn
    FROM xxx t
) p
WHERE p.rn = 1;

4、怎么查看数据库表中某个字段的值有哪些重复记录

SELECT *
FROM TableA
WHERE b IN (SELECT b
	FROM TableA
	GROUP BY b
	HAVING COUNT(b) > 1)

5、取表中某个日期查每年的数据量

SELECT SUBSTR(s.日期, 1, 4) AS ob
	, COUNT(DISTINCT s.主键)
FROM 表 s
WHERE SUBSTR(s.日期, 1, 4) >= '1800'
	AND SUBSTR(s.日期, 1, 4) <= '2019'
GROUP BY SUBSTR(s.日期, 1, 4)
ORDER BY ob

6、行转列，列转行

表1
id	name
1	a
2	b
3	c
4	d

表2
name	address
a	add1
a	add2
b	add3
c	add4
d	add5

表3
id	name	address
1	a	add1,add2
2	b	add3
3	c	add4
4	d	add5

表4
id	name	address
1	a	add1
1	a	add2
2	b	add3
3	c	add4
4	d	add5

1、表1和表2的数据转为表3

SELECT 表1.id
	, 表1.name
	, wm_concat(',', 表2.address) AS address
FROM 表1
JOIN 表2
ON 表1.name = 表2.name
GROUP BY 表1.id, 
	表1.name;

2、表3的数据转为表4

SELECT id
	, name
	, add_new
FROM 表3
	LATERAL VIEW EXPLODE(SPLIT(表3.address, ',')) adtable AS add_new;

7、查询某字段值重复的数据

SELECT
    result.accountid
FROM
    (
        SELECT
            accountid,
            count(*) AS count
        FROM
            vtiger_assetaccount
        GROUP BY
            accountid
        HAVING
            count > 1
    ) AS result
LEFT JOIN vtiger_assetaccount ON vtiger_assetaccount.accountid = result.accountid

　　注：如果查询全部字段值是否重复可以先进行union操作在做重复查询

8、正则表达式-校验数字，字符，特殊需求等

本文链接：https://blog.csdn.net/NextAction/article/details/80520606
一、校验数字的表达式
1 数字：^[0-9]*$
2 n位的数字：^\d{n}$
3 至少n位的数字：^\d{n,}$
4 m-n位的数字：^\d{m,n}$
5 零和非零开头的数字：^(0|[1-9][0-9]*)$
6 非零开头的最多带两位小数的数字：^([1-9][0-9]*)+(.[0-9]{1,2})?$
7 带1-2位小数的正数或负数：^(\-)?\d+(\.\d{1,2})?$
8 正数、负数、和小数：^(\-|\+)?\d+(\.\d+)?$
9 有两位小数的正实数：^[0-9]+(.[0-9]{2})?$
10 有1~3位小数的正实数：^[0-9]+(.[0-9]{1,3})?$
11 非零的正整数：^[1-9]\d*$ 或 ^([1-9][0-9]*){1,3}$ 或 ^\+?[1-9][0-9]*$
12 非零的负整数：^\-[1-9][]0-9"*$ 或 ^-[1-9]\d*$
13 非负整数：^\d+$ 或 ^[1-9]\d*|0$
14 非正整数：^-[1-9]\d*|0$ 或 ^((-\d+)|(0+))$
15 非负浮点数：^\d+(\.\d+)?$ 或 ^[1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0$
16 非正浮点数：^((-\d+(\.\d+)?)|(0+(\.0+)?))$ 或 ^(-([1-9]\d*\.\d*|0\.\d*[1-9]\d*))|0?\.0+|0$
17 正浮点数：^[1-9]\d*\.\d*|0\.\d*[1-9]\d*$ 或 ^(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*))$
18 负浮点数：^-([1-9]\d*\.\d*|0\.\d*[1-9]\d*)$ 或 ^(-(([0-9]+\.[0-9]*[1-9][0-9]*)|([0-9]*[1-9][0-9]*\.[0-9]+)|([0-9]*[1-9][0-9]*)))$
19 浮点数：^(-?\d+)(\.\d+)?$ 或 ^-?([1-9]\d*\.\d*|0\.\d*[1-9]\d*|0?\.0+|0)$

二、校验字符的表达式
1 汉字：^[\u4e00-\u9fa5]{0,}$
2 英文和数字：^[A-Za-z0-9]+$ 或 ^[A-Za-z0-9]{4,40}$
3 长度为3-20的所有字符：^.{3,20}$
4 由26个英文字母组成的字符串：^[A-Za-z]+$
5 由26个大写英文字母组成的字符串：^[A-Z]+$
6 由26个小写英文字母组成的字符串：^[a-z]+$
7 由数字和26个英文字母组成的字符串：^[A-Za-z0-9]+$
8 由数字、26个英文字母或者下划线组成的字符串：^\w+$ 或 ^\w{3,20}$
9 中文、英文、数字包括下划线：^[\u4E00-\u9FA5A-Za-z0-9_]+$
10 中文、英文、数字但不包括下划线等符号：^[\u4E00-\u9FA5A-Za-z0-9]+$ 或 ^[\u4E00-\u9FA5A-Za-z0-9]{2,20}$
11 可以输入含有^%&',;=?$\"等字符：[^%&',;=?$\x22]+
12 禁止输入含有~的字符：[^~\x22]+

三、特殊需求表达式
1 Email地址：^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$
2 域名：[a-zA-Z0-9][-a-zA-Z0-9]{0,62}(/.[a-zA-Z0-9][-a-zA-Z0-9]{0,62})+/.?
3 InternetURL：[a-zA-z]+://[^\s]* 或 ^http://([\w-]+\.)+[\w-]+(/[\w-./?%&=]*)?$
4 手机号码：^(13[0-9]|14[0-9]|15[0-9]|166|17[0-9]|18[0-9]|19[8|9])\d{8}$
5 电话号码("XXX-XXXXXXX"、"XXXX-XXXXXXXX"、"XXX-XXXXXXX"、"XXX-XXXXXXXX"、"XXXXXXX"和"XXXXXXXX)：^(\(\d{3,4}-)|\d{3.4}-)?\d{7,8}$
6 国内电话号码(0511-4405222、021-87888822)：\d{3}-\d{8}|\d{4}-\d{7}
7 18位身份证号码(数字、字母x结尾)：^((\d{18})|([0-9x]{18})|([0-9X]{18}))$
8 帐号是否合法(字母开头，允许5-16字节，允许字母数字下划线)：^[a-zA-Z][a-zA-Z0-9_]{4,15}$
9 密码(以字母开头，长度在6~18之间，只能包含字母、数字和下划线)：^[a-zA-Z]\w{5,17}$
10 强密码(必须包含大小写字母和数字的组合，不能使用特殊字符，长度在8-10之间)：^(?=.*\d)(?=.*[a-z])(?=.*[A-Z]).{8,10}$
11 日期格式：^\d{4}-\d{1,2}-\d{1,2}
12 一年的12个月(01～09和1～12)：^(0?[1-9]|1[0-2])$
13 一个月的31天(01～09和1～31)：^((0?[1-9])|((1|2)[0-9])|30|31)$
14 钱的输入格式：
15    1.有四种钱的表示形式我们可以接受:"10000.00" 和 "10,000.00", 和没有 "分" 的 "10000" 和 "10,000"：^[1-9][0-9]*$
16    2.这表示任意一个不以0开头的数字,但是,这也意味着一个字符"0"不通过,所以我们采用下面的形式：^(0|[1-9][0-9]*)$
17    3.一个0或者一个不以0开头的数字.我们还可以允许开头有一个负号：^(0|-?[1-9][0-9]*)$
18    4.这表示一个0或者一个可能为负的开头不为0的数字.让用户以0开头好了.把负号的也去掉,因为钱总不能是负的吧.下面我们要加的是说明可能的小数部分：^[0-9]+(.[0-9]+)?$
19    5.必须说明的是,小数点后面至少应该有1位数,所以"10."是不通过的,但是 "10" 和 "10.2" 是通过的：^[0-9]+(.[0-9]{2})?$
20    6.这样我们规定小数点后面必须有两位,如果你认为太苛刻了,可以这样：^[0-9]+(.[0-9]{1,2})?$
21    7.这样就允许用户只写一位小数.下面我们该考虑数字中的逗号了,我们可以这样：^[0-9]{1,3}(,[0-9]{3})*(.[0-9]{1,2})?$
22    8.1到3个数字,后面跟着任意个逗号+3个数字,逗号成为可选,而不是必须：^([0-9]+|[0-9]{1,3}(,[0-9]{3})*)(.[0-9]{1,2})?$
23    备注：这就是最终结果了,别忘了"+"可以用"*"替代如果你觉得空字符串也可以接受的话(奇怪,为什么?)最后,别忘了在用函数时去掉去掉那个反斜杠,一般的错误都在这里
24 xml文件：^([a-zA-Z]+-?)+[a-zA-Z0-9]+\\.[x|X][m|M][l|L]$
25 中文字符的正则表达式：[\u4e00-\u9fa5]
26 双字节字符：[^\x00-\xff]    (包括汉字在内，可以用来计算字符串的长度(一个双字节字符长度计2，ASCII字符计1))
27 空白行的正则表达式：\n\s*\r    (可以用来删除空白行)
28 HTML标记的正则表达式：<(\S*?)[^>]*>.*?</\1>|<.*? />    (网上流传的版本太糟糕，上面这个也仅仅能部分，对于复杂的嵌套标记依旧无能为力)
29 首尾空白字符的正则表达式：^\s*|\s*$或(^\s*)|(\s*$)    (可以用来删除行首行尾的空白字符(包括空格、制表符、换页符等等)，非常有用的表达式)
30 腾讯QQ号：[1-9][0-9]{4,}    (腾讯QQ号从10000开始)
31 中国邮政编码：[1-9]\d{5}(?!\d)    (中国邮政编码为6位数字)
32 IP地址：\d+\.\d+\.\d+\.\d+    (提取IP地址时有用)
33 IP地址：((?:(?:25[0-5]|2[0-4]\\d|[01]?\\d?\\d)\\.){3}(?:25[0-5]|2[0-4]\\d|[01]?\\d?\\d))

使用函数：REGEXP

9、Mysql事件

-- 函数
delimiter // -- 分隔符
CREATE PROCEDURE get_date ()
BEGIN
	INSERT INTO getdate 
SELECT
		now()
	FROM
		getdate ;
	END//

delimiter ;

-- 事件
delimiter //
CREATE EVENT `getDate` 
ON SCHEDULE EVERY 1 MINUTE STARTS "2019-10-22 14:46:00"  -- 调度时间 MINUTE 按分钟运行
ON COMPLETION PRESERVE ENABLE DO
BEGIN
		INSERT INTO getdate 
SELECT
		UUID(),NOW() ;
DELETE FROM getdate WHERE NOW()
END//
delimiter ;

-- 事件状态
SHOW VARIABLES LIKE 'event_scheduler' ;

SELECT	* FROM	getdate;
-- 打开事件
SET GLOBAL event_scheduler = 0 ;
-- 关闭事件
SET GLOBAL event_scheduler = 1 ;

10、Mysql查询库、表存储量

1、要查询表所占的容量，就是把表的数据和索引加起来就可以了。

SELECT SUM(DATA_LENGTH) + SUM(INDEX_LENGTH) FROM information_schema.tables WHERE table_schema='table_name';

2、查询所有的数据大小

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024), 2), 'M') FROM
information_schema.tables;

3、查询某个表的数据

SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'M')
FROM information_schema.tables
WHERE table_schema='database_name' AND table_name='table_name';

----- 注：获取结果是字节单位，转换为M单位。

mysql中information_schema数据库，存储数据库元数据，包括数据库信息、数据库中表的信息等。

schemata表：这个表里面主要是存储在mysql中的所有的数据库的信息
tables表：这个表里存储了所有数据库中的表的信息，包括每个表有多少个列等信息。
columns表：这个表存储了所有表中的表字段信息。
statistics表：存储了表中索引的信息。
user_privileges表：存储了用户的权限信息。
schema_privileges表：存储了数据库权限。
table_privileges表：存储了表的权限。
column_privileges表：存储了列的权限信息。
character_sets表：存储了mysql可以用的字符集的信息。
collations表：提供各个字符集的对照信息。
collation_character_set_applicability表：相当于collations表和character_sets表的前两个字段的一个对比，记录了字符集之间的对照信息。
table_constraints表：这个表主要是用于记录表的描述存在约束的表和约束类型。
key_column_usage表：记录具有约束的列。
routines表：记录了存储过程和函数的信息，不包含自定义的过程或函数信息。
views表：记录了视图信息，需要有show view权限。
triggers表：存储了触发器的信息，需要有super权限。

1.查看所有数据库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

2.查看所有数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

3.查看指定数据库容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';

4.查看指定数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

10、SQL语句执行流程

from > where >group by >having >select >order by

11、sql拆分省市县

select wtsd , SUBSTR(REGEXP_SUBSTR(a.wtsd,'[[:alpha:]]{2}省[[:alpha:]]{2}市[[:alpha:]]{2,10}(?:区|市|县)') , 1,3 )  as sheng
, SUBSTR(REGEXP_SUBSTR(a.wtsd,'[[:alpha:]]{2}省[[:alpha:]]{2}市[[:alpha:]]{2,10}(?:区|市|县)') , 4,3 )  as ds
,SUBSTR(REGEXP_SUBSTR(a.wtsd,'[[:alpha:]]{2}省[[:alpha:]]{2}市[[:alpha:]]{2,10}(?:区|市|县)') , 7 )  as qx
from (select '浙江省杭州市上城区' as wtsd
union all
select '浙江省嘉兴市桐乡市' as wtsd
union all
select '浙江省宁波市镇海区蛟川街道' as wtsd
union all
select '浙江省绍兴市越城区' as wtsd
union all
select '浙江省宁波市慈溪市白沙路街道' as wtsd
union all
select '浙江省湖州市长兴县雉城街道' as wtsd
union all
select '浙江省温州市温州经济技术开发区' as wtsd
) a

12、mysql5.6以下排序

SELECT
tt.id,
( @rowNum := @rowNum + 1 ) AS ranking
FROM
( select 5 as id union all select 4 as id union all select 3 as id union all select 2 as id union all select 1 as id
ORDER BY id
) tt
,( SELECT ( @rowNum := 0 ) ) as e

13、mysql5.6以下去重

select * from (
SELECT cs.*,IF(@p = id,@r:=@r+1,@r:=1) AS rank ,@p:=id
FROM tab1 cs,(SELECT @p:=NULL,@r:=0)r
ORDER BY id ,日期 desc ) aa where aa.rank = 1

14、mysql 字段总长度超过限制

　　修改ROW_FORMAT=DYNAMIC或者 ROW_FORMAT=COMPRESSED

posted @ 2018-11-12 10:21 苏su 阅读(1280) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

苏su

笔记

1、MaxCompute SQL 中，使用 not in 后面接子查询，子查询返回的结果是上万级别的数据量， 但阿里云中 in 和 not in 后面的子查询返回数量是 1000，那么在 not in 必须使用的情况下， 该如何实现此查询？

1、