SQL Server and Access database structure SQL statement Summary

The following are common SQL statements used by SQL Server and access to operate database structures.
The content is organized by HAI wa. if the content is incorrect or incomplete, please submit it. Thank you.

Create a table:
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'