Technology Sharing

한어Русский языкEnglishFrançaisIndonesianSanskrit日本語DeutschPortuguêsΕλληνικάespañolItalianoSuomalainenLatina

---

learning target:

• 1. Understand the basic concepts and functions of DQL (Data Query Language).

• 2. Master the basic syntax structure of SQL queries, including the use of clauses such as SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.

• 3. Understand and be able to use the WHERE clause for conditional screening, including the use of logical operators (AND, OR, NOT) and comparison operators (=, <>, >, <, >=, <=).

• 4. Master the use of the GROUP BY clause to group query results, and be able to use aggregate functions (such as COUNT, SUM, AVG, MAX, MIN) to perform statistical analysis of data.

• 5. Learn to use the HAVING clause to perform conditional screening on the grouped results.

• 6. Understand and be able to use the ORDER BY clause to sort query results, including single-column sorting and multi-column sorting, and use the ASC and DESC keywords to specify the sorting direction.

• 7. Master the use of subqueries, including nesting subqueries in SELECT, FROM, and WHERE clauses.

• 8. Understand and be able to use the DISTINCT keyword to remove duplicate records in query results.

• 9. Learn to use the LIMIT clause to limit the number of query results.

---

Learning Content:

DQL (Data Query Language) is an important part of SQL (Structured Query Language) and is specifically used to retrieve data from a database. It allows users to request information from a database by writing query statements without modifying the data itself. The core of DQL is the SELECT statement, which is one of the most commonly used commands in database queries.

• SELECT

SELECT The clause is used to specify the columns to be retrieved from the database. It can be a single column name, multiple column names, or use an asterisk (*) to select all columns.

SELECT column1, column2, ...
FROM table_name;
1
2

• FROM

FROM The clause is used to specify which table or tables to retrieve data from.

SELECT column1, column2, ...
FROM table_name;
1
2

• WHERE

WHERE The clause is used to filter the result set and return only the records that meet the specified conditions.

SELECT column1, column2, ...
FROM table_name
WHERE condition;
1
2
3

• GROUP BY

GROUP BY The clause is used to group data in the result set and is usually used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), MIN(), etc.

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1;
1
2
3

• HAVING

HAVING Clauses are used toGROUP BYThe result set returned by the clause is filtered by conditions, similar toWHEREClause, butHAVINGAggregate functions can be used.

SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1
HAVING COUNT(column2) > 10;
1
2
3
4

• ORDER BY

ORDER BY The clause is used to sort the result set. One or more columns can be specified, and the sort direction can be specified (ascending ASC or descending DESC).

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC, column2 DESC;
1
2
3

• Operators

In SQL,WHEREThe clause is used to specify the conditions for retrieving data from the database table. By using logical operators (AND, OR, NOT) and comparison operators (=, <>, >, <, >=, <=), you can build complex query conditions to filter data.

Here are some examples of using these operators:

1. Using comparison operators:

   • = equal
   • <> not equal to
   • > more than the
   • < Less than
   • >= greater or equal to
   • <= Less than or equal to

2. Using logical operators:

   • AND Used to combine multiple conditions. Records will be selected only when all conditions are true.
   • OR Used to combine multiple conditions. As long as one of the conditions is true, the record will be selected.
   • NOT Used to invert the result of a condition.

When using these operators, you need to pay attention to the precedence of the operators.NOT has the highest priority, followed byAND,After that ORIf you are unsure of the precedence, you can use parentheses. () to explicitly specify the order of operations.

• ASC and DESC

In SQL query,ASC andDESC Keywords are used to specify the direction in which the column should be sorted.ASC indicates ascending order (low to high), whileDESC Indicates descending order (from high to low). By default, if no sort direction is specified, the SQL query will use ascending order.

1. Sort ascending:

SELECT * FROM employees
ORDER BY salary ASC;
1
2

This query will return all employee records sorted by salary in ascending order.

2. Sort descending:

SELECT * FROM employees
ORDER BY salary DESC;
1
2

This query will return all employee records sorted by salary in descending order.

3. CombinationGROUP BYandHAVINGOrder of clauses:

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY avg_salary DESC;
1
2
3
4
5

This query will return the average salary for each department and only include those departments where the average salary is over 50,000. The results will be sorted by average salary in descending order.

---

Summarize

图片来源于https://www.bilibili.com/video/BV1Kr4y1i7ru?p=24&spm_id_from=pageDriver&vd_source=1f3140ce51cb028326bf69f5117609d8