SQL 常用語法

SQLite - 線上版

SQL四大主要功能

1. Create
2. Read
3. Update
4. Destroy

01. Create Table

• Create Table
• Data Types
• Primary Key

CREATE TABLE Products (
    id INT NOT NULL,
    name STRING,
    price MONEY,
    PRIMARY KEY (id)
)

02. Create Data

• Insert into

have all values

INSERT INTO products
VALUES (1, "pen", 1.20)

miss some values

INSERT INTO products (id, name)
VALUES (2, "pencil")

03. Read Table

• Select
• SELECT DISTINCT
• Select Where

select everything

SELECT * FROM 'products';

select particular column

SELECT name, price FROM Products

select particular row

SELECT * 
FROM Products
WHERE id = 1

05. Update Data

• Update

UPDATE products
SET price = 1.0
WHERE id = 2

06. Update by Alter Column

• alter column

ALTER TABLE Product
ADD stock INT;

07. Delete Data

• Delete

DELETE FROM Product
WHERE id = 2

08. Relationship built with other table

• Foreign Key

CREATE TABLE orders (
    id int NOT NULL,
    order_number Int,
    customer_id INT,
    product_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (customer_id) REFERENCES customers (id),
    FOREIGN KEy (product_id) REFERENCES Product (id)
)

09. Relationship showed by table

• join inner

SELECT orders.order_number, customers.first_name, customers.last_name, customers.address
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;