你的位置:首页 > 数据库

[数据库]oracle(sql)基础篇系列(一)基础select语句、常用sql函数、组函数、分组函数


 

花点时间整理下sql基础,温故而知新,也方便平时查阅。文章的demo来自oracle自带的dept,emp,salgrade三张表。解锁scotty用户,使用scott用户登录就可以看到自带的表。

#使用oracle用户登录linux

[oracle@localhost ~]$ 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 , sal*5 from emp;

 

--表中取出2*3的结果

select 2*3 from emp;在emp;

 

--当我们需要显示一个结果的时候就用系统提供的dual虚表

select 2*3 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;

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;

 

本文就到此,下一篇我们继续整理多表连接查询、子查询、视图相关内容。