表结构操作
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