**关于oracle的概念的复习**
一 常见的概念
1.dual(哑表或伪表)在不知道使用什么表的情况下,可用dual。
2.|| 字符串连接符 select ‘hello’ ||‘world’ from dual.
3.使用sysdate,显示系统当前时间,在默认情况下,oracle只显示日期,而不显示时间,格式:26-4月-15
select sysdate from dual;
4.使用%表示0个,1个或多个字符, 查询姓名以大写字母S开头的员工:
select * from emp where ename like ‘S’;
等价
select * from emp where ename = ‘S’;
select * from emp where ename like ‘S%’;
凡是精确查询用=符号
凡是不精确查询用like符号,我们通常叫模糊查询
5.null不能参与=运算
null能参与number/date/varchar2类型运算
6.不等于的三种写法
!=;<>;not。
7.升序:order by+字段 asc;
降序:order by+字段 desc;
8.order by后面可以跟列名、别名、表达式、列号(从1开始,在select子句中的列号)
列号:从1开始:select empno,ename,sal,hiredate,sal12 “年薪”
from emp
order by 5 desc;(5相当于sal12)
9.使用等值连接/内连接(只能使用=号),显示员工的编号,姓名,部门名,使用表别名简化
select emp.empno,emp.ename,dept.dname,dept.deptno
from emp,dept
where emp.deptno = dept.deptno;
10.内连接查询:只能查询出符合条件的记录 只查询等值的部分要求显示两个或者多个表中都有的数据叫内连接
外连接查询:既能查询出符合条件的记录,也能根据一方强行将另一个方查询出来
11.子查询的作用:查询条件未知的事物
–查询员工薪资最高的员工信息
1.select max(sal) from emp; —>aaa
2.select * from emp where sal=aaa;
select * from emp e
where e.sal=
(select max(sal) from emp)
12.-子查询中如何使用别名
select *
from
(select * from emp) e1,(select * from emp) e2
where e1.mgr=e2.empno;
子查询细节:
1)子查询与父查询可以针对同一张表
2)子查询与父查询可以针对不同张表
3) 子查询与父查询在传输参数时,数量要相同
4) 子查询与父查询在传输参数时,类型要相同
5) 子查询与父查询在传输参数时,含义要相同
查询部门名为’SALES’的员工信息(方式二:多表查询)
select emp.*
from dept,emp
where (dept.deptno=emp.deptno) and (dept.dname=‘SALES’);
查询每个员工编号,姓名,部门名,工资等级(三表查询,这三张表并无外健关联)
select e.empno,e.ename,d.dname,s.grade
from emp e,dept d,salgrade s
where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal);
查询工资最低的员工信息(单行子查询,使用=号)
第一:查询出工资最低是多少?
select min(sal) from emp;
第二:查询工资为800的员工信息?
select * from emp where sal = 800;
子查询:
select *
from emp
where sal = (
select min(sal)
from emp
);
查询部门名为’ACCOUNTING’或’SALES’的员工信息(多行子查询,使用in关键字)
第一:查询部门名为’ACCOUNTING’或’SALES’的部门编号?
select deptno from dept where dname in (‘ACCOUNTING’,‘SALES’);
第二:查询部门号为10或30号的员工信息?
select * from emp where deptno in (10,30);
子查询:
select *
from emp
where deptno in (
select deptno
from dept
where dname in (‘ACCOUNTING’,‘SALES’)
);
查询工资比20号部门【任意any】一个员工工资【低<】的员工信息(多行子查询,使用any关键字)
第一:查询20号部门的所有工资?
select sal from emp where deptno = 20;
第二:查询工资比(800,2975,3000,1100,3000)任意一个低的员工信息?
select * from emp where sal < any (800,2975,3000,1100,3000);
在oracle看来,<any就等于<集合中最大的那个值
子查询:
select *
from emp
where sal <any (
select sal
from emp
where deptno = 20
);
查询工资比30号部门【所有all】员工【低<】的员工信息(多行子查询,使用all关键字)
第一:查询出30部门所有员工的工资?
select sal from emp where deptno = 30;
第二:查询工资比(1600,1250,1250,2850,1500,950)中所有的工资都低的员工信息?
select * from emp where sal <all (1600,1250,1250,2850,1500,950);
子查询:
select *
from emp
where sal <all (
select sal
from emp
where deptno = 30
);
二 常见的函数
1.测试lower/upper/initcap函数,使用dual哑表
select lower(‘www.BAIdu.COM’) from dual; (转换成小写)
select upper(‘www.BAIdu.COM’) from dual; 转换成大写)
select initcap(‘www.BAIdu.COM’) from dual; (每个的首字母大写)
2.测试concat/substr函数,从1开始,表示字符,不论中英文
select concat(‘hello’,concat(‘你好’,‘世界’)) from dual;
select substr(‘hello你好’,5,3) from dual; 5表示从第几个字符开始算,第一个字符为1,中英文统一处理
3表示连续取几个字符
3.使用NVL(a,b)通用函数,如果a不为null,取a,反之,取b。
使用NVL2(a,b,c)通用函数,如果a不为NULL,取b值,否则取c值,统计员工年收入
使用NULLIF(a,b)通用函数,在类型一致的情况下,如果a与b相同,返回NULL,否则返回a
4.case 字段
when 条件1 then 表达式1
when 条件2 then 表达式2
else 表达式n
end
select ename “姓名”,job “职位”,sal “涨前工资”,
case job
when ‘ANALYST’ then sal+1000
when ‘MANAGER’ then sal+800
else sal+400
end “涨后工资”
from emp;
5.count()表示总数
avg()取平均值
round(avg(),数字)四舍五入,数字表示取几位小数
trunc()截断
group by()分组
group by 子句的细节:
1)在select子句中出现的非多行函数的所有列,【必须】出现在group by子句中
2)在group by子句中出现的所有列,【可出现可不现】在select子句中
在select后面出现的列名要不出现在聚合函数中,要不出现在group by子句中
where和having的区别:
where:
1)行过滤器
2)针对原始的记录
3)跟在from后面
4)先执行
除10号部门外,查询部门平均工资大于2000元的部门,方式一【having deptno<>10】
select deptno,avg(sal)
from emp
group by deptno
having deptno<>10;
having deptno<>10;
1)组过滤器
2)针对分组后的记录
3)跟在group by后面
4)后执行
select deptno,avg(sal)
from emp
where deptno<>10
group by deptno;
提倡,效率高