oracle之sql查詢

oracle表管理

表名和列的命名規(guī)則：

必須以字母開(kāi)頭

長(zhǎng)度不能超過(guò)30字符

不能使用oracle的保留字

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

字符型：

char：定長(zhǎng) 最大2000字符

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

比如×××定長(zhǎng)，查詢速度很快

varchar2(20) 變長(zhǎng) 最大4000字符，oracle推薦使用的類(lèi)型

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

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

數(shù)字類(lèi)型

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

可以表示整數(shù)，也可以表示小數(shù)

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

日期類(lèi)型

date 包含年月日和時(shí)分秒

timestamp精度高

圖片類(lèi)型

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

建表語(yǔ)句：

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;       查看表結(jié)構(gòu)

 Name                                      Null?    Type

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

 XH                                                 NUMBER(4)

 XM                                                 VARCHAR2(20)

 SEX                                                CHAR(3)

 BIRTHDAY                                           DATE

 SAL                                                NUMBER(7,2)

刪表：

SQL> drop table student;     由于先前已經(jīng)有張表為student

Table dropped.

向表stu中添加一個(gè)新字段

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

Table altered.

修改表stu中的某一個(gè)字段

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

Table altered.

刪除表stu中某一個(gè)字段

SQL> alter table stu drop column sal;

Table altered.

修改表的名字

SQL> rename stu to student;

Table renamed.

向表student中插入數(shù)據(jù)，先查看表結(jié)構(gòu)

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

插入數(shù)據(jù)一直出錯(cuò)，月份一直有問(wèn)題，百度了一下，解決方法如下

先查看數(shù)據(jù)庫(kù)支持的是什么語(yǔ)言的

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

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

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

1 row created.

表示插入數(shù)據(jù)成功，查看數(shù)據(jù)

SQL> select * from student;

        XH XM                             SEX BIRTHDAY     CLASSID

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

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

可以看出插入數(shù)據(jù)最好匹配數(shù)據(jù)庫(kù)原始支持的語(yǔ)言

修改默認(rèn)日期格式：

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

插入數(shù)值也可以為空

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

查出生日為空的同學(xué)：

錯(cuò)誤操作如下：

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修改某個(gè)字段：

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

模擬刪除用戶能否恢復(fù)

SQL> savepoint a;        預(yù)先定義一個(gè)保存點(diǎn)

Savepoint created.

SQL> delete from student;  模擬刪除表

3 rows deleted.

SQL> select * from student;  查看表數(shù)據(jù)，發(fā)現(xiàn)沒(méi)有數(shù)據(jù)

no rows selected

SQL> rollback to a;      rollback回滾到保存點(diǎn)

SQL> select * from student;   查看數(shù)據(jù)，發(fā)現(xiàn)數(shù)據(jù)恢復(fù)，回滾成功

        XH XM                             SEX BIRTHDAY      CLASSID

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

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

         2 xiaobai                        F   2016-09-06          2

         3 xiaohua                        F                       2

Rollback complete.

于是結(jié)論如下：

delete from table,刪除表所有記錄，但表結(jié)構(gòu)還在，寫(xiě)日志，可以恢復(fù)

drop table student:刪除表的結(jié)構(gòu)和數(shù)據(jù)

truncate table student:刪除表中的所有記錄，但表結(jié)構(gòu)還在，但不寫(xiě)日志，無(wú)法找回刪除的記錄，速度很快

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

表用來(lái)存放數(shù)據(jù)

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：?jiǎn)T工）

MGR：雇員上級(jí)id

HIREDATE：入職時(shí)間

SAL:薪水工資

COMM：獎(jiǎng)金

DEPTNO：所在的部門(mén)，部門(mén)編號(hào)

2、dept部門(mén)表

SQL> select * from dept;

DEPTNO DNAME          LOC

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

    10 ACCOUNTING     NEW YORK

    20 RESEARCH       DALLAS

    30 SALES          CHICAGO

    40 OPERATIONS     BOSTON

DEPTNO：所在的部門(mén)，部門(mén)編號(hào)

ACCOUNTING：財(cái)務(wù)部

RESEARCH：研發(fā)

SALES：銷(xiāo)售

顯示一張表有多少行數(shù)據(jù)？

SQL> select count(*) from emp;

  COUNT(*)

----------

        14

關(guān)鍵字

1、distinct過(guò)濾掉重復(fù)的行

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一般不區(qū)分大小寫(xiě)，但如下情況卻區(qū)分

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

當(dāng)匹配表中某內(nèi)容時(shí)，需要區(qū)分大小寫(xiě)

2、使用算是表達(dá)式

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

當(dāng)然也可以將列去個(gè)另外的別名

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

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

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

SMITH            9600

ALLEN           19200

如果字段中有一個(gè)值為null，那么計(jì)算出來(lái)的值也是null，該如何準(zhǔn)確查詢呢？

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

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

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

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函數(shù)

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

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