（转）（增加自己的更改）
下面是Sql Server 和 Access 操作数据库结构的常用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 [表名]
插入数据：INSERT INTO [表名] (字段1,字段2) VALUES (100,\'51WINDOWS.NET\')
删除数据：DELETE FROM [表名] WHERE [字段名]>100
更新数据：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数据库中重命名表)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 中的日志，减小数据库文件大小dump transaction 数据库名 with no_logbackup log 数据库名 with no_logdbcc shrinkdatabase(数据库名)exec sp_dboption \'数据库名\', \'autoshrink\', \'true\'
\\\'添加字段通用函数Sub AddColumn(TableName,ColumnName,ColumnType)Conn.Execute(\"Alter Table \"&TableName&\" Add \"&ColumnName&\" \"&ColumnType&\"\")End Sub
\\\'更改字段通用函数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\")\'返回一个数值，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 nullforeign key(Sno)references student(Sno)foreign key(Cno)references course(Cno))
(1)seleCt top 1 S.sno,snamefrom SC,Swhere Cno='C2' and SC.sno=S.snoorder by grade desC;
(2)seleCt sname,agefrom Student,SCwhere SC.sno not in(seleCt SC.snofrom SCwhere Cno='C2' )and SC.sno=S.sno;(3)seleCt sno, avg(grade) as averagefrom SCgroup by snohaving(avg(grade)>80);(3)法二seleCt sno, avg(grade) ' average'from 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 averagefrom 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 分数,cname as 课程名from SC,Cwhere SC.cno=C.cno and cname='OS'order by grade 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=95001 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<=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
原文来自：http://www.cnblogs.com/xulb597/archive/2009/05/19/1459944.html
转载于:https://www.cnblogs.com/lidaofei1991/articles/4146937.html
相关资源：连接二值图像中断开的点