Basic query

Basic grammar

select [distinct] * | {colunm1,colunm2,...}
	from table_name;
	
characteristic
1,The query list can be fields, constants, expressions, functions, or multiple
2,The query result is a virtual table

Basic query statement

SELECT 1; #There are no clauses
SELECT 9/2; #There are no clauses

SELECT Identifies which columns to select
	FROM Identifies which table to select from

De duplication alias table structure

distinct (de duplication)

SELECT DISTINCT job_id FROM employees;

Alias

Space AS double quotation mark

# as: full name: alias (alias), which can be omitted
# Column aliases can be referenced with a pair of '', not ''.
SELECT employee_id emp_id,last_name AS lname,department_id "department id",salary * 12 AS "annual sal"
	FROM employees;

View table structure:

DESCRIBE employees;
DESC employees;

In general, unless you need to use all the field data in the table, it is best not to use the wildcard '*'. Although using wildcards can save the time of entering query statements, obtaining unnecessary column data usually reduces the efficiency of the query and the application used. The advantage of wildcards is that when you don't know the names of the required columns, you can get them through it.

In the production environment, it is not recommended that you directly use SELECT * for query.

mark of emphasis

SQL syntax needs to ensure that the fields and table names in the table do not conflict with reserved words, database systems or common methods. If it is really the same, use a pair of ` ` (emphasis marks) in the SQL statement.

mysql> SELECT * FROM ORDER;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'ORDER' at
line 1

Condition query

Condition query: filter the data of the original table according to the conditions and query the desired data

Syntax:
select
Field | expression | constant value | function to query
from table name
where conditions;

-- Query the scores of students with the name of Zhao Yun
SELECT * FROM student WHERE `name` = 'Zhao Yun';
-- Query students with English scores greater than 90
SELECT * FROM student WHERE english>90;
-- Query all students whose total score is greater than 250
SELECT * FROM student 
	WHERE (chinese + english + math)>250;

Note:

Aggregate functions cannot be used in a WHERE clause.

Sorting and paging

Sorting using the ORDER BY clause

ASC (ascend): ascending (default)

DESC (descend): descending

The ORDER BY clause is at the end of the SELECT statement (except the limit statement).

Single column sorting:

-- On the scores of students surnamed Han[Total score]Sort output(Ascending order) where + order by
SELECT `name`,(chinese + english + math)AS total_score FROM student
	WHERE `name` LIKE 'Han%'
	ORDER BY (chinese + english + math);

SELECT *,(chinese + english + math)AS total FROM student
	WHERE `name` LIKE 'Han%'
	ORDER BY total;

Multi column sorting:

-- First in ascending mathematical order, if equal, in descending English order
SELECT `name` ,math,english FROM student
	ORDER BY math,english DESC;

You can sort using columns that are not in the SELECT list.

When sorting multiple columns, the first column to be sorted must have the same column value before sorting the second column. If all values in the first column of data are unique, the second column will no longer be sorted.

paging

When the number of items to be queried is too many, one page cannot be displayed completely

Format:

LIMIT [Position offset,] Number of rows

The first "position offset" parameter indicates which row MySQL displays from. It is an optional parameter. If "position offset" is not specified, it will start from the first record in the table (the position offset of the first record is 0, the position offset of the second record is 1, and so on); The second parameter "number of rows" indicates the number of records returned.

"LIMIT 3 OFFSET 4" can be used in MySQL 8.0, which means to obtain the following three records from the fifth record, and "LIMIT 4,3;" The results returned are the same.

Formula:

If the number of pages to be displayed is page, the number of entries on each page is size
select query list
from table
limit (page-1)*size,size;

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

Grouping query

You can use the GROUP BY clause to divide the data in a table into groups.

Single column grouping

In the SELECT list, all columns not included in the aggregate function should be included in the GROUP BY clause. Columns included in the GROUP BY clause do not have to be included in the SELECT list

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

Multi column grouping

example
-- Displays the average wage and minimum wage for each position in each department
/*Analysis 1 Displays the average wage and minimum wage for each department
	2. Displays the average wage and minimum wage for each position in each department
*/
SELECT FORMAT(AVG(sal),2),MIN(sal),deptno,job
	FROM emp
	GROUP BY deptno,job;	

Note:

If there are both aggregate and non aggregate functions in the query, you need to use group by.

Use WITH ROLLUP in GROUP BY

After using the WITH ROLLUP keyword, add a record after all the queried grouping records. The record calculates the sum of all the queried records, that is, the number of statistics records.

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

be careful:

When using ROLLUP, you cannot use the ORDER BY clause to sort the results at the same time, that is, ROLLUP and ORDER BY are mutually exclusive.

HAVING

Filtering groups: HAVING clause

1. Rows have been grouped.

2. Aggregate function used.

3. Groups that meet the conditions in the HAVING clause are displayed.

4. HAVING cannot be used alone. It must be used together with GROUP BY.

-- Displays departments with a maximum wage of more than 10000 id
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;

-- Displays the department number whose average salary is less than 2000 and its average salary
SELECT AVG(sal),deptno FROM emp
	GROUP BY deptno
	HAVING AVG(sal)<2000;

Comparison between WHERE and HAVING

1. WHERE can directly use the fields in the table as the filter criteria, but cannot use the calculation function in the grouping as the filter criteria; HAVING must be used in conjunction with GROUP BY. Grouping calculation functions and grouping fields can be used as filtering criteria.
2. If you need to obtain the required data from the associated table through connection, WHERE is filtering before connection, and HAVING is connecting before filtering.

Features: WHERE is more efficient than HAVING.

multi-table query

Multi table query, also known as association query, refers to two or more tables completing the query operation together.

Prerequisite: these tables queried together are related (one-to-one, one to many). There must be an associated field between them. This associated field may or may not have a foreign key. For example, employee table and department table, which are related by department number.

Cartesian product

Cartesian product is a mathematical operation. Suppose I have two sets X and y, then the Cartesian product of X and Y is all possible combinations of X and y, that is, the first object comes from X and the second object comes from all possible combinations of Y. The number of combinations is the product of the number of elements in the two sets.

Cartesian product occurs under the following conditions:

• Omit join conditions (or association conditions) for multiple tables
• The connection condition (or association condition) is invalid
• All rows in all tables are connected to each other

In order to avoid Cartesian product, effective connection conditions can be added in WHERE.

Equivalent connection vs non equivalent connection

Equivalent connection – the condition after where is the equivalent connection condition

SELECT employees.employee_id, employees.last_name,
	employees.department_id, departments.department_id,
	departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

extend

1. Use AND operator for multiple connection conditions

2. If multiple tables have the same column, the table name prefix must be added before the column name.

3. Columns with the same column name in different tables can be distinguished by table name.

4. Connecting n tables requires at least n-1 connection conditions

Note:

If we use the alias of the table, we can only use the alias in the query field and filter criteria instead of the original table name, otherwise an error will be reported.

Ali development specification

[mandatory] for the query and change of table records in the database, as long as multiple tables are involved, the alias (or table name) of the table needs to be added before the column name.

Non equivalent connection – the condition after where is a non equivalent connection condition

SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

-- Query English score at 80－90 Between the students.
SELECT * FROM student
	WHERE english>=80 AND english<=90;

Self connected vs non self connected

Self connection

When it is necessary to use the same table as two tables

When table1 and table2 are essentially the same table, they are only virtualized into two tables by alias to represent different meanings. Then the two tables are queried for internal connection and external connection.

-- Self join syntax:
	select Query list
	from Table 1 aliases,Table 1 aliases
	where [Non equivalent connection conditions
	[and [filter criteria];

SELECT worker.ename AS 'Employee name',boss.ename AS 'Superior name'
	FROM AS emp  worker,emp boss
	WHERE worker.mgr = boss.empno;

Non self connection

The query table does not have the same table in essence

Internal connection vs external connection

Inner join –: merge rows of more than two tables with the same column. The result set does not contain rows that do not match one table with another.

Outer join –: two tables return rows in the left (or right) table that do not meet the conditions in addition to the rows that meet the connection conditions. This connection is called left (or right) outer join. When there is no matching row, the corresponding column in the result table is empty (NULL).

SQL92: create connection using (+)

Internal connections are those above. External connections are as follows

#Left outer connection
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#Right outer connection
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

In SQL92, (+) is used to represent the location of the slave table. That is, in the left or right outer join, (+) indicates which is the slave table.

Oracle supports SQL92 well, while MySQL does not support the external connection of SQL92. Moreover, in SQL92, there are only left external connections and right external connections, and there are no full (or full) external connections.

Implementation of multi table query with SQL99 syntax

Basic grammar

SELECT table1.column, table2.column,table3.column
	FROM table1
	JOIN table2 ON table1 and table2 Connection conditions
	JOIN table3 ON table2 and table3 Connection conditions

Inner join

#grammar
select Query list
	from Table 1 aliases
	[INNER]join Table 2 aliases on Connection conditions
	WHERE Other clauses such as;

External join

-- 1 Left outer connection: all tables on the left are displayed
-- 2 Right outer connection: all tables on the right are displayed
 Syntax:
select Query list
	from Table 1 aliases
	left|right|full [outer] join Table 2 aliases on Connection conditions
	where Screening conditions;
-- For example, list the Department name and the employee name and work of these departments, and display the departments without employees.
-- Use left outer connection	
SELECT dname, ename, job 
	FROM dept LEFT JOIN emp
	ON dept.deptno = emp.deptno;

characteristic:
① Query result = all rows in the master table. If the slave table matches it, the matching row will be displayed. If there is no matching row in the slave table, null will be displayed
② The main table is on the left of left join and on the right of right join
Both sides of the full join are primary tables
③ It is generally used to query the remaining mismatched rows except the intersection part

Note:

LEFT JOIN and RIGHT JOIN only exist in SQL99 and later standards. They do not exist in SQL92 and can only be represented by (+).

Full outer join

The result of full external connection = data matched by the left and right tables + data not matched by the left table + data not matched by the right table. SQL99 supports full external connections. Use FULL JOIN or FULL OUTER JOIN to implement. It should be noted that MySQL does not support FULL JOIN, but LEFT JOIN UNION RIGHT join can be used instead.

Use of UNION

Union (merge) query – combines multiple query results into one result.

The UNION operator returns the UNION of the result sets of two queries to remove duplicate records.

The UNION ALL operator returns the union of the result sets of two queries. For the duplicate parts of the two result sets, the duplicate is not removed.

Note: less resources are required to execute the UNION ALL statement than the UNION statement. If you clearly know that there is no duplicate data in the result data after merging data, or you do not need to remove the duplicate data, try to use the UNION ALL statement to improve the efficiency of data query.

-- union all This is to merge the two query results without duplication
SELECT ename,sal,job FROM emp WHERE sal>2500 
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' 

-- union  Merging the two query results will eliminate duplication
SELECT ename,sal,job FROM emp WHERE sal>2500 
UNION 
SELECT ename,sal,job FROM emp WHERE job='MANAGER' 

characteristic

• The number of columns of multiple query statements must be consistent
• The types of columns in a query statement are almost the same
• Union stands for de duplication, and union all stands for no de duplication

New features of SQL99 syntax

Natural connection

SQL99 provides some special syntax based on SQL92. For example, NATURAL JOIN is used to represent NATURAL JOIN.

It will help you automatically query all the same fields in the two connection tables, and then make equivalent connection.

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

USING connection

SQL99 also supports equivalent connection of fields with the same name in the data table specified by USING. However, it can only be used with JOIN.

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

Different from the NATURAL JOIN, USING specifies the specific same field name. You need to fill in the parentheses () of USING with the same name. USING JOIN... USING at the same time can simplify the equivalent connection of JOIN ON.

[mandatory] join is prohibited for more than three tables. The data types of the fields that need to be joined are absolutely consistent; In multi table Association query, ensure that the associated fields need to have indexes.

Subquery

Basic use

A subquery is a query in which one query statement is nested within another query statement.

Usage:

The query needs to obtain data from the result set, or calculate a data result from the same table, and then compare it with the data result (which may be a scalar or a set).

example:

# How do I display all employees in the same department as SMITH?
/*
1. First query the department number of SMITH
SELECT deptno FROM emp
	WHERE ename = 'SMITH';
2. Use the above select statement as a temporary table
*/
-- Method 1:	
SELECT ename FROM emp,(-- cursor
	SELECT deptno FROM emp
	WHERE ename = 'SMITH'
)table_deptno	
	WHERE emp.deptno = table_deptno.deptno;
-- Method 2:	
SELECT ename FROM emp
	WHERE deptno = (SELECT deptno FROM emp
				WHERE ename = 'SMITH'
			);
			
# Ask which of the employees has a higher salary than Abel			
SELECT last_name,salary
FROM employees
WHERE salary > (
SELECT salary
FROM employees
WHERE last_name = 'Abel'
);

Use of subqueries

• The sub query (internal query) is executed once before the main query.
• The results of the sub query are used by the main query (external query).

matters needing attention

• Subqueries should be enclosed in parentheses
• Place the subquery to the right of the comparison criteria
• Single row operators correspond to single row subqueries, and multi row operators correspond to multi row subqueries

Classification of subqueries

• According to whether one or more records are returned according to the results of internal query, the sub query is divided into single line sub query and multi line sub query.

• Sub queries are divided into related (or associated) sub queries and unrelated (or non associated) sub queries according to whether the internal query has been executed multiple times.

single-row subqueries

There is only one row in the query result

Single line comparison operator: = > > = < < = < >

Multiline subquery

There are multiple rows of results in the query

Multiline operator: in any all

Related sub query

If the execution of a subquery depends on an external query, it is usually because the tables in the subquery use external tables and are conditionally associated. Therefore, every time an external query is executed, the subquery must be recalculated. Such a subquery is called an associated subquery. Related sub queries are executed row by row, and sub queries are executed once for each row of the main query.

EXISTS and NOT EXISTS keywords

The associated subquery is usually used together with the EXISTS operator to check whether there are qualified rows in the subquery.

• If there are no qualified rows in the subquery:

  • The condition returns FALSE
  • Continue to find in subquery

• If there are qualified rows in the subquery:

  • Do not continue finding in subquery
  • The condition returns TRUE

• NOT EXISTS keyword indicates that if there is no condition, it returns TRUE; otherwise, it returns FALSE.

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT *
                  FROM employees
                  WHERE department_id = d.department_id
                 );

**Subquery or self connection can be used in the topic:**

In general, it is recommended that you use self connection, because in the processing of many DBMS, the processing speed of self connection is much faster than that of sub query.

SELECT execution process

#Mode 1:
SELECT ...,....,...
FROM ...,...,....
WHERE Join conditions for multiple tables
AND Filter conditions without group functions
GROUP BY ...,...
HAVING Contains filter criteria for group functions
ORDER BY ... ASC/DESC
LIMIT ...,...

#Mode 2:
SELECT ...,....,...
FROM ... JOIN ...
ON Join conditions for multiple tables
JOIN ...
ON ...
WHERE Filter conditions without group functions
AND/OR Filter conditions without group functions
GROUP BY ...,...
HAVING Contains filter criteria for group functions
ORDER BY ... ASC/DESC
LIMIT ...,...
#Of which:
#(1) From: which tables to filter from
#(2) on: Descartes product is removed when associating multi table queries
#(3) where: filter criteria from table
#(4) Group by: group by
#(5) having: filter again in the statistical results
#(6) order by: sort
#(7) limit: paging

There are two orders in SELECT query:

1. The order of keywords cannot be reversed

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

1. The execution order of the SELECT statement

From - > where - > group by - > having - > selected fields - > distinct - > order by - > limit