时间日期函数，类型转化，子查询，分页查询

1.时间日期函数：

SET DATEFIRST 1 --设置星期一为第一天
--datepart函数，返回时间日期中的某一个部分
--参数1是指返回哪一个部分，dw表示dayofweek
--参数2是指哪个时间日期里面去返回
--datefirst是系统常量，在使用时需要加上@@
SELECT @@DATEFIRST AS ‘1st Day‘, DATEPART(dw, GETDATE()) AS ‘Today‘
--getdate指在执行时获取当前系统时间
SELECT GETDATE()--在执行时取当前系统时间

--dateadd 对某一个部分添加或者减去多少时间
--参数1 是对哪一个部分进行添加或减少
--参数3 是在哪一个时间日期上进行增加或减少
--参数2 是增加或减去多少
select DATEADD(YEAR,-2,‘2015-11-20‘)
select DATEADD(MONTH,3,‘2015-11-20‘)
select DATEADD(DAY,5,‘2015-11-20‘)

--datediff 算时间差 different 不同的，相差的
--参数1 是指对哪一个部分进行算法
--参数2 是开始的时间日期
--参数3 是结束的日期
select DATEDIFF(YEAR,‘2011-1-2‘,‘2015-2-2‘) --年份相差
select DATEDIFF(MONTH,‘2011-1-2‘,‘2015-2-2‘)--月份相差(算上中间所有月份)
select DATEDIFF(DAY,‘2011-1-2‘,‘2015-2-2‘)--日相差(算上中间所有日)

--计算恋爱的天数
declare @startday varchar(50) --声明变量时需要添加数据类型,变量名前加@
set @startday = ‘2008-8-8‘ --设置变量的值
--getdate是获取当前系统时间
select DATEDIFF(DAY,@startday,GETDATE()) as 恋爱天数

--datepart 返回时间日期的某一个部分
--参数1是返回的哪一个部分
--参数2是以哪个日期作为被计算的日期
select DATEPART(YEAR,‘2008-8-8‘)--返回年
select DATEPART(DY,‘2008-8-8‘)--返回dayofyear 这一年的第几天
select DATEPART(QQ,‘2008-8-8‘)--返回季度 共四个季度

--day month year 相当于上面的datepart
--返回天,月,年
select DAY(‘2008-8-8‘)
select year(‘2008-8-8‘)
select month(‘2008-8-8‘)

--isdate 判断时间日期格式是否是正确
--返回值为1表示正确
--返回值为0表示错误
select ISDATE(‘2012-2-29‘)
select ISDATE(‘2008-80-8‘)

--sysdatetime 系统精确时间
select SYSDATETIME()
SELECT GETDATE()-- 区别是精确与否

2.类型转换：

--数据类型转换 cast convert
--cast 先写被转换的value + as + 被转换成的类型
select CAST(1.73 as int)
select CAST(1.73 as varchar(50))
select CAST(1.73333333 as decimal(18,2))
select cast(CAST(‘1.78‘ as decimal(18,2)) as int)
select CAST(‘1.78‘ as decimal(18,2))
--convert 参数1是需要转换成为的类型,参数2是需要被转换的value
select CONVERT(int , ‘87‘)
select CONVERT(decimal(18,2) , ‘87.8787‘)--在精确后面位数的时候会自动四舍五入
select CONVERT(varchar(50) , 342523.234)

3.子查询：

use Student
go
--需要建立两个表，一个部门表，一个人员表
--部门：部门的编号（主键），部门的名称，部门的职责
--人员：人员的编号，姓名，年龄，性别，cid，所属部门编号

create table bumen
(
bcode int primary key,
bname varchar(50),
bzhi varchar(50)
)
go
create table ren
(
code int identity(1,1),
--表示自增长列，从1开始，每次增加1
--在添加values时，不需要添加此列
name varchar(50),
age int,
sex char(10),
cid varchar(50),
bumen int
)
go

insert into bumen values(1001,‘人事部‘,‘负责人员面试考核‘)
insert into bumen values(1002,‘市场部‘,‘负责开拓市场‘)
insert into bumen values(1003,‘销售部‘,‘负责产品销售‘)
insert into bumen values(1004,‘生产部‘,‘负责产品生产‘)
insert into bumen values(1005,‘质检部‘,‘负责产品质量检验‘)
go

insert into ren values(‘张三‘,24,‘男‘,‘370303199903044440‘,1002)
insert into ren values(‘李四‘,25,‘女‘,‘370303199905044440‘,1001)
insert into ren values(‘王五‘,26,‘男‘,‘370303199907044440‘,1002)
insert into ren values(‘赵六‘,27,‘女‘,‘370303199908044440‘,1001)
insert into ren values(‘冯七‘,28,‘男‘,‘370303199909044440‘,1003)
insert into ren values(‘钱大‘,29,‘女‘,‘370303199901044440‘,1001)
insert into ren values(‘赵二‘,22,‘男‘,‘370303199911044440‘,1003)
insert into ren values(‘陈赫‘,31,‘男‘,‘370303199912044440‘,1001)
insert into ren values(‘王祖蓝‘,32,‘女‘,‘370303199904044440‘,1004)
insert into ren values(‘宝贝儿‘,33,‘男‘,‘370303199903034440‘,1004)
insert into ren values(‘李晨‘,34,‘女‘,‘370303199903224440‘,1004)
insert into ren values(‘范爷‘,35,‘男‘,‘370303199903234440‘,1005)
insert into ren values(‘任泉‘,36,‘男‘,‘370303199903254440‘,1002)
insert into ren values(‘李冰冰‘,37,‘男‘,‘370303199903264440‘,1003)
insert into ren values(‘冯小刚‘,38,‘女‘,‘370303199903274440‘,1005)
insert into ren values(‘郭德纲‘,39,‘男‘,‘370303199903284440‘,1004)
insert into ren values(‘赵本山‘,40,‘女‘,‘370303199903294440‘,1004)
go

select *from bumen
select * from ren
--查看销售部有哪些人
select bcode from bumen where bname=‘销售部‘
select * from ren where bumen =1003
--子查询(嵌套查询)
select * from ren where bumen =(select bcode from bumen where bname=‘销售部‘)

--查看张三所属的部门职责
select bumen from ren where name =‘张三‘
select bzhi from bumen where bcode =1002
--子查询
select bzhi as 部门职责 from bumen where bcode =(select bumen from ren where name =‘张三‘)

--查询部门人数最多的部门里面年龄最大的人员的所有信息
select top 1 bumen , COUNT(*) from ren group by bumen order by COUNT(*) desc
select top 1 * from ren where bumen =1004 order by age desc
--子查询
select top 1 * from ren where bumen =(select top 1 bumen from ren group by bumen order by COUNT(*) desc) order by age desc

--按照年龄排序,从小到大,不要前五个人的
select * from ren where code not in (select top 5 code from ren order by age)

--找男职员里面年纪最大的全部信息
select *from ren where code =( select top 1 code from ren where sex=‘男‘ order by age desc)

4.分页查询：

--分页查询 五条作为一页显示
--前五条
select top 5*from ren
--第6-10条
select top 5*from ren where code not in(select top 5 code from ren)
--第11-15条
select top 5*from ren where code not in(select top 10 code from ren)
--第16、17条（最后一次时可以把前面的top 5删除）
select *from ren where code not in(select top 15 code from ren)

--想在一开始就想知道能分成几页（五条一页）
select CEILING(COUNT(*)/5.0) from ren--注意要加上.0

select *from bumen
select * from ren
--将人员表内部门的编码转换成部门名称查询出来
select code,name ,age,sex,cid ,(select bname from bumen where bcode = ren.bumen) as 部门 from ren

--查询销售部里的年龄大于30岁的人的所有信息
select * from ren where age>30 and bumen =(select bcode from bumen where bname=‘销售部‘)
--exists 存在。与上句代码作用一致，不常用
select * from ren where exists(select * from bumen where bcode=ren.bumen and bname= ‘销售部‘) and age>30

时间日期函数，类型转化，子查询，分页查询