The SQL Basics Cheat Sheet provides you with the syntax of all basics clauses, shows you how to write different conditions, and has examples. You can download this cheat sheet as follows: Download 2-page SQL Basics Cheat Sheet in PDF format (A4) Download 2-page SQL Basics Cheat Sheet in PDF format (Letter).

$ mysql mysql UPDATE mysql.user SET password=PASSWORD('newpass') WHERE user='root'; Switch back to the mysqldsafe terminal and kill the process using Control $ /etc/init.d/mysql start Conclusion. Thought the article, You can use the command line in “MySQL cheat sheet” as above. I hope will this your helpful. The SQL cheat sheet commands can be used in any IDE or tool where the user has connected to the database using the JAR file of the database type. The different databases existing in the market are Oracle, Microsoft SQL Server, IBM DB2, etc., which all these can be connected to by using their respective jars and tools to manage the data operations.

Structured Query Language (SQL) is a set-based language as opposed to a procedural language. It is the defacto language of relational databases.

The difference between a set-based language vs. a procedural language is that in a set-based language you define what set of data you want or want to operate on and the atomic operation to apply to each element of the set. You leave it up to the Database process to decide how best to collect that data and apply your operations. In a procedural language, you basically map out step by step loop by loop how you collect and update that data.
There are two main reasons why SQL is often better to use than procedural code.

• It is often much shorter to write - you can do an update or summary procedure in one line of code that would take you several lines of procedural.
• For set-based problems - SQL is much faster processor-wise and IO wise too because all the underlining looping iteration is delegated to a database server process that does it in a very low level way and uses IO/processor more efficiently and knows the current state of the data - e.g. what other processes are asking for the data.

Example SQL vs. Procedural

If you were to update say a sales person of all customers in a particular region - your procedural way would look something like this

The SQL way would be:
UPDATE customers SET salesperson = 'Mike' WHERE state = 'NH'
If you had say 2 or 3 tables you need to check, your procedural quickly becomes difficult to manage as you pile on nested loop after loop.

Mysql Query Cheat Sheet 2019

In this article we will provide some common data questions and processes that SQL is well suited for and SQL solutions to these tasks. Most of these examples are fairly standard ANSI-SQL so should work on most relational databases such as IBM DBII, PostGreSQL, MySQL, Microsoft SQL Server, Oracle, Microsoft Access, SQLite with little change. Some examples involving subselects or complex joins or the more complex updates involving 2 or more tables may not work in less advanced relational databases such as MySQL, MSAccess or SQLite. These examples are most useful for people already familiar with SQL. We will not go into any detail about how these work and why they work, but leave it up to the reader as an intellectual exercise. What customers have bought from us?

Example: What customers have never ordered anything from us?
SELECT customers.* FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.customer_id IS NULL
More advanced example using a complex join: What customers have not ordered anything from us in the year 2004 - this one may not work in some lower relational databases (may have to use an IN clause)
SELECT customers.* FROM customers LEFT JOIN orders ON (customers.customer_id = orders.customer_id AND year(orders.order_date) = 2004) WHERE orders.order_id IS NULL
Please note that year is not an ANSI-SQL function and that many databases do not support it, but have alternative ways of doing the same thing.

• SQL Server, MS Access, MySQL support year().
• PostGreSQL you do date_part('year', orders.order_date)
• SQLite - substr(orders.order_date,1,4) - If you store the date in form YYYY-MM-DD
• Oracle - EXTRACT(YEAR FROM order_date) or to_char(order_date,'YYYY')

Note: You can also do the above with an IN clause, but an IN tends to be slower
Same question with an IN clause
SELECT customers.* FROM customers WHERE customers.customer_id NOT IN(SELECT customer_id FROM orders WHERE year(orders.order_date) = 2004)
How many customers do we have in Massachusetts and California?
SELECT customer_state As state, COUNT(customer_id) As total FROM customers WHERE customer_state IN('MA', 'CA') GROUP BY customer_state
What states do we have more than 5 customers?

How many states do we have customers in?

_{Note the above does not work in Microsoft Access or SQLite - they do not support COUNT(DISTINCT ..)}
Alternative but slower approach for the above - for databases that don't support COUNT(DISTINCT ..), but support derived tablesList in descending order of orders placed customers that have placed more than 5 orders
Value Insert

Copy data from one table to another table

Creating a new table with a bulk insert from another table
Update from values
UPDATE customers SET customer_salesperson = 'Billy' WHERE customer_state = 'TX'
Update based on information from another table
UPDATE customers SET rating = 'Good' FROM orders WHERE orderdate > '2005-01-01' and orders.customer_id = customers.customer_id
_{Please note the date format varies depending on the database you are using and what date format you have it set to.}
Update based on information from a derived table
_{Please note the update examples involving additional tables do not work in MySQL, MSAccess, SQLite.}
MS Access Specific syntax for doing multi-table UPDATE joins
UPDATE customers INNER JOIN orders ON customers.customer_id = orders.customer_id SET customers.rating = 'Good'
MySQL 5 Specific syntax for doing multi-table UPDATE joins
UPDATE customers, orders SET customers.rating = 'Good' WHERE orders.customer_id = customers.customer_id

Articles of Interest
PostgreSQL 8.3 Cheat Sheet	Summary of new and old PostgreSQL functions and SQL constructs complete xml query and export, and other new 8.3 features, with examples.
SQLite	If you are looking for a free and lite fairly SQL-92 compliant relational database, look no further. SQLite has ODBC drivers, PHP 5 already comes with an embedded SQLite driver, there are .NET drivers, freely available GUIs , and this will run on most Oses. All the data is stored in a single .db file so if you have a writeable folder and the drivers, that’s all you need. So when you want something lite and don't want to go thru a database server install as you would have to with MySQL, MSSSQL, Oracle, PostgreSQL, or don't have admin access to your webserver and you don't need database group user permissions infrastructure, this is very useful. It also makes a nice transport mechanism for relational data as the size the db file is pretty much only limited to what your OS will allow for a file (or 2 terabytes which ever is lower).
PostgreSQL Date Functions	Summary of PostGresql Date functions in 8.0 version
The Future of SQL by Craig Mullins	Provides a very good definition of what set-based operations are and why SQL is superior for these tasks over procedural, as well as a brief history of the language.
Summarizing data with SQL (Structured Query Language)	Article that defines all the components of an SQL statement for grouping data. We wrote it a couple of years ago, but it is still very applicable today.
Procedural Versus Declarative Languages	Provides some useful anlaogies for thinking about the differences between procedural languages and a declarative language such as SQL
PostgreSQL Cheat Sheet	Cheat sheet for common PostgreSQL tasks such as granting user rights, backing up databases, table maintenance, DDL commands (create, alter etc.), limit queries
MySQL Cheat Sheet	Covers MySQL datatypes, standard queries, functions
Comparison of different SQL implementations	This is a great summary of the different offerings of Standard SQL, PostGreSQL, DB2, MSSQL, MySQL, and Oracle. It demonstrates by clear example how each conforms or deviates from the ANSI SQL Standards with join syntax, limit syntaxx, views, inserts, boolean, handling of NULLS

Download this 2-page SQL Basics Cheat Sheet in PDF or PNG format, print it out, and stick to your desk.

The SQL Basics Cheat Sheet provides you with the syntax of all basics clauses, shows you how to write different conditions, and has examples. You can download this cheat sheet as follows:

You may also read the contents here:

SQL Basics Cheat Sheet

SQL

SQL, or Structured Query Language, is a language to talk to databases. It allows you to select specific data and to build complex reports. Today, SQL is a universal language of data. It is used in practically all technologies that process data.

SAMPLE DATA

QUERYING SINGLE TABLE

Fetch all columns from the country table:

Fetch id and name columns from the city table:

Fetch city names sorted by the rating column in the default ASCending order:

Fetch city names sorted by the rating column in the DESCending order:

Aliases

Columns

Tables

FILTERING THE OUTPUT

COMPARISON OPERATORS

Fetch names of cities that have a rating above 3:Fetch names of cities that are neither Berlin nor Madrid:

TEXT OPERATORS

Fetch names of cities that start with a 'P' or end with an 's':Fetch names of cities that start with any letter followed by'ublin' (like Dublin in Ireland or Lublin in Poland):

OTHER OPERATORS

Fetch names of cities that have a population between 500K and 5M:Fetch names of cities that don't miss a rating value:Fetch names of cities that are in countries with IDs 1, 4, 7, or 8:

QUERYING MULTIPLE TABLES

INNER JOIN

JOIN (or explicitly INNER JOIN) returns rows that have matching values in both tables.

LEFT JOIN

LEFT JOIN returns all rows from the left table with corresponding rows from the right table. If there's no matching row, NULLs are returned as values from the second table.

RIGHT JOIN

RIGHT JOIN returns all rows from the right table with corresponding rows from the left table. If there's no matching row, NULLs are returned as values from the left table.

FULL JOIN

FULL JOIN (or explicitly FULL OUTER JOIN) returns all rows from both tables – if there's no matching row in the second table, NULLs are returned.

CROSS JOIN

CROSS JOIN returns all possible combinations of rows from both tables. There are two syntaxes available.

NATURAL JOIN

NATURAL JOIN will join tables by all columns with the same name.

NATURAL JOIN used these columns to match rows:
city.id, city.name, country.id, country.name.
NATURAL JOIN is very rarely used in practice.

AGGREGATION AND GROUPING

GROUP BYgroups together rows that have the same values in specified columns. It computes summaries (aggregates) for each unique combination of values.

AGGREGATE FUNCTIONS

• avg(expr) − average value for rows within the group
• count(expr) − count of values for rows within the group
• max(expr) − maximum value within the group
• min(expr) − minimum value within the group
• sum(expr) − sum of values within the group

EXAMPLE QUERIES

Find out the number of cities:

Find out the number of cities with non-null ratings:

Find out the number of distinctive country values:

Find out the smallest and the greatest country populations:

Find out the total population of cities in respective countries:

Find out the average rating for cities in respective countries if the average is above 3.0:

SUBQUERIES

A subquery is a query that is nested inside another query, or inside another subquery. There are different types of subqueries.

SINGLE VALUE

The simplest subquery returns exactly one column and exactly one row. It can be used with comparison operators =, <, <=, >, or >=.

This query finds cities with the same rating as Paris:

MULTIPLE VALUES

A subquery can also return multiple columns or multiple rows. Such subqueries can be used with operators IN, EXISTS, ALL, or ANY.

This query finds cities in countries that have a population above 20M:

CORRELATED

A correlated subquery refers to the tables introduced in the outer query. A correlated subquery depends on the outer query. It cannot be run independently from the outer query.

This query finds cities with a population greater than the average population in the country:

This query finds countries that have at least one city:

SET OPERATIONS

Set operations are used to combine the results of two or more queries into a single result. The combined queries must return the same number of columns and compatible data types. The names of the corresponding columns can be different

UNION

UNION combines the results of two result sets and removes duplicates. UNION ALL doesn't remove duplicate rows.

This query displays German cyclists together with German skaters:

INTERSECT

INTERSECT returns only rows that appear in both result sets.

This query displays German cyclists who are also German skaters at the same time:

EXCEPT

EXCEPT returns only the rows that appear in the first result set but do not appear in the second result set.

This query displays German cyclists unless they are also German skaters at the same time:

Mysql Query Cheat Sheet