? 判斷表或其他對象及列是否存在

--判斷某個表或對象是否存在 if (exists (select * from sys.objects where name = 'classes')) print '存在'; go if (exists (select * from sys.objects where object_id = object_id('student'))) print '存在'; go if (object_id('student', 'U') is not null) print '存在'; go ? --判斷該列名是否存在，如果存在就刪除 if (exists (select * from sys.columns where object_id = object_id('student') and name = 'idCard')) alter table student drop column idCard go if (exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel')) alter table student drop column tel go

?

? 創建、刪除表

--判斷是否存在當前table if (exists (select * from sys.objects where name = 'classes')) drop table classes go create table classes( id int primary key identity(1, 2), name varchar(22) not null, createDate datetime default getDate() ) go if (exists (select * from sys.objects where object_id = object_id('student'))) drop table student go --創建table create table student( id int identity(1, 1) not null, name varchar(20), age int, sex bit, cid int ) go

?

? 給表添加字段、修改字段、刪除字段

--添加字段 alter table student add address varchar(50) not null; --修改字段 alter table student alter column address varchar(20); --刪除字段 alter table student drop column number; ? --添加多個字段 alter table student add address varchar(22), tel varchar(11), idCard varchar(3); ? --判斷該列名是否存在，如果存在就刪除 if (exists (select * from sys.columns where object_id = object_id('student') and name = 'idCard')) alter table student drop column idCard go if (exists (select * from information_schema.columns where table_name = 'student' and column_name = 'tel')) alter table student drop column tel go

?

? 添加、刪除約束

--添加新列、約束 alter table student add number varchar(20) null constraint no_uk unique; --增加主鍵 alter table student add constraint pk_id primary key(id); --添加外鍵約束 alter table student add constraint fk_cid foreign key (cid) references classes(id) go --添加唯一約束 alter table student add constraint name_uk unique(name); --添加check約束 alter table student with nocheck add constraint check_age check (age > 1); alter table student add constraint ck_age check (age >= 15 and age <= 50) --添加默認約束 alter table student add constraint sex_def default 1 for sex; --添加一個包含默認值可以為空的列 alter table student add createDate smalldatetime null constraint createDate_def default getDate() with values; ? ----- 多個列、約束一起創建-------- alter table student add /*添加id主鍵、自增*/ id int identity constraint id primary key, /* 添加外鍵約束*/ number int null constraint uNumber references classes(number), /*默認約束*/ createDate decimal(3, 3) constraint createDate default 2010-6-1 go ? --刪除約束 alter table student drop constraint no_uk;

?

? 插入數據

insert into classes(name) values('1班'); insert into classes values('2班', '2011-06-15'); insert into classes(name) values('3班'); insert into classes values('4班', default); ? insert into student values('zhangsan', 22, 1, 1); insert into student values('lisi', 25, 0, 1); insert into student values('wangwu', 24, 1, 3); insert into student values('zhaoliu', 23, 0, 3); insert into student values('mazi', 21, 1, 5); insert into student values('wangmazi', 28, 0, 5); insert into student values('jason', null, 0, 5); insert into student values(null, null, 0, 5); ? insert into student select 'bulise' name, age, sex, cid from student where name = 'tony'; --多條記錄同時插入 insert into student select 'jack', 23, 1, 5 union select 'tom', 24, 0, 3 union select 'wendy', 25, 1, 3 union select 'tony', 26, 0, 5;

?

? 查詢、修改、刪除數據

--查詢數據 select * from classes; select * from student; select id, 'bulise' name, age, sex, cid from student where name = 'tony'; select *, (select max(age) from student) from student where name = 'tony'; ? --修改數據 update student set name = 'hoho', sex = 1 where id = 1; ? --刪除數據（from可省略） delete from student where id = 1;

?

? 備份數據、表

--備份、復制student表到stu select * into stu from student; select * into stu1 from (select * from stu) t; select * from stu; select * from stu1;

?

? 利用存儲過程查詢表信息

--查詢student相關信息 exec sp_help student; exec sp_help classes;

轉載于:https://www.cnblogs.com/sumflower/archive/2012/03/13/2394251.html

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的SQL Server 数据库基础编程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。