外观
Oracle的常见操作
Oracle的常见操作
1.生产系统中一个服务器一般=只使用一个Oracle数据库，一个数据库实例只能连接一个数据库，多个软件系统工作在同一个服务器，一定要共用数据库，为了区分模式对象，需要依据软件系统建立不同的用户
2.Oracle数据库不自动提交事务，执行ddl（数据定义语言）会自动提交事务
1.选择操作
横向选择部分行（在结果集中选出部分记录行）
关系运算：=,!=,<>,>,<,>=,<=,in,not in,is null,is not null,between..... and,like,
逻辑运算：not and or
is null 是判断结果是否为null的唯一条件，=null不可使用
like：模糊查找（%表示多个字符，_表示一个字符）关系运算的优先级高于逻辑运算
>some 等同于 >any 大于最小的
>all 大于最大的exist按行匹配，不经过投影过程，效率略高
2.排序
结果集默认不排序，按照聚集索引及内容排序
order by子句，可以多个关键字排序，多个关键字使用逗号分开，每个关键字可单独设置排序规则（ASC升序（默认）DESC为降序）
一般只在结果集的末尾排序
3.分组
group by .... having........分组后的条件
分组的投影字段，要么是分组条件列，要么是聚合函数的列
having 执行在分组之后，
where子句先执行可以把结果集范围缩小，提高查询效率
having一般用于限制聚合列，分组用于消除重复
4.连接
笛卡儿积：行数为两表行数的乘积，列数为两表列数的和
等值连接：表1 join 表2 on 条件
行数由查询条件决定，列数为两表之和
推荐 inner join 表2 on 条件的写法
左外连接：outer join 表2 on 条件
记录数取决于连接条件，保证左表数据全部显示
左联与等联的区别：
1.都可以连接2表
2.都可以连接字段
3.若引用表等于被引用表，数据符合引用完整性，等联与左联无区别
若数据不符合规范，，左联更安全，总能保证左表的所有记录会被加载
（编号转名称是最常用的应用场景）
--DML 数据操纵语言
--查
-- 1.投影（对字段的操作）
-- 1.1 选取全部字段
select * from emp;
-- 1.2 选取部分字段
select empno from emp;
select empno,ename from emp;
-- 1.3 选取部分字段，并且调整顺序
select ename,empno from emp;
-- 1.4 可以使用函数，或者计算，产生表达式
select substr(ename,1,2) from emp;
--null做任何运算结果都是null
select ename,sal,comm,sal+nvl(comm,0) from emp;
-- 1.5 别名
select ename,sal,comm,sal+nvl(comm,0) as 收入 from emp;
select ename,sal,comm,sal+nvl(comm,0) as "收 入" from emp;
select ename,sal,comm,sal+nvl(comm,0) "收 入" from emp E;
select ename,sal,comm,sal+nvl(comm,0) "收 入" from emp as E;--不行，表的别名是没有as
--1.6 常数列
select ename,1 常数列,sysdate,'abcd' from emp;
--1.7 剔重
select distinct job from emp;--只按投影列表（选择的字段列表）来剔重
select dist job from emp;-- 不行
-- 2.选择
-- 在结果集中选择出部分记录行
select * from emp
where ;
--关系运算 =,!=,<>,>,<,>=,<=,in,not in,is null ,is not null,between ...and ,like
-- 逻辑运算 not and or
-- 逻辑谓词 some any all exists
= 是单的
between ...and 是闭区间
is null 是判别null的正确方式，不能用=null
like
select * from emp
where comm=null ;--错
select * from emp
where comm is null ;
select * from emp
where ename like 'A';--错
select * from emp
where ename like '%A%';
select * from emp
where ename like 'A____';
select * from emp
where deptno=10 or deptno=20
select * from emp
where (deptno=10) or (deptno=20)
select * from emp
where sal>some (select sal from emp where deptno=10) --任意一个
select * from emp
where sal>any (select sal from emp where deptno=10) --任意一个
select * from emp
where sal>all (select sal from emp where deptno=10) --全部
select * from emp E1
where exists(select 1 from emp E2 where E1.Deptno=E2.deptno and E2.Deptno=10 )
-- exists 按记录行匹配，不经过投影，所以效率高
-- 3.排序
--结果集不排序，默认认为是无序的，是按照聚集索引，内容排列顺序
select ename,job from emp
order by empno, job asc,ename desc --可以多个关键字排序 ，用逗号分开
--每个关键字是可以，单独设置升序(asc)降序(desc)
--可以使用没有投影的字段排序
--一般不在子查询中排序，只在结果集最后排序
-- 4.分组
--group by
--having
--分组的2个用处
--1.使用聚合函数 max,min,sum,count,avg
--2.剔重
select count(1) from emp --把所有记录视作一组
--分组的投影字段，分组条件列，或者是聚合列
select deptno,min(ename),count(1) from emp
where deptno>0 --where 先执行，能把结果集限制的更小，处理的数据更少，效率高
group by deptno
having count(1)>0 --having 分组条件列，或者是聚合列,在分组后执行
-- having 一般只用于限制聚合列
--（精确的）消除重复
select job,min(empno) from emp
group by job
-- 5.连接
select * from emp
select * from dept
select * from emp,dept
--笛卡尔积
行数=2表之积，列数=2表之和
--等联（内等联结）
行数=取决于连接条件，列数=2表之和
select * from emp,dept
where emp.deptno=dept.deptno
select * from emp
inner join dept on emp.deptno=dept.deptno --推荐
select * from emp
inner join dept on emp.deptno!=dept.deptno
select * from emp
join dept on emp.deptno=dept.deptno
--左联（左联结）
行数=取决于连接条件,保证左表记录返回，列数=2表之和
select * from emp
left outer join dept on emp.deptno=dept.deptno --推荐
select * from dept
left outer join emp on emp.deptno=dept.deptno
order by emp.deptno
select * from dept
left join emp on emp.deptno=dept.deptno --推荐
order by emp.deptno
select * from emp,dept
where emp.deptno=dept.deptno(+) --左联的古典写法
左联与等连的区别？
都可以连接2表
连接字段
如何引用表与被引用表，数据符合引用完整性的，等连和左联没区别
数据不是那么规范，不符合引用完整性，左联更安全，总能保证左表的所有记录会被加载。
！！！编号转名称，是连接的最主要场景
--右联（就是左联的对称）
select * from emp
right outer join dept on emp.deptno=dept.deptno
select * from emp
right join dept on emp.deptno=dept.deptno
select * from emp,dept
where emp.deptno(+)=dept.deptno --右联的古典写法
--全连接(左联+右联)
--保证左表，右表记录都被加载
select * from emp
full outer join dept on emp.deptno=dept.deptno
--自然连接(按2表相同字段名的列，等连)
select * from emp
Natural join dept
-- 6.子查询
--1.单行子查询
select * from emp
where deptno=(
select deptno from Dept
where loc='DALLAS');
--2.多行子查询
select * from emp
where deptno in(
select deptno from Dept
where deptno>10);
--3.多列子查询
select * from emp
where (ename,job)=( select ename,job from emp where empno=7369)
update emp
set sal=0
where (ename,job)=( select ename,job from emp where empno=7369)
--4.标量子查询
select ename,sal,5000-sal 差额 from emp
select ename,sal,(select max(sal) from emp)-sal 差额 from emp
--5.相关子查询
select ename,sal,deptno,
(select max(sal) from emp E1 where Emp.Deptno=E1.Deptno)-sal 差额 from emp
--6.DDL子查询(复制表与数据)
create table EmpInDept10
as
select empno,ename,sal from emp
where deptno=10
select * from EmpInDept10
-- 7.联合
-- 并集
select * from emp
where deptno=10
union --带剔重
select * from emp
select * from emp
where deptno=10
union all --不带剔重
select * from emp
--不需要字段一致，只要数据类型一致,字段数量一致
select empno,ename from emp
where deptno=10
union
select empno,job from emp
--交集
select * from emp
where deptno=10
INTERSECT
select * from emp
--差集
select * from emp
minus
select * from emp
where deptno=104.Oracle常用函数
1.字符函数
1.ASCii('abcd')获取首字母的ASCII码
2.chr(n)获取n对应ASCII码的字符
3.concat(str1,str2)连接两个字符
4.instr(str1,str2[n])查找从n开始str2在str1中出现的位置，未找到为0；
5.length(Str)返回str的长度
6.lpad( str,n,char)格式化str，转为n位，不够的在左边补char，rpad类似
7.ltrim(str,str2)去除str1里面的str2
8.replace(str1,str2)去除str1里面的str2
9.replace(str1,str2,str3)将str1里面的str2用str3代替
10.substr(str ,n,l)截取字符，支持反向截取
2.数字函数
1.floor(n)向下取整
2.ceil(n)向上取整
3.round(n)绝对值四舍五入
4.round(n,l)保留l位小数四舍五入
5.mod(x,y)取x%y
6.power(x,y)取x^y
3.日期函数
1.current_date()|current_timestrap()|sysdate()|systimestrap()获取系统当前时间
2.month_between(date1,date2)获取两个date之间的月数
3.add_months(date,n)返回n个月后的日期
4.last_day(date)返回指定日期的月份的最后一天的日期
5.next_day(date)返回指定日期月份的后一天日期
4.转换函数
1.to_date(str,掩码)字符串转日期
2.to_char(str,掩码)
3.to_number(str,掩码)
5.其他函数
nvl(null,0)null保护
nvl(str,str1)使用str1代替str
nvl2(str,str1,str2)
三目运算
/*函数*/
--单行函数
--字符函数
--dual系统提供的测试表
select 'abcdef' from dual;
select ascii('abcdef') from dual;--97
select ascii('a') from dual;
select chr(97) from dual;--a
select concat('ab','cd') from dual;--abcd
select concat('ab','cd','ef') from dual;--错
select 'ab'||'cd' from dual;--abcd
select 'ab'+'cd' from dual;--错
select 3+2 from dual;
select '3'+'2' from dual;--自动转换
select '3'+'2'+10 from dual;--自动转换
select instr('abcdef','abc') from dual;--1
select instr('abcdef','bbc') from dual;--0
select instr('abcdefabc','abc',2) from dual;
select instr('abcdefabc','abc',1,2) from dual;
select length('123456') from dual;
select lpad('123',5,0) from dual;
select lpad('123456',5,0) from dual;--截取，填充
select rpad('123',5,0) from dual;
select rpad('123456',5,0) from dual;
select ltrim(' 123456 ') from dual;
select ltrim('123456','12') from dual;
select ltrim('21232456','312') from dual;
select trim(' 21232456 ') from dual;
select replace('1231245612','12') from dual;
select replace('1231245612','12','*') from dual;
select substr('12345678',2) from dual;
select substr('12345678',2,3) from dual;
select substr('12345678',-2) from dual;--反向截取
--数学函数
select floor(-3.14) from dual;-- -4
select ceil(-3.14) from dual;-- -3
select round(-3.14) from dual;-- -3
select round(-3.14,1) from dual;-- -3.1
select mod(8,5) from dual;-- 3
select power(8,2) from dual;-- 64
--日期函数
select CURRENT_DATE from dual;--2021/10/19 11:36:46
select SYSDATE from dual;--2021/10/19 11:37:18
select CURRENT_TIMESTAMP(9) from dual;--19-10月-21 11.37.47.833000000 上午 +08:00
select SYSTIMESTAMP(9) from dual;--19-10月-21 11.38.25.140000000 上午 +08:00
select SYSDATE+1 from dual;--天
select date'2021-10-19'- date'2021-10-16' from dual;
select SYSDATE- date'2021-10-16' from dual;
select months_between(date'2021-6-19', date'2021-10-16') from dual;
select ADD_MONTHS(date'2021-6-19',3) from dual;
select last_day(date'2021-10-16') from dual;
select last_day(date'2020-02-16')-3 from dual;
select next_day(sysdate,2) from dual;--下周一
select next_day(sysdate,'星期一') from dual;--下周一
select ROUND(sysdate,'year') from dual;
select ROUND(sysdate,'month') from dual;
select extract(day from sysdate) from dual;
select extract(year from sysdate) from dual;
--转换函数
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'dd')+10 from dual;
select to_date('2021-10-19 11:51:44','yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(1234567.879,'$000,000,000.00') from dual;
select to_number('$001,234,567.88','$000,000,000.00') from dual;
--其它函数
select nvl(null,2) from dual;
select empno,nvl(comm,0) from emp;
select empno,nvl(comm,'a') from emp;--错
--三目运算
select nvl2(1,2,3) from dual;
--多选一结构
select decode(5,1,'甲',2,'乙','爱咋咋') from dual;