MySQL笔记（三）SQL经典实例（下）

SQL示例中用到的表的结构和数据在文章

目录

字符串处理

数值处理

日期运算

区间查询

高级查询

---

字符串处理

了解SQL在字符串处理方面能做什么和不能做什么。

1、按照子字符串排序

select ename 
from emp
order by substr(ename, length(ename)-1, 2);

参考链接

2、创建分隔列表

想把行数据变成以某种符号分隔的列表，例如以逗号分隔。

select deptno, 
group_concat(ename order by empno separator ',') as emps
from emp
group by deptno;

3、分隔数据转换为多值in列表

例如，有一个字符串  "7654,7698,7782,7788" 。我想在where子句的in 后面用这个字符串。

 

数值处理

 

计算平均值
select avg(sal) as avg_sal from emp;

找最大、最小值
select min(sal) as min_sal, max(sal) as max_sal from emp;

求和
select sum(sal) from emp;

计算行数
select count(*) from emp;

计算非null值的个数
select count(comm) from emp;

日期运算

1、年月日加减法

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;

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

例如，想知道员工ALLEN和WARD的HIREDATE相差多少天。

select datediff( allen_hd, ward_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、计算两个日期之间相差的月份和年份

想找出两个日期之间相差多少个月或多少年。例如，希望知道第一个和最后一个员工的入职开始日期之间相差多少个月，同时想把这个差值换算成年。

select mnth, mnth/12
from (
select (year(max_hd) - year(min_hd))*12 + (month(max_hd) - month(min_hd)) as mnth
from (
select min(hiredate) as min_hd, max(hiredate) as max_hd
from emp
) x
) y;

4、计算两个日期之间相差的秒数、分钟数、小时数

例如计算ALLEN和WARD的hiredate之间相差的时间，秒、分、小时。

先计算相差的天数，再进行时间单位换算。

select datediff(allen_hd, ward_hd)*24 hr,
datediff(allen_hd, ward_hd)*24*60 min,
datediff(allen_hd, ward_hd)*24*60*60 sec
from (
select max(case when ename = 'WARD' then hiredate end) as ward_hd,
max(case when ename = 'ALLEN' then hiredate end) as allen_hd
from emp
) x;

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

计算两个日期之间相差多少天。例如，对于deptno等于10的部门的每一个员工，希望计算出他们入职日期之间相差多少天。

技巧：在早于当前入职时间的所有记录里找出hiredate的最小值，然后计算两个日期之间相差的天数

select x.*, datediff(x.hiredate, x.next_hd) 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;

 

 

6、计算这个月的第一天和最后一天的日期

select date_add(current_date, interval -day(current_date)+1 day) firstday,
last_day(current_date) lastday;

interval DATE_ADD() 

7、列出一年中每个季度的开始日期和结束日期

select quarter(adddate(dy, -1)) QTR, 
date_add(dy, interval -3 month) Q_start, 
adddate(dy, -1) Q_end
from (
select date_add(dy, interval (3*id) month) dy
from (
  select id, adddate(current_date, -dayofyear(current_date)+1) dy
  from t100
  where id <= 4
) x
) y;

dayofyear   DATE_ADD和ADDDATE函数：向日期添加指定时间间隔   QUARTER() 函数

8、依据特定时间单位检索数据

例如想找出入职月份为2月或者12月，并且入职当天是星期二的所有员工。

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

 

区间查询

1、定位连续的值空间

select * from V;

使用自连接找出包含连续值的行

select v1.proj_id, v1.proj_start, v1.proj_end
from V v1, V v2
where v1.proj_end = v2.proj_start;

高级查询

1、结果集分页

select sal from emp
order by sal limit 5 offset 0;

select sal from emp
order by sal limit 5 offset 5;

2、跳过 n 行记录

结果集排序后，隔行查询记录。使用标量子查询模拟实现员工名字排序。

select *
from (
select a.ename, 
(select count(*) from emp b where b.ename <= a.ename) as rn
from emp a
) x
where mod(x.rn, 2) = 1 order by rn;

3、在外连接查询中使用 or 逻辑

想返回部门编号为10 和20 的全体员工的名字和部门信息，以及部门编号为30和40的部门信息（但不包含员工信息）。

select e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
on (d.deptno = e.deptno and (e.deptno = 10 or e.deptno = 20))
order by 2;

错误示例1

select e.ename, d.deptno, d.dname, d.loc
from dept d, emp e
where d.deptno = e.deptno
and (e.deptno = 10 or e.deptno = 20)
order by 2;

这个是内连接。

错误示例2

select e.ename, d.deptno, d.dname, d.loc
from dept d left join emp e
on (d.deptno = e.deptno)
where e.deptno = 10 or e.deptno = 20 order by 2;

左外连接。

4、提取最靠前的N行记录

先排序，再提取。

返回前5种不同的工资值的员工。

select * from (
select 
(select count(distinct b.sal) from emp b where a.sal <= b.sal) as rnk,
a.sal, a.ename
from emp a ) x
where rnk <= 5
order by rnk;

返回工资是前5名的员工

select x.ename, x.sal
from
(select * from emp order by sal) x
order by x.sal desc limit 5;

 

5、找出最大和最小的记录

select ename, sal 
from emp 
where sal in ((select min(sal) from emp),(select max(sal) from emp));

 

6、查询未来的行

想找出这样的员工：当前员工的工资低于紧随其后入职的同事，找出工资低的那些人。

• 入职比他晚、且工资更高的员工当中最早入职的那个人的入职日期（next_sal_grtr）
• 入职比他晚的员工中最早入职的那个人的入职日期（next_hire）

select ename, sal, hiredate
from (
select a.ename, a.sal, a.hiredate, 
(select min(hiredate) from emp b where b.hiredate > a.hiredate and b.sal > a.sal) as next_sal_grtr,
(select min(hiredate) from emp b where b.hiredate > a.hiredate) as next_hire
from emp a
) x
where next_sal_grtr = next_hire;

7、对结果排序，允许Tie（平手）

select (
select count(distinct b.sal) from emp b where b.sal <= a.sal
) as rnk, a.sal
from emp a order by a.sal;

8、删除重复项

想找出emp表中不同的职位种类，但又不希望看到重复项。

select distinct job from emp;

select job from emp group by emp;