《SQL必知必会》读书笔记,30分钟入门SQL!(四)

2. 写一个函数,获取第 N 高的分数

create function getNthHighestScore(N int) return int
begin
  declare M int default N-1;
  return (
    select distinct score from student
    order by score desc limit M, 1;
  )
end;
select getNthHighestScore(2);

Result:

《SQL必知必会》读书笔记,30分钟入门SQL!(四)

3. 检索每个班级分数前两名学生,并显示排名

select 
class.id class_id,
class.name class_name,
s.name student_name,
score,
rank
from (
  select *,
(
    select count(distinct score) from student s2
where s2.score >= s1.score
and s2.class_id = s1.class_id
  ) as rank 
from student s1
) as s
left join class on s.class_id = class.id
where rank <= 2;
--如果不想在from中包含select子句,也可以像如下检索,不过不显示排名
select 
class.id class_id,
class.name class_name,
s1.name name,
score
from student s1
left join class on s1.class_id = class.id
where (
select count(*) from student s2
where s2.class_id = s1.class_id
and s1.score <= s2.score) <= 2
order by s1.class_id, score desc;


Result:


《SQL必知必会》读书笔记,30分钟入门SQL!(四)

FAQ

1. inner join 与 outer join 的区别是什么

2. 如何根据一个表的数据更新另一个表

比如以上 student 表保存着成绩,另有一表 score_correct 内存因失误而需修改的学生成绩。

在mysql中,可以使用如下语法

update 
student,
score_correct
set student.score = score_correct.score
where student.id = score_correct.uid;


3. 索引是如何工作的

简单来说,索引分为 hashB-Tree 两种。hash 查找的时间复杂度为O(1)。B-Tree 其实是 B+Tree,一种自平衡多叉搜索数,自平衡代表每次插入和删除数据都会需要动态调整树高,以降低平衡因子。B+Tree 只有叶子节点会存储信息,并且会使用链表链接起来。因此适合范围查找以及排序,不过只能搜索最左前缀,如只能索引以 a开头的姓名,却无法索引以 a结尾的姓名。另外,Everything is trade off。B+Tree的自平衡特性保证能够快速查找的同时也降低了更新的性能,需要权衡利弊。

4. 如何联接多个行的字段

在mysql中,可以使用 group_concat

select group_concat(name) from student;


5. 如何在一个sql语句中插入多行数据

values 使用逗号相隔,可以插入多行数据

insert into student(id, name) values (), (), ()


6. 如何在 select中使用条件表达式

示例,在student表中,查询所有人成绩,小于60则显示为0

select id, name, if(score < 60, 0, score) score from student;


7. 如何找到重复项

select name, sex, count(*) times from student
group by name, sex
having times > 1;


8. 什么是SQL注入

如有一条查询语句为

"select * from (" + table + ");"


当table取值 student);drop table student;-- 时,语句变为了,会删掉表,造成攻击。

"select * from (student); drop table student; --);"
上一篇:2016年12月25日 星期日 --出埃及记 Exodus 21:20


下一篇:8421BCD码转换为十进制