07-30-db/notes.txt
// start MySQL. Will create an empty database on first start

$ mysql-ctl start
$ mysql-ctl stop
$ mysql-ctl cli

// once inside the mysql shell

SHOW DATABASES;
USE [dbname];
CREATE DATABASE [dbname];
DROP DATABASE [dbname];


CREATE TABLE Users (
    UID int NOT NULL AUTO_INCREMENT,
    USERNAME varchar(255),
    EMAIL varchar(255),
    PASSWORD varchar(255),
    PRIMARY KEY (UID) 
);

INSERT INTO Users
(USERNAME, EMAIL, PASSWORD)
VALUES
("bigErn", "bigern@gmail.com", "bigern19");

INSERT INTO Users
(USERNAME, EMAIL, PASSWORD)
VALUES
("weirdAl", "weirdAl@yahoo.com", "weirdAl13");

INSERT INTO Users
(USERNAME, EMAIL, PASSWORD)
VALUES
("crazySue", "crazySue@aol.com", "crazySue22");


CREATE TABLE Jokes (
    JID int NOT NULL AUTO_INCREMENT,
    SETUP varchar(255),
    PUNCHLINE varchar(255),
    SUBMITTEDBY int,
    PRIMARY KEY (JID),
    FOREIGN KEY (SUBMITTEDBY) REFERENCES Users(UID)
);


INSERT INTO Jokes
(SETUP, PUNCHLINE, SUBMITTEDBY)
VALUES
("I have a dog that does magic...", "He's a Labra-cadabra-dor", 1);

INSERT INTO Jokes
(SETUP, PUNCHLINE, SUBMITTEDBY)
VALUES
("I used to be addicted to soap...", "I'm clean now", 1);

INSERT INTO Jokes
(SETUP, PUNCHLINE, SUBMITTEDBY)
VALUES
("I used to be addicted to the Hokey Pokey...", "But then I turned myself around", 3);

INSERT INTO Jokes
(SETUP, PUNCHLINE, SUBMITTEDBY)
VALUES
("What did one skeleton say to the other...", "I got your back", 3);

INSERT INTO Jokes
(SETUP, PUNCHLINE, SUBMITTEDBY)
VALUES
("I have a dog that does magic...", "He's a Labra-cadabra-dor", 1),
("I used to be addicted to soap...", "I'm clean now", 1),
("I used to be addicted to the Hokey Pokey...", "But then I turned myself around", 3),
("What did one skeleton say to the other...", "I got your back", 3);


SELECT * FROM Users INNER JOIN Jokes ON Users.UID = Jokes.SUBMITTEDBY;

SELECT Users.USERNAME, Jokes.SETUP, Jokes.PUNCHLINE
FROM Users INNER JOIN Jokes ON Users.UID =  Jokes.SUBMITTEDBY
WHERE Users.USERNAME = "bigErn";








DESCRIBE [table_name];
DROP TABLE [table_name];

INSERT INTO persons
(personID, lastName, firstName, address, city)
VALUES
(1, "Brandl", "Rob", "665 Almostevil Ln", "Mesa");





SELECT * FROM [table_name];

UPDATE persons SET 
lastName = "Purple",
city = "Mesa"
WHERE firstName = "Harold";