Oracle學習（1）：基本語法
Oracle基本語法
登陸
安裝完畢Oracle之後，在services裡面開啟Oracle服務，運行cmd，輸入sqlplus scott/
你的scott密碼 連接成功。
錄屏清屏
SQL> --錄屏為txt文件
SQL>spool off
SQL> --清屏
SQL> host cls
select基本查詢
SQL> --當前用戶下的表
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
SQL> desc emp
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> desc dept
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> --查詢所有員工信息
SQL> select * from emp;
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
7788 SCOTT ANALYST 7566 13-7月 -87 3000
20
7839 KING PRESIDENT 17-11月-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0
30
7876 ADAMS CLERK 7788 13-7月 -87 1100
20
7900 JAMES CLERK 7698 03-12月-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-12月-81 3000
20
7934 MILLER CLERK 7782 23-1月 -82 1300
10
已選擇14行。
顯示表格設置
SQL> --設置行寬
SQL> set linesize 150
SQL> --設置列寬
SQL> col ename for a8 （a代表字符串 a8表示8位字符串）
SQL> col sal for 9999 （每一個9代表從0到9的一個數字位）
SQL> /
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
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
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------- --------- ---------- -------------- ----- ---------- ----------
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
SQL> set pagesize 20 （設置每頁顯示多少）
SQL> /
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
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
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno
2 from emp;
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
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
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
SQL> --*和所有列的方式，哪個好？
SQL> --列名好，但在oracle 9i後，可以認為一樣
SQL> host cls
查詢多項
SQL> --查詢員工的編號，姓名和月薪
SQL> select empno,ename,sal
2 from emp;
EMPNO ENAME SAL
---------- -------- -----
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
7876 ADAMS 1100
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
已選擇14行。
語句出現錯誤如何修改
SQL> --查詢員工的編號，姓名,月薪,年薪
SQL> select empno,ename,sal, sal*12
2 form emp;
form emp
*
第 2 行出現錯誤:
ORA-00923: 未找到要求的 FROM 關鍵字
SQL> --使用c命令修改上一條SQL
SQL> 2
2* form emp
SQL> c /form/from
2* from emp
SQL> /
EMPNO ENAME SAL SAL*12
---------- -------- ----- ----------
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 15600
已選擇14行。
SQL> --使用ed命令
SQL> ed
已寫入 file afiedt.buf
1 select empno as "員工號",ename "姓名",sal 月薪, sal*12 年薪
2* from emp
SQL> /
員工號 姓名 月薪 年薪
---------- ---------- ---------- ----------
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 15600
已選擇14行。
SQL> ed
已寫入 file afiedt.buf
1 select empno as "員工號",ename "姓名",sal 月 薪, sal*12 年薪
2* from emp
SQL> /
select empno as "員工號",ename "姓名",sal 月 薪, sal*12 年薪
*
第 1 行出現錯誤:
ORA-00923: 未找到要求的 FROM 關鍵字
SQL> ed
已寫入 file afiedt.buf
1 select empno as "員工號",ename "姓名",sal "月 薪", sal*12 年薪
2* from emp
SQL> /
員工號 姓名 月 薪 年薪
---------- ---------- ---------- ----------
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 15600
已選擇14行。
SQL> hostlcs
SP2-0042: 未知命令 "hostlcs" - 其余行忽略。
SQL> host cls
SQL> --查詢員工的編號，姓名,月薪,年薪,年收入
SQL> select * from emp;
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
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
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇14行。
有關空值的查詢
SQL> ed
已寫入 file afiedt.buf
1* select * from emp
SQL> select empno,ename,sal 月薪,sal*12 年薪,comm 獎金 ,sal*12+comm 年收入
2 from emp;
EMPNO ENAME 月薪 年薪 獎金 年收入
---------- -------- ---------- ---------- ---------- ----------
7369 SMITH 800 9600
7499 ALLEN 1600 19200 300 19500
7521 WARD 1250 15000 500 15500
7566 JONES 2975 35700
7654 MARTIN 1250 15000 1400 16400
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 18000 0 18000
7876 ADAMS 1100 13200
7900 JAMES 950 11400
7902 FORD 3000 36000
7934 MILLER 1300 15600
已選擇14行。
SQL> /*
SQL> 關於空值:
SQL> 1. 含義空值的表達式為空
SQL> 2. 在SQL中，null!=null
SQL> */
SQL> ed
已寫入 file afiedt.buf
1 select empno,ename,sal 月薪,sal*12 年薪,comm 獎金 ,sal*12+nvl(comm,0) 年收入
2* from emp
SQL> /
EMPNO ENAME 月薪 年薪 獎金 年收入
---------- -------- ---------- ---------- ---------- ----------
7369 SMITH 800 9600 9600
7499 ALLEN 1600 19200 300 19500
7521 WARD 1250 15000 500 15500
7566 JONES 2975 35700 35700
7654 MARTIN 1250 15000 1400 16400
7698 BLAKE 2850 34200 34200
7782 CLARK 2450 29400 29400
7788 SCOTT 3000 36000 36000
7839 KING 5000 60000 60000
7844 TURNER 1500 18000 0 18000
7876 ADAMS 1100 13200 13200
7900 JAMES 950 11400 11400
7902 FORD 3000 36000 36000
7934 MILLER 1300 15600 15600
已選擇14行。
SQL> -- 2. 在SQL中，null!=null
SQL> 查詢獎金為空的員工
SP2-0734: 未知的命令開頭 "查詢獎金為..." - 忽略了剩余的行。
SQL> --查詢獎金為空的員工
SQL> select *
2 from emp
3 where comm=null;
未選定行
SQL> ed
已寫入 file afiedt.buf
1 select *
2 from emp
3* where comm is null
SQL> /
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
7698 BLAKE MANAGER 7839 01-5月 -81 2850 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7788 SCOTT ANALYST 7566 13-7月 -87 3000 20
7839 KING PRESIDENT 17-11月-81 5000 10
7876 ADAMS CLERK 7788 13-7月 -87 1100 20
7900 JAMES CLERK 7698 03-12月-81 950 30
7902 FORD ANALYST 7566 03-12月-81 3000 20
7934 MILLER CLERK 7782 23-1月 -82 1300 10
已選擇10行。
SQL> 1. 含義空值的表達式為空
SP2-0734: 未知的命令開頭 "1. 含義空..." - 忽略了剩余的行。
SQL> host cls
關於對重復值的查找
SQL> --DISTINCT:重復的只取一次
SQL> select deptno from emp;
DEPTNO
----------
20
30
30
20
30
30
10
20
10
30
20
30
20
10
已選擇14行。
SQL> ed
已寫入 file afiedt.buf
1* select DISTINCT deptno from emp
SQL> /
DEPTNO
----------
30
20
10
SQL> select job from emp;
JOB
---------
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
CLERK
已選擇14行。
SQL> ed
已寫入 file afiedt.buf
1* select distinct deptno,job from emp
SQL> /
DEPTNO JOB
---------- ---------
20 CLERK
30 SALESMAN
20 MANAGER
30 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
已選擇9行。
SQL> --distinct後面如果有多列，作用於後面所有的列
SQL> host cls
拼接字符串
SQL> --concat:拼加字符串
SQL> select concat('Hello',' World') from dual;
CONCAT('HEL
-----------
Hello World
dual偽表
SQL> --dual表：偽表，方便測試，滿足語法的要求
SQL> select 3+2 from dual;
3+2
----------
5
SQL> select sysdate from dual;
SYSDATE
--------------
25-3月 -12
連接符
SQL> --連接符
SQL> select 'Hello'||' World' from dual;
'HELLO'||'W
-----------
Hello World
SQL> --查詢員工信息：***的職位是****
SQL> select ename||'的職位是'||job
2 from emp;
ENAME||'的職位是'||JOB
---------------------------
SMITH的職位是CLERK
ALLEN的職位是SALESMAN
WARD的職位是SALESMAN
JONES的職位是MANAGER
MARTIN的職位是SALESMAN
BLAKE的職位是MANAGER
CLARK的職位是MANAGER
SCOTT的職位是ANALYST
KING的職位是PRESIDENT
TURNER的職位是SALESMAN
ADAMS的職位是CLERK
JAMES的職位是CLERK
FORD的職位是ANALYST
MILLER的職位是CLERK
已選擇14行。
SQL> /*
SQL> 掌握：
SQL> 1. 空值
SQL> 2. distinct
SQL> 3. 連接符
SQL> 4. dual表
SQL> */
SQL> desc emp
名稱 是否為空? 類型
----------------------------------------------------------------------------------- -------- --------------------------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> select ename||'的職位是'||job
2 from emp;
ENAME||'的職位是'||JOB
---------------------------
SMITH的職位是CLERK
ALLEN的職位是SALESMAN
WARD的職位是SALESMAN
JONES的職位是MANAGER
MARTIN的職位是SALESMAN
BLAKE的職位是MANAGER
CLARK的職位是MANAGER
SCOTT的職位是ANALYST
KING的職位是PRESIDENT
TURNER的職位是SALESMAN
ADAMS的職位是CLERK
JAMES的職位是CLERK
FORD的職位是ANALYST
MILLER的職位是CLERK
已選擇14行。
sql的導出導入
SQL> save c:\aa.sql
已創建 file c:\aa.sql
SQL> @c:\aa.sql
ENAME||'的職位是'||JOB
---------------------------
SMITH的職位是CLERK
ALLEN的職位是SALESMAN
WARD的職位是SALESMAN
JONES的職位是MANAGER
MARTIN的職位是SALESMAN
BLAKE的職位是MANAGER
CLARK的職位是MANAGER
SCOTT的職位是ANALYST
KING的職位是PRESIDENT
TURNER的職位是SALESMAN
ADAMS的職位是CLERK
JAMES的職位是CLERK
FORD的職位是ANALYST
MILLER的職位是CLERK
已選擇14行。
SQL> spool off
注意點：
SQL語言注意點：
SQL語言大小寫不敏感。 SQL可以寫在一行或者多行 關鍵字不能被縮寫也不能分行 各子句一般要分行寫。 使用縮進提高語句的可讀性。
空值
空值是無效的，未指定的，未知的或不可預知的值 空值不是空格或者0。
包含空值的數學表達式的值都為空值
列的別名:
重命名一個列。 便於計算。 緊跟列名，也可以在列名和別名之間加入關鍵字‘AS’，別名使用雙引號，以便在別名中包含空格或特殊的字符並區分大小寫。 AS可以省略
連接符:
把列與列，列與字符連接在一起。 用‘||’表示。 可以用來‘合成’列。
字符串
字符串可以是SELECT列表中的一個字符,數字,日期。 日期和字符只能在單引號中出現。 每當返回一行時，字符串被輸出一次。
重復行
默認情況下，查詢會返回全部行，包括重復行
在SELECT 子句中使用關鍵字‘DISTINCT’刪除重復行
sql與sql*plus
SQL
一種語言 ?ANSI 標准 ?關鍵字不能縮寫 ?使用語句控制數據庫中的表的定義信息和表中的數據
SQL*Plus
一種環境 ?Oracle的特性之一 ?關鍵字可以縮寫 ?命令不能改變數據庫中的數據的值 集中運行
