在SQLite中是AUTOINCREMENT not AUTO_INCREMENT
並且需要宣告成INTEGER not INT
/* PRIMARY KEY 加入 AUTO_INCREMENT 可以在新增時自動累加 id 數值,就不用手動輸入 */
CREATE table users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(30) NOT NULL,
email VARCHAR(120) NOT NULL,
age INT NOT NULL,
created_at DATETIME
);
/* INSERT INTO 語法加入指定要插入的屬性欄位,就可以明確指出要插入的欄位,這樣就可以忽略自動產生或有預設值的欄位 */
INSERT INTO users (name, email, age, created_at)
VALUES
('Jack Hung', 'jackh32@gmail.com', 20, '2019-07-24 17:11:01'),
('Tony Liu', 'tonykk@gmail.com', 62, '2020-06-03 17:11:01'),
('Amy Chang', 'amychang@gmail.com', 32, '2020-05-11 17:11:01');
CREATE table products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(30) NOT NULL,
price INT NOT NULL,
weight DECIMAL(5, 2),
category VARCHAR(20),
created_at DATETIME
);
/*
INSERT INTO 資料表 (欄位) VALUES` 後面可以透過 , 分隔多筆資料,就可以一次新增多筆資料
*/
INSERT INTO products (name, price, weight, category, created_at)
VALUES
('華速 intel i3 筆電', 20000, 2.12, 'NB', '2020-05-11 17:11:01'),
('Mac Pro 筆電', 62000, 1.4, 'NB', '2020-05-11 17:11:01'),
('微興電競筆電', 32000, 3.00, 'NB', '2020-05-11 17:11:01'),
('戈林冰箱', 22000, 13.78, '3C', '2020-05-11 17:11:01'),
('三力冰箱', 52000, 23.18, '3C', '2020-05-11 17:11:01'),
('C 語言入門', 420, 0.31, 'Book', '2020-05-11 17:11:01'),
('python3 實戰', 580, 0.28, 'Book', '2020-05-11 17:11:01'),
('JavaScript 英雄', 1000, 0.12, 'Book', '2020-05-11 17:11:01'),
('Java 資料分析', 340, -1, 'Book', '2020-05-11 17:11:01'),
('python 資料分析', 640, -0.43, 'Book', '2020-05-11 17:11:01');
CREATE table orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
amount INT NOT NULL,
-- 先宣告欄位,再設定外鍵 FOREIGN KEY REFERENCES
customer_id INT,
created_at DATETIME,
FOREIGN KEY (customer_id) REFERENCES users(id)
);
INSERT INTO orders (amount, customer_id, created_at)
VALUES
(188420, 1, '2020-05-11 17:11:01'),
(54000, 3, '2019-04-13 17:11:01'),
(104420, 2, '2020-06-21 17:11:01'),
(52420, 2, '2020-05-01 17:11:01'),
(104000, 2, '2020-07-11 17:11:01'),
(32420, 2, '2019-03-24 17:11:01');
CREATE table order_details (
id INTEGER PRIMARY KEY AUTOINCREMENT,
-- 先宣告欄位,再設定外鍵 FOREIGN KEY REFERENCES
order_id INT,
product_id INT,
created_at DATETIME,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
INSERT INTO order_details (order_id, product_id, created_at)
VALUES
(1, 1, '2020-05-11 17:11:01'),
(1, 2, '2020-05-11 17:11:01'),
(1, 3, '2020-05-11 17:11:01'),
(1, 4, '2020-05-11 17:11:01'),
(1, 5, '2020-05-11 17:11:01'),
(1, 6, '2020-05-11 17:11:01'),
(2, 3, '2020-04-13 17:11:01'),
(2, 4, '2020-04-13 17:11:01'),
(3, 5, '2020-06-21 17:11:01'),
(3, 6, '2020-06-21 17:11:01'),
(3, 5, '2020-06-21 17:11:01'),
(4, 5, '2020-05-01 17:11:01'),
(4, 6, '2020-05-01 17:11:01'),
(5, 5, '2020-07-11 17:11:01'),
(5, 5, '2020-07-11 17:11:01'),
(6, 6, '2020-03-24 17:11:01'),
(6, 3, '2020-03-24 17:11:01');