常见的sql查询语句有哪些

SQL>DESC emp;

SQL>SELECT * FROM emp;

SQL>SELECT empmo, ename, mgr FROM emp;

SQL>SELECT * FROM emp WHERE job='CLERK';

SQL>SELECT ename, sal*13+nvl(comm,0)  FROM emp;

SQL>SELECT * FROM emp WHERE hiredate>'01-1月-82';

SQL>SELECT * FROM emp WHERE ename like 'S__T%';

SQL>SELECT * FROM emp WHERE job IN ('CLERK','ANALYST');

SQL>SELECT * FROM emp WHERE mgr IS/IS NOT NULL;

SQL>SELECT * FROM emp WHERE (sal>500 or job='MANAGE') and ename like 'J%';

SQL>SELECT * FROM emp ORDER BY deptno, sal DESC;

SQL>SELECT DATE_FORMAT(start_time,"%Y-%m-%d") as "时间";

SQL>SELECT MAX(sal),MIN(age),AVG(sal),SUM(sal) from emp;
SQL>SELECT * FROM emp where sal=(SELECT MAX(sal) from emp));
SQL>SELEC COUNT(*) FROM emp;

SQL>SELECT deptno,MAX(sal),AVG(sal) FROM emp GROUP BY deptno;
SQL>SELECT deptno, job, AVG(sal),MIN(sal) FROM emp group by deptno,job having AVG(sal)<2000;

SQL>SELECT e.name,e.sal,d.dname FROM emp e, dept d WHERE e.deptno=d.deptno order by d.deptno;
SQL>SELECT e.ename,e.sal,s.grade FROM emp e,salgrade s WHER e.sal BETWEEN s.losal AND s.hisal;
Select a.*, b.x, c.y from a
　　left outer join (Select * from tablex where condition1)b
　　on a.id=b.id
　　left outer join (Select * from tabley where condition2)c
　　on a.id=c.id
where condition3;

SQL>SELECT er.ename, ee.ename mgr_name from emp er, emp ee where er.mgr=ee.empno;

SQL>SELECT ename FROM emp WHERE deptno=(SELECT deptno FROM emp where ename='SMITH');

SQL>SELECT ename,job,sal,deptno from emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10);

SQL>SELECT ename,sal,deptno FROM emp WHERE sal> ALL (SELECT sal FROM emp WHERE deptno=30);或SQL>SELECT ename,sal,deptno FROM emp WHERE sal> (SELECT MAX(sal) FROM emp WHERE deptno=30);

SQL>SELECT ename,sal,deptno FROM emp WHERE sal> ANY (SELECT sal FROM emp WHERE deptno=30);或SQL>SELECT ename,sal,deptno FROM emp WHERE sal> (SELECT MIN(sal) FROM emp WHERE deptno=30);

SQL>SELECT * FROM emp WHERE (job, deptno)=(SELECT job, deptno FROM emp WHERE ename='SMITH');

 SQL>SELECT emp.deptno,emp.ename,emp.sal,t_avgsal.avgsal FROM emp,(SELECT emp.deptno,avg(emp.sal) avgsal FROM emp GROUP BY emp.deptno) t_avgsal where emp.deptno=t_avgsal.deptno AND emp.sal>t_avgsal.avgsal ORDER BY emp.deptno;

SQL>SELECT a2.* FROM (SELECT a1.*, ROWNUM rn FROM (SELECT * FROM emp ORDER BY sal) a1 WHERE ROWNUM<=10) a2 WHERE rn>=6;

SQL>CREATE TABLE mytable (id,name,sal,job,deptno) AS SELECT empno,ename,sal,job,deptno FROM emp;

SQL>SELECT ename, sal, job FROM emp WHERE sal>2500 UNION(INTERSECT/UNION ALL/MINUS) SELECT ename, sal, job FROM emp WHERE job='MANAGER';

SQL>CREATE TABLE myEmp(empID number(4), name varchar2(20), sal number(6), job varchar2(10), dept number(2));

SQL>INSERT INTO myEmp(empID, name, sal, job, dept) SELECT empno, ename, sal, job, deptno FROM emp WHERE deptno=10;

SQL>UPDATE emp SET(job, sal, comm)=(SELECT job, sal, comm FROM emp where ename='SMITH') WHERE ename='SCOTT';