SQL server 2000数据库基础编程(一)

以下所有内容适用于SQL server 2000，其他版本或类型数据库可能会有部分不同，语句都差不多，请随机应变，例：
sysdatabases <–> sys.databases
sysobjects <–> sys.objects

一、使用、切换数据库
例如跳到 master数据库

use master
go

go批处理语句，用于同时执行多个语句。

二、创建、删除数据库
1、判断该数据库是否存在，如果存在就删除；
2、创建数据库，设置数据库文件、日志文件保存路径

if (exists (select * from sysdatabases where name = 'studb'))
drop database studb
go

create database studb
on(
    name = 'studb',
    filename = 'c:\data\students.mdf'
)
log on(
    name = 'studb_log',
    filename = 'c:\data\studb_log.ldf'
)
go

如果是自己的用户数据库，直接创建就行了
三、创建、删除表
1、判断当前table是否存在，如果存在，删除；
2、创建表

--创建class表
if(exists(select *from sysobjects where name = 'class'))
drop table class
go

create table class(
    id int primary key identity(1,1),
    name varchar(22) not null,
    createDate datetime default getdate()
)
go

--创建student表
if(exists(select * from sysobjects where name = 'student'))
drop table student
go

create table student(
    id int identity(1,1) not null,
    name varchar(22),
    age int,
    sex bit
)
go

identity(a,b)，标识列，a、b均为正整数，a表示开始数，b表示增幅，就像identity(1,1)意思就是该列自动增长，由1开始每次增加1，依次为12345···；identity(1,2)，则表示id依次为13579···；identity(2,2)，则表示id依次为2468···

三、给表添加字段、修改字段、删除字段

--添加字段
alter table student add addr varchar(50)

--修改字段
alter table student alter column addr varchar(20)

--删除字段
alter table student drop column addr

--添加多个字段
alter table student 
add addr varchar(22),
    tel varchar(11),
    idCard varchar(3)

--判断该列名是否存在，如果存在就删除
if (exists (select * from syscolumns where 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 constraint pk_id primary key(id)

--添加新列、约束
alter table student 
add number varchar(20) null constraint no_uk unique

--添加唯一约束
alter table student
add constraint name_uk unique(name)

--添加check约束
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
/*添加id主键、自增*/  
    id int identity constraint id primary key,   
/*添加外键约束*/   
    number int null constraint uNumber references class(number),  
/*默认约束*/  
    createDate decimal(3, 3) constraint createDate default 2016-6-1  
go

--删除约束
alter table student  drop constraint no_uk

五、插入数据

--添加班级表信息
insert into class(name) values('1班');
insert into class values('2班', '2016-06-15');
insert into class values('3班', default);
 --添加学生表信息
insert into student values('ada', 22, 1, 1);
insert into student values('lucy', 25, 0, 1);
insert into student values('petter', 24, 1, 3);
insert into student values('bob', 23, 0, 3);
insert into student values('lilei', 21, 1, 5);
insert into student values('tom', 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 'taomi', 24, 0, 3 union
select 'wendy', 25, 1, 3 union
select 'tony', 26, 0, 5;

六、查询、修改、删除、备份数据

--查询数据
select * from class;
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 = 'hehe', sex = 1 where id = 1;

--删除数据（from可省略）
delete from student where id = 1;

--备份、复制student表到stu
select * into stuBack from student;
select * into stu1 from (select * from stuBack);

select * from stuBack;
select * from stu1;