目录
本文所有操作都是基于scott库
SQL Server 附加Scott数据库的方法:
https://www.cnblogs.com/fenglinyu/p/11067097.html?ivk_sa=1024320u
计算列
use scott
go
-- 计算列
select * from emp
-- * 表示所有
-- from emp 从emp表中查
select empno, ename from emp
select ename, sal from emp
select ename "姓名", "月薪"=sal, sal*12 as "年薪", job from emp
distinct (不重复的)
-- distinct 不允许重复的
select deptno from emp
select distinct deptno from emp
select comm from emp
select distinct comm from emp -- 可以过滤重复的null
select distinct comm, deptno from emp -- 整体过滤(组合过滤)
between (两者之间)
--between 两者之间(闭区间)
-- 查找工资在1500~3000之间(包括1500,3000)的所有员工
select * from emp
where sal>=1500 and sal<=3000
-- 等价于
select * from emp
where sal between 1500 and 3000
-- 查找工资小于1500或大于3000的所有员工信息
select * from emp
where sal < 1500 or sal > 3000
select * from emp
where sal not between 1500 and 3000
in (属于若干个孤立的值)
--in 属于若干个孤立的值
select * from emp
where sal in (1500,3000,5000)
--等价于
select * from emp
where sal = 1500 or sal=3000 or sal=5000
select * from emp
where sal not in (1500,3000,5000)
-- 等价于
select * from emp
where sal<>1500 and sal<>3000 and sal<>5000
-- 数据库中不等与有两种 != <>
top
-- top
select top 2 * from emp
select top 20 percent * from emp -- 输出前20%(小数会加一)
-- 工资1500到3000之间的前四个输出
select top 4 * from emp
where sal between 1500 and 3000
order by sal desc
null
--null
-- 输出奖金非空的员工信息
select * from emp
where comm <> null
-- null 不能参与<> != =运算
-- 可以参与 is is not
select * from emp where comm is null -- 输出奖金为空的员工信息
select * from emp where comm is not null -- 输出奖金不为空的员工信息
-- 输出每个员工的姓名,年薪(包含了奖金)
-- select ename, sal*12+comm "年薪" from emp -- 错误写法,不能参与任何数字运算,否则结果为空
select ename, sal*12 + isnull(comm, 0) "年薪" from emp -- isnull(comm, 0) 如果comm是null就返回0 否则返回comm的值
order by(排序)
--order by 以某个字段排序
select * from emp order by sal -- 默认按照升序排序
select * from emp order by deptno,sal -- 先按deptno排序,如果deptno相同,再按sal排序,在每个deptno分段中都是有序的
select * from emp order by deptno desc, sal--先deptno降序,如果deptno相同,再sal升序
模糊查询
-- 模糊查询
-- % 表示任意零个或多个字符
select * from emp where ename like '%A%' -- 只要含有A
select * from emp where ename like '%A' -- 只要结尾是A
select * from emp where ename like 'A%' -- 只要首字母是A
-- _ 表示任意单个字符
select * from emp where ename like '_A%' -- 只要第二个是A
-- [a-f] 表示a~f的任意单个字符
select * from emp where ename like '_[A-F]%' -- 第二个字母只要是[A,F]之间
-- [a,f] 表示a或f
select * from emp where ename like '[A,F]%' -- 第一个字符只要是A或F
-- [^A-F] 表示[A-F]之外的
select * from emp where ename like '[^A-F]%' -- 第一个字符不是[A-F]
select * from emp where ename like '%\%%' escape '\' -- 将 \ 看作转义字符,\ 后面的第一个 字符 当作普通字符
聚合函数
-- 聚合函数
/*
函数的分类:
单行函数 每行返回一个值
select lower(ename)from emp
多行函数 多行返回一个值 聚合函数属于多行函数
select max(sal)from emp
单行函数和多行函数不能混用
聚合函数分类: max() min() avg()平均值 count()求个数
*/
select count(*) from emp -- 返回emp表所有记录的个数
select count(deptno) from emp --返回14行 说明deptno重复的记录也当作有效的记录
select count(distinct deptno) from emp -- 返回3行 说明没有统计deptno重复的记录
select count(comm) from emp -- 返回4行 说明comm为null的记录不被当有效记录
select max(sal) "最高工资", min(sal) "最低工资", count(*) "人数" from emp
group by(分组)
-- group by
--输出每个部门的编号,平均工资
select deptno, avg(sal) "部门平均工资" from emp
group by deptno
-- 使用了 group by 之后 select 中只能出现分组后的整体信息,不能出现详细信息
select deptno, job, avg(sal) "平均工资", count(*) "职位人数",sum(sal) "职位最高工资",min(sal) "职位最低工资"
from emp
group by deptno, job
order by deptno
--group by a,b 先按a分组,a相同再按b分组,最终统计的是最小分组的信息
having(对分组后的数据过滤)
-- having 对分组之后的信息进行过滤
/*
having是用来对分组后的信息过滤,因此使用having时通常会先使用group by
如果没有使用group by 但使用了having意味着把所有数据当成一组进行过滤
having子句出现的字段必须时分组之后的组的整体信息,不能出现组内的详细信息
尽管select字段中可以出现别名,但是having子句中不能出现字段的别名
*/
-- 输出部门平均工资大于2000的部门的部门编号 部门的平均工资
select deptno "部门编号", avg(sal) "平均工资" from emp
group by deptno
having avg(sal) > 2000
-- 把工资大于2000的
-- 输出部门平均工资大于3000的部门的部门编号 部门的平均工资
select deptno "部门编号", avg(sal) "平均工资", count(*) "部门人数",max(sal)"部门最高工资" from emp
where sal > 2000 -- 对原始的数据过滤
group by deptno
having avg(sal) > 3000 -- 对分组之后的数据过滤
/*
having 和 where 的异同:
相同:
都是对数据过滤,只保留有效的数据
都不允许出现字段的别名
不同:
where是对原始数据的过滤 having是对分组之后的数据过滤
where必须写在having之前,不可颠倒
*/