数据库sqlite3学习

数据库sqlite3学习笔记

选择sqlite3的原因

数据库课程 CMU 15-445 的作业所使用的数据库系统。

学习来源

tutorialspoint学习网站

下载

各操作系统的下载教程及链接

sqlite3的基础命令

1. 在shell中使用 sqlite3 或 sqlite3 test.db 进入数据库

2. 在数据库系统中 .help 帮助

3. .databases

   列出所有的数据库文件

4. .exit 和 .quit

   均为退出数据库系统

5. .table

   列出该数据库系统中所有的表

6. .schema table_name

   table_name为表的名字，显示出该表的内部结构

7. .show

   显示出当前的配置设置信息

sqlite3对大小写不敏感

如题，select与SELECT等价。

数据类型

1. NULL

2. INTEGER

   整数，视数据大小储存在1, 2, 3, 4, 6, 8个比特位中

3. REAL

   8比特浮点数

4. TEXT

   即string

5. BLOB

   The value is a blob of data, stored exactly as it was input.

   翻译：该值是一个数据团，完全按照输入的方式存储。

sqlite3数据库语法

1. 创建，导出，导入

   可直接在shell中sqlite3 test.db即可创建新的数据库

   sqlite3 testDB.db .dump > testDB.sql即将 testDB.db 导出为 testDB.sql

   sqlite3 testDB.db < testDB.sql即使用 testDB.sql 创建 testDB.db

2. ATTACH

3. DETACH

4. CREATE table

   在数据库中创建一个新表格

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

   Example:

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

5. DROP table

   删除一个表格

   DROP TABLE database_name.table_name;

   Example:

   sqlite>DROP TABLE COMPANY;

6. INSERT

   在一个表格中插入一行数据

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

   INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

   Example:

   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 );

7. SELECT

   查数据

   SELECT column1, column2, columnN FROM table_name;

   SELECT * FROM table_name;

   这个更多是和WHERE进行搭配使用

8. 运算符

   • 算术运算符

     +, -, *, /, \%$

     加，减，乘，除，取模

   • 比较运算符

     ==, =, !=, <>, >, <, >=, <=, !<, !>

     均为字面意思，其中 $<>$ 和 $!=$ 等价

   • 逻辑运算符

     AND, BETWEEN, EXISTS, IN, NOT IN, LIKE, GLOB, NOT, OR, IS NULL, IS, IS NOT, ||, UNIQUE

     其中||用于连接两个字符串

   • 位运算符

     &, |, ~, <<, >>

     如字面意思

9. WHERE

   条件语句，用于限定条件，可用于SELECT, UPDATE, DELETE等语句中。

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

   Example:

   SELECT * FROM COMPANY WHERE NAME LIKE 'Ki%';

10. AND & OR

    用于连接条件语句

    SELECT column1, column2, columnN 
    FROM table_name
    WHERE [condition1] AND [condition2]...AND [conditionN];

    OR与其类似

11. UPDATE

    修改数据

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

    Example:

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

12. DELETE

    删除数据

    DELETE FROM table_name
    WHERE [condition];

    Example:

    sqlite> DELETE FROM COMPANY WHERE ID = 7;

13. 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_'

    Example:

    sqlite> SELECT * FROM COMPANY WHERE AGE LIKE '2%';
    sqlite> SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

14. GLOB

    用于字符串或数字匹配，区分大小写

    *表示任何数量的字符或数字

    ?表示一个字符或数字

    用法与 LIKE 一致。

15. LIMIT

    限制输出的行数及偏移量

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

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

    Example:

    sqlite> SELECT * FROM COMPANY LIMIT 6;

    sqlite> SELECT * FROM COMPANY LIMIT 3 OFFSET 2;

16. ORDER BY

    用于给输出的数据排序

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

    ASC为从上到下递增

    DESC为从上到下递减

    默认为ASC

    Example:

    sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;

    sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC;

17. GROUP BY

    给输出的数据分组

    位于WHERE语句的末尾，ORDER BY语句之前

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

    Example:

    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

    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

18. HAVING

    HAVING是针对于GROUP BY进行条件过滤的

    需要位于GROUP BY的后面，位于ORDER BY的前面。

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

    Example:

    sqlite > SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;
    
    ID          NAME        AGE         ADDRESS     SALARY
    ----------  ----------  ----------  ----------  ----------
    10          James       45          Texas       5000

19. DISTINCT

    去重，将输出的结果进行去重

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