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;