php怎么做直播网站,网站建设与管理 pdf,咸宁网站seo排名,体育新闻--1、 查询Student表中的所有记录的Sname、Ssex和Class列。SELECT sname,ssex,class FROMstudent;--2、查询教师所有的单位即不重复的Depart列SELECT DISTINCT depart FROMteacher;--3、查询Student表的所有记录。SELECT * FROMstudent;--4、查询Score表中成绩在60到80之间的所…--1、 查询Student表中的所有记录的Sname、Ssex和Class列。SELECT sname,ssex,class FROMstudent;--2、查询教师所有的单位即不重复的Depart列SELECT DISTINCT depart FROMteacher;--3、查询Student表的所有记录。SELECT * FROMstudent;--4、查询Score表中成绩在60到80之间的所有记录SELECT * FROM score WHERE degree60 AND degree80;SELECT * FROM score WHERE degree BETWEEN 60 AND 80;--5、查询Score表中成绩为8586或88的记录SELECT * FROM score WHERE degree85 OR degree86 OR degree88;--6、查询Student表中“95031”班或性别为“女”的同学记录。SELECT * FROM student WHERE class95031 OR ssex女;--7、以Class降序查询Student表的所有记录。SELECT * FROM student ORDER BY class DESC;--8、以Cno升序、Degree降序查询Score表的所有记录。SELECT * FROM score ORDER BY cno ASC,degree DESC;--9、查询“95031”班的学生人数。SELECT class,COUNT(*) FROM student GROUP BY class HAVING class95031;--10、查询Score表中的最高分的学生学号和课程号。(子查询或者排序)SELECT sno,cno FROM score WHERE degree(SELECT MAX(degree) FROMscore);SELECT sno,cno FROM score ORDER BY degree DESC LIMIT 0,1;--11、查询每门课的平均成绩。SELECT cno,AVG(degree) FROM score GROUP BYcno;--12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。SELECT AVG(degree) AS 平均数 FROM score WHERE cno LIKE 3% GROUP BY cno HAVING COUNT(*)5;--13、查询分数大于70小于90的Sno列。SELECT sno FROM score WHERE degree BETWEEN 70 AND 90;--14、查询所有学生的Sname、Cno和Degree列SELECT sname,cno,degree FROM student JOIN score ON student.snoscore.sno;--15、查询所有学生的Sno、Cname和Degree列。SELECT sno,cname,degree FROM score JOIN course ON score.cnocourse.cno;--16、查询所有学生的Sname、Cname和Degree列。SELECT sname,cname,degree FROM student JOIN score ON student.snoscore.sno JOIN course ON score.cnocourse.cno;--17、 查询“95033”班学生的平均分SELECT class,AVG(degree) FROM student JOIN score ON student.snoscore.sno GROUP BY class HAVING class95033;--18、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。SELECT * FROM student JOIN score ON student.snoscore.snoWHERE cno3-105 AND degree(SELECT degree FROM score WHERE sno109 AND cno3-105);--20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。SELECT * FROM score WHERE--21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。SELECT * FROM student JOIN score ON student.snoscore.snoWHERE degree(SELECT degree FROM score WHERE sno109 AND cno3-105)--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列SELECT sno,sname,sbirthday FROM student WHERE YEAR(sbirthday) (SELECT YEAR(sbirthday) FROM student WHERE sno108);--23、查询“张旭“教师任课的学生成绩(姓名)。SELECT sname,degree FROM teacher JOIN course ON teacher.tnocourse.tno JOIN score ON course.cnoscore.cno JOIN student ON score.snostudent.snoWHERE tname张旭;--24、查询考计算机导论的学生成绩SELECT sname,degree FROM teacher JOIN course ON teacher.tnocourse.tno JOIN score ON course.cnoscore.cno JOIN student ON score.snostudent.snoWHERE cname计算机导论;--25、查询李诚老师教的课程名称SELECT DISTINCT cname FROM teacher JOIN course ON teacher.tnocourse.tno JOIN score ON course.cnoscore.cno JOIN student ON score.snostudent.snoWHERE tname李诚;--26、教高等数学的老师是哪个系的SELECT depart FROM teacher RIGHT JOIN course ON teacher.tnocourse.tno LEFT JOIN score ON course.cnoscore.cno LEFT JOIN student ON score.snostudent.snoWHERE cname高等数学;--27、查询选修某课程的同学人数多于5人的教师姓名。SELECT DISTINCT tname FROM teacher RIGHT JOIN course ON teacher.tnocourse.tno LEFT JOIN score ON course.cnoscore.cno LEFT JOIN student ON score.snostudent.snoWHERE course.cno(SELECT cno FROM score GROUP BY cno HAVING COUNT(*)5);--28、查询95033班和95031班全体学生的记录。SELECT * FROM teacher RIGHT JOIN course ON teacher.tnocourse.tno LEFT JOIN score ON course.cnoscore.cno LEFT JOIN student ON score.snostudent.snoWHERE class95031 OR class95033;--29、 查询存在有85分以上成绩的课程CnoSELECT DISTINCT score.cno FROM teacher RIGHT JOIN course ON teacher.tnocourse.tno LEFT JOIN score ON course.cnoscore.cno LEFT JOIN student ON score.snostudent.snoWHERE degree85;--30、查询出“计算机系“教师所教课程的成绩表。SELECT degree FROM score WHERE cno IN (SELECT cno FROM teacher JOIN course ON teacher.tnocourse.tno WHERE depart计算机系);--31、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。SELECT sno,cno,degree FROM score WHERE degreeALL(SELECT degree FROM score WHERE cno3-245) AND cno3-105 ORDER BY degree DESC;--32、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的 Cno、Sno和Degree.SELECT sno,cno,degree FROM score WHERE degreeANY(SELECT degree FROM score WHERE cno3-245) AND cno3-105;--33、查询所有教师和同学的name、sex和birthdaySELECT tname AS name,tsex AS sex,tbirthday AS birthday FROMteacherUNIONSELECT sname AS name,ssex AS sex,sbirthday AS birthday FROMstudent;--34、查询所有“女”教师和“女”同学的name、sex和birthdaySELECT tname AS name,tsex AS sex,tbirthday AS birthday FROM teacher WHERE tsex女UNIONSELECT sname AS name,ssex AS sex,sbirthday AS birthday FROM student WHERE ssex女;--35、查询成绩比该课程平均成绩低的同学的成绩表。SELECT * FROM score a WHERE degreeSELECT tname,depart FROMteacher;--37、查询所有未讲课的教师的Tname和Depart--38、查询至少有2名男生的班号。SELECT class FROM student WHERE ssex男 GROUP BY class HAVING COUNT(*)1;--39、查询Student表中不姓“王”的同学记录。SELECT * FROM student WHERE sname NOT LIKE王%;--40、查询Student表中每个学生的姓名和年龄。SELECT sname,YEAR(NOW())-YEAR(sbirthday) FROMstudent;--41、查询Student表中最大和最小的Sbirthday日期值。SELECT MAX(sbirthday),MIN(sbirthday) FROMstudent;--42、以班号和年龄从大到小的顺序查询Student表中的全部记录。SELECT * FROM student ORDER BY class DESC,sbirthday ASC;--43、查询“男”教师及其所上的课程。SELECT tname,cname FROM teacher JOIN course ON teacher.tnocourse.tno WHERE tsex男;--44、查询最高分同学的Sno、Cno和Degree列。SELECT sno,cno,degree FROM score WHERE degree(SELECT MAX(degree) FROMscore);--45、查询和“李军”同性别的所有同学的Sname.SELECT sname FROM student WHERE ssex(SELECT ssex FROM student WHERE sname李军)AND sname (李军);--46、查询和“李军”同性别并同班的同学Sname.SELECT sname FROM student WHERE ssex(SELECT ssex FROM student WHERE sname李军)AND class(SELECT class FROM student WHERE sname李军)AND sname (李军);--47、查询所有选修“计算机导论”课程的“男”同学的成绩表。SELECT sname,degree FROMstudentJOIN score ON student.snoscore.snoJOIN course ON score.cnocourse.cno WHERE cname计算机导论 AND ssex男;
