Oracle笔记3
查询语句（重点）
SQL语句中
算术运算 + - * /
select ename, sal , sal*12+2000 from emp where sal>2000;
select ename 姓名, sal "月工资" , sal*12+2000 as "年总工资" from emp where sal>2000;
可以给列设置别名 列名 [as] 别名
逻辑运算
与 或 非 and or not
select ename, job, sal from emp where sal>2000 and sal<3000;
select ename, job, sal from emp where sal>2000 or job='SALESMAN';
select ename, job, sal from emp where not job='SALESMAN';
关系运算
> < >= <= <> != ^=
主要用于where子句中
字符串连接操作符
'Stephone' || 'Curry' = 'StephoneCurry'
select ename||' is a '||job "EmpDetails" from emp where sal>2000;
高级查询
消除重复的行
select distinct deptno from emp;
判断是否为空值 is null
select * from emp where comm is not null;
in 操作 -- 判断一个值是否在一个集合里面 ('SALESMAN',''PRESIDENT','ANALYST')
select * from emp where job in ('SALESMAN','PRESIDENT','ANALYST');
--job = 'SALESMAN' or job = 'PRESIDENT' or job = 'ANALYST'
between ... and ... 在...之间... 取值范围
select * from emp where sal between 1000 and 2000;
-- sal>=1000 and sal<=2000
like 模糊查询
% 零个或多个字符
_ 一个字符
select * from emp where ename like '_A%' ;
集合运算
交集 intersect
并集 union
union all
补集 minus
select * from emp where sal between 1000 and 2000
intersect
select * from emp where ename like '_A%'
/
INSERT INTO DEPT
SELECT 50,'公关部','台湾' FROM DUAL
UNION
SELECT 60,'研发部','西安' FROM DUAL
UNION
SELECT 70,'培训部','西安' FROM DUAL
连接查询 多表查询
笛卡尔集
内连接 满足连接条件的记录连接在一起并提取出来，不满足连接条件的记录不提取。
[ inner ] join
等值连接
select d.dname, e.ename, e.job, e.sal from emp e , dept d where e.deptno=d.deptno;
select d.dname, e.ename, e.job, e.sal from emp e join dept d on e.deptno=d.deptno; --标准写法
非等值连接
select e.ename , e.job, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal ;
外连接 满足连接条件的记录连接在一起并提取出来，不满足连接条件的记录也要提取出来。
[outer] join
左连接 left join
以左表为基准，左表所有记录都提取出来，包括跟右表没有连接的记录。右表只提取跟左表连接的记录。
select d.dname, e.ename, e.job, e.sal from emp e left join dept d on e.deptno=d.deptno;
右连接 right join
以右表为基准，右表所有记录都提取出来，包括跟左表没有连接的记录。左表只提取跟右表连接的记录。
select d.dname, e.ename, e.job, e.sal from emp e right join dept d on e.deptno=d.deptno;
完全连接
full join
select t1.* , t2.* from t1 full join t2 on t1.x=t2.y;
三表连接（多表连接同理）
select t1.*, t2.*, t3.* from t1 [left|right] join t2 on t1.x=t2.y [left|right] join t3 on t2.y=t3.z ; --标准的连接查询
select t1.*, t2.*, t3.* from t1, t2, t3 where t1.x=t2.y and t2.y=t3.z ; --多表查询写法 内连接
select t1.*, t2.* from t1, t2 where t1.x(+)=t2.y ; -- 右外连接
select t1.*, t2.* from t1, t2 where t1.x=(+)t2.y ; -- 左外连接 Oracle私有写法
