SQL statement and keyword usage, SQL keyword usage

SQL statement and keyword usage, SQL keyword usage

I. SELECT

Select [ALL | DISTINCT] select_list [into new table] FROM table_source [where serch_conditaion] [group by group_by_expression] [Having serch_conditaion] [Order by order_expression [ASC | DESC] -- FROM the preceding the statement shows that the SELECT query statement has five clauses. SELECT \ FROM is a required statement -- SELECT clause: used to specify the columns returned by the query -- ALL | DISTINCT: used to specify the processing method of the query result set for the same row. ALL-for all distinct: keep a record -- select_list: used to display target columns to be displayed. All columns can be replaced by *. Not all columns and fields are used. Split -- into new table: used to create a new temporary table -- FROM table_source: specify the data source to be queried -- where serch_conditaion: used to specify the Search Condition for the returned row -- group by group_by_expression]: used to specify the grouping condition for the query result -- Having serch_conditaion: used to specify the search criteria for composition or aggregation -- Order by order_expression [ASC | DESC]: used to specify the sorting method of the result set

Ii. Shard row-to-column Conversion

Note: Limit and unlimit are syntaxes of SQL Server 2005. You need to modify the database compatibility level.
Change the database attribute> Option> Compatibility Level to 90.

Rotate is used to rotate column values into column names (that is, row-to-column conversion). in SQL Server 2000, Aggregate functions can be used in combination with CASE statements.

The general syntax of aggregate is: aggregate (aggregate function (column) FOR column in (...) ) AS P

Complete Syntax:

Table_source -- Data Source

Partition (

Aggregate Function (value_column) -- count the aggregate function value of the converted row Value

FOR each t_column -- Name of the row and column to be converted

IN (<column_list>) -- combine row values

)

Typical instances:

1. Create a table ifobject_id ('tb') isnotnulldroptabletbgocreate table tb (Name: varchar (10), course varchar (10), score: int) insert into tb values ('zhang san ', 'China', 74) insert into tb values ('zhang san', 'mat', 83) insert into tb values ('zhang san', 'Physical ', 93) insertintotbvalues ('Li si', 'China', 74) insert into tb values ('Li si', 'mat', 84) insert into tb values ('Li si', 'Physical ', 94) goselect * from tbgo name course score ---------- ----------- Zhang San Chinese 74 Zhang San mathematics 83 Zhang San physical 93 Li Si Chinese 74 Li Si mathematics 84 Li Si physical 94 2. Use SQL Server 2000 static SQL -- cselect name, max (case course when 'chine' then score else 0 end) language, max (case course when 'mate' then score else 0 end) mathematics, max (case course when 'physical 'then score else 0 end) physical from tbgroup by name Chinese mathematics physics ---------- ----------- --------- ----------- Li Si 74 84 94 Zhang 3 74 83 93 3. Use SQL Server 2005 static SQLselect * fromtb limit (max (score) for course in (Chinese, mathematics, physics)

III,

Uncombine is used to convert a list into a column value (that is, a column to a row). in SQL Server 2000, UNION can be used to implement this function.

Complete Syntax:

Table_source -- table structure query after data source conversion, for example: select name, course, score from tb1

Unregister (

Value_column -- column value (the column name value, for example, Chinese column value 83) for example: Score

FOR each t_column -- column name (the name after the set column is converted to a row, FOR example, course)

IN (<column_list>) -- set parameters (for example, [Chinese], [mathematics], and [physics])

)

Example:

Create table tb (name varchar (10), Chinese int, mathematical int, physical int) insert into tb values ('zhang san', 93) insert into tb values ('Lee 4', 94) goselect * from tbgo name Chinese mathematics physics ---------- ----------------- ------------- Zhang San 74 83 93 Li Si 74 84 94 2. Use SQL Server 2000 static SQL--SQL SERVER 2000 static SQL. Select * from (select name, course = 'China', score = Chinese from tb union all select name, course = 'mat', score = mathematics from tb union all select name, course = 'physical ', score = physical from tb) torder B y name, case course when 'chine' then 1 when' math 'then 2 when' then 3 end name course score ---------- ---- ----------- Li Si language 74 Li Si mathematics 84 Li Si physics 94 Zhang San Language 74 Zhang San mathematics 83 Zhang San physical 932, using SQL Server 2005 static SQL--SQL SERVER 2005 dynamic SQLselect name, course, score from tb Untitled (score for course in ([Chinese], [mathematics], [physics]) t

 

IV, Common keywords used by merge for two table updates

Basic Syntax:

MERGE table -- target table a to be matched
Using scourtb-name of the queried source data or metadata Table B
On conditaion -- condition for matching metadata with the target table
when matched Then -- matching successfulupdate setA. Field = X -- generally, if the matching succeeds, update is executed.
when not matched Then -- the matching fails.insert (Field) values (Corresponding value); -- Execute insert to insert metadata to the target table.
WHEN NOT MATCHED BY SOURCE THENDELETE; -- the target table exists and the source table does not exist. The target table deletes the data.

Example:

Create table employee (empid integer, fname nvarchar (20), lname nvarchar (20) insert into employeeselect 2021110, 'Min', 'zhang' insert into employeeselect 2021110, 'Min ', 'lil' create table test (id integer, num integer) insert into testselect 2021110,2 insert into testselect 2021110 expected result statement: select * from employee2021110, 'day', 'zhang, 'da', 'lil' 2, 'da', 'da'

SQL statement implementation:

Merge into employee a using test B on B. id =. empid when matched then update set. fname = 'day' when not matched then insert values (id, 'day', 'day '); end exec test_mergeselect * from employee/* empid fname lname ------------- -------------------- 2021110 large sheets 2021110 Large Li 2 large (3 rows affected)

V. SQL STUFF usage

1. Role

Delete characters of the specified length and insert a group of characters at the specified start point.

2. Syntax

STUFF (character_expression, start, length, character_expression)

3. Example

The following example deletes the three characters starting from the first position (character B) in abcdef, and inserts the second string at the start of the deletion to create and return a string.

Select stuff ('abcdef', 2, 3, 'ijklmn ')
GO

The following is the result set.

Aijklmnef

4. Parameters
Character_expression

A character data expression. Character_expression can be a constant, variable, character column, or binary data column.

Start

An integer that specifies the start position of deletion and insertion. If start or length is negative, an empty string is returned. If start is longer than the first character_expression, an empty string is returned. Start can be of the bigint type.

Length

An integer that specifies the number of characters to delete. If the length is longer than the first character_expression, the last character in the last character_expression can be deleted at most. Length can be of the bigint type.

5. Return type
If character_expression is a supported character data type, character data is returned. If character_expression is a supported binary data type, binary data is returned.

6. Remarks
If the result value is greater than the maximum value supported by the return type, an error is returned.

Vi. SQL for xml path usage

 1. FOR XML PATH

 

First, let's introduce the for xml path. Suppose there is a hobby table used to store hobbies. The table structure is as follows:

 

Next, let's look at the Query Result Statement using the for xml path:

 

SELECT * FROM @ holobby FOR XML PATH

 

Result:

 

<Row>
<HobbyID> 1 <HName> mountain creation </Row>
<Row>
<HobbyID> 2 <HName> swimming </Row>
<Row>
<HobbyID> 3 <HName> food </Row>

 

From this we can see that for xml path can output query results into various XML types based on rows!

 

How can I change the name of an XML row node? The Code is as follows:

 

SELECT * FROM @ holobby for xml path ('myhobby ')

 

 

 

The results can be imagined, right? The original row node <row> is changed to the custom name <myholobby> in the brackets () behind PATH. The result is as follows:

 

<Myholobby>
<HobbyID> 1 <HName> mountain creation </Myholobby>
<Myholobby>
<HobbyID> 2 <HName> swimming </Myholobby>
<Myholobby>
<HobbyID> 3 <HName> food </Myholobby>

 

At this time, careful friends will certainly ask how the column nodes are changed? Do you still remember the AS keyword for the column alias? By the way, use it! The Code is as follows:

 

SELECT hobbyID as 'mycode', hName as 'myname' FROM @ holobby for xml path ('myhobby ')

 

 

At this time, the node names in our column will also program our custom names <MyCode> and <MyName>. The results are as follows:

<Myholobby>
<MyCode> 1 </MyCode>
<MyName> mountain creation </MyName>
</Myholobby>
<Myholobby>
<MyCode> 2 </MyCode>
<MyName> swimming </MyName>
</Myholobby>
<Myholobby>
<MyCode> 3 </MyCode>
<MyName> food </MyName>
</Myholobby>

Oh! Since we can customize the row and column nodes, can we build our favorite output methods? Check the Code:

SELECT '[' + hName + ']' FROM @ holobby for xml path ('')

 

Yes, we can also define the output format of string fields by symbol +. The result is as follows:

 

[Hiking] [Swimming] [food]

 

How can I customize columns of other types? It doesn't matter. Just convert them to the string type! For example:

 

SELECT '{' + STR (hobbyID) + '}', '[' + hName + ']' FROM @ holobby for xml path ('')

 

Okay. Here is a basic introduction to the for xml path,