Understanding Table Data and Creating Tables


For this web page only, landscape orientation is best for printing!
 

Understanding Table Data

Data in tables can be various types, and must be designated as to which type applies to each field. The tables below describe all the possibilities. Don't try to memorize it all, come back to this table as you need.

NUMERICAL and DATE DATA
Integers
Type Range Bytes Description
TINYINT -127/128 1 Very small integers
SMALLINT -32768/
32767
2 Small integers
MEDIUMINT -8388608/
8388607
3 Medium-sized integers
INTEGER -231/231 4 Regular integers
BIGINT -263/263 8 Big integers
Floating Point
Type Range Bytes Description
FLOAT 1.17E-38/
3.40E+38
4 Single precision
DOUBLE 1.79E-308/
2.22E+308
8 Double precision
DECIMAL varies varies FLOAT stored as CHAR
Date and Time
Type Range Description
YEAR 1901/2155 4-digit year
DATE 1000-01-01
9999-12-31
Displayed as
YYYY-MM-DD
DATETIME 1000-01-01
9999-12-31
Displayed as
YYYY-MM-DDHH:MM:SS
TIME -838:59:59/
838:59:59
Displayed as
HH:MM:SS
TIMESTAMP 1970-01-01
2037-??-??
Displayed as
YYYYMMDDHHMMSS
STRING DATA
Regular
Type Range Description
CHAR 1/255
characters
Fixed length string
VARCHAR 1/255
characters
Variable length string
Long Text
Type Max length Description
TINYTEXT 255 Tiny text field
TEXT 65,535 Normal text field
MEDIUMTEXT 224 - 1 Medium text field
LONGTEXT 232 - 1 Long text field
Binary Large Object
Type Max length Description
TINYBLOB 255 Tiny BLOB field
BLOB 65,535 Normal BLOB field
MEDIUMBLOB 224 - 1 Medium BLOB field
LONGBLOB 232 - 1 Long BLOB field

 

CREATE Tables

Now you can fill your database with tables. Table names cannot contain spaces, slashes or periods. The syntax is: CREATE TABLE <table_name> (<field_name_1> <field_type_1> <modifiers>, <field_name_2> <field_type_2> <modifiers>, ... , <field_name_n> <field_type_n> <modifiers>) Here is the code to create the People Table used in previous examples. The DESCRIBE command shows us the fields in a table.

mysql> CREATE TABLE people ( -> ID char(8) NOT NULL, -> NAME_LAST char(20) NOT NULL, -> NAME_FIRST char(20) NOT NULL, -> MI char(1) NOT NULL, -> POSITION char(6) NOT NULL, -> PRIMARY KEY (ID) -> ); Query OK, 0 rows affected (0.00 sec) mysql> describe people;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| ID         | char(8)  |      | PRI |         |       |
| NAME_LAST  | char(20) |      |     |         |       |
| NAME_FIRST | char(20) |      |     |         |       |
| MI         | char(1)  |      |     |         |       |
| POSITION   | char(6)  |      |     |         |       |
+------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec) mysql>

As you can see, the table name is people. The fields are ID, NAME_LAST, NAME_FIRST, MI and POSITION. I chose the char type from the tables above for all fields. The numbers in parentheses are the maximum number of characters I allowed for each field (the larger the maximum number, the more storage space used in the table). Lastly, I set the Primary Key to ID

When records are entered and fields are populated, some of them may be empty. For instance, when we first input the record for Barbara Smith, we might not know her middle initial, so we leave it blank. An empty string "" is something; the number 0 is something; NULL is nothing. When you use null, it reflects no value. To provide some value, even if it is a blank space, use the NOT NULL modifier. Now, if we don't know Barbara Smith's middle initial, a blank space is entered.

Other modifiers you can employ include

Copy the CREATE TABLE command (shown in blue above) and paste it after your mysql prompt so you can create the same table. To test the creation, type describe people; .


SQL Intro., Show   < <  PREVIOUS   Table of Contents NEXT  > >   Alter, Insert

Developed with HTML-Kit
Sandersongs Web Tutorials
Contact the Webmasterwith comments.
©2017, by Bill Sanders, all rights reserved.
This domain had 3,990 different visits in the last 30 days.
435,556 hits on this domain since 24 Nov 2006.
http://www.sandersongs.com/PHPsqlCourse/SQL02.php
This page was last modified on our server on 4 Jun 2008
and last refreshed on our server at 3:55 am, MST
This file took 0.01345 seconds to process.