devbox
菜鸟追梦旅行
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

SQLite 基础语法学习_sqlite学习

作者：菜鸟追梦旅行 | 2024-05-13 07:14:55

赞

踩

sqlite学习

SQLite 是一个进程内的库，实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。它是一个零配置的数据库，这意味着与其他数据库不一样，您不需要在系统中配置。

就像其他数据库，SQLite 引擎不是一个独立的进程，可以按应用程序需求进行静态或动态连接。SQLite 直接访问其存储文件。据说 SQLite 的语法和 MySQL 的大部分是一样的，而且从 Lite 的后缀可以看出，这应该像是一个 Mini 版本的 MySQL。虽说如此，我们还是来看看它和 MySQL 有哪些区别吧。

进入数据库命令行

  1. [root@master ~]# sqlite3
  2. SQLite version 3.27.2 2019-02-25 16:06:06
  3. Enter ".help" for usage hints.
  4. Connected to a transient in-memory database.
  5. Use ".open FILENAME" to reopen on a persistent database.
  6. sqlite>

语句语法

所有的 SQLite 语句可以以任何关键字开始，如 SELECT、INSERT、UPDATE、DELETE、ALTER、DROP 等，所有的语句以分号 ; 结束。

数据库备份和恢复

  1. 备份数据库：sqlite3 test.db ".dump" > test.sql
  2. 恢复数据库：sqlite3 test.db < test.sql
  3.  
  4. 备份数据库：sqlite3 test.db ".dump" | sqlite3 test_bak.db
  5. 恢复数据库：sqlite3 test_bak.db ".dump" | sqlite3 test.db

将当前数据库备份成数据库文件 

sqlite> .backup test.db

 从数据库文件进行恢复

sqlite> .restore test.db

压缩数据库

SQLite 采用变长记录存储，当你从 Sqlite 删除数据后，未使用的磁盘空间被添加到一个内在的 “空闲列表” 中用于存储你下次插入的数据，用于提高效率，磁盘空间并没有丢失，但也不向操作系统返回磁盘空间，这就导致删除数据乃至清空数据库后，数据库文件大小还是没有任何变化，可以使用 vacuum 命令对数据库文件重新进行空间压缩整理。

sqlite> vacuum;

创建数据库

  1. [root@master ~]# sqlite3
  2. SQLite version 3.27.2 2019-02-25 16:06:06
  3. Enter ".help" for usage hints.
  4. Connected to a transient in-memory database.
  5. Use ".open FILENAME" to reopen on a persistent database.
  6. sqlite> .databases
  7. main: 
  8. sqlite> .open test.db
  9. sqlite> .databases
  10. main: /root/test.db
  11. sqlite> .quit

附加数据库

有点类似于 MySQL 的 use database; 命令

  1. [root@master SQLite]# sqlite3
  2. SQLite version 3.27.2 2019-02-25 16:06:06
  3. Enter ".help" for usage hints.
  4. Connected to a transient in-memory database.
  5. Use ".open FILENAME" to reopen on a persistent database.
  6. sqlite> .open test.db
  7. sqlite> .open testDB.db
  8. sqlite> .database
  9. main: /root/SQLite/testDB.db
  10. sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';
  11. sqlite> .database
  12. main: /root/SQLite/testDB.db
  13. TEST: /root/SQLite/testDB.db

分离数据库

  1. sqlite> .database
  2. main: /root/SQLite/testDB.db
  3. TEST: /root/SQLite/testDB.db
  4. sqlite> detach database TEST;
  5. sqlite> .database
  6. main: /root/SQLite/testDB.db

创建数据表

  1. CREATE TABLE COMPANY(
  2.    ID INT PRIMARY KEY     NOT NULL,
  3.    NAME           TEXT    NOT NULL,
  4.    AGE            INT     NOT NULL,
  5.    ADDRESS        CHAR(50),
  6.    SALARY         REAL    
  7. );
  8. CREATE TABLE DEPARTMENT(
  9.    ID INT PRIMARY KEY      NOT NULL,
  10.    DEPT           CHAR(50) NOT NULL,
  11.    EMP_ID         INT      NOT NULL
  12. );
  13. ------------------------------------------------------------
  14.  
  15. sqlite> CREATE TABLE COMPANY(
  16.    ...>    ID INT PRIMARY KEY     NOT NULL,
  17.    ...>    NAME           TEXT    NOT NULL,
  18.    ...>    AGE            INT     NOT NULL,
  19.    ...>    ADDRESS        CHAR(50),
  20.    ...>    SALARY         REAL
  21.    ...> );
  22. sqlite> CREATE TABLE DEPARTMENT(
  23.    ...>    ID INT PRIMARY KEY      NOT NULL,
  24.    ...>    DEPT           CHAR(50) NOT NULL,
  25.    ...>    EMP_ID         INT      NOT NULL
  26.    ...> );
  27. sqlite> .tables
  28. COMPANY     DEPARTMENT
  29. sqlite> .schema company
  30. CREATE TABLE COMPANY(
  31.    ID INT PRIMARY KEY     NOT NULL,
  32.    NAME           TEXT    NOT NULL,
  33.    AGE            INT     NOT NULL,
  34.    ADDRESS        CHAR(50),
  35.    SALARY         REAL
  36. );

删除数据表

  1. sqlite> .tables
  2. COMPANY     DEPARTMENT
  3. sqlite> drop table company;
  4. sqlite> .tables
  5. DEPARTMENT

插入数据

  1. sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  2.    ...> VALUES (1, 'Paul', 32, 'California', 20000.00 );
  3.  
  4. sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  5.    ...> VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
  6.  
  7. sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  8.    ...> VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
  9.  
  10. sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  11.    ...> VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
  12.  
  13. sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  14.    ...> VALUES (5, 'David', 27, 'Texas', 85000.00 );
  15.  
  16. sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  17.    ...> VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
  18.  
  19. sqlite> INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

查询数据表

  1. sqlite> 
  2. sqlite> .header on
  3. sqlite> .mode column
  4. sqlite> select * from company;
  5. ID          NAME        AGE         ADDRESS     SALARY    
  6. ----------  ----------  ----------  ----------  ----------
  7. 1           Paul        32          California  20000.0   
  8. 2           Allen       25          Texas       15000.0   
  9. 3           Teddy       23          Norway      20000.0   
  10. 4           Mark        25          Rich-Mond   65000.0   
  11. 5           David       27          Texas       85000.0   
  12. 6           Kim         22          South-Hall  45000.0   
  13. 7           James       24          Houston     10000.0 
  14.  
  15. sqlite> select id, name, salary from company;
  16. ID          NAME        SALARY    
  17. ----------  ----------  ----------
  18. 1           Paul        20000.0   
  19. 2           Allen       15000.0   
  20. 3           Teddy       20000.0   
  21. 4           Mark        65000.0   
  22. 5           David       85000.0   
  23. 6           Kim         45000.0   
  24. 7           James       10000.0 
  25.  
  26. # 可自己设置列宽
  27. sqlite> .width 10, 20, 10
  28. sqlite> select id, name, salary from company;
  29. ID          NAME                  SALARY    
  30. ----------  --------------------  ----------
  31. 1           Paul                  20000.0   
  32. 2           Allen                 15000.0   
  33. 3           Teddy                 20000.0   
  34. 4           Mark                  65000.0   
  35. 5           David                 85000.0   
  36. 6           Kim                   45000.0   
  37. 7           James                 10000.0  
  38.  
  39. sqlite> .width 10, 20, 10
  40. sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
  41. sql       
  42. ----------
  43. CREATE TAB
  44. sqlite> .width on
  45. sqlite> SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
  46. sql                                                                                                                                                                                       
  47. ------------------------------------------------------------------------------------                                                                   
  48. CREATE TABLE COMPANY(
  49.    ID INT PRIMARY KEY     NOT NULL,
  50.    NAME           TEXT    NOT NULL,
  51.    AGE            INT     NOT NULL,
  52.    ADDRESS        CHAR(50),
  53.    SALARY         REAL    
  54. )

运算符

SQLite 运算符 | 菜鸟教程

不用 .mode line 的时候，好像和 MySQL 的输出差不多，不过好像行模式确实很不错。

  1. sqlite> select 10 + 20;
  2. 10 + 20   
  3. ----------
  4. 30        
  5. sqlite> .mode line
  6. sqlite> select 10 + 20;
  7. 10 + 20 = 30
  8.  
  9. sqlite> select * from company;
  10.      ID = 1
  11.    NAME = Paul
  12.     AGE = 32
  13. ADDRESS = California
  14.  SALARY = 20000.0
  15.  
  16.      ID = 2
  17.    NAME = Allen
  18.     AGE = 25
  19. ADDRESS = Texas
  20.  SALARY = 15000.0
  21.  
  22. ...
  1. sqlite> .mode column
  2. sqlite> select * from company;
  3. ID          NAME                  AGE         ADDRESS     SALARY    
  4. ----------  --------------------  ----------  ----------  ----------
  5. 1           Paul                  32          California  20000.0   
  6. 2           Allen                 25          Texas       15000.0   
  7. 3           Teddy                 23          Norway      20000.0   
  8. 4           Mark                  25          Rich-Mond   65000.0   
  9. 5           David                 27          Texas       85000.0   
  10. 6           Kim                   22          South-Hall  45000.0   
  11. 7           James                 24          Houston     10000.0   
  12. sqlite> select * from company where salary=20000;
  13. ID          NAME                  AGE         ADDRESS     SALARY    
  14. ----------  --------------------  ----------  ----------  ----------
  15. 1           Paul                  32          California  20000.0   
  16. 3           Teddy                 23          Norway      20000.0   
  17. sqlite> select * from company where salary>50000;
  18. ID          NAME                  AGE         ADDRESS     SALARY    
  19. ----------  --------------------  ----------  ----------  ----------
  20. 4           Mark                  25          Rich-Mond   65000.0   
  21. 5           David                 27          Texas       85000.0

更新数据表

  1. sqlite> select * from company;
  2. ID          NAME        AGE         ADDRESS     SALARY    
  3. ----------  ----------  ----------  ----------  ----------
  4. 1           Paul        32          California  20000.0   
  5. 2           Allen       25          Texas       15000.0   
  6. 3           Teddy       23          Norway      20000.0   
  7. 4           Mark        25          Rich-Mond   65000.0   
  8. 5           David       27          Texas       85000.0   
  9. 6           Kim         22          South-Hall  45000.0   
  10. 7           James       24          Houston     10000.0   
  11. sqlite> UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;
  12. sqlite> select * from company;
  13. ID          NAME        AGE         ADDRESS     SALARY    
  14. ----------  ----------  ----------  ----------  ----------
  15. 1           Paul        32          California  20000.0   
  16. 2           Allen       25          Texas       15000.0   
  17. 3           Teddy       23          Norway      20000.0   
  18. 4           Mark        25          Rich-Mond   65000.0   
  19. 5           David       27          Texas       85000.0   
  20. 6           Kim         22          Texas       45000.0   
  21. 7           James       24          Houston     10000.0

删除数据

  1. sqlite> select * from company;
  2. ID          NAME        AGE         ADDRESS     SALARY    
  3. ----------  ----------  ----------  ----------  ----------
  4. 1           Paul        32          California  20000.0   
  5. 2           Allen       25          Texas       15000.0   
  6. 3           Teddy       23          Norway      20000.0   
  7. 4           Mark        25          Rich-Mond   65000.0   
  8. 5           David       27          Texas       85000.0   
  9. 6           Kim         22          Texas       45000.0   
  10. 7           James       24          Houston     10000.0   
  11. sqlite> delete from company where id=7;
  12. sqlite> select * from company;
  13. ID          NAME        AGE         ADDRESS     SALARY    
  14. ----------  ----------  ----------  ----------  ----------
  15. 1           Paul        32          California  20000.0   
  16. 2           Allen       25          Texas       15000.0   
  17. 3           Teddy       23          Norway      20000.0   
  18. 4           Mark        25          Rich-Mond   65000.0   
  19. 5           David       27          Texas       85000.0   
  20. 6           Kim         22          Texas       45000.0

like 子句

这个典型的和 MySQL 的 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 结尾的任意值

glob 子句

我一般见到的 glob 竟然是在 ruby 的语法里边（不过话说回来，用法其实还真是差不多）：

  1. 返回一个数组，包含与指定的通配符模式 pat 匹配的文件名：
  2.  
  3. * - 匹配包含 null 字符串的任意字符串
  4. ** - 递归地匹配任意字符串
  5. ? - 匹配任意单个字符
  6. [...] - 匹配封闭字符中的任意一个
  7. {a,b...} - 匹配字符串中的任意一个
  8. Dir["foo.*"] # 匹配 "foo.c"、 "foo.rb" 等等
  9. Dir["foo.?"] # 匹配 "foo.c"、 "foo.h" 等等
语句描述
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 结尾的任意值

limit 子句

  1. sqlite> select * from company;
  2. ID          NAME        AGE         ADDRESS     SALARY    
  3. ----------  ----------  ----------  ----------  ----------
  4. 1           Paul        32          California  20000.0   
  5. 2           Allen       25          Texas       15000.0   
  6. 3           Teddy       23          Norway      20000.0   
  7. 4           Mark        25          Rich-Mond   65000.0   
  8. 5           David       27          Texas       85000.0   
  9. 6           Kim         22          Texas       45000.0   
  10.  
  11. sqlite> select * from company limit 3;
  12. ID          NAME        AGE         ADDRESS     SALARY    
  13. ----------  ----------  ----------  ----------  ----------
  14. 1           Paul        32          California  20000.0   
  15. 2           Allen       25          Texas       15000.0   
  16. 3           Teddy       23          Norway      20000.0   
  17.  
  18. sqlite> select * from company limit 3 offset 2;
  19. ID          NAME        AGE         ADDRESS     SALARY    
  20. ----------  ----------  ----------  ----------  ----------
  21. 3           Teddy       23          Norway      20000.0   
  22. 4           Mark        25          Rich-Mond   65000.0   
  23. 5           David       27          Texas       85000.0

order by

  1. sqlite> select * from company;
  2. ID          NAME        AGE         ADDRESS     SALARY    
  3. ----------  ----------  ----------  ----------  ----------
  4. 1           Paul        32          California  20000.0   
  5. 2           Allen       25          Texas       15000.0   
  6. 3           Teddy       23          Norway      20000.0   
  7. 4           Mark        25          Rich-Mond   65000.0   
  8. 5           David       27          Texas       85000.0   
  9. 6           Kim         22          Texas       45000.0   
  10. sqlite> select * from company order by salary;
  11. ID          NAME        AGE         ADDRESS     SALARY    
  12. ----------  ----------  ----------  ----------  ----------
  13. 2           Allen       25          Texas       15000.0   
  14. 1           Paul        32          California  20000.0   
  15. 3           Teddy       23          Norway      20000.0   
  16. 6           Kim         22          Texas       45000.0   
  17. 4           Mark        25          Rich-Mond   65000.0   
  18. 5           David       27          Texas       85000.0

group by

  1. INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00 );
  2. INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00 );
  3. INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00 );
  4.  
  5. sqlite> select * from company;
  6. ID          NAME        AGE         ADDRESS     SALARY    
  7. ----------  ----------  ----------  ----------  ----------
  8. 1           Paul        32          California  20000.0   
  9. 2           Allen       25          Texas       15000.0   
  10. 3           Teddy       23          Norway      20000.0   
  11. 4           Mark        25          Rich-Mond   65000.0   
  12. 5           David       27          Texas       85000.0   
  13. 6           Kim         22          Texas       45000.0   
  14. 8           Paul        24          Houston     20000.0   
  15. 9           James       44          Norway      5000.0    
  16. 10          James       45          Texas       5000.0    
  17. sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
  18. NAME        SUM(SALARY)
  19. ----------  -----------
  20. Allen       15000.0    
  21. David       85000.0    
  22. James       10000.0    
  23. Kim         45000.0    
  24. Mark        65000.0    
  25. Paul        40000.0    
  26. Teddy       20000.0

having 子句

在一个查询中，HAVING 子句必须放在 GROUP BY 子句之后，必须放在 ORDER BY 子句之前。

  1. sqlite> select * from company;
  2. ID          NAME        AGE         ADDRESS     SALARY    
  3. ----------  ----------  ----------  ----------  ----------
  4. 1           Paul        32          California  20000.0   
  5. 2           Allen       25          Texas       15000.0   
  6. 3           Teddy       23          Norway      20000.0   
  7. 4           Mark        25          Rich-Mond   65000.0   
  8. 5           David       27          Texas       85000.0   
  9. 6           Kim         22          Texas       45000.0   
  10. 8           Paul        24          Houston     20000.0   
  11. 9           James       44          Norway      5000.0    
  12. 10          James       45          Texas       5000.0  
  13. sqlite> SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
  14. NAME        SUM(SALARY)
  15. ----------  -----------
  16. Allen       15000.0    
  17. David       85000.0    
  18. James       10000.0    
  19. Kim         45000.0    
  20. Mark        65000.0    
  21. Paul        40000.0    
  22. Teddy       20000.0    
  23. sqlite> SELECT NAME, SUM(SALARY) as TOTAL FROM COMPANY GROUP BY NAME having TOTAL > 40000;
  24. NAME        TOTAL     
  25. ----------  ----------
  26. David       85000.0   
  27. Kim         45000.0   
  28. Mark        65000.0   
  29. sqlite> SELECT NAME, SUM(SALARY) as TOTAL FROM COMPANY GROUP BY NAME HAVING TOTAL > 40000 ORDER BY TOTAL ASC;
  30. NAME        TOTAL     
  31. ----------  ----------
  32. Kim         45000.0   
  33. Mark        65000.0   
  34. David       85000.0

distinct

  1. sqlite> select name from company;
  2. NAME      
  3. ----------
  4. Paul      
  5. Allen     
  6. Teddy     
  7. Mark      
  8. David     
  9. Kim       
  10. Paul      
  11. James     
  12. James     
  13. sqlite> select distinct name from company;
  14. NAME      
  15. ----------
  16. Paul      
  17. Allen     
  18. Teddy     
  19. Mark      
  20. David     
  21. Kim       
  22. James

字段约束

约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整个表。

以下是在 SQLite 中常用的约束。

  • NOT NULL 约束：确保某列不能有 NULL 值。

  • DEFAULT 约束：当某列没有指定值时，为该列提供默认值。

  • UNIQUE 约束：确保某列中的所有值是不同的。

  • PRIMARY Key 约束：唯一标识数据库表中的各行/记录。

  • CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

NOT NULL 约束

默认情况下，列可以保存 NULL 值。如果您不想某列有 NULL 值，那么需要在该列上定义此约束，指定在该列上不允许 NULL 值。NULL 与没有数据是不一样的，它代表着未知的数据。

  1. CREATE TABLE COMPANY(
  2.    ID INT PRIMARY KEY     NOT NULL,
  3.    NAME           TEXT    NOT NULL,
  4.    AGE            INT     NOT NULL,
  5.    ADDRESS        CHAR(50),
  6.    SALARY         REAL
  7. );

DEFAULT 约束 

DEFAULT 约束在 INSERT INTO 语句没有提供一个特定的值时，为列提供一个默认值。

  1. CREATE TABLE COMPANY(
  2.    ID INT PRIMARY KEY     NOT NULL,
  3.    NAME           TEXT    NOT NULL,
  4.    AGE            INT     NOT NULL,
  5.    ADDRESS        CHAR(50),
  6.    SALARY         REAL    DEFAULT 50000.00
  7. );

UNIQUE 约束

UNIQUE 约束防止在一个特定的列存在两个记录具有相同的值。在 COMPANY 表中，例如，您可能要防止两个或两个以上的人具有相同的年龄。

  1. CREATE TABLE COMPANY(
  2.    ID INT PRIMARY KEY     NOT NULL,
  3.    NAME           TEXT    NOT NULL,
  4.    AGE            INT     NOT NULL UNIQUE,
  5.    ADDRESS        CHAR(50),
  6.    SALARY         REAL    DEFAULT 50000.00
  7. );

PRIMARY KEY 约束

  1. CREATE TABLE COMPANY(
  2.    ID INT PRIMARY KEY     NOT NULL,
  3.    NAME           TEXT    NOT NULL,
  4.    AGE            INT     NOT NULL,
  5.    ADDRESS        CHAR(50),
  6.    SALARY         REAL
  7. );

CHECK 约束

CHECK 约束启用输入一条记录要检查值的条件。如果条件值为 false，则记录违反了约束，且不能输入到表。

  1. CREATE TABLE COMPANY3(
  2.    ID INT PRIMARY KEY     NOT NULL,
  3.    NAME           TEXT    NOT NULL,
  4.    AGE            INT     NOT NULL,
  5.    ADDRESS        CHAR(50),
  6.    SALARY         REAL    CHECK(SALARY > 0)
  7. );

删除约束

SQLite 支持 ALTER TABLE 的有限子集。在 SQLite 中，ALTER TABLE 命令允许用户重命名表，或向现有表添加一个新的列。重命名列，删除一列，或从一个表中添加或删除约束都是不可能的。

触发器

  1. sqlite> CREATE TABLE AUDIT(
  2.    ...>     EMP_ID INT NOT NULL,
  3.    ...>     ENTRY_DATE TEXT NOT NULL
  4.    ...> );
  5. sqlite> CREATE TRIGGER audit_log AFTER INSERT 
  6.    ...> ON COMPANY
  7.    ...> BEGIN
  8.    ...>    INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
  9.    ...> END;
  10.  
  11. sqlite> select * from audit;
  12. sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  13.    ...> VALUES (11, 'Paul', 32, 'California', 20000.00 );
  14. sqlite> select * from audit;
  15. 11          2021-01-22 06:35:27
  16. sqlite> SELECT name FROM sqlite_master
  17.    ...> WHERE type = 'trigger' AND tbl_name = 'COMPANY';
  18. audit_log
  19. sqlite> DROP TRIGGER audit_log;

索引

创建索引

CREATE INDEX index_name ON table_name;

单列索引

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

CREATE INDEX index_name ON table_name (column_name);

唯一索引

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

CREATE UNIQUE INDEX index_name ON table_name (column_name);

组合索引

组合索引是基于一个表的两个或多个列上创建的索引。

CREATE INDEX index_name ON table_name (column1, column2);

隐式索引

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

删除索引

一个索引可以使用 SQLite 的 DROP 命令删除。当删除索引时应特别注意，因为性能可能会下降或提高。

DROP INDEX index_name;

什么情况下要避免使用索引？

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

  • 索引不应该使用在较小的表上。

  • 索引不应该使用在有频繁的大批量的更新或插入操作的表上。

  • 索引不应该使用在含有大量的 NULL 值的列上。

  • 索引不应该使用在频繁操作的列上。

视图

创建视图

视图（View）只不过是通过相关的名称存储在数据库中的一个 SQLite 语句。所以当原始表发生变化时，视图的结果也会根据相应的结果发生变化。

  1. sqlite> select * from company;
  2. ID          NAME        AGE         ADDRESS     SALARY    
  3. ----------  ----------  ----------  ----------  ----------
  4. 1           Paul        32          California  20000.0   
  5. 2           Allen       25          Texas       15000.0   
  6. 3           Teddy       23          Norway      20000.0   
  7. 4           Mark        25          Rich-Mond   65000.0   
  8. 5           David       27          Texas       85000.0   
  9. 6           Kim         22          Texas       45000.0   
  10. 8           Paul        24          Houston     20000.0   
  11. 9           James       44          Norway      5000.0    
  12. 10          James       45          Texas       5000.0    
  13. 11          Paul        32          California  20000.0   
  14. sqlite> create view company_view as select id, name, age from company;
  15. sqlite> select * from company_view;
  16. ID          NAME        AGE       
  17. ----------  ----------  ----------
  18. 1           Paul        32        
  19. 2           Allen       25        
  20. 3           Teddy       23        
  21. 4           Mark        25        
  22. 5           David       27        
  23. 6           Kim         22        
  24. 8           Paul        24        
  25. 9           James       44        
  26. 10          James       45        
  27. 11          Paul        32        
  28. sqlite> delete from company where id=11;
  29. sqlite> select * from company_view;
  30. ID          NAME        AGE       
  31. ----------  ----------  ----------
  32. 1           Paul        32        
  33. 2           Allen       25        
  34. 3           Teddy       23        
  35. 4           Mark        25        
  36. 5           David       27        
  37. 6           Kim         22        
  38. 8           Paul        24        
  39. 9           James       44        
  40. 10          James       45

删除视图

要删除视图，只需使用带有 view_name 的 DROP VIEW 语句（和删除数据表的语法类似）。

sqlite> DROP VIEW view_name;

子查询

子查询或称为内部查询、嵌套查询，指的是在 SQLite 查询中的 WHERE 子句中嵌入查询语句。一个 SELECT 语句的查询结果能够作为另一个语句的输入值。

子查询可以与 SELECT、INSERT、UPDATE 和 DELETE 语句一起使用，可伴随着使用运算符如 =、<、>、>=、<=、IN、BETWEEN 等。

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

  • 子查询必须用括号括起来。

  • 子查询在 SELECT 子句中只能有一个列，除非在主查询中有多列，与子查询的所选列进行比较。

  • ORDER BY 不能用在子查询中，虽然主查询可以使用 ORDER BY。可以在子查询中使用 GROUP BY，功能与 ORDER BY 相同。

  • 子查询返回多于一行，只能与多值运算符一起使用，如 IN 运算符。

  • BETWEEN 运算符不能与子查询一起使用，但是，BETWEEN 可在子查询内使用。

  1. sqlite> select * from company;
  2. ID          NAME        AGE         ADDRESS     SALARY    
  3. ----------  ----------  ----------  ----------  ----------
  4. 1           Paul        32          California  20000.0   
  5. 2           Allen       25          Texas       15000.0   
  6. 3           Teddy       23          Norway      20000.0   
  7. 4           Mark        25          Rich-Mond   65000.0   
  8. 5           David       27          Texas       85000.0   
  9. 6           Kim         22          Texas       45000.0   
  10. 8           Paul        24          Houston     20000.0   
  11. 9           James       44          Norway      5000.0    
  12. 10          James       45          Texas       5000.0    
  13.  
  14. sqlite> select * from company where id in (select id from company where salary > 45000);
  15. ID          NAME        AGE         ADDRESS     SALARY    
  16. ----------  ----------  ----------  ----------  ----------
  17. 4           Mark        25          Rich-Mond   65000.0   
  18. 5           David       27          Texas       85000.0   
  19.  
  20. sqlite> select * from company where salary > 45000;
  21. ID          NAME        AGE         ADDRESS     SALARY    
  22. ----------  ----------  ----------  ----------  ----------
  23. 4           Mark        25          Rich-Mond   65000.0   
  24. 5           David       27          Texas       85000.0 
  1. sqlite> CREATE TABLE COMPANY_BKP(
  2.    ...>    ID INT PRIMARY KEY     NOT NULL,
  3.    ...>    NAME           TEXT    NOT NULL,
  4.    ...>    AGE            INT     NOT NULL,
  5.    ...>    ADDRESS        CHAR(50),
  6.    ...>    SALARY         REAL    
  7.    ...> );
  8. sqlite> select * from company;
  9. ID          NAME        AGE         ADDRESS     SALARY    
  10. ----------  ----------  ----------  ----------  ----------
  11. 1           Paul        32          California  20000.0   
  12. 2           Allen       25          Texas       15000.0   
  13. 3           Teddy       23          Norway      20000.0   
  14. 4           Mark        25          Rich-Mond   65000.0   
  15. 5           David       27          Texas       85000.0   
  16. 6           Kim         22          Texas       45000.0   
  17. 8           Paul        24          Houston     20000.0   
  18. 9           James       44          Norway      5000.0    
  19. 10          James       45          Texas       5000.0    
  20. sqlite> INSERT INTO COMPANY_BKP SELECT * FROM COMPANY WHERE ID IN (SELECT ID FROM COMPANY) ;
  21. sqlite> UPDATE COMPANY SET SALARY = SALARY * 0.50 WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE >= 27 );
  22. sqlite> select * from company;
  23. ID          NAME        AGE         ADDRESS     SALARY    
  24. ----------  ----------  ----------  ----------  ----------
  25. 1           Paul        32          California  10000.0   
  26. 2           Allen       25          Texas       15000.0   
  27. 3           Teddy       23          Norway      20000.0   
  28. 4           Mark        25          Rich-Mond   65000.0   
  29. 5           David       27          Texas       42500.0   
  30. 6           Kim         22          Texas       45000.0   
  31. 8           Paul        24          Houston     20000.0   
  32. 9           James       44          Norway      2500.0    
  33. 10          James       45          Texas       2500.0 
  1. sqlite> DELETE FROM COMPANY WHERE AGE IN (SELECT AGE FROM COMPANY_BKP WHERE AGE > 27 );
  2. sqlite> select * from company;
  3. ID          NAME        AGE         ADDRESS     SALARY    
  4. ----------  ----------  ----------  ----------  ----------
  5. 2           Allen       25          Texas       15000.0   
  6. 3           Teddy       23          Norway      20000.0   
  7. 4           Mark        25          Rich-Mond   65000.0   
  8. 5           David       27          Texas       42500.0   
  9. 6           Kim         22          Texas       45000.0   
  10. 8           Paul        24          Houston     20000.0

聚合函数

序号函数 & 描述
1COUNT 函数
COUNT 聚集函数是用来计算一个数据库表中的行数。
2MAX 函数
MAX 聚合函数允许我们选择某列的最大值。
3MIN 函数
MIN 聚合函数允许我们选择某列的最小值。
4AVG 函数
AVG 聚合函数计算某列的平均值。
5SUM 函数
SUM 聚合函数允许为一个数值列计算总和。
6RANDOM 函数
RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。
7ABS 函数
ABS 函数返回数值参数的绝对值。
8UPPER 函数
UPPER 函数把字符串转换为大写字母。
9LOWER 函数
LOWER 函数把字符串转换为小写字母。
10LENGTH 函数
LENGTH 函数返回字符串的长度。
11sqlite_version 函数
sqlite_version 函数返回 SQLite 库的版本。
声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：【wpsshop博客】
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号