二进制字符串类型
二进制字符串类型
类型 | 描述 |
binary | 长度为 n 字节的固定长度二进制数据,范围从 1 到 8,000 的值。存储大小为 n 字节。 |
varbinary | 可变长度二进制数据。n 可以取从 1 到 8,000 的值。最大的存储大小为 2^31-1 字节 |
image | 长度可变的二进制数据,从 0 到 2^31-1 (2,147,483,647) 个字节 |
判断表或其他对象及列是否存在
--判断某个表或对象是否存在 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表到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;
总结
本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注的更多内容!
更多SQL内容来自木庄网络博客