筆記 課程中SQL command 轉SQLite

在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');