oracle（sql）基础篇系列（一）——基础select语句、常用sql函数、组函数、分组函数 - ITPSC - 博客园
Wednesday, January 11, 2017
12:43 PM

oracle（sql）基础篇系列（一）——基础select语句、常用sql函数、组函数、分组函数

文章主目录

• 四大语句
• 基础select语句
• 常用sql函数
• 组函数
• 分组函数
• 综合练习

花点时间整理下sql基础，温故而知新。文章的demo来自oracle自带的dept,emp,salgrade三张表。解锁scott用户，使用scott用户登录就可以看到自带的表。
#使用oracle用户登录linux
[oraclelocalhost ~]$ sqlplus / as sysdba;
……
SQL> alter user scott account unlock；
回到顶部

四大语句

DQL语句——select
DML语句——insert,upate,delete等（关键字后带表名）
DDL语句——create,alter,drop等（关键字后带table关键字再跟表名）
事务控制语句——commit,rollback
回到顶部

基础select语句

简单select语句

select * from emp;

包含算术表达式的语句

—查询出来的sal乘以5
select ename , sal5 from emp;
—表中取出23的结果
select 23 from emp;在emp;
—当我们需要显示一个结果的时候就用系统提供的dual虚表
select 23 form dual;

获取系统当前的时间

—sysdate在Oracle中表示当前系统时间
select sysdate from dual;

含有别名的语句

—显示出来的是salary字段名而不是sal
select ename, sal*12 salary from emp;

含有空值

空值表达式

—含有任何null值的数学表达式最后的结果都为null
select ename, sal*5+comm from emp;

空字符串（字符串、字符串连接符）

—含有任何null值的字符串表达式中，null被当作空字符串处理
select empno, ename || ‘abc’ || mgr from emp;
—字符串连接符：||。select语句中用单引号表示字符串

distinct消除重复

—用distinct消除结果集中deptno相同的记录
select distinct deptno from emp;
—消除结果集中job和deptno都相同的记录
select distinct job, deptno from emp;

where 条件过滤

等值判断

select * from emp where empno = 7369;

非等值判断

（1）>,<,>=, <= ,<>
—取出所有部门号不是10的雇员的名字和薪水
select ename, sal from emp where deptno <> 10;
（2）between..and..
—包含最大值和最小值
select ename, sal from emp where sal between 800 and 1500;
（3）字符串比较
比较字符串ASCII码值的比较，先比较第一字母，依次比较每个字母
select ename, sal from emp where ename > ‘DBA’;

空值处理

—为空
select ename from emp where comm is null;
—不为空
select ename from emp where comm is not null;
注意，select ename from emp where comm = null;这种写法表示comm字段的值等于null，is null 或者is not null返回的boolean值，而=null返回的是null。null是一种特有的数据类型，其等价于没有任何值、是未知数。因此通常会获取不到结果却不提示语法错误。

in语句

—在选定范围
select ename, sal from emp where sal in (800, 1250, 1500, 2000);
—不在选定范围
select ename, sal from emp where sal not in (800, 1500, 2000);

日期处理

—Oracle默认的日期格式为：DD-MON-RR
select ename, hiredate from emp where hiredate > ’20-2月-1981’;
—to_char()，把日期类型字段转为指定格式的字符串
—输出2008-04-04
select to_char(sysdate, ‘YYYY-MM-DD-HH24:MI:SS’) from dual;
—to_date()，将日期字符串转为指定格式的日期
select * from emp where hiredate > to_date(‘1981-03-02’, ‘YYYY-MM-DD’);

and,or,not语句

—and
select deptno, ename, sal from emp where deptno = 10 and sal > 1000;
—or
select deptno, ename, sal from emp where deptno = 10 or sal > 1000;
—not
select ename, sal from emp where sal not in (800, 1500, 2000);

like模糊查询（通配符匹配、单字符匹配、转义字符）

（1）通配符%，代表0个或者多个字符
select ename from emp where ename like ‘%ALL%’;
（2）单个字符-，代表一个字符
select ename from emp where ename like ‘_A%’;
（3）转义字符\，统默认的转义字符是 ‘\’
—中间的%不表示通配符，而是表示姓名包含%
select ename from emp where ename like ‘%%%’；

order by排序

asc升序

select ename, sal from emp order by ename asc;

desc降序

select ename, deptno from emp order by ename desc;
回到顶部

常用sql函数

lower()——将字符串全部转成小写

select lower(ename),sal from emp where lower(ename) like ‘%a%’;

upper()——将字符串全部转成大写

select upper(ename),sal from emp where upper(ename) like ‘%a%’;

substr()——截取字符串

—从第2个字符开始截取ename
select substr(ename, 2) from emp;
—substr(‘hello’,1,3)，输出hel，第三个参数可以省略
select substr(‘hello’, 1,3) from dual;

chr()——求一个与某个ASCII码值对应的字符

select chr(65) from dual;

ascii()——求一个字符的ASCII码值

select ascii(‘A’) from dual;

round()——四舍五入

对参数值进行四舍五入，可以指定四舍五入到小数点后几位，可以用负数指定小数点前面几位
—输出24
select round(23.652) from dual;
—输出23.65
select round(23.652，2) from dual;
—输出20，-1表示对个位数进行四舍五入
select round(23.652，-1) from dual;
—输出0，-2表示对十位数进行四舍五入
select round(23.652，-2) from dual;

to_char()——日期类型转字符串

把日期类型字段转为指定格式的字符串，可以精确到时分秒
—输出2008-04-04
select to_char(sysdate, ‘YYYY-MM-DD’) from dual;
—2008-04-04 01:10:21
select to_char(sysdate,’YYYY-MM-DD hh:mm:ss’) from dual;

to_date()——字符串转为日期类型

将日期字符串转为指定格式的日期
select * from emp where hiredate > to_date(‘1981-03-02’, ‘YYYY-MM-DD’);

to_number()——字符串转数字

将指定的字符串转换成数字格式,第一个参数为要转为数字的字符串（一定要确保所转换字段是可转换为数字的，否则报“invalid number”），第二个参数为指定的格式
select * from emp where sal > to_number(‘$1,200.00’, ‘$9,999.99’);

nvl()——空值处理

这个函数有两个参数：
第一参数：字段名或表达式，如果这个参数值为null，就返回第二参数值，否则返回第一参数值。
select ename, sal*12+nvl(comm, 0) from emp;
回到顶部

组函数

min()——求最小值

max()——求最大值

avg()——求平均值

sum()——求总和

select sum(sal), avg(sal), max(sal), min(sal) from emp;

count()——求数量

—求记录数量
select count(*) from emp;
—求comm字段中所有非空记录的数量
select count(comm) from emp;
—和distinct一起使用，得到所有唯一值记录的数量
select count(distinct deptno) from emp;
回到顶部

分组函数

对组函数的查询结果进行分组

group by

使用group by 的规律：出现在select列表中的字段，如果没有出现在组函数中，则必须出现在group by子句中。
select deptno, avg(sal) from emp group by deptno;
—典型错误，group by缺少ename
select ename, deptno, max(sal) from emp group by deptno;
—求出每个部门中最赚钱的那个人的名字
select ename, deptno, sal from emp where sal in (
select sal from (
select deptno, max(sal) sal from emp group by deptno
))

having

having语句的作用是对分组之后的数据进行过滤数据，where 语句的作用是分组之前过滤数据。
—注意不能使用where
select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;
回到顶部

综合练习

从emp这张表里把平均工资和部门编号列出来，并且过滤掉大写是A的名字 把剩下的数据按照部门编号进行分组，分组之后的平均薪水必须大于2000，按照部门编号的倒序排列。
分析：分组，过滤，排序；过滤又包含分组前过滤(where)和分组后过滤(having)。编写顺序：分组前过滤(where) > 分组（group by） > 分组后过滤(having) > 排序（order by）
select avg(sal),deptno,ename from emp where ename not like ‘%A%’ group by deptno,ename having avg(sal) > 2000 order by deptno desc;
本文就到此，下一篇我们继续整理多表连接查询、子查询、视图相关内容。
作者：ITPSC出处：http://www.cnblogs.com/hjwublog/温馨提示：如果您觉得阅读本文能让你有所收获，请点一下“**推荐**”按钮或者“**关注我**”按钮，您的肯定将是我写作的动力！欢迎转载，**转载请注明出处**！
分类: oracle：sql基础
好文要顶 关注我 收藏该文



ITPSC
关注 - 1
粉丝 - 47
+加关注
1
0
关注ITPSC | 快速评论
« 上一篇：分布式缓存技术redis学习系列（五）——redis实战（redis与spring整合，分布式锁实现）
» 下一篇：oracle（sql）基础篇系列（二）——多表连接查询、子查询、视图
posted @ 2016-10-11 18:10 ITPSC 阅读(210) 评论(0) 编辑 收藏
已使用 OneNote 创建。