If the column is changed to auto-increment auto-segment

[From network]
Create Table [Table name]
(
[Automatic number field] int identity (1, 1) primary key,
[Field 1] nvarchar (50) default 'default value 'null,
[Field 2] ntext null,
[Field 3] datetime,
[Field 4] Money null,
[Field 5] int default 0,
[Field 6] decimal (12, 4) default 0,
[Field 7] image null,
)

Delete table:
Drop table [Table name]

Insert data:
Insert into [Table name] (Field 1, Field 2) values (100, '51windows. net ')

Delete data:
Delete from [Table name] Where [field name]> 100

Update Data:
Update [Table name] Set [Field 1] = 200, [Field 2] = '51windows. net' where [Field 3] = 'haiwa'

New field:
Alter table [Table name] add [field name] nvarchar (50) null

Delete field:
Alter table [Table name] Drop column [field name]

Modify Field:
Alter table [Table name] alter column [field name] nvarchar (50) null

Rename a table: (access to rename a table, referArticle: Rename the table in the ACCESS database)
Sp_rename 'table name', 'new table name', 'object'

New constraint:
Alter table [Table name] add constraint name check ([constraint field] <= '2017-1-1 ')

Delete constraints:
Alter table [Table name] Drop constraint name

Create Default Value
Alter table [Table name] add constraint default name: '51windows. net' for [field name]

Delete default value
Alter table [Table name] Drop constraint default name

Delete logs in SQL Server to reduce the size of database files
Dump transaction database name with no_log
Backup log database name with no_log
DBCC shrinkdatabase (database name)
Exec sp_dboption 'database name', 'autowrite', 'true'

The auto-incrementing Column cannot be directly modified. You must delete the original ID column and then re-add an ID field with the identity attribute. The statement is as follows:

Alter table table name
Drop column ID

Alter table table name
Add ID int identity (1, 1)

To change the date field to the datatime type, you must first ensure that there is no data in the table or that the existing data can be directly converted to the datetime type. Otherwise, the field type cannot be modified.
Alter table table name
Alter column Date Field name datetime

1. Reference a database before using it: Use Database Name
2. exists () is used to determine whether the subquery result exists. True or false is returned.
3. object_id ('object name') returns the ID corresponding to this object name, which is stored in the sysobjects table.
4. When creating a table, the primary key constraint is automatically created: Create Table A (B char (4), c int, and constraint primary key name primary key (c ))
Or: Create Table A (a char (4) CINT primary key) or: Create Table A (a char (4) CINT constraint primary key name primary key)
5. Create an index: Create Index name on table name (field name)
6. Add a foreign key to a table: alter table table name Add constraint foreign key name foreign key (field) References table name (field)
7. Identity (seed, range) creates an ID column, which is in parallel with null and not null. Seed indicates the seed, that is, the initial value; range indicates the growth.
8. The local temporary table # xxx can only be accessed by the current session and disappears automatically after the session ends.
9. Global temporary table # XX, which can be used by multiple users and automatically disappears after the session ends.

10. Add/modify/delete columns to/from a table: alter table table name Add column name type/alter column name newtype/drop column name
11. You can install sqlserver multiple times to create multiple instances for it.
12. Rename the table and column with the system process sp_rezgxx_xm:
Sp_rezgxx_xm original table name, new table name
Sp_rezgxx_xm "table name. Original column name", new column name
13. If you want to display the insert value in the identity column, you must first set: Set identity_insert table name on
Set identity_insert table name off.
14. Use writetext to write values to the text or image column:

Declare @ var varbinary (16)
Select @ Var = textptr (c) from test where a = 10
Writetext test. c @ var 'zhongguo'

Note: To use writetext, you must first set it as follows: sp_dboption Database Name, 'select into/bulkcopy', true
15. The truncate table name deletes all data in the table, which is fast.
16. The null value (null) is ignored except count (*) in the statistical function ).
17. Because the data of the text and image types is very long, you can set the global variable textsize to specify the length of the returned data before the query. Set textsize 50
If you want to check the global variable textsize value: Select @ textsize

18. Read text data through readtext:

Declare @ var varbinary (16)
Select @ Var = textptr (c) from test where a = 10
Readtext test. c @ var 4 3
In this example, the characters 5, 6, and 7 are returned. It must be 3 characters in length and 0 characters in start, SO 4 indicates reading from the fifth character.
Textptr returns the pointer of the specified text, ntext, and image columns, which are generally stored in varbinary variables.
19. In the like statement, you can specify a simple regular expression. [A-Z] indicates an arbitrary letter, and [^ A-Z] indicates a non-letter character.
20. Like is also the only operator that can be used in text columns.
21. logical operator Priority: Not> and> or
22. In a group by record set, all null values constitute a group.
23. The SELECT statement with the group by clause may have a where clause, but the WHERE clause must be placed before the Group by clause.
24. If the group by clause uses all, that is, group by all XXX, records that do not meet the search criteria are also displayed, but are not included in the statistics.
25. Having can contain only the columns specified in the group by clause, or statistical functions. Any column can be specified in the WHERE clause, but no statistical function can be used.
26. The having clause removes groups that do not meet the condition from the final result.
27. You can also use the having clause without the group by clause and use the entire query result as a group.
And the columns that appear in the having clause must be columns in the group by clause. Therefore, when the group by clause is not included in the having clause
Select a column name in the list and use only statistical functions.
28. When the order by clause is specified after the group by clause, you can only specify columns or statistical functions in the group by clause in the order by clause.
29. When the union operation is performed, duplicate rows in the result are automatically deleted. If the all option is used, all rows can be displayed in the result: Union all
30. During union, the column names in the merge result set are given in the first query. Therefore, you must pay attention to the field names in the order by clause when the clause is executed in the end.
31. You can use the select fieldslist into new table name from table name to create a new table and insert all the data in the current table to the new table.
But before doing this, make sure that the select into/bulkcopy option of the database is set to true. The method is as follows:
Use master/* The setting command must be performed in the master database */
Sp_dboption database a name, "select into/bulkcopy", true/* set database options */
Use Database a name
Checkpoint/* Make the setting take effect */

If you want to insert statistical or computation results into a new table, the column name must be given in the form of a title, such:
Select a, B = AVG (c) into mm from Nn group by
32. The method for getting N records: Select Top N * from table name order by newid ()

33. Create a unique constraint: Create Table A (B INT not NUL constraint name unique, C char (10) null)
Or: Create Table A (B INT, C char (10), constraint name unique (B ))

34. Select a cell in the SQL Server table and press Ctrl + 0 to set the cell value to null.

35. How to assign a value to a variable: Set @ xxx = ??? If the value of the variable is taken from a query, select is required, for example: Select @ xxx = ??? From ??? Where ????
If you want to return a record set, but not from a table, it is composed of all system variables or custom variables. You do not have to write the from clause: Select ??,??,??
36. Create a function that returns a table:
Create Function fn_tree (@ id int)
Returns Table @ Tb (ID int, fid int)
As
Begin
Insert @ TB select ID, FID from tablename where FID = @ ID
While exists (select 1 from tablename where FID in (select ID from @ TB)
And id not in (select ID from @ TB ))
Insert @ TB select ID, FID from tablename where FID in (select ID from @ TB)
And id not in (select ID from @ TB)
Return
End

The table field is ID, FID
Insert @ tb select statement to insert the query result to the current table (@ TB)

Call:
Select * From DBO. fn_tree (0)
Go
Select * From DBO. fn_tree (1
Go
37. A date format conversion example can be converted using functions such as convert and cast, but this example does not:
Declare @ A varchar (50)
Declare @ B varchar (50)
Declare @ C int
Declare @ d varchar (50)
Declare @ E varchar (50)

Set @ A = '2017/03 10: 06: 41: 59'
Set @ B = reverse (@)
Set @ C = charindex ('/', @ B)
Set @ d = stuff (@ B, @ C, 0, '02 ')
Set @ E = reverse (@ D)
Select @ A, @ B, @ C
Select @ D, @ E

Brave Heart

0 0

0

(Please comment on the article)