Automatically reconnect to mysql database if connection is dropped

Automatically reconnect to mysql database if connection is dropped

Posted using ShareThis

shareonthis

http://sharethis.com/

linux awk for occurance count

filename | cut -f24 --delimiter="\""  | awk -F"|" '{if (NF > 50) print NF}'

MySQL Basic Details

1 :: What Is MySQL?

MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com.

2 :: What Is mSQL?

Mini SQL (mSQL) is a light weight relational database management system capable of providing rapid access to your data with very little overhead. mSQL is developed by Hughes Technologies Pty Ltd.

MySQL was started from mSQL and shared the same API.

3 :: What Is SQL?

SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.

4 :: What Is Table?

A table is a data storage structure with rows and columns.

5 :: What Is Column?

A column defines one piece of data stored in all rows of the table. 

6 :: What Is Row?

A row is a unit of data with related data items stored as one item in one column in a table.

7 :: What Is Primary Key?

A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.

8 :: What Is Foreign Key?

A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.

9 :: What Is Index?

An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.

10 :: What Is View?

A view is a logical table defined by a query statement.

11 :: What Is Join?

Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.

12 :: What Is Union?

Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.

13 :: What Is ISAM?

ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.

14 :: What Is MyISAM?

MyISAM is a storage engine used as the default storage engine for MySQL database. MyISAM is based on the ISAM (Indexed Sequential Access Method) concept and offers fast data storage and retrieval. But it is not transaction safe.

15 :: What Is InnoDB?

InnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).

• StumbleUpon
• Digg
• Delicious
• Twitter
• FaceBook
• LinkedIn
• Google
• Yahoo
• MySpace
• Tell Your Friend

16 :: What Is BDB (BerkeleyDB)?

BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).

17 :: What Is CSV?

CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.

18 :: What Is Transaction?

A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

19 :: What Is Commit?

Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.

20 :: What Is Rollback?

Rollback is a way to terminate a transaction with all database changes not saving to the database server.

29 MySQL interview questions

1. How do you start and stop MySQL on Windows? - net start MySQL, net stop MySQL
2. How do you start MySQL on Linux? - /etc/init.d/mysql start
3. Explain the difference between mysql and mysqli interfaces in PHP? - mysqli is the object-oriented version of mysql library functions.
4. What’s the default port for MySQL Server? - 3306
5. What does tee command do in MySQL? - tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.
6. Can you save your connection settings to a conf file? - Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.
7. How do you change a password for an existing user via mysqladmin? - mysqladmin -u root -p password "newpassword"
8. Use mysqldump to create a copy of the database? - mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql
9. Have you ever used MySQL Administrator and MySQL Query Browser? Describe the tasks you accomplished with these tools.
10. What are some good ideas regarding user security in MySQL? - There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.
11. Explain the difference between MyISAM Static and MyISAM Dynamic. - In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.
12. What does myisamchk do? - It compressed the MyISAM tables, which reduces their disk usage.
13. Explain advantages of InnoDB over MyISAM? - Row-level locking, transactions, foreign key constraints and crash recovery.
14. Explain advantages of MyISAM over InnoDB? - Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
15. What are HEAP tables in MySQL? - HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
16. How do you control the max size of a HEAP table? - MySQL config variable max_heap_table_size.
17. What are CSV tables? - Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
18. Explain federated tables. - Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
19. What is SERIAL data type in MySQL? - BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
20. What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table? - It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
21. Explain the difference between BOOL, TINYINT and BIT. - Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
22. Explain the difference between FLOAT, DOUBLE and REAL. - FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
23. If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table? - 999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.
24. What happens if a table has one column defined as TIMESTAMP? - That field gets the current timestamp whenever the row gets altered.
25. But what if you really want to store the timestamp data, such as the publication date of the article? - Create two columns of type TIMESTAMP and use the second one for your real data.
26. Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP - The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.
27. What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do? - On initialization places a zero in that column, on future updates puts the current value of the timestamp in.
28. Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44′ ON UPDATE CURRENT_TIMESTAMP. - A default value is used on initialization, a current timestamp is inserted on update of the row.
29. If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table? - CHAR(3), since MySQL automatically adjusted the data type.

Select Random Records in Database Table

In this tip, you will learn various techniques to select random items from a database table in MySQL.
MySQL does not have any automatic way to select random items from a database table. In some programming tasks, it is required and very useful to select random items from a result set such as:

• You'll need to select a random picture to display it for a category, so each time when the visitor come to your website, they will see different pictures displaying on the categories.
• You'll need to select a random banner to display to visitors.
• You'll need to pick a row in "quote of the day" application.
• And more and more cases you can think of...

In order to achieve this result, MySQL allows to do that via RAND function. To select a random value from a result set you perform the following query:

SELECT * FROM table  ORDER BY RAND()  LIMIT 1 

The key technique used above is randomizing the returned records and then pick the first one. If you want to select n random items just change the parameter after LIMIT as follows:

SELECT * FROM table  ORDER BY RAND()  LIMIT n

The technique as demonstrated above work very well with the small table. With the table which has many records, it could be very slow because we have to sort the entire table to pick random items. To work around this problem, we will use another technique as demonstrated below:

1. First we select a random ID(s) of a column. This column should be the primary key and the value is in sequential range.
2. Then pick the rows based on the ID(s) we selected

The SQL script for doing this is as follows:

SET @ID = FLOOR(RAND( )* N) + 1; 
 SELECT *  FROM table  WHERE ID >= @ID LIMIT 1  

This technique work faster because the ID column is indexed and we don't have to order the whole table as previous one.

MySQL Copy Database Table

In this tutorial, you will learn how to copy data from one table into a new table by using SQL CREATE TABLE and SELECT statement. Copying data from an existing table to a new one is useful in some cases such as backing up data, create a copying of real data for testing. In order to copy data from one table to a new one you can use the following command:

CREATE TABLE new_table 
 SELECT *  FROM existing_table 

MySQL will first create a new table with name as indicated after CREATE TABLE statement, new_table in this case. Then it will fill the new table with all the data from an existing table (existing_table).
To copy a part of data from an existing table, you can use WHERE clause to filter the selected data base on conditions. The command is as follows:

CREATE TABLE new_table 
SELECT *  FROM existing_table WHERE conditions

It is very important to check whether table you want to create is existed or not, you should use IF NOT EXIST after CREATE TABLE statement. The full sql command of copying data from an existing table to a new one will be as follows:

CREATE TABLE IF NOT EXISTS new_table 
 SELECT *  FROM existing_table 
 WHERE conditions 

Here is the example of using copying data command. We have Office data table, now we can copy the table from this table into a new one by using the following command:

CREATE TABLE IF NOT EXISTS offices_bk 
 SELECT * FROM offices 

If we need only copy all offices in US, so we can use WHERE condition for it as follows:

CREATE TABLE IF NOT EXISTS offices_usa 
 SELECT *  FROM offices 
 WHERE country = 'USA' 

Maintaining Database Tables

MySQL provides a bunch of statements to allow you to maintain database table more efficiently. Those statements enable you to analyze, optimize, check, and repair the database table. Here you will learn them all together with examples.

Analyze table statement

Basically analyze table statement allow you to update cardinality of an index column. By updating cardinality, you can retrieve the database faster by utilizing all index features of database tables.
You will work with the employees and offices table in our sample database. Let’s follow the example bellow to understand more how analyze table statement works.
We can get the indexes information from employees table by executing the show index statement as follows:

SHOW INDEX FROM employees

MySQL gives you quite a lot of information about index in the employee table including cardinality; in this case the cardinality is 23.
Now we create a new index in the officeCode column to allow us to retrieve office and employee information by using join statement faster.

ALTER TABLE employees 
ADD INDEX employee_offices (officeCode)

At this time the cardinality of the index is not updated, we can see this by performing the show index statement on employees table again.

SHOW INDEX FROM employees

The cardinality now is NULL.
So the cardinality of index is not updated automatically when index created and a new record is inserted to the table. In this case, we can use analyze table statement to make an update on cardinality of the index column. By executing the following query:

ANALYZE TABLE employees

And perform the show index statement on the table employees again you can see that the cardinality of indexed column employeeNumber and officeCode are updated.

Optimize table statement

While working with the database, you do a lot of changes such as insert, update and delete data in the database tables therefore it causes the physical of database table fragmented. So the performance of database server is also degraded. MySQL provides you optimize table statement to allow you to optimize the database table to avoid this problem by defragmenting the table in physical level. The optimize table statement is as follows:

OPTIMIZE TABLE table_name

With this optimize table statement, you should run it often with the tables which updated frequently.
Suppose you want to optimize the employees table to make it defragmented, you can perform the following query:

OPTIMIZE TABLE employees

Here is the output

Table                    Op        Msg_type  Msg_text
-----------------------  --------  --------  --------
classicmodels.employees  optimize  status    OK      

Check table statement

Something wrong can happen to the database server such as server turn off unexpectedly, error while writing data to the hard disk and so on… all of these situations could make the database operate incorrectly and for the worst thing it can be crashed.MySQL supports you to check database table by using check table statement. Here is the syntax:

CHECK TABLE table_name

The check table statements above check both table and correspondent indexes. For example, you can you check table statement to check the table employees as follows:

CHECK TABLE employees

And here is the output

Table                    Op      Msg_type  Msg_text
-----------------------  ------  --------  --------
classicmodels.employees  check   status    OK      

Check table statement only detect problems in a database table but it does not repair them. In order to do so you can you repair table statement.

Repair table statement

Repair table statement allows you to repair some errors occurred in database tables. MySQL does not guarantee that this statement can repair all errors which your database may have. The repair table statement can be written as follows:

REPAIR TABLE table_name

Suppose you have some errors in the employees table and need to fix, you can use repair table statement by performing this query:

REPAIR TABLE employees

MySQL will return what it has done with the table and the table is repaired or not. Here is the output you always want to see in such cases:

Table                    Op      Msg_type  Msg_text
-----------------------  ------  --------  --------
classicmodels.employees  repair  status    OK      

Deleting Data from Datatable Tables

To remove a data row or all rows from a table you can use SQL DELETE statement. The syntax of SQL DELETE statement in MySQL is as follows:

DELETE [LOW_PRIORITY] [QUICK]   
 FROM table_name   
[WHERE conditions] [ORDER BY ...] [LIMIT rows]

DELETE [LOW_PRIORITY] [QUICK]  
  table_name[.*] [, table_name[.*] ...]   
 FROM table-references   
 [WHERE where_definition] 

DELETE [LOW_PRIORITY] [QUICK] 
  FROM table_name[.*] [, table_name[.*] ...]   
  USING table-references   
  [WHERE where_definition]

In the first from of DELETE statement, followed the DELETE FROM is the table name you want to delete data. WHERE clause specifies which rows to remove. If the WHERE clause is ignored in the DELETE statement, all rows from table is removed.
The second form of DELETE statement, MySQL allows us to delete row(s) from multiple tables with references to other table.
The third form of DELETE statement is quite similar to the second form except that instead of FROM keyword it uses USING keyword.
Let's have some example of using SQL DELETE statement with the sample database. It is recommended that you make a copy of employee table before practice with delete statement. If you want to delete all employees in an office with officeNumber is 4, just execute this query:

DELETE FROM employees 
 WHERE officeCode = 4  

To delete all employees, just ignore the WHERE clause:

DELETE FROM employees 

It will remove all rows from employees table.
If you want to remove all employees, in employees table, who work in office with the officeCode is 1 and also the office, just use the second form of DELETE statement:

DELETE employees,offices 
 FROM employees,offices 
 WHERE employees.officeCode = offices.officeCode  AND 
       offices.officeCode = 1 

You can of course achieve the same above result by using the third form of DELETE statement

DELETE FROM employees,offices 
 USING employees,offices 
 WHERE employees.officeCode = offices.officeCode  AND 
       offices.officeCode = 1 

Updating Data in Database Tables

SQL UPDATE statement is used to update existing data in a data table. It can be used to change values of single row, group of rows or even all rows in a table. In MySQL, the SQL UPDATE statement form is as follows:

UPDATE [LOW_ PRIORITY] [IGNORE] table_name [, table_name...] 
 SET column_name1=expr1 
     [, column_name2=expr2 ...] 
[WHERE condition] 

The UPDATE keyword is followed by the name of a table you want to change data. In MySQL, you can change the data of many tables at a time. If an UPDATE statement violates an integrity constraint, MySQL does not perform the update and it will return an error message.
The SET clause determines the column(s) and the changed values. The changed values could be a constant value expression or even a subquery.
WHERE clause determines which rows of the tables will be updated. It is an optional part of SQL UPDATE statement. If WHERE clause is ignored, all rows in the tables will be updated. In some cases, you should take care about unexpected situation when working with SQLUPDATE statement. Sometime, you want to change just one row of a table but you forget WHERE clause so you accidentally update the whole table.
LOW_ PRIORITY keyword is used to delay the execution until no other client applications reading data from the table.
IGNORE keyword is used to execute the update even error(s) can be occurred during execution. Errors could be duplicated value on unique column, or new data does not match with the column data type. In the first situation data are not updated and in the second oneMySQL try to convert the data into closest valid values.
Let's practice with SQL UPDATE statement in our sample database. In employees table, if you want to update the email of Mary Patterson with employeeNumber 1 with the new email as mary-patterso@classicmodelcars.com, you can execute this query:
Before updating

SELECT firstname,
        lastname,
        email 
 FROM employees 
 WHERE employeeNumber = 1 

+-----------+-----------+--------------------------------+
| lastname  | firstname | email                          |
+-----------+-----------+--------------------------------+
| Patterson | Mary      | mpatterso@classicmodelcars.com |
+-----------+-----------+--------------------------------+
1 row in set (0.02 sec)

Update her email to the new email as mary-patterso@classicmodelcars.com

UPDATE employees 
 SET email = 'mary-patterso@classicmodelcars.com'  
 WHERE employeeNumber = 1 

Execute the select query above again; you will see the email change to

+-----------+-----------+------------------------------------+
| lastname  | firstname | email                              |
+-----------+-----------+------------------------------------+
| Patterson | Mary      | mary-patterso@classicmodelcars.com |
+-----------+-----------+------------------------------------+
1 row in set (0.00 sec)

Inserting Data into Database Tables

In this tutorial, you will learn how to insert data into database tables by using SQL INSERT statement.

INSERT Statement

INSERT statement allows you to insert one or more rows to the table. In MySQL, the INSERT statement form is listed as follows:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] table_name [(column_name,...)]
        VALUES ((expression | DEFAULT),...),(...),...
        [ ON DUPLICATE KEY UPDATE column_name=expression, ... ]

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] table_name [(column_name,...)]
        SELECT statement

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]         
 [INTO] table_name         
 SET column_name=(expression | DEFAULT), ...         
 [ ON DUPLICATE KEY UPDATE column_name=expression, ... ]

As you can see INTO in the INSERT statement is optional. In the first form, you insert a new data row into an existing table by specifying the column name and data for each. As an example to insert a new office to the offices table in the sample database you can do as follows:

INSERT INTO classicmodels.offices 
 (officeCode, 
 city, 
 phone, 
 addressLine1, 
 addressLine2, 
 state, 
 country, 
 postalCode, 
 territory
 )
 VALUES
 ('8', 
 'Boston', 
 '+1 215 837 0825', 
 '1550 dummy street', 
 'dummy address', 
 'MA', 
 'USA', 
 '02107', 
 'NA'
 )

In the second form, instead of providing explicit data, you select it from other table by using SELECT statement. This form allows you to copy some or some part of data from other table to the inserted table. As an example, we can create a temporary table andinsert all offices which locate in US into that one by using this query:

INSERT INTO temp_table 
 SELECT * FROM offices WHERE country = 'US'  

The third form enables you to specify the column you want to insert the data. For example, we have the query like this:

INSERT INTO productlines  
 SET productLine = 'Luxury Cars' 

It means we only insert the data into productLine column in productLines table.

Aggregate Functions in MySQL

In some cases, the information we need is not actually stored in the database but we can retrieve it by computing in some ways from stored data. For example, we have OrderDetails table to store order data, we don't know total of money of all selling products when we look at the table. In order to do so, we can use aggregate functions. By definition, aggregate functions allow us to perform a calculation on a set of records and return a single returned value. Aggregate functions ignore null value when performing calculation except COUNT function. Aggregate functions are often used with GROUP BY clause of SELECT statement. Here are aggregate functions which MySQL supports:

SUM Function

SUM function returns the sum of all values in an expression.
Let's practice with OrderDetails table by following examples:
To get the total money for each selling product we just use the SUM function and group by product. Here is the query:

SELECT productCode,sum(priceEach * quantityOrdered) total
FROM orderdetails
GROUP by productCode

productCode    total  
-----------  ---------
S10_1678      90157.77
S10_1949     190017.96
S10_2016     109998.82
S10_4698        170686
S10_4757     127924.32
S10_4962     123123.01
S12_1099     161531.48

To see the result more details, we can join the OrderDetails table with Product table.

SELECT P.productCode,
       P.productName,
       SUM(priceEach * quantityOrdered) total
FROM orderdetails O
INNER JOIN products  P ON O.productCode = P.productCode
GROUP by productCode
ORDER BY total

productCode  productName                                    total  
-----------  -------------------------------------------  ---------
S24_1937     1939 Chevrolet Deluxe Coupe                   28052.94
S24_3969     1936 Mercedes Benz 500k Roadster              29763.39
S24_2972     1982 Lamborghini Diablo                       30972.87
S24_2840     1958 Chevy Corvette Limited Edition           31627.96
S32_2206     1982 Ducati 996 R                             33268.76
S24_2022     1938 Cadillac V-16 Presidential Limousine     38449.09
S50_1341     1930 Buick Marquette Phaeton                  41599.24
S24_1628     1966 Shelby Cobra 427 S/C                     42015.54
S72_1253     Boeing X-32A JSF                              42692.53

AVG Function

AVG is used to calculate average value of an expression. It ignores NULL values.

AVG(expression)

We can use AVG function to calculate the average price of all products buy executing the following query.

SELECT AVG(buyPrice) average_buy_price
FROM Products

Here is the result

average_buy_price
-----------------
  54.395181818182

MAX and MIN Function

MAX function returns the maximum and MIN function returns the minimum value of the set of values in expression.

MAX(expression)

MIN(expression)

As an example, we can use MIN and MAX function to retrieve the highest and lowest price product as follows:

SELECT MAX(buyPrice) highest_price,
       MIN(buyPrice) lowest_price
FROM Products

You will get the result

highest_price  lowest_price
-------------  ------------
       103.42         15.91

COUNT Function

COUNT function returns the count of the items in expression. We can use COUNT function to count how many products we have as follows:

SELECT COUNT(*) AS Total
FROM products

Total 
------
   110

For more information on count function check it out here

Learning Advanced Queries: Join and Subqueries

In the previous tutorial, you've learn how to retrieve data from one table by using SELECT statement. But in the real database programming task, you usually does not select data from just one table because of the normalization process (a big table is divided into a subset of smaller tables). So to get the complete data, you need to use SQL JOIN clause inSELECT statement. The normal form of SQL JOIN clause in MySQL is simple follows:

SELECT column_list FROM table_1 
 [INNER | LEFT | RIGHT] table_2 ON conditions_2 
 [INNER | LEFT | RIGHT] table_3 ON conditions_3 ... 
 WHERE conditions 

After SELECT keyword is a list of column name in which you want to retrieve the data. These columns have to be in the tables you select such as table_1, table_2... If the columns' name in those tables are the same, you have to explicit declare the as the form table_name.column_name, otherwise MySQL will returns you an error message to say that the column name you selected is ambiguous. Next you will list the main table and then a listof table you want to join. You can use INNER JOIN, LEFT JOIN or RIGHT JOIN. You can join a table with more than two tables or even with itself. In the JOIN clause you have to declare the join conditions. If all the conditions on each join clause match,MySQL will return the corresponding data.

Using INNER JOIN

INNER JOIN is used to retrieve the data from all tables listed based on condition listed after keyword ON. If the condition is not meet, nothing is returned. For example, see the following tables in our classicmodels sample database. We have employees table and offices table. Two tables are linked together by the column officeCode. To find out who is in which country and state we can use INNER JOIN to join these tables. Here is the SQL code:

SELECT employees.firstname,
       employees.lastname,
       offices.country,
       offices.state
FROM employees
INNER JOIN offices 
  ON offices.officeCode = employees.officeCode

And we will get the data like this:

+-----------+-----------+-----------+------------+
| firstname | lastname  | country   | state      |
+-----------+-----------+-----------+------------+
| Diane     | Murphy    | USA       | CA         |
| Mary      | Patterson | USA       | CA         |
| Jeff      | Firrelli  | USA       | CA         |
| William   | Patterson | Australia | NULL       |
| Gerard    | Bondur    | France    | NULL       |
| Anthony   | Bow       | USA       | CA         |
| Leslie    | Jennings  | USA       | CA         |
| Leslie    | Thompson  | USA       | CA         |
| Julie     | Firrelli  | USA       | MA         |
| Steve     | Patterson | USA       | MA         |
| Foon Yue  | Tseng     | USA       | NY         |
| George    | Vanauf    | USA       | NY         |
| Loui      | Bondur    | France    | NULL       |
| Gerard    | Hernandez | France    | NULL       |
| Pamela    | Castillo  | France    | NULL       |
| Larry     | Bott      | UK        | NULL       |
| Barry     | Jones     | UK        | NULL       |
| Andy      | Fixter    | Australia | NULL       |
| Peter     | Marsh     | Australia | NULL       |
| Tom       | King      | Australia | NULL       |
| Mami      | Nishi     | Japan     | Chiyoda-Ku |
| Yoshimi   | Kato      | Japan     | Chiyoda-Ku |
| Martin    | Gerard    | France    | NULL       |
+-----------+-----------+-----------+------------+
23 rows in set (0.02 sec)

If you want to find only employees in USA, just execute this query:

SELECT e.firstname,
       e.lastname,
       state
FROM employees e
INNER JOIN offices p 
  ON o.officeCode = e.officeCode AND country = 'USA' 

Here is the data back

+-----------+-----------+-------+
| firstname | lastname  | state |
+-----------+-----------+-------+
| Diane     | Murphy    | CA    |
| Mary      | Patterson | CA    |
| Jeff      | Firrelli  | CA    |
| Anthony   | Bow       | CA    |
| Leslie    | Jennings  | CA    |
| Leslie    | Thompson  | CA    |
| Julie     | Firrelli  | MA    |
| Steve     | Patterson | MA    |
| Foon Yue  | Tseng     | NY    |
| George    | Vanauf    | NY    |
+-----------+-----------+-------+
10 rows in set (0.00 sec)

Using LEFT and RIGHT JOIN

LEFT JOIN can be used when you want to retrieve the data from the main table (table1) even if there is no match in other tables (table_2, table_3....). While RIGHT JOIN is used toretrieve the data the from all other tables (table_2, table_3...) even if there is no match in the main table. As an example, in our classicalmodels sample database, when the company want to establish a new office, the SQL script to insert a new office to the databse as follows:

INSERT INTO classicmodels.offices 
 (officeCode, 
         city, 
         phone, 
         addressLine1, 
         addressLine2, 
  state, 
  country, 
  postalCode, 
  territory
 )
VALUES  ('8', 
        'Boston', 
        '+1 215 837 0825', 
        '1550 dummy street', 
         'dummy address', 
 'MA', 
 'USA', 
 '02107', 
 'NA'
 )

At this time, the company hasn't any hire new employee yet so the new office does not have any employee. If we want to know employee who belongs to which office and all the offices of the company, we can use RIGHT JOIN as follows:

SELECT firstname,
        lastname,
 addressLine1 
 FROM employees e
 RIGHT JOIN offices o ON o.officeCode = e.officeCode 

+-----------+-----------+--------------------------+
| firstname | lastname  | addressLine1             |
+-----------+-----------+--------------------------+
| Mary      | Patterson | 100 Market Street        |
| Diane     | Murphy    | 100 Market Street        |
| Jeff      | Firrelli  | 100 Market Street        |
| Anthony   | Bow       | 100 Market Street        |
| Leslie    | Jennings  | 100 Market Street        |
| Leslie    | Thompson  | 100 Market Street        |
| Julie     | Firrelli  | 1550 Court Place         |
| Steve     | Patterson | 1550 Court Place         |
| Foon Yue  | Tseng     | 523 East 53rd Street     |
| George    | Vanauf    | 523 East 53rd Street     |
| Gerard    | Bondur    | 43 Rue Jouffroy D'abbans |
| Loui      | Bondur    | 43 Rue Jouffroy D'abbans |
| Gerard    | Hernandez | 43 Rue Jouffroy D'abbans |
| Pamela    | Castillo  | 43 Rue Jouffroy D'abbans |
| Martin    | Gerard    | 43 Rue Jouffroy D'abbans |
| Mami      | Nishi     | 4-1 Kioicho              |
| Yoshimi   | Kato      | 4-1 Kioicho              |
| William   | Patterson | 5-11 Wentworth Avenue    |
| Andy      | Fixter    | 5-11 Wentworth Avenue    |
| Peter     | Marsh     | 5-11 Wentworth Avenue    |
| Tom       | King      | 5-11 Wentworth Avenue    |
| Larry     | Bott      | 25 Old Broad Street      |
| Barry     | Jones     | 25 Old Broad Street      |
| NULL      | NULL      | 1550 dummy street        |
+-----------+-----------+--------------------------+
24 rows in set (0.00 sec)

As you can see, the RIGHT JOIN get the all the data from second table (offices) and data from the first table even the conditiondoes not match.

Joining a Table to Itself or Self joins

You can also using JOIN clause to join table to itself. Consider the following situation to our sample databse, we want to know who has to report to whom in organization structure. Here is the SQL script:

SELECT concat(e.firstname,',',e.lastname) AS employee,        
       concat(m.firstname,',',m.lastname) AS manager 
FROM employees AS m 
INNER JOIN employees AS e ON m.employeeNumber  = e.reportsTo 
ORDER BY employee 

+------------------+-------------------+
| employee         | manager           |
+------------------+-------------------+
| Andy,Fixter      | William,Patterson |
| Barry,Jones      | Gerard,Bondur     |
| Foon Yue,Tseng   | Anthony,Bow       |
| George,Vanauf    | Anthony,Bow       |
| Gerard,Hernandez | Gerard,Bondur     |
| Jeff,Firrelli    | Diane,Murphy      |
| Julie,Firrelli   | Anthony,Bow       |
| Larry,Bott       | Gerard,Bondur     |
| Leslie,Jennings  | Anthony,Bow       |
| Leslie,Thompson  | Anthony,Bow       |
| Loui,Bondur      | Gerard,Bondur     |
| Martin,Gerard    | Gerard,Bondur     |
| Mary,Patterson   | Diane,Murphy      |
| Pamela,Castillo  | Gerard,Bondur     |
| Peter,Marsh      | William,Patterson |
| Steve,Patterson  | Anthony,Bow       |
| Tom,King         | William,Patterson |
| Yoshimi,Kato     | Mami,Nishi        |
+------------------+-------------------+

Querying data by using SQL UNION

SQL UNION allows you to combine two or more result sets from select statements into a single result set. The usage of using SQL UNION is as follows:

SELECT statement
UNION [DISTINCT | ALL]
SELECT statement
UNION [DISTINCT | ALL]

…
The column list of each individual SELECT statement must have the same data type. By default the UNION removes all duplicated rows from the result set even if you don’t explicit using DISTINCT after the UNION keyword. If you use UNION ALL explicitly, the duplicated rows will remain in the result set. Let’s practice with couples of examples which use SQL UNION. Suppose you want to combine customers and employees into one, you just perform the following query:

SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNumber id,firstname name
FROM employees

Here is the excerpt of the output

id  name           
------  ---------------
   103  Schmitt        
   112  King           
   114  Ferguson       
   119  Labrune        
   121  Bergulfsen     
   124  Nelson         
   125  Piestrzeniewicz
   128  Keitel         
   129  Murphy         
   131  Lee            
 

In order to use ORDER BY to sort the result you have to use it after the last SELECT statement. It would be the best to parenthesize all the SELECT statements and place ORDER BY after the last one. Suppose we use the want to sort the combination of employees and customers in the query above we can do as follows:

(SELECT customerNumber id,contactLastname name
FROM customers)
UNION
(SELECT employeeNumber id,firstname name
FROM employees)
ORDER BY name,id

First it orders the result set by name and then by id What if we don’t use alias for each column in the SELECT statement? MySQL uses the column names in the first SELECT statement as the label of the result therefore you can rewrite the query above as follows:

(SELECT customerNumber, contactLastname
FROM customers)
UNION
(SELECT employeeNumber, firstname
FROM employees)
ORDER BY contactLastname, customerNumber

or you can also use the column position in the ORDER BY clause like following query

(SELECT customerNumber, contactLastname
FROM customers)
UNION
(SELECT employeeNumber,firstname
FROM employees)
ORDER BY 2, 1

Using SQL LIKE Operator

SQL LIKE allows you to perform pattern matching in your characters column in a database table. SQL LIKE is often used with SELECT statement in WHERE clause. MySQL provides you two wildcard characters for use with LIKE, the percentage % and underscore _.

• Percentage (%) wildcard allows you to match any string of zero or more characters
• Underscore (_) allows you to match any sing character.

Let’s practice with couples of examples which use SQL Like with different wildcard characters.
Suppose you want to search all employees in employees table who have first name starting with character ‘a’, you can do it as follows:

SELECT employeeNumber, lastName, firstName
 FROM employees
 WHERE firstName LIKE 'a%'

+----------------+----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+----------+-----------+
|           1611 | Fixter   | Andy      |
+----------------+----------+-----------+
1 row in set (0.00 sec)

MySQL scans the whole employees table to find all employees which have first name starting with character ‘a’ and followed by any number of characters.
To search all employees which have last name ended with ‘on’ string you can perform the query as follows:

SELECT employeeNumber, lastName, firstName
 FROM employees
 WHERE lastName LIKE '%on'

+----------------+-----------+-----------+
| employeeNumber | lastName  | firstName |
+----------------+-----------+-----------+
|           1088 | Patterson | William   |
|           1216 | Patterson | Steve     |
+----------------+-----------+-----------+
2 rows in set (0.00 sec)

If you know a searched string is embedded somewhere in a column, you can put the percentage wild card at the beginning and the end of it to find all possibilities. For example, if you want to find all employees which havelast name containing ‘on’ string you can execute following query:

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastName LIKE '%on%' 

+----------------+-----------+-----------+
| employeeNumber | lastName  | firstName |
+----------------+-----------+-----------+
|           1088 | Patterson | William   |
|           1102 | Bondur    | Gerard    |
|           1216 | Patterson | Steve     |
|           1337 | Bondur    | Loui      |
|           1504 | Jones     | Barry     |
+----------------+-----------+-----------+
5 rows in set (0.00 sec)

To search all employees whose name are such as Tom, Tim… You can use underscore wildcard

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE firstName LIKE 'T_m'

+----------------+----------+-----------+
| employeeNumber | lastName | firstName |
+----------------+----------+-----------+
|           1619 | King     | Tom       |
+----------------+----------+-----------+
1 row in set (0.00 sec)

SQL LIKE allows you to put the NOT keyword to find all strings which are unmatched with a specific pattern. Suppose you want tosearch all employees whose last name are not starting with ‘B’ you can perform the following query

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastName NOT LIKE 'B%'

+----------------+-----------+-----------+
| employeeNumber | lastName  | firstName |
+----------------+-----------+-----------+
|           1088 | Patterson | William   |
|           1188 | Firrelli  | Julie     |
|           1216 | Patterson | Steve     |
|           1286 | Tseng     | Foon Yue  |
|           1323 | Vanauf    | George    |
|           1370 | Hernandez | Gerard    |
|           1401 | Castillo  | Pamela    |
|           1504 | Jones     | Barry     |
|           1611 | Fixter    | Andy      |
|           1612 | Marsh     | Peter     |
|           1619 | King      | Tom       |
|           1621 | Nishi     | Mami      |
|           1625 | Kato      | Yoshimi   |
|           1702 | Gerard    | Martin    |
+----------------+-----------+-----------+
14 rows in set (0.00 sec)

Be noted that SQL LIKE is not case sensitive so ‘b%’ and ‘B%’ are the same.
What if you want to search for records which have a field starting with a wildcard character? In this case, you can use ESCAPE to shows that the wildcardcharacters followed it has literal meaning not wildcard meaning. If ESCAPE does not specify explicitly, the escape character in MySQL by default is ‘\’. For example, if you want to find all products which as product code which has _20 embedded on it, you can perform following query

SELECT productCode, productName
FROM products
WHERE productCode LIKE '%\_20%'

+-------------+-------------------------------------------+
| productCode | productName                               |
+-------------+-------------------------------------------+
| S10_2016    | 1996 Moto Guzzi 1100i                     |
| S24_2000    | 1960 BSA Gold Star DBD34                  |
| S24_2011    | 18th century schooner                     |
| S24_2022    | 1938 Cadillac V-16 Presidential Limousine |
| S700_2047   | HMS Bounty                                |
+-------------+-------------------------------------------+
5 rows in set (0.00 sec)

SQL LIKE gives you a convenient way to find records which have character columns match specified patterns. Because SQL LIKE scans the whole table to find all the matching records therefore it does not allow database engine to use the index when searching. When the data in the table is big enough, the performance of SQL LIKE will degrade. In some cases you can avoid this problem by using other techniques to achieve the same result as SQL LIKE. For example, if you want to find all employees which havefirst name starting with a specified string you can use LEFT function in where clause like the following query

SET @str = 'b';
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE LEFT(lastname,length(@str)) = @str;

It returns the same result as the query bellow but it faster because we can leverage the index on the column lastname.

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastname LIKE 'b%'

And another technique to achieve all string which end with a specified string by using RIGHT function. Suppose we want to retrieve all employees which have last name ended with ‘on’ string, we can use RIGHT function instead of SQL LIKE as bellow:

SET @str = 'on';
SELECT employeeNumber, lastName, firstName
FROM employees
WHERE RIGHT (lastname,length(@str)) = @str;

+----------------+-----------+-----------+
| employeeNumber | lastName  | firstName |
+----------------+-----------+-----------+
|           1088 | Patterson | William   |
|           1216 | Patterson | Steve     |
+----------------+-----------+-----------+
2 rows in set (0.00 sec)

It returns the same result as the following query

SELECT employeeNumber, lastName, firstName
FROM employees
WHERE lastname LIKE '%on'

Retrieving Data in a Range by SQL BETWEEN

SQL BETWEEN allows you to retrieve values within a specific range. The usage of SQL BETWEEN is as follows:

SELECT column_list 
 FROM table_name
 WHERE column_1 BETWEEN lower_range AND upper_range

MySQL returns all records in which the column_1 value is in the range of lower_rage and upper_range as well as the values lower_rage and upper_range. The query which is equivalent to SQL BETWEEN to get the same result is

SELECT column_list 
 FROM table_name
 WHERE column_1 >= lower_range AND column_1 <= upper_range

Let’s practice with several examples of using SQL BETWEEN to search values in a range.
Suppose we want to find all products which buy price is in a range of 90$ and 100$, we can perform the following query to do so:

SELECT productCode,ProductName,buyPrice 
 FROM products
 WHERE buyPrice BETWEEN 90 AND 100
 ORDER BY buyPrice DESC

Here is the output

+-------------+--------------------------------------+----------+
| productCode | ProductName                          | buyPrice |
+-------------+--------------------------------------+----------+
| S10_1949    | 1952 Alpine Renault 1300             |    98.58 |
| S24_3856    | 1956 Porsche 356A Coupe              |     98.3 |
| S12_1108    | 2001 Ferrari Enzo                    |    95.59 |
| S12_1099    | 1968 Ford Mustang                    |    95.34 |
| S18_1984    | 1995 Honda Civic                     |    93.89 |
| S18_4027    | 1970 Triumph Spitfire                |    91.92 |
| S10_4698    | 2003 Harley-Davidson Eagle Drag Bike |    91.02 |
+-------------+--------------------------------------+----------+

The output contains all products in the range of 90$ and 100$, and if there is a product with buy price 90$ or 100$, it will be included in the output too.
In order to find all records which are not in a range we use NOT BETWEEN. To find all products that buy price outside the range of 20 and 100, we can operate following query:

SELECT productCode,ProductName,buyPrice 
 FROM products
 WHERE buyPrice NOT BETWEEN 20 AND 100
 ORDER BY buyPrice DESC

+-------------+-------------------------------------+----------+
| productCode | ProductName                         | buyPrice |
+-------------+-------------------------------------+----------+
| S10_4962    | 1962 LanciaA Delta 16V              |   103.42 |
| S18_2238    | 1998 Chrysler Plymouth Prowler      |   101.51 |
| S24_2972    | 1982 Lamborghini Diablo             |    16.24 |
| S24_2840    | 1958 Chevy Corvette Limited Edition |    15.91 |
+-------------+-------------------------------------+----------+

The query above is equivalent to the following query

SELECT productCode,ProductName,buyPrice 
FROM products
 WHERE buyPrice < 20 OR buyPrice > 100
 ORDER BY buyPrice DESC

Selecting Data with SQL IN

SQL IN allows you to select values which match any one of a list of values. The usage of SQL IN is as follows:

SELECT column_list
FROM table_name
WHERE column IN ("list_item1","list_item2"…)

The column in WHERE clause does not need to be in column_list  you selected, but it has to be a column in the table table_name. If the list has more than one value, each item has to be separated by a comma.
In addition, you can use NOT operator with SQL IN to get values which does not match any value in a list of value.
Let’s practice with several examples of SQL IN.
Suppose if  you want to find out all offices which are located in US and France, you can perform the following query:

SELECT officeCode, city, phone
FROM offices
WHERE country = 'USA'  OR country = 'France'

In this case, we can use SQL IN instead of the above query:

SELECT officeCode, city, phone
FROM offices
WHERE country IN ('USA','France')

Here is the output

+------------+--------+-----------------+
| officeCode | city   | phone           |
+------------+--------+-----------------+
| 2          | Boston | +1 215 837 0825 |
| 3          | NYC    | +1 212 555 3000 |
| 4          | Paris  | +33 14 723 5555 |
| 8          | Boston | +1 215 837 0825 |
+------------+--------+-----------------+

To get all countries which does are not located in USA and France, we can use NOT IN in the where clause as follows:

SELECT officeCode, city, phone
FROM offices
WHERE country NOT IN ('USA','France')

Here is the output of offices which does not in USA and France

+------------+--------+------------------+
| officeCode | city   | phone            |
+------------+--------+------------------+
| 5          | Tokyo  | +81 33 224 5000  |
| 6          | Sydney | +61 2 9264 2451  |
| 7          | London | +44 20 7877 2041 |
+------------+--------+------------------+

SQL IN is used most often in sub-query. For example, if you want to find out all orders in the orders table which have total cost greater than $60000, we can use SQL IN with sub-query.
First to select all the orders which has total cost greater than $60000, you can retrieve it from orderDetails table as follows:

SELECT orderNumber
FROM orderDetails
GROUP BY orderNumber
HAVING SUM (quantityOrdered * priceEach) > 60000

Second you use the sub-query with SQL IN as follows:

SELECT orderNumber,customerNumber,status,shippedDate
FROM orders 
WHERE orderNumber IN (
     SELECT  orderNumber
     FROM  orderDetails
     GROUP BY  orderNumber
     HAVING  SUM(quantityOrdered * priceEach) > 60000

)
You get all the orders which have total cost greater than $60000

+-------------+----------------+---------+---------------------+
| orderNumber | customerNumber | status  | shippedDate         |
+-------------+----------------+---------+---------------------+
|       10165 |            148 | Shipped | 2003-12-26 00:00:00 |
|       10287 |            298 | Shipped | 2004-09-01 00:00:00 |
|       10310 |            259 | Shipped | 2004-10-18 00:00:00 |
+-------------+----------------+---------+---------------------+

Querying Data from MySQL

In order to retrieve data from MySQL database server you use SELECT statement. Here is the simple form of it:

SELECT column_name1,column_name2...
FROM tables
[WHERE conditions]
[GROUP BY group
[HAVING group_conditions]]
[ORDER BY sort_columns]
[LIMIT limits];

The SELECT statement has many option clauses which you can use these or not. If you use, the order has to be appear as shown.
To select all columns in a table you can use (*) notation instead of listing all column name. For example, if you need to query all the columns in offices table just execute this query:

SELECT * FROM employees

Because of the long result so we don't listed here, you can see it on your PC. SELECT statement also allows you to to view some partial data of a table by listing columns' name after the SELECT keyword. For example if you need to view only first name, last name and job title of all employee you can do the following query :

SELECT lastname,firstname,jobtitle
FROM employees

The MySQL database server will return the record set like follows:

+-----------+-----------+----------------------+
| lastname  | firstname | jobtitle             |
+-----------+-----------+----------------------+
| Murphy    | Diane     | President            |
| Patterson | Mary      | VP Sales             |
| Firrelli  | Jeff      | VP Marketing         |
| Patterson | William   | Sales Manager (APAC) |
| Bondur    | Gerard    | Sale Manager (EMEA)  |
| Bow       | Anthony   | Sales Manager (NA)   |
| Jennings  | Leslie    | Sales Rep            |
| Thompson  | Leslie    | Sales Rep            |
| Firrelli  | Julie     | Sales Rep            |
| Patterson | Steve     | Sales Rep            |
| Tseng     | Foon Yue  | Sales Rep            |
| Vanauf    | George    | Sales Rep            |
| Bondur    | Loui      | Sales Rep            |
| Hernandez | Gerard    | Sales Rep            |
| Castillo  | Pamela    | Sales Rep            |
| Bott      | Larry     | Sales Rep            |
| Jones     | Barry     | Sales Rep            |
| Fixter    | Andy      | Sales Rep            |
| Marsh     | Peter     | Sales Rep            |
| King      | Tom       | Sales Rep            |
| Nishi     | Mami      | Sales Rep            |
| Kato      | Yoshimi   | Sales Rep            |
| Gerard    | Martin    | Sales Rep            |
+-----------+-----------+----------------------+
23 rows in set (0.00 sec)

WHERE Clause

WHERE clause enables you to select a particular rows which match its conditions or search criteria. In our example we can find the president of company by doing this query:

SELECT firstname,lastname,email 
FROM employees 
WHERE jobtitle="president"

And of course MySQL returns the result

+-----------+----------+------------------------------+
| firstname | lastname | email                        |
+-----------+----------+------------------------------+
| Diane     | Murphy   | dmurphy@classicmodelcars.com |
+-----------+----------+------------------------------+
1 row in set (0.00 sec)

DISTINCT

With DISTINCT keyword, you can eliminate the duplicated result from SELECT statement. For example, to find how many job title of all employee in employees table, we use DISTINCT keyword in SELECT statement like below:

SELECT DISTINCT jobTitle FROM employees;

And here are all job titles the employee has

+----------------------+
| jobTitle             |
+----------------------+
| President            |
| VP Sales             |
| VP Marketing         |
| Sales Manager (APAC) |
| Sale Manager (EMEA)  |
| Sales Manager (NA)   |
| Sales Rep            |
+----------------------+
7 rows in set (0.00 sec)

GROUP BY

If you need to find number of employee who hold each job, you can use GROUP BY clause. GROUP BY clause allows use to retrieve rows in group. Here is the query example:

SELECT count(*), jobTitle 
FROM employees 
GROUP BY jobTitle;

And here is the result

+----------+----------------------+
| count(*) | jobTitle             |
+----------+----------------------+
|        1 | President            |
|        1 | Sale Manager (EMEA)  |
|        1 | Sales Manager (APAC) |
|        1 | Sales Manager (NA)   |
|       17 | Sales Rep            |
|        1 | VP Marketing         |
|        1 | VP Sales             |
+----------+----------------------+
7 rows in set (0.02 sec)

HAVING Clause

HAVING clause usually use with GROUP BY clause to selecting a particular of group. For example:

SELECT count(*), jobTitle 
FROM employees 
GROUP BY jobTitle 
HAVING count(*) = 1

This query select the job in the company for which we have one employee in each job title. HAVING clause select all group which have count(*) equal 1. Here is the output:

+----------+----------------------+
| count(*) | jobTitle             |
+----------+----------------------+
|        1 | President            |
|        1 | Sale Manager (EMEA)  |
|        1 | Sales Manager (APAC) |
|        1 | Sales Manager (NA)   |
|        1 | VP Marketing         |
|        1 | VP Sales             |
+----------+----------------------+
6 rows in set (0.00 sec)

Sorting with ORDER BY

The ORDER BY clause allows you to sort the result set on one or more column in ascending or descending order. To sort the result set in ascending order you use ASC and in descending order you use DESC keywords. By default,the ORDER BY will sort the result set in ascending order. For example, to sort the name of employee on the first name and job title you can execute the following query:

SELECT firstname,lastname, jobtitle 
FROM employees 
ORDER BY firstname ASC,jobtitle DESC;

+-----------+-----------+----------------------+
| firstname | lastname  | jobtitle             |
+-----------+-----------+----------------------+
| Andy      | Fixter    | Sales Rep            |
| Anthony   | Bow       | Sales Manager (NA)   |
| Barry     | Jones     | Sales Rep            |
| Diane     | Murphy    | President            |
| Foon Yue  | Tseng     | Sales Rep            |
| George    | Vanauf    | Sales Rep            |
| Gerard    | Hernandez | Sales Rep            |
| Gerard    | Bondur    | Sale Manager (EMEA)  |
| Jeff      | Firrelli  | VP Marketing         |
| Julie     | Firrelli  | Sales Rep            |
| Larry     | Bott      | Sales Rep            |
| Leslie    | Jennings  | Sales Rep            |
| Leslie    | Thompson  | Sales Rep            |
| Loui      | Bondur    | Sales Rep            |
| Mami      | Nishi     | Sales Rep            |
| Martin    | Gerard    | Sales Rep            |
| Mary      | Patterson | VP Sales             |
| Pamela    | Castillo  | Sales Rep            |
| Peter     | Marsh     | Sales Rep            |
| Steve     | Patterson | Sales Rep            |
| Tom       | King      | Sales Rep            |
| William   | Patterson | Sales Manager (APAC) |
| Yoshimi   | Kato      | Sales Rep            |
+-----------+-----------+----------------------+
23 rows in set (0.00 sec)

Managing Database Index

Creating Indexes

Database indexes help to speed the retrieval of data from MySQL database server faster. When retrieving the data, MySQL first check whether the indexes exists; If yes it will use index to select exact physical corresponding rows without scanning the whole table.
In general, it is suggested that you should put indexes on columns you usually use in retrieval such as primary key columns and columns used in join and sorts. Why not index every column? The most significant is that building and maintaining anindexes tables take time and storage space on database.
Usually you create indexes when creating tables. Any column in creating table statement declared as PRIMARY KEY, KEY, UNIQUE or INDEX will be indexed byMySQL. In addition, you can add indexes to the tables which has data. The statement to create index in MySQL as follows:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
USING [BTREE | HASH | RTREE] 
ON table_name (column_name [(length)] [ASC | DESC],...)

First you specify the index based on the table types or storage engine:

• UNIQUE means MySQL will create a constraint that all values in the index must be distinct. Duplicated NULL is allowed in all storage engine except BDB.
• FULLTEXT index is supported only by MyISAM storage engine and only accepted columns which have data type is CHAR,VARCHAR or TEXT.
• SPATIAL index supports spatial column and available in MyISAM storage engine. In addition, the column value must not be NULL.

Then you name the index using index types such as BTREE, HASH or RTREE also based on storage engine. Here are the list:

Storage Engine	Allowable Index Types
MyISAM	BTREE, RTREE
InnoDB	BTREE
MEMORY/HEAP	HASH, BTREE
NDB	HASH

Finally you declare which column on which table using the index.
In our sample database you can add index to officeCode column on employees table to make the join operation with office table faster as follows:

CREATE INDEX officeCode ON employees(officeCode)

Removing Indexes

Beside creating index you can also removing index by using DROP INDEX statement in MySQL. Interestingly, DROP INDEX statement is also mapped to ALTER TABLE statement. Here is the syntax:

DROP INDEX index_name ON table_name

For example, if you want to drop index officeCode which we have added to the employees table, just execute following query:

DROP INDEX officeCode ON employees

Working with Tables - Part II

Altering Table Structures

Beside creating table, MySQL allows you to alter existing table structures with a lot of options. Here are the ALTER TABLE statement:

ALTER [IGNORE] TABLE table_name options[, options...]
options:
        ADD [COLUMN] create_definition [FIRST | AFTER col_name ]
  or    ADD [COLUMN] (create_definition, create_definition,...)
  or    ADD INDEX [index_name] (index_col_name,...)
  or    ADD PRIMARY KEY (index_col_name,...)
  or    ADD UNIQUE [index_name] (index_col_name,...)
  or    ADD FULLTEXT [index_name] (index_col_name,...)
  or    ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  or    ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  or    CHANGE [COLUMN] old_col_name create_definition
               [FIRST | AFTER column_name]
  or    MODIFY [COLUMN] create_definition [FIRST | AFTER col_name]
  or    DROP [COLUMN] col_name
  or    DROP PRIMARY KEY
  or    DROP INDEX index_name
  or    DISABLE KEYS
  or    ENABLE KEYS
  or    RENAME [TO] new_table_name
  or    ORDER BY col_name
  or    table_options

Most of these option are obvious, we will explain some here:

• The CHANGE and MODIFY are the same, they allow you to change the definition of the column or its position in the table.
• The DROP COLUMN will drop the column of the table permanently, if the table contain data all the data of the column will be lost.
• The DROP PRIMARY KEY and DROP INDEX only remove the primary key or index of the column.
• The DISABLE and ENABLE KEYS turn off and on updating indexes for MyISAM table only.
• The RENAME Clause allows you the change the table name to the new one.

Dropping Tables

To delete table from the database, you can use DROP TABLE statement:

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name,...]

TEMPORARY keyword is used for dropping temporary tables. MySQL allows you to drop multiple table at once by listing them and separated each by a comma. IF EXISTS is used to prevent you from deleting table which does not exist in the database.

Empty Table's Data

In some cases, you want to delete all table data in a fast way and reset all auto increment columns. MySQL provide you TRUNCATE table statement to do so. The statement is in this form:

TRUNCATE TABLE table_name

There are some points you should remember before using TRUNCATE TABLE statement:

• TRUNCATE TABLE statement drop table and recreate it so it is much faster than DELETE TABLE statement but it is not transaction-safe.
• The number of deleted rows is not return like DELETE TABLE statement.
• ON DELETE triggers are not invoked because TRUNCATE does not use DELETE statement.