Data Types

There's More to Discuss!

Storing Text

VARCHAR

CHAR

What's The Difference?

CHAR has a fixed length

Char(3) -> Only 3 Characters "Allowed"

The length of a CHAR column is fixed to the 
length that you declare when you create the table. 
The length can be any value from 0 to 255. 
When CHAR values are stored, they are right-padded 
with spaces to the specified length. When CHAR 
values are retrieved, trailing spaces are removed 
unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled.

CHAR is faster for fixed length text

zip codes: 59715, 94924

State Abbreviations: CA, NY

Yes/No Flags: Y/N

Otherwise...

USE VARCHAR

Value	Char(4)	Storage	Varchar(4)	Storage
' '	' '	4 bytes	' '	1 byte
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes

Here's A Table

NUMBERS

INT

WHOLE NUMBERS

DECIMAL

DECIMAL(13, 2)

DECIMAL(5, 2)

Total Number Of Digits

Digits After Decimal

999.99

5 Digits

2 Digits

DECIMAL(5, 2)

But there's also...

FLOAT

and DOUBLE???

Store larger numbers using less space

BUT.....

(and it's a big BUT)

It comes at the cost of precision

Data Type	Memory Needed	Precision Issues
FLOAT	4 Bytes	~7 digits
DOUBLE	8 Bytes	~15 digits

Another Table

So Which Do I Use?

On To

Dates & Times

DATE

Values With a Date But No Time

'YYYY-MM-DD' Format

TIME

Values With a Time But No Date

'HH:MM:SS' Format

DATETIME

Values With a Date AND Time

'YYYY-MM-DD HH:MM:SS' Format

I Typically Use DATETIME

Let's Try It Out!

Some Useful Date Functions

Formatting

DATES

DATE

MATH

TIMESTAMPS?!

YOUR

TURN

What's a good use case for CHAR?

(just make one up)

CREATE TABLE inventory (
    item_name ____________,
    price ________________,
    quantity _____________
);

Fill In The Blanks

(price is always < 1,000,000)

What's the difference between

DATETIME and TIMESTAMP?

Print Out The Current Time

Print Out The Current Date (but not time)

Print Out The Current Day Of The Week

(The Number)

Print Out The Current Day Of The Week

(The Day Name)

Print out the current day and time using this format:

mm/dd/yyyy

Print out the current day and time using this format:

January 2nd at 3:15

April 1st at 10:18

Create a tweets table that stores:

The Tweet content
A Username
Time it was created

Pat Yourself On The Back