sql server 条件查询案例

查询 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]'