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 COMPANYBEGIN INSERT INTO AUDIT(EMP_ID, ENTRY_DATE) VALUES (new.ID, datetime('now'));END; -- 创建触发器，每增加一条数据，就插入一条数据到AUDIT中 查询已存在的触发器
SELECT name FROM sqlite_masterWHERE type = 'trigger';SELECT name FROM sqlite_masterWHERE 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 ASSELECT ID, NAME, AGEFROM 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);
方法论:整理的好辛苦