MySQL数据库之SQL实例（字符串处理+数值处理+日期处理+SQL连接模式）

本文分为以下几个方面来介绍SQL实例：

1. 字符串处理
2. 数值处理
3. 日期处理
4. 常见SQL连接模式

一、字符串处理

1. 遍历字符串
2. 嵌入引号
3. 统计字符出现次数
4. 删除不想要的字符
5. 判断含有字母和数字的字符串
6. 分离数字和字母（*待续）
7. 提取姓名首字母（*待续）
8. 将group分组后的字符串用逗号拼接
   1. 遍历字符串
   目标：从前向后遍历字符串，从后向前遍历字符串；从前往后依次删除第i个元素，从后往前依次增加第i个元素。
   先建立一张下标表：
   

select substr(e.name,iter.pos,1) as a,
substr(e.name,length(e.name)-iter.pos+1,1) as b,
substr(e.name,iter.pos) as A, 
substr(e.name,length(e.name)-iter.pos+1) as B
	from (select 'KING' as name) as e,
	(select id as pos from pos) as iter
	where iter.pos<=length(e.name)

结果：

2. 嵌入引号
有两种方式，’‘或’。

select 'he''s Bob' as bob,'and I\'m Jack' as jack

3. 统计字符出现次数
目标：统计逗号出现次数

select (length(str)-length(replace(str,',',''))) as len 
from 
(select 'hello,hi,hey' as str) as x

4. 删除不想要的字符
目标：将字符串‘ABCDEFGHIJKLMNOPQRSTUVWXYZ’中的大写元音字母删除掉

select replace(
replace(
replace(
replace(
replace(str,'A',''),
'E',''),
'I',''),
'O',''),
'U','') as st
from (select 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' as str) as x

5. 判断含有字母和数字的字符串

目标：查找仅包含字母和数字的字符串。本数据集中有：clark，king，miller，ward30.

select * from test where name REGEXP '[^0-9A-Za-z]'=0

8. 将group分组后的字符串用逗号拼接

目标：按deptno分组，将emps以逗号拼接。
MySQL提供了分组拼接函数GROUP_CONCAT，默认就是按照’，'拼接

select deptno,GROUP_CONCAT(emps) from emp group by deptno

二、数值处理

1. 计算平均值
2. 计算平均值去掉最大值和最小值
3. 计算最大值和最小值
4. 求和
5. 计算行数
6. 累计求和
7. 计算众数
8. 计算中位数
9. 计算百分比
10. 修改累计值（*待续）
11. 累计求乘积
12. 累积求差
    1.计算平均值
    

select avg(sal) as avgsal from sal

2. 计算平均值去掉最大值和最小值

select avg(sal) as avgsal from sal 
where sal.sal not in (
(select min(sal) from sal),
(select max(sal) from sal))

3. 计算最大值和最小值

select max(sal) as max,min(sal) as min from sal

4.求和

select sum(sal) as sum from sal

5. 计算行数

select count(sal) as count from sal

6. 累计求和
目标：第k行结果显示前k行的累积和。
为sal表加上id列。

select id, sal, (select sum(sal) from sal a where a.id<=sal.id) as sumi from sal

7. 计算众数

select * from sal group by sal HAVING count(*) >= all(select count(*) from sal group by sal)

8. 计算中位数

select avg(sal) from (
	select myindex, sal from (
	select @index:=@index+1 as myindex, sal from sal, (select @index:=0) as i order by sal ) 
		as t where floor(@index/2+1)=myindex or ceil(@index/2)=myindex
) as x

9. 计算百分比
目标：计算众数所占比例

select 
concat(
(select count(*) from sal group by sal HAVING count(*) >= all(select count(*) from sal group by sal))/(select count(*) from sal) * 100,
'%')
as percent

11. 累计求乘积

select exp(sum(ln(id)))as multi1,round(exp(sum(ln(id))))as multi2 from pos;

12. 累计求差

select -sum(id) as sub1, sum(-id) as sub2 from pos;

三、日期处理

1. 年月日加减法
2. 计算两个日期之间的天数
3. 计算两个日期之间的工作日天数
4. 计算当前记录和下一条记录之间的日期差
5. 判断闰年
6. 计算一年有多少天
7. 找到当前月份的第一个和最后一个星期一
   1. 年月日加减法
   目标：当前日期加33天后的结果

select date_add(now(), interval 33 day) as after33days

2. 计算两个日期之间的天数

select datediff('2021-3-3','2019-1-3') as diff 

3. 计算两个日期之间的工作日天数
目标：计算’2020-1-1‘到’2021-1-1‘的工作日天数。
创建t500透视表，插入500条数据

CREATE PROCEDURE `i500`()
BEGIN 
 DECLARE a INT default 1;
 while a<=500 do
 insert into t500 (id) value(a);
 set a=a+1;
 end while;
 end;
 call i500()

去除周六周日，剩余的天数。

select SUM(
	case when DATE_FORMAT(date_add('2020-1-1', INTERVAL t500.id-1 DAY),'%a') in ('Sat','Sun') 
	then 0 else 1 end
) as workdays
from t500
where t500.id<=datediff('2021-1-1','2020-1-1')

参考：https://blog.csdn.net/weixin_33836874/article/details/92379913
4. 计算当前记录和下一条记录之间的日期差
数据集，child表：

思路：获取比当前日期更大的最小日期，或比当前日期更小的最大日期。为了防止出现null行，过滤掉最小日期

select datediff(birth,nextbirth) as diff
from (
	select c2.birth as birth, (select max(c1.birth) from child c1 where DATEDIFF(c2.birth,c1.birth)>0) as nextbirth
	from child c2 where c2.birth not in (select min(birth) from child)
) x 

5. 判断闰年
思路：获取2月的最后一天（last_day函数获取一个月最后一天），判断是28还是29。详细思路，获取当前日期的元旦（用当前日期减去当前日期的年天数+1），再加上1个月，再算出最后一天，再获取日期中的day。

select (
case 
when (
select 
day( 
LAST_DAY(
 date_add(
 date_add(current_date, interval 1-DAYOFYEAR(current_date) DAY),
INTERVAL 1 month))))=28 then '平年'
else '闰年' end 
) as '今年是什么年?'

6. 计算一年有多少天
思路一：按照上述的闰年判断方法，闰年对应366天，平年365天。
思路二：获取当前年的元旦，和下一年的元旦之差。
以思路二为例：

select 
datediff(
 date_add(date_add(current_date,INTERVAL 1 year), interval 1-DAYOFYEAR(date_add(current_date,INTERVAL 1 year)) DAY),
 date_add(current_date, interval 1-DAYOFYEAR(current_date) DAY)
) as dayofYear

7.找到当前月份的第一个和最后一个星期一
思路：星期一对应的dayofweek是2（因为sunday是第一天）。

1. 第一步，获取当前月份的第一天及其月份
2. 第二步，判断该月份的第一天是星期几，并根据第一天是星期几求出第一个星期一。如4月1号是星期四，则第一个星期一就是4月1号加上（7-(5-2)）=4月5号；或恰好是星期一；或是星期日，则4月1号+abs(1-2)=4月2号。
3. 第三步，将第一个星期一对应的日期加上28天，判断是否跳到下一个月了，若是，则加上21天，为当前月份最后一个星期一；若仍是当前月份，则为当前月份最后一个星期一。

select first_monday, case month(adddate(first_monday,28))
when mth then adddate(first_monday,28)
else adddate(first_monday,21) end last_monday
from (
select case sign(DAYOFWEEK(dy)-2)
	when 0 then dy
	when -1 then adddate(dy,abs(DAYOFWEEK(dy)-2))
	when 1 then ADDDATE(dy,(7-(DAYOFWEEK(dy)-2)))
	end first_monday,mth
from 
(select adddate(adddate(CURRENT_DATE,-day(CURRENT_DATE)),1) dy,month(CURRENT_DATE) mth
) x
 ) y

四、常见SQL连接模式

1. Union和Union all
2. 差运算
3. 左外连接
4. 右外连接
5. 全外连接
6. 内连接

1. Union 和Union all

区别：union会对结果集去重，union all 不会，因此union all效率更高。
目标：对deptno=10和deptno=30的数据进行并操作。

select * from emp where deptno=10
union all 
select * from emp where deptno=30

事实上，也可以用deptno in(10,30)来替代。
2. 差运算
目标：查询不在CLARK和JaMEs所在部门的员工记录。

select * from emp where deptno not in (select deptno from emp where emps in ('CLARK','JaMEs'))

3. 左外连接

select * from emp e left JOIN emp d on (e.deptno=d.deptno)

4. 右外连接
以右边的表为主，右边的数据都保留下来，左表若没有与之匹配的则用null填充。
5. 全外连接
full outer join
左右表都保留，任一方没有对应数据用null填充。
mysql不支持全外连接，使用左外连接和右外连接的union即可。
6. 内连接
用=连接，要求左右表都存在对应的值。