实验环境:公司SQL Server中名为bdqn的数据库中有employee表,结构如下:
表中数据如下:
分析主要使用五类函数
系统函数,字符串函数,日期函数聚合函数与数学函数,参考命令如下:
要求查询:
-
显示employee表中运维工程师的姓名和基本工资
select ‘运维’+name+‘的基本工资是 :’+cast(salary as varchar(10))+‘元’
from employee where job=‘运维’ -
显示距离当前10天之后的日期和时间
select DATEADD(dd,10,getdate()) -
显示employee表中所有人的姓名和年龄
select name,datediff (yy,birthday,getdate()) as 年龄 from employee -
显示employee表中所有90后员工的姓名和出生年份
select name,datename(year,birthday) as 出生年份 from employee where
birthday between ‘1990-01-01’ and ‘1999-12-31’ -
使用字符串函数,将字符串“Hello SQLServer”替换为“Hello Benet”
select REPLACE (‘hello SQL Server’,‘SQL Server’, ‘Benet’) -
使用日期函数,计算出你的生存天数
select datediff (dd,‘1989-12-11’,‘2020-12-18’) -
使用SQLServer函数,获取当前年份和月份
select getdate() -
计算工资总和、平均工资、最高工资、最低工资、90后员工人数
select sum(salary) as 工资总和 from employee
select avg(salary) as 平均工资 from employee
select max(salary) as 最高工资 from employee
select min(salary) as 最低工资 from employee
select count (*) as 九零后员工人数 from employee
where birthday>=‘1990-01-01’ -
查询employee表中每个职务的平均工资
select avg(salary) as 平均工资 from employee group by job -
查询employee表中平均工资小于5000的职务,员工“小绿”不计算在内
select job, avg(salary) as 每个职务工资小于5000 from employee
where name!=‘小绿’ group by job having avg (salary)<5000
select CEILING (avg(salary)) as 平均工资 from employee -
查询employee表中所有人的平均工资,用CEILING()函数取整
select CEILING (avg(salary)) as 平均工资 from employee -
查询未满30岁的员工的生日和年龄,并计算出距离30岁的天数,用字符串拼接显示结果
select name, birthday,DATEDIFF(yy,birthday,getdate()) as 年龄,datediff(dd,getdate(),
dateadd(yy,30,birthday)) as 距离30岁天数
from employee
where datediff(yy,birthday,getdate())<=30
order by birthday
实验截图: