Database Retrieval statements and Database Retrieval statements

Database Retrieval statements and Database Retrieval statements

1. Basic SELECT usage

1.1 simple data retrieval

1.1.1 retrieve required Columns

You only need to execute "SELECT * FROM name. SELECT * FROM T_Employee. The SELECT * FROM T_Employee statement reads the information of all columns in the database and caches the information to the memory. The SQL statement for retrieving all columns is "SELECT * FROM T_Employee", and the asterisk "*" indicates "all columns ", then we only need to replace the star number "*" with the name of the column we want to retrieve. For example, SELECT FNumber FROM T_Employee. You only need to list the column names of each column after the SELECT statement. Note that the columns must be separated by commas. For example, execute the following SQL statement: SELECT FName, FAge FROM T_Employee.

1.1.2 column alias

The alias Definition Format is "column name AS alias". For example, we want to use the alias Number14 for the FNumber field.
For the FName field, the alias is Name, The FAge field is Age, and the FSalary field is Salary. Write the following SQL statement: SELECT FNumber AS Number1, FName AS Name, FAge AS Age, FSalary AS Salary FROM
T_Employee. When defining an alias, "AS" is not mandatory and can be omitted. For example, the following SQL statement is correct:
SELECT FNumber Number1, FName Name, FAge Age, FSalary Salary FROM T_Employee.

1.1.3 filter by conditions

Because all the data in the table is retrieved from the database, the memory consumption and network resource consumption are very high. If you need to retrieve each piece of data one by one to meet the filtering conditions, the retrieval speed is very slow. When the data volume is large, this speed is intolerable. Complex filtering conditions cannot be implemented. SELECT FName FROM T_Employee WHERE FSalary <5000. SELECT * FROM T_Employee WHERE FSalary <5000 OR FAge> 25. When using the WHERE clause, you only need to specify the filtering conditions. We do not need to worry about the database system's search. The database uses the appropriate optimization algorithm for query, which greatly reduces the CPU resource usage.

The SQL standard specifies the following Aggregate functions:

These aggregate functions have a parameter, which indicates the field name to be counted. The first example is to query the highest salary of an employee older than 25 years old. Execute the following SQL statement:
Select max (FSalary) FROM T_Employee WHERE FAge> 25.

To reference the query results, you can also specify an alias for the calculation results of Aggregate functions and execute the following SQL:

Select max (FSalary) as MAX_SALARY FROM T_Employee WHERE FAge> 25.

Select avg (FAge) FROM T_Employee WHERE FSalary> 3800.

Select sum (FSalary) FROM T_Employee.

Select min (FSalary), MAX (FSalary) FROM T_Employee.

The last introduced function is the COUNT of the number of records. This function is special because it can be used as a parameter like other Aggregate functions, you can also use the asterisk (*) as the parameter. Run the following SQL statement:
Select count (*), COUNT (FNumber) FROM T_Employee.

COUNT (*) counts the total number of results sets, while COUNT (FName) counts not NULL (that is, not equal to NULL) except that FName is not NULL in the result set) the total number of records.

1.1.4 sorting

The SELECT statement allows the order by clause to sort the result set. The order by clause is located at the end of the SELECT statement. It allows you to specify to sort BY one or more columns. You can also specify to sort BY Ascending ORDER (Ascending ORDER) or descending order (from large to small ). For example, the following SQL statement demonstrates a list of all employees sorted by age:
SELECT * FROM T_EmployeeORDER BY FAge ASC.

ASC stands for ascending order, which is the default sorting method. SELECT * FROM T_Employee order by FAge.

To sort data in descending order, replace ASC with DESC. DESC indicates descending order. Run the following SQL statement: SELECT * FROM T_Employee ORDER BY FAge DESC.

Because the order by statement allows multiple sorting columns to be specified, each column can be separated BY a comma. Execute the following SQL statement:
SELECT * FROM T_Employee order by FAge DESC, FSalary DESC. We can see records of the same age are arranged in the order of salary from high to low. The database system processes multiple sorting rules based on their priorities. The database system first sorts by the first sorting rule. If the order of two records cannot be distinguished by the first sorting rule, it sorts by the second sorting rule; if the order of two records cannot be distinguished according to the second sorting rule, the records are sorted according to the third sorting rule ;...... And so on. The order by clause can be fully used with the WHERE clause. The only thing to note is that the order by clause must be placed after the WHERE clause and their ORDER cannot be reversed. SELECT * FROM T_Employee WHERE FAge> 23 order by FAge DESC, FSalary DESC.

1.2 advanced Filtering

Advanced data filtering techniques are applicable to the Where clause in Update and Delete statements.

1.2.1 wildcard Filtering

The LIKE keyword is used to filter wildcards in SQL. It is a binary operator. The left expression is the field to be matched, and the right expression is the wildcard expression to be matched.

1.2.1.1 single character matching

"B _d" matches the string with the first character B, the second character as any character, and the third character as d. The wildcard expression "_ oo _" matches strings whose first character is any character, the second character is o, the third character is o, and the fourth character is any character.

SELECT * FROM T_Employee WHERE FName LIKE '_ erry '.

SELECT * FROM T_Employee WHERE FName LIKE '_ n _'.

1.2.1.2 multi-character matching

The wildcard for multi-character matching is the half-width percent sign "%", which matches any character that occurs at any number of times (zero or multiple. For example, the wildcard expression "k %" matches a string of any length starting with "k. SELECT * FROM T_Employee WHERE FName LIKE't % '.

SELECT * FROM T_Employee WHERE FName LIKE '% n % '.

Character matching and multi-character matching can also be used together. SELECT * FROM T_Employee WHERE FName LIKE '% n _'.

1.2.2 null value detection

If no non-empty constraint column is added, it can be NULL (that is, NULL ). The field name to be checked is null. For example, to query information about all employees whose names are unknown, run the following SQL statement: SELECT * FROM T_Employee WHERE FNAME IS NULL.

If you want to check that "the field is not empty", use is not null and the method IS "the field name to be checked is not null". For example, you want to query information of all employees whose names are known, run the following SQL statement: SELECT * FROM T_Employee WHERE FNAME IS NOT NULL.

SELECT * FROM T_Employee where fname is not null and FSalary <5000.

1.2.3 negative Operators

At the same time, SQL provides a general operator "<>" indicating "not equal ", in this way, "not equal", "not greater than", and "not less than" can be expressed as "<>", "<=", and "> =" respectively ". Retrieve all ages not equal to 22 years old and salaries not less than 2000 Yuan ", we can write the following SQL
Sentence: SELECT * FROM T_Employee WHERE FAge <> 22 and fsalary> = 2000.

Therefore, to complete the following function "retrieve all ages not equal to 22 years old and wages not less than 2000 Yuan", we can write the following SQL statement:
SELECT * FROM T_Employee where not (FAge = 22) and not (FSALARY <2000 ).

1.2.4 multi-value detection

You can use the OR statement to connect multiple equals judgments. The SQL statement is as follows: SELECT FAge, FNumber, FName FROM T_EmployeeWHERE FAge = 23 OR FAge = 25 OR FAge = 28.

SELECT FAge, FNumber, FName FROM T_Employee WHERE FAge IN (, 28 ).

1.2.5 range detection

Retrieve Information about all employees between the ages of 23 and 27.
Sentence: SELECT * FROM T_Employee where fage> = 23 and fage <= 27. SQL provides a special term range value check
The tested statement "bettween and" can be used to check whether a value is in a certain range (including the Boundary Value of the range, that is, the closed range ). The usage is as follows: "field name BETTWEEN left range value AND right range value ". SELECT * FROM T_Employee
Where fage between 23 AND 27.

You can use the following SQL statement to retrieve information about employees whose salaries are between 2000 and 3000 yuan and between 5000 and 8000 yuan:
SELECT * FROM T_Employee WHERE (FSalary BETWEEN 2000 AND 3000) OR (FSalary BETWEEN 5000 AND 8000 ).

The database system optimizes the query of "bettween and" AND uses it for range value detection to achieve better performance than other methods, therefore, "bettween and" should be used first for range value detection ". Note that "bettween and" includes the boundary value (closed interval) of the range during the detection ), if you want to check the range value of an open or semi-open half-closed interval, you must use other solutions.

1.2.6 inefficient where 1 = 1

Because the database system will not be able to use index and other query optimization policies after the "1 = 1" filter condition is added, the database system will be forced to scan each row of data (that is, full table scan) to compare whether this row meets the filtering conditions. When the table has a large amount of data, the query speed will be very slow. Therefore, if data retrieval requires high performance, do not use this "simple" method.

1.3 Data grouping

In SQL statements, the GROUP BY clause is used as the GROUP BY field ". The grouping statement must be used with aggregate function 1.
The group by clause is used to divide data into logical groups, while the aggregate function performs statistical calculation on each GROUP.

SELECT FAge FROM T_Employee group by FAge. The group by clause divides the search results into multiple groups, each of which is a subset of all records. The group by clause must be placed after the SELECT statement. If the SELECT statement has a WHERE clause, the group by clause must be placed after the WHERE clause.

SELECT FAge FROM T_Employee WHERE FSubCompany = 'beijing' group by FAge all columns to be grouped must be in the column name list of the group by clause, that is, columns not in the group by clause (except for Aggregate functions) cannot be placed in the column name list after the SELECT statement.

SELECT FAge, AVG (FSalary) FROM T_Employee group by FAge. You can specify multiple columns in the group by clause. You only need to separate the column names of multiple columns with commas. After multiple grouping rules are specified, the database system groups the data layer by layer according to the defined grouping sequence. First, the data is grouped according to the first grouping column, then, group again in each group according to the second grouping column ...... Group layer by layer to achieve the effect of "group in group", and the query result set is output by the last-level grouping.

SELECT FSubCompany, FDepartment FROM T_Employee group by FSubCompany, FDepartment.

SELECT FAge, COUNT (*) AS CountOfThisAge FROM T_Employee group by FAge.

SELECT FSubCompany, FAge, COUNT (*) AS CountOfThisSubCompAge FROM T_Employee group by FSubCompany, FAge.

SELECT FSubCompany, FAge, COUNT (*) AS CountOfThisSubCompAge FROM T_Employee group by FSubCompany, FAge order by FSubCompany.

COUNT (*) calculates the total number of employees in each group. SUM, AVG, MIN, and MAX can also be used in groups. For example, the following SQL statement can calculate the salaries of each company.
Total value: SELECT FSubCompany, SUM (FSalary) AS FSalarySUM FROM T_Employee group by FSubCompany.

1.3.1having clause

Sometimes you need to filter some groups. When you execute the following SQL statement in the database system, the database system will prompt a syntax error because the aggregate function cannot be used in the WHERE statement, the HAVING clause must be used instead. For example, SELECT FAge, COUNT (*) AS CountOfThisAge FROM T_Employee group by FAge having count (*)> 1.

HAVING statements can also use complex filtering conditions like WHERE statements. For example, if the following SQL statement is used to retrieve one or three age groups, you can use the following SQL: SELECT FAge, COUNT (*) AS CountOfThisAge FROM T_Employee
Group by FAge having count (*) = 1 or count (*) = 3.

MYSQL provides the LIMIT keyword to restrict returned result sets. LIMIT is placed at the last position of the SELECT statement. The syntax is "LIMIT first row number, maximum number of result sets to be returned ". For example, the following SQL statement returns a maximum of five records starting FROM the second row (the row number starts FROM 0) in descending ORDER of wages: SELECT * FROM T_Employee ORDER BY FSalary DESC LIMIT 2, 5.

The DISTINCT keyword is the simplest function used to suppress duplicate data, and all database systems support DISTINCT. DISTINCT is also very easy to use, as long as DISTINCT is added after SELECT. DISTINCT suppresses data duplication in the entire result set, rather than executing the following SQL statement for each column: SELECT DISTINCT FDepartment, FSubCompany FROM T_Employee. DISTINCT is used to suppress data duplication in the entire result set.

Union result set
Sometimes we need to combine two completely different query result sets, and there is no necessary link between the two query results, but we need to display them in a result set. In SQL, you can use the UNION operator to combine two or more query result sets into one result set.

You only need to use the UNION operator to connect the two query statements to combine the two query result sets into one result set,
The SQL statement is as follows:
SELECT FNumber, FName, FAge FROM T_Employee
UNION
SELECT FIdCardNumber, FName, FAge FROM T_T empEmpl oyee.

Principles of Union result set
UNION result sets do not have to be restricted by the relationship between multiple result sets that are joined. However, there are still two basic principles to use UNION: First, each result set must have the same number of columns; second, columns in each result set must be of Type compatibility.

Database Table data retrieval and How to Write SQL statements

Well, you have to filter out NULL or not null.
If you want to filter not null, use
SELECT * FROM table WHERE column 3 IS NOT NULL
 
Database Table data retrieval and How to Write SQL statements

Well, you have to filter out NULL or not null.
If you want to filter not null, use
SELECT * FROM table WHERE column 3 IS NOT NULL