当前位置:网站首页>MySQL面试题——MySQL常见查询
MySQL面试题——MySQL常见查询
2022-08-10 11:59:00 【YZL40514131】
一、建表语句,并且插入数据
学生表student
create table student(
student_id int(6) primary key auto_increment,
student_name varchar(12) not null,
student_age int(3),
student_sex varchar(4) not null
);
insert into student values(1000,'科比',16,'男'),(1001,'乔丹',17,'男'),(1002,'詹姆斯',18,'男'),(1003,'库里',14,'女'),(1004,'巴特勒',14,'男'),(1005,'乔治',21,'女'),(1006,'伦纳德',21,'男'),(1007,'汤普森',19,'男');
教师表teacher
create table teacher(
teacher_id int(6) primary key auto_increment,
teacher_name varchar(5) not null,
teacher_sex varchar(4) not null,
teacher_age int(3)
);
insert into teacher values(1,'波波','男',30),(2,'斯波','女',31),(3,'科尔','男',33),(4,'里弗斯','女',28),(5,'少帅','女',27),(6,'小红','女',30),(7,'谢霆锋','女',45),(8,'吴京','男',41),(9,'张三','男',28),(10,'李四','男',35);
课程表course
create table course(
course_id int(6) primary key auto_increment,
course_name varchar(10) not null,
teacher_id int(6)
);
insert into course values(301,'高等数学',1),(302,'线性代数',2),(303,'概率论',3),(304,'计算机基础',4),(305,'计算机原理',5),(306,'原件工程',6),(307,'python',7),(308,'java',8),(309,'c',9),(310,'c++',10),(311,'shell',1),(312,'javascript',2),(313,'单片机',3),(311,'NBA',4),(312,'mysql',5),(313,'劳动课',6);
成绩表score
create table score(
student_id int(6),
course_id varchar(10) not null,
student_score int(6)
);
insert into score values(1000,301,98),(1001,302,97),(1002,303,82),(1003,304,88),(1004,305,70),(1005,306,54),(1006,307,62),(1007,308,93),(1008,309,98),(1009,310,89),(1000,311,54),(1001,312,97),(1002,313,99),(1003,314,66),(1004,315,95);
二、简单查询
(1)查询学生表所有数据
select * from student;
(2)查询姓“巴”学生
SELECT * FROM student where student_name LIKE "巴%";
(3)查询男性教师人数
select count(*) as 男性人数 from student where student_sex="男"
(4)查询男性、女性教师人数
select student_sex as 性别,count(*) as 总数 from student group by student_sex;
(5)查询各科成绩最高和最低的分,并以课程ID,课程最高分,课程最低分形式显示。
select score.course_id as 课程id,max(student_score) as 最高分,min(student_score) as 最低分
from score
group by course_id;
三、复杂查询
(6)查询各科成绩最高和最低的分,并以课程ID,课程名称,授课老师,最高分,最低分形式显示。
select course.course_id as 课程id,MAX(student_score) as 最高分,MIN(student_score) as 最低分,course_name,teacher_name
from course,teacher,score
where course.course_id=score.course_id and teacher.teacher_id=course.teacher_id
GROUP BY 课程id;
(7)查询学过“斯波老师”所教的所有课的同学的学号、姓名
1、查询斯波老师所教的课的课程id
select course.course_id
from course,teacher
where course.teacher_id=teacher.teacher_id and teacher_name='斯波';
2、查询选择该课程的学生
select student.student_id,student.student_name,score.course_id
from student,score
where student.student_id=score.student_id and score.course_id=(select course.course_id from course,teacher where course.teacher_id=teacher.teacher_id and teacher_name='斯波');
(8)查询没学过"林春雁"老师所教的课的学生的学号、姓名(重点)
先是3表联查
1、查询学过"波波"老师所教的课的学生id
select score.student_id
from score,teacher,course
where score.course_id=course.course_id and teacher.teacher_id=course.teacher_id
and teacher.teacher_name='波波';
2、查询学过"波波"老师所教的课的学生的学号、姓名
select student.student_id,student.student_name
from student
where student_id in (select score.student_id
from score,teacher,course
where score.course_id=course.course_id and teacher.teacher_id=course.teacher_id
and teacher.teacher_name='波波');
3、查询没有学过"波波"老师所教的课的学生的学号、姓名
select student.student_id,student.student_name
from student
where student_id not in (select score.student_id
from score,teacher,course
where score.course_id=course.course_id and teacher.teacher_id=course.teacher_id
and teacher.teacher_name='波波');
(9)查询学过课程ID为‘301’和‘302’的学生的学号、姓名(重点)
这道题最最最关键的所在,(划重点)就是:把学过编号为“01”课程的同学查询出来,再把学过编号为“02”课程的同学查询出来,然后把两张表合并到一张表中。
错误sql编写:
select student.student_id,student.student_name
from student,score
where student.student_id=score.student_id
and score.course_id=301 and score.course_id=302;
1、查询学过301课程的学生
select student_id
from score
where course_id=301;
2、查询学过302课程的学生
select student_id
from score
where course_id=302;
3、将查询到,学过课程编号为301的同学id,作为表a,和查询到,学过课程编号为302的同学id,作为表b, 通过内连接的方式合并
select *
from (select student_id from score where course_id=301) as a
INNER JOIN (select student_id from score where course_id=302) as b
on a.student_id=b.student_id;
4、将student_id通过student表获取学生信息。因为最终是一组数据,所以用IN关键字
select student.student_id,student.student_name
from student
where student.student_id IN(select a.student_id
from (select student_id from score where course_id=301) as a
INNER JOIN (select student_id from score where course_id=302) as b
on a.student_id=b.student_id);
(10)查询学过学号为1005同学所有课程的同学的学号
1、查询学号为1005同学所学的所有课程
select score.course_id
FROM student,score
where student.student_id=score.student_id and student.student_id=1005;
2、综合查询
select student.student_id,student.student_name,score.course_id
FROM student,score
where student.student_id=score.student_id and score.course_id = (select score.course_id
FROM student,score
where student.student_id=score.student_id and student.student_id=1005) AND student.student_id!=1005;
(11)查询不同老师所教不同课程平均分从高到低显示
SELECT course.teacher_id,course.course_name,AVG(score.student_score)
from course,score
where course.course_id=score.course_id
group by course.teacher_id
ORDER BY AVG(score.student_score) DESC;
(12)查询选修“波波”老师所授课程的学生中成绩最高的学生姓名,课程名及其成绩
1、查询“波波”老师所教的课程
select course.course_id
FROM course,teacher
where course.teacher_id=teacher.teacher_id and teacher.teacher_name='波波';
2、查询学该课程的所有学生
select student.student_name,score.student_score,score.course_id
from score,student
where student.student_id=score.student_id and score.course_id = (select course.course_id
FROM course,teacher
where course.teacher_id=teacher.teacher_id and teacher.teacher_name='波波')
ORDER BY score.student_score
DESC
LIMIT 1;
(13)查询至少有一门课与学号为“1001”的学生所学课程相同的学生的学号和姓名
1、查询学号为“1001”的学生所学课程
select score.course_id
from score
where score.student_id=1001;
2、查询所学该课程的学生
select student.student_id,student.student_name,score.course_id
from student,score
where student.student_id=score.student_id and score.course_id in (select score.course_id
from score
where score.student_id=1001);
(14)查询某门课程成绩小于等于60分的学生的学号、姓名
1、查询成绩小于等于60分的学生
select score.student_score
from score
where score.student_score<=60;
2、查询选择该课程的学生
select student.student_id,student.student_name,score.student_score
from student,score
where student.student_id=score.student_id and score.student_score in(select score.student_score
from score
where score.student_score<=60);
(15)按各科的成绩从高到低排列,以如下形式显示:课程号、课程名、平均成绩。
select course.course_id,course.course_name,AVG(score.student_score)
from course,score
where score.course_id=course.course_id
GROUP BY course.course_id
order by AVG(score.student_score)
desc;
边栏推荐
- 47Haproxy Cluster
- mpf6_Time Series Data_quandl_correct kernel PCA_AIC_BIC_trend_log_return_seasonal_decompose_sARIMAx_ADFull
- Color map and depth map to point cloud
- dedecms支持Word内容一键导入
- 查看 CUDA cudnn 版本 & 测试 cuda 和 cudnn 有效性「建议收藏」
- Custom filters and interceptors implement ThreadLocal thread closure
- AICOCO AI Frontier Promotion (8.10)
- 解决 idea 单元测试不能使用Scanner
- Samsung plans to start producing semiconductor components in Vietnam in 2023
- 苹果逆势扩大iPhone 14系列备货,总量或达9500万部
猜你喜欢
嘉为蓝鲸荣获工信部“数字技术融合创新应用解决方案”
How many constants and data types do you remember?
吃透Chisel语言.36.Chisel实战之以FIFO为例(一)——FIFO Buffer和Bubble FIFO的Chisel实现
16、Pytorch Lightning入门
LT8911EXB MIPI CSI/DSI to EDP signal conversion
Alibaba Cloud Jia Zhaohui: Cloud XR platform supports Bizhen Technology to present a virtual concert of national style sci-fi
Guo Jingjing's personal chess teaching, the good guy is a robot
CV复习:空洞卷积
培训机构学习费用是多少呢?
漏洞管理计划的未来趋势
随机推荐
嘉为蓝鲸荣获工信部“数字技术融合创新应用解决方案”
How many constants and data types do you remember?
堪称神级的阿里巴巴“高并发”教程——基础+实战+源码+面试+架构 全包了
Dining (网络流)
LeetCode 83. Remove Duplicate Elements in Sorted List
16. Getting Started with Pytorch Lightning
LeetCode 445. Adding Two Numbers II
一文读懂NFT数字藏品为何风靡全球?
LeetCode 138. Copy a linked list with random pointers
Data Analysis of Time Series (5): Simple Prediction Method
海外媒体宣发.国内媒体发稿要注意哪些问题?
【集合】HashSet和ArrayList的查找Contains()时间复杂度
CLIP还能做分割任务?哥廷根大学提出一个使用文本和图像prompt,能同时作三个分割任务的模型CLIPSeg,榨干CLIP能力...
LeetCode 82. Remove Duplicate Elements in Sorted List II
基于PLECS的离网(孤岛)并联逆变器的Droop Control下垂控制仿真
MySQL索引的B+树到底有多高?
Diary 16
可视化服务编排在金融APP中的实践
动态规划之最长回文子串
LT8911EXB MIPI CSI/DSI to EDP signal conversion