oracle基础知识

目录

 

一，    表结构（DDL）

DDL数据库定义(create、alter、drop、rename)

1,创建一张表

CREATE TABLE  table_name (

        id NUMBER(4),

        name VARCHAR2(20)  NOT NULL,

        gender CHAR(1) DEFAULT 'M',

        birth DATE,

       salary NUMBER(6,2),

       jobVARCHAR2(30),

       deptno NUMBER(2)

       );

2，查看表结构

DESC table_name

3，删除表结构

DROP TABLE table_name

4，修改表名

RENAME old_name TO new_name

5，为表添加表字段

ALTER TABLE table_name

ADD (hiredate DATE DEFAULT sysdate);

6，删除表中字段（一列）

ALTER TABLE emp_fanchuanqi

DROP (hiredate);

7，修改表字段

ALTER TABLE table_name

MODIFY (

   job VARCHAR2(40)DEFAULT 'CLERK'

);

select * from user_tables  where table_name like 'WF%' --查询当前用户所拥有的表

 

 

二，    表数据（DML）

DML数据库操纵(insert、delete、select、update、truncate)

1，插入数据

INSERT INTO

  emp_fanchuanqi(id,name,salary)

VALUES(1,'boss',1500)

2，查询表数据

SELECT   *    FROM   emp_fanchuanqi  / dual(虚表) ;

3，修改表数据

UPDATE emp_fanchuanqi

SET job='MANAGER'

WHERE salary=8500;

4，删除表数据

DELETE FROM emp_fanchuanqi

WHERE name='tom'

 

三，    函数

1，TO_DATE()函数

SELECT

  TO_DATE(

    '2008-08-1222:45:33',    'yyyy-mm-ddhh24:mi:ss'  )

FROM   DUAL;

 

SELECT

  TO_CHAR(

    sysdate,  'yyyy"年"mm"月"dd"日" hh:mi:ss'   )

FROM   DUAL;

2，“||”的用法

"||"在数据库中是连接字符串，相当于java中的"+"，注意和java中的"||"区分。

例如:

 java中 "hello"+"world" ==> "helloworld"

 DB中   ‘hello’||'world' ==> 'helloworld'

oracle中 CONCAT('hello','world') ==>'helloworld'

'think'||'in'||'java' 等价于CONCAT(CONCAT('think','in'),'java')

3，LENGTH函数

SELECT ename,LENGTH(ename)

FROM table_name

4,大写，小写，首字母大写

SELECT

  UPPER(ename),

  LOWER(ename),

  INITCAP(ename)

FROM   table_name;

5,去空白，左空白，右空白

SELECT  TRIM('e'from'eeeeeliteeeeee')FROM  DUAL;

SELECT  LTRIM('eeeeliteeeee','e')FROM DUAL;

SELECT  RTRIM('eeeeliteeeee','e') FROM DUAL;

6，补字符

SELECT  LPAD('aaa',5,'$') FROM dual          ------   $$aaa

SELECT  RPAD('aaa',5,'$') FROM DUAL        ------   aaa$$

7，NVL函数

NVL函数的作用:将null替换为给定值

SELECT ename,sal,NVL(comm,0) FROM emp_fanchuanqi

SELECT ename,NVL2(comm,'有奖金','没奖金') FROMemp_fanchuanqi

8，AND与OR

AND优先级是高于OR的。

SELECT  ename, sal,job  FROM  emp_fanchuanqi

WHERE

 sal > 1250   AND   job = 'CLERK'  OR   job = 'SALESMAN'

9，ANY，ALL与IN

>ANY 大于最小的

<ANY 小于最大的

>ALL 大于最大的

<ALL 小于最小的

区别:in是与给定的数据进行等值或不等值比较

      any和all是与给定数据进行范围比较

SELECT ename, sal, job  FROM  emp_fanchuanqi

WHERE

 job IN ('MANAGER','SALESMAN','CLERK')

SELECT empno, ename, job, sal, deptno  FROM emp

WHERE sal > ALL(2500,4000,4500);

10，聚合函数

用在where后必须有group by

例：查看每个部门中每种职位的工资情况

SELECT

  MAX(sal), MIN(sal),AVG(sal),  SUM(sal) FROM  emp

GROUP BY deptno,job

例：查看平均工资超过1800的部门，以及平均工资

SELECT  deptno,AVG(sal) FROM   emp

GROUP BY  deptno  HAVING AVG(sal)>1800

11，左右连接

--内连接和where相同  
inner join  
--左向外连接，返回左边表所有符合条件的  
left join  
--右向外连接，返回右边表所有符合条件的  
right join  
--完整外部连接，左向外连接和右向外连接的合集  
full join  

例：select* from a left join b on a.id=b.id;  

12,like的用法

like匹配符有：
%：0个或多个任意字符
_：一个任意字符
like '%a' :以a结尾的字符串
like 'a%' :以a开始的字符串
like '%a%' :包含a开始的字符串
13，子查询

例：简单子查询（where后面的子查询）

SELECT   ename,sal  FROM  emp

WHERE

  sal < (SELECT  AVG(sal) FROM  emp)

例：查看 与SALESMAN相同部门的其他员工都有谁

SELECT  ename,job,deptno  FROM emp

WHERE

 deptno IN (SELECT   deptno   FROM  emp  WHERE   job='SALESMAN')

AND job <> 'SALESMAN'

例：比30号部门最低工资高的最低工资的部门

SELECT deptno,MIN(sal) FROM  emp

GROUP BY deptno

HAVING

 MIN(sal) >(SELECT  MIN(sal)   FROM  emp   WHERE  deptno=30)

 

例：from后面的子查询

SELECT  ename,sal,deptno  FROM

  emp e,  (SELECT deptno , AVG(sal) avgsal  FROM emp  GROUP BY deptno)  a

WHERE  e.deptno = a.deptno  AND e.sal > a.avgsal

14，分页

分页的算法：page页数   pagesize一页的条数

(page-1)*pagesize+1 start  

page*pagesize        end   

分页的三步:

1:排序

2:编号

3:取范围

SELECT *

FROM (

         SELECTROWNUM rw,e.*

         FROM (

                SELECT empno,ename,sal

                FROM emp

                ORDER BY empno

                 ) e

        )

WHERE rw BETWEEN 1 AND 3

15，常用总结

Group by 必须放在order by和limit之前，distinct(去重)必须放在开头，

union和union all

SQL中UNION和Union All指令的目的是将两个SQL语句的结果合并起来。union和Union All只是将两个结果联结起来一起显示，并不是联结两个表。区别是：union是集合中的并集运算，即把所有的查询结果并集处理，而union all表示无论多条结果中有无重复都将各自的结果相加，而不删除其中重复部分。

 

 

 

 

四，    常用sql

1，将a表所有数据复制到b表中

insert into A (a,b,c,d,e) select a,b,c,d,e from B

2，一段时间内查询两张表的总金额和总记录

思路：分别查出两张表的金额和记录数，然后用union all连在一起作为一个表

          selectoperation_time, sum(count)count,sum(amount)cost

         from (select to_char(operation_time,'yyyy/mm/dd') asoperation_time,

                      count(orderno)count,

                      sum(amount)amount

                 fromweixin_wap_order

                groupby to_char(operation_time,'yyyy/mm/dd')

               unionall

               selectto_char(operation_time, 'yyyy/mm/dd') as operation_time,

                      count(orderno)count,

                      sum(amount)amount

                 fromweixin_wap_order_history

                groupby to_char(operation_time,'yyyy/mm/dd')            

               )

        whereoperation_time between '2015/04/10'and'2015/09/24'

        group by operation_time;