Database Developers' Quick-Reference to MySQL

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 DBMS's, 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 walkthrough for DB newbies ... introducing data types and explaining syntax.

John Coggeshall's MySql Crash Course is similar and helpful.

I'm actually looking for something for experienced DB developers ... a quick list to get going.

Creating a database:

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

Run a Script File:

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

Remember the Semicolon!

Every command terminates with a semicolon. Coming from MS Sql server, that's a little hard to get used to.

UPDATE with a JOIN:

This is different from MSSQL. Instead of UPDATE T1 Set Field1=T2.F1, ...Fieldn = T2.Fn FROM T1 inner join T2
try Update T1 inner join T2 Set T1.F1 = T2.F1, ... T1.Fn = T2.Fn
See the MySQL Manual Update Page for more information.
Example
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
The same thing is accomplished in MySQL with:
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;

LIKE operator -- similar to that in MSSQL

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

Data Types

The official data type explanation is good, but not quick.

MS Sql Server data type	MySQL data type equivalent	Comment
char(M)	char(M)	0<=M<=255 fixed-width, right-padded with spaces
varchar(M)	varchar(M)	0<=M<=65535 variable width
bit	bool or boolean	synonymous w/ tinyint(1)
tinyInt	TinyInt(M)	M indicates maximum display width, not the size of the number. TinyInt is 1 byte, so -128 to 127 or 0 to 255 depending on signed/unsigned usage
	bit(M)	indicates a bit field. Not something I've used in MS SQL.
	SmallInt(M)	16-bit integer: -32768 to 32767 or 0 to 65535
	MediumInt(M)	The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215. That would be 24-bit ... perhaps designed for storing RGB values?
Int	Int(M) or Integer(M)	32-bit integer. Again, MySQL offers an UNSIGNED flag as an option
BigInt	BigInt(M)	64-bit integer. Again, MySQL offers an UNSIGNED flag as an option

Autonumber, Identity, auto_increment

CREATE TABLE volunteer_action(va_id int NOT NULL auto_increment);
auto_increment is used in MySQL just like Identity(m,n) in MS SQL.

@@Identity and LAST_INSERT_ID()

LAST_INSERT_ID() returns the last auto_increment number much like @@Identity returns the last identity issued in MS SQL Server.