mysql 两个结果集比较

基于MySQL数据库来练习的，各种数据库语法不一样。使用了 SQLyog 可视化工具。

emp表结构

CREATE TABLE `emp` (
  `EMPNO` int(11) NOT NULL,
  `ENAME` varchar(15) DEFAULT NULL,
  `JOB` varchar(15) DEFAULT NULL,
  `MGR` int(11) DEFAULT NULL,
  `HIREDATE` date DEFAULT NULL,
  `SAL` int(11) DEFAULT NULL,
  `COMM` int(11) DEFAULT NULL,
  `DEPTNO` int(11) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`)
)

t10表结构

CREATE TABLE `t10` (
  `ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
)

 

t1表结构

1.1 遍历字符串

遍历一个字符串，并以一个字符一行的形式将他们显示出来

SELECT SUBSTR(e.ename,iter.pos,1) AS C
FROM (
SELECT ename
FROM emp
WHERE ename = 'ZHANGXIAOHEI')e,
(
SELECT id 
AS pos
FROM t10)iter
WHERE iter.pos <= LENGTH(e.ename)

 

要遍历一个字符串里的全部字符，关键在于要先和另一个表做连接查询，该表必须有足够多的行以保证循环操作的次数。

上述使用t10表作为一个数据透视表，该表有15行记录(它只有一列，列名为ID，它的值分别从1-15)，也就是说上述查询最多能返回15行。

实现思路步骤：

下拉代码仅仅展示 e 和 iter的笛卡尔积（例如某个员工名字和 t10 表的 15行数据的笛卡尔积）

SELECT e.ename,iter.pos
FROM (SELECT ename FROM emp WHERE ename = 'ZHANGXIAOHEI')e,
(SELECT id AS pos FROM t10)iter

内嵌视图e的行数是1，而内嵌视图iter的行数是15，所以得到的笛卡尔积就是15行。产生这样的笛卡尔积就是使用SQL来模拟循环操作的第一步。（名字是英文，中文都适用）

解决方案使用了WHERE子句在查询语句返回了14行数据之后跳出了循环。为了保证结果集的行数等于给定员工名字的字符个数，WHERE子句把iter.pos <= LENGTH(e.ename)作为条件。

SELECT e.ename,iter.pos
FROM (SELECT ename FROM emp WHERE ename = 'ZHANGXIAOHEI')e,
(SELECT id AS pos FROM t10)iter
WHERE iter.pos <= LENGTH(e.ename)

现在得到的记录行数和E.ENAME的字符数一样多，接下来就可以把ITER.POS作为SUBSTR的参数，这样就你能遍历字符串里面的每个字符。ITER.POS的值会逐行递增，这样每一行都能从E.ENAME里提取出一个连续的字符。

 

1.2嵌入引号

在字符串常量里面嵌入引号

SELECT 'XIAOHEI''s girl friend' EXAMPLE FROM t1 UNION ALL
SELECT 'beavers'' teeth'                FROM t1 UNION ALL
SELECT ''''                             FROM t1

 

处理SQL的引号，可以把它理解为圆括号，这样比较容易理解。就比如写在了左括号，那么就必须写上右括号，否则就出现问题。引号使用也是如此。如果想在字符串中添加引号，那么这个引号必须被两个引号括起来。

两个外层引号用于定义一个字符串常量，在该字符串常量还使用两个引号代表一个引号。

 

1.3 统计字符串出现的次数

比如想统计" , " 字符在字符串出现了多少次。

比如 "我，不，知，道" 统计 , 出现了 多少次，肉眼可以看出出现了 3 次，但是如何用SQL语句实现？

SELECT(LENGTH('我,不,知,道') - 
	LENGTH(REPLACE('我,不,知,道',',','')))/LENGTH(',')
	AS cnt
FROM t1

字符串的总长度 减去 去掉逗号之后的字符串长度，就得到了逗号的个数。

第1行SQL语句调用length函数获取字符串总长度，第2行仍然调用length函数获取不含逗号的字符串长度，而逗号的删除则借助了REPLACE函数。

把上述的两个长度相减，得到的差值就是字符串里面逗号的个数。最后的除法运算是用上述 两个长度的差值 除以 正在搜索的那个字符串长度(',')。如果被搜索的字符串的长度大于1的话，就必须使用除法运算。

就比如下面的例子，如果不使用除法运算的话，就不会得到正确的结果。

统计LOOK BOOK IN NOON 中出现了多少个OO

SELECT(LENGTH('LOOK BOOK IN NOON') -
	LENGTH(REPLACE('LOOK BOOK IN NOON','OO','')))/LENGTH('OO')
	AS 正确的答案,
	(LENGTH('LOOK BOOK IN NOON') -
	LENGTH(REPLACE('LOOK BOOK IN NOON','OO',''))) AS 错误的答案
FROM t1

 

1.4 删除不想要的字符

比如以下表格，我想删除 张 这个字， 并且要删除所有数据里面的 0

SELECT ename,
       REPLACE(ename,'张','') AS 新ENAME,
       sal,
       REPLACE(sal,0,'') AS 新SAL
 FROM name_sal

 

1.5 判断含有字母和数字的字符串

创建例子视图

CREATE VIEW b AS
SELECT ename AS DATA
FROM emp
WHERE deptno <= 3
UNION ALL
SELECT CONCAT(ename,',$',sal,'.00') AS DATA
FROM emp
WHERE deptno > 3 AND deptno <=6
UNION ALL
SELECT CONCAT(ename,deptno) AS DATA
FROM emp
WHERE deptno > 6

过滤除了字母和数字还包含其他字符的行

SELECT DATA
FROM b
WHERE DATA REGEXP '[^0-9a-zA-Z]' = 0

解决思路：

首先找出字符串中所有可能出现的非字母数字字符，这似乎是更为直观的解决思路。但是我发现从反面着手更加容易：首先找出所有的字母字符和数字字符。这样就先把所有字母字符和数字字符转换成一个单一的字符，然后就能把它们当作一个字符。这样做的好处是经过转换处理之后这些字母和数字可以被当作一个整体来操作。一旦生成了原有字符串的副本，并把其中的字母字符和数字字符替换成某个指定的字符，很容易就可以将字母字符和数字字符从其他字符中分离出来。

WHERE DATA REGEXP '[^0-9a-zA-Z]' = 0

上述条件使得那些仅包含数字和字母的行会被筛选出来。方括号里的取值范围0-9a-zA-Z表示所有可能出现的数字和字母。符号^表示否定，因而该表达式可被解释为”非数字或者非字母“。返回值等于1代表TRUE，0代表FALSE,因此整个表达式的意思是：”执行非数字和字母字符匹配操作，返回结果等于FALSE的行 “

 

1.6 提取姓名的首字母

把英文名字变成首字母的形式，比如Kobe Bryant，得到 K.B

使用了MySQL内置函数CONCAT,CONCAT_WS,SUBSTRING和SUBSTRING_INDEX提取字母

SELECT CASE
       WHEN cnt = 2 THEN
        TRIM(TRAILING '.' FROM
	     CONCAT_WS('.',
	     SUBSTR(SUBSTRING_INDEX(NAME,' ',1),1,1),
	     SUBSTR(NAME,
		    LENGTH(SUBSTRING_INDEX(NAME,' ',1))+2,1),
		    SUBSTR(SUBSTRING_INDEX(NAME,' ',-1),1,1),
		    '.'))
	ELSE
	 TRIM(TRAILING '.' FROM
		CONCAT_WS('.',
			  SUBSTR(SUBSTRING_INDEX(NAME,' ',1),1,1),
			  SUBSTR(SUBSTRING_INDEX(NAME,' ',-1),1,1)
			  ))
	END AS initials
FROM(
SELECT NAME,LENGTH(NAME)-LENGTH(REPLACE(NAME,' ','')) AS cnt
FROM(
SELECT REPLACE('Kobe Bryant','.','') AS NAME FROM t1
)Y
)X

 

实现步骤与思路:

内嵌视图Y用于删除姓名中出现的英文句号。内嵌视图X可以找出姓名中空格符的个数，以便调用适当次数的SUBSTR函数来提取首字母。先后调用SUBSTRING_INDEX函数，根据空格的位置把字符串拆成三个独立的部分。在上面代码中出现的姓名包括 First Name 和 Last Name,CASE语句的ELSE部分会被执行。

SELECT  SUBSTR(SUBSTRING_INDEX(NAME,' ',1),1,1) AS FirstName,
	SUBSTR(SUBSTRING_INDEX(NAME,' ',-1),1,1) AS LastName
FROM (SELECT 'Kobe Bryant' AS NAME FROM t1) X

如果英文姓名中包含Middle Name或其首字母，那么执行下面的代码可以获得首字母。

 SUBSTR(NAME, LENGTH(SUBSTRING_INDEX(NAME,' ',1))+2,1)

上面的查询先找出FirstName的结束位置，并前进两个字符移动到Middle Name或其首字母的开始位置；计算结果将作为SUBSTR函数的开始位置。因为只需要保留第一个字符，所以Middle Name或其首字母能被成功地返回。然后提取除地首字母会传递给CONCAT_WS函数，这样就能用英文句号分割各个首字母了。

SELECT CONCAT_WS('.',
		 SUBSTR(SUBSTRING_INDEX(NAME,' ',1),1,1),
	         SUBSTR(SUBSTRING_INDEX(NAME,' ',-1),1,1),
	         '.') a
FROM (SELECT 'Kobe Bryant' AS NAME FROM t1) X

最后删除首字母无关的英文句号

 

1.7 按照子字符串排序

按照下表每个名字最后两个字符进行排序

使用内置函数LENGTH 和 SUBSTR，根据字符串的特定部分排序

SELECT 	Player_Name
FROM NBA_NAMES
ORDER BY SUBSTR(Player_Name,LENGTH(Player_Name)-1,2)

通过在ORDER BY子句里使用SUBSTR表达式，我们可以选择一个字符串的任意部分用于结果集的排序。

 

1.8 按字母表顺序列表字符

对字符串里面的字符按照字母表顺序排序

SELECT Player_Name,GROUP_CONCAT(c ORDER BY c SEPARATOR '') AS sort_Name
FROM(
SELECT Player_Name,SUBSTR(a.`Player_Name`,iter.pos,1) c
FROM NBA_NAMES a,
	( SELECT id pos FROM t10) iter
WHERE iter.pos <= LENGTH(a.`Player_Name`)
)X
GROUP BY Player_Name

内嵌视图X把每个名字的字符都提取出来，并当作一行返回。SUBSTR函数可以提取名字字符串里的每个字符。

内嵌视图ITER用于遍历字符串。其余的工作都交给GROUP_CONCAT函数完成。通过指定排序方式，GROUP_CONCAT函数不仅能串接每个字母，还能按照字母表顺序对它们进行排序。

 

1.9 识别字符串里面的数字字符

创建英文中混有数字字符串的视图

CREATE VIEW c AS
SELECT CONCAT(
	SUBSTR(Player_Name,1,2),
	REPLACE(CAST(id AS CHAR(4)),' ',''),
	SUBSTR(Player_Name,3,2)
	)AS mixed
FROM NBA_NAMES

遍历每一行字符串，并评估每一个字符串。

SELECT mixed,CAST(GROUP_CONCAT(a ORDER BY pos SEPARATOR '') AS UNSIGNED)
       AS MIXED1
FROM(
     SELECT c.`mixed`,iter.pos,SUBSTR(c.mixed,iter.pos,1) AS a
     FROM c,
     (SELECT id pos FROM t10) iter
	WHERE iter.pos <= LENGTH(c.mixed)
	AND ASCII(SUBSTR(c.mixed,iter.pos,1)) BETWEEN 48 AND 57
	)Y
GROUP BY mixed
ORDER BY 1

 

实现步骤和思路：

首先遍历每个字符串，评估每个字符并判断其是否为数字

SELECT c.`mixed`,iter.pos,SUBSTR(c.mixed,iter.pos,1) AS a
     FROM c,
      (SELECT id pos FROM t10) iter
 WHERE iter.pos <= LENGTH(c.mixed)
 ORDER BY 1,2

现在就可以单独评估字符串中的每个字符，接下来要筛选出在a列中有一个数字的行

SELECT c.`mixed`,iter.pos,SUBSTR(c.mixed,iter.pos,1) AS a
     FROM c,
      (SELECT id pos FROM t10) iter
 WHERE iter.pos <= LENGTH(c.mixed)
	AND ASCII(SUBSTR(c.mixed,iter.pos,1)) BETWEEN 48 AND 57
 ORDER BY 1,2

到这一步，a列只剩下数字。调用GROUP_CONCAT函数串接这些数字，并形成MIXED列的数字。需要将最好的结果转换为数字类型。

 

1.10 提取第n个分隔子字符串

比如我现在要提取下表中每行的第二个名字  那么 应该输出 Paul,Bradley

CREATE VIEW V AS
SELECT 'James,Paul,Giannis,Damian,Luka' AS NAME
FROM t1
UNION ALL
SELECT 'Kyrie,Bradley,Trae,Pascal,Kawhi' AS NAME
FROM t1

遍历视图V返回的NAME,并使用逗号的位置来筛选出每一个字符串里的第二个名字

SELECT NAME
FROM(
SELECT iter.pos,
	SUBSTRING_INDEX(
	SUBSTRING_INDEX(src.name,',',iter.pos),',',-1) NAME
FROM v src,
     (SELECT id pos FROM t10) iter
     WHERE iter.pos <= LENGTH(src.`name`) - LENGTH(REPLACE(src.`name`,',',''))
	) X
WHERE pos = 2

 

实现思路和步骤：

使用内嵌视图X遍历每个字符串，可以通过计算字符串中的分隔符的个数来确定该字符串中有多少个值。

SELECT iter.pos,src.name
FROM (SELECT id pos FROM t10) iter,
      V src
 WHERE iter.pos <= LENGTH(src.`name`) - LENGTH(REPLACE(src.`name`,',',''))

上面的查询结果中，每个字符串对应的数据行相较于字符串里实际的值的个数少了一行，因为是这是需要的值。SUBSTRING_INDEX函数可以解析需要的这些值。

SELECT iter.pos,src.name name1,
	SUBSTRING_INDEX(src.name,',',iter.pos) name2,
	SUBSTRING_INDEX(
	SUBSTRING_INDEX(src.name,',',iter.pos),',',-1) name3
FROM (SELECT id pos FROM t10) iter,
      V src
 WHERE iter.pos <= LENGTH(src.`name`) - LENGTH(REPLACE(src.`name`,',',''))

现在已经展示了3个和名字相关的字段。SUBSTRING_INDEX内层的函数能够找到逗号第n次出现的位置，并且提取该位置左侧的全部字符。外层的函数可以找到(从字符串到末尾开始计数)逗号第一次出现的位置，并提取其右侧的全部字符。最后将pos等于n的name3的值保留下来。

 

1.11 解析IP地址

把IP地址的各个字段分解为4列

比如 111.22.3.4 分割成  111  22  3  4

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(y.ip,'.',1),'.',-1)a,
	SUBSTRING_INDEX(SUBSTRING_INDEX(y.ip,'.',2),'.',-1)b,
	SUBSTRING_INDEX(SUBSTRING_INDEX(y.ip,'.',3),'.',-1)c,
	SUBSTRING_INDEX(SUBSTRING_INDEX(y.ip,'.',4),'.',-1)d
FROM (SELECT '111.22.3.4' AS ip FROM t1) Y

使用数据库的内置函数SUBSTRING_INDEX，很容易遍历字符串的各个部分。