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
);方法论:整理的好辛苦
本文转自TBHacker博客园博客，原文链接：http://www.cnblogs.com/jiqing9006/p/6809076.html，如需转载请自行联系原作者
