ORACLE REVIEW 1

Oracle Review1

基本查询

select[distinct] * from dept;

显示EMP表的结构

DESC emp

查询日期且制定格式：

select enname, to_char(hiredata, 'YY-MM-DD') from emp

去掉重复项

Select distinct deptno,job FROM emp

算术表达式 + 使用别名

SELECT ename, sal, sal*12 "Annual Salary" FROM emp

处理NULL

nvl(comm, 0) : NULL 转为0

连接字符串 ：

SELECT ename ||''s occupation'|| job "employee" FROM emp

---

限制数据

比较运算符

= 等于
<>， != 不等于
< 或 >
<=, >=
between ..and.., 在两个值之间
in(list) 匹配于列标值
like 匹配于字符样式
is NULL 测试NULL

where 子句 + AND/OR

SELECT ename, sal FROM emp
WHERE sal > 2000 AND/OR WHERE job = 'manager'
或 WHERE sal BETWEEN 2000 AND 3000
或 hiredate > '01-1月-82'

where + like + 通配符

% 表示0个或多个字符
_ 表示单个字符

SELECT ename,sal FROM emp WHERE ename
LIKE 'S%';

SELECT ename,sal FROM emp WHERE ename
LIKE '__O%';

特别的：显示EMP表中所有雇员名包含_的雇员的雇员名及工资信息

SELECT ename, sal FROM emp
where ename = '%a_%' ESCAPE 'a'

where + in

SELECT ename, sal, job FROM emp
job IN( 'CLERK', 'MANAGER') # job in(list)

Where + IS NULL

select ename from emp
WHERE mgr IS NULL # 不是 where mgr = NULL

Where + NOT

SELECT ename, sal, comm, deptno FROM
where comm IS NOT NULL

Where + Order by 排序数据

Select ename, sal from EMP
order by sal ASC/DESC

Select ename, deptno, sal FROM emp
order by deptno, sal DESC # 默认升序

---

复杂查询

Max / Min /AVG/ SUM 函数

VARIANCE/STDDEV 函数

select max(sal), min(sal), avg(sal), sum(sal) from emp

Group by / Having 子句

SELECT deptno, avg(sal) from emp
Group by deptno
select deptno, job, avg(sal), max(sal)from emp
Group by deptno, job
select deptno, avg(sal), max(sal) from emp
Group by deptno Having avg(sal) < 2500

连接查询

相等连接

select emp.deptno(或 e.deptno), dname, ename from dept d, emp, e
where d.deptno = e.deptno

不相等连接

select e.ename, e.sal, sg.grade from emp e, salgrad sg
where e.sal between salgrad.losal and salgrade.hisal

自连接

Q : 显示BLAKE雇员的上级领导名字
select manager.ename
from emp manager, emp worker
where manager.empno = worker.mgr
AND worker.ename = 'BLAKE'

内连接和外连接

内连接(INNER JOIN)：返回所有满足条件的记录

select e.deptno, e.ename, e.sal from emp e, dept d
where e.deptno = d.deptno and e.deptno = 10
等价于
select d.dname, e.ename, e.sal
from dept d INNER JOIN emp p
ON d.deptno = e.deptno AND e.deptno = 10 # 不用where了

左外连接：1）返回满足连接条件的所有记录 2）返回不满足连接条件的连接操作符左边表的其他行。

Select d.dname, e.ename
from dept d LEFT JOIN emp e
ON d.deptno = e.deptno AND d.deptno = 10
或者select d.dname, e.ename from dept d, emp e
Where d.deptno = e.deptno(+) # 返回没有(+)的一边
AND e.deptno (+) = 10

右外连接：1）返回满足连接条件的所有记录 2）返回不满足连接条件的连接操作符右边表的其他行。

Select d.dname, e.ename
from dept d RIGHT JOIN emp e
ON d.deptno = e.deptno AND d.deptno = 10
或者
select d.dname, e.ename from dept d, emp e
Where d.deptno(+) = e.deptno # 返回没有(+)的一边; 只能跟where一起使用
AND e.deptno (+) = 10

完全外连接 ：1）返回满足连接条件的所有记录2）返回不满足连接条件的所有其它行

Select d.dname, e.ename
from dept d FULL JOIN emp e
ON d.deptno = e.deptno AND d.deptno = 10

---

子查询:嵌入在其他SQL语句中的SELECT语句

返回单行单列/单行多列/多行多列

IN 匹配于子查询结果的任一个值即可
ALL 必须要符合子查询结果的所有值
ANY 主要符合子查询结果的任一个值

select ename, job, sal, deptno from emp
Where job IN (select DINSTINCT job from emp where deptno = 10)

其他子查询

使用集合操作符

UNION: 取得两个结果集的并集，并自动去掉结果集中的重复
UNION ALL: 并集
INTERSECT 交集
MINUS：差集

SELECT ename,sal,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER'

---

SQL函数

插入数据

INSERT INTO 单条函数

INSERT INTO dept VALUES(50, 'TRAIN', 'BOSTON')
INSERT INTO emp(empno, ename, job, hiredate) VALUES(1234, 'JOHN', 'CLERK', '01-3月-86')
INSERT INTO emp(empno, ename, job,hiredate) VALUES(1356, 'MARY', 'CLERK', to_date('1983-1', 'YYYY-MM-DD')
INSERT INTO dept(deptno, dname, loc) VALUES(60, 'MARKET', DEFAULT) # default 默认值

INSERT INTO … SubQuery 插入多行数据

CREAT TABLE employee
(empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2))

更改属性

alter table employee modify empno not null

INSERT 查询插入

INSERT INTO employee(empno, ename, sal, deptno)
SELECT empno, ename, sal, deptno from emp
where deptno = 20
INSERT '/+APPEND/ INTO employee(empno, ename, sal, deptno)

INSERT 多表同时插入

INSERT ALL
WHEN deptno = 10 THEN INTO dept10
WHEN deptno = 20 THEN INTO dept20
WHEN deptno = 30 THEN INTO dept30
WHEN job = 'CLERK' THEN INTO clerk
ELSE INTO other
SELECT * FROM emp

更新数据 UPDATE…SET

UPDATE emp SET sal = 2460 WHERE ename = 'SCOTT'
UPDATE emp SET hiredate = to_date('1984-01-01', 'YYYY-MM-DD') WHERE empno = 7788

UPDATE+子查询

UPDATE employee SET deptno =
(SELECT deptno FROM emp where empno = 7788)
where job = (SELECT job FROM emp where empno = 7788 )

删除数据

DELETE FROM employee # 删除所有行
TRUNCATE TABLE employee # 截断表
DELETE FROM emp
where deptno = (SELECT deptno FROM dept WHERE dname = 'SALES' )

回退事务

ROLLBACK

控制事务

• 事务：连续地执行提交操作(COMMIT) 或回退操作(ROLLBACK)之间的操作
• 事务的组成 - DML语句(INSERT、UPDATE、 DELETE)
• 事务的作用 - 确保数据库的一致性