<

Database Developers' Quick-Reference to MySQL

Published on
10,307 Points
3,507 Views
3 Endorsements
Last Modified:
Approved
Community Pick

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 

Open in new window

where mysql_dir is replaced by the actual directory path.
    * Windows:
 mysql -u UserName -p DBName 

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;

Open in new window


4. Running a Script File


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

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 

Open in new window


7. Special table/column names


In MSSQL, use square brackets [ ]:
SELECT * FROM [my table name with space];

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

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

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

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

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

Open in new window

try (moving the "set...")
Update T1 inner join T2 Set T1.F1 = T2.F1, ... T1.Fn = T2.Fn

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

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;

Open in new window


For a more complete discussion of this point as well as the syntax for Oracle, see http://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%';

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 :

http://www.experts-exchange.com/Database/MySQL/Q_23672635.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.

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.
http://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

Open in new window

MySQL:
Describe tablename;

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.
3
Comment
  • 2
4 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
Hi Daniel nice article. Probably will reduce the point opportunities in the MySQL zone since this answers a lot of newbie questions, IF they will read it. :)

An idea, from a DBA that often uses MySQL for minor things, I often forget how to login to mysql from an OS prompt. Yes, that sounds a bit embarassing, as I am an Oracle, DB2, Sybase DBA, but its true. Its due to seldom usage, and always having to hit the docs to remember. Maybe you could add that to your quick reference.

Good article.
0
 
LVL 61

Expert Comment

by:Kevin Cross
Daniel,

Voted yes above.

Good job!
0
 
LVL 22

Expert Comment

by:theGhost_k8
I suppose @ point 4 you missed a "<"

1: mysql -u <username> -p <pwd>  < ScriptFile.sql
0
 
LVL 61

Expert Comment

by:Kevin Cross
Good catch, theGhost_k8!

And a good practice is to not include the password clear text in the command string.  From some command line / terminal based systems, when another user comes to the same server all they have to do is pull up the previous command and run it regardless of their having the clearance as a database administrator or not.  Aside from potential harmful scripts being run without authority, if this is your root password it is now known to other users.

So I tend to do this as :
(then type in the password when prompted)
mysql -u username -p < ScriptFile.sql

Open in new window

0

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Join & Write a Comment

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month