Oracle優化之sql基本功
花了幾個小時研究了坤哥部落格裡的sql舉重思維訓練，原生sql語句是一個高階程式設計師的必備基礎，這裡在坤哥的基礎上，做些簡單的思路總結。
建表和初始化sql(本例子使用oralce資料庫):
<span style="font-size:18px;">--建立表
create table T_STUDENT(sno NUMBER not null, sname VARCHAR2(30), sdree VARCHAR2(50), sage NUMBER, ssex CHAR(2));
alter table T_STUDENT add primary key (SNO);
create table T_SCORE(sno NUMBER, cno NUMBER, grade NUMBER(4,1), tno NUMBER, id NUMBER not null);
alter table T_SCORE add primary key (ID);
create table T_COURSE(cno NUMBER not null, cname VARCHAR2(30));
alter table T_COURSE add primary key (CNO);
--初始化學生表
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (1, '李坤', '天融信', 26, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (2, '曹貴生', '中銀', 26, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (3, '柳波', '買賣寶', 27, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (4, '紀爭光', 'IBM', 23, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (5, '李學宇', '微軟', 25, '女');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (6, '李雪琪', '文思創新', 25, '女');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (7, '陳緒', '中海油', 26, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (8, '韓正陽', '中海油', 24, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (9, '陳偉東', '中核', 24, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (10, '劉兵', '優酷', 24, '男');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (11, '丁成雲', '聯想', 25, '女');
insert into T_STUDENT (SNO, SNAME, SDREE, SAGE, SSEX) values (12, '王鵬', '中興', 25, '男');
commit;
--初始化課程表
insert into T_COURSE (CNO, CNAME) values (1, 'JAVA程式設計');
insert into T_COURSE (CNO, CNAME) values (2, 'ORACLE開發');
insert into T_COURSE (CNO, CNAME) values (3, 'C++程式設計');
insert into T_COURSE (CNO, CNAME) values (4, 'C#程式設計');
insert into T_COURSE (CNO, CNAME) values (5, 'Windows實戰');
insert into T_COURSE (CNO, CNAME) values (6, 'Center OS教程');
insert into T_COURSE (CNO, CNAME) values (7, 'Jsp/Servlet開發');
insert into T_COURSE (CNO, CNAME) values (8, 'J2EE從入門到精通');
insert into T_COURSE (CNO, CNAME) values (9, 'EJB及設計模式');
insert into T_COURSE (CNO, CNAME) values (10, 'Javascript/jQuery實戰');
insert into T_COURSE (CNO, CNAME) values (11, 'Flash設計');
insert into T_COURSE (CNO, CNAME) values (12, 'HTML/CSS/JAVASCRIPT實戰');
insert into T_COURSE (CNO, CNAME) values (13, '精通ASP.NET');
insert into T_COURSE (CNO, CNAME) values (14, 'JBoss入門');
insert into T_COURSE (CNO, CNAME) values (15, 'Spring開發');
commit;
--初始化成績表
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (1, 2, 90.0, 2, 1);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (1, 3, 80.0, 3, 2);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (1, 4, 90.0, 4, 3);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 2, 70.0, 2, 4);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 11, 66.0, 11, 5);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 15, 77.0, 15, 6);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 8, 87.0, 8, 7);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (2, 6, 96.0, 6, 8);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (3, 2, 89.0, 2, 9);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (3, 1, 91.0, 1, 10);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (4, 2, 83.0, 2, 11);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (5, 4, 73.0, 4, 12);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (5, 1, 60.0, 1, 13);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (5, 8, 82.0, 8, 14);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (6, 8, 90.5, 10, 15);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (8, 2, 58.0, 2, 16);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 3, 80.0, 3, 17);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 11, 65.0, 11, 18);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 12, 67.0, 12, 19);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 15, 95.0, 15, 20);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (9, 13, 59.0, 13, 21);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 4, 98.0, 4, 22);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 6, 97.0, 6, 23);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 7, 96.0, 7, 24);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 7, 95.0, 7, 25);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (12, 8, 69.0, 8, 26);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (12, 9, 85.0, 9, 27);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (10, 14, 100.0, 14, 28);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (6, 9, 100.0, 9, 29);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 1, 59.0, 1, 30);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 4, 90.0, 4, 31);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 5, 91.0, 5, 32);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 6, 58.0, 6, 33);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 8, 93.0, 8, 34);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 9, 57.0, 9, 35);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 10, 95.0, 10, 36);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 11, 96.0, 11, 37);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 12, 97.0, 12, 38);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 13, 98.0, 13, 39);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 14, 99.0, 14, 40);
insert into T_SCORE (SNO, CNO, GRADE, TNO, ID) values (11, 15, 89.0, 15, 41);
commit;
</span>思維舉重前的熱身運動:
<strong><span style="font-size:18px;">--(一)查詢選修課程名稱為'JAVA程式設計'的學員學號和姓名
--方法一:
--1.查詢'JAVA程式設計'這門課程的課程號
--2.在成績表中查詢課程號為步驟1的課程號的成績資訊..
--3.在學生表中查詢學號為步驟2結果的學生資訊
select st.sno, st.sname
from student st
where st.sno in
(select sno
from score sc
where sc.cno =
(select co.cno from course co where co.cname = 'JAVA程式設計'));
--方法二:
--1.查詢'JAVA程式設計'這門課程的課程號
--2.在成績表和學生表關聯結果中查詢課程號等於步驟1的課程號的成績資訊.
--sql86
select st.sno, st.sname
from student st, score sc
where st.sno = sc.sno
and sc.cno =
(select co.cno from course co where co.cname = 'JAVA程式設計');
--sql92
select st.sno, st.sname
from student st
join score sc
on st.sno = sc.sno
where sc.cno =
(select co.cno from course co where co.cname = 'JAVA程式設計');
--方法三:
--1.在成績表和課程表關聯結果中查詢選修了'JAVA程式設計'這門課的學生的學號
--2.在學生表中查詢步驟1中的學號的學生的詳細資訊.
--sql86
select st.sno, st.sname
from student st
where st.sno in (select sc.sno
from score sc, course co
where sc.cno = co.cno
and co.cname = 'JAVA程式設計');
--sql92
select st.sno, st.sname
from student st
where st.sno in (select sc.sno
from score sc
join course co
on sc.cno = co.cno
where co.cname = 'JAVA程式設計');
--方法四:
--在成績表和課程表和學生表三表關聯的結果中過濾得到選修了 'JAVA程式設計'的學生基本資訊
--sql86
select st.sno, st.sname
from student st, score sc, course co
where st.sno = sc.sno
and co.cno = sc.cno
and co.cname = 'JAVA程式設計';
--sql92(1) 用where過濾
select st.sno, st.sname
from student st
join score sc
on st.sno = sc.sno
join course co
on co.cno = sc.cno
where co.cname = 'JAVA程式設計';
--sql92(2) 在關聯條件中過濾
select st.sno, st.sname
from student st
join score sc
on st.sno = sc.sno
join course co
on co.cno = sc.cno
and co.cname = 'JAVA程式設計';
注: 1.對於sql86 和sql92的區別見這篇文章: http://blog.csdn.net/lk_blog/article/details/7580300
2.如果您在看的過程中覺得不熟悉的地方很多,建議您先看此文: </span><a target=_blank href="http://blog.csdn.net/lk_blog/article/details/7585501"><span style="font-size:18px;">http://blog.csdn.net/lk_blog/article/details/7585501</span></a></strong>進入正文:
<span style="font-size:18px;"><strong>--(一)查詢不選修課程編號為'1'的學員姓名和所屬單位
--1.在成績表中查詢課程號為'1'的所有學生學號
--2.在學生表中查詢學號不在步驟1中的學生的基本資訊.
select st.sname, st.sdree
from t_student st
where st.sno not in (select sc.sno from t_score sc where sc.cno = '1');
--(二)查詢平均成績大於85的所有學生的學號、姓名和平均成績?
select sc.sno, st.sname, avg(sc.grade)
from t_score sc
join t_student st
on sc.sno = st.sno
group by sc.sno, st.sname
having avg(sc.grade) > 85;
--(三)查詢課程名稱為"JAVA程式設計",且分數低於60的學生姓名和分數
select st.sname, sc.grade
from t_score sc
join t_student st
on sc.sno = st.sno
join t_course co
on sc.cno = co.cno
where sc.grade < 60
and co.cname = 'JAVA程式設計';
select st.sname, sc.grade
from t_score sc
join t_student st
on sc.sno = st.sno
join t_course co
on sc.cno = co.cno
and sc.grade < 60
and co.cname = 'JAVA程式設計' ;
select st.sname, sc.grade
from t_score sc
join t_student st
on sc.sno = st.sno
where sc.cno = (select cno from t_course where cname = 'JAVA程式設計')
and sc.grade < 60
--(四)查詢任何一門課程成績全部都在70分以上的姓名、課程名稱和分數?
--1.查詢出成績小於70分的學生的學號.
--2.將學生,成績,課程三張表作關聯.
--3.在關聯表中過濾出不在步驟1查詢結果中的學生資訊.
select st.sname, co.cname, sc.grade
from t_student st
join t_score sc
on st.sno = sc.sno
join t_course co
on sc.cno = co.cno
where st.sno not in (select sc1.sno from t_score sc1 where sc1.grade < 70);
--(五)查詢出選了課的學生的人數.
select count(distinct(sc.sno)) from t_score sc;
--(六)查詢每門課程被選課的學生數
select sc.cno, count(distinct(sc.sno)) from t_score sc group by sc.cno;
--(七)查詢選了全部課程的學員姓名和所屬單位
--1.在課程表中查詢出所有課程的數量
--2.在成績表中查詢出學生選課數等於步驟1中總選課數的學生的學號,注意要用distinct,having中可以使用count,where中不能使用count.
--3.在學生表中查出步驟2中學生的基本資訊.
select st.sname, st.sdree
from t_student st
where st.sno in
(select sc.sno
from t_score sc
group by sc.sno
having count(distinct sc.cno) = (select count(distinct sc1.cno)
from t_course sc1));
--(八) 查詢沒有學全所有課的同學的學號、姓名
select st.sname, st.sdree
from t_student st
where st.sno in
(select sc.sno
from t_score sc
group by sc.sno
having count(distinct sc.cno) != (select count(*) from t_course));
--(九)查詢選修課程超過5門的學員學號和所屬單位
--1.在成績表中查詢出選課程超過5門的學生學號.
--2.在學生表中查詢步驟1中學號的學生的基本資訊.
select st.sname, st.sdree
from t_student st
where st.sno in (select sc.sno
from t_score sc
group by sc.sno
having count(distinct sc.cno) > 5);
--(十)查詢出沒有選課的學生基本資訊
--1.在成績表中查詢出所有選過課的學生的學號.
--2.在學生表中查詢出步驟1中學生的基本資訊.
select *
from t_student st
where st.sno not in (select sc.sno from t_score sc);
--下面的兩個sql等價,在成績表中資料量很大時使用下面的sql
select *
from t_student st
where st.sno not in (select distinct (sc.sno) from t_score sc);
select *
from t_student st
where st.sno not in (select sc.sno from t_score sc group by sc.sno);
--(十一) 列出有二門以上不及格課程的學生姓名及其平均成績
--方法一
--1.在成績表中查詢出2門不及格學生的學號,結果記作t1
--2.將學生表和t1和成績表三表作關聯得到關聯表,在關聯表中取學生基本資訊和平均成績.
--sql92
select st.sno, st.sname, avg(sc.grade)
from t_student st
join (select sc.sno
from t_score sc
where sc.grade < 60
group by sc.sno
having count(distinct sc.cno) > 2) t1
on st.sno = t1.sno
join t_score sc
on sc.sno = t1.sno
group by st.sno, st.sname;
--sql86
select st.sno, st.sname, avg(sc.grade)
from t_student st,
t_score sc,
(select sc.sno
from t_score sc
where sc.grade < 60
group by sc.sno
having count(distinct sc.cno) > 2) t1
where st.sno = t1.sno
and sc.sno = t1.sno
group by st.sno, st.sname;
--方法二:
--1.在成績表中查詢出2門不及格學生的學號
--2.將學生表和成績表通過學號作關聯並根據步驟1中的結果作過濾,在關聯結果中取出學生基本資訊和平均成績
select st.sno, st.sname, avg(sc.grade)
from t_student st
join t_score sc
on st.sno = sc.sno
where st.sno in (select sc.sno
from t_score sc
where sc.grade < 60
group by sc.sno
having count(distinct sc.cno) > 2)
group by st.sno, st.sname;
--(十二) 查詢平均成績大於60分的同學的學號和平均成績
--學生表和課程表關聯,在having子句中過濾平均成績大於60分.
select st.sno, avg(sc.grade)
from t_student st, t_score sc
where st.sno = sc.sno
group by st.sno
having avg(sc.grade) > 60;
--1.學生表和課程表關聯,將關聯的結果記作t1
--2.在t1中過濾平均成績大於60的學生學號.
select t1.sno, t1.avg_grade
from (select st.sno, avg(sc.grade) avg_grade
from t_student st, t_score sc
where st.sno = sc.sno
group by st.sno) t1
where t1.avg_grade > 60;
--(十三)查詢出只選修了一門課程的全部學生的學號和姓名
--方法一:
--1.將學生表和成績表作關聯,在分組函式中使用having子句過濾出只選了一門課程的學生基本資訊.
select sc.sno, st.sname
from t_score sc
join t_student st
on sc.sno = st.sno
group by sc.sno, st.sname
having count(distinct sc.cno) = 1;
--方法二:
--1.在成績表中查詢學號,分組函式的過濾條件判斷只選擇了一門課程的學生.
--2.在學生表中查詢學號在步驟1中的值的學生的基本資訊
select st.sno,st.sname
from t_student st
where st.sno in (select sc.sno
from t_score sc
group by sc.sno
having count(distinct sc.cno) = 1);
--(十四)查詢至少有一門課與學號為"1"的同學所學相同的同學的學號和姓名
select st.sno, st.sname
from t_student st
join t_score sc1
on st.sno = sc1.sno
where sc1.cno in (select sc.cno from t_score sc where sc.sno = '1')
group by st.sno, st.sname;
--(十五)列出既學過"1"號課程,又學過"2"號課程的所有學生姓名
--1.將成績表和課程表作關聯,在關聯條件中作過濾查詢出既選過課程'1'又選過課程'2'的學生的學號,注意看 co.cno in ('1', '2')和having count(distinct sc.cno) = 2 的位置.
--2.在學生表中根據步驟1的結果作過濾查詢出學生的基本資訊.
--方法一:
--sql86
select st.sno, st.sname
from t_student st,
(select sc.sno
from t_score sc, t_course co
where sc.cno = co.cno
and co.cno in ('1', '2')
group by sc.sno
having count(distinct sc.cno) = 2) t1
where st.sno = t1.sno;
--sql92
select st.sno, st.sname
from t_student st join
(select sc.sno
from t_score sc join t_course co
on sc.cno = co.cno
and co.cno in ('1', '2')
group by sc.sno
having count(distinct sc.cno) = 2) t1
on st.sno = t1.sno;
--方法二:
--sql86
select st.sno, st.sname
from t_student st
where st.sno in (select sc.sno
from t_score sc, t_course co
where sc.cno = co.cno
and co.cno in ('1', '2')
group by sc.sno
having count(distinct sc.cno) = 2);
--sql92
select st.sno, st.sname
from t_student st
where st.sno in (select sc.sno
from t_score sc
join t_course co
on sc.cno = co.cno
and co.cno in ('1', '2')
group by sc.sno
having count(distinct sc.cno) = 2);
--(十六)查詢至少學過學號為"1"的同學所有門課的同學學號和姓名
--1.查詢出'1'號同學學習的全部課程.
--2.查詢出'1'號同學學習全部課程的數量.
--3.將課程表和成績表做關聯,在關聯表中查詢出學生的學號,關聯條件中加入過濾條件[課程號在步驟1查詢結果範圍內],過濾條件中加入數量等級步驟2中得到的數量.
--4.在學生表中查詢步驟3中的學號的學生的基本資訊.
select st.sno, st.sname
from t_student st
where st.sno in
(select sc.sno
from t_score sc
join t_course co
on sc.cno = co.cno
and co.cno in (select sc.cno from t_score sc where sc.sno = '1')
group by sc.sno
having count(distinct sc.cno) = (select count(distinct sc.cno)
from t_score sc
where sc.sno = '1'))
--(十七)查詢和"6"號同學學習的課程完全相同的同學的學號和姓名
--分析:要查詢與6號同學完全相同的課程的學生資訊,等價於學過6號同學的學過的所有課程並且選課數量與6同學選課數量相等.
--方法一:
--1.查詢出'1'號同學學習的全部課程.
--2.查詢出'1'號同學學習全部課程的數量.
--3.將課程表和成績表做關聯,在關聯表中查詢出學生的學號和選課數量,記作 t2,關聯條件中加入過濾條件[課程號在步驟1查詢結果範圍內],過濾條件中加入數量等級步驟2中得到的數量.
--4.在成績表中查詢出學號和每個學生選課數量.得到結果記作: t1
--5.將步驟3中的t2和步驟4中的t1通過學生學號關聯,新增過濾條件,t1中的選課數量等於t2中的選課數量.
--6.在學生表中查詢不步驟5的學生學號的基本資訊.
select st.sno, st.sname
from t_student st
where st.sno in
(select t1.sno
from (select sc_a.sno, count(distinct sc_a.cno) num_outer
from t_score sc_a
group by sc_a.sno) t1
join (select sc.sno, count(distinct sc.cno) num_inner
from t_score sc
join t_course co
on sc.cno = co.cno
and co.cno in
(select sc.cno from t_score sc where sc.sno = '6')
group by sc.sno
having count(distinct sc.cno) = (select count(distinct sc.cno)
from t_score sc
where sc.sno = '6')) t2
on t1.sno = t2.sno
where t1.num_outer = t2.num_inner);
--(十八)列出"1"號課成績比"2"號課成績高的所有學生的學號及其"1"號課和"2"號課的成績
--1.將學生表和課程表作兩次關聯,一次關聯用於取該學生課程'1'的成績,另一次關聯用於取該學生課程'2'的成績.
--sql86
select st.sno, st.sname, sc_a.grade, sc_b.grade
from t_student st, t_score sc_a, t_score sc_b
where sc_a.cno = '1'
and sc_b.cno = '2'
and st.sno = sc_a.sno
and st.sno = sc_b.sno
and sc_a.grade > sc_b.grade
--sql92
select st.sno, st.sname, sc_a.grade, sc_b.grade
from t_student st
join t_score sc_a
on st.sno = sc_a.sno
join t_score sc_b
on st.sno = sc_b.sno
where sc_a.cno = '1'
and sc_b.cno = '2'
and sc_a.grade > sc_b.grade
--(十九)查詢所有同學的學號、姓名、選課數、總成績
select st.sno, st.sname, count(sc.cno), sum(sc.grade)
from t_student st, t_score sc
where st.sno = sc.sno
group by st.sno, st.sname;
--(二十)查詢課程成績小於60分的同學的學號,姓名,課程名,成績
--下面兩條sql雖然結果相同,但意義不同,注意理解一下哦.
--1.將學生表,課程表,成績表作關聯
--2.對關聯後的結果作過濾,過濾出成績小於60的學生基本資訊.
select st.sno, st.sname, co.cname, sc.grade
from t_score sc
join t_student st
on sc.sno = st.sno
join t_course co
on sc.cno = co.cno
where sc.grade < 60
--1.將學生表,課程表,成績表作關聯,在關聯條件中過濾成績小於60.
select st.sno, st.sname, co.cname, sc.grade
from t_score sc
join t_student st
on sc.sno = st.sno
join t_course co
on sc.cno = co.cno
and sc.grade <60
--(二十一)按平均成績從到低顯示所有學生的"JAVA程式設計"、"J2EE從入門到精通"、"EJB及設計模式"三門的課程成績,
--並按如下形式顯示: 學生ID,姓名,JAVA程式設計,J2EE從入門到精通,EJB及設計模式,有效課程數,有效課程平均分
--1.將成績表和課程表關聯得到結果記作: t1, 關聯時的條件選擇只統計以上三門課程.
--2.按題目中的要求組織統計結果.
select st.sno,
st.sname,
sum(decode(t1.cname, 'JAVA程式設計', t1.grade)) JAVA程式設計,
sum(decode(t1.cname, 'J2EE從入門到精通', t1.grade)) J2EE從入門到精通,
sum(decode(t1.cname, 'EJB及設計模式', t1.grade)) EJB及設計模式,
count(distinct t1.grade) 有效課程數,
avg(t1.grade) 有效課程平均分
from t_student st
join (select *
from t_score sc
join t_course co
on sc.cno = co.cno
and co.cname in
('JAVA程式設計', 'J2EE從入門到精通', 'EJB及設計模式')) t1
on st.sno = t1.sno
group by st.sno, st.sname
--將decode可以換成case when 第一種形式
select st.sno,
st.sname,
sum(case t1.cname
when 'JAVA程式設計' then
t1.grade
end) JAVA程式設計,
sum(case t1.cname
when 'J2EE從入門到精通' then
t1.grade
end) J2EE從入門到精通,
sum(case t1.cname
when 'EJB及設計模式' then
t1.grade
end) EJB及設計模式,
count(distinct t1.grade) 有效課程數,
avg(t1.grade) 有效課程平均分
from t_student st
join (select *
from t_score sc
join t_course co
on sc.cno = co.cno
and co.cname in
('JAVA程式設計', 'J2EE從入門到精通', 'EJB及設計模式')) t1
on st.sno = t1.sno
group by st.sno, st.sname
--將decode可以換成case when 第二種形式
select st.sno,
st.sname,
sum(case
when t1.cname = 'JAVA程式設計' then
t1.grade
end) JAVA程式設計,
sum(case
when t1.cname = 'J2EE從入門到精通' then
t1.grade
end) J2EE從入門到精通,
sum(case
when t1.cname = 'EJB及設計模式' then
t1.grade
end) EJB及設計模式,
count(distinct t1.grade) 有效課程數,
avg(t1.grade) 有效課程平均分
from t_student st
join (select *
from t_score sc
join t_course co
on sc.cno = co.cno
and co.cname in
('JAVA程式設計', 'J2EE從入門到精通', 'EJB及設計模式')) t1
on st.sno = t1.sno
group by st.sno, st.sname
--(二十二)查詢各科成績最高和最低的分:以如下形式顯示:課程ID,課程名,最高分,最低分
select sc.cno, co.cname, max(grade), min(grade)
from t_score sc
join t_course co
on sc.cno = co.cno
group by sc.cno, co.cname
--(二十三)按各科平均成績從低到高和及格率的百分數從高到低順序
--1.在成績表中查出課程號,平均成績,課程人數,記作 : t1
--2.在成績表中查出課程號,及格的課程人數,記作 : t2
--3.將步驟1中的成績和步驟2中的課程2關聯,查出所要的結果並排序.
select t1.cno, t1.avg_num 平均成績, (count_num1 / count_num) * 100 及格率
from (select sc.cno, avg(grade) avg_num, count(distinct sc.sno) count_num
from t_score sc
group by sc.cno) t1
join (select sc1.cno, count(distinct sc1.sno) count_num1
from t_score sc1
where sc1.grade > 60
group by sc1.cno) t2
on t1.cno = t2.cno
order by t1.avg_num asc, 及格率 desc
--(二十四)統計各科成績,各分數段人數:課程ID,課程名稱,[100-90]優,[90-80]良,[80-70]中,[70-60]一般,[<60]不及格
--1.在成績表中根據成績值分段
--2.將步驟1中的結果與課程表關聯.
select sc.cno,co.cname,
sum(case
when sc.grade > 90 then
1
end) 優,
sum(case
when sc.grade > 80 and sc.grade < 90 then
1
end) 良,
sum(case
when sc.grade > 70 and sc.grade < 80 then
1
end) 中,
sum(case
when sc.grade > 60 and sc.grade < 70 then
1
end) 一般,
sum(case
when sc.grade < 60 then
1
end) 不及格
from t_score sc join t_course co on sc.cno = co.cno
group by sc.cno,co.cname
--(二十五)查詢學生平均成績及其名次
select st.sno, st.sname, avg(sc.grade) avg_num
from t_score sc
join t_student st
on sc.sno = st.sno
group by st.sno, st.sname
order by avg_num desc
--(二十六)查詢課程號分別為1,2,3的課程,成績前三名的學生基本資訊:(不考慮成績並列情況)
--方法一:
--1.分別查出1,2,3各自的前3名的學生的學號,並用union all將結果集關聯.
--2.在學生表中查詢步驟1中查到的id的學生的基本資訊.
select *
from t_student
where sno in (select t1.sno
from (select sc1.*
from t_score sc1
where sc1.cno = 1
order by sc1.grade desc) t1
where rownum < 4
union all
select t1.sno
from (select sc1.*
from t_score sc1
where sc1.cno = 2
order by sc1.grade desc) t1
where rownum < 4
union all
select t1.sno
from (select sc1.*
from t_score sc1
where sc1.cno = 3
order by sc1.grade desc) t1
where rownum < 4)
--方法二:
--rank() over(Partition .. order by ...) 是按照某個欄位的值進行分組並編號
select t1.cno, t1.sno, t1.grade, r
from (select sc.sno,
sc.cno,
sc.grade,
rank() over(partition by sc.cno order by grade desc) r
from t_score sc) t1
where r < 4
and t1.cno in (1, 2, 3)
order by t1.cno, t1.sno, r;
--(二十七)查詢各科成績前三名的記錄(不考慮成績並列情況)
--rank() over(Partition .. order by ...) 是按照某個欄位的值進行分組並編號
select t1.cno, t1.sno, t1.grade, r
from (select sc.sno,
sc.cno,
sc.grade,
rank() over(partition by sc.cno order by grade desc) r
from t_score sc) t1
where r < 4
order by t1.cno, t1.sno, r;
</strong></span>
總結：
1、在開發Oracle9i時， 資料庫還時間了ANSL SQL/92標準的連結語法， 在書中建議在使用Oracle 9i及更高版本時，應該使用SQL/92標準的語法；在使用Oracle 8i 及更低版本時，應該使用SQL/86標準的語法。
2、多表查詢時，可以先建立相互連線的表，之後再新增過濾條件；也可以使用where逐條限制；中間關聯表可以和左邊的表先建立關聯，也可以和右邊的表先建立連線，也可以將相關的左右表一次性建立關聯，不同的關聯策略需要配合不同的篩選條件。
3、join on and 和join on where 可以得到相同的結果，但意義不同。
4、一些使用join的地方可以使用in。
5、如果遇到“至少”這樣的需求往往會用到in。
6、當查詢出的結果有重複的時候使用group by，如果還需要進一步篩選，配合使用having。
7、同一張表和不同表關聯一樣，重視自身關聯。
8、Oracle函式運用：rank() over(Partition .. order by ...) 是按照某個欄位的值進行分組並編號 可以替代原生的case 語句。
相關文章
- oracle優化一例之sql優化Oracle優化SQL
- Oracle之sql語句優化OracleSQL優化
- Oracle SQL語句優化之UNIONOracleSQL優化
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- oracle sql優化OracleSQL優化
- oracle sql 優化OracleSQL優化
- SQL Server優化之SQL語句優化SQLServer優化
- Oracle SQL優化之sql tuning advisor(STA)OracleSQL優化
- oracle筆記整理13——效能調優之SQL優化Oracle筆記SQL優化
- sql優化之邏輯優化SQL優化
- MySQL 效能優化之SQL優化MySql優化
- Oracle SQL效能優化OracleSQL優化
- oracle sql 排序優化OracleSQL排序優化
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- SQL Server SQL效能優化之引數化SQLServer優化
- Oracle SQL優化總結OracleSQL優化
- Oracle Sql優化筆記OracleSQL優化筆記
- oracle sql like優化（一）OracleSQL優化
- Oracle SQL優化 總結OracleSQL優化
- MySQL之SQL優化技巧MySql優化
- SQL優化之limit 1SQL優化MIT
- 【基本功】深入剖析Swift效能優化Swift優化
- 【SQL】sql優化小工具之SQLHCSQL優化
- ORACLE SQL效能優化系列 (一)OracleSQL優化
- 【優化】ORACLE set autotrace in SQL*Plus優化OracleSQL
- Oracle SQL優化基本步驟OracleSQL優化
- Oracle SQL效能優化常用方法OracleSQL優化
- MySQL之SQL語句優化MySql優化
- 資料庫效能優化之SQL語句優化資料庫優化SQL
- Oracle SQL調優系列之SQL Monitor ReportOracleSQL
- oracle效能優化之--hintsOracle優化
- Oracle SQL效能優化系列介紹OracleSQL優化
- MySQL之SQL優化詳解（一）MySql優化
- MySQL之SQL優化詳解（二）MySql優化
- MySQL之SQL優化詳解（三）MySql優化