content

Tutorial on SQL

Why another Tutorial on SQL?
Aren't there already enough?
Maybe, but we want to be better.
But you will have to be patient, because this website has just started!

Creating a Table

In the previous chapter, we have created a database "company". We want to populate this database with tables now. We assume, that the database has been selected as the default database. This can be done with the "use" command, as we did in the previous chapter. Alternatively, you can start MySQL with the name of this database:

Current Database

You can find out at any time which database is currently selected by using the "SELECT DATABASE();" command.
The next session starts no database selected. 
bernd@venus:~$ mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.1.61-0ubuntu0.11.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> USE company;
Database changed
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| company   |
+------------+
1 row in set (0.00 sec)

mysql>

Seeing the Tables of a Database

It's possible to see all the tables of a database with the "SHOW TABLES" command, as you can see in the following example session. As we haven't created any tables in company so far, we get the "Empty set" result for company: 
mysql> USE company
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
23 rows in set (0.00 sec)

mysql>

Creating a Table

We want to create now a table containing just some essential information of an employee of our imaginary company. It seems natural to call our table "employee". Of course, as it usually contains more than one employee, we could use the plural "employees" as well for a table name. 
mysql> CREATE TABLE employee
    -> ( staff_number INT NOT NULL AUTO_INCREMENT,
    -> fname VARCHAR(20),
    -> lname VARCHAR(30),
    -> gender CHAR(1),
    -> birth_date DATE,
    -> PRIMARY KEY (staff_number) );
Query OK, 0 rows affected (0.09 sec)

mysql>
We used the RETURN key, when we typed in the previous table definition. You can recognize this by the "->" prompt. We could have typed it in in one large line without using the RETURN key.
We will talk about datatypes like VARCHAR in the following chapter. For the time being, you may consider fname, lname, gender to be strings with fixed lengths. The last line of our table definition is not self-explanatory: You have to tell the MySQL database server which column or columns of your table definition serve as the primary key for the table. This is done by the "PRIMARY KEY ..." command. We will have a deeper look at primary keys and their function in the following chapters, so please do not worry, if there are some questions open now.

The Describe Command

After having typed in the table definition or at some other point in time, you may want to review the description of a table. SQL provides for this purpose the "DESCRIBE" command, which can be abbreviated to "DESC": 
mysql> DESC employee;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| staff_number | int(11)     | NO   | PRI | NULL    | auto_increment |
| fname        | varchar(20) | YES  |     | NULL    |                |
| lname        | varchar(30) | YES  |     | NULL    |                |
| gender       | char(1)     | YES  |     | NULL    |                |
| birth_date   | date        | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> DESCRIBE employee;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| staff_number | int(11)     | NO   | PRI | NULL    | auto_increment |
| fname        | varchar(20) | YES  |     | NULL    |                |
| lname        | varchar(30) | YES  |     | NULL    |                |
| gender       | char(1)     | YES  |     | NULL    |                |
| birth_date   | date        | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql>

Getting Rid of a Table

Now, we know how to create a table and we can have a look at what we have created with the describe command. We want to prevent, that you are stuck like the Sorcerer's Apprentice: "Ah, here comes the master!
I have need of Thee!
From the spirits (or in our case "the tables")
that I called Sir, deliver me!" Getting rid of tables is as easy as getting rid of databases, which we have explained in the previous chapter. Tables are "dropped": 
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employee          |
+-------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE employee;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql>
"DROP TABLES" removes one or more tables and all data, indexes, triggers, constraints, and permission specifications for those tables.

The INSERT Statement

Everything is ready now to add some employees to our database. 
mysql> INSERT INTO employee
    -> (staff_number, fname, lname, gender, birth_date)
    -> VALUES (null, "William", "Shakespeare", "m", "1961-10-25");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee
    -> (staff_number, fname, lname, gender, birth_date)
    -> VALUES (null, "Frank", "Schiller", "m", "1955-08-17");
Query OK, 1 row affected (0.00 sec)

mysql>
"Query OK, 1 row affected (0.00 sec)" tells us that our statement was correct and that one row was added to the database.

The next logical step to go on from here is to learn now is how to see what's in a table. We learn about the "SELECT" command in our next chapter.