MySQL Tutorials: Introduction to Tables

	Introduction to Tables

Tables Fundamentals

Introduction

In the previous lesson, we saw that a database is one or many lists of values. Actually, a list in a database is called a table. A table is the arrangement of the values of a database. The values are stored in the computer memory (in one or more hard drives).

To better organize its values, a table is made of (vertical) arrangements named columns. Each column must have a name and the table must specify the types of values of its columns.

Creating a Table

Therefore, the primary formula to create a table is:

CREATE TABLE table-name(column1, column2, column_n)

To make the code easy to read, each column can be on its own line:

CREATE TABLE table-name
(
    column1,
    column2,
    column_n
)

In order to get a table in a database through a webpage, you can create a SQL statement and pass it to the mysqli_query() function. Here is an example:

<?php
$link = mysqli_connect("localhost", "usermine", "P@s$w0rd1", "Exercise1");

mysqli_query($link, "CREATE TABLE statement")

?>

Remember that an alternative is to use the mysqli class and call its query() function. Here is an example:

<?php
$link = new mysqli("localhost", "usermine", "P@s$w0rd1", "exercise1");

$link->query("CREATE TABLE statement")

?>

Creating a Column

Different programming environments or websites provide different ways to visually create a table. For example, the control panel of some websites provide a button labeled PHP My Admin. Clicking that button would open a special webpage for database management. To programmatically create a column, the formula to follow is:

column-name data-type options

A column must have a name, which is made of letters and digits. A table can have various types of values, including words, numbers, dates, etc. When creating a column, you must specify the type of values it will hold. In the next sections, we will review data types. In future lessons, we will study the possible options.

Characters and Strings

Characters

A character is any kind of symbol: alphabetic, numeric, readable, or not. To indicate that a column would use any symbol, specify its data type as CHAR (or char). Here is an example:

CREATE TABLE LetterGrades
(
    Grade CHAR
)

When performing data entry, to specify the value of a CHAR-based column, include the value in quotes.

Strings

A string is a group of characters, letters, or symbols, also referred to as a variable number of characters. To create a column that can hold strings, set its data type as VARCHAR or varchar. You should apply parentheses to the data type. In the parentheses, specify the maximum number of characters that a value can hold. Here is an example of creating a string-based column:

<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("CREATE TABLE GradesScales15
             (
                 LetterGrade CHAR,
                 Descriptor VARCHAR(25)
             );");

$link->close();
?>

</body>
</html>

Both the CHAR and the VARCHAR types are made for strings. There are some differences in their behaviors:

Like VARCHAR, the CHAR type uses parentheses to indicate the maximum number of characters that the values of the column can hold. Here are examples:
```
CREATE TABLE StudentsGrades
(
    LetterGrade CHAR(2),
    Descriptor VARCHAR(25)
);
```
If you don't specify the default number of characters for CHAR, it is automatically set to 1
The maximum number of characters of a CHAR type is 255 while the maximum number of characters of a VARCHAR type is 65,535
When a CHAR-based column has been created and the maximum number of characters has been specified, a fixed number of bytes is used to hold that number of characters. A CHAR-based column cannot hold a higher number of characters
A VARCHAR-based column can handle a very large length of a text, including many paragraphs. It is based, or limited by, the computer memory

Numeric Data Types

Introduction

MySQL supports various types of numbers. A whole number, or natural number, also called an integer, is a number that doesn't have a fraction. The length of an integer is the number of bytes it can hold.

Signed and Unsigned Numbers

A number is referred to as signed if it can be expressed as a negative or a positive number. A negative number must start with a - symbol. An example is -62. A positive number may or may not use the + symbol. An unsigned number is a number that doesn't use a - or + symbol. Such a number is considered positive. To support these concepts, MySQL provides the UNSIGNED keyword that is added to a numeric data type to indicate that the values of a column must always be positive.

Integers

The Bit Type

In the computer industry, a bit is a value that can be either 0 or 1. To support the bit as a data type, MySQL provides a data type named BIT. Here is an example of applying it to a column:

CREATE TABLE MetroStations
(
    ParkingAvailable BIT
);

Tiny Integers

If the values of a column will be small natural numbers that range from 0 to 255 (1 byte), set its data type as TINYINT or tinyint. Here are examples:

CREATE TABLE GradesScales
(
    MinPercent TINYINT,
    MaxPercent TINYINT
);

If you want to indicate that the values of the column must always be positive, set the data type as TINYINT UNSIGNED. Here are examples:

<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("CREATE TABLE GradesScales1
              (
                  LetterGrade CHAR(2),
	          MinPercent TINYINT UNSIGNED,
 	          MaxPercent TINYINT UNSIGNED,
                  Descriptor VARCHAR(25)
              );");

$link->close();
?>

</body>
</html>

Small Integers

If a column needs small integers in the range of -32,768 to 32,767 (2 bytes), set its data type as SMALLINT or smallint. To indicate that the values of the column must always be positive, add UNSIGNED to the data type.

Medium Integers

If you want a column to carry signed numbers between -8388608 and 8388607 or unsigned numbers between 0 and 16777215 (3 bytes), set its data type as MEDIUMINT. To indicate that the values of the column must always be positive, add UNSIGNED to the data type.

Integers

If the values of a column would range from -2,147,483,648 to 2,147,483,647, (4 bytes) set its data type to INT or INTEGER (or int or integer. To indicate that the values of the column must always be positive, add UNSIGNED.

Long Integers

If you want a column that can hold large numbers (8 bytes) between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807, set its type as BIGINT or bigint

Binary Values

The binary type is used for a column that would hold natural numbers in series of bits. Each bit is treated, checked or used individually. When creating a column for such values, apply the BIT data type that uses parentheses. In the parentheses, type the number of bits necessary to store the value. The number is between 1 and 64.

Decimal Numbers

Floating-Point Decimal Numbers

A floating-point decimal number is a number with a fractional part. MySQL supports various types of decimal numbers. Some of the decimal types use an approximate precision for their values. These data types are float, real, and double. Here is an example that uses the float type:

CREATE TABLE GradesScales
(
    MinRange float,
    MaxRange float
);

If you want the numbers to always be positive, add the UNSIGNED keyword after the parentheses of the data type. Here are examples:

<!DOCTYPE html>
<html>
<head>
<title>Students Grades - Scales</title>
</head>
<body>
<h2>Students Grades - Scales</h2>

<?php
$link = mysqli_connect("localhost", "username", "P@s$w0rd1", "DBExercise");

mysqli_connect_errno();

$link->query("CREATE TABLE GradesScales24
              (
                  LetterGrade CHAR(2),
	          MinRange float UNSIGNED,
	          MaxRange float UNSIGNED,
	          MinPercent TINYINT UNSIGNED,
 	          MaxPercent TINYINT UNSIGNED,
                  Descriptor VARCHAR(25)
              );");

$link->close();
?>

</body>
</html>

Fixed-Point Decimal Numbers

To let you create a column for values that use a fixed precision, MySQL provides data types as NUMERIC (or numeric) or DECIMAL (or decimal). Here is an example:

CREATE TABLE Distances
(
    FromCity varchar(40),
    ToCity   varchar(40),
    Distance decimal
);

This also means that you can use the decimal types where you would use an integer. Normally, the NUMERIC (or numeric) or DECIMAL (or decimal) type is suitable for a column that will store monetary values. If you want to specify the amount of precision for the values, add a set of parentheses to the type. In the parentheses, add the precision and the scale as natural numbers separated by a comma. The column can have positive or negative values between -922,337,203,685,477.5808 and +922,337,203,685,477.5807. If you want the numbers to always be positive, add the UNSIGNED keyword after the parentheses of the data type.

Geometric Value-Based Columns

MySQL supports coordinates of a geometric figure. It does this through a data type named GEOMETRY or geometry.

Geographical Location-Based Columns

MySQL supports geographical locations using a data type named GEOGRAPHY.