在iOS开发中常见的SQLite语句

如题,记录一些iOS开发中常见的SQLite语句.以备查阅.

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

CREATE TABLE IF NOT EXISTS "T_Person" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" TEXT,
"age" INTEGER,
"heigth" REAL
)
//下边是sqllite编译器里边的语句

/*简单约束*/
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)
);

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

/*分页*/
SELECT * FROM t_student
ORDER BY id ASC LIMIT 30, 10;

/*排序*/
SELECT * FROM t_student
WHERE score > 50
ORDER BY age DESC;

SELECT * FROM t_student
WHERE score < 50
ORDER BY age ASC , score DESC;

/*计量*/
SELECT COUNT(*)
FROM t_student
WHERE age > 50;

/*别名*/
SELECT name as myName, age as myAge, score as myScore
FROM t_student;

SELECT name myName, age myAge, score myScore
FROM t_student;

SELECT s.name myName, s.age myAge, s.score myScore
FROM t_student s
WHERE s.age > 50;

/*查询*/
SELECT name, age, score FROM t_student;
SELECT * FROM t_student;

修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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';

插入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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);

删除表

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