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