SQL Application and Development: (vii) Data manipulation · Check (ii) Advanced type

2. Advanced Type

In advanced queries, it is an advanced application of the statement for Select, and how it can be used to query the database for qualifying data, such as between to limit the scope and condition of the query, and to save the resulting results to a table or view.

2.1BETWEEN Limit Query Scope

Using the keyword between can easily limit the range of data. Between is used to specify a range that allows users to search for upper boundary values, lower boundary values, and their values.

The basic syntax format for qualifying query conditions using the BETWEEN keyword is:

SELECT <select_list>

From <table_reference>

WHERE test_expression [NOT] between begin_expression and end_expression

2.1.1 For example, using the between keyword to limit the date , from the database "Sales relationship system" in the "Inbound Orders Information" table, the "Inbound date" from August 1, 2004 to September 1, 2005 for the storage order information.

SELECT Warehousing order number, vendor number, stock Number, item number, date of entry, operator number

From Inbound order information

WHERE storage date between ' 2004-8-1 ' and ' 2005-9-1 '

2.1.2 For example, using the between keyword to limit the text , from the database "Sales relationship system" in the "Warehousing Order Information" table, the city is located in Beijing to Nanjing supplier information.

SELECT vendor number, supplier name, Supplier City, contact name, contact number, remark

From supplier information

Where suppliers are located in the city of between ' Beijing ' and ' Nanjing '

2.2 Compound condition using where

The purpose of using the WHERE clause is to filter the data in the table to match the qualifying rows, where you can limit the scope of the query and improve efficiency. The conditional expression in the WHERE clause includes an arithmetic expression, a logical expression, and so on. This will synthesize the above, using compound conditions in the WHERE clause to implement some complex query operations.

2.2.1 Using compound conditions in the WHERE clause

You can use 3 kinds of Boolean connectors to form compound conditions, and these three Boolean connectors are: And, or, and not. And and or combine two conditions into a compound condition, and repeating these combinations can combine many conditions into a single compound condition; not applies to a single condition, meaning to negate.

and the result is returned only if all conditions are true. When both A and B are true, the statement "A and B" is true.

OR If one of the adjustments is established, the result is returned. When a or B is true, the statement "A or B" is true.

not means denying an expression, which is the result of a non-repudiation when the expression is established. That is, the statement "not A" is true when A is false.

For example, from the "Customer information" table in the "Jewelry Marketing System" database, customers who are not in Beijing and spend more than 2000 of their money, or Beijing customers.

SELECT consumer name, consumer spending amount, the city where the consumer is located, contact telephone, zip code

From Customer information

WHERE (consumer spending > and not consumer city = ' Beijing ') OR Consumer city = ' Beijing ')

2.2.2 Using multiple keywords

Not can be used in two different ways with multiple conditions, including in, between, like, and is null, at which point the meaning is exactly the same.

For example, in this example, the product information that meets all of the following conditions is displayed from the reseller database, including the product number, the vendor code, the product name, the product category, the unit price, and the commodity unit.

the condition is: not 7, 5, 4, 2 of any one of the supplier code; Product number starting at 10; Unit Price is not between 20~100; 12, 13, 14 any of the categories of goods; commodity units cannot be null.

SELECT Product number, supplier code, product name, commodity category, Unit price, commodity unit

From product information

WHERE Vendor code not in (' 7 ', ' 5 ', ' 4 ', ' 2 ')

and commodity number like ' 10% '

and unit price not between and 100

and commodity category not in (12,13,14)

and commodity unit is not NULL

2.3 Using constant values, distinct, and all

At the table's data level, the constant value is the same in each row that is worth a column. We usually have two methods to replace the constant value. The first method is to put a literal value into the SELECT clause as the encoded value, which works for only a few select statements. However, when there are a large number of SELECT statements, this approach is not flexible enough and it is not easy to change the code to meet the changing requirements.

The second method is to put the constant value into a separate table, which we refer to as the "constant table". This table is defined as a table with only one row. In such a table, each of the different constant values has a separate column. The names of these columns are usually unique and differ from the table names in any other table. After it is created, you can use it to write a SELECT statement with any other table. This method is used whenever there are more SELECT statements that use the same set of constant numbers.

In the database "dealer", the check out 2004 of the goods received, and list the name of the goods, price, quantity and date of purchase.

SELECT TOP10 t1. Commodity name, T1. Unit price, T2. Quantity of purchase, T2. Arrival time

From commodity information as T1, incoming information as T2

WHERE T2. Arrival time between ' 2004-1-1 ' and ' 2004-12-31 '

and t2. Product Code =T1. Product Code

ORDER by T2. Arrival time

When you specify a date, you cannot give only the year. In order to establish a year, this date must be set to the date of the year between January 1 and December 31.

The distinct and all keywords in the SELECT clause allow us to specify how to handle duplicate rows in the results. All returns all qualifying rows, which are the default usage. If all or distinct is not explicitly specified, it is set to all by default. Distinct only returns a unique row.

In the previous summary, it has been explained that the relevant usage of the two, here, is not described.

Let's take a look at the requirements: distinct or all can only be used once in the SELECT clause, is considered the first word in the select list, and cannot be appended with commas.

For example, in the "Jewelry Marketing System" database query consumers in the city and zip code, requires not to return duplicate rows.

SELECT DISTINCT Consumer City, postcode from customer information

2.4 Saving Select results

The result of the query is a table that can be processed like a processor. When the query statement runs, the query results knowledge is displayed on the screen, and the results of these queries disappear if the computer is turned off. To make it easier to see the results of the query, we typically save the query results to a new table or a view.

2.4.1 Saving results to a table

Saves the query results to a new table, that is, a new table is created from the query results of the SELECT statement. Once the query results are saved to a new table, the user can modify the data in the table by using a new line, updating the row, or deleting the row.

In SQL Server, the new row is added after the SELECT statement, and then an into clause is added before the FROM keyword, and the name of the new table is given after the INTO keyword. In addition, you do not need to modify other clauses of the query statement.

For example, from the Sales Information table in the database reseller, query the sales information for August 2005 and save the query results to a new table called Sales details 05_08, which requires that the table contain column "Item number", "Amount", "Salesperson Number", and "time to sell".

SELECT Product side number, amount, salesperson number, time of sale

Into sales details 05_08

From Sales information

WHERE sell time between ' 2005-8-1 ' and ' 2005-8-31 '

The above statement is in the database "reseller", creating a new table named "Sales Details 05_08". The following SELECT statement allows you to view the newly marked data:

SELECT * FROM Sales details 05_08

2.4.2 Saving results to a view

You can also save query results to a single view without dying to a table. Views are very similar to tables.

After you create a view, you can use it as you would with a base table, and you can use the view in the FROM clause of any SELECT statement.

For example, from the "Commodity information" table of the Database "reseller", the product information provided by a vendor named 7 is queried and the results are saved to a view named "Vendor 07_view", which requires the column "Product number", "Product name", and "unit price" in the view to be changed.

CREATE VIEW supplier 07_view

As

SELECT Product number, product name, Unit price

From product information

WHERE Supplier Code =7

Once the above statement is run, a new view named "Vendor 07_view" is added to the database "reseller". Use the SELECT statement to query the data in the view.

SELECT *

From supplier 07_view

ORDER by Unit Price

About the database check operation, not to be continued ...

SQL Application and Development: (vii) Data manipulation · Check (ii) Advanced type