For more information about Transact-SQL, see Transact-SQL reference (tsql. HLP) (abbreviated as "T-SQL") Creation purpose: it is best to write the reserved SQL words for data operations in uppercase when writing SQL scripts. Note: The syntax format here is only a common format, it is not a standard SQL format. For standard formats, see T-SQL
The following Program Code All use VB6.0 code. In this example, the SQL has no practical significance. Select selectselect to select the specified data column, for example: select * From sysobjectsselect [name] From syscolumns when "[]" is applied when system reserved words exist in SQL, or "[]" is applied when special characters exist in SQL, for example, when using aliases in select [Object Name] From objects, pay attention to the preceding principles. You can use the following two methods to use aliases: the as in the middle of column_name as Alias column_name alias can be omitted. In select, the conditional selection syntax can be used. For details, see the following "conditions", such as select [name], xtype, case when xtype = 'u'then' User table 'else case when xtype = 's' then' system table 'end end as type from sysobjects return table:
| Name |
Xtype |
Type |
| Syscolumns |
S |
System Table |
| Tabledefine |
U |
User table |
Merge Two Queries into a separate returned table: Use the Union keyword such as select a, B from Table1 unoin select C, D from Table2 Description: When union is used, if the All parameter is not specified, the same record is ignored by default, for example:
| Table1 |
|
Table 2 |
| ID |
TF1 |
Value1 |
|
ID |
TF2 |
Value2 |
| 1 |
A |
10 |
|
5 |
A |
10 |
| 5 |
B |
20 |
|
6 |
D |
21 |
| 2 |
A |
30 |
|
3 |
C |
31 |
| 3 |
C |
40 |
|
1 |
B |
41 |
Select TF1, value1 from Table1 Union select TF2, value2 from Table2
Returned table:
| TF1 |
value1 |
| A |
10 |
| B |
20 |
| A |
30 |
| C |
40 |
| d |
21 |
| C |
31 |
| B |
41 |
It can be seen that a record with "TF2 = A, value2 = 10" is missing, but when the following query is used, select TF1, value1 from Table1 Union all select TF2, value2 from Table2 returns the table:
| TF1 |
value1 |
| A |
10 |
| B |
20 |
| A |
30 |
| C |
40 |
| A |
10 |
| d |
21 |
| C |
31 |
| B |
41 |
This query will return all records. This issue may occur in report statistics. For example, an employee performs the same product and data in different dates, however, when the non-all method is used for aggregation, one less record will be combined with the into select .... Into B from a can store specified data in Table A to table B. 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 the query result select field1, field2 into result from Table1 where ID> 1000 create a new table and add an automatic serial number to the new table. Some tables need an automatic serial number column to distinguish it from Select Identity (INT,) as autoid of each row, * Into new_table1 from Table1 where the identity Function Description: Format: Identity (<datatype> [seed, increment]) parameter description: datatype: data type, depending on the number of records, generally, you can set the int type. For details, refer to the SQL limit parameter seed: start value, that is, the base number of the start. The default value is 1 increment: increment, and the step size is the interval between data, the default value is 1. The preceding SQL statement indicates that the automatic number starts from 1 and the table returned by adding 1 to each row is:
| autoid |
field1 |
field2 |
| 1 |
Hello |
Joy |
| 2 |
Hello |
Tom |
| 3 |
Hi |
Lily |
| 4 |
Hello |
Lily |
Note: You can also set the format of identity ([seed, increment]) when creating a table, for example, create table Table1 (autoid int identity ), or autoid int identity field1 nvarchar (30), field2 nvarchar (30) modify the alter table Table1 add autoid int identity) when inserting data, pay attention to the setting of the identity_insert attribute. When set identity_insert <Table> On, implicit insertion is not allowed, for example, set identity_insert Table1 on insert into Table1 select ('r1c1 ', 'r1c2 ') -- this will cause an error. You must use: insert into Table1 select (1, 'r1c1', 'r1c2 ') implicit insertion is allowed only when set identity_insert <Table> off. For example, set identity_insert table off must be used: insert into Table1 select ('r1c1 ', 'r1c2 ') otherwise, insert into Table1 select (1, 'r1c1 ', 'r1c2 ') -- in this case, the system value @ identity can be used after implicit insertion to return the number of inserted rows insert into Table1 select ('r1c1 ', 'r1c2') to return the table:
| autoid |
field1 |
field2 |
| 1 |
r1c1 |
r1c2 |
Select @ identity return value: 1 in the application, you can use the following method: Set recs1_cnn.exe cute ("insert into Table1 select ('r1c1 ', 'r1c2 ')") recordnum1_cnn.exe cute ("select @ identity "). fields (0 ). after the value and above statements are executed, the value of recordnum will be set to the last auto-numbered Association case:
| Table1 |
|
Table 2 |
| ID |
TF1 |
Value1 |
|
ID |
TF2 |
Value2 |
| 1 |
TFI1-1 |
10 |
|
5 |
TFI2-1 |
11 |
| 5 |
TFI1-2 |
20 |
|
6 |
TFI2-2 |
21 |
| 2 |
TFI1-3 |
30 |
|
3 |
TFI2-3 |
31 |
| 3 |
TFI1-4 |
40 |
|
1 |
TFI2-4 |
41 |
Table2inner join only displays the record select * From Table1 inner join Table2 on table1.id = table2.id order by table1.id returned 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) shows all records in the left table corresponding to the right table. When no records exist in the right table, select * From Table1 left join Table2 on table1.id = table2.id order by table1.id is returned for the corresponding field in the right table with NULL:
| 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) displays the records in the right table corresponding to the left table. If no records exist in the left table, select * From Table1 left join Table2 on table1.id = table2.id order by table1.id:
| id |
TF1 |
value1 |
id |
TF2 |
value2 |
| null |
null |
null |
6 |
TFI2-2 |
21 |
| 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) displays all records in both the left and right tables. When no records exist in the left table, the corresponding fields in the left table are filled with null, if no record exists in the right table, the relevant fields in the right table are filled with null select * From Table1 left join Table2 on table1.id = table2.id order by table1.id to return 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 |
21 |
Note: the principle of proximity association should be used for multi-level association, for example: select * From Table1 inner join Table2 inner join Table2-1 on table2.id = Table2-1.ID on table1.id = table2.id that Table2 associates with Table2-1 Table1 with Table2, it is best to structure the base statement format 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 (, 3) Note: after writing the query statement, you can format the SQL statement in the "Enterprise Manager". However, the statements produced in this process must be tested, this is because, during Automatic formatting, it is possible to incorrectly group some complex relationships into gro groups. Up by (nothing to say !!) For example, select a, B, sum (d) from Table1 group by a, B order by a NOTE: When performing group by, pay attention to the use of fields in group, as long as all fields in the same query statement that have not been merged need to be grouped, in the preceding SQL statement, fields A and B are not merged, and field a is sorted, the sum function of field D is used to collect statistics. Therefore, field a and field B need to be grouped, and field D does not need to be used, for example, select a, B, sum (d) from Table1 group by, b, C order by C in this query, although field C is not selected, but it is set by order, so field C should also be in the group field 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, field a and field B are the selection fields, and field C is the sorting field, although field D is also in the same table Table1, however, in subqueries, the user does not need to perform group operations on D. to filter aggregation results, the user should use the having keyword instead of the where keyword, for example, select a, B, sum (d) from Table1 where count (*)> 2 group by a, B --- this will cause an error because count is an aggregate function, in the WHERE clause, you cannot filter Aggregate functions by selecting a, B, sum (d) from Table1 group by a, B having count (*)> 2. Application Group can use the cube and rollup keywords for classification statistics, but these two keywords are not intended to be used. In general, if the grid in the program has the Classification summary function, the corresponding speed will be faster than using these two keywords. The aggregate function used with these two keywords is Grouping (), that is, when the project is classified and summarized, grouping () returns 1, and vice versa, it is 0, which provides a reference for statistics titles, for more information, see T-SQL condition case when. The function of this set of keywords can replace if... Then .... Else or select case syntax structure: case [expression] When <condition> then result [else else_result] end when using this statement in the query, try to add an alias after the end, such: select [name], xtype, case when xtype = 'u'then' User table 'else case when xtype = 's' then' system table 'end end as type from sysobjects
Returned table:
| Name |
Xtype |
Type |
| Syscolumns |
S |
System Table |
| Tabledefine |
U |
User table |
This statement can be used together with select to use Union to perform a row-column transpose procedural statement. The Application variables are defined in SQL. User variables are strings with @ headers. System variables are marked with @ headers, for example: @ I @ tmpstr definition method: declare @ I int declare @ tmpstr nvarchar (30) it is best to set the variable after it is defined, for example, set @ I = 0 set @ tmpstr = ''or select @ I = 0, @ tmpstr ='' in SQL, set or select is used to define the cursor for variable assignment, the query result can be returned as the cursor type definition method: declare cursor <curname> for <SQL SCRIPT> For example: declare cursor getname for select [name] From sysobjects usage: Open the cursor: open <curname> For example: Open getname retrieval cursor: Fetch [next | Prior | first | last] form <curname> [into <valuename>…] For example, when the value of fetch next from getname into @ tmpname is successful, the corresponding record value is filled in the @ tmpname variable and the @ fetch_status variable is set to 0, if the cursor fails, the variable @ fetch_status is-1. Close the cursor after the cursor is used, so that other processes can close the cursor. For example: close getname Delete cursor after the cursor is used, if you no longer need to delete the used cursor, deallocate <curname> For example: deallocate getname
