MySQL Cheat Sheet Basics is something I hope you find useful for those wishing to have a reference to MySQL syntax and how to get started.
MySQL is an open-source relational database management system. Its name is a combination of “My”, the name of co-founder Michael Widenius’s daughter, and “SQL”, the abbreviation for Structured Query Language.
To get started with MySQL you will want to visit https://www.mysql.com/ and download 2 software items from the MySQL.com including
- MySQL Community Server
- MySQL Workbench software
Below are directions for downloading MySQL for the Apple macOS system. For Windows users, the screens will be slightly different.
MySQL Community Edition Server is a freely downloadable version of the world’s most popular open-source database that is supported by an active community of open source developers and enthusiasts.
MySQL Workbench provides DBAs and developers an integrated tools environment for:
- Database Design & Modeling
- SQL Development
- Database Administration
- Database Migration
MySQL Cheat Sheet
Top Things to remember prior to coding in MySQL
- Not Case Sensitive – SQL is not case sensitive.
- Comments “–“ – to add a comment use 2 dashes –. Comments can be made at the beginning of the line or anywhere after.
- Semi-Colin “;” – All SQL statements must end with a semi-colon.
- Proper Order – SQL commands need to be made in proper order, meaning you can’t execute for instance the WHERE clause before the FROM clause.
- Capitalize SQL Commands and Operators – To make SQL easier to read and maintain a good coding structure use capital letters for SQL commands and operators.
- ALIAS – To reduce the amount of code needed, SQL allows the creation of aliases for databases, tables, and fields.
Sample SQL Query using 6 SQL commands
USE sql_database1; -- Use the database sql_database1
SELECT * -- Select all fields
FROM customers . -- pull records from the customer table
WHERE state = ‘VA’ -- only return records where state = 'VA'
ORDER BY last_name, first_name -- order by last name and then first name
LIMIT 20; -- only retun the first 20 recordsSELECT Command
- SELECT * – Return All Fields
- SELECT (Table Field List) – Return the table fields in the field list. Use commas to separate each field. Make sure the fields are spelled correctly.
- SELECT Field AS Name – The SQL aliases “AS” statement is used to give a table, or a column in a table, a temporary name.
SELECT *
FROM Customers;
-- Return all records and fields from the customer table
SELECT First_Name, Last_Name
FROM Customers;
-- Return all records for first name and last name from the customer table
SELECT Customer_ID AS ID
FROM Customers;
-- Return all records and the field ID from the customer tableWHERE Command
The WHERE command is used to filter data based on the operator.
Comparison Operators:
- > : Greater than
- >= : Greater than or equal to
- < : Less than
- <= : Less than or equal to
- = : Equal
- <> or != : Not equal
Logical Operators
- AND : Both sides of the AND must be true
- OR : One or the other side of the OR must be true
- NOT: Converts True to False or False to True
SELECT *
FROM customers
WHERE bday > ‘1960-01-01’;
SELECT *
FROM customers
WHERE bday <= '2000-01-01';
SELECT *
FROM customers
WHERE bday > ‘1960-01-01’ AND last_name = '%Smith%';
-- Returns records where birth dates are greater than 1960 AND a person has a last name that contains smith.
SELECT *
FROM customers
WHERE bday > ‘1960-01-01’ OR last_name = '%Smith%';
-- Returns records where birth dates are greater than 1960 OR a person has a last name that contains smith.
SELECT *
FROM customers
WHERE NOT (bday > ‘1960-01-01’ OR last_name = '%Smith%');
-- Returns records where birth dates are <= 1960 OR where a person has a last name that does not contain smith.
Common SQL WHERE Operators
- IN – The IN operator allows you to specify multiple values in a WHERE clause comma-separated to compare against. The IN operator is a shorthand for multiple OR conditions.
- BETWEEN – The BETWEEN operator is used for selecting values within a given range.
- LIKE – The LIKE operator is used to search for a specified pattern in a column.
- REGEXP – The REGEXP is used for performing regular expression pattern matches including (^: the beginning of a string, $ the end of a string, |: logical OR, [abc]: match case)
- IS NULL – The IS NULL operator is used to test a table’s field to see if it is null (i.e. doesn’t contain a value)
- IS NOT NULL – The IS NOT NULL operator is used to test a table’s field to see if it has a value (i.e. is not null)
- ORDER BY – Is used to sort the result-set in ascending or descending order. To Order by descending, use DESC at the end.
- LIMIT – Is used to restrict how many rows a SQL query returns.
SELECT *
FROM customers
WHERE Last_Name IN ('Smith', 'Adams','West')
;
-- Returen customers that have a last name of Smith, Adams, and West
SELECT *
FROM customers
WHERE bday BETWEEN '1980-01-01' AND '2000-12-31';
-- Return records WHERE the bday is between 1980 and 2000
SELECT *
FROM customers
WHERE Last_Name LIKE ('c%')
;
-- Return Records WHERE last name starts with "c"
SELECT *
FROM customers
WHERE Last_Name LIKE ('__c%')
;
-- %: Wild Card
-- _: Any Character
-- Return Records WHERE last name 3rd letter contains a "c"
SELECT *
FROM customers
WHERE Last_Name REGEXP '^a';
-- Return Records WHERE last name starts with "a"
SELECT *
FROM customers
WHERE Last_Name REGEXP 'a$'
;
-- Return Records WHERE last name ends with "a"
SELECT *
FROM customers
WHERE Last_Name REGEXP '^a|b'
;
-- Return Records WHERE last name starts with "a" OR contains a 'b'
SELECT *
FROM customers
WHERE Last_Name REGEXP '^s[mi]'
;
-- Return Records WHERE last name starts with "s" followed by a 'm' or 'i'
SELECT *
FROM customers
WHERE Email IS NULL;
-- Return Records WHERE Emails are Null
SELECT *
FROM customers
WHERE Email IS NOT NULL;
-- Return Records WHERE Emails have been added
SELECT *
FROM customers
ORDER BY Last_Name;
-- Return Records and sort by last name assending
SELECT *
FROM customers
ORDER BY Last_Name DESC;
-- Return Records and sort by last name descending
SQL JOINS | LEFT, RIGHT INNER, and CROSS
One of the powers of SQL is the ability to extract data from multiple tables or databases and combine the data into organized query results. JOINS allows you to join multiple table data or database data or both.
There are several types of SQL JOINS including
- LEFT JOIN – returns all rows from the Left Table and only returns matching rows from the right.
- RIGHT JOIN – returns all rows from the Right Table and only returns matching rows from the left.
- INNER JOIN – selects all rows from two tables as long as there is a match
- Outer JOIN – OUTER JOIN keyword will return all records when a match in left (table1) or right (table2) table is found.
- CROSS JOIN
In the following JOIN examples, colors black, grey, white and olive represent matches. So, for example, the grey “2” in (Table A’s) id matches the grey “B” in (Table B’s) id.
JOIN
JOIN returns all rows from the left table (Table A) and right table (Table B) regardless of whether a row from the left table (Table A) or right table (Table B) has a matching id.
If you include an ON condition in the JOIN statement, it will filter the list based on the ON condition.
SELECT *
FROM Customers c
JOIN ADDRESS a
ON c.customers_id = a.customer_id;
-- Return records from customers and address where the customer id = the address idLEFT JOIN
The LEFT JOIN returns all rows from the left table (Table A) regardless of whether a row from the left table (Table A) has a matching id from the right table (Table B) or not. If there is no match, the columns of the row from the right table will contain NULL.
SELECT *
FROM Customers c
LEFT JOIN Address a
ON c.customers_id = a.customer_id;
-- LEFT JOIN - Return all records from Customers and only records from Address where the customer id = the address id. Address records that don't match will return NULL.RIGHT JOIN
The RIGHT JOIN returns all rows from the right table (Table B) regardless of whether a row from the left table (Table A) has a matching id from the right table (Table A) or not. If there is no match, the columns of the row from the left table (Table A) will contain NULL.
SELECT *
FROM Customers c
RIGHT JOIN Address a
ON c.customers_id = a.customer_id;
-- RIGHT JOIN - Return all records from Address and only records from Custombers where the customer id = the address id. Customer records that don't match will return NULL.INNER JOIN
The INNER JOIN returns rows from the left table (Table A) only if there is a matching row in the right table (Table B). If there is no match, no record will be returned.
CROSS JOIN
ACROSS JOIN is used to return every combination of rows from two tables. An example might be a table containing shirt sizes (S, M, L, and XL) and another table containing colors (Red, Green, and Blue).
SELECT *
FROM SHIRTS s
CROSS JOIN COLORS c
-- Return all records from customers and address where the customer id = the address id
