Oracle第二课（学习中笔记）

What is a  join?
1.Equijoin
2.Non-equijoin
3.Outer join
4. Self join
1、等值连接
SELECT A.ID,A.NAME,B.CNAME FROM TABLE1 A,TBALE2 B WHERE A.ID=B.CID;

建立多个表
1。学生表(JUL12_STUDENT_04)
CREATE TABLE JUL12_STUDENT_04(
    SID NUMBER(4),
    SNAME VARCHAR2(20),
    CID NUMBER(4)
);
INSERT INTO JUL12_STUDENT_04 VALUES (101,'ZHANG',1);
INSERT INTO JUL12_STUDENT_04 VALUES (102,'SHELI',2);
INSERT INTO JUL12_STUDENT_04 VALUES (103,'GUOSHEN',1);
INSERT INTO JUL12_STUDENT_04 VALUES (104,'WEB',2);
INSERT INTO JUL12_STUDENT_04 VALUES (105,'LUEHUA',1);
INSERT INTO JUL12_STUDENT_04 VALUES (106,'HEJING',2);
INSERT INTO JUL12_STUDENT_04 VALUES (107,'WEN',1);
INSERT INTO JUL12_STUDENT_04 VALUES (108,'JIANG',2);

2。班级表(JUL12_CLASS_04)
CREATE TABLE JUL12_CLASS_04(
    CID NUMBER(4),
    CNAME VARCHAR2(20)
);
INSERT INTO JUL12_CLASS_04 VALUES (1,'SD0001');
INSERT INTO JUL12_CLASS_04 VALUES (2,'SD0002');
INSERT INTO JUL12_CLASS_04 VALUES (3,'SD0003');
3。课程表(JUL12_LESSION_04)
CREATE TABLE JUL12_LESSION_04(
    LID NUMBER(4),
    LNAME VARCHAR2(20)
);
INSERT INTO JUL12_LESSION_04 VALUES (100,'MATH');
INSERT INTO JUL12_LESSION_04 VALUES (101,'ENG');
INSERT INTO JUL12_LESSION_04 VALUES (102,'FHY');
4。学生课程表(JUL12_S_L_04)
CREATE TABLE JUL12_S_L_04(
    SID NUMBER(4),
    LID NUMBER(4),
    SOURCE NUMBER(4)
);
INSERT INTO JUL12_S_L_04 VALUES (101,100,59);
INSERT INTO JUL12_S_L_04 VALUES (101,101,60);
INSERT INTO JUL12_S_L_04 VALUES (101,102,88);
INSERT INTO JUL12_S_L_04 VALUES (102,100,59);
INSERT INTO JUL12_S_L_04 VALUES (102,101,60);
INSERT INTO JUL12_S_L_04 VALUES (102,102,88);
INSERT INTO JUL12_S_L_04 VALUES (103,100,59);
INSERT INTO JUL12_S_L_04 VALUES (103,101,60);
INSERT INTO JUL12_S_L_04 VALUES (103,102,88);
INSERT INTO JUL12_S_L_04 VALUES (104,100,59);
INSERT INTO JUL12_S_L_04 VALUES (104,101,60);
INSERT INTO JUL12_S_L_04 VALUES (104,102,88);

CREATE TABLE JUL12_GRADE_04 (
    LEVEL1 VARCHAR2(2),
    LOW NUMBER(3),
    HIGH NUMBER(3)
);
INSERT INTO JUL12_GRADE_04 VALUES('A',80,100);
INSERT INTO JUL12_GRADE_04 VALUES('B',50,80);

SELECT S.SNAME,G.LEVEL1 FROM JUL12_STUDENT_04 S,JUL12_S_L_04 SL,JUL12_GRADE_04 G WHERE S.SID =SL.SID AND SL.SOURCE BETWEEN G.LOW AND G.HIGH


SELECT S.SNAME,G.LEVEL1 ,L.LNAME
FROM JUL12_STUDENT_04 S,JUL12_S_L_04 SL,JUL12_GRADE_04 G ,JUL12_LESSION_04 L
WHERE (S.SID =SL.SID) AND (SL.SOURCE BETWEEN G.LOW AND G.HIGH)

－－自连接
SELECT SNAME
FROM JUL12_STUDENT_04 A,JUL12_STUDENT_04 B
WHERE (A.SNAME= B.SNAME) AND (A.SID!=B.SID)

－－外连接
SELECT SNAME,AGE,CNAME
FROM JUL12_CLASS_04 A, JUL12_STUDENT_04 B
WHERE A.CID=B.SID(+);
符号＋为为外连接，可以分为左外连接，和右外连接。
在少的一方给与加号。然后就可以显示多的一方的所有的内容。

－－group分组查询
GROUP BY
SELECT COUNT(*) FROM JUL12_STUDENT_04 GROUP BY TRUNC (AGE/10)

－－分组函数
SELECT AVG(AGE) FROM TABLEOFSTUDENT
COUNT(DISTINCT|ALL|EXPR|*)
MAX (DISTINCT|ALL|EXPR)
MIN (DISTINCT|ALL|EXPR)
SUM (DISTINCT|ALL|N)
SELECT COUNT(*)  FROM JUL12_STUDENT_04

查询出年龄大于23，每个班机的人数
SELECT COUNT(*) FROM JUL12_STUDENT_04 WHERE AGE >13 GROUP BY CID;

SELECT COUNT(*),TRUNC(AGE/10) FROM JUL12_STUDENT_04 GROUP BY TRUNC(AGE/10);

ORDER BY  是可以使用函数的
SELECT COUNT(*) AGE FROM JUL12_STUDENT_04
GROUP BY AGE
ORDER BY COUNT(*);
不可将分组函数使用WHERE语句中

使用HAVING
对组的条件进行过滤
SELECT COUNT(*) AS AGES FROM JUL12_STUDENT_04
ORDER BY AGE
HAVING COUNT(*)>2

Subqueries子查询
把一个查询的结果嵌入在另一个查询的条件的值
列出大于平均年龄的人
SELECT SNAME FROM JUL12_STUDENT_04
WHERE SAGE > SELECT AVG(AGE)FROM JUL12_STUDENT_04;

查询参加考试学生的姓名
SELECT SNAME FROM JUL12_STUDENT_04
WHERE SID NOT  IN (
    SELECT SID FROM JUL12_S_L_04
    WHERE SOURCE !=NULL
)

SELECT  TOP 3 * FROM JUL12_STUDENT_04
WHERE SID NOT IN (SELECT TOP 3 SID FROM JUL12_STUDENT_04)
分页
ROWNUM为伪字段
SELECT * FROM JUL12_STUDENT_04 WHERE ROWNUM<6 获取前5列的内容
查询到当前数据库中的第三个数据和第四个数据。为以后的分页作准备。
SELECT * FROM JUL12_STUDENT_04
WHERE SID NOT IN (
    SELECT SID FROM JUL12_STUDENT_04
    WHERE ROWNUM <=3)
AND ROWNUM<=3;


SELECT SID ,SNAME FROM (
    SELECT SID, SNAME, ROWNUM R
    FROM JUL12_STUDENT_04
) WHERE R >=4 AND R<= 6
提高效率
SELECT SID,SNAME FROM(
    SELECT SID ,SNAME,ROWNUM R
    FROM JUL12_STUDENT_04 WHERE ROWNUM<=6)
WHERE R >=4;

SELECT SID, SNAME FROM(
    SELECT SID,SNAME, ROWNUM R
    FROM (
        SELECT SID,SNAME FROM
        JUL12_STUDENT_04
        ORDER BY SID DESC
    )
)
WHERE R >=4 AND R<= 6

SELECT SID,SNAME FROM(
SELECT SID,SNAME ,ROWNUM R
FROM(
SELECT * FROM JUL12_STUDENT_04 ORDER BY SID DESC)
WHERE ROWNUM <=6
)
WHERE R>=4;