MySQL第五次作业-查询数据

1. 查询教师表中教师号在T5到T10之间的所有字段数据

select *from zhongzheng_teacher where NO between “T10” and “T5”;

2. 查询授课表中 周数为14 的 课程号、教师号、周数、教室号 字段数据

select *from zhongzheng_school_teaching where week=15;

3. 查询授课表中去重后的所有教师号

select distinct teacher_no from zhongzheng_school_teaching;

4. 查询授课表中教室在J栋的所有字段数据，取2-3条

select * from zhongzheng_school_teaching where class_num like “j%”
limit 1,2;

5. 查询课程表中课时数在45，30，50中的所有字段数据

select * from zhongzheng_course where class_hours in(“45”,“30”,“50”);

6. 查看教师表中工资不等于3000的 姓名、工资、岗位津贴 字段数据

select name,prof,sal,comm from zhongzheng_teacher where sal!=“3000”;

7. 查询教师表中岗位津贴小于1500的 姓名、职称、工资、岗位津贴 字段数据

select * from zhongzheng_teacher where comm<“1500”;

8. 查询教师表中岗位津贴为空的所有字段数据，按工资从小到大排序

select * from zhongzheng_teacher where comm is null order by sal asc;

9. 查询课程表中课程号在C3到C7之间的所有字段数据，按课时数从大到小排序

select *from zhongzheng_course where no between “C3” and “C7” order by
class_hours desc;

10. 查询授课表中教室在Y栋2楼的 课程号、教师号、教室号 字段数据，取第一条

select id,course_no,teacher_no,week from zhongzheng_school_teaching
where class_num like “y2%” limit 1;

11. 查询教师表中 工资为2000 的 教师号、职称、工资 字段数据

select name,prof,sal from zhongzheng_teacher where sal=“2000”;

12. 查询教师表中去重后的所有职称

select distinct prof from zhongzheng_teacher;

13. 查询授课表中教室号在J102，Y104，Y303，J301中的所有字段数据

select * from zhongzheng_school_teaching where class_num
in(“J102”,“y102”,“y303”,“J302”);

14. 查看课程表中课时数不等于45的 课程号、课程名、周数 字段数据，按周数从小到大排序，取前两条。

select * from zhongzheng_course where class_hours!=45 limit 2;

15. 查询授课表中周数大于等于50的 课程号、教师号 字段数据

select course_no,teacher_no from zhongzheng_school_teaching where
week>=50;

16. 查询课程表中课程名称以字母“M”开头并且课程名称中有“L”字母的所有字段数据

select * from zhongzheng_course where name like “m%l%”;