SQLite(二)

数据库操作

• 创建

  sqlite3 DatabaseName.db

• 查看数据库列表

  .databases

• 导出完整的数据库到一个文本文件

  sqlite3 DatabaseName.db .dump > DatabaseName.sql
  
  恢复数据库
  sqlite3 DatabaseName.db < DatabaseName.sql

• SQLite 的 ATTACH DATABASE 语句是用来选择一个特定的数据库，使用该命令后，所有的 SQLite 语句将在附加的数据库下执行

  如果数据库尚未被创建，将创建一个数据库，如果数据库已存在，则把数据库文件名称与逻辑数据库 ‘Alias-Name’ 绑定在一起

  ATTACH DATABASE 'DatabaseName' As 'Alias-Name';

• DETACH DTABASE 分离数据库，上述附件数据库的反操作，DETACH 命令将只断开给定名称的连接，而其余的仍然有效，无法分离 main 或 temp 数据库

  DETACH DATABASE 'Alias-Name';

• 创建表 CREATE TABLE创建表 .tables 命令来验证表是否已成功创建 .schema命令得到表的完整信息

  
• 删除表 DROP TABLE database_name.table_name;

• INSERT 添加新的记录，新的数据

  INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]  
  VALUES (value1, value2, value3,...valueN);
  
  省略列名，保证顺序一致
  INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
  
  INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
  VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

• 将第二个表数据添加到第一个表

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

• SELECT 获取表中数据，返回结果集

  SELECT column1, column2, columnN FROM table_name;
  
  SELECT * FROM table_name;
  
  
  .header on
  .mode column
  .width 10, 20, 10

• Schema 信息

  列出所有在数据库中创建的表
  SELECT tbl_name FROM sqlite_master WHERE type = 'table';
  产生以下结果：
  
  tbl_name
  ----------
  COMPANY
  
  列出关于 COMPANY 表的完整信息
  SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
  假设在 testDB.db 中已经存在唯一的 COMPANY 表，产生以下结果：
  
  CREATE TABLE COMPANY(
     ID INT PRIMARY KEY     NOT NULL,
     NAME           TEXT    NOT NULL,
     AGE            INT     NOT NULL,
     ADDRESS        CHAR(50),
     SALARY         REAL
  )

运算符

• 算术运算符

  sqlite> select 10+20;
  30
  sqlite> .mode line
  sqlite> select 10-20;
  10-20 = -10
  sqlite> select 1/3;
    1/3 = 0
  sqlite> select 12%5;
   12%5 = 2

• 比较运算符

  

• 逻辑运算符

  

  WHERE AGE >= 25 AND/OR SALARY >= 65000;
  WHERE AGE IS NOT NULL
  WHERE NAME LIKE 'Ki%';
  WHERE NAME GLOB 'Ki*';
  WHERE AGE NOT IN ( 25, 27 );
  WHERE AGE BETWEEN 25 AND 27;
  
  SQL 子查询，子查询查找 SALARY > 65000 的带有 AGE 字段的所有记录，后边的 WHERE 子句与 EXISTS 运算符一起使用
   SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
  
  AGE
  ----------
  25
  23
  25
  
  SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 65000);

• 位运算符 A=60， B=13

  

表达式

• 布尔表达式

  SELECT * FROM COMPANY WHERE SALARY = 10000;

• 数值表达式

  SELECT COUNT(*) AS "RECORDS" FROM COMPANY; 
  RECORDS = 7

• 日期表达式

  select CURRENT_TIMESTAMP;
  CURRENT_TIMESTAMP
  -------------------
  2018-04-07 15:36:11

Where子句

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

AND/OR

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

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

Update

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

UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00;

Delete

DELETE FROM COMPANY WHERE ID = 7;
DELETE FROM COMPANY;

Like

通配符 百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符

AGE 以 2 开头的所有记录
SELECT * FROM COMPANY WHERE AGE  LIKE '2%';

ADDRESS 文本里包含一个连字符（-）的所有记录
SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';

Glob

星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符,大小写敏感

SELECT * FROM COMPANY WHERE AGE  GLOB '2*';

Limit

• 限制返回数据数量

  SELECT * FROM COMPANY LIMIT 6;

• 特定的偏移开始提取记录

  SELECT * FROM COMPANY LIMIT 3 OFFSET 2;
  
  ID          NAME        AGE         ADDRESS     SALARY
  ----------   ----------   ----------      ----------    ----------
  3           Teddy       23          Norway      20000.0
  4           Mark        25          Rich-Mond   65000.0
  5           David       27          Texas       85000.0

Order By

• 按 NAME 和 SALARY 升序降序排序

  SELECT * FROM COMPANY ORDER BY NAME, SALARY [ASC|DESC];

Group By

• 对相同的数据进行分组,在 SELECT 语句中，GROUP BY 子句放在 WHERE 子句之后，放在 ORDER BY 子句之前

  查找名称，薪资总和数据，以名称分组
  SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME;
  SELECT NAME, SUM(SALARY) FROM COMPANY GROUP BY NAME ORDER BY NAME;
  
  NAME        SUM(SALARY)
  ----------  -----------
  Allen       15000.0
  David       85000.0
  James       10000.0

Having

• 过滤分组数据

  SELECT column1, column2
  FROM table1, table2
  WHERE [ conditions ]
  GROUP BY column1, column2
  HAVING [ conditions ]
  ORDER BY column1, column2
  
  名称出现2次的数据
  SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2;

Distinct

• 获取重复记录中的唯一一次记录

  SELECT DISTINCT column1, column2,.....columnN 
  FROM table_name
  WHERE [condition]
  
  去掉重复名称
  SELECT DISTINCT name FROM COMPANY;