SQL创建数据库以及表的基本语法:
设置当前数据库为系统数据库
use master
go
判断数据库是否已经存在,如果已存在就删除
if exists(select * from sysdatabases where name='student')
drop database student
go
创建数据库
create database student
on primary
(
name="student",
filename="D:\student.mdf",
size=5mb,
filegrowth=3mb,
maxsize=100mb
)
log on
(
name="student_log.ldf",
filename="D:\student.ldf",
size=3mb,
filegrowth=10%,
maxsize=100mb
)
go
创建表之前 把数据库切换成新建的数据库
use student
go
创建表,创建表的同时直接添加约束条件
create table stuInfo
(
stuId int primary key not null,
stuName varchar(50),
stuSex varchar(2) default('男'),
stuAge int check(stuAge>0 and stuAge<150),
stuAddr varchar(200),
stuPid int identity(1,1)
)
go
create table stuMarks
(
stuId int references stuInfo(stuId),
sub varchar(50),
score int
)
go
删除表
drop table stuInfo
go
删除数据库
drop database student
go
只创建表,限制条件后面添加
create table stuInfo
(
stuId int not null,
stuName varchar(50),
stuSex varchar(2),
stuAge int,
stuAddr varchar(200),
stuPid varchar(18),
stuSid int
)
go
create table stuMarks
(
stuId int,
sub varchar(50),
score int
)
go
--设置主键
alter table stuInfo
add constraint PK_stuId primary key(stuId)
--设置唯一约束
alter table stuInfo
add constraint UQ_stuPid unique(stuPid)
--check约束
alter table stuInfo
add constraint CK_stuAge check(stuAge>0 and stuAge<150)
--检查约束
alter table stuInfo
add constraint DF_stuSex default('男') for stuSex
--外键约束
alter table stuMarks
add constraint FK_stuId foreign key(stuId) references stuInfo(stuId)
--删除约束条件
alter table stuInfo
drop constraint DF_stuSex