[MySQL] CRUD commands : Create / Read / Update / Delete

1. CREATE

INSERT INTO

CREATE TABLE cats 
  ( 
     cat_id INT NOT NULL AUTO_INCREMENT, 
     name   VARCHAR(100), 
     breed  VARCHAR(100), 
     age    INT, 
     PRIMARY KEY (cat_id) 
  ); 

2. READ

(1) SELECT

Give me all columns.

SELECT * FROM cats;

Give me the columns which is named "name."

SELECT name FROM cats;

(2) WHERE

Give me all columns with age 4.

SELECT * FROM cats WHERE age=4;

(3) Aliases

Easier to read results
(usually used to simplify the name of datatypes)

SELECT cat_id AS id, name FROM cats;

3. UPDATE

change/revise data.

UPDATE cats SET breed='Shorthair' 
WHERE breed='Tabby';

UPDATE cats SET age=14 
WHERE name='Misty';

4. DELETE

Delete everything in the table.
(not delete the table itself)

DELETE FROM table_name

Delete specific data in the table.

DELETE FROM table_name WHERE datatype = sth;