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)
- 事务的作用 - 确保数据库的一致性
