Black Locust Software

Technology Solutions for Business

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.

Log Into MySQL from the OS Prompt

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.

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

Autonumber / Identity -- Auto_Increment

The keyword in MySQL is Auto_Increment:

CREATE TABLE MyTable (ID int unsigned NOT NULL Auto_Increment Primary Key);

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

Default to Current Date

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

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 This one's actually a bit easier than in MS SQL Server as no join is required ...
SELECT count( * )
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 -- IDENTICAL to that in MSSQL

SELECT * FROM books WHERE title LIKE "%PHP%"; Thanks to Guy Hengel for the correction!

PIVOT - type expression for reporting

Have a look at Group_Concat See a question on Experts Exchange for an example of its need.

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
Thanks, Mark Wills, for adding this!

Does Table X Exist in my MySQL DB?

This is as easy as in SQL Server, but a bit different. In MySQL it's:
SELECT COUNT( * ) FROM information_schema.tables WHERE table_schema = 'MyDBName' AND table_name = 'MyTableName';

Does Table X Have Column Y in my MySQL DB?

FROM information_schema.columns WHERE table_schema = 'MyDBName' AND Table_Name = 'MyTableName' AND column_Name = 'MyColumnName';

Add table only if it does not exist

This one's better in MySQL than in MS SQL Server also
CREATE TABLE IF NOT EXISTS tablename ( ... );

Drop Table only if it Exists

This one's better in MySQL than in MS SQL Server also
Again, there's just an If Exists at the end of the DDL statement. DROP TABLE IF EXISTS tablename;

Add a column only if it does not exist

This one's clunky! YUCK!!!
Because the IF statement is only available within procedures and functions, you have to have a procedure that does this. The solution is here Drop a column only if it exists Clunky just like the above:
Fill in the DropColumnIfExists function -->

List the Stored Procedures or Functions