SQL单表查询
- SELECT *
- FROM EMP
- WHERE DEPTNO = 30;
-
- SELECT ENAME,EMPNO,DEPTNO
- FROM EMP
- WHERE JOB = 'CLERK';
-
- SELECT *
- FROM EMP
- WHERE NVL(COMM,0) > SAL;
-
- SELECT *
- FROM EMP
- WHERE NVL(COMM,0) > SAL * 0.6;
-
- SELECT *
- FROM EMP
- WHERE (DEPTNO = 10 AND JOB = 'MANAGER')
- OR (DEPTNO = 20 AND JOB = 'CLERK');
- SELECT *
- FROM EMP
- WHERE DEPTNO = 10
- AND JOB = 'MANAGER'
- UNION
- SELECT *
- FROM EMP
- WHERE DEPTNO = 20
- AND JOB = 'CLERK';
-
- SELECT *
- FROM EMP
- WHERE (DEPTNO = 10 AND JOB = 'MANAGER')
- OR (DEPTNO = 20 AND JOB = 'CLERK')
- OR (JOB NOT IN ('MANAGER','CLERK')
- AND SAL >= 2000);
- SELECT *
- FROM EMP
- WHERE DEPTNO = 10
- AND JOB = 'MANAGER'
- UNION
- SELECT *
- FROM EMP
- WHERE DEPTNO = 20
- AND JOB = 'CLERK'
- UNION
- SELECT *
- FROM EMP
- WHERE JOB != 'MANAGER'
- AND JOB != 'CLERK'
- AND SAL >= 2000;
-
- SELECT DISTINCT JOB
- FROM EMP
- WHERE NVL(COMM,0) > 0;
-
- SELECT *
- FROM EMP
- WHERE NVL(COMM,0) < 100
- OR COMM IS NULL;
-
- SELECT *
- FROM EMP
- WHERE HIREDATE = LAST_DAY(HIREDATE) -2;
-
- SELECT *
- FROM EMP
- WHERE HIREDATE < ADD_MONTHS(SYSDATE,-12*12);
-
- SELECT INITCAP(ENAME) AS 姓名
- FROM EMP;
-
- SELECT ENAME
- FROM EMP
- WHERE LENGTH(ENAME) = 5;
- SELECT ENAME
- FROM EMP
- WHERE ENAME LIKE '_____';
-
- SELECT ENAME
- FROM EMP
- WHERE ENAME NOT LIKE '%R%';
-
- SELECT SUBSTR(ENAME,1,3) AS ENAME
- FROM EMP;
-
- SELECT REPLACE(ENAME,'A','a') AS ENAME
- FROM EMP;
- SELECT TRANSLATE(ENAME,'A','a') AS ENAME
- FROM EMP;
-
- SELECT ENAME,HIREDATE
- FROM EMP
- WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE) > 120;
- SELECT ENAME,HIREDATE
- FROM EMP
- WHERE SYSDATE > ADD_MONTHS(HIREDATE,12 * 10);
-
- SELECT *
- FROM EMP
- ORDER BY ENAME;
-
- SELECT ENAME,HIREDATE
- FROM EMP
- ORDER BY HIREDATE;
-
- SELECT ENAME,JOB,SAL
- FROM EMP
- ORDER BY JOB DESC,SAL;
-
- SELECT ENAME,TO_CHAR(HIREDATE,'YYYY"年"MM"月"')
- FROM EMP
- ORDER BY TO_CHAR(HIREDATE,'MM'),TO_CHAR(HIREDATE,'YYYY')
-
- SELECT ENAME,TRUNC(SAL/30)
- FROM EMP;
-
- SELECT *
- FROM EMP
- WHERE TO_CHAR(HIREDATE,'MM') = '02';
-
- SELECT ENAME,FLOOR(SYSDATE - HIREDATE)
- FROM EMP;
-
- SELECT *
- FROM EMP
- WHERE ENAME LIKE '%A%';
-
- SELECT EMPNO,ENAME,
- (
- '在职'||TRUNC((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||'年'||
- TRUNC(MOD((MONTHS_BETWEEN(SYSDATE,HIREDATE)),12))||'个月'||
- ROUND(SYSDATE-(ADD_MONTHS(HIREDATE,MONTHS_BETWEEN
- (SYSDATE,HIREDATE))))||'天'
- )
- FROM EMP;
原文地址:https://www.cnblogs.com/guanghe/p/6054098.html