\n
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));\ninsert into Student values(\\'01\\' , \\'\u8d75\u96f7\\' , \\'1990-01-01\\' , \\'\u7537\\');\ninsert into Student values(\\'02\\' , \\'\u94b1\u7535\\' , \\'1990-12-21\\' , \\'\u7537\\');\ninsert into Student values(\\'03\\' , \\'\u5b59\u98ce\\' , \\'1990-05-20\\' , \\'\u7537\\');\ninsert into Student values(\\'04\\' , \\'\u674e\u4e91\\' , \\'1990-08-06\\' , \\'\u7537\\');\ninsert into Student values(\\'05\\' , \\'\u5468\u6885\\' , \\'1991-12-01\\' , \\'\u5973\\');\ninsert into Student values(\\'06\\' , \\'\u5434\u5170\\' , \\'1992-03-01\\' , \\'\u5973\\');\ninsert into Student values(\\'07\\' , \\'\u90d1\u7af9\\' , \\'1989-07-01\\' , \\'\u5973\\');\ninsert into Student values(\\'09\\' , \\'\u5f20\u4e09\\' , \\'2017-12-20\\' , \\'\u5973\\');\ninsert into Student values(\\'10\\' , \\'\u674e\u56db\\' , \\'2017-12-25\\' , \\'\u5973\\');\ninsert into Student values(\\'11\\' , \\'\u674e\u56db\\' , \\'2017-12-30\\' , \\'\u5973\\');\ninsert into Student values(\\'12\\' , \\'\u8d75\u516d\\' , \\'2017-01-01\\' , \\'\u5973\\');\ninsert into Student values(\\'13\\' , \\'\u5b59\u4e03\\' , \\'2018-01-01\\' , \\'\u5973\\');\n<\/pre>\n<\/div>\n\u79d1\u76ee\u8868 Course<\/p>\n
\n
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10))\ninsert into Course values(\\'01\\' , \\'\u8bed\u6587\\' , \\'02\\')\ninsert into Course values(\\'02\\' , \\'\u6570\u5b66\\' , \\'01\\')\ninsert into Course values(\\'03\\' , \\'\u82f1\u8bed\\' , \\'03\\')\n<\/pre>\n<\/div>\n\u6559\u5e08\u8868 Teacher<\/p>\n
\n
create table Teacher(TId varchar(10),Tname varchar(10))\ninsert into Teacher values(\\'01\\' , \\'\u5f20\u4e09\\')\ninsert into Teacher values(\\'02\\' , \\'\u674e\u56db\\')\ninsert into Teacher values(\\'03\\' , \\'\u738b\u4e94\\')\n<\/pre>\n<\/div>\n\u6210\u7ee9\u8868 SC<\/p>\n
\n
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1))\ninsert into SC values(\\'01\\' , \\'01\\' , 80)\ninsert into SC values(\\'01\\' , \\'02\\' , 90)\ninsert into SC values(\\'01\\' , \\'03\\' , 99)\ninsert into SC values(\\'02\\' , \\'01\\' , 70)\ninsert into SC values(\\'02\\' , \\'02\\' , 60)\ninsert into SC values(\\'02\\' , \\'03\\' , 80)\ninsert into SC values(\\'03\\' , \\'01\\' , 80)\ninsert into SC values(\\'03\\' , \\'02\\' , 80)\ninsert into SC values(\\'03\\' , \\'03\\' , 80)\ninsert into SC values(\\'04\\' , \\'01\\' , 50)\ninsert into SC values(\\'04\\' , \\'02\\' , 30)\ninsert into SC values(\\'04\\' , \\'03\\' , 20)\ninsert into SC values(\\'05\\' , \\'01\\' , 76)\ninsert into SC values(\\'05\\' , \\'02\\' , 87)\ninsert into SC values(\\'06\\' , \\'01\\' , 31)\ninsert into SC values(\\'06\\' , \\'03\\' , 34)\ninsert into SC values(\\'07\\' , \\'02\\' , 89)\ninsert into SC values(\\'07\\' , \\'03\\' , 98)\n<\/pre>\n<\/div>\n\u7ec3\u4e60\u9898\u76ee<\/h2>\n 1.\u67e5\u8be2\\” 01 \\”\u8bfe\u7a0b\u6bd4\\” 02 \\”\u8bfe\u7a0b\u6210\u7ee9\u9ad8\u7684\u5b66\u751f\u7684\u4fe1\u606f\u53ca\u8bfe\u7a0b\u5206\u6570<\/p>\n
\n
select *\nfrom (select SId ,score from sc where sc.CId=\\'01\\')as t1 , (select SId ,score from sc where sc.CId=\\'02\\') as t2\nwhere t1.SId=t2.SId\nand t1.score>t2.score\n<\/pre>\n<\/div>\n1.1 \u67e5\u8be2\u540c\u65f6\u5b58\u5728\\” 01 \\”\u8bfe\u7a0b\u548c\\” 02 \\”\u8bfe\u7a0b\u7684\u60c5\u51b5<\/p>\n
\n
select *\nfrom (select SId ,score from sc where sc.CId=\\'01\\')as t1 , (select SId ,score from sc where sc.CId=\\'02\\') as t2\nwhere t1.SId=t2.SId\n<\/pre>\n<\/div>\n1.2 \u67e5\u8be2\u5b58\u5728\\” 01 \\”\u8bfe\u7a0b\u4f46\u53ef\u80fd\u4e0d\u5b58\u5728\\” 02 \\”\u8bfe\u7a0b\u7684\u60c5\u51b5(\u4e0d\u5b58\u5728\u65f6\u663e\u793a\u4e3a null )<\/p>\n
\n
select *\nfrom (select SId ,score from sc where sc.CId=\\'01\\')as t1 left join (select SId ,score from sc where sc.CId=\\'02\\') as t2\non t1.SId=t2.SId\n<\/pre>\n<\/div>\n1.3 \u67e5\u8be2\u4e0d\u5b58\u5728\\” 01 \\”\u8bfe\u7a0b\u4f46\u5b58\u5728\\” 02 \\”\u8bfe\u7a0b\u7684\u60c5\u51b5<\/p>\n
\n
select *\nfrom sc\nwhere sc.SId not in (select SId from sc where sc.CId=\\'01\\')\nand sc.CId=\\'02\\'\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u5e73\u5747\u6210\u7ee9\u5927\u4e8e\u7b49\u4e8e 60 \u5206\u7684\u540c\u5b66\u7684\u5b66\u751f\u7f16\u53f7\u548c\u5b66\u751f\u59d3\u540d\u548c\u5e73\u5747\u6210\u7ee9<\/li>\n<\/ol>\n\n
select student.*,t1.avgscore\nfrom student inner JOIN(\nselect sc.SId ,AVG(sc.score)as avgscore\nfrom sc\nGROUP BY sc.SId\nHAVING AVG(sc.score)>=60)as t1 on student.SId=t1.SId\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u5728 SC \u8868\u5b58\u5728\u6210\u7ee9\u7684\u5b66\u751f\u4fe1\u606f<\/li>\n<\/ol>\n\n
select DISTINCT student.*\nfrom student ,sc\nwhere student.SId=sc.SId\n<\/pre>\n<\/div>\n4.\u67e5\u8be2\u6240\u6709\u540c\u5b66\u7684\u5b66\u751f\u7f16\u53f7\u3001\u5b66\u751f\u59d3\u540d\u3001\u9009\u8bfe\u603b\u6570\u3001\u6240\u6709\u8bfe\u7a0b\u7684\u603b\u6210\u7ee9(\u6ca1\u6210\u7ee9\u7684\u663e\u793a\u4e3anull)<\/p>\n
\n
select student.SId,student.Sname,t1.sumscore,t1.coursecount\nfrom student ,(\nselect SC.SId,sum(sc.score)as sumscore ,count(sc.CId) as coursecount\nfrom sc\nGROUP BY sc.SId) as t1\nwhere student.SId =t1.SId\n<\/pre>\n<\/div>\n4.1 \u67e5\u6709\u6210\u7ee9\u7684\u5b66\u751f\u4fe1\u606f<\/p>\n
\n
select *\nfrom student\nwhere EXISTS(select * from sc where student.SId=sc.SId)\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u300c\u674e\u300d\u59d3\u8001\u5e08\u7684\u6570\u91cf<\/li>\n<\/ol>\n\n
select count(*)\nfrom teacher\nwhere teacher.Tname like \\'\u674e%\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u5b66\u8fc7\u300c\u5f20\u4e09\u300d\u8001\u5e08\u6388\u8bfe\u7684\u540c\u5b66\u7684\u4fe1\u606f<\/li>\n<\/ol>\n\n
select student.*\nfrom teacher ,course ,student,sc\nwhere teacher.Tname=\\'\u5f20\u4e09\\'\nand teacher.TId=course.TId\nand course.CId=sc.CId\nand sc.SId=student.SId\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u6ca1\u6709\u5b66\u5168\u6240\u6709\u8bfe\u7a0b\u7684\u540c\u5b66\u7684\u4fe1\u606f<\/li>\n<\/ol>\n\n\u89e3\u6cd51<\/li>\n<\/ul>\n\n
select student.*\nfrom sc ,student\nwhere sc.SId=student.SId\nGROUP BY sc.SId\nHaving count(*)<(select count(*) from course)\n<\/pre>\n<\/div>\n\u4f46\u8fd9\u79cd\u89e3\u6cd5\u5f97\u51fa\u6765\u7684\u7ed3\u679c\u4e0d\u5305\u62ec\u4ec0\u4e48\u8bfe\u90fd\u6ca1\u9009\u7684\u540c\u5b66\u3002<\/p>\n
\n\u89e3\u6cd52<\/li>\n<\/ul>\n\n
select DISTINCT student.*\nfrom\n(select student.SId,course.CId\nfrom student,course ) as t1 LEFT JOIN (SELECT sc.SId,sc.CId from sc)as t2 on t1.SId=t2.SId and t1.CId=t2.CId,student\nwhere t2.SId is null\nand t1.SId=student.SId\n<\/pre>\n<\/div>\n\u5229\u7528\u7b1b\u5361\u5c14\u79ef\u53ef\u4ee5\u628a\u4ec0\u4e48\u8bfe\u90fd\u6ca1\u9009\u7684\u540c\u5b66\u67e5\u8be2\u51fa\u6765<\/p>\n
\n\u67e5\u8be2\u81f3\u5c11\u6709\u4e00\u95e8\u8bfe\u4e0e\u5b66\u53f7\u4e3a\\” 01 \\”\u7684\u540c\u5b66\u6240\u5b66\u76f8\u540c\u7684\u540c\u5b66\u7684\u4fe1\u606f<\/li>\n<\/ol>\n\n
select DISTINCT student.*\nfrom sc ,student\nwhere sc.CId in (select CId from sc where sc.SId=\\'01\\')\nand sc.SId=student.SId\n<\/pre>\n<\/div>\n9.\u67e5\u8be2\u548c\\” 01 \\”\u53f7\u7684\u540c\u5b66\u5b66\u4e60\u7684\u8bfe\u7a0b\u5b8c\u5168\u76f8\u540c\u7684\u5176\u4ed6\u540c\u5b66\u7684\u4fe1\u606f<\/p>\n
\n
select DISTINCT student.*\nfrom (\nselect student.SId,t.CId\nfrom student ,(select sc.CId from sc where sc.SId=\\'01\\') as t) as t1 LEFT JOIN sc on t1.SId=sc.SId and t1.CId=sc.CId,student\nwhere sc.SId is null\nand t1.SId=student.SId\n<\/pre>\n<\/div>\n10.\u67e5\u8be2\u6ca1\u5b66\u8fc7\\”\u5f20\u4e09\\”\u8001\u5e08\u8bb2\u6388\u7684\u4efb\u4e00\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u59d3\u540d<\/p>\n
\n
select *\nfrom student\nwhere student.SId not in\n(\nselect student.SId\nfrom student left join sc on student.SId=sc.SId\nwhere EXISTS\n(select *\nfrom teacher ,course\nwhere teacher.Tname=\\'\u5f20\u4e09\\'\nand teacher.TId=course.TId\nand course.CId=sc.CId))\n<\/pre>\n<\/div>\n11.\u67e5\u8be2\u4e24\u95e8\u53ca\u5176\u4ee5\u4e0a\u4e0d\u53ca\u683c\u8bfe\u7a0b\u7684\u540c\u5b66\u7684\u5b66\u53f7\uff0c\u59d3\u540d\u53ca\u5176\u5e73\u5747\u6210\u7ee9<\/p>\n
\n
select student.SId,student.Sname,avg(sc.score)\nfrom student ,sc\nwhere student.SId=sc.SId\nand sc.score<60\nGROUP BY sc.SId\nHAVING count(*)>=2\n<\/pre>\n<\/div>\n\n\u68c0\u7d22\\” 01 \\”\u8bfe\u7a0b\u5206\u6570\u5c0f\u4e8e 60\uff0c\u6309\u5206\u6570\u964d\u5e8f\u6392\u5217\u7684\u5b66\u751f\u4fe1\u606f<\/button><\/li>\n<\/ol>\n\n
select student.*\nfrom student,sc\nwhere sc.CId =\\'01\\'\nand sc.score<60\nand student.SId=sc.SId\n<\/pre>\n<\/div>\n\n\u6309\u5e73\u5747\u6210\u7ee9\u4ece\u9ad8\u5230\u4f4e\u663e\u793a\u6240\u6709\u5b66\u751f\u7684\u6240\u6709\u8bfe\u7a0b\u7684\u6210\u7ee9\u4ee5\u53ca\u5e73\u5747\u6210\u7ee9<\/button><\/li>\n<\/ol>\n\n
select\nsc.SId,sc.CId,sc.score,t1.avgscore\nfrom sc left join (select sc.SId,avg(sc.score) as avgscore\nfrom sc\nGROUP BY sc.SId) as t1 on sc.SId =t1.SId\nORDER BY t1.avgscore DESC\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u5404\u79d1\u6210\u7ee9\u6700\u9ad8\u5206\u3001\u6700\u4f4e\u5206\u548c\u5e73\u5747\u5206\uff1a\n
select sc.CId ,max(sc.score)as \u6700\u9ad8\u5206,min(sc.score)as \u6700\u4f4e\u5206,AVG(sc.score)as \u5e73\u5747\u5206,count(*)as \u9009\u4fee\u4eba\u6570,sum(case when sc.score>=60 then 1 else 0 end )\/count(*)as \u53ca\u683c\u7387,sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end )\/count(*)as \u4e2d\u7b49\u7387,sum(case when sc.score>=80 and sc.score<90 and sc.score<80 then 1 else 0 end )\/count(*)as \u4f18\u826f\u7387,sum(case when sc.score>=90 then 1 else 0 end )\/count(*)as \u4f18\u79c0\u7387\nfrom sc\nGROUP BY sc.CId\nORDER BY count(*)DESC,sc.CId asc\n<\/pre>\n<\/div>\n\n\u6309\u5404\u79d1\u6210\u7ee9\u8fdb\u884c\u6392\u5e8f\uff0c\u5e76\u663e\u793a\u6392\u540d\uff0c Score \u91cd\u590d\u65f6\u4fdd\u7559\u540d\u6b21\u7a7a\u7f3a<\/li>\n<\/ol>\n\n
select sc.CId ,@curRank:=@curRank+1 as rank,sc.score\nfrom (select @curRank:=0) as t ,sc\nORDER BY sc.score desc\n<\/pre>\n<\/div>\n15.1 \u6309\u5404\u79d1\u6210\u7ee9\u8fdb\u884c\u6392\u5e8f\uff0c\u5e76\u663e\u793a\u6392\u540d\uff0c Score \u91cd\u590d\u65f6\u5408\u5e76\u540d\u6b21<\/p>\n
\n
select sc.CId , case when @fontscore=score then @curRank when @fontscore:=score then @curRank:=@curRank+1 end as rank,sc.score\nfrom (select @curRank:=0 ,@fontage:=null) as t ,sc\nORDER BY sc.score desc\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u5b66\u751f\u7684\u603b\u6210\u7ee9\uff0c\u5e76\u8fdb\u884c\u6392\u540d\uff0c\u603b\u5206\u91cd\u590d\u65f6\u4fdd\u7559\u540d\u6b21\u7a7a\u7f3a<\/li>\n<\/ol>\n\n
select t1.*,@currank:= @currank+1 as rank\nfrom (select sc.SId, sum(score)\nfrom sc\nGROUP BY sc.SId\nORDER BY sum(score) desc) as t1,(select @currank:=0) as t\n<\/pre>\n<\/div>\n16.1 \u67e5\u8be2\u5b66\u751f\u7684\u603b\u6210\u7ee9\uff0c\u5e76\u8fdb\u884c\u6392\u540d\uff0c\u603b\u5206\u91cd\u590d\u65f6\u4e0d\u4fdd\u7559\u540d\u6b21\u7a7a\u7f3a<\/p>\n
\n
select t1.*, case when @fontscore=t1.sumscore then @currank when @fontscore:=t1.sumscore then @currank:=@currank+1 end as rank\nfrom (select sc.SId, sum(score) as sumscore\nfrom sc\nGROUP BY sc.SId\nORDER BY sum(score) desc) as t1,(select @currank:=0,@fontscore:=null) as t\n<\/pre>\n<\/div>\n\n\u7edf\u8ba1\u5404\u79d1\u6210\u7ee9\u5404\u5206\u6570\u6bb5\u4eba\u6570\uff1a\u8bfe\u7a0b\u7f16\u53f7\uff0c\u8bfe\u7a0b\u540d\u79f0\uff0c[100-85]\uff0c[85-70]\uff0c[70-60]\uff0c[60-0] \u53ca\u6240\u5360\u767e\u5206\u6bd4<\/li>\n<\/ol>\n\n
select course.CId,course.Cname,t1.*\nfrom course LEFT JOIN (\nselect sc.CId,CONCAT(sum(case when sc.score>=85 and sc.score<=100 then 1 else 0 end )\/count(*)*100,\\'%\\') as \\'[85-100]\\',\nCONCAT(sum(case when sc.score>=70 and sc.score<85 then 1 else 0 end )\/count(*)*100,\\'%\\') as \\'[70-85)\\',\nCONCAT(sum(case when sc.score>=60 and sc.score<70 then 1 else 0 end )\/count(*)*100,\\'%\\') as \\'[60-70)\\',\nCONCAT(sum(case when sc.score>=0 and sc.score<60 then 1 else 0 end )\/count(*)*100,\\'%\\') as \\'[0-60)\\'\nfrom sc\nGROUP BY sc.CId) as t1 on course.CId=t1.CId\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u5404\u79d1\u6210\u7ee9\u524d\u4e09\u540d\u7684\u8bb0\u5f55<\/li>\n<\/ol>\n\u601d\u8def\uff1a\u524d\u4e09\u540d\u8f6c\u5316\u4e3a\u82e5\u5927\u4e8e\u6b64\u6210\u7ee9\u7684\u6570\u91cf\u5c11\u4e8e3\u5373\u4e3a\u524d\u4e09\u540d\u3002<\/p>\n
\n
select *\nfrom sc\nwhere (select count(*) from sc as a where sc.CId =a.CId and sc.score <a.score )<3\nORDER BY CId asc,sc.score desc\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u88ab\u9009\u4fee\u7684\u5b66\u751f\u6570<\/li>\n<\/ol>\n\n
select sc.CId,count(*)\nfrom sc\nGROUP BY sc.CId\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u51fa\u53ea\u9009\u4fee\u4e24\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u5b66\u53f7\u548c\u59d3\u540d<\/li>\n<\/ol>\n\n
select student.SId,student.Sname\nfrom sc,student\nwhere student.SId=sc.SId\nGROUP BY sc.SId\nHAVING count(*)=2\n<\/pre>\n<\/div>\n21.\u67e5\u8be2\u7537\u751f\u3001\u5973\u751f\u4eba\u6570<\/p>\n
\n
select student.Ssex ,count(*) as \u4eba\u6570\nfrom student\nGROUP BY student.Ssex\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u540d\u5b57\u4e2d\u542b\u6709\u300c\u98ce\u300d\u5b57\u7684\u5b66\u751f\u4fe1\u606f<\/li>\n<\/ol>\n\n
select *\nfrom student\nwhere student.Sname like \\'%\u98ce%\\'\n<\/pre>\n<\/div>\n23.\u67e5\u8be2\u540c\u540d\u540c\u6027\u5b66\u751f\u540d\u5355\uff0c\u5e76\u7edf\u8ba1\u540c\u540d\u4eba\u6570<\/p>\n
\n
select *\nfrom student LEFT JOIN (select Sname,Ssex,COUNT(*)\u540c\u540d\u4eba\u6570 from Student group by Sname,Ssex) as t1\non student.Sname =t1.Sname and student.Ssex=t1.Ssex\nwhere t1.\u540c\u540d\u4eba\u6570>1\n<\/pre>\n<\/div>\n24.\u67e5\u8be2 1990 \u5e74\u51fa\u751f\u7684\u5b66\u751f\u540d\u5355<\/p>\n
\n
select *\nfrom student\nwhere YEAR(student.Sage)=1990\n<\/pre>\n<\/div>\n25.\u67e5\u8be2\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5e73\u5747\u6210\u7ee9\uff0c\u7ed3\u679c\u6309\u5e73\u5747\u6210\u7ee9\u964d\u5e8f\u6392\u5217\uff0c\u5e73\u5747\u6210\u7ee9\u76f8\u540c\u65f6\uff0c\u6309\u8bfe\u7a0b\u7f16\u53f7\u5347\u5e8f\u6392\u5217<\/p>\n
\n
select sc.CId,AVG(sc.score)\nfrom sc\nGROUP BY sc.CId\nORDER BY AVG(sc.score) desc ,sc.CId asc\n<\/pre>\n<\/div>\n26.\u67e5\u8be2\u5e73\u5747\u6210\u7ee9\u5927\u4e8e\u7b49\u4e8e 85 \u7684\u6240\u6709\u5b66\u751f\u7684\u5b66\u53f7\u3001\u59d3\u540d\u548c\u5e73\u5747\u6210\u7ee9<\/p>\n
\n
select student.SId,student.Sname,t1.avgscore\nfrom student INNER JOIN (select sc.SId ,AVG(sc.score) as avgscore from sc GROUP BY sc.SId HAVING AVG(sc.score)>85) as t1 on\nstudent.SId=t1.SId\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u8bfe\u7a0b\u540d\u79f0\u4e3a\u300c\u6570\u5b66\u300d\uff0c\u4e14\u5206\u6570\u4f4e\u4e8e 60 \u7684\u5b66\u751f\u59d3\u540d\u548c\u5206\u6570<\/li>\n<\/ol>\n\n
select student.Sname ,t1.score\nfrom student INNER JOIN (select sc.SId,sc.score\nfrom sc,course\nwhere sc.CId=course.CId\nand course.Cname=\\'\u6570\u5b66\\'\nand sc.score<60)as t1 on student.SId=t1.SId\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u6240\u6709\u5b66\u751f\u7684\u8bfe\u7a0b\u53ca\u5206\u6570\u60c5\u51b5\uff08\u5b58\u5728\u5b66\u751f\u6ca1\u6210\u7ee9\uff0c\u6ca1\u9009\u8bfe\u7684\u60c5\u51b5\uff09<\/li>\n<\/ol>\n\n
select student.SId,sc.CId,sc.score from Student left join sc on student.SId=sc.SId\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u4efb\u4f55\u4e00\u95e8\u8bfe\u7a0b\u6210\u7ee9\u5728 70 \u5206\u4ee5\u4e0a\u7684\u59d3\u540d\u3001\u8bfe\u7a0b\u540d\u79f0\u548c\u5206\u6570<\/li>\n<\/ol>\n\n
select student.Sname,course.Cname,sc.score\nfrom student , sc ,course\nwhere sc.score>=70\nand student.SId=sc.SId\nand sc.CId=course.CId\n<\/pre>\n<\/div>\n30.\u67e5\u8be2\u5b58\u5728\u4e0d\u53ca\u683c\u7684\u8bfe\u7a0b<\/p>\n
\n
select DISTINCT sc.CId\nfrom sc\nwhere sc.score <60\n<\/pre>\n<\/div>\n31.\u67e5\u8be2\u8bfe\u7a0b\u7f16\u53f7\u4e3a 01 \u4e14\u8bfe\u7a0b\u6210\u7ee9\u5728 80 \u5206\u4ee5\u4e0a\u7684\u5b66\u751f\u7684\u5b66\u53f7\u548c\u59d3\u540d<\/p>\n
\n
select student.SId,student.Sname\nfrom student ,sc\nwhere sc.CId=\\'01\\'\nand student.SId=sc.SId\nand sc.score>80\n<\/pre>\n<\/div>\n\n\u6c42\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u4eba\u6570<\/li>\n<\/ol>\n\n
select sc.CId,count(*) as \u5b66\u751f\u4eba\u6570\nfrom sc\nGROUP BY sc.CId\n<\/pre>\n<\/div>\n\n\u6210\u7ee9\u4e0d\u91cd\u590d\uff0c\u67e5\u8be2\u9009\u4fee\u300c\u5f20\u4e09\u300d\u8001\u5e08\u6240\u6388\u8bfe\u7a0b\u7684\u5b66\u751f\u4e2d\uff0c\u6210\u7ee9\u6700\u9ad8\u7684\u5b66\u751f\u4fe1\u606f\u53ca\u5176\u6210\u7ee9<\/li>\n<\/ol>\n\n
select student.*,sc.score\nfrom student ,course ,teacher ,sc\nwhere course.CId=sc.CId\nand course.TId=teacher.TId\nand teacher.Tname=\\'\u5f20\u4e09\\'\nand student.SId =sc.SId\nLIMIT 1\n<\/pre>\n<\/div>\n\n\u6210\u7ee9\u6709\u91cd\u590d\u7684\u60c5\u51b5\u4e0b\uff0c\u67e5\u8be2\u9009\u4fee\u300c\u5f20\u4e09\u300d\u8001\u5e08\u6240\u6388\u8bfe\u7a0b\u7684\u5b66\u751f\u4e2d\uff0c\u6210\u7ee9\u6700\u9ad8\u7684\u5b66\u751f\u4fe1\u606f\u53ca\u5176\u6210\u7ee9<\/li>\n<\/ol>\n\n
select student.*,t1.score\nfrom student INNER JOIN (select sc.SId,sc.score, case when @fontage=sc.score then @rank when @fontage:=sc.score then @rank:=@rank+1 end as rank\nfrom course ,teacher ,sc,(select @fontage:=null,@rank:=0) as t\nwhere course.CId=sc.CId\nand course.TId=teacher.TId\nand teacher.Tname=\\'\u5f20\u4e09\\'\nORDER BY sc.score DESC) as t1 on student.SId=t1.SId\nwhere t1.rank=1\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u4e0d\u540c\u8bfe\u7a0b\u6210\u7ee9\u76f8\u540c\u7684\u5b66\u751f\u7684\u5b66\u751f\u7f16\u53f7\u3001\u8bfe\u7a0b\u7f16\u53f7\u3001\u5b66\u751f\u6210\u7ee9<\/li>\n<\/ol>\n\n
select *\nfrom sc as t1\nwhere exists(select * from sc as t2 where t1.SId=t2.SId and t1.CId!=t2.CId and t1.score =t2.score )\n<\/pre>\n<\/div>\n36.\u67e5\u8be2\u6bcf\u95e8\u529f\u6210\u7ee9\u6700\u597d\u7684\u524d\u4e24\u540d<\/p>\n
\n
select *\nfrom sc as t1\nwhere (select count(*) from sc as t2 where t1.CId=t2.CId and t2.score >t1.score)<2\nORDER BY t1.CId\n<\/pre>\n<\/div>\n37.\u7edf\u8ba1\u6bcf\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u9009\u4fee\u4eba\u6570\uff08\u8d85\u8fc7 5 \u4eba\u7684\u8bfe\u7a0b\u624d\u7edf\u8ba1\uff09<\/p>\n
\n
select sc.CId as \u8bfe\u7a0b\u7f16\u53f7,count(*) as \u9009\u4fee\u4eba\u6570\nfrom sc\nGROUP BY sc.CId\nHAVING count(*)>5\n<\/pre>\n<\/div>\n38.\u68c0\u7d22\u81f3\u5c11\u9009\u4fee\u4e24\u95e8\u8bfe\u7a0b\u7684\u5b66\u751f\u5b66\u53f7<\/p>\n
\n
select DISTINCT t1.SId\nfrom sc as t1\nwhere (select count(* )from sc where t1.SId=sc.SId)>=3\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u9009\u4fee\u4e86\u5168\u90e8\u8bfe\u7a0b\u7684\u5b66\u751f\u4fe1\u606f<\/li>\n<\/ol>\n\n
select student.*\nfrom sc ,student\nwhere sc.SId=student.SId\nGROUP BY sc.SId\nHAVING count(*) = (select DISTINCT count(*) from course )\n<\/pre>\n<\/div>\n40.\u67e5\u8be2\u5404\u5b66\u751f\u7684\u5e74\u9f84\uff0c\u53ea\u6309\u5e74\u4efd\u6765\u7b97<\/p>\n
\n
select student.SId as \u5b66\u751f\u7f16\u53f7,student.Sname as \u5b66\u751f\u59d3\u540d,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as \u5b66\u751f\u5e74\u9f84\nfrom student\n<\/pre>\n<\/div>\n\n\u6309\u7167\u51fa\u751f\u65e5\u671f\u6765\u7b97\uff0c\u5f53\u524d\u6708\u65e5 < \u51fa\u751f\u5e74\u6708\u7684\u6708\u65e5\u5219\uff0c\u5e74\u9f84\u51cf\u4e00<\/li>\n<\/ol>\n\n
select student.SId as \u5b66\u751f\u7f16\u53f7,student.Sname as \u5b66\u751f\u59d3\u540d,TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as \u5b66\u751f\u5e74\u9f84\nfrom student\n<\/pre>\n<\/div>\n42.\u67e5\u8be2\u672c\u5468\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/p>\n
\n
select *\nfrom student\nwhere YEARWEEK(student.Sage)=YEARWEEK(CURDATE())\n<\/pre>\n<\/div>\n\n\u67e5\u8be2\u4e0b\u5468\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/li>\n<\/ol>\n\n
select *\nfrom student\nwhere YEARWEEK(student.Sage)=CONCAT(YEAR(CURDATE()),week(CURDATE())+1)\n<\/pre>\n<\/div>\n44.\u67e5\u8be2\u672c\u6708\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/p>\n
\n
select *\nfrom student\nwhere EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM CURDATE())\n<\/pre>\n<\/div>\n45.\u67e5\u8be2\u4e0b\u6708\u8fc7\u751f\u65e5\u7684\u5b66\u751f<\/p>\n
\n
select *\nfrom student\nwhere EXTRACT(YEAR_MONTH FROM student.Sage)=EXTRACT(YEAR_MONTH FROM DATE_ADD(CURDATE(),INTERVAL 1 MONTH))<\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"\u7ec3\u4e60\u6570\u636e \u6570\u636e\u8868 –1.\u5b66\u751f\u8868 Student(SId,Sname,Sage,Ssex) R …<\/p>\n
MySQL\u7b14\u8bd5\u9898<\/span> \u67e5\u770b\u5168\u6587 »<\/a><\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":""},"categories":[90,88],"tags":[],"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false,"mailpoet_newsletter_max":false,"woocommerce_thumbnail":false,"woocommerce_single":false,"woocommerce_gallery_thumbnail":false,"shop_catalog":false,"shop_single":false,"shop_thumbnail":false},"uagb_author_info":{"display_name":"weijing","author_link":"http:\/\/www.weijing.co\/?author=4"},"uagb_comment_info":0,"uagb_excerpt":"\u7ec3\u4e60\u6570\u636e \u6570\u636e\u8868 –1.\u5b66\u751f\u8868 Student(SId,Sname,Sage,Ssex) R…","_links":{"self":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1657"}],"collection":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1657"}],"version-history":[{"count":0,"href":"http:\/\/www.weijing.co\/index.php?rest_route=\/wp\/v2\/posts\/1657\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1657"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1657"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.weijing.co\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1657"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}