¶ SQL - Structured Query Language
SQL is case insensitive, doesn't care about white spaces and multiple lines.
¶ Statements
¶ SELECT
Used to select data from a database. The result is stored in a result table, which is called the result-set.
SELECT column_list FROM TABLE_NAME;
SELECT * FROM customers;
Use an asterisk * to SELECT everything FROM something.
SELECT * FROM Customers;
SELECT * FROM Customers -- WHERE City='Berlin';
¶ SHOW
SHOW - List things
SHOW DATABASES;
SHOW DATABASES - lists the databases managed by the server.
SHOW TABLES - display all of the tables in the currently selected MySQL database.
SHOW COLUMNS - displays information about the columns in a given table.
¶ INSERT
INSERT INTO statement is used to add new rows of data to a table in the database.
INSERT INTO TABLE_NAME VALUES (value1, value2, value3,...);
INSERT INTO Employees VALUES (8, 'Anthony', 'Young', 35);
Can specify the table's column names in the INSERT INTO statement:
INSERT INTO TABLE_NAME (column1, column2, column3, ...,columnN) VALUES (value1, value2, value3,...valueN);
INSERT INTO Employees (ID, FirstName, LastName, Age) VALUES (8, 'Anthony', 'Young', 35);
Possible to insert data into specific columns only:
INSERT INTO Employees (ID, FirstName, LastName) VALUES (9, 'Samuel', 'Clark');
¶ UPDATE
UPDATE is used to alter data in a table.
UPDATE TABLE_NAME SET column1=value1, column2=value2, ... WHERE condition;
UPDATE multiple columns at the same time by using a comma.
UPDATE Employees SET Salary=5000 WHERE ID=1;
UPDATE Employees SET Salary=5000, FirstName='Robert' WHERE ID=1;
¶ DELETE
DELETE used to remove data from your table.
DELETE FROM TABLE_NAME WHERE condition;
If you omit the WHERE clause, all records in the table will be deleted !
¶ CREATE
CREATE TABLE is used to create a new table.
CREATE TABLE TABLE_NAME
(
column_name1 data_type(SIZE),
column_name2 data_type(SIZE),
column_name3 data_type(SIZE),
....
columnN data_type(SIZE)
);
CREATE TABLE Users
(
UserID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100)
);
¶ ALTER
ALTER TABLE is used to add, delete, or modify columns in an existing table.
ALTER TABLE People ADD DateOfBirth DATE;
¶ DROP
DROP is used to delete element inside a database, or the databasde itself.
DROP DATABASE databasename;
DROP TABLE tablename;
Attention lors de l'utilisation de la commande DROP
DROP DATABASE databasename; efface la base de donnée databasename !
DROP TABLE tablename; efface la table tablename !
¶ TRUNCATE
TRUNCATE is used to delete the data inside a table
TRUNCATE TABLE tablename;
¶ RENAME
ALTER TABLE is used to rename columns.
ALTER TABLE People CHANGE FirstName name VARCHAR(100);
RENAME TABLE is used to rename and entire TABLE
RENAME TABLE People TO Users;
¶ VIEW
In SQL, a VIEW is a virtual table that is based on the result-set of an SQL statement.
- A view contains rows and columns, just like a real table.
- The fields in a view are fields from one or more real tables in the database.
Views allow us to:
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
- Summarize data from various tables and use it to generate reports.
¶ Create a VIEW
Use CREATE VIEW to create a new view.
CREATE VIEW view_name AS SELECT column_name(s) FROM TABLE_NAME WHERE condition;
CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = "Brazil";
¶ Update a VIEW
A view can be updated with the CREATE OR REPLACE VIEW command.
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = "Brazil";
¶ Drop a VIEW
DROP VIEW view_name;
DROP VIEW [Brazil Customers];
¶ Keywords
¶ ORDER BY
ORDER BY - used with SELECT to sort the returned data.
SELECT * FROM customers ORDER BY LastName, Age;
¶ DISTINCT
DISTINCT - used in conjunction with SELECT to eliminate all duplicate records and return only unique ones.
SELECT DISTINCT column_name1, column_name2 FROM TABLE_NAME;
¶ LIMIT
LIMIT - used to retrieve just a subset of records.
SELECT COLUMN list FROM TABLE_NAME LIMIT [offset (optional), NUMBER OF records];
¶ AS
AS - Renames a column or table with an alias
SELECT CONCAT(FirstName,', ', City) AS new_column FROM customers;
¶ DESC & ASC
DESC - sorts results in descending order.
ASC - sorts the results in ascending order.
SELECT FirstName, Salary FROM employees WHERE Salary > 3100 ORDER BY Salary DESC;
SELECT FirstName, Salary FROM employees WHERE Salary > (SELECT AVG(Salary) FROM employees) ORDER BY Salary DESC;
¶ PRIMARY KEY
The PRIMARY KEY ensures that a column has a unique identity.
CREATE TABLE Users
(
UserID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100),
PRIMARY KEY(UserID)
);
¶ AUTO INCREMENT
AUTO INCREMENT allows a unique number to be generated when a new record is inserted into a table. Default starting value is 1
CREATE TABLE Persons (
Personid INT NOT NULL AUTO_INCREMENT,
LastName VARCHAR(255) NOT NULL,
FirstName VARCHAR(255),
Age INT,
PRIMARY KEY (Personid)
);
¶ Constraints Keywords
NOT NULL - cannot contain any NULL value.
UNIQUE - Does not allow to insert a duplicate value. More than one UNIQUE column can be used in a table.
PRIMARY KEY - Enforces the table to accept unique data for a specific column and create a unique index.
CHECK - Determines whether the value is valid or not from a logical expression.
DEFAULT - While inserting data, if no value is supplied, gets the value set as DEFAULT.
name VARCHAR(100) NOT NULL
¶ Data Types
Data types specify the type of data for a particular column.
CREATE TABLE USER (
UserID INT,
FirstName VARCHAR(100)
);
¶ Numeric
INT - A normal-sized integer that can be signed or unsigned.
FLOAT(M,D) - A floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).
DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).
¶ Date and Time
DATE - A date in YYYY-MM-DD format.
DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format.
TIMESTAMP - A timestamp, calculated from midnight, January 1, 1970
TIME - Stores the time in HH:MM:SS format.
¶ String Type
CHAR(M) - Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
VARCHAR(M) - Variable-length character string. Max size is specified in parenthesis.
BLOB - “Binary Large Objects” and are used to store large amounts of binary data, such as images or other types of files.
TEXT - Large amount of text data.
¶ Operators
Operators used with WHERE to filter the data to be selected
¶ Arithmetic
+, -, *, /, %
SELECT ID, FirstName, LastName, Salary+500 AS Salary FROM employees;
¶ Comparison
=, !=, >, <, >=, ⇐, BETWEEN
SELECT * FROM customers WHERE ID != 5;
SELECT * FROM customers WHERE ID BETWEEN 3 AND 7;
SELECT ID, FirstName, LastName, City FROM customers WHERE City = 'New York';
¶ Logical
Used to combine two Boolean values and return a result of true, false, or null.
AND, OR, IN, NOT
SELECT ID, FirstName, LastName, Age FROM customers WHERE Age >= 30 AND Age <= 40;
SELECT * FROM customers WHERE City = 'New York' AND (Age=30 OR Age=35);
SELECT * FROM customers WHERE City IN ('New York', 'Los Angeles', 'Chicago');
¶ Bitwise
& - Bitwise AND
| - Bitwise OR
^ - Bitwise exclusive OR
SELECT 21^22
SELECT 21 | 22
¶ UNION
UNION[1] - combines multiple datasets into a single dataset, and removes any existing duplicates.
TIP If your columns don't match exactly across all queries, you can use a NULL (or any other) value.
SELECT FirstName, LastName, Company FROM BusinessContacts
UNION
SELECT FirstName, LastName, NULL FROM otherContacts;
¶ UNION ALL
UNION ALL - combines multiple datasets into one dataset, but does not remove duplicate rows.
UNION ALL is faster than UNION, as it does not perform the duplicate removal operation over the data set.
All SELECT statements within the UNION must have the same number of columns. The columns must also have the same data types. Also, the columns in each SELECT statement must be in the same order.
SELECT ID, FirstName, LastName, City FROM FIRST
UNION ALL
SELECT ID, FirstName, LastName, City FROM SECOND;
¶ Search Conditions
¶ WHERE
WHERE - used to extract only those records that fulfill a specified criterion.
SELECT column_list FROM TABLE_NAME WHERE condition;
¶ =, !=, >, <, >=, ⇐, BETWEEN
Operators used with WHERE to filter the data to be selected
SELECT * FROM customers WHERE ID != 5;
SELECT * FROM customers WHERE ID BETWEEN 3 AND 7;
SELECT ID, FirstName, LastName, City FROM customers WHERE City = 'New York';
¶ AND, OR, IN, NOT
Logical Operators used to combine two Boolean values and return a result of true, false, or null.
SELECT ID, FirstName, LastName, Age FROM customers WHERE Age >= 30 AND Age <= 40;
SELECT * FROM customers WHERE City = 'New York' AND (Age=30 OR Age=35);
SELECT * FROM customers WHERE City IN ('New York', 'Los Angeles', 'Chicago');
¶ SQL pattern matching
use _ to match any single character
SELECT * FROM employees WHERE last_name LIKE '_ _C%';
% to match an arbitrary number of characters (including zero characters).
SELECT * FROM employees WHERE FirstName LIKE 'A%';
¶ LIKE
LIKE operator used when specifying a search condition within your WHERE clause.
SELECT column_name(s) FROM TABLE_NAME WHERE column_name LIKE pattern;
SELECT * FROM employees WHERE FirstName LIKE 'A%';
¶ BETWEEN
BETWEEN selects values within a range.
SELECT column_name(s) FROM TABLE_NAME WHERE column_name BETWEEN value1 AND value2;
¶ Functions
¶ String Functions
¶ CONCAT
CONCAT[2] - used to concatenate two or more text values and returns the concatenating string, results in a new column.
SELECT CONCAT(FirstName, ', ' , City) FROM customers;
RESULT:
John,Philadelphia
Emily,Houston
¶ UPPER & LOWER
UPPER - converts all letters in the specified string to uppercase
LOWER - converts the string to lowercase
SELECT FirstName, UPPER(LastName) AS LastName FROM employees;
¶ Numeric Functions
¶ SORT
SORT - returns the square root of given value in the argument
AVG - returns the average value of a numeric column
SELECT Salary, SQRT(Salary) FROM employees;
SELECT AVG(Salary) FROM employees;
¶ SUM
SUM - used to calculate the sum for a column's values.
SELECT SUM(Salary) FROM employees;
¶ MIN
MIN - used to return the minimum value of an expression.
SELECT MIN(Salary) AS Salary FROM employees;
¶ Clause
¶ JOIN
Joining tables means combining data from two or more tables. A table join creates a temporary table showing the data from the joined tables.
SELECT customers.ID, customers.Name, orders.Name, orders.Amount
FROM customers, orders
WHERE customers.ID=orders.Customer_ID
ORDER BY customers.ID;
Shortening the join statement is possible
SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders AS ord
WHERE ct.ID=ord.Customer_ID
ORDER BY ct.ID;
¶ INNER JOIN
Equivalent to JOIN
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;
¶ LEFT JOIN
Returns all rows from the left table, even if there are no matches in the right table (NULL).
SELECT table1.column1, table2.column2... FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;
¶ RIGHT JOIN
Returns all rows from the right table, even if there are no matches in the left table (NULL).
SELECT table1.column1, table2.column2... FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;
¶ Extras
¶ Fully Qualified Names
Fully Qualified Names: provide the table name prior to the column name by separating them with a dot, useful when working with multiple tables that may share the same column names. The following statements are equivalent:
SELECT City FROM customers;
SELECT customers.City FROM customers;