数据库 查询 复习 （详细）

 

--查询所有列所有行
select *from department


--查询指定列（姓名，性别，生日，月薪，电话）
select people name,peoplesex,peoplebirth,peoplesalary,peoplephone from people


--查询指定列（姓名，性别，生日，月薪，电话）(显示别名)
select people name 姓名,peoplesex 性别,peoplebirth 生日,peoplesalary 月薪,peoplephone 电话from people


--查询出员工所在城市（不需要重复数据）
select distinct(peopleaddress) from people


--假设准备加工资（上调20%），查询出加工资后的员工数据
select peoplesalary*1.2 加薪后的工资from people

条件查询 

--查询性别为女的员工信息
select *from people where peoplesex ='女'


--查询工资大于等于10000元的员工信息
select *from people where peoplesalay>=10000


--查询性别为女，工资大于等于10000元
select *from people where peoplesex ='女'and peoplesalay>=10000


--查询性别为女工资大于8000或工资大于等于10000元
select *from people where (peoplesex ='女' and peoplesalay>=8000)or peoplesalay>=10000


--查询月薪在10000-200000之间的员工信息
select *from people where peoplesalary between 10000 and 20000


--查询出地址在武汉或者北京的员工信息
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 top5 *from people order by peoplesalary desc


--查询工资最高的10%的员工信息
select top 10 percent *from people order by peoplesalary desc


--null:空值
--查询出地址没有填写的员工信息
select *from people where peopleaddress is null
--查询地址已经填写的员工信息
select *from people where peopleaddress is not null

--查询出工资比赵云高的人的信息
select *from people where peoplesalary >(select peoplesalary from where peoplename ='赵云')


--查询所有员工信息，添加一列，显示生肖
select *,
case year(peoplebirth)%12
    when 4 then '鼠'
    when 5 then '牛'
    else''
end 生肖

模糊查询

--查询出姓刘的员工信息
select *from people where peoplename like '刘%'




--查询出名字中含有“尚”的员工信息
select *from people where peoplename like '%尚%'





--查询名字中含有“尚”或者“史”的员工信息
select *from people where peoplename like '%尚%' or peoplename like '%史%'





--查询姓刘的员工信息，名字是两个字
select *from people where peoplename like '刘_'

select *from people where SUBSTRING(peoplename,1,1)='刘'
and len(peoplename)=2





--查询名字最后一个字为香，名字一共是三个字的员工信息
select *from people where peoplename like '__香'


select *from people where SUBSTRING(peoplename,3,1)='香'
and len(peoplename)=3




--查询出电话开头为138的，第四位是7或8，最后一位是5
select *from people where peoplephone like '138[7,8]%5'





--查询出电话号码开头为138的，第四位是2-5之间，最后一个号码不是2和3
select *from people where peoplephone like '138[2-5]%[^2,3]'