SQL 数据库学习 027 查询-10 Group by --- 以某字段分组

Oct 17th, 2016 1:05 pm

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

`group by` — 以某字段分组

--输出每个部门的编号 和 该部门的平均工资
select deptno, avg(sal) as "部门平均工资"
  from emp
  group by deptno

Alt text

-- 判断下面语句是否正确
select deptno, avg(sal) as "部门平均工资" ename
  from emp
  group by deptno

Alt text

总结：使用了 group by 之后， select 中只能出现分组后的整体信息，不能出现组内的详细信息。

--error
select *
  from emp
  group by deptno, job

Alt text

--error
select deptno, job, sal
  from emp
  group by deptno, job

Alt text

select deptno, job, avg(sal)
  from emp
  group by deptno, job

Alt text

显示的不是很好，我们就对其进行排序。（没有实际的意义，我们就是讲讲语法。）

select deptno, job, avg(sal)
  from emp
  group by deptno, job
  order by deptno

Alt text

只要是聚合函数，在这里都可以使用。

select deptno, job, avg(sal), count(*), sum(sal),  min(sal)
  from emp
  group by deptno, job
  order by deptno

Alt text

select deptno, job, avg(sal) "平均工资", count(*) "部门人数", sum(sal) "部门总工资",  min(sal) "部门最低工资"
  from emp
  group by deptno, job
  order by deptno

Alt text

总结：

group by

格式： group by 字段的集合
功能：把表中的记录按照字段分成不同的组
注意：理解 group by a, b, c 的用法
- 先按 a 分组，如果 a 相同，再按 b 分组，如果 b 相同，再按 c 分组。最终统计的最小分组的信息。

SQL 数据库学习 026 查询-09 聚合函数 --- 多行记录返回至一个值，通常用于统计分组的信息

Oct 17th, 2016 11:05 am

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

函数的分类

单行函数

每一行返回一个值

多行函数

多行返回一个值
聚合函数是多行函数

例子：

select lower(ename) from emp;
  --最终返回的是14行

Alt text

select max(sal) from emp;
  --返回时1行 max() 是多行函数

Alt text

聚合函数的分类

max() 求最大值
min() 求最小值
avg() 平均值
count() 求个数
- count(*) ：返回表中所有的记录的个数
- count(字段名) : 返回字段值非空的记录的个数，重复的记录也会被当做有效记录
- count(distinct 字段名) ：返回字段不重复并且非空的记录的个数

select count(*) from emp;
  --返回emp表所有记录的个数

Alt text

select count(deptno) from emp;
  --返回值是14，这说明deptno重复的记录也被当做有效的记录

Alt text

select count(distinct deptno) from emp;
  --返回值是3， 统计deptno不重复的记录的个数

Alt text

select count(comm) from emp;
  --返回值是4。 这说明comm为null的记录不会被当做有效的记录

Alt text

注意的问题

select max(sal), min(sal), count(*) from emp;

Alt text

select max(sal) "最高工资", min(sal) "最低工资", count(*) "员工人数" from emp;

Alt text

select max(sal), lower(ename) from emp;
  --error 单行函数和多行函数不能混用

Alt text

SQL 数据库学习 025 查询-08 模糊查询 --- 怎样编写模糊查询语句

Oct 17th, 2016 6:59 am

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

怎样编写模糊查询语句

select * from emp where ename like '%A%'
  --ename只要含有字母A就输出
select * from emp where ename like 'A%'
  --ename只要首字母是A的就输出
select * from emp where ename like '%A'
  --ename只要尾字母是A的就输出

Alt text

select * from emp where ename like '_A%'
  --ename只要第二个字母是A的就输出

Alt text

select * from emp where ename like '_[A-F]%'
  --把ename中第二个字符是A或者B或者C或者D或者E或者F的记录输出

Alt text

select * from emp where ename like '_[^A-F]%'
  --把ename中第二个字符不是A也不是B也不是C也不是D也不是E也不是F的记录输出

Alt text

创建一个 student 表。（预备操作）

create table student
(
  name nvarchar(20) null,
  age int
);

insert into student values ('张三', 68);
insert into student values ('Tom', 66);
insert into student values ('a_b', 22);
insert into student values ('c%d', 44);
insert into student values ('abc_fe', 56);
insert into student values ('haobin', 25);
insert into student values ('HapBin', 88);
insert into student values ('c%', 66);
insert into student values ('long''s', 100)

select * from student;

Alt text

select * from student where name like '%\%%' escape '\'
  --把name中包含有%的输出
select * from student where name like '%\_%' escape '\'
  --把name中包含有_的输出

Alt text

总结： 通配符：

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

%
- 表示任意0个或多个字符
_ （这个是下划线，不是减号）
- 表示任意单个字符
[a-f]
- a 到 f 中的任意单个字符。只能是a、b、c、d、e、f 中的任意一个字符
[a, f]
- a 或者 f
[^a-c]
- 不是a、也不是b，也不是c的任意单个字符

注意：

匹配的内容必须使用单引号括起来。不能省略，也不能改用双引号。
escape '\' 里面 escape 后面 '' 里面的\ 字符就自定义的：通配符。它可以替换成其他的字符（比如：a、b、m 等等）。

SQL 数据库学习 024 查询-07 Order by 的用法 --- 以某个字段排序

Oct 17th, 2016 3:13 am

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

`order by` — 以某个字段排序

例子：

select * from emp order by sal;
  --默认是按照升序排序

Alt text

select * from emp order by deptno, sal;
  --先按照deptno排序，然后再按照sal排序

Alt text

--asc是升序的意思，默认可以不写 desc是降序
select * from emp order by deptno desc, sal;
  --先按照deptno降序排序，如果deptno相同，再按照sal升序排序
  --记住sal是升序不是降序
  --order by a desc, b, c, d   desc只对a产生影响，不会对后面的b, c, d 产生影响

Alt text

select * from emp order by deptno, sal desc
  --问题：desc是否会对deptno产生影响？
  --答案：不会
  --先按deptno升序，如果deptno相同，再按sal降序排序

Alt text

总结：

order by a, b --a和b都是升序
order by a, b desc --a升序 b降序
order by a desc, b --a降序 b升序
order by a desc, b desc --a和b都是降序

文字描述：

如果不制定排序的标准，则默认是升序。升序用asc表示，默认可以不写
为一个字段制定的排序标准并不会对另一个字段产生影响
强烈建议为每一个字段都指定排序的标准

SQL 数据库学习 023 查询-06 Null 的用法 --- 没有值空值

Oct 17th, 2016 1:48 am

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

`null` — 没有值、空值

--null不能参与<> != = 运算
select * from emp where comm <> null; --输出为空 error
select * from emo where comm != null; --输出为空 error
select * from emp where comm = null; --输出为空 error

--null 可以参与is 、 not is 
select * from emp where comm is null;
  --输出奖金为空的员工的信息
select * from emp where comm is not null;
  --输出奖金不为空的员工的信息

--任何类型的数据都允许为null
create table t1 (name nvachar(20), cnt int, riqi datetime)
insert into t1 values (null, null, null)
select * from t1;

--输出每一个员工的姓名、年薪（包含了奖金）、comm假设是一年的奖金
select ename, sal*12+comm "年薪" from emp;
  --这段指令证明了：null不能参与任何数学运算，结果永远是null

--正确的写法
select ename, sal*12+isnull(comm, 0) from emp;
  --isnull(comm, 0) 如果comm是null，就返回为0，否则返回comm的值。

总结：

0 和 null 是不一样的，null 表示空值，没有值，0表示一个确定的值
null 不能参与如下运算：<>、!=、=
null 可以参与如下运算：is、not is
任何类型的数据都允许为null
null不能参与任何数学运算，结果永远是null

SQL 数据库学习 022 查询-05 Top 的用法 --- 最前面的若干个记录

Oct 17th, 2016 12:55 am

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

`top` — 输出最前面的若干个记录

select top 5 * from emp;
  --输出从emp表的上面数，前5个元组

Alt text

select top 15 percent * from emp;
  --输出从emp表的上面数，15%的元组。（输出的是3个，不是2个）

Alt text

SQL 数据库学习 021 查询-04 in 的用法 --- 属于若干个孤立的值

Oct 17th, 2016 12:39 am

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

select * from emp where sal in (1500, 3000)

Alt text

select * from emp where sal in (1500, 3000, 5000)
  --把sal里面是1500 和 3000 和 5000 的值都输出

Alt text

上面的命令等价于：

select * from emp
  where sal=1500 or sal=3000 or sal=5000

select * from emp where sal not in (1500, 3000, 5000)

Alt text

上面的命令等价于：

select * from emp
  where sal<>1500 and sal<>3000 and sal<>5000
      --在数据库中 不等号有两种表示： != 和 <> ，推荐使用第二种
      --对或（or）取反是并且（and） 对并且（and）取反是或（or）

SQL 数据库学习 020 查询-03 Between 的用法

Oct 17th, 2016 12:20 am

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

`between` 的用法

--查找工资在1500到3000之间（包括1500和3000）的所有的员工的信息
select * from emp
  where sal>=1500 and sal<=300

Alt text

上面的指令等价于：

select * from emp
  where sal between 1500 and 3000

--查找工资在小于1500或大于3000之间的所有的员工的信息
select * from emp
  where sal<1500 or sal>3000

Alt text

上面的指令等价于：

select * from emp
  where sal not between 1500 and 3000

SQL 数据库学习 019 查询-02 Distinct 的用法 --- 不允许重复

Oct 16th, 2016 9:10 pm

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

`distinct` 的用法

distinct 的意思是：不允许重复。

select deptno from emp;
  -- 14行记录 不是3行记录

Alt text

select 10000 from emp;
  -- 也是14行记录

Alt text

select distinct deptno from emp;
  --distince deptno 会过滤掉重复的deptno

Alt text

select distinct comm from emp;
  --distince 也可以过滤掉重复的null。或者说如果有多个null，只输出一个

Alt text

select distinct comm, deptno from emp;
  --把 comm 和 deptno 的组合进行过滤

Alt text

select deptno, distinct comm from emp;
  --error 逻辑上有冲突

执行输出错误：

消息 156，级别 15，状态 1，第 11 行
关键字 'distinct' 附近有语法错误。

SQL 数据库学习 018 查询-01 计算列的用法

Oct 16th, 2016 8:15 pm

我的电脑系统：Windows 10 64位
SQL Server 软件版本： SQL Server 2014 Express

计算列

select * from emp;
  -- * 表示所有的
  -- * from emp 表示从emp表查询

执行输出：

Alt text

select empno, ename from emp;
  -- 将员工表里面所有员工的 empno 和 ename 都输出出来

执行输出：

Alt text

select ename, sal*12 as "年薪" from emp;
  -- as 可以省略。 记住："年薪" 不要写成 '年薪' 、也不要写成 年薪

执行输出：

Alt text

注意： ：在Oracle软件中字段的别名不允许用单引号括起来。但是 SQL Server 软件却允许，因此为了兼容性考虑，最好字段的别名用""（双引号）括起来，不要使用''（单引号）。

上面这段命令 select ename, sal*12 as "年薪" from emp 就是计算列。解释：原本 emp 表里面没有年薪这个属性，我们使用 sal*12 as "年薪" 得到年薪的信息，这就是 计算列。

select 5 from emp;
  -- ok，可以运行
  --输出的行数是emp表的行数，每行只有一个字段，值是5
  -- 5 就是一个值，仅此而已，没有实际意义

执行输出：

Alt text

select 5;
  --ok，可以运行
  --但是不推荐

执行输出：

Alt text

select ename, sal*12 as "年薪", sal "月薪", job from emp;

执行输出：

Alt text

← Older Blog Archives Newer →

AoboSir 博客

与15年前的我比，我现在是大人；与15年后的我比，我现在还是个婴儿

SQL 数据库学习 027 查询-10 Group by --- 以某字段分组

`group by` — 以某字段分组

SQL 数据库学习 026 查询-09 聚合函数 --- 多行记录返回至一个值，通常用于统计分组的信息

函数的分类

聚合函数的分类

注意的问题

SQL 数据库学习 025 查询-08 模糊查询 --- 怎样编写模糊查询语句

怎样编写模糊查询语句

SQL 数据库学习 024 查询-07 Order by 的用法 --- 以某个字段排序

`order by` — 以某个字段排序

SQL 数据库学习 023 查询-06 Null 的用法 --- 没有值空值

`null` — 没有值、空值

SQL 数据库学习 022 查询-05 Top 的用法 --- 最前面的若干个记录

`top` — 输出最前面的若干个记录

SQL 数据库学习 021 查询-04 in 的用法 --- 属于若干个孤立的值

SQL 数据库学习 020 查询-03 Between 的用法

`between` 的用法

SQL 数据库学习 019 查询-02 Distinct 的用法 --- 不允许重复

`distinct` 的用法

SQL 数据库学习 018 查询-01 计算列的用法

计算列

group by — 以某字段分组

函数的分类

聚合函数的分类

注意的问题

怎样编写模糊查询语句

order by — 以某个字段排序

null — 没有值、空值

top — 输出最前面的若干个记录

between 的用法

distinct 的用法

计算列

`group by` — 以某字段分组

`order by` — 以某个字段排序

`null` — 没有值、空值

`top` — 输出最前面的若干个记录

`between` 的用法

`distinct` 的用法