1. Sort

1.1 Basic Usage

ORDER BY is used to sort the queried data.

  • Ascending: ASC (Ascend) (default)
  • Descend: DESC (descend)

1.2 We can use column aliases for sorting

1.3 Emphasis format: WHERE must be declared after FROM, ORDER BY before.

1.4 Two-level Sorting

2. Paging

2.1 mysql uses limit to implement paging display of data

# demand1: Displays on each page20The first record is displayed1SELECT employee_id,last_name FROM employees LIMIT0.20; # demand2: Displays on each page20The first record is displayed2SELECT employee_id,last_name FROM employees LIMIT20.20; # demand3: Displays on each page20The first record is displayed3SELECT employee_id,last_name FROM employees LIMIT40.20;
Copy the code
# formula: LIMIT (pageNo-1) * pageSize,pageSize;
Copy the code

Note: The LIMIT clause must be placed at the end of the entire SELECT statement!

2.2 the WHERE… ORDER BY … LIMIT is declared in the following order:

  • Strictly speaking: LIMIT position offset, number of entries
  • Structure “LIMIT 0, number of items “is equivalent to “LIMIT number of items”
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary DESC
#limit 0.10;
LIMIT 10; # Exercise: It's in the table107Item of data, we just want to show the number32,33What about a piece of data? SELECT employee_id,last_name FROM employees LIMIT31.2;
Copy the code

2.3 MySQL8.0 new feature: LIMIT… OFFSET …

# Exercise: It's in the table107Item of data, we just want to show the number32,33What about a piece of data? SELECT employee_id,last_name FROM employees LIMIT2 OFFSET 31; SELECT employee_id,last_name,salary FROM employees ORDER BY salary DESC #limit 1 SELECT last_name,salary FROM employees ORDER BY salary DESC #limit 10.1
LIMIT 1;
Copy the code

2.4 LIMIT can be used in MySQL, PGSQL, MariaDB, SQLite and other databases, indicating paging.

Cannot be used in SQL Server, DB2, Oracle!

3. Multiple tables

If n tables implement multi-table queries, at least N-1 join conditions are required

  • Angle 1: Equivalent connection vs non-equivalent connection
  • Angle 2: Self-connected vs. non-self-connected
  • Angle 3: Inside connection vs. outside connection

Since the connection

SELECT emp.employee_id,emp.last_name,mgr.employee_id,mgr.last_name
FROM employees emp ,employees mgr
WHERE emp.`manager_id` = mgr.`employee_id`;
Copy the code

Inner join: Merges the rows of more than two tables that have the same column so that the result set does not contain rows that do not match one table with another. Outputs only those equal to and qualified.

Outer join: Join the rows of more than two tables that have the same column. In addition to the rows that match one table and another table, the result set also finds the rows that do not match in the left or right table.

Outer connection classification: left outer connection, right outer connection, full outer connection

  • Left-outer join: A join between two tables that returns not only the rows that meet the join condition but also the rows that do not meet the join condition in the left table is called a left-outer join.
  • Right-outer join: a join between two tables that returns not only the row that meets the join condition but also the row that does not meet the join condition. This join is called a right-outer join.

SQL99 use JOIN… ON to achieve the query of multiple tables. This approach also solves the external connection problem. MySQL supports this approach

SELECT last_name,department_name FROM employees e INNER JOIN DEPARTMENTS D ON E.`department_id` = d.`department_id`;

SELECT last_name,department_name,city
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON d.`location_id` = l.`location_id`;
Copy the code
Select last_name,department_name from all employees where department_name = last_name,department_name  SELECT last_name,department_name FROM employees e LEFT JOIN departments d ON e.`department_id` = d.`department_id`; SELECT last_name,department_name FROM employees e RIGHT OUTER JOIN DEPARTMENTS D ON E.`department_id` = d.`department_id`; Mysql does not support FULL OUTER JOIN SELECT last_name,department_name FROM employees E FULL OUTER JOIN DEPARTMENTS D ON E.`department_id` = d.`department_id`;
Copy the code

4. Multiple tables — implementations of seven joins

The use of UNION and UNION ALL

  • UNION: the deduplication operation will be performed
  • UNION ALL: no redo operation will be performed (priority)

Conclusion: If it is clear that there is no duplicate data in the result data after merging data, or there is no need to remove duplicate data, UNION ALL statement should be used as far as possible to improve the efficiency of data query.

SELECT employee_id,department_name FROM employees E JOIN DEPARTMENTS D ON E.`department_id` = d.`department_id`; SELECT employee_id,department_name FROM employees E LEFT JOIN DEPARTMENTS D ON E.`department_id` = d.`department_id`; SELECT employee_id,department_name FROM employees E RIGHT JOIN DEPARTMENTS D ON E.`department_id` = d.`department_id`; SELECT employee_id,department_name FROM employees E LEFT JOIN DEPARTMENTS D ON E.`department_id` = d.`department_id`
WHERE d.`department_id`IS NULL; SELECT employee_id,department_name FROM employees E RIGHT JOIN DEPARTMENTS D ON E`department_id` = d.`department_id`
WHERE e.`department_id`IS NULL; # Bottom left: Full external connection # mode1SELECT employee_id,department_name FROM employees E LEFT JOIN DEPARTMENTS D ON E`department_id` = d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id`IS NULL; # way2SELECT employee_id,department_name FROM employees E LEFT JOIN DEPARTMENTS D ON E`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`; SELECT employee_id,department_name FROM employees E LEFT JOIN DEPARTMENTS D ON E.`department_id` = d.`department_id`
WHERE d.`department_id` IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;
Copy the code

5. New features of SQL99 syntax

5.1: Natural connection.

NATURAL JOIN: This will help you to automatically query all the same fields in two JOIN tables and then perform an equivalent JOIN.

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`
AND e.`manager_id` = d.`manager_id`;


SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;
Copy the code

5.2 USING

Unlike a NATURAL JOIN, where USING specifies the exact same field name, you need to fill in the parentheses () of the USING field with the same name. Use JOIN… USING simplifies the equivalence JOIN of JOIN ON. (Recommend on)

SELECT employee_id,last_name,department_name FROM employees e JOIN departments d ON e.department_id = d.department_id; SELECT employee_id,last_name,department_name FROM employees e JOIN departments d USING (department_id); # development:  SELECT last_name,job_title,department_name FROM employees INNER JOIN departments INNER JOIN jobs ON employees.department_id = departments.department_id AND employees.job_id = jobs.job_id;Copy the code

Note:

We want to control the number of join tables. Multiple table joins, like nested for loops, can be very resource-intensive and can significantly degrade SQL query performance, so don’t join unnecessary tables. In many DBMSS, there is also a maximum join table limit.

[Mandatory] Disallow join when more than three tables are used. The data types of the fields to be joined must be the same. When multiple tables are used for associated query, ensure that the associated fields have indexes.

Note: Pay attention to table index, SQL performance even if double table JOIN.

Source: Java Development Manual of Alibaba