format:
CREATE TABLE database_name.table_name(
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,.....
columnN datatype,);
example:
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
.tables 打开数据库后的命令行模式下查询数据库下的所有表名称
.DROP TABLE tableName 按数据库的表名称删除表
.schema tableName 按数据库的表名称查看表的结构
格式化打印数据库数据
sqlite>.header on //显示列表名称
sqlite>.mode column //格式化输出列(好看一点)
sqlite>.timer on //加上run time
增删改查
---------INSERT----------------增------------------------
INSERT INTO COMPANY(ID,NAME,AGE,ADDRESS,SALARY)VALUES(1,'Paul',32,'California',20000.00);---------SELECT----------------查-------------------------
sqlite> SELECT * FROM COMPANY; 输出表内容 *代表全部
sqlite> SELECT ID FROM COMPANY; 输出部分列内容
sqlite> SELECT * FROM COMPANY WHERE SALARY >10000; WHERE设置输出条件(AND/OR)
sqlite> SELECT * FROM COMPANY WHERE AGE IN(25,27); 输出限定在数组内
sqlite> SELECT CURRENT_TIMESTAMP;
sqlite> select datetime('now','localtime'); 显示当前系统时间
---------UPDATE----------------改--------------------------
sqlite> UPDATE COMPANY SET SALARY='100000' WHERE ID =2; 修改ID=2的薪水 不加where则批量改
---------DELETE----------------删------------------
sqlite> DELETE FROM COMPANY; 删除表中所有数据
sqlite> DELETE FROM COMPANY WHERE ID =1; 删除表中ID=1的行数据