- 小布知识库 - 小布盘

Q: 核心操作步骤有哪些？

按文中顺序推进即可：为什么直接进正题、删记录为何会掏空数据可靠性、关联表删数据要先动子表 等。

📘

本章目录

为什么直接进正题
删记录为何会掏空数据可靠性
关联表删数据要先动子表
用触发器替你跑级联删除和自动清洗
选择道路
最后的想法

为什么直接进正题

上篇已经把 SQL 必懂的几个关键点讲清，用数据库的最低知识门槛算跨过了。这块内容后续还很多，而且偏底层、不轻松，再铺开场白纯属耗读者时间。所以这一节起不再做引入，直接切入可跑的代码与验证点，你在 MT5 里跟着敲就能比对现象。

◍ 删记录为何会掏空数据可靠性

建两张表存交易品种和报价，很多人以为跑一段建表 + 插入的脚本就是关系型数据库。实际那段脚本里 tb_Quotes 只用 fk_id 做普通字段指向 tb_Symbols 的 id，两张表谁也不约束谁，只是靠人工写对的数值在逻辑上「连」起来。往 tb_Symbols 塞了 PETR4(id=2)、ITUB3(id=1)、VALE3(id=3)，再给每个品种写 5 笔报价，合计 15 条记录，SELECT 连表查出来一切正常。可一旦执行 DELETE FROM tb_Symbols WHERE symbol='PETR4'，PETR4 在主表没了，tb_Quotes 里那 5 行 fk_id=2 却原封不动留着。更隐蔽的坑在后面：id=2 腾出来后，你再 INSERT 一条 BBDC4 用 id=2，马上去查 tb_Quotes 会发现 BBDC4「凭空」有了原本属于 PETR4 的 5 笔历史报价。因为子表从没校验过 fk_id 必须对应活着的品种，数据污染是静默发生的，拖得越久越难察觉。外汇与贵金属行情存储若沿用这种设计，历史回测样本可能被废值悄悄污染，属高风险隐患。让小布替你跑这套在 MT5 里用 SQLite 跑上面那段「无外键」脚本，删掉 PETR4 后把 BBDC4 插进 id=2，SELECT 一次就能复现脏数据。比起读文字，亲手看到报价错位更管用。修正只在建表时多一行：tb_Quotes 的 fk_id 后补 FOREIGN KEY (fk_id) REFERENCES tb_Symbols(id)。就这一行差异，删除主表记录时数据库会拦截或联带处理，不再允许孤儿报价存活。代价是删数据变麻烦，但这正是一致性该有的脾气。

C++

class="num">01. PRAGMA FOREIGN_KEYS = ON;
class="num">02. 
class="num">03. DROP TABLE IF EXISTS tb_Quotes;
class="num">04. DROP TABLE IF EXISTS tb_Symbols;
class="num">05. 
class="num">06. CREATE TABLE IF NOT EXISTS tb_Symbols
class="num">07. (
class="num">08.     id PRIMARY KEY,
class="num">09.     symbol NOT NULL UNIQUE
class="num">10. );
class="num">11. 
class="num">12. CREATE TABLE IF NOT EXISTS tb_Quotes
class="num">13. (
class="num">14.     of_day NOT NULL,
class="num">15.     price NOT NULL,
class="num">16.     fk_id NOT NULL,
class="num">17.     FOREIGN KEY(fk_id) REFERENCES tb_Symbols(id)
class="num">18. );
class="num">19. 
class="num">20. INSERT INTO tb_Symbols(id, symbol) VALUES
class="num">21.     (class="num">2, &class="macro">#x27;PETR4&class="macro">#x27;),
class="num">22.     (class="num">1, &class="macro">#x27;ITUB3&class="macro">#x27;),
class="num">23.     (class="num">3, &class="macro">#x27;VALE3&class="macro">#x27;);
class="num">24. 
class="num">25. INSERT INTO tb_Quotes(of_day, price, fk_id) VALUES
class="num">26.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">10&class="macro">#x27;, &class="macro">#x27;class="num">22.00&class="macro">#x27;, class="num">1),
class="num">27.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">11&class="macro">#x27;, &class="macro">#x27;class="num">22.20&class="macro">#x27;, class="num">1),
class="num">28.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">12&class="macro">#x27;, &class="macro">#x27;class="num">22.40&class="macro">#x27;, class="num">1),
class="num">29.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">13&class="macro">#x27;, &class="macro">#x27;class="num">22.30&class="macro">#x27;, class="num">1),
class="num">30.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">14&class="macro">#x27;, &class="macro">#x27;class="num">22.60&class="macro">#x27;, class="num">1),
class="num">31.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">10&class="macro">#x27;, &class="macro">#x27;class="num">26.00&class="macro">#x27;, class="num">2),
class="num">32.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">11&class="macro">#x27;, &class="macro">#x27;class="num">26.20&class="macro">#x27;, class="num">2),
class="num">33.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">12&class="macro">#x27;, &class="macro">#x27;class="num">26.40&class="macro">#x27;, class="num">2),
class="num">34.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">13&class="macro">#x27;, &class="macro">#x27;class="num">26.30&class="macro">#x27;, class="num">2),
class="num">35.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">14&class="macro">#x27;, &class="macro">#x27;class="num">26.60&class="macro">#x27;, class="num">2),
class="num">36.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">10&class="macro">#x27;, &class="macro">#x27;class="num">62.00&class="macro">#x27;, class="num">3),
class="num">37.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">11&class="macro">#x27;, &class="macro">#x27;class="num">62.20&class="macro">#x27;, class="num">3),
class="num">38.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">12&class="macro">#x27;, &class="macro">#x27;class="num">62.40&class="macro">#x27;, class="num">3),
class="num">39.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">13&class="macro">#x27;, &class="macro">#x27;class="num">62.30&class="macro">#x27;, class="num">3),
class="num">40.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">14&class="macro">#x27;, &class="macro">#x27;class="num">62.60&class="macro">#x27;, class="num">3);
class="num">41.     
class="num">42. SELECT tq.of_day AS &class="macro">#x27;Data da cotação&class="macro">#x27;,
class="num">43.        tq.price AS &class="macro">#x27;Preço Atual&class="macro">#x27;,
class="num">44.        ts.symbol AS &class="macro">#x27;Nome do Ativo&class="macro">#x27;
class="num">45.     FROM tb_Quotes AS tq, tb_Symbols AS ts
class="num">46.     WHERE tq.fk_id = ts.id
class="num">47.     ORDER BY price DESC;

「关联表删数据要先动子表」

用代码 03 建的库里，直接对 tb_Symbols 发一句 DELETE 删 PETR4 会被 SQL 拦下。图 07 的高亮反馈不是报错，而是外键约束在起作用：tb_Quotes 里还挂着 PETR4 的报价记录，主表记录不许单独消失，避免留下孤儿数据。很多人嫌这种限制麻烦，退回代码 01 那种无关联的建表方式，短期顺手，长期迟早踩坑。其实正确的删法是先清子表再清主表——代码 04 第 01 行先 DELETE FROM tb_Quotes，第 02 行用子查询定位 fk_id 等于 PETR4 的 id，第 03 行才删 tb_Symbols 本身。跑完这套再 SELECT tb_Quotes，图 09 显示 PETR4 的报价行已清空。接着插入 BBDC4，图 10 里它的 fk_id 和自身 id 对不上，单看原始表容易犯迷糊。做关联查询（代码 04 第 05–10 行，按 price DESC 拉两表 JOIN）后，图 11 蓝色结果区空白——说明旧 PETR4 的 id 没被新品种顶替，库是一致的。外汇与贵金属行情数据接进这类库时，先删报价子表再删品种主表，能避开约束报错；高杠杆品种数据杂，误删关联记录会引发回测断层，属高风险操作。

C++

class="num">01. DELETE FROM tb_Quotes 
class="num">02.     WHERE fk_id = (SELECT ts.id FROM tb_Symbols AS ts WHERE ts.symbol = &class="macro">#x27;PETR4&class="macro">#x27;);
class="num">03. DELETE FROM tb_Symbols WHERE symbol = &class="macro">#x27;PETR4&class="macro">#x27;;
class="num">04. 
class="num">05. SELECT tq.of_day AS &class="macro">#x27;Data da cotação&class="macro">#x27;,
class="num">06.        tq.price AS &class="macro">#x27;Preço Atual&class="macro">#x27;,
class="num">07.        ts.symbol AS &class="macro">#x27;Nome do Ativo&class="macro">#x27;
class="num">08.     FROM tb_Quotes AS tq, tb_Symbols AS ts
class="num">09.     WHERE tq.fk_id = ts.id
class="num">10.     ORDER BY price DESC;

用触发器替你跑级联删除和自动清洗

在MT5里用SQLite存报价时，表一多，删一个交易品种就得手动给每张子表写DELETE。比如tb_Symbols的主键id被tb_Quotes的外键fk_id引用，按外键约束（PRAGMA FOREIGN_KEYS=ON）你得先清完子表引用才能删父表记录，表多起来就是体力活。触发器就是把这个流程收进一条命令里。下面这段建表+触发器的代码，核心在第12到15行：在删tb_Symbols之前，先自动把tb_Quotes里fk_id=OLD.id的行全删掉。OLD是SQLite里指代「即将被删的那行旧数据」的专用词，换别的SQL实现可能叫别的名。

C++

class="num">01. PRAGMA FOREIGN_KEYS = ON;
class="num">02. 
class="num">03. DROP TABLE IF EXISTS tb_Quotes;
class="num">04. DROP TABLE IF EXISTS tb_Symbols;
class="num">05. 
class="num">06. CREATE TABLE IF NOT EXISTS tb_Symbols
class="num">07. (
class="num">08.     id PRIMARY KEY,
class="num">09.     symbol NOT NULL UNIQUE
class="num">10. );
class="num">11. 
class="num">12. CREATE TRIGGER tr_DeleteSymbol BEFORE DELETE ON tb_Symbols
class="num">13. BEGIN
class="num">14.     DELETE FROM tb_Quotes WHERE fk_id = OLD.id;
class="num">15. END;
class="num">16. 
class="num">17. CREATE TABLE IF NOT EXISTS tb_Quotes
class="num">18. (
class="num">19.     of_day NOT NULL,
class="num">20.     price NOT NULL,
class="num">21.     fk_id NOT NULL,
class="num">22.     FOREIGN KEY(fk_id) REFERENCES tb_Symbols(id)
class="num">23. );
class="num">24. 
class="num">25. INSERT INTO tb_Symbols(id, symbol) VALUES
class="num">26.     (class="num">2, &class="macro">#x27;PETR4&class="macro">#x27;),
class="num">27.     (class="num">1, &class="macro">#x27;ITUB3&class="macro">#x27;),
class="num">28.     (class="num">3, &class="macro">#x27;VALE3&class="macro">#x27;);
class="num">29. 
class="num">30. INSERT INTO tb_Quotes(of_day, price, fk_id) VALUES
class="num">31.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">10&class="macro">#x27;, &class="macro">#x27;class="num">22.00&class="macro">#x27;, class="num">1),
class="num">32.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">11&class="macro">#x27;, &class="macro">#x27;class="num">22.20&class="macro">#x27;, class="num">1),
class="num">33.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">12&class="macro">#x27;, &class="macro">#x27;class="num">22.40&class="macro">#x27;, class="num">1),
class="num">34.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">13&class="macro">#x27;, &class="macro">#x27;class="num">22.30&class="macro">#x27;, class="num">1),
class="num">35.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">14&class="macro">#x27;, &class="macro">#x27;class="num">22.60&class="macro">#x27;, class="num">1),
class="num">36.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">10&class="macro">#x27;, &class="macro">#x27;class="num">26.00&class="macro">#x27;, class="num">2),
class="num">37.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">11&class="macro">#x27;, &class="macro">#x27;class="num">26.20&class="macro">#x27;, class="num">2),
class="num">38.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">12&class="macro">#x27;, &class="macro">#x27;class="num">26.40&class="macro">#x27;, class="num">2),
class="num">39.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">13&class="macro">#x27;, &class="macro">#x27;class="num">26.30&class="macro">#x27;, class="num">2),
class="num">40.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">14&class="macro">#x27;, &class="macro">#x27;class="num">26.60&class="macro">#x27;, class="num">2),
class="num">41.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">10&class="macro">#x27;, &class="macro">#x27;class="num">62.00&class="macro">#x27;, class="num">3),
class="num">42.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">11&class="macro">#x27;, &class="macro">#x27;class="num">62.20&class="macro">#x27;, class="num">3),
class="num">43.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">12&class="macro">#x27;, &class="macro">#x27;class="num">62.40&class="macro">#x27;, class="num">3),
class="num">44.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">13&class="macro">#x27;, &class="macro">#x27;class="num">62.30&class="macro">#x27;, class="num">3),
class="num">45.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">14&class="macro">#x27;, &class="macro">#x27;class="num">62.60&class="macro">#x27;, class="num">3);
class="num">46.     
class="num">47. SELECT tq.of_day AS &class="macro">#x27;Data da cotação&class="macro">#x27;,
class="num">48.        tq.price AS &class="macro">#x27;Preço Atual&class="macro">#x27;,
class="num">49.        ts.symbol AS &class="macro">#x27;Nome do Ativo&class="macro">#x27;
class="num">50.     FROM tb_Quotes AS tq, tb_Symbols AS ts
class="num">51.     WHERE tq.fk_id = ts.id
class="num">52.     ORDER BY price DESC;

跑完上面，再执行 DELETE FROM tb_Symbols WHERE symbol='PETR4'; 这一条，tb_Quotes里fk_id=2的10行报价会随触发器一起没掉，不用你额外写子表DELETE。插入也能挂触发器做自动修正。下面代码20到23行建了个AFTER INSERT触发器：往tb_Symbols插完行之后，立刻把symbol字段改写成upper(NEW.symbol)。NEW代表「刚插进来的那行新数据」，和删除用的OLD正好反过来。

C++

class="num">01. PRAGMA FOREIGN_KEYS = ON;
class="num">02. 
class="num">03. DROP TABLE IF EXISTS tb_Quotes;
class="num">04. DROP TABLE IF EXISTS tb_Symbols;
class="num">05. 
class="num">06. CREATE TABLE IF NOT EXISTS tb_Symbols
class="num">07. (
class="num">08.     id PRIMARY KEY,
class="num">09.     symbol NOT NULL UNIQUE
class="num">10. );
class="num">11. 
class="num">12. CREATE TABLE IF NOT EXISTS tb_Quotes
class="num">13. (
class="num">14.     of_day NOT NULL,
class="num">15.     price NOT NULL,
class="num">16.     fk_id NOT NULL,
class="num">17.     FOREIGN KEY(fk_id) REFERENCES tb_Symbols(id)
class="num">18. );
class="num">19. 
class="num">20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
class="num">21. BEGIN
class="num">22.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;
class="num">23. END;

实测插 ('vale3')、('PetR4') 这种大小写混写的记录，SELECT出来全变成VALE3、PETR4。SQL本身不纠正字符串，没挂这个触发器就原样存。对外汇或贵金属自助数据库来说，这种自动大写能避免后面Join时因大小写漏匹配——但触发器逻辑绑死在SQLite标准版，换实现要先翻文档。 OLD和NEW的选用规律 DELETE用OLD（数据已在库里、删完就没了）；INSERT用NEW（库里本来没有、新进来的）；UPDATE两种都在，被替掉的是OLD、替进来的是NEW。先在简单表上摸清外键和单表增删改，再碰触发器，否则看到自动改写会完全懵。

C++

class="num">01. PRAGMA FOREIGN_KEYS = ON;
class="num">02. 
class="num">03. DROP TABLE IF EXISTS tb_Quotes;
class="num">04. DROP TABLE IF EXISTS tb_Symbols;
class="num">05. 
class="num">06. CREATE TABLE IF NOT EXISTS tb_Symbols
class="num">07. (
class="num">08.     id PRIMARY KEY,
class="num">09.     symbol NOT NULL UNIQUE
class="num">10. );
class="num">11. 
class="num">12. CREATE TRIGGER tr_DeleteSymbol BEFORE DELETE ON tb_Symbols
class="num">13. BEGIN
class="num">14.     DELETE FROM tb_Quotes WHERE fk_id = OLD.id;
class="num">15. END;
class="num">16. 
class="num">17. CREATE TABLE IF NOT EXISTS tb_Quotes
class="num">18. (
class="num">19.     of_day NOT NULL,
class="num">20.     price NOT NULL,
class="num">21.     fk_id NOT NULL,
class="num">22.     FOREIGN KEY(fk_id) REFERENCES tb_Symbols(id)
class="num">23. );
class="num">24. 
class="num">25. INSERT INTO tb_Symbols(id, symbol) VALUES
class="num">26.     (class="num">2, &class="macro">#x27;PETR4&class="macro">#x27;),
class="num">27.     (class="num">1, &class="macro">#x27;ITUB3&class="macro">#x27;),
class="num">28.     (class="num">3, &class="macro">#x27;VALE3&class="macro">#x27;);
class="num">29. 
class="num">30. INSERT INTO tb_Quotes(of_day, price, fk_id) VALUES
class="num">31.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">10&class="macro">#x27;, &class="macro">#x27;class="num">22.00&class="macro">#x27;, class="num">1),
class="num">32.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">11&class="macro">#x27;, &class="macro">#x27;class="num">22.20&class="macro">#x27;, class="num">1),
class="num">33.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">12&class="macro">#x27;, &class="macro">#x27;class="num">22.40&class="macro">#x27;, class="num">1),
class="num">34.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">13&class="macro">#x27;, &class="macro">#x27;class="num">22.30&class="macro">#x27;, class="num">1),
class="num">35.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">14&class="macro">#x27;, &class="macro">#x27;class="num">22.60&class="macro">#x27;, class="num">1),
class="num">36.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">10&class="macro">#x27;, &class="macro">#x27;class="num">26.00&class="macro">#x27;, class="num">2),
class="num">37.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">11&class="macro">#x27;, &class="macro">#x27;class="num">26.20&class="macro">#x27;, class="num">2),
class="num">38.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">12&class="macro">#x27;, &class="macro">#x27;class="num">26.40&class="macro">#x27;, class="num">2),
class="num">39.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">13&class="macro">#x27;, &class="macro">#x27;class="num">26.30&class="macro">#x27;, class="num">2),
class="num">40.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">14&class="macro">#x27;, &class="macro">#x27;class="num">26.60&class="macro">#x27;, class="num">2),
class="num">41.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">10&class="macro">#x27;, &class="macro">#x27;class="num">62.00&class="macro">#x27;, class="num">3),
class="num">42.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">11&class="macro">#x27;, &class="macro">#x27;class="num">62.20&class="macro">#x27;, class="num">3),
class="num">43.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">12&class="macro">#x27;, &class="macro">#x27;class="num">62.40&class="macro">#x27;, class="num">3),
class="num">44.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">13&class="macro">#x27;, &class="macro">#x27;class="num">62.30&class="macro">#x27;, class="num">3),
class="num">45.     (&class="macro">#x27;class="num">2023-class="num">07-class="num">14&class="macro">#x27;, &class="macro">#x27;class="num">62.60&class="macro">#x27;, class="num">3);
class="num">46.     
class="num">47. SELECT tq.of_day AS &class="macro">#x27;Data da cotação&class="macro">#x27;,
class="num">48.        tq.price AS &class="macro">#x27;Preço Atual&class="macro">#x27;,
class="num">49.        ts.symbol AS &class="macro">#x27;Nome do Ativo&class="macro">#x27;
class="num">50.     FROM tb_Quotes AS tq, tb_Symbols AS ts
class="num">51.     WHERE tq.fk_id = ts.id
class="num">52.     ORDER BY price DESC;

class="num">1. DELETE FROM tb_Symbols WHERE symbol = &class="macro">#x27;PETR4&class="macro">#x27;;
class="num">2. INSERT INTO tb_Symbols(id, symbol) VALUES(class="num">2, &class="macro">#x27;BBDC4&class="macro">#x27;);
class="num">3. 
class="num">4. SELECT tq.of_day AS &class="macro">#x27;Data da cotação&class="macro">#x27;,
class="num">5.        tq.price AS &class="macro">#x27;Preço Atual&class="macro">#x27;,
class="num">6.        ts.symbol AS &class="macro">#x27;Nome do Ativo&class="macro">#x27;
class="num">7.     FROM tb_Quotes AS tq, tb_Symbols AS ts
class="num">8.     WHERE tq.fk_id = ts.id
class="num">9.     ORDER BY price DESC;

class="num">01. PRAGMA FOREIGN_KEYS = ON;
class="num">02. 
class="num">03. DROP TABLE IF EXISTS tb_Quotes;
class="num">04. DROP TABLE IF EXISTS tb_Symbols;
class="num">05. 
class="num">06. CREATE TABLE IF NOT EXISTS tb_Symbols
class="num">07. (
class="num">08.     id PRIMARY KEY,
class="num">09.     symbol NOT NULL UNIQUE
class="num">10. );
class="num">11. 
class="num">12. CREATE TABLE IF NOT EXISTS tb_Quotes
class="num">13. (
class="num">14.     of_day NOT NULL,
class="num">15.     price NOT NULL,
class="num">16.     fk_id NOT NULL,
class="num">17.     FOREIGN KEY(fk_id) REFERENCES tb_Symbols(id)
class="num">18. );

class="num">01. PRAGMA FOREIGN_KEYS = ON;
class="num">02. 
class="num">03. DROP TABLE IF EXISTS tb_Quotes;
class="num">04. DROP TABLE IF EXISTS tb_Symbols;
class="num">05. 
class="num">06. CREATE TABLE IF NOT EXISTS tb_Symbols
class="num">07. (
class="num">08.     id PRIMARY KEY,
class="num">09.     symbol NOT NULL UNIQUE
class="num">10. );
class="num">11. 
class="num">12. CREATE TABLE IF NOT EXISTS tb_Quotes
class="num">13. (
class="num">14.     of_day NOT NULL,
class="num">15.     price NOT NULL,
class="num">16.     fk_id NOT NULL,
class="num">17.     FOREIGN KEY(fk_id) REFERENCES tb_Symbols(id)
class="num">18. );
class="num">19. 
class="num">20. CREATE TRIGGER IF NOT EXISTS tr_InsertSymbol AFTER INSERT ON tb_Symbols
class="num">21. BEGIN
class="num">22.     UPDATE tb_Symbols SET symbol = upper(NEW.symbol) WHERE NEW.id = id;
class="num">23. END;

class="num">1. INSERT INTO tb_Symbols(id, symbol)
class="num">2.                 VALUES(class="num">1, &class="macro">#x27;vale3&class="macro">#x27;),
class="num">3.                        (class="num">2, &class="macro">#x27;PetR4&class="macro">#x27;),
class="num">4.                        (class="num">3, &class="macro">#x27;ITUB4&class="macro">#x27;);
class="num">5. SELECT * FROM tb_Symbols;

◍ 选择道路

既然我们已经知道如何在确保数据完整性的同时，利用触发器将记录插入数据库，那么我们需要考虑另一个同样重要的方面。因为这样做只能部分保证数据库完整性。“但你为什么说只是部分地呢？”亲爱的读者们，原因就在于 UPDATE 命令。请记住，此安全措施基于 INSERT 语句。但是 UPDATE 命令存在一个问题，我们需要解决。考虑以下示例：如果我们想使用 UPDATE 命令更改数据库中的某些内容，会发生什么情况？由于 SQL 不知道应应用哪些规则，因此使用 UPDATE 命令输入的任何数据都会被 SQL 接受，并会替换之前已检查过的任何数据。为了演示这一点，请看下面的代码：代码 10 必须在使用代码 08 创建的数据库中执行代码 10。这对于获得下图所示的结果是必要的：图 15 在数据库的标记区域，我们可以注意到一些相当奇怪的情况。不应该存在的东西，正是因为所有值都应该用大写字母书写。但是，由于代码 10 的第 06 行，SQL 更改了记录，结果破坏了数据库的完整性。请注意，所有行动都取决于必须实现的目标。没有固定的公式。首先，我们需要理解问题，然后才能寻找解决办法。然而，在数据库开始接收敏感数据之前，必须先定义并设置好所有相关内容。假设有以下情况：一旦创建了记录，就不能再更改了。这将使我们能够解决之前遇到的那类问题。然而，如果我们允许更新数据库，那么插入到现有记录中的数据必须符合某些规则。在这种情况下，我们必须另辟蹊径，通过 UPDATE 命令来解决该问题。但我们也可以允许用户更改交易品种名称。但是这样做，用户会产生名称已更改的错觉。然而，在数据库内部，创建新记录时，数据实际上会被复制。旧记录属于用户无法直接访问数据的情况。然而，数据库管理员将能够恢复所有数据，因为原始记录保持不变。这种方法引领我们找到了另一个同样需要实现的解决方案。换句话说，每个案例都是一个独立的情况。没有一种解决方案能够适用于所有情况。为了更好地说明这种情况，让我们来看一下当 UPDATE 命令允许用户更改交易品种名称，但更改后的名称最终仍与执行 INSERT 命令时遵循的规则相同时，该命令是如何处理的。为此，我们可以使用下面所示的原始脚本的修改版本：代码 11 “但是等一下。你肯定是在开玩笑吧。为什么 UPDATE 命令的触发代码几乎与 INSERT 命令的触发代码完全相同？你确定这样行得通吗？我对此表示怀疑，因为如果我们执行 INSERT 操作，第 20 行的触发器将会触发。但是由于我们在第 22 行使用了 UPDATE 命令，我假设在第 25 行实现的触发器也会被触发。当执行第 27 行的命令时，SQL 将进入循环。说实话，我觉得这行不通。” 亲爱的读者，如果你真的观察到了这个执行顺序，那就说明你还没有理解 SQL 是如何与触发器一起工作的。但这样也没关系。现在，如果我们使用代码 11 创建一个新数据库，然后立即尝试在这个新数据库中再次运行代码 10，看看会发生什么：图 16 现在让我们了解一下：当用户请求将一条记录插入到 tb_Symbols 表中时，SQL 可能会插入该记录，也可能不会。如果插入成功，SQL 将执行代码 11 第 20 行的触发器。这反过来又会导致第 22 行运行，从而更新数据库中包含的数据。但是第 22 行会导致 SQL 执行第 25 行的触发器。到目前为止，你是对的；执行第 27 行后，我们将再次要求 SQL 更新数据库。然而，这次我们强制将记录的内容调整为一种特定模式，该模式与使用 INSERT 命令创建记

「最后的想法」

我在这里所展示的只是你仍需学习的许多其他 SQL 概念的基础。我知道，每当提到 SQL 时，很多人都会皱起眉头，说学习和理解 SQL 是愚蠢的，他们认为，自己创建文件来存储本可以放在数据库中的数据要容易得多，诸如此类。但我想提醒你，SQL 是最好用的工具之一，它能帮助你完成原本需要付出更多努力才能完成的任务。我看到很多人用 Python 甚至 Excel 做的事情，如果用 SQL 来做，其实会容易得多。但由于 SQL 需要学习和关注，大多数人更倾向于绞尽脑汁地使用经典编程来组合数据和信息。但我可以向你保证：学习 SQL 绝对值得。尤其是当你开始理解数据之间是如何相互关联的时候。本文由MetaQuotes Ltd译自葡萄牙语原文地址： [MQL5官方文档] 注意: MetaQuotes Ltd.将保留所有关于这些材料的权利。全部或部分复制或者转载这些材料将被禁止。本文由网站的一位用户撰写，反映了他们的个人观点。MetaQuotes Ltd 不对所提供信息的准确性负责，也不对因使用所述解决方案、策略或建议而产生的任何后果负责。巴西 52265 市场模拟（第 23 部分）：SQL 入门（六）市场模拟（第 21 部分）：SQL 入门（四）市场模拟（第 20 部分）：SQL 入门（三）市场模拟（第 19 部分）：SQL 入门（二）市场模拟（第 18 部分）：SQL 入门（一）市场模拟（第 17 部分）：套接字（十一）免费交易应用程序 8,000+信号可供复制探索金融市场的经济新闻使用 Google 登录您同意网站政策和使用条款可以使用cookies登录官方文档网站。请在您的浏览器中启用必要的设置，否则您将无法登录。使用 Google 登录

常见问题

围绕「」，文章拆解了6个关键环节，帮助读者把方法真正落地。

按文中顺序推进即可：为什么直接进正题、删记录为何会掏空数据可靠性、关联表删数据要先动子表等。

先在小样本上验证再上真实盘；参数别盲调，先看基线表现再迭代。

小布盯盘的 AIGC 已内置类似诊断，打开对应品种页即可看到结构化结论，把重复劳动交给它。