笔记081111
2008-11-11 下午8:26 发布人 tiger chang
*************************************************
* NAME :ChangTiger *
* DATE :19:28 2008-11-11 *
* EMAIL :o52tiger@yahoo.com.cn *
*************************************************
*****************************************************************
查询语句练习
SQL> SELECT SID , TRUNC(AVG(SCORE)) FROM S_L2 GROUP BY SID;
SID TRUNC(AVG(SCORE))
---------- -----------------
1001 62
1002 76
1003 69
1004 46
————————————————————————————————————————
SQL> SELECT SID , AVG(SCORE) FROM S_L2 GROUP BY SID;
SID AVG(SCORE)
---------- ----------
1001 62.6666667
1002 76
1003 69.3333333
1004 46
————————————————————————————————————————
————————————————————————————————————————
————————————————————————————————————————
*****************************************************************
SQL> SELECT AGE , COUNT(*) FROM STUDENT2
2 GROUP BY AGE
3 HAVING COUNT(*) > 2;
AGE COUNT(*)
---------- ----------
24 4
————————————————————————————————————————
SQL> SELECT SID, TRUNC(AVG(SCORE)) FROM S_L2
2 GROUP BY SID
3 HAVING TRUNC(AVG(SCORE)) > 60;
SID TRUNC(AVG(SCORE))
---------- -----------------
1001 62
1002 76
1003 69
————————————————————————————————
SQL> SELECT SID, MAX(SCORE) FROM S_L2
2 GROUP BY SID
3 HAVING MAX(SCORE) > 80;
SID MAX(SCORE)
---------- ----------
1001 86
1002 86
1003 96
SQL>
——————————————————————————————————————
SQL> SELECT SID, LID ,COUNT(*)
2 FROM S_L2
3 GROUP BY SID, LID ;
SID LID COUNT(*)
---------- ---------- ----------
1001 110 1
1001 111 1
1001 112 1
1002 110 1
1002 111 1
1002 112 1
1003 110 1
1003 111 1
1003 112 1
1004 110 1
1004 111 1
SID LID COUNT(*)
---------- ---------- ----------
1004 112 1
12 rows selected.
SQL>
——————————————————————————————————————
SQL> SELECT SID , SUM(SCORE)PAYROLL
2 FROM S_L2
3 WHERE SID > 1000
4 GROUP BY SID
5 HAVING SUM(SCORE) > 100
6 ORDER BY SUM(SCORE) DESC;
SID PAYROLL
---------- ----------
1002 228
1003 208
1001 188
1004 138
SQL>
——————————————————————————————————————
SQL> SELECT * FROM S_L2
2 WHERE SCORE >
3 ( SELECT AVG(SCORE) FROM S_L2 );
SID LID SCORE
---------- ---------- ----------
1001 110 86
1002 110 86
1001 111 86
1002 111 86
1003 112 96
SQL>
——————————————————————————————————————
SQL> SELECT * FROM S_L2
2 WHERE SCORE =
3 (SELECT MIN(SCORE) FROM S_L2 );
SID LID SCORE
---------- ---------- ----------
1001 112 16
SQL>
——————————————————————————————————————
子查询
把一个查询的结果做为另一个查询的条件
*****************************************************************
查询前N 条数据 ROWNUM 关键字
不能使用大于号 相当与一个伪字段
SQL> SELECT * FROM STUDENT2
2 WHERE ROWNUM < 5;
ID SNAME CID AGE
---------- -------------------- ---------- ----------
1001 CHANGTIGER 1 24
1003 SHANGHAI 2 24
1004 BEIJING 2 24
1002 NANCHANG 1 24
*****************************************************************
SQL> SELECT * FROM
2 ( SELECT * FROM STUDENT2 ORDER BY ID )
3 WHERE ROWNUM < 3;
ID SNAME CID AGE
---------- -------------------- ---------- ----------
1001 CHANGTIGER 1 24
1002 NANCHANG 1 24
*************************************************************************
SQL> SELECT * FROM
2 ( SELECT * FROM STUDENT2 WHERE ID > 1002 ORDER BY ID )
3 WHERE ROWNUM < 2;
ID SNAME CID AGE
---------- -------------------- ---------- ----------
1003 SHANGHAI 2 24
**************************************************************************
SQL>
SQL> SELECT ROWNUM , ID FROM STUDENT2;
ROWNUM ID
---------- ----------
1 1001
2 1003
3 1004
4 1002
****************************************************************
SQL> SELECT ROWNUM , S.* FROM
2 ( SELECT * FROM STUDENT2 WHERE ID > 1002 ORDER BY ID ) S
3 WHERE ROWNUM < 4
4 ;
ROWNUM ID SNAME CID AGE
---------- ---------- -------------------- ---------- ----------
1 1003 SHANGHAI 2 24
2 1004 BEIJING 2 24
SQL>
*****************************************************************
----效率低下
SQL> SELECT * FROM STUDENT2 WHERE ROWNUM <= 4
2 AND ID NOT IN
3 ( SELECT ID FROM STUDENT2 WHERE ROWNUM <=2 );
ID SNAME CID AGE
---------- -------------------- ---------- ----------
1004 BEIJING 2 24
1002 NANCHANG 1 24
SQL>
*****************************************************************
--效率不错
SQL> SELECT ID , SNAME , AGE FROM
2 (SELECT ID , SNAME ,AGE ,ROWNUM R
3 FROM STUDENT2 )
4 WHERE R < 3 AND R > 1;
ID SNAME AGE
---------- -------------------- ----------
1003 SHANGHAI 24
SQL>
*****************************************************************
SQL> SELECT ID , SNAME , AGE FROM
2 ( SELECT ID , SNAME ,AGE ,ROWNUM R
3 FROM STUDENT2 ORDER BY ID )
4 WHERE R < 3
5 AND R > 1;
ID SNAME AGE
---------- -------------------- ----------
1003 SHANGHAI 24
*****************************************************************
INSERT INTO STUDENT2 VALUES (1006 , 'LLO', 1, 12);
INSERT INTO STUDENT2 VALUES (1007 , 'ELLO', 1, 12);
INSERT INTO STUDENT2 VALUES (1008 , 'HELL', 1, 12);
INSERT INTO STUDENT2 VALUES (1009 , 'HLLO', 1, 12);
INSERT INTO STUDENT2 VALUES (1010 , 'HELO', 1, 12);
INSERT INTO STUDENT2 VALUES (1011 , 'HELO', 1, 12);
****************************************************************
SQL> SELECT ID , SNAME , AGE
2 FROM
3 ( SELECT ID , SNAME , AGE ,ROWNUM R
4 FROM STUDENT2 ORDER BY ID )
5 WHERE R < 5
6 AND R > 1;
ID SNAME AGE
---------- -------------------- ----------
1003 SHANGHAI 24
1004 BEIJING 24
1005 HELLO 12
SQL>
*****************************************************************
效率再提高查询结果不对
SQL> SELECT ID , SNAME , AGE
2 FROM (
3 SELECT ID , SNAME , AGE , ROWNUM R
4 FROM STUDENT2 WHERE ROWNUM <= 7 ORDER BY ID )
5 WHERE R > 5;
ID SNAME AGE
---------- -------------------- ----------
1002 NANCHANG 24
1007 ELLO 12
*****************************************************************
最终结果
SQL> SELECT ID , SNAME , AGE
2 FROM (
3 SELECT ID , SNAME , AGE , ROWNUM R
4 FROM (SELECT ID , SNAME , AGE FROM STUDENT2 ORDER BY ID) WHERE ROWNUM <= 7)
5 WHERE R > 5;
*****************************************************************
结果对比
1 ， 先排序在找前七个
SQL> SELECT ID , SNAME , AGE FROM (
2 SELECT ID ,SNAME , AGE , ROWNUM R FROM (
3 SELECT ID ,SNAME ,AGE FROM STUDENT2 ORDER BY ID )
4 WHERE ROWNUM <= 7 )
5 WHERE R > 5;
ID SNAME AGE
---------- -------------------- ----------
1006 LLO 12
1007 ELLO 12
SQL>
2 ， 先找七个在排序 结果不对
SQL> SELECT ID , SNAME , AGE
2 FROM (
3 SELECT ID , SNAME , AGE , ROWNUM R
4 FROM STUDENT2 WHERE ROWNUM <= 7 ORDER BY ID )
5 WHERE R > 5;
ID SNAME AGE
---------- -------------------- ----------
1002 NANCHANG 24
1007 ELLO 12
*****************************************************************
ROLLBACK 回滚数据
COMMIT 执行提交
*****************************************************************