CRUD Commands

CRUD is an acronym for the different types of interactions you have with a database:

• Create

• Read

• Update

• Delete

You've already seen create and a bit of read:

-- Create
INSERT INTO <tablename>(col1, col2) VALUES('value1', 2);

-- Read (BUT there's way more to it)
SELECT * from <tablename>;

This section will focus on reading via SELECT, updating via UPDATE, and deleting via DELETE.

SELECT (Read)

Here's the basic syntax for selecting all data with restrictions on what columns you want back:

SELECT * from cats; -- ALL columns
SELECT name from cats; -- one column
SELECT name, age from cats; -- multiple columns

Adding conditions using WHERE

When reading a database, you're almost never reading every row. You usually are trying to pull out particular pieces. That's where the WHERE clause comes in.

Here's the basic syntax for WHERE:

SELECT * FROM cats WHERE age=4;
SELECT * FROM cats WHERE name='eGg'; -- <= case insensitive

Note: The WHERE clause is useful for UPDATE and DELETE too, not just SELECT. (You usually want to update or delete specific things, not your entire database/table.)

Aliases

When displaying a table's columns, I can specify an alias that changes how it appears (without changing the database).

Syntax:

SELECT id AS cat_id, name AS 'cat name' FROM cats;
-- Notice that you can provide a string as alias

Pro tip: Aliases are useful when you create JOIN tables. For example, if you have a dogs table and cats table, they may both have the column name. During your JOIN, you can create aliases to make it clear which is which, i.e., dog_name and cat_name.

UPDATE

Sometimes you need to UPDATE values in a database. For example, maybe a user forgot their password and wants to change it. Here's the syntax for that:

-- This command updates breed to 'Shorthair' for every cat with the breed 'Tabby'
UPDATE cats SET breed='Shorthair' WHERE breed='Tabby';

Pro tip: Run the SELECT equivalent of your UPDATE command first. Once your SELECT command returns the data you expect, then update it. Example:

SELECT * FROM cats WHERE name='no name provided';

-- Check if the data returned is what you want to update, then...

UPDATE cats SET name='Cat Doe' WHERE name='no name provided';

DELETE

The basic syntax for the DELETE command is this:

SELECT * FROM cats WHERE name='Ringo';

-- Once your `SELECT` returns what you want, then...

DELETE FROM cats WHERE name='Ringo';

Running SQL Files

Bonus!

You can run a series of SQL commands by running source filename.sql inside of mysql-ctl cli (provided you're in the same directory as filename.sql).

This is a quick way of executing a bunch of commands at once after having written it in a nice text editor.