--创建数据库
use master
if exists (select * from sysdatabases where name='XY')
drop database XY
create database XY
on primary
(
name='XY_data',
filename='D:\\XY_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='ZXY_log',
filename='D:\\XY_log.ldf',
size=2mb,
maxsize=100mb,
filegrowth=10%
)
go
--删除数据库
use master
if exists (select * from sysdatabases where name='XY')
drop database XY
--创建表Student
use ZXY
go
create table Studnet
(
StudentNo int identity(1,1),
loginPwd nvarchar(20) not null,
studentname nvarchar(20) not null,
sex bit not null,
graderId int not null,
Phone nvarchar(50) null,
Address nvarchar(50) null
)
go
--像表Studnet中添加数据
insert into Studnet(studentname,loginPwd ,sex,graderid,phone ,address)
select '张三','123',0,1,'11111111111111111','北京' union
select '张一三','1234',1,1,'22222222222222222','上海' union
select '三三','1235',1,1,'33333333333333333','' union
select '张小三','12345',1,1,'44444444444444444','' union
select '张三三','123456',0,1,'55555555555555555',''
--创建表Grade
use ZXY
go
create table Grade
(
gradeId int identity (1,1),
gradeName nvarchar(50) not null
)
go
--像表Grade中添加数据
insert into Grade (gradename) values ('S1')
insert into Grade (gradename) values ('S2')
insert into Grade (gradename) values ('Y2')
--删除表
drop table Studnet
drop table Grade
--创建约束
--1.主键约束
alter table Studnet
add constraint PK_StudnetNo primary key (StudentNo)
alter table Grade
add constraint PK_gradeId primary key (gradeId)
--2.唯一约束
alter table Studnet
add constraint UQ_studnetName unique (studentname)
--3.默认约束
alter table Studnet
add constraint DF_address default ('地址不详') for address
--4.检查约束
alter table Studnet
add constraint CK_loginPwd check (loginPwd<='6')
--5.外键约束
alter table Studnet
add constraint FK_gradeId
foreign key (graderId) references Grade (gradeId)
--删除约束
alter table Studnet
drop constraint PK_StudnetNo
优化MySchool 第二章
最新推荐文章于 2021-02-05 12:22:58 发布