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.
Log Into MySQL from the OS Prompt
- Linux/Unix: mysql_dir/bin/mysql -h hostname -u root -p where mysql_dir is replaced by the actual directory path.
- Windows: mysql -u UserName -p DBName
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.
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
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?
This one's actually a bit easier than in MS SQL Server as no join is required ...
SELECT count( * )
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 (
...
);
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
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.
Thanks again, Mark Wills!
