SQLite 常用查询语句

03 August 2020

SQLite 常用查询语句

创建表

DROP TABLE IF EXISTS "tb_chizhu";
CREATE TABLE "tb_chizhu" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "hanZi" text(20),
  "pingYin" text(30),
  "wuBiBianMa" text(5),
  "leiXing" integer(11),
  "wuBiQuanMa" text(5),
  "wuBiBianMa98" text(5),
  "frequency" integer(11),
  "number" integer(11),
  "pingYin2" text(45)
);

查询与更新

UPDATE tb_word SET imgChaiJie86=REPLACE(imgChaiJie86,'cjt/own/','') WHERE imgChaiJie86 IS NOT NULL; 

SELECT * FROM tb_word WHERE id=12018;

SELECT REPLACE('ctj/own/一.gif','ctj/own/','')

SELECT * FROM tb_word WHERE LENGTH(imgChaiJie86) > 5

SELECT COUNT(*) FROM tb_word WHERE LENGTH(wuBiQuanMa) = 0
SELECT COUNT(*) FROM tb_word WHERE LENGTH(wuBiQuanMa) > 0
SELECT COUNT(*) FROM tb_word WHERE wuBiQuanMa IS NULL

SELECT * FROM tb_chizhu WHERE id=1 AND wuBiQuanMa IS NOT NULL AND LENGTH(wuBiQuanMa) > 0

UPDATE tb_chizhu SET wuBiQuanMa=NULL WHERE LENGTH(wuBiQuanMa) = 0

查询和更新一级简码

SELECT COUNT(*) FROM T_98CodeTable;

-- 1. 查询和更新一级简码  
SELECT * FROM T_86CodeTable WHERE LENGTH(Code)=1 AND Word IN('一','地','在','要','工','上','是','中','国','同','和','的','有','人','我','主','产','不','为','这','民','了','发','以','经')
SELECT * FROM T_98CodeTable WHERE LENGTH(Code)=1 AND Word IN('一','地','在','要','工','上','是','中','国','同','和','的','有','人','我','主','产','不','为','这','民','了','发','以','经')

UPDATE T_86CodeTable SET UserCnt=100 WHERE LENGTH(Code)=1 AND Word IN('一','地','在','要','工','上','是','中','国','同','和','的','有','人','我','主','产','不','为','这','民','了','发','以','经')
UPDATE T_98CodeTable SET UserCnt=100 WHERE LENGTH(Code)=1 AND Word IN('一','地','在','要','工','上','是','中','国','同','和','的','有','人','我','主','产','不','为','这','民','了','发','以','经')

查询和更新二级简码

-- 查询和更新二级简码
-- SELECT `hanZi` FROM tb_word where `leiXing` = 2 and LENGTH(wuBiBianMa)=2;

SELECT * FROM T_86CodeTable WHERE LENGTH(Code)=2 AND Code NOT LIKE '%z%' AND Word NOT IN('一','地','在','要','工','上','是','中','国','同','和','的','有','人','我','主','产','不','为','这','民','了','发','以','经')
SELECT * FROM T_98CodeTable WHERE LENGTH(Code)=2 AND Code NOT LIKE '%z%' AND Word NOT IN('一','地','在','要','工','上','是','中','国','同','和','的','有','人','我','主','产','不','为','这','民','了','发','以','经')

UPDATE T_86CodeTable SET UserCnt=1 WHERE LENGTH(Code)=2 AND Code NOT LIKE '%z%' AND Word NOT IN('一','地','在','要','工','上','是','中','国','同','和','的','有','人','我','主','产','不','为','这','民','了','发','以','经')
UPDATE T_98CodeTable SET UserCnt=1 WHERE LENGTH(Code)=2 AND Code NOT LIKE '%z%' AND Word NOT IN('一','地','在','要','工','上','是','中','国','同','和','的','有','人','我','主','产','不','为','这','民','了','发','以','经')