第8章 日期运算
8.1 加减年月日
MYSQL:
select empno,hiredate ,ename from emp
empno hiredate ename
7369 1980-12-17 SMITH
7499 1981-02-20 ALLEN
7521 1981-02-22 WARD
7566 1981-04-02 JONES
7654 1981-09-28 MARTIN
7698 1981-05-01 BLAKE
7782 1981-06-09 CLARK
7788 1982-12-09 SCOTT
7839 1981-11-17 KING
7844 1981-09-08 TURNER
7876 1983-01-12 ADAMS
7900 1981-12-03 JAMES
7902 1981-12-03 FORD
7934 1982-01-23 MILLER
使用关键字 interval 指定单位, 有点类似python中的timedelta:
select empno,hiredate,hiredate + interval 5 year as h_plus5y,
hiredate - interval 5 month as h_minus5m,
hiredate - interval 5 day as h_minus5d
from emp
empno hiredate h_plus5y h_minus5m h_minus5d
7369 1980-12-17 1985-12-17 1980-07-17 1980-12-12
7499 1981-02-20 1986-02-20 1980-09-20 1981-02-15
7521 1981-02-22 1986-02-22 1980-09-22 1981-02-17
7566 1981-04-02 1986-04-02 1980-11-02 1981-03-28
7654 1981-09-28 1986-09-28 1981-04-28 1981-09-23
7698 1981-05-01 1986-05-01 1980-12-01 1981-04-26
7782 1981-06-09 1986-06-09 1981-01-09 1981-06-04
7788 1982-12-09 1987-12-09 1982-07-09 1982-12-04
7839 1981-11-17 1986-11-17 1981-06-17 1981-11-12
7844 1981-09-08 1986-09-08 1981-04-08 1981-09-03
7876 1983-01-12 1988-01-12 1982-08-12 1983-01-07
7900 1981-12-03 1986-12-03 1981-07-03 1981-11-28
7902 1981-12-03 1986-12-03 1981-07-03 1981-11-28
7934 1982-01-23 1987-01-23 1981-08-23 1982-01-18
8.2 计算两个日期之间的天数
计算 ward 和 allen 聘用日期相差的天数
select ward_h - allen_h from
(select hiredate as ward_h from emp where ename='ward') x,
(select hiredate as allen_h from emp where ename='allen') y
ward_h - allen_h
2
8.3 确定两个日期之间的工作日数目
思路是创建一个基干表,然后和这个表比较. 用python处理更方便.
(略)
import datetime
week = datetime.datetime.strptime("2020-05-04","%Y-%m-%d").weekday()
print(week)
# 该方法返回数字0—6，依次代表周一到周天
8.4 确定两个日期之间的月份数或年数
(略)
8.5 确定两个日期之间的秒,分,小时数
(略)
8.6 计算一年中周内各日期的次数
计算周一,周二,…,周日出现的次数:这得多无聊
(略)
以下是python实现
import datetime
def get_week_days(intYear):
# 计算指定年份周一,周二,...,周日出现的次数:这得多无聊
days = (datetime.date(intYear + 1,1,1) - datetime.date(intYear ,1,1)).days
week_days = [0] *7
for i in range(days):
theDay = datetime.date(intYear,1,1) + datetime.timedelta(days=i)
week_days[theDay.weekday()] +=1
for i in range(7):
print('Days of week ' + str(i+1),' of year ',str(intYear),':',week_days[i])
return week_days
if __name__=='__main__':
get_week_days(2020)
Days of week 1 of year 2020 : 52
Days of week 2 of year 2020 : 52
Days of week 3 of year 2020 : 53
Days of week 4 of year 2020 : 53
Days of week 5 of year 2020 : 52
Days of week 6 of year 2020 : 52
Days of week 7 of year 2020 : 52
8.7 确定当前记录和下一条记录之间相差的天数
(略)
第9章 日期操作
9.1 确定一年是否为闰年
判定公历闰年应遵循的一般规律为:四年一闰，百年不闰，四百年再闰.
(略)
9.2 确定1年内的天数
(略)
9.3 从日期中提取时间的各部分
(略)
9.4 确定某个月的第一天和最后一天
基本思路: 下个月的第一天减去1天就是上个月的最后一天
(略)
9.5 确定一年内属于周内某一天的所有日期
思路参考 8.6 计算一年中周内各日期的次数
(略)
9.6 确定某月内第一个和最后一个“周内某天”的日期
(略)
9.7 创建日历
(略)
9.8 列出一年中每个季度的开始日期和结束日期
(略)
9.9 确定某个给定季度的开始日期和结束日期
(略)
9.10 填充丢失的日期
思路: 按月统计某列的sum
(略)
9.11 按照给定的时间单位进行查找
(略)
9.12 使用日期的特殊部分比较记录
(略)
9.13 识别重叠的日期范围
(略)
第10章 范围处理
(略)
第11章 高级查找
11.1 给结果集分页
MySQL,PG:
- limit 指定返回的行数
- offset 指定跳过的行数
select empno,sal from emp order by sal limit 5 offset 0
empno sal
7369 800.00
7900 950.00
7876 1100.00
7521 1250.00
7654 1250.00
select empno,sal from emp order by sal limit 5 offset 5
empno sal
7934 1300.00
7844 1500.00
7499 1600.00
7782 2450.00
7698 2850.00
11.2 跳过表中特定的行
隔行返回员工数据
MySQL,PG:
- 使用名字计数增加一列 rn 序号,用于取模计算
select *, (select count(*) from emp b where b.ename <= a.ename) as rn from emp a order by rn
empno ename job mgr hiredate sal comm deptno rn
7876 ADAMS CLERK 7788 1983-01-12 1100.00 NULL 20 1
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30 2
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30 3
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10 4
7902 FORD ANALYST 7566 1981-12-03 3000.00 NULL 20 5
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30 6
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20 7
7839 KING PRESIDENT NULL 1981-11-17 5000.00 NULL 10 8
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 9
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10 10
7788 SCOTT ANALYST 7566 1982-12-09 3000.00 NULL 20 11
7369 SMITH CLERK 7902 1980-12-17 800.00 NULL 20 12
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 13
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30 14
2.取模筛选
select * from (
select *, (select count(*) from emp b where b.ename <= a.ename) as rn
from emp a order by rn
) x
where mod(x.rn,2)=1 order by x.rn
empno ename job mgr hiredate sal comm deptno rn
7876 ADAMS CLERK 7788 1983-01-12 1100.00 NULL 20 1
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30 3
7902 FORD ANALYST 7566 1981-12-03 3000.00 NULL 20 5
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20 7
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30 9
7788 SCOTT ANALYST 7566 1982-12-09 3000.00 NULL 20 11
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30 13
11.3 在外连接中使用 OR逻辑
返回10,20 中所有员工的姓名和部门信息,并返回30,40的部门信息(不包括员工信息)
select emp.ename,dept.deptno,dept.dname,dept.loc from emp
right join dept on (emp.deptno=dept.deptno and (emp.deptno='10' or emp.deptno='20') ) order by dept.deptno
ename deptno dname loc
CLARK 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
KING 10 ACCOUNTING NEW YORK
SCOTT 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
JONES 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
NULL 30 SALES CHICAGO
NULL 40 OPERATIONS BOSTON
- right join dept, 会列出所有的 dept
- 不要在join平级使用where子句
11.4 确定哪些行是彼此可以互换的
(略)
11.5 选择top n个记录
select ename,sal from emp order by sal desc limit 5
ename sal
KING 5000.00
FORD 3000.00
SCOTT 3000.00
JONES 2975.00
BLAKE 2850.00
11.6 找到包含最大值和最小值的记录
select * from emp where sal in ((select max(sal) from emp),(select min(sal) from emp))
empno ename job mgr hiredate sal comm deptno
7369 SMITH CLERK 7902 1980-12-17 800.00 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.00 NULL 10
11.7 存取"未来"行
(略)
11.8 轮换行值
(略)
11.9 给结果分等级
(略)
11.10 抑制重复
使用关键字 distinct
select distinct job from emp
job
CLERK
SALESMAN
MANAGER
ANALYST
PRESIDENT
11.11 找到骑士值
(略)
11.12 生成简单的预测
(略)
正文完
