屌丝学编程,SQL视频学习笔记
一、基础查询
(1)查询所有行所有列
--查询所有的部门
SELECT * FROM Department
--查询所有的职级
SELECT * FROM [Rank]
--查询所有的员工信息
SELECT * FROM People
(2)指定列查询(姓名,性别,生日,月薪,电话)
select PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone from People
(3)指定列查询,并自定义中文列名(姓名,性别,生日,月薪,电话)
select PeopleName 姓名,PeopleSex 性别,PeopleBirth 生日,PeopleSalary 月薪,PeoplePhone 电话 from People
(4)查询公司员工所在城市(不需要重复数据)
select distinct(PeopleAddress)from People
(5)假设工资普调20%,查询原始工资和调整后的工资,显示(姓名,性别,月薪,加薪后的月薪)(添加列查询)。
select PeopleName,PeopleSex,PeopleSalary,PeopleSalary*1.2 加薪后的工资 from people
二、条件查询
SQL中常用运算符:
=:等于,比较是否相等及赋值
!=:比较不等于
>:比较大于
<:比较小于
>=:比较大于等于
<=:比较小于等于
IS NULL:比较为空
IS NOT NULL:比较不为空
in:比较是否在其中
like:模糊查询
BETWEEN...AND...:比较是否在两者之间
and:逻辑与(两个条件同时成立表达式成立)
or:逻辑或(两个条件有一个成立表达式成立)
not:逻辑非(条件成立,表达式则不成立;条件不成立,表达式则成立)
查询示例: (1)根据指定列(姓名,性别,月薪,电话)查询性别为女的员工信息
select*from People where PeopleSex = '女'
(2)查询月薪大于等于10000 的员工信息( 单条件 )
select*from People where PeopleSalary >= 10000
(3)查询月薪大于等于10000 的女员工信息(多条件)
select*from People where PeopleSex = '女' and PeopleSalary >= 10000
(4)显示出出身年月在1980-1-1之后,而且月薪大于等于10000的女员工信息。
select*from People where PeopleBirth>='1980-1-1' and PeopleSex = '女' and PeopleSalary >= 10000
(5)显示出月薪大于等于15000 的员工,或者月薪大于等于8000的女员工信息。
select*from People where PeopleSalary >= 15000 or (PeopleSex = '女' and PeopleSalary >= 8000)
(6)查询月薪在10000-20000 之间员工信息( 多条件 )
select*from People where PeopleSalary >= 10000 and PeopleSalary <= 20000
或者
select*from People where PeopleSalary between 10000 and 20000
(7)查询出地址在北京或者上海的员工信息
select*from People where PeopleAddress in('北京','上海')
(8)查询所有员工信息(根据工资排序,降序排列)
--asc:升序,desc:降序;不写则是升序
select*from People order by PeopleSalary desc
(9)显示所有的员工信息,按照名字的长度进行倒序排列
select*from People order by len(PeopleName) desc
(10)查询工资最高的5个人的信息
select top 5*from People order by len(PeopleName) desc
(11)查询工资最高的10%的员工信息
select top 10percent*from People order by PeopleSalary desc
(12)查询出地址没有填写的员工信息
--null:空值;空值与空字符串不一样
select*from People where PeopleAddress is null
(13)查询出地址已经填写的员工信息
select*from People where PeopleAddress is not null
(14)查询所有的80后员工信息
select*from People where PeopleBirth >= '1980-1-1' and PeopleBirth < '1990-1-1'
或者
select*from People where year(PeopleBirth) between 1980 and 1989
(15)查询年龄在30-40 之间,并且工资在15000-30000 之间的员工信息
select*from People where
(year(getdate())-year(PeopleBirth) between 30 and 40)
and
(PeopleSalary between 15000 and 30000)
(16)查询出巨蟹 6.22--7.22 的员工信息
select*from People where
(month(PeopleBirth)=6 and day(PeopleBirth)>=22)
or
(month(PeopleBirth)=7 and day(PeopleBirth)<=22)
(17)查询工资比赵云高的人
select*from People where
PeopleSalary > (select PeopleSalary from People where PeopleName = '赵云' )
(18)查询出和赵云在同一个城市的人
select*from People where PeopleAddress = (select PeopleAddress from People where PeopleName = '赵云')
(19)查询出生肖为鼠的人员信息
-- 鼠、牛、虎、兔、龙、蛇、马、羊、猴、鸡、狗、猪
-- 4 、5 、6 、7 、8 、9、10、11、0 、1 、2 、3
select*from People where year(PeopleBirth) % 12 = 4
(20)查询所有员工信息,添加一列显示属相(鼠,牛,虎,兔,龙,蛇,马,羊,猴,鸡,狗,猪)
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
三、模糊查询
模糊查询使用like关键字和通配符结合来实现,通配符具体含义如下:
- %:代表匹配0个字符、1个字符或多个字符。
- _:代表匹配有且只有1个字符。
- []:代表匹配范围内
- [^]:代表匹配不在范围内
(1)查询姓刘的员工信息
select*from People where PeopleName like '刘%'
(2)查询名字中含有 " 尚 " 的员工信息
select*from People where PeopleName like '%尚%'
(3)显示名字中含有“尚”或者“史”的员工信息
select*from People where PeopleName like '%尚%'or PeopleName like '%史%'
(4)查询姓刘的员工,名字是2个字
select*from People where PeopleName like '刘_'
或者
select*from People where SUBSTRING(PeopleName,1,1) like '刘'
and len(PeopleName)=2
(5)查询出名字最后一个字是香,名字一共三个字的员工信息
select*from People where SUBSTRING(PeopleName,3,1) like '香'
and len(PeopleName)=3
或者
select*from People where PeopleName like '__香'
(6)查询出电话号码开头138的员工信息
select*from People where PeoplePhone like '138%'
(7)查询出电话号码开头138的员工信息,第4位可能是7,可能8 ,最后一个号码是5
select*from People where PeoplePhone like '138[7,8]%5'
(8)查询出电话号码开头133的员工信息,第4位是2-5之间的数字 ,最后一个号码不是2和3
select*from People where PeoplePhone like '138[2-5]%[^2,3]'