卷王D4--数据库（从入门到跑路）

SQL sever学习-基础篇1（day4）

9、多条件查询

-查询出80后的员工信息
SELECT * FROM PEOPLE 
WHERE PEOPLEBIRTH >= '1980-1-1' AND PEOPLEBIRTH <= '1989-12-31'
--查询30-40岁之间，并且工资在15000-30000之间的员工信息
--假设 年龄 = 当前年份-生日年份
SELECT * FROM PEOPLE
WHERE (YEAR(GETDATE())-YEAR(PEOPLEBIRTH) >= 30 AND (YEAR(GETDATE())-YEAR(PEOPLEBIRTH)) < =40)
AND (PEOPLESALARY BETWEEN 5000 AND 7000)

--查询出工资比赵云高的人的信息
SELECT * FROM PEOPLE 
WHERE PEOPLESALARY > (SELECT PEOPLESALARY FROM PEOPLE WHERE PEOPLENAME = '孙权')


--查询出生肖是鼠的人员信息
--鼠，牛，虎，兔，龙，蛇，马，养，猴，鸡，狗，猪
--4,5,6,7,8,9,10,11,0,1,2,3,

SELECT * FROM PEOPLE
WHERE YEAR(PEOPLEBIRTH)%12 =3

--查询所有员工信息，添加一列，显示生肖
SELECT *, 
CASE
   WHEN YEAR(PEOPLEBIRTH)%12 =4THEN '鼠'
   WHEN YEAR(PEOPLEBIRTH)%12 =5THEN '牛'
   WHEN YEAR(PEOPLEBIRTH)%12 =6THEN '虎'
   WHEN YEAR(PEOPLEBIRTH)%12 =7THEN '兔'
   WHEN YEAR(PEOPLEBIRTH)%12 =8THEN '龙'
   WHEN YEAR(PEOPLEBIRTH)%12 =9THEN '蛇'
   WHEN YEAR(PEOPLEBIRTH)%12 =10THEN '马'
   WHEN YEAR(PEOPLEBIRTH)%12 =11THEN '羊'
   WHEN YEAR(PEOPLEBIRTH)%12 =0THEN '猴'
   WHEN YEAR(PEOPLEBIRTH)%12 =1THEN '鸡'
   WHEN YEAR(PEOPLEBIRTH)%12 =2THEN '狗'
   WHEN YEAR(PEOPLEBIRTH)%12 =3THEN '猪'
ELSE''
END 生肖
FROM PEOPLE

10、模糊查询

SELECT * FROM PEOPLE 
--查询出姓刘的员工信息(like)
--%:代表匹配0个字符，1个字符或者多个字符
--_：代表匹配有且只有1个字符
--[]:代表匹配范围内
--[^]:代表匹配不在范围内
SELECT * FROM PEOPLE WHERE PEOPLENAME like '刘%' 
SELECT SUBSTRING('JHDHHSHFIH ,HUHSFHSLIE',5,5)--取字符串中的第5位开始选取5位

--查询出电话号码开头137的 ,第四位好像是5或者6，最后一位是4
SELECT * FROM PEOPLE WHERE PEOPLEPHONE like '137%[5,6]%4'