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"

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 = 

😡

NOT

GOOD

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 (signed) value of 2147483647

12

0

-9999

3145677

42

varchar

A Variable-Length String

'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!

So... How Do We Know It Worked?

Sometimes there is no easy order to teach this stuff... Here's a command from 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

To view errors and warnings

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 ('Bean');

Bean is a great name for a cat

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 PRIMARY KEY, 
  name VARCHAR(100), 
  age INT
 );

Primary Key

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

Another Option

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

NOT NULL is redundant

PRIMARY KEYs cannot be NULL

CREATE TABLE unique_cats3 (
    cat_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    PRIMARY KEY (cat_id)
);

Auto-Increment

cat_id will automatically increment for each new cat inserted into the table

YOUR

TURN

Define an Employees table, with the following fields:

 

  • id - number(automatically increments) and  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 AUTO_INCREMENT PRIMARY KEY,
    last_name VARCHAR(100) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    age INT NOT NULL,
    current_status VARCHAR(100) NOT NULL DEFAULT 'employed'
);

THE SOLUTION

MySQL 2.0: Getting Started with MySQL

By Colt Steele

MySQL 2.0: Getting Started with MySQL

  • 32,545

More from Colt Steele