SQL Server---T-SQL basic statement

T-SQL is a structured query language for Sql-server.

Basic data manipulation language.

Base statement

Create a table first the following is the list of the use of this table, the list of what is about to look at it ~ ~ Purely to learn the sentence, grammar ~ ~ So first create a table bar ~

1.SELECT Statements

Note: It is best to choose the desired column, do not because the easy to use "*" to select all the columns, because the data will be retrieved than you actually need more data, the actual use will also reduce the use of the application and network performance.

WHERE clause

Non-restrictive queries are useful when populating list boxes and combo boxes, and are useful in other situations where you want to provide a list of domains.

The order of operations in the WHERE clause is not , and, or is Jiangzi.

returns Ture when the left-hand expression matches any value on the right . Often used for subqueries.

Order by clause

Determines the output order of the data and selects the least expensive method to output the query results

DECS Descending ASC Ascending

Group BY clause

Used to aggregate information.

If you do not use the GROUP BY clause, the SUM function returns all the rows of the specified column. With GROUP BY, the SUM function returns each set of sums (PS: These drawings last line forgot to delete ~ no Use ~)

Note: when using the GROUP BY clause ,all columns in the select list must be either aggregate columns or columns contained in GROUP BY.

If an aggregate column is used in the select list, then the select list must contain only the aggregate columns, otherwise a Group by clause must be available.

A little dizzy, okay?

That is, select aggregation, aggregation from XX where xx columns are aggregate columns can be used without Group by

But the select ID, aggregation from XX where xx GROUP by because not all are aggregate columns, so there is a Group by

The following sentence is in the same vein.

A. Aggregation functions

When an aggregate function is used independently, the aggregate function is simply used to aggregate the entire result set just like the third statement in the diagram. However, it can be very powerful when used with group by.

B.avg Average

c.max/min Maximum/Minimum value

When the maximum and minimum values are evaluated, the AS keyword can be used in order to distinguish the columns. Although the as key column is optional, one for program readability, and two for writing code that conforms to the ANSI/ISO standard.

D.count (Expression/*)

Calculate the number of rows returned in a query

when the first count (*) is divided into 2 cases

When the second count (expression)

Write this time encounter a bit of a problem, about the primary key constraints, the definition of a unique constraint use, come back to write a good study.

Well, I can see that I've written two different expressions, since each row has columns, why does it come out differently?

This is the place to pay attention.

Note: Except for the count (*) function, all aggregate functions ignore null values.

When the Count function is used with the GROUP by function

E. Setting conditions for grouping using the HAVING clause

If you want to put the query criteria into a group, you can use the having

NOTE: The HAVING clause is only used for query statements with a GROUP BY clause. The WHERE clause applies to a row, and the HAVING clause is applied to the aggregated value of the grouping.

F. Exporting XML using a FOR XML statement

Here will be a special introduction of the link, in this do not do too much introduction.

G. Using the OPTION clause to take advantage of hints

Here will be a special introduction of the link, in this do not do too much introduction.

H.distinct and all predicates

These two are related to the processing of duplicate data.

Seconds to understand ~

This allows you to filter out the data that is not duplicated, which in practice causes a significant decrease in the number of returns, and the following statement executes faster than the statement above . There will be a link later to discuss why it is quicker.

In aggregate functions you can also use DISTINCT, syntax: Select COUNT (distinct ID) from table name.

The meaning of all is exactly the opposite of distinct, so what he says is the case except distinct. Now you need to understand, will put a special introduction of the link, in this do not do too much introduction.

2. Adding data using INSERT statements

The INTO keyword can be added without adding it to enhance the readability of the statement. Casual ~

Note: Columns cannot be set to a null value if there are three cases in which the value is interpolated

1. The column is defined as the default value.

2. The column is defined to accept some form of system generated value. For example, the identity value

3. The value of the column has been supplied when inserting the data.

(The stored Procedure sp_help feature gives information about any database object, user-defined data type, or SQL Server data type.) View Course Table Properties)

INSERT INTO ... SELECT statement

If the block of data to be inserted is obtained from the following:

• Another table in the database
• A completely different database on the same server
• heterogeneous queries or other data from another SQL Server
• Same table

 from where 1 ;

3. Changing data with the UPDATE statement

Set where column name = ' old value ' or the random column name and random column name value of the column that needs to be changed

Note: Some columns are not updated or are not recommended, such as timestamp and the primary key should not be updated.

3.DELETE Statements

SQL Server---T-SQL basic statement