1、下面是sql server 和 access 操作數(shù)據(jù)庫結(jié)構(gòu)的常用sql，希望對你有所幫助。新建表：create table 表名(自動編號字段 int identity (1,1) primary key ,字段1 nvarchar(50) default 默認值 null ,字段2 ntext null ,字段3 datetime,字段4 money null ,字段5 int default 0,字段6 decimal (12,4) default 0,字段7 image null ,)刪除表：drop table 表名插入數(shù)據(jù)：insert into 表名 (字段1,字段2) valu

2、es (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_r

3、ename 表名, 新表名, object新建約束：alter table 表名 add constraint 約束名 check (約束字段 80);(3)法二select sno, avg(grade) averagefrom scgroup by snohaving(avg(grade)80);(4)delete from sc where sc.sno in( select sno from s where sname=s5);(5)select snamefrom swhere sdept=英語and sex=男;(6)select sc.sno,avg(grade) as aver

4、agefrom s,scwhere s.sno=sc.snogroup by sc.sno;(7)select s.sname as 姓名 ,grade as 成績 ,c.cname as 選修課程from sc,s,cwhere 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,cwhere sc.cno=c.cno and cname=osorder by grade

5、desc;(9)select snamefrom swhere not exists( select * from sc where sno=s.sno and cno=1);（10）select snamefrom swhere not exists( select * from c where not exists( select * from sc where sno=s.sno and cno=c.cno);(11)select distinct snofrom sc,scxwhere not exists( select * from sc scy where scy.sno=950

6、01 and not exists( select * from sc scz where scz.sno=scx.sno and scz.cno=scy.cno);(12)select top 3 cno as 課程號, snofrom scwhere cno=1order 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=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) re