mysql经典50题

已于 2022-12-08 16:45:19 修改
阅读量527 收藏 1
点赞数 1
文章标签： mysql 数据库
于 2022-12-08 13:51:04 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_55680176/article/details/128203302
版权

 create table `student`(
 `StudentNo` int(4) not null  comment '学号',
 `LoginPwd` varchar(20) DEFAULT  null,
 `StudentName` varchar(20) DEFAULT null  comment '学生姓名',
 `Sex` tinyint(1) DEFAULT null comment '性别，取值0或1',
 `GandeId` int(11) DEFAULT null comment '年级编号',
 `Phone` varchar(50) comment '联系电话',
 `Address` varchar(255) comment '地址',
 `BronDate` datetime DEFAULT null comment '出生日期',
 `Email` varchar(50) comment '邮箱账号',
 `IdentityCard` varchar(18) DEFAULT null comment '身份证号');
 CREATE TABLE `student` (
  `StudentNO` int NOT NULL COMMENT '学号',
  `LoginPwd` varchar(20) DEFAULT NULL,
  `StudentName` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `Sex` tinyint(1) DEFAULT NULL COMMENT '性别，取值0或1',
  `GradeId` int DEFAULT NULL COMMENT '年级编号',
  `Phone` varchar(50) NOT NULL COMMENT '联系电话',
  `Address` varchar(255) NOT NULL COMMENT '地址',
  `BornDate` datetime DEFAULT NULL COMMENT '出生时间',
  `Email` varchar(50) NOT NULL COMMENT '邮箱账号',
  `IdentityCard` varchar(18) DEFAULT NULL COMMENT '身份证号'
)
CREATE TABLE `subjet`(
`SubjectNo` int(11) NOT NULL COMMENT "课程编号",
`SubjectName` VARCHAR(50) DEFAULT null comment"课程名称",
`ClassHour` int(4) DEFAULT NULL comment"学时",
`GradeID`int(4) DEFAULT NULL comment"年纪编号")
ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
CREATE TABLE`student`(`StudentNO` int(10) PRIMARY KEY auto_increment,
`LoginPwd` varchar(20),
`StudentName` VARCHAR(20),
sex tinyint,gradeid int,
phone VARCHAR(50),
address VARCHAR(255),
borndate datetime,email varchar(50),
identintycard VARCHAR(18),
CONSTRAINT FK_gradeid FOREIGN key (gradeid) REFERENCES grade (gradeid));


CREATE table `grade` (gradeid int(10) PRIMARY KEY auto_increment,
subjectno int(10), 
gradename varchar(50));
create table `result`
(studentno int(10),
subjectno int(10),
examedate datetime,
studentresult int
)
CREATE TABLE `subject`(subjectno int(10) PRIMARY KEY auto_increment ,
subjectname varchar(50),
classhour int(10),
gradeid int );
insert into student(studentno,studentname,sex,gradeid,borndate,identintycard)
values (1000,'张三',1,1,now(),'140020319349583928'),
(1001,'李四',0,2,now(),'140020319349583918'),
(1002,'王五',1,3,now(),'140020319349583938');
insert into subject(subjectno,subjectname,classhour,gradeid)
values(1,'高等数学1',40,1),(2,'高等数学2',38,2),(3,'java编程',38,3),(4,'hadoop理论',44,4);
insert into result(studentno,subjectno,examedate,studentresult)
values(1000,1,now(),98),(1001,1,now(),100),(1000,2,now(),78),(1000,3,now(),100),
(1000,4,now(),85),(1001,2,now(),100),(1001,3,now(),100),(1001,4,now(),100),
(1002,1,now(),68),(1002,2,now(),60),(1002,3,now(),61),(1002,4,now(),63);
select  r.studentno as'学号',st.studentname '姓名',s.subjectname,r.studentresult 
from result r  left join subject s on s.subjectno=r.subjectno  
left join student st on st.studentno=r.studentno 
where s.subjectname='hadoop理论' order by r.studentresult desc limit 2;
select   r.studentno as'学号',stu.studentname '姓名',s.subjectname,r.studentresult           
FROM result r left join subject s on s.subjectno=r.subjectno
left join student stu on stu.studentno=r.studentno
where s.subjectname='java编程' and r.studentresult>80 order by r.studentresult DESC limit 4;
SELECT  
from result r WHERE 
(SELECT subjectname from subject)='高等数学1'
INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set @@global.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
#--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
mysql> select stu.*,sc.score,sc2.score from sc join sc sc2 on sc.sid=sc2.sid and sc.cid=01 and sc2.cid=02 join student stu on stu.sid=sc.sid where sc.score>sc2.score;
+------+--------+---------------------+------+-------+-------+
| SID  | Sname  | Sage                | Ssex | score | score |
+------+--------+---------------------+------+-------+-------+
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |  70.0 |  60.0 |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |  50.0 |  30.0 |
+------+--------+---------------------+------+-------+-------+

mysql> select stu.*,sc.score,sc2.score from sc join sc sc2 on sc.sid=sc2.sid and sc.cid=01 and sc2.cid=02 join student stu on stu.sid=sc.sid where sc.score<sc2.score;
+------+--------+---------------------+------+-------+-------+
| SID  | Sname  | Sage                | Ssex | score | score |
+------+--------+---------------------+------+-------+-------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |  80.0 |  90.0 |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |  76.0 |  87.0 |
+------+--------+---------------------+------+-------+-------+

# 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select sc.sid,stu.sname,avg(sc.score) avg from sc join student stu on stu.sid=sc.sid group by sc.sid,stu.sname having avg>=60;
+------+--------+----------+
| sid  | sname  | avg      |
+------+--------+----------+
| 01   | 赵雷   | 89.66667 |
| 02   | 钱电   | 70.00000 |
| 03   | 孙风   | 80.00000 |
| 05   | 周梅   | 81.50000 |
| 07   | 郑竹   | 93.50000 |
+------+--------+----------+

# 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
mysql> select stu.sid,stu.sname,count(sc.cid),sum(sc.score)from student stu left join sc on stu.sid=sc.sid  group by stu.sid,stu.sname;
+------+--------+---------------+---------------+
| sid  | sname  | count(sc.cid) | sum(sc.score) |
+------+--------+---------------+---------------+
| 01   | 赵雷   |             3 |         269.0 |
| 02   | 钱电   |             3 |         210.0 |
| 03   | 孙风   |             3 |         240.0 |
| 04   | 李云   |             3 |         100.0 |
| 05   | 周梅   |             2 |         163.0 |
| 06   | 吴兰   |             2 |          65.0 |
| 07   | 郑竹   |             2 |         187.0 |
| 08   | 王菊   |             0 |          NULL |
+------+--------+---------------+---------------+
#--6、查询"李"姓老师的数量
mysql> select count(*)from teacher t where t.tname like '李%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
#7、查询学过"张三"老师授课的同学的信息
mysql> select * from student stu where stu.sid in (select sid from sc where cid=(select cid from course where tid=(select tid from teacher where tname='张三')));
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-07-01 00:00:00 | 女   |
+------+--------+---------------------+------+
# --8、查询没学过"张三"老师授课的同学的信息
mysql> select * from student stu where stu.sid not in (select sid from sc where cid=(select cid from course where tid=(select tid from teacher where tname='张三')));
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 06   | 吴兰   | 1992-03-01 00:00:00 | 女   |
| 08   | 王菊   | 1990-01-20 00:00:00 | 女   |
+------+--------+---------------------+------+
#--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
mysql> select stu.* from sc join sc sc2 on sc.sid=sc2.sid and sc.cid=01 and sc2.cid=02 join student stu on stu.sid=sc.sid;
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
+------+--------+---------------------+------+
#--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select stu.* from student stu where stu.sid not in(select stu.sid from sc join sc sc2 on sc.sid=sc2.sid andsc.cid=01 and sc2.cid=02 join student stu on stu.sid=sc.sid);
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 06   | 吴兰   | 1992-03-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-07-01 00:00:00 | 女   |
| 08   | 王菊   | 1990-01-20 00:00:00 | 女   |
+------+--------+---------------------+------+
#--11、查询没有学全所有课程的同学的信息
mysql> select stu.sid,count(sc.cid) c from student stu left join sc on stu.sid=sc.sid group by stu.sid having c<(select count(*)from course);
+------+---+
| sid  | c |
+------+---+
| 05   | 2 |
| 06   | 2 |
| 07   | 2 |
| 08   | 0 |
+------+---+
# --12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
mysql> select distinct stu.* from sc join student stu on sc.sid=stu.sid where sc.sid!=01 and cid in(select cid from sc where sid=01);
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 05   | 周梅   | 1991-12-01 00:00:00 | 女   |
| 06   | 吴兰   | 1992-03-01 00:00:00 | 女   |
| 07   | 郑竹   | 1989-07-01 00:00:00 | 女   |
+------+--------+---------------------+------+

# --13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select  t.sid,t.count1 
from  
(select sid,count(sid) count1 from
(select sc.sid from sc left join sc sc2 on sc.cid=sc2.cid and sc2.sid=01 and sc.sid!=01 where sc.sid!=01 and sc2.sid is not null) t group by t.sid) t 
having t.count1= (select count(*) from sc where sid=01);
+------+--------+
| sid  | count1 |
+------+--------+
| 02   |      3 |
| 03   |      3 |
| 04   |      3 |
+------+--------+

#--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * 
from student stu 
where stu.sid not in 
(select sid from sc where cid=
(select cid from course where tid=
(select tid from teacher where tname='张三')));
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 06   | 吴兰   | 1992-03-01 00:00:00 | 女   |
| 08   | 王菊   | 1990-01-20 00:00:00 | 女   |
+------+--------+---------------------+------+

#--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select stu.sid, stu.sname,avg(sc.score) as 平均分
from student as stu
join sc on stu.sid = sc.sid
where sc.score < 60
group by stu.sid, stu.sname
having count(sc.cid) >= 2;
+------+--------+-----------+
| sid  | sname  | 平均分    |
+------+--------+-----------+
| 04   | 李云   |  33.33333 |
| 06   | 吴兰   |  32.50000 |
+------+--------+-----------+

#--16、检索"01"课程分数小于60，按分数降序排列的学生信息
select stu.* 
from sc 
join student stu on sc.sid=stu.sid 
where sc.cid=01 and sc.score<60;
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 06   | 吴兰   | 1992-03-01 00:00:00 | 女   |
+------+--------+---------------------+------+

# --17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select stu.sname, stu.sid,ifnull(t.avg,0) avgscore,
 sum(case when sc.cid='01' then sc.score else 0 end )as '语文',
 sum(case when sc.cid='02' then sc.score else 0 end )as '数学',
 sum(case when sc.cid='03' then sc.score else 0 end )as '英语'
 from (select sc.sid,avg(score) avg from sc group by sc.sid order by avg desc) t  
 left  join sc on t.sid=sc.sid  
 right join student stu on stu.sid=t.sid 
 group by stu.sid,t.avg,stu.sname ;
+--------+------+----------+--------+--------+--------+
| sname  | sid  | avgscore | 语文   | 数学   | 英语   |
+--------+------+----------+--------+--------+--------+
| 赵雷   | 01   | 89.66667 |   80.0 |   90.0 |   99.0 |
| 钱电   | 02   | 70.00000 |   70.0 |   60.0 |   80.0 |
| 孙风   | 03   | 80.00000 |   80.0 |   80.0 |   80.0 |
| 李云   | 04   | 33.33333 |   50.0 |   30.0 |   20.0 |
| 周梅   | 05   | 81.50000 |   76.0 |   87.0 |    0.0 |
| 吴兰   | 06   | 32.50000 |   31.0 |    0.0 |   34.0 |
| 郑竹   | 07   | 93.50000 |    0.0 |   89.0 |   98.0 |
| 王菊   | 08   |  0.00000 |    0.0 |    0.0 |    0.0 |
+--------+------+----------+--------+--------+--------+

#--18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
# 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select sc.cid,course.cname,max(sc.score) 最高分,
min(sc.score) 最低分,
avg(sc.score) 平均分,
SUM(case WHEN sc.score>=60 THEN 1 else 0 end)/ COUNT(sc.cid) 及格率,
SUM(case WHEN sc.score>=70 and sc.score<80 THEN 1 else 0 end)/ COUNT(sc.cid) 中等率,
SUM(case WHEN sc.score>=80 and sc.score<90 THEN 1 else 0 end)/ COUNT(sc.cid) 优良率,
SUM(case WHEN sc.score>=90 THEN 1 else 0 end)/ COUNT(sc.cid) 优秀率
from sc left join course on sc.cid=course.cid group BY sc.cid,course.cname;
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| cid  | cname  | 最高分    | 最低分    | 平均分    | 及格率    | 中等率    | 优良率    | 优秀率    |
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 01   | 语文   |      80.0 |      31.0 |  64.50000 |    0.6667 |    0.3333 |    0.3333 |    0.0000 |
| 02   | 数学   |      90.0 |      30.0 |  72.66667 |    0.8333 |    0.0000 |    0.5000 |    0.1667 |
| 03   | 英语   |      99.0 |      20.0 |  68.50000 |    0.6667 |    0.0000 |    0.3333 |    0.3333 |
+------+--------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

# --19、按各科成绩进行排序，并显示排名
SELECT a.* ,  (SELECT COUNT(DISTINCT score) FROM SC WHERE CID = a.CID AND score >a.score) +1 
AS 排名 FROM SC AS a ORDER BY a.cid , 排名;
+------+------+-------+--------+
| SID  | CID  | score | 排名   |
+------+------+-------+--------+
| 01   | 01   |  80.0 |      1 |
| 03   | 01   |  80.0 |      1 |
| 05   | 01   |  76.0 |      2 |
| 02   | 01   |  70.0 |      3 |
| 04   | 01   |  50.0 |      4 |
| 06   | 01   |  31.0 |      5 |
| 01   | 02   |  90.0 |      1 |
| 07   | 02   |  89.0 |      2 |
| 05   | 02   |  87.0 |      3 |
| 03   | 02   |  80.0 |      4 |
| 02   | 02   |  60.0 |      5 |
| 04   | 02   |  30.0 |      6 |
| 01   | 03   |  99.0 |      1 |
| 07   | 03   |  98.0 |      2 |
| 03   | 03   |  80.0 |      3 |
| 02   | 03   |  80.0 |      3 |
| 06   | 03   |  34.0 |      4 |
| 04   | 03   |  20.0 |      5 |
+------+------+-------+--------+

# --21、查询不同老师所教不同课程平均分从高到低显示 
select cs.cid,t.tname,avg(sc.score) avg from sc  left join course cs on sc.cid=cs.cid left join teacher t on t.tid=cs.tid  group by cs.cid,t.tname order by avg DESC ;
+------+--------+----------+
| cid  | tname  | avg      |
+------+--------+----------+
| 02   | 张三   | 72.66667 |
| 03   | 王五   | 68.50000 |
| 01   | 李四   | 64.50000 |
+------+--------+----------+

# --21、查询不同老师所教不同课程平均分从高到低显示 
select cs.cid,t.tname,avg(sc.score) avg from sc  left join course cs on sc.cid=cs.cid left join teacher t on t.tid=cs.tid  group by cs.cid,t.tname order by avg DESC ;
+------+--------+----------+
| cid  | tname  | avg      |
+------+--------+----------+
| 02   | 张三   | 72.66667 |
| 03   | 王五   | 68.50000 |
| 01   | 李四   | 64.50000 |
+------+--------+----------+

# --22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
(select sc.sid,sc.cid,sc.score,dense_rank()over (order by score desc) as r from sc where sc.cid=01 limit 1,2) union (select sc.sid,sc.cid,sc.score,dense_rank()over (order by score desc) as r from sc where sc.cid=02 limit 1,2) union (select sc.sid,sc.cid,sc.score,dense_rank()over (order by score desc) as r from sc where sc.cid=03 limit 1,2);
+------+------+-------+---+
| sid  | cid  | score | r |
+------+------+-------+---+
| 03   | 01   |  80.0 | 1 |
| 05   | 01   |  76.0 | 2 |
| 07   | 02   |  89.0 | 2 |
| 05   | 02   |  87.0 | 3 |
| 07   | 03   |  98.0 | 2 |
| 02   | 03   |  80.0 | 3 |
+------+------+-------+---+

# --23、统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比
select sc.cid,course.cname,
SUM(case WHEN 0<sc.score and sc.score<60 THEN 1 else 0 end)/7 '0-60',
SUM(case WHEN 60<=sc.score and sc.score<70 THEN 1 else 0 end)/7 '60-70',
SUM(case WHEN 70<=sc.score and sc.score<85 THEN 1 else 0 end)/7 '70-85',
SUM(case WHEN 85<=sc.score THEN 1 else 0 end)/7 '85-100'
from sc left join course on sc.cid=course.cid group BY sc.cid,course.cname;
+------+--------+--------+--------+--------+--------+
| cid  | cname  | 0-60   | 60-70  | 70-85  | 85-100 |
+------+--------+--------+--------+--------+--------+
| 01   | 语文   | 0.2857 | 0.0000 | 0.5714 | 0.0000 |
| 02   | 数学   | 0.1429 | 0.1429 | 0.1429 | 0.4286 |
| 03   | 英语   | 0.2857 | 0.0000 | 0.2857 | 0.2857 |
+------+--------+--------+--------+--------+--------+

# --24、查询学生平均成绩及其名次
select  sc.sid,avg(sc.score) avg from sc group by sc.sid order by avg desc;
+------+----------+
| sid  | avg      |
+------+----------+
| 07   | 93.50000 |
| 01   | 89.66667 |
| 05   | 81.50000 |
| 03   | 80.00000 |
| 02   | 70.00000 |
| 04   | 33.33333 |
| 06   | 32.50000 |
+------+----------+
# --25、查询各科成绩前三名的记录
(select sc.sid,sc.cid,sc.score,dense_rank()over (order by score desc) as r from sc where sc.cid=01 limit 0,3) 
union (select sc.sid,sc.cid,sc.score,dense_rank()over (order by score desc) as r from sc where sc.cid=02 limit 0,3) 
union (select sc.sid,sc.cid,sc.score,dense_rank()over (order by score desc) as r from sc where sc.cid=03 limit 0,3);
+------+------+-------+---+
| sid  | cid  | score | r |
+------+------+-------+---+
| 01   | 01   |  80.0 | 1 |
| 03   | 01   |  80.0 | 1 |
| 05   | 01   |  76.0 | 2 |
| 01   | 02   |  90.0 | 1 |
| 07   | 02   |  89.0 | 2 |
| 05   | 02   |  87.0 | 3 |
| 01   | 03   |  99.0 | 1 |
| 07   | 03   |  98.0 | 2 |
| 02   | 03   |  80.0 | 3 |
+------+------+-------+---+

# --26、查询每门课程被选修的学生数
select  c.cid,count(sc.sid)from course c join sc on sc.cid=c.cid group by c.cid;
+------+---------------+
| cid  | count(sc.sid) |
+------+---------------+
| 01   |             6 |
| 02   |             6 |
| 03   |             6 |
+------+---------------+
# --27、查询出只有两门课程的全部学生的学号和姓名
select  stu.sid,stu.sname from (select  sc.sid,count(sc.cid) c from  student stu join sc on sc.sid=stu.sid group by sc.sid) t join student stu on t.sid=stu.sid where t.c=2;
+------+--------+
| sid  | sname  |
+------+--------+
| 05   | 周梅   |
| 06   | 吴兰   |
| 07   | 郑竹   |
+------+--------+
#--28、查询男生、女生人数
(select t.ssex,count(*) from (select stu.sid,ssex from student stu where stu.ssex='男') t) union all (select t.ssex,count(*) from (select stu.sid,ssex from student stu where stu.ssex='女') t);
+------+----------+
| ssex | count(*) |
+------+----------+
| 男   |        4 |
| 女   |        4 |
+------+----------+

# --29、查询名字中含有"风"字的学生信息
select stu.* from student stu where stu.sname LIKE '%风%';
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 03   | 孙风   | 1990-05-20 00:00:00 | 男   |
+------+--------+---------------------+------+

#--30、查询同名同性学生名单，并统计同名人数
select stu.sname,count(*) from student stu join student stu1 on stu.sname=stu1.sname where stu.sid!=stu1.sid and stu.ssex=stu1.ssex group by stu.sname;
Empty set (0.00 sec)
# --31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
select stu.* from student stu where stu.sage like '1990%';
+------+--------+---------------------+------+
| SID  | Sname  | Sage                | Ssex |
+------+--------+---------------------+------+
| 01   | 赵雷   | 1990-01-01 00:00:00 | 男   |
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |
| 03   | 孙风   | 1990-05-20 00:00:00 | 男   |
| 04   | 李云   | 1990-08-06 00:00:00 | 男   |
| 08   | 王菊   | 1990-01-20 00:00:00 | 女   |
+------+--------+---------------------+------+
# --32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select sc.cid,avg(score) avg from sc group by sc.cid order by avg desc,sc.cid; 
+------+----------+
| cid  | avg      |
+------+----------+
| 02   | 72.66667 |
| 03   | 68.50000 |
| 01   | 64.50000 |
+------+----------+
# --33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select  sc.sid,stu.sname,avg(sc.score) avg from sc  join student stu on stu.sid=sc.sid group by sc.sid,stu.sname having avg >=85;
+------+--------+----------+
| sid  | sname  | avg      |
+------+--------+----------+
| 01   | 赵雷   | 89.66667 |
| 07   | 郑竹   | 93.50000 |
+------+--------+----------+
#--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select  stu.sname,sc.score from sc join course cs on cs.cid=sc.cid join student stu on stu.sid=sc.sid where cs.cname='数学' and sc.score<60;
+--------+-------+
| sname  | score |
+--------+-------+
| 李云   |  30.0 |
+--------+-------+
# --35、查询所有学生的课程及分数情况；
select stu.sid,sc.cid,sc.score from sc  right join student stu on stu.sid=sc.sid; 
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
| 01   | 03   |  99.0 |
| 01   | 02   |  90.0 |
| 01   | 01   |  80.0 |
| 02   | 03   |  80.0 |
| 02   | 02   |  60.0 |
| 02   | 01   |  70.0 |
| 03   | 03   |  80.0 |
| 03   | 02   |  80.0 |
| 03   | 01   |  80.0 |
| 04   | 03   |  20.0 |
| 04   | 02   |  30.0 |
| 04   | 01   |  50.0 |
| 05   | 02   |  87.0 |
| 05   | 01   |  76.0 |
| 06   | 03   |  34.0 |
| 06   | 01   |  31.0 |
| 07   | 03   |  98.0 |
| 07   | 02   |  89.0 |
| 08   | NULL |  NULL |
+------+------+-------+
#--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select stu.sname,c.cname,sc.score from sc  join student stu on stu.sid=sc.sid  join course c on c.cid=sc.cid where sc.score>70;
+--------+--------+-------+
| sname  | cname  | score |
+--------+--------+-------+
| 赵雷   | 英语   |  99.0 |
| 赵雷   | 数学   |  90.0 |
| 赵雷   | 语文   |  80.0 |
| 钱电   | 英语   |  80.0 |
| 孙风   | 英语   |  80.0 |
| 孙风   | 数学   |  80.0 |
| 孙风   | 语文   |  80.0 |
| 周梅   | 数学   |  87.0 |
| 周梅   | 语文   |  76.0 |
| 郑竹   | 英语   |  98.0 |
| 郑竹   | 数学   |  89.0 |
+--------+--------+-------+
# --37、查询不及格的课程
select DISTINCT cs.cname from sc join course cs  on cs.cid=sc.cid where sc.score<60; 
+--------+
| cname  |
+--------+
| 语文   |
| 数学   |
| 英语   |
+--------+
#--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select sc.sid,stu.sname from sc  join student stu on stu.sid=sc.sid where sc.cid=01 and sc.score>80;
Empty set (0.00 sec)
# --39、求每门课程的学生人数
select sc.cid,COUNT(sc.sid)from sc group by sc.cid;
+------+---------------+
| cid  | COUNT(sc.sid) |
+------+---------------+
| 01   |             6 |
| 02   |             6 |
| 03   |             6 |
+------+---------------+
# --40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select stu.sid,stu.sname,sc.score 
from sc join course c on sc.cid=c.cid 
join teacher t on t.tid=c.tid 
join student stu on sc.sid=stu.sid
where t.tname='张三' 
group by stu.sid,stu.sname,sc.score 
limit 1;
#--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select sc.sid,sc.cid,sc.score from sc join sc sc2 on sc.sid=sc2.sid where sc.score=sc2.score and sc.cid!=sc2.cid;
+------+------+-------+
| sid  | cid  | score |
+------+------+-------+
| 03   | 03   |  80.0 |
| 03   | 02   |  80.0 |
| 03   | 03   |  80.0 |
| 03   | 01   |  80.0 |
| 03   | 02   |  80.0 |
| 03   | 01   |  80.0 |
+------+------+-------+
#--42、查询每门功课成绩最好的前两名
(select sc.sid,sc.cid,sc.score,dense_rank()over (order by score desc) as r from sc where sc.cid=01 limit 2) 
union (select sc.sid,sc.cid,sc.score,dense_rank()over (order by score desc) as r from sc where sc.cid=02 limit 2) 
union (select sc.sid,sc.cid,sc.score,dense_rank()over (order by score desc) as r from sc where sc.cid=03 limit 2); 
+------+------+-------+---+
| sid  | cid  | score | r |
+------+------+-------+---+
| 01   | 01   |  80.0 | 1 |
| 03   | 01   |  80.0 | 1 |
| 01   | 02   |  90.0 | 1 |
| 07   | 02   |  89.0 | 2 |
| 01   | 03   |  99.0 | 1 |
| 07   | 03   |  98.0 | 2 |
+------+------+-------+---+
# --43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，
# 查询结果按人数降序排列，若人数相同，按课程号升序排列
select sc.cid,COUNT(sc.sid) c from sc group by sc.cid order by c desc,sc.cid;
+------+---+
| cid  | c |
+------+---+
| 01   | 6 |
| 02   | 6 |
| 03   | 6 |
+------+---+
# --44、检索至少选修两门课程的学生学号
select sid,count(cid) c from sc group  by sid having c>=2;
  +------+---+
| sid  | c |
+------+---+
| 01   | 3 |
| 02   | 3 |
| 03   | 3 |
| 04   | 3 |
| 05   | 2 |
| 06   | 2 |
| 07   | 2 |
+------+---+

# --45、查询选修了全部课程的学生信息
select sid,count(cid) c from sc group  by sid having c=3;
+------+---+
| sid  | c |
+------+---+
| 01   | 3 |
| 02   | 3 |
| 03   | 3 |
| 04   | 3 |
+------+---+

# --46、查询各学生的年龄
#1.只按年份来算
select year(now())-year(stu.sage) year from student stu;

+------+
| year |
+------+
|   32 |
|   32 |
|   32 |
|   32 |
|   31 |
|   30 |
|   33 |
|   32 |
+------+
#2.按出生日期来算，过了生日那一天肯定就是大一岁了
select 
if((month(stu.sage)=month(now()) and day(stu.sage)>day(now())) or month(stu.sage)>month(now()),YEAR(now())-YEAR(stu.sage),YEAR(now())-1-YEAR(stu.sage)) age from student stu;
+------+
| age  |
+------+
|   31 |
|   32 |
|   31 |
|   31 |
|   30 |
|   29 |
|   32 |
|   31 |
+------+
# --47、查询本周过生日的学生
select IF(week(stu.sage)=week(now()),stu.sname,0) from student stu;
+--------------------------------------------+
| IF(week(stu.sage)=week(now()),stu.sname,0) |
+--------------------------------------------+
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
+--------------------------------------------+

# --48、查询下周过生日的学生
select IF(week(stu.sage)=week(now()),stu.sname,0) from student stu;
+--------------------------------------------+
| IF(week(stu.sage)=week(now()),stu.sname,0) |
+--------------------------------------------+
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
| 0                                          |
+--------------------------------------------+

# --49、查询本月过生日的学生
select IF(month(stu.sage)=month(now()),stu.sname,0) from student stu;
+----------------------------------------------+
| IF(month(stu.sage)=month(now()),stu.sname,0) |
+----------------------------------------------+
| 0                                            |
| 钱电                                         |
| 0                                            |
| 0                                            |
| 周梅                                         |
| 0                                            |
| 0                                            |
| 0                                            |
+----------------------------------------------+

# --50、查询下月过生日的学生
select 
IF((MONTH(now())=12 and (MONTH(stu.sage)=MONTH(now())-11)) 
or (MONTH(stu.sage)=(MONTH(now())+1)),stu.sname,NULL) name
from student stu; 
+--------+
| name   |
+--------+
| 赵雷   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| NULL   |
| 王菊   |
+--------+

重戟宇
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql经典例题50道52
weixin_51630304的博客
08-12 261
mysql经典例题50道52初学mysql写了50道例题 初学mysql写了50道例题 建表：create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)); insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'
mysql50题
weixin_50968397的博客
05-08 285
查找的01和02课程是在同一张表中且在同一列，所以使用一张表进行查询比较不是很好做 思路：使用子查询，将01，02课程的分数和sid分别查询出来做成两个表，然后再通过两张表中相同的sid进行分数比较 select s.*, a.SCore as SCore_01, b.SCore as SCore_02 from Student s, (select sid, SCore from SC where cid=01) a, (select sid, SCore from SC where cid=02) b
《《MySQL编程基础》练习题库（全）》试卷.doc
06-12
MySQL数据库开发 ，让初学者能达到熟悉MySQL数据库并灵活运用的程度针对每一个所讲解的知识点都进行了深入地分析，并使用生动形象的情境举例，将原本复杂的、难于理解的知识点和问题进行简化，真正遵循了由浅入深、由易到难的学习过程。
MySQL15道经典练习题
weixin_64313980的博客
07-04 3946
前言：本期博客就不标明目录了，我们直接上题目，这一期内容主要是唤醒哪些沉睡在大家心中的之前的SqlServer与oralc的知识点，以及巩固我们的MySQL，其中较难的知识点可能就是联表了，什么内联，外联，左联右联让人头皮发麻，但是不要慌，跟着小刘的步伐慢慢理清思路首先是我们的建表，这里已经把需要的表的代码都交代上了一共四张表,学生，教师，成绩，课程 然后来看看我们的题目： 接下来就是我们的解题部分：01）查询" 01 "课程比" 02 "成绩高的学生的信息及课程分数 语句： 结果： 02）查询同
MySQL SQL语句练习题
喝醉酒的小白
08-04 5698
34道MySQL作业题
MySQL学习：50道经典题目（含参考答案）(更新中）
weixin_42661635的博客
04-27 8791
首先根据course_id将成绩表分为两张表，筛选出" 1 "课程比" 2 "课程成绩高的学生，再与学生表关联。1. 查询" 1 "课程比" 2 "课程成绩高的学生的信息（学号、姓名、性别、出生日期）及课程分数。利用自连接，统计比当前分数高的分数有几个，排名就是几加1。使用case when语句，多个排序条件时用逗号分隔。利用课程数量，计算出所有课程数和每个学生的选课数量。先筛选与" 1 "号同学选课数量相同的同学，再剔除。10. 查询没有学全所有课程的同学的信息。选了" 1 "号同学没选的课的同学。
Mysql经典45道题(详解1-15)
热门推荐
Hudas的博客
02-10 1万+
Mysql经典45道题题解(1-15)
MySQL经典50题1
08-03
这篇“MySQL经典50题1”提供了丰富的练习，旨在帮助用户深入理解和熟练掌握MySQL的基础操作和高级功能。以下是一些可能涵盖的知识点： 1. **建表和插入数据**： - SQL语句的结构：CREATE TABLE用于创建新表，...
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做...
mysql经典50题_大数据_mysql经典50题_mysql经典五十题_hive_
10-01
下面，我们将深入探讨MySQL的经典问题以及它们与Hive的相关性。 1. **SQL基础** - **数据类型**：MySQL支持多种数据类型，如整型（INT, TINYINT等）、浮点型（FLOAT, DOUBLE）、字符串（VARCHAR, CHAR）和日期时间...
MySQL经典50题tidyverse版1
08-04
【MySQL经典50题tidyverse版1】这篇文章主要展示了如何使用R语言的tidyverse包来解决一些基本的SQL查询问题。tidyverse是一系列R包的集合，旨在提供一个统一的、高效的处理和分析数据的环境。下面将详细解释如何使用...
MySQL基础篇 | 经典三十四道练习题
m0_61933976的博客
09-18 5999
关于MySQL的三十四道练习题，大佬们都会做吗
MySQL实操题
Candy_candy_appl的博客
11-30 113
SELECT COUNT(*)AS 总数 FROM bookinfo WHERE B_Publisher='清华大学出版社' AND B_Date > '2014-07-01';(7)将Orders表中会员 linli的订单的订单状态（O_Status）全部修改为2。(3)对 BookInfo表按市场价格降序排序，市场价格相同的按出版日期升序排序。(5）统计Orders表中每天的订单总额，并按照订单总额进行降序排序。(4）统计Orders表中每个会员的订单总额。
MYSQL期末总结+题
浪漫的诗人博客
06-18 2398
(1）创建一个企业员工管理的数据库，数据库名称为“userdb”。要求判断建库库时是否已存在。数据库字符集要求设置为支持中文字符的字符集“gbk”，字符集校验规则设置为gbi_chinese_ci。 (2)建立数据表 (3)为雇员表创建外键fk_dm参照完整性约束，被参照的父表是部门表。定义参照约束操作策略为:删除操作置空值，更新操作级联。 （4）为雇员薪水表增加一列实际薪资RealIncome，类型为整型，可以为空。（增加字段） （5）向部门表Departments 中插入记录。 (6）查询各部门员工的
Mysql语法+编程题汇总
xc_nrn的博客
03-18 1097
mysql中一些常见的语法和实践过程
Mysql代码练习题
weixin_44060484的博客
03-05 507
1.写出dept（部门表）与salgrade（工资等级表）的建表语句 create table dept( deptno int, dname varchar(20), loc varchar(20) ); create table salgrade( grade int, losal int, hisal int ); 2.在表emp中,查询出工资最高的员工职位 select job,max(s...
[Mysql] 经典 50 题
licn188的博客
07-22 7093
Mysql 经典 50题
MYSQL
最新发布
maoqiwei的博客
07-23 261
1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint 2.为Course表中Cno 课程号字段设置索引,并查看索引 3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩5.删除所有索引
mysql经典50题pdf
09-25
MySQL经典50题PDF是一份内容丰富、涵盖各个方面的MySQL面试题集合。这份PDF中包含了MySQL数据库的基础知识、数据查询、索引优化、性能调优等方面的问题。 MySQL是一种流行的关系型数据库管理系统，广泛应用于各种...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值