SQL基础(SOLO LEARN + W3CSCHOOL)

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

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.

Notice: What’s the differences between char and varchar

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];