SQLite 实现if not exist 类似功能的操作

需要实现：

if not exists(select * from ErrorConfig where Type='RetryWaitSeconds')
begin
  insert into ErrorConfig(Type,Value1)
  values('RetryWaitSeconds','3')
end

只能用：

insert into ErrorConfig(Type,Value1)
select 'RetryWaitSeconds','3'
where not exists(select * from ErrorConfig where Type='RetryWaitSeconds')

因为 SQLite 中不支持SP

补充：sqlite3中NOT IN 不好用的问题

在用sqlite3熟悉SQL的时候遇到了一个百思不得其解的问题，也没有在google上找到答案。虽然最后用“迂回”的方式碰巧解决了这个问题，但暂时不清楚原理是什么，目前精力有限，所以暂时记录下来，有待继续研究。

数据库是这样的：

CREATE TABLE book (
 id integer primary key,
 title text,
 unique(title)
);
CREATE TABLE checkout_item (
 member_id integer,
 book_id integer,
 movie_id integer,
 unique(member_id, book_id, movie_id) on conflict replace,
 unique(book_id),
 unique(movie_id)
);
CREATE TABLE member (
 id integer primary key,
 name text,
 unique(name)
);
CREATE TABLE movie (
 id integer primary key,
 title text,
 unique(title)
);

该数据库包含了4个表:book, movie, member, checkout_item。其中，checkout_item用于保存member对book和movie的借阅记录，属于关系表。

问一：哪些member还没有借阅记录？

SQL语句（SQL1)如下：

SELECT * FROM member WHERE id NOT IN(SELECT member_id FROM checkout_item);

得到了想要的结果。

问二：哪些book没有被借出？

这看起来与上一个是类似的，于是我理所当然地运行了如下的SQL语句(SQL2)：

SELECT * FROM book WHERE id NOT IN(SELECT book_id FROM checkout_item);

可是——运行结果没有找到任何记录！ 我看不出SQL2与SQL1这两条语句有什么差别，难道是book表的问题？于是把NOT去掉，运行了如下查询语句：

SELECT * FROM book WHERE id IN(SELECT book_id FROM checkout_item);

正确返回了被借出的book，其数量小于book表里的总行数，也就是说确实是有book没有借出的。

接着google（此处省略没有营养的字），没找到解决方案。可是，为什么member可以，book就不可以呢？它们之前有什么不同？仔细观察，发现checkout_item里的book_id和movie_id都加了一个unique，而member_id则没有。也许是这个原因？不用id了，换title试试：

SELECT * FROM book WHERE
 title NOT IN(
 SELECT title FROM book WHERE id IN(
 SELECT book_id FROM checkout_item));

确实很迂回，但至少work了。。。

问题原因：当NOT碰上NULL

事实是，我自己的解决方案只不过是碰巧work，这个问题产生跟unique没有关系。邱俊涛的解释是，“SELECT book_id FROM checkout_item”的结果中含有null值，导致NOT也返回null。当一个member只借了movie而没有借book时，产生的checkout_item中book_id就是空的。

解决方案是，在选择checkout_item里的book_id时，把值为null的book_id去掉：

SELECT * FROM book WHERE id NOT IN(SELECT book_id FROM checkout_item WHERE book_id IS NOT NULL);

总结

我在解决这个问题的时候方向是不对的，应该像调试程序一样，去检查中间结果。比如，运行如下语句，结果会包含空行：

SELECT book_id FROM checkout_item

而运行下列语句，结果不会包含空行：

SELECT member_id FROM checkout_item

这才是SQL1与SQL2两条语句执行过程中的差别。根据这个差别去google，更容易找到答案。当然了，没有NULL概念也是我“百思不得其解”的原因。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持我们。如有错误或未考虑完全的地方，望不吝赐教。

赞 (0)

相关推荐

  • SQLITE3 使用总结

    前序: 这里要注明,我是一个跨平台专注者,并不喜欢只用 windows 平台.我以前的工作就是为 unix 平台写代码.下面我所写的东西,虽然没有验证,但是我已尽量不使用任何 windows 的东西,只使用标准 C 或标准C++.但是,我没有尝试过在别的系统.别的编译器下编译,因此下面的叙述如果不正确,则留待以后修改. 下面我的代码仍然用 VC 编写,因为我觉得VC是一个很不错的IDE,可以加快代码编写速度(例如配合 Vassist ).下面我所说的编译环境,是VC2003.如果读者觉得自己习惯

  • mssql和sqlite中关于if not exists 的写法

    在sql语名中,if not exists 即如果不存在,if exists 即如果存在. 下面学习下二者的用法. a,判断数据库不存在时 复制代码 代码如下: if not exists(select * from sys.databases where name = 'database_name') b,判断表不存在时 复制代码 代码如下: if not exists (select * from sysobjects where id = object_id('table_name') a

  • SQLite3中的日期时间函数使用小结

    复制代码 代码如下: import sqlite3conn = sqlite3.connect('/tmp/sqlite.db')cur = conn.cursor() 接下来干嘛呢?建一张表吧.这里需要注意的是,SQLite不支持在创建表的同时创建索引,所以要分两步走,先创建表然后再创建索引 复制代码 代码如下: create_table_stmt = '''CREATE TABLE IF NOT EXISTS test_table ( id INTEGER PRIMARY KEY AUTOI

  • SQLite 实现if not exist 类似功能的操作

    需要实现: if not exists(select * from ErrorConfig where Type='RetryWaitSeconds') begin insert into ErrorConfig(Type,Value1) values('RetryWaitSeconds','3') end 只能用: insert into ErrorConfig(Type,Value1) select 'RetryWaitSeconds','3' where not exists(select

  • Android+SQLite数据库实现的生词记事本功能实例

    本文实例讲述了Android+SQLite数据库实现的生词记事本功能.分享给大家供大家参考,具体如下: 主activity命名为 Dict: 代码如下: package example.com.myapplication; import android.app.Activity; import android.content.Intent; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase;

  • java自定义Scanner类似功能类的实例讲解

    读取键盘输入 package com.zjx.io; import java.io.BufferedReader; import java.io.IOException; import java.io.InputStreamReader; /** * 面试题 * 读取键盘各个数据类型 * */ public class TestFaceIo { public static void main(String[] args) { System.out.print("请输入姓名: "); S

  • 如何使用Reactor完成类似Flink的操作

    一.背景 Flink在处理流式任务的时候有很大的优势,其中windows等操作符可以很方便的完成聚合任务,但是Flink是一套独立的服务,业务流程中如果想使用需要将数据发到kafka,用Flink处理完再发到kafka,然后再做业务处理,流程很繁琐. 比如在业务代码中想要实现类似Flink的window按时间批量聚合功能,如果纯手动写代码比较繁琐,使用Flink又太重,这种场景下使用响应式编程RxJava.Reactor等的window.buffer操作符可以很方便的实现. 响应式编程框架也早已

  • PHP封装类似thinkphp连贯操作数据库Db类与简单应用示例

    本文实例讲述了PHP封装类似thinkphp连贯操作数据库Db类与简单应用.分享给大家供大家参考,具体如下: <?php header("Content-Type:text/html;charset=utf-8"); /** *php操作mysql的工具类 */ class Db{ private $_db = null;//数据库连接句柄 private $_table = null;//表名 private $_where = null;//where条件 private $

  • JavaScript中变量、指针和引用功能与操作示例

    本文实例讲述了JavaScript中变量.指针和引用功能与操作.分享给大家供大家参考,具体如下: 1.变量 我们可能产生这样一个疑问:编程语言中的变量到底是什么意思呢? 事实上,当我们定义了一个变量a时,就是在存储器中指定了一组存储单元,并将这组存储单元命名为a.变量a的值实际上描述的是这组存储单元中存放的具体信息. 例如,在JS中 var a; a=10; 第一个语句在存储器中指定了一组存储单元,并命名为a: 第二个语句在这组存储单元中存储了数字10. 变量a的值为10实际上是说存储单元组a存

  • java使用ffmpeg实现上传视频的转码提取视频的截图等功能(代码操作)

    ffmpeg视频采集功能非常强大,不仅可以采集视频采集卡或USB摄像头的图像,还可以进行屏幕录制,同时还支持以RTP方式将视频流传送给支持RTSP的流媒体服务器,支持直播应用.ffmpeg能解析的格式和不能解析的格式都一一给大家说明了,具体内容详情跟随一起看看吧, 1.能支持的格式 ffmpeg能解析的格式:(asx,asf,mpg,wmv,3gp,mp4,mov,avi,flv等) 2.不能支持的格式 对ffmpeg无法解析的文件格式(wmv9,rm,rmvb等),可以先用别的工具(menco

  • go语言context包功能及操作使用详解

    go语言context包功能及操作使用详解

    目录 Context包到底是干嘛用的? context原理 什么时候应该使用 Context? 如何创建 Context? 主协程通知有子协程,子协程又有多个子协程 context核心接口 emptyCtx结构体 Backgroud TODO valueCtx结构体 WithValue向context添加值 Value向context取值 示例 WithCancel可取消的context cancelCtx结构体 WithDeadline-超时取消context WithTimeout-超时取消

  • php文件管理基本功能简单操作

    php文件管理基本功能简单操作

    (1)先要想好要操作哪个文件? (2)确定文件的路径? (3)要有什么文件管理功能? 一.先做一下简单的查看文件功能,文件中的文件和文件夹都显示,但是双击文件夹可以显示下一级子目录,双击"返回上一级"就可以返回到上一级目录 (1)先将需要管理的文件遍历出来,可以加个样式 <?php //打开目录资源 $dir = opendir($fname); //循环读取 while($n = readdir($dir)) { <br> //拼个路径 $url = $fname.

  • C#连接SQL数据库和查询数据功能的操作技巧

    C#连接SQL数据库和查询数据功能的操作技巧

    大数据时代在编程可能需要用到一些文本内容,不可能全部写到代码里,不好更改,用户也不方便使用 所以需要用到我们的数据库来保存这些数据,直接更改数据 SQL: 下载地址:https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 1.下载后打开选择登录:Windows身份验证 2.创建登录的账号和密码(右键创建) 3.创建数据库表 表的右键新建即可 "dbo.table_1" 就是创建成功的 4.导入数据 这里以 信息表为

随机推荐