Oracle之数据操作__简单查询和多表查询

#测试数据：

-- 使用超级管理员登录
CONN sys/change_on_install AS SYSDBA;


-- 创建c##用户 
CREATE USER c##scott IDENTIFIED BY tiger;


--为用户授权
GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO c##scott CONTAINER=ALL;




--设置用户使用的表空间

ALTER USER c##scott DEFAULT TABLESPACE USERS;

ALTER USER c##scott TEMPORARY TABLESPACE TEMP;


sid:mldn

--使用c##scott用户登录

CONNECT c##scott/tiger


-- 删除数据表
DROP TABLE grade;
DROP TABLE sporter;
DROP TABLE item;
PURGE RECYCLEBIN;  

--创建表

 CREATE TABLE "C##SCOTT"."BONUS" 
   (    "ENAME" VARCHAR2(14 BYTE), 
    "JOB" VARCHAR2(14 BYTE), 
    "SAL" NUMBER, 
    "COMM" NUMBER
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
REM INSERTING into C##SCOTT.BONUS
SET DEFINE OFF;


CREATE TABLE "C##SCOTT"."DEPT" 
   (    "DID" NUMBER(2,0), 
    "DNAME" VARCHAR2(14 BYTE), 
    "LOC" VARCHAR2(13 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into C##SCOTT.DEPT
SET DEFINE OFF;

CREATE TABLE "C##SCOTT"."EMP" 
   (    "EMPNO" NUMBER(4,0), 
    "ENAME" VARCHAR2(10 BYTE), 
    "JOB" VARCHAR2(9 BYTE), 
    "MGR" NUMBER(4,0), 
    "HIREDATE" DATE, 
    "SAL" NUMBER(7,0), 
    "COMM" NUMBER(7,0), 
    "DID" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into C##SCOTT.EMP
SET DEFINE OFF;


CREATE TABLE "C##SCOTT"."SALGRADE" 
   (    "GRADE" NUMBER, 
    "LOSAL" NUMBER, 
    "HISAL" NUMBER
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into C##SCOTT.SALGRADE
SET DEFINE OFF;



--测试数据
Insert into C##SCOTT.DEPT (DID,DNAME,LOC) values (20,'后勤部','上海');
Insert into C##SCOTT.DEPT (DID,DNAME,LOC) values (30,'公关部','深圳');
Insert into C##SCOTT.DEPT (DID,DNAME,LOC) values (40,'技术部','杭州');
Insert into C##SCOTT.DEPT (DID,DNAME,LOC) values (10,'市场部','北京');
Insert into C##SCOTT.DEPT (DID,DNAME,LOC) values (50,'保安部','深山');
Insert into C##SCOTT.DEPT (DID,DNAME,LOC) values (60,'会计部','深山');



Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7369,'大宝','CLERK',7759,to_date('17-12月-80','DD-MON-RR'),800,null,20);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7521,'钢蛋','SALESMAN',7759,to_date('20-2月 -81','DD-MON-RR'),1200,300,30);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7389,'如花','SALESMAN',7692,to_date('22-2月 -81','DD-MON-RR'),1500,500,30);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7470,'张三','MANAGER',7759,to_date('26-9月 -81','DD-MON-RR'),1111,null,20);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7520,'李四','SALESMAN',7759,to_date('11-8月 -81','DD-MON-RR'),1666,1499,30);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7642,'王二','MANAGER',7692,to_date('10-2月 -81','DD-MON-RR'),6000,1499,50);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7692,'大boss','MANAGER',null,to_date('19-12月-81','DD-MON-RR'),3333,1000,40);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7710,'赛貂蝉','PRESIDENT',7692,to_date('29-5月 -81','DD-MON-RR'),1600,1499,10);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7749,'汤姆','LAOSHI',7692,to_date('01-5月 -81','DD-MON-RR'),-59,200,10);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7759,'二宝','CLERK',7692,to_date('11-11月-81','DD-MON-RR'),666,300,50);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (8888,'风清扬','CLERK',7692,to_date('10-10月-00','DD-MON-RR'),666,100,null);
Insert into C##SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DID) values (7750,'杰瑞','LAOSHI',7692,to_date('29-10月-81','DD-MON-RR'),100,300,10);



Insert into C##SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (1,700,1200);
Insert into C##SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (2,1201,1400);
Insert into C##SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (3,1401,2000);
Insert into C##SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (4,2001,3000);
Insert into C##SCOTT.SALGRADE (GRADE,LOSAL,HISAL) values (5,3001,9999);


--事务提交
COMMIT;

表和数据

一简单查询

A. 限定查询

1. 使用 BETWEEN ... AND，查询1000~2000（包含1000和2000）

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 2000;

2. null不能使用 = 判断

#不会得到结果
SELECT * FROM emp WHERE comm=null AND empno=7369;  

#正确操作
SELECT * FROM emp WHERE comm is null AND empno=7369;

3. in和not in

SELECT * FROM emp WHERE empno IN(7369,7389);

4. like (百分号%：匹配任意类型和长度的字符。下划线_：匹配单个字符)

SELECT * FROM dept WHERE loc like '深%';

SELECT * FROM dept WHERE loc like '_州';

B. 排序显示：order by

#  ASC：升序，默认
# DESC: 降序（从大到小）

#  在所有的子句之中，一定要记住，ORDER BY 子句是放在查询语句的最后一行，是最后一个执行的。它的执行顺序：FROM,WHERE,SELECT,ORDER BY,既然 ORDER BY 在SELECT 
之后执行，那么就表示 ORDER BY 子句可以使用 SELECT 子句之中设置的别名。

SELECT * FROM emp ORDER BY sal;

SELECT * FROM emp WHERE job='CLERK' ORDER BY sal DESC;

二多表查询

A，多表查询的基本语法

　emp表和dept表：

#方式一：
SELECT * FROM emp,dept WHERE emp.did=dept.did;

#方式二：
SELECT e.empno,e.ename,e.job,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.did=d.did;

emp表和salgrade表：

SELECT e.empno,e.ename,e.sal,
    decode(s.grade, 1,'E等工资', 2,'D等', 3,'C等', 4,'B等', 5,'A等') grade
FROM emp e,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

B. 表的连接操作

添加一条数据（雇员的部门编号是 null）：

INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,did)
VALUES (8888,'风清扬','CLERK',7369,SYSDATE,800,100,NULL);

一：没有部门的雇员没有显示

二：有一个 60 的部门没有显示

　　使用内连接只有满足连接条件的数据才会全部显示。如果想要emp或dept表中数据显示完整，那么可使用外连接：左外链接，右外链接

将雇员8888,显示出来：

SELECT * FROM emp e,dept d WHERE e.did=d.did(+);

显示60的部门：

select * from emp e,dept d WHERE e.did(+)=d.did;  #右连接

C. 自身关联

　　emp表中，mgr字段，表示的是雇员领导

SELECT e.empno eno,e.ename ename,m.empno mno,m.ename mname
FROM emp e,emp m
WHERE e.mgr=m.empno(+);

SELECT e.empno,e.ename,e.hiredate,e.sal,(e.sal+NVL(E.COMM,0))*12 income,
       m.ename mname,d.loc,s.grade,
       DECODE(s.grade, 1,'E等',2,'D等',3,'C等',4,'B等',5,'A等') 工资等级
FROM emp e,emp m,dept d,salgrade s
WHERE TO_CHAR(e.hiredate,'yyyy')='1981'
      AND e.sal BETWEEN 1000 AND 2000
      AND e.mgr=m.empno(+)
      AND e.did=d.did
      AND e.sal BETWEEN s.losal AND s.hisal
      ORDER BY income DESC,e.job;

D. SQL：1999语法

#左连接，右连接，全外连接（只有SQL：1999语法）
SELECT * from emp e LEFT JOIN dept d on e.did=d.did;
SELECT * from emp e RIGHT JOIN dept d on e.did=d.did;
SELECT * from emp e Full join dept d on e.did=d.did;

E. 数据的集合运算

1. 并集操作：UNION，UNION ALL

#第一个查询已经包含了第二个查询的内容，所以重复数据就不显示。

SELECT * FROM dept
    UNION
SELECT * from dept where did=10;


#使用 UNION ALL,显示全部

SELECT * FROM dept
    UNION ALL
SELECT * from dept where did=10;

## 以后进行查询操作编写过程中，尽量使用UNION 或 UNION ALL 来代替 OR

示例：

#----- 查询所有销售人员和办事员的信息
SELECT * FROM emp WHERE job='SALESMAN' OR job='CLERK';
SELECT * FROM emp WHERE job IN ('SALESMAN','CLERK');


#上面两种虽可以达到，结果。但效率低
SELECT * FROM emp WHERE job='SALESMAN'
    UNION
SELECT * FROM emp where job='CLERK';
（执行了两个单表查询，效率高）

2. 差集：MINUS

SELECT * FROM dept 
    MINUS
SELECT * FROM dept WHERE did=10;

3. 交集：INTERSECT

SELECT * FROM dept 
    INTERSECT
SELECT * FROM dept WHERE did=10;