程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> 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到99999 2、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           21600
ALLEN           19200
WARD            15000
JONES           35700
MARTIN          15000
BLAKE           34200
CLARK           29400
SCOTT           36000
KING            60000
TURNER          18000
ADAMS           13200
JAMES           11400
FORD            36000
MILLER          15600
14 rows selected
注:用nvl將null轉化為0
SQL> select ename "姓名",sal*12+nvl(comm,0)*12 "年工資" from emp;
姓名              年工資
---------- ----------
SMITH           21600
ALLEN           22800
WARD            21000
JONES           35700
MARTIN          31800
BLAKE           34200
CLARK           29400
SCOTT           36000
KING            60000
TURNER          18000
ADAMS           13200
JAMES           11400
FORD            36000
MILLER       16014.72
14 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       20
2916.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 SALESMAN

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
----------
SMITH
JONES
SCOTT
ADAMS
FORD

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     10
BLAKE      MANAGER     2850.00     30
JONES      MANAGER     2975.00     20
KING       PRESIDENT   5000.00     10
MILLER     CLERK       1300.00     10
JAMES      CLERK        950.00     30
ADAMS      CLERK       1100.00     20
SMITH      CLERK       1800.00     20

9、顯示工資比部門30的所有員工的工資高的員工姓名、工資和部門號。
ENAME            SAL DEPTNO
---------- --------- ------
JONES        2975.00     20
SCOTT        3000.00     20
KING         5000.00     10
FORD         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     20
SCOTT        3000.00     20
KING         5000.00     10
FORD         3000.00     20

10、查詢與SMITH部門和崗位完全相同的所有雇員
SQL> select ename from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH');
ENAME
----------
SMITH
ADAMS

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.6666666
JONES        2975.00         2375
BLAKE        2850.00 1566.6666666
SCOTT        3000.00         2375
KING         5000.00 2916.6666666
FORD         3000.00         2375
6 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 created
Executed in 0.532 seconds

SQL> 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 MILLER
14 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 MILLER
14 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 MILLER
28 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.00
JONES        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.00
KING         5000.00
SCOTT        3000.00


  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved