sqlite知识点（脚本批量造数据等）

目录

建表、设定字段自增语法 

脚本批量造数据案例

insertdb.bat

insertdbSiteusesql.bat

insertdbSiteusesql.bat

随机数、随机字符串生成

instr(a,b)返回b在a中第一次出现的位置

---

建表、设定字段自增语法 

CREATE TABLE "main"."user1" (
  "id" integer PRIMARY KEY autoincrement NOT NULL ON CONFLICT ABORT COLLATE BINARY,
  "account" text(20),
  "password" text(20) DEFAULT NULL,
  "username" text(30) DEFAULT NULL,
  "status" integer(1) DEFAULT 0,
  "tag" text(50) DEFAULT 0,
  "login_date" text,
  "login_ip" text(20),
  "last_login_date" TEXT,
  "last_login_ip" TEXT(20),
  "is_lock" integer(1),
  "last_lock_time" TEXT,
  "create_user" integer,
  "create_date" TEXT,
  "update_user" integer,
  "update_date" TEXT,
  "is_del" integer
  
);

CREATE TABLE "main"."site1" (
  "id" integer PRIMARY KEY autoincrement NOT NULL ON CONFLICT ABORT COLLATE BINARY,
  "name" text(20),
  "description" TEXT(100),
  "url" TEXT(50),
  "is_active" integer,
  "tag" TEXT(10),
  "type" integer(1),
  "type_id" INTEGER,
  "parent_site_id" INTEGER,
  "membership_restriction" integer(1),
  "login_interface_json" text,
  "project_id" INTEGER,
  "create_user" integer,
  "create_date" TEXT,
  "update_user" integer,
  "update_date" TEXT,
  "is_del" integer(1)
);

 设定自增字段后，会自动生成一张sqlite_sequence表

---

脚本批量造数据案例

在sqlite安装目录（db文件所在目录，新建bat脚本）

insertdb.bat

@ECHO OFF
For /L %%i in (1,1,994) do (sqlite3.exe demo.db<insertdbSiteusesql.bat)
pause

insertdbSiteusesql.bat

保存要执行的语句

insert into site (name,url,is_active,login_interface_json,is_del) VALUES 
('xx4站点','https://www.aia.nginx1.com:30888/default/aia-accesscontrol-target-svc-'||(SELECT FLOOR(1000 + (RANDOM()+9223372036854775808)/2.0/9223372036854775808 * 64536)),
1,'{"htmlTitle":"标题8883","title":"你从我面前缓缓走过的时候，天气都变晴朗","backgroundImg":"/opt/nginx/背景图.jpg"}',0);

insertdbSiteusesql.bat

保存要执行的语句

WITH rand_string(n, v, chars) AS (
  SELECT FLOOR(5 + (RANDOM()+9223372036854775808)/2.0/9223372036854775808 * 11), '', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz@-#￥&*!=+?]中华人民共和国美利坚合众国大日本帝国英格兰法兰西俄罗斯大叔的亚阿拉卡塔卡'
  UNION ALL
  SELECT n-1, v||SUBSTR(chars, FLOOR((RANDOM()+9223372036854775808)/2.0/9223372036854775808 * LENGTH(chars)) + 1, 1), chars
  FROM rand_string
  WHERE n > 0
)
INSERT into user (account,password,username,status,tag,is_del) VALUES ((SELECT v
FROM rand_string
WHERE n = 0),'12345678','张三',0,0,0);

---

随机数、随机字符串生成

返回一个由指定字符（所有数字、大小写字母）组成、长度为 10 的随机字符串。

WITH rand_string(n, v, chars) AS (
  SELECT 1, '', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
  UNION ALL
  SELECT n+1, v||SUBSTR(chars, FLOOR((RANDOM()+9223372036854775808)/2.0/9223372036854775808 * LENGTH(chars)) + 1, 1), chars
  FROM rand_string
  WHERE n <= 20
)
SELECT v
FROM rand_string
WHERE n = (SELECT MAX(n) FROM rand_string);

 返回一个长度大于等于 10 且小于 20 的随机字符串

WITH rand_string(n, v, chars) AS (
  SELECT FLOOR(10 + (RANDOM()+9223372036854775808)/2.0/9223372036854775808 * 10), '', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
  UNION ALL
  SELECT n-1, v||SUBSTR(chars, FLOOR((RANDOM()+9223372036854775808)/2.0/9223372036854775808 * LENGTH(chars)) + 1, 1), chars
  FROM rand_string
  WHERE n > 0
)
SELECT v
FROM rand_string
WHERE n = 0;

返回一个长度大于等于 5 且小于 5+2=7 的随机字符串 

WITH rand_string(n, v, chars) AS (
  SELECT FLOOR(5 + (RANDOM()+9223372036854775808)/2.0/9223372036854775808 * 2), '', '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
  UNION ALL
  SELECT n-1, v||SUBSTR(chars, FLOOR((RANDOM()+9223372036854775808)/2.0/9223372036854775808 * LENGTH(chars)) + 1, 1), chars
  FROM rand_string
  WHERE n > 0
)
SELECT v
FROM rand_string
WHERE n = 0;

随机4位数字验证码

#随机4位数字验证码
SELECT printf('%06d',FLOOR((RANDOM()+9223372036854775808)/2.0/9223372036854775808 * 1000000)) AS captcha;

返回了一个大于等于 1000 并且小于 65536 的随机整数

SELECT FLOOR(1000 + (RANDOM()+9223372036854775808)/2.0/9223372036854775808 * 64536) AS rd;

---

instr(a,b)返回b在a中第一次出现的位置

select id,name,url,login_interface_json as loginInterfaceJson from site where instr('https://www.aia.nginx1.com:30888/default/aia-accesscontrol-target-svc/', url) > 0；

---