SQLite 语法快速入门

SQLite 是一个软件库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。

提供一个免费的在线SQLite编辑器

（0）常用命令

# 格式化
.header on
.mode column
.timer on

# 查看表格
.tables

# 查看表结构(建表语句)
.schema table_name

#备份
.backup database.db

PS:SQLite不区分大小写的，但也有一些命令是大小写敏感的，比如 GLOB 和 glob 在 SQLite 的语句中有不同的含义。

（1）创建与删除数据库

• 创建数据库

  • 基础语法

  $ sqlite3 DatabaseName.db
  

• 删除数据库

  • 在指定位置删除数据库文件即可。（无语句）

（2）创建表

• 基础语法

  CREATE TABLE IF NOT EXISTS database_name.table_name(
     column1 datatype  PRIMARY KEY(one or more columns) AUTOINCREMENT,
     column2 datatype,
     column3 datatype,
     .....
     columnN datatype,
  );
  

  AUTOINCREMENT：自增

（3）删除表

• 基础语法

  DROP TABLE database_name.table_name;
  

  PS：数据库名可省，代码当前数据库。

（4）插入数据

• 基础语法

  INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
  VALUES (value1, value2, value3,...valueN);
  

  PS：如果要为表中的所有列添加值，您也可以不需要在 SQLite 查询中指定列名称。但要确保值的顺序与列在表中的顺序一致。*

  CREATE TABLE COMPANY(ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(50),SALARY REAL);

在 COMPANY 表中创建六个记录：

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

• 或：

INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

（5）修改数据

• 基础语法

  UPDATE table_name
  SET column1 = value1, column2 = value2...., columnN = valueN
  WHERE [condition];

• 实例 更新 ID 为 6 的客户地址：

   UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
  

PS:where后可使用 AND 或 OR 运算符来结合 N 个数量的条件。

（6）删除数据

• 基础语法

  DELETE FROM table_name
  WHERE [condition];
  

• 实例

   DELETE FROM COMPANY WHERE ID = 7;
  

（7）查询数据

• 基础语法

  SELECT column1, column2, columnN FROM table_name;
  

  或

  SELECT * FROM table_name; -- 获取所有字段的数据
  

（8）条件查询

• 基础语法

  SELECT column1, column2, columnN 
  FROM table_name
  WHERE [condition]
  

• 实例1：下面的 SELECT 语句列出了 AGE 大于等于 25 且工资大于等于 65000.00 的所有记录：

  select * from company where age>=25 and salary >=65000;
  

• 实例2：下面的 SELECT 语句列出了 AGE 大于等于 25 或工资大于等于 65000.00 的所有记录：

  select * from company where age>=25 or salary >=65000;
  

• 实例3：下面的 SELECT 语句列出了 AGE 不为 NULL 的所有记录，结果显示所有的记录，意味着没有一个记录的 AGE 等于 NULL：

  select * from company where age is not null;
  

• 实例4：下面的 SELECT 语句列出了 NAME 以 ‘Ki’ 开始的所有记录，‘Ki’ 之后的字符不做限制：

  select * from company where name like 'Ki%';
  

• 实例5：下面的 SELECT 语句列出了 NAME 以 ‘Ki’ 开始的所有记录，‘Ki’ 之后的字符不做限制：

  select * from company where name GLOB 'Ki*';
  

• 实例6：下面的 SELECT 语句列出了 AGE 的值为 25 或 27 的所有记录：

  select * from company where age in(25,27);
  

• 实例7：下面的 SELECT 语句列出了 AGE 的值既不是 25 也不是 27 的所有记录：

  select * from company where age not in(25,27);
  

• 实例8：下面的 SELECT 语句列出了 AGE 的值在 25 与 27 之间的所有记录：

  select * from company where age between 25 and 27
  

（9）聚合函数

• 实例：SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。下面是实例：

 SELECT count(*) FROM COMPANY;

（10）分组查询

• GROUP BY对相同的数据进行分组。

• 基础语法

  SELECT column-list
  FROM table_name
  WHERE [ conditions ]
  GROUP BY column1, column2....columnN
  ORDER BY column1, column2....columnN
  

  PS：GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。

• 实例：如果您想查询不同年龄段的员工有多少个，则可以使用Group By查询，如下所示

SELECT AGE, COUNT(*) AS age_count
FROM COMPANY
GROUP BY AGE
ORDER BY AGE;

（11）Having子句

• HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。 WHERE 子句在所选列上设置条件，而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

• 基础语法

  SELECT column1, column2
  FROM table1, table2
  WHERE [ conditions ]
  GROUP BY column1, column2
  HAVING [ conditions ]
  ORDER BY column1, column2
  

• 实例：显示年龄大于 25 岁的分组的平均工资：

SELECT AGE, AVG(SALARY) AS average_salary
FROM COMPANY
GROUP BY AGE
HAVING AGE > 25
ORDER BY AGE;

（12）Order by子句

• SQLite 的 ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据。

• 基础语法

  SELECT column-list 
  FROM table_name 
  [WHERE condition] 
  [ORDER BY column1, column2, .. columnN] [ASC | DESC];
  

  ASC 默认值，从小到大，升序排列

  DESC 从大到小，降序排列

• 实例：将COMPANY表中的数据按 SALARY 降序排序。

  SELECT *
  FROM COMPANY
  ORDER BY SALARY DESC;
  

（13）Limit子句

• SQLite 的 LIMIT 子句用于限制由 SELECT 语句返回的数据数量。

• 基础语法

  SELECT column1, column2, columnN 
  FROM table_name
  LIMIT [no of rows]
  

  下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法：

  SELECT column1, column2, columnN 
  FROM table_name
  LIMIT [no of rows] OFFSET [row num]
  

  SQLite 引擎将返回从下一行开始直到给定的 OFFSET 为止的所有行.

• 实例1：从COMPANY表中取前6条记录。

  SELECT * FROM COMPANY LIMIT 6;
  

• 实例2：从COMPANY表中的第三条记录开始取3条记录。

  SELECT * FROM COMPANY LIMIT 3 OFFSET 3;
  SELECT * FROM COMPANY LIMIT 3,3;
  

• 分页查询

  • 第一页（假设每页10条）

    select * from COMPANY LIMIT 10 OFFSET 0
    

  • 第二页（假设每页10条）

    select * from COMPANY LIMIT 10 OFFSET 10
    

  • 第三页（假设每页10条）

    select * from COMPANY LIMIT 10 OFFSET 20
    

分页公式：

select * from 表 LIMIT PAGESIZE OFFSET （PAPGER-1）*PAGESIZE

凡是过去，皆为序章；凡是未来，皆有可期。

命令：