Table data types

As seen in the previous lesson, when creating a table we must set a data type with each field. There are several different data types we can choose from depending on the data we plan to enter in each field. Below the data types are classified into three categories to make it easier to differentiate, number, string, and date and time.

Number data types

Some of the number data types allow for a signed and unsigned option. A number that is signed will have the set of available number split to accommodate including negative numbers. Unsigned numbers will use the entire data set in positive values and zero. Below are a list of data types and descriptions.

TINYINT: A whole number with a value between -128 and 127
TINYINT UNSIGNED: A whole number with a value between 0 and 255

SMALLINT: A whole number with a value between -32,768 and 32,767
SMALLINT UNSIGNED: A whole number with a value between 0 and 65,535

MEDIUMINT: A whole number with a value between -8,388,608 and 8,388,607
MEDIUMINT UNSIGNED:  A whole number with a value between 0 and 16,777,215

INT: A whole number with a value between -2,147,483,648 and -2,147,483,647
INT UNSIGNED: A whole number with a value between 0 and 4,294,967,295

BIGINT: A whole number with a value between -9,223,372,036,854,775,808 and -9,223,372,036,854,775,807
BIGINT UNSIGNED: A whole number with a value between 0 and 18,446,744,073,709,551,615

FLOAT: A number with decimal spaces, with a value no bigger than 1.1E38 or smaller than -1.1E38
DOUBLE: A number with decimal spaces, with a value no bigger than 1.7E308 or smaller than -1.7E308
DECIMAL(p,d): A number with a total of p digits, of which d being after the decimal point. There is a maximum of 65 digits.

String data types

Strings are characters stored together. We often see them as words, but they can be any combination of letters, symbols, and numbers.  Below is a list of various string data types.

CHAR(n): A character string with a fixed length n, with a maximum up to 255 characters
VARCHAR(n): A character string with a variable length up to n, with a maximum up to 255 characters
BLOB: Can contain 2^16 bytes of data
ENUM(a,b): A character string that has a limited number of total values, which you must define. For example a and b
SET: A list of legal possible character strings. Unlike ENUM, a SET can contain multiple values in comparison to the one legal value with ENUM.
BINARY(n): Binary string with a fixed length n

Date and time data types

Date and time data types allow us to enter in a date or time to a field. Below is a list of data types.

DATE: A date value with the format of (YYYY-MM-DD)
TIME: A time value with the format of (HH:MM:SS)
DATETIME: A time value with the format of (YYYY-MM-DD HH:MM:SS)
TIMESTAMP: A time value with the format of (YYYY-MM-DD HH:MM:SS) and that changes based on timezone
YEAR: A year value with the format of (YYYY)

 

An example of the student table using various data types is illustrated below:

CREATE TABLE student(
student_id  INT  UNSIGNED  PRIMARY KEY,
first_name VARCHAR(30),
last_name VARCHAR(30),
state CHAR(2),
zip MEDIUMINT  UNSIGNED,
birth_date DATE,
sex ENUM('M', 'F'),
date_enrolled DATETIME
);

Lesson Activities

  1. We are going to create a table for the faculty members in a table called faculty. The faculty_id field contains an auto incrementing attribute which will be covered in the next lesson. Note: If you have an error on your code you can use the up arrow to load the previous command. Enter the following code:
    Click to see code
    CREATE TABLE faculty(
    faculty_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    email VARCHAR(60),
    street VARCHAR(50),
    city VARCHAR(40),
    state CHAR(2) DEFAULT "VA",
    zip MEDIUMINT UNSIGNED,
    phone VARCHAR(20),
    birth_date DATE,
    sex ENUM('M', 'F')
    );
  2. To view the structure of the table lets use the DESCRIBE command. Use the following command:
    Click to see code
    DESCRIBE faculty;
  3. We are going to also create a table that defines the programs a student can enroll in. Use the following command:
    Click to see code
    CREATE TABLE program(
    program_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    program_name VARCHAR(30),
    description VARCHAR(255)
    );
  4. To view a listing of the tables, which should now be faculty and program, use the SHOW TABLES command.
    Click to see code
    SHOW TABLES;

Questions