mysql查表的数据长度 mysql查表数量

转载

mob6454cc73e9a6 2024-06-12 19:35:00

文章标签 mysql查表的数据长度数据库 mysql sql ci 文章分类 MySQL 数据库

现在有很多人都会Mysql的基本操作（Create,Retrive,Update,Delete）,但是涉及到多表联查，就完全没有头绪，做完下面58道练习，轻松掌握多表联查，学习内联，外联，普通联查，子查询，分组查询，内置函数等。

表结构

CREATE TABLE `student` (
  `s_id` int NOT NULL,
  `s_name` varchar(20) NOT NULL,
  `s_birth` varchar(20) DEFAULT NULL,
  `s_sex` varchar(2) DEFAULT NULL,
  `class` varchar(50) DEFAULT NULL,
  `profess` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `score` (
  `s_id` int NOT NULL COMMENT '学生编号',
  `c_id` varchar(10) NOT NULL COMMENT '课程编号',
  `s_score` int NOT NULL COMMENT '分数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `teacher` (
  `t_id` int NOT NULL COMMENT '教师编号',
  `t_name` varchar(20) NOT NULL COMMENT '教师姓名',
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


CREATE TABLE `course` (
  `c_id` varchar(10) NOT NULL COMMENT '课程编号',
  `c_name` varchar(20) NOT NULL COMMENT '课程名称',
  `t_id` int NOT NULL COMMENT '教师编号'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

练习

-- 查询01课程比02课程成绩高的学生信息
	SELECT stu.s_id, stu.s_name, s1.s_score FROM student stu, score s1, score s2
	WHERE stu.s_id = s1.s_id
	AND stu.s_id = s2.s_id
	AND s1.c_id = '01'
	AND s2.c_id = '02'
	AND s1.s_score > s2.s_score;
	
	-- 查询每门功成绩最好的前两名 
	select s1.c_id,s1.s_id,s1.s_score from score s1 left join score s2 on s1.c_id = s2.c_id
	and s1.s_score < s2.s_score
	group by s1.c_id,s1.s_id
	having count(s1.c_id) < 2
	ORDER BY s1.c_id ASC,
	s1.s_score DESC;
	
	-- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。
select stu.s_id,stu.s_name, sum(s1.s_score) / count(*) '平均成绩' from student stu,score s1
where stu.s_id = s1.s_id
group by s1.s_id
having sum(s1.s_score) / (select count(*) from student) >= 60;

-- 4.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩。(包括有成绩的和无成绩的)
select stu.s_id,stu.s_name,sum(s1.s_score) / count(*) '平均成绩' from student stu, score s1
where stu.s_id = s1.s_id
group by s1.s_id
having sum(s1.s_score) / (select count(*) from student) < 60;

-- 5.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩。
select stu.s_id '编号',stu.s_name '姓名',count(sc.c_id) '选课总数',sum(sc.s_score) '总成绩' 
from student stu,score sc
where stu.s_id = sc.s_id
group by stu.s_id;

-- 6.查询"李"姓老师的数量。
select count(t_id) from teacher where t_name like '李%';

-- 7.查询学过"张三"老师授课的同学的信息。
SELECT s_id, s_name FROM student
WHERE s_id IN (
	SELECT sc.s_id FROM score sc, course c, teacher t
	WHERE sc.c_id = c.c_id
	AND c.t_id = t.t_id
	AND t.t_name = '张三'
);

-- 8.查询没学过"张三"老师授课的同学的信息。
select stu.s_id,stu.s_name from student stu
where stu.s_id not in (
 select sc.s_id from score sc, course c, teacher t
 where sc.c_id = c.c_id
 and c.t_id = t.t_id
 and t.t_name='张三'
);

-- 9.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息。
select stu.s_id,stu.s_name from student stu,score sc1,score sc2
where stu.s_id = sc1.s_id
and stu.s_id = sc2.s_id
and sc1.c_id = '01'
and sc2.c_id = '02';

-- 10.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。
select stu.s_id,stu.s_name from student stu, score sc
where stu.s_id = sc.s_id
and sc.c_id = '01'
and stu.s_id not in (
 select s_id from score where c_id = '02'
);

-- 11.查询没有学全所有课程的同学的信息 。
select stu.s_id,stu.s_name from student stu
where stu.s_id not in
(
 select s_id from score
 group by s_id
 having count(c_id) = (select count(c_id) from course)
);

-- 12.查询至少有一门课与学号为"01"的同学所学相同的同学的信息。
SELECT DISTINCT stu.s_id, stu.s_name FROM student stu, score sc
WHERE stu.s_id = sc.s_id
AND sc.c_id IN (
	SELECT c_id FROM score WHERE s_id = '1'
);

-- 13.查询和"01"号的同学学习的课程完全相同的其他同学的信息。
select * from student where s_id in (
 select s_id from score sc
 group by s_id
 having group_concat(c_id) = (
  select GROUP_CONCAT(c_id) from score
	where s_id = 1
  )
 and s_id != 1
);

-- 14.查询没学过"张三"老师讲授的任一门课程的学生姓名。
select s_id,s_name from student 
where s_id not in (
 select sc.s_id from score sc,course c,teacher t
 where sc.c_id = c.c_id
 and c.t_id = t.t_id
 and t.t_name = '张三'
);

-- 15.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩。
select stu.s_id,stu.s_name,avg(sc.s_score) '平均成绩' from score sc
join student stu on stu.s_id = sc.s_id
group by stu.s_id
having count(sc.s_score < 60 || null) > 1

-- 16.检索"01"课程分数小于60，按分数降序排列的学生信息。
select stu.s_id,stu.s_name,sc.s_score from score sc
left join student stu on stu.s_id = sc.s_id
where sc.s_score < 60
and sc.c_id = '01'
order by sc.s_score desc;

-- 17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。
select stu.s_id,stu.s_name,group_concat(c.c_name) '课程',group_concat(sc.s_score) '分数',avg(sc.s_score) '平均分' 
from student stu
left join score sc on stu.s_id = sc.s_id
join course c on c.c_id = sc.c_id
group by sc.s_id
order by avg(sc.s_score) desc;

-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分。
SELECT 
	c.c_id AS'课程id',
	c.c_name AS'课程名称',
	MAX(sc.s_score) AS'最高分',
	MIN(sc.s_score) AS'最低分',
	AVG(sc.s_score) AS'平均分'
FROM course c, score sc
WHERE c.c_id = sc.c_id
GROUP BY sc.c_id
 
-- 19.查询出各科成绩总分，并按总分降序排序：以如下形式显示：课程ID，课程name，总分
select c.c_id '课程id',c.c_name '课程名称',sum(sc.s_score) '总分' 
from course c, score sc
where c.c_id = sc.c_id
group by sc.c_id
order by sum(sc.s_score) desc;

-- 20.查询学生的总成绩及学生信息。
select stu.s_id,stu.s_name,sum(sc.s_score) '总成绩' from student stu, score sc
where stu.s_id = sc.s_id
group by stu.s_id;

-- 21.查询不同老师所教不同课程平均分从高到低显示。
select t.t_id,t.t_name,c.c_name,avg(sc.s_score) '平均分' from teacher t,course c,score sc
where sc.c_id = c.c_id
and c.t_id = t.t_id
group by t.t_id
order by avg(sc.s_score) desc;

-- 22.查询每门课程被选修的学生数。
select c.c_id,c.c_name,count(c.c_id) '选修人数' from course c,score sc
where sc.c_id = c.c_id
group by c.c_id;

-- 23.查询出只有两门课程的全部学生的学号和姓名。
select stu.s_id,stu.s_name from student stu,score sc
where sc.s_id = stu.s_id
group by sc.s_id
having count(sc.c_id) = 2;

-- 24.查询男生、女生人数
select stu.s_sex '性别',count(1) '人数' from student stu group by s_sex;

-- 25.查询名字中含有"风"字的学生信息。
select * from student where s_name like '%风%';

-- 26.查询1990年出生的学生名单。
select * from student where s_birth like '1990%';

-- 27.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列。
select c.c_id '课程id',c.c_name '课程名称',avg(sc.s_score),'平均成绩' from course c, score sc
where c.c_id = sc.c_id
group by c.c_id
order by avg(sc.s_score) desc,sc.c_id;

-- 28.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩。
select stu.s_id,stu.s_name,avg(sc.s_score) '平均成绩' from student stu, score sc
where sc.s_id = stu.s_id
group by stu.s_id
having avg(sc.s_score) >= 85;

-- 29.查询课程名称为"数学"，且分数低于60的学生姓名和分数。
select stu.s_name,sc.s_score from student stu,score sc,course c
where stu.s_id = sc.s_id
and sc.c_id = c.c_id
and c.c_name = '数学'
and (sc.s_score < 60 || null)

-- 30.查询任何一门课程成绩在70分以上的姓名、课程名称和分数。
select stu.s_name,c.c_name '课程名称', sc.s_score from student stu, score sc, course c
where stu.s_id = sc.s_id
and sc.c_id = c.c_id
group by sc.s_score
having sc.s_score > 70

-- 31.查询不及格的课程。
select c.c_id,c.c_name '课程名称' from course c, score sc
where c.c_id = sc.c_id
group by sc.s_score
having sc.s_score < 60

-- 32.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名。
select stu.s_id,stu.s_name,c.c_name,sc.s_score from student stu,score sc,course c
where stu.s_id = sc.s_id
and sc.c_id = c.c_id
and sc.c_id = '01'
group by sc.s_score
having sc.s_score > 80;

-- 33.求每门课程的学生人数。
select c.c_name,count(c.c_id) '人数' from course c, score sc
where sc.c_id = c.c_id
group by c.c_id;

-- 34.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩。
select stu.s_id,stu.s_name,max(sc.s_score) '成绩' from student stu,score sc,course c,teacher t
where sc.s_id = stu.s_id
and sc.c_id = c.c_id
and c.t_id = t.t_id
and t.t_name = '张无忌'
group by c.c_id;

-- 35.统计每门课程的学生选修人数（超过5人的课程才统计）。
SELECT c_id, COUNT(s_id) AS num FROM score 
GROUP BY c_id HAVING num > 5 
ORDER BY num DESC, c_id ASC

-- 36.检索至少选修两门课程的学生学号。
select stu.s_id,stu.s_name from student stu,score sc
where sc.s_id = stu.s_id
group by stu.s_id having count(sc.c_id) > 2;

-- 37.查询选修了全部课程的学生信息。
select stu.s_id,stu.s_name from student stu,score sc,course c
where stu.s_id = sc.s_id
and sc.c_id = c.c_id
group by stu.s_id having count(sc.c_id) = (select count(c_id) from course);

-- 38.查询各学生的年龄。
select s_name,YEAR(CURRENT_DATE) - year(s_birth) 'age' from student;

-- 39.查询本周过生日的学生。
select * from student where WEEKOFYEAR(s_birth) = WEEKOFYEAR(CURDATE());

-- 40.查询下周过生日的学生。
select * from student where WEEKOFYEAR(s_birth) = WEEKOFYEAR(CURDATE()) + 1;

-- 41.查询本月过生日的学生。
select * from student stu
where MONTH(stu.s_birth) = MONTH(CURDATE());

-- 42.查询下月过生日的学生。
select * from student where MONTH(s_birth) = MONTH(CURDATE()) + 1;

-- 43.笛卡尔积乘积
select * from student stu,teacher t;
select * from student cross join teacher;

-- 1.查询“1”号学生的姓名和各科成绩
select stu.s_id,stu.s_name,c.c_name,sc.s_score from student stu, course c, score sc
where stu.s_id = sc.s_id
and c.c_id = sc.c_id
and stu.s_id = '01'
group by sc.s_score;

-- 2.查询各个学科的平均成绩和最高成绩
select c.c_id,c.c_name,avg(sc.s_score) '平均成绩',max(sc.s_score) '最高成绩' from course c, score sc
where c.c_id = sc.c_id
group by c.c_id;

-- 3.查询所有姓张的同学的各科成绩
select stu.s_id,stu.s_name,c.c_name,sc.s_score from student stu,score sc,course c
where stu.s_id = sc.s_id
and sc.c_id = c.c_id
and stu.s_name like '张%';

-- 4.查询每个同学的最高成绩和科目名称
select stu.s_id,stu.s_name,c.c_name,max(sc.s_score) from student stu
left join score sc on sc.s_id = stu.s_id
left join course c on c.c_id = sc.c_id
group by stu.s_id;

-- 5.查询每个课程的最高分的学生信息
select stu.s_id,stu.s_name,c.c_name,max(sc.s_score) from student stu
left join score sc on sc.s_id = stu.s_id
left join course c on c.c_id = sc.c_id
group by stu.s_id;

-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
select stu.s_id,stu.s_name,c.c_name,sc.s_score from student stu
left join score sc on sc.s_id = stu.s_id
left join course c on c.c_id = sc.c_id
group by stu.s_id
having stu.s_name like '%张%' or '%李%';

-- 7.查询平均成绩大于70的同学的信息。（子查询）
select stu.s_id,stu.s_name,avg(sc.s_score) from student stu
left join score sc on sc.s_id = stu.s_id
group by stu.s_id
having avg(sc.s_score) > 70;

-- 8.将学生按照总分数进行排名。（从高到低）
select stu.s_id,stu.s_name,sum(sc.s_score) from student stu
left join score sc on sc.s_id = stu.s_id
group by stu.s_id
order by sum(sc.s_score) desc;

-- 9.查询数学成绩的最高分、最低分、平均分。
select c.c_name,max(sc.s_score) '最高分',min(sc.s_score) '最低分',avg(sc.s_score) '平均分' from course c
left join score sc on sc.c_id = c.c_id
where c.c_name = '数学';

-- 10.将各科目按照平均分排序。
select c.c_name,avg(sc.s_score) '平均分' from course c
left join score sc on sc.c_id = c.c_id
group by c.c_name
order by avg(sc.s_score) desc; 

-- 11.查询老师的信息和他所带的科目的平均分
select t.t_name,c.c_name,avg(sc.s_score) '平均分' from teacher t
left join course c on c.t_id = t.t_id
left join score sc on sc.c_id = c.c_id
group by c.c_id;

-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
select c.c_name,max(sc.s_score) '最高分',min(sc.s_score) '最低分',t.t_name from course c
left join score sc on sc.c_id = c.c_id
left join teacher t on t.t_id = c.t_id
where t.t_name in('Tom','Jerry')
group by t.t_id;

-- 13.查询每个学生的最好成绩的科目名称（子查询）
select stu.s_id,stu.s_name,c.c_name,max(sc.s_score) from course c,student stu,score sc
where stu.s_id = sc.s_id
and sc.c_id = c.c_id
group by c.c_id,stu.s_id having (select max(sc.s_score) from score sc);

-- 14.查询所有学生的课程及分数
select stu.s_id,stu.s_name,c.c_name,sc.s_score from student stu,course c,score sc
where stu.s_id = sc.s_id
and c.c_id = sc.c_id
group by c.c_name,stu.s_id

-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
select stu.s_id,stu.s_name from student stu,score sc
where stu.s_id = sc.s_id
and sc.c_id = 1
and sc.s_score > 60
group by stu.s_id

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。