The purpose of this tutorial is to introduce some steps how to create a database and some manipulation instructions. The Demo script that I will show in this post is the demo that the Campus Ambassadors use in their tech demos about the RDBMS. So, this tutorial could be useful for user who are interested to learn some MySQL steps by Ambassadors showed examples.
For this tutorial we are going to build a database to store our textbook collection that we have bought during our college years. There are few key properties we want to associate with our books such as title, author, publisher, edition, date of purchase, and maybe also how much we spent on buying that book which is cost. For this book table, we have related to this a list of friends we lent books to. o we also needs a database table that keeps track of all our frends concat information. At mininum, we would need the name, email, AIM, and phone of our friend so that we could track down this person.
In a typical database table, it is a good practice to have a field with a unique key for each entry such that it can be used as a hash key for searching an entry. So our two tables, book and friend, we want add book_id, and friend_id as the primary keys as references or pointers if in other tables we want to associate which books to which friends. For example we can have a third table lending, that keeps track of which friends friend_id, borrowed which book book_id, on what date lend_date. Below are the three tables summaries our database design with sample entries.
Table of book
book_id |
title |
author |
publisher |
edition |
date |
Cost |
1 |
Digital Integrated Circuits
|
Rabaey
|
Prentice Hall
|
2
|
3/12/2006 |
110.32
|
2 |
Compliers: Principles, Techniques and Tools
|
Aho
|
Addision Wesley
|
2
|
6/23/2007
|
80.54
|
3 |
Introduction to Algorithms
|
Cormem
|
MIT Press
|
2
|
5/10/2005
|
85.00
|
4 |
Design of Analog CMOS IC
|
Razavi
|
McGrew-Hill Science
|
2
|
5/10/2005
|
154.20
|
5 |
Analysis and Design of Analog Integrates Circuits
|
Gray
|
Wiley
|
4
|
5/10/2005
|
118.64
|
6 |
MySQL Cookbook
|
DuBois
|
O' Reilly Media
|
2
|
3/10/2007
|
49.99 |
Table of friends
friend_id
|
Name
|
email
|
AIM
|
Phone
|
1
|
Franz Beckenbauer
|
beckenbauer-AT-bayernmunchen-DOT-com
|
kaiser
|
312-432-534-1324
|
2
|
Michel Platini
|
platini-AT-uefa-DOT-com
|
platini
|
432-534-123-5432
|
3
|
Diego Maradona
|
maradona-AT-afa-DOT-com
|
elpibe
|
321-145-123-9786
|
4
|
Zico
|
zico-AT-cskamoscou-DOT-com
|
galinho
|
123-321-243-54
|
Table of lending
lend_id |
friend_id
|
book_id
|
date
|
1
|
1 |
3 |
1/12/2008
|
2 |
1 |
4 |
3/23/2008 |
3 |
2 |
2 |
5/21/2008 |
4 |
4 |
1 |
2/23/2008 |
5 |
5 |
6 |
4/10/2008 |
Using MySQL Database
This part of the tutorial, screenshot will show how to manipulate the information listed in the last lines.
1) First of all, login MySQL as root and create the database DEMO.
2) Create a table friend, a first version without id, and tip SHOW TABLE < ;table_name> ;.
3) To drop a table, just tip DROP TABLE < number_of_table > ; .
4) Now, let´s create the tables with primary keys:
Syntax: CREATE TABLE < table_name > (< variable1 > ,..., < variable_n > ) VALUES(< value1 > , .. < value_n > , PRIMARY KEY(< id > ));
4.1) Creation of table Friend, with fid variable as primary key:
4.2) Tip DESCRIBE TABLE < table_name > to describe the table created.
4.3) Let's do the same for tables lending and book:
5) Now, let's insert some information in the database. We will use the follow syntax:
INSERT INTO < table_name > (< variable-1 > ,..< variable-n > ) VALUES(< value-1 > ,..., < value-n > ) ;
5.1)Inserting values in book table:
5.2)Inserting values in friend table:
5.3)Inserting values in lending table:
6) Select syntax.
Here, I am not list the SELECT syntax, because you can combine it with many clauses. The next topic, a list with many SQL syntax will be shown.
Below, you just see some select syntax examples:
6.1) Let's list all information in lending table.
6.2) Let's say we want to find all the expensive books cost more than $100.
6.3) Now, let's say we want to find who borrow our books. To do this, we need to query the lending table to find all friend_ids that borrowed our books and match that the friend_ids in our friends contact list. To do that we can use the SELECT command with JOIN table.
7) Now, let's see an example with UPDATE clause:
Basic syntax: UPDATE < table_name > ; SET < variable> = < new_value > [WHERE < condition > ] ;
7.1) Let's update the cost of book with id=1.
After changing:
8) To finalize our first short tutorial, let's see how we use the basic syntax DELETE.
Basic syntax: DELETE FROM < table_name > [WHERE < condition >];
8.1) To demonstrate it, let's dela row of book table where the book's cost is 200.90.
After delete the row:
That concludes our first short tutorial in MySQL. Next topic related to the RDBMS, a list of many SQL syntax will be posted.
