MySQL Full Tutorial-Part2

1 2	-- display the dynamic sql statement SELECT @droplike;

You can see that it works as expected.

After that, you can execute the statement using prepared statement in MySQL as follows:

1 2 3 4

-- execute dynamic sql PREPARE stmt FROM @dropcmd; EXECUTE stmt; DEALLOCATE PREPARE stmt;

For more information on MySQL prepared statement, check it out the MySQL prepared statement tutorial.

Putting it all together.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

-- set table schema and pattern matching for tables SET @schema = 'classicmodels'; SET @pattern = 'test%'; -- build dynamic sql (DROP TABLE tbl1, tbl2...;) SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(CONCAT(@schema,'.',table_name)),';') INTO @droplike FROM information_schema.tables WHERE @schema = database() AND table_name LIKE @pattern; -- display the dynamic sql statement SELECT @droplike; -- execute dynamic sql PREPARE stmt FROM @dropcmd; EXECUTE stmt; DEALLOCATE PREPARE stmt;

So if you want to drop multiple tables that have a specific pattern in a database, you just use the script above to save time. All you need to do is replacing the pattern and the database schema in @patternand @schema variables. If you often have to deal with this task, you can always develop a stored procedure based on the script and reuse the stored procedure in the future.

In this tutorial, we’ve shown you how to use the DROP TABLE statement to remove existing tables in a particular database. We also discussed about a workaround that allows you to use the DROP TABLE statement to remove tables based on pattern matching.

Create MySQL temporary table

Like the CREATE TABLE statement, MySQL provides many options to create a temporary table. To create a temporary table, you just add the TEMPORARY keyword to the CREATE TABLE statement.

For example, the following statement creates a top 10 customers by revenue temporary table based on the result set of a SELECT statement:

1 2 3 4 5 6 7 8 9

CREATE TEMPORARY TABLE top10customers SELECT p.customerNumber,        c.customerName,        FORMAT(SUM(p.amount),2) total FROM payments p INNER JOIN customers c ON c.customerNumber = p.customerNumber GROUP BY p.customerNumber ORDER BY total DESC LIMIT 10

Now, you can query data from the top10customers temporary table as from a permanent table:

1	SELECT * FROM top10customers

Drop MySQL temporary table

You can use the DROP TABLE statement to remove temporary tables however it is good practice to use the DROP TEMPORARY TABLE statement instead. Because the DROP TEMPORARY TABLEremoves only temporary tables, not the permanent tables. In addition, the DROP TEMPORARY TABLEstatement helps you avoid the mistake of removing a permanent table when you name your temporary table the same as the name of the permanent table.

For example, to remove the top10customers temporary table, you use the following statement:

1	DROP TEMPORARY TABLE top10customers

Notice that if you try to remove a permanent table with the DROP TEMPORARY TABLE statement, you will get an error message saying that the table you are trying drop is unknown.

Note if you develop an application that uses a connection pooling or persistent connections, it is not guaranteed that the temporary tables are removed automatically when your application is terminated. Because the database connection that the application used may be still open and is placed in a connection pool for other clients to reuse it. This means you should always remove the temporary tables that you created whenever you are done with them.

In this tutorial, you have learned about MySQL temporary table and its characteristic. We also gave you an example of how to create, use and drop a temporary table.

MySQL Managing Database Index

Summary: in this tutorial, you will learn how to work with MySQL index and how to take advantages of  the index to speed up the data retrieval. We will introduce you several useful statements that allows you to manage MySQL indexes.

Database index, or just index, helps speed up the retrieval of data from tables. When you query data from a table, first MySQL checks if the indexes exist, then MySQL uses the indexes to select exact physical corresponding rows of the table instead of scanning the whole table.

A database index is similar to an index of a book. If you want to find a topic, you look up in the index first, and then you open the page that has the topic without scanning the whole book.

It is highly recommended that you should create index on columns of table from which you often query the data. Notice that all primary key columns are in the primary index of  the table automatically.

If index helps speed up the querying data, why don’t we use indexes for all columns? If you create an index for every column, MySQL has to build and maintain the index table. Whenever a change is made to the records of the table, MySQL has to rebuild the index, which takes time as well as decreases the performance of the database server.

Creating MySQL Index

You often create indexes when you create tables. MySQL automatically add any column that is declared as PRIMARY KEY, KEY, UNIQUE or INDEX to the index. In addition, you can add indexes to the tables that already have data.

In order to create indexes, you use the CREATE INDEX statement. The following illustrates the syntax of the CREATE INDEX statement:

1 2 3

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 type or storage engine:

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

Then, you name the index and its type after the USING keyword such as BTREE, HASH or RTREEalso based on the storage engine of the table.

Here are the storage engines of the table with the corresponding allowed index types:

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

Third, you declare table name and a list columns that you want to add to the index.

Example of creating index in MySQL

In the sample database, you can add  officeCode column of  the employees table to the index by using the CREATE INDEX statement as follows:

1	CREATE INDEX officeCode ON employees(officeCode)

Removing Indexes

Besides creating index, you can also remove index by using the DROP INDEX statement. Interestingly, the DROP INDEX statement is also mapped to ALTER TABLE statement. The following is the syntax of removing the index:

1	DROP INDEX index_name ON table_name

For example, if you want to drop index officeCode of the employees table,  which we have created above, you can execute following query:

1	DROP INDEX officeCode ON employees

In this tutorial, you’ve learned about indexes and how to manage MySQL index including creating and removing indexes.

Using MySQL SELECT Statement to Query Data

Summary: in this tutorial, you will learn how to MySQL SELECT statement to query data from adatabase table.

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 2 3 4 5 6 7 8

SELECT column_1,column_2... FROM table_1 [INNER | LEFT |RIGHT] JOIN table_2 ON conditions WHERE conditions GROUP BY group HAVING group_conditions ORDER BY column_1 [ASC | DESC] 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.

You will learn about each clause in more detail in the next tutorial. In this tutorial, we are going to focus on the simple form of the SELECT statement.

MySQL SELECT Examples

To select all columns and rows from the employees table, you use the following query:

1	SELECT * FROM employees

The asterisk (*) notation is a shorthand of selecting all columns in the table.

The SELECT statement also allows you to query partial data of a table by specifying a list of comma-separated columns in the SELECT clause. For example, if you want to view only first name,last name and job title of the employees,  you use the following query:

1 2 3 4

SELECT lastname,        firstname,        jobtitle FROM employees

In this tutorial, you’ve learned about the basic MySQL SELECT statement to query data from a database table in MySQL

Filter Rows Using MySQL WHERE

Summary: you will learn how to use MySQL WHERE clause to filter rows returned from the SELECT statement.

If you use the SELECT statement to query the data from a table without the WHERE clause, you will get all rows in the  table, which sometimes brings more data than you need. The WHERE clause allows you to specify exact rows to select based on given conditions e.g., find all customers in the U.S.

The following query selects all customers whose country is U.S. from the customers table. We use the WHERE clause to filter the customers. In the WHERE clause, we compare the values of thecountry column with the USA literal string.

1 2 3

SELECT customerName, city FROM customers WHERE country = 'USA';

You can form a simple condition like the query above, or a very complex one that combines multiple expressions with logical operators such as AND and OR. For example, to find all customers in the U.S . and also in the New York city, you use the following query:

1 2 3 4

SELECT customerName, city FROM customers WHERE country = 'USA' AND       city    = 'NYC';

You can test the condition for not only equality but also inequality. For example, to find all customers whose credit limit is greater than 200.000 USD, you use the following query:

1 2 3

SELECT customerName, creditlimit FROM customers WHERE creditlimit > 200000;

There are several useful operators that you can use in the WHERE clause to form more practical queries such as:

• BETWEEN selects values within a range of values.
• LIKE matches value based on pattern matching.
• IN specifies if the value matches any value in a list.
• IS NULL checks if the value is NULL

The WHERE clause is used not only with the SELECT statement but also other SQL statements to filter rows such as DELETE and UPDATE.

MySQL ORDER BY

Summary: in this tutorial, you will learn how to sort a result set by using MySQL ORDER BY clause.

Introduction to MySQL ORDER BY clause

When you use the SELECT statement to query data from a table, the result set is not sorted in a specific order. To sort the result set, you use the ORDER BY clause. The ORDER BY clause allows you to:

• Sort a result set by a single column or multiple columns.
• Sort a result set by different columns in ascending or descending order.

The following illustrates the syntax of the ORDER BY clause:

1 2 3

SELECT col1, col2,... FROM tbl ORDER BY col1 [ASC|DESC], col2 [ASC|DESC],...

The ASC stands for ascending and the DESC stands for descending. By default, the ORDER BY clause sorts the result set in ascending order  if you don’t  specify ASC or DESC explicitly

Let’s practice with some examples of using the ORDER BY clause.

MySQL ORDER BY examples

The following query selects contacts from the customers table and sorts the contacts by last name in ascending order.

1 2 3 4

SELECT contactLastname,        contactFirstname FROM customers ORDER BY contactLastname;

If you want to sort the contact by last name in descending order, you specify the DESC after thecontactLastname column in the ORDER BY clause as the following query:

1 2 3 4

SELECT contactLastname,        contactFirstname FROM customers ORDER BY contactLastname DESC

If you want to sort the contacts by last name in descending order and first name in ascending order, you specify both  DESC and ASC in the corresponding column as follows:

1 2 3 4 5

SELECT contactLastname,        contactFirstname FROM customers ORDER BY contactLastname DESC,          contactFirstname ASC;

In the query above, the ORDER BY clause sorts the result set by  last name in descending order first, and then sorts the sorted result set by first name in ascending order to produce the final result set.

MySQL ORDER BY sort by an expression example

The ORDER BY clause also allows you to sort the result set based on an expression. The following query selects the order line items from the orderdetails table. It calculates the subtotal for each line item and sorts the result set based on the order number and subtotal.

1 2 3 4 5

SELECT ordernumber,        quantityOrdered * priceEach FROM orderdetails ORDER BY ordernumber,          quantityOrdered * priceEach

To make the result more readable, you can use a column alias, and sort the result based on the column alias.

1 2 3 4 5

SELECT orderNumber,        quantityOrdered * priceEach AS subTotal FROM orderdetails ORDER BY orderNumber,          subTotal;

1

In the query above, we used subtotal as the column alias for the quantityOrdered * priceEach expression and sorted the result set based on the subtotal alias.

If you use  a function that returns a value whose data type is different from the column’s and sort the result based on the alias, the ORDER BY clause will sort the result set based on the return type of the function, which may not work as expected.

For example, if you use the DATE_FORMAT function to format the date values and sort the result set based on the strings returned by the DATE_FORMAT function, the order is not always correct. For more information, check it out the example in the DATE_FORMAT function tutorial.

MySQL ORDER BY with customer sort order

The ORDER BY clause enables you to define your own custom sort order for the values in a column using the FIELD() function. For example, if you want to sort the orders based on the following status by the following order:

1. In Process
2. On Hold
3. Cancelled
4. Resolved
5. Disputed
6. Shipped

You can use the FIELD() function to map those values to a list of numeric values and use the numbers for sorting; See the following query:

1 2 3 4 5 6 7 8

SELECT orderNumber, status FROM orders ORDER BY FIELD(status, 'In Process',                        'On Hold',                        'Cancelled',                        'Resolved',                        'Disputed',                        'Shipped');

In this tutorial, we’ve shown you various techniques to sort a result set by using the MySQL ORDER BYclause in the   SELECT statement.

How to Use MySQL DISTINCT to Eliminate Duplicate Rows

Summary: in this tutorial, you will learn how to use MySQL DISTINCT operator with the SELECTstatement to eliminate duplicate rows in the result set.

When querying data from a table, you may get duplicate rows. In order to remove the duplicate rows, you use the DISTINCT operator in the SELECT statement. The syntax of using the DISTINCToperator is as follows:

1 2 3

SELECT DISTINCT columns FROM table_name WHERE where_conditions

Let’s take a look a simple example of using the DISTINCT operator to select the distinct last names of employees from the employees table.

First, we query the last names of employees from the employees table using the SELECTstatement as follows:

1 2 3

SELECT lastname FROM employees ORDER BY lastname

Some employees has the same last name  Bondur, Firrelli, etc. To remove the duplicate last names, you use the DISTINCT operator in the SELECT clause as follows: