"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