SQl Server 查询语句总结

use yggl

--选择列。
--1.查询表中的全部内容
select * from departments

--2.查询表中指定的列。
select departmentname,departmentid from departments

--3.定义列别名
select * from EMPLOYEES
select name as 姓名, education as 学历 from  EMPLOYEES where sex=1
select name  姓名, education  学历 from  EMPLOYEES where sex=0
select 姓名=name,学历=education from EMPLOYEES where sex=0 

--4.替换查询结果中的数据
select name,等级=
case
	when workyear<2 then '刚来'
	when workyear>=2 and workyear<=5 then  '来了一段时间'
	else '来了很长时间'
	end
from EMPLOYEES where sex=1

--5.计算列值
select name,工作时间=workyear*0.1 from EMPLOYEES where sex=0

--6.消除结果集中的重复行。
select distinct workyear from EMPLOYEES

--7.限制结果集返回行数
select top 6  name from EMPLOYEES

--8.聚合函数
select * from salary
select COUNT(income) as '收入在3000以上' from salary 
where income>3000 
select max(income) as '收入最高' from salary

--where子句
--1.模式匹配
select name  from EMPLOYEES 
select * from EMPLOYEES where name like '李_'--下划线代表单个字符
select * from EMPLOYEES where name like '李丽'

--2.范围比较
select name,workyear from EMPLOYEES 
where workyear not between 5 and 8 

select name,workyear from EMPLOYEES 
where workyear in (5,8)

--3.空值比较
select * from EMPLOYEES where sex=null

--4.子查询
select * from EMPLOYEES
where WORKYEAR in 
(select workyear from EMPLOYEES where name like '李_')

select name from EMPLOYEES
where EMPLOYEEID=
(select EMPLOYEEID from salary where income=1980)

--from子句
select employeeid from 
(select employeeid from salary where income>3000) as tb

--group by 子句
select name from EMPLOYEES group by name