一、《SQL SERVER2005数据库管理系统》
1、创建数据库
if exists(select * from sysdatabases where name='stuDB')
drop database stuDB
go --批处理
create database stuDB
on --数据文件
(
name='stuDB_data',
filename='D:\data\stuDB_data.mdf',
size=3mb,
filegrowth=10%
)
log on --日志文件
(
name='stuDB_log',
filename='D:\data\stuDB_log.ldf',
size=1mb,
filegrowth=10%
)
go
-------------------------------------------------------------
2、创建表
if exists(select * from sysobjects where name='student')
drop table student
go
create table student
(
stuId int primary key identity(1,1), --主键，自动增长列
stuName varchar(50) not null,
--默认值为男，只能是男或者女
stuSex varchar(2) not null default('男') check(stuSex='男' or stuSex='女'),
stuAge int,
stuEmail varchar(50),
classId int
)
go
-------------------------------------------------------------
3、给表添加约束
(1)给student表添加默认约束(stuAge默认为20)
alter table student
add constraint DF_student_stuAge default(20) for stuAge
(2)给student表添加检查约束(stuEmail必须包含@)
alter table student
add constraint CK_student_stuEmail check(stuEmail like '%@%')
---------------------------------------------------------------
4、增
a)一次性插入一条数据
insert into student(stuName,stuSex,stuAge,stuEmail,classId)values
('张三','男',20,'zs@163.com',2)
b)一次性插入多条数据
insert into student(stuName,stuSex,stuAge,stuEmail,classId)values
select '李四','女',18,'lisi@163.com',1 union
select '王五','男',19,'wangwu@163.com',3
c)把数据从一张表中查询出来放入到另一张表中
select stuName,stuSex
into stuNew --新表stuNew不能事先存在
