05.条件查询

条件查询

SQL中常用的运算符

=     等于，比较是否相等及赋值
!=    比较不等于
>     比较大于
<     比较小于
>=    比较大于等于
<=    比较小于等于
IS NULL    比较为空
IS NOT NULL  比较不为空
in    比较是否在其中
like   模糊查询
BETWEEN...AND...     比较是否在两者之间
and    逻辑与
or     逻辑或
not   逻辑非

--查询员工为男的信息
select*from People where PeopleSex = '男'
--查询员工工资大于3000的员工信息
select*from People where PeopleSalary > 3000
--查询员工为男且工资大于3000的员工信息
select*from People where PeopleSex = '男' and PeopleSalary>3000
--查询出生日期在1980-1-1之后，月薪大于3000的男员工
select*from People 
where PeopleBirth>='1980-1-1' and PeopleSalary > 3000 
and PeopleSex = '男' 
--查询工资大于5000或者性别为女工资大于3000的员工信息
select*from People 
where PeopleSalary>5000 or (PeopleSex='女' and PeopleSalary>3000)
--查询工资在3000-5000之间的员工信息
select*from People where PeopleSalary >= 3000 and PeopleSalary <= 5000 --第1种
select*from People where PeopleSalary between 3000 and 5000  --第2种
--查询地址在北京或者武汉的员工信息
select*from People where PeopleAddr='武汉' or PeopleAddr='北京'
select*from People where PeopleAddr in ('武汉','北京')
--将员工信息按员工工资进行降序    升序(asc)  降序(desc)
--升序为默认值
select*from People order by PeopleSalary desc
--将员工信息按员工名字的长度进行降序
select*from People order by len(PeopleName) desc
--将员工工资最高的2个人显示出来
select top 2 *from People order by PeopleSalary desc
--将员工最高的3%的员工显示出来 
select top 3 percent *from People order by PeopleSalary desc
--查询员工地址没有填写的员工信息
select*from People where PeopleAddr is null
--查询员工地址填写的员工信息
select*from People where PeopleAddr is not null

--查询80后的员工
select*from People where PeopleBirth>='1980-1-1'
 and PeopleBirth<='1989-12-31'
select*from People where PeopleBirth between '1980-1-1' and '1989-12-31'
select*from People where year(PeopleBirth) between '1980' and '1989'

--查询30岁到40岁之间，工资在4000-7000之间的员工
select*from People where 
(year(getdate()) - year(PeopleBirth) between 30 and 40)
and (PeopleSalary>=4000 and PeopleSalary<=7000)
 
 --查询出出生日期(6.22-7.22)的员工信息
 select*from People where
 (month(PeopleBirth)=6 and day(PeopleBirth)>=22)
 or
 (month(PeopleBirth)=7 and day(PeopleBirth)<=22)

 --查询出工资比5号员工高的员工信息 子查询
 select*from People where 
 PeopleSalary > (select PeopleSalary from People where PeopleId=5)

--查询出生肖为猴的员工信息（2004%12=0）
select*from People where (year(PeopleBirth)%12 = 0)

--查询所有员工信息，添加一列，显示员工的生肖
select *, 
case
	when (year(PeopleBirth)%12 = 4) then '鼠'
	when (year(PeopleBirth)%12 = 5) then '牛'
	when (year(PeopleBirth)%12 = 6) then '虎'
	when (year(PeopleBirth)%12 = 7) then '兔'
	when (year(PeopleBirth)%12 = 8) then '龙'
	when (year(PeopleBirth)%12 = 9) then '蛇'
	when (year(PeopleBirth)%12 = 10) then '马'
	when (year(PeopleBirth)%12 = 11) then '羊'
	when (year(PeopleBirth)%12 = 0) then '猴'
	when (year(PeopleBirth)%12 = 1) then '鸡'
	when (year(PeopleBirth)%12 = 2) then '狗'
	when (year(PeopleBirth)%12 = 3) then '猪'
	else ''
end 生肖
from People 

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 '猪'
	else ''
end 生肖
from People