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

炼码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:

-- 查看表锁

show status like 'Table%';

2620 · 查看 MySQL 数据库的自增锁模式

在 MySQL 中，自增锁（Auto-Increment Locking）是指当多个线程同时执行插入（INSERT）操作时，为了避免自增列（通常是主键）生成重复值，MySQL 会对自增列进行锁定。自增锁模式用于保证在并发插入时每个插入的自增值都是唯一且有序的。

题：

sql:

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

2617 · 查看当前数据库的事务隔离级别

题：

sql:

SHOW VARIABLES like '%isolation%';

2616 · 向教师表插入 Kansas 的信息

题：

sql:

-- 不要删除预置代码 --
-- 开启一个事务 -- 
BEGIN;

-- 插入 Kansas 的信息 --
-- Write your SQL Query here --

insert into teachers (name,email,age,country) values ('Kansas', null ,41,'UK');


COMMIT;

2598 · 显示 teachers 表的所有触发器

触发器（Trigger）是数据库管理系统（DBMS）中的一种特殊的存储过程，它会在数据库表中发生特定事件时自动执行。触发器与数据库表的操作（如插入、更新或删除）关联，当这些操作发生时，触发器会自动被触发并执行预定义的动作。触发器通常用于保证数据完整性、自动化任务以及审计操作等场景。

题：

sql:

-- triggers 触发器

show triggers;

2587 · 删除 teachers 表信息时的关联处理（一）（触发器-删除之后触发）

题：

sql:

-- 触发时机：我们使用 AFTER DELETE，即在教师数据被删除之后执行。
-- FOR EACH ROW：触发器针对每一行被删除的记录执行。如果有多条记录被删除，触发器会为每条记录执行一次。
-- OLD.teacher_id 引用了被删除教师记录的 teacher_id

-- 创建一个触发器 aaa ，在删除 teachers 表的每一行数据时被触发。
create trigger aaa after delete on teachers for each row

begin
    -- 在触发器中，OLD 关键字代表删除（或更新）前的记录。当触发器在 AFTER DELETE 事件上被触发时，OLD 表示已删除的记录的值。因此，OLD 访问的是被删除行的字段值。
    -- 这里的 old.id 是指被删除掉的teachers表的那行记录的id，不是指 courses 表的那行记录的id
    update courses set teacher_id = 0 where teacher_id = old.id;

end;

解释：触发器和存储过程的区别：

触发器：

创建触发器语法： create trigger aaa
MySQL 使用 FOR EACH ROW 来表明触发器是逐行处理的。

自动触发：触发器是自动执行的，当某个数据库事件（如 INSERT、UPDATE、DELETE）发生时，触发器会自动被调用。
无参数：触发器通常不接受外部输入参数，它会针对每一行受影响的数据自动执行。
绑定在事件上：触发器绑定在特定的表和事件（比如某表的插入、删除或更新）上。
应用场景：常用于数据完整性约束、审计、自动更新其他表等。

存储过程：

通常是通过 CREATE PROCEDURE 语句来定义，并且需要通过 CALL 来显式触发。

显式调用：存储过程需要显式调用（CALL 或 EXEC），你需要通过 SQL 命令或程序代码来触发它的执行。
可以有参数：存储过程可以接受输入和输出参数，可以根据传入的参数执行不同的操作。
独立于事件：存储过程通常是独立存在的，与特定的表或数据库事件没有直接绑定。
应用场景：常用于封装复杂的业务逻辑，批量操作，或需要与用户交互的任务。

2568 · 删除触发器 “before_teachers_update”

题：

sql:

drop trigger before_teachers_update;

2567 · 删除触发器 “before_teachers_insert”

题：

sql:

drop trigger before_teachers_insert;

2565 · 创建一个触发器 “before_teachers_update”（修改之前执行）

题：

sql:

-- 如果触发器只执行一个语句，set new.country = 'CN' 可以直接写在触发器体内：
create trigger before_teachers_update before update on teachers  for each row set new.country = 'CN';

-- 如果触发器中包含多个语句，比如你想修改多个字段或者执行其他逻辑，就需要使用 BEGIN 和 END;

2564 · 创建一个触发器 “before_teachers_insert”（新增之前执行）

题：

sql:

-- 触发器在数据修改之前执行，所以用 before 

create trigger before_teachers_insert before insert on teachers for each row set new.country = 'CN';

2092 · 查询授课教师编号（distinct 去重）

题：

sql:

select distinct teacher_id from courses;

2091 · 对课程表添加外键约束（添加物理联系）

题：

sql:

-- 在数据库中，主表是存储主键（通常是父表）的表，而从表则是包含外键（通常是子表）的表。
-- 主表（父表）：teachers，其中的 id 是主键。
-- 从表（子表）：courses，其中的 teacher_id 是外键，它引用了 teachers 表中的 id 列。

-- 给 courses 表中 teacher_id 添加外键约束的名称：fk_courses

-- 外键名称：fk_courses
-- 外键字段名称：teacher_id
-- 主表列名称：id

alter table courses add constraint fk_courses foreign key (teacher_id) references teachers(id) ;

-- alter： 修改
-- constraint ：限制、约束
-- foreign : 外国的、外来的  ，foreign key 就是外键约束
-- references：参照

解释：

什么是外键约束。

外键约束：外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性。

比如：以往teacher表的 id 和 student 表的 teacher_id 数据是一致的，如果没有给这个teacher_id 添加外键约束，
那么 这里的 id 和 student_id 只能说是有逻辑联系，但是没有物理联系。

只有逻辑联系的话，如果把teacher 表的某个老师的数据删掉，但是student表的某些学生数据依然有存在该老师的id，这就会造成数据不一致。
如果加上外键约束的话，那么在删除teacher表的某个老师的数据之前，和这个老师相关联的那些学生的 teacher_id，就得先处理好，才能删除老师的数据。

有外键约束和没外键约束的区别：

有外键约束和没有外键约束的区别，简而言之，主要体现在数据的一致性、完整性和约束控制方面。

1、数据一致性和完整性

有外键约束：数据库会确保子表中的外键列（如 courses.teacher_id）只包含父表（如 teachers.id）中存在的有效值。也就是说，子表中的外键值必须与父表的主键值匹配，防止插入无效的外键值。

没有外键约束：数据库不会强制检查外键列的值是否有效。因此，可能会插入无效或不一致的数据，比如 courses.teacher_id 的值指向 teachers 表中不存在的 id。

2、数据删除与更新的控制

有外键约束：可以通过定义级联删除（CASCADE）、级联更新（CASCADE）、置空（SET NULL）等规则来自动管理子表数据的删除或更新。例如，如果父表的某行数据被删除，子表中的相关数据也可以自动删除或更新，避免数据孤立。

没有外键约束：数据库不会自动管理删除或更新操作。如果父表的某条记录被删除或更新，子表中的相关记录将不会自动删除或更新，可能会留下“孤立记录”，即外键指向不存在的记录。

总结：

有外键约束：自动保证数据的一致性、完整性，防止不合法的数据插入和不一致的更新/删除操作。

没有外键约束：没有自动的数据一致性保障，容易导致无效或不一致的数据，需要开发人员手动管理。

有外键约束的数据库能确保数据的安全性和完整性，而没有外键约束则完全依赖于应用层的管理，容易出现错误和不一致的情况。

2086 · 查询 ‘U’ 字开头且学生总数在 2000 到 5000 之间的教师国籍和该国籍教师的学生总数

题：

sql:

select t.country , sum(student_count) as 'student_count' from courses c left join teachers t on c.teacher_id = t.id

-- 注意：这里的student_count是 sum(student_count) 的别名 'student_count'，不是表里面的 student_count 字段。
-- 这里的student_count 的数据是经过 sum 合计出来的了，用的是上面的别名，这点不要搞混。
group by t.country having t.country like 'U%' and student_count between 2000 and 5000
-- 这个 student_count  也是，对应的是 sum(student_count) as 'student_count' 这里的 student_count。
order by student_count desc , t.country asc;

2082 · 统计每个老师教授课程的数量

题：

sql:

select t.name as 'teacher_name' , count(c.id) as 'course_count' from teachers t 
left join courses c on t.id = c.teacher_id 
group by t.id order by course_count desc , teacher_name asc;

2062 · 查询指定老师教的所有课程的编号和名称（内连接 inner join）

题：

sql:

-- 内连接：inner join ，只返回两个表中匹配的数据  , 和 join 是等价的。

select c.id , c.name as 'course_name', t.name as 'teacher_name' from courses c 
inner join teachers t on c.teacher_id = t.id
where t.name = 'Eastern Heretic'

2060 · 查询 ‘Big Data’ 课程对应的老师姓名

题：

sql:

select t.name from courses c inner join teachers t on c.teacher_id = t.id 
where c.name = 'Big Data';

解释

因为这道题的要求是【如果 teachers 表中没有 ‘Big Data’ 对应的老师，就什么都不返回】，所以不能用 left join 这种，因为查不到的话，返回的是null ，不是要求的不返回数据。

所以这里用的 inner join 内连接，如果没有匹配的数据则不返回。

2050 · 查询教师名称以及所教课程名称

题：

sql:

select c.name as 'course_name', t.name as 'teacher_name'  from teachers t
left join courses c on t.id = c.teacher_id;