第四章 Oracle 表操作

第一节：scott 用户默认表介绍

---

第二节：插入 insert 语句

给指定列插入数据：insert into dept(deptno,dname) values(50,’xx’);
插入全部列数据：insert into dept(deptno,dname,loc) values(60,’xx’,’lll’); 简写 insert into dept values(70,’xxx’,’llll’);

---

第三节：更新 update 语句

更新指定数据：update dept set dname=’司法部’ where deptno=50;
update dept set dname=’司法部’ ,loc=’china’ where deptno=50;

---

第四节：删除 delete 语句

删除指定数据：delete from dept where deptno=70;
删除指定条件的数据：delete from dept where deptno>40;

---

第五节：查询 select 语句

查询所有：select * from emp;
指定字段查询：select ename,sal from emp;
加 where 条件：select * from emp where sal>=800; select * from emp where sal>=1500 and job=’SALESMAN’;
Distinct 去重复记录；
Group by 分组查询：select job,count(ename) as num from EMP t group by job;
Having 过滤分组：select job,count(ename) as num from EMP t group by job having count(ename)>=2;
Order by 排序：select * from emp order by sal desc;
子查询：查询出基本工资大于平均工资的员工：select * from emp where sal>(select avg(sal) from emp)
联合查询：
并集（去重复）：
select * from t_user1
union
select * from t_user2;
并集：
select * from t_user1
union all
select * from t_user2;
交集：
select * from t_user1
intersect
select * from t_user2;
差集：
select * from t_user1
minus
select * from t_user2;
内连接：
select * from emp t,dept d where t.deptno=d.deptno;
类似：select * from emp e inner join dept d on e.deptno=d.deptno; inner 可以省略；
外连接：
左外连接：select * from emp e left join dept d on e.deptno=d.deptno;
右外连接：select * from emp e right join dept d on e.deptno=d.deptno;

---