sqlite faq

表结构操作

SQL创建表

CREATE TABLE IF NOT EXISTS t_user(uid integer primary key,uname varchar(20),mobile varchar(20))

CREATE TABLE `data_source` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
, `org_id` INTEGER NOT NULL
, `version` INTEGER NOT NULL
, `type` TEXT NOT NULL
, `name` TEXT NOT NULL
, `access` TEXT NOT NULL
, `url` TEXT NOT NULL
, `password` TEXT NULL
, `user` TEXT NULL
, `database` TEXT NULL
, `basic_auth` INTEGER NOT NULL
, `basic_auth_user` TEXT NULL
, `basic_auth_password` TEXT NULL
, `is_default` INTEGER NOT NULL
, `json_data` TEXT NULL
, `created` DATETIME NOT NULL
, `updated` DATETIME NOT NULL
, `with_credentials` INTEGER NOT NULL DEFAULT 0, `secure_json_data` TEXT NULL, `read_only` INTEGER NULL, `uid` TEXT NOT NULL DEFAULT 0)

新增列

alter table   表名 add column  列名 列数据类型

删除列/重命名列

SQlite不支持drop，因此不能使用alter table student drop column name
假如你有一个表t1,有a,b,c列，现在要删除列c

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

上面的方法存在bug，表的字段类型可能发生变化

重命名表

alter table B rename to A

删除表

drop table if exists A

创建表并录入数据

create table B as select colunmA,columnB from A;
create table bookmark1 as select * from bookmark;

SQL查询表是否存在

SELECT count(*) FROM sqlite_master WHERE type="table" AND name = "查询的表名"

数据操作

查询插入数据

insert into data_source_bak select * from data_source;

修改表数据

UPDATE table
SET column_1 = new_value_1,
    column_2 = new_value_2
WHERE
    search_condition 
ORDER column_or_expression
LIMIT row_count OFFSET offset;

UPDATE employees
SET city = 'Toronto',
    state = 'ON',
    postalcode = 'M5P 2N7'
WHERE
    employeeid = 4;
    
UPDATE employees
SET email = LOWER(
	firstname || "." || lastname || "@chinookcorp.com"
)
ORDER BY
	firstname
LIMIT 1;

UPDATE employees
SET email = LOWER(
	firstname || "." || lastname || "@chinookcorp.com"
);

参考：https://www.sqlite.org/faq.html