¡Descarga Guía rápida para SQL en MySQL: consultas, tipos de datos y funciones y más Apuntes en PDF de Informática solo en Docsity!
In this guide, you’ll find a useful cheat sheet that documents some of
the more commonly used elements of SQL, and even a few of the
less common. Hopefully, it will help developers – both beginner and
experienced level – become more proficient in their understanding
of the SQL language.
Use this as a quick reference during development, a learning aid, or even print it out and bind it if
you’d prefer (whatever works!).
But before we get to the cheat sheet itself, for developers who may not be familiar with SQL, let’s
start with…
What is SQL SQL vs MySQL Installing MySQL Using MySQL Cheat Sheet Comments MySQL Data Types Operators Functions Wildcard Characters Keys Indexes Joins View Conclusions 03 07 08 09 11 20 21 25 27 36 37 39 40 42 43 Table of Contents
Using SQL, you are able to interact with the database by writing queries, which when executed,
return any results which meet its criteria.
Here’s an example query:-
Using this SELECT statement, the query selects all data from all columns in the user’s table. It
would then return data like the below, which is typically called a results set:-
SELECT * FROM users; WebsiteSetup.orfi - MySQL Cheat Sheet
If we were to replace the asterisk wildcard character (*) with specific column names instead, only
the data from these columns would be returned from the query.
We can add a bit of complexity to a standard SELECT statement by adding a WHERE clause, which
allows you to filter what gets returned.
This query would return all data from the products table with a stock_count value of less than 10
in its results set. The use of the ORDER BY keyword means the results will be ordered using the
stock_count column, lowest values to highest.
SELECT first_name, last_name FROM users; SELECT * FROM products WHERE stock_count <= 10 ORDER BY stock_count ASC ; WebsiteSetup.orfi - MySQL Cheat Sheet
SQL vs MySQL
You may have heard of MySQL before. It’s important that you don’t confuse this with SQL itself, as
there’s a clear difference.
SQL is the language. It outlines syntax that allows you to write queries that manage relational
databases. Nothing more.
MySQL meanwhile is a database system that runs on a server. It implements the SQL language,
allowing you to write queries using its syntax to manage MySQL databases.
In addition to MySQL, there are other systems that implement SQL. Some of the more popular
ones include:
- PostgreSQL
- SQLite
- Oracle Database
- Microsoft SQL Server
Installing MySQL Windows MacOS
The recommended way to install MySQL on Windows is by using the installer you can download
from the MySQL website.
On macOS, the recommended way to install MySQL is using native packages, which sounds a lot
more complicated than it actually is. Essentially, it also involves just downloading an installer.
Our favorite of these is the world database, which provides some interesting data to practice writing
SQL queries for. Here’s a screenshot of its country table within Sequel Pro.
Whilst this one returns all European countries with a population of over 50million along with their
capital city and its population.
This example query returns all countries with Queen Elizabeth II as their head of state .
WebsiteSetup.orfi - MySQL Cheat Sheet
Cheat Sheet Keywords
A collection of keywords used in SQL statements, a description, and where appropriate an example.
Some of the more advanced keywords have their own dedicated section later in the cheat sheet.
Where MySQL is mentioned next to an example, this means this example is only applicable to
MySQL databases (as opposed to any other database system).
SQL Keywords
Keyword Description
ADD
Adds a new column to an existing table.
Example : Adds a new column named ‘email_address’ to a table named
‘users’.
ALTER TABLE users ADD email_address varchar( 255 );
ADD
CONSTRAINT
It creates a new constraint on an existing table, which is used to specify
rules for any data in the table.
Example : Adds a new PRIMARY KEY constraint named ‘user’ on columns
ID and SURNAME.
ALTER TABLE users ADD CONSTRAINT user PRIMARY KEY (ID, SURNAME);
ALTER TABLE
Adds, deletes or edits columns in a table. It can also be used to add and
delete constraints in a table, as per the above.
Example : Adds a new boolean column called ‘approved’ to a table named
‘deals’.
ALTER TABLE deals ADD approved boolean;
Example 2 : Deletes the ‘approved’ column from the ‘deals’ table
ALTER TABLE deals DROP COLUMN approved;
SQL Keywords
Keyword Description
BETWEEN
Selects values within the given range.
Example 1 : Selects stock with a quantity between 100 and 150.
SELECT * FROM stock WHERE quantity BETWEEN 100 AND 150 ;
Example 2 : Selects stock with a quantity NOT between 100 and 150.
Alternatively, using the NOT keyword here reverses the logic and selects
values outside the given range.
SELECT * FROM stock WHERE quantity NOT BETWEEN 100 AND 150 ;
CASE
Change query output depending on conditions.
Example : Returns users and their subscriptions, along with a new column
called activity_levels that makes a judgement based on the number of
subscriptions.
SELECT first_name, surname, subscriptions CASE WHEN subscriptions > 10 THEN 'Very active' WHEN Quantity BETWEEN 3 AND 10 THEN 'Active' ELSE 'Inactive' END AS activity_levels FROM users;
CHECK
Adds a constraint that limits the value which can be added to a column.
Example 1 (MySQL) : Makes sure any users added to the users table are 18
or over.
CREATE TABLE users ( first_name varchar( 255 ), age int, CHECK (age>= 18 ) );
Example 2 (MySQL) : Adds a check after the table has already been
created.
ALTER TABLE users ADD CHECK (age>= 18 );
SQL Keywords
Keyword Description
CREATE
DATABASE
Creates a new database.
Example : Creates a new database named ‘websitesetup’.
CREATE DATABASE websitesetup;
CREATE
TABLE
Creates a new table.
Example : Creates a new table called ‘users’ in the ‘websitesetup’ database.
CREATE TABLE users ( id int, first_name varchar( 255 ), surname varchar( 255 ), address varchar( 255 ), contact_number int );
DEFAULT
Sets a default value for a column;
Example 1 (MySQL) : Creates a new table called Products which has a
name column with a default value of ‘Placeholder Name’ and an available_
from column with a default value of today’s date.
CREATE TABLE products ( id int, name varchar( 255 ) DEFAULT 'Placeholder Name', available_from date DEFAULT GETDATE() );
Example 2 (MySQL) : The same as above, but editing an existing table.
ALTER TABLE products ALTER name SET DEFAULT 'Placeholder Name', ALTER available_from SET DEFAULT GETDATE();
DELETE
Delete data from a table.
Example : Removes a user with a user_id of 674.
DELETE FROM users WHERE user_id = 674 ;
DESC
Used with ORDER BY to return the data in descending order.
Example : Raddish, Peaches, Bananas, Apples
SQL Keywords
Keyword Description
IN
Used alongside a WHERE clause as a shorthand for multiple OR conditions.
So instead of:
SELECT * FROM users WHERE country = 'USA' OR country = 'United Kingdom' OR country = 'Russia' OR country = 'Australia';
You can use:
SELECT * FROM users WHERE country IN ('USA', 'United Kingdom', 'Russia', 'Australia');
INSERT INTO
Add new rows to a table.
Example : Adds a new vehicle.
INSERT INTO cars (make, model, mileage, year) VALUES ('Audi', 'A3', 30000 , 2016 );
IS NULL
Tests for empty (NULL) values.
Example : Returns users that haven’t given a contact number.
SELECT * FROM users WHERE contact_number IS NULL;
IS NOT NULL The reverse of NULL. Tests for values that aren’t empty / NULL.
LIKE
Returns true if the operand value matches a pattern.
Example : Returns true if the user’s first_name ends with ‘son’.
SELECT * FROM users WHERE first_name LIKE '%son';
NOT
Returns true if a record DOESN’T meet the condition.
Example : Returns true if the user’s first_name doesn’t end with ‘son’.
SELECT * FROM users WHERE first_name NOT LIKE '%son';
OR
Used alongside WHERE to include data when either condition is true.
Example : Returns users that live in either Sheffield or Manchester.
SELECT * FROM users WHERE city = 'Sheffield' OR 'Manchester';
SQL Keywords
Keyword Description
ORDER BY
Used to sort the result data in ascending (default) or descending order
through the use of ASC or DESC keywords.
Example : Returns countries in alphabetical order.
SELECT * FROM countries ORDER BY name;
ROWNUM
Returns results where the row number meets the passed condition.
Example : Returns the top 10 countries from the countries table.
SELECT * FROM countries WHERE ROWNUM <= 10 ;
SELECT
Used to select data from a database, which is then returned in a results set.
Example 1 : Selects all columns from all users.
SELECT * FROM users; Example 2 : Selects the first_name and surname columns from all users.xx SELECT first_name, surname FROM users;
SELECT
DISTINCT
Sames as SELECT, except duplicate values are excluded.
Example : Creates a backup table using data from the users table.
SELECT * INTO usersBackup FROM users;
SELECT INTO
Copies data from one table and inserts it into another.
Example : Returns all countries from the users table, removing any duplicate
values (which would be highly likely)
SELECT DISTINCT country from users;
SELECT TOP
Allows you to return a set number of records to return from a table.
Example : Returns the top 3 cars from the cars table.
SELECT TOP 3 * FROM cars;
SQL Keywords
Keyword Description
UNIQUE
This constraint ensures all values in a column are unique.
Example 1 (MySQL) : Adds a unique constraint to the id column when
creating a new users table.
CREATE TABLE users ( id int NOT NULL, name varchar( 255 ) NOT NULL, UNIQUE (id) );
Example 2 (MySQL) : Alters an existing column to add a UNIQUE
constraint.
ALTER TABLE users ADD UNIQUE (id);
UPDATE
Updates existing data in a table.
Example : Updates the mileage and serviceDue values for a vehicle with an
id of 45 in the cars table.
UPDATE cars SET mileage = 23500 , serviceDue = 0 WHERE id = 45 ;
VALUES
Used alongside the INSERT INTO keyword to add new values to a table.
Example : Adds a new car to the cars table.
INSERT INTO cars (name, model, year) VALUES ('Ford', 'Fiesta', 2010 );
WHERE
Filters results to only include data which meets the given condition.
Example : Returns orders with a quantity of more than 1 item.
SELECT * FROM orders WHERE quantity > 1 ;
Comments Single Line Comments Multiline Comments
Comments allow you to explain sections of your SQL statements, or to comment out code and
prevent its execution.
In SQL, there are 2 types of comments, single line and multiline.
Single line comments start with –. Any text after these 2 characters to the end of the line will be
ignored.
Multiline comments start with /* and end with */. They stretch across multiple lines until the
closing characters have been found.
-- My Select query SELECT * FROM users; /* This is my select query. It grabs all rows of data from the users table / SELECT * FROM users; / This is another select query, which I don’t want to execute yet SELECT * FROM tasks; */
