Reviewed MySQL Database Tutorial

MySQL Database Tutorial
What is MySQL?
 MySQL is a database system used on the web
 MySQL is a database system that runs on a server
 MySQL is ideal for both small and large applications
 MySQL is very fast, reliable, and easy to use
 MySQL uses standard SQL
 MySQL compiles on a number of platforms
 MySQL is free to download and use
 MySQL is developed, distributed, and supported by Oracle Corporation
 MySQL is named after co-founder Monty Widenius's daughter: My
The data in a MySQL database are stored in tables. A table is a collection of related data,
and it consists of columns and rows.
Databases are useful for storing information categorically


Introduction: MySQL
The MySQL database package consists of the following:
1. The MySQL server: This is the heart of MySQL. You can consider it a program that stores and
manages your databases.

2. MySQL client programs: MySQL comes with many client programs. The one with which we'll
be dealing a lot is called mysql (note: smallcaps). This provides an interface through which you
can issue SQL statements and have the results displayed.

3. MySQL client Library: This can help you in writing client programs in C. (We won't be
taking about this in our tutorial).


Creating MySQL database on Windows system
1. Invoke the mysql client program by typing mysql at the prompt.
2. The prompt is changed to a mysql> prompt. Type:

create database employees;

(Note: The command ends with a semi-colon).

3. The MySQL server responds with something like:
Query OK, 1 row affected (0.00 sec)

4. This means that you have successfully created the database.

1

, 5. Now, let's see how many databases you have on your system. Issue the following command.

show databases;

The server responds with the list of databases.
+----------------+
| Database |
+-----------------+
| employees |
| mysql |
| test |
+----------------+
3 rows in set (0.00 sec)
Here we have three databases, two created by MySQL during installation and our employees database.
6. To come back to the DOS prompt, type quit at the mysql prompt.

MySQL - Creating tables
In this section of the mysql course we will explore the MySQL commands to create database tables and
selecting the database.

Databases store data in tables. So what are these tables?
In simplest terms, tables consist of rows and columns. Each column defines data of a particular type. Rows
contain individual records.




2

,Consider the following:

Name Age Country Email
Manish Sharma 28 India
John Doe 32 Australia
John Wayne 48 U.S.A.
Alexander 19 Greece

The table above contains four columns that store the name, age, country and email. Each row contains data
for one individual. This is called a record. To find the country and email of Alexander, you'd first pick the
name from the first column and then look in the third and fourth columns of the same row.

A database can have many tables; it is tables that contain the actual data. Hence, we can segregate related
(or unrelated) data in different tables. For our employees database we'll have one table that stores company
details of the employees. The other table would contain personal information.
Let's make the first table.

The SQL command for creating tables looks complex when you view it for the first time. Don't worry if
you get confused, it shall be elaborated later in more detail.

CREATE TABLE employee_data
(
emp_id int unsigned not null auto_increment primary key,
f_name varchar(20),
l_name varchar(20),
title varchar(30),
age int,
yos int,
salary int,
perks int,
email varchar(60)
);

Note: In MySQL, commands and column names are not case-sensitive; however, table and database
names might be sensitive to case depending on the platform (as in Linux). You can thus, use create
table instead of CREATE TABLE.

The CREATE TABLE keywords are followed by the name of the table we want to create,
employee_data. Each line inside the parenthesis represents one column. These columns store the employee
id, first name, last name, title, age, years of service with the company, salary, perks and emails of our
employees and are given descriptive names emp_id, f_name, l_name, title, age, yos, salary, perks and
email, respectively.

Each column name is followed by the column type. Column types define the type of data the column is set
to contain. In our example, columns, f_name, l_name, title and email would contain small text strings, so
we set the column type to varchar, which means varriable characters. The maximum number of
characters for varchar columns is specified by a number enclosed in parenthesis immediately following the
column name. Columns age, yos, salary and perks would contain numbers (integers), so we set the column
type to int.
Our first column (emp_id) contains an employee id.




3

, Let's break it down.
int: specifies that the column type is an integer (a number).
unsigned: determines that the number will be unsigned (positive integer).
not null: specifies that the value cannot be null (empty); that is, each row in the column would have a
value.
auto_increment: When MySQL comes across a column with an auto_increment attribute, it generates a
new value that is one greater than the largest value in the column. Thus, we don't need to supply values for
this column, MySQL generates it for us! Also, it follows that each value in this column would be unique.
primary key: helps in indexing the column that help in faster searches. Each value has to be unique.

Using a database
We've already created our employees database. Now let's start the mysql client program and select our
database. Once at the mysql prompt,(mysql>), issue the command:

SELECT DATABASE();

The system responds with

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

The above shows that no database has been selected. Actually, every time we work with mysql client, we
have to specify which database we plan to use. There are several ways of doing it.

a) Specifying the database name at the start.
type the following at the system prompt:

mysql employees (under Windows)
mysql employees -u manish -p (under Linux)

b) Specifying the database with the USE statement at the mysql prompt:

mysql>USE employees;

c) Specifying the database with \u at the mysql prompt:

mysql>\u employees;

It's necessary to specify the database we plan to use, else MySQL will throw an error.


MySQL tables
Now that we've created our employee_data table, let's check its listing.
Type SHOW TABLES; at the mysql prompt. This should present you with the following
display:




4