首页 > 代码库 > (三)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语句