sqlite 学习笔记

1
2
3
4
5
6

# 打开数据库
sqlite3 a.db
# 查看库里的表 (如果没有 a.db, 这时候本地会创建一个 a.db 文件)
.tables
# 退出
.exit

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );

INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );

1

SELECT * FROM DEPARTMENT;

1
2
3
4

-- 导出整个数据库
sqlite3 a.db .dump > a.sql
-- 导出部分表
sqlite3 a.db ".dump 'table1' 'table2'" > a.sql

1
2
3
4

sqlite3 a2.db < a.sql
--
-- sqlite 交互式环境
.read a.sql

1
2
3

sqlite>.header on
sqlite>.mode column
sqlite>.timer on

1

ALTER TABLE DEPARTMENT RENAME TO _department_old;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 (column1, column2, ... column_n)
  SELECT column1, column2, ... column_n
  FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

-- article 表 添加 description 字段

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

-- DROP TABLE _article_old;

ALTER TABLE article RENAME TO _article_old;

CREATE TABLE "article" (
  "id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  "title" varchar NOT NULL,
  "description" varchar NOT NULL,
  "content" varchar NOT NULL,
  "createTime" datetime NOT NULL DEFAULT (datetime('now')),
  "updateTime" datetime NOT NULL DEFAULT (datetime('now')),
  "deleteTime" datetime,
  "authorId" integer, CONSTRAINT "FK_a9c5f4ec6cceb1604b4a3c84c87" FOREIGN KEY ("authorId") REFERENCES "user" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION
);

INSERT INTO article (title, description, content,createTime ,updateTime, deleteTime, authorId)
  SELECT title,substr(content, 1, 200), content,createTime ,updateTime, deleteTime, authorId
  FROM _article_old;

COMMIT;

PRAGMA foreign_keys=on;

1
2
3
4
5

-- 完整复制表
CREATE TABLE _article_old_bak AS SELECT * FROM article

-- 复制表结构
CREATE TABLE _article_old_bak AS SELECT * FROM article WHERE 0