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

1. CREATE

CREATE

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

INSERT INTO

INSERT INTO cats(name, breed, age) 
VALUES ('Ringo', 'Tabby', 4),
       ('Cindy', 'Maine Coon', 10),
       ('Dumbledore', 'Maine Coon', 11),
       ('Egg', 'Persian', 4),
       ('Misty', 'Tabby', 13),
       ('George Michael', 'Ragdoll', 9),
       ('Jackson', 'Sphynx', 7);

2. READ

(1) SELECT

Give me all columns.

SELECT * FROM cats;

Give me the columns which is named "name".

SELECT name FROM cats;

Give me the columns which is named "name" and "age".

SELECT name,age 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.

INSERT INTO...VALUE <=> UPDATE...SET

UPDATE cats SET breed='Shorthair' 

여러개를 한번에 업데이트

UPDATE cats SET breed='Shorthair',age=2

특정 항목을 지정하여 업데이트

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;