MySQL Point

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.

MySQL CONCAT function is used to concatenate two strings to form a single string. Try out following example:

mysql> SELECT CONCAT('FIRST ', 'SECOND');
+----------------------------+
| CONCAT('FIRST ', 'SECOND') |
+----------------------------+
| FIRST SECOND               |
+----------------------------+
1 row in set (0.00 sec)

To understand CONCAT function in more detail consider an employee_tbl table which is having following records:

mysql> SELECT * FROM employee_tbl;
+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
|    1 | John | 2007-01-24 |                250 |
|    2 | Ram  | 2007-05-27 |                220 |
|    3 | Jack | 2007-05-06 |                170 |
|    3 | Jack | 2007-04-06 |                100 |
|    4 | Jill | 2007-04-06 |                220 |
|    5 | Zara | 2007-06-06 |                300 |
|    5 | Zara | 2007-02-06 |                350 |
+------+------+------------+--------------------+
7 rows in set (0.00 sec)
Now suppose based on the above table you want to concatenate all the names employee ID and work_date then you ca do it using following command:
 
mysql> SELECT CONCAT(id, name, work_date)
    -> FROM employee_tbl;
+-----------------------------+
| CONCAT(id, name, work_date) |
+-----------------------------+
| 1John2007-01-24             |
| 2Ram2007-05-27              |
| 3Jack2007-05-06             |
| 3Jack2007-04-06             |
| 4Jill2007-04-06             |
| 5Zara2007-06-06             |
| 5Zara2007-02-06             |
+-----------------------------+
7 rows in set (0.00 sec)
 

You can use UNION if you want to select rows one after the other from several tables, or several sets of rows from a single table all as a single result set.

You can use UNION if you want to select rows one after the other from several tables, or several sets of rows from a single table all as a single result set.

UNION is available as of MySQL 4.0.This section illustrates how to use it.

Suppose you have two tables that list prospective and actual customers, a third that lists vendors from whom you purchase supplies, and you want to create a single mailing list by merging names and addresses from all three tables. UNION provides a way to do this. Assume the three tables have the following contents:

mysql> SELECT * FROM prospect;
+---------+-------+------------------------+
| fname   | lname | addr                   |
+---------+-------+------------------------+
| Peter   | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr.          |
+---------+-------+------------------------+
mysql> SELECT * FROM customer;
+-----------+------------+---------------------+
| last_name | first_name | address             |
+-----------+------------+---------------------+
| Peterson  | Grace      | 16055 Seminole Ave. |
| Smith     | Bernice    | 916 Maple Dr.       |
| Brown     | Walter     | 8602 1st St.        |
+-----------+------------+---------------------+
mysql> SELECT * FROM vendor;
+-------------------+---------------------+
| company           | street              |
+-------------------+---------------------+
| ReddyParts, Inc.  | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+-------------------+---------------------+

It does not matter if all the three tables have different column names. The following query illustrates how to select names and addresses from the three tables all at once:

mysql> SELECT fname, lname, addr FROM prospect
-> UNION
-> SELECT first_name, last_name, address FROM customer
-> UNION
-> SELECT company, '', street FROM vendor;
+-------------------+----------+------------------------+
| fname             | lname    | addr                   |
+-------------------+----------+------------------------+
| Peter             | Jones    | 482 Rush St., Apt. 402 |
| Bernice           | Smith    | 916 Maple Dr.          |
| Grace             | Peterson | 16055 Seminole Ave.    |
| Walter            | Brown    | 8602 1st St.           |
| ReddyParts, Inc.  |          | 38 Industrial Blvd.    |
| Parts-to-go, Ltd. |          | 213B Commerce Park.    |
+-------------------+----------+------------------------+
If you want to select all records, including duplicates, follow the first UNION keyword with ALL:

mysql> SELECT fname, lname, addr FROM prospect
-> UNION ALL
-> SELECT first_name, last_name, address FROM customer
-> UNION
-> SELECT company, '', street FROM vendor;
+-------------------+----------+------------------------+
| fname             | lname    | addr                   |
+-------------------+----------+------------------------+
| Peter             | Jones    | 482 Rush St., Apt. 402 |
| Bernice           | Smith    | 916 Maple Dr.          |
| Grace             | Peterson | 16055 Seminole Ave.    |
| Bernice           | Smith    | 916 Maple Dr.          |
| Walter            | Brown    | 8602 1st St.           |
| ReddyParts, Inc.  |          | 38 Industrial Blvd.    |
| Parts-to-go, Ltd. |          | 213B Commerce Park.    |
+-------------------+----------+------------------------+

ANALYZE TABLE analyzes and stores the key distribution for a table. The MySQL query optimizer is the magic inside MySQL that decides which keys, if any, to use to in the query. ANALYZE helps query optimizer to make accurate decisions by detailed analysis of the data, unlike query optimizer which makes quick analysis.

The command to analyze all tables in a running database is:
mysqlcheck -Aa -uroot -p

Provide the password when prompted.

You can add it to your daily / weekly cron job for optimum performance of the database. The password can be provided in command line too.

During the analysis, the table is locked with a read lock for MyISAM and BDB. ANALYZE works with MyISAM, BDB, and InnoDB tables.

Note: Running ANALYZE is equivalent to running myisamchk

Here are few tips (with undocumented features) to help you export huge MS Access database (production database with real data) to MySQL.

Use mdbtools utilities as described below:

mdb-tables displays the list of tables in MS Access

mdb-scheme export the MS Access schema to MySQL database. The documentation doesn’t mention that MySQL is supported. You should use the following command:

mdb-schema [-S] database mysql

You may need to replace column names if they collide with MySQL reserved keywords. few column names I needed to change were: GROUP, PRIMARY & CROSS. I changed the column names to GROUP_, PRIMARY_ & CROSS_ respectively. MySQL error messages are very cryptic (unhelpful).
(more…)

Suppose you want to change and enable the remote access to MySQL DB on your linux server (Debian). By default, you don’t get such privileges so you will have to work your way through this. I have a very simple article for you so that you can enable the remote access. This is a pretty useful trick for all the database admins and controllers.

1. Find my.cnf file. If you don’t know about it, go and search in this path

If you are using Debian Linux file is located at /etc/mysql/my.cnf location
If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf
The my.cnf file will look like this

[mysqld]

If you have never set a root password for MySQL, the server does not require a password at all for connecting as root. To set up a root password for the first time, use the mysqladmin command at the shell prompt as follows:

$ mysqladmin -u root password newpass

If you want to change (or update) a root password, then you need to use the following command:

$ mysqladmin -u root -p oldpassword newpass

I hope this will work for you perfectly.