Common SQL statements-create a table, add a primary key, and add a column

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, refer to the article: rename a table in Access database)
Sp_rename \ 'table name \ ', \ 'new table name \', \ 'object \'

New constraint:
Alter table [TABLE name] add constraint name CHECK ([CONSTRAINT field] <= \ '2014-1-1 \')

Delete constraints:
Alter table [TABLE name] drop constraint name

Create Default Value
Alter table [TABLE name] add constraint default name: DEFAULT \ '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 \ ', \ 'autoshrink \', \ 'true \'

\\\' Add a common field function
Sub AddColumn (TableName, ColumnName, ColumnType)
Conn. Execute (\ "alter table \" & tablename & \ "add \" & columnname & \ "\" & columntype &\"\")
End sub

\\\ 'Change the common function of a field
Sub modcolumn (tablename, columnname, columntype)
Conn. Execute (\ "alter table \" & tablename & \ "alter column \" & columnname & \ "\" & columntype &\"\")
End sub

\\\' Check whether the table exists

SQL = \ "select count (*) as Dida from sysobjects where id = object_id (n \ '[owner]. [Table name] \ ') and objectproperty (ID, n \ 'isusertable \') = 1 \"

Set rsw.conn.exe cute (SQL)

Response. Write RS (\ "Dida \") \ 'returns a value. 0 indicates none, and 1 indicates existence.

Determine the existence of a table:
Select * From sysobjects where id = object_id (n \ '[DBO]. [tablename] \') and objectproperty (ID, n \ 'isusertable \ ') = 1

Structure of a table
Select * From syscolumns where id = object_id (n \ '[DBO]. [your table name] \') and objectproperty (ID, n \ 'isusertable \ ') = 1