Application of SELECT statement

Starting with this section, we will use a large number of examples to describe the application of the SELECT statement. Start with the simplest and most commonly used list queries first.


10.2.1 Select column


(1) uses the SELECT clause to specify the columns required for the query, separated by commas between multiple columns








(2) You can use the symbol "*" To select all columns of a table








(3) Adding columns to query results





10.2.2 Select Row


(1) using the WHERE clause


when querying a database, users often do not need to know all the information, but only part of the information that satisfies certain conditions. In this case, you need to add a condition in the SELECT statement to select the data row, where the WHERE clause is used. The condition in the WHERE clause consists of an expression and a logical join word and, or, not.





Example 10-4: Query the employee's name between 2000 Yuan and 3000 yuan.


Use Pangu


Select E_name


from Employee


where e_wage between and 3000

The results of
operation are as follows:


E_name


--------------------


Wang


Wu Tong


(2 row (s) affected)





(2) using the DICTINCT keyword


users will have duplicate results when querying the database, you need to use the DISTINCT keyword to eliminate the duplicates.


Example 10-5: Lists the department number of the employee whose salary is greater than 7000.


Use Pangu


SELECT DISTINCT dept_id


from Employee


where e_wage > 7000

The results of
operation are as follows:


dept_id


-------


1001


1005


(2 row (s) affected





(3) using the IN keyword


when querying with a WHERE clause, if several conditions in the conditional expression appear to be the same, it makes the expression lengthy and inconvenient for the user, and can be simplified using the in keyword.


Example 10-6: Queries the names of employees who work in departments numbered ' 1001 ' and ' 1002 '.


Use Pangu


Select E_name


from Employee


where dept_id in (' 1001 ', ' 1002 ')

The results of
operation are as follows:


E_name


--------------------


John


Dick


......


(4) using wildcard characters


You can use predicate like in the WHERE clause to check for string matching, which will use a lot of the wildcard characters we've covered in the Transact-SQL language Foundation.








if the data that the user is looking for contains wildcard characters, such as "Sql_mail", you need to use escape characters to distinguish between wildcards and actual characters. The format is as follows;


like ' character match string ' escape ' escape word '











10.2.3 to sort query results


(1) using the order clause


When a user wants to sort the query results, it is necessary to include an ORDER BY clause in the SELECT statement. You can use one or more sorting requirements in the ORDER BY clause, with a precedence sequence of left-to-right.


Example 10-9: Queries the employee's name at work Level ' 2 ', and the results are sorted by payroll.








(2) Select the first few rows of data


uses top n or Top n PERCENT in a SELECT statement to select the first n rows or N of the data in the query results. This statement is often used with the order clause.


Example 10-11: Check the names and salaries of the three employees who have the highest salary.


Use Pangu


Select Top 3 e_name, E_wage


from Employee


ORDER BY e_wage Desc

The results of
operation are as follows:


e_name e_wage


-------------------- ---------------------


John 8000.0000


Chef 7500.0000


Zhang Long 7000.0000


(3 row (s) affected)





10.2.4 Query Result grouping


(1) using the group clause


when users want to group query results, they need to include the GROUP BY clause in the SELECT statement.


Example 10-12: Query the employee's name at work Level ' 2 ', and the query results are grouped by department;








(2) using with {CUBE | ROLLUP} option


use these two options to return data rows that are grouped by group. Unlike the cube, the ROLLUP option returns only the statistics for the highest level of grouped columns (that is, the first grouping column). The reader can analyze the differences from the following examples.


Example 10-13: Query the company number, the product number ordered by the company, and the amount to be paid, and the results are grouped by company number and product number (statistical functions are used in this example, see the next section for their use).

















(3) using the HAVING clause

The
HAVING clause is used to select a special group that compares some of the group's properties to a constant value, and if a group satisfies the logical expression in the HAVING clause, it can be included in the query result.








Note:


in the SELECT statement, where, GROUP by, having clauses, and statistical functions are executed in the following order: Where clauses are removed from the data source that do not conform to the


the data rows of its search criteria; The GROUP BY clause collects rows of data into each group; Statistical functions calculate statistical values for each group; a HAVING clause removes rows of data that do not conform to a common group of search criteria.





10.2.5 using statistical functions


uses statistical functions in a SELECT statement to get a lot of useful information.