(1)查询年龄姓张,并且年龄为20,22,24,26之一的记录
select stuname ,age from studentinfo where stuname like'张%' and age in(20,22,24,26)
(2)查询姓张的id大于4并且年龄为20,22,24,26之一的记录
select stuname, stuid ,age from studentinfo where stuname like'张%' and stuid>4 and age in(20,22,24,26);
(3)查询姓张的或者id大于4并且年龄20,22,24,26之一的记录
select stuname, stuid ,age from studentinfo where stuname like'张%' or stuid>4 and age in(20,22,24,26);
(4)根据两个表查询查询学生的姓名,年龄,地址,出成绩这些信息
select s.stuname, s.age ,s.city ,e.examcj from studentinfo as s,exam as e where s.stuid=e.stuid;
(5)查询学生来自两个表的全部信息
SELECT*FROM studentinfo a,exam b WHERE a.stuId = b.stuid
(6)查询满足连接条件的所有记录
select *from studentinfo a inner join exam b on a.stuid=b.stuid;
(7)满足连接条件的所有信息 左边没满足的也显示出来
select * from studentinfo a left join exam e on a.stuid=b.stuid
(8)满足连接条件的所有信息 右边没满足的也显示出来
select *from studentinfo as a right join exam as e on a.stuid=b.stuid
(9)查询所有人的id,姓名,成绩和科目信息
select s.stuid ,s.stuname,e.examcj, k.subject FROM studentinfo a INNER JOIN exam b on a.stuId = b.stuid INNER JOIN `subject` c on b.subjectid = c.subId
(10)查询所有及格人的id,姓名,成绩和科目信息
select s.stuid ,s.stuname,e.examcj, k.subject FROM studentinfo a INNER JOIN exam b on a.stuId = b.stuid INNER JOIN `subject` c on b.subjectid = c.subId where examcj>60
(11)查询所有及格人的id,姓名,成绩和科目信息,并按照成绩排序
select s.stuid ,s.stuname,e.examcj, k.subject FROM studentinfo a INNER JOIN exam b on a.stuId = b.stuid INNER JOIN `subject` c on b.subjectid = c.subId where examcj>60 order by examcj asc
(12)查询所有及格人的id,姓名,成绩和科目信息,并按照成绩,姓名升序排序
select s.stuid ,s.stuname,e.examcj, k.subject FROM studentinfo a INNER JOIN exam b on a.stuId = b.stuid INNER JOIN `subject` c on b.subjectid = c.subId where examcj>60 order by examcj asc,s.stuname asc
数据库课后练习
最新推荐文章于 2022-08-05 16:57:40 发布
本文档详细介绍了如何使用SQL查询数据库,包括筛选姓张的学生年龄在20到26岁之间,ID大于4的记录,以及进行多表联接获取姓名、年龄、地址和成绩等信息。还涵盖了连接条件的多种操作,如内连接、左连接和右连接,并按成绩排序。
摘要由CSDN通过智能技术生成