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

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 \'
[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

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 \'
[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