数据库之--- SQLite 语句

一. 基础创表操作:

• 1. 创建表

CREATE TABLE IF NOT EXISTS t_dog(name text, age bolb, weight real);

• 2. 插入记录

INSERT INTO t_shop (name, left_count) VALUES ('扇子', 100);

• 3.修改记录

UPDATE t_shop SET price = 6288, left_count = 0;

• 4. 删除表中数据

DELETE FROM t_shop;

• 5. 删除表

DROP TABLE t_dog;

二. 条件语句

• 更改记录

UPDATE t_shop SET left_count = 0 WHERE price < 1000;

• 删除记录

DELETE FROM t_shop WHERE left_count < 70 AND price < 500;

DELETE FROM t_shop WHERE left_count < 70 OR price < 500;

• 查询数据

SELECT name, price, left_count FROM t_shop;

SELECT * FROM t_shop WHERE left_count > 80;

1. 别名: SELECT name shop_name, price AS shop_price, left_count 库存 FROM t_shop s;
2. count: SELECT count(*) 剩余数量 FROM t_shop WHERE left_count > 50;
3. 降序查询:SELECT *FROM t_shop ORDER BY left_count DESC;
4. 默认ASC:SELECT *FROM t_shop ORDER BY left_count DESC, price ASC;
5. limit:SELECT *FROM t_shop LIMIT 2, 4;
   SELECT *FROM t_shop ORDER BY price DESC LIMIT 1, 10; ---- 取价格最高的第2页, 10条记录;
   
6. 约束: CREATE TABLE IF NOT EXISTS t_student (name text NOTNULL, age integer);
7. CREATE TABLE IF NOT EXISTS t_student (name text NOT NULL UNIQUE, age integer NOT NULL);
8. CREATE TABLE IF NOT EXISTS t_student (name text NOT NULL UNIQUE, age integer DEFAULT 1 NOT NULL);
9. 主键约束: primary key

CREATE TABLE IF NOT EXISTS t_class (id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL UNIQUE);
INSERT INTO t_class(name) VALUES ('ios');
INSERT INTO t_class(name) VALUES ('andriod');