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 ( ... );

    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

    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!

    • Williamston Consulting is now ...

    Black Locust Software

    Technology Solutions for Business ... Large or Small
    .



    Home

    Auto Dealers' Form Software

    Software Development

    Website Development

    Website Advice

    IT Guys' Stuff:

    Certification
    Study Guides

    Programming
    Tips