My SQL quick reference

Pun intended.

SQL graffiti

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 linequit;
list available databases # comments start with a pound sign
SHOW DATABASES;
select the database named employees to useUSE employees;
create a new database called someDatabase # database and table names are case-sensitive
CREATE DATABASE someDatabase;
delete database someDatabaseDROP 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 tablename1INSERT INTO tablename1 VALUES('Richard','Mutt');
delete the table tablename1DROP TABLE tablename1;
show all data in the departments tableSELECT * FROM departments;
show just the dept_no and dept_name columns from the departments tableSELECT dept_no, dept_name FROM departments;
just get the first 5 rows from table departmentsSELECT * 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 itSELECT * FROM departments WHERE dept_name LIKE "S____";
Select title values from the titles table but don't show duplicatesSELECT DISTINCT title FROM titles;
Same as above, but sorted (case-sensitive) by the title valuesSELECT DISTINCT title FROM titles ORDER BY title;
Count the rows in the departments tableSELECT count(*) FROM departments;
Count the rows in the departments table that have "en" as a substring of the dept_name valueSELECT 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 tableDELETE 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 databaseSHOW 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.sqlSOURCE 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 everythingCREATE 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-delimitedmysql -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 promptmysql -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" modemysql -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.

CC BY-NC photo by duncan