下面是Sql Server 和 Access 操作數(shù)據(jù)庫結構的常用Sql，希望對你有所幫助。
新建表：
create table [表名]
(
int IDENTITY (1,1) PRIMARY KEY ,
nVarChar(50) default \'默認值\' null ,
ntext null ,
datetime,
money null ,
int default 0,
Decimal (12,4) default 0,
image null ,
)
刪除表：
Drop table [表名]
插入數(shù)據(jù)：
INSERT INTO [表名] (字段1,字段2) VALUES (100,\'51WINDOWS.NET\')
刪除數(shù)據(jù)：
DELETE FROM [表名] WHERE [字段名]>100
更新數(shù)據(jù)：
UPDATE [表名] SET [字段1] = 200,[字段2] = \'51WINDOWS.NET\' WHERE [字段三] = \'HAIWA\'
新增字段：
ALTER TABLE [表名] ADD [字段名] NVARCHAR (50) NULL
刪除字段：
ALTER TABLE [表名] DROP COLUMN [字段名]
修改字段：
ALTER TABLE [表名] ALTER COLUMN [字段名] NVARCHAR (50) NULL
重命名表：(Access 重命名表，請參考文章：在Access數(shù)據(jù)庫中重命名表)
sp_rename \'表名\', \'新表名\', \'OBJECT\'
新建約束：
ALTER TABLE [表名] ADD CONSTRAINT 約束名 CHECK ([約束字段] <= \'2000-1-1\')
刪除約束：
ALTER TABLE [表名] DROP CONSTRAINT 約束名
新建默認值
ALTER TABLE [表名] ADD CONSTRAINT 默認值名 DEFAULT \'51WINDOWS.NET\' FOR [字段名]
刪除默認值
ALTER TABLE [表名] DROP CONSTRAINT 默認值名
刪除Sql Server 中的日志，減小數(shù)據(jù)庫文件大小
dump transaction 數(shù)據(jù)庫名 with no_log
backup log 數(shù)據(jù)庫名 with no_log
dbcc shrinkdatabase(數(shù)據(jù)庫名)
exec sp_dboption \'數(shù)據(jù)庫名\', \'autoshrink\', \'true\'
\\\'添加字段通用函數(shù)
Sub AddColumn(TableName,ColumnName,ColumnType)
Conn.Execute(\"Alter Table \"&TableName&\" Add \"&ColumnName&\" \"&ColumnType&\"\")
End Sub
\\\'更改字段通用函數(shù)
Sub ModColumn(TableName,ColumnName,ColumnType)
Conn.Execute(\"Alter Table \"&TableName&\" Alter Column \"&ColumnName&\" \"&ColumnType&\"\")
End Sub
\\\'檢查表是否存在
sql=\"select count(*) as dida from sysobjects where id = object_id(N\'[所有者].[表名]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1\"
set rs=conn.execute(sql)
response.write rs(\"dida\")\'返回一個數(shù)值，0代表沒有，1代表存在
判斷表的存在:
select * from sysobjects where id = object_id(N\'[dbo].[tablename]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1
某個表的結構
select * from syscolumns where id = object_id(N\'[dbo].[你的表名]\') and OBJECTPROPERTY(id, N\'IsUserTable\') = 1
create table student(
Sno int not null primary key,
Sname char(10)not null,
Ssex bit not null,
Sage tinyint not null,
Sdept char(20) not null)
create table course(
Cno int not null primary key,
Cname char(20)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)法二
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='英語'and sex='男';
(6)
seleCt SC.sno,avg(grade) as average
from S,SC
where S.sno=SC.sno
group by SC.sno;
(7)
seleCt S.sname as 姓名 ,grade as 成績 ,C.cname as 選修課程
from SC,S,C
where S.sno=SC.sno and SC.cno=C.cno and SC.cno in(
? ? seleCt cno
? ? from C
? ? where cname='DB');
(8)
select TOP 1 sno as 學號,grade as 分數(shù),cname as 課程名
from SC,C
where SC.cno=C.cno and cname='OS'
order by grade desc;
(9)
select Sname
from? S
where not exists(
? ? ? select *
? ? ? from SC
? ? ? where Sno=S.Sno and Cno=1);
（10）
select Sname
from S
where not exists(
? ? ? select *
? ? ? from C
? ? ? where not exists(
? ? ? ? ? ? select *
? ? ? ? ? ? from SC
? ? ? ? ? ? where Sno=S.Sno and Cno=C.Cno));
(11)
select distinct Sno
from SC,SCX
where not exists(
? ? ? select *
? ? ? from SC SCY
? ? ? where SCY.Sno=95001 and
? ? ? ? not exists(
? ? ? ? ? ? select *
? ? ? ? ? ? from SC SCZ
? ? ? ? ? ? where SCZ.Sno=SCX.Sno and SCZ.Cno=SCY.Cno));
(12)
select top 3 Cno as 課程號, Sno
from SC
where Cno=1
order by Grade desc;
create database stu
use stu
create table S
(
sno char(6),
sname char(10),
age int,
sex char(2),
constraint PK_S primary key (sno),
constraint CK_age check(age>=0 and age<=150)
)
create table C
(
cno char(8),
cname char(16),
credit int,
constraint PK_C primary key (cno),
constraint CK_credit check (credit>=0)
)
create table SC
(
sno char(6),
cno char (8),
constraint PK_SC primary key (sno,cno),
constraint FK_s foreign key (sno) references S(sno),
constraint FK_c foreign key (cno) references C(cno)
)
insert into S values ('001','zhang',19,'男')
insert into S values('002','li',16,'女')
select * from S
