多表连接基本查询
1.使用一张以上的表做查询就是多表查询
语法:SELECT {DISTINCT} *|列名… FROM 表名 别名,表名1 别名
{WHERE 限制条件 ORDER BY 排序字段 ASC|DESC…}

范例:查询员工表和部门表

SELECT * FROM EMP,DEPT; //笛卡尔积 如果EMP表有5条数据DEPT表有10条数据，那么结果是乘积的关系50条数据

使用内连接和等值连接
范例:查询出雇员的编号,姓名,部门的编号和名称,地址

	SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, D.LOC
	FROM EMP E
	INNER JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO
	
	SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME, D.LOC
	FROM EMP E, DEPT D
	WHERE E.DEPTNO = D.DEPTNO

范例:查询出每个员工的上级领导
分析:emp表中的mgr字段是当前雇员的上级领导的编号,所以该字段对emp表产生了自身关联,
可以使用mgr字段和empno来关联

SELECT E1.EMPNO, E1.ENAME, E2.EMPNO, E2.ENAME
	FROM EMP E1, EMP E2
	WHERE E1.MGR = E2.EMPNO

范例:在上一个例子的基础上查询该员工的部门名称
分析:只要在上一个例子基础上再加一张表的关联,使用deptno来做关联字段即可

SELECT E1.EMPNO, E1.ENAME, E2.EMPNO, E2.ENAME, D.DNAME
FROM EMP E1, EMP E2, DEPT D
WHERE E1.MGR = E2.EMPNO
AND D.DEPTNO = E1.DEPTNO

2.外连接(左右连接)
右连接
当我们在做基本连接查询的时候,查询出所有的部门下的员工,我们发现编号为40的部门下没有员工,
但是要求把该部门也展示出来,我们发现上面的基本查询是办不到的

SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO;

使用(+)表示左连接或者右连接,当(+)在左边表的关联条件字段上时是右连接,
如果是在右边表的关联条件字段上就是左连接
范例:查询出所有员工的上级领导
分析:我们发现使用我们以前的做法发现KING的上级领导没有被展示,我们需要使用左右连接把他查询出来

SELECT E1.EMPNO, E1.ENAME, E2.EMPNO, E2.ENAME
	FROM EMP E1, EMP E2
	WHERE E1.MGR = E2.EMPNO(+)

3.ON子句,自己编写连接条件(重要)
On相当于where

SELECT * FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO

4.左连接和右连接LEFT JOIN和RIGHT JOIN(重要)

5.分组统计
分组统计需要使用GROUP BY来分组
语法:语法:SELECT * |列名 FROM 表名 {WEHRE 查询条件} {GROUP BY 分组字段} ORDER BY 列名1 ASC|DESC,列名2…ASC|DESC
范例:查询每个部门的人数

SELECT D.DNAME, COUNT(D.DEPTNO)
	FROM EMP E
	INNER JOIN DEPT D
	ON E.DEPTNO = D.DEPTNO
	GROUP BY D.DNAME

范例:查询出每个部门的平均工资

SELECT D.DEPTNO, D.DNAME, AVG(SAL)
	FROM EMP E
	INNER JOIN DEPT D
    ON E.DEPTNO = D.DEPTNO
	GROUP BY D.DEPTNO, D.DNAME

注意:
1)如果使用分组函数,SQL只可以把GOURP BY分组条件字段和分组函数查询出来,不能有其他字段
2)如果使用分组函数,不使用GROUP BY只可以查询出来分组函数的值

范例:查询出部门人数大于5人的部门
分析:需要给count(ename)加条件,此时在本查询中不能使用where,可以使用HAVING

SELECT D.DNAME, COUNT(D.DEPTNO)
FROM EMP E
INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME
HAVING COUNT(D.DEPTNO) > 5

范例:查询出部门平均工资大于2000的部门

SELECT D.DEPTNO, D.DNAME, AVG(SAL)
FROM EMP E
INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME
HAVING AVG(SAL) > 2000

6.子查询
在一个查询的内部还包括另一个查询,则此查询称为子查询
Sql的任何位置都可以加入子查询

范例:查询比7900工资高的雇员
分析:查询出7900员工的工资是多少,把它作为条件

SELECT *
FROM EMP
WHERE EMP.SAL > (SELECT E.SAL FROM EMP E WHERE E.EMPNO = 7900)

所有的子查询必须在"()"中编写
子查询在操作中有三类:
单列子查询:返回的结果是一列的一个内容
单行子查询:返回多个列,有可能是一个完整的记录
多行子查询:返回多条记录

范例:查询出比雇员7900的工资高,同时从事和7499的工作一样的员工

SELECT *
	FROM EMP
	WHERE EMP.SAL > (SELECT E.SAL FROM EMP E WHERE E.EMPNO = 7900)
	AND EMP.JOB=(SELECT E2.JOB FROM EMP E2 WHERE E2.EMPNO = 7499)

范例:要求查询每个部门的最低工资和最低工资的雇员和部门名称

SELECT D.DNAME, E2.ENAME, E2.SAL
	FROM DEPT D,
       EMP E2,
       (SELECT E.DEPTNO DEPTNO, MIN(E.SAL) SAL FROM EMP E GROUP BY E.DEPTNO) A
	WHERE D.DEPTNO = A.DEPTNO
	AND E2.SAL = A.SAL

7.exists和not exists关键字
exists (sql 返回结果集为真)
not exists(sql 不返回结果集为真)

范例:查询出有员工的部门有哪些?

SELECT * FROM DEPT D WHERE D.DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP E)

范例:查询出没有员工的部门

SELECT * FROM DEPT D WHERE D.DEPTNO NOT IN (SELECT DISTINCT DEPTNO FROM EMP E);

8.Rownum与分页查询(重点)
ROWNUM:表示行号,实际上此是一个列,但是这个列是一个伪列,此列可以在每张表中出现
范例:查询EMP表带有ROWNUM列

SELECT ROWNUM,E.* FROM EMP E

我们可以根据ROWNUM来取结果集的前几行,比如前5行

SELECT ROWNUM, E.* FROM EMP E WHERE ROWNUM < 6

但是我们不能取到中间几行,因为ROWNUM不支持大于号,只支持小于号,
如果想实现我们的需求怎么办呢?答案是使用子查询,也正是oracle分页的做法

select *
	from (select rownum rm, a.* from (select * from emp) a where rownum <= 11[当前页*容量]) b
	where b.rm > 5[(当前页-1)*容量]
	

9.视图
视图就是封装了一条复杂查询的语句
语法:CREATE [OR REPLACE] VIEW 视图名称 AS 子查询 WITH READ ONLY
范例:建立一个视图,此视图包括了20部门的全部员工信息

create view empvd20 as select * from emp t where t.deptno = 20

视图创建完毕就可以使用视图来查询,查询出来的都是20部门的员工
我们尝试着修改视图但是发现是视图所查询的表的字段值被修改了.所以我们一般不会去修改视图.
我们可以设置视图为只读.

将创建视图的权限赋给xiaoguan

GRANT CREATE ANY VIEW TO xiaoguan;

10.PL/SQL(选学内容)
procedure n. 程序,手续;步骤
language n. 语言;语言文字;表达能力
declare vt. 宣布,声明;断言,宣称vi. 声明,宣布
begin vt. 开始

什么是PL/SQL?
PL/SQL(Procedure Language/SQL)
PLSQL是Oracle对sql语言的过程化扩展,指在SQL命令语言中增加了过程处理语句(如分支、循环等),
使SQL语言具有过程处理能力.把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来,
使得PLSQL面向过程但比过程语言简单、高效、灵活和实用.

范例1:为职工涨工资,每人涨10％的工资.

update emp set sal=sal*1.1

范例2:按职工的职称长工资,总裁涨1000元,经理涨800元,其他人员涨400元.
这样的需求我们就无法使用一条SQL来实现,需要借助其他程序来帮助完成,也可以使用pl/sql.

1)pl/sql程序语法
程序语法:
declare
说明部分 (变量说明,游标声明,例外说明 )
begin
语句序列 (DML语句)…
exception
例外处理语句
end;

2)常量和变量定义
在程序的声明阶段可以来定义常量和变量.
变量的基本类型就是ORACLE中的建表时字段的变量如char, varchar2, date, number, boolean, long
定义语法: varl char(15);
Psal number(9,2);
说明变量名、数据类型和长度后用分号结束说明语句.
范例:声明变量并且输出

DECLARE
mychar CHAR(10);
mystr VARCHAR2(32);
BEGIN
mychar:=‘bdqn’;
mystr:=‘uplooking’;
dbms_output.put_line(LENGTH(mychar));
dbms_output.put_line(mychar||mystr);
END;

常量定义:

DECLARE
TEST1 CONSTANT CHAR(10):=‘TEST’;
BEGIN
dbms_output.put_line(TEST1);
END;

引用变量
MY_NAME EMP.ENAME%TYPE;
引用型变量,即MY_NAME的类型与EMP表中ENAME列的类型一样
在sql中使用into来赋值
DECLARE
EMPREC EMP.ENAME%TYPE; --引用类型,类型和EMP表中的ENAME字段类型一致
BEGIN
SELECT E.ENAME INTO EMPREC FROM EMP E WHERE E.EMPNO = 7369;
dbms_output.put_line(EMPREC);
END;
将查询到的语句赋值给EMPREC

记录型变量
EMPREC EMP%ROWTYPE 代表一行
记录变量分量的引用
EMPREC.ENAME:=‘ADAMS’;

DECLARE
EMPREC EMP%ROWTYPE;
BEGIN
SELECT * INTO EMPREC FROM EMP WHERE EMP.EMPNO = 7369;
dbms_output.put_line(EMPREC.ENAME || ‘-’ || EMPREC.SAL);
END;
注意 := 赋值符号等价于java中的=号;
= 逻辑等,判断两个值是否相等,等价于java中的==号

3)if分支
语法1:
IF 条件 THEN
语句1;
语句2;
END IF;
语法2:
IF 条件 THEN
语句1;
ELSE
语句2;
END IF;
语法3:
IF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
ELSIF 条件 THEN 语句;
…
ELSE 语句;
END IF;

范例1:

DECLARE
	  PNUM NUMBER := 1;
	BEGIN
	  IF PNUM = 1 THEN
		DBMS_OUTPUT.PUT_LINE('我是1');
	  END IF;
	END;

范例2:

DECLARE
  MYNUM NUMBER := 32;
BEGIN
  IF MYNUM < 18 THEN
	DBMS_OUTPUT.PUT_LINE('未成年人');
  ELSIF MYNUM >= 18 AND MYNUM < 40 THEN
	DBMS_OUTPUT.PUT_LINE('中年人');
  ELSIF MYNUM >= 40 THEN
	DBMS_OUTPUT.PUT_LINE('老年人');
  END IF;
END;

4).LOOP循环语句
其中语法2比较常用
语法1:
WHILE 条件 LOOP
… .
total:= total + salary;
END LOOP;
语法2:
LOOP
EXIT WHEN 条件;
……
End LOOP;
语法3:
FOR I IN 1…3 LOOP
语句1;
END LOOP ;
范例:使用语法1输出1到10的数字

//第一种
	DECLARE
	  STEP NUMBER := 1;
	BEGIN
	  WHILE STEP <= 10 LOOP
		DBMS_OUTPUT.PUT_LINE(STEP);
		STEP := STEP + 1;
	  END LOOP;
	END;
	//第二种
	DECLARE
	  STEP NUMBER := 1;
	BEGIN
	  LOOP
		EXIT WHEN STEP > 10;
		DBMS_OUTPUT.PUT_LINE(STEP);
		STEP := STEP + 1;
	  END LOOP;
	END;
	//第三种
	BEGIN
	  FOR I IN 1 .. 10 LOOP
		DBMS_OUTPUT.PUT_LINE(I);
	  END LOOP;
	END;