SQL

Basic

Create a table
--Create a table called t1 which has 2 columns, col1 and col2. 
--col1 is defined to allow only 1 characters and col2 is defined to allow integer.
CREATE TABLE t1( col1 CHAR(1), col2 INT);
Insert data in table
INSERT INTO t1 VALUES('A', 123); -- Insert 'A' and 123 into table t1.
INSERT INTO t1 VALUES('Z', 23 ); -- Insert 'Z' and 23 into table t1.
INSERT INTO t1 VALUES('B', 23 ); -- Insert 'Z' and 23 into table t1.
View data in table
--View data of 1st column.       View data of 1st & 2nd column.       View data of all columns(*).
SELECT col1 FROM t1;             SELECT col1, col2 FROM t1;           SELECT * FROM t1;
-- Results:                      Results:                             Results:
-- +------+                      +------+------+                      +------+------+
-- | col1 |                      | col1 | col2 |                      | col1 | col2 |
-- +------+                      +------+------+                      +------+------+
-- | A    |                      | A    |  123 |                      | A    |  123 |
-- | Z    |                      | Z    |   23 |                      | Z    |   23 |
-- | B    |                      | B    |   23 |                      | B    |   23 |
-- +------+                      +------+------+                      +------+------+ 
Delete rows in table
-- Delete from table t1 where records in col1 is equal to 'Z'
DELETE FROM t1 WHERE col1='Z';
 
-- Delete all rows from table t1.
DELETE FROM t1;
Change value of records in table
-- Change all records which has value 123 to 555.
UPDATE t1 SET col2=555 WHERE col2=123;
 
-- Change value of all records of col1 to X.
UPDATE t1 SET col1='X';

Useful Statements

SELECT INTO
Create a new table with results returned by SELECT statement. This statement is very useful because you don't have to defined beforehand the description of the table specified.
--The important thing to remember here is to insert INTO after SELECT.
--The INNER JOIN serves as an example. You can make the SELECT query as complex as you want.
SELECT *
INTO MyNewTable
FROM TableA INNER JOIN TableB ON TableA.col = TableB.col;
LEFT JOIN
Return all records of the left table, and fill them with NULL values if it can’t find a matching record on the right table.
RIGHT JOIN
RIGHT join is the opposite of LEFT join. It will return all records of the right table, and fill them with NULL values if it can't find a matching record on the left table.
IN() operator
DELETE TableA.*
FROM TableA
WHERE TableA.Col IN (
        SELECT TableB.Col
        FROM TableB
        GROUP BY TableB.Col
        )
;
--Make sure that the nested select doesn't refer to the table that you will delete from.

MySQL

Administration Grant privileges syntax to allow users to access defined databases from defined IPs. After the GRANT command is executed, you have to flush privileges for the changes to be effective. The following commands should be ran in MySQL shell.

--Grant all privileges to root from localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
 
--Grant read-only privilege on mydatabasename to user.
GRANT SELECT ON mydatabasename.* TO 'user'@'192.168.0.101' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
 
--Show granted privileges of user.
SHOW GRANTS FOR 'user'@'192.168.0.101';
 
--Delete guest user from MySQL
DELETE FROM mysql.user WHERE User = 'guest';


Misc. commands

mysqladmin -uusername -ppassword create databasename
Create database
mysqladmin -uusername -ppassword -f drop databasename
Delete database
mysql -uusername -ppassword databasename < SQLcommands.sql
Execute sql commands from SQLcommands.sql.
mysql -uusername -ppassword databasename <<< "SELECT * FROM aTable;"
Execute SQL commands from string.
mysql -uusername -ppassword databasename -e "SELECT * FROM aTable;"
Execute SQL commands from string.
mysqldump -uusername -ppassword databasename > filename.sql
Export data of databasename to filename.sql.
mysqldump --opt --user=username --password=password databasename > filename.sql
--opt, it does a few things, dumps each table one at a time, tells MySQL not to load the whole database into memory during the dump, optimizes the table
Add a column called "colname" to the table
ALTER TABLE yourTableName ADD colname VARCHAR(30);
Rename a column in the table from "address" to "home_address"
ALTER TABLE p_addr CHANGE address home_address INTEGER; Note: You cannot use this method to rename a column which is a primary key.
Change column definition to VARCHAR(111)
ALTER TABLE yourTableName MODIFY colname VARCHAR(111);
Copy from original_table to new_table
CREATE TABLE new_table SELECT * FROM original_table;
Insert rows from table(t1) to another table(t2)
INSERT t1 SELECT * FROM t2;
Selectively dump tables
mysqldump -t -u[username] -p[password] [DATABASE] [TABLE] --where="[columname]='some_value'" > filename.sql
Drop all tables
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
Save results/outputs to file
\T c:\temp\filename.txt

Mysqldump: --no-create-info : Output only INSERT statements

Backup MySQL database using Ant

The Ant backup script example below is designed to backup a MySQL database name called Test. Before you run this script, change the db.username, db.password, db.name and the path of mysqldump accordingly. To backup, run the following in the Command prompt:

ant -f build_backup.xml backup_db

Here is the Ant backup script for MySQL:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!-- 
Author: Xuan Ngo
Description: Backup MySQL database name called Test and put in it archive_db/Test_YYYY-MM-DD_HH.MM.SS directory.
              Before you run this script, change the username, password and the path of mysqldump accordingly.
-->
<project basedir="." default="nothing" name="Test">
 
  <!-- Change the following properties accordingly. -->
  <property name="db.username" value="root"/>
  <property name="db.password" value="mypassword"/>
  <property name="db.name" value="${ant.project.name}"/><!-- The name of the database to backup. -->
  <property name="mysqldump" value="C:\wamp\bin\mysql\mysql5.1.33\bin\mysqldump"/><!-- Path to mysqldump program. -->
 
  <property name="archive.dir" value="archive_db"/>
  <tstamp><format property="DAY_TIME_NOW" pattern="yyyy-MM-dd_HH.mm.ss" /></tstamp>
 
  <target name="nothing">
    <echo message="By default, it does nothing."/>
  </target>
 
  <!-- ***************************************
  Backup MySQL database 
  **************************************** -->
  <target name="backup_db">
    <mkdir dir="${archive.dir}"/><!-- Create the archive directory anyway. -->
 
    <!-- Create a directory to dump the backup -->
    <property name="backup.dir" value="${archive.dir}/${ant.project.name}_${DAY_TIME_NOW}"/>
    <mkdir dir="${backup.dir}"/>
 
    <!-- Command to dump the database to *.sql file.-->
    <exec executable="${mysqldump}" output="${backup.dir}/${ant.project.name}_${DAY_TIME_NOW}.sql">
      <arg value="--hex-blob"/>
      <arg value="--extended-insert=false"/>
      <arg value="--complete-insert=true"/>
      <arg value="--user=${db.username}"/>
      <arg value="--password=${db.password}"/>
      <arg value="${db.name}"/>
    </exec>
 
    <!-- Compress the dumped file(*.sql) -->
    <tar destfile="${backup.dir}/${ant.project.name}_${DAY_TIME_NOW}.sql.tar.gz" 
          compression="gzip">
      <tarfileset dir="${backup.dir}">
        <include name="${ant.project.name}_${DAY_TIME_NOW}.sql"/>
      </tarfileset>
    </tar>
 
    <!-- Delete the dumped file(*.sql) -->
    <delete file="${backup.dir}/${ant.project.name}_${DAY_TIME_NOW}.sql"/>
  </target>
 
</project>
AttachmentSize
File build_backup.xml2.23 KB

Backup your mysql database and website of Bluehost

#!/bin/bash
 
############################################################################
# Author: Xuan Ngo
# Description: Backup your mysql database and website from Bluehost.com
############################################################################
 
DOMAIN_NAME=$1
WWW_ROOT=$2
BACKUP_ROOT_PATH=$3
MYSQL_HOST=$4
MYSQL_USER=$5
MYSQL_PWD=$6
MYSQL_DBNAME=$7
 
BACKUP_DIR_NAME=${DOMAIN_NAME}_`date +"%Y-%m-%d_%0k.%M.%S"`
BACKUP_DIR_PATH=${BACKUP_ROOT_PATH}/${BACKUP_DIR_NAME}
 
# Create backup directory
mkdir -p ${BACKUP_DIR_PATH}
 
# Get time as a UNIX timestamp (seconds elapsed since Jan 1, 1970 0:00 UTC)
T="$(date +%s)"
 
# Backup databases
mysqldump --hex-blob --extended-insert=false --complete-insert=true -u ${MYSQL_USER} -p${MYSQL_PWD} -h ${MYSQL_HOST} ${MYSQL_DBNAME} > ${MYSQL_DBNAME}.sql
tar -cjf ${BACKUP_DIR_PATH}/${BACKUP_DIR_NAME}.sql.tar.bz2 ${MYSQL_DBNAME}.sql
rm -f ${MYSQL_DBNAME}.sql
 
# Backup website
cd ${WWW_ROOT}
tar -cjf ${BACKUP_DIR_PATH}/${BACKUP_DIR_NAME}.tar.bz2 .
cd -
 
# Get total elapsed time
T="$(($(date +%s)-T))"
printf "${DOMAIN_NAME}: Backup elapsed time: %02d:%02d:%02d:%02d [DD:HH:MM:SS]\n" "$((T/86400))" "$((T/3600%24))" "$((T/60%60))" "$((T%60))" > ${BACKUP_DIR_PATH}/${BACKUP_DIR_NAME}.info.txt

Best command to backup MySQL database

Best command to backup MySQL database

mysqldump --hex-blob --extended-insert=false --complete-insert=true -uUSERNAME -pPASSWORD DATABASENAME > backup.sql
--hex-blob
Ensure that blob columns are exported correctly. MySQL will convert BLOB to hexadecimal.
--extended-insert=false and --complete-insert=true
Ensure that when backup.sql is imported back, it uses less memory. MySQL will generate a separate INSERT query for every record in every table instead of only generating one INSERT command per table.

If you think you have a better command, please comment.

Clean up MySQL Binary Log files that are taking a lot of space

Clean up mysql-bin.00000* files

msyql> reset master;

BIN log files are created because this option set in MySql configuration file(my.ini):

log-bin=mysql-bin

So, comment the line out by putting # in front of it.

Convert timestamp to date

SELECT DATE(from_unixtime(1244084155)) AS DATE;
--Output: 2009-06-03

Create indexes to speed up SQL query

-- Check what indexes are already defined for a table.
SHOW INDEX FROM tablename;
 
-- Create the index for col1, col2, ...
CREATE INDEX index_name ON tablename(col1, col2, ...);

Create mysql database using Ant

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<project basedir="." default="create_db" name="Create a database in mysql using Ant">
 
  <!-- The following properties need to be changed according to your environment. -->
  <property name="mysqladmin" value="C:\wamp\bin\mysql\mysql5.1.36\bin\mysqladmin.exe"/>
  <property name="mysql.username" value="root"/>
  <property name="mysql.pwd" value="mypassword"/>
  <property name="mysql.dbname" value="mydatabasename"/>
 
  <!-- mysqladmin is used to create a database. -->
  <target name="create_db">
    <exec executable="${mysqladmin}">
      <arg value="--user=${mysql.username}"/>
      <arg value="--password=${mysql.pwd}"/>
      <arg value="CREATE"/>
      <arg value="${mysql.dbname}"/>
    </exec>
  </target>
 
</project>

Delete mysql database using Ant

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<project basedir="." default="delete_db" name="Delete a database in mysql using Ant">
 
  <!-- The following properties need to be changed according to your environment.-->
  <property name="mysqladmin" value="C:\wamp\bin\mysql\mysql5.1.36\bin\mysqladmin.exe"/>
  <property name="mysql.username" value="root"/>
  <property name="mysql.pwd" value="mypassword"/>
  <property name="mysql.dbname" value="mydatabasename"/>
 
  <!-- mysqladmin is used to delete a database. -->
  <target name="delete_db">
    <exec executable="${mysqladmin}">
      <arg value="--user=${mysql.username}"/>
      <arg value="--password=${mysql.pwd}"/>
      <arg value="--force"/>
      <arg value="DROP"/>
      <arg value="${mysql.dbname}"/>
    </exec>
  </target>
 
</project>

Food for thought: Why this query is slower than the other 1?

Queries ran in MySQL:

-- Slow query: 3.6 seconds
SELECT COUNT(*) FROM sessions WHERE uid = 0 
  AND unix_timestamp() TIMESTAMP < (60*15)
 
-- Fast query: 30 milliseconds
SELECT COUNT(*) FROM sessions WHERE uid = 0 
  AND TIMESTAMP > (unix_timestamp() (60*15))

Why the first query is slower than the last one?

Get the size used by each database

SELECT table_schema "Database Name"
     , SUM( data_length + index_length ) / 1048576  AS "Database Size in MB"
     , SUM( data_free )/ 1048576  AS "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

Output:

+--------------------+---------------------+------------------+
| Database Name      | Database Size in MB | Free Space in MB |
+--------------------+---------------------+------------------+
| bt_96580           |              1.1242 |           0.0014 |
| drupal             |              2.4711 |           0.0006 |
| information_schema |              0.0078 |           0.0000 |
| mysql              |              0.5875 |           0.0000 |
| rth                |              0.1172 |           0.0000 |
| test               |              0.0011 |           0.0000 |
| tp                 |              4.3792 |           0.0000 |
+--------------------+---------------------+------------------+

Got a packet bigger than 'max_allowed_packet' bytes

When you are importing a huge *.sql file into MySQL, it sometimes shows the following error message:

mysql -u root -p databasename < huge.sql
ERROR:...Got a packet bigger than 'max_allowed_packet' bytes ...

Simply open my.ini and increase max_allowed_packet and net_buffer_length variables.

max_allowed_packet = 10M
net_buffer_length = 2M


Update: 2010-08-17: I got the freaking error mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when du' at line 1. Naturally, I edited my my.ini and increased the max_allowed_packet and net_buffer_length variables. But it didn't work. My *.sql file is 55 MB and I even increase max_allowed_packet up to 512 MB. But, it still returns the same error. I opened my *.sql file and found out that mysqldump generates only one INSERT command per table, resulting in one very long line of inserted data for each table that got dumped. So, I tried to re-export my database and this time, I requested mysqldump to export a separate INSERT query for every record in every table. I then imported it back and it worked. Here are the commands:

# Export a separate INSERT query for every record in every table
mysqldump --extended-insert=FALSE --complete-insert=TRUE -uUsername -p databasename > s.sql
 
# Import the database back.
mysql -uUsername -p databasename < s.sql

Insert a record only if it doesn't exist

--DROP TABLE person;
CREATE TABLE person
(
  id INT NOT NULL AUTO_INCREMENT,
  firstname VARCHAR(255),
  lastname VARCHAR(255),
  PRIMARY KEY (id)
);
 
INSERT INTO person(firstname, lastname) VALUES('John', 'Smith');
INSERT INTO person(firstname, lastname) VALUES('Lisa', 'Simpson');
 
-- Here is the single query where it checks whether the record is a duplicate before inserting it into the database.
--  If (SELECT * FROM person WHERE firstname='John' and lastname='Smith') exists, then add
--  "'John', 'Smith'" in the person table.
--  Note: dual is a dummy table. It is used to make the query syntactically correct.
INSERT INTO person(firstname, lastname) SELECT 'John', 'Smith' FROM dual WHERE NOT EXISTS 
(SELECT * FROM person WHERE firstname='John' AND lastname='Smith');
 
INSERT INTO person(firstname, lastname) SELECT 'Xuan', 'Ngo' FROM dual WHERE NOT EXISTS 
(SELECT * FROM person WHERE firstname='Xuan' AND lastname='Ngo');
 
SELECT * FROM person;

Put a table into memory

CREATE TABLE new_table ENGINE=MEMORY AS SELECT * FROM old_table;

Restore MySQL database using Ant

The Ant restore script example below is designed to restore a MySQL database name called Test. Before you run this script, change the username, password and the path of mysqldump accordingly. Also, you have to ensure that your compressed backup file is located in archive_db/Test_YYYY-MM-DD_HH.MM.SS.sql.tar.gz. To restore, run the following in the command prompt:

ant -f build_restore.xml restore_db -Drestore.dir=Test_YYYY-MM-DD_HH.MM.SS

Here is the Ant restore script for MySQL:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!-- 
Author: Xuan Ngo
Description: Restore MySQL database name called Test.
              Before you run this script, change the username, password and the path of mysqldump accordingly.
Usage: Your compressed backup file should be in .../archive_db/Test_YYYY-MM-DD_HH.MM.SS.sql.tar.gz
        Run
          ant -f build_restore.xml restore_db -Drestore.dir=Test_YYYY-MM-DD_HH.MM.SS
-->
<project basedir="." default="nothing" name="Test">
 
  <!-- Change the following properties accordingly. -->
  <property name="db.username" value="root"/>
  <property name="db.password" value="mypassword"/>
  <property name="db.name" value="${ant.project.name}"/><!-- The name of the database to backup. -->
  <property name="mysql" value="C:\wamp\bin\mysql\mysql5.1.33\bin\mysql"/><!-- Path to mysql program. -->
  <property name="mysqladmin" value="C:\wamp\bin\mysql\mysql5.1.33\bin\mysqladmin"/><!-- Path to mysqladmin program. -->
 
  <property name="archive.dir" value="archive_db"/>
  <tstamp><format property="DAY_TIME_NOW" pattern="yyyy-MM-dd_HH.mm.ss" /></tstamp>
 
  <target name="nothing">
    <echo>By default, it does nothing.</echo>
  </target>
 
  <!-- ***************************************
  Restore MySQL database 
  **************************************** -->  
  <target name="restore_db">
    <!-- Clean up the database by deleting and then creating it. -->
    <antcall target="delete_db"/>
    <antcall target="create_db"/>
 
    <!-- Decompress the backup file. -->
    <untar dest="." compression="gzip" src="${archive.dir}/${restore.dir}/${restore.dir}.sql.tar.gz"/>
 
    <!-- Import data back to the database from the decompressed backup file. -->
    <exec executable="${mysql}" input="${restore.dir}.sql">
      <arg value="--user=${db.username}"/>
      <arg value="--password=${db.password}"/>
      <arg value="${db.name}"/>
    </exec>
 
    <!-- Delete decompressed backup file -->
    <delete file="${restore.dir}.sql"/>
  </target>
 
  <target name="delete_db">
    <exec executable="${mysqladmin}">
      <arg value="--user=${db.username}"/>
      <arg value="--password=${db.password}"/>
      <arg value="--force"/>
      <arg value="drop"/>
      <arg value="${db.name}"/>
    </exec>
  </target>
 
  <target name="create_db">
    <exec executable="${mysqladmin}">
      <arg value="--user=${db.username}"/>
      <arg value="--password=${db.password}"/>
      <arg value="--force"/>
      <arg value="create"/>
      <arg value="${db.name}"/>
    </exec>
  </target>
 
</project>
AttachmentSize
File build_restore.xml2.58 KB

Search and replace string

UPDATE tableName SET fieldname = REPLACE(fieldname, 'replace this string', 'with that string');
 
--Replace Smth with Smith
UPDATE Person SET lastname = REPLACE(lastname, 'Smth', 'Smith');