SQLite 常用查询语句

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('一','地','在','要','工','上','是','中','国','同','和','的','有','人','我','主','产','不','为','这','民','了','发','以','经')

创建触发器

DROP TABLE IF EXISTS "weblinks";
CREATE TABLE "weblinks" (
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "title" TEXT,
  "link_url" TEXT NOT NULL,
  "order_idx" INTEGER NOT NULL DEFAULT 0,
  "update_date" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "is_home_up" INTEGER NOT NULL DEFAULT 0,
  "is_home_down" INTEGER NOT NULL DEFAULT 0,
  "is_favorite" INTEGER NOT NULL DEFAULT 0,
  "is_secret" INTEGER NOT NULL DEFAULT 0
);

DROP TRIGGER IF EXISTS "update_date_trigger";
CREATE TRIGGER update_date_trigger UPDATE OF title, link_url, order_idx,is_home_up,is_home_down,is_favorite,is_secret ON weblinks
BEGIN
  UPDATE weblinks SET update_date=CURRENT_TIMESTAMP WHERE id=OLD.id;
END;

版权所有,转载请注明出处 luowei.github.io.