Community Pick: Many members of our community have endorsed this article.

Database Developers' Quick-Reference to MySQL


1. Intro

Many developers have database experience, but are new to MySQL. It is increasingly capable and the price is right!

My own experience up to this point is with commercial DBMSs, especially Microsoft's SQL Server. Here are some notes and links I have found helpful. Hopefully they will help you as well.

Blaire Ireland's Introduction to MySQL gives a whirlwind walk through for DB newbies, introducing data types and explaining syntax :

John Coggeshall's MySql Crash Course is similar and also very helpful :

I thought I would share my discoveries for the more experienced DB developers new to the MySQL database. So, here is a quick list to get going.

2. Log Into MySQL from the OS Prompt

    * Linux/Unix ( ):
 mysql_dir/bin/mysql -h hostname -u root -p 

Open in new window

where mysql_dir is replaced by the actual directory path.
    * Windows:
 mysql -u UserName -p DBName 

Open in new window

Notes about the above:

    * The Windows example assumes that mysql is already on your path
    * LOCALHOST is assumed as the host name (at least under Windows ...) but -h HostName can still be specified
    * -p makes it query for a password. You do not specify the password on the command line. Omitting -p works only if UserName requires no password.
    * DBName is optional. If not specified, the mysql DB becomes the current DB.

3. Creating a database

At the MySQL Prompt (available from your start menu):
create database DBNAME;
                      use DBNAME;

Open in new window

4. Running a Script File

Under Windows, this is
mysql -u <username> -p <pwd> < ScriptFile.sql

Open in new window

5. Remember the Semicolon!

In MySQL, every command terminates with a semicolon. Coming from MS SQL server, that's a little hard to get used to.

6. Single-line comment --

This may just be the command-line utility ... I'm not sure ... but a space is required between the -- and the comment
                      --invalid comment
                      -- valid comment 

Open in new window

7. Special table/column names

In MSSQL, use square brackets [ ]:
SELECT * FROM [my table name with space];

Open in new window

In MySQL, use backticks.  That mark is the backwords single-quote mark ... on the same key as the ~ just left of 1 for those with US keyboards:
SELECT * FROM `my table name with space`;

Open in new window

In either DBMS, you can set the ANSI_QUOTES option so that standard double quotes "  " may be used around identifiers.
In MySQL, that's:
SET sql_mode='ANSI_QUOTES';

Open in new window

Thanks to both AngelIII and Mark_Wills for mentioning this.

8. Autonumber / Identity -- Auto_Increment

The keyword in MySQL is Auto_Increment:
                      CREATE TABLE MyTable (ID int unsigned NOT NULL Auto_Increment Primary Key); 

Open in new window

It seems that an Auto_Increment field MUST be defined as a key ... which one would usually want to do anyway.

9. Default to Current Date

Thanks to for this one
                      CREATE TABLE MyTable (ID int unsigned NOT NULL Auto_Increment Primary Key, View_Date timestamp NOT NULL Default now( ) ); 

Open in new window

10. UPDATE with a JOIN

The update statement is different from MSSQL. Instead of
UPDATE T1 Set Field1=T2.F1, ...Fieldn = T2.Fn FROM T1 inner join T2

Open in new window

try (moving the "set...")
Update T1 inner join T2 Set T1.F1 = T2.F1, ... T1.Fn = T2.Fn

Open in new window

See the MySQL Manual Update Page for more information :
Here is a more realistic example comparing MS SQL and MySQL

The following code was written for MS SQL Server:
UPDATE Furnace_log 
                      SET max_thk=thickness.h_limit, min_thk=thickness.l_limit 
                      FROM Furnace_log 
                      INNER JOIN thickness on Furnace_log.mattyp=thickness.Material 
                         AND Furnace_log.Coating=thickness.Coating

Open in new window

The equivalent update is accomplished in MySQL using:
UPDATE Furnace_log 
                      INNER JOIN thickness on Furnace_log.mattyp=thickness.Material 
                         AND Furnace_log.Coating=thickness.Coating 
                      SET Furnace_Log.max_thk=thickness.h_limit, 

Open in new window

For a more complete discussion of this point as well as the syntax for Oracle, see by AngelIII.

11. LIKE operator -- it is EXACTLY as in MSSQL

SELECT * FROM books WHERE title LIKE '%PHP%';

Open in new window

12. PIVOT - Crosstab type expression for reporting

Have a look at Group_Concat :

Here's an example of using Group_Concat :'different-versions'-in-string.html

13. LIMIT - controlling the number of Rows

In MS SQL we use the TOP (n) qualifier at the start of the select statement.
In MySQL we use the LIMIT n qualifier at the end of the select statement.

LIMIT in MySQL is actually more capable than TOP in MS SQL Server.

Consider the following valid MySQL commands:
Select Table_Name, Table_Rows from  information_schema.tables order by table_rows limit 10;
                      -- OK, that was very basic ... equivalent to SELECT top 10 ...
                      Select Table_Name, Table_Rows from  information_schema.tables order by table_rows limit 10, 10;
                      -- This one gives you the NEXT 10 records.  Hmm ... pagination on your web page, anyone?
                      Delete from wt_usersettings limit 2;
                      -- No kidding, that deletes only the "first" 2 records.  Defining "first" is still a little up-in-the-air ...
                      update wt_usersettings set SettingValue = 'Something' Where SettingValue is NULL limit 2;
                      -- Again, only 2 records will be updated.

Open in new window

Thanks to Mark Wills for suggesting inclusion of this point!

14. Date and Time Functions

AngelIII recommended that I include these.  Before I got to it, he wrote a better article on it than I would have. His includes MS SQL Server, MySQL, and Oracle.

15. Getting into DDL -- Describe

What does the silly table look like?  What are it's fields and what are their types?
exec sp_help tablename

Open in new window

Describe tablename;

Open in new window

I hope to post another article shortly with more DDL (Create Table, Drop Column, etc.) statements.  But this article is about long enough!

16. RTM - Read the Manuals

I highly recommend you refer to the MySQL manuals ( and look at some of the advanced String and Date handling routines that MySQL developers have available to them.

As I come across more MySQL samples for DB developers, I will post them.
In the meantime, feel free to visit updated notes :

Please add any comments below with examples or insights that you think will help.

Comments (4)

Top Expert 2009

Hi Daniel nice article. Probably will reduce the point opportunities in the MySQL zone since this answers a lot of newbie questions, IF they will read it. :)

An idea, from a DBA that often uses MySQL for minor things, I often forget how to login to mysql from an OS prompt. Yes, that sounds a bit embarassing, as I am an Oracle, DB2, Sybase DBA, but its true. Its due to seldom usage, and always having to hit the docs to remember. Maybe you could add that to your quick reference.

Good article.
Kevin CrossChief Technology Officer
Most Valuable Expert 2011


Voted yes above.

Good job!
theGhost_k8Database Consultant

I suppose @ point 4 you missed a "<"

1: mysql -u <username> -p <pwd>  < ScriptFile.sql
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Good catch, theGhost_k8!

And a good practice is to not include the password clear text in the command string.  From some command line / terminal based systems, when another user comes to the same server all they have to do is pull up the previous command and run it regardless of their having the clearance as a database administrator or not.  Aside from potential harmful scripts being run without authority, if this is your root password it is now known to other users.

So I tend to do this as :
(then type in the password when prompted)
mysql -u username -p < ScriptFile.sql

Open in new window

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.