[SQL]: CRUD commands

Most common operations in database (or others)

• Create (CREATE, INTERT INTO)
• Read (SELECT)
• Update (UPDATE)
• Delete (DROP, DELETE)

READ

SELECT * FROM dogs;

• Retrieve all(*) colummns from dogs table

SELECT name FROM dogs;

• Shows name column

SELECT name, age FROM dogs;

• Display a multiple columns in order typed

SELECT * FROM dogs WHERE age=5;

• Get all columns where age=5

SELECT * FROM dogs where name="Buzz";

• Get all columns where name="Buzz"
• Again, case insensitive

SELECT age FROM dogs where name="Fizz";

• Show age column where name="Fizz"

SELECT * FROM dogs where dogId=age;

• Display all columns where id=age;

Aliases (AS)

We can set up how data is displayed

SELECT dogId AS id, name FROM dogs;

• Shows dogID column as id
• Doesn't change the real data
• Useful for Join tables (upcoming)
• AS can be skipped and used as

SELECT dogID id, name FROM dogs;

Update

UPDATE dogs SET age=14
WHERE name="Loxi";

• Change Loxi's age to 14

Changing multiple attributes

UPDATE dogs SET age=5, name="Lucky"
WHERE name="Loxi";

• Change Loxi's name to Lucky and set age to 5

Changing multiple rows

UPDATE dogs SET age=13
WHERE name="BUZZ" OR name="Lucky";

• Set Buzz and Lucky's age to 13

UPDATE dogs SET age=111;

• Set all dogs' age to 111

Important

• Try SELECTing before UPDATE. It's a good practice to avoid updating data that you don't want
• Same for deletion(next content)

Delete

Usage for deletion is similar to SLECT

DELETE FROM dogs WHERE name="Buzz";

• Delete data where name="Buzz"
• Try SELECTing first!
• dogId for other rows won't alter
  • After deletion and another insertion the primary key will NOT make up for the removed row

DELETE FROM dogs;

• Delete all dogs or all rows
• Table still exists! When desired, don't forget to drop it with

DROP TABLE dogs;