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
SQl Server 查询语句总结
最新推荐文章于 2021-04-22 23:51:29 发布