首页 > 代码库 > (三)Oracle学习笔记—— sql语句

(三)Oracle学习笔记—— sql语句

0. scott 用户默认表介绍

技术分享

scott用户Tables目录下包含四张表

技术分享

技术分享

技术分享

技术分享

1. insert(插入)语句

给指定列插入数据:

insert into dept(deptno,dname) values(50,xx);

插入全部列数据:

insert into dept(deptno,dname,loc) values(60,xx,lll);

2. update(更新)语句

update dept set dname=司法部 where deptno=50;update dept set dname=司法部 ,loc=china where deptno=50;

3. delete(删除)语句

删除指定数据:

delete from dept where deptno=70;

删除指定条件的数据:

delete from dept where deptno>40;

4. select(查询)语句

查询所有:

select * from emp;

指定字段查询:

select ename,sal from emp;

简写 

insert into dept values(70,xxx,llll);

加 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_user1unionselect * from t_user2;

并集:

select * from t_user1union allselect * from t_user2;

交集:

select * from t_user1intersectselect * from t_user2;

差集:

select * from t_user1minusselect * 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;

 

(三)Oracle学习笔记—— sql语句