SQL复习(数据库的设计,数据库的实现,高级查询,存储过程,事务处理,视图,索引,触发器)

SQL复习
------数据库的设计 ------数据库的实现 ------高级查询 ------存储过程 ------事务处理 ------视图,索引 ------触发器
创建新文件夹
--启用外围配置 exec sp_configure 'show advanced options',1 reconfigure exec sp_configure 'xp_cmdshell',1 reconfigure --调用dos命令创建文件夹 --在F盘创建'新文件夹'文件夹 exec xp_cmdshell 'md F:\新文件夹',no_output
为表ReaderInfo添加约束 alter table ReaderInfo add constraint PK_ReaderId primary key(ReaderId), --主键约束 constraint DF_Sex default(0) for Sex, --默认约束 constraint DF_ReaderCount default(0) for ReaderCount, constraint DF_IsLoss default(0) for IsLoss, constraint DF_RegisterCount default getdate() for RegisterCount, constraint UQ_ReaderNo unique(ReaderNo) --唯一约束 为表ReaderInfo插入语句 insert into ReaderInfo(ReaderNo,ReaderName) values('023-001','张三') insert into ReaderInfo(ReaderNo,ReaderName) values('023-002','李四') insert into ReaderInfo(ReaderNo,ReaderName) values('023-003','王五') insert into ReaderInfo(ReaderNo,ReaderName) values('023-004','赵六') insert into ReaderInfo(ReaderNo,ReaderName) values('023-005','田七')
创建BorrowInfo(图书借阅表) if exists (select 1 from sys.objects where [name]='BorrowInfo') drop table BorrowInfo create table BorrowInfo ( BorrowId int identity, --借阅编号 BookId int not null, --图书编号 ReaderId int not null, --读者编号 BorrowTime datetime, --借阅时间 ShouldTime datetime, --应还时间 MortgageMoney money, --押金 BorrowState int --借阅状态 0:新借 1:未还 2:已还 ) 为表BorrowInfo创建约束 alter table BorrowInfo add constraint PK_BorrowId primary key(BorrowId), constraint FK_BorrowInfo_BookId foreign key(BookId) references BookInfo(BookId), constraint FK_BorrowInfo_ReaderId foreign key(ReaderId) references ReaderInfo(ReaderId), constraint DF_BorrowState default(0) for BorrowState
创建BookInfo(图书信息表) if exists (select 1 from sys.objects where [name]='BookInfo') drop table BookInfo create table BookInfo ( BookId int identity(1,1), --图书编号 BookName varchar(50) not null, --图书名称 TypeId int not null, --图书类型编号 Author varchar(50), --作者 Press varchar(50), --出版社 PressDate datetime, --出版日期 Price money, --价钱 Page int, --页数 NowNum int, --现存量 BookNum int, --库存总量 AddDate datetime, --入库时间 ByCount int, --借出次数 IsOff int, --是否注销 0:没有注销 1:注销 Remark varchar(50) --图书简介 )
exec sp_helpconstraint 'BackInfo' --查看表BackInfo的所有约束 删除表的外键约束 alter table BookInfo drop constraint FK_BookInfo_TypeId alter table BorrowInfo drop constraint FK_BorrowInfo_BookId alter table BorrowInfo drop constraint FK_BorrowInfo_ReaderId alter table BackInfo drop constraint FK_BackInfo_BookId alter table BackInfo drop constraint FK_BackInfo_ReaderId
创建BookType(图书类型表) if exists (select 1 from sys.objects where [name]='BookTYpe') drop table BookType create table BooKType ( TypeId int identity(1,1), --图书类型编号 TypeName varchar(50) not null, --图书类型名称 Remark varchar(50) --备注信息 ) 为表BookType添加约束 alter table BookType add constraint PK_TypeId primary key(TypeId) 为表BookType插入信息 insert into BookType values ('主科','语数英') insert into BooKType values ('理科','化物生') insert into BooKType values ('文科','史地政')
创建数据库
if exists (select 1 from sys.databases where [name]='libraryDB2' ) drop database libraryDB2 --判断数据库libraryDB2是否存在 create database libraryDB2 on ( name=libraryDB2_mdf, filename='F:\新文件夹\libraryDB2_mdf.mdf', size=3mb, maxsize=100mb, filegrowth=1mb --超出maxsize时,自动增长的大小 ) log on --日志文件 ( name=libraryDB2_ldf, filename='F:\新文件夹\libraryDB2_ldf.ldf', size=3mb, maxsize=100mb, filegrowth=1mb )
创建表ReaderInfo(读者图书表) if exists (select 1 from sys.objects where [name]='ReaderInfo') drop table ReaderInfo --判断表ReaderInfo是否存在 create table ReaderInfo ( ReaderId int identity(1,1), -读者编号 ReaderNo varchar(50) not null, --借书证号 ReaderName varchar(50) not null, --姓名 Sex int, --性别 0:男 1:女 BirthDate datetime, --出生日期 Phone varchar(50), --联系电话 NumberID varchar(50), --身份证号码 [Address] varchar(50), --家庭地址 --[]关键字 RegisterCount datetime, --登记日期 Remark varchar(50), --备注 ReaderCount int, --借阅次数 Isloss int --是否挂失,0:没有挂失,1:挂失 )
为表BookInfo创建约束 alter table BookInfo add constraint PK_BookId primary key(BookId), constraint Fk_BookInfo_TypeId foreign key(TypeId) references BookType(TypeId), --外键约束 constraint DF_ByCount default(0) for ByCount, constraint DF_IsOff default(0) for IsOff 为表BookInfo插入信息 insert into BookInfo (BookName,TypeId) values ('语文',1) insert into BookInfo (BookName,TypeId) values ('数学',1) insert into BookInfo (BookName,TypeId) values ('英语',1) insert into BookInfo (BookName,TypeId) values ('化学',2) insert into BookInfo (BookName,TypeId) values ('物理',2) insert into BookInfo (BookName,TypeId) values ('生物',2) insert into BookInfo (BookName,TypeId) values ('历史',3) insert into BookInfo (BookName,TypeId) values ('地理',3) insert into BookInfo (BookName,TypeId) values ('政治',3)
创建BackInfo(图书归还表) if exists (select 1 from sys.objects where [name]='BackInfo') drop table BackInfo create table BackInfo ( BackId int identity, --借阅编号 BookId int not来自百度文库null, --图书编号 ReaderId int not null, --读者编号 BackMoney money, --退还押金 EndTime datetime, --归还时间 IsBack int --确认归还 0:归还 1:未归还 ) 为表BackInfo创建约束 alter table BackInfo add constraint PK_BackId primary key(BackId), constraint FK_BackInfo_BookId foreign key(BookId) references BookInfo(BookId), constraint FK_Backinfo_ReaderId foreign key(ReaderId) references ReaderInfo(ReaderId), constraint DF_IsBack default(0) for IsBack