炼码LintCode–数据库题库（级别：简单；数量：55道）–刷题笔记_02

炼码LintCode–数据库题库（级别：入门；数量：144道）–刷题笔记_01

炼码LintCode–数据库题库（级别：中等；数量：更新中~）–刷题笔记_03

3618 · 耗时前三的任务（日期差）

题：

sql：

select id , DATEDIFF(end_date , start_date) as 'diff' from Tasks
order by diff  desc limit 3;

解释：

DATEDIFF 天数差

-- 日期天数差
DATEDIFF(date1(新日期), date2(旧日期))：计算两个日期之间的天数差。

order by 别名

order by (别名diff) ：在内部执行过程中，sql引擎已经计算出了列的值和别名,因此可以在 ORDER BY 子句中使用这些别名。

TIMESTAMPDIFF 月、年差

-- 如果是计算月份差的话，可以用这个函数
TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-11-14')

-- 计算年份差
TIMESTAMPDIFF(YEAR, date1, date2)

3616 · 整合成绩单

题：

sql:

-- join(内连接) 只有那些在两个表中都有出现的学生（名字相同）才会出现在结果中

select c1.name , c1.score as 'score1' ,c2.score as 'score2' from course1_score c1 
join course2_score c2 on c1.name = c2.name
order by c1.name asc;

3614 · 查询客户的推荐人

题：

sql:

-- 注意点：null 也要算进去

select name from customer where referrer_id != 1 or referrer_id is null;

3613 · 查询成绩排名在第二到第五的学生

题：

sql:

select
 ROW_NUMBER() OVER (ORDER BY (course1 + course2 + course3) DESC, sno ASC) AS score_rank,
 sno , 
 (course1 + course2 + course3) as 'total_score' from score 
order by total_score desc , sno asc 
limit 1,4;

解释

ROW_NUMBER() 开窗函数排序

开窗函数 ROW_NUMBER() 详细解释

limit

limit 语法： LIMIT [offset], [count]
-- offset 是你希望跳过的行数。
-- count 是你希望返回的行数。

LIMIT 0, 1: 从第 0 行开始，返回 1 条数据。
LIMIT 1, 2: 从第 1 行开始，返回 2 条数据。

limit 1 ：表示返回一条数据

如：a,b,c,d,e,f,g,h
limit 0： 返回0条数据
limit 1： 返回1条数据： a
limit 2： 返回2条数据： a,b
limit 0,1 跳过0行数据，返回1条数据：返回：a
limit 1,2 返回：b,c（这里1表示跳过第一条数据，返回2条数据）
limit 5,1 返回：f  （这里5表示跳过前面5条数据，返回接下来的1条数据）
limit 5,2 返回：f,g

limit 计算是从0开始的。比如a（0），b（1）

3610 · 所有学生都选修的课程

题：

sql:

-- 因为要查所有学生都选修的课程，也就是所有学生的数量=该课程出现的次数

SELECT course_id
FROM courses
GROUP BY course_id
HAVING COUNT(DISTINCT student_id) = (SELECT COUNT(DISTINCT student_id) FROM courses)
order by course_id asc ;

解释

having、count、distinct

-- 因为要查所有学生都选修的课程，也就是所有学生的数量=该课程出现的次数
-- 因为学生会选多门课程，所以子查询的 student_id 要去重
HAVING COUNT(distinct student_id) = (SELECT COUNT(DISTINCT student_id) FROM courses)

3607 · 考试通过的人数

题：

sql:

select 

st.class , 
-- SUM() 函数会把每一行的布尔表达式结果（TRUE 或 FALSE）转换为 1 或 0，然后对所有行进行求和。因此，它可以准确地统计满足条件的行数。
sum(sc.course1 >= 60 and sc.course2 >= 60 and sc.course3 >= 60) as 'pass_count'

from  students st join score sc on st.sno = sc.sno
group by st.class 
having sum(sc.course1 >= 60 and sc.course2 >= 60 and sc.course3 >= 60);

解释

sum 数值和表达式区分

-- sum(某列) : 如果sum()里面具体是数值，那么就会进行求和。
-- sum(表达式 true或false)：true 会等于1 ，false = 0，然后再求和。

-- SUM() 函数会把每一行的布尔表达式结果（TRUE 或 FALSE）转换为 1 或 0，然后对所有行进行求和。因此，它可以准确地统计满足条件的行数。

sum(sc.course1 >= 60 and sc.course2 >= 60 and sc.course3 >= 60) as 'pass_count'

3501 · 最棒的销售

题：

sql:

-- 写法1：
select id from sales 
where sales_sum = (select sales_sum from sales order by sales_sum desc limit 1);

-- 写法2：  

select id from  sales 
where sales_sum = (select max(sales_sum) from sales);

解释

直接使用子查询把最高的销售额查出来即可。
用 max 函数 或者 order by 获取第一个数据。

2812 · 存储过程变量（一)

题：

sql:

-- 设置存储过程的语句分隔符

-- DELIMITER $$ 

-- 创建一个名为：GetTotalTeacher 的存储过程
CREATE PROCEDURE GetTotalTeacher()

-- 存储过程开始：开始编写sql语句，实现逻辑代码
begin

-- 声明一个变量 totalTeacher， 默认值为 0
declare totalTeacher int default 0;

-- 获取teachers 表中的教师数量，用 into 关键字赋值给 totalTeacher 变量
select count(*) into totalTeacher from teachers;

-- 返回 totalTeacher 变量的值
select totalTeacher ;

-- 存储过程结束
end;

-- 将 $$ 分隔符恢复成默认的分号 ; 作为分隔符
-- DELIMITER ; 

解释

这里如果分隔符不注释掉的话，会报这个错，但是我看分隔符好像没啥问题。

2811 · 存储过程 INOUT 参数（二）

题：

sql:

CREATE PROCEDURE UpdateStudentCount(
    --inout 参数：表示输入输出参数，这个参数既可以在调用时传递一个初始值，也可以在存储过程内部被修改。存储过程内部的修改会影响传递给它的原始变量
    inout count int,
    -- in 参数：表示输入参数，这个参数的值在调用存储过程时传递给它，并且存储过程不能修改这个值。它是只读的。
    in des int
)
begin
    set count = count - des;
    -- 修改人数
    update courses set student_count = count  where name = 'Django';
end;

解释

-- 创建一个名为 UpdateStudentCount 的存储过程，然后有两个参数
CREATE PROCEDURE UpdateStudentCount(
    --inout 参数：表示输入输出参数，这个参数既可以在调用时传递一个初始值，也可以在存储过程内部被修改。存储过程内部的修改会影响传递给它的原始变量
    inout count int,
    -- in 参数：表示输入参数，这个参数的值在调用存储过程时传递给它，并且存储过程不能修改这个值。它是只读的。
    in des int
)

简单来说，inout 参数值可变 ， in 参数值不可变。

2810 · 存储过程 INOUT 参数（一）

题：

sql:

create procedure UpdateTeacherAge(
    inout age int,
    in incr int
)
begin
    set age = age + incr;
    update teachers set age = age where name = 'Linghu Chong';
end;

2809 · 存储过程 OUT 参数（二）

题：

sql:

create procedure aaa(
    in Teacher int,
    out totalaaa int
)
begin
    select count(*) into totalaaa from courses where teacher_id = Teacher;
end;

-- 调用存储过程 ，@total 会保存存储过程输出的值(也就是 totalaaa )，
call aaa(3,@total);
-- 执行查询
select @total

解释

-- @total 和 totalaaa 的关系
-- 在存储过程内部，totalaaa 是输出参数，它保存了查询结果（即指定教师教授的课程数量）。在调用存储过程时，@total 用来接收存储过程输出的值。
-- totalaaa 和 @total 是相同的，只是它们处于不同的作用域：totalaaa 是存储过程内部的局部变量，而 @total 是存储过程外部的会话变量。


-- @ 符号用于标识 用户定义的变量（User-defined Variables），这些变量的作用范围通常是会话级的，即它们在当前数据库连接（会话）中有效，而非在存储过程内部

2808 · 存储过程 OUT 参数（一）

题：

sql:

create procedure bbb(
    -- 输入参数
    in teacherCountry varchar(10),
    -- 输出参数
    out total int
)
begin
    -- into 把值赋值给 total
    select count(*) into total from teachers where country = teacherCountry;
end;

-- 调用存储过程
call bbb('CN',@total);
-- 查询输出的值
select @total

2807 · 存储过程 IN 参数（二）

题：

sql:

create procedure ccc(
    -- 输入参数，只读
    in teacherAge int
)
begin

select * from teachers where age = teacherAge;

end;

-- 直接调用存储过程
call ccc(21);

2806 · 存储过程 IN 参数（一）

题：

sql:

create procedure ddd(
    in countryName varchar(25)
)
begin

select * from teachers where country = countryName;

end;

call ddd('CN');

2802 · 删除存储过程（二）

题：

sql:

drop procedure if exists getTeachers_2;

2801 · 删除存储过程（一）

题：

sql:

drop procedure if exists getTeachers;

2761 · 认识存储过程（二）

题：

sql:

-- 写法1：
SHOW CREATE PROCEDURE getTeachers;

-- 写法2：
SHOW PROCEDURE STATUS WHERE Name = 'getTeachers';

-- 写法3：
call getTeachers();

2760 · 认识存储过程（一）

题：

sql:

call getTeachers();

2725 · 视图的本地检查

视图本身是一种虚拟表，它只包含查询结果的数据，而不是存储数据。

题：

sql:

-- 基于v_teachers 表创建视图v_teachers_1，用 create view v_teachers_1 as  语法
create view v_teachers_1 as 
-- 要查询的数据
select * from  v_teachers where age < 20

2724 · 视图的级联检查

题：

sql:

-- 创建 v_teachers_1  视图，该视图数据来源于 v_teachers_1  视图。
create view v_teachers_1 as select * from v_teachers_1 where age  < 20;

2723 · 显示一个视图的定义

题：

sql:

-- SHOW CREATE VIEW 是用于显示视图的创建语句的命令，类似于 SHOW CREATE PROCEDURE 或 SHOW CREATE TABLE，它会返回视图创建时使用的 SQL 语句。

show create view v_teachers;

2721 · 创建一个确保一致性的视图（二）

题：

sql:

create view v_CN_teachers as select * from teachers where country = 'CN';

2720 · 创建一个确保一致性的视图（一）

题：

sql:

create view v_teachers as select * from teachers where age < 30;

2716 · 视图处理算法 TEMPTABLE

题：

sql:

-- algorithm 算法
-- algorithm=TEMPTABLE 强制 MySQL 使用 TEMPTABLE 算法

create  algorithm=TEMPTABLE view v_teachers as select * from teachers  where country = 'CN';

-- ALGORITHM=TEMPTABLE 强制 MySQL 使用 TEMPTABLE 算法。这意味着：

-- MySQL 在查询 v_teachers 视图时会先创建一个临时表来存储符合条件（country = 'CN'）的教师记录。
-- 然后，外部查询将从这个临时表中获取数据，而不是直接查询原始的 teachers 表。

解释

TEMPTABLE：MySQL 会在执行视图时创建一个临时表。所有视图的查询结果会被存储在临时表中，外部查询再从该临时表中获取数据。

TEMPTABLE 算法适用于视图查询较复杂、无法直接合并的情况。虽然这样可能会稍微降低查询的效率，因为需要创建临时表，但它更具通用性，适用于大部分情况。

2715 · 视图处理算法 Merge

题：

sql:

create algorithm=Merge view v_teachers  as select * from teachers where age > 25;

解释

2709 · 删除视图（二）

题：

sql:

drop view v_courses_teachers;

2708 · 删除视图（一）

题：

sql:

drop view v_teachers;

2707 · 视图的重命名（二）

题：

sql:

rename table v_teachers_test to v_teachers;

2706 · 视图的重命名（一）

题：

sql:

rename table v_teachers to v_teachers_1;

2705 · 显示当前数据库的视图

题：

sql:

-- 用于列出当前数据库中所有类型为 VIEW 的对象（即视图）

-- show full tables 查询所有表
-- table_type = 'VIEW' 表的类型是 view (也就是视图)

show full tables where table_type = 'VIEW';

2702 · 通过视图删除 Linghu Chong 的教师数据

题：

sql:

delete from v_teachers where name = 'Linghu Chong';

2700 · 通过视图更新 Linghu Chong 的年龄

题：

sql:

update v_teachers set age = 30 where name = 'Linghu Chong';

2692 · 统计每个教师教授的总学生数（视图）

题：

sql:

create view v_teachers  as 
select vc.teacher_id, t.name,t.email ,t.age ,t.country,vc.student_count  
from v_courses vc left join teachers t on vc.teacher_id = t.id ;

2689 · 重新定义 teachers 视图

题：

sql:

-- ALTER VIEW 用于修改视图的定义，而不能直接用于修改视图返回的数据内容。
-- 视图本身是一种虚拟表，它只包含查询结果的数据，而不是存储数据。

alter view v_teachers as select * from teachers where country = 'CN';

2665 · 认识视图（二）

题：

sql:

show full tables;

2654 · 给编号为 3 的数据上行锁（读锁）

题：

sql:

select * from teachers where id = '3' LOCK IN SHARE MODE;

解释

LOCK IN SHARE MODE 作用：

查询该行数据的时候，加上这个读锁，作用是：
  当前事务在查询这条数据时，其实事务也能查这行数据，但是不能对这行数据进行修改。
  
  确保数据一致性和防止数据在读取过程中被其他事务修改

2633 · 排查当前数据库的行锁，查看行锁分析

题：

sql:

-- 查看行锁

SHOW STATUS LIKE 'Innodb_row_lock%';

2627 · 排查当前数据库的表锁，查看表锁分析

题：

sql: