ORACLE循序漸進講解（第二講），oracle第二講

ORACLE循序漸進講解（第二講），oracle第二講
一、ORACLE常用資料類型（其他類型不太常用）（1）字元類型          1、char 定長，最大2000字元。例如：char(10) ‘小明’ 前四個字元放‘小明’後添加6個空格補全。          2、varchar2(20) 變長，最大4000字元。例如：varchar(10) ‘小明’ oracle分配四個字元，這樣可心節省空間的。          3、clob(character large objiect)字元型大對象最大4G（2）數字類型number範圍-10的38次方到10的38次方，可以表示整數，也可以表示小數。        1、number(5)表示一個五位整數範圍-99999到999992、number(5,2)表示一個小數有5位效數,2位小數，範圍-999.99到999.99（3）日期類型1、data 包含年月日和時分秒。2、timestamp 它與date的區別是不僅可以儲存日期。時間，還能儲存小數秒。（4）圖片類型blob 位元據可以存放圖片/聲音。二、建表（1）建立學生表和班級表用上所有類型

SQL> create table student(  2  xh number(4),  3  xm varchar2(20),  4  sex char(2),  5  birthday date,  6  sal number(7,2)  7  );Table created

SQL> create table class(  2  classid number(2),  3  cname varchar2(20)  4  );Table created

（2）表修改。1、添加欄位。為學生表添加classid

SQL> alter table student add (classid number(2));Table altered

2、修改欄位的長度。修改學生表xm長度為30

SQL> alter table student modify (xm varchar2(30));Table altered

3、修改欄位的類型或名字（不能有資料）。修改學生表xm類型為char(30)

SQL> alter table student modify(xm char(30));Table altered

SQL> alter table student rename column xm to mc;Table altered

4、刪除一個欄位。刪除學生表sal欄位。

SQL> alter table student drop column sal;Table altered

5、修改表名。修改student為stu。

SQL> rename student to stu;Table renamed

6、刪除表。刪除stu表。

SQL> drop table stu;Table dropped

三、表基本查詢（使用scott使用者）。1、查看錶結構。

SQL> desc emp;Name     Type         Nullable Default Comments -------- ------------ -------- ------- -------- EMPNO    NUMBER(4)                              ENAME    VARCHAR2(10) Y                         JOB      VARCHAR2(9)  Y                         MGR      NUMBER(4)    Y                         HIREDATE DATE         Y                         SAL      NUMBER(7,2)  Y                         COMM     NUMBER(7,2)  Y                         DEPTNO   NUMBER(2)    Y    

2、查詢每個僱員的年工資。

SQL> select ename "姓名",sal*12 "年工資" from emp;姓名              年工資---------- ----------SMITH           21600ALLEN           19200WARD            15000JONES           35700MARTIN          15000BLAKE           34200CLARK           29400SCOTT           36000KING            60000TURNER          18000ADAMS           13200JAMES           11400FORD            36000MILLER          1560014 rows selected

註：用nvl將null轉化為0

SQL> select ename "姓名",sal*12+nvl(comm,0)*12 "年工資" from emp;姓名              年工資---------- ----------SMITH           21600ALLEN           22800WARD            21000JONES           35700MARTIN          31800BLAKE           34200CLARK           29400SCOTT           36000KING            60000TURNER          18000ADAMS           13200JAMES           11400FORD            36000MILLER       16014.7214 rows selected

3、如何顯示第三個字元為大寫O的所有員工的姓名和工資。

SQL> select ename,sal from emp where ename like '__O%';ENAME            SAL---------- ---------SCOTT        3000.00

註：%表示任意0到多個字元，_表示任意單個字元。
4、顯示每個部門的平均工資和最高工資。

SQL> select avg(sal) "平均工資",max(sal) "最高工資",deptno "部門編號" from emp group by deptno;      平均工資       最高工資     部門編號---------- ---------- --------1566.66666       2850       30      2375       3000       202916.66666       5000       10

5、顯示每個部門的每種崗位的平均工資和最低工資。

SQL> select avg(sal) "平均工資",min(sal) "最低工資",deptno,job from emp group by deptno,job order by deptno;      平均工資       最低工資 DEPTNO JOB---------- ---------- ------ ---------      1300       1300     10 CLERK      2450       2450     10 MANAGER      5000       5000     10 PRESIDENT      3000       3000     20 ANALYST      1450       1100     20 CLERK      2975       2975     20 MANAGER       950        950     30 CLERK      2850       2850     30 MANAGER      1400       1250     30 SALESMAN9 rows selected

6、顯示平均工資低於2000的部門號和他的平均工資。

SQL> select deptno,avg(sal) "平均工資" from emp group by deptno having avg(sal)<2000;DEPTNO       平均工資------ ----------    30 1566.66666

7、顯示和SMITH同一部門的所有員工。

SQL> select ename from emp where deptno=(select deptno from emp where ename='SMITH');ENAME----------SMITHJONESSCOTTADAMSFORD

8、查詢和部門10的工作相同的僱員的名字、崗位、工資、部門號

SQL> select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);ENAME      JOB             SAL DEPTNO---------- --------- --------- ------CLARK      MANAGER     2450.00     10BLAKE      MANAGER     2850.00     30JONES      MANAGER     2975.00     20KING       PRESIDENT   5000.00     10MILLER     CLERK       1300.00     10JAMES      CLERK        950.00     30ADAMS      CLERK       1100.00     20SMITH      CLERK       1800.00     208 rows selected

9、顯示工資比部門30的所有員工的工資高的員工姓名、工資和部門號。

SQL> select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30);ENAME            SAL DEPTNO---------- --------- ------JONES        2975.00     20SCOTT        3000.00     20KING         5000.00     10FORD         3000.00     20

或者

SQL> select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);ENAME            SAL DEPTNO---------- --------- ------JONES        2975.00     20SCOTT        3000.00     20KING         5000.00     10FORD         3000.00     20

10、查詢與SMITH部門和崗位完全相同的所有僱員

SQL> select ename from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');ENAME----------SMITHADAMS

11、查詢高於自己部門的平均工資的員工資訊。

SQL> select ename,sal,svg "部門平均工資" from emp,(select deptno,avg(sal) svg from emp group by deptno) t where sal>svg and emp.deptno=t.deptno;ENAME            SAL       部門平均工資---------- --------- ------------ALLEN        1600.00 1566.6666666JONES        2975.00         2375BLAKE        2850.00 1566.6666666SCOTT        3000.00         2375KING         5000.00 2916.6666666FORD         3000.00         23756 rows selected

12、修改員工scott的崗位、工資、補助與smith員工一樣。

SQL> update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='SMITH') where ename='SCOTT';1 row updated

13、oracle分頁。

SQL> select * from emp where empno in(select empno from (select empno,rownum rn from (select empno from emp order by sal desc) where rownum<=10) t where t.rn>=6);EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO----- ---------- --------- ----- ----------- --------- --------- ------ 7782 CLARK      MANAGER    7839 1981-06-09    2450.00               10 7844 TURNER     SALESMAN   7698 1981-09-08    1500.00      0.00     30 7934 MILLER     CLERK      7782 1982-01-23    1300.00     34.56     10 7499 ALLEN      SALESMAN   7698 1981-02-20    1600.00    300.00     30 7369 SMITH      CLERK      7902 1980-12-17    1800.00               20

14、用查詢結果建立新表

SQL> create table emp1(id,name) as select empno,ename from emp;Table createdExecuted in 0.532 secondsSQL> select * from emp1;   ID NAME----- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER14 rows selected

15、合并查詢。（union、union all、intersect、minus）（1）union 取兩個結果集的並集，會自動去掉結果集中重複行。

SQL> select empno id,ename name from emp  2  union  3  select id,name from emp1;   ID NAME----- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER14 rows selected

（2）union all 取兩個結果集的並集，不會去掉結果集中重複行。

SQL>  select empno id,ename name from emp  2   union all  3   select id,name from emp1  4  ;   ID NAME----- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE   ID NAME----- ---------- 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER28 rows selected

（3）intersect 取兩個結果的交集。

SQL>  select ename,sal from emp where sal>2500  2    intersect  3    select ename,sal from emp where job='MANAGER';ENAME            SAL---------- ---------BLAKE        2850.00JONES        2975.00

（4）minus 取兩個結果的相減。

SQL> select ename,sal from emp where sal>2500  2    minus  3    select ename,sal from emp where job='MANAGER'  4  ;ENAME            SAL---------- ---------FORD         3000.00KING         5000.00SCOTT        3000.00