常用数据类型
NULL - 空值
INTERGER - 有符号整数类型
REAL - 浮点数类型
TEXT - 字符串(其编码取决于DB的编码)
BLOB - 二进制表示(binary large object),二进制大对象
常用操作
数据库的常用操作,基本都是四个:增、删、改、查
增
-- 新增表 CREATE TABLE IF NOT EXISTS "user" ( "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "name" TEXT, "age" INTEGER, "icon" TEXT ); -- 插入记录 INSERT INTO user (name, age, icon) VALUES ("Tom", 23, "Tom_icon"); INSERT INTO user (name, age, icon) VALUES ("Jimi", 24, "Jimi_icon"); INSERT INTO user (name, age, icon) VALUES ("Jack", 25, "Jack_icon"); INSERT INTO user (name, age, icon) VALUES ("Mike", 26, "Mike_icon"); -- 新建表,从已有表导入记录 CREATE TABLE IF NOT EXISTS new_user( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT ) INSERT INTO new_user(name) SELECT name FROM user;
删
-- 新建一个临时表 CREATE TABLE IF NOT EXISTS "user_name" ("name" TEXT); -- 删除表 DROP TABLE IF EXISTS user_name; -- 删除记录 DELETE FROM user WHERE id = 1;
改
-- 修改表 ALTER TABLE user RENAME TO new_user; ALTER TABLE new_user RENAME TO user; -- 表结尾增加一列 ALTER TABLE "user" ADD COLUMN note TEXT; -- 修改记录 UPDATE user SET name = "杰克" WHERE name = "Jack"; UPDATE user SET name = "汤姆" WHERE name = "Tom"; UPDATE user SET icon = "iconxxx" WHERE age >= 25;
查
-- 查询 * 表示所有 SELECT * FROM user; -- 查询部分字段 SELECT name, age FROM user; -- 条件约束 SELECT name FROM user WHERE age >= 25; -- 模糊查询 使用like关键词 %零个或多个 SELECT name, icon FROM user WHERE icon LIKE "%icon%"; -- 模糊查询 "_" 一个字符 SELECT name FROM user WHERE name LIKE "汤_"; -- 查询数量 SELECT COUNT(*) FROM user; SELECT COUNT(name) FROM user; -- 排序 默认:ASC (ascend) 升序 SELECT * FROM user ORDER BY age; -- 降序 DESC (desend) SELECT * FROM user ORDER BY age DESC; -- 分页查询 LIMIT, 查询5条 SELECT * FROM user LIMIT 5; -- 前面跳过3条,查询5条 SELECT * FROM user LIMIT 3, 5; -- 字段别名 SELECT name AS n, age AS a FROM user; -- 表别名 SELECT t.name, t.age FROM user AS t;
参考:
《SQLite数据库常用操作》
http://blog.csdn.net/qxuewei/article/details/52900455
《SQL *和%有什么区别》