学习大数据DAY02 SQL基础语法2

目录

数据类型说明

字符类型

like

作业

排列、分组查询

排列查询

分组查询

聚合函数：定义：也叫组函数，对一组数据（一列或多列）进行处理，返回单

having 分组过滤

where 条件里为什么不能有聚合函数

作业二

---

SQL 基础语法（二）

数据类型说明

字符类型

• char： char 数据类型用于存储固定长度的字符串。char 类型的长度为 2000

个字节。

• varchar2: varchar2 用于存储字符串数据。varchar 会根据实际数据的长度自

动调整;因此，在大多数情况下，都会使用 varchar2 类型。varchar2 最大长度

是 4000 个字节

数值类型

• number: number 数据类型具有精度(PRECISION)和范围(SCALE)两个参数。精

度指定所有数字位的个数，范围指定小数的位数。

日期时间类型

• date:它可以存储日期和时间的组合数据。结合 oracle 提供的日期时间函数便

地处理数据。

• timestamp:时间戳类型,可存放世纪、纪元、年、月、日、时、分、秒还可以

存放秒后 6 位。

CLOB/BLOB:

• CLOB:大文本文件类型，可存放 word,excel 等文档

• BLOB:二进制文件类型，可存放视频、音频等 操作符优先级

算术操作符>链接操作符>比较操作符合>not 逻辑操作符>and 逻辑操作符>or 逻

辑操作符

>all:表示大于最大值

<all：表示小于最小值

>any：表示大于最小值

<any：表示小于最大值

=any: 和 in 类似

and 和 or 可在 where 子语句中把两个或多个条件结合起来。

and 是左右两边的条件都要满足，取交集

or 是左右两边的条件只需要满足一个就行，取并集

not 就是选择反向结果

and 的优先级比 or 高，可以用（）括起来强制执行

操作符 between ... and 会选取介于两个值之间的数据范围。这些值可以是数

值、文本或者日期

通配符必须与 like 运算符一起使用，表示模糊匹配，而不是跟在数学运算符比

如=后面

in,在集合里操作

is null 是不是空值操作

not 取反

like

在 where 子句中使用 like 关键字查询数据的方式也称为字符串模式匹配或字符

串模糊查询，like 关键字需要使用通配符在字符串内查找指定的模式，所以要

了解常用的通配符。

Like 关键字可以使用以下两个通配符：

• %：代表 0 或者多个字符

• _：代表一个且只能是一个字符

例如：

“k%”代表以 k 开头的任意长度的字符串；

“%k%”代表含有 k 字母的任意长度的字符串；

“_kk”代表 3 个字符长度且后面 2 个字符是 kk 的字符串

如果要查询的字符串中含有“%”或“ ” ,该如何处理？ 要查询的字符串中含有“%”或“ ”时，可以使用转义（escape）关键字实现。

例：

select * from EMP where ename like '%\_%' escape '\';

上述查询语句中使用了“\”,”\”为转义字符，即在“\”之后的“ ”字符已不

是通配符，而是他本来的含义，即下划线。因此该查询的结果为：含有“ ”，任

意长度字符的字符串。

没有必要一定使用“\”字符，可以使用任何字符来作为转义符，当然，许多 oracle

的专业人员之所以经常用“\”作为转义符，是因为该字符在 Lunix 操作系统和

python 语言就是转义符。

select * from emp where ename='SMITH';

select ename,sal from emp where sal>2000;

select * from emp where deptno=30;

select * from emp where sal<=3000;

select * from emp where ename<>'SMITH';

select * from emp where deptno!=30;

select * from emp where sal>any(1600,1250,2850,1500,950);--大于最小值

select * from emp where sal>all(1600,1250,2850,1500,950);--大于最大值

select * from emp where deptno=30 and comm>500;

select * from emp where deptno=30 or comm>500;

select * from emp where (deptno=10 and sal>1000) or (deptno=20 and

sal>1500);

select * from emp where not 1=0;

select * from emp where 1=1;

--模糊查询

select * from emp;

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

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

select * from emp where ename like '\_%' escape '\';--修改数据测试

select * from emp where sal between 1000 and 2000;

select * from emp where deptno in(10,20);

select * from emp where comm is null;

作业

--方便查看而打开一份完整的 emp 表
select *
from emp;
--1.查询名字是 BLAKE 的人的编号,名字,工资
select ename,empno,sal
from emp
where ename='BLAKE';
--2.查询编号是 7782 的员工的编号,名字,工资,奖金
select empno,ename,sal,comm
from emp
where empno='7782';
--3.查询职位是销售(SALESMAN)的人的名字,职位,入职日期
select ename,job,hiredate
from emp
where job='SALESMAN';
--4.查询部门是 10 的人的编号,名字,部门编号
select empno,ename,deptno
from emp
where deptno='10';
--5.查询工资大于 1500,并且小于 2500 的人的编号,名字,工资
select empno,ename,sal
from emp
where sal>1500 and sal<2500 ;
--6.查询工资小于 2000 的人的名字,工资,奖金
select ename,sal,comm
from emp
where sal<2000 ;
--7.求工作是 CLERK 的或者工资小于 2000 员工姓名,工作,工资
select ename,job,sal
from emp
where job='CLERK'or sal<2000;
--8.求工资小于 800 或者大于 1500 的员工姓名,工作,工资
select ename,job,sal
from emp
where sal<800 or sal>1500 ;
--9.求工作是 CLERK,并且工资小于 950 或者大于 1500 的员工姓名,工作,工资
select ename,job,sal
from emp
where job='CLERK' and (sal<950 or sal>1500 );
--10.求 10 号部门工资大于 1000 和 20 号部门工资大于 1500 的员工姓名
select ename
from emp
where deptno=10 and sal>1000 or deptno=20 and sal>1500 ;
--11.求工资在 1500 到 2000 之间的员工姓名
select ename
from emp
where sal between 1500 and 2000 ;
--12.求部门编号是 10 号，20 号，30 号中任意一个部门的员工姓名
select ename
from emp
where deptno=any(10,20,30);
--13.求姓名中包含'M'的员工姓名
select ename
from emp
where ename like '%M%';
--14.求员工姓名第二位是'M'的
select ename
from emp
where ename like '_M%';
--15.求姓名中包含 A 和 L 的姓名
select ename
from emp
where ename like '%A%' and ename like '%L%';
--16.求姓名是五位的员工信息
select ename
from emp
where length(ename)=5 ;
--17.求姓名中第 3 位是%的员工姓名
select ename
from emp
where ename like '__\%%' escape '\';
--18.求第一位是_,倒数第二位也是_的员工姓名
select ename
from emp
where ename like '\_%\__' escape '\';
--19.求以_开头,第三位也是下划线_,第六位是%的员工姓名
select ename
from emp
where ename like '\__\___\%' escape '\';
--20.求名字中不包含 M 的员工姓名
select ename
from emp
where ename not like '%M%';

排列、分组查询

排列查询

select col_name from table_name order by

col_name1,col_name2....(asc/desc)

order:排列查询的关键字

by；介词，后面跟列名

asc:升序（默认）

desc:降序

可以用数字来指定排序列

select deptno,ename,sal from emp order by deptno,ename,sal

select deptno,ename,sal from emp order by 3,2,1 select deptno,ename,sal from emp order by 1,3

order by 后面接数字，1,2,3 分别对应 select 后面的第 1,2,3 列

--排序查询

select * from emp order by sal ;

select * from emp order by sal asc;

select * from emp order by sal desc;

select * from emp where deptno=10 order by sal desc;

select * from emp order by deptno asc,sal desc;

select * from emp order by 8 asc,6 desc;

--空值在做排序的时候是最大的值，排在最高

select * from emp order by comm desc

--order by 是唯一能重用别名的

select empno e,ename from emp where e>7369

select empno e,ename from emp order by e desc

注意：

降序排序时如果有空值，那么空值会作为最大值排在最前面

order by 是唯一一个可以使用别名的关键字，与 sql 语句的执行顺序有关

可以在 order by 后通过数字指定列进行排序，通常不使用这种方法

分组查询

数据分组的目的是用来汇总数据或为某个分组显示单行的汇总信息，通常在查询

结果集中使用 group by 子句对记录进行分组。在 select 语句中，group by 子

句位于 from 子句之后，其语法格式如下：

select col_name from table_name group by col_name

group:分组的关键字

by:介词，后面加列名

Having：过滤组关键字

聚合函数：定义：也叫组函数，对一组数据（一列或多列）进行处理，返回单

个结果

group by 子句经常与聚合函数一起使用。使用 group by 子句和聚合函数，可以

实现对查询结果中每一组数据进行分类统计。

max() 最大值

min() 最小值

sum() 求和

avg() 求平均值

count() 求个数

having 分组过滤

select A from B where C group by D having E order by F

SQL 执行顺序 from——where——group by——having——select——order by

---where 和 having 都是用来过滤，区别是什么？

（1）where 是用来过滤行的，having 是用来过滤分组的 （2）having 一般跟在 group by 子句后

（3）where 是在分组前进行过滤，而 having 是在分组后进行过滤的

1.两个词后面都加过滤条件

2.where 后面不可以加聚合函数的过滤条件，having 可以

3.只有出现聚合函数作为过滤条件时用 having，其余所有情况都用 where，因为

效率完全不同，where 效率比 having 高

where 条件里为什么不能有聚合函数

聚集函数也叫列函数，它们都是基于整列数据进行计算的，而 where 子句则是对

数据行进行过滤的(这里过滤是在一个记录里边过滤的,基于"行")，在筛选过程

中依赖“基于已经筛选完毕的数据得出的计算结果”是一种悖论，这是行不通的。

更简单地说，因为聚集函数要对全列数据时行计算，因而使用它的前提是：结

果集已经确定！而 where 子句还处于“确定”结果集的过程中，因而不能使用

聚集函数。 与 where 子句不能出现聚集函数正相反的是，我们几乎看不到不使用

聚集函数的 having 子句。为什么？因为在水平方向上根据外部指定条件的筛选

（也就是对行的筛选），where 子句可以独立完成，剩下的往往都是需要根据结

果集自身的统计数据进一步筛选了，这时，几乎都需要通过 having 子句配合聚

集函数来完成。

--分组查询

select deptno,count(*) from emp group by deptno ;

select deptno,max(sal),min(sal) from emp group by deptno ;

select deptno,avg(sal),sum(sal) from emp group by deptno ;

select deptno,max(hiredate) from emp group by deptno ;

--根据多个字段分组

select deptno,job from emp where sal>1000

group by deptno,job order by deptno desc;

--having:分组过滤

select deptno,job from emp where sal>1000

group by deptno,job having deptno>10 order by deptno desc;

作业二

--获取一份 emp 表方便查看
select *
from emp; 
--1.查询员工姓名、工资，按照工资的正序排列
select ename,sal
from emp
order by sal asc; 
--2.查询员工信息，先按照部门编号降序排列，再按照工资的正序排列
select *
from emp
order by deptno desc,sal asc; 
--获取一份 dept 表方便查看
select *
from dept; 
--获取一份 dept 和 emp 合表
select *
from dept,emp
where dept.deptno=emp.deptno; 
--3.查询员工信息，先按照工资降序排列，再按照部门降序排列
select *
from dept,emp
where dept.deptno=emp.deptno
order by emp.sal desc,dept.deptno desc; 
--4.求每个部门有多少员工
select dept.dname,count(1)
from dept,emp
where dept.deptno=emp.deptno
group by dept.dname; 
--5.求每个部门里的最高工资
select dept.dname,max(emp.sal)
from dept,emp
where dept.deptno=emp.deptno
group by dept.dname; 
--6.参加每种工作的人数
select job,count(1) from emp group by job; 
--7.求员工里面的最高工资
select max(sal)
from emp; 
--8.求公司有多少个部门
select max(rownum)
from dept; 
--9.求部门平均工资大于 1600 的部门编号
select dept.deptno,avg(emp.sal)
from dept,emp
where dept.deptno=emp.deptno
group by dept.deptno
having avg(emp.sal)>1600 ; 
--10.求部门平均工资大于 1600,并且不是 20 号部门的部门编号
select deptno,avg(sal)
from emp
where deptno!=20
group by deptno
having avg(sal)>1600 ; 
--11.列出最低薪金大于 1500 的各种工作
select job,sal
from emp
where sal>1500 ; 
--12.列出在每个部门工作的员工数量、平均工资
select D.dname,count(1),avg(E.sal)
from dept D,emp E
where D.deptno=E.deptno
group by D.dname; 
--13.列出各个部门的 MANAGER（经理）的最低薪金
select D.dname,min(E.sal)
from dept D join emp E on D.deptno=E.deptno
where E.job='MANAGER' group by D.dname; 
--14.查询每个部门每个岗位的工资总和。
select D.dname,E.job,sum(E.sal)
from dept D,emp E
group by D.dname,E.job; 
--15.查询部门人数大于 2 的部门编号，最低工资、最高工资,部门人数。
select deptno,min(sal),max(sal),count(1)
from emp
group by deptno
having count(1)>2; 
--16.查询部门平均工资大于 2000，且人数大于 2 的 部门编号、部门人数、部门
平均工资 ，并按照部门人数升序排序。
select deptno,count(1),avg(sal)
from emp
group by deptno
having avg(sal)>2000 and count(1)>2
order by count(1) asc; 
--17.查询员工岗位中不是以“SA”开头并且平均工资在 2500 元以上的岗位及平均
工资，并按平均工资降序排序
select job,avg(sal)
from emp
where job not like 'SA%' group by job
having avg(sal)>2500
order by avg(sal) desc; 
--18.查询岗位不为 SALESMAN，工资和大于等于 2500 的岗位及 每种岗位的工资
和
select job,sum(sal)
from emp
where job!='SALESMAN' group by job
having sum(sal)>=2500 ;