SQL in the Database Management System MySQL
To modify and display data in a database management system, Structured Query Language (SQL) is used. More specifically, SQL is used in relational systems. The database management system used in my examples is MySQL. This is a free, Swedish-developed database management system that is now owned by Sun.
MySQL is available for download for both Windows, Mac OS X, Linux, and several Unix variants, so it should suit most users. I assume that you, being interested, can download MySQL from mysql.com and install it on your own, as it functions just like any other program. Once this is done, you can continue reading…
Create a Database
To check if MySQL is working correctly, we run the program. The examples below are made on a PC, and if you are using one, mysql.exe can be found in the mysql/bin directory. The SQL program looks essentially the same regardless of the environment, so it shouldn't be difficult to follow along if you are using a different setup.
If we run the following two commands:
mysql> CREATE DATABASE music;
mysql> SHOW DATABASES;
you should see the following output:
+-----------+
| Database |
+-----------+
| music |
| mysql |
| test |
+-----------+
3 rows in set (0.00 sec)
To indicate that we want to perform changes in this database going forward, we use the USE command:
mysql> USE music;
Create and Display a Table
The first thing we need to do now is to add the tables that will form the basis for the examples. This is done with a new CREATE command. When you press enter at the end of a line without terminating it with a semicolon, you will get a -> prompt instead of the usual one, and MySQL interprets this as the command continuing; remember to terminate the last line with a semicolon.
mysql> CREATE TABLE cds
-> (
-> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> title VARCHAR(40),
-> artist VARCHAR(40),
-> year DATE
-> );
For each column we create, the first parameter is the name of the column, the second is the data type, and the others set constraints and rules for that specific column.
In the id column, we will have a positive integer (int unsigned); this id cannot be empty when we add data to the database (not null), the value will automatically increase by one for each new entry (auto_increment), and it will also be the primary key (primary key) in the cds table, i.e., the column that ensures each row has a unique field of data allowing us to keep track of all entries.
mysql> SHOW TABLES;
+-------------------+
| Tables_in_music |
+-------------------+
| cds |
+-------------------+
1 row in set (0.00 sec)
Or, if you want more detailed information, you can use:
mysql> DESCRIBE cds;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| title | varchar(40) | YES | | NULL | |
| artist | varchar(40) | YES | | NULL | |
| year | date | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)
Adding Data to Tables
To add data to a table in SQL, the INSERT command is used. The syntax for INSERT is:
INSERT INTO table_name (column1, column2….) VALUES (value1, value2…);
To add a record to our table, we can thus add a CD with the command:
mysql> INSERT INTO cds (title, artist, year)
-> VALUES ("Out of Time", "R.E.M.", "1991-03-12");
To see which records are in the table, we use the most common SQL command, SELECT. It is with SELECT that we choose data from tables:
mysql> SELECT * FROM cds;
+----+-------------+--------+------------+
| id | title | artist | year |
+----+-------------+--------+------------+
| 1 | Out of Time | R.E.M. | 1991-03-12 |
+----+-------------+--------+------------+
1 row in set (0.00 sec)
Since we set the id column to auto_increment, we do not need to specify this value; it is added automatically and becomes one greater than the highest value for each new row added. In this case, the id becomes 1 since there was no data in the table before.
Now, it's time to make some use of this…
After this short introduction to SQL, I hope it feels like you have a bit of an idea of how it works. Of course, there is much more to go through, but now it shouldn't feel completely distant to write the first SQL statements, either directly in the interface we used above or in an initial PHP script.
How to use MySQL together with PHP or other programming languages is the subject of another article, but a good start can be found on PHP's website where there are simple examples.