SQL
BASICS
Selecting All Columns
SELECT * FROM customers;
The DISTINCT Keyword
The SQL DISTINCT keyword is used in conjunction with SELECT to eliminate all duplicate records and return only unique ones.
SELECT DISTINCT column_name1, column_name2
FROM table_name;
The LIMIT Keyword
By default, all results that satisfy the conditions specified in the SQL statement are returned. However, sometimes we need to retrieve just a subset of records. In MySQL, this is accomplished by using the LIMIT keyword.
SELECT column list
FROM table_name
LIMIT [number of records];
You can also pick up a set of records from a particular offset.
In the following example, we pick up four records, starting from the third position:
SELECT ID, FirstName, LastName, City
FROM customers LIMIT 3, 4;
The reason that it produces results starting from ID number four, and not three, is that MySQL starts counting from zero, meaning that the offset of the first row is 0, not 1.
Fully Qualified Names
SELECT City FROM customers;
SELECT customers.City FROM customers;
This form of writing is especially useful when working with multiple tables that may share the same column names.
Order By
SELECT * FROM customers
ORDER BY FirstName;
ORDER BY A,B will order the result by A, then by B
FILTERING,FUNCTIONS,SUBQUERIES
The WHERE Statement
The WHERE clause is used to extract only those records that fulfill a specified criterion.
SELECT column_list
FROM table_name
WHERE condition;
Comparison Operators and Logical Operators
Comparison Operators and Logical Operators are used in the WHERE clause to filter the data to be selected.
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3Mgbvain-1577425812971)(https://ooo.0o0.ooo/2019/12/22/bIh3zmkB9fjUuPo.jpg)]
Comparison Operators
The between operator
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
TEXT VALUE
When working with text columns, surround any text that appears in the statement with single quotation marks (’).
EXAMPLE:
SELECT ID, FirstName, LastName, City
FROM customers
WHERE City = 'New York';
Logical Operators
Logical operators can be used to combine two Boolean values and return a result of true, false, or null.
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dJBEouZl-1577425812973)(https://ooo.0o0.ooo/2019/12/22/HO3GiNIewYygAxP.jpg )]
SELECT ID, FirstName, LastName, Age
FROM customers
WHERE Age >= 30 AND Age <= 40;
Conjunctive operators
When combining these conditions, it is important to use parentheses, so that the order to evaluate each condition is known.
SELECT * FROM customers
WHERE City = 'New York'
AND (Age=30 OR Age=35);
IN AND NOT IN STATEMENT
IN
You can achieve the same result with a single IN condition, instead of the multiple OR conditions:
SELECT * FROM customers
WHERE City IN ('New York', 'Los Angeles', 'Chicago');
NOT IN
The NOT IN operator allows you to exclude a list of specific values from the result set.
The CONCAT Function
The CONCAT function is used to concatenate two or more text values and returns the concatenating string.
SELECT CONCAT(FirstName, ', ' , City) FROM customers;
output
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o4WXXVbv-1577425812976)(https://ooo.0o0.ooo/2019/12/23/TYbImfShplDwXMv.jpg)]
The AS Keyword
A concatenation results in a new column. The default column name will be the CONCAT function.You can assign a custom name to the resulting column using the AS keyword
SELECT CONCAT(FirstName,', ', City) AS new_column
FROM customers;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5vImeGHW-1577425812978)(https://ooo.0o0.ooo/2019/12/23/lZyFkzmiaSV5CTg.jpg)]
Arithmetic operator
Arithmetic operators perform arithmetical operations on numeric operands. The Arithmetic operators include addition (+), subtraction (-), multiplication (*) and division (/).
The example below adds 500 to each employee’s salary and selects the result:
SELECT ID, FirstName, LastName, Salary+500 AS Salary
FROM employees;
Upper
SELECT FirstName, UPPER(LastName) AS LastName
FROM employees;
SORT & AVG & SUM
The SQRT function returns the square root (平方根)of given value in the argument.
Similarly, the AVG function returns the average value of a numeric column.
The SUM function is used to calculate the sum for a column’s values
SUBQUERIES
A subquery is a query within another query.
SELECT FirstName, Salary FROM employees
WHERE Salary > 3100
ORDER BY Salary DESC;
ASC : ascending
DESC : descending
SELECT FirstName, Salary FROM employees
WHERE Salary > (SELECT AVG(Salary) FROM employees)
ORDER BY Salary DESC;
LIKE & MIN
LIKE OPERATOR
The LIKE keyword is useful when specifying a search condition within your WHERE clause. NOT LIKE is the opposite of LIKE.
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
SQL pattern matching enables you to use “_” to match any single character and “%” to match an arbitrary number of characters (including zero characters).
Notice: In MS Access a “*” will be used rather than “%”, a “?” will be used rather than “_”.
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
In the pattern which exclude the string start with “b”, “s”, “p”
MIN
The MIN function is used to return the minimum value of an expression in a SELECT statement.
SELECT MIN(Salary) AS Salary FROM employees;
JOIN TABLE & OPERATIONS
Joining Tables
To join the two tables, specify them as a comma-separated list in the FROM clause:
SELECT customers.ID, customers.Name, orders.Name, orders.Amount
FROM customers, orders
WHERE customers.ID=orders.Customer_IDq
ORDER BY customers.ID;
Custom Names
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;
TYPE OF JOIN
The following are the types of JOIN that can be used in MySQL:
- INNER JOIN: selects all rows from both table as long as there is a match between the columns in both table
- LEFT JOIN:return all rows from the left table,with the matching rows in the right table
- RIGHT JOIN:return all rows from the right table,with the matching rows in the right table
INNER JOINT
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c98VkZIf-1577425812983)(https://ooo.0o0.ooo/2019/12/24/UOsNLQPljZHi1pw.jpg)]
SELECT column_name(s)
FROM table1 INNER JOIN table2
ON table1.column_name=table2.column_name
Note the ON keyword for specifying the inner join condition.
LEFT JOINT
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Tl0U4YWz-1577425812986)(https://ooo.0o0.ooo/2019/12/24/QzsrqJtHnlNVP7f.jpg)]
SELECT table1.column1, table2.column2...
FROM table1 LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name
The OUTER keyword is optional, and can be omitted.
RIGHT JOINT
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Y2JYXIal-1577425812988)(https://ooo.0o0.ooo/2019/12/24/7eKykvzrQdnch64.jpg)]
SELECT table1.column1, table2.column2...
FROM table1 RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;
Notice: Inner joint use select xxx from table A, table B where table A.xxx=table B.xxx; Left joint and Right joint use on rather than where
FULL OUT JOINT
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8EJB5hQV-1577425812991)(https://img.vim-cn.com/09/bf44cdf4b452fd15976545cb29d9c3063a797f.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PWTc5nME-1577425812992)(https://img.vim-cn.com/8c/029c9745716e1e86f270370322542860252383.png)]
UNION
The UNION operator is used to combine the result-sets of two or more SELECT statements.
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
Union: The duplicates will be removed, and the column name must be the same.
Union All: The duplicates will be included.
INSERT
The 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 table_name (column1, column2, column3, ...,columnN)
VALUES (value1, value2, value3,...valueN);
UPDATE AND DELETE STATEMENT
UPDATE
UPDATE table_name
SET column1=value1, column2=value2, ...
WHERE condition;
If you omit the WHERE clause, all records in the table will be updated!
Update one value
UPDATE Employees
SET Salary=5000
WHERE ID=1;
Update Multi values
UPDATE Employees
SET Salary=5000, FirstName='Robert'
WHERE ID=1;
DELETE DATA
The DELETE statement is used to remove data from your table. DELETE queries work much like UPDATE queries.
DELETE FROM table_name
WHERE condition;
DELETE FROM Employees
WHERE ID=1;
CREATING A TABLE
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
columnN data_type(size)
);
The data type in sql
Data Types
Data types specify the type of data for a particular column.
If a column called “LastName” is going to hold names, then that particular column should have a “varchar” (variable-length character) data type.
The most common data types:
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.
Primary Key
CREATE TABLE Users
(
UserID int,
FirstName varchar(100),
LastName varchar(100),
City varchar(100),
PRIMARY KEY(UserID)
);
NOT NULL AND AUTO INCREMENT
SQL Constraints
SQL constraints are used to specify rules for table data.
The following are commonly used SQL constraints:
NOT NULL - Indicates that a column cannot contain any NULL value.
UNIQUE - Does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. 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 this constraint create a unique index for accessing the table faster.
CHECK - Determines whether the value is valid or not from a logical expression.
DEFAULT - While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.
AUTO INCREMENT
Auto-increment allows a unique number to be generated when a new record is inserted into a table.
UserID int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (UserID)
ALERT, DROP AND RENAME A TABLE
ALERT
The ALTER TABLE command is used to add, delete, or modify columns in an existing table.
You would also use the ALTER TABLE command to add and drop various constraints on an existing table.
ALTER TABLE People ADD DateOfBirth date;
NOTICE: use insert table to insert a new record but use alert to insert a now column
Dropping column
ALTER TABLE People
DROP COLUMN DateOfBirth;
Renaming
Rename column
ALTER TABLE People
CHANGE FirstName name varchar(100);
Rename table
RENAME TABLE People TO Users;
Views
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 VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
Updating a View
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
Delete a view
use DROP VIEW
DROP VIEW List;
IMPROVE
Check if null
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
COUNT
select count(DISTINCT column_name)
from table_name
where condition_1,condition_2...;
The code above can help to count the number of the columns satisfy the conditions.
Using DISTINCT can help to make sure the column unique.
EXPRESSION
SELECT column1, column2, columnN
FROM table_name
WHERE [CONDITION|EXPRESSION];