自己做外贸网站能接到单吗,北京建工集团有限公司官网,flash网站规划,学校网站php源码---恢复内容开始--- 1,用于设定所select出来的数据是否允许出现重复行#xff08;完全相同的数据行#xff09; all#xff1a;允许出现——默认不写就是All#xff08;允许的#xff09;。 distinct#xff1a;不允许出现——就是所谓的“消除重复行” 2#xff0c;whe…---恢复内容开始--- 1,用于设定所select出来的数据是否允许出现重复行完全相同的数据行 all允许出现——默认不写就是All允许的。 distinct不允许出现——就是所谓的“消除重复行” 2where:条件 3group by分组依据 后面加表的字段名通常只进行一个字段的分组 mysql表查询语法形式select [all | distinct] 字段名或表达式 from 表名 [where] [group by] [having] [order by] [limit]; 练习题:共有下面四张表 学生表student 教师表teacher 课程表course 成绩表score 1查询Score表中至少有5名学生选修的并以3开头的课程的平均分数 --操作表score以cno分组并且cno是以3开头取出各个cno的总数及与cno对应的degree的平均值 select count(cno),avg(degree) from score where cno like 3% group by cno; --从上面的虚拟表中找到cno总数大于5的 select * from (select count(cno) a,avg(degree) b from score where cno like 3% group by cno) c where a5; 2,查询所有学生的Sno、Cname和Degree列 --找到student的sno select sno from student; --找到score 的sno,degree,cno select sno,degree,cno from score; --找到course的cno select cno,cname from course; --组成新表cno sno degree select a.cno,b.sno,b.sname,a.degree from (select sno,degree,cno from score) a join (select sno,sname from student) b on a.snob.sno; --组成有cname cno sn degree sname的表 select d.cname,e.cno,e.sno,e.degree,e.sname from (select a.cno,b.sno,b.sname,a.degree from (select sno,degree,cno from score) a join (select sno,sname from student) b on a.snob.sno) as e join (select cname,cno from course) as d on d.cnoe.cno; 3查询“95033”班学生的平均分 --从student取sno class,条件是class是95033的 select sno,class from student where class95033; --取出score中sno degree select sno,degree from score; --将上面两张表组成一张,取degree的平均值 select avg(degree) from (select sno,class from student where class95033) a join (select sno,degree from score) b on a.snob.sno; 4,查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录 --将109号的成绩取出 select degree from score where sno109 and cno3-105; -- 得出最终表 select * from score where degree(select degree from score where sno109 and cno3-105) and cno3-105; 5,查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列 --找到student中sno为108 的Sbirthdayselect year(sbirthday) from student where sno108; -- 得出最终表select sno,sname,sbirthday from student where year(sbirthday)(select year(sbirthday) from student where sno108); 6.查询“张旭“教师任课的学生成绩姓名 --找到teacher中tname为张旭的tno select tno from teacher where tname张旭; --找到course中tno和teacher中tname为张旭的tno相同的cno select cno from course where tno(select tno from teacher where tname张旭); --找到score中cno为6-166的sno cno degree select sno,cno,degree from score where cno(select cno from course where tno(select tno from teacher where tname张旭)); --找到student表中与上表sno相同的sname与上表组成新表 select a.sname,b.sno,b.cno,b.degree from (select sno,sname from student) a join (select sno sno,cno,degree from score where cno(select cno from course where tno(select tno from teacher where tname张旭))) b on a.snob.sno; 7,查询考计算机导论的学生成绩--找到course中cname为计算机导论的cnoselect cno from course where cname计算机导论; --找到score中cno与上表中相同时的sno degreeselect sno,degree from score where cno(select cno from course where cname计算机导论); -- 得出最终表select b.sname,a.sno,a.degree from (select sno,degree from score where cno(select cno from course where cname计算机导论)) a join (select sname,sno from student) b on a.snob.sno; 8,查询李诚老师教的课程名称select tno from teacher where tname李诚;select cname,cno from course where tno(select tno from teacher where tname李诚); 9,查询选修某课程的同学人数多于5人的教师姓名--score以cno分组统计cno的数量select count(cno) a,cno from score group by cno; --找到上表中cno数量大于5的的cnoselect cno from (select count(cno) a,cno from score group by cno) b where a5; --找到与上表cno一致的表course表中的tnoselect tno from course where cno(select cno from (select count(cno) a,cno from score group by cno) b where a5); --与上表tno一致的表teacher中的tnameselect tname from teacher where tno(select tno from course where cno(select cno from (select count(cno) a,cno from score group by cno) b where a5)); 10,查询95033班和95031班全体学生的记录--找到表score中的sno和cno 和degreeselect sno,cno,degree from score;--将上表和student组合select a.sno,a.sname,a.ssex,a.sbirthday,a.class,b.cno,b.degree from (select * from student) a join (select cno,degree,sno from score) b on a.snob.sno; 11,查询存在有85分以上成绩的课程Cnoselect cno,degree from score where degree85; 12,查询出“计算机系“教师所教课程的成绩表--找到teacher中的tno tnameselect tno,tname from teacher where dapart计算机系;--找到course中tno和上表相同的cnoselect a.cno,b.tno,b.tname from (select tno,cno from course) a join (select tno,tname from teacher where dapart计算机系) b on a.tnob.tno;--找到score中cno与上表相同的degreeselect c.sno,c.cno,c.degree,d.tname from (select sno,cno,degree from score) c join (select a.cno,b.tno,b.tname from (select tno,cno from course) a join (select tno,tname from teacher where dapart计算机系) b on a.tnob.tno) d on c.cnod.cno; 13,查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学 的Cno、Sno和Degree,并按Degree从高到低次序排序--score中找到cno3-245的degree,取出最小值select min(degree) from score where cno3-245;--score中找到cno3-105的cno sno degree并且degree大于上表的degreeselect cno,sno,degree from score where cno3-105 and degree(select min(degree) from score where cno3-245) order by degree desc; 14,查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的 Cno、Sno和Degree.--找到score中cno3-245中的degree的最大值select max(degree) from score where cno3-245;--找到score中cno3-105的cno sno degreeselect cno,sno,degree from score where degree(select max(degree) from score where cno3-245) order by degree asc; 15,查询所有教师和同学的name、sex和birthdayselect tname,tsex,tbirthday from teacherunionselect sname,ssex,sbirthday from student;16,查询所有“女”教师和“女”同学的name、sex和birthdayselect tname,tsex,tbirthday from teacher where tsex女unionselect sname,ssex,sbirthday from student where ssex女; 17,查询成绩比该课程平均成绩低的同学的成绩表select avg(degree) from score where cno3-105;select avg(degree) from score where cno3-245;select avg(degree) from score where cno6-166;select * from score where cno3-105 and degree(select avg(degree) from score where cno3-105)unionselect * from score where cno3-245 and degree(select avg(degree) from score where cno3-245)unionselect * from score where cno6-166 and degree(select avg(degree) from score where cno6-166); 18,查询所有任课教师的Tname和Departselect a.tname,a.dapart from (select tno,tname,dapart from teacher) a join (select tno from course) b on a.tnob.tno; 19,查询所有未讲课的教师的Tname和Departselect a.tname,a.dapart from (select tno,tname,dapart from teacher) a join (select tno from course) b on a.tnob.tno; 20,查询至少有2名男生的班号select count(ssex),class from student where ssex男 group by class; select b.class from (select count(ssex) a,class from student where ssex男 group by class) b where a2; 21,查询Student表中不姓“王”的同学记录select * from student where sname not like 王%; 22,查询Student表中每个学生的姓名和年龄select sname,year(now())-year(sbirthday) from student; 23,查询Student表中最大和最小的Sbirthday日期值select min(day(sbirthday)),max(day(sbirthday)) from student; 24,以班号和年龄从大到小的顺序查询Student表中的全部记录select * from student order by class desc,date(sbirthday) asc; 25,查询“男”教师及其所上的课程select tno from teacher where tsex男;select a.tno,b.cname,a.tname from (select tno,tname from teacher where tsex男) a join (select * from course) b on a.tnob.tno; 转载于:https://www.cnblogs.com/wfc139/p/8939605.html
