Data Types in SQL

Numeric Data Types

INT: Whole Numbers

FLOAT (M,D): Decimal numbers (approximate)

  • example: 1.78 = FLOAT (3,2)
  • 1.625 will round to a 1.63

DECIMAL (M,D): Decimal numbers (precise)

  • best for things like $
  • DECIMAL (4,2) = $10.32

Non-Numeric Data Types

CHAR(N): Fixed length character

  • CHAR(3) = any value in that column has to be this length
  • example: USD

VARCHAR(N): Varying length character

  • Any string up to the N

ENUM('M','F'): Value from a defined list

  • This is male or female
  • good for gender or yes or no

BOOLEAN: True or False values

  • examples In stock - set to true or false

Date and time types

DATE: Date (YYYY-MM-DD)

DATETIME: Date and tiem (YYYY-MM-DD HH-MM-SS)

TIME: Time (HHH-MM-SS)

  • Not just used for times of day
  • Can be used for the time between two events

YEAR: Year (YYYY)

 

 

 

Primary Key

  • Set of columns which uniquely identifies a record within a table
  • must be unique
  • cannot be null
  • table can only have one primary key

Foreign Key

  • Used to link two tables together
  • column whose values match the values of another tables primary key column
  • table with the primary key is called the reference, or parent, table and the table with the foreign key is called the child table
  • A table can have multiple foreign keys

 

Leave a comment