Mysql
ZYXGW
这个作者很懒,什么都没留下…
展开
-
Mysql必练50题(第十天)
第四十六天查询各学生的年龄(周岁)select s_id,s_name,s_birth,if( month(current_date()) < month(s_birth) or (month(current_date()) = month(s_birth) and day(current_date()) < day(s_birth)), year(current_date()) - year(s_birth) -1, year(current_原创 2021-11-25 10:00:07 · 1690 阅读 · 0 评论 -
Mysql必练50题(第九天)
第四十一题查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩select s_id,c_id,s_score from score where s_score in (select s_score from score group by s_score having count(*)>1);第四十二题查询每门课程成绩最好的前三名select sc.c_id,c.c_name,s.s_id,s.s_name,sc.s_score from student s inner join原创 2021-11-24 14:11:46 · 595 阅读 · 0 评论 -
Mysql必练50题练习(第八天)
第三十六题查询任何一门课程成绩在70分以上的学生姓名、课程名称和分数select s.s_name,c.c_name,sc.s_scorefrom student s inner join score sc on s.s_id = sc.s_idinner join course c on sc.c_id = c.c_idgroup by sc.s_id having sc.s_score>70;第三十七题查询课程不及格的学生select s.s_name,c.c_name,原创 2021-11-23 19:49:20 · 313 阅读 · 0 评论 -
Mysql必练50题(第七天)
第三十一题select * from student where year(s_birth) = 1990;第三十二题查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列select sc.c_id,round(avg(sc.s_score),2) as avg_score,c.c_name from score sc left join course c on sc.c_id = c.c_id group by sc.c_idorder by avg_scor原创 2021-11-22 12:13:46 · 193 阅读 · 0 评论 -
Mysql必练50题(第六天)
第二十六题查询每门课程被选修的学生数select sc.c_id,c.c_name,count(*) as cntfrom score sc left join course c on sc.c_id = c.c_id group by sc.c_id;第二十七题查询出只有两门课程的全部学生的学号和姓名select s.s_id,s.s_name from student s inner join score sc on s.s_id = sc.s_id group by sc.s_原创 2021-11-21 12:58:21 · 204 阅读 · 0 评论 -
Mysql必练50题第五天
第二十一题查询不同老师所教不同课程平均分从高到低显示select t_name,c_name,round(avg(sc.s_score),2) as avg_score from score sc left join course c on sc.c_id = c.c_id left join teacher t on c.t_id = t.t_id group by t.t_id order by avg_score desc;第二十二题查询所有课程的成绩第2名到第3名的学生信息及该课程成原创 2021-11-19 20:43:17 · 359 阅读 · 0 评论 -
Mysql必练50题(第四天)
第十六题检索"01"课程分数小于60,按分数降序排列的学生信息select * from student where s_id in (select s_id from score where c_id = 1 and s_score < 60);第十七题select s.s_id,s.s_name,sum(case c_id when 1 then s_score else 0 end) as "语文",sum(case c_id when 2 then s_score else原创 2021-11-16 10:53:17 · 503 阅读 · 0 评论 -
Mysql必练50题(第三天)
第十一题查询没有学全所有课程的同学的信息select s.* from student s left join score sc on s.s_id = sc.s_idgroup by s_id having count(*)<3;第十二题查询至少有一门课与学号为"01"的同学所学相同的同学的信息select distinct s.* from student s inner join score scon s.s_id = sc.s_id where c_id in(select原创 2021-11-15 14:21:42 · 645 阅读 · 0 评论 -
Mysql必练50题(第二天)
第六题查询"李"姓老师的数量select t_name,count(*) from teacher where t_name like "李%";第七题查询学过"张三"老师授课的同学的信息select s.* from student s inner join score sc on s.s_id = sc.s_id inner join course c on sc.c_id = c.c_idinner join teacher t on c.t_id = t.t_idwhere t原创 2021-11-14 15:39:44 · 235 阅读 · 0 评论 -
MYSQL必练50题(第一天 )
一、表和库的建立create database mysql_test;use mysql_test;drop table student;drop table score;drop table course;drop table teacher;create table student( s_id int, s_name varchar(8), s_birth date, s_sex varchar(4));insert into stu原创 2021-11-13 20:46:26 · 426 阅读 · 0 评论