sql是测试从业者必备的技能之一,基本上也是笔试必考内容。

所以,不要让sql拖了后腿,有些测友一遇到多表关联查询就犯晕,甚至连单表的执行顺序都没搞懂,下面简单介绍下,顺便给一些题供大家练习。

单表执行顺序

select distinct 字段1,...,字段n from 库.表
where 条件
group by 分组字段
having 过滤
order by 排序字段
limit n;

执行顺序

from
where
group by
having
select
distinct
order by
limit

多表关联的几个概念

交叉连接:无任何匹配条件,生成笛卡尔积

内连接:共同部分

左连接:在内连接的基础上保留左表的记录

右连接:在内连接的基础上保留右表的记录

全外连接:在内连接的基础上保留左右两表没有对应关系的记录

练习题表结构

创建表

班级表

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
`cid` int(11) NOT NULL AUTO_INCREMENT,
`caption` varchar(255) NOT NULL,
`grade_id` int(11) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

年级表

DROP TABLE IF EXISTS `class_grade`;
CREATE TABLE `class_grade` (
`gid` int(11) NOT NULL AUTO_INCREMENT,
`gname` varchar(255) NOT NULL,
PRIMARY KEY (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

课程表

DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`cid` int(11) NOT NULL,
`cname` varchar(255) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

成绩表

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`student_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`score` varchar(255) DEFAULT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

学生表

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`sid` int(11) NOT NULL AUTO_INCREMENT,
`sname` varchar(255) NOT NULL,
`gender` enum('女','男') NOT NULL DEFAULT '男',
`class_id` int(11) NOT NULL,
PRIMARY KEY (`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

老师表

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL,
`tname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

班级任职表

DROP TABLE IF EXISTS `teacher2cls`;
CREATE TABLE `teacher2cls` (
`tcid` int(11) NOT NULL AUTO_INCREMENT,
`tid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
PRIMARY KEY (`tcid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8; 

关于表数据,建议自己造,加深对表结构的理解。

常见笔试题

01、查询同时选修了物理课和生物课的学生id和姓名

02、查询“2”课程分数小于60,按分数降序排列的同学学号

03、查询有两门及以上课程超过60分的学生id及其平均成绩

04、查询没有带过任何班级的老师id和姓名

05、查询没有学生选修的课程的课程号和课程名

06、查询至少选修两门课程的学生学号

07、查询各个课程及相应的选修人数

08、查询课程编号为“2”且课程成绩在80分以上的学生的学号和姓名

09、查询课程名称为“生物”,且分数低于60的学生姓名和分数

10、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列

11、查询同名同姓学生名单,并统计同名人数

12、查询男生、女生的人数,按倒序排列;

13、查询选修了2门以上课程的全部学生的学号和姓名

14、查询每门课程被选修的学生数

15、查询至少学过学号为“1”同学所选课程中任意一门课的其他同学学号和姓名

16、查询没有学全所有课的同学的学号、姓名

17、查询有课程成绩小于60分的同学的学号、姓名

18、查询所带班级数最多的老师id和姓名

19、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名

20、查询带过超过2个班级的老师的id和姓名

21、查询没有带过高年级的老师id和姓名

22、查询学过编号‘1’课程和编号‘2’课程的同学的学号、姓名

23、查询教授课程超过2门的老师的id和姓名

24、查询学过‘张老师’老师2门课以上的同学的学号、姓名

25、查询班级信息,包括班级id、班级名称、年级、年级级别(12为低年级,34为中年级,56位高年级)

26、查询班级数小于5的年级id和年级名

27、查询每位学生的学号,姓名,选课数,平均成绩

28、查询每个年级的学生人数

29、查询每个年级的班级数,取出班级数最多的前三个年级

30、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名

补充:查询成绩表里不同课程的成绩倒数5名

参考答案

01:

select s.sid,s.sname from student s
where s.sid in(
select s.student_id from score s
join course c on c.cid = s.course_id
where c.cname in ('体育','生物')
group by s.student_id
having count(s.student_id)=2
);

...

最好先自己做一遍,如需要参考答案,请微信联系作者。

bak:https://www.cnblogs.com/uncleyong/p/14757684.html

最新原文:https://www.cnblogs.com/uncleyong/p/14758383.html

最新文章

  1. MySql 获取表的字段名
  2. Python中的map( )和reduce( )
  3. MongoDB学习系列(1)--入门介绍
  4. 使用 Spring Boot 快速构建 Spring 框架应用--转
  5. HDOJ(1348)二维凸包
  6. 利用yum下载软件包的三种方法
  7. Windows下查看进程及结束进程命令[转]
  8. SQL Server代理服务无法启动的处理方法(转载)
  9. MVC4过滤器(转)
  10. MFC 可以设置背景色、字体、字体颜色、透明背景的 Static 静态文本控件
  11. NYOJ-1057 寻找最大数(三)(贪心)
  12. python中关于元组的操作
  13. [机器学习实战]K-近邻算法
  14. 【运维】浪潮服务器一块硬盘显示红色Offline(或者Failed)解决办法
  15. Java类的5个加载步骤
  16. poj1511,线段树扫描线面积
  17. CloudStack+KVM环境搭建(步骤很详细,说明ClockStack是用来管理虚拟机的)
  18. 为什么你的session不见了
  19. Vue--- 手动禁止ESlint
  20. ionic路由(页面切换)

热门文章

  1. Javascript——ES6( ECMAScript 6.0)语法
  2. Javascript实现让小图片一直跟着鼠标移动
  3. 热词cloud-EChart安装
  4. 【刷题-PAT】A1112 Stucked Keyboard (20 分)
  5. nacos集群开箱搭建
  6. vue文档1-VSCode介绍
  7. lua之自索引
  8. java多态instanceof介绍
  9. 用8个命令调试Kubernetes集群
  10. Java多线程专题6: Queue和List