Categories
SQL / MySQL Tips and Tutorials

SQL: CREATE TABLE … SELECT … to generate new table from existing tables

CREATE TABLE … SELECT comes rather useful when you need to generate reports from existing tables or do a mixture. The title just shows the basic usage of it, for instance, to create a new table xxx from 2 columns in yyy, namely col1, col2.

After executing SQL:

CREATE TABLE xxx SELECT col1, col2 FROM yyy

All the records in yyy with fields col1 and col2 is thus now copied to the new table xxx.

A blending mixture of various columns, stats of columns from various different tables can be then mingled and combined into one table upon your need. Consider the examples below.

CREATE TABLE mingle1 SELECT table1.col1, table1.col3, table2.col2, table2.col6 FROM table1, table2

This query selects different columns from 2 tables into one table mingle1.

CREATE TABLE stats1 SELECT table1.col1, COUNT(table2.col2) WHERE table2.col3 = table1.col1 GROUP BY table2.col3

This query does a little statistic job by counting the number of all unique table2.col2 records on each table1.col1 and put the data into a new table stats1.

Categories
Linux Server Administration Tips SQL / MySQL Tips and Tutorials

Backup and recover a MySQL database under command line

Yes you can log into phpmyadmin, backup your database by simply using Export and recover it by using Import. However all lamp servers come with a max upload file size, limited by the smaller of upload_max_filesize and post_max_size while also restricted by php.ini configurations such as memory_limit and max_input_time. What if you’ve got a huge database of hundreds of thousands of records taking up a few dozens of megabytes or even more?

Use MySQL command line to fulfill the same task.

Backup a database to sqlfile.sql:

mysqldump -h localhost -u username -p dbname > sqlfile.sql

After dumping it as a SQL text file, you may want to first zip it using the following command:

tar zcf sqlfile.sql.tar.gz sqlfile.sql

And download it to your local computer to be kept safe. When an emergency occurs, upload sqlfile.sql.tar.gz to your web server and unzip it:

tar zxf sqlfile.sql.tar.gz

Now that you have sqlfile.sql, you may proceed to restore it back into database, using the command below.

Recover a database from sqlfile.sql:

mysql -h localhost -u username -p dbname < sqlfile.sql

Beware of the less than / greater than mark that’s representing operation directions.

Change localhost to your own mysql server address, username to your own mysql account username and dbname to the name of the database to be backed up or recovered. Straight enough.

Also notice that MySQL user password is not specified in the command line but you will be prompted to enter it after hitting enter.

Categories
SQL / MySQL Tips and Tutorials

Using SQL to Find records existing in one table but not in another

The 2 tables are built with an identical data structure. Say you have records spread in both tables but neither of them are complete because both contain unique records. You need to consolidate the 2 tables into one with all unique records from each table.

NOT EXISTS clause is just the way to do it in SQL. Let’s try:

SELECT * FROM table2 WHERE NOT EXISTS (SELECT * FROM table1 WHERE id = table2.id)

This will select all records from table2 that don’t exist in table1 by id. To get a complete table of unique records from both table1 and table2, you need to just insert the results here into table1.

INSERT INTO table1 (SELECT * FROM table2 WHERE NOT EXISTS (SELECT * FROM table1 WHERE id = table2.id))