MySQL第六次作业-查询数据-多条件

1. 查询教师表中T2到T9之间工资大于2000并且有岗位津贴的教师所有字段数据

SELECT * FROM zhongzheng_teacher where no between “T2” and “T9” and
sal>2000 and comm is not null;

2. 查询教师表中教师姓名不以字母“A”开头的教师所有字段数据，用工资排序，去前三条

SELECT * FROM zhongzheng_teacher where name not like"a%" order by sal
limit 3;

3. 查询教师表中工资等于2000或者岗位津贴大于等于1000的教师所有字段数据

SELECT * FROM zhongzheng_teacher where sal=2000 or comm>1000;

4. 查询教师表中教师姓名以字母“d”开头并且含有”a”字母的教师号、教师姓名字段数据

SELECT no,name FROM zhongzheng_teacher where name like"d%a%";

5. 查询教师表中教师姓名不以字符“e”开头的并且没有岗位津贴的教师号、教师姓名、工资、岗位津贴字段数据，用工资排序

SELECT no,name,sal,comm from zhongzheng_teacher where name not
like"e%" and comm is not null order by sal asc;

6. 查询课程表中带字母”L”的并且课时数大于等于50的课程名称、课时数字段数据

SELECT name,class_hours FROM zhongzheng_course where name like"%l%"
and class_hours>=50;

7. 查询课程表中课程号在C5到C7之间或者没有课时数的所有字段数据

SELECT * FROM zhongzheng_course where no between “C5” and “C7” or
class_hours is null;

8. 查询课程表中课时数不等于45并且课程名以“sh”结尾的课程名称、课时数字段数据

SELECT name,class_hours FROM zhongzheng_course where class_hours!=45
and name like “%sh”;

9. 查询课程表中课程号不在C3、C5、C6中或者课时数等于45的所有字段数据，用课时数排序

SELECT * FROM zhongzheng_course where no not in(“C3”,“C5”,“C6”) or
class_hours=45 order by class_hours asc;

10. 查询授课表中教室在Y栋2楼的所有课程号、教师号、教室号字段数据, 用周数排序，取前两条

SELECT * FROM zhongzheng_school_teaching where class_num like"Y2%"
order by week limit 2;

11. 查询授课表中序号在2到9之间或者周数为15的序号、教室号、周数字段数据

SELECT id,teacher_no,week FROM zhongzheng_school_teaching where id
between “2” and “9” or week=15;

12. 查询授课表中教师号在T2到T9之间并且教室在J栋的教师号、教室号字段数据, 用周数排序

SELECT course_no,teacher_no,week FROM zhongzheng_school_teaching where
teacher_no between “t2” and “t9” and class_num like “J%” order by week
asc;

13. 查询授课表中周数大于14或者课程号在C3、C5、C6中的所有字段数据

SELECT * FROM zhongzheng_school_teaching where week>14 or course_no
in(“C3”,“c5”,“c6”);

14. 查询授课表中周数不为空并且周数大于等于50的课程号、教室号、周数字段数据

SELECT course_no,teacher_no,week FROM zhongzheng_school_teaching where
week is not null and week>50;

15. 查询授课表中教室在一楼或者教室在三楼的所有字段数据, 用周数排序

SELECT * FROM zhongzheng_school_teaching where class_num like “_1%” or
class_num like “_3%” order by week asc;