MySQL
SELECT Statement
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
The following operators can be used in the WHERE clause.
The WHERE clause can be combined with AND, OR, and NOT operators.
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The INSERT INTO statement is used to insert new records in a table.
A field with a NULL value is a field with no value.
The UPDATE statement is used to modify the existing records in a table.
The DELETE statement is used to delete existing records in a table.
The LIMIT clause is used to specify the number of records to return.
The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
The COUNT() function returns the number of rows that matches a specified criterion.
The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
The BETWEEN operator selects values within a given range.
Aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
The INNER JOIN keyword selects records that have matching values in both tables.
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table (table2).
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table (table1).
The CROSS JOIN keyword returns all records from both tables (table1 and table2).
A self join is a regular join, but the table is joined with itself.
The UNION operator is used to combine the result-set of two or more SELECT statements.
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.
The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.
The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement).
IFNULL and COALESCE functions let you return an alternative value if an expression is NULL.
Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.
The CREATE DATABASE statement is used to create a new SQL database.
The DROP DATABASE statement is used to drop an existing SQL database.
The CREATE TABLE statement is used to create a new table in a database.
The DROP TABLE statement is used to drop an existing table in a database.
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
The NOT NULL constraint enforces a column to NOT accept NULL values.
The UNIQUE constraint ensures that all values in a column are different.
The PRIMARY KEY constraint uniquely identifies each record in a table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The CHECK constraint is used to limit the value range that can be placed in a column.
The DEFAULT constraint is used to set a default value for a column.
The default value will be added to all new records, if no other value is specified.
The CREATE INDEX statement is used to create indexes in tables.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
In SQL, a view is a virtual table based on the result-set of an SQL statement.
The choice between CHAR, BINARY, TEXT, and BLOB depends on the type of data you intend to store and how you plan to interact with that data.
All the numeric data types may have an extra option: UNSIGNED or ZEROFILL.
The choice between DATE, DATETIME, TIMESTAMP, TIME and YEAR depends on the type of data you intend to store and how you plan to interact with that data.