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

🕗 发布于 2024-11-18 14:33 数据库 笔记

炼码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 来显式触发。


-- 在数据库中，主表是存储主键（通常是父表）的表，而从表则是包含外键（通常是子表）的表。
-- 主表（父表）：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）等规则来自动管理子表数据的删除或更新。例如，如果父表的某行数据被删除，子表中的相关数据也可以自动删除或更新，避免数据孤立。

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

总结：

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

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


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;

原文地址：https://blog.csdn.net/weixin_44411039/article/details/143771735

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：【Pytorch】torch.utils.data模块
下一篇：Linux dpkg命令详解

第2章-PostgreSQL 15安装及登录
PostgreSQL数据库Windows及Linux下安装操作
阅读更多2024-11-18
操作系统学习笔记-5 传输层
传输层TCP协议，UDP协议
阅读更多2024-11-18
代码随想录第46期单调栈
这道题主要是单调栈的简单应用。比上一题多了个处理循环的操作。这道题同样是一个双指针问题。与上一题类似，但是更麻烦些。也可以是直接扩充数组。
阅读更多2024-11-18
常见长选项和短选项对应表
【代码】常见长选项和短选项对应表。
阅读更多2024-11-18
学习日记_20241115_聚类方法（DBSCAN）
学习日记，聚类方法DBSCAN
阅读更多2024-11-18
chatgpt训练需要什么样的gpu硬件
**显存容量**: 训练大型语言模型需要处理大量的数据和模型参数，因此需要大显存。- **散热系统**: 高性能GPU在训练过程中会产生大量的热量，因此需要良好的散热系统来保证GPU的稳定运行。- *
阅读更多2024-11-18
Redis设计与实现学习笔记第十八章发布与订阅
因为服务器状态中的pubsub_patterns链表记录了所有模式的订阅关系，所以为了将消息发送给所有与channel频道相匹配的模式的订阅者，PUBLISH命令要做的就是遍历整个pubsub_pat
阅读更多2024-11-18
C++和OpenGL实现3D游戏编程【连载18】——加载OBJ三维模型
以前我们加载过立方体木箱，立方体的顶点数据都是在程序运行时临时定义的。但后期如果模型数量增多，模型逐步复杂，我们就必须加载外部模型文件。这节课我们就先了解一下加载OBJ模型文件的方法，这样可以让编程和
阅读更多2024-11-18
LeetCode题解：18.四数之和【Python题解超详细】，三数之和 vs. 四数之和
LeetCode题解：18.四数之和【Python题解超详细】，四数之和 vs. 三数之和的异同，求解五数之和。四数之和：给你一个由n个整数组成的数组nums，和一个目标值target。请你找出并返回
阅读更多2024-11-18
C/C++学习-常量指针&指针常量
指针常量指的是指针本身是一个常量，也就是说，一旦指针被初始化指向某个地址，它的值就不能再改变，但是它可以用来修改它所指向的数据（前提是该数据不是常量）。当我们在C或C++中使用一个常量指针指向常量数据
阅读更多2024-11-18

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

目录