sql執(zhí)行順序?

(1)from?

(3) join?

(2) on?

(4) where?

(5)group by(開始使用select中的別名锅铅，后面的語句中都可以使用)

(6) avg,sum....?

(7)having?

(8) select?

(9) distinct?

(10) order by?

select?考生姓名,?max(總成績)?as?max總成績

from?tb_Grade? ?

where?考生姓名?is?not?null? ?

group?by?考生姓名? ?

having?max(總成績)>?600? ?

order?by?max總成績? ?

表結(jié)構(gòu)：

學(xué)生表：

CREATE TABLE STUDENT

(SNO VARCHAR(3) NOT NULL,?

SNAME VARCHAR(4) NOT NULL,

SSEX VARCHAR(2) NOT NULL,?

SBIRTHDAY VARCHAR(200),

CLASS VARCHAR(5))

班級表：

CREATE TABLE COURSE

(CNO VARCHAR(5) NOT NULL,?

CNAME VARCHAR(10) NOT NULL,?

TNO VARCHAR(10) NOT NULL)

成績表：

CREATE TABLE SCORE?

(SNO VARCHAR(3) NOT NULL,?

CNO VARCHAR(5) NOT NULL,?

DEGREE NUMERIC(10, 1) NOT NULL)?

老師表：

CREATE TABLE TEACHER?

(TNO VARCHAR(3) NOT NULL,?

TNAME VARCHAR(4) NOT NULL, TSEX VARCHAR(2) NOT NULL,?

TBIRTHDAY DATETIME NOT NULL, PROF VARCHAR(6),?

DEPART VARCHAR(10) NOT NULL)

插入數(shù)據(jù)：

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾華'?

,'男' ,'1977-09-01',95033);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明'?

,'男' ,'1975-10-02',95031);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王麗'?

,'女' ,'1976-01-23',95033);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李軍'?

,'男' ,'1976-02-20',95033);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳'?

,'女' ,'1975-02-10',95031);

INSERT INTO STUDENT (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陸君'?

,'男' ,'1974-06-03',95031);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-105' ,'計(jì)算機(jī)導(dǎo)論',825)

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('3-245' ,'操作系統(tǒng)' ,804);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('6-166' ,'數(shù)據(jù)電路' ,856);

INSERT INTO COURSE(CNO,CNAME,TNO)VALUES ('9-888' ,'高等數(shù)學(xué)' ,100);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-245',86);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-245',75);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-245',68);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (103,'3-105',92);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (105,'3-105',88);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (109,'3-105',76);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'3-105',64);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'3-105',91);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'3-105',78);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (101,'6-166',85);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (107,'6-106',79);

INSERT INTO SCORE(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)?

VALUES (804,'李誠','男','1958-12-02','副教授','計(jì)算機(jī)系');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)?

VALUES (856,'張旭','男','1969-03-12','講師','電子工程系');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)

VALUES (825,'王萍','女','1972-05-05','助教','計(jì)算機(jī)系');

INSERT INTO TEACHER(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)?

VALUES (831,'劉冰','女','1977-08-14','助教','電子工程系');

題目：

1僵娃、 查詢Student表中的所有記錄的Sname筹陵、Ssex和Class列究恤。

2冤竹、 查詢教師所有的單位即不重復(fù)的Depart列财饥。

3落竹、 查詢Student表的所有記錄闸氮。

4剪况、 查詢Score表中成績在60到80之間的所有記錄。

5蒲跨、 查詢Score表中成績?yōu)?5译断，86或88的記錄。

6或悲、 查詢Student表中“95031”班或性別為“女”的同學(xué)記錄孙咪。

7、 以Class降序查詢Student表的所有記錄巡语。

8翎蹈、 以Cno升序、Degree降序查詢Score表的所有記錄男公。

9荤堪、 查詢“95031”班的學(xué)生人數(shù)。

10枢赔、查詢Score表中的最高分的學(xué)生學(xué)號和課程號澄阳。

11、查詢‘3-105’號課程的平均分踏拜。

12碎赢、查詢Score表中至少有5名學(xué)生選修的并以3開頭的課程的平均分?jǐn)?shù)。

13速梗、查詢最低分大于70肮塞，最高分小于90的Sno列襟齿。

14、查詢所有學(xué)生的Sname峦嗤、Cno和Degree列蕊唐。

15、查詢所有學(xué)生的Sno烁设、Cname和Degree列替梨。

16、查詢所有學(xué)生的Sname装黑、Cname和Degree列副瀑。

17、查詢“95033”班所選課程的平均分恋谭。

18糠睡、假設(shè)使用如下命令建立了一個(gè)grade表：

create table grade(low?? number(3,0),upp?? number(3),rank?? char(1));

insert into grade values(90,100,’A’);

insert into grade values(80,89,’B’);

insert into grade values(70,79,’C’);

insert into grade values(60,69,’D’);

insert into grade values(0,59,’E’);

commit;

現(xiàn)查詢所有同學(xué)的Sno、Cno和rank列疚颊。

19狈孔、查詢選修“3-105”課程的成績高于“109”號同學(xué)成績的所有同學(xué)的記錄。

20材义、查詢score中選學(xué)一門以上課程的同學(xué)中分?jǐn)?shù)為非最高分成績的記錄均抽。

21、查詢成績高于學(xué)號為“109”其掂、課程號為“3-105”的成績的所有記錄油挥。

22、查詢和學(xué)號為108的同學(xué)同年出生的所有學(xué)生的Sno款熬、Sname和Sbirthday列深寥。

23、查詢“張旭“教師任課的學(xué)生成績贤牛。

24惋鹅、查詢選修某課程的同學(xué)人數(shù)多于5人的教師姓名。

25殉簸、查詢95033班和95031班全體學(xué)生的記錄闰集。

26、查詢存在有85分以上成績的課程Cno.

27喂链、查詢出“計(jì)算機(jī)系“教師所教課程的成績表。

28妥泉、查詢“計(jì)算機(jī)系”與“電子工程系“不同職稱的教師的Tname和Prof椭微。

29、查詢選修編號為“3-105“課程且成績至少高于選修編號為“3-245”的同學(xué)的Cno盲链、Sno和Degree,并按Degree從高到低次序排序蝇率。

30迟杂、查詢選修編號為“3-105”且成績高于選修編號為“3-245”課程的同學(xué)的Cno、Sno和Degree.

31本慕、查詢所有教師和同學(xué)的name排拷、sex和birthday.

32、查詢所有“女”教師和“女”同學(xué)的name锅尘、sex和birthday.

33监氢、查詢成績比該課程平均成績低的同學(xué)的成績表。

34藤违、查詢所有任課教師的Tname和Depart.

35? 查詢所有未講課的教師的Tname和Depart.?

36浪腐、查詢至少有2名男生的班號。

37顿乒、查詢Student表中不姓“王”的同學(xué)記錄议街。

38、查詢Student表中每個(gè)學(xué)生的姓名和年齡璧榄。

39特漩、查詢Student表中最大和最小的Sbirthday日期值。

40骨杂、以班號和年齡從大到小的順序查詢Student表中的全部記錄涂身。

41、查詢“男”教師及其所上的課程腊脱。

42访得、查詢最高分同學(xué)的Sno、Cno和Degree列陕凹。

43悍抑、查詢和“李軍”同性別的所有同學(xué)的Sname.

44、查詢和“李軍”同性別并同班的同學(xué)Sname.

45杜耙、查詢所有選修“計(jì)算機(jī)導(dǎo)論”課程的“男”同學(xué)的成績表

參考答案：

1. SELECT SNAME,SSEX,CLASS FROM STUDENT;

2. SELECT DISTINCT DEPART FROM TEACHER;

3. SELECT * FROM STUDENT;

4. SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 80;

5.SELECT * FROM SCORE WHERE DEGREE IN (85,86,88);

6. SELECT * FROM STUDENT WHERE CLASS='95031' OR SSEX='女';

7.SELECT * FROM STUDENT ORDER BY CLASS DESC;

8.SELECT * FROM SCORE ORDER BY CNO ASC,DEGREE DESC;

9.SELECT? COUNT(*) FROM STUDENT WHERE CLASS='95031';

10.SELECT SNO,CNO FROM SCORE WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE);

SELECT SNO,CNO FROM SCORE ORDER BY DEGREE DESC LIMIT 1;

11.SELECT AVG(DEGREE) FROM SCORE WHERE CNO='3-105';

12.select avg(degree),cno

from score

where cno like '3%'

group by cno

having count(sno)>= 5;

13.SELECT SNO FROM SCORE GROUP BY SNO HAVING MIN(DEGREE)>70 AND MAX(DEGREE)<90;

14.SELECT A.SNAME,B.CNO,B.DEGREE FROM STUDENT AS A JOIN SCORE AS B ON A.SNO=B.SNO;

15.SELECT A.CNAME, B.SNO,B.DEGREE FROM COURSE AS A JOIN SCORE AS B ON A.CNO=B.CNO ;

16.SELECT A.SNAME,B.CNAME,C.DEGREE FROM STUDENT A JOIN (COURSE B,SCORE C)

ON A.SNO=C.SNO AND B.CNO =C.CNO;

17.SELECT AVG(A.DEGREE) FROM SCORE A JOIN STUDENT B ON A.SNO = B.SNO WHERE B.CLASS='95033';

18.SELECT A.SNO,A.CNO,B.RANK FROM SCORE A,GRADE B WHERE A.DEGREE BETWEEN B.LOW AND B.UPP?

ORDER BY RANK;

19.SELECT A.* FROM SCORE A JOIN SCORE B WHERE A.CNO='3-105' AND A.DEGREE>B.DEGREE AND?

B.SNO='109' AND B.CNO='3-105';

另一解法：SELECT A.* FROM SCORE A? WHERE A.CNO='3-105' AND A.DEGREE>ALL(SELECT DEGREE FROM?

SCORE B WHERE B.SNO='109' AND B.CNO='3-105');

20.SELECT * FROM score s WHERE DEGREE<(SELECT MAX(DEGREE) FROM SCORE) GROUP BY SNO HAVING?

COUNT(SNO)>1 ORDER BY DEGREE ;

21.見19的第二種解法

22搜骡。SELECT SNO,SNAME,SBIRTHDAY FROM STUDENT WHERE YEAR(SBIRTHDAY)=(SELECT YEAR(SBIRTHDAY)?

FROM STUDENT WHERE SNO='108');

ORACLE:select x.cno,x.Sno,x.degree from score x,score y where x.degree>y.degree and?

y.sno='109'and y.cno='3-105';

select cno,sno,degree from score?? where degree >(select degree from score where sno='109'?

and cno='3-105')

23.SELECT A.SNO,A.DEGREE FROM SCORE A JOIN (TEACHER B,COURSE C)

ON A.CNO=C.CNO AND B.TNO=C.TNO

WHERE B.TNAME='張旭';

另一種解法：select cno,sno,degree from score where cno=(select x.cno from course x,teacher y?

where x.tno=y.tno and y.tname='張旭');

根據(jù)實(shí)際EXPLAIN此SELECT語句，第一個(gè)的掃描次數(shù)要小于第二個(gè)

24.SELECT A.TNAME FROM TEACHER A JOIN (COURSE B, SCORE C) ON (A.TNO=B.TNO AND B.CNO=C.CNO)?

GROUP BY C.CNO HAVING COUNT(C.CNO)>5;

另一種解法：select tname from teacher where tno in(select x.tno from course x,score y where?

x.cno=y.cno group by x.tno having count(x.tno)>5);

實(shí)際測試1明顯優(yōu)于2

25佑女。select cno,sno,degree from score where cno=(select x.cno from course x,teacher y where?

x.tno=y.tno and y.tname='張旭');

26记靡。SELECT CNO FROM SCORE GROUP BY CNO HAVING MAX(DEGREE)>85;

另一種解法：select distinct cno from score where degree in (select degree from score where?

degree>85);

27。SELECT A.* FROM SCORE A JOIN (TEACHER B,COURSE C) ON A.CNO=C.CNO AND B.TNO=C.TNO

WHERE B.DEPART='計(jì)算機(jī)系';

另一種解法：SELECT * from score where cno in (select a.cno from course a join teacher b on?

a.tno=b.tno and b.depart='計(jì)算機(jī)系');

此時(shí)2略好于1团驱，在多連接的境況下性能會迅速下降

28摸吠。select tname,prof from teacher where depart='計(jì)算機(jī)系' and prof not in (select prof from?

teacher where depart='電子工程系');

29。SELECT * FROM SCORE WHERE DEGREE>ANY(SELECT DEGREE FROM SCORE WHERE CNO='3-245') ORDER?

BY DEGREE DESC;

30嚎花。SELECT * FROM SCORE WHERE DEGREE>ALL(SELECT DEGREE FROM SCORE WHERE CNO='3-245') ORDER?

BY DEGREE DESC;

31.SELECT SNAME AS NAME, SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT

UNION

SELECT TNAME AS NAME, TSEX AS SEX, TBIRTHDAY AS BIRTHDAY FROM TEACHER;

32.SELECT SNAME AS NAME, SSEX AS SEX, SBIRTHDAY AS BIRTHDAY FROM STUDENT WHERE SSEX='女'

UNION

SELECT TNAME AS NAME, TSEX AS SEX, TBIRTHDAY AS BIRTHDAY FROM TEACHER WHERE TSEX='女';

33.SELECT A.* FROM SCORE A WHERE DEGREE<(SELECT AVG(DEGREE) FROM SCORE B WHERE A.CNO=B.CNO);

須注意********此題

34寸痢。解法一：SELECT A.TNAME,A.DEPART FROM TEACHER A JOIN COURSE B ON A.TNO=B.TNO;

解法二：select tname,depart from teacher a where exists

(select * from course b where a.tno=b.tno);

解法三：SELECT TNAME,DEPART FROM TEACHER WHERE TNO IN (SELECT TNO FROM COURSE);

實(shí)際分析，第一種揭發(fā)貌似更好紊选，至少掃描次數(shù)最少啼止。

35.解法一：SELECT TNAME,DEPART FROM TEACHER A LEFT JOIN COURSE B USING(TNO) WHERE ISNUL

(B.tno);

解法二：select tname,depart from teacher a where not exists

(select * from course b where a.tno=b.tno);

解法三：SELECT TNAME,DEPART FROM TEACHER WHERE TNO NOT IN (SELECT TNO FROM COURSE);

NOT IN的方法效率最差道逗，其余兩種差不多

36.SELECT CLASS FROM STUDENT A WHERE SSEX='男' GROUP BY CLASS HAVING COUNT(SSEX)>1;

37.SELECT * FROM STUDENT A WHERE SNAME not like '王%';

38.SELECT SNAME,(YEAR(NOW())-YEAR(SBIRTHDAY)) AS AGE FROM STUDENT;

39.select sname,sbirthday as THEMAX from student where sbirthday =(select min(SBIRTHDAY)?

from student)

union

select sname,sbirthday as THEMIN from student where sbirthday =(select max(SBIRTHDAY) from?

student);

40.SELECT CLASS,(YEAR(NOW())-YEAR(SBIRTHDAY)) AS AGE FROM STUDENT ORDER BY CLASS DESC,AGE?

DESC;

41.SELECT A.TNAME,B.CNAME FROM TEACHER A JOIN COURSE B USING(TNO) WHERE A.TSEX='男';

42.SELECT A.* FROM SCORE A WHERE DEGREE=(SELECT MAX(DEGREE) FROM SCORE B );

43.SELECT SNAME FROM STUDENT A WHERE SSEX=(SELECT SSEX FROM STUDENT B WHERE B.SNAME='李軍');

44.SELECT SNAME FROM STUDENT A WHERE SSEX=(SELECT SSEX FROM STUDENT B WHERE B.SNAME='李軍' )

AND CLASS=(SELECT CLASS FROM STUDENT C WHERE c.SNAME='李軍');

45.解法一：SELECT A.* FROM SCORE A JOIN (STUDENT B,COURSE C) USING(sno,CNO) WHERE B.SSEX='男

' AND C.CNAME='計(jì)算機(jī)導(dǎo)論';

解法二：select * from score where sno in(select sno from student where

ssex='男') and cno=(select cno from course

where cname='計(jì)算機(jī)導(dǎo)論');