当前位置：Gxlcms > 数据库问题 > sqlite学习笔记

sqlite学习笔记

时间：2021-07-01 10:21:17 帮助过：2人阅读

1.java中的数据类型与sqlite中的对应关系

    Boolean 对应 INTEGER(SQLite 并没有单独的布尔存储类型，而是将布尔值存储为整数 0 (false) 和 1 (true)。)
    Byte 对应 INTEGER  
    Short 对应 INTEGER  
    Integer 对应 INTEGER
    Long 对应 INTEGER

    Float 对应 REAL  
    Double 对应 REAL

    String 对应 TEXT

    Byte[] 对应 BLOB

    Date 对应 INTEGER/TEXT/REAL

    [更详细请参考](http://www.oschina.net/translate/data-types-in-sqlite-version-3?cmp)

2.sqlite 数据库导入导出：.dump 命令

  • $sqlite3 testDB.db .dump > testDB.sql
    ==>将 testDB.db 数据库的内容到转储到 ASCII 文本文件 testDB.sql 中

  • $sqlite3 testDB.db < testDB.sql
    ==>将testDB.sql中的数据恢复(插入)到数据库testDB.db中

3.sqlite创建表：

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

4.SQLIte 命令中的 .tables 命令来验证表是否已成功创建

  sqlite>.tables
  COMPANY   DEPARTMENT

5.SQLite 删除表

sqlite>DROP TABLE COMPANY;--删除表
sqlite>.tables；查看删除后的表

6.SQLite Insert 语句

创建表：

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

插入数据

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, ‘Paul‘, 32, ‘California‘, 20000.00 );

也可对应所有字段全部插入：

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

也可使用一个表来填充另一个表

INSERT INTO first_table_name [(column1, column2, ... columnN)] 
SELECT column1, column2, ...columnN 
FROM second_table_name
[WHERE condition];

7.SQLite 逻辑运算符

  • AND
    • AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
    • sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
  • BETWEEN
    • BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
    • qlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;
  • EXISTS
    • EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。
    • sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
    • sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
  • IN
    • IN 运算符用于把某个值与一系列指定列表的值进行比较。
    • sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );
  • NOT IN
    • NOT IN 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
    • sqlite> SELECT * FROM COMPANY WHERE AGE NOT IN ( 25, 27 );
  • LIKE
    • LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
    • sqlite> SELECT * FROM COMPANY WHERE NAME LIKE ‘Ki%’;
    • ‘Ki%’ 表示name是以ki开头的（也可’ki*’,后面类似），’%ki’表示name是以ki结尾的，’%ki%’表示name中间包含ki
  • GLOB
    • GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
    • sqlite> SELECT * FROM COMPANY WHERE NAME GLOB ‘Ki*’;
  • NOT
    • NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
  • OR
    • OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
    • sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;
  • IS NULL
    • NULL 运算符用于把某个值与 NULL 值进行比较。
  • IS NOT
    • IS NOT 运算符与 != 相似。
    • sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
  • IS
    • IS 运算符与 = 相似。
  • ||
    • 连接两个不同的字符串，得到一个新的字符串。
  • UNIQUE
    • UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。

8.SQLite Update 语句

更新 ID 为 6 的客户地址
sqlite> UPDATE COMPANY SET ADDRESS = ‘Texas‘ WHERE ID = 6;

修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值
sqlite> UPDATE COMPANY SET ADDRESS = ‘Texas‘, SALARY = 20000.00;

9.SQLite Delete 语句

删除 ID 为 7 的客户
sqlite> DELETE FROM COMPANY WHERE ID = 7;

从 COMPANY 表中删除所有记录
sqlite> DELETE FROM COMPANY;

10.SQLite Like 子句

SQLite 的 LIKE 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，LIKE 运算符将返回真（true），也就是 1。
这里有两个通配符与 LIKE 运算符一起使用：
百分号 （%）
下划线 （_）
百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。这些符号可以被组合使用。

SELECT FROM table_name
WHERE column LIKE ‘XXXX%‘

or 

SELECT FROM table_name
WHERE column LIKE ‘%XXXX%‘

or

SELECT FROM table_name
WHERE column LIKE ‘XXXX_‘

or

SELECT FROM table_name
WHERE column LIKE ‘_XXXX‘

or

SELECT FROM table_name
WHERE column LIKE ‘_XXXX_‘

WHERE SALARY LIKE ‘200%‘    查找以 200 开头的任意值
WHERE SALARY LIKE ‘%200%‘   查找任意位置包含 200 的任意值
WHERE SALARY LIKE ‘_00%‘    查找第二位和第三位为 00 的任意值
WHERE SALARY LIKE ‘2_%_%‘   查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY LIKE ‘%2‘      查找以 2 结尾的任意值
WHERE SALARY LIKE ‘_2%3‘    查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY LIKE ‘2___3‘   查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

注意区分：_下划线和，-减号：
显示 COMPANY 表中 ADDRESS 文本里包含一个连字符（-）的所有记录：
sqlite> SELECT * FROM COMPANY WHERE ADDRESS  LIKE ‘%-%‘;

11.SQLite GLOB 子句

SQLite 的 GLOB 运算符是用来匹配通配符指定模式的文本值。如果搜索表达式与模式表达式匹配，GLOB 运算符将返回真（true），
也就是 1。与 LIKE 运算符不同的是，GLOB 是大小写敏感的，对于下面的通配符，它遵循 UNIX 的语法。
星号 （*）
问号 （?）
星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。这些符号可以被组合使用


SELECT FROM table_name
WHERE column GLOB ‘XXXX*‘

or 

SELECT FROM table_name
WHERE column GLOB ‘*XXXX*‘

or

SELECT FROM table_name
WHERE column GLOB ‘XXXX?‘

or

SELECT FROM table_name
WHERE column GLOB ‘?XXXX‘

or

SELECT FROM table_name
WHERE column GLOB ‘?XXXX?‘

or

SELECT FROM table_name
WHERE column GLOB ‘????‘


WHERE SALARY GLOB ‘200*‘    查找以 200 开头的任意值
WHERE SALARY GLOB ‘*200*‘   查找任意位置包含 200 的任意值
WHERE SALARY GLOB ‘?00*‘    查找第二位和第三位为 00 的任意值
WHERE SALARY GLOB ‘2??‘     查找以 2 开头，且长度至少为 3 个字符的任意值
WHERE SALARY GLOB ‘*2‘      查找以 2 结尾的任意值
WHERE SALARY GLOB ‘?2*3‘    查找第二位为 2，且以 3 结尾的任意值
WHERE SALARY GLOB ‘2???3‘   查找长度为 5 位数，且以 2 开头以 3 结尾的任意值

12.SQLite Limit 子句

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

从表中提取的行数：sqlite> SELECT * FROM COMPANY LIMIT 6;

从跳过2行提取3条记录：sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

13.SQLite Order By 子句

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

安装SALARY升序排列：
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;
结果：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
7           James       24          Houston     10000.0
2           Allen       25          Texas       15000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
=========================================================

将结果按 NAME 和 SALARY 升序排序：（顺序从左到右，先按NAME排序，得到结果集在按SALARY排序（不影响第一次排序的基础上排序））
如：有Allen1和Allen2，第二次排序只是对此两者的SALARY进行排序
sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;
结果：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
5           David       27          Texas       85000.0
7           James       24          Houston     10000.0
6           Kim         22          South-Hall  45000.0
4           Mark        25          Rich-Mond   65000.0
1           Paul        32          California  20000.0
3           Teddy       23          Norway      20000.0

按 NAME 降序排序：

sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC;

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0
1           Paul        32          California  20000.0
4           Mark        25          Rich-Mond   65000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
5           David       27          Texas       85000.0
2           Allen       25          Texas       15000.0

14.SQLite Group By

下面给出了 GROUP BY 子句的基本语法。GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。
如：
SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN
ORDER BY column1, column2....columnN

数据表如下：  
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

如果您想了解每个客户的工资总额，则可使用 GROUP BY 查询，
如下所示：
sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;

这将产生以下结果：
NAME        SUM(SALARY)
----------  -----------
Allen       15000.0
David       85000.0
James       10000.0
Kim         45000.0
Mark        65000.0
Paul        20000.0
Teddy       20000.0

我们的表具有重复名称的记录，如下所示：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

让我们用同样的 GROUP BY 语句来对所有记录按 NAME 列进行分组，
如下所示：

sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;

这将产生以下结果：

NAME        SUM(SALARY)
----------  -----------
Allen       15000
David       85000
James       20000
Kim         45000
Mark        65000
Paul        40000
Teddy       20000

SQLite Having 子句

下面是 HAVING 子句在 SELECT 查询中的位置

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT OFFSET 

在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。下面是包含 HAVING 子句的 SELECT 语句的语法：
SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2


COMPANY 表有以下记录：
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

下面是一个实例，它将显示名称计数小于 2 的所有记录：

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) < 2;

这将产生以下结果：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000
5           David       27          Texas       85000
6           Kim         22          South-Hall  45000
4           Mark        25          Rich-Mond   65000
3           Teddy       23          Norway      20000


下面是一个实例，它将显示名称计数大于 2 的所有记录：

sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

这将产生以下结果：

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
10          James       45          Texas       5000

SQLite Distinct 关键字

SQLite 的 DISTINCT 关键字与 SELECT 语句一起使用，来消除所有重复的记录，并只获取唯一一次记录。
有可能出现一种情况，在一个表中有多个重复的记录。当提取这样的记录时，DISTINCT 关键字就显得特别有意义，
它只获取唯一一次记录，而不是获取重复记录。

语法

用于消除重复记录的 DISTINCT 关键字的基本语法如下：
SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

实例
假设 COMPANY 表有以下记录：
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0
8           Paul        24          Houston     20000.0
9           James       44          Norway      5000.0
10          James       45          Texas       5000.0

首先，让我们来看看下面的 SELECT 查询，它将返回重复的工资记录：

sqlite> SELECT name FROM COMPANY;

这将产生以下结果：

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James
Paul
James
James

现在，让我们在上述的 SELECT 查询中使用 DISTINCT 关键字：

sqlite> SELECT DISTINCT name FROM COMPANY;

这将产生以下结果，没有任何重复的条目：

NAME
----------
Paul
Allen
Teddy
Mark
David
Kim
James

参考自：http://www.runoob.com/sqlite/sqlite-operators.html

sqlite学习笔记

标签：

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！