SQLite3.0语法 语句

• 建表格式: create table if not exists 表名 (列名 类型,....) 注: 如需生成默认增加的id: id integer primary key auto increment

• 删除表

DROP TABLE IF EXISTS 't_student';

• 创建表

CREATE TABLE IF NOT EXISTS 't_student' (

        'id' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

        'name' TEXT,

        'age' INTEGER

);

• 插入数据

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

• 更新数据

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’;

• 基本查询

SELECT * FROM t_student;2.

• 查询特殊的字段

SELECT name, age FROM t_student;3.

• 通过条件来查询语句

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

• 通过条件查询

  • 模糊查询

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

• 多个条件的查询

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;

• 数据的排序

• 升序 ASC 默认情况

SELECT * FROM t_student ORDER BY age;

• 降序 DESC

SELECT * FROM t_student ORDER BY age DESC;

• 例如以年龄的升序排序
  如果年龄相同 以名字的降序排序

SELECT * FROM t_student ORDER BY age, name DESC;

• 起别名
• 给表起别名

SELECT [s.name](http://s.name "Page 2")
, s.age FROM t_student AS s;

• 给数据库字段起别名

SELECT name AS myName, age AS myAge FROM t_student;

• 查询

SELECT name, age FROM t_student LIMIT 4, 2;2>

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

SELECT name, age FROM t_student LIMIT 5;