In this tutorial, you will learn various ways to concatenate two or more string together by using concat function which is provided by MySQL.
Almost RMDMSs support us to concatenate two or more strings together by using different implementations. MS SQL server us operator plus (+) to concatenate strings. Oracle only allows you to concatenate two strings with concat function or operator . MySQL supports a more flexible way by enabling us to concatenate more than two strings or even concatenate strings with predefined separator. Let's start with each concatenate function.
Concat Function
CONCAT(str1,str2,...)The concat function is used to concatenate two or more string and returns concatenating string. If the arguments are numeric, they will be converted to strings before concatenating. If any of argument in the argument list is NULL, the concat function will return NULL.
Here are several examples to demonstrate the concat function. We will use the sample database to practice.
In order to display the first 5 full name of contacts of the customers we use concat function to concatenate the first name and last name and a separator between them. Here is the query:
SELECT CONCAT(contactLastname,', ',contactFirstname) fullname
FROM customers
LIMIT 5
The output result is
fullname
------------------
Schmitt, Carine
King, Jean
Ferguson, Peter
Labrune, Janine
Bergulfsen, Jonas
Concat with Separator Function
MySQL also supports concat_ws function which allows us to concatenate two or more than two strings with a predefined separator. The syntax of the concat_ws function is:
CONCAT_WS(seperator,str1,str2,...) The first parameter is the predefined separator you specified and the others are the string you want to concatenate. the result is the concatenating string with separator between each. For example, you can achieve the same result in the above example by using concat_ws function instead of concat function.
SELECT CONCAT_WS(', ',contactLastname,contactFirstname) fullname
FROM customers
LIMIT 5
Here is another example of using concat_ws to get address format of customers.
SELECT CONCAT_WS(char(13),
CONCAT_WS(' ',contactLastname,contactFirstname),addressLine1,addressLine2,CONCAT_W
(' ',postalCode,city),country,CONCAT_WS(char(13),'')) AS Customer_Address
FROM customers
LIMIT 2
Here is the output result
Customer_Address
---------------------------------------------------
Schmitt Carine
54, rue Royale
44000 Nantes
France
King Jean
8489 Strong St.
83030 Las Vegas
USA
Thursday, April 23, 2009
Compare Two Tables to Find Unmatched Records ( Membandingkan dua table untuk menemukan record yang tidak sama )
In database programming, sometimes you have to compare two tables (or two views of more than two tables) to find the unmatched records and the difference between two records in two tables with the same identity. As an example, in database migration you have a legacy database and new database with two different database schemata. Your task is to migration all data from the legacy database to the new one so how do you validate your result? At this time, you have to use this technique to see your result; what records you missed in the legacy database; what records you migrated with the wrong result.
One of the fastest and easiest ways to do so is using UNION. The idea is we use UNION to union two tables on all columns which we want to compare. Then we group the union on all columns which we want to compare. If the all columns are identical we get COUNT (*) equal 2 otherwise all unmatched records and the records in one table but not in other table will have COUNT (*) equal 1.
SELECT MIN (tbl_name) AS tbl_name, PK, column_list
FROM
(SELECT ' source_table ' as tbl_name, S.PK, S.column_list FROM source_table AS S UNION ALL SELECT 'destination_table' as tbl_name, D.PK, D.column_list FROM destination_table AS D ) AS alias_table
GROUP BY PK, column_list HAVING COUNT (*) = 1
ORDER BY PK
In the above query:
# tbl_name is just additional column name to display the name of tables you want to compare. These are source_table and destination_table
# PK is identity name of source_table and destination_table
# column_list is the list of column you want to compare
# The MIN aggregate on table_name is just arbitrary. It is used because we only returns group of rows in which there was no consolidation with GROUP BY.
If two tables are identical on the column list you compared, no row will return.
One of the fastest and easiest ways to do so is using UNION. The idea is we use UNION to union two tables on all columns which we want to compare. Then we group the union on all columns which we want to compare. If the all columns are identical we get COUNT (*) equal 2 otherwise all unmatched records and the records in one table but not in other table will have COUNT (*) equal 1.
SELECT MIN (tbl_name) AS tbl_name, PK, column_list
FROM
(SELECT ' source_table ' as tbl_name, S.PK, S.column_list FROM source_table AS S UNION ALL SELECT 'destination_table' as tbl_name, D.PK, D.column_list FROM destination_table AS D ) AS alias_table
GROUP BY PK, column_list HAVING COUNT (*) = 1
ORDER BY PK
In the above query:
# tbl_name is just additional column name to display the name of tables you want to compare. These are source_table and destination_table
# PK is identity name of source_table and destination_table
# column_list is the list of column you want to compare
# The MIN aggregate on table_name is just arbitrary. It is used because we only returns group of rows in which there was no consolidation with GROUP BY.
If two tables are identical on the column list you compared, no row will return.
Select the nth Highest Record in a Database Table
In this tutorial, you will learn how to select the nth highest record in a database table in various techniques. These techniques are very useful, for instance you may want to see the product which has second most expensive in Products database table.
The first idea is we get the nth highest record and sort them in ascending order. The nth highest record is the last record in the result set. And then we order the result set in descending order and get the first one. Here is SQL script to accomplish it:
Get the Nth highest result set in ascending order:
SELECT * FROM table_name ORDER BY column_name AS C LIMIT n
Get the nth highest record:
SELECT * FROM ( SELECT * FROM table_name ORDER BY column_name AS C LIMIT n) AS tbl ORDER BY column_name DESC LIMIT 1
Luckily, with LIMIT clause you can rewrite the query as follows:
SELECT *FROM table_name ORDER BY column_name DESC LIMIT n - 1, 1
The query just returns the first row after n-1 row(s) so you get the nth highest record.
For example, if you want to get the second most expensive product (n = 2) in Products database table. You just perform the following query:
SELECT productCode, productName, buyPrice
FROM products
ORDER BY buyPrice desc
LIMIT 1, 1
Here is the result
+-------------+--------------------------------+----------+
productCode productName buyPrice
+-------------+--------------------------------+----------+
S18_2238 1998 Chrysler Plymouth Prowler 101.51
+-------------+--------------------------------+----------+
1 row in set (0.00 sec)
The second technique to get the nth highest record is using subquery
SELECT *FROM table_name AS a
WHERE n - 1 = (SELECT COUNT(primary_key_column) FROM products b WHERE b.column_name > a. column_name)
And we achieve the same result as the first technique on Products table to get the second most expensive product by performing the following query:
SELECT productCode, productName, buyPrice
FROM products a
WHERE 1 = ( SELECT COUNT(productCode) FROM products b WHERE b.buyPrice > a.buyPrice)
The first idea is we get the nth highest record and sort them in ascending order. The nth highest record is the last record in the result set. And then we order the result set in descending order and get the first one. Here is SQL script to accomplish it:
Get the Nth highest result set in ascending order:
SELECT * FROM table_name ORDER BY column_name AS C LIMIT n
Get the nth highest record:
SELECT * FROM ( SELECT * FROM table_name ORDER BY column_name AS C LIMIT n) AS tbl ORDER BY column_name DESC LIMIT 1
Luckily, with LIMIT clause you can rewrite the query as follows:
SELECT *FROM table_name ORDER BY column_name DESC LIMIT n - 1, 1
The query just returns the first row after n-1 row(s) so you get the nth highest record.
For example, if you want to get the second most expensive product (n = 2) in Products database table. You just perform the following query:
SELECT productCode, productName, buyPrice
FROM products
ORDER BY buyPrice desc
LIMIT 1, 1
Here is the result
+-------------+--------------------------------+----------+
productCode productName buyPrice
+-------------+--------------------------------+----------+
S18_2238 1998 Chrysler Plymouth Prowler 101.51
+-------------+--------------------------------+----------+
1 row in set (0.00 sec)
The second technique to get the nth highest record is using subquery
SELECT *FROM table_name AS a
WHERE n - 1 = (SELECT COUNT(primary_key_column) FROM products b WHERE b.column_name > a. column_name)
And we achieve the same result as the first technique on Products table to get the second most expensive product by performing the following query:
SELECT productCode, productName, buyPrice
FROM products a
WHERE 1 = ( SELECT COUNT(productCode) FROM products b WHERE b.buyPrice > a.buyPrice)
Count a Number of Records in a Database Table
In order to count a number of records in a database table we can use standard SQL COUNT function. The syntax is quite simple as follows:
SELECT COUNT(*) AS total_founds
FROM table_name
The query will return the total record of the table. We can use WHERE clause in SELECT statement to filter records in the table we want to count as follows:
SELECT COUNT(*) AS total_founds
FROM table_name
WHERE conditions
The query will only returns the number of records or rows which match the conditions in WHERE clause.
Be noted that the COUNT(*) will return all rows in the database table even though a specified column value in a database table is NULL. In order to count records without NULL value in a specified column, we can use COUNT(column) instead. Here is the syntax:
SELECT COUNT(column)
FROM table_name
Let's practice with a couple of examples to understand more how SQL COUNT works in MySQL:
We can count all the offices in the office table by executing following query:
SELECT COUNT(*) AS total_office FROM offices
+--------------+
| total_office |
+--------------+
| 7 |
+--------------+
To count all office which has state we can operate following query:
SELECT COUNT(state) AS total_office_with_state
FROM offices
+-------------------------+
| total_office_with_state |
+-------------------------+
| 4 |
+-------------------------+
SELECT COUNT(*) AS total_founds
FROM table_name
The query will return the total record of the table. We can use WHERE clause in SELECT statement to filter records in the table we want to count as follows:
SELECT COUNT(*) AS total_founds
FROM table_name
WHERE conditions
The query will only returns the number of records or rows which match the conditions in WHERE clause.
Be noted that the COUNT(*) will return all rows in the database table even though a specified column value in a database table is NULL. In order to count records without NULL value in a specified column, we can use COUNT(column) instead. Here is the syntax:
SELECT COUNT(column)
FROM table_name
Let's practice with a couple of examples to understand more how SQL COUNT works in MySQL:
We can count all the offices in the office table by executing following query:
SELECT COUNT(*) AS total_office FROM offices
+--------------+
| total_office |
+--------------+
| 7 |
+--------------+
To count all office which has state we can operate following query:
SELECT COUNT(state) AS total_office_with_state
FROM offices
+-------------------------+
| total_office_with_state |
+-------------------------+
| 4 |
+-------------------------+
Using Regular Expression in MySQL
Regular expression is a powerful tool which gives you a concise and flexible way to identify strings of text, for instance characters, words or patterns of characters. As an example, you can use regular expression to find email, IP address, phone number, social security number… which has their own patterns. In computing world, regular expression usually refers as regex in short. Regular expression uses its own language with special syntax which can be interpreted by a regular expression processor. Regular expression is now widely use in almost platform from programming language to database world. MySQL built-in supports regular expression which allows you to search for strings of text by using special operator call REGEXP. Here is the syntax:
SELECT column_list
FROM table_name
WHERE column REGEXP pattern
The advantage of using regular expression is you are not limited to search for a string based on a fixed pattern in comparison with LIKE operator. In addition, patterns in regular expression can help you to reduce the lengthy SQL statements with AND and OR operators in condition of WHERE clause.
The disadvantage of using regular expression is that it is quite difficult to understand and maintain such a complicated pattern. Therefore you should write comment the meaning of regular expression when you use it in SQL statement. In some cases, the performance of data retrieval may degrade if you use complicated pattern in non-indexed column.
Let’s take a look at an example of using regular expression in MySQL. Suppose you want to find out all employees who has last name starting with M, B or T. we can use regular expression in SQL statement like this:
SELECT lastname,firstname
FROM employees
WHERE lastname REGEXP '^(M|B|T)'Here is the output:
lastname firstname
-------- ---------
Murphy Diane
Bondur Gerard
Bow Anthony
Thompson Leslie
Tseng Foon Yue
Bondur Loui
Bott Larry
Marsh Peter
The pattern says find all last name which has the initial characters is started with M or (|) B or T.
SELECT column_list
FROM table_name
WHERE column REGEXP pattern
The advantage of using regular expression is you are not limited to search for a string based on a fixed pattern in comparison with LIKE operator. In addition, patterns in regular expression can help you to reduce the lengthy SQL statements with AND and OR operators in condition of WHERE clause.
The disadvantage of using regular expression is that it is quite difficult to understand and maintain such a complicated pattern. Therefore you should write comment the meaning of regular expression when you use it in SQL statement. In some cases, the performance of data retrieval may degrade if you use complicated pattern in non-indexed column.
Let’s take a look at an example of using regular expression in MySQL. Suppose you want to find out all employees who has last name starting with M, B or T. we can use regular expression in SQL statement like this:
SELECT lastname,firstname
FROM employees
WHERE lastname REGEXP '^(M|B|T)'Here is the output:
lastname firstname
-------- ---------
Murphy Diane
Bondur Gerard
Bow Anthony
Thompson Leslie
Tseng Foon Yue
Bondur Loui
Bott Larry
Marsh Peter
The pattern says find all last name which has the initial characters is started with M or (|) B or T.
MySQL Copying Data from one Table to a New One
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'
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'
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 1The 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 nThe 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
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 1The 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 nThe 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
SQL LIKE Operator in MySQL
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 have last 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 to search 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 wildcard characters 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 have first 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'
■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 have last 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 to search 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 wildcard characters 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 have first 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 with 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_rangeMySQL 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_rangeLet’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 DESCHere 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
SELECT column_list
FROM table_name
WHERE column_1 BETWEEN lower_range AND upper_rangeMySQL 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_rangeLet’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 DESCHere 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 us to select values which match any one of a list of values. The use 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 select, 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, we 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 we want to find out all offices which locate in US and France, we 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 country which does not locate in USA and France, we can use NOT IN in 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 subquery. For example, if we want to find out all orders in orders table which has total cost greater than 60000, we can use SQL IN with subquery.
First to select all order which has total cost greater than 60000$, we can retrieve it from orderDetails table as follows:
SELECT orderNumber
FROM orderDetails
GROUP BY orderNumber
HAVING SUM (quantityOrdered * priceEach) > 60000Second we use subquery 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)
We get all orders which has 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 |+-------------+----------------+---------+---------------------+
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 select, 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, we 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 we want to find out all offices which locate in US and France, we 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 country which does not locate in USA and France, we can use NOT IN in 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 subquery. For example, if we want to find out all orders in orders table which has total cost greater than 60000, we can use SQL IN with subquery.
First to select all order which has total cost greater than 60000$, we can retrieve it from orderDetails table as follows:
SELECT orderNumber
FROM orderDetails
GROUP BY orderNumber
HAVING SUM (quantityOrdered * priceEach) > 60000Second we use subquery 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)
We get all orders which has 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 employeesBecause 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 employeesThe 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(*) = 1This 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)
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 employeesBecause 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 employeesThe 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(*) = 1This 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)
Creating and Removing 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 an indexes 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 by MySQL. 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_nameFor 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
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 an indexes 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 by MySQL. 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_nameFor 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_nameThere 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.
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_nameThere 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.
Working with Tables - Part I
Creating Tables
To create table we use the CREATE TABLE statement. The usual form of this statement is:
CREATE TABLE [IF NOT EXISTS] table_name(
column_list
) type=table_typeMySQL supports IF NOT EXISTS after CREATE TABLE statement to prevent you from error to create table which already exists on the database server. table_name is the name of table you would like to create. After that, you can define a set of columns which is usually in this form: column_name data_type(size) [NOT] NULL. And finally, you can specify the storage engine type you prefer to use for the table. MySQL supports various storage engines such as InnoDB, MyISAM... If you don't explicit declare storage engine type, MySQL will use MyISAM by default.
In our classicmodels sample database, to create employees table we can apply the statement above as follows:
CREATE TABLE employees (
employeeNumber into(11) NOT NULL,
lastName varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
extension varchar(10) NOT NULL,
email varchar(100) NOT NULL,
officeCode varchar(10) NOT NULL,
reportsTo int(11) default NULL,
jobTitle varchar(50) NOT NULL,
PRIMARY KEY (employeeNumber)
);You specify table name employees after CREATE TABLE statement. Then columns list with its characteristics followed such as data type, size, NOT NULL or not; And finally you can specify the primary key of the table, in this case the primary key is employeeNumber. If the table has more than one primary key, you can seperate them by a comma. For example, the payments table has two primary keys customerNumber and checkNumber, you can create it by execute following query:
CREATE TABLE payments (
customerNumber int(11) NOT NULL,
checkNumber varchar(50) NOT NULL,
paymentDate datetime NOT NULL,
amount double NOT NULL,
PRIMARY KEY (customerNumber,checkNumber)
);By default, we use MyISAM storage engine for the table we created.
Showing and Describing Tables in a Database
In order to show all tables in a database you use SHOW TABLES statment, the server will returns all tables name of the current selected database you work with.
SHOW TABLESHere is the output of classicmodels database
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers |
| employees |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
+-------------------------+
8 rows in set (0.00 sec)
In some cases, you need to see the table characteristics or table metadata, you can use DESCRIBE statement as follows:
DESCRIBE employees;for instance, we can describe employees table like this query
DESCRIBE employees;The output return from the database server:
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| extension | varchar(10) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| officeCode | varchar(10) | NO | | NULL | |
| reportsTo | int(11) | YES | | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
8 rows in set (0.02 sec
To create table we use the CREATE TABLE statement. The usual form of this statement is:
CREATE TABLE [IF NOT EXISTS] table_name(
column_list
) type=table_typeMySQL supports IF NOT EXISTS after CREATE TABLE statement to prevent you from error to create table which already exists on the database server. table_name is the name of table you would like to create. After that, you can define a set of columns which is usually in this form: column_name data_type(size) [NOT] NULL. And finally, you can specify the storage engine type you prefer to use for the table. MySQL supports various storage engines such as InnoDB, MyISAM... If you don't explicit declare storage engine type, MySQL will use MyISAM by default.
In our classicmodels sample database, to create employees table we can apply the statement above as follows:
CREATE TABLE employees (
employeeNumber into(11) NOT NULL,
lastName varchar(50) NOT NULL,
firstName varchar(50) NOT NULL,
extension varchar(10) NOT NULL,
email varchar(100) NOT NULL,
officeCode varchar(10) NOT NULL,
reportsTo int(11) default NULL,
jobTitle varchar(50) NOT NULL,
PRIMARY KEY (employeeNumber)
);You specify table name employees after CREATE TABLE statement. Then columns list with its characteristics followed such as data type, size, NOT NULL or not; And finally you can specify the primary key of the table, in this case the primary key is employeeNumber. If the table has more than one primary key, you can seperate them by a comma. For example, the payments table has two primary keys customerNumber and checkNumber, you can create it by execute following query:
CREATE TABLE payments (
customerNumber int(11) NOT NULL,
checkNumber varchar(50) NOT NULL,
paymentDate datetime NOT NULL,
amount double NOT NULL,
PRIMARY KEY (customerNumber,checkNumber)
);By default, we use MyISAM storage engine for the table we created.
Showing and Describing Tables in a Database
In order to show all tables in a database you use SHOW TABLES statment, the server will returns all tables name of the current selected database you work with.
SHOW TABLESHere is the output of classicmodels database
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers |
| employees |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
+-------------------------+
8 rows in set (0.00 sec)
In some cases, you need to see the table characteristics or table metadata, you can use DESCRIBE statement as follows:
DESCRIBE employees;for instance, we can describe employees table like this query
DESCRIBE employees;The output return from the database server:
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| employeeNumber | int(11) | NO | PRI | NULL | |
| lastName | varchar(50) | NO | | NULL | |
| firstName | varchar(50) | NO | | NULL | |
| extension | varchar(10) | NO | | NULL | |
| email | varchar(100) | NO | | NULL | |
| officeCode | varchar(10) | NO | | NULL | |
| reportsTo | int(11) | YES | | NULL | |
| jobTitle | varchar(50) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
8 rows in set (0.02 sec
Understanding MySQL Table Types
MySQL supports various of table types or storage engines to allow you to optimize your database. The table types are available in MySQL are:
■ISAM
■MyISAM
■InnoDB
■BerkeleyDB (BDB)
■MERGE
■HEAP
The most important feature to make all the table types above distinction is transaction-safe or not. Only InnoDB and BDB tables are transaction safe and only MyISAM tables support full-text indexing and searching feature. MyISAM is also the default table type when you create table without declaring which storage engine to use. Here are some major features of each table types:
ISAM
ISAM had been deprecated and removed from version 5.x. All of it functionality entire replace by MyISAM. ISAM table has a hard size 4GB and is not portable.
MyISAM
MyISAM table type is default when you create table. MyISAM table work very fast but not transaction-safe. The size of MyISAM table depends on the operating system and the data file are portable from system to system. With MyISAM table type, you can have 64 keys per table and maximum key length of 1024 bytes.
InnoDB
Different from MyISAM table type, InnoDB table are transaction safe and supports row-level locking. Foreign keys are supported in InnoDB tables. The data file of InnoDB table can be stored in more than one file so the size of table depends on the disk space. Like the MyISAM table type, data file of InnoDB is portable from system to system. The disadvantage of InnoDB in comparison with MyISAM is it take more disk space.
BDB
BDB is similar to InnoDB in transaction safe. It supports page level locking but data file are not portable.
MERGE
Merge table type is added to treat multiple MyISAM tables as a single table so it remove the size limitation from MyISAM tables.
HEAP
Heap table is stored in memory so it is the fastest one. Because of storage mechanism, the data will be lost when the power failure and sometime it can cause the server run out of memory. Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.
■ISAM
■MyISAM
■InnoDB
■BerkeleyDB (BDB)
■MERGE
■HEAP
The most important feature to make all the table types above distinction is transaction-safe or not. Only InnoDB and BDB tables are transaction safe and only MyISAM tables support full-text indexing and searching feature. MyISAM is also the default table type when you create table without declaring which storage engine to use. Here are some major features of each table types:
ISAM
ISAM had been deprecated and removed from version 5.x. All of it functionality entire replace by MyISAM. ISAM table has a hard size 4GB and is not portable.
MyISAM
MyISAM table type is default when you create table. MyISAM table work very fast but not transaction-safe. The size of MyISAM table depends on the operating system and the data file are portable from system to system. With MyISAM table type, you can have 64 keys per table and maximum key length of 1024 bytes.
InnoDB
Different from MyISAM table type, InnoDB table are transaction safe and supports row-level locking. Foreign keys are supported in InnoDB tables. The data file of InnoDB table can be stored in more than one file so the size of table depends on the disk space. Like the MyISAM table type, data file of InnoDB is portable from system to system. The disadvantage of InnoDB in comparison with MyISAM is it take more disk space.
BDB
BDB is similar to InnoDB in transaction safe. It supports page level locking but data file are not portable.
MERGE
Merge table type is added to treat multiple MyISAM tables as a single table so it remove the size limitation from MyISAM tables.
HEAP
Heap table is stored in memory so it is the fastest one. Because of storage mechanism, the data will be lost when the power failure and sometime it can cause the server run out of memory. Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.
Manage Database in MySQL
Creating Database
To create a database in MySQL, you use the CREATE DATABASE statement as follows:
CREATE DATABASE [IF NOT EXISTS] database_name;CREATE DATABASE statement will create the database with the given name you specified. IF NOT EXISTS is an option part of the statement, this part prevents you from error if there is a database with the given name exists on the database server. In our tutorial, for example, to create classicmodels database, you just only apply CREATE DATABASE statement above as follows:
CREATE DATABASE classicmodels; After executing the statement, the MySQL will returns you a message to indicate whether the execution are successful or not.
Showing Databases
SHOW DATABASE statement will show all databases in your server. You can use this statement to check the database you've created or to see all the databases' name on the server before you create a new database.
SHOW DATABASES;On my server, the output is :
+--------------------+
| Database |
+--------------------+
| information_schema |
| classicmodels |
| mysql |
+--------------------+
8 rows in set (0.00 sec)
Selecting Database
To select a database which you will work with, you use this statement
USE database_name;you can select our sample database by using USE statement as follows:
USE classicmodels;From now you can query the tables' data and do whatever you want inside the selected database
Removing Database
Removing database means you delete the database, all the data and related objects inside the database permanently and cannot undo it. So it is very important to execute this query with cares. To remove the database you can use DROP DATABASE statement as follows :
DROP DATABASE [IF EXISTS] database_name;Like CREATE DATABASE statement, IF EXIST part is an optional part to prevents you from removing database which is not existed. In order to practice with DROP DATABASE statement, you can create a temporary database, show the database on the database server, and drop it step by step as follows :
CREATE DATABASE IF NOT EXISTS temp_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS temp_database;
To create a database in MySQL, you use the CREATE DATABASE statement as follows:
CREATE DATABASE [IF NOT EXISTS] database_name;CREATE DATABASE statement will create the database with the given name you specified. IF NOT EXISTS is an option part of the statement, this part prevents you from error if there is a database with the given name exists on the database server. In our tutorial, for example, to create classicmodels database, you just only apply CREATE DATABASE statement above as follows:
CREATE DATABASE classicmodels; After executing the statement, the MySQL will returns you a message to indicate whether the execution are successful or not.
Showing Databases
SHOW DATABASE statement will show all databases in your server. You can use this statement to check the database you've created or to see all the databases' name on the server before you create a new database.
SHOW DATABASES;On my server, the output is :
+--------------------+
| Database |
+--------------------+
| information_schema |
| classicmodels |
| mysql |
+--------------------+
8 rows in set (0.00 sec)
Selecting Database
To select a database which you will work with, you use this statement
USE database_name;you can select our sample database by using USE statement as follows:
USE classicmodels;From now you can query the tables' data and do whatever you want inside the selected database
Removing Database
Removing database means you delete the database, all the data and related objects inside the database permanently and cannot undo it. So it is very important to execute this query with cares. To remove the database you can use DROP DATABASE statement as follows :
DROP DATABASE [IF EXISTS] database_name;Like CREATE DATABASE statement, IF EXIST part is an optional part to prevents you from removing database which is not existed. In order to practice with DROP DATABASE statement, you can create a temporary database, show the database on the database server, and drop it step by step as follows :
CREATE DATABASE IF NOT EXISTS temp_database;
SHOW DATABASES;
DROP DATABASE IF EXISTS temp_database;
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-de-gook 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. An individual column must be created for each type of data you wish to store (i.e. Age, Weight, Height).
On the other hand, a row contains the actual values for these specified columns. Each row will have 1 value for each and every column. For example a table with columns (Name, Age, Weight-lbs) could have a row with the values (Bob, 65, 165). If all this relational database talk is too confusing, don't despair. We will talk about and show a few examples in the coming lessons.
Databases are most useful when it comes to storing information that fits into logical 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.
In a relational structured database there are tables that store data. The columns define which kinds of information will be stored in the table. An individual column must be created for each type of data you wish to store (i.e. Age, Weight, Height).
On the other hand, a row contains the actual values for these specified columns. Each row will have 1 value for each and every column. For example a table with columns (Name, Age, Weight-lbs) could have a row with the values (Bob, 65, 165). If all this relational database talk is too confusing, don't despair. We will talk about and show a few examples in the coming lessons.
Databases are most useful when it comes to storing information that fits into logical 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.
Subscribe to:
Posts (Atom)
