SQL创建数据库

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