山东网站建设找哪家,南通网站怎么推广,做衣服网站的实验感想,wordpress关键词采集文章文章目录前言1、热身题实践其他前言
一直有个想法#xff1a;把面试需要的知识点全都总结一下#xff0c;包括数据库#xff0c;语言#xff0c;算法#xff0c;数据结构等知识#xff0c;形成一个面试总结笔记#xff0c;这样以后面试的时候只看这些文章回顾下就行了。…
文章目录前言1、热身题实践其他前言
一直有个想法把面试需要的知识点全都总结一下包括数据库语言算法数据结构等知识形成一个面试总结笔记这样以后面试的时候只看这些文章回顾下就行了。今天就先总结下Mysql的面试热身题吧后续会总结其他方面的点。当然文章同样会不定时更新。
1、热身题实践
说明以下五十个语句都按照测试数据进行过测试最好每次只单独运行一个语句。
问题及描述
--1.学生表
Student(S#,Sname,Sage,Ssex) --S# 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
--2.课程表
Course(C#,Cname,T#) --C# --课程编号,Cname 课程名称,T# 教师编号
--3.教师表
Teacher(T#,Tname) --T# 教师编号,Tname 教师姓名
--4.成绩表
SC(S#,C#,score) --S# 学生编号,C# 课程编号,score 分数
*/
--创建测试数据
create table student(sno 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 , 男);
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 , 女);create table course(cno varchar(10),cname varchar(10),tno varchar(10));
insert into course values(01 , 语文 , 02);
insert into course values(02 , 数学 , 01);
insert into course values(03 , 英语 , 03);create table teacher(tno varchar(10),tname varchar(10));
insert into teacher values(01 , 张三);
insert into teacher values(02 , 李四);
insert into teacher values(03 , 王五);create table sc(sno varchar(10),cno varchar(10),score int(4));
insert into sc values(01 , 01 , 80);
insert into sc values(01 , 02 , 90);
insert into sc values(01 , 03 , 99);
insert into sc values(02 , 01 , 70);
insert into sc values(02 , 02 , 60);
insert into sc values(02 , 03 , 80);
insert into sc values(03 , 01 , 80);
insert into sc values(03 , 02 , 80);
insert into sc values(03 , 03 , 80);
insert into sc values(04 , 01 , 50);
insert into sc values(04 , 02 , 30);
insert into sc values(04 , 03 , 20);
insert into sc values(05 , 01 , 76);
insert into sc values(05 , 02 , 87);
insert into sc values(06 , 01 , 31);
insert into sc values(06 , 03 , 34);
insert into sc values(07 , 02 , 89);
insert into sc values(07 , 03 , 98);1、查询01课程比02课程成绩高的学生的信息及课程分数 方法一连表查询SELECT * FROM student sLEFT JOIN sc on s.snosc.snoLEFT JOIN sc sc1 on s.snosc1.snoWHERE sc.cno01and sc1.cno02and sc.scoresc1.score方法二形成子表然后再连表查询select a.S# from (select s#,score from SC where C#001) a,(select s#,score from SC where C#002) b where a.scoreb.score and a.s#b.s#;(注意子查询的使用场景)方式三注意限制条件添加join条件中对左表没有约束左表没有被限制条件限制SELECT * FROM student sLEFT join sc on s.Snosc.Sno and sc.cno01LEFT join sc sc1 on s.Snosc1.Sno and sc1.cno02WHERE sc.scoresc1.score2、查询同时存在01课程和02课程的情况
SELECT *
FROM student s
LEFT join sc on s.Snosc.Sno and sc.cno01
LEFT join sc sc1 on s.Snosc1.Sno and sc1.cno02
WHERE sc.score is not NULL and sc1.score is not null3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SELECT s.sno,s.sname,avg(sc.score)
FROM student s
LEFT join sc on s.snosc.sno
GROUP BY s.sno
HAVING avg(sc.score)60
ORDER BY avg(sc.score) desc4、查询在sc表存在成绩的学生信息的SQL语句。
SELECT * FROM Student s
LEFT JOIN SC on s.SnoSC.Sno
GROUP BY s.Sno
HAVING count(score)05、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SELECT s.Sno , Sname, count(Cno),sum(score)
from Student s left join SC on s.SnoSC.Sno
GROUP BY s.Sno6、查询李姓老师的数量
SELECT count(*) FROM Teacher t WHERE t.Tname LIKE 李%7、查询学过张三老师授课的同学的信息
方法一连表查询
SELECT *
FROM student s
LEFT join sc on s.snosc.sno
left join course c on c.cnosc.cno
LEFT join teacher t on c.tnot.tno
WHERE t.tname张三
方法二利用子查询
SELECT sname,sno
FROM student
WHERE sno in
(SELECT distinct sno
FROM sc
WHERE cno in(
SELECT c.cno
FROM course c
LEFT JOIN teacher t on c.tnot.tno
WHERE t.tname张三))8、查询没学过张三老师授课的同学的信息
#注意子查询的使用场景
SELECT * FROM Student WHERE Sno not in
(SELECT s.Sno FROM
Student s LEFT join SC on s.SnoSC.Sno
LEFT join Course c on SC.Cnoc.Cno
LEFT join Teacher t on c.Tnot.Tno
WHERE t.Tname 张三)9、查询学过编号为01但是没有学过编号为02的课程的同学的信息 方法一连表查询SELECT DISTINCT s.sno,s.snameFROM student s LEFT JOIN sc on s.sno sc.sno LEFT JOIN sc s1 on s1.snos.snoWHERE sc.cno01 and s1.cno!02方法二 #注意子查询的灵活使用SELECT s.*FROM student sLEFT join sc on s.snosc.snoWHERE sc.cno01and s.sno in(SELECT DISTINCT s1.sno FROM student s1LEFT join sc sc1 on s1.snosc1.snoWHERE sc1.cno!02)10、查询没有学全所有课程的同学的信息
SELECT s.*
FROM Student s
LEFT join SC on s.SnoSC.Sno
GROUP BY s.Sno
HAVING count(Cno)
(select count(C#) from SC)
#注意group by之后是对相应的属性做限制之后将满足限制的所有数据筛选出来(分成多少组有多少数据)11、查询至少有一门课与学号为01的同学所学相同的同学的信息
SELECT DISTINCT student.sno,sname
FROM student
LEFT JOIN sc on student.snosc.sno
WHERE sc.cno in
(SELECT cno
FROM sc
WHERE sno01)
and sc.sno!0112、查询两门及其以上不及格课程的同学的学号姓名及其平均成绩
#注意不能在where子句中限制组函数如where max(score)60
SELECT sc.sno,sname,avg(score)
FROM student s
LEFT JOIN sc on s.snosc.sno
where s.sno in (
SELECT sc.sno
FROM student s
LEFT JOIN sc on s.snosc.sno
WHERE sc.score60
GROUP BY sc.sno
HAVING count(*)2)group by sno 13、检索01课程分数小于60按分数降序排列的学生信息
SELECT *
FROM student s LEFT JOIN sc
on s.snosc.sno
WHERE sc.cno01
and sc.score60
ORDER BY score desc14、查询每门课程被选修的学生数 SELECT cno,count(sno)FROM scGROUP BY cno 15、查询出只有两门课程的全部学生的学号和姓名
SELECT s.sno,sname
FROM student s LEFT JOIN sc
on s.snosc.sno
GROUP BY s.sno
HAVING count(*)216、查询1990年出生的学生名单
SELECT s.*
FROM Student s
WHERE s.Sage BETWEEN1990-01-01 and 1990-12-3117、查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列
SELECT cno,avg(score)
FROM sc
GROUP BY cno
ORDER BY avg(score) desc,cno asc18、查询任何一门课程成绩在70分以上的姓名、课程名称和分数。 SELECT s.sname,c.cname,scoreFROM student s LEFT join scon s.snosc.snoLEFT JOIN course c on sc.cnoc.cnoGROUP BY s.snoHAVING min(score)7019、查询选修张三老师所授课程的学生中成绩最高的学生信息及其成绩
SELECT s1.*,score
FROM student s1 LEFT JOIN sc
on s1.snosc.sno
WHERE sc.cno in(
SELECT c.cno
FROM teacher t LEFT JOIN course c
on t.tnoc.tno
WHERE t.tname张三)
ORDER BY score desc
LIMIT 120、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 SELECT DISTINCT sc.cno,sc.score,sc.snoFROM sc LEFT join sc sc1on sc.snosc1.snoWHERE sc.cno!sc1.cnoand sc.scoresc1.score21、查询各科成绩最好的前两/三名#重点注意
方法一连表查询重点参考
SELECT *
FROM sc a
LEFT join sc b on a.cnob.cno and a.scoreb.score#筛选我的成绩比别人的成绩差的数据
GROUP BY a.cno,a.sno
HAVING count(1)2#如果我的成绩比别人的成绩差的数据条数小于2也就代表有0或1个人的成绩比我好则对应的用户就被筛选出来了
直观的意义是取分组后的TOPN,实际操作时候只能是从每组里面取第一条数据只是利用多重分组来将满足条件的TopN条数据给筛选出来而已#方法二使用子查询
SELECT s1.*
FROM SC s1
WHERE
( SELECT COUNT(1)
FROM SC s2
WHERE s1.Cnos2.CnoAND s2.scores1.score
)2
ORDER BY s1.Cno,s1.score DESC
分析下这个sql
select * from test1 a where 2 (select count(*) from test1 where coursea.course and scorea.score)
相关子查询的特点就是子查询依赖与外部查询在这里面其实是 select * from test 已经先执行了一遍了查出了所有的数据
然后相关子查询针对每一行数据进行select count(*) from test1 where coursea.course and scorea.score
例如第一行是张三数学77那么相关子查询做的工作就是找出test表所有课程是数学的行查询 张三77|李四68|王五99然后where条件score77查询出王五99count1这时候外部条件21,符合。第二行是李四数学68那么相关子查询做的工作就是找出test表所有课程是数学的行查询 张三77|李四68|王五99然后where条件score68查询出张三77,王五99count2这时候外部条件22,不符合。第三行是王五数学99那么相关子查询做的工作就是找出test表所有课程是数学的行查询 张三77|李四68|王五99然后where条件score99没有数据这时候外部条件20,符合。那么就筛选出了数学最大的2个人张三和王五。
其实这里的子查询就是找出和当前行类型能匹配上的比他大的有多少没有比他大的他就是最大
那么找top1就是 1(xxx)topN就是N(xxxxx)#以下几个题目要了解下子查询在select字段时的原理 22、查询各科成绩最高分、最低分和平均分以如下形式显示课程ID课程name最高分最低分平均分及格率中等率优良率优秀率 –及格为60中等为70-80优良为80-90优秀为90
SELECT sc.cno as 课程ID,c.cname as 课程name,
max(sc.score) as 最高分,min(sc.score) as 最低分,avg(sc.score) as 平均分,
CAST(((select count(1) from sc s WHERE s.cnosc.cno and score60)*100/(select count(1) from sc s WHERE s.cnosc.cno)) as DECIMAL(18,2)) as 及格率,
cast((select count(1) from sc where cno c.cno and score 60)*100.0 / (select count(1) from sc where cno c.cno) as decimal(18,2)) as [及格率(%)]
FROM sc
LEFT JOIN course c on sc.cnoc.cno
GROUP BY sc.cno
#这里的s指的就是外表s按照sql的执行顺序从from到where-group by-having-select时
对应的表的形态。即整个查询没有select子查询作为字段时对应的输出。23、按各科成绩进行排序并显示排名
--Score重复时合并名次利用select字段里的子查询利用两张表的关联之后通过score的大小的个数来生成序列
select t.* , (select count(distinct score)
from sc
where cno t.cno
and score t.score) as px
from sc t
order by t.cno, px
#这里的s指的就是外表s按照sql的执行顺序从from到where-group by-having-select时对应的表的形态这里即
Select *
FROM sc s
ORDER BY s.cno,s.score desc
的输出结果
之后在select子查询中利用连表进行连接24、查询学生平均成绩及其名次 SELECT a.*,(SELECT count(1) FROM
(select s.*,avg(sc.score) as avg2
FROM student s
left JOIN sc on sc.snos.sno
WHERE avg2a.avg1
GROUP BY s.sno
ORDER BY avg(sc.score) desc) b
)
FROM
(select s.*,avg(sc.score) as avg1
FROM student s
left JOIN sc on sc.snos.sno
GROUP BY s.sno
ORDER BY avg(sc.score) desc) a# 实例说明原理 查询学生成绩及其名次
select t.*
FROM sc t
GROUP BY t.cno
ORDER BY cno,score desc主查询的输出结果
select t.*,
(SELECT count(DISTINCT score)
FROM sc
WHERE cnot.cno
and scoret.score)
FROM sc t
GROUP BY t.cno
ORDER BY cno,score desc最终输出结果 解释这里在子查询的t就是主查询的输出结果如上图所示然后拿t和sc做连接查询再加上scoret.score的限制条件得到最终结果即sc中对应的各科的成绩要大于t.score
25、查询每门课程的平均成绩结果按平均成绩降序排列平均成绩相同时按课程编号升序排列
#注意cast函数的应用
select cno,(CAST(avg(score) as DECIMAL(18,2))) as avg_score
FROM sc
GROUP BY cno
ORDER BY avg_score desc,cno desc26、查询选修张三老师所授课程的学生中成绩最高的学生信息及其成绩
SELECT sno
FROM sc
WHERE cno01
ORDER BY score desc
LIMIT 1
查询topn的另一种方法通过orderby排序后利用limit来实现27、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
#注意下几种join的链接结果
SELECT *
FROM sc a
join sc b
WHERE a.cno!b.cno
and a.scoreb.score
and a.sno!b.sno总结 内连接是最常见的一种连接只连接匹配的行 LEFT JOIN返回左表的全部行和右表满足ON条件的行如果左表的行在右表中没有匹配那么这一行右表中对应数据用NULL代替。 FULL JOIN 会从左表 和右表 那里返回所有的行。如果其中一个表的数据行在另一个表中没有匹配的行那么对面的数据用NULL代替
其他
累加求和套路求截止当前月份的累计消费额 练习数据 建表语句
create table test
(user VARCHAR(3),month DATE,salary NUMERIC(6)
);
INSERT INTO test(user, month, salary) VALUES (A, 2015-01-03 15:20:52, 33);
INSERT INTO test(user, month, salary) VALUES (A, 2015-01-05 15:20:52, 23);
INSERT INTO test(user, month, salary) VALUES (A, 2015-01-08 15:20:52, 66);
INSERT INTO test(user, month, salary) VALUES (A, 2015-02-08 15:20:52, 55);
INSERT INTO test(user, month, salary) VALUES (A, 2015-02-02 15:20:52, 35);
INSERT INTO test(user, month, salary) VALUES (B, 2015-02-02 15:20:52, 62);
INSERT INTO test(user, month, salary) VALUES (B, 2015-02-12 15:20:52, 52);
INSERT INTO test(user, month, salary) VALUES (B, 2015-01-12 15:20:52, 52);
INSERT INTO test(user, month, salary) VALUES (B, 2015-01-16 15:20:52, 23);数据展示
1、求每个用户每月的消费总和
SELECT user,date_format(month, %Y-%m),sum(salary)
from test
GROUP BY user,date_format(month, %Y-%m)2、将月总金额表 自己连接 自己连接
SELECT * FROM
(SELECT user,date_format(month, %Y-%m),sum(salary)
from test
GROUP BY user,date_format(month, %Y-%m)) a
join
(SELECT user,date_format(month, %Y-%m),sum(salary)
from test
GROUP BY user,date_format(month, %Y-%m)) b
on a.userb.user3、从上一步的结果中 进行分组查询分组的字段是a.username a.month 求月累计值 将b.month a.month的所有b.salary求和即可 最终求用户A和B每月累计消费总和
SELECT a.user,a.dt,max(a.money),sum(b.money) FROM
(SELECT user,date_format(month, %Y-%m) dt,sum(salary) money
from test
GROUP BY user,date_format(month, %Y-%m)) a
join
(SELECT user,date_format(month, %Y-%m) dt,sum(salary) money
from test
GROUP BY user,date_format(month, %Y-%m)) b
on a.userb.user
WHERE a.dtb.dt
GROUP BY a.user,a.dt
