© 逻辑运算符和谓词
in 和 not in
有哪些职员和分析员
SELECT ename,job
FORM emp
WHERE job IN ('clerk','analyst')
between 和
not between
哪些雇员的工资在 2000 和 3000
之间
SELECT ename,job,sal
FORM emp
WHERE sal BETWEEN 2000 AND 3000
like,not like
SELECT ename,deptno
FORM emp
WHERE ename LIKE 'S%'
is null,is not null
SELECT ename,job
FORM emp
WHERE comm IS NULL
© 数据操作
数据的插入
普通插入
INSERT INTO dept (empno,ename,deptno,empdate)
VALUES
(10,'accounting','new york',to_char(SYSDATE,'YYYY/MM/DD hh24:mi:ss') )
插入的时候同时查询
INSERT INTO dept (empno,ename,deptno,empdate)
VALUES
(10,'accounting',(SELECT deptno FROM depemp WHERE empno=10 AND
rownum<=1),to_char(SYSDATE,'YYYY/MM/DD hh24:mi:ss') )
从其它表中选择插入数据
INSERT INTO emp (empno,ename,deptno)
SELECT id,name,department
FORM old_emp
WHERE department in(10,20,30,40)
插入空值(NULL)
INSERT INTO dept VALUES(50,'education',NULL)
数据更新
UPDATE emp SET deptno=40,job='market rep' WHERE job='salesman'
数据删除
DELETE emp WHERE empno=765;
数据查询
等值连接
join
SELECT empno,ename,job,emp.deptno,dname
FORM emp
JOIN dept ON emp.deptno=dept.deptno
等价于
SELECT empno,ename,job,emp.deptno,dname
FORM emp,dept
WHERE emp.deptno=dept.deptno
左外连接
left (outer) join
SELECT m_user.user_cd,m_user.user_number,m_user.user_name
FORM m_user
LEFT OUTER JOIN m_user_account ON m_user.user_cd=m_user_account.user_cd
等价于
SELECT m_user.user_cd,m_user.user_number,m_user.user_name
FORM m_user,m_user_account
WHERE m_user.user_cd=m_user_account.user_cd(+)
右外连接
right (outer) join
SELECT m_user.user_cd,m_user.user_number,m_user.user_name
FORM m_user
RIGHT JOIN m_user_account ON m_user.user_cd=m_user_account.user_cd
等价于
SELECT m_user.user_cd,m_user.user_number,m_user.user_name
FORM m_user,m_user_account
WHERE m_user.user_cd(+)=m_user_account.user_cd
全外连接
full (outer) join
SELECT m_user.user_cd,m_user.user_number,m_user.user_name
FROM m_user
FULL JOIN m_user_account ON m_user.user_cd=m_user_account.user_cd
等价于
SELECT m_user.user_cd,m_user.user_number,m_user.user_name