Home arrow MySQL arrow Page 3 - Beginning MySQL Tutorial

Part 2: Datatypes and Tables - MySQL

One of the fastest SQL (Structured Query Language) database servers currently on the market is the MySQL server, developed by T.c.X. DataKonsultAB. MySQL, available for download at http://www.mysql.com, offers the data base programmer with an array of options and capabilities rarely seen in other database servers. What's more, MySQL is free of charge for those wishing to use it for private and commercial use.

TABLE OF CONTENTS:
  1. Beginning MySQL Tutorial
  2. Part 1: At First Glance
  3. Part 2: Datatypes and Tables
  4. Part 3: Manipulating the Database
  5. Part 4: Advanced MySQL Commands
By: W.J. Gilmore
Rating: starstarstarstarstar / 293
April 03, 1999

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

A database is really nothing more than a hierarchy of increasingly complex data structures. In MySQL, the acknowledged structure for holding blocks (or records) of information is called the table.

These records, in turn, are made up of the smallest object that can be manipulated by the user, known as the datatype. Together, one or more of these datatypes form a record. A table holds the collection of records that make up part of the database. We can consider the hierarchy of a database to be that of the following:

Database < Table < Record < Datatype

Datatypes come in several forms and sizes, allowing the programmer to create tables suited for the scope of the project. The decisions made in choosing proper datatypes greatly influence the performance of a database, so it is wise to have a detailed understanding of these concepts.

MySQL Datatypes

MySQL is capable of many of the datatypes that even the novice programmer has probably already been exposed to. Some of the more commonly used include:

CHAR (M)
CHAR's are used to represent fixed length strings. A CHAR string can range from 1-255 characters. In later table creation, an example CHAR datatype would be declared as follows:

ex.
car_model CHAR(10);

VARCHAR (M)
VARCHAR is a more flexible form of the CHAR data type. It also represents data of type String, yet stores this data in variable length format. Again, VARCHAR can hold 1-255 characters. VARCHAR is usually a wiser choice than CHAR, due to it's variable length format characteristic. Although, keep in mind that CHAR is much faster than VARCHAR, sometimes up to 50%.
(A CHAR stores the whole length of the declared variable, regardless of the size of the data contained within, whereas a VARCHAR only stores the length of the data, thus reducing size of the database file.)

ex.
car_model VARCHAR(10);

INT (M) [Unsigned]
The INT datatype stores integers ranging from -2147483648 to 2147483647. An optional "unsigned" can be denoted with the declaration, modifying the range to be 0 to 4294967295

ex.
light_years INT;
Valid integer: '-24567'.    Invalid integer: '3000000000'.

ex.
light_years INT unsigned;
Valid integer: '3000000000'.    Invalid integer: '-24567'.

FLOAT [(M,D)]
A FLOAT represents small decimal numbers, used when a somewhat more precise representation of a number is required.

ex.
rainfall FLOAT (4,2);
This could be used to represent rainfall average in centimeters per year, which could be a decimal value. More specifically, FLOAT (4,2) states the fact that rainfall can hold up to four characters and two decimal places. Thus,

42.35 is valid, accurately represented.
324.45 is invalid, rounded to 324.5.
2.2 is valid, accurately represented.
34.542 is invalid, rounded to 34.54.

Note: Due to the fact that FLOAT is rounded, those wishing to represent money values would find it wise to use DECIMAL, a datatype found within MySQL that does not round values. Consult the documentation for a complete explanation.

DATE
Stores date related information. The default format is 'YYYY-MM-DD', and ranges from '0000-00-00' to '9999-12-31'. MySQL provides a powerful set of date formatting and manipulation commands, too numerous to be covered within this article. However, one can find these functions covered in detail within the MySQL documentation.

the_date DATE;

TEXT / BLOB
The text and blob datatypes are used when a string of 255 - 65535 characters is required to be stored. This is useful when one would need to store an article such as the one you are reading. However, there is no end space truncation as with VARCHAR AND CHAR. The only difference between BLOB and TEXT is that TEXT is compared case insensitively, while BLOB is compared case sensitively.

SET
A datatype of type string that allows one to choose from a designated set of values, be it one value or several values. One can designate up to 64 values.

ex.
transport SET ("truck", "wagon") NOT NULL;

From the above declaration, the following values can be held by transport:

""
"truck"
"wagon"
"truck,wagon"

ENUM
A datatype of type string that has the same characteristics as the SET datatype, but only one set of allowed values may be chosen. Usually only takes up one byte of space, thus saving time and space within a table.

ex.
transport ENUM ("truck", "wagon") NOT NULL;

From the above declaration, the following values can be held by transport:

""
"truck"
"wagon"

Records

Together, a group of declared datatypes form what is known as a record. A record can be as small as one data variable, or as many as deemed needed. One or more records form the structure of a table.

The Bigger Picture: Tables

Before we can execute commands on the database, we must first create a table in which data can be stored. This is accomplished in the following manner:

mysql> CREATE TABLE test (
> name VARCHAR (15),
> email VARCHAR (25),
> phone_number INT,
> ID INT NOT NULL AUTO_INCREMENT,
> PRIMARY KEY (ID));

Ensuing output:

Query OK, 0 rows affected (0.10 sec)
mysql>

The first table in your database has now been created. Note: no two tables can have the same name.
Note(2): Each dataspace is more often referred to as a column.

Column Characteristics:

  • A name may not be made up of strictly numbers.
  • A name may start with a number.
  • A name may be up to 64 characters.

Other table options:

The following options can be placed after any datatype, adding other characteristics and capabilities to them.

  • Primary Key. Used to differentiate one record from another. No two records can have the same primary key. This is obviously useful when it is imperative that no two records are mistaken to be the other.
  • Auto_Increment. A column with this function is automatically incremented one value (previous + 1) when an insertion is made into the record. The datatype is automatically incremented when 'NULL' is inserted into the column.
  • NOT NULL. Signifies that the column can never be assigned a NULL value.
ex.
soc_sec_number INT PRIMARY KEY;
No two soc_sec_number records can hold the same value.

ID_NUMBER INT AUTO_INCREMENT;
Automatically increments in value, starting at '1', with every subsequent insertion.

Table-Relevant Commands

We can execute a number of useful commands pertaining to the tables, such as the following:

Show Tables

mysql> show tables;

Result:
This will list all tables currently existing within the database.

Show Columns

mysql> show columns from test;

Result:
This will return the columns and column information pertaining to the designated table.

Take a minute to execute each one of the above commands after you have created the test table. They will prove very helpful as your database increases in size and complexity.

You should now have a basic understanding of the creation of tables, one of the most important concepts of the MySQL server. You now know that tables are constructed using datatypes, which when grouped together form a record. In the next section, we will begin learning how to manipulate the database.



 
 
>>> More MySQL Articles          >>> More By W.J. Gilmore
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: