MySQL

The Basics of CRUD

Create

Read

Update

Delete

Create

This Should Be Review

INSERT INTO

INSERT INTO cats(name, age)
VALUES('Taco', 14);

Create

Read

Update

Delete

Let's start with a clean slate...

DROP TABLE cats;

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

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);

Read

How do we retrieve and search data?

SELECT

SELECT * FROM cats;

Remember This?

SELECT * FROM cats;

"Give Me All Columns"

SELECT Expression

What columns do you want?

SELECT name FROM cats;

SELECT age FROM cats;

SELECT name, age FROM cats;

The WHERE clause

Let's Get Specifical

We'll use WHERE all the time

Not just to SELECT...

SELECT * FROM cats WHERE age=4;

SELECT * FROM cats WHERE name='Egg';

Rapid Fire Exercises

+--------+
| cat_id |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
+--------+

Write the SQL that selects the following:

+----------------+------------+
| name           | breed      |
+----------------+------------+
| Ringo          | Tabby      |
| Cindy          | Maine Coon |
| Dumbledore     | Maine Coon |
| Egg            | Persian    |
| Misty          | Tabby      |
| George Michael | Ragdoll    |
| Jackson        | Sphynx     |
+----------------+------------+

Write the SQL that selects the following:

+-------+------+
| name  | age  |
+-------+------+
| Ringo |    4 |
| Misty |   13 |
+-------+------+

Write the SQL that selects the following:

(Just the Tabby cats)

+--------+------+
| cat_id | age  |
+--------+------+
|      4 |    4 |
|      7 |    7 |
+--------+------+

Write the SQL that selects the following:

cat_id is same as age

Aliases

Easier to read results

SELECT cat_id AS id, name FROM cats;

+----+----------------+
| id | name           |
+----+----------------+
|  1 | Ringo          |
|  2 | Cindy          |
|  3 | Dumbledore     |
|  4 | Egg            |
|  5 | Misty          |
|  6 | George Michael |
|  7 | Jackson        |
+----+----------------+

Update

How do we alter existing data?

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

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

A Good Rule of 👍

Thumb

Try SELECTing before you UPDATE

Your Turn!

Relatively Painless UPDATE exercises

Change Jackson's name to "Jack"

Change Ringo's breed to "British Shorthair"

Update both Maine Coons' ages to be 12

Delete

Time to learn to...delete things

DELETE FROM cats WHERE name='Egg';

DELETE FROM cats;

Always run SELECT first to double check...

Your Turn!

Get Some Practice With DELETE

DELETE all 4 year old cats

DELETE cats whose age is the same as their cat_id

DELETE all cats 🙀

Super

Ultra

Hyper

Mega

CRUD

Exercise

Spring Cleaning

The Annual Closet Inventory

Create a new database

shirts_db

Create a new table

shirts

shirt_id	article	color	shirt_size	last_worn
1	t-shirt	white	S	10
2	t-shirt	green	S	200
3	polo shirt	black	M	10
4	tank top	blue	S	50
5	t-shirt	pink	S	0
6	polo shirt	red	M	5
7	tank top	white	S	200
8	tank top	blue	M	15

Cannot Be NULL

Primary Key

('t-shirt', 'white', 'S', 10),
('t-shirt', 'green', 'S', 200),
('polo shirt', 'black', 'M', 10),
('tank top', 'blue', 'S', 50),
('t-shirt', 'pink', 'S', 0),
('polo shirt', 'red', 'M', 5),
('tank top', 'white', 'S', 200),
('tank top', 'blue', 'M', 15)

Get All That Data In There

With a single line

Add A New Shirt

Purple polo shirt, size M last worn 50 days ago

SELECT all shirts

But Only Print Out Article and Color

SELECT all medium shirts

Print Out Everything But shirt_id

Update all polo shirts

Change their size to L

Update the shirt last worn 15 days ago

Change last_worn to 0

Update all white shirts

Change size to 'XS' and color to 'off white'

Delete all old shirts

Last worn 200 days ago

Delete all tank tops

Your tastes have changed...

Delete all shirts

Catastrophe!

Drop the entire shirts table

Catastrophe Again!

CREATE TABLE shirts
  (
    shirt_id INT NOT NULL AUTO_INCREMENT,
    article VARCHAR(100),
    color VARCHAR(100),
    shirt_size VARCHAR(100),
    last_worn INT,
    PRIMARY KEY(shirt_id)
  );