查询 80 后员工信息:
select * from People
where PeopleBirth >= '1980-1-1' and PeopleBirth <= '1989-12-31'
-- 或者使用 between
select * from People
where PeopleBirth between '1980-1-1' and '1989-12-31'
-- 也可以使用 year()
select * from People
where year(PeopleBirth ) between 1980 and 1989
查询 30-40 岁之间,并且工资在 15000-30000之间的员工信息
select * from People
where (year(getDate())-year(PeopleBirth) between 30 and 40)
and (PeopleSalary between 15000 and 3000)
-
getDate()
:获取当前时间
查询星座是巨蟹座的员工信息(6.22-7.22)
select * from People
where (month(PeopleBirth)=6 and day(PeopleBirth) >= 22)
or (month(PeopleBirth)=7 and day(PeopleBirth) <= 22)
查询出工资比赵云高的人的信息
select * from People
where PeopleSalary > (select PeopleSalary from People where PeopleName = '赵云')
查询出生肖是鼠的人员信息
取一个属鼠的年份,如 1984,年数除以 12 根据余数判断即可。
鼠4、牛5、虎6、兔7、龙8、蛇9、马10、羊11、猴0、鸡1、狗2、猪3
select * from People where year(PeopleBirth)%12 = 4
查询所有员工信息,添加一列,显示生肖
select *
case year(PeopleBirth)%12
when 4 then '鼠'
when 5 then '牛'
when 6 then '虎'
when 7 then '兔'
when 8 then '龙'
when 9 then '蛇'
when 10 then '马'
when 11 then '羊'
when 0 then '猴'
when 1 then '鸡'
when 2 then '狗'
when 3 then '猪'
end 生肖
from People
查询姓刘的员工信息,名字是两个字
-- 使用 like
select * from People where PeopleName like '刘_'
-- 也可以使用函数sbustring()
select * from People
where SUBSTRING(PeopleName,1,1) = '刘' and len(PeopleName)=2
查询电话号码 138开头,第四位是 7 或 8,最后一个号码是 5:
select * from People
where PeoplePhone like '138[7,8]%5'
查询电话号码 138开头,第四位是 2 到 5 之间,最后一个号码不是 2 和 3:
select * from People
where PeoplePhone like '138[2-5]%[^2,3]'
网友评论