SQLServer2019-条件查询
- SQL中常用的运算符
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