use StudySQL 
go

--查询所有列所有行
---* 代表所有列
select * from Department
select * from [Rank]
select * from People

--查询指定列(姓名,性别,生日,月薪,电话)
select PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone
from People
--查询指定列(姓名,性别,生日,月薪,电话)(显示中文列名)
select PeopleName 姓名,PeopleSex 性别,PeopleBirth 生日,PeopleSalary 月薪,PeoplePhone 电话
from People
--查询员工所在城市(不重复distinct)
select distinct PeopleAddress from People
--查询工资上调20%后,员工数据
select  PeopleName,PeopleSex,PeopleSalary,PeopleSalary*1.2 加薪后工资
from People


--条件查询-----------------
select * from People
--查询性别为女的员工信息
select * from People
where PeopleSex='女'
--查询工资大于等于10000元的员工信息
select * from People
where PeopleSalary>=10000
--查询性别为女,工资大于等于10000的员工信息
select * from People
where PeopleSex='女' and PeopleSalary>=10000
--查询出生年月在1980-1-1之后,而且月薪月薪大于等于10000的女员工
select * from People
where PeopleBirth>='1980-1-1' and PeopleSex='女' and PeopleSalary>=10000
--查询月薪大于等于15000的员工,或者月薪大于等于8000的女员工
select * from People
where PeopleSalary>=15000 or (PeopleSex='女' and PeopleSalary>=8000)
--查询月薪在10000-20000之间的员工信息(多条件)
select * from People
where PeopleSalary between 10000 and 20000
--查询出地址在武汉或者北京的员工信息
select * from People
where PeopleAddress='武汉'or PeopleAddress='北京'
select * from People
where PeopleAddress in('武汉','北京')

-----排序
--查询所有员工信息,根据工资排序,降序
-- asc 升序(默认值)
--desc 降序
select * from People
order by PeopleSalary desc
--查询所有员工信息,根据名字长度排序,降序
select * from People order by len(PeopleName) desc
--查询出工资最高的5个人的信息
select top 5 * from People
order by PeopleSalary desc
--查询出工资最高的10%的员工信息
select top 10 percent * from People
order by PeopleSalary desc
--null:空值
insert into People(DepartmentId,RankId,PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone,PeopleAddTime)
values(1,1,'马云','男','1977-7-7',50000,'13858585858',getdate())
--IS NULL
--查询出地址没有填写的员工信息
select * from People  
where PeopleAddress is null
--查询出地址已经填写的员工信息
select * from People  
where PeopleAddress 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岁之间,并且工资在15000-30000之间的员工信息
--假设 年龄=当前年份-生日年份
select * from People
where (year(getdate())-year(PeopleBirth) between 30 and 40)
and (PeopleSalary between 15000 and 30000)
--查询出星座是巨蟹座(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='赵云')
--查询出和赵云在一个城市的人的信息
select * from People
where PeopleAddress=(select PeopleAddress from People where PeopleName='赵云')
--查询生肖是鼠的人员信息
--鼠,牛,虎,兔,龙,蛇,马,羊,猴,鸡,狗,猪
--4  5   6  7  8  9  10 11 0 1  2  3
select * from People
where year(PeopleBirth)%12=4
--添加列
--查询所有员工信息,添加一列,显示生肖
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