Creating Databases

Finally!!!!

Database Server

Dog Walker

Database

Soap Shop

Database

Practice

Database

News Site

Database

Dog Walker Database

Soap Shop Database

Dogs

Soaps

Users

Users

Payments

Payments

show databases;
CREATE DATABASE <name>;
CREATE DATABASE soap_store;
CREATE DATABASE DogApp;
CREATE DATABASE My App;
DROP DATABASE <name>;
DROP DATABASE soap_store;

I Can Create Databases...

Now What?

Time To Use Them! 

USE <database name>;
SELECT database();

Tables!

The True Heart of SQL

A database is just a bunch of tables

In a relational database, at least

Tables Hold The Data!

"a collection of related data held in a structured format within a database"

Boring Wikipedia Definition

Quick Example Using Cats

The Cat's Table!

Name Breed Age
Blue Scottish Fold 1
Rocket Persian 3
Monty Tabby 10
Sam Munchkin 5

The Cats Table!

Name Breed Age

Columns (headers)

Name Breed Age
Blue Scottish Fold 1
Rocket Persian 3
Monty Tabby 10
Sam Munchkin 5

Rows (the actual data)

Databases are made up of lots of tables.

CLICK THIS LINK FOR PROOF!

Sometimes it gets crazy.

Data Types

Tables Pt. 2

Name Breed Age
Blue Scottish Fold 1
Rocket Persian 3
Monty Tabby ten
Sam Munchkin I am yung cat

The Importance of Data Types

Name Breed Age
Blue Scottish Fold 1
Rocket Persian 3
Monty Tabby ten
Sam Munchkin I am yung cat

Calculate "cat age"

Age * 7

 1 * 7 = 7

 3 * 7 = 21

 'ten' * 7 = ???

 'I am yung cat' * 7 = 

😡

Really
Not

Good

(AKA Bad)

Name Breed Age
Blue Scottish Fold 1
Rocket Persian 3
Monty Tabby ten
Sam Munchkin I am yung cat

Must Be

Text

Must Be

Text

Must Be

Number

In reality, there are A LOT of different MySQL data types

  • INT
  • SMALLINT
  • TINYINT
  • MEDIUMINT
  • BIGINT
  • DECIMAL
  • NUMERIC
  • FLOAT
  • DOUBLE
  • BIT
  • CHAR
  • VARCHAR
  • BINARY
  • VARBINARY
  • BLOB
  • TINYBLOB
  • MEDIUMBLOB
  • LONGBLOB
  • TEXT
  • TINYTEXT
  • MEDIUMTEXT
  • LONGTEXT
  • ENUM
  • DATE
  • DATETIME
  • TIMESTAMP
  • TIME
  • YEAR

Numeric Types

String Types

Date Types

It's Crazy.

😳

  • INT
  • SMALLINT
  • TINYINT
  • MEDIUMINT
  • BIGINT
  • DECIMAL
  • NUMERIC
  • FLOAT
  • DOUBLE
  • BIT
  • CHAR
  • VARCHAR
  • BINARY
  • VARBINARY
  • BLOB
  • TINYBLOB
  • MEDIUMBLOB
  • LONGBLOB
  • TEXT
  • TINYTEXT
  • MEDIUMTEXT
  • LONGTEXT
  • ENUM

Numeric Types

String Types

INT

A Whole Number 

with a max value of 4294967295

12

0

-9999

3145677

42

varchar

A Variable-Length String

Between 1 and 255 characters

'coffee!!'

'aAbbb akljsd'

'-9999'

'L'

'The quick brown fox jumps over the lazy brown dog'

Name Breed Age
Blue Scottish Fold 1
Rocket Persian 3
Monty Tabby ten
Sam Munchkin I am yung cat

Must Be

Text

Must Be

Text

Must Be

Number

varchar(100)

varchar(100)

int

Super Short Activity

Draw a Tweets Table

  • A username (max 15 chars)
  • The tweet content (max 140 chars)
  • Number of favorites

At a minimum the columns must include:

Make sure to specify correct MySQL datatypes!

username content favorites
'coolguy' 'my first tweet!' 1
guitar_queen 'I love music :) ' 10
'lonely_heart' 'still looking 4 love' 0

Username (max 15 chars)

Favorites

varchar(15)

varchar(140)

int

Content (max 140 chars)

Creating Tables

Finally!

CREATE TABLE tablename 
  ( 
     column_name data_type, 
     column_name data_type
  ); 
CREATE TABLE cats 
  ( 
     name VARCHAR(100),
     age  INT 
  );

How Do You Know It Worked?

SHOW TABLES;
SHOW COLUMNS FROM <tablename>;
DESC <tablename>;

Or...

DROP TABLE <tablename>;

Deleting Tables

Time For Another Activity!

Creating Your Own Table!

Create a pastries table

  • It should include 2 columns: name and quantity.  Name is 50 characters max.
  • Inspect your table/columns in the CLI
  • Delete your table!

INSERT

Adding Data to Your Tables

INSERT INTO cats(name, age) 
VALUES ('Jetson', 7);

INSERT

INSERT INTO cats(name, age) VALUES ("Jetson", 7);
INSERT INTO cats 
            (NAME, 
             age) 
VALUES      ("jetson", 
             7); 

INSERT INTO cats(name, age) 
VALUES ("Jetson", 7);
INSERT INTO cats(age, name) 
VALUES (12, 'Victoria');

THE ORDER MATTERS

Let's Try It In Cloud9

So... How Do We Know It Worked?

I come from the future, with a new SQL command you won't learn until the next section.

SELECT * FROM cats;
INSERT INTO cats(name, age) 
VALUES ('Charlie', 10)
      ,('Sadie', 3)
      ,('Lazy Bear', 1);

MULTIPLE INSERT

Time For You To Try!

Create a people table

  • first_name - 20 char limit
  • last_name - 20 char limit
  • age

Insert Your 1st Person!

first_name last_name age
'Tina' 'Belcher' 13

Insert Your 2nd Person!

first_name last_name age
'Bob' 'Belcher' 42

Multiple Insert Time!

first_name last_name age
'Linda' 'Belcher' 45
'Phillip' 'Frond' 38
'Calvin' 'Fischoeder' 70

A Note On Warnings

Try This

INSERT INTO cats(name, age) 
VALUES ('This cat is named 
        Charlie which is also a 
        human name.  In fact I know
        a couple of Charlies.  Fun Fact',
        10)

1 Warning??!

Query OK, 1 row affected, 1 warning (0.01 sec)

😱

Let's Take A Look

SHOW WARNINGS;

What's Up With This?

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(5) | YES  |     | NULL    |       |
| age   | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

"The Value Is Not Known"

Null Does Not Mean Zero!

Right now, we could do this...

INSERT INTO cats(name) 
VALUES ('Alabama');

Who names their cat 'Alabama'?

Or This! gasp 

INSERT INTO cats() 
VALUES ();

The Solution?

NOT NULL

CREATE TABLE cats2
  ( 
     name VARCHAR(100) NOT NULL,
     age  INT NOT NULL
  );

Notice The Difference!

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | int(11)      | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

What's Up With This?

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(5) | YES  |     | NULL    |       |
| age   | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+

To Set Default Values

CREATE TABLE cats3
  ( 
     name VARCHAR(100) DEFAULT 'unnamed',
     age  INT DEFAULT 99
  ); 
CREATE TABLE cats4
  ( 
     name VARCHAR(100) NOT NULL DEFAULT 'unnamed',
     age  INT NOT NULL DEFAULT 99
  );

Isn't This Redundant?

No!

We can still manually set things to NULL if we don't specify NOT NULL

INSERT INTO cats3(name, age) 
VALUES(NULL, 3);

One More Thing

What's Up With This?

+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| name  | varchar(5) | YES  |     | NULL    |       |
| age   | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
Name Breed Age
Monty Tabby 10
Monty Tabby 10
Monty Tabby 10
Monty Tabby 10

Right now, this could happen!

How Do We Make Each Unique?

Name Breed Age CatID
Monty Tabby 10 1
Monty Tabby 10 2
Monty Tabby 10 3
Monty Tabby 10 4

Primary Key

A Unique Identifier

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


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

YOUR

TURN

Define an Employees table, with the following fields:

 

  • id - number(automatically increments), mandatory, primary key
  • last_name - text, mandatory
  • first_name - text, mandatory
  • middle_name - text, not mandatory
  • age - number mandatory
  • current_status - text, mandatory, defaults to 'employed' 
CREATE TABLE employees (
    id INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    middle_name VARCHAR(255),
    age INTEGER NOT NULL,
    current_status VARCHAR(100) NOT NULL DEFAULT 'employed',
    PRIMARY KEY (id)
);

THE SOLUTION

CREATE TABLE employees (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    last_name VARCHAR(255) NOT NULL,
    first_name VARCHAR(255) NOT NULL,
    middle_name VARCHAR(255),
    age INTEGER NOT NULL,
    current_status VARCHAR(100) NOT NULL DEFAULT 'employed'
);

THE SOLUTION

(with a slight difference)