SQL CREATE TABLE, delete table Add field Delete field action

"Go" SQL create TABLE, delete table Add field Delete field action

Here are some common SQL Server and Access operations database structures that you would like to help.

New table:
CREATE table [table name]
(
[AutoNumber field] int IDENTITY (PRIMARY KEY),
[Field 1] NVarChar (+) default \ ' defaults \ ' 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,
)

To delete a 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 three] = \ ' Haiwa\ '

New fields:
ALTER table [table name] ADD [field name] NVARCHAR () NULL

To delete a field:
ALTER table [table name] DROP COLUMN [field name]

To modify a field:
ALTER TABLE [table name] Alter COLUMN [field name] NVARCHAR () NULL

Rename table: (Access rename table, refer to article: renaming tables in an Access database)
sp_rename \ ' table name \ ', \ ' new table name \ ', \ ' object\ '

New constraint:
ALTER table [table name] ADD CONSTRAINT constraint name CHECK ([constraint field] <= \ ' 2000-1-1\ ')

To delete a constraint:
ALTER table [table name] DROP CONSTRAINT constraint name

New default value
ALTER table [table name] ADD CONSTRAINT default name \ ' 51windows.net\ ' for [field name]

Delete default values
ALTER table [table name] DROP CONSTRAINT Default value name

Reduce the size of the database file by removing logs from SQL Server
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 field general function
Sub AddColumn (Tablename,columnname,columntype)
Conn.execute (\ "Alter Table \" &tablename&\ "ADD \" &columnname&\ "\" &columntype&\ "\")
End Sub

\\\ ' Change field general function
Sub Modcolumn (Tablename,columnname,columntype)
Conn.execute (\ "Alter Table \" &tablename&\ "alter Column \" &columnname&\ "\" &columntype&\ "\")
End Sub

\\\ ' Check if 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 Rs=conn.execute (SQL)

Response.Write rs (\ "dida\") \ ' Returns a numeric value, 0 means no, 1 represents existence

To 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 TABLE Student (
Sno int NOT null primary key,
Sname Char (TEN) is not NULL,
Ssex bit NOT NULL,
Sage tinyint NOT NULL,
Sdept Char (a) NOT null)

CREATE TABLE Course (
Cno int NOT null primary key,
Cname Char (a) is not NULL,
Cpno int NOT NULL,
Ccredit tinyint NOT NULL)

CREATE TABLE SC (
Sno int NOT NULL,
Cno int NOT NULL,
Grade tinyint NOT NULL
Foreign KEY (Sno) references student (Sno)
Foreign KEY (Cno) References course (CNO)
)

(1)
SeleCt Top 1 S.sno,sname
From Sc,s
where cno= ' C2 ' and Sc.sno=s.sno
Order by grade DesC;

(2)
SeleCt Sname,age
From STUDENT,SC
where Sc.sno not in (
SeleCt Sc.sno
From SC
where cno= ' C2 ') and Sc.sno=s.sno;
(3)
SeleCt Sno, AVG (grade) as average
From SC
GROUP BY Sno
Having (avg (grade) >80);
(3) Law II
SeleCt Sno, avg (grade) ' average '
From SC
GROUP BY Sno
Having (avg (grade) >80);

(4)
Delete from SC
where Sc.sno in (
SeleCt Sno
From S
where sname= ' S5 ');
(5)
SeleCt sname
From S
where sdept= ' English ' and sex= ' men ';
(6)
SeleCt Sc.sno,avg (grade) as average
From S,SC
where S.sno=sc.sno
Group BY Sc.sno;

SQL CREATE TABLE, delete table Add field Delete field action