--==============================SQLite数据库语法====================.start================
-- 创建表(类似Mysql)
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,-- 主键,自动递增
    name TEXT NOT NULL,
    age INTEGER,
    annual_salary float(18, 2) NOT NULL,
    create_time INTEGER NOT NULL,-- 时间类型直接使用数字或者字符串存储
    update_time INTEGER NOT NULL
);
-- 查询并创建表
create  table users_log as select * from users;

-- 表删除
DROP TABLE IF EXISTS users;

-- 新增时间
INSERT INTO users (name, age, annual_salary, create_time, update_time) VALUES ('张三', 30, 5600.45, 1737198128677, 1737198128677);
INSERT INTO users (name, age, annual_salary, create_time, update_time) VALUES ('Joni', 42, 7600.45, 1837398128677, 1837398128677);
INSERT INTO users (name, age, annual_salary, create_time, update_time) VALUES ('李四', 35, 4600.45, 1937798128677, 1937898128677);

-- 查询数据
SELECT * FROM users;
SELECT name, age FROM users WHERE age > 25 ORDER BY age ASC LIMIT 10;

-- 更新数据
UPDATE users SET age = 31,update_time=1957798128677 WHERE name = 'Joni';

-- 删除数据
DELETE FROM users WHERE name = 'Joni';

-- 列-增加
ALTER TABLE users ADD COLUMN is_deleted INTEGER;

-- 创建索引
CREATE INDEX idx0_users ON users (name);


-- 视图-创建
CREATE VIEW vw_users AS
SELECT id, name, age FROM users WHERE is_deleted = 2;

-- 触发器-创建
CREATE TRIGGER trig_users_before_update
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_log (id, name, age, annual_salary, create_time, update_time) VALUES (OLD.id, OLD.name,OLD.age,OLD.annual_salary,OLD.create_time,OLD.update_time);
END;

--==============================SQLite数据库语法====================.end==================



---================================com.wmx.sqliteapp3.sqlite.PhoneController====对应的脚本=====./start====
DROP TABLE IF EXISTS iphone;
CREATE TABLE iphone (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price float(18,4) NOT NULL,
  publish_time INTEGER DEFAULT NULL
);
INSERT INTO iphone (name ,price, publish_time) VALUES ('华为P30', '2488', '1737198128677');
INSERT INTO iphone (name ,price, publish_time) VALUES ('小米11', '4388', '1737198128677');
INSERT INTO iphone (name ,price, publish_time) VALUES ('小米23', '4388', '1737198128677');
INSERT INTO iphone (name ,price, publish_time) VALUES ('小米243', '4388', '1737198128677');
INSERT INTO iphone (name ,price, publish_time) VALUES ('小米78', '4388', '1737198128677');
INSERT INTO iphone (name ,price, publish_time) VALUES ('小米788', '4388', '1737198128677');
---================================com.wmx.sqliteapp3.sqlite.PhoneController====对应的脚本=====./end====