SQLite详解，案例，手册

SQLite 存储类型

1.NULL

2.INTEGER

3.REAL

4.TEXT

5.BLOB

创建表

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

删除数据表

DROP TABLE COMPANY;

插入数据表

1.方式一

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

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'David', 27, 'Texas', 85000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );

2.方式二

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

查询数据表

1.查询全部

SELECT * FROM COMPANY;

2.查询部分

SELECT ID, NAME, SALARY FROM COMPANY;

SQLite 算术运算符

select 10 + 20;
select 10 - 20;
select 10 * 20;
select 10 / 5;
select 12 %  5;

SQLite 比较运算符

SELECT * FROM COMPANY WHERE SALARY > 50000;
SELECT * FROM COMPANY WHERE SALARY = 20000;
SELECT * FROM COMPANY WHERE SALARY != 20000;
SELECT * FROM COMPANY WHERE SALARY <> 20000;
SELECT * FROM COMPANY WHERE SALARY >= 65000;

SQLite 逻辑运算符

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

数值表达式

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

更新数据

UPDATE COMPANY SET ADDRESS = 'Texas' WHERE ID = 6;  -- 按条件修改
UPDATE COMPANY SET ADDRESS = 'Texas', SALARY = 20000.00; -- 修改全部

删除数据

DELETE FROM COMPANY WHERE ID = 7; -- 按条件删除
DELETE FROM COMPANY; -- 删除全部

LIKE查询

SELECT * FROM COMPANY WHERE AGE  LIKE '2%'; -- 查询2开头的年龄数据
SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%'; -- 查询文本里包含一个连字符（-）的所有记录
SELECT * FROM COMPANY WHERE AGE  LIKE '_5'; -- 查询第二位是5的年龄数据

GLOB查询

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

glob： 星号（*）代表零个、一个或多个数字或字符。问号（?）代表一个单一的数字或字符。

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

Limit 子句

SELECT * FROM COMPANY LIMIT 6; -- 获取6条记录
SELECT * FROM COMPANY LIMIT 3 OFFSET 2; -- 获取第二行之后的三条记录

Order By

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

Group By

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

Having

SELECT * FROM COMPANY GROUP BY name HAVING count(name) > 2; -- 对group by之后进行筛选

DISTINCT

SELECT DISTINCT name FROM COMPANY; -- 去除重复记录

SQLite 约束

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

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

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

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

5.CHECK 约束

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

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

Joins

• 交叉连接 - CROSS JOIN

• 内连接 - INNER JOIN

• 外连接 - OUTER JOIN

  准备表

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

​

​

SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT; -- 交叉连接
SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; -- 内连接
SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID; -- 外连接

NULL处理

UPDATE COMPANY SET ADDRESS = NULL, SALARY = NULL where ID IN(6,7);
SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NOT NULL;
SELECT  ID, NAME, AGE, ADDRESS, SALARY FROM COMPANY WHERE SALARY IS NULL;

别名

SELECT C.ID, C.NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

SELECT C.ID AS COMPANY_ID, C.NAME AS COMPANY_NAME, C.AGE, D.DEPT
        FROM COMPANY AS C, DEPARTMENT AS D
        WHERE  C.ID = D.EMP_ID;

触发器

准备表

CREATE TABLE AUDIT(
    EMP_ID INT NOT NULL,
    ENTRY_DATE TEXT NOT NULL
);

CREATE TRIGGER audit_log AFTER INSERT 
ON COMPANY
BEGIN
   INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));
END; -- 创建触发器，每增加一条数据，就插入一条数据到AUDIT中

查询已存在的触发器

SELECT name FROM sqlite_master
WHERE type = 'trigger';

SELECT name FROM sqlite_master
WHERE type = 'trigger' AND tbl_name = 'COMPANY';

DROP TRIGGER audit_log; -- 删除触发器

ALTER修改表

ALTER TABLE COMPANY RENAME TO OLD_COMPANY; -- 修改名字
ALTER TABLE OLD_COMPANY ADD COLUMN SEX char(1); -- 增加字段

视图

CREATE VIEW COMPANY_VIEW AS
SELECT ID, NAME, AGE
FROM  COMPANY; -- 创建视图

SELECT * from COMPANY_VIEW; -- 查询视图

DROP VIEW COMPANY_VIEW; -- 删除视图

事务

BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
ROLLBACK; -- 取消

BEGIN;
DELETE FROM COMPANY WHERE AGE = 25;
COMMIT;  -- 确认

子查询

SELECT * 
     FROM COMPANY 
     WHERE ID IN (SELECT ID 
                  FROM COMPANY 
                  WHERE SALARY > 45000) ; -- 查询
INSERT INTO COMPANY_BKP
     SELECT * FROM COMPANY 
     WHERE ID IN (SELECT ID 
                  FROM COMPANY) ;  -- 插入
 UPDATE COMPANY
     SET SALARY = SALARY * 0.50
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE >= 27 ); -- 更新

DELETE FROM COMPANY
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE > 27 ); -- 删除

自增

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

方法论:整理的好辛苦