Common SQL data

TRANSACT-SQL can be referred to "Transact-SQL Reference" (TSQL.HLP) (Shredders "T-SQL")

suggestion:

It is best to use uppercase when writing SQL Script.

Note:

The syntax format here is only a common format, not a SQL standard format, and see "T-SQL" in the standard format.

(No practical significance in the example in the example)

select

SELECT

SELECT can select the specified data column

Such as:

SELECT *

SELECT [Name] from syscolumns

When there is a system reserved word in SQL, "[]" is caused, or there is special characters in SQL, "[]" is caused,

Such as:

SELECT [Object Name] from Objects

At the time of using an alias, you should also pay attention to the above principles, and the alias can be used with the following two methods:

COLUMN_NAME AS Alias

Column_name alias

The middle AS can be omitted

You can use the condition selection syntax in SELECT to see "Conditions" below

Such as:

SELECT [Name], Xtype, Case When Xtype = 'u' TEN 'User Table' Else Case When Xtype = 'S' THEN 'System Table' End End As Type from Sysobjects

Return to the table:

Name

Xtype

Types of

Syscolumns

S

System table

TableDefine

U

user table

Separate two queries synthesized separate returns:

UIY keyword

Such as SELECT A, B from Table1

Unoin

SELECT C, D from Table2

Description:

When using union, if there is no ALL parameter, the same record will be approved by default.

Such as:

Table1

Table2

Id

TF1

Value1

Id

TF2

Value2

1

A

10

5

A

10

5

B

20

6

Di

twenty one

2

A

30

3

C

31

3

C

40

1

B

41

SELECT TF1, VALUE1 from Table1

Union

SELECT TF2, Value2 from Table2

Return to the table:

TF1

Value1

A

10

B

20

A

30

C

40

Di

twenty one

C

31

B

41

It can be seen that a record of "TF2 = A, Value2 = 10"

But with the following query

SELECT TF1, VALUE1 from Table1

Union all

SELECT TF2, Value2 from Table2

Return to the table:

TF1

Value1

A

10

B

20

A

30

C

40

A

10

Di

twenty one

C

31

B

41

Just this query will return all records

This problem may appear on the report statistics, such as an employee has done the same product and data in different dates, but will be less a record when using non-ALL mode.

Common with INTO

SELECT .... INTO B from A

You can store the specified data of a table in B table

Application Type:

Backup data table:

SELECT * INTO TABLE1_BAK from Table1

Create a new table

SELECT * INTO new_TABLE1 from Table1 Where 1 <> 1

SELECT TOP 0 * INTO new_TABLE1 from Table1

Save query results

Select Field1, Field2 INTO Result from Table1 Where Id> 1000 Create a new table and add an automatic serial number in a new table

One table has some tables that require an automatic number to distinguish between various lines.

Select Id, 1, 1) AS Autoid, * INTO new_TABLE1 from Table1

Where Identity function description:

format:

Identity ( [Seed, Increment])

Parameter Description:

DataType: Data type, depending on the number of types, usually set the INT type, specifically refer to the limit parameters of SQL

SEED: Start value, the beginning of the base, default is 1

Increment: increment, step size, interval between data, default is 1

The above SQL is said, the automatic number starts from 1 and add 1 per line.

The returned table is:

AutoID

Field1

Field2

1

Hello

Joy

2

Hello

Tom

3

Hi

Lily

4

Hello

Lily

Note:

Identity can also set up when you create a table

format:

Identity ([Seed, INCREMENT])

Such as:

Create a table

Create Table Table1

Autoid Int Idness (1, 1), or Autoid Int Idnessity

Field1 nvarchar (30),

FIELD2 NVARCHAR (30)

)

Modification table

ALTER TABLE TABLE1 Add Autoid Int Idness (1, 1)

Pay attention to the setting of Identity_Insert attribute when performing data insertion

If set identity_insert

is ON, implicit insertion

Such as:

Set Identity_INSERT TABLE1 ON

INSERT INTO TABLE1 SELECT ('R1C1', 'R1C2') - this will be wrong

Required:

INSERT INTO TABLE1 SELECT (1, 'R1C1', 'R1C2')

I only allow implicit inserts to be allowed when set identity_insert

OFF

Such as:

Set Identity_Insert Table Off

Required:

INSERT INTO TABLE1 SELECT ('R1C1', 'R1C2')

otherwise

INSERT INTO TABLE1 SELECT (1, 'R1C1', 'R1C2') - this will be wrong

You can use @@ iDENTITY this system value to return to the number of the plug line after using the implicit insertion.

INSERT INTO TABLE1 SELECT ('R1C1', 'R1C2')

Return to the table:

AutoID

Field1

Field2

1

R1C1

R1C2

SELECT @@ identity

return value:

1

You can do the following methods in your application:

Set recs = cnn.execute ("INSERT INTO TABLE1 SELECT ('R1C1', 'R1C2')")

Recordnum = cnn.execute ("SELECT @@ Identity"). Fields (0) .value

The value of RecordNum after execution of the above statement will be set to the last automatic number

Association

Example:

Table1

Table2

Id

TF1

Value1

Id

TF2

Value2

1

TFI1-1

10

5

TFI2-1

11

5

TFI1-2

20

6

TFI2-2

twenty one

2

TFI1-3

30

3

TFI2-3

31

3

TFI1-4

40

1

TFI2-4

41

Table2

Inner Join

Only the record corresponding to two tables

Select * from table1 inner join table2 on table1.id = Table2.id Order by table1.id

Return to the table:

Id

TF1

Value1

Id

TF2

Value2

1

TFI1-1

10

1

TFI2-4

41

3

TFI1-4

40

3

TFI2-3

31

5

TFI1-2

20

5

TFI2-1

11

Left Join (Left Outer Join)

Display the left table all the records corresponding to the left table corresponding to the right, when there is no record in the right table, the right table is filled with NULL

Select * from table1 left join table2 on table1.id = Table2.id Order by table1.id

Return to the table:

Id

TF1

Value1

Id

TF2

Value2

1

TFI1-1

10

1

TFI2-4

41

2

TFI1-3

30

NULL

NULL

NULL

3

TFI1-4

40

3

TFI2-3

31

5

TFI1-2

20

5

TFI2-1

11

Right Join (Left Outer Join)

Display the record of the right table and the left table corresponding to the right table, when there is no record in the left table, the left table is filled with NULL

Select * from table1 left join table2 on table1.id = Table2.id Order by table1.id

Return to the table:

Id

TF1

Value1

Id

TF2

Value2

NULL

NULL

NULL

6

TFI2-2

twenty one

1

TFI1-1

10

1

TFI2-4

41

3

TFI1-4

40

3

TFI2-3

31

5

TFI1-2

20

5

TFI2-1

11

Full Join (Full Outer Join)

Display the left and right tables, when the left table has no record, the left table corresponding fields are filled with NULL, and when the right table is not recorded, the corresponding field is filled with NULL

Select * from table1 left join table2 on table1.id = Table2.id Order by table1.id

Return to the table:

Id

TF1

Value1

Id

TF2

Value2

1

TFI1-1

10

1

TFI2-4

41

2

TFI1-3

30

NULL

NULL

NULL

3

TFI1-4

40

3

TFI2-3

31

5

TFI1-2

20

5

TFI2-1

11

NULL

NULL

NULL

6

TFI2-2

twenty one

Description:

In the case of multi-level associations, it should be adopted on the principle of close relationship.

Such as:

Select * from table1 inner join table2 inner join table2-1 on table2.id = Table2-1.id on table1.id = table2.id

Table2 is associated with Table2-1

Table1 is associated with Table2

suggestion:

When writing such a association, it is best to make the base format structure.

Such as:

SELECT *

From

Table1

Inner Join Table2

Inner Join Table2-1

On Table2.id = Table2-1.ID

On Table1.id = Table2.ID

WHERE

ID IN (1, 2, 3)

Note:

After the query statement is written, the SQL statement can be formatted by the Enterprise Manager, but the statement coming out must be tested, because when he is automatically formatted, some complex relationships may be wrong.

Group

GROUP BY

(Nothing to say !!)

Such as:

SELECT A, B, SUM (D) from table1 group by a, b order by a

Note:

Be careful in the group by in the group by, you should pay attention to the use of Group BY.

As long as all fields that have not been processed in the same query statement need to be Group,

As in SQL above, field A, and B are not split, and the field A is sorted, and the field D is submitted to the SUM SUM.

So the fields a, b needs to be done by group D.

Such as:

SELECT A, B, SUM (D) from table1 group by c, c order by c

In this query, although the field c is not selected, he is also in the field of the field, should also be in the group of Group.

Such as:

SELECT A, B, SUM (D) from table1 Where a in (SELECT D from Table1 T1 Where Not C Is Null) Group by a, b, c Order by C

In this query, fields A, b are sequencing fields, but the field D is also in the same table table1, but he does not have to do D's group in the subquery.

To filter the aggregate results, you should use the HAVING keyword, not where keywords,

Such as:

SELECT A, B, SUM (D) from table1 where count (*)> 2 Group by A, b --- This will be wrong because count is a polymerization function, and the polymer function cannot be filtered in the WHERE clause.

Should be changed to:

SELECT A, B, SUM (D) from table1 group by a, b Having count (*)> 2

Application Group can make a classification statistics

The related keywords are cube, rollup but does not worry, use these two keywords,

In general, if the Grid in the program has a classification summary function, the corresponding speed will be fast than using these two keywords,

The aggregation function used with these two keywords is grouping (), that is, when the project classification summary, grouping () will return 1, and it is 0, which provides a reference to write a statistical title.

See "T-SQL" for details.

Specific examples have applications in "Summary Summary" in Somic Human Resources Management

condition

Case When

This group of keywords can be replaced by ife ... .lse or Select Case

grammar structure:

Case [expression]

When Then Result

[ELSE ELSE_RESULT]

End

When using this statement in the query, try to add a different name after End,

Such as:

Select [name], Xtype, Case When Xtype = 'u' TEN 'user table' else case when xi xtype = 's' Then' system

Return to the table:

Name

Xtype

Types of

Syscolumns

S

System table

TableDefine

U

user table

For detailed use, please refer to "Textile Meeting Sales"

Use this statement with the SELECT to use UNION connections

Process statement application

Variable definitions

The user variable in SQL is based on @ headed strings, system variables with @@ head

Such as:

@i

@Tmpstr

Definition method:

Declare @i int

Declare @TMPSTR NVARCHAR (30)

It is best to perform initial settings after completing the variable definition, such as

Set @ i = 0

SET TMPSTR = ''

or

SELECT @ i = 0, @ Tmpstr = ''

Apply the value of the variable to the value of the variable in SQL.

Cursor definition

Cursor, you can return the results of the query to the cursor type

Definition method:

Declare cursor

For

Such as:

Declare Cursor getName

For select [name] from sysobjects

Cursor usage:

Open the cursor:

Open

Open getname

Retrieve the game:

Fetch [Next | PRIOR | First | Last] Form [INTO ...]

Such as:

Fetch next from getname @tmpname

When the value is successful, the corresponding record value will be filled in the @tmpname variable, and the @@ fetch_status variable is set to 0.

If the failure is @@ fetch_status variable is -1

Close a cursor

Close him after using the date, so that other processes use this cursor

Close

Such as:

Close getName

Delete cursor

After using the date of use, if you need to delete the used cursor,

Deallocate

Such as:

Deallocate getName

---