一些常见mysql入门练习

一些常见mysql入门

数据库

/*
 Navicat Premium Data Transfer

 Source Server         : June
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : localhost:3306
 Source Schema         : hand2

 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001

 Date: 07/08/2022 21:52:58
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `c_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`  (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `c_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_score` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`s_id`, `c_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `s_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_birth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  `s_sex` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`  (
  `t_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `t_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`t_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

-- 1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

-- 学生表，课程01的成绩表结果集，课程02成绩表的结果集  
-- 两个结果集的学号相连 01课程结果集成绩大于02课程结果集 学生表学号与01结果集相连
select st.s_id,st.s_name,st.s_sex,cid1.s_score as c1_score,cid2.s_score as c2_sore
 from student st,
 (select s_id, s_score,c_id from score where c_id='01') cid1,
 (select s_id, s_score,c_id from score where c_id='02') cid2
where cid1.s_id=cid2.s_id and cid1.s_score>cid2.s_score and st.s_id=cid1.s_id

#--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数


-- 学生表，课程01的成绩表结果集，课程02成绩表的结果集  
-- 两个结果集的学号相连 01课程结果集成绩小于02课程结果集 学生表学号与01结果集相连
select st.s_id,st.s_name,st.s_sex,cid1.s_score as c1_score,cid2.s_score as c2_sore
 from student st,
 (select s_id, s_score,c_id from score where c_id='01') cid1,
 (select s_id, s_score,c_id from score where c_id='02') cid2
where cid1.s_id=cid2.s_id and cid1.s_score<cid2.s_score and st.s_id=cid1.s_id

# 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

-- 学生表，成绩表 相连 对学号分组 avg()求分数平均值 作为判断条件
select st.s_id,st.s_name, AVG(sc.s_score) as avg_score
 from student st,score sc
 where st.s_id=sc.s_id
 GROUP BY sc.s_id 
 having avg_score>=60
 
 # 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
 
--  学生表，成绩表相连 对学号分组 avg()函数求平均值 作为判断不及格条件
 select st.s_id,st.s_name, AVG(sc.s_score) as avg_score
 from student st,score sc
 where st.s_id=sc.s_id
 GROUP BY sc.s_id 
 having avg_score<60
 
 # 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
 
--  学生表，课程表 相连，对学号分组  用count()函数求选课总是，sum()求总成绩
 select st.s_id,st.s_name,COUNT(sc.c_id),SUM(sc.s_score)
  from student st,score sc
	where st.s_id=sc.s_id
	GROUP BY sc.s_id
	
	#--6、查询"李"姓老师的数量
	
	
-- 	用substr(姓名，1，1)截取姓名的姓作为条件
	select COUNT(1)
	from teacher 
	where SUBSTR(t_name,1,1)='李'
	
	#7、查询学过"张三"老师授课的同学的信息
	
-- 	学生表，教师表，成绩表，课程表 四表相连 条件老师是张三
#方法一：
  select st.s_id,st.s_name,st.s_sex,st.s_birth
	 from student st,teacher te,course co,score sc
	 where st.s_id=sc.s_id and te.t_id=co.t_id and co.c_id=sc.c_id and te.t_name='张三'
# 方法二：
  select st.s_id,st.s_name,st.s_sex,st.s_birth
	from student st
	where st.s_id in (select sc.s_id from teacher te,course co,score sc where te.t_id = co.t_id and co.c_id=sc.c_id and te.t_name='张三' )
	
# --8、查询没学过"张三"老师授课的同学的信息

-- 教师，课程，，成绩表相连 条件张三老师 作为not in 的子查询条件

  select st.s_id,st.s_name,st.s_sex,st.s_birth
	from student st
	where st.s_id not in (select sc.s_id from teacher te,course co,score sc where te.t_id = co.t_id and co.c_id=sc.c_id and te.t_name='张三' )
	
#--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

-- 学生表，01课程的成绩表的结果集，02课程的成绩表的结果集 相连
-- 01课程结果集的学号与02课程结果集学号相连 ，学生表分别与01课程和01课程结果集的学生编号相连

	select st.s_id,st.s_name,st.s_sex,cid1.s_score as c1_score,cid2.s_score as c2_sore
 from student st,
 (select s_id, s_score,c_id from score where c_id='01') cid1,
 (select s_id, s_score,c_id from score where c_id='02') cid2
where cid1.s_id=cid2.s_id and st.s_id=cid2.s_id and st.s_id=cid1.s_id

#--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

-- 学生表与01课程的成绩表结果集相连
-- 查询出学过课程编号02同学的学号作为not in 子查询

select st.s_id,st.s_name,st.s_sex,cid1.s_score as c1_score
 from student st,
 (select s_id, s_score,c_id from score where c_id='01') cid1
where st.s_id=cid1.s_id
and st.s_id
not in (select s_id from score where c_id='02')

#--11、查询没有学全所有课程的同学的信息

-- 统计出课程表所有课程的数量作为子查询判断条件，
-- 学生版与成绩表相连 对学号分组 统计上过的数量与子查询判断

select st.s_id,st.s_name,st.s_sex,st.s_birth,COUNT(sc.s_id) num
from student st,score sc
where st.s_id=sc.s_id
GROUP BY sc.s_id
HAVING num<(select count(1) from course)

# --12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

-- 把01学号的成绩表查询出课程编号作为子查询  
-- 学生表与成绩表相连 学号不为01

select DISTINCT st.s_id,st.s_name,st.s_sex
from student st,score sc
where st.s_id=sc.s_id and st.s_id<>'01'
and sc.c_id in (
select c_id from score where s_id='01')

# --13、查询和"01"号的同学学习的课程完全相同的其他同学的信息

-- 把01号同学的课程信息查询出来作为结果集，在把成绩表的查询出来作为一个结果集
-- 学生表作为一个表，
-- 条件 两个成绩结果集数量统计相等条件， 学生表的学号不等于01同学的学号，学生表的学号与成绩表的结果集的学号相连

select st.s_id,st.s_name,st.s_sex
from student st,
(select s_id,c_id,COUNT(c_id) num from  score GROUP BY s_id) sc1,
(select s_id, c_id,COUNT(c_id) num from score where s_id='01') sc2
where st.s_id<>sc2.s_id
and st.s_id=sc1.s_id and
sc1.num=sc2.num

#--14、查询没学过"张三"老师讲授的任一门课程的学生姓名

-- 教师表，课程表，分数表联表查询出张三老师授课的学生编号作为子查询 用not in 与主表学生表的编号判断出没有学过张三老师的学生
  select st.s_name
	from student st
	where st.s_id not in (select sc.s_id from teacher te,course co,score sc where te.t_id = co.t_id and co.c_id=sc.c_id and te.t_name='张三')

#--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

-- 学生表和课程表相连，对学号排序，用avg函数求平均值，sum(case when )统计学生成绩不及格的数量 作为条件

select st.s_id,st.s_name,AVG(sc.s_score) avg_score,sum(case when sc.s_score<60 then 1 else 0 end) num
from student st,score sc
where st.s_id=sc.s_id
GROUP BY sc.s_id
having num>=2

#--16、检索"01"课程分数小于60，按分数降序排列的学生信息

-- 学生表和成绩表相连，条件：01课程分数小于60分，对分数降序

select DISTINCT st.s_id,st.s_name,st.s_sex,st.s_birth
from student st,score sc
where st.s_id=sc.s_id and sc.c_id='01' and sc.s_score<60
ORDER BY sc.s_score desc

# --17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

-- 把课程表课程编号分组 用avg函数对成绩求出各个课程平均成绩的结果集

select sco.c_id,sco.s_score,score.avg_score
from score sco,
(select sc.s_id, AVG(sc.s_score) avg_score
from score sc
GROUP BY sc.s_id) score
where 
sco.s_id=score.s_id
ORDER BY score.avg_score desc

#--18、查询各科成绩最高分、最低分和平均分：
-- 以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
# 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

-- 课程表和成绩表相连 用sum(case when 条件 then 1 else 0 end)/COUNT() 求各个阶段的率

select sc.c_id,co.c_name,MAX(sc.s_score),MIN(sc.s_score),
SUM(case when sc.s_score>=60 then 1 else 0 end)/COUNT(sc.c_id)
'及格率',
SUM(case when sc.s_score between 70 and 80 then 1 else 0 end)/COUNT(sc.c_id)
'中等率',
SUM(case when sc.s_score between 80 and 90 then 1 else 0 end)/COUNT(sc.c_id)
'优良率',
SUM(case when sc.s_score >=90 then 1 else 0 end)/COUNT(sc.c_id)
'优秀率'
from score sc,course co
where  co.c_id=sc.c_id
GROUP BY sc.c_id

# --19、按各科成绩进行排序，并显示排名

-- 用DESN_RANK() OVER() 排序函数对课程编号的成绩进行排名
select DENSE_RANK() OVER(PARTITION BY sc.c_id ORDER BY sc.s_score desc) rank_num,
sc.c_id,sc.s_score
from score sc 

# --20、查询学生的总成绩并进行排名

-- 用DESN_RANK() OVER() 排名函数对学生总成绩进行排名  求学生总成绩对学号分组 用sum函数对分数求和
select DENSE_RANK() OVER(ORDER BY SUM(sc.s_score) desc) rank_num ,sc.s_id,SUM(sc.s_score)
from score sc
GROUP BY sc.s_id

# --21、查询不同老师所教不同课程平均分从高到低显示

-- 课程表和成绩表相连，对教师进行分组，用avg函数求各个课程的平均值 用倒叙对平均值进行排序
select co.t_id, AVG(sc.s_score) avg_score
from score sc,course co
where sc.c_id=co.c_id
GROUP BY co.t_id
ORDER BY avg_score desc

# --22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

-- 利用DENSE_RANK() OVER() 对各个课程的成绩进行排序做子查询  in(2,3)去取两到三名

select score.ranks,stu.s_id,stu.s_name, score.c_id,score.s_score
from 
student stu,
(select DENSE_RANK() OVER(PARTITION BY sc.c_id ORDER BY sc.s_score DESC) ranks,sc.c_id,sc.s_score ,st.s_id
from student st,score sc
where st.s_id=sc.s_id) as score
where
stu.s_id=score.s_id
and score.ranks in(2,3)
-- 排序函数 的别名不能直接调用判断，故有嵌套一层子查询才可以调用表名进行判断2-3名的排序

# --23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-69,0-60及所占百分比

-- 课程表和成绩表相连，用sum(case when )/count() 求出各个分数的的比率，用round函数保留两位小数，用concat函数拼接‘%’号

select sc.c_id,co.c_name,
CONCAT(ROUND(SUM(case when sc.s_score between 85 and 100 then 1 else 0 end)/count(sc.c_id)*100,2),'%') '[100-85]',
CONCAT(ROUND(SUM(case when sc.s_score between 70 and 85 then 1 else 0 end)/count(sc.c_id)*100,2),'%') '[85-70]',
CONCAT(ROUND(SUM(case when sc.s_score between 60 and 70 then 1 else 0 end)/count(sc.c_id)*100,2),'%') '[70-60]',
CONCAT(ROUND(SUM(case when sc.s_score between 0 and 60 then 1 else 0 end)/count(sc.c_id)*100,2),'%') '[0-60]'
from course co,score sc
where co.c_id=sc.c_id
GROUP BY sc.c_id

# --24、查询学生平均成绩及其名次

-- c 排序函数对学生成绩平均值进行排序

select DENSE_RANK() OVER(ORDER BY AVG(sc.s_score) desc)rank_num ,st.s_id,st.s_name, AVG(sc.s_score)
from student st,score sc
where st.s_id=sc.s_id
GROUP BY sc.s_id


# --25、查询各科成绩前三名的记录

-- 用ROW_NUMBER() OVER() 排序函数对课程的成绩进行排序，用in去取排序后成绩1，2，3名。

select score.rank_num,score.s_id,score.c_id,score.s_score
from student st,
(select ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) rank_num,s_id,c_id,s_score
from score) score
where st.s_id=score.s_id
and score.rank_num in (1,2,3)

-- 在用排序函数 嵌套 取别名的时候 注意与排序函数结果集连接的不能是排序函数的表，要用与排序函数表相关的表.
-- 本题就是对成绩表排序的结果集不能在与成绩表相关联，可以与学生表关联一下。
 
# --26、查询每门课程被选修的学生数

-- 用课程表和成绩表相连,对课程编号分组,统计各个科门的人数
select co.c_name,COUNT(sc.c_id) num
from score sc,course co
where co.c_id=sc.c_id
GROUP BY sc.c_id

# --27、查询出只有两门课程的全部学生的学号和姓名

-- 学生表和成绩表相连,对学号进行分组,统计每个同学课程=2
select COUNT(sc.s_id) num,st.s_id,st.s_name
from student st,score sc
where st.s_id=sc.s_id
GROUP BY sc.s_id
HAVING num=2

#--28、查询男生、女生人数

-- 对学生表的性别进行分组 对性别进行统计
select s_sex ,COUNT(s_sex) num
from student
GROUP BY s_sex

# --29、查询名字中含有"风"字的学生信息

-- 对学生表的名字字段用like进行模糊查询
select s_id,s_name,s_sex,s_birth
from student 
where s_name like '%风%'

#--30、查询同名同性学生名单，并统计同名人数

-- 对学生表的姓名字段进行分组统计 大于1的就是有同名同姓
select COUNT(s_name) num,s_id,s_name,s_sex,s_birth
from student
GROUP BY s_name
HAVING num>1

# --31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)

-- 用extract函数对学生生日的年份进行日期截取=1990的符合条件

select s_id,s_name,s_sex,s_birth
from student
where EXTRACT(year from s_birth)=1990

# --32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

-- 对课程表的课程编号分组 用avg函数求每门课程成绩平均值 对平均成绩进行降序 ,又对课程编号升序

select c_id, AVG(s_score) avg_score
from score
GROUP BY c_id
ORDER BY avg_score DESC, c_id ASC

-- ORDER BY 排序时就近原则 对各排序要用逗号隔开

# --33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

-- 学生表,成绩表相连,对学号分组 用avg函数求每个同学的平均分,用平均分大于等于85为条件
select st.s_id,st.s_name,AVG(sc.s_score) avg_score
from student st,score sc
where st.s_id=sc.s_id
GROUP BY sc.s_id
HAVING avg_score>=85

#--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

-- 学生表,分数表,课程表相连,课程为数学 成绩对于60作为判断条件

select st.s_name,sc.s_score
from student st,score sc,course co
where st.s_id=sc.s_id and co.c_id=sc.c_id
and co.c_name='数学' and sc.s_score<60

# --35、查询所有学生的课程及分数情况；DENSE_RANK() OVER(PARTITION by co.c_name)

-- 把所有学生对应的课程都列出来，可以把学生表作为左连接的主表与分数表连接

select st.s_id,st.s_name,sc.c_id,sc.s_score
from student st left join score sc on st.s_id=sc.s_id

#--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

-- 学生表，课程表，成绩表 三表相连，成绩大于70判断条件

select  st.s_name ,co.c_name,sc.s_score
from student st,score sc,course co
where st.s_id=sc.s_id and co.c_id=sc.c_id
and sc.s_score>70

# --37、查询不及格的课程

-- 把成绩低于60的成绩表显示出来
select s_id , c_id ,s_score
from score 
where s_score<60

#--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；

-- 成绩表和学生表连接 课程编号01和成绩大于80作为条件,表中01课程的成绩都小于等于80，把条件改为成绩大于等于80有数据
select st.s_id,st.s_name
from student st,score sc
where st.s_id=sc.s_id
and sc.c_id='01' and sc.s_score>=80

# --39、求每门课程的学生人数

-- 课程表和成绩表连接，对成绩表的课程进行分组，统计每门课程的人数
select co.c_name , COUNT(sc.c_id) num
from course co,score sc
where co.c_id=sc.c_id
GROUP BY sc.c_id

# --40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

-- 学生表 成绩表 教师表,课程表四表连接,主表查询张三老师授课的学生,利用关联子查询,查询授课学生的成绩最大值

select st.s_id,st.s_name,st.s_birth,st.s_sex,sc.s_score
from student st,teacher te,course co,score sc
where st.s_id=sc.s_id and te.t_id=co.t_id and co.c_id=sc.c_id
and te.t_name='张三' 
and sc.s_score=(select MAX(sco.s_score) from score sco where sco.c_id=sc.c_id)

#--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

-- 学生表,两张成绩表作为结果集,课程编号 和成绩相同作为条件 同一个学生不同课程成绩相同

select DISTINCT st.s_id,sco1.c_id,sco1.s_score
from student st,
(select s_id,c_id,s_score from score ) sco1,
(select s_id ,c_id,s_score from score) sco2
where sco1.s_id=sco2.s_id and sco1.s_score=sco2.s_score and sco1.c_id<>sco2.c_id
and st.s_id=sco1.s_id

#--42、查询每门功课成绩最好的前两名

-- 用排序函数 ROW_NUMBER() 不重复排名 给每门课程排序 取每门课程的前两名
select sc.rank_num,sc.c_id,sc.s_score
from student st,
(select ROW_NUMBER() OVER(PARTITION BY c_id ORDER BY s_score DESC) rank_num,c_id,s_score,s_id
from score ) sc
where st.s_id=sc.s_id
and sc.rank_num in(1,2)

# --43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，
# 查询结果按人数降序排列，若人数相同，按课程号升序排列 

-- 对成绩表的课程进行分组 统计超过5人的 对统计人数降序 对人数相同的课程号升序

select c_id,COUNT(c_id) num
 from score
 GROUP BY c_id
 HAVING num>5
 ORDER BY num DESC , c_id ASC

# --44、检索至少选修两门课程的学生学号

-- 对成绩表的学生学号分组统计 数量大于等于2门的作为条件
select s_id
from score
GROUP BY s_id
HAVING COUNT(s_id)>=2

# --45、查询选修了全部课程的学生信息

-- 把课程表的课程进行总的统计作为子查询的条件,成绩表统计各个学生选修数量等于总统计就符合条件

select st.s_id,st.s_name,st.s_sex,st.s_birth
from score sc,student st
where st.s_id=sc.s_id
GROUP BY sc.s_id
HAVING COUNT(sc.s_id)=(
select COUNT(1) from course)

# --46、查询各学生的年龄

-- 对学生表的生日与今天的日期求差就是年龄

select ROUND(DATEDIFF(CURRENT_DATE(),s_birth)/360) s_age
from student 

# --47、查询本周过生日的学生

-- 利用日期函数week对学生生日和今日日期判断是否在同一个周 在同一周返回1
select s_id,s_name,s_sex,s_birth
from student
where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))=week(s_birth)=1

# --48、查询下周过生日的学生

-- 用日期函数week对学生生日和今日日期的周数加一  判断是否在同一个周 在同一周返回1  就是下周过生日

select s_id,s_name,s_sex,s_birth
from student
where WEEK(DATE_FORMAT(NOW(),'%Y%m%d'))+1=week(s_birth)=1

# --49、查询本月过生日的学生

-- 用extract日期函数 分别截取今天和学生生日的月数相等就是在本月过生日

select  s_id,s_name,s_birth,s_sex
from student
where EXTRACT(month from CURRENT_DATE())=EXTRACT(month from s_birth)

# --50、查询下月过生日的学生

-- 用extract日期函数 分别截取今天的月份+1和学生生日的月数相等就是在下月过生日

select  s_id,s_name,s_birth,s_sex
from student
where EXTRACT(month from CURRENT_DATE())+1=EXTRACT(month from s_birth)