SQL语句汇总(方便粘贴sqlite语句)

DDL语句

删除表

DROP TABLE IF EXISTS 't_student';

创建表

CREATE TABLE IF NOT EXISTS 't_student' ( 'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 'name' TEXT, 'age' INTEGER);

DML语句

插入语句

INSERT INTO t_student (name, age) VALUES ('why', 18);

INSERT INTO t_student (name, age) VALUES ('lmj', 25);

INSERT INTO t_student (name, age) VALUES ('lnj', 22);

INSERT INTO t_student (name, age) VALUES ('yz', 17);

INSERT INTO t_student (name, age) VALUES ('wsz', 28);

更新数据

UPDATE t_student SET age = 30 WHERE name = 'why';

UPDATE t_student SET name = 'zs' WHERE age > 20;

删除数据

DELETE FROM t_student WHERE name = 'zs’;

DQL语句

基本查询

SELECT * FROM t_student;

查询特殊的字段

SELECT name, age FROM t_student;

通过条件来查询语句

SELECT name, age FROM t_student WHERE age <= 25;

SELECT name, age FROM t_student WHERE name LIKE '%l%';

多个条件的查询

SELECT name, age FROM t_student WHERE name LIKE '%l%' AND age >=25;

SELECT name, age FROM t_student WHERE name LIKE '%l%' OR age >=25;

计算个数

SELECT count(*) FROM t_student;

SELECT count(name) FROM t_student;

SELECT count(age) FROM t_student;

数据的排序

1> 升序 ASC(默认)

SELECT * FROM t_student ORDER BY age;

2> 降序

DESCSELECT * FROM t_student ORDER BY age DESC;

3> 以年龄的升序排序,如果年龄相同,以名字的降序排序

SELECT * FROM t_student ORDER BY age, name DESC;

起别名

1> 给表起别名

SELECT s.name, s.age FROM t_student AS s;

2> 给字段起别名

SELECT name AS myName, age AS myAge FROM t_student;

分页查询(limit)

1> 基本分

SELECT name, age FROM t_student LIMIT 4, 2;

2> 该语句的意思是:跳过0条数据,查询前五条数据

SELECT name, age FROM t_student LIMIT 5;

更多自动化测试相关内容，尽在TestBird。

原文链接：http://www.apkbus.com/blog-881091-62858.html