´óѧÊý¾Ý¿â¿¼ÊÔ¸´Ï°ÊÔÌâ´ø´ð°¸ ÏÂÔØ±¾ÎÄ

ij¸öѧУÓÐÈô¸É¸öϵ£¬Ã¿¸öϵÓÐÈô¸É¸öѧÉú£¬¿ªÉèÈô¸É¸ö¿Î³Ì£¬Ã¿ÃſγÌÓÐÈô¸ÉѧÉúÑ¡ÐÞ£¬Ä³Ò»ÃſοÉÒÔΪ²»Í¬µÄϵ¿ªÉè¡£ÓÃE-Rͼ»­³ö¸ÃѧУµÄ¸ÅÄîÄ£ÐÍ¡£ ÃèÊöѧÉúµÄÊôÐÔÓУºÑ§ºÅ¡¢ÐÕÃû¡¢³öÉúÈÕÆÚ¡¢ÏµºÅ¡¢°àºÅ£» ÃèÊöϵµÄÊôÐÔÓУºÏµÃû¡¢ÏµºÅ¡¢Ïµ°ì¹«Êҵص㡢ÈËÊý£» ÃèÊöѧУµÄÊôÐÔÓУºÑ§Ð£Ãû¡¢³ÉÁ¢Äê·Ý¡¢µØµã£» ÃèÊö¿Î³ÌµÄÊôÐÔÓУº¿Î³ÌºÅ¡¢¿Î³ÌÃû¡¢Ñ§·Ö¡£ £¨1£© Éè¼ÆÑ§Ð£µÄE-Rͼ¡£

£¨2£©½«¸ÃE-Rͼת»»Îª¹ØÏµÄ£Ê½¡£

¹ØÏµÄ£ÐÍÈçÏ¡£

ѧÉú£¨Ñ§ºÅ¡¢ÐÕÃû¡¢³öÉúÈÕÆÚ¡¢ÏµºÅ¡¢°àºÅ£©¡£ ϵ£¨ÏµÃû¡¢ÏµºÅ¡¢Ïµ°ì¹«Êҵص㡢ÈËÊý£©¡£ ѧУ£¨Ñ§Ð£Ãû¡¢³ÉÁ¢Äê·Ý¡¢µØµã£© ¿Î³Ì£¨¿Î³ÌºÅ¡¢¿Î³ÌÃû¡¢Ñ§·Ö£©

£¨3£©Ö¸³öת»»½á¹ûÖÐÿ¸ö¹ØÏµÄ£Ê½µÄºòÑ¡Âë¡£

ѧÉú¹ØÏµµÄÖ÷Â룺Óû§Ãû ϵµÄÖ÷Â룺ϵºÅ

.. ..

ѧУÖ÷Â룺ѧУÃû ¿Î³ÌÖ÷Â룺ѧУÃû

ÓÐÈý¸ö¹ØÏµÈçÏÂ

£¨1£©Ñ§Éú¹ØÏµStudent£¬°üÀ¨Ñ§ºÅSno¡¢ÐÕÃûSname¡¢ÄêÁäSage¡¢ÐÔ±ðSsex£» £¨2£©¿Î³Ì¹ØÏµCourse£¬°üÀ¨¿Î³ÌºÅCno¡¢¿Î³ÌÃûCname¡¢ÈονÌʦCteacher£» £¨3£©Ñ§ÉúÑ¡¿Î¹ØÏµSC£¬°üÀ¨Sno¡¢CnoºÍ³É¼¨Grade¡£

1.¶¨ÒåѧÉú¹ØÏµStudent£¬Ñ§ºÅ²»ÄÜΪ¿Õ,ÆäֵΨһ£¬ÐÕÃûֵҲΨһ£¬Ñ§ºÅΪÖ÷Â룻 CREATE TABLE Student

(Sno CHAR£¨10£©NOT NULL UNIQUE PRIMARY KEY, Sname CHAR(30) UNIQUE, Sage INT £¬ Ssex CHAR(2)) £»

2. ²éѯ1ºÅ¿Î³ÌµÄƽ¾ù³É¼¨£»

SELECT AVG£¨Grade£©FROM SC WHERE Cno=¡®1¡¯£» 3. ²éѯѧºÅ¡°95001¡±µÄѧÉúÑ¡ÐÞÈ«²¿¿Î³ÌºÅºÍ³É¼¨£»

SELECT Cno£¬Grade FROM SC WHERE Sno=¡®95001¡¯; 4. ²éѯÄêÁäÔÚ19-20ËêÖ®¼äµÄÄÐÉúºÍÅ®ÉúµÄÊýÁ¿£»

SELECT Ssex£¬COUNT£¨Sno£© FROM Student

WHERE Sage¡Ý19 and Sage¡Ü20 GROUP BY Ssex;

»òÕß:WHERE Sage BETWEEN 19 and 20

ÓÐÈý¸ö¹ØÏµÈçÏ£º

£¨1£©Ñ§Éú¹ØÏµStudent£¬°üÀ¨Ñ§ºÅSno¡¢ÐÕÃûSname¡¢ÄêÁäSage¡¢ÐÔ±ðSsex£» £¨2£©¿Î³Ì¹ØÏµCourse£¬°üÀ¨¿Î³ÌºÅCno¡¢¿Î³ÌÃûCname¡¢ÈονÌʦCteacher£» £¨3£©Ñ§ÉúÑ¡¿Î¹ØÏµSC£¬°üÀ¨Sno¡¢CnoºÍ³É¼¨Grade¡£

1.²éѯ ¡°Èí¼þ¹¤³Ì¡±¿Î³ÌµÄƽ¾ù³É¼¨£» SELECT AVG(Grade) FROM Course£¬SC

WHERE Cname=¡®Èí¼þ¹¤³Ì¡¯ AND SC.Cno=Course.Cno;

.. ..

»òÕߣºSELECT AVG(Grade)

FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname=¡®Èí¼þ¹¤³Ì¡¯) 2.²éѯÃû×ÖÖеÚ2¸ö×ÖΪ\Ñô\×ÖµÄѧÉúµÄÐÕÃûºÍѧºÅ¡£ SELECT Sname£¬Sno FROM Student

WHERE Sname LIKE ¡®_ _Ñô%¡¯£»

3.²éѯÁõÑôͬѧËùÑ¡Ð޵Ŀγ̺źͿγÌÃû

SELECT Course.Cno£¬Cname FROM Course WHERE Cno IN (SELECT Cno FROM SC WHERE Sno IN

(SELECT SnoFROM Student WHERE Sname= ¡®ÁõÑô¡¯));

»òÕߣºSELECT Course.Cno£¬Sname FROM Student£¬Course£¬SC

WHERE Sname= ¡®ÁõÑô¡¯ AND SC.Cno=Course.Cno AND Student.Sno=SC.Sno; 4²éѯѧÉúÊý¾Ý¿â¿ÎµÄ³É¼¨£¬Êä³öѧÉúÐÕÃûºÍ³É¼¨£¬°´³É¼¨ÅÅÐò£¨½µÐò£© ¡£

SELECT Sname,Grade FROM Student,Course,SC WHERE Student.Sno=SC.Sno Course.Cno=SC.Cno AND Course.Cname=¡¯Êý¾Ý¿â¡¯ ORDER BY Grade DESC; 5.¼ìË÷ûÓгɼ¨µÄѧÉúÐÕÃûºÍ¿Î³ÌºÅ¡£

Select Sname,Cno From Student,SC Where Grade is NULL AND Student.Sno=SC.Sno

ÓÐÈý¸ö¹ØÏµÈçÏÂ

£¨1£©Ñ§Éú¹ØÏµStudent£¬°üÀ¨Ñ§ºÅSno¡¢ÐÕÃûSname¡¢ÄêÁäSage¡¢ÐÔ±ðSsex£» £¨2£©¿Î³Ì¹ØÏµCourse£¬°üÀ¨¿Î³ÌºÅCno¡¢¿Î³ÌÃûCname¡¢ÈονÌʦCteacher£» £¨3£©Ñ§ÉúÑ¡¿Î¹ØÏµSC£¬°üÀ¨Sno¡¢CnoºÍ³É¼¨Grade¡£ ʹÓÃSQLÓï¾äʵÏÖ£º

1. ΪѧÉú±í½¨Á¢Ò»¸ö¾Û´ØË÷ÒýSTUNO,°´Ñ§ºÅÉýÐòºÍÄêÁä½µÐò¡£

CREAT CLUSTER INDEX Stuon ON Student(SNO ASC,SAGE DESC); 2. ²éѯÍõº®Í¬Ñ§Ã»ÓÐÑ¡Ð޵Ŀγ̵Ŀγ̺Å

SELECT Cno FROM Course

WHERE Cno NOT IN (SELECE Cno

FROM SC, Student

WHERE SC.Sno=Student.Sno AND Sname=¡±Íõº®¡±) ;

3. ´´½¨Ò»¸ö³É¼¨¸ßÓÚ90·ÖµÄѧÉúÊÓͼS1 £¬°üÀ¨Ñ§ÉúѧºÅ¡¢³öÉúÄê·Ý¡¢ËùÑ¡¿Î³ÌºÅ¡¢³É¼¨¡£

CREAT VIEW S1£¨Sno,Sbirth,Cno,Grade £© AS SELECT Sno,2018-Sage,Cno, Grade

FROM Student,SC WHERE Student.Sno=Sc.Sno AND Grade>90 4. Ïò¿Î³Ì±íÖÐÔö¼ÓÊôÐÔÈονÌʦ(Tname)£¬ÀàÐÍÊÇ×Ö·ûÐÍ

ALTER TABLE Course Add Tname char(20) 5.½«¿Î³ÌºÅΪ¡®003¡¯ ¿Î³Ì³É¼¨Ôö¼Ó10·Ö

UPDATE SC SET Grade= Grade+10 WHERE Cno=¡®003¡¯

.. ..

AND

ÓÐÈý¸ö¹ØÏµÈçÏÂ

£¨1£©Ñ§Éú¹ØÏµStudent£¬°üÀ¨Ñ§ºÅSno¡¢ÐÕÃûSname¡¢ÄêÁäSage¡¢ÐÔ±ðSsex£» £¨2£©¿Î³Ì¹ØÏµCourse£¬°üÀ¨¿Î³ÌºÅCno¡¢¿Î³ÌÃûCname¡¢ÈονÌʦCteacher£» £¨3£©Ñ§ÉúÑ¡¿Î¹ØÏµSC£¬°üÀ¨Sno¡¢CnoºÍ³É¼¨Grade¡£ ÏÂÃæÊ¹ÓùØÏµ´úÊý±í´ïʽʵÏÖ£º

1. ²éѯ¿Î³ÌÃûΪÊý¾Ý¿âÔ­Àí£¬ÊڿνÌʦΪÁõÑóµÄ¿Î³ÌºÅ¡£

§±cno(¦Òcname=¡®Êý¾Ý¿âÔ­Àí¡¯ ¡ÄCteacher=¡®ÁõÑó¡¯(Course))

2. ²éѯ¼ÈÑ¡ÐÞÁ˿γÌ1ÓÖÑ¡ÐÞÁ˿γÌ3µÄѧÉúѧºÅ,ÐÕÃû¡£

ʹÓÃSQLÓï¾äʵÏÖ£º

3. ½«Êý¾Ý¿âÔ­Àí¿Î³ÌµÄ³É¼¨Ìá¸ß10·Ö¡£ UPDATE SC SET GRADE=GRADE+10

where ¡®Êý¾Ý¿âÔ­Àí¡¯=£¨select Cname from Course where Course.cno=SC.cno); 4. ²éѯÖÁÉÙ2ÃÅ¿ÎÔÚ80·ÖÒÔÉϵÄѧÉúѧºÅ¼°¿Î³ÌÊý SELECT Sno, COUNT(*) FROM SC

WHERE Grade>=80 GROUP BY Sno

HAVING COUNT(*)>=2; 5.²éѯ¡°CÓïÑÔ_¿Î³ÌÉè¼Æ¡±µÄ¿Î³ÌºÅ SELECT Cno FROM Course

WHERE Cname Like ¡°CÓïÑÔ\\_³ÌÐòÉè¼Æ¡±ESCAPE¡®\\¡¯;

§±Sno£¬Sname (¦Òcno=¡°1¡± (Student¡ÞSC)) ¡É §±Sno,Sname (¦Òcno=¡°2¡± (Student¡ÞSC))

ÓÐËĸö¹ØÏµÈçÏÂ:

£¨1£©S(sno,sname,city)£»

£¨2£©P(pno,pname,color,weight)£» £¨3£©J(jno,jname,city) £»

£¨4£©SPJ(sno,pno,jno,qty) £»Æäº¬Òå¼û½Ì²ÄP64¡£ ÏÂÃæÊ¹ÓùØÏµ´úÊý±í´ïʽʵÏÖ£º 1. ²éѯ¹©Ó¦ºìÉ«Áã¼þµÄ¹©Ó¦É̺š£

§±Sno (¦Òcolor=¡°ºì¡± (S¡ÞSPJ ¡ÞP))

2.²éѯ±±¾©µÄ¹©Ó¦É̹©Ó¦¹¤³Ìj1Áã¼þµÄ¹©Ó¦É̺ż°ÊýÁ¿¡£

§±sno£¬qty (¦Òcity=¡°±±¾©¡± ¡Äjno=¡°j1¡± (S¡ÞSPJ ¡ÞP))

ʹÓÃSQLÓï¾äʵÏÖ£º

3¡¢½¨Á¢ÉÏÊöµÄ¹©Ó¦Çé¿ö±íSPJ¡£

.. ..