SQL> --查詢10號部門的所有員工信息
SQL> select *
2 from emp
3 where empno = 10;
未選定行
SQL> ed
SP2-0110: 無法創建保存文件 "afiedt.buf"
SQL> 3
3* where empno = 10
SQL> c /empno/deptno;
3* where deptno = 10
SQL> /
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
7934 MILLER CLERK 7782 23-1月 -82
1300 10
SQL> set linesize 120l
SP2-0268: linesize 選項的編號無效
SQL> set linesize 120;
SQL> /
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
7934 MILLER CLERK 7782 23-1月 -82 1300 10
SQL> -- 查詢名字為KING的員工信息
SQL> select *
2 from emp
3 where ename = 'KING';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
SQL> 3
3* where ename = 'KING'
SQL> c /KING/King;
3* where ename = 'King'
SQL> /
未選定行
SQL> --在oracle中字段的名稱是不區分大小寫的，但是字段里面的值是區分大小的
SQL> --查詢入職日期是81-11-17號的員工信息
SQL> select *
2 from emp
3 where hiredate = '17-11月-81';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11月-81 5000 10
SQL> -- 在oracle中時間的表示格式是 DD-MON-RR
SQL> select * from
2 emp
3 where hiredate = '1981-11-17';
where hiredate = '1981-11-17'
*
第 3 行出現錯誤:
ORA-01861: 文字與格式字符串不匹配
SQL> --查詢oracle中的一般常亮設置
SQL> select * from v$nls_paramters;
select * from v$nls_paramters
*
第 1 行出現錯誤:
ORA-00942: 表或視圖不存在
SQL> select * from v$nls_parameters;
PARAMETER
------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE
NLS_TERRITORY
CHINA
NLS_CURRENCY
￥
PARAMETER
------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA
NLS_NUMERIC_CHARACTERS
.,
NLS_CALENDAR
GREGORIAN
PARAMETER
------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR
NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE
NLS_CHARACTERSET
AL32UTF8
PARAMETER
------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
PARAMETER
------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY
￥
PARAMETER
------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_COMP
BINARY
NLS_LENGTH_SEMANTICS
BYTE
PARAMETER
------------------------------------------------------------------------------------------------------------------------
VALUE
------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE
已選擇19行。
SQL> set linesize 200;
SQL> /
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE
NLS_TERRITORY
CHINA
NLS_CURRENCY
￥
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA
NLS_NUMERIC_CHARACTERS
.,
NLS_CALENDAR
GREGORIAN
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR
NLS_DATE_LANGUAGE
SIMPLIFIED CHINESE
NLS_CHARACTERSET
AL32UTF8
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_SORT
BINARY
NLS_TIME_FORMAT
HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY
￥
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16
NLS_COMP
BINARY
NLS_LENGTH_SEMANTICS
BYTE
PARAMETER
--------------------------------------------------------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------
NLS_NCHAR_CONV_EXCP
FALSE
已選擇19行。
SQL> col parameter for a50;
SQL> /
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ￥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY ￥
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
已選擇19行。
SQL> alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd';
會話已更改。
SQL> select *enaee
2 from emp
3 where hiredate='1981-11-17';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 5000 10
SQL> select *
2 from emp
3 where hiredate='17-11月-81';
where hiredate='17-11月-81'
*
第 3 行出現錯誤:
ORA-01861: 文字與格式字符串不匹配
SQL> select * from v$nls_parameters;
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ￥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT yyyy-mm-dd
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_CHARACTERSET AL32UTF8
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
-------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY ￥
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
已選擇19行。
SQL> select *
2 from emp
3 where ename='KING' and job='PRESIDENT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 5000 10
SQL> --查詢工資在2000-5000之間的所有員工
SQL> select *
2 from emp
3 where sal>2000 and sal < 5000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7902 FORD ANALYST 7566 1981-12-03 3000 20
SQL> select *
2 from emp
3 where sal between 2000 and 5000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7902 FORD ANALYST 7566 1981-12-03 3000 20
已選擇6行。
SQL> select *
2 from emp
3 where sal between 5000 and 2000;
未選定行
SQL> --between ...and 只能將小的寫在前面大的寫在后面
SQL> -- in/not in
SQL> --查詢20?30號部門的所有員工
SQL> select *
2 from emp
3 where deptno = 20 or deptno = 30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
已選擇11行。
SQL> select *
2 from emp
3 where deptno in(20,30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
已選擇11行。
SQL> -- 查詢不在20和30號部門里面的所有員工
SQL> select *
2 from emp
3 where deptno != 20 and deptno != 30;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7934 MILLER CLERK 7782 1982-01-23 1300 10
SQL> select *
2 from emp
3 where deptno not in(20,30);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7839 KING PRESIDENT 1981-11-17 5000 10
7934 MILLER CLERK 7782 1982-01-23 1300 10
SQL> -- 查詢獎金為300和為空的所有員工信息
SQL> select *
2 from emp
3 where comm in(300,null
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
SQL> select *
2 from emp
3 where comm not in (300,null);
未選定行
SQL> -- in里面可以跟null，而not in 里面不能跟null
SQL> -- 查詢comm為空的所有員工信息
SQL> select *
2 from emp
3 where comm is null;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7566 JONES MANAGER 7839 1981-04-02 2975 20
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7876 ADAMS CLERK 7788 1987-05-23 1100 20
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10
已選擇10行。
SQL> -- 模糊查詢
SQL> -- 查詢員工名字里面包含了O的所有員工信息
SQL> select *
2 from emp
3 where ename like '%O%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7566 JONES MANAGER 7839 1981-04-02 2975 20
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7902 FORD ANALYST 7566 1981-12-03 3000 20
SQL> -- like 是一個關鍵字 表示模糊查詢，其中的% 代表通配
SQL> -- 查詢員工名字里面以K打頭的員工信息
SQL> select *
2 from emp
3 where ename like 'K%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7839 KING PRESIDENT 1981-11-17 5000 10
SQL> -- 查詢ename的長度為4的所有員工信息
SQL> select *
2 from emp
3 where ename like '____';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7839 KING PRESIDENT 1981-11-17 5000 10
7902 FORD ANALYST 7566 1981-12-03 3000 20
SQL> -- 在oracle中_ 代表一個字符
SQL> insert into emp(empno,ename,sal,deptno) values(1001,'TOM_ab',50000,20);
已創建 1 行。
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10
1001 TOM_ab 50000 20
已選擇15行。
SQL> -- 查詢ename字段里面包含了_的員工信息
SQL> select *
2 from emp
3 where ename like '%_%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10
1001 TOM_ab 50000 20
已選擇15行。
SQL> -- oracle中的轉義
SQL> select *
2 from emp
3 where ename like '%\_%' escape '\';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
1001 TOM_ab 50000 20
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10
1001 TOM_ab 50000 20
已選擇15行。
SQL> rollback;
回退已完成。
SQL> /
回退已完成。
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 30
7782 CLARK MANAGER 7839 1981-06-09 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 3000 20
7839 KING PRESIDENT 1981-11-17 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ---------- ---------- ---------- ----------
7900 JAMES CLERK 7698 1981-12-03 950 30
7902 FORD ANALYST 7566 1981-12-03 3000 20
7934 MILLER CLERK 7782 1982-01-23 1300 10
已選擇14行。
SQL> spool off;
文章列表
留言列表
