我爱编程java学习资料

oracle学习笔记

2017-12-13  本文已影响0人  一格命MrLixinyu

oracle基本运算符

select  [distinct]{*|column|expression [alisa],...}
from table

select 子句表示所检索的数据列
from子句表示检索的数据来自哪个表
不区分大小写，建议小写，建议分多行书写，可以增强代码的可读性；通常以子句为单位进行分行；
关键字不可缩写、分开以及跨行书写；
选择所有列

select *
from emp;

选择指定的列

select deptno,sal
from emp;

算数运算符主要包含：
（+ - * /）
算数运算符的运用

select enanme,sal,sal*12
from emp

算数优先级

select ename||job as "employess"
from emp

原义字符串

select ename ||'is a'|| job
from emp

上面其中的is a就为原义字符串 、
消除重复行

select distinct depton
from emp

“*”代表所有的

where运算语句的运算符

select * 
from emp
where 
sal in(1500,3000) and ename like '%M_' and comm is not null 
ORDER BY sal,deptno desc

分组函数

别名：聚集函数

  1. 查询部门20的员工，每个月的工资总和及平均工资。
  2. 查询工作在CHICAGO的员工人数，最高工资及最低工资。
  3. 查询员工表中一共有几种岗位类型。
SELECT
    SUM (sal),
    AVG (sal)
FROM
    EMP
WHERE
    DEPTNO = 20

SELECT
    COUNT (*),
    MAX (sal),
    MIN (sal)
FROM
    emp A
JOIN dept b ON A .deptno = b.deptno
WHERE
    b.loc = 'CHICAGO'

SELECT
    COUNT (DISTINCT(JOB))
FROM
    EMP

多表连接

等值连接

select *
from emp,dept
where emp.deptno =dept.deptno

非等值连接

select a.ename,a.sal,b.GRADE 
from emp a,salgrade b
where a.sal between b.losal and b.hisal

内部连接

外部连接

1）LEFT JOIN或LEFT OUTER JOIN
左向外联接的结果集包括 LEFT OUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。

2）RIGHT JOIN 或 RIGHT OUTER JOIN
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
3）FULL JOIN 或 FULL OUTER JOIN
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

笛卡尔积

select *
from emp,dept

交叉联接

id name
1 张3
2 李四
3 王五

b表

id job parent_id
1 23 1
2 34 2
3 34 3

1） 内连接

select a.*,b.*   
from a inner join b on a.id=b.parent_id

2）左连接

   select a.*,b.*   
  from a left join b on a.id=b.parent_id

3） 右连接

select a.*,b.*   
from a right join b on a.id=b.parent_id

4） 完全连接

  select a.*,b.*   
  from a full join b on a.id=b.parent_id

查出员工的姓名及其编号和他直接上级的姓名及员工编号

select e2.ename,e2.empno.e1.ename,e1.empno  
from emp e1
join emp e2 on e1.empno = e2.mgr

a,b两个表那个是员工那个是经理，需要看连接条件

子查询和高级子查询

子查询

子查询结果中有一条是空值，这条空值会导致查询没有记录返回；这是因为所有条件和空值比较的结果都是空值，因此无论什么时候只要空值有可以能成为子查询结果集合中的一部分，就不能使用not in运算符

select rownum,sal
from (select sal from emp 
        order by  sal   asc)
        where rownum <= 3

分页
按照每页显示五条记录，分别查询第一页，第二页，第三页信息，要求显示员工姓名，入职日期、部门名称。

第一页
SELECT
    A .ename,
    A .hiredate,
    b.dname
FROM
    (
        SELECT
            ROWNUM,
            EMP.*
        FROM
            emp
        WHERE
            ROWNUM < 5
    ) A
JOIN dept b ON A .DEPTNO = b.deptno
第二页
SELECT
    A .ename,
    A .hiredate,
    b.dname
FROM
    (
        SELECT
            ROWNUM rb,
            EMP.*
        FROM
            emp
        WHERE
            ROWNUM <= 10
    ) A
JOIN dept b ON A .DEPTNO = b.deptno
WHERE
    A .rb > 5
    第三页
SELECT
    A .ename,
    A .hiredate,
    b.dname
FROM
    (
        SELECT
            ROWNUM rb,
            EMP.*
        FROM
            emp
        WHERE
            ROWNUM <= 15
    ) A
JOIN dept b ON A .DEPTNO = b.deptno
WHERE
    A .rb > 10

集合运算

是用来把两个或多个查询结果集做并、交、差的集合运算，包含集合运算的查询称为复合查询，
联合 union完全联合union all相交intersect相减minus

select sal,job
from emp
where empno = 7839
union
select sal,job
from emp_jobhistory
where empno = 7839

使用union all会比union的速度快，因为省去了去掉重复记录和排序的时间

相关子查询

列>查询比本部门平均薪水高的员工姓名，薪水

嵌套子查询的写法
select empno,ename,sal
from emp e
join (select deptno,avg(sal) avgsal
from emp
group by deptno) d  on e.deptno = d.deptno
where  e.sal > d.avgsal

相关子查询的写法
select empno,ename,sal,deptno
from emp a
where sal > 
(select avg(sal)
from emp
where deptno = a.deptno)

列>查询所有部门名称和人数

select dname,
(select count(empno)
from emp
where deptno = d.deptno)
from dept d

select ename,job,sal,deptno
from emp e
where exists 
(select  1
from emp
where mgr = e.empno)

列>查询那些人不是经理

select ename,job,sal,deptno
from emp e
where not exists 
(select  1
from emp
where mgr = e.empno)

子查询中select 后边可以是任何值，没有确切的返回记录，因为exists只看存不存在返回记录，
数据库操作与事务控制

dml数据库操作语言

create table 'biaoming' as
select * from emp
where 1=0

向biaoming表中插入职位为MANAGER的记录

insert into biaoming
select *
from emp
where job = 'MANAGER'

插入多行数据
不必书写values子句
INSERT子句和数据类型必须和子查询中列的数量和类型相匹配中列的数量

UPDATE  emp
SET deptno = 20
WHERE   empno = 7782

WHERE子句用来限定修改哪些行。
SET子句用来限定修改哪些列。
修改数据中可以应用子查询、相关子查询

DELETE FROM emp
    WHERE   job = 'CLERK';

使用删除数据时一定要看清条件，避免错误删除而发生无法挽回的可怕事件；
删除数据也可以使用子查询、相关子查询；

事务(Transaction)概念

数据定义语言

CREATE TABLE DOSSIER(创建表的表名) (
    ID NUMBER(4), 
   CNAME VARCHAR2(20 ), 
    BIRTHDAY DATE,               
    STATURE    NUMBER(3), 
    WEIGHT NUMBER(5, 2), 
    COUNTRY_CODE CHAR(2 ) DEFAULT ‘01’);（表里面列的名称） 

ALTER TABLE table  
ADD (columnname datatype[DEFAULT expr]
          [, columnname datatype]...);
上一篇下一篇

猜你喜欢

热点阅读