oracle之sql查询

buser

　　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>>
　　Table>　　修改表stu中的某一个字段

　　SQL>>
　　Table>　　删除表stu中某一个字段

　　SQL>>
　　Table>　　修改表的名字
　　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    >　　---------- ------------------------------ --- --------- ----------
　　1 ??????                         F   09-JUL-16          1
　　可以看出插入数据最好匹配数据库原始支持的语言
　　修改默认日期格式：
　　alter session set nls_date_format = 'yyyy-mm-dd';--->修改后为2016-06-09

　　SQL>>
　　Session>　　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>　　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