Introduction


Many of the applications that a Web developer wants to use can be made easier by the use of a standardized database to store, organize, and access information. MySQL is an Open Source (GPL) Standard Query Language (SQL) database that is fast, reliable, easy to use, and suitable for applications of any size. SQL is the ANSI-standard database query language used by most databases (though all have their nonstandard extensions).

MySQL can easily be integrated into Perl programs by using the Perl DBI (DataBase Independent interface) module. DBI is an Application Program Interface (API) that allows Perl to connect to and query a number of SQL databases (among them MySQL, mSQL, PostgreSQL, Oracle, Sybase, and Informix).

What is a Database?

A database is a structure that comes in two flavors: a flat database and a relational database. A relational database is much more oriented to the human mind and is often preferred over the gabble-degook flat database that are just stored on hard drives like a text file. MySQL is a relational database. In a relational structured database there are tables that store data. The columns define which kinds of information will be stored in the table.

Why Use a Database?

Databases are most useful when it comes to storing information that fits intological categories. For example, say that you wanted to store information of all the employees in a company. With a database you can group different parts of your business into separate tables to help store your information logically. Example tables might be: Employees, Supervisors, and Customers. Each table would then contain columns specific to these three areas. To help store information related to each employee, the Employees table might have the following columns: Hire, Date, Position, Age, and Salary.

MySQL Installation


If you want to install MySQL on Windows environment, using MySQL installer is the easiest way. MySQL installer provides you with an easy-to-use wizard that helps you to install MySQL with the following components:

MySQL Server

All Available Connectors

MySQL Workbench with Sample Data Models

MySQL Notifier

Tools for Excel and Microsoft Visual Studio

MySQL Sample Database

MySQL Documentation

To download MySQL installer, go to the following link http://dev.mysql.com/downloads/installer/. There are two files are available. If you are connecting to the internet while installing MySQL, you can choose the online installation version mysql-installer-web-community.exe. If you want to install MySQL offline, you can download the mysql-installer-community.exe file.

Install MySQL via MySQL Installer

To install MySQL using the MySQL installer, double click on the MySQL installer file and follow the steps below:

Install MySQL Step 1: Windows configures MySQL Installer

Install MySQL Step 2 – Welcome Screen: A welcome screen provides several options. Choose the first option: Install MySQL Products

Install MySQL Step 3 – Download the latest MySQL products : MySQL installer checks and downloads the latest MySQL products including MySQL server, MySQL Workbench,etc.

Install MySQL Step 4: Click Next button to continue

Install MySQL Step 5 – Choosing a Setup Type: there are several setup types available. Choose the Full option to install all MySQL products and features.

Install MYSQL Step 6 – Checking Requirements

Install MySQL Step 7 – Installation Progress: MySQL Installer downloads all selected products. It will take a while, depending on which products that you selected and the speed of your internet connection.

Install MySQL Step 7 – Installation Progress : downloading Products in progress

Install MySQL Step 7 – Installation Progress : Complete Downloading. Click Next button to continue….



MySQL Connect

Before you can do anything with MySQL in PHP you must first establish a connection to your webhost's MySQL database. This is done with the MySQL connect function.

When the PHP script and MySQL are on the same machine, you can use localhost as the address you wish to connect to. localhost is a shortcut to just have the machine connect to itself. If yourMySQL service is running at a separate location you will need to insert the IP address or URL in place of localhost. Please contact your web host for more details if localhost does not work.

  1. <?php
  2. mysql_connect("localhost", "admin", "password") or die(mysql_error());
  3. echo "Connected to MySQL<br />";
  4. ?>
  5.  

If you load the above PHP script to your webserver and everything works properly,then you should see "Connected to MySQL" displayed when you view the .php page

The mysql_connect function takes three arguments.Server,username, and password. In our example above these arguments were:

Server - localhost

Username - admin

Password - password

The "or die(mysql..." code displays an error message in your browser if --you've probably guessed it -- there is an error in processing the connection! Double-check your username, password, or server if you receive this error.

Creating table


In order to create a new table within a database, you use the MySQL CREATE TABLE statement. The CREATE TABLE statement is one of the most complex statement in MySQL.

The following illustrates the syntax of the CREATE TABLE statement in the simple form:.

  1. CREATE TABLE [IF NOT EXISTS] table_name(
  2.         column_list
  3.         ) engine=table_type

To define a column for the table in the CREATE TABLE statement, you use the following syntax:

  1. column_name data_type[size] [NOT NULL|NULL] [DEFAULT value] 
  2. [AUTO_INCREMENT]

The most important components of the syntax above are:

The column_name specifies the name of the column. Each column associates with a specific data type and the size e.g., VARCHAR(255).

The NOT NULL or NULL indicates that the column accepts NULL value or not.

The DEFAULT value is used to specify the default value of the column:

The AUTO_INCREMENT indicates that the value of column is increased automatically whenever a new row is inserted into the table. Each table has one and only one AUTO_INCREMENT column.

If you want to set particular columns of the table as the primary key, you use the following syntax:

  1. PRIMARY KEY (col1,col2,...)

Examples:


  1. CREATE TABLE IF NOT EXISTS tasks (
  2.   task_id INT(11) NOT NULL AUTO_INCREMENT,
  3.   subject VARCHAR(45) DEFAULT NULL,
  4.   start_date DATE DEFAULT NULL,
  5.   end_date DATE DEFAULT NULL,
  6.   description VARCHAR(200) DEFAULT NULL,
  7.   PRIMARY KEY (task_id)
  8. ) ENGINE=InnoDB

Data Types

A database table contains multiple columns with specific data types such as numeric or string. MySQL provides more data types other than just numeric or string. Each data type in MySQL can be determined by the following characteristics:

The kind of values it represents.

The space that takes up and whether the values are fixed-length or variable-length.

The values of the data type can be indexed or not.

How MySQL compares the values of a specific data type.


Numeric Data Types

You can find all SQL standard numeric types in MySQL including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addition, MySQL also supports BIT data type for storing bit field values. Numeric types can be signed or unsigned except the BIT type. The following table shows you the summary of numeric types in MySQL:

Numeric TypesDescription
TINYINT A very small integer
SMALLINTA small integer
MEDIUMINT A medium-sized integer
INT A standard integer
BIGINT A large integer
DECIMAL A fixed-point number
FLOAT A single-precision floating-point number
DOUBLE A double-precision floating-point number
BIT A bit field

String Data Types


In MySQL, string can hold anything from plain text to binary data such as images and files. String can be compared and searched based on pattern matching by using the LIKE operator or regular expression. The following table shows you the string data types in MySQL:

String TypesDescription
CHAR A fixed-length non-binary (character) string
VARCHARA variable-length non-binary stringr
BINARY A fixed-length binary string
VARBINARY A variable-length binary string
TINYBLOBA very small BLOB (binary large object)
BLOBA small BLOB
MEDIUMBLOBA medium-sized BLOB
LONGBLOBA large BLOB
TINYTEXTA very small non-binary string
TEXT A small non-binary string
MEDIUMTEXT A medium-sized non-binary string
LONGTEXT A large non-binary string
ENUM An enumeration; each column value may be assigned one enumeration member
SETA set; each column value may be assigned zero or more set members

Drop tables


The MySQL DROP TABLE statement allows you to remove or delete a table from the MySQL database

In its simplest form, the syntax for the DROP TABLE statement in MySQL is:

  1. DROP TABLE table_name;

However, the full syntax for the MySQL DROP TABLE statement is:

  1. DROP [ TEMPORARY ] TABLE [ IF EXISTS ]
  2. table_name1, table_name2, ...
  3. [ RESTRICT | CASCADE ];

Parameters or Arguments


TEMPORARY

Optional. It specifies that only temporary tables should be dropped by the DROP TABLE statement.

table_name

The name of the table to remove from the database.

table_name1, table_name2

The tables to remove from the database, if removing more than one table in the DROP TABLE statement.

IF EXISTS

Optional. If specified, the DROP TABLE statement will not raise an error if one of the tables does not exist.

RESTRICT

Optional. It has no impact or effect on the DROP TABLE statement but is included in the syntax to make porting the tables to different databases easier.

CASCADE

Optional. It has no impact or effect on the DROP TABLE statement but is included in the syntax to make porting the tables to different databases easier.

SELECT


The MySQL SELECT statement allows you to retrieve zero or more rows from tables or views. The SELECT statement is the one of the most commonly used queries in MySQL.

The SELECT statement returns a result that is a combination of columns and rows, which is also known as a result set.

MySQL SELECT syntax

The following illustrates the syntax of the SELECT statement:

  1. SELECT column_1,column_2...
  2. FROM table_1
  3. [INNER | LEFT |RIGHT] JOIN table_2 ON conditions
  4. WHERE conditions
  5. GROUP BY group 
  6. HAVING group_conditions
  7. ORDER BY column_1 [ASC | DESC]
  8. LIMIT offset, row_count

The SELECT statement is composed of several clauses:

SELECT chooses which columns of the table you want to get the data.

FROM specifies the table from which you get the data.

JOIN gets data from multiple table based on certain join conditions.

WHERE filters rows to select.

GROUP BY group rows to apply aggregate functions on each group.

HAVING filters group based on groups defined by GROUP BY clause.

ORDER BY specifies the order of the returned result set.

LIMIT constrains number of returned rows.

insert


The MySQL INSERT statement allows you to insert data into tables. The following illustrates the syntax of the INSERT statement:

  1. INSERT INTO table(column1,column2...)
  2. VALUES (value1,value2,...

First, after the INSERT INTO, you specify the table name and a list of comma-separated columns inside parentheses. Then you put a comma-separated values of the corresponding columns inside parentheses followed the VALUES keyword

You need to have the INSERT privilege to use the INSERT statement.

Let’s create a new table named tasks for practicing the INSERT statement.

  1. CREATE TABLE IF NOT EXISTS tasks (
  2.   task_id int(11) NOT NULL AUTO_INCREMENT,
  3.   subject varchar(45) DEFAULT NULL,
  4.   start_date DATE DEFAULT NULL,
  5.   end_date DATE DEFAULT NULL,
  6.   description varchar(200) DEFAULT NULL,
  7.   PRIMARY KEY (task_id)
  8. )

if you want to insert a new task into the tasks table, you use the INSERT statement

  1. INSERT INTO tasks(subject,start_date,end_date,description)
  2. VALUES('Learn MySQL INSERT','2010-01-01','2010-01-02','Start learning..')

UPDATE


The UPDATE statement is used to update existing data in tables. It can be used to change column values of a single row, a group of rows or all rows in a table.

The following illustrates the MySQL UPDATE statement syntax:

  1.  UPDATE [LOW_ PRIORITY] [IGNORE] TABLE_NAME [, TABLE_NAME...] 
  2.  SET column_name1 = expr1 
  3. [, column_name2=expr2 ...] 
  4. [WHERE condition]

Let’s examine the UPDATE statement in greater detail:

Followed by the UPDATE keyword is the name of the table that you want to update data. In MySQL, you can change the data of multiple tables using a single UPDATE statement. If the UPDATE statement violates any integrity constraint, MySQL does not perform the update and issues an error message.

The SET clause determines the column names of the table and the new values. The new values could be literal values, result of expressions or subqueries.

The WHERE clause determines which rows will be updated. It is an optional element of the UPDATE statement. If the WHERE clause is omitted, all rows in the table will be updated. The WHERE clause is so important that you should not forget. Sometimes, you may want to change just one row of the table; if you forget the WHERE clause, the UPDATE statement will update all the rows, which is not what you expected.

The LOW_PRIORITY keyword is used to delay the execution until no other connections read data from the table. It is used for controlling the update process in MySQL database server.

The IGNORE keyword is used to execute the update even there is an error occurred during the execution of the UPDATE statement. The error in the update process could be duplicate value on a unique column, the new value does not match with the column’s data type, etc.

Delete

To remove data from a table, you use the MySQL DELETE statement. The MySQL DELETE statement allows you to remove records from not only one table but also multiple tables using a single DELETE statement.

MySQL delete from one table

To remove data from a single table, you use the following DELETE statement:

  1. DELETE FROM TABLE   
  2. [WHERE conditions] [ORDER BY ...] [LIMIT ROWS]

Followed the DELETE FROM clause is the table name that you want to delete records.

The WHERE clause specifies which rows you want to delete. If a record meets the WHERE condition, it is deleted permanently from the table. If you omit the WHERE clause, all records in the table are deleted.

The DELETE statement returns the number of rows deleted specified by the ROW_COUNT() function.

Suppose you want to remove employees whose officeNumber is 4, you use the DELETE statement with the WHERE clause as the following query:

  1. DELETE FROM employees 
  2. WHERE officeCode = 4

To delete all employee records from the employees table, you use the DELETE statement without the WHERE clause as follows:

DELETE FROM employees

All employee records in the employees table were deleted.

ORDER BY


learn about various natural sorting techniques in MySQL by using the ORDER BY clause.

Let’s start the tutorial with sample data.

Suppose we have a table named items that contains two columns: id and item_no. To create items table we use the CREATE TABLE statement as follows:

  1. CREATE TABLE IF NOT EXISTS items(
  2.  id INT AUTO_INCREMENT PRIMARY KEY,
  3.  item_no VARCHAR(255) NOT NULL
  4. )

We use the INSERT statement to insert some data into the items table:

  1. INSERT INTO items(item_no)
  2. VALUES ('1'),
  3.        ('1C'),
  4.        ('10Z'),
  5.        ('2A'),
  6.        ('2'),
  7.        ('3C'),
  8.        ('20D');

When we select data and sort it by item_no, we get the following result:

  1. SELECT item_no
  2. FROM items
  3. ORDER BY item_no;

To overcome this, first we split the item_no column into 2 columns: prefix and suffix. The prefix column stores the number part of the item_no and suffix column stores the alphabetical part. Then, we can sort the data based on these columns as the following query:

  1. SELECT CONCAT(prefix,suffix)
  2. FROM items
  3. ORDER BY prefix, suffix

The query sorts data numerically first and sort the data alphabetically then. We get the expected result.

The disadvantage of this solution is that we have to break the item_no into two parts before you insert or update it. In addition, we have to combine two columns into one when we select the data.

If the item_no data is in fairly standard format, you can use the following query to perform natural sorting without changing the table structure.

  1. SELECT item_no
  2. FROM items
  3. ORDER BY CAST(item_no AS  UNSIGNED), item_no;

In this query, first we convert item_no data into unsigned integer by using the CAST function. Second, we use the ORDER BY clause to sort the rows numerically first and alphabetically then.

Let’s take a look at other common set of data that we often have to deal with

  1. TRUNCATE TABLE items;
  2.  
  3. INSERT INTO items(item_no)
  4. VALUES('A-1'),
  5.       ('A-2'),
  6.       ('A-3'),
  7.       ('A-4'),
  8.       ('A-5'),
  9.       ('A-10'),
  10.       ('A-11'),
  11.       ('A-20'),
  12.       ('A-30');

Table Join

“JOIN” is an SQL keyword used to query data from two or more related tables. Unfortunately, the concept is regularly explained using abstract terms or differs between database systems. It often confuses me. Developers cope with enough confusion, so this is my attempt to explain JOINs briefly and succinctly to myself and anyone who’s interested.

Related Tables

MySQL, PostgreSQL, Firebird, SQLite, SQL Server and Oracle are relational database systems. A well-designed database will provide a number of tables containing related data. A very simple example would be users (students) and course enrollments:

IDNAMECourse
1ravi 1
2raju2
3 ramesh3
4 ramu1
5srinunull

MySQL table creation code

  1. CREATE TABLE `user` (
  2. `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  3. `name` varchar(30) NOT NULL,
  4. `course` smallint(5) unsigned DEFAULT NULL,
  5. PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB;

The course number relates to a subject being taken in a course table

IDCourse
1html
2css
3 php
4 mysql
5javascript

  1. CREATE TABLE `course` (
  2. `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  3. `name` varchar(50) NOT NULL,
  4. PRIMARY KEY (`id`)
  5. ) ENGINE=InnoDB;

Since we’re using InnoDB tables and know that user.course and course.id are related, we can specify a foreign key relationship:

  1. ALTER TABLE `user`
  2. ADD CONSTRAINT `FK_course`
  3. FOREIGN KEY (`course`) REFERENCES `course` (`id`)
  4. ON UPDATE CASCADE;

INNER JOIN (or just JOIN)

The most frequently used clause is INNER JOIN. This produces a set of records which match in both the user and course tables, i.e. all users who are enrolled on a course:

  1. SELECT user.name, course.name
  2. FROM `user`
  3. INNER JOIN `course` on user.course = course.id;

LEFT JOIN

What if we require a list of all students and their courses even if they’re not enrolled on one? A LEFT JOIN produces a set of records which matches every entry in the left table (user) regardless of any matching entry in the right table (course):

  1. SELECT user.name, course.name
  2. FROM `user`
  3. LEFT JOIN `course` on user.course = course.id;

RIGHT JOIN

Perhaps we require a list all courses and students even if no one has been enrolled? A RIGHT JOIN produces a set of records which matches every entry in the right table (course) regardless of any matching entry in the left table (user):

  1. SELECT user.name, course.name
  2. FROM `user`
  3. RIGHT JOIN `course` on user.course = course.id;

RIGHT JOINs are rarely used since you can express the same result using a LEFT JOIN. This can be more efficient and quicker for the database to parse:

  1. SELECT user.name, course.name
  2. FROM `course`
  3. LEFT JOIN `user` on user.course = course.id;

OUTER JOIN

Our last option is the OUTER JOIN which returns all records in both tables regardless of any match. Where no match exists, the missing side will contain NULL.

OUTER JOIN is less useful than INNER, LEFT or RIGHT and it’s not implemented in MySQL. However, you can work around this restriction using the UNION of a LEFT and RIGHT JOIN, e.g.

  1. SELECT user.name, course.name
  2. FROM `user`
  3. LEFT JOIN `course` on user.course = course.id
  4.  
  5. UNION
  6.  
  7. SELECT user.name, course.name
  8. FROM `user`
  9. RIGHT JOIN `course` on user.course = course.id;

ALTER

MySQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table.

The MySQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The MySQL ALTER TABLE statement is also used to rename a table.

The syntax to add a column in a table in MySQL (using the ALTER TABLE statement) is:

  1. ALTER TABLE table_name
  2.   ADD new_column_name column_definition
  3.     [ FIRST | AFTER column_name ];

MODIFY COLUMN IN TABLE

The syntax to modify a column in a table in MySQL (using the ALTER TABLE statement) is:

  1. ALTER TABLE table_name
  2. MODIFY column_name column_definition
  3. [ FIRST | AFTER column_name ];

DROP COLUMN IN TABLE

The syntax to drop a column in a table in MySQL (using the ALTER TABLE statement) is:

  1. ALTER TABLE table_name
  2.   DROP COLUMN column_name;