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 :

http://www.thescripts.com/serversidescripting/mysql/tutorials/introductiontomysql/index.html

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

http://www.onlamp.com/pub/a/php/2003/12/23/php_foundations.html

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 ( http://php.about.com/od/mysqlcommands/g/mysql_login.htm ):

 mysql_dir/bin/mysql -h hostname -u root -p 

Select all Open in new window

where mysql_dir is replaced by the actual directory path.
    * Windows:

 mysql -u UserName -p DBName 

Select all 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;

Select all Open in new window

4. Running a Script File

Under Windows, this is

mysql -u <username> -p <pwd> < ScriptFile.sql

Select all 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 

Select all Open in new window

7. Special table/column names

In MSSQL, use square brackets [ ]:

SELECT * FROM [my table name with space];

Select all 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`;

Select all 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';

Select all 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); 

Select all 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 http://www.devdaily.com/blog/post/mysql/default-field-current-date-time-timestamp-today-now/ for this one

 
                      CREATE TABLE MyTable (ID int unsigned NOT NULL Auto_Increment Primary Key, View_Date timestamp NOT NULL Default now( ) ); 

Select all 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

Select all Open in new window

try (moving the "set...")

Update T1 inner join T2 Set T1.F1 = T2.F1, ... T1.Fn = T2.Fn

Select all Open in new window

See the MySQL Manual Update Page for more information : http://dev.mysql.com/doc/refman/5.0/en/update.html
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

Select all 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, 
                         Furnace_Log.min_thk=thickness.l_limit;

Select all Open in new window

For a more complete discussion of this point as well as the syntax for Oracle, see https://www.experts-exchange.com/articles?id=1517 by AngelIII.

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

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

Select all Open in new window

12. PIVOT - Crosstab type expression for reporting

Have a look at Group_Concat :

http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat

Here's an example of using Group_Concat :

https://www.experts-exchange.com/questions/23672635/Run-a-simple-DISTINCT-and-for-each-distinct-result-put-values-of-'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.

Select all 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.
https://www.experts-exchange.com/articles?id=1499

15. Getting into DDL -- Describe

What does the silly table look like?  What are it's fields and what are their types?
MS SQL:

exec sp_help tablename

Select all Open in new window

MySQL:

Describe tablename;

Select all 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 (http://dev.mysql.com/doc/) 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 :

http://www.blacklocustsoftware.com/CodeNote/Intro_To_MySQL.php

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