SQL SERVER学习笔记(四)

一. SQL中常用的运算符
= 比较是否相等及赋值
!= 不等于
大于> >= 大于等于
< 小于 <= 小于等于
IS NULL 是空值
IS NOT NULL 不是空值
in 判断是否在其中
like 模糊查询
between…and… 判断是否在两者之间
and or not 逻辑与或非

1.查询工资在10000-20000之间的员工信息

select * from People where Salary between 10000 and 20000
select * from People where Salary >= 10000 and Salary <= 20000

2.查询地址在北京或上海或河南或广东的员工

select * from People where Address = '北京' or Address = '上海' or Address = '河南' or Address = '广东'			-----or 的条件过多时，代码会很长
select * from People where Address in('北京','上海','河南','广东')					-----in运算符的使用 ： 简化

3.查询所有员工信息，根据工资排序

select * from People order by Salary asc			--升序(默认asc，可不写)
select top 5 * from People order by Salary desc		--降序：工资高的前五位
select top 10 percent * from People order by Salary desc		--降序：工资高的前10%
select * from People order by LEN([Name]) desc		--按名字长度降序

4.查询所有80后员工信息

select * from People where Birth >= '1980-1-1' and Birth <= '1989-12-31'
select * from People where Birth between '1980-1-1' and '1989-12-31'
select * from People where YEAR(Birth) between 1980 and 1989

5.查询所有20-40岁的员工信息

select * from People where YEAR(GETDATE()) - YEAR(Birth) between 20 and 40

6.查询狮子座的员工信息(7.23 - 8.23)

select * from People where (MONTH(Birth) = 7 and DAY(Birth) >= 23) or (MONTH(Birth) = 8 and DAY(Birth) <= 23)

7.查询出工资比马超高的员工信息 (子查询：查出马超的工资)

select * from People where Salary > (select Salary from People where [Name] = '马超')

8.查询所有的员工的信息并新增生肖列

select *,					----生肖为查询新增列  需要用,隔开
case YEAR(Birth) % 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

9.只查询所有员工的姓名 生日及生肖

select [Name] 姓名,Birth 生日,
case YEAR(Birth) % 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