oracle之sql查詢

oracle表管理

表名和列的命名規則：

必須以字母開頭

長度不能超過30字符

不能使用oracle的保留字

只能使用如下字符：A-Z，a-z，0-9，$,#等

字符型：

char：定長 最大2000字符

例子：char(10) '小韓' 前四個字符放'小韓'，后面添6個空格補全

比如×××定長，查詢速度很快

varchar2(20) 變長 最大4000字符，oracle推薦使用的類型

例子：varchar2(10) '小韓' oracle分配四個字符，這樣可以省略空間

clob(character large object) 字符型大對象，最大4G

數字類型

number 范圍10的-38次方，10的38次方

可以表示整數，也可以表示小數

number(5,2),表示有小數部位，且最大允許5位有效數，2位小數,number(5)表示最大允許5位整數

日期類型

date 包含年月日和時分秒

timestamp精度高

圖片類型

blob   二進制數據 可以存放圖片，聲音視頻 4G（如果需要安全很高就存放數據庫中）

建表語句：

SQL>create table stu(xh number(4),xm varchar2(20),sex char(3),birthday date,sal number(7,2));

或者：

SQL> create table stu(

  2  xh number(4),

  3  xm varchar2(20),

  4  sex char(3),

  5  sal number(7,2)

  6  );

Table created.

SQL> desc stu;       查看表結構

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 XH                                                 NUMBER(4)

 XM                                                 VARCHAR2(20)

 SEX                                                CHAR(3)

 BIRTHDAY                                           DATE

 SAL                                                NUMBER(7,2)

刪表：

SQL> drop table student;     由于先前已經有張表為student

Table dropped.

向表stu中添加一個新字段

SQL> alter table stu add (classid number(2));                

Table altered.

修改表stu中的某一個字段

SQL> alter table stu modify (xm varchar2(30));

Table altered.

刪除表stu中某一個字段

SQL> alter table stu drop column sal;

Table altered.

修改表的名字

SQL> rename stu to student;

Table renamed.

向表student中插入數據，先查看表結構

SQL> desc student;

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 XH                                                 NUMBER(4)

 XM                                                 VARCHAR2(30)

 SEX                                                CHAR(3)

 BIRTHDAY                                           DATE

 CLASSID                                            NUMBER(2)

SQL> insert into student values (1,'小黃','F','9-6月-16',1);

insert into student values (1,'小黃','F','9-6月-16',1)

                                                       *

ERROR at line 1:

ORA-01843: not a valid month

插入數據一直出錯，月份一直有問題，百度了一下，解決方法如下

先查看數據庫支持的是什么語言的

SQL> select * from nls_session_parameters where parameter='NLS_DATE_LANGUAGE'

  2  ;

PARAMETER

--------------------------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

NLS_DATE_LANGUAGE

AMERICAN       由這里可以看出是AMERICAN 

SQL> show parameter nls_language;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

nls_language                         string      AMERICAN

由這條語句查看也是AMERICAN，所以猜想不能使用漢字模式，修改如下

SQL> insert into student values (1,'小黃','F','9-July-16',1);

1 row created.

表示插入數據成功，查看數據

SQL> select * from student;

        XH XM                             SEX BIRTHDAY     CLASSID

---------- ------------------------------ --- --------- ----------

         1 ??????                         F   09-JUL-16          1

可以看出插入數據最好匹配數據庫原始支持的語言

修改默認日期格式：

alter session set nls_date_format = 'yyyy-mm-dd';--->修改后為2016-06-09

SQL> alter session set nls_date_format='yyyy-mm-dd';

Session altered.

SQL> insert into student values (2,'xiaobai','F','2016-09-06',2);

1 row created.

SQL> select * from student;

        XH XM                             SEX BIRTHDAY      CLASSID

---------- ------------------------------ --- ---------- ----------

         1 ??????                         F   2016-07-09          1

         2 xiaobai                        F   2016-09-06          2

插入數值也可以為空

SQL> insert into student values (3,'xiaohua','F',null,3);

1 row created.

SQL> select * from student;

        XH XM                             SEX BIRTHDAY      CLASSID

---------- ------------------------------ --- ---------- ----------

         1 ??????                         F   2016-07-09          1

         2 xiaobai                        F   2016-09-06          2

         3 xiaohua                        F                       3

查出生日為空的同學：

錯誤操作如下：

SQL> select * from student where  birthday='';

no rows selected

正確操作如下：

SQL> select * from student where  birthday is null;

        XH XM                             SEX BIRTHDAY      CLASSID

---------- ------------------------------ --- ---------- ----------

         3 xiaohua                        F                       3

SQL> select * from student where birthday is not null;

        XH XM                             SEX BIRTHDAY      CLASSID

---------- ------------------------------ --- ---------- ----------

         1 ??????                         F   2016-07-09          1

         2 xiaobai                        F   2016-09-06          2

update修改某個字段：

SQL> update student set classid=2 where xm='xiaohua';

1 row updated.

SQL> select * from student;

        XH XM                             SEX BIRTHDAY      CLASSID

---------- ------------------------------ --- ---------- ----------

         1 ??????                         F   2016-07-09          1

         2 xiaobai                        F   2016-09-06          2

         3 xiaohua                        F                       2

模擬刪除用戶能否恢復

SQL> savepoint a;        預先定義一個保存點

Savepoint created.

SQL> delete from student;  模擬刪除表

3 rows deleted.

SQL> select * from student;  查看表數據，發現沒有數據

no rows selected

SQL> rollback to a;      rollback回滾到保存點

SQL> select * from student;   查看數據，發現數據恢復，回滾成功

        XH XM                             SEX BIRTHDAY      CLASSID

---------- ------------------------------ --- ---------- ----------

         1 ??????                         F   2016-07-09          1

         2 xiaobai                        F   2016-09-06          2

         3 xiaohua                        F                       2

Rollback complete.

于是結論如下：

delete from table,刪除表所有記錄，但表結構還在，寫日志，可以恢復

drop table student:刪除表的結構和數據

truncate table student:刪除表中的所有記錄，但表結構還在，但不寫日志，無法找回刪除的記錄，速度很快

表空間：就是存放某些表的那些空間（oracle事先就有很多表空間）

表用來存放數據

scott用戶下面的表

1、emp表

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7369 SMITH      CLERK      7902 1980/12/17     800.00               20

 7499 ALLEN      SALESMAN   7698 1981/2/20     1600.00    300.00     30

 7521 WARD       SALESMAN   7698 1981/2/22     1250.00    500.00     30

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

 7654 MARTIN     SALESMAN   7698 1981/9/28     1250.00   1400.00     30

 7698 BLAKE      MANAGER    7839 1981/5/1      2850.00               30

 7782 CLARK      MANAGER    7839 1981/6/9      2450.00               10

 7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 7844 TURNER     SALESMAN   7698 1981/9/8      1500.00      0.00     30

 7876 ADAMS      CLERK      7788 1987/5/23     1100.00               20

 7900 JAMES      CLERK      7698 1981/12/3      950.00               30

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

14 rows selected

EMPNO：雇員number

ENAME：雇員名字

JOB：工作職位（clerk：員工）

MGR：雇員上級id

HIREDATE：入職時間

SAL:薪水工資

COMM：獎金

DEPTNO：所在的部門，部門編號

2、dept部門表

SQL> select * from dept;

DEPTNO DNAME          LOC

------ -------------- -------------

    10 ACCOUNTING     NEW YORK

    20 RESEARCH       DALLAS

    30 SALES          CHICAGO

    40 OPERATIONS     BOSTON

DEPTNO：所在的部門，部門編號

ACCOUNTING：財務部

RESEARCH：研發

SALES：銷售

顯示一張表有多少行數據？

SQL> select count(*) from emp;

  COUNT(*)

----------

        14

關鍵字

1、distinct過濾掉重復的行

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

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

Oracle一般不區分大小寫，但如下情況卻區分

SQL> select * from emp where ename='smith';

no rows selected

SQL> select * from emp where ename='SMITH';

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM

---------- ---------- --------- ---------- ---------- ---------- ----------

    DEPTNO

----------

      7369 SMITH      CLERK           7902 1980-12-17        800

當匹配表中某內容時，需要區分大小寫

2、使用算是表達式

SQL> select ename,sal*12 from emp;

ENAME          SAL*12

---------- ----------

SMITH            9600

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

當然也可以將列去個另外的別名

SQL> select ename,sal*12 nianxin from emp;

ENAME         NIANXIN     此時這里就使用了別名替換了sal*12

---------- ----------

SMITH            9600

ALLEN           19200

如果字段中有一個值為null，那么計算出來的值也是null，該如何準確查詢呢？

SQL> select ename,sal*13+comm*13,comm from emp;

ENAME      SAL*13+COMM*13      COMM     由于字段comm有些員工并沒有，所以有些為空

---------- -------------- ---------

SMITH                     

ALLEN               24700    300.00

WARD                22750    500.00

JONES                     

MARTIN              34450   1400.00

BLAKE                     

CLARK                     

SCOTT                     

KING                      

TURNER              19500      0.00

ADAMS                     

JAMES                     

FORD                      

MILLER                    

如何處理null值，使用nvl函數

SQL> select ename,sal*13+nvl(comm,0)*13 as 年工資,comm from emp;   ----》當comm為空值時，用后面的0替換，當comm有值時，用自己本身的值計算

ENAME      SAL*13+NVL(COMM,0)*13      COMM

---------- --------------------- ---------

SMITH                      10400 

ALLEN                      24700    300.00

WARD                       22750    500.00

JONES                      38675 

MARTIN                     34450   1400.00

BLAKE                      37050 

CLARK                      31850 

SCOTT                      39000 

KING                       65000 

TURNER                     19500      0.00

ADAMS                      14300 

JAMES                      12350 

FORD                       39000 

MILLER                     16900