SQL Server database Training (SQL)----SELECT statement constituent elements

1. Select statement Constituent element 1.1 SELECT *

Avoid using SELECT *

When you want to list all columns in the SELECT clause, it is a convenient way to refer to ' * ' Using a dynamic SQL column. Unfortunately, this is a very inefficient approach. In fact, during parsing, SQL converts ' * ' to all column names, which is done by querying the data dictionary, which means more time is spent.

1.2 The order of execution of elements in a Select statement

To describe the logical query processing and the various select query clauses, we use the following examples to introduce you.

Example: Query the Canadian market for all users who are bound to more than 1 phones, and sort by user ID.

Select Acctid,count (*) numphones

From Ifd_phone_list

where nation_code= ' CA '

GROUP BY Acctid

Having Count (*) >1

ORDER BY Acctid

The execution sequence is as follows:

1. From

2. WHERE

3. Gtoup by

4. Having

5. SELECT

6. ORDER by

This query statement will complete the following functions

1. Querying data rows from the Ifd_phone_list table

2. Filter the data in the table and keep only the records of the market as CA

3. Group data by User ID

4. Filter the data after grouping and keep only the users who are bound to multiple phones

5. Return each group user ID and phone number

6. Sort the output results by user ID

So if SQL changes to

Select Acctid,count (*) numphones

From Ifd_phone_list

where nation_code= ' CA ' and Numphones >1

GROUP BY Acctid

Having Count (*) >1

ORDER BY Acctid

Then the SQL will error because the field does not have an alias, and the operation is executed after the WHERE statement executes.

1.3 DISTINCT

The Select command can detect all the data we want, but there may be a lot of duplicate values in the data.

When you need to query for different values in a table, and the number of occurrences of each value is not important, then you can use distinct

How to use

Select DISTINCT "field name" from "Table name"

1.4 GROUP by

The group by stage can combine the rows returned by the preceding logical query processing stage by group.

Example: Querying a user's nearly June bill

Select Cycle_start as Cyclestart,

Cycle_end as Cycleend,

SUM (toll_charge) as Tollcharge,

SUM (credit) as credits,

SUM (Debit) as Debit,

Min (due_date) as DueDate

From Cycle_call_fee_history

where Acctid = 39129 and cycle_start>= ' 2010-1-1 ' and cycle_end<= ' 2010-6-1 '

Group BY Cycle_start, Cycle_end

Because the aggregate function returns only one value for each group, an element can only be entered as an aggregate function (Count,sum,avg,min,max) if it is not present in the Group by table. However, all aggregate functions ignore null values, with only one exception, COUNT (*). For example, a column with a value of 30,10,null,10.count (*) returns 4, and COUNT (column name) returns 3.

1.5 ORDER by

The data in a table is not necessarily sort-stored. So when you need to sort

The order BY statement is used to sort the result set based on the specified column.

The order BY statement sorts records by default in ascending order.

If you want to sort records in descending order, you can use the DESC keyword.

1.6 is NULL

When you want to query for a field that is null for a record, use is null instead of! = nul

1.7 Not

The NOT operator is used to negate the Boolean value of the search condition. The use of the NOT operator, its application, and its differences from the <> operator are discussed here.

Unlike other operators, the NOT operator that represents negation cannot be applied alone, and is often used in conjunction with other operators. For example, the not in operator is actually a union use of the in operator and the NOT operator

Example: using the NOT operator to implement a query

SELECT Tname, Dname,age, tsex from TEACHER WHERE not dname= ' computer ' ORDER by dname

The NOT operator negates the condition immediately followed, not dname= ' computer ' is actually equivalent to dname<> ' computer ' or dname!= ' computer '.

It is important to emphasize that null values are reversed and the result is still null.

Example of inverse of null value

As in the following code:

SELECT * from TEACHER WHERE isn't Sal >1500 Oredr by Sal

The above example code is actually querying all the teachers with a salary of not more than 1500, and from the results, it is found that the teacher record with a null salary is not included in the result table. Because when Sal is null, the execution result of "Sal >1500" is also null, and the result of not NULL is still null, and the query condition is not satisfied.

1.8 Between and

Use between ... The AND operator can select data that is arranged between two values. The data can be numbers, text, or dates. Using the equivalent of >= and <= in SQL

1.9 Top

Example: Check out the top 10 off Records

Select Top Ten Account_id,plan_id,status

From Account_extension

ORDER BY account_id

Note that the order of execution, the query data will be sorted before the first 10 rows of records

In traditional SQL statements prior to SQL Server 2005, the top statement was not supported for local variables. You can use set RowCount at this point, but in SQL Server 2005/2008, top usually executes faster, so you should replace set RowCount with the top keyword.

Declare @percentage Floatset

@percentage =1

Select Top (@percentage) percent PName from [demo_top] ORDER by PName

Example: Using top and percent pagination

Find out the top 1% data

Select Top 1 percent OrderID from Orders order by OrderID

Find out the top 2% data

Select Top 2 percent * from Orders where OrderID not in

(select Top 1 percent OrderID from Orders order by OrderID) Order by OrderID

1.10 Table Partition

The rationality of database structure and index affects the performance of database to a great extent, but with the increase of database information load, the performance of database is also greatly affected. Maybe our database has high performance at first, but with the rapid growth of data storage-such as Call log data-the performance of the data is also greatly affected, one obvious result is that the query response is very slow. At this time, in addition to you can optimize the index and query, you can also create a partitioned table (table Partition), in some cases to improve the performance of the database, in SQL Server 2008 provides a wizard form to create a partitioned table.

Example: Execute the following statement after partitioning the Fact_sales table for date_id in months.

SELECT date_id, SUM (quantity*unit_price) as Total_price

From Fact_sales

WHERE date_id between 20080801 and 20080831

When querying the date_id, data is extracted from the specified partition table to improve query efficiency

1.11 Over

Determines the partitioning and sorting of rowsets before the associated window function is applied

PARTITION by Parameters

Divides the result set into multiple partitions. The window functions are applied to each partition separately, and the calculation is restarted for each partition.

Cases:

Select Orderid,custid,val,

Sum (val) over () as Totalvalue,

Sum (val) over (partition by CustID) as Custtotalval

From Ordervalues

Query Result:

OrderID	CustID	Val	Totalvalue	Custtotalval
10001	1	800.50	1232323.23	4343.00
10002	1	340.23	1232323.23	4343.00
10003	1	123.31	1232323.23	4343.00
10004	2	343.34	1232323.23	1343.00
10005	2	123.32	1232323.23	1343.00

Use PARTITIONby in overand sort by specific fields

Example 2:

Select Orderid,custid,val,

Row_number () over (partition by CustID

Order by Val) as RowNum

From Ordervalues

ORDER BY Custid,val

Query Result:

OrderID	CustID	Val	RowNum
10001	1	100.50	1
10002	1	340.23	2
10003	1	444.23	3
10004	2	123.32	1
10005	2	234.23	2
10006	2	435.34	3

SQL Server database Training (SQL)----SELECT statement constituent elements