本系列为@牛旦教育IT课堂在微头条上发布的内容,为便于查阅,特辑录于此,都是常用SQL基本用法。
前两篇连接:
(一):SQL点滴(查询篇):数据库基础查询案例实战
(二):SQL点滴(排序篇):数据常规排序查询实战示例
(三):常用SQL系列之:记录叠加、匹配、外连接及笛卡尔等
(四):常用SQL系列之:Null值、插入方式、默认值及复制等
(五):常用SQL系列之:多表和禁止插入、批量与特殊更新等
(六):常用SQL系列之:删除方式、数据库、表及索引元信息查询等
(七):常用SQL系列之:表约束、最大/小值、非null数、平均值等
(八):常用SQL系列之:列值累计、占比、平均值以及日期运算等
![228f7401d7ca19fb1ab4bdb0855cf0af.png](https://i-blog.csdnimg.cn/blog_migrate/d4e84f70423e9b0e670fdfda8a3b1a73.jpeg)
SQL点滴(51):如何计算两个日期之间相差的月数或年数?
也就是确定两个日期间的月份数或年份数,计算其间差。比如计算第一个员工和最后一个员工聘用日期相差的月份数,以及这些月折合的年数。首先来看——
1)MySQL的实现示例参考:
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 employee ) x
) y
注意这个写法,使用了函数Year和Month为给定的日期返回4位数年份和两位数月份。这个写法,也适用DB2。
2)MS SQL中的计算参考:
select datediff(month,min_hd,max_hd),datediff(month,min_hd,max_hd)/12
from( SELECT min( hiredate ) AS min_hd, max( hiredate ) AS max_hd FROM employee )x
若是Oracle中,其写法与此类似,只是所有内部函数不同,如下所示:
select months_between(min_hd,max_hd),months_between(min_hd,max_hd)/12
from( SELECT min( hiredate ) AS min_hd, max( hiredate ) AS max_hd FROM employee )x
这种计算,主要要想清楚他们的关系机理,你可以分别显示年份和月份以作对比,比如:
SELECT
YEAR( max_hd ), YEAR( min_hd ),
MONTH ( max_hd ),MONTH ( min_hd )
FROM
( SELECT min( hiredate ) AS min_hd, max( hiredate ) AS max_hd FROM employee ) x
![bf9d8864312767706615f2eb2171b8bd.png](https://i-blog.csdnimg.cn/blog_migrate/3c21597cf7440ffbb6ed8d4bc2c931e6.jpeg)
好了,动手试试吧。
SQL点滴(52):如何实现对查询结果集的分页操作?
进一步讲,就是给查询结果分页,或者“滚动显示”所查询的结果。比如针对人员信息表有500条记录,希望每次显示10行,那么酱油50页可以“翻阅”,从第1页到第50页,在操作页面上,体现为1-50页,可以一页页“点击翻页”,顺序查看每一页,如何在SQL中实现呢?首先,我们来看:
1)在MySQL中的实现:
select id,col01,col02,col03,coln from sometable order by id limit 10 offset 0
上面的意思为我们以id为主键排序 ,第一次从第一条记录开始,一次查看前10条记录。需要注意的是,关键字limit 后的数值,指定要查看多少记录,offerset定义了返回结果的第一条记录的偏移位置(就是从排序集中哪条记录开始依次返回10条记录,或跳过哪些记录),如果体现在页面上,那么offer 0可以看做从头显示第一页10条记录,那么第二页的偏移量offset就是从(2-1)*10,以此类推。当然,具体返回那些字段,根据需要指定。
这种SQL的写法,也适合PostgreSQL。
2)在Oracle中的实现:
select col1,col2,coln
from(
select row_number() over (order by id) as rn,col1,col2,coln from sometable
) x where rn between 1 and 10 .
在where子句的between 和and后的数字,就是表示要返回从那条记录到哪条记录的返回值返回。
此写法同样适用DB2和MS SQL Server。row_number()给每行记录非配唯一序号,以便可以明确返回需要的页记录。
另外,对于Oracle,还有另外一种 方式,使用rownum来生成唯一记录号。
Oracle的ROWNUM的具体SQL实现,自己动手实现一下吧。
SQL点滴(53):小花招—怎样使用SQL语句来跳过表中的指定n行返回所有结果?
比如说,从employee表中每隔一行返回一名员工,或者查找第1、3、5等以此类推的记录,如何实现?其实这个实现可以基于我前面介绍的翻页模式来实现。具体来参考如下实现:
1)MySQL间隔模式返回结果:
SELECT
x.ename
FROM
(
SELECT
a.ename,
( SELECT count( * ) FROM employee b WHERE b.ename <= a.ename ) AS rn
FROM
employee a
) x
WHERE
MOD ( x.rn, 2 ) =1
由于MySQL没有为行分等级或给行编号的函数,所以需要用子查询给行分顺序等级(作为行编号,本查询中用名字来进行分级实现记录的行编号),然后使用求模操作来跳过行。
上面的SQL语句也适用PostgreSQL。
2)在Oracle中的实现:
select ename from (
select row_number() over (order by ename) rn,ename from employee
) x
where mod(rn,2) = 1
关于求模,上面的写法也适用DB2,若在MS SQL中,则用%运算符。上面的语句将跳过偶数行号。
自己动手试试吧。
SQL点滴(54):如何查询指定部门的员工和部门名称信息,而其余部门只显示部门名称而无员工信息?
比如说,只返回部门编号为2和3的部门员工信息以及部门信息,而编号为1和4的只返回部门信息,如何实现。其实,这个用外连接并在连接中用or逻辑即可。参考SQL如下:
SELECTe.ename,d.dpid,d.dpname,d.dpaddressFROMdepartment dLEFT JOIN employee e ON ( d.dpid = e.departid AND ( e.departid = 2 OR e.departid = 3 ) )ORDER BY 1
注意,order by 1,是指按ename进行默认升序排序,如果是2,则指定位dpid升序排序。
这个写法适合MySQL、DB2、PostgreSQL以及SQL Server。也适用Oracle9i及以上版本。
另外,上面的语句还可以用e.departid进行筛选,然后进行外部链接,如下所示:
SELECT
e.ename,
d.dpid,
d.dpname,
d.dpaddress
FROM
department d
LEFT JOIN
( select ename,departid from employee where departid in(2,3)
) e on (e.departid = d.dpid )
order by 1
自己动手试试吧。如果有错,记得找找原因哦 ^_^
SQL点滴(55):如何通过查询语句为返回结果分级并返回感兴趣的结果?
比如说,对employee表的工资进行分级,然后返回想要的等级的相关数据。比如薪资一样的为同级别,不同的为不同级别。如果有10条记录,那分出来级别是小于等于10的。
在MySQL中,我使用了子查询,为每个工资创建了一个等级,然后利用等级做条件返回结果,参考语句如下:
第一步,可以查分级结果;
SELECT
( SELECT count( DISTINCT b.salary ) FROM employee b WHERE a.salary <= b.salary ) AS rnk,
a.salary,
a.ename
FROM
employee a order by 1
第二步,把分级结果作为查询对象,添加where子句来返回感兴趣的结果:
select * from x where rnk<=3
x指代第一步的查询语句(作为第二步的子查询),试试吧。我这里演示的两步结果如图所示。
![6f84841bdab4df66c57d32895fd00943.png](https://i-blog.csdnimg.cn/blog_migrate/5a3b8d0d9c992d49d1637f1f9ce901d7.jpeg)
另外,可以看看本号@牛旦教育IT课堂 中关于JPA学习文章:系列总结:JPA核心接口综合实战案例