Sqlite3 语句

常用 SQL

创建表

/*    创建数据表    CREATE TABLE '表名' (        '字段名' 类型(INTEGER, REAL, TEXT, BLOB)                NOT NULL    不允许为空                PRIMARY KEY 主键                AUTOINCREMENT 自增长,        '字段名2' 类型,        ...    )    注意：在开发中，如果是从 Navicat 粘贴的 SQL，需要自己添加一个指令    IF NOT EXISTS 加在表名前，如果数据表已经存在，就什么也不做*/CREATE TABLE IF NOT EXISTS "T_Person" (     "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,     "name" TEXT,     "age" INTEGER,     "heigth" REAL)/* 简单约束 */CREATE TABLE IF NOT EXISTS t_student(    id INTEGER PRIMARY KEY AUTOINCREMENT,    name TEXT,    age INTEGER);CREATE TABLE IF NOT EXISTS t_student(    id INTEGER PRIMARY KEY AUTOINCREMENT,    name TEXT UNIQUE,    age INTEGER);/* 添加主键 */CREATE TABLE IF NOT EXISTS t_student(    id INTEGER PRIMARY KEY AUTOINCREMENT,    name TEXT,    age INTEGER,    score REAL);/* 添加主键 */CREATE TABLE IF NOT EXISTS t_student(    id INTEGER,    name TEXT,    age INTEGER,    score REAL,    PRIMARY KEY(id));

插入

 INSERT INTO t_student    (age, score, name)    VALUES    ('28', 100, 'zhangsan'); INSERT INTO t_student    (name, age)    VALUES    ('lisi', '28');INSERT INTO t_student    (score)    VALUES    (100);

修改

UPDATE t_student    SET name = 'MM'    WHERE age = 10;UPDATE t_student    SET name = 'WW'    WHERE age is 7;UPDATE t_student    SET name = 'XXOO'    WHERE age < 20;UPDATE t_student    SET name = 'NNMM'    WHERE age < 50 and score > 10;/*更新记录的name*/UPDATE t_student SET name = 'zhangsan';

删除

DELETE FROM t_student;DELETE FROM t_student WHERE age < 50;

查询

/* 分页 */SELECT * FROM t_studentORDER BY id ASC LIMIT 30, 10;/* 排序 */SELECT * FROM t_studentWHERE score > 50ORDER BY age DESC;SELECT * FROM t_studentWHERE score < 50ORDER BY age ASC , score DESC;/* 计量 */SELECT COUNT(*)FROM t_studentWHERE age > 50;/* 别名 */SELECT name as myName, age as myAge, score as myScoreFROM t_student;SELECT name myName, age myAge, score myScoreFROM t_student;SELECT s.name myName, s.age myAge, s.score myScoreFROM t_student sWHERE s.age > 50;/* 查询 */SELECT name, age, score FROM t_student;SELECT * FROM t_student;

删除表

/*删除表*/DROP TABLE IF EXISTS t_student;