sqlite3

最新推荐文章于 2024-08-06 09:47:19 发布
最新推荐文章于 2024-08-06 09:47:19 发布
阅读量581 收藏 1
点赞数
分类专栏： sqlite3 文章标签： 数据库
原文链接：https://www.runoob.com/sqlite/sqlite-subquery.html
版权

转载：https://www.runoob.com/sqlite/sqlite-subquery.html

文章目录

基础教程

1.数据库

1.1.创建数据库

sqlite3 test.db

1.2.备份数据库

sqlite3 test.db .dump > testDB.sql

1.3.导出数据库

sqlite3 test.db < testDB.sql

1.4.附加数据库（不实用）

#当在同一时间有多个数据库可用，您想使用其中的任何一个，可以附加数据库（就是在sqlte3命令行下可以同时使用两个数据库）
#attach database 'test.db' as 'test';

# ls
ceshi.db  test.db
# sqlite> attach database 'test.db' as 'test';
sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/jianglw/sql/ceshi.db                                
2    test             /home/jianglw/sql/test.db 
# ls 
ceshi.db  test.db

1.5.分离数据库（不实用）

# 与附加数据库相反 detach database 'test'
# sqlite> .database
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/jianglw/sql/ceshi.db                                
2    test             /home/jianglw/sql/test.db                                 
# sqlite> detach database 'test'; # 主要命令
sqlite> .database
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/jianglw/sql/ceshi.db                                
sqlite>

2.表

2.1.字段数据类型

sqlite采用动态的数据类型，会根据存入的值自动判断

数据类型说明
1NULL空值
2INTEGER带符号的整形，具体取决于存入数字的范围大小
3REAL浮点数字
4TEXT字符串文本
5BLOB二进制对象

sqlite支持列的亲和类型概念，任何列仍然可以存储任何类型的数据，当数据插入的，该字段的数据会优先采取亲缘类型作为该值的存储方式。sqllite支持以下五种亲缘类型

亲和类型概述
TEXT数值型数据被插入之前，会转换为文本格式，在插入到目标字段中
NUMERIC当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式
INTEGER对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC
REAL其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。
NONE不做任何的转换，直接以该数据所属的数据类型进行存储

sqlite亲和类型以及类型名称

Example Typenames From The CREATE TABLE Statement or CAST ExpressionResulting AffinityRule Used To Determine Affinity
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8INTEGER1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOBTEXT2
BLOB no datatype specifiedNONE3
REAL DOUBLE DOUBLE PRECISION FLOATREAL4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIMENUMERIC5

sqlite3常用的数据类型

类型说明
smallint16 位元的整数
integer32 位元的整数
float32位元的实数
double64位元的实数
char(n)n 长度的字符，n不能超过 254
varchar(n)长度不固定且其最大长度为 n 的字符，n不能超过 4000
graphic(n)和 char(n) 一样，不过其单位是两个字节 double-bytes， n不能超过127。这个形态是为了支援两个字节长度的字体，例如中文字
vargraphic(n)可变长度且其最大长度为 n 的双字元字串，n不能超过 2000
date包含了 年份、月份、日期
time包含了 小时、分钟、秒
timestamp包含了 年、月、日、时、分、秒、千分之一秒
datetime包含日期时间格式，必须写成’2010-08-05’不能写为’2010-8-5’，否则在读取时会产生错误！

char、varchar、text和nchar、nvarchar、ntext的区别

  • CHAR。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。
  • VARCHAR。存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+1”呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。
  • TEXT。text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。
  • NCHAR、NVARCHAR、NTEXT。这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失

Date和Time数据类型

SQLite 没有一个单独的用于存储日期和/或时间的存储类，但 SQLite 能够把日期和时间存储为 TEXT、REAL 或 INTEGER 值。

存储类日期格式
TEXT格式为 “YYYY-MM-DD HH:MM:SS.SSS” 的日期。
REAL
INTEGER从 1970-01-01 00:00:00 UTC 算起的秒数

2.2.创建表

基本语法如下

# 创建表
CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);
# 查看表的属性
.schema database_name.table_name

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

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

2.3.删除表

基本语法如下

drop table database_name.table_name;

# 实例一
sqlite>drop  table COMPANY;

2.4.添加数据

基本语法如下

insert into TABLE_NAME [(column1, column2, column3,...columnN)]  values (value1, value2, value3,...valueN);
# 或者
insert into TABLE_NAME values (value1,value2,value3,...valueN);
# 使用一个表填充另一个表
INSERT INTO first_table_name [(column1, column2, ... columnN)] 
   SELECT column1, column2, ...columnN 
   FROM second_table_name
   [WHERE condition];

2.5.select语句

基本语法如下

# 设置格式化输出
.header on
.mode column

SELECT column1, column2, columnN FROM table_name;
# 或者
SELECT * FROM table_name;

实例

SELECT * FROM COMPANY;
SELECT ID, NAME, SALARY FROM COMPANY;

3.运算符

3.1.算术运算符

运算符号：+,-,*,/,%

实例

sqlite> select 10 + 20;
10 + 20 = 30

3.2.比较运算符

运算符描述实例
==检查两个操作数的值是否相等，如果相等则条件为真。
=检查两个操作数的值是否相等，如果相等则条件为真。和 ==一样
!=检查两个操作数的值是否相等，如果不相等则条件为真。
<>检查两个操作数的值是否相等，如果不相等则条件为真。和!=一样
>
<
>=
<=
!<检查左操作数的值是否不小于右操作数的值，如果是则条件为真
!>检查左操作数的值是否不大于右操作数的值，如果是则条件为真

实例

# 下面的 SELECT 语句列出了 SALARY 不等于 20,000.00 的所有记录：sqlite> SELECT * FROM COMPANY WHERE SALARY <> 20000;ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------2           Allen       25          Texas       15000.04           Mark        25          Rich-Mond   65000.05           David       27          Texas       85000.06           Kim         22          South-Hall  45000.07           James       24          Houston     10000.0

3.3.逻辑运算符

运算符描述
ANDAND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。
BETWEEN…AND…BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。
EXISTS判断语句是否存在，存在就执行对应的结果，否则反之
ININ 运算符用于把某个值与一系列指定列表的值进行比较。
NOT ININ 运算符的对立面，用于把某个值与不在一系列指定列表的值进行比较。
LIKELIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。
GLOBGLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOTNOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OROR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULLNULL 运算符用于把某个值与 NULL 值进行比较。
ISIS 运算符与 = 相似。
IS NOTIS NOT 运算符与 != 相似。
||链接两个相同的字符串，得到一个新的字符串。
UNIQUEUNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。

实例

# 第一步：创建表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,'xinyang',20000.0);insert into company(id,name,age,address,salary) values (2,'Allen',33,'dongbei',15000.0);insert into company(id,name,age,address,salary) values (3,'Teddy',25,'bj',20000.0);insert into company(id,name,age,address,salary) values (4,'Mark',23,'sh',65000.0);insert into company(id,name,age,address,salary) values (5,'David',27,'sz',85000.0);insert into company(id,name,age,address,salary) values (6,'kim',22,'gd',45000.0);insert into company(id,name,age,address,salary) values (7,'James',24,'cs',10000.0);insert into company(id,name,age,salary) values (8,'kangkang',16,1000.0);# AND:查找年龄大于25且工资大于20000的员工sqlite> select * from company where age > 25 and salary > 20000;id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------5           David       27          sz          85000.0# BETWEEN:查找年龄在25和33之间的员工(包含25和33)sqlite> select * from company where age between 25 and 33;id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0   2           Allen       33          dongbei     15000.0   3           Teddy       25          bj          20000.0   5           David       27          sz          85000.0 # EXISTS：# 案例一：因为salary有个85000，所有就会输出select *select * from company where exists(select age from company where salary > 65000);id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0   2           Allen       33          dongbei     15000.0   3           Teddy       25          bj          20000.0   4           Mark        23          sh          65000.0   5           David       27          sz          85000.0   6           kim         22          gd          45000.0   7           James       24          cs          10000.0   8           kangkang    16                      1000.0  # 案例2：因为salary最高位85000，这个select age from company where salary > 85000 不存在，所有就输出空select * from company where exists(select age from company where salary > 85000);# IN：列出age值等于25或者27select * from company where age in (25,27);id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------3           Teddy       25          bj          20000.0   5           David       27          sz          85000.0   # NOT IN:列出age不等于25且不等于27select * from company where age not in (25,27);id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0   2           Allen       33          dongbei     15000.0   4           Mark        23          sh          65000.0   6           kim         22          gd          45000.0   7           James       24          cs          10000.0   8           kangkang    16                      1000.0 # LIKE：不区分大小写sqlite> select * from company where name like 'k%';id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------6           kim         22          gd          45000.0   8           kangkang    16                      1000.0    sqlite> select * from company where name like '%a%';id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0   2           Allen       33          dongbei     15000.0   4           Mark        23          sh          65000.0   5           David       27          sz          85000.0   7           James       24          cs          10000.0   8           kangkang    16                      1000.0    #GLOB：区分大小写和like用法类似# ORselect * from company where age >25 or salary > 65000;id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0   2           Allen       33          dongbei     15000.0   5           David       27          sz          85000.0 # IS NULLselect * from company where address id null;id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------8           kangkang    16                      1000.0    # NOT IS NULL# IS

3.4.位运算符

A = 0011 1100

B = 0000 1101

-----------------

A&B = 0000 1100

A|B = 0011 1101

~A = 1100 0011

运算符描述实例
&如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中(A & B) 将得到 12，即为 0000 1100
|如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中。(A |B) 将得到 61，即为 0011 1101
~二进制补码运算符是一元运算符，具有"翻转"位效应，即0变成1，1变成0。(~A ) 将得到 -61，即为 1100 0011，一个有符号二进制数的补码形式。
<<二进制左移运算符。左操作数的值向左移动右操作数指定的位数。A << 2 将得到 240，即为 1111 0000
>>二进制右移运算符。左操作数的值向右移动右操作数指定的位数。A >> 2 将得到 15，即为 0000 1111

4.where子句

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;SELECT * FROM COMPANY WHERE AGE IS NOT NULL;SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';SELECT * FROM COMPANY WHERE AGE IN ( 25, 27 );SELECT * FROM COMPANY WHERE AGE BETWEEN 25 AND 27;SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

5.AND/OR运算符

SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000;

6.Update语句

 UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;# 如果想要修改company表中address和salary列中所有的值，不需要使用where子句====>>>不常用 sqlite> UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00; ID          NAME        AGE         ADDRESS     SALARY----------  ----------  ----------  ----------  ----------1           Paul        32          Texas       20000.02           Allen       25          Texas       20000.03           Teddy       23          Texas       20000.04           Mark        25          Texas       20000.05           David       27          Texas       20000.06           Kim         22          Texas       20000.07           James       24          Texas       20000.0

7.Delete语句

DELETE FROM COMPANY WHERE ID = 7;

8.Like子句

  • 百分号（%）：代表零个，一个或者多个数字以及字符
  • 下划线（__）：代表单一数字或者字符，这些符号可以组合使用
语句描述
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 结尾的任意值

9.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 结尾的任意值

10.Limit语句

# 语法规则
SELECT column-list 
FROM table_name 
[WHERE condition] 
[Limit]

SELECT column-list 
FROM table_name 
[WHERE condition] 
[Limit offset ]

实例

# 输出限制3行select * from company where age > 22 limit 3;# 年龄大于22 从下标为2的地方开始，并且限制输出2行select * from company where age > 22 limit 2 offset 2;

11.Order By

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

实例

select * from company order by salary asc; # 升序，DESC为降序id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------8           kangkang    16          cx          1000.0    7           James       24          cs          10000.0   2           Allen       33          dongbei     15000.0   1           Paul        32          xinyang     20000.0   3           Teddy       25          bj          20000.0   6           kim         22          gd          45000.0   4           Mark        23          sh          65000.0   5           David       27          sz          85000.0  select * from company where age > 27 order by salary asc; # 升序，DESC为降序id          name        age         address     salary    ----------  ----------  ----------  ----------  ----------2           Allen       33          dongbei     15000.0   1           Paul        32          xinyang     20000.0

12.Group By

SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用，来对相同的数据进行分组。

在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前

# 语法格式SELECT column-listFROM table_nameWHERE [ conditions ]GROUP BY column1, column2....columnNORDER BY column1, column2....columnN

实例

select name,sum(age) as total from company where age > 22 group by name;name        total     ----------  ----------Allen       33        David       27        James       24        Mark        23        Paul        32        Teddy       25        kim         47

13.Having子句

Having 子句允许指定条件进行过滤

Having子句必须放在Group by 之后。必须放在order by 语句之前

# 语法格式SELECTFROMWHEREGROUP BYHAVINGORDER BYSELECT column1, column2FROM table1, table2WHERE [ conditions ]GROUP BY column1, column2HAVING [ conditions ]ORDER BY column1, column2

实例

sqlite> select name,sum(age) as total from company where age > 22 group by name having salary < 30000 order by sum(age) asc;name        total     ----------  ----------James       24        Teddy       25        Paul        32        Allen       33

14.Distinct 关键字

SELECT DISTINCT column1, column2,.....columnN FROM table_nameWHERE [condition]

实例

select distinct name from company;name      ----------Paul      Allen     Teddy     Mark      David     kim       James     kangkang

高级教程

1.SQLite约束

1.1.NOT NULL约束

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

1.2.DEFAULT约束

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

1.3.UNIQUE约束

# 下面的 SQLite 语句创建一个新的表 COMPANY，并增加了五列。在这里，AGE 列设置为 UNIQUE，所以不能有两个相同年龄的记录：
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL UNIQUE,
   ADDRESS        CHAR(50),
   SALARY         REAL    DEFAULT 50000.00
);

1.4.PRIMARY KEY 约束

  • 主键是表中的一个字段，唯一标识数据库表中的各行/记录。主键必须包含唯一值
  • 一个表只能有一个主键，它可以由一个或多个字段组成
  • 通过把主键设置为其他表的外键，来创建表之间的关系。
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

1.5.CHECK约束

# 例如，下面的 SQLite 创建一个新的表 COMPANY，并增加了五列。在这里，我们为 SALARY 列添加 CHECK，所以工资不能为零：

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

2.Join

2.1.CROSS JOIN 交叉连接

# 语法
SELECT ... FROM table1 CROSS JOIN table2 ...

实例

select * from company cross join department;id          name        age         address     salary      ID          DEPT        EMP_ID    ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0     1           IT Billing  1         1           Paul        32          xinyang     20000.0     2           Engineerin  2         1           Paul        32          xinyang     20000.0     3           Finance     7         2           Allen       33          dongbei     15000.0     1           IT Billing  1         2           Allen       33          dongbei     15000.0     2           Engineerin  2         2           Allen       33          dongbei     15000.0     3           Finance     7         3           Teddy       25          bj          20000.0     1           IT Billing  1         3           Teddy       25          bj          20000.0     2           Engineerin  2         3           Teddy       25          bj          20000.0     3           Finance     7         4           Mark        23          sh          65000.0     1           IT Billing  1         4           Mark        23          sh          65000.0     2           Engineerin  2         4           Mark        23          sh          65000.0     3           Finance     7         5           David       27          sz          85000.0     1           IT Billing  1         5           David       27          sz          85000.0     2           Engineerin  2         5           David       27          sz          85000.0     3           Finance     7         6           kim         22          gd          45000.0     1           IT Billing  1         6           kim         22          gd          45000.0     2           Engineerin  2         6           kim         22          gd          45000.0     3           Finance     7         7           James       24          cs          10000.0     1           IT Billing  1         7           James       24          cs          10000.0     2           Engineerin  2         7           James       24          cs          10000.0     3           Finance     7         8           kangkang    16          cx          1000.0      1           IT Billing  1         8           kangkang    16          cx          1000.0      2           Engineerin  2         8           kangkang    16          cx          1000.0      3           Finance     7         9           kim         23          sx          30000.0     1           IT Billing  1         9           kim         23          sx          30000.0     2           Engineerin  2         9           kim         23          sx          30000.0     3           Finance     7         10          kim         24          sx          40000.0     1           IT Billing  1         10          kim         24          sx          40000.0     2           Engineerin  2         10          kim         24          sx          40000.0     3           Finance     7

2.2.INNER JOIN 内连接

就是求交集

select * from company inner join department on company.id=department.emp_id;id          name        age         address     salary      ID          DEPT        EMP_ID    ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0     1           IT Billing  1         2           Allen       33          dongbei     15000.0     2           Engineerin  2         7           James       24          cs          10000.0     3           Finance     7

2.3.Left OUTER JOIN外连接

外连接（OUTER JOIN）是内连接（INNER JOIN）的扩展。虽然 SQL 标准定义了三种类型的外连接：LEFT、RIGHT、FULL，但 SQLite 只支持 左外连接（LEFT OUTER JOIN）。

# 语法SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...

实例

 select * from company left outer join department on company.id=department.emp_id;  id          name        age         address     salary      ID          DEPT        EMP_ID    ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0     1           IT Billing  1         2           Allen       33          dongbei     15000.0     2           Engineerin  2         3           Teddy       25          bj          20000.0                                       4           Mark        23          sh          65000.0                                       5           David       27          sz          85000.0                                       6           kim         22          gd          45000.0                                       7           James       24          cs          10000.0     3           Finance     7         8           kangkang    16          cx          1000.0                                        9           kim         23          sx          30000.0                                       10          kim         24          sx          40000.0

3.Unions子句

  • UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。为了使用 UNION，每个 SELECT 被选择的列数必须是相同的，相同数目的列表达式，相同的数据类型，并确保它们有相同的顺序
  • UNION ALL 运算符用于结合两个 SELECT 语句的结果，包括重复行。
# 语法SELECT column1 [, column2 ]FROM table1 [, table2 ][WHERE condition]UNIONSELECT column1 [, column2 ]FROM table1 [, table2 ][WHERE condition]

实例

select * from company inner join department on company.id=department.emp_id;id          name        age         address     salary      ID          DEPT        EMP_ID    ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0     1           IT Billing  1         2           Allen       33          dongbei     15000.0     2           Engineerin  2         7           James       24          cs          10000.0     3           Finance     7 select * from company left outer join department on company.id=department.emp_id;id          name        age         address     salary      ID          DEPT        EMP_ID    ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0     1           IT Billing  1         2           Allen       33          dongbei     15000.0     2           Engineerin  2         3           Teddy       25          bj          20000.0                                       4           Mark        23          sh          65000.0                                       5           David       27          sz          85000.0                                       6           kim         22          gd          45000.0                                       7           James       24          cs          10000.0     3           Finance     7         8           kangkang    16          cx          1000.0                                        9           kim         23          sx          30000.0                                       10          kim         24          sx          40000.0      # 这一步相当于将重复行去掉了 其实就相当于 select * from company left outer join department on company.id=department.emp_id;select * from company inner join department on company.id=department.emp_idunionselect * from company left outer join department on company.id=department.emp_id;id          name        age         address     salary      ID          DEPT        EMP_ID    ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0     1           IT Billing  1         2           Allen       33          dongbei     15000.0     2           Engineerin  2         3           Teddy       25          bj          20000.0                                       4           Mark        23          sh          65000.0                                       5           David       27          sz          85000.0                                       6           kim         22          gd          45000.0                                       7           James       24          cs          10000.0     3           Finance     7         8           kangkang    16          cx          1000.0                                        9           kim         23          sx          30000.0                                       10          kim         24          sx          40000.0     # 这一步相当于将两个表的数据合并到一张表中select * from company inner join department on company.id=department.emp_idunion allselect * from company left outer join department on company.id=department.emp_id;id          name        age         address     salary      ID          DEPT        EMP_ID    ----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------1           Paul        32          xinyang     20000.0     1           IT Billing  1         2           Allen       33          dongbei     15000.0     2           Engineerin  2         7           James       24          cs          10000.0     3           Finance     7         1           Paul        32          xinyang     20000.0     1           IT Billing  1         2           Allen       33          dongbei     15000.0     2           Engineerin  2         3           Teddy       25          bj          20000.0                                       4           Mark        23          sh          65000.0                                       5           David       27          sz          85000.0                                       6           kim         22          gd          45000.0                                       7           James       24          cs          10000.0     3           Finance     7         8           kangkang    16          cx          1000.0                                        9           kim         23          sx          30000.0                                       10          kim         24          sx          40000.0

4.NULL值

NULL值是用来表示一个缺失值的项，表中的NULL值在字段中显示为空白的一个值。

实例

# 1. 创建表SQLite> CREATE TABLE COMPANY(   ID INT PRIMARY KEY     NOT NULL,   NAME           TEXT    NOT NULL,   AGE            INT     NOT NULL,   ADDRESS        CHAR(50),   SALARY         REAL);# 2.更新 UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7); # 3.IS NOT NULL用法sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY        FROM COMPANY        WHERE SALARY IS NOT NULL;# 4.IS NULL用法 sqlite> SELECT  ID, NAME, AGE, ADDRESS, SALARY        FROM COMPANY        WHERE SALARY IS NULL;

5.别名

# 语法# 1.表的别名SELECT column1, column2....FROM table_name AS alias_nameWHERE [condition];# 2.列的别名SELECT column_name AS alias_nameFROM table_nameWHERE [condition];

6.触发器

**触发器（Trigger）**是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用

  • SQLite 的触发器（Trigger）可以指定在特定的数据库表发生 DELETE、INSERT 或 UPDATE 时触发，或在一个或多个指定表的列发生更新时触发。
  • BEFORE 或 AFTER 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作
  • 当触发器相关联的表删除时，自动删除触发器（Trigger）
  • SQLite 只支持 FOR EACH ROW 触发器（Trigger）
  • WHEN 子句和触发器（Trigger）动作可能访问使用表单 NEW.column-name 和 OLD.column-name 的引用插入、删除或更新的行元素，其中 column-name 是从与触发器关联的表的列的名称。
# 语法# event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作CREATE  TRIGGER trigger_name [BEFORE|AFTER] event_name ON table_nameBEGIN -- 触发器逻辑....END;

实战

# 创建表sqlite> CREATE TABLE AUDIT(    EMP_ID INT NOT NULL,    ENTRY_DATE TEXT NOT NULL);# 创建tigger触发器CREATE TRIGGER audit_log AFTER INSERT ON COMPANYBEGIN   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));END;# 插入数据insert into company(id,name,age,address,salary) values (11,'ul',32,'xinyang',20000.0);# 查看数据sqlite> select * from audit;EMP_ID      ENTRY_DATE         ----------  -------------------11          2021-06-01 03:22:35

# 列出触发器SELECT name FROM sqlite_master WHERE type = 'trigger';# 列出指定表中的触发器SELECT name FROM sqlite_master WHERE type = 'trigger' AND tbl_name = 'COMPANY';# 删除触发器DROP TRIGGER trigger_name;

# for each row 和 when 的实例# for each row 是操作语句每影响到一行的时候就触发一次，也就是删了 10 行就触发 10 次 # for each state 一条操作语句就触发一次，有时没有被影响的行也执行# 例如更新操作，更新的数据和原先的数据一样 for each row 就不会执行，for each state就会执行CREATE TRIGGER trigger_name AFTER UPDATE OF id ON table_1 FOR EACH ROW WHEN new.id>30 BEGIN UPDATE table_2 SET id=new.id WHERE table_2.id=old.id;END;上面的触发器在 table_1 改 id 的时候如果新的 id>30 就把 表table_2 中和表table_1 id 相等的行一起改为新的 id

7.索引

什么是索引？

索引（Index）是一种特殊的查找表，数据库搜索引擎用来加快数据检索，简单地说，索引是一个指向表中数据的指针。一个数据库中的索引与一本书的索引目录是非常相似的。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据

7.1.单列索引

单列索引是一个只基于表的一个列上创建的索引

# 语法
CREATE INDEX index_name
ON table_name (column_name);

# 实例
create index id on company (id);

7.2.唯一索引

使用唯一索引不仅是为了性能，同时也为了数据的完整性。唯一索引不允许任何重复的值插入到表中。

# 语法
CREATE UNIQUE INDEX index_name on table_name (column_name);

# 实例
create unique index ceshiid on company(id);

7.3.组合索引

组合索引是基于一个表的两个或多个列上创建的索引。是否要创建一个单列索引还是组合索引，要考虑到您在作为查询过滤条件的 WHERE 子句中使用非常频繁的列。如果只使用到一个列，则选择使用单列索引。如果在作为过滤的 WHERE 子句中有两个或多个列经常使用，则选择使用组合索引

# 语法
CREATE INDEX index_name on table_name (column1, column2);

# 实例
create index nameandage on company (age,name);

7.4.隐式索引

隐式锁引是在创建对象时，又数据库服务器自动创建的索引，索引自动创建为主键约束和唯一约束

7.5.查看索引

# 查看数据库里面的所有索引
select * from sqlite_master where type = 'index';


# 产看指定表的索引
.indices company
select * from sqlite_master where type = 'index' and tbl_name='company';

7.6.删除索引

# 语法格式
DROP INDEX index_name;

7.7.什么情况下避免使用索引

虽然索引的目的在于提高数据库的性能，但这里有几个情况需要避免使用索引。使用索引时，应重新考虑下列准则：

  • 索引不应该使用较小的表中
  • 索引不应该使用在有频繁的大批量的更新和插入的操作表中
  • 索引不应该使用在含有大量的NULL值列中
  • 索引不应该使用在频繁操作的列中

8.ALter命令

  • 重命名表
  • 在已有的表中添加对应的列
# 语法# 用来重命名已有的表的 ALTER TABLE 的基本语法如下：ALTER TABLE database_name.table_name RENAME TO new_table_name;# 用来在已有的表中添加一个新的列的 ALTER TABLE 的基本语法如下：ALTER TABLE database_name.table_name ADD COLUMN column_def...;## 实例# 重命名alter table company rename to old-company;# 添加列alter table company add column sex char(1);

9. Truncate Table清空数据

什么是Truncate Table？

就是清空表中所有的数据，同时会保存表的结构。

在 SQLite 中，并没有 TRUNCATE TABLE 命令，但可以使用 SQLite 的 DELETE 命令从已有的表中删除全部的数据。

# 语法规则sqlite> DELETE FROM table_name; # 注意这种方法无法将递增数归零# 如果想让递增数归零，可以使用以下方法sqlite> DELETE FROM sqlite_sequence WHERE name = 'table_name';

10.视图

视图只不过是通过相关的名称存储在数据库中的要给SQLite语句

视图（View）可以包含一个表的所有行或从一个或多个表选定行。视图（View）可以从一个或多个表创建，这取决于要创建视图的 SQLite 查询。

视图（View）是一种虚表，允许用户实现以下几点：

  • 用户或用户组查找结构数据的方式更自然或直观。
  • 限制数据访问，用户只能看到有限的数据，而不是完整的表。
  • 汇总各种表中的数据，用于生成报告

SQLite 视图是只读的，因此可能无法在视图上执行 DELETE、INSERT 或 UPDATE 语句。但是可以在视图上创建一个触发器，当尝试 DELETE、INSERT 或 UPDATE 视图时触发，需要做的动作在触发器内容中定义。

# 语法CREATE [TEMP | TEMPORARY] VIEW view_name ASSELECT column1, column2.....FROM table_nameWHERE [condition];# 实例create view company_view as select ID,NAME,AGE from company;select * from company_view;# 删除视图 DROP VIEW view_name;

11.事务

事务（Transaction）是一个对数据库执行工作单元。事务（Transaction）是以逻辑顺序完成的工作单位或序列，可以是由用户手动操作完成，也可以是由某种数据库程序自动完成

事务（Transaction）是指一个或多个更改数据库的扩展。例如，如果您正在创建一个记录或者更新一个记录或者从表中删除一个记录，那么您正在该表上执行事务。重要的是要控制事务以确保数据的完整性和处理数据库错误

实际上，您可以把许多的 SQLite 查询联合成一组，把所有这些放在一起作为事务的一部分进行执行

事务的属性：

  • **原子性（Atomicity）：**确保工作单位内的所有操作都成功完成，否则，事务会在出现故障时终止，之前的操作也会回滚到以前的状态。
  • **一致性（Consistency)：**确保数据库在成功提交的事务上正确地改变状态。
  • **隔离性（Isolation）：**使事务操作相互独立和透明。
  • **持久性（Durability）：**确保已提交事务的结果或效果在系统发生故障的情况下仍然存在。

实例

# 这些语句要不全部成功，要不全部失败sqlite> BEGIN;sqlite> DELETE FROM COMPANY WHERE AGE = 23;sqlite> DELETE FROM COMPANY WHERE AGE = 25;sqlite> ROLLBACK;sqlite> BEGIN;sqlite> DELETE FROM COMPANY WHERE AGE = 23;sqlite> DELETE FROM COMPANY WHERE AGE = 25;sqlite> COMMIT;

12.子查询

以下是子查询必须遵循的几个规则：

  • 子查询必须用括号括起来
  • 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。
  • ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。
  • 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符
  • BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

12.1.SELECT 语句中的子查询使用

# 语法规则
SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])
      
# 实例：

# 创建数据库
CREATE TABLE COMPANY(
   ID INTEGER PRIMARY KEY   AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ('Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ('Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'James', 24, 'Houston', 10000.00 );

# 创建数据库
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 );


# DEPARTMENT里面的EMP_ID 查询company里面用户
select * from company where ID in (select EMP_ID from DEPARTMENT);

ID          NAME        AGE         ADDRESS     SALARY    
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0   
2           Allen       25          Texas       15000.0   
7           James       24          Houston     10000.0

12.2.INSERT 语句中的子查询使用

#语法规则
INSERT INTO table_name [ (column1 [, column2 ]) ]
           SELECT [ *|column1 [, column2 ]
           FROM table1 [, table2 ]
           [ WHERE VALUE OPERATOR ]
           
#实例1
CREATE TABLE COMPANY(
   ID INTEGER PRIMARY KEY   AUTOINCREMENT,
   NAME           TEXT      NOT NULL,
   AGE            INT       NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ('Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ('Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'James', 24, 'Houston', 10000.00 );

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


# 向NEWCOMPANY 写入数据
insert into  newcompany select * from company where id in (select id from 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

12.3.UPDATE 语句中的子查询使用

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]
   
 # 实例  
 UPDATE COMPANY
     SET SALARY = SALARY * 0.50
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE >= 27 );
 ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  10000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

12.4.DELETE 语句中的子查询使用

# 语法
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]
   
# 实例：
sqlite> DELETE FROM COMPANY
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE > 27 );
ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       42500.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

13.主键自增长

# 创建表
CREATE TABLE table_name(
   column1 INTEGER AUTOINCREMENT,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

# 向表中插入数据
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ('Allen', 25, 'Texas', 15000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ('Teddy', 23, 'Norway', 20000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'David', 27, 'Texas', 85000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );
INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY) VALUES ( 'James', 24, 'Houston', 10000.00 );

14.Explain

# 用来描述表的细节，然后进行分析故障
EXPLAIN  QUERY PLAN [SQLite Query]

# 如下显示的为全表查询
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
0|0|0|SCAN TABLE t1

# 如果使用了索引
sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
0|0|0|SEARCH TABLE t1 USING INDEX i1 # 这边就显示使用了索引

17.常用函数

函数描述
SQLite COUNT 函数SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。
SQLite MAX 函数SQLite MAX 聚合函数允许我们选择某列的最大值。
SQLite MIN 函数SQLite MIN 聚合函数允许我们选择某列的最小值。
SQLite AVG 函数SQLite AVG 聚合函数计算某列的平均值。
SQLite SUM 函数SQLite SUM 聚合函数允许为一个数值列计算总和。
SQLite RANDOM 函数SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。
SQLite ABS 函数SQLite ABS 函数返回数值参数的绝对值。
SQLite UPPER 函数SQLite UPPER 函数把字符串转换为大写字母
SQLite LOWER 函数SQLite LOWER 函数把字符串转换为小写字母。
SQLite LENGTH 函数SQLite LENGTH 函数返回字符串的长度。
SQLite sqlite_version 函数SQLite sqlite_version 函数返回 SQLite 库的版本。
# 实例
sqlite> SELECT count(*) FROM COMPANY;
count(*)
----------
7

sqlite> SELECT max(salary) FROM COMPANY;
max(salary)
-----------
85000.0

sqlite> SELECT min(salary) FROM COMPANY;
min(salary)
-----------
10000.0

sqlite> SELECT avg(salary) FROM COMPANY;
avg(salary)
----------------
37142.8571428572

sqlite> SELECT sum(salary) FROM COMPANY;
sum(salary)
-----------
260000.0

sqlite> SELECT random() AS Random;
Random
-------------------
5876796417670984050

sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");
abs(5)      abs(-15)    abs(NULL)   abs(0)      abs("ABC")
----------  ----------  ----------  ----------  ----------
5           15                      0           0.0

sqlite> SELECT upper(name) FROM COMPANY;
upper(name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMES

sqlite> SELECT lower(name) FROM COMPANY;
lower(name)
-----------
paul
allen
teddy
mark
david
kim
james

sqlite> SELECT name, length(name) FROM COMPANY;
NAME        length(name)
----------  ------------
Paul        4
Allen       5
Teddy       5
Mark        4
David       5
Kim         3
James       5

实战

1.sqlite多表关联update

set时，要将table2的num2的值赋给table1的num1字段，要select一下table2，并在括号关联起来

update table1
set  num1 = (select num2 from table2 where table2.pid=table1.id)
where...

更新多个字段时：

update table1
set  num1 = (select num2 from table2 where table2.pid=table1.id),
num11 = (select num22 from table2 where table2.pid=table1.id)
where...

使用where

update table1
set  num = 99
where table1.id=(select pid from table2 where table2.pid=table1.id)

2…sqlite多表插入

# 希望插入全部数据
insert into tab1 select * from tab2
# 希望导入指定的字段
insert into t_a  ( name, age ) select name, age from t_b where age > 10;
# 只插入目标表中不存在的记录
insert into t_a (name) select name from t_b where not exists (select *  from t_a where t_a.name = t_b.name);

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

sqlite 原理

1.锁

定义：

​ SQLite 采用粗放型的锁。当一个连接要写数据库，所有其它的连接被锁住，直到写连接结束了它的事务。SQLite 有一个加锁表，来帮助不同的写数据库都能够在最后一刻再加锁，以保证最大的并发性。

SQLite 使用锁逐步上升机制，为了写数据库，连接需要逐级地获得排它锁。SQLite 有 5 个不同的锁状态：未加锁(UNLOCKED)、共享 (SHARED)、保留 (RESERVED)、未 决(PENDING) 和排它(EXCLUSIVE)。每个数据库连接在同一时刻只能处于其中一个状态。每种状态(未加锁状态除外)都有一种锁与之对应

原理：

  • 最初的状态是未加锁状态，在此状态下，连接还没有存取数据库。当连接到了一个数据库，甚至已经用BEGIN 开始了一个事务时，连接都还处于未加锁状态
  • 未加锁状态的下一个状态是共享状态。为了能够从数据库中读(不写)数据，连接必须首先进入共享状态，也就是说首先要获得一个共享锁。**多个连接可以同时获得并保持共享锁，**也就是说多个连接可以同时从同一个数据库中读数据。但哪怕只有一个共享锁还没有释放，也不允许任何连接写数据库
  • 如果一个连接想要写数据库，它必须首先获得一个保留锁。一个数据库上同时只能有一个保留锁。保留锁可以与共享锁共存，保留锁是写数据库的第1 阶段。保留锁即不阻止其它拥有共享锁的连接继续读数据库，也不阻止其它连接获得新的共享锁。 一旦一个连接获得了保留锁，它就可以开始处理数据库修改操作了，尽管这些修改只能在缓冲区中进行，而不是实际地写到磁盘。
  • 当连接想要提交修改(或事务)时，还必须首先将保留锁提升为未决锁。获得未决锁之后，其它连接就不能再获得新的共享锁了，但已经拥有共享锁的连接仍然可以继续正常读数据库。此时，拥有未决锁的连接等待其它拥有共享锁的连接完成工作并释放其共享锁。
  • 一旦所有其它共享锁都被释放，拥有未决锁的连接就可以将其锁提升至排它锁，此时就可以自由地对数据库进行修改了。所有以前对缓冲区所做的修改都会被写到数据库文件。

1.1.锁的事务

定义

SQLite有三种不同的事务，使用不同的锁状态。 事务可以开始于： DEFERRED、 MMEDIATE或 EXCLUSIVE。事务类型在BEGIN 命令中指定： BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] TRANSACTION;

  • 一个DEFERRED事务不获取任何锁(直到它需要锁的时候)，BEGIN 语句本身也不会做什么事情——它开始于 UNLOCK 状态。默认情况下就是这样的，如果仅仅用 BEGIN 开始一个事务，那么事务就是DEFERRED的，同时它不会获取任何锁；当对数据库进行第一次读操作时，它会获取SHARED锁；同样，当进行第一次写操作时，它会获取RESERVED锁
  • 由 BEGIN 开始的 IMMEDIATE 事务会尝试获取 RESERVED 锁。如果成功，BEGIN IMMEDIATE保证没有别的连接可以写数据库。但是，别的连接可以对数据库进行读操作；但是，RESERVED锁会阻止其它连接的BEGIN IMMEDIATE或者BEGIN EXCLUSIVE命令。
  • EXCLUSIVE事务会试着获取对数据库的EXCLUSIVE锁。这与 IMMEDIATE类似，但是一旦成功，EXCLUSIVE 事务保证没有其它的连接，所以就可对数据库进行读写操作了。

**总结：**如果两个连接都以BEGIN IMMEDIATE 开始事务，那么死锁就不会发生。在这种情况下，在同一时刻只能有一个连接进入BEGIN IMMEDIATE，其它的连接就得等待。BEGIN IMMEDIATE 和BEGIN EXCLUSIVE 通常被写事务使用。就像同步机制一样，它防止了死锁的产生。

**基本准则：**如果你正在使用的数据库没有其它的连接，用BEGIN 就足够了。但是，如果你使用的数据库有其它的连接也会对数据库进行写操作，就得使用 BEGIN IMMEDIATE或 BEGIN EXCLUSIVE开始你的事务。

1.2.死锁案例

案例一

		A连接                                     B连接 
sqlite> BEGIN; 
                                    sqlite> BEGIN; 
                                    sqlite> INSERT INTO foo VALUES('x'); 
sqlite> SELECT * FROM foo; 
                                    sqlite> COMMIT; 
                                    SQL error: database is locked 
sqlite> INSERT INTO foo VALUES ('x'); 
SQL error: database is locked

#说明：
# 1.B连接insert into 会将锁更新为pending锁
# 2.A连接会获取shard锁，但是没有释放，INSERT INTO  会将shard锁升级为RESERVED锁然后升级为pending锁，但是pending锁已经被B连接拿了，此时A连接的shard锁     没有释放，就造成了死锁

案例二

		A连接                                     B连接                               c连接
sqlite> begin immediate; 
                                    sqlite>  begin immediate;                   sqlite>  begin EXCLUSIVE; 
								    Error: database is locked                   Error: database is locked
#说明：
# 1.A连接获reserved锁，就不会让别的连接开启  MMEDIATE或 EXCLUSIVE事务

案例三：

		A连接                                     B连接                              
sqlite>  begin EXCLUSIVE;
                                    sqlite>  select * from company;                   
								    Error: database is locked  
#说明：
# 1.A连接获取到了EXCLUSIVE锁，就不会允许其它连接获取SHARED锁，就会导致死锁

2.sqlite源码分析

尼古拉斯丶六六
关注
专栏目录
sqlite3 sqlite3 sqlite3
11-20
sqlite3 svn cleanup sqlite3 svn cleanup sqlite3 svn cleanup
sqlite3.exe绿色版 下载
10-01
SQLite是一款轻型的数据库，它的设计目标是嵌入式的，而且目前已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作...
sqlite3使用
kriszhang的博客
05-24 1002
sqlite3使用sqlite可以在https://www.sqlite.org/网站上下载。支持Linux, MacOS, Windows等各种操作系统。下载后不需要安装，解压后就可以使用，追加路径到path中，让其可以在任何目录下都可以使用。进入命令行交互环境如果已经有了数据库，则在cmd中命令为：sqlite3 dbfile没有的话就直接输入sqlite3回车。查看帮助sqlite所有的命令前
C语言/Sqlite3数据库
最新发布
qq_61802519的博客
08-06 1539
SQL struct query language 关系型数据库 非关系 芒果dbddl data defination language 建表dml 新增 修改 删除一行 data modiftydql 查询 data query language 查询 select数据库sun1、分类： 大型 中型 小型关系型数据库2、名词：DB 数据库 select update databaseDBMS 数据库管理系统。
如何在Windows下编译sqlite3，生成动态链接库并使用(New_070929)
Leon's Blog——Study Notes
12-28 6352
作者：zieckey（ zieckey@yahoo.com.cn ）All Rights Reserved!一. 编译动态链接库库文件下面的是我的编译过程，或许对你有些帮助：1). 打开VC新建一个“Win32 Dynamic-Link Library”工程，命名为:sqlite32). 在接下来的对话框中选择"An empty DLL project",点 FINISH->OK3).
SQLite3
qq_45698138的博客
08-17 1828
*.**DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别，主要的语句关键字包括grant、revoke等。**.**DDL(Data Definition Languages)语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象。数据库管理软件：为数据库而设计的电脑软件系统，一般具有存储、截取、安全保障备份等基本功能。......
sqlite3.exe
07-20
SQLite3.exe 是 SQLite 数据库引擎的一个命令行接口工具，它允许用户通过文本命令与 SQLite 数据库进行交互。SQLite 是一个开源、轻量级、自包含的 SQL 数据库引擎，广泛应用于嵌入式系统和移动应用中，因为它无需...
sqlite3文件（sqlite3.dll，sqlite3.h，sqlite3.lib）
07-07
标题提到的"sqlite3文件"主要包括三个关键组成部分：`sqlite3.dll`、`sqlite3.h`和`sqlite3.lib`，它们在开发和运行使用SQLite3的应用程序时起着至关重要的作用。 1. `sqlite3.dll`: 这是一个动态链接库文件，通常...
sqliteodbc驱动sqlite3
03-11
### SQLiteODBC 驱动 SQLite3：深入解析与应用指南 #### 一、SQLiteODBC简介 SQLiteODBC是一种连接SQLite数据库与支持ODBC（开放式数据库连接）的应用程序之间的桥梁。通过SQLiteODBC，开发人员可以利用ODBC标准...
sqlite3lib.rar
09-19
例如，`sqlite3_open()`函数用于打开或创建数据库，`sqlite3_exec()`用于执行SQL命令，而`sqlite3_prepare_v2()`和`sqlite3_step()`则用于处理更复杂的预编译语句。了解这些基本的API使用方法，可以让你在实际开发中...
Sqlite3.zip
06-18
这个东西是用来解决SVN的bug的，svn报"Error: Previous operation has not finished; run 'cleanup' if it was interrupted"这个错时解决
SQLite3数据库
别呀的博客
04-08 5114
目录数据库系统数据库系统结构图数据库的基本概念SQLite3数据库简介创建SQLite数据库附加数据库AttachSQLite3点命令SQLite数据库逻辑对象SQL92标准SQLite不支持的特性SQLite数据类型 数据库系统 数据库系统结构图 注意：现实社会中，经常将数据库系统简称为“数据库“ 数据库的基本概念 数据库是按照数据结构来组织、存储和管理数据的仓库。 数据库系统（DBS）=数据库（DB）+数据库管理系统（DBMS）+应用程序（App） 数据模型：概念模型 、关系模型 、网状模型、层次模型
嵌入式数据库之sqlite3
qq_43341187的博客
06-14 1803
sqlite> create table stu1 as select id, name, score from stu//创建一张新的表。―在程序运行过程中，当需要进行数据库操作时，应用程序会首先尝试打开数据库，此时如果数据库并不存在，程序则会自动建立数据库，然后再打开数据库。sqlite> alter table stu1 rename to stu//讲新的表名字改成原有旧表的名字。IBM 的DB2是第一个具备网上功能的多媒体关系数据库管理系统，支持包Linux在内的一系列平台。
Sqlite3 简易操作
一码算一码
03-04 207
终端通过sqlite语句 简单查看db数据库 先找到数据库所在文件夹 cd /xx/xx/xx 进入数据库(可以通过ls查看文件夹下目录) sqlite3 xx.db 查看数据库中的表 .tables 创建一个表结构 create table testTabel( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID ...
Linux下安装SQLite3
grace666的专栏
03-11 8510
1. 安装SQLite3 sudo apt-get install sqlite3   2. 安装Sqlite3编译需要的工具包 如果，你需要的话可以安装该工具包。只是为了体验一把，可以不安装。该项是可选项。 apt-get install libsqlite3-dev   3. 检查安装是否成功 执行下面命令，会出现sqlite版本号，如3.6.22
数据库——sqlite3
m0_73492050的博客
12-05 1592
SQLite是一个进程内的库，实现了自给自足、无服务器、零配置、事务性的SQL数据库引擎。无需安装和管理配置；储存在单一磁盘文件中的一个完整数据库；数据库可以在不同字节顺序的机器间共享；支持数据库大小之2TB;足够小，全部源码大约3万c代码，250KB；比目前流行的大多数数据库对数据的操作要快。(1)系统命令（点命令） 常用点命令(2)sql命令 数据定义语言（对整个表的操作，创建、修改、删除）：CREATE、ALTER、DROP 数据操作语言（对整个表中的记录的操作，创建、修改、删除）：数据查询语言： s
sqlite3学习
weixin_38966713的博客
05-18 447
sqlite3学习一、sqlite3数据库移植1、下载sqlite3源码2 解压，编译源码二、 sqllite3 接口使用1、打开/创建、关闭一个数据库2、操作数据库a)创建数据表b)插入接口c)查询接口 一、sqlite3数据库移植 1、下载sqlite3源码 sqlite3 源码可以从官方下载，下载地址https://www.sqlite.org/download.html 2 解压，编译源码 a)创建一个目录用来存放编译后的bin以及lib和头文件：mkdir sqlite3_arm b)解压tar
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值