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;
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;
