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.

Comparing tables using LEFT and RIGHT JOINS

Comparing tables using LEFT and RIGHT joins is easy. We will exploit the definitions of the LEFT and RIGHT joins to compare every records in every columns of each table. By definition, the LEFT join will returns all records of the left table even though the ON clause doesn't find any matching records on the right table. The non-matching records in the right table is still returned with the record of the left table but its value is NULLed. The RIGHT join is exactly the same as the LEFT join, except that it returns all records of the right table instead of the left table.

Let's have an example to make it clear. Suppose that we have 2 tables, t1 and t2. The records inside both tables are shown below.

SELECT * FROM t1;         SELECT * FROM t2; 
-- +------+------+        -- +------+------+
-- | c1   | c2   |        -- | c1   | c2   |
-- +------+------+        -- +------+------+
-- | A    |   12 |        -- | A    |   12 |
-- | G    |   62 |        -- | B    |   62 |
-- +------+------+        -- +------+------+

For simplicity, we will only do a LEFT join on the records of the 1st colum(c1). Here are the results:

SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
-- +-------+-------+
-- | t1_c1 | t2_c1 |
-- +-------+-------+
-- | A     | A     | 
-- | G     | NULL  | 
-- +-------+-------+

The only matching records of the 1st column(c1) on both tables is (A, A). Since, there is no G record in the 1st column of the 2nd table, the join is also returning G record of the left table(t1) and a NULL value for right table(t2). Looking at the results, all records of the left table(t1) are returned. Therefore, all records that don't exist in 1st column(t2_c1) of the 2nd table will always have NULL as value. With this characteristic, we simply add a WHERE condition to return only rows that contains NULL value to get non-matching records between both tables.

SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1
WHERE t2.c1 IS NULL;
-- +-------+-------+
-- | t1_c1 | t2_c1 |
-- +-------+-------+
-- | G     | NULL  | 
-- +-------+-------+



Complete SQL script to compare 2 tables

-- DROP TABLE t1;
-- DROP TABLE t2;
 
-- Create tables and insert data.----
-- ----------------------------------
CREATE TABLE t1 (c1 CHAR(1), c2 INT);
  INSERT INTO t1 VALUES( 'A', 12);
  INSERT INTO t1 VALUES( 'G', 62);
 
 
 
CREATE TABLE t2 (c1 CHAR(1), c2 INT);
  INSERT INTO t2 VALUES( 'A', 12);
  INSERT INTO t2 VALUES( 'B', 62);
 
SELECT * FROM t1;
SELECT * FROM t2;
 
 
-- Comparison starts here. ----------
-- ----------------------------------
 
-- LEFT JOIN only
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
 
 
-- LEFT JOIN and filter NULL
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1
WHERE t2.c1 IS NULL;
 
-- RIGHT JOIN only
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;
 
-- RIGHT JOIN and filter NULL
SELECT t1.c1 AS t1_c1, t2.c1 AS t2_c1
FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 IS NULL;

Find rows with duplicate values

The ideal is to group and count the number of rows that have the same values. If there are more than 1 row, then it has duplicates. Here is the syntax of the query:

SELECT column1, column2, columnX, COUNT(*) AS Duplicates FROM tablename
GROUP BY column1, column2, columnX
HAVING COUNT(*) > 1


An example

-- Create a table t.
CREATE TABLE t
(
  a INT,
  b VARCHAR(5),
  c VARCHAR(5)
);
 
-- Insert some values into table t.
INSERT INTO t VALUES(3, 'j', 'h' );
INSERT INTO t VALUES(3, 'j', 'h' );
INSERT INTO t VALUES(3, 'j', 'Xh' );
INSERT INTO t VALUES(6, 'u', 'g' );
INSERT INTO t VALUES(6, 'u', 'g' );
INSERT INTO t VALUES(4, 'y', 'k' );
INSERT INTO t VALUES(1, 'w', 'x' );
 
-- Show values that are being inserted.
SELECT * FROM t ORDER BY a;
 
-- Show duplicate values for column a and b.
SELECT a, b, COUNT(*) AS Duplicates FROM t
GROUP BY a, b
HAVING COUNT(*) > 1;


The output

SELECT * FROM t ORDER BY a;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | w    | x    |
|    3 | j    | h    |
|    3 | j    | h    |
|    3 | j    | Xh   |
|    4 | y    | k    |
|    6 | u    | g    |
|    6 | u    | g    |
+------+------+------+
 
SELECT a, b, count(*) as Duplicates FROM t
GROUP BY a, b
HAVING count(*) > 1;
+------+------+------------+
| a    | b    | Duplicates |
+------+------+------------+
|    3 | j    |          3 |
|    6 | u    |          2 |
+------+------+------------+

Find the highest value among a set

Suppose you have a table containing daily price of stocks. You would like to know which day each stock is the most expensive.

-- Create the table stock_prices.
CREATE TABLE stock_prices (
  symbol CHAR(10) NOT NULL,
  DATE DATE NOT NULL,
  price NUMERIC(12, 4) NOT NULL
);
 
-- Insert some values into stock_prices.
INSERT INTO stock_prices(symbol, DATE, price) VALUES('GOOG', '2008-01-01', '1.00');
INSERT INTO stock_prices(symbol, DATE, price) VALUES('GOOG', '2010-08-01', '1.20');
INSERT INTO stock_prices(symbol, DATE, price) VALUES('GOOG', '2009-07-01', '1.25');
 
INSERT INTO stock_prices(symbol, DATE, price) VALUES('IBM', '2008-03-01', '2.00');
INSERT INTO stock_prices(symbol, DATE, price) VALUES('IBM', '2010-02-01', '1.70');
INSERT INTO stock_prices(symbol, DATE, price) VALUES('IBM', '2009-09-08', '1.95');
 
INSERT INTO stock_prices(symbol, DATE, price) VALUES('YHOO', '2008-03-01', '2.01');
INSERT INTO stock_prices(symbol, DATE, price) VALUES('YHOO', '2010-08-01', '6.80');
INSERT INTO stock_prices(symbol, DATE, price) VALUES('YHOO', '2009-04-07', '3.95');
 
-- Show values that are being inserted.
SELECT * FROM stock_prices;
 
-- The meat here: You have to use LEFT JOIN to compare the price.
SELECT * FROM stock_prices 
LEFT JOIN stock_prices AS stock_prices2 
ON stock_prices.symbol = stock_prices2.symbol AND stock_prices.price < stock_prices2.price
WHERE stock_prices2.price IS NULL;



The output

SELECT * FROM stock_prices;
+--------+------------+--------+
| symbol | date       | price  |
+--------+------------+--------+
| GOOG   | 2008-01-01 | 1.0000 |
| GOOG   | 2010-08-01 | 1.2000 |
| GOOG   | 2009-07-01 | 1.2500 |
| IBM    | 2008-03-01 | 2.0000 |
| IBM    | 2010-02-01 | 1.7000 |
| IBM    | 2009-09-08 | 1.9500 |
| YHOO   | 2008-03-01 | 2.0100 |
| YHOO   | 2010-08-01 | 6.8000 |
| YHOO   | 2009-04-07 | 3.9500 |
+--------+------------+--------+
 
SELECT * FROM stock_prices
LEFT JOIN stock_prices as stock_prices2
ON stock_prices.symbol = stock_prices2.symbol AND stock_prices.price < stock_prices2.price
WHERE stock_prices2.price is null;
+--------+------------+--------+--------+------+-------+
| symbol | date       | price  | symbol | date | price |
+--------+------------+--------+--------+------+-------+
| GOOG   | 2010-08-01 | 1.2000 | NULL   | NULL |  NULL |
| IBM    | 2010-02-01 | 1.7000 | NULL   | NULL |  NULL |
| YHOO   | 2010-08-01 | 6.8000 | NULL   | NULL |  NULL |
+--------+------------+--------+--------+------+-------+

Hibernate

In Eclipse, include the following JARs:

  • hibernate-distribution-3.3.2.GA/hibernate3.jar
  • hibernate-distribution-3.3.2.GA/required/antlr-2.7.6.jar
  • hibernate-distribution-3.3.2.GA/required/commons-collections-3.1.jar
  • hibernate-distribution-3.3.2.GA/required/dom4j-1.6.1.jar
  • hibernate-distribution-3.3.2.GA/required/javassist-3.9.0.GA.jar
  • hibernate-distribution-3.3.2.GA/required/jta-1.1.jar
  • hibernate-distribution-3.3.2.GA/required/slf4j-api-1.5.8.jar
  • slf4j-1.5.8/slf4j-log4j12-1.5.8.jar <-- Not part of Hibernate
  • apache-log4j-1.2.15/log4j-1.2.15.jar <-- Not part of Hibernate
  • mysql-connector-java-5.1.7/mysql-connector-java-5.1.7-bin.jar <-- Not part of Hibernate: Add the JDBC driver of your database

XML Mapping files: hibernate.cfg.xml & all *.hbm.xml should be in the BIN/ folder. To circumvent this: Run Configuration->Classpath->User Entries Advanced...->Add Folder: src/

Multiple primary keys:

<hibernate-mapping>
 
  <class name="org.path.to.class" table="myTablename">
    <composite-id>
      <key-property name="columnName_id_1"/>
      <key-property name="columnName_id_2"/>
      <key-property name="columnName_id_X"/>
    </composite-id> 
 
    <property name="columnName_1" />
    <property name="columnName_2" />
    <property name="columnName_3" />
    <property name="columnName_4" />
    <property name="columnName_X" />
 
  </class>
 
</hibernate-mapping>
public class myTablename implements Serializable
{
...
}

Add this

log4j.logger.org.hibernate.type=trace

in your log properties file to show parametrized values.

hibernate dynamic-update

Eclipse install: http://download.jboss.org/jbosstools/updates/stable/

Data type int, double, float are not nullable

When you use primitive data types(i.e int, double and float) with hibernate, you will not be able to convert them to null because Java doesn't allow them to be null. In order for these data types to be null, use their object counter part.(i.e Integer, Double, Float). However, it is very expensive to use the object data type. Therefore, use them only when needed.

SQL IN() operator

Here is an example of how you define a list of values in hibernate for the SQL IN() operator:

/*
 * Update the gender field of Person table where IDs are equal to 1, 21, 45
 */
Query oQuery = session.createSQLQuery("UPDATE Person SET gender=:gender WHERE id IN(:id_list)");
oQuery.setString("gender", "male");
oQuery.setParameterList("id_list", new Object[]{new Integer(1), new Integer(21), new Integer(45)});
int iRowCnt = oQuery.executeUpdate();

org.hibernate.Query.setInteger VS org.hibernate.Query.setParameter(..., ..., Hibernate.INTEGER)

org.hibernate.Query.setInteger() function doesn't allow your integer to be null whereas org.hibernate.Query.setParameter(..., ..., Hibernate.INTEGER) function allows your integer to be null.

For example, if you are parsing a file to retrieve the age of a person and then update that age into your database. However, the age is not always written in the file. Therefore, the age is nullable. Here is a code example using setParameter() function to deal with nullable integer:

//...
Query oQuery = session.createSQLQuery("UPDATE Person SET age=:age WHERE id:=id");
oQuery.setInteger("id", id); // id is guaranteed to exist and it is an integer.
oQuery.setParameter("age", age, Hibernate.INTEGER); // age may be null or an integer.
//...
int iRowCnt = oQuery.executeUpdate();
//...

MS Access

Useful Functions

Nz( variant, [ value_if_null ] )
Return defined value if variant is null. value_if_null is optional. If value_if_null is not defined, then it will return zero-length string. Example of usage in MS Access Query Design View: Field: Expr1: Nz(AnyColumValue, "Value is null")
Ask for user input in query
You can use square bracket to ask for user input each time that the query is ran.
SELECT TableA.col
FROM TableA
WHERE TableA.col=[Enter a VALUE];
IIF( condition, value_if_true, value_if_false )
If condition is true, then return value_if_true. Otherwise, return value_if_false. Example: IIF([Amount]>100, "Large", "Small")
Format([zipcode],"00000")
Zeros padding.

References http://www.filex.com/htmlhelp/traxhelpMS_Access_Functions.html

MS SQL

Escape underscore(_) in LIKE statement
To escape underscore(_) in LIKE statement, you have to enclose the underscore(_) inside square brackets.
-- Select all values that end with _B.
SELECT *
FROM TableA
WHERE TableA.col LIKE '%[_]B';
Check SQL Server version and edition
SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition');
Copy all data of a table to another 1
--Copy all data of tableName to newTablename
SELECT * INTO newTablename FROM tableName 
 
 
-- Update data of fields from fromTable to toTable where their IDs match
--  This useful when you made a data copy of a table and then you want to revert the original data back.
UPDATE toTable
SET
  toTable.field1 = fromTable.field1, 
  toTable.field2 = fromTable.field2, 
  toTable.field3 = fromTable.field3, 
  toTable.fieldX = fromTable.fieldX, 
 
FROM fromTable
  INNER JOIN toTable ON toTable.field_ID = fromTable.field_ID
Date manipulation
--Example of date manipulation
UPDATE tablename SET dateField=DATEADD(DAY, -1, GETDATE()) WHERE (dateField = CONVERT(DATETIME, '2009-01-30 00:00:00', 102))
Search for column name in database
--Search for column name
SELECT TABLE_NAME=sysobjects.name,
    column_name=syscolumns.name,
    datatype=systypes.name,
    LENGTH=syscolumns.LENGTH
FROM sysobjects
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE syscolumns.name LIKE '%columnName%'

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');

Oracle

Oracle

Check Oracle version
  1. Login to SQL *Plus.
  2. Get the version by running query:
    SELECT * FROM v$version;