数据库的物理步骤：（1）创建数据库 （2）创建表 （3）添加各种约束 （4）创建数据库的登录账户 并授权 创建数据库 的语句：create database 删除数据库 的语句：drop database创建表 的语句：create table删除表结构 的语句：drop table使用 alter table语句可以创建以下语句：添加主键约束（将studentno设置为主键）alter table studentadd constraint PK_studentno primary key (studentno)添加唯一约束alter table studentadd constraint uq_IdentityCard unique (IdentityCard)添加默认约束alter table studentadd constraint DF_address default ('地址不详')for address添加检查约束alter table studentadd constraint CK_Borndate check(Borndate>='1988-8-18'）添加外键约束(主表为student 从表为result 关联列为studentno)alter table result add constraint FK_studentno foreign key (studentno ) references student (studentno)go删除约束列：（删除student地址列默认约束）alter table studentdrop constraint DF_address数据库文件的组成主数据文件：.mdf次要数据文件： .ndf日志文件： .ldf
--创建数据库USE master GOIF EXISTS (SELECT*FROM SYSDATABASES WHERE name='S222')DROP DATABASE S222CREATE DATABASE S222ON PRIMARY(NAME ='S222_DATA',FiLENAME ='F:\PROJICT\S222_data.mdf',SIZE =5MB,MAXSIZE=50MB,FiLEGROWTH=15%)log on(NAME ='S222_log',FiLENAME ='F:\PROJICT\S222_log.ldf',SIZE =2MB,MAXSIZE=50MB,FiLEGROWTH=1MB)--创建Student表USE S222GOCREATE TABLE Student(Sid int NOT NULL,Sname Varchar(20) NOT NULL,Sage int NOT NULL,Srenark Varchar(50) NOT NULL,Cid int NOT NULL)--创建Grade表USE S222GO CREATE TABLE Grade(Cid int identity(1,1),Cname Varchar(20))--插入数据INSERT INTO dbo.Grade(Cname)SELECT('S1')UNIONSELECT('S1')UNIONSELECT('S1')INSERT INTO dbo.Student(Sid, Sname, Sage, Srenark, Cid)VALUES(1,'韩庚',22,'tttttt',1)INSERT INTO dbo.Student(Sid, Sname, Sage, Srenark, Cid)VALUES(2,'莫绍谦',21,'tttttt',1)INSERT INTO dbo.Student(Sid, Sname, Sage, Srenark, Cid)VALUES(3,'罗志祥',20,'tttttt',1)--添加主键约束（Cid）ALTER TABLE StudentADD CONSTRAINT PK__sid primary key (Sid)ALTER TABLE GradeADD CONSTRAINT PK__Gid primary key (Cid)--添加外键约束Alter table StudentADD CONSTRAINT FK_Grade_cid Foreign key (Cid)REFERENCES Grade(Cid)--添加唯一约束ALTER TABLE StudentADD CONSTRAINT Sid_IDENTITYCard UNIQUE (SID)--添加默认约束ALTER TABLE StudentADD CONSTRAINT DF_Srenark DEFAULT ('地址不详') for Srenark--添加检查约束ALTER TABLE StudentADD CONSTRAINT CK_Sage CHECK (Sage>=20) --删除约束ALTER TABLE dbo.StudentDROP CONSTRAINT FK_Grade_cid--删除表--先删除从表或者子表DROP TABLE dbo.Student--删除数据库DROP DATABASE S222--课后练习--创建数据库LibraryUSE master GOIF EXISTS (SELECT*FROM SYSDATABASES WHERE name='Library')DROP DATABASE LibraryCREATE DATABASE LibraryON PRIMARY(NAME ='Library_DATA',FiLENAME ='D:\project\Library_data.mdf',SIZE =5MB,MAXSIZE=50MB,FiLEGROWTH=15%)log on(NAME ='Library_log',FiLENAME ='D:\project\Library_log.ldf',SIZE =2MB,MAXSIZE=50MB,FiLEGROWTH=1MB)--创建表BOOKUSE LibraryGOCREATE TABLE Book(BID int NOT NULL,Bname Varchar(20) NOT NULL,Author Varchar(20) NULL,PubComp Varchar(20) null,pubDate Datetime ,Bcount int not null,Price money not null)--创建表Readeruse LibraryGOCREATE TABLE Readr(RID VARCHAR(20) NOT NULL,Rname VARCHAR(20) not null,LendNum int null,RADDRESS VARCHAR(50) NULL)--创建表Borrowuse LibraryGoCREATE TABLE Borrow(RID VARCHAR(20) NOT NULL,BID VARCHAR(20) NOT NULL,LendDate DateTime not null,WillDate DateTime null,ReturnDate DateTime null)--创建表Penaltyuse LibraryGoCREATE TABLE PENALTY(RID VARCHAR(20) NOT NULL,BID VARCHAR(20) NOT NULL,PDate DateTime not null,PType int null,Amount money )--Book表约束ALTER TABLE dbo.BookADD CONSTRAINT PK_BID PRIMARY KEY(BID)ALTER TABLE dbo.BookADD CONSTRAINT CK_BID CHECK (BID LIKE 'ISNM%')alter table dbo.Bookadd constraint CK_pubDate CHECK (pubDate<GETDATE())ALTER TABLE dbo.BookADD CONSTRAINT CK_Bcount CHECK (Bcount>=1)ALTER TABLE dbo.BookADD CONSTRAINT CK_Price CHECK (Price>0)--Reader表格约束ALTER TABLE dbo.ReadrADD CONSTRAINT PK_RID PRIMARY KEY(RID)ALTER TABLE LendNumADD CONSTRAINT CK_LendNum CHECK (LendNum>0)--Borrow表格约束 alter table Borrow add constraint PK_LendDate primary key(LendDate) alter table Borrow add constraint DF_LendDate default(GetDate()) for LendDate alter table Borrow add constraint CK_WillDate check(WillDate>LendDate) alter table Borrow add constraint DF_WillDate default(DateAdd(mm,1,getdate())) for WillDate alter table Borrow add constraint DF_ReturnDate default(null) for ReturnDate alter table Borrow add constraint FK_RID foreign key(RID) references Reader(RID) alter table Borrow add constraint FK_BID foreign key(BID) references Book(BID)--Penalty表格约束 alter table Penalty add constraint PK_PDate primary key(PDate) alter table Penalty add constraint DF_PDate default(GetDate()) for PDate alter table Penalty add constraint CK_PType check (1='延期' or 2='损坏' or 3='丢失') alter table Penalty add constraint FK_RID foreign key(RID) references Reader(RID) alter table Penalty add constraint FK_BID foreign key(BID) references Book(BID)
1 0
