本文共 22155 字,大约阅读时间需要 73 分钟。
例如:SELECT DISTINCT deptno, job FROM emp;
SQL> conn scott/02000059已连接。SQL> SELECT deptno, job FROM emp; DEPTNO JOB 20 CLERK 30 SALESMAN 30 SALESMAN 20 MANAGER 30 SALESMAN 30 MANAGER 10 MANAGER 20 ANALYST 10 PRESIDENT 30 SALESMAN 20 CLERK 30 CLERK 20 ANALYST 10 CLERK 已选择14行。SQL> SELECT DISTINCT deptno, job FROM emp; DEPTNO JOB 20 CLERK 30 SALESMAN 20 MANAGER 30 CLERK 10 PRESIDENT 30 MANAGER 10 CLERK 10 MANAGER 20 ANALYST已选择9行。SQL>
指定日期 | 当前年份 | RR格式说明 | RR结果年份 | YY格式说明 | YY结果年份 |
---|---|---|---|---|---|
18-1月-12 | 2017 | 指定年份在0-49之间,当前年份在0-49之间 | 2012 | 当前年份的前两位 + 指定日期的后两位 | 2012 |
18-1月-81 | 2017 | 指定年份在50-99之间,当前年份在0-49之间 | 1981(上一世纪) | 当前年份的前两位 + 指定日期的后两位 | 2081 |
18-1月-12 | 2060 | 指定年份在0-49之间,当前年份在50-99之间 | 2112(下一世纪) | 当前年份的前两位 + 指定日期的后两位 | 2012 |
18-1月-81 | 2060 | 指定年份在50-99之间,当前年份在50-99之间 | 2081 | 当前年份的前两位 + 指定日期的后两位 | 2081 |
SQL> ALTER SESSION SET nls_language='SIMPLIFIED会话已更改。pno, hiredate from emp; EMPNO HIREDATE 7369 17-12月-80 7499 20-2月 -81 7521 22-2月 -81 7566 02-4月 -81 7654 28-9月 -81 7698 01-5月 -81 7782 09-6月 -81 7788 19-4月 -87 7839 17-11月-81 7844 08-9月 -81 7876 23-5月 -87 7900 03-12月-81 7902 03-12月-81 7934 23-1月 -82已选择14行。
SQL> ALTER SESSION SET nls_language = 'AMERICAN';Session altered.SQL> SELECT empno, hiredate from emp; EMPNO HIREDATE 7369 17-DEC-80 7499 20-FEB-81 7521 22-FEB-81 7566 02-APR-81 7654 28-SEP-81 7698 01-MAY-81 7782 09-JUN-81 7788 19-APR-87 7839 17-NOV-81 7844 08-SEP-81 7876 23-MAY-87 7900 03-DEC-81 7902 03-DEC-81 7934 23-JAN-8214 rows selected.
SQL> ALTER SESSION SET nls_date_format = 'YYYY/MM/DD';Session altered.SQL> SELECT empno, hiredate from emp; EMPNO HIREDATE 7369 1980/12/17 7499 1981/02/20 7521 1981/02/22 7566 1981/04/02 7654 1981/09/28 7698 1981/05/01 7782 1981/06/09 7788 1987/04/19 7839 1981/11/17 7844 1981/09/08 7876 1987/05/23 7900 1981/12/03 7902 1981/12/03 7934 1982/01/2314 rows selected.
SQL> SELECT empno, ename, sal, sal *12 year_sal from emp; EMPNO ENAME SAL YEAR_SAL 7369 SMITH 800 9600 7499 ALLEN 1600 19200 7521 WARD 1250 15000 7566 JONES 2975 35700 7654 MARTIN 1250 15000 7698 BLAKE 2850 34200 7782 CLARK 2450 29400 7788 SCOTT 3000 36000 7839 KING 5000 60000 7844 TURNER 1500 18000 7876 ADAMS 1100 13200 7900 JAMES 950 11400 7902 FORD 3000 36000 7934 MILLER 1300 1560014 rows selected.
SQL> SELECT empno, ename, sal * 12 + sal/2 year_sal from emp; EMPNO ENAME YEAR_SAL 7369 SMITH 10000 7499 ALLEN 20000 7521 WARD 15625 7566 JONES 37187.5 7654 MARTIN 15625 7698 BLAKE 35625 7782 CLARK 30625 7788 SCOTT 37500 7839 KING 62500 7844 TURNER 18750 7876 ADAMS 13750 EMPNO ENAME YEAR_SAL 7900 JAMES 11875 7902 FORD 37500 7934 MILLER 1625014 rows selected.
SQL> SELECT empno, ename, sal, comm, sal+ comm FROM emp; EMPNO ENAME SAL COMM SAL+COMM 7369 SMITH 800 7499 ALLEN 1600 300 1900 7521 WARD 1250 500 1750 7566 JONES 2975 7654 MARTIN 1250 1400 2650 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 0 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 130014 rows selected.
- 附件说明:空值和默认值
SQL> create table student(sid number(8,0) primary key, name varchar2(10), sex char(2) default '男', age number(2,0) default 20, address varchar2(50));Table created.SQL> Insert into student(sid, name) values(20012001, '张三');1 row created.SQL> Insert into student values(20011002, '王五', default, default, '广东广州');1 row created.
不使用列的名称别名:SELECT empno, ename, sal * 12 from emp;
SQL> conn scott/02000059已连接。SQL> select empno, ename, sal * 12 FROM emp; EMPNO ENAME SAL*12 7369 SMITH 9600 7499 ALLEN 19200 7521 WARD 15000 7566 JONES 35700 7654 MARTIN 15000 7698 BLAKE 34200 7782 CLARK 29400 7788 SCOTT 36000 7839 KING 60000 7844 TURNER 18000 7876 ADAMS 13200 EMPNO ENAME SAL*12 7900 JAMES 11400 7902 FORD 36000 7934 MILLER 15600已选择14行。
使用列的别名:SELECT empno "雇员编号" , ename "雇员名" , sal 12 "全年工资" FROM emp;、SELECT empno "雇员编号", ename "雇员名" , sal 12 AS "全年工资" FROM emp;、SELECT empno 雇员编号, ename 雇员名, sal * 12 全年工资 FROM emp;。
SQL> SELECT empno "雇员编号" , ename "雇员名" , sal * 12 "全年工资" FROM emp; 雇员编号 雇员名 全年工资 7369 SMITH 9600 7499 ALLEN 19200 7521 WARD 15000 7566 JONES 35700 7654 MARTIN 15000 7698 BLAKE 34200 7782 CLARK 29400 7788 SCOTT 36000 7839 KING 60000 7844 TURNER 18000 7876 ADAMS 13200 雇员编号 雇员名 全年工资 7900 JAMES 11400 7902 FORD 36000 7934 MILLER 15600已选择14行。
SQL> SELECT empno "雇员编号", ename "雇员名" , sal * 12 AS "全年工资" FROM emp; 雇员编号 雇员名 全年工资 7369 SMITH 9600 7499 ALLEN 19200 7521 WARD 15000 7566 JONES 35700 7654 MARTIN 15000 7698 BLAKE 34200 7782 CLARK 29400 7788 SCOTT 36000 7839 KING 60000 7844 TURNER 18000 7876 ADAMS 13200 雇员编号 雇员名 全年工资 7900 JAMES 11400 7902 FORD 36000 7934 MILLER 15600已选择14行。
SQL> SELECT empno 雇员编号, ename 雇员名, sal * 12 全年工资 FROM emp; 雇员编号 雇员名 全年工资 7369 SMITH 9600 7499 ALLEN 19200 7521 WARD 15000 7566 JONES 35700 7654 MARTIN 15000 7698 BLAKE 34200 7782 CLARK 29400 7788 SCOTT 36000 7839 KING 60000 7844 TURNER 18000 7876 ADAMS 13200 雇员编号 雇员名 全年工资 7900 JAMES 11400 7902 FORD 36000 7934 MILLER 15600已选择14行。
举例:select ename || '的岗位是:' || job as 员工的职位信息描述 from emp;
SQL> select ename || '的岗位是:' || job as 员工的职位信息描述 from emp;员工的职位信息描述SMITH的岗位是:CLERKALLEN的岗位是:SALESMANWARD的岗位是:SALESMANJONES的岗位是:MANAGERMARTIN的岗位是:SALESMANBLAKE的岗位是:MANAGERCLARK的岗位是:MANAGERSCOTT的岗位是:ANALYSTKING的岗位是:PRESIDENTTURNER的岗位是:SALESMANADAMS的岗位是:CLERK员工的职位信息描述JAMES的岗位是:CLERKFORD的岗位是:ANALYSTMILLER的岗位是:CLERK已选择14行。
SQL> SELECT ename || '的月工资是: ' || SAL from emp;ENAME||'的月工资是:'||SAL-SMITH的月工资是: 800ALLEN的月工资是: 1600WARD的月工资是: 1250JONES的月工资是: 2975MARTIN的月工资是: 1250BLAKE的月工资是: 2850CLARK的月工资是: 2450SCOTT的月工资是: 3000KING的月工资是: 5000TURNER的月工资是: 1500ADAMS的月工资是: 1100ENAME||'的月工资是:'||SALJAMES的月工资是: 950FORD的月工资是: 3000MILLER的月工资是: 1300已选择14行。
SQL> SELECT * from emp where deptno = 20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-12月-80 800 20 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7902 FORD ANALYST 7566 03-12月-81 3000 20
SQL> SELECT * FROM emp WHERE job = 'MANAGER'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450
SQL> select * from emp where hiredate = '02-4月-81'; EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO 7566 JONES MANAGER 7839 02-4月 -81 2975 20
运算符 | 说明 |
---|---|
= | 等于(不是==) |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
<> | 不等于!= |
SQL> SELECT * FROM emp WHERE sal <> 3000; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7934 MILLER CLERK 7782 23-1月 -82 1300 10已选择12行。
SQL> SELECT empno, ename, sal from emp where sal between 1500 and 3000; EMPNO ENAME SAL 7499 ALLEN 1600 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7844 TURNER 1500 7902 FORD 3000已选择7行。
SQL> select empno, ename, job from emp where job in ('SALESMAN', 'MANAGER' , 'CLERK'); EMPNO ENAME JOB 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7934 MILLER CLERK已选择11行。
SQL> select * from emp where ename like 'J%'; EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7900 JAMES CLERK 7698 03-12月-81 950 30
QL> select * from emp where ename like '_AR%';EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30
SQL> select * from emp where ename like 'G\_%' escape '\'; EMPNO ENAME JOB MGR HIREDATE SAL COMMDEPTNO 7369 G_EASON CLERK 7902 17-12月-80 800 20
SQL> select empno, ename, sal, comm from emp where comm is null; EMPNO ENAME SAL COMM 7369 G_EASON 800 7566 JONES 2975 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300已选择10行。
SQL> select empno, ename ,sal, comm from emp where comm is not null; EMPNO ENAME SAL COMM 7499 ALLEN 1600 300 7521 WARD 1250 500 7654 MARTIN 1250 1400 7844 TURNER 1500 0
SQL> SELECT empno, ename, job, deptno from emp where job='MANAGER' and deptno = 10; EMPNO ENAME JOB DEPTNO 7782 CLARK MANAGER 10
SQL> select empno, ename, job, sal from emp where job='MANAGER' or sal > 2000; EMPNO ENAME JOB SAL 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7902 FORD ANALYST 3000已选择6行。
SQL> select empno, ename, job from emp where job not in('CLERK', 'SALESMAN', 'MANAGER'); EMPNO ENAME JOB 7788 SCOTT ANALYST 7839 KING PRESIDENT 7902 FORD ANALYST
SQL> SELECT empno, ename, job, sal from emp where (sal > 2000 or deptno = 30) and job not in ('PRESIDENT', 'MANAGER'); EMPNO ENAME JOB SAL 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 7788 SCOTT ANALYST 3000 7844 TURNER SALESMAN 1500 7900 JAMES CLERK 950 7902 FORD ANALYST 3000已选择7行。
SELECT ename, sal FROM emp ORDER BY sal;(默认升序)
SQL> SELECT ename, sal FROM emp ORDER BY sal ASC;ENAME SALG_EASON 800JAMES 950ADAMS 1100WARD 1250MARTIN 1250MILLER 1300TURNER 1500ALLEN 1600CLARK 2450BLAKE 2850JONES 2975ENAME SALSCOTT 3000FORD 3000KING 5000已选择14行。
SQL> SELECT ename, sal FROM emp ORDER BY sal DESC;ENAME SALKING 5000FORD 3000SCOTT 3000JONES 2975BLAKE 2850CLARK 2450ALLEN 1600TURNER 1500MILLER 1300WARD 1250MARTIN 1250ENAME SALADAMS 1100JAMES 950G_EASON 800已选择14行。
select empno, ename, sal * 12 年收入 from emp order by 年收入 ASC;
SQL> select empno, ename, sal * 12 年收入 from emp order by 年收入 ASC; EMPNO ENAME 年收入 7369 G_EASON 9600 7900 JAMES 11400 7876 ADAMS 13200 7521 WARD 15000 7654 MARTIN 15000 7934 MILLER 15600 7844 TURNER 18000 7499 ALLEN 19200 7782 CLARK 29400 7698 BLAKE 34200 7566 JONES 35700 7788 SCOTT 36000 7902 FORD 36000 7839 KING 60000已选择14行。
SQL> SELECT empno, ename, deptno, sal from emp order by deptno asc, sal desc; EMPNO ENAME DEPTNO SAL 7839 KING 10 5000 7782 CLARK 10 2450 7934 MILLER 10 1300 7788 SCOTT 20 3000 7902 FORD 20 3000 7566 JONES 20 2975 7876 ADAMS 20 1100 7369 G_EASON 20 800 7698 BLAKE 30 2850 7499 ALLEN 30 1600 7844 TURNER 30 1500 7654 MARTIN 30 1250 7521 WARD 30 1250 7900 JAMES 30 950已选择14行。
转载于:https://blog.51cto.com/12402717/2046382