SQL statements and the use of keywords

First, 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 above statement, you can see that there are 5 clauses in the SELECT query statement where Select\from is a required statement--select clause: Used to specify the columns returned by the query--all| DISTINCT: Used to specify how the same row is handled for the query result set, all-for all DISTINCT: Keep a record--select_list: used to display the target column to display, all can be replaced by *, not all column fields, split--into new Table: Used to create a new temporary table--from table_source: Specifies the data source of the query--where serch_conditaion: The search criteria used to qualify the returned rows--group by group_by_expression] : Used to specify the grouping criteria for finding results--having serch_conditaion: A search condition used to specify composition or aggregation--order by order_expression[asc| DESC]: Used to specify how the result set is sorted

Second, PIVOT row to column

Note: PIVOT, Unpivot is the syntax for SQL Server 2005, use the database compatibility level that you want to modify
In the database properties, options, compatibility level, change to 90

Pivot is used to rotate column values to column names (row to column), and SQL Server 2000 can be implemented with an aggregate function with a case statement

The general syntax for pivot is: Pivot (aggregate function (column) for column in (...)) As P

Full syntax:

Table_source--Data source

PIVOT (

aggregate function (Value_column)--aggregate function values for statistical conversion row values

For Pivot_column--row and column names that need to be converted

In (<column_list>)--row worth combining

)

Typical examples:

1. Establish table ifobject_id (' TB ') isnotnulldroptabletbgocreate table TB (name varchar (10), course varchar (10), fractional int) insert INTO TB Values (' Zhang San ', ' language ', ' a ') insert into TB values (' Zhang San ', ' math ', ') insert into TB values (' Zhang San ', ' physical ', ') insertintotbvalues (' John Doe ', ' Language ', * * * * INSERT INTO TB values (' John Doe ', ' math ', ' + ') insert into TB values (' John Doe ', ' physical ', 94) goselect * FROM Tbgo name       course       score------ -------------------------Zhang San       language        74 Zhang three       mathematics        83 Zhang three       physics        93 John Doe       language        74 John Doe       Mathematics        84 John Doe       Physics        94 2, using SQL Server 2000 static sql--cselect name, Max (case course when ' language ' then fraction else 0 end) language, Max (case course when ' math ' t Hen score Else 0 end) Math, Max (case course when ' physical ' then fraction else 0 end) physical from Tbgroup by name       Chinese        mathematical        Physics------------ -------------------------------John Doe        94 three-in-          one          93 3. Using SQL Server 2005 Static SQLSELECT*FROMTB pivot (max (score) for course in (language, mathematics, physics)) a

Three

Unpivot is used to convert columns to column values (that is, column change), which can be implemented with union in SQL Server 2000

Full syntax:

Table_source--Data source converted table structure query For example: Select Name, course, score from TB1

UNPIVOT (

Value_column--Column value (the value of the column name itself, such as: Language column value 83) For example: Score

For Pivot_column--column name (the name of the collection column after it has been transferred to a row for example: course)

In (<column_list>)--Set parameters (for example: [Language],[Mathematics],[Physics])

)

Example:

CREATE table TB (name varchar (10), language int, math int, physical int) insert into TB values (' Zhang San ', 74,83,93) inserts into TB values (' John Doe ', 74,84,94) goselect  *from tbgo name       Chinese        mathematical        Physics-------------------------------------------Zhang San       74          93 John Doe          94 2, use SQL Server 2000 static SQL--SQL Server 2000 static SQL. Select*from (select name, course = ' language ', score = language from TB union ALL select Name, course = ' math ', score = Math from TB UNION ALL select Name, course = ' physical ', fraction = physical fro M TB) Torder b y name, case course when ' language ' then 1 when ' math ' then 2 when ' physical ' then 3 end name       course score------------------------- John Doe       Chinese 74 John Doe       Mathematics 84 John Doe       Physics 94 three       languages 74 sheets Three       Mathematics 83 sheets Three       physical 932, using SQL Server 2005 static Sql--sql Server 2005 Dynamic Sqlselect name, course, score from TB Unpivot (score for course in ([Language],[Math],[physics]) t

Four merge 用于2张表更新的常用关键字

Basic syntax:

MERGE  Table         --target table A to match
Using  SCOURTB       --The source data of the query or the metadata table name b
On  conditaion        
whenmatched then     --匹配成功  update setA. Field =x  --General match success is to perform the update
whennot matched insert(A字段) values (对应的值);  --执行插入 向目标表插入元数据的新数据
WHEN NOT MATCHED BY SOURCE THEN DELETE; -- 目标表有，源表没有，目标表该数据删除.

示例：

CREATE TABLE employee (empid integer,fname nvarchar, lname nvarchar) insert into employeeselect 2021110, ' small ', ' Zhang '  Insert INTO Employeeselect 2021110, ' small ', ' Lee '  CREATE TABLE Test (ID integer,num integer) insert into Testselect 2021110,2 Insert INTO Testselect 2,2  expected Result statement: SELECT * from  employee2021110, ' big ', ' Zhang ' 2021110, ' big ', ' Lee ' 2, ' big ', ' big '

SQL Statement implementation:

Merge into  employee a   using  Test b   on   b.id = a.empid when    matched then     update set A.fname= ' Big ' when not   matched then   Insert values (ID, ' big ', ' big ');  End   EXEC test_mergeselect * FROM employee/*empid       fname                lname--------------------------------------- ------------2021110     Big                    Zhang 2021110                    2 large                    (3 rows affected)

V. SQL Stuff Usage

1. function

Deletes a character of the specified length and inserts another set of characters at the specified starting point.

2. Grammar

STUFF (character_expression, start, length, character_expression)

3. Example

The following example removes the three characters from the 2nd position (character B) in the first string abcdef, and then inserts a second string at the beginning of the deletion, creating and returning a string

SELECT STUFF (' abcdef ', 2, 3, ' IJKLMN ')
GO

Here is the result set

Aijklmnef

4. Parameters
character_expression

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

Start

An integer value that specifies the start position of the delete and insert. 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 a bigint type.

Length

An integer that specifies the number of characters to delete. If length is longer than the first character_expression, it is removed up to the last character in the last character_expression. Length can be a 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 generated.

Vi. SQL FOR XML Path usage

A. For XML PATH Brief introduction

So let's start with the for XML PATH, assuming that there is now a hobby table (hobby) to store interests, the table structure is as follows:

Next, let's look at the query result statement that applies the for XML path as follows:

SELECT * from @hobby for XML PATH

Results:

<row>
</row>
<row>
</row>
<row>
</row>

This shows that the FOR XML PATH can output query results according to the row into XML all kinds!

So, how do you change the name of the XML row node? The code is as follows:

SELECT * from @hobby for XML PATH (' Myhobby ')

The results must have been imagined, right? That's right. The original line node <row> became our custom name <myhobby> in parentheses () after the path, and the result is as follows:

<MyHobby>
</MyHobby>
<MyHobby>
</MyHobby>
<MyHobby>
</MyHobby>

This time the careful friend must ask then how does the column node change? Do you remember the keyword as that lists the aliases? Yes, that's it! The code is as follows:

SELECT Hobbyid as ' MyCode ', hname as ' MyName ' from @hobby for XML PATH (' Myhobby ')

So at this point the node name of our column will also be programmed with our custom name <MyCode> and <MyName> results as follows:

<MyHobby>
<MyCode>1</MyCode>
<MyName> Climbing </MyName>
</MyHobby>
<MyHobby>
<MyCode>2</MyCode>
<MyName> Swimming </MyName>
</MyHobby>
<MyHobby>
<MyCode>3</MyCode>
<MyName> Food </MyName>
</MyHobby>

Oh! Now that we can customize the nodes and columns of the row, can we build the output that we like? Or look at the code:

SELECT ' [' +hname+ '] ' from @hobby for XML PATH (')

Yes, we can also define the output format of a String type field by using the symbol + number. The results are as follows:

Climbing Swimming Food

So how do other types of columns customize? That's OK, let's convert them to string type! For example:

SELECT ' {' +str (Hobbyid) + '} ', ' [' +hname+ '] ' from @hobby for XML PATH (')

Good for XML path is basically introduced here, more about the FOR XML knowledge please consult the Help document!

Let's take a look at an application scenario for XML path! So let's start ...

Two. One application scenario with the for XML path application

First of all! We are adding a student table, the column is (Stuid,sname,hobby), Stuid on behalf of the student number, SName on behalf of the student name, hobby the student's hobby! So now the table structure is as follows:

At this point, our request is to query the student table, showing the result set of all students ' hobbies, the code is as follows:

SELECT B.sname,left (Stulist,len (stulist)-1) as Hobby from (
SELECT SName,
(SELECT hobby+ ', ' from student
WHERE Sname=a.sname
For XML PATH (")) as Stulist
From student A
GROUP by SName
) B

The results are as follows:

Analysis: OK, then let's analyze it, first look at this sentence:

SELECT hobby+ ', ' from student
WHERE Sname=a.sname
For XML PATH (")

This sentence is through the for XML PATH to a name like Zhang San's hobby, shown in the format of: "Hobby 1, Hobby 2, Hobby 3," the format!

Then look at:

SELECT B.sname,left (Stulist,len (stulist)-1) as Hobby from (
SELECT SName,
(SELECT hobby+ ', ' from student
WHERE Sname=a.sname
For XML PATH (")) as Stulist
From student A
GROUP by SName
) B

The rest of the code begins by grouping tables, executing the for XML PATH format, when the structure that is queried when the outermost select is not executed is:

You can see that the data in the Stulist column will have a comma, then the statement with the outer layer: SELECT b.sname,left (Stulist,len (stulist)-1) as hobby is to remove the comma, and give a meaningful listing!

You can also write this:

Select NAME,
STUFF ((select ', ' +fv+ ' from #TEMP tb1 where tb1.name=tb.name for XML Path (")), ("), ") as FV--intercepts the result set of the query, replacing the first character
From #TEMP as TB GROUP by Tb.name

Third, on the master-slave table associated with the largest data from the table record a problem

Select a.*,b.* from DBO.SQ_AJBL as a
Left join Sq_ajblworkflow as B on a.gid=b.gpid
where B.dcreatedate in (select Max (dcreatedate) from Sq_ajblworkflow GROUP by Gpid)

SQL statements and the use of keywords