Oracle学习笔记（二）
Oracle学习笔记（二）
sqlplus username/password
sql
DQL: select*****
DML: insert / update / delete
DDL: create / drop / alter / truncate
DCL: grant / revoke
TCL: commit / rollback / savepoint
select 列名,表达式,函数,distinct,别名
from 表名
where 条件表达式,between..and..,like,in
order by 列名,列别名,表达式,数字
desc, asc
函数-单行函数
字符函数: upper/lower/initcap/lpad/rpad/length/substr/trim/replace/concat
数字函数:round/trunc/mod
日期函数:sysdate/months_between/add_months/next_day/last_day
转换函数:to_char/to_date/to_number
通用函数:nvl/coalesce/decode
默认的日期格式: dd-mon-RR
日期格式: yyyy/year/mm/dd/day/hh24/mi/ss/am/month
日期函数:
months_between
add_months
next_day(sysdate, 6) --下一个周五
next_day(sysdate,'FRI') --下个周五
next_day(sysdate,'星期五')--下个周五
--英文环境下用"FRI",中文环境下用"星期五",数字通用.
last_day
9 数字位
0 数字位,如果超过实际数字宽度,强制显示0
. 小数点
, 千位符
$ 美元货币符号
L 本地货币符号
如果格式长度小于实际数据长度,显示#
将字符串变成数字 / 数字变成字符串
to_number / to_char
将日期变成字符 / 字符变日期
to_char / to_date
多表查询
查找叫smith的人所在的部门名称?
select t1.ename, t2.dname
from t1 join t2
on t1.c1 = t2.c2
emp dept
pk/fk:约束条件,用来保证数据的完整性.pk: primary key, 主键
fk: foreign key, 外键
外键用来表示表间关系.一对多.
select e.ename, e.job, e.sal,
d.dname, d.loc
from emp e join dept d
on e.deptno = d.deptno
where e.ename = 'SMITH';
dept: 主键被参照,主表,父表
emp: 外键参照dept表,从表,子表
select e.ename,e.job,
e.sal, d.dname
from emp e join dept d
on e.deptno = d.deptno
and e.job = 'MANAGER';
select e.ename, e.sal, s.grade,
s.losal, s.hisal
from emp e join salgrade s
on e.sal between s.losal
and s.hisal;
1 [700,1200]
2 [1201,1400]
3 [1401,2000]
4 [2001,3000]
5 [3001,9999]
自连接
worker: 普通职员表
manager: 经理表
select worker.ename, manager.ename
from emp worker join emp manager
on worker.mgr = manager.empno;
内连接: 等值连接/非等值连接/自连接
一张表里的记录一定在另一张表中存在匹配的记录,否则不能出现在结果集中.
select t1.c1, t1.c2, t2.c1, t2.c2
from t1
[left|right|full outer join]
join t2
on t1.c1 = t2.c2
select e.ename, d.dname
from emp e left outer join dept d
on e.deptno = d.deptno;
select e.ename, d.dname
from dept d left outer join emp e
on e.deptno = d.deptno;
t1 left outer join t2
等价于: t2 right outer join t1
t1 right outer join t2
等价于:t2 left outer join t1
t1 full outer join t2
select e.ename, d.dname
from emp e full outer join dept d
on e.deptno = d.deptno;
select worker.ename, manager.ename
from emp worker
left outer join emp manager
on worker.mgr = manager.empno;
--哪些员工没有下属(不是别人的领导)
--外连接+匹配表pk is null表示否定问题,不是,不包括,等等.
select manager.ename
from emp worker
right outer join emp manager
on worker.mgr = manager.empno
where worker.empno is null;
--哪些部门没有叫smith的员工
--执行顺序:
--1)用ename='SMITH'过滤emp表;
--2)外连接,dept做驱动表,得到结果集=有smith的部门(内连接的结果集)+没有smith的部门(驱动表中匹配不到的记录和null值的组合)
--3)用匹配表的pk is null过滤掉内连接的结果集;
select d.dname
from emp e
right outer join dept d
on e.deptno = d.deptno
and e.ename = 'SMITH'
where e.empno is null;
总结:
内连接:等值连接/非等值连接/自连接
外连接:左/右/全外连接
组函数:
max / min / avg / sum / count
--avg/sum操作数字
--max/min可以操作各种数据类型
--组函数忽略空值
select avg(sal) from emp;
select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select max(hiredate) from emp;
select min(job) from emp;
select count(*) from emp;
--计算员工分布在几个部门
select count(distinct deptno)
from emp;
--按部门分组,计算每个部门的薪水总和
select deptno,sum(sal)
from emp
group by deptno;
--按职位分组,计算每个职位的平均薪水
select job, avg(sal)
from emp
group by job;
--在select后面,所有的非组函数的列必须出现在group by后面.否则会出错.
select job, avg(sal) from emp;
--出现在group by后面的列,不一定出现在select后面,但是信息不完整.
select avg(sal) from emp
group by job;
--结论:select后面的非组函数的列和group by 后面的列名保持一致.
--获得每个部门每个职位的平均薪水
select deptno, job, avg(sal)
from emp
group by deptno, job
order by deptno, job;
