-查询出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'