use master--创建文件夹exec xp_cmdshell ‘md d:project‘exec xp_cmdshell ‘dir d:‘--判断数据库是否存在if exists(select * from sysdatabases where name=‘stuDB‘)--如果存在先删除drop database stuDB--创建数据库create database stuDB--创建主数据库文件  on primary( name=‘stuDB_data_1‘, filename=‘d:\project\stuDB_data_1.mdf‘, size=5mb, maxsize=100mb, filegrowth=10%),--创建次要数据库文件( name=‘stuDB_data_2‘, filename=‘d:\project\stuDB_data_2.ndf‘, size=5mb, maxsize=100mb, filegrowth=10% )--创建日志文件  log on( name=‘stuDB_log_1‘, filename=‘d:\project\stuDB_log_1.ldf‘),  --创建其他日志文件( name=‘stuDB_log_2‘, filename=‘d:\project\stuDB_log_2.ldf‘)--批处理gouse stuDBselect * from sysdatabasesselect * from sysobjects创建约束 constraint主键 primary key唯一约束 unique检查约束 check莫认约束 default外键约束 foreign引用  references创建约束 alter table 表名 add constraint 为约束起名字删除约束 alter table 表名 drop constraint 约束名use stuDBdrop table stuInfodrop table stuMarkscreate table stuInfo( stuName varchar(20) not null, stuNo char(6) not null, stuSex char(2) not null, stuAge int not null,  stuSeat int identity (1,1), stuAddress varchar(50) not null)gocreate table stuMarks( examNo char(7), stuNo char(6) not null, writtwenExam int not null, labeExam int not null)goalter table stuInfo --主键约束 add constraint PK_stuNo primary key (stuNo)alter table stuInfo --唯一约束 add constraint UK_stuId unique (stuId)alter table stuInfo --默认约束 add constraint DK_stuAddress default(‘地址不详‘) for stuAddressalter table stuInfo --检查约束 add constraint CK_stuAge check (stuAge between 15 and 40)alter table stuMarks--外键约束 add constraint FK_stuNo foreign key (stuNo)references stuInfo(stuNo)alter table stuMarks --检查约束 add constraint CK_writtwenExam check(writtwenExam between 0 and 100)alter table stuMarks--检查约束 add constraint CK_labeExam check(labeExam between 0 and 120)alter table stuMarks--删除约束 drop constraint FK_stuNo insert into stuInfo values(‘张秋丽‘,‘s25301‘,‘男‘,18,‘北京海淀‘)insert into stuInfo values(‘李文才‘,‘s25302‘,‘男‘,28,default)insert into stuInfo values(‘李斯文‘,‘s25303‘,‘女‘,22,default)insert into stuInfo values(‘欧阳俊雄‘,‘s25304‘,‘女‘,34,default)insert into stuInfo values(‘梅超风‘,‘s25318‘,‘女‘,23,default)select * from stuInfodeclare @name varchar(20)--定义一个局部变量set @name=‘李文才‘--为变量赋值declare @seat int--定义个变量select @seat=stuSeat from stuInfo where stuName=@name--查找stuName的编号并且为stuSeat赋值select * from stuInfo where (stuSeat=@seat+1)or(stuSeat=@seat-1)--查询姓名并且stuSeat+1 or stuSeat-1set nocount on --不显示影响的行数print @@errorselect * from stuInfo--常用的全局变量print ‘最后一个T-SQL错误的错误号:‘+@@errorprint ‘最后一次插入的标识值‘+ @@identityprint ‘本系统的版本号为:‘+@@versionprint ‘本地服务器的名称:‘+@@servername--不常用的全局变量print ‘当前使用的语言的名称:‘+@@languageprint ‘可以创建的同时连接的最大数目:‘+@@max_connectionsprint ‘受上一个SQL语句影响的行数:‘+@@rowcountprint ‘当前连接打开的事物数:‘+@@trancountinsert into stuMarks values(‘s271811‘,‘s25303‘,80,58)insert into stuMarks values(‘s271813‘,‘s25302‘,50,90)insert into stuMarks values(‘s271816‘,‘s25301‘,77,82)insert into stuMarks values(‘s271818‘,‘s25318‘,45,65)select * from stuMarksselect * from stuInfodeclare @avg intselect @avg=avg(writtwenExam) from stuMarksprint ‘本班成绩为:‘+convert(varchar(4),@avg)if(@avg<70) begin  print ‘本班成绩较差:‘         select top 3 * from stuMarks order by writtwenExam end    else begin  print ‘成绩优秀:‘   select top 3 * from stuMarks order by writtwenExam desc end--先统计未及格人数while(1=1)  begin declare @noopass int select @noopass=count(*) from stuMarks where writtwenExam<60 if(@noopass>0)  begin   update stuMarks set writtwenExam=writtwenExam+2  end else  begin  break; endendselect * from stuMarks--笔试成绩有两个成绩未及格的状态while(1=1) begin  declare @noopass int  select @noopass=count(*) from stuMarks where writtwenExam<60 if(@noopass=2)  begin  break;     end else  begin  update stuMarks set writtwenExam=writtwenExam-2 endend--采用美国的ABCDE等级来评定(笔试成绩)select *, 成绩=case when writtwenExam<60 then ‘E‘ when writtwenExam between 61 and 70 then ‘D‘ when writtwenExam between 71 and 80  then ‘C‘ when writtwenExam between 81 and 90 then ‘B‘ else  ‘A‘endfrom stuMarks--增加一列平均分并采用美国的ABCDE等级来评定(笔试成绩)select *,平均成绩=(writtwenExam+labeExam)/2, 等级=case when writtwenExam<60 then ‘差‘ when writtwenExam between 61 and 70 then ‘一般‘ when writtwenExam between 71 and 80  then ‘中‘ when writtwenExam between 81 and 90 then ‘良‘ else  ‘优‘endfrom stuMarks