MySQL Cheatsheet by Example
The MySQL Cheatsheet by Example includes samples of the most commonly used MySQL commands and queries. We have also included a summary of data types. For a more detailed explanation of how each data type should be used, see Using MySQL Data Types.
NEW Version 1.1 is Now Available
We just added the following to our free MySQL cheat sheet:
- 3 Ways to Insert Rows
- How to Update a Row
- MySQL One-Line Commands
Download: MySQL Cheatsheet (32 kb) Downloads thus far: 4500
The cheatsheet is in Adobe Acrobat format.
This will be a "work in progress", so if you find any typos or have useful suggestions to share or think we need to add more examples, just leave a comment and let me know. This is an evolving project and the MySQL Cheatsheet will be updated periodically. The goal is to make it the most useful MySQL cheat sheet on the planet.
Jakob Egger Says:
I like your cheat sheet… It’s very nice and compact, and gives a good overview!
But as every Version 1.0, it’s not yet perfect.
Two things:
a) the integer data types range is not symmetric, ie. the range of tinyint is not +/-127 but -128 to +127! Smallint ranges from -32768 to +32767. This fact is related to the way the numbers are stored (for more info look for “two’s complement encoding” on the web)
b) You don’t mention the SET datatype
Best regards, Jakob
TE Says:
Now that is the type of constructive feedback I am looking for.
There is, however, a rationale behind the way I put the cheat sheet together. It is different because I’m trying to make it more practical than just a list of specifications.
a) I did not have enough room for large numeric ranges like that of BIGINT (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807), so I simplified the range. As a rule you should never use a numeric data type where the numeric values have any chance of exceeding the range limits.
b) I didn’t mention the SET data type because it is the most difficult to understand and I have never seen it used. The MySQL web site itself advises against using it. I could add some SET data type examples, but it may take a half of a page to explain how to use it.
Thanks again for the feedback. I may add SET to the next version. Perhaps there is someone who wants to use it. Obviously, you are one person who knows about it.
Jakob Egger Says:
a) sounds reasonable…
b) The fact that I mentioned the SET datatype is because I found your cheat sheet when I was looking for a concise overview of the MySQL Data Types. I was looking for an exhaustive list to quickly check I didn’t forget any datatype that my code could have problems with.
But I agree with you, for most applications the SET datatype is not practical – especially because it effectively stores data in the CREATE TABLE statements. And that totally goes against database normalisation.
Best Regards!
TE Says:
Hi Jakob
After you drew attention to it, I recently run into a couple MySQL update scripts where the SET datatype was used. I may add it to the next version of the cheat sheet, and add a separate article explaining its use for those who are curious.
Thanks for the response.
WFB Says:
INSERT might also be included.
hotshot309 Says:
Thanks a lot for the great cheatsheet! I’m a beginner and will surely benefit from it. I’ll look out for an updated version.
Any other cheatsheets you’re considering adding?
TE Says:
Hi WFB
It will be in the next version, along with a lot of other suggestions that I have received. The next version should be ready soon.
Hi hotshot309
There are CSS, FileZilla and other cheat sheets in the works. All it takes it takes is the time that I don’t have right now. *sigh* :/
Doogie Says:
Version 1.1 of the MySQL cheat sheet is posted. It has the additions people requested.
Duce Says:
Thanks for this, it is most helpful.
There is just one command I do not seem to get right…
To scroll inside a record… On a unix system under normal command line I would use “| more”… How do I do this in MySQL?
TE Says:
Hi Duce
With MySQL, you use the LIMIT clause. You have to set the values for LIMIT in order to page through a recordset of results. There is an example on page 2 of the cheat sheet.
LIMIT 0, 10 < – returns the first 10 rows, starting with row #0
LIMIT 9, 10 <– returns the next 10 rows, starting with row #9
You are not paging through a record; you are paging through a recordset of multiple rows that are returned as query results.
The LIMIT clasue is commonly used to page through results from a SELECT query. I’ll include a better example in the next version and as soon as I find the time I will write a tutorial about paging through results with MySQL. Keep checking back. I will move the tutorial up to the top of my long list of tutorials that have not yet been written.