SQL Server【二】单表查询

查询

计算列

select * from emp;
-- *通配符，表示所有的字段
-- from emp 从emp表查询

select empno, ename from emp;
select ename as "员工姓名", sal*12 as "年薪" from emp;

-- as可以省略，用于设置字段名
-- 注意用双引号将字段名括起来，单引号虽然可以但是无法移植

select 5 from emp;
-- 输出一个字段，字段的长度是emp记录的个数，每行只有一个元素5，无字段名

select 5;
-- 输出一个字段，长度为1，元素为5，无字段名，不推荐，Oracle不知此

在Oracle中字段的别名不允许单引号括起来，但是SQL Server允许。

dictinct

select distinct deptno from emp;
-- distinct 会过滤重复的数据
select distinct comm from emp;
-- distinct 可以过滤重复的null
select distinct comm, deptno from emp;
-- distinct 可以过滤组合
select deptno ,distinct comm from emp;
--error，逻辑上有冲突

between

select * from emp 
where sal >=1500 and sal <= 3000

select * from emp
where sal between 1500 and 3000

--between 字段元素在某个范围内

in

属于若干个孤立的值

select * from emp where sal in (1500, 3000, 5000);
--等价于
--in表示孤立的值
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

select top 2 * from emp;
select top 15 percent * from emp;
--如果不是整数，向上取整，例如2.1->3
--使用top可是实现分页查询，Oracle不支持top，应该使用rownum<x的方式
--把工资在1500~3000之间的员工工资最高的两个人的信息输出
select top 2 * from emp
where sal between 1500 and 3000
order by sal desc

如果没有使用order by则默认对主键进行排序

null

没有值，空值

--输出奖金非空的员工的信息
select * from emp where comm <> null;
--error:null不能参与布尔判断

--可以使用is和not is
select * from emp where comm is null;
--0和null不一样，null表示空值，0表示整型数字
--任何类型的数据都允许为null

--输出每个员工的姓名 年薪（包含了奖金） comm假设是一年的奖金

select ename as '姓名' , sal * 12 + comm as '年薪' from emp;
--error:null值无法参与运算
--任何数字和null运算结果都是null

select ename as '姓名', sal * 12 + isnull(comm, 0) as '年薪' from emp
--correct
--isnull(字段， 默认值)表示如果字段为空返回默认值

order by

以某个字段排序

select * from emp order by sal 
-- 默认是按照升序排序， asc可以省略
select * from emp order by deptno, sal, comm
--先按照第一个字段排序，再将相同的按照第二个字段排序

select * from emp order by deptno, sal, comm desc
--每一个desc或者asc只对一个字段生效
--即为一个字段指定的排序标准不会对其他字段产生影响

NULL值是所有值中最小的
order by常常和top一起使用

模糊查询

select 字段的集合 from 表名 where 某个字段的名字 like 匹配的条件

匹配的条件通常含有通配符

• %任意0个或者多个字符，例如%A%表示包含字母A的字符串

select * from emp where ename like 'A%';

• _任意的单个字符
• [a-f]a到f之间的任意一个字符
• [a,f]a或者f
• [^a-f]不是a到f之间的字符

select * from emp where ename like '[^A-F]%'

• 如果想要查找%或者_，需要使用转义字符进行转义，格式为

select * from emp where name like '%\%%' escape '\'

escape后面跟的字符表示转义标志字符，也可以为其他的，一般习惯用反斜杠

聚合函数

函数分为单行函数和多行函数

• 单行函数：每一行都进行操作，例如upper() lower()
• 多行函数：对多个行进行操作，例如max()

select LOWER(ename) as '姓名'from emp;

select MAX(sal) from emp;

聚合函数的分类
多行函数返回一个值，通常返回统计分组信息

• max()
• min()
• avg()
• count()
  用count可以统计字段元素的个数，重复的也会统计，不包含Null值。如果不想统计重复的，可以使用distinct

select COUNT(deptno) from emp;			--返回14
select COUNT(distinct deptno) from emp;	-- 返回3
select MAX(sal) as '最高工资', MIN(sal) as '最低工资', COUNT(comm) as '获得年终奖人数' from emp;

需要注意的是多行函数和单行函数不能混合使用。

group by

分组，只能查询分组后的信息，后面只能是聚合函数或者是分组信息。通俗的讲，只能查询group by后面的和聚合函数。

select deptno, AVG(sal) as '部门平均工资'
	from emp
	group by deptno
select deptno as '部门', job as '职位', AVG(sal) as '平均工资', COUNT(*) as '人数'
	from emp
	group by deptno, job
	order by deptno, job
	--可以根据多个信息分组，会首先根据第一个相等的分类，再根据第二个相等的。

having

对分组之后的信息进行过滤。同样地，having后面也只能跟分组信息

--输出部门平均工资大于2000的部门的部门编号 部门的平均工资
select deptno as '部门', AVG(sal) as '平均工资'
	from emp
	group by deptno
	having AVG(sal) > 2000
	
	
--输出部门员工数大于3的部门编号和平均工资	
select deptno as '部门编号', AVG(sal) as '平均工资'
	from emp
	group by deptno
	having COUNT(*) > 3

如果我们不使用group by只使用having，则会将每一个记录都当作一个组进行过滤。

having和where的异同：

• 相同点：都是对数据过滤，保留有效的数据，不允许使用字段的别名
• 不同点：where是对原始记录过滤，having是对分组之后的记录过滤。
• 不同点：where不能和聚合函数搭配使用，having必须和聚合函数或者分组信息搭配使用，使用having之后select后面也只能是聚合函数或者分组信息

select COUNT(*)
	from emp where ename like '%A%'
	having AVG(sal) > 1500

如果我们使用as指定别名，别名只能用于输出字段，不能在后面使用
语句的执行顺序：

• from从表中迭代取出一个个记录
  • 使用where判断是否符合条件，如果不符合条件就舍弃，如果符合条件则执行下面语句
  • 使用group by按照条件对该记录进行分组，同时计算having和select后面的分组信息

等待分组结束以后

• having循环迭代每个分组是否符合条件
  • 如果符合条件则将select后面的分组信息输出
  • 如果不符合条件则舍弃该分组

最后对分组用order by进行排序，此时的order by后面同样只能够跟分组信息

--将工资大于1500的员工按照部门编号分组，并输出组中人数大于2的组的部门编号和平均工资
select deptno as '部门编号', AVG(sal) as '平均工资'
	from emp where sal > 1500
	group by deptno
	having COUNT(*) > 2

select 的参数的相对位置是固定的，不能变化的。相对位置如下：

-- 把姓名不包含A的所有员工按照部门编号分组
-- 统计输出部门平均工资大于2000的部门的部门编号 部门平均工资 部门人数

select deptno as '部门编号', AVG(sal) as '平均工资', COUNT(*) as '部门人数'
	from emp where ename not like '%A%'
	group by deptno
	having AVG(sal) > 2000
	order by deptno

select语句的基本结构

SELECT select_list
	[INTO new_table_name]
	FROM table_name
	[WHERE search_conditions]
	[GROUP BY group_by_list]
	[HAVING group_conditions]
	[ORDER BY order_list[ASC|DESC]]
	--其中order_list如果使用了GROUP BY 或者HAVING就必须也是分组信息