数据库(oracle)复习day1（select语句）下午

最新推荐文章于 2025-08-10 14:45:00 发布

原创最新推荐文章于 2025-08-10 14:45:00 发布 · 429 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #单行函数 #分组函数 #多表查询

数据库(Oracle和mysql) 专栏收录该内容

6 篇文章

订阅专栏

oracle 复习day1 下午

1 单行函数（字符、数值、日期、转换、通用）
2 分组函数（多行函数）
3 多表查询（跨表查询或自身查询）

1 单行函数（字符、数值、日期、转换、通用）

1.1 字符函数

—1 转大小写和首字母转大写函数

select lower('Hello') 转小写, Upper('Hello') 转大写, initcap('hello world') 首字母大写 from dual;
select 'aaa' || 'bbb' 连接1, concat('1111', 2222) 连接2 from dual;

–2 substr(a, b) 从a中,第b位开始取
–substr(a, b, c) 从a中,第b位开始取,取5个字符

select substr('abcdefg', 3) 第一次提取, substr('abcdefg', 3, 5)  第2次提取 from dual;

–3 length字符数 –lengthb字节数
–一个中文字符,两个字节

select length('abcde中国') , lengthb('abcde') , lengthb('中国') from dual;

–4 向左右填充函数

select lpad('abcd', 10, '*' ), rpad('abcd', 10, '*' ) from dual;

–5 instr(‘母串’, ‘子串’) ,在母串中查找字串,返回子串在母串中的位置;找不到返回0;

select instr('abcd', 'cd' ) from dual;

–6 trim 去掉首尾指定的字符,字符可以是空格,也可以不是空格

SQL> select trim('H' from 'Hellowrold') from dual;  //结果为 ellowrold

–7 replace(‘aaaa’,‘b’, ‘c’) --从母串中检索符合条件的字串b,替换成c

select replace('hello world', 'l','*') from dual;  //结果为 he**o wor*d

1.2 数值函数

– 2：保留小说点后2位 1：保留小说点后1位 0：保留小说点后个位
– 1：保留小说点十位(要看个位数,要看个位数,根据十位数,进行四舍五入)
– 2：保留百位(要看十位数,根据十位数,进行四舍五入)

select round(45.926, 2) AA, round(45.926, 1) BB,  round(45.926, 0) CC,  round(45.926) DD,
round(44.926, -1) EE, round(45.926, -2) FF from dual;

结果为

        AA         BB         CC         DD         EE         FF
---------- ---------- ---------- ---------- ---------- ----------
     45.93       45.9         46         46         40          0

1.3 日期函数

select sysdate-1 昨天, sysdate 今天, sysdate+1 明天  from dual;

– 1 to_char(‘日期/数字’, format)

select to_char(sysdate-1, 'yyyy-mm-dd hh24:mi:ss') 昨天, 
to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 今天, 
to_char(sysdate+1, 'yyyy-mm-dd hh24:mi:ss') 明天  from dual;

– 2 粗略方式查询员工的入职时间,按照天周月年方式显示

select ename, sysdate-hiredate 天, (sysdate-hiredate)/7 周, (sysdate-hiredate)/30 月 from emp

–3.精确查询员工的入职时间,按照天周月年方式显示

select ename, (sysdate-hiredate)/30 月1, MONTHS_BETWEEN(sysdate, hiredate) from emp;

– 4 显示当前日期的下一个周一

select next_day(sysdate, '星期一') from dual

– 5 日期的四舍五入

select round(sysdate,'MONTH') from dual;

1.4 转换函数

– 1 类型转换
–比这个日期都要大的08-9月 -81 所有员工信息

 selct * from emp
 where hiredate > '08-9月 -81'       --隐式类型转换  字符转换成日期

select * from emp
where hiredate > to_data('1981-02-02', 'yyyy-mm-dd')  --把字符串转成日期

select to_char(sysdate, 'yyyy-mm-dd') from dual       --把日期转成字符串

– 2 oracle自动转换
char===>number
number====>char

select 3+2 || 'aaaa' from dual;

data===>char
char===>data

select * from emp
where hiredate > to_data('1981-02-02', 'yyyy-mm-dd')  --把字符串转成日期

select to_char(sysdate, 'yyyy-mm-dd') from dual 	  --把日期转成字符串

– 3 查询员工的薪水:两位小数 本地货币代码 千位符

select ename, to_char(sal, 'L9,999.99') from emp; --把数字转成字符串(格式化字符串)

–把这个字符串￥1,250.00转成数字

select to_number('￥1,250.00' , 'L9,999.99')   from dual;

1.5 通用函数

— 1 nvl2(a, b, c) 当a=null 返回c,否则返回b

select sal, sal*12+nvl2(comm, comm, 0) 年收入 from emp;

— 2 nullif(a,b) 当a=b时,返回null; 否则返回a

select nullif('abc', 'abcd') from dual;

select nullif('abc', 'abc') AA,   nullif('abc', 'abcd') BB from dual;

— 3 coalesce(a, b, c, …) ;从左到右,返回第一个不为空的…

select comm,sal, COALESCE(sal, comm) from emp;

1.6 CASE 表达式 —>做报表

-给员工涨工资:总裁 1000 经理:800 其他涨500,
前后工资给列出来
检索JOB列:思路如下

	if  'PRESIDENT'  SAL+1000	
	else if  'MANAGER'  SAL+800
	else  SAL+500

语法，方法1：

	CASE expr WHEN comparison_expr1 THEN return_expr1
         [WHEN comparison_expr2 THEN return_expr2
          WHEN comparison_exprn THEN return_exprn
          ELSE else_expr]
	END

代码：

	CASE job    WHEN 'PRESIDENT' THEN SAL+1000
         		WHEN 'MANAGER' THEN SAL+800       
                ELSE SAL+500	
	END

最终代码：

select ename, job, sal 涨前工资, 
			(CASE job   WHEN 'PRESIDENT' THEN SAL+1000
         				WHEN 'MANAGER' THEN SAL+800       
          				ELSE SAL+500	
				END) 涨后工资

语法，方法2：

DECODE(job, 'PRESIDENT', SAL+1000 , 'MANAGER', SAL+800, SAL+500)
      			   [, search2, result2,...,]
      			   [, default])

select ename, job, sal 涨前工资, 
			(DECODE(job, 'PRESIDENT', SAL+1000 , 'MANAGER', SAL+800, SAL+500)) 涨后工资 
from emp

2 分组函数（多行函数）

2.1 组函数能自动滤空

– 1.求员工的平均工资

select avg(sal) AA, sum(sal)/count(empno) BB from emp;

– 2 求员工的平均comm 奖金

select avg(comm) AA, sum(comm)/count(empno) BB, sum(comm)/count(comm) CC from emp;
结果为
        AA         BB         CC
       550      157.14     550

造成这个问题的原因:奖金含有空值, 组函数能自动滤空。

--count(comm)  count()组函数, 组函数自动滤空
select count(empno) ,count(nvl(comm, 0)), count(*) from emp;  //结果都为14

2.2 分组数据(对数据进行分组)

– 1 查询部门的平均工资

select avg(sal) from emp
group by deptno;	--对部门进行分组 10部门1组 20部门1组 30部门1组

 //抽象........
 select a, b, c  from emp
 group by a, b, c, d //ok
 
 select a, b, c，d, e  from emp
 group by a, b, c, d //err

注意要点：
–1 在使用分组函数时,在select中出现的a b c列, 必须是group by 后面集合的子集
–2 在group by 中没有出现的项,不能再select 进行检索
–3 先对数据进行分组，然后在组上进行数据查询

– 2 group by后面有多列
– 查询部门的平均工资
– 查部门\ 不同的职位统计平均工资

select deptno, job, avg(sal)   // 这里列 里面可以有分组函数
from emp
group by deptno, job
order by 1；

2.3 过滤分组 having (对数据分组以后,再增加检索条件)

–1. 查询部门的平均工资
–进一步,查询平均工资大于2000的部门

select deptno from emp
group by deptno
having avg(sal) > 2000；

2.4 having 和 where子句

–求10号部门的平均工资
1 先分组,再在having过滤

select avg(sal)
from emp
group by deptno
having deptno=10；

2 先where过滤 ,再分组

select avg(sal)
from emp
where deptno = 10
group by deptno；

–sql优化2 先where过滤 ,再分组：效率更高

–3 有关where子句和having子句都可以条件过滤
最大区别: 在where子句中不能有组函数

	  1  select avg(sal)
	  2  from emp
	  3  where deptno = avg(sal);
	
	第 3 行出现错误:
	ORA-00934: 此处不允许使用分组函数

3 多表查询（跨表查询或自身查询）

3.1 笛卡尔积

列数相加
行数记录相乘
– 1 通过连接条件可以避免笛卡尔积

		  1  select *
		  2  from dept d, emp e
		  3  where e.deptno = d.deptno；

3.2 等值连接

–查询员工信息,员工号,姓名,月薪,部门名称

	select e.empno, e.ename, e.sal, d.dname
	from emp e, dept d
	where e.deptno = d.deptno //如果没有这一行 等值连接 就会出现64行

3.3 不等值连接