SELECT * FROM EMP,DEPT;
SELECT *
FROM EMP,DEPT
WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT E.EMPNO,E.ENAME,E.JOB,E.COMM,D.LOC
FROM EMP E,DEPT D --可以为表取个别名
WHERE E.DEPTNO=D.DEPTNO;
SELECTE.EMPNO,E.ENAME,E.HIREDATE,E.SAL,S.GRADE
FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;
INSERT INTO emp
VALUES(8888,'李聪聪','CLERK',7369,SYSDATE,800,100,NULL);
COMMIT;
SELECT *
FROM EMP E,DEPT D
WHERE E.DEPTNO=D.DEPTNO(+); --左外连接
SELECT *
FROM EMP E LEFT JOIN DEPT D --左外连接
ON(E.DEPTNO=D.DEPTNO);
SELECT *
FROM EMP E,DEPT D
WHERE E.DEPTNO(+)=D.DEPTNO; --右外连接
SELECT *
FROM EMP E RIGHT JOIN DEPT D --右外连接
ON(E.DEPTNO=D.DEPTNO);
SELECT *
FROM EMP E FULL JOIN DEPT D --全外连接
ON(E.DEPTNO=D.DEPTNO);
SELECT *
FROM EMP E,EMP M
WHERE E.MGR=M.EMPNO(+);
SELECT *
FROM EMP NATURAL JOIN DEPT; --自然连接
SELECT *
FROM EMP JOIN DEPT USING(DEPTNO); --自然连接USING()设置连接字段
SELECT *
FROM EMP E JOIN SALGRADE S ON(E.SAL BETWEENS.LOSAL AND S.HISAL); --ON()由用户自己制定条件
SELECT * FROM DEPT
UNION --并集操作,重复元素不显示
SELECT * FROM DEPT WHERE DEPTNO=10;
SELECT * FROM DEPT
UNION ALL --并集操作,重复元素显示
SELECT * FROM DEPT WHERE DEPTNO=10;
SELECT * FROM DEPT
MINUS --差集操作
SELECT * FROM DEPT WHERE DEPTNO=10;
SELECT * FROM DEPT
INTERSECT --交集操作
SELECT * FROM DEPT WHERE DEPTNO=10;
SELECT SUM(SAL) --求和函数
FROM EMP;
SELECT MAX(SAL),MIN(SAL),ROUND(AVG(SAL),2)
FROM EMP;
SELECT MIN(HIREDATE)最早雇佣日期,MAX(HIREDATE)最晚雇佣日期
FROM EMP;
SELECT MEDIAN(SAL) --求中间值
FROM EMP;
SELECT STDDEV(SAL)标准差,VARIANCE(SAL)方差 --求标准差与方差
FROM EMP;