# What is MySQL
* a relational database management system
* SQL keywords are NOT case sensitive: select is the same as SELECT
# SELECT
* used to select data from a database.
* returned is stored in a result table
``` sql
SELECT column1, column2, ...
FROM table_name;
```
> column1, column2, ... are the field names of the table you want to select data from.
``` sql
SELECT * FROM table_name;
```
> select all the fields available in the table
### DISTINCT
* used to return only distinct (different) values.
``` sql
SELECT DISTINCT column1, column2, ...
FROM table_name;
```
``` sql
SELECT COUNT(DISTINCT Country) FROM Customers;
```
> counts and returns the number of different (distinct) countries in the "Customers" table
# WHERE
* used to extract only those records that fulfill a specified condition
``` sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;
```
> not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.
### Text Fields
* SQL requires single quotes around text values (most database systems will also allow double quotes).
``` sql
SELECT * FROM Customers
WHERE Country = 'Mexico';
```
### Numeric Fields
* numeric fields should not be enclosed in quotes
``` sql
SELECT * FROM Customers
WHERE CustomerID = 1;
```
### Operators
| Operator | Description | Example |
| -------- | ------------------------------------------------ | ---------------------------------------------------------------------- |
| = | Equal | ```SELECT * FROM Products WHERE Price = 18;``` |
| > | Greater than | ```SELECT * FROM Products WHERE Price > 30;``` |
| < | Less than | ```SELECT * FROM Products WHERE Price < 30;``` |
| >= | Greater than or equal | ```SELECT * FROM Products WHERE Price >= 30;``` |
| <= | Less than or equal | ```SELECT * FROM Products WHERE Price <= 30;``` |
| <>(!=) | Not equal | ```SELECT * FROM Products WHERE Price <> 18;``` |
| BETWEEN | Between a certain range | ```SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;``` |
| LIKE | Search for a pattern | ```SELECT * FROM Customers WHERE City LIKE 'b%';``` |
| IN | To specify multiple possible values for a column | ```SELECT * FROM Customers WHERE City IN ('Bern','London','Paris');``` |
### AND, OR and NOT Operators
* AND and OR operators are used to filter records based on more than one condition
``` sql
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
```
``` sql
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
```
* NOT operator displays a record if the condition(s) is NOT TRUE
``` sql
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
```
* Combining AND, OR and NOT
``` sql
SELECT * FROM Customers
WHERE Country = 'Germany' AND (City = 'Berlin' OR City = 'Stuttgart');
```
> selects all fields from "Customers" where country is "Germany" AND city must be "Berlin" OR "Stuttgart" (use parenthesis to form complex expressions)
``` sql
SELECT * FROM Customers
WHERE NOT Country = 'Germany' AND NOT Country = 'USA';
```
> selects all fields from "Customers" where country is NOT "Germany" and NOT "USA"
# ORDER BY
* used to sort the result-set in ascending or descending order.
* sorts in ascending order by default.
* sort in descending order, use the DESC keyword.
``` sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
```
``` sql
SELECT * FROM Customers
ORDER BY Country, CustomerName;
```
> it orders by Country, but if some rows have the same Country, it orders them by CustomerName
``` sql
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
```
> sorted ascending by the "Country" and descending by the "CustomerName"
# INSERT INTO
* used to insert new records in a table.
* write the INSERT INTO statement in two ways
* Specify both the column names and the values to be inserted
* `INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);`
* adding values for all the columns of the table
* `INSERT INTO table_name
VALUES (value1, value2, value3, ...);`
* It is also possible to only insert data in specific columns.
``` sql
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
```
# NULL Values
* A field with a NULL value is one that has been left blank during record creation
### IS NULL
* used to test for empty values
``` sql
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
```
### IS NOT NULL
* used to test for non-empty values
``` sql
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
```
# UPDATE
* used to modify the existing records in a table
``` sql
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
```
> If you omit the WHERE clause, all records in the table will be updated
# DELETE
* used to delete existing records in a table.
``` sql
DELETE FROM table_name WHERE condition;
```
> If you omit the WHERE clause, all records in the table will be deleted
``` sql
DELETE FROM table_name;
```
> delete all rows in a table without deleting the table.the table structure, attributes, and indexes will be intact.
# LIMIT
* used to specify the number of records to return.
* useful on large tables with thousands of records.
``` sql
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
```
### OFFSET
* if we want to select records 4 - 6 (inclusive)
``` sql
SELECT * FROM Customers
LIMIT 3 OFFSET 3;
```
> return only 3 records, start on record 4 (OFFSET 3)
``` sql
SELECT * FROM Customers LIMIT 3,3; --first is start,second is the number
```
> it alse return 3 records, start on record 4
# MIN()
* returns the smallest value of the selected column.
``` sql
SELECT MIN(column_name)
FROM table_name
WHERE condition;
```
``` sql
SELECT MIN(Price) AS SmallestPrice
FROM Products;
```
return is 
# MAX()
* returns the largest value of the selected column.
``` sql
SELECT MAX(column_name)
FROM table_name
WHERE condition;
```
``` sql
SELECT MAX(Price) AS LargestPrice
FROM Products;
```
return is 
# COUNT()
* returns the number of rows that matches a specified criterion
* NULL values are not counted.
``` sql
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
```
# AVG()
* returns the average value of a numeric column.
* NULL values are ignored.
``` sql
SELECT AVG(column_name)
FROM table_name
WHERE condition;
```
# SUM()
* returns the total sum of a numeric column.
``` sql
SELECT SUM(column_name)
FROM table_name
WHERE condition;
```
# LIKE
* used in a WHERE clause to search for a specified pattern in a column.
``` sql
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
```
* NOT LIKE
* `SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';`
### Wildcard Characters
* used to substitute one or more characters in a string.
* can be used in combinations
* % represents zero, one, or multiple characters
* _ represents one, single character
# IN
* to specify multiple values in a WHERE clause
* a shorthand for multiple OR conditions.
``` sql
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
```
or
``` sql
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
```
* NOT IN
* `SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');`
``` sql
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
```
> selects all customers that are from the same countries as the suppliers
# BETWEEN
* elects values within a given range. The values can be numbers, text, or dates.
* begin and end values are included.
``` sql
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
```
* NOT BETWEEN
* `SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;`
# AS
* used to give a table, or a column in a table, a temporary name
* only exists for the duration of that query
``` sql
SELECT column_name AS alias_name
FROM table_name;
```
``` sql
SELECT column_name(s)
FROM table_name AS alias_name;
```
``` sql
SELECT CustomerName, CONCAT_WS(', ', Address, PostalCode, City, Country) AS Address
FROM Customers;
```
> creates an alias named "Address" that combine four columns (Address, PostalCode, City and Country)
* Aliases can be useful when:
* There are more than one table involved in a query
* Functions are used in the query
* Column names are big or not very readable
* Two or more columns are combined together
# JOIN
* used to combine rows from two or more tables, based on a related column between them
``` sql
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
```
> the "CustomerID" column in the "Orders" table refers to the "CustomerID" in the "Customers" table. The relationship between the two tables above is the "CustomerID" column.
* **INNER JOIN**: Returns records that have matching values in both tables
``` sql
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
```
* **LEFT JOIN**: Returns all records from the left table(table1), and the matched records from the right table
``` sql
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
```
* **RIGHT JOIN**: Returns all records from the right table(table2), and the matched records from the left table
``` sql
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
```
* **CROSS JOIN**: Returns all records from both tables
``` sql
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
```
* **Self Join**:a regular join, but the table is joined with itself.
``` sql
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
```
> T1 and T2 are different table aliases for the same table.
# UNION
* used to combine the result-set of two or more SELECT statements
* Every SELECT statement within UNION must have the same number of columns
* The columns must also have similar data types
* The columns in every SELECT statement must also be in the same order
* use **UNION ALL** to allow duplicate values
``` sql
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
```
# GROUP BY
* groups rows that have the same values into summary rows, like "find the number of customers in each country".
* often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns
``` sql
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
```
``` sql
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
```
> lists the number of customers in each country
# HAVING
* added to SQL because the WHERE keyword cannot be used with aggregate functions.
``` sql
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
```
# EXISTS
* used to test for the existence of any record in a subquery
* returns TRUE if the subquery returns one or more records.
``` sql
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
```
# ANY
* the condition will be true if the operation is true for any of the values in the range
* returns a boolean value as a result
``` sql
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
```
# ALL
* the condition will be true only if the operation is true for all values in the range.
* returns a boolean value as a result
* used with SELECT, WHERE and HAVING statements
``` sql
SELECT ALL column_name(s)
FROM table_name
WHERE condition;
```
```sql
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name
FROM table_name
WHERE condition);
```
# INSERT INTO SELECT
* copies data from one table and inserts it into another table
* requires that the data types in source and target tables matches.
``` sql
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
```
``` sql
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
```
# CASE
* goes through conditions and returns a value when the first condition is met(like an if-then-else statement)
* If no conditions are true, it returns the value in the ELSE clause.
```sql
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
```
Sign in with Wallet
Connect another wallet