MySQL for Newbies: Day one with MySQL

ER diagram

So when you join any company(unless the company is also not on day one), they will be having their on database, tables and the relevant data. So your senior is likely to provide you with an ER (Entity-Relation diagram). An ER diagrams is used to show various tables in a database(can be multiple databases as well and their relationship between each other. We will continue with our school project, a small subset of the ER diagram looks like this.

First ER Diagram

It should self explanatory I believe. There are different tables namely students, faculty, students and faculty_student_mapping. Usually in an ER diagram, only the key columns will be shown. For example, in students block we have shown columns like id, name, class, date_of_joining. But there can more than that like parents name, emergency contact number etc. But for the sake of sanity most people try to keep it clean.

Exploring Database

We are not living in an ideal world, so ER diagram is quite often a luxury to dream when you join. And we may have to explore the tables by self. So your senior is most likely to give you access to database or might set up a DB server in your local machine. And he is most likely to tell you the key databases and table names. Now how to proceed.

Lets assume you have a local database set up now. To connect to a database, mysql command is used. The simple syntax is:

mysql -h<hostname> -u<user_name> -p<password>

By default, localhost is considered as localhost, username is current user. If you have just set the DB server on your local machine, the user will be root and there wont be a password by default. So you can connect as mysql -u root

And your are connected to your database for the first time. You will be get a prompt as

mysql>

So what next?

(If you would like to have the DB, we are using, please copy the data from the URL
http://zerounknown.orgfree.com/academic.sql and paste the same in your mysql terminal)

To identify the databases you have, show databases can be used.

Most of the DBs listed here are default one. Out of these, you were told to explode academics DB. So you have to connect to the appropriate DB. For that we have "use <database_name>" command

mysql> use academic;

Exploring Table

Now we have reached a database. Next step to identify tables in the database. So now you have "show tables" command just like databases

mysql> show tables;

Data of a table

Now, you are going to understand what is there in table. For a table, there are two things you have to care about - data and meta data. Data in a table can be viewed using a select query.

mysql> select * from students limit 2;
+----+-----------+-------+---------------------+
| id | name | class | date_of_joining |
+----+-----------+-------+---------------------+
| 1 | Paul Matt | 7 | 2011-06-10 00:00:00 |
| 2 | Liz Frank | 8 | 2008-06-12 00:00:00 |
+----+-----------+-------+---------------------+
2 rows in set (0.00 sec)

So what have you done here? You have run your first select query. Let us break the query into 3 parts.
select * - The select clause tells you which all columns you need to be fetched. As were not aware of which all columns where present in the table, we did as * to list all columns. Now you can to a select id, name, date_of_joining
from students - The from clause defines from which table(s) you would like to fetch the data.
limit 2 - The limit clause is not a mandatory clause, limit number of rows being displayed. If you are not giving a limit clause, it will show all records in the table.

Try yourself the output of below and see when \G can be useful for you
mysql> select * from students limit 2 \G

Meta Data of a table

Now need to move to next set of information - meta data of table. The query is simpler this time, but your result is complex.

mysql> desc students;
+-----------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+-------------------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | MUL | NULL | |
| class | varchar(10) | YES | | NULL | |
| date_of_joining | timestamp | NO | | CURRENT_TIMESTAMP | |
+-----------------+-------------+------+-----+-------------------+----------------+
4 rows in set (0.01 sec)

A describe(desc) table is used to list all the columns in a table. And it always have these 6 columns. Each row represents a column in the actual table. Now what each column in the list means? Most of you might have answered it yourself.
Field - the name of the column in the table
Type - data type of the field (will explain the same soon)
Null - whether the field can have an empty value, (for eg, date_of_joining can be empty for a student who is joining next month.)
Key - tells about the indexing on the column, (leave it for couple of days, you dont have to bother about it)
Default - the default is the value saved if nothing is specified for the column. (for eg, you may assume that student normally joins to grade 1, and in that case, default value for class will be "grade 1".
Extra - it holds other information about the column
Comment - It gives a brief description about the column (Trust me, if you find a value in comments column, your seniors are saints and you are in paradise ;-) )

Data Types

People who are familiar with any programming language can understand the datatypes in the MySQL even without proceeding further. I will give a small introduction of data types

Numeric - Bit, Int, Tinyint, Smallint, Bigint, Decimal, Float, Double (qualifier signed/unsigned available)

String - Char, Varchar, Text

Date & Time - Date, Time, DateTime, Timestamp

Any binary data - Blob

Enumerator - Enum (its used to map with a pre defined set of values, like sex, whose set of possible values is well defined as Male/Female).

A small note on char and varchar. In case both cases, it you can specify the maximum required space required. In the case of char, it always reserves that much memory mentioned while in case of varchar it allocates only to save the date contained.

Also other pair of similar datatype is Datetime and Timestamp - both hold a date and time and is displayed as "YYYY-mm-dd hh:ii:ss". They difference is that, datetime just saved the date as string with now validations (and you can even save 2012-13-13 00:00:00) while in timestamp, it internally saved the time elapsed since UNIX timestamp epoch(as an integer)

If you would like learn more on the data type and its range, you can go to http://dev.mysql.com/doc/refman/5.1/en/data-types.html

MySQL for Newbies

Saturday, January 26, 2013

Day one with MySQL