My SQL quick reference
Pun intended.
I sometimes go many months with no need to use SQL, so over the years I’ve developed my own quick reference to remind me how to do basic tasks when necessary. Most SQL quick reference sheets out there try to pack as much different syntax as they can in a small space, but mine focuses on what the basic tasks are and how to do them. I hope that someone finds it useful.
Most of my SQL experience has been with MySQL, and I separated what I believe are the standard SQL parts below from the MySQL-specific ones. Corrections welcome. If you really want to know where SQL implementations differ from the standard, Comparison of different SQL implementations is an excellent, detailed reference on what’s different from one implementation to another.
I tested all the SELECT commands shown with the MySQL employee sample database that I downloaded from github.
(I also later converted this to be the SQL page for the wonderful Learn X in Y Minutes site; that page has since been translated to Spanish, Italian, Russian, Turkish, and Chinese!)
Standard SQL
Enter these at the SQL command line. I don’t think semicolons are necessary after every one of these commands, but I find it simplest to just always add them. SQL is not case-sensitive about keywords, and I tend to enter them in lower-case, but I’m showing them in the conventional upper-case here because it makes it easier to distinguish them from database, table, and column names.
| quit to return to the operating system command line | quit; |
| list available databases | # comments start with a pound sign SHOW DATABASES; |
| select the database named employees to use | USE employees; |
| create a new database called someDatabase | # database and table names are case-sensitive CREATE DATABASE someDatabase; |
| delete database someDatabase | DROP DATABASE someDatabase; |
| create a table called tablename1, with the two columns shown, for the database currently in use | # lots of other options available for how you specify the columns... CREATE TABLE tablename1 ('fname' VARCHAR(20),'lname' VARCHAR(20)); # The apostrophes in the line above should be backticks (`). # Hugo's rendering engine won't let me put them there. |
| insert a row of data into the table tablename1 | INSERT INTO tablename1 VALUES('Richard','Mutt'); |
| delete the table tablename1 | DROP TABLE tablename1; |
| show all data in the departments table | SELECT * FROM departments; |
show just the dept_no and dept_name columns from the departments table | SELECT dept_no, dept_name FROM departments; |
| just get the first 5 rows from table departments | SELECT * FROM departments LIMIT 5; |
| show dept_name column values in table departments where dept_name has the substring "en" | SELECT dept_name FROM departments WHERE dept_name LIKE "%en%"; |
| show all columns from table departments where the dept_name column starts with an "S" and has exactly 4 characters after it | SELECT * FROM departments WHERE dept_name LIKE "S____"; |
| Select title values from the titles table but don't show duplicates | SELECT DISTINCT title FROM titles; |
| Same as above, but sorted (case-sensitive) by the title values | SELECT DISTINCT title FROM titles ORDER BY title; |
| Count the rows in the departments table | SELECT count(*) FROM departments; |
| Count the rows in the departments table that have "en" as a substring of the dept_name value | SELECT count(*) FROM departments WHERE dept_name LIKE "%en%"; |
In tablename1, change the fname value to "John" for all rows that have an lname value of "Mutt" | UPDATE tablename1 SET fname="John" WHERE lname="Mutt"; |
| delete all rows from the tablename1 table | DELETE FROM tablename1; |
| delete rows from the tablename1 table where the lname value begins with "M" | DELETE FROM tablename1 WHERE lname like "M%"; |
MySQL-specific SQL prompt commands
| list the tables in the currently selected database | SHOW TABLES; |
| Describe the columns in table departments (handy before doing SELECT statements to see column names and types) | DESCRIBE departments; |
| run the SQL commands stored in the file myscript.sql | SOURCE myscript.sql; |
| Load a local csv file (enabling this may require --local-infile with the mysql startup command or the adjustment of a config file) | # Enter the following as one command LOAD DATA LOCAL INFILE '/some/path/names.csv' INTO TABLE tablename1 COLUMNS TERMINATED BY ','; |
| Create new user jane with password janepw, then grant her access to everything | CREATE USER 'jane' IDENTIFIED BY 'janepw'; GRANT ALL ON *.* TO 'jane'; |
Handy MySQL commands from the operating system prompt
There are often multiple ways to execute some of the following tasks, but these work for me. Treat all as single-line commands.
| start up MySQL with a single command (which includes the plain text password, which is not a good idea for any kind of production system) | mysql -u someuser --password=somepassword |
| Run a script of SQL commands from the operating system command line and then return to the command line; output of the SELECT statements will be tab-delimited | mysql -u someuser --password=somepassword -t < employees.sql |
| create a file of SQL commands to recreate the database employees (with the employees demo database, this created a 168MB file) | mysqldump -u someuser --password=somepassword employees > makeemployees.sql |
| Run a SQL command (or more than one using a semicolon to separate them) from the operating system prompt | mysql -u someuser --password=somepassword -e 'USE employees; SELECT * FROM departments' |
| Same as above, but getting output as tab-separated values--only difference is to add -B for "batch" mode | mysql -u someuser --password=somepassword -B -e 'USE employees; SELECT * FROM departments' |
Other handy tricks, as covered in the MySQL documentation
The MySQL documentation’s Examples of Common Queries covers many additional useful tasks:
- The Maximum Value for a Column
- The Row Holding the Maximum of a Certain Column
- Maximum of Column per Group
- The Rows Holding the Group-wise Maximum of a Certain Column
- Using User-Defined Variables
- Using Foreign Keys
- Searching on Two Keys
- Calculating Visits Per Day
- Using AUTO_INCREMENT
Note that my URL for the link to this information doesn’t include a version number, but gets redirected by mysql.com to the URL for the latest release’s version of this documentation, as documentation URLs should do.
Share this post