MySQL年龄在25或30或35_MYSQL查询语句（No.10）--1：查询全体学⽣的学号和姓名
select sno, sname from student;
--2：查询全体学⽣的姓名、学号和所在系
select sno, sname,sdept from student;
--3:  查询全体学⽣的详细记录
select * from student
--4: 查询全体学⽣的姓名及其出⽣年份
mysql语句分类
select sname, 2011-sage as 出⽣年份 from student;
--5：查询全体学⽣姓名、出⽣年份和所在系，要求⽤⼩写字母表⽰所有系名
select sname, 2011-sage as 出⽣年份,lower(sdept) from student;
--6：查询选修了课程的学⽣学号
select distinct sno from sc;
--7：查询计算机系(IS)所有学⽣的名单
select sname from student where sdept = "is";
--8：查询所有年龄在20以下学⽣的姓名和年龄
select sname ,sage from student where sage <20;
--9:  查询考试成绩有不及格的学⽣的学号
unix操作系统目前属于哪个公司
select distinct sno from sc where grade < 60;
--10: 查询年龄在20-23 (包括20和23)之间的学⽣的姓名、系别和年龄
select sname,sdept,sage from student where sage>=20 and sage<=23;
--11: 查询信息系(IS)、数学系(MA)和计算机科学系(CS)学⽣的姓名和性别
select sname ,ssex from student where sdept = 'is' or sdept='ma' or sdept = 'CS'；或
select sname,ssex from student where sdept in("is","ma","cs");
--12: 查询学号为95001的学⽣的详细情况
select * from student where sno = 95001;
--13: 查询所有姓林的学⽣的姓名、学号和性别
select sname,sno,ssex from student where sname like "林%";
--14: 查询姓“欧阳”且全名为三个汉字的学⽣的姓名
select sname from student where sname like "欧阳_";
--15：查询名字中第⼆个字为“燕”字的学⽣姓名和学号
select sname,sno from student where sname like "_燕%";
--16：查询所有不姓“刘”的学⽣的姓名
select sname from student where sname not like "^刘%";
-
-17：查询课程名为“DB_DESIGN”的课程号的学分
select ccredit from course where cname = "db_design";
怎么在ps里面切图--18：查询缺少成绩的学⽣的学号和相应的课程号(成绩字段值为Null)
select sno,cno from sc where grade<=>null;
--19: 查询所有有成绩的学⽣的学号和课程号
select sno ,cno from sc ;
--20: 查询所有计算机系年龄在20以下的学⽣姓名
select sname from student where sage<20 and sdept="cs";
--21: 查询选修了3号课程的学⽣的学号和成绩，查询结果按分数降序排列
select sno,grade from sc where cno =3 order by grade desc;
生活中的常见常量元素--22: 查询全体学⽣情况，查询结果按所在系的系号升序排列，同⼀系中的学⽣按年龄降序排列
select * from student order by sdept,sage desc;
--23: 查询学⽣总⼈数
select count(sno) from student;
--24: 查询选修了课程的学⽣⼈数
select count(distinct sno) from sc;
--25: 计算1号课程的学⽣的平均成绩
select avg(grade) from sc where cno = 1;
--26: 计算1号课程的学⽣的最⾼成绩分数
select max(grade) from sc where cno =1;
--27：求各个课程号及相应的选课⼈数
select distinct cno,count(sno) from sc group by cno;
-
-    查询每个学⽣选修的的课程数
select sno,count(sno) from sc group by sno;
--28:  查询选修了三门以上课程的学⽣学号
select sno from sc group by sno having count(sno)>3;
select sno,count(cno) as kcnum from sc group by sno having kcnum>3;
--29：查询每个学⽣及其选修课情况
select sno,sname,sage ,sdept ,grade from student left join sc on student.sno=sc.sno;
--30：查询每⼀门课的间接先⾏课
select cno, (select c2.cpno from course as c2 where c2o=c1.cpno ) as 间接先修课程 from course as c1; --31：选修2号课程且成绩在90以上的学⽣的学号和姓名
select student.sno,sname from student,sc where student.sno = sc.sno and cno=2 and grade>=90;
/
/join连接 select s.sno,sname from student as s join sc on s.sno =sc.sno where cno=2 and grade>=90;
--32：查询每个学⽣的学号、姓名、选修的课程名及成绩
select sno,sco,cname from sc join course using(cno);
select s.sno,sname,sage,cname,grade from student as s left join(select sno,sco,cname from sc join course using(cno))as t on s.sno=t.sno;编程培训入门班
--33：查询与’林燕芳’在同⼀个系学习的学⽣姓名
select sname from student where sdept=(select sdept from student where sname="林燕芳") and sname !="林燕芳";
--34: 查询其他系中⽐信息系某⼀学⽣⼩的学⽣姓名和年龄
select sname,sage from student where sage
constellation手机
--35：查询所有选修了1号课程的学⽣的学⽣姓名
select student.sname from student,sc where student.sno=sc.sno and sco=1;
//select sname from student where sno in (select sno from sc where cno=1);
--36：查询选修了全部课程的学⽣姓名
select sname from student where sno in(select sno from sc group by sno having count(cno)=(select count(*) from course));

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。