数据库笔记（4.5 - 4.8）

最新推荐文章于 2023-01-29 11:52:50 发布

APPTI

最新推荐文章于 2023-01-29 11:52:50 发布

阅读量409

点赞数

分类专栏：数据库 mysql

本文链接：https://blog.csdn.net/APPTITE/article/details/104816015

版权

数据库同时被 2 个专栏收录

6 篇文章 1 订阅

订阅专栏

mysql

4 篇文章 0 订阅

订阅专栏

文章目录

源自南京大学软件学院刘嘉老师的《数据库开发》课程，课堂笔记

1. 字符串处理

1.3 统计字符出现的次数

问题：统计字符传中有多少个逗号？
```
selete(length('10,CLARK,MANAGER')-
	length(replace('10,CLARK,MANAGER',',','')))/length(',')
	as cnt
from t1
```
现将字符串中所有的,都转变成空格，然后用原来的长度减去转化后的长度（要记得除以所有减去的字符的长度）

1.4 删除不想要的字符

问题：从数据里删除指定的字符，从左边的结果集中的数据里删除所有的0和元音字母(AEIOU)，并且将删除后的值显示在STRIPPED2列中
```
selete ename
	replace(
	replace(
	replace(
	replace(
	replace(ename,'A',''),'E',''),'I',''),'O',''),'U','')
	as stripped1,
	sal,
	replace(sal,0,'')stripped2
from emp
```
mysql只能用这种笨方法

1.5 分离数字和字符数据

问题：把数据中的数字数据和字符数据分开，怎么办？

oracle——使用replace和translate解决，先把所有的字母换成小写的z，然后把所有的小写z删除。

selete replace(
	translate(data,'0123456789','0000000000','0')ename,
	to_number(
		replace(
			translate(lower(data),
				'qwertyuiopasdfghjklzxcvbnm',
				rpad('z',26,'z')),'z'))sal
from(
	selete ename||sal data
	from emp
)

mysql只能用26个replace（）函数进行嵌套
- 或者在程序体内将字符串先转换好，别在mysql语句里面干这种蠢事。

1.6 判断含有字母和数字的字符

问题：从表中筛选出部分行数据，筛选条件是只包含字母和数字字符。

mysql直接使用正则表达式^0-9a-zA-Z来匹配所有的数字和字母，比较方便

selete data  
from V
where data regexp'[^0-9a-zA-Z]'=0 # 如果是字母或者数字就会返回0，不是字母或者数字就会返回1.

oracle解决思路：找到所有的可能出现的非字母以及数字的形式；先把所有的字符和数字都转化为一个单一的字符，然后就能把它们当成一个字符，然后就可以分离出来，全部删掉。还是使用translate函数
- 把所有的字母和数字全部变成a，如果到某一行所有的内容都是a，那么这一行就是我们需要找的行
```
selete data 
from V
where translate(lower(data),'0123456789qwertyuiopasdfghjklzxcvbnm',rpad('a',36,'a'))
	= rpad('a',length(data),'a')
```

1.7 提取姓名的首字母

问题：你想把姓名变成首字母缩写形式
mysql：使用
- concat_ws
- substr
- substring_index
- length

oracle：使用

selete replace(
	replace(
		translate(replace('Stewie Griffin','.',''),
			'qwertyuiopasdfghjklzxcvbnm',
			rpad('#',26,'#')),'#',''),'','.')||'.'
from t1

2. 数值处理

2.0 示例表结构

emp表
- EMPNO
- ENAME
- JOB
- MGR
- HIREDATE
- SAL
- COMM
- DEPTNO
dept表
- DEPTNO
- DNAME
- LOC
为什么要在sql里面写这些数值计算的东西？
- 因为这样可以大大减小与数据库的交互次数，大大减少了吞吐量
- sql原则——尽量单一sql，完成所有任务

2.1 计算平均值

selete avg(sal) as avg_sal from emp

遇到空值怎么办？——使用coalesce
```
selete avg(coalesce(sal,0)) from emp
```

2.2 计算最大值和最小值

max() min()

空值对于寻找最大值和最小值没有影响

2.3 求和

sum() 同样对null值不影响

2.4 计算行数

count(*)来计算行数，所有的空值与非空值都会被记入总数
但是如果只针对某一列的行数，那么就不一样了，不会计算空值（count(*)和count(某一列)是不一样的）
```
selete count(comm)
from emp
```
这样就会把空值排除出去。

分组计算不同组的行数：

selete deptno,count(*)
from emp
group by deptno

2.5 累计求和(Running Total)

保留加的过程的中间结果

Mysql & Oracle —— sum over

selete ename, sal
	sum(sal) over (order by sal, empno)
		as running_total
from emp
order by 2 # order by empno

2.6 计算众数

Oracle

selete max(sal)
	keep(dense_rank first order by cnt desc)sal
from(
	select sal, count(*)cnt
	from emp 
	where deptno=20
	group by sal
)

Mysql

select sal
from emp 
where deptno=20
group by sal
having count(*) >= all (select count(*)
	from emp 
	where deptno = 20
	group by sal)

2.7 计算中位数

mysql

selete avg(sal)
from (
	select e.sal
	from emp e, emp d
	where e.deptno = d.deptno
		and e.deptno = 20
		group by e.sal
		having sum(case when e.sal = d.sal then 1 else 0 end)
			>= abs(sum(sign(e.sal - d.sal)))
)

Oracle——直接有中位数接口

select median(sal)
from emp 
where deptno=20

2.8 计算百分比

mysql

select(sum(
	case when deptno = 10 then sal end)/sum(sal)
	)*100 as pct
from emp

Oracle

select distinct(d10/total)*100 as pct  
from (
select deptno, 
	sum(sal)over() total,
	sum(sal)over(partition by deptno)d10
from emp
)x
where deptno = 10

2.9 计算平均值时去掉最大值和最小值

mysql

select avg(sal)
from emp 
where sal not in(
	(select min(sal) from emp),
	(select max(sal) from emp)
)

Oracle

select avg(sal)
from(
select sal, min(sal) over() min_sal,max(sal) over() max_sal from emp
)x
where sal not in (min_sal, max_sal)

2.10 修改累计值

问题，你想一句另一列的值来修改累计值。有这样一个场景，你希望显示一个信用卡账户的交易历史，并显示每一笔交易完成后的余额。

3. 日期处理

3.1 年月日加减法

问题，以员工CLARK的hiredate为例，计算入职的前后五天，入职的前后五个月，以及入职前后5年的日期，hiredate=‘09-JUN-1981’

Oracle 通过add_months()操作来以月为单位进行计算

select hiredate - 5								as hd_minus_5D,
			 hiredate + 5       				as hd_plus_5D,
			 add_months(hiredate,-5)		as hd_minus_5M,
			 add_months(hiredate,5)			as hd_plus_5M,
			 add_months(hiredate,-5*12)	as hd minus_5Y,
			 add_months(hiredate,5*12)	as hd plus_5Y
from emp 
where deptno = 10

mysql 使用date_add()函数或者interval

select date_add(hiredate,interval -5 day)								as hd_minus_5D,
			 date_add(hiredate,interval 5 day)								as hd_plus_5D,
			 date_add(hiredate,interval -5 month)							as hd_minus_5M,
			 date_add(hiredate,interval 5 month)							as hd_plus_5M,
			 date_add(hiredate,interval -5 year)							as hd minus_5Y,
			 date_add(hiredate,interval -5 year)							as hd plus_5Y
from emp 
where deptno = 10

------------------ or ------------------

select hiredate - interval 5 day								as hd_minus_5D,
			 hiredate + interval 5 day       					as hd_plus_5D,
			 hiredate - interval 5 month							as hd_minus_5M,
			 hiredate + interval 5 month							as hd_plus_5M,
			 hiredate - interval 5 year								as hd minus_5Y,
			 hiredate - interval 5 year								as hd plus_5Y
from emp 
where deptno = 10

3.2 计算两个日期之间的天数

Mysql & SQL Server

select datediff(ward_hd,allen_hd)
from (
	select hiredate as ward_hd
  from emp
  where ename='WARD'
)x,
(
  select hiredate as allen_hd
  from emp
  where ename='ALLEN'
)y

Oracle

select ward_hd-allen_hd
from(
select hiredate as ward_hd
  from emp 
  where ename = 'WARD'
)x,
(
select hiredate as allen_hd
  from emp 
  where ename = 'ALLEN'
)y

3.3 计算两个日期之间的工作日天数

Mysql——使用数据透视表

select sum (case when date_fromat)
……（详见ppt）

计算出两个日期之间相隔多少天
排除掉周末（检索有多少条满足“工作日”条件的记录）

3.4 计算当前记录和下一条记录之间的日期差

计算deptno = 10的部门的每一个员工入职时间相差多少天

Mysql

select x.*, 
	datediff(x.next_hd, x.hiredate)diff
from(
	select e.deptno, e.ename, e.hiredate,
  	(select min(d.hiredate) from emp d
    where d.hiredate > e.hiredate)next_hd
  from emp e
  where e.deptno = 10
)x

3.5 判断闰年

Mysql——需要从当前日期开始，酸楚当前日期是第几天，然后用date_add函数计算current_date - dayofyear 再加上1来找到1月1号，然后用date_add继续加一个月，再调用last_day找到最后一天
```
select day(
	last_day(
	date_add(
	date_add(
	date_add(current_date)day),
	interval 1 day),
	interval 1 month)))dy
from t1
```

3.6 计算一年有多少天

计算下一年份的第一天和当前年份的第一天的差值（以天为单位）

Oracle

select add_months(trunc(susdate,'y'),12)-trunc(sysdate,'y')
from dual

Mysql 找到今年的第一天是比较麻烦的事情；先查出当前的日期是当前的年份的第几天，然后用当前日期减去那个值加上1，就能找到今年的第一天；在此基础上加一年，得到的结果就是当前年份有多少天了
```
select datediff(curr_year + intercal 1 year),curr_year)
from (
	select adddate(current_date, -dateofyear(current_date)_1)curr_year
  from t1
)x
```

3.7 找到当前月份的第一个和最后一个星期一

Oracle 先找到钱一个月份的最后一天，然后调用next_day函数计算紧随的前一个月的最后一天的第一个星期一，也就是当前月份的第一个星期一。

先用trunc函数计算当前月份的第一天，嗲用last_day函数找到这个月的最后一天，然后再减去7，然后根据减去7之后的日子为起点，寻找第一个星期一。
```
select next_day(trunc(sysdate,'mm')-1,'MONDAY')first_monday,
	next_day(last_day(trunc(sysdate,'mm'))-7,'MONDAY')last_monday
from dual
```

Mysql 先找出当前月份的第一天，然后紧接着找出第一个星期一，第七行开始的case when语句，dayofweek函数中，星期一对应的值是2（星期天对应的值是1）

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
  from t1
)x
)y

3.8 依据特定时间单位检索数据

指定月份、星期或者其他时间单位来筛选记录行
比如：找到入职月份是February或者December，而且入职当天是星期二的所有员工

Mysql

select ename 
from emp 
where monthname(hiredate) in ('February','December') 
	or dayname(hiredate) = 'Tuesday'

Oracle

select ename 
from emp 
where rtrim(to_char(hiredate,'month')) in ('february','december')
	or rtrim(...)

3.9 识别重叠的日期区间

识别两段时间是否冲突

Mysql

select a.empno, a.ename,
	concat('project',b.proj_id,
  'overlaps project',a.proj_...)

4. 常见的SQL连接模式

4.1 叠加行集（Union & Union all）

如果需要显示EMP表中部门ID等于10的信息以及DEPT表中各个部门的名称和编号

select ename as ename_and_dname, deptno 
from emp 
where deptno = 10 
union all 
select '--------', null   # 增加分隔符
from t1 
union all 
select dname, deptno 
from dept

最终显示的结果：

注意点：
- select后面需要展示的列的数目必须保持一致而且数据类型匹配
- 如果有重复记录，union all会默认纳入；如果希望过滤掉重复记录，可以使用union而不是union all
- union相比union all 效率比较低（因为union会多一个排序操作，然后进行排序以进行删除多余项）

4.2 查找只存在于一张表的数据（差函数）

从源表找到那些目标表之中不存在的值
DEPT表中DEPTNO = 40的数据并不存在于EMP表中，怎么把它找出来？

Oracle

select deptno from dept 
minus # minus要求参与运算的两者必须有相同的数据类型和数据个数，而且不会反悔重复值；null值不会返回任何东西
select deptno from emp

DB2

select deptno from dept 
except 
select deptno from emp

Mysql

select deptno 
from dept 
where deptno not in (select deptno from emp)  

# 如果DEPTNO不是主键（不唯一）  
select distinct deptno 
from dept 
where deptno not in (select deptno from emp)  



# 如果想要用原始的方式进行查询：
select deptno from dept 
where deptno not in(10,50,null)
# 这样是查不出结果的。因为 in 本质上是一种 “all”运算。应该这样写：

select deptno 
from dept 
where not (deptno = 10 or deptno = 50 or deptno = null)

4.3 从一个表检索另一个表不相关的行（外链接）

Oracle

select d.* 
from dept d, emp e 
where d.deptno = e.deptno(+) 
	and e.deptno is null

Mysql

select d.* 
from dept d left outer join emp e 
	on (d.deptno = e.deptno)
where e.deptno is null

4.4 确定两个表是否有相同的数据

问题：想知道两个表是否有相同的数据

要求：sql不仅能找到不同的数据，还能找到重复的数据（怎么判断两个表是否有相同的数据？怎么把不同的数据提取出来？）
先找出存在于员工表而不存在于视图V的行，然后再找出存在于视图V而不存在于员工表中的行，然后再将其union all

Oracle

# 找出V中emp没有的
(select empno, ename, job, mgr, hiredate, sal, comm, deptno, 
	count(*) as cnt
from V 
group by emptno, ename, job, mgr, hiredate, sal, comm, deptno 
 
minus 

select emptno,... 
 	count(*) as cnt 
from emp 
group by empno,...
)

# 加上
union all 

# 找出emp中V没有的
(
select empno,... 
	count(*) as cnt 
from emp 
group by emptno,...
  
minus 
  
select empno,...
  count(*) as cnt
from v
group by empno,...
)

Mysql

详见https://teaching.applysquare.com/S/Course/index/cid/6319#S-Lesson-view_media-id-68834 18:00处

4.5 从多个表中返回缺失值

FULL OUTER JOIN

select d.deptno, d.dname, e.ename 
from dept d full outer join emp e 
	on (d.deptno = e.deptno)

union

select d.deptno, d.dname, e.ename 
from dept d right outer join emp e 
	on (d.deptno = e.deptno) 
	
union  

select d.deptno, d.dname, e.ename 
from dept d left outer join emp e 
	on (d.deptno = e.deptno)

4.6 连接和聚合函数的使用

考虑新增加一张bonus表，注意，存在重复记录（有员工重复获得bonus）
这往往是不得不使用distinct的情形：
- 比如要将工资表、奖金表、员工表进行连接；然后再进行求和，假如有一个人他拿了两次奖金，那么他的工资也会被连接两次；结果就是工资到最后会出问题。

解决方案：调用聚合函数的时候使用distinct；或者调用聚合函数之前使用distinct

select deptno, 
	sum(distinct sal) as total_sal, 
	sum(bonus) as total_bonus
from(
	select e.empno, 
  	e.ename, 
  	e.sal, 
  	e.deptno, 
  	e.sal*case when eb.type = 1 then .1
  				when eb.type = 2 then .2
  				else .3
					# 这里的eb.type是约定的sal的一种计算方式，可以不用深究，要结合具体例子
  		end as bonus
 	from emp e, emp_bonus eb 
  where e.empno = eb.empno 
  and e.deptno = 10
)x 
group by deptno