优化SQLServer数据库设计

--删除数据库
drop database Myschool

use Myschool

--如果有就删除
if exists(select * from sysdatabases  where name='Myschool')
drop database Myschool

--执行DOS命令,前面的是获取权限，最后一句是执行dos
EXEC sp_configure 'show advancced options',1
go
reconfigure 
go
EXEC sp_configure 'xp_cmdshell',1
go
reconfigure
go
EXEC xp_cmdshell 'mkdir D:\project'

--添加约束
/*
alter table 表名
add constraint 约束名 约束类型 具体的约束说明
-*/
--添加主键约束--
alter table Grade
add constraint PK_GradeID primary key (GradeId)

--添加唯一约束(身份证)--
alter table Student
add constraint UQ_stuID unique (IdeentityCard)

--添加默认约束--
alter table Student 
add constraint DF_stuAddress default('地址不详') for Address

--添加检查约束（出生在1980年1.1之后）
alter table Student
add constraint CK_stuBornDate check(BornDate>='1980-01-01')

--添加外键约束
alter table Result
add constraint FK_stuNo foreign key(stuNo) references Student(stuNo)
  
go

---删除约束
/*
alter table 表名
drop constraint 约束名
*/
alter table Student
drop constraint DF_stuAddress 

--局部变量
declare @name varchar(8) 
set @name='李文才'
select StudentNo,studentName,BornDate,Address from Student
where StudentName=@name

--print语句和select 语句
/*
print 局部变量或字符串
select 局部变量 as 自定义列明
*/
print '服务器名称：'+@@servername   --文本方式显示
select @@SERVERNAME as '服务器名称'  --表格方式显示

--转换成字符串
print '当前错误号：'+Convert(varchar(50),@@error)  
print '当前错误号：'+Cast(@@error as varchar)

--子查询
begin--开始{

select * from Student
where BornDate>(select BornDate from Student where StudentName='李斯文')

end --结束}
go

--exists子查询(存在)--
if exists(
 select * from Result where SubjectId=(
  select SubjectId from Subject where SubjectName='java logic'
)and ExamDate =(
 select MAX(ExamDate) from Result where SubjectId=(
  select SubjectId from Subject where SubjectName='java logic')
  )and StudentResult>80
)
drop table Student  --举个例子不要这样做

---执行事务---------------------------------------------------
begin transaction  --开始事务
commit transaction  --提交事务
rollback transaction --撤销事务

--------开始事务--------------------------------------------
use SchoolDB
go
set nocount on --不显示受影响的行数信息
print '----查看转账事务前的余额----'
select * from bank 
go
begin transaction  --开始事务
declare @errorSum int     --设置局部变量
set @errorSum=0     --初始化
update bank set currentMoney-=1000 where customerName='张三'
set @errorSum+=@@ERROR   
update bank set currentMoney+=1000 where currentName='李四'
set @errorSum+=@@ERROR  --累计是否有错误

print '----查看转账事务过程中的余额----'
select * from Book
if @errorSum<>0
 begin
  print '交易失败，滚回事务'
  rollback transaction  --滚回
 end
else
 begin
  print '交易成功，提交事务，写入硬盘，永久保存'

  commit transaction  --提交事务 
 end 
go

print '----查看转账事务后的余额----'
select * from bank
go 
------------------------------------------------------------------

---视图（select语句不包括：<order by,into,临时表或变量>）-----------------------
--视图在表中则删除
if exists(select * from sysobjects where name='view_name')
drop view view_name  --删除视图
go
create view view_name as select * from  Student  --创建视图
go
select * from view_name  --查看视图

----索引(不推荐使用)-----

if exists(select * from sysindexes where name='IX_Table1_tableName')
drop index table1.IX_Table1_tableName --删除
go
create nonclustered index IX_Table1_tableName  --创建非聚集索引
 on table1(tableName) with fillfactor=30   --填充因子为30%
 go
 --查询
select * from table1 with(index=IX_Table1_tableName) where tableName like '小%'

-----查看索引--------
--1：用存储过程sp_helpIndex查看
exec sp_helpindex table1  --查看table1表的所有索引
--1：用sys.indexes查看
use Test3DB
select * from sys.indexes   --查看该数据所有索引

-------存储过程----
EXEC sp_databases  --所有数据库
EXEC sp_renamedb 'Test3DB','TestDB'  --改变数据库名称（单用户访问）
USE Test3DB
go
EXEC sp_tables                             -- 当前数据库中可查询对象的列表
EXEC sp_columns table1              --table1表中的列
EXEC sp_help table1                     --查看table1表的所有所有信息
EXEC sp_helpconstraint table1     --查看表中的约束
EXEC sp_helptext 'view_name'     --查看视图的语句文本
EXEC sp_stored_procedures        --库中存储过程列表