数据库从入门到精通

大型数据库复习 1.创建数据库

create database 教师管理 create table Teachers( TID varchar(50) not null, TName varchar(50) null, TGender varchar(50) null, TTitle varchar(50) null, TSalary int null, TDeptID varchar(50) null, primary key(TID) ) create table Departments( DeptID varchar(50) not null, DeptName varchar(50) null, DeptSuperID varchar(50) null, PRIMARY KEY(DeptID) )

2.插入记录

INSERT INTO DEPARTMENTS VALUES ('1','Computer','J001') INSERT INTO DEPARTMENTS VALUES ('2','English','W001') INSERT INTO DEPARTMENTS VALUES ('3','Economy','JG001') INSERT INTO TEACHERS VALUES('J001','张林','男','教授',5500,'1') INSERT INTO TEACHERS VALUES('J002','赵强','男','副教授',5000,'1') INSERT INTO TEACHERS VALUES('J003','陈伟','男','教授',5600,'1') INSERT INTO TEACHERS VALUES('J004','张婷婷','女','讲师',4000,'1') INSERT INTO TEACHERS VALUES('JG001','吕楠','女','教授',5200,'3') INSERT INTO TEACHERS VALUES('JG002','孙新林','男','讲师',3800,'3') INSERT INTO TEACHERS VALUES('W001','卓华','男','教授',5800,'2') INSERT INTO TEACHERS VALUES('W002','谢世杰','男','讲师',3800,'2') INSERT INTO TEACHERS VALUES('W003','陈芳','女','助教',3000,'2')

3.数据库操作、事务

select *from Teachers select *from Departments //添加外键 alter table Departments add constraint con_1 foreign key(deptsuperid) references Teachers(TID) alter table Teachers add constraint con_2 foreign key(TDeptID) references Departments(DeptID) select *from Teachers select *from Departments //显式事务 try…catch回滚 go begin try begin transaction insert into teachers values('2015','蛊','男',null,null,null) insert into departments values('4','Petroleum','123456') commit tran end try begin catch rollback transaction end catch select *from Teachers select *from Departments go //显式事务@@error回滚 go declare @del_error int,@ins_error int begin transaction insert into Teachers(tid,tname,tgender) values ('2015','更好','男') select @del_error =@@ERROR insert into Departments (DeptID ,DeptName ,DeptSuperID )values('4','Petroleum','123456') select @ins_error =@@ERROR if @del_error =1 and @ins_error =1 begin commit tran end else begin rollback tran end go select *from Departments //删除主键和约束 alter table departments drop constraint con_1 alter table teachers drop constraint PK__Teachers__C456D7297F60ED59 alter table teachers drop constraint con_2 alter table departments drop constraint PK__Departme__0148818E03317E3D

4.索引、视图、触发器等

go create unique clustered index ix_TID on teachers(TID) with (fillfactor=70) go sp_helpindex 'teachers' //创建视图 instead of触发器 go create view Teachers_Dept5 as select tid,Tname,Tdeptid,deptname from Teachers,Departments go create trigger t5 on Teachers_Dept instead of insert as declare @tid1 varchar(50),@Tname1 varchar(50),@Tdeptid1 varchar(50),@deptname1 varchar(50) select @tid1=tid,@Tname1=Tname,@Tdeptid1=Tdeptid,@deptname1=deptname from inserted insert Teachers(tid,tname,Tdeptid)values('P001','李华斌','5') insert departments(deptid,deptname)values('5','Production') select * from Teachers_Dept select * from departments //添加列，游标计算 alter table departments add DeptCount int null declare @sum int,@Tdeptid varchar(30) declare num cursor for select Tdeptid from Teachers join Departments on TDeptID=DeptID open num fetch next from num into @Tdeptid while(@@FETCH_STATUS=0) begin select @sum=COUNT(*) from Teachers join Departments on TDeptID=DeptID where TDeptID=@Tdeptid update Departments set DeptCount=@sum where current of num fetch next from num into @Tdeptid end close num deallocate num select * from Departments

5.DBCC分析方法 dbcc traceon(3604) dbcc extentinfo(教师管理,departments) dbcc page(教师管理,1,21,1) 30000800 04000000 04000003 0014001c 00200031 436f6d70 75746572 4a303031 30:状态位，表示变长 0800：表示找到字段数的位置 04000000：代表第四个字段的值为4 0400：表示字段数 03：null位图 1400，1c00：表示第2变长个字段，第3个变长字段结束位置 436f6d70 75746572 4a30：代表computer 30为随机数 4a303031：表示字段三数据

6.自定义函数方法、存储过程、触发器等

go create function demo7(@name varchar(30)) returns int as begin declare @sum int select @sum=count(*) from Teachers join Departments on TDeptID=DeptID where DeptName=@name and TGender='女' return (@sum) end go print dbo.demo7('Computer') //存储过程 go create proc num1(@deptid varchar(30),@aversal int output) as begin if not exists(select * from Departments where DeptName=@deptid) print '该部门不存在' else select AVG(TSalary) from Teachers join Departments on TDeptID=DeptID where DeptName=@deptid end declare @sal int exec num1 'English',@sal output print @sal go //insert触发器等等 go create trigger t_name on Teachers for insert(update,delete) as declare @avgsalary int select @avgsalary = AVG(TSalary) from Teachers t join Departments d on t.TDeptID=d.DeptID group by d.DeptName update Departments set DeptAvgSalary = @avgsalary select DeptAvgSalary from Departments where DeptName = 'Computer' insert into Teachers (TID,TName,Tsalary) values('10086','张老师',5000) select DeptAvgSalary from Departments where DeptName = 'Computer' go