MySQL Syntax | Howard (Huaye) Zhan

CREATE TABLE W/ CODE

USE myfirstcodeschema;

CREATE TABLE myfirstcodetable (
    myfirstcodetable_id BIGINT NOT NULL,
    my_character_field VARCHAR(50),
    my_text_field TEXT,
    my_created_at TIMESTAMP
);

INSERTING NEW RECORDS

USE thriftshop;

SELECT * FROM inventory;

INSERT INTO inventory (inventory_id, item_name, number_in_stock) VALUES(10,'wolf skin hat', 1);

(Another) :

INSERT INTO inventory VALUES
(11,'fur fox skin',1),
(12,'plaid button up shirt',8),
(13,'flannel zebra jammies',6);

DELETE RECORDS

DELETE FROM inventory
WHERE inventory_id = 7

IMPORT DATA FROM A FILE

CREATE SCHEMA survey;

USE survey;

CREATE TABLE salary_survey (
    country VARCHAR(50),
    years_experience INT,
    employment_status VARCHAR(50),
    job_title VARCHAR(50),
    is_manager VARCHAR(50),
    education_level VARCHAR(50),
    );

ALTER TABLE W/ CODE

SELECT * FROM customer_purchases;

ALTER TABLE customer_purchases
DROP COLUMN 

ALTER TABLE customer_purchases
ADD COLUMN purchase_amount DECIMAL(6,2) AFTER customer_purchase_id;

SELECT * FROM customer_purchases;

UPDATING RECORDS

UPDATE inventory
SET number_in_stock = 0;
WHERE inventory_id = 9;

(Another) :

UPDATE inventory
SET number_in_stock = 0
WHERE inventory_id IN(1,9);

TRIGGERS HELPFUL HINTS:

CREATE TRIGGER trigger_name
<BEFORE|AFTER><INSERT|UPDATE|DELETE>
ON table_name FOR EACH ROW
<<FOLLOW|PRECEDES> existing_trigger_name>
<<some statement we want to execute by trigger>>
<WHERE column_name =<OLD|NEW>.column_name>

Enjoy Reading This Article?