PhpMyAdmin Tutorial 4

Learning SQL Using phpMyAdmin

What is SQL?

Structured Query Language is a non-procedural language used to define, manipulate and retrieve data. It was developed by IBM (System/R project) in 1974-1979. The American National Standards Institute (ANSI) published in 1986 the first official standard of the language (later revised in 1989, 1992 and 1999), and since then,  the industry has widely adopted SQL as the relational database language. Virtually every database system nowadays is interfaced through SQL.

The specific data architecture addressed by SQL is called the relational architecture. The various pieces of data (columns) are grouped into tables contained in databases, and we retrieve data using relations expressed between the tables.

In this article, we will use MySQL, a popular open-source implementation of SQL that is deployed by most Web host providers.

Toolkit for this guide

To be able to do the exercises in this guide, you will need an access to a MySQL server. Your interface to MySQL will be phpMyAdmin, a PHP application running on a PHP-enabled Web server.

Creating Sample Tables

We will use a geographical information system as an example. We decide that we need information about cities and countries, so we design two tables, which will be part of a database called geodb (although any database name would do). To create the tables, we can use phpMyAdmin's Structure sub-page inDatabase view, or we can use the SQL query box to enter the appropriate statement:

The table creation is accomplished with the CREATE TABLE statement, in which we give the new table's name. The statement begins with CREATE TABLE, followed by the table name. Then, enclosed in brackets, we put the list of columns,  and information about the keys. Each column is assigned a name, data type, the NULL or NOT NULL attribute (here, NOT NULL means the column cannot have a NULL value) and a default value, if appropriate.

CREATE TABLE cities (

  id int(11) NOT NULL auto_increment,

  city_name varchar(50) NOT NULL default '',

  latitude varchar(15) NOT NULL default '',

  longitude varchar(15) NOT NULL default '',

  population int(11) NOT NULL default '0',

  country_code char(2) NOT NULL default '',

  PRIMARY KEY  (id)

) TYPE=MyISAM AUTO_INCREMENT=1 ;

The id column is our primary key, a column which uniquely identifies each city. Its data type is INT (an integer number), and MySQL will assign unique numbers to it, thanks to the auto_increment attribute. Note that we cannot use the city name as a primary key, as some city names are not unique in the world. We also use an integer for the population data.

The other columns use character (CHAR) or variable character (VARCHAR) data types. When we know the exact length of data, it's better to use CHAR, specifying the length of the column as in CHAR(2). Otherwise we use a variable character data type, which will take only the space needed by each piece of data, and we specify the maximum length, as in VARCHAR(15).

After the columns list, we have some table-specific information, like its type, and the first value for the auto-increment column. SQL statements end with a semi-colon.

Having created our cities table, we do the same operation, this time for the countries table.

CREATE TABLE countries (

  country_code char(2) NOT NULL default '',

  country_name varchar(100) NOT NULL default ''

) TYPE=MyISAM;

We notice that the country_code column is present in both tables. This shows the relational principle: the country_code in cities refers to the same column incountries. This way, we save on space, having each  country name only once in our database.


<<< PREVIOUS                                                                                                                  NEXT >>>