SQLServer2019-条件查询

SQL中常用的运算符

	SQL中常用的运算符
=	等于,比较是否相等及赋值
!=	比较不等于
>	比较大于
<	比较小于
>=	比较大于等于
<=	比较小于等于
IS NULL	比较为空
IS NOT NULL	比较不为空
in	比较是否在其中
like	模糊查询
BETWEEN…AND…	比较是否在两者之间
and	逻辑与(两个条件同时成立表达式成立)
or	逻辑或(两个条件有一个成立表达式成立)
not	逻辑非(条件成立,则表达式不成立;条件不成立,则表达式成立)

1.查询性别为女的员工信息

select*from People where PeopleSex = '女'

2.查询工资大于等于100000元的员工信息

select*from People where PeopleSalary>=100000

3.查询出性别为女,工资大于等于100000元的员工信息

select*from People where PeopleSex = '女'and PeopleSalary>=100000

4.查询月薪大于等于90000的员工,或者月薪大于等于60000的男员工

select*from People where 条件1 or 条件2
select*from People where PeopleSalary>=90000 or (PeopleSalary>=60000 and PeopleSex='男')

5.查询出出生年月在2003-1-1之后而且月薪大于100000的男员工

select*from People where PeopleBirth>=2003-1-1 and (PeopleSalary>=100000 and PeopleSex = '男')

6.查询月薪在70000到200000之间的员工信息(多条件)

select*from People where PeopleSalary>=70000 and PeopleSalary <=200000
select*from People where PeopleSalary between 70000 and 200000

7.查询出地址在重庆或河南的员工信息

select*from People where PeopleAddress ='重庆'or PeopleAddress ='河南'
select*from People where PeopleAddress in('重庆','河南')

asc:升序 (默认值,可以不写) desc:降序
8.查询所有的员工信息,根据工资排序,降序

select*from People order by PeopleSalary desc

9.查询所有的员工信息,根据名字的长度排序(降序)

select*from People order by len(PeopleName)desc

10.查询出工资最高的五个人的信息

select top 5*from People order by PeopleSalary desc

11.查询出工资最高的10%的员工信息

select top 10 percent *from People order by PeopleSalary desc

null:空值
12.查询出地址没有填写的员工信息

select*from People where PeopleAddress is null

13.查询出地址已经填写的用户信息

select*from People where PeopleAddress is not null

空字符串,给了地址,但是没有填写内容
14.查询没有填写地址的员工信息

select*from People where PeopleAddress =''

15.查询出00后的员工信息(推荐使用between…and…)

select*from People where PeopleBirth>'1999-12-31'and PeopleBirth<'2010-1-1'
select*from People where PeopleBirth between '2000-1-1'and '2010-1-1'
select*from People where year(PeopleBirth)between'2000'and'2010'

年龄=当前年份-出生年份
16.查询16-18岁之间,并且工资在60000-90000之间的员工信息

select*from People where(year(getdate())-year(PeopleBirth)>=16 and year(getdate())-year(PeopleBirth)<=18)
and (PeopleSalary>=60000and PeopleSalary<=90000)

select*from People where (year(getdate())-year(PeopleBirth) between 16 and 18)
and (PeopleSalary between'60000'and'90000')

17.查询出星座是射手座的员工信息(11.23-12.21)

select*from People where(month(PeopleBirth)=11 and day(PeopleBirth)>=23)or(month(PeopleBirth)=12 
and day(PeopleBirth)<=21)

18.查询出工资再晨光以上的员工信息

select*from People where PeopleSalary>
(select PeopleSalary from People where PeopleName ='晨光')

19.查询出和慕辰在一个城市的人的信息

select*from People where PeopleAddress=(select PeopleAddress from People where PeopleName='慕辰')

• 本尊2003年生,生肖为🐏,2003%12=11

生肖	对应数字
鼠	4
牛	5
虎	6
兔	7
龙	8
蛇	9
马	10
羊	11
猴	0
鸡	1
狗	2
猪	3

20…查询出生肖是🐏的人员信息

select*from People where year(PeopleBirth)%12=11

21.之前添加一列

select*,PeopleSalary+1000 from People

22.查询所有的员工信息,添加一列来显示她的生肖

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