create database school;
use school;
-- 创建学生表student
create table student(
s_id varchar(10) primary key,
s_name varchar(10) not null,
s_age date not null,
s_sex varchar(10) not null default '未知');
-- 导入学生表数据
insert into student(s_id,s_name,s_age,s_sex)
value('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1992-04-21' , '女'),
('08' , '王菊' , '1990-01-20' , '女');
-- 创建课程表course
create table course(c_id varchar(10) primary key,
c_name varchar(10) not null,
t_id varchar(10) not null);
-- 导入课程表数据
insert into course(c_id,c_name,t_id)
value('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');
-- 创建教师表teacher
create table teacher(t_id varchar(10) not null primary key,
t_name varchar(10) not null);
-- 导入教师表数据
insert into teacher(t_id,t_name)
value('01' , '张三'),
('02' , '李四'),
('03' , '王五');
-- 创建成绩表score
create table score(s_id varchar(10),
c_id varchar(10) not null,
score int not null);
-- 导入外部数据---------------示范，在本例子中不需要前面两个数据
#在选中要导入的数据文件按shift右击即可找到要复制的路径
路径不能出现中文,注意路径中的\改成/或者\\，路径要用英文状态的' ' 导入的表名与数据文件的命名不可以一样
#为Monthy_Indicator表导入外部txt文件
从文本导入load data local infile 'E:/Monthly_Indicator.txt'
into table monthy_indicator
fields terminated by '\t'
ignore 1 lines;）#不需要原始数据集标题时
从csv导入load data local infile 'E:/final3.csv' into table Final fields terminated by '\,';
-- 导入成绩表数据
-- 手打导入数据----------------------------------------------------------
#insert into 插入数据 字符串要用引号‘’，数值型的不用，要用逗号隔开
insert into score(s_id,c_id,score)
value('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);
-- 条件查询------------------------------------------
-- 1、查询学生表所有数据
select * from student;
-- 2、查询教师表所有数据
select * from teacher;
-- 3、查询课程表所有数据
select * from course;
-- 4、查询成绩表所有数据
select * from score;
-- 5、查询学号为01的学生信息
select * from student where s_id=01;
-- 6、查询男性同学的学生编号、学生姓名
select s_id,s_name from student where s_sex='男';
-- 7、查询本月过生日的同学及学生信息 curdate() curtime() now()
select curdate();#2019-07-24
select curtime();#19:12:23
select now();#2019-07-24 19:13:05
select * from student where month(curdate())=month(s_age);
-- 8、查询课程编号为01的课程名称
select c_name from course where c_id = '01';
-- 9、查询学生编号为01的学生总成绩：学生编号、总成绩
select s_id,sum(score) as 总成绩 from score group by s_id having s_id='01';
-- 10、查询学生编号为01的学生成绩总分、平均分、最高分、最低分
select sum(score) as 总分,avg(score) as 平均分,max(score) as 最高分,min(score) as 最低分 from score group by s_id having s_id='01';
-- 11、查询不及格的学生成绩信息
select * from student where s_id in (select s_id from score where score <60);
select * from score where score<60;
-- 12、查询学生编号不为01的其他同学的成绩信息
select * from score where s_id <>'01';
-- 13、查询成绩在60-80分闭区间的成绩信息
select * from score where score between 60 and 80;
select * from score where score>=60 and score <=80;
-- 14、查询成绩不在60-80分闭区间的成绩信息
select *from score where score not between 60 and 80;
select * from score where score < 60 or score >80;
-- 15、查询学生编号为01和02的学生信息
select * from student where s_id in('01','02');
-- 16、查询学生编号不为01和02的学生信息
select * from student where s_id not in('01','02');
select * from student where s_id <> '01' and s_id <> '02';
-- 17、查询学生编号为01或者02的课程编号为01的成绩信息 (and 和 or 的优先级)
select * from score where (s_id='01' or c_id='02') and c_id='01';
-- 18、查询名字中含有“风”字的学生信息 like '%风%'
select * from student where s_name like '%风%';
-- 19、查询“李”姓老师的数量 like '李%'
select count(*) from teacher where t_name like '李%';
-- 20、查询出现过学生考试不及格的课程ID distinct删除重复的记录
select distinct course.c_id from course
left join score on course.c_id=score.c_id where score.score<60;
select distinct c_id from score where score < 60;
-- 排序------------------------------------------------------------------------------------------------------
-- 21、按成绩升序显示成绩信息 默认是升序,升序是asc 降序是desc
select * from score order by score asc;
-- 22、按成绩降序显示成绩信息
select * from score order by score desc;
-- 23、按学生编号升序，课程编号降序显示成绩信息
select * from score order by s_id asc, c_id desc;
-- 24、查询课程编号为01的学生成绩，按分数降序，学生编号升序排列
select * from score where c_id='01' order by score desc,s_id asc;
-- 分组----------------------------------------------------------------------------------------------------
-- 25、查询每门课程被选修的学生数
select c_id,count(*) from score group by c_id;
-- 26、查询每位同学的平均成绩，并按分数降序、学生编号升序排列
select s_id,avg(score) from score group by s_id order by score desc,s_id asc;
-- 27、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id,avg(score) from score group by c_id order by avg(score),c_id asc;
-- 28、查询90后学生的男女数量，并按照数量降序排列 (扩展：横向显示)
select s_sex,count(*) from student where right(year(s_age),2)>=90 group by s_sex order by count(*);
select sum(s_sex='男') 90后男,sum(s_sex='女') 90后女 from student
where year(s_age)>=1990 group by s_sex order by count(s_sex) desc;
-- 30、查询不同性别、各年份的出生人数
select s_sex, year(s_age) 年份, count(*) from student group by s_sex, 年份;
select s_sex,year(s_age),count(*) from student group by s_sex,s_age;
-- 31、查询同名同姓学生名单，并统计有同名的学生姓名和同名人数
select s_name,count(*) from student group by s_name having count(*)>1;
-- 32、查询每门课程的平均成绩，并且平均成绩大于等于70分
select c_id, avg(score) 平均成绩 from score group by c_id having 平均成绩>=70;
-- 33、查询每位同学的平均成绩，并且平均分数>=60分
select s_id, avg(score) 平均成绩 from score group by s_id having 平均成绩>=60;
-- 34、查询至少选修了2门课的学生学号
select s_id, count(c_id) 课程数量 from score group by s_id having 课程数量>=2;
-- 35、查询至少两门课程及格的学生学号
select s_id from score where score > 60 group by s_id having count(*) >=2;
-- 36、查询成绩前三名的成绩信息 limit
select * from score order by score desc limit 0,3;-- 从第一名开始取3名
select * from score order by score desc limit 3;-- 默认取前面的三位
select * from score order by score desc limit 3 offset 0;
-- 37、查询成绩第三名到第五名的成绩信息
#limit里的第一个位置是从0开始的，因此第三个位置就是2，取3为到第五名
select * from score order by score desc limit 2,3;
-- 多表查询--------------------------------------------------------------------------------------------------
-- 38、连接学生表和成绩表
select * from score left join student on score.s_id=student.s_id;
-- 39、连接学生表、成绩表和课程表
select * from score left join student on score.s_id=student.s_id
left join course on score.c_id=course.c_id;
-- 40、连接四张表，删除不必要字段 left join ….on…..
select student.*,score,c_name,t_name from score
left join student on score.s_id=student.s_id
left join course on score.c_id=course.c_id
left join teacher on teacher.t_id=course.t_id;
-- 41、将学生表的s_id、s_name与课程表的c_id、c_name做纵向链接
#注意区分union和union all （一般包含有重复项）
select s_id,s_name from student
union
select c_id,c_name from course;
-- 子查询-----------------------------------------------------
-- 42、查询比学生编号为02的学生的所有课程成绩都高的成绩信息
select * from score where score> all (select score from score where s_id='02');
-- 43、查询比学生编号为02的学生的任意一门课程成绩高的成绩信息
select * from score where score >any(select score from score where s_id='02');
-- 44、查询平均成绩大于等于60分的学生的成绩信息
select * from score where s_id in
(select s_id from score group by s_id having avg(score)>=60);
select * ,avg(score) from score group by s_id having avg(score)>=60;
-- 45、查询选修了全部课程的学生信息_
select student.*,group_concat(c_id) from student
left join score on student.s_id=score.s_id
group by student.s_id having count(c_id)=(select count(c_id) from course);
select student.*,group_concat(c_id) from student,score
where student.s_id=score.s_id
group by student.s_id having count(c_id)=(select count(c_id) from course);
-- 函数------------------------------------------------------
-- 46、查询学生01选了哪些课程,放一个单元格内 group_concat
select group_concat(course.c_name) from score
left join course on score.c_id=course.c_id and score.s_id='01';
select group_concat(course.c_name) from course,score
where score.c_id=course.c_id and score.s_id='01';
select student.s_name,group_concat(course.c_name)from score
left join course on score.c_id=course.c_id left join student on score.s_id=student.s_id
where student.s_id='01';
-- 47、针对成绩表，添加新列(>90 优秀，>80 良好， >=60 及格，<60不及格) IF / case when
/*case when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
....
else 结果n
end (as //)命名 */
select *,if(score>90,'优秀',if(score>80,'良好',if(score>=60,'及格','不及格'))) 等级 from score;
select *, case
when score>90 then '优秀'
when score>80 then '良好'
when score>=60 then '及格'
else '不及格'
end
from score;
when score<=80 then '及格'
when score<=90 then '良好'
else '优秀'
end
as 成绩等级
from score;
select *,if(score>90,'优秀',if(score>80,'良好',if(score>60,'及格','不及格')))
as 成绩等级 from score;
-- 48、查询所有和学生01选修完全一样的同学信息
select s_id,group_concat(c_id) 课程 from score
group by s_id having 课程=(select group_concat(c_id) from score where s_id='01' group by s_id ) and s_id<>01;
concat_ws( )
1，功能跟concat() 一样，将多个字符连接成一个字符串，但是可以一次
性指定分隔符~（concat_ws就是concat with separator（分隔符））
create table pjm(select s_id,group_concat(c_id) 课程 from score
group by s_id having 课程=(select group_concat(c_id) from score
where s_id='01' group by s_id ) and s_id<>01);
select concat_ws(',','课程1','课程2','课程3') from pjm;
select s_id,group_concat(c_id order by c_id desc) 课程 from score
group by s_id;
-- 49、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select s_name, score from student
left join score on student.s_id = score.s_id
left join course on score.c_id = course.c_id
where c_name = '数学' and score < 60;
select s_name, score from student,score,course
where student.s_id = score.s_id and score < 60
and score.c_id = course.c_id and c_name = '数学';
-- 50、查询学生姓名并且为每个姓名前加“学生”两个字
#文本合并用concat（）
select concat('学生',s_name) 姓名 from student;
-- --提高---------------------------------------------------------------
-- 51、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select student.s_id,s_name,sc1.score 课程一,sc2.score 课程二 from
student join
(select s_id,score from score where c_id=01) as sc1 on student.s_id=sc1.s_id
join
(select s_id, score from score where c_id=02) as sc2 on student.s_id=sc2.s_id
where sc1.score>sc2.score;
select student.s_id, s_name,s1.score 课程一, s2.score 课程二 from student
join score s1 on student.s_id=s1.s_id and s1.c_id=01#注意这里省略了一个as
join score s2 on student.s_id=s2.s_id and s2.c_id=02#注意这里省略了一个as
where s1.score>s2.score;
select student.s_id,s_name,s1.score 课程一, s2.score 课程二
from student, score s1, score s2
where student.s_id=s1.s_id and s1.c_id=01
and student.s_id=s2.s_id and s2.c_id=02
and s1.score>s2.score;
-- 52、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
(inner join中inner是可以省略的）
select student.*,sc1.c_id,sc2.c_id from student
join score sc1 on student.s_id=sc1.s_id and sc1.c_id='01'
join score sc2 on student.s_id=sc2.s_id and sc2.c_id='02';
-- 53、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select student.*,sc1.c_id,sc2.c_id from student
left join score sc1 on student.s_id=sc1.s_id and sc1.c_id='01'
left join score sc2 on student.s_id=sc2.s_id and sc2.c_id='02'
where sc2.c_id is null and sc1.c_id='01';
select student.*,sc1.c_id,sc2.c_id from student
join score sc1 on student.s_id=sc1.s_id and sc1.c_id='01'
left join score sc2 on student.s_id=sc2.s_id and sc2.c_id<>'02'and sc2.c_id<>'01';
-- 54、查询成绩表的不及格率
-- 法一：
set @allcount = (select count(*) from score);
select count(*)/@allcount from score where score<60;
-- 法二：
select count(*)/(select count(*) from score) from score where score<60;
-- 法三:
select sum(score<60)/count(*) from score;
-- 法四:
select avg(score<60) from score;
-- 55、统计各科成绩各分数段：课程编号,课程名称,(0-60]不及格率,(60,100]及格率,(70,85]优良率,(85,100]优秀率
select score.c_id,c_name,
sum(case when score>0 and score<=60 then 1 else 0 end)/count(*) 不及格率,
sum(case when score>70 and score<=85 then 1 else 0 end)/count(*) 优良率,
sum(case when score>85 and score<=100 then 1 else 0 end)/count(*) 优秀率,
sum(case when score>60 and score<=100 then 1 else 0 end)/count(*) 及格率
from score left join course on score.c_id=course.c_id group by score.c_id;
-- 56、成绩表按分数排序，并显示排名
set @rank :=0;#冒号可以省略，但是@不可以省略
select*,@rank := @rank+1 排名 from score order by score desc;
-- 57、查询学生的总成绩并进行排名
set @rank :=0;
select*,@rank := @rank+1 总成绩排名 from
(select *,sum(score) from score group by s_id order by sum(score) desc) as 总成绩;
-- 58、查询下月过生日的学生
select * from student where
month(s_age)=case when month(curdate())+1>12 then month(curdate()) else month(curdate())+1 end;
-- 59、查询本周过生日的学生
select curdate();#2019-07-25
select weekday(curdate())+1;#3
select subdate(curdate(),2); #2019-07-23 查看指定日期的前几天，2是和这个日期相隔的前2天，要表示未来的两天，要用-2
select date_format(curdate(),'%m-%d');#07-25 date_fromat修改日期格式
删除数据表
drop table emp; #或者truncate emp / delete from emp
1534
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
