oracle表查询、插入、删除

常用关键字

1.范围查询
支持数字/日期
（1）between 小 and 大 （>=小 and <=大）
（2）in(值1，值2) not in(值1，值2) （值不能为null）
2.模糊查询
like
_:一个字符； %：多个字符
例：

1.姓名长度大于3：ename like '___%'
2.姓名包含下划线时：ename like '%\_%' escape '\'

3.排序
order by 字段名 asc(默认升序)/desc(降序)
注意：
（1）null默认最大，解决：desc nulls last
(2)多列排序
order by sal desc ,hiredate asc;

4.分组 group by
(1)分组查询时，不在组函数（多行函数）中的列必须在group by 中。
（2）多行分组

select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>5000;

(3)对组筛选用having,对行筛选用where，where中不能使用组函数
5.去重distinct
6.伪列
rownum:逻辑序列1 2 3，在不同sql语句执行时rownum的值不一致，相同sql语句执行时rownum的值不变

rowid:物理序列（18位）去掉重复行

delete from  mytab where rowid not in (select stuno,min(rowid) from mytab group by stuno);

多行函数（组函数）

count（） sum() max() min() avg()

运算符

操作运算符：+ - / * %
关系运算符：> >= < <= =可赋值也可判断 ！=或<> 若为null需用is null 或 is not null
逻辑运算符：or and not
例：… where not(mgr=7788 and job=‘CLERK’)

集合连接符

union all 返回两个结果集的所有行，包括重复行
union 返回两个结果集的所有行，不包括重复行
minus 返回第一个结果集中有，但第二个没有的行
intersect 返回两个结果集都有的行
null可做常量列

select deptno ,job, sum(sal) from emp group by deptno,job
union select deptno ,null, sum(sal) from emp group by deptno
union select null,null, sum(sal) from emp;
改进：增强group by :rollup()
select deptno ,job, sum(sal) from emp group by rollup(deptno,job);

rollup(a,b)相当于先group by a,b 再group by a最后不分组

内连接

留下相同的，只显示匹配成功的
两张表一般存在主外键
用=或 表名 inner join 表名 on 条件

第一种：select empno,emp.deptno from emp,dept where emp.deptno=dept.deptno;
第二种：select empno,emp.deptno from emp e inner join dept  d on emp.deptno=dept.deptno;

左外连接

内连接加左边关系中失配的元组，缺少的右边关系属性值用null表示
表名.外键=表名.主键（+）保留左表所有数据
或 表名 left outer join 表名on 条件

第一种oracle独有：select empno,emp.deptno from emp,dept where emp.deptno=dept.deptno(+);
第二种：select empno,emp.deptno from emp e left outer join dept  d on emp.deptno=dept.deptno;

右外连接

内连接加右边关系中失配的元组，缺少的左边关系属性值用null表示
表名.外键（+）=表名.主键 保留右表所有数据
或 表名 right outer join 表名 on 条件

自连接

将一张表通过别名视为不同表

select e.ename,b.ename from emp e,emp b where e.mgr=b.empno;

自连接比较耗性能，采用层次连接

select level，empno,ename,mgr from  emp connect by prior
empno=mgr start with mgr is null order by level;

子查询

1.子查询可以出现的位置：where、select、having，from之后；不能写在group by 后面，若出现在select 后返回常量列即返回一行值。
2.主查询和子查询可以不是同张表
3.子查询可以使用单行操作符（=，<）,多行操作符in
4.子查询结果不能有null,解决：select mgr from emp where mgr is not null;

全连接

左外+右外-去重

插入

insert into emp(empno,ename) values(‘555’,‘kkk’);字符和日期要加’’

批量插入

1.创建新表（复制表）

create table mytab as select *from emp;
只留表结构：create table mytab2 as select *from emp where 1=0;

2.在旧表中插入

insert into mytab2 (empno,ename,sal) select empno,ename,sal from emp;

3.事物

begin 
 insert into emp ...
  insert into emp ...
  end;
  /

4.插入海量数据用：数据泵、SQL Loader、外部表

删除

delete from emp where empno=444;
全表删除 truncate table emp;
1.delete 一行一行删，会产生碎片；truncate 先丢弃整张表，再重新创建表，不会产生碎片。
2.整理碎片 alter table 表名 move;

注意事项

1.字符类型需加’ ’
2.将查询数据放在同一列select empno ,ename ||'+"||sal from emp;结果如smith+800
3选前5条Oracle: select * from emp rownum<=5; Mysql: select * from emp limit 0,5
4.所有工资加300select empno,sal+300 from emp;
5.任何数字与null运算结果为null
6.top-n问题要用到子查询和rownum
7.分析问题时是翻译思想
（1）分析要查询那些表、字段
（2）分析表之间如何连接