SQL逻辑判断(begin end)
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‘ ) --批处理 go use stuDB select * from sysdatabases select * from sysobjects 创建约束 constraint 主键 primary key 唯一约束 unique 检查约束 check 莫认约束 default 外键约束 foreign 引用 references 创建约束 alter table 表名 add constraint 为约束起名字 删除约束 alter table 表名 drop constraint 约束名 use stuDB drop table stuInfo drop table stuMarks create 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 ) go create table stuMarks( examNo char(7), stuNo char(6) not null, writtwenExam int not null, labeExam int not null ) go alter 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 stuAddress alter 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 stuInfo declare @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-1 set nocount on --不显示影响的行数 print @@error select * from stuInfo --常用的全局变量 print ‘最后一个T-SQL错误的错误号:‘+@@error print ‘最后一次插入的标识值‘+ @@identity print ‘本系统的版本号为:‘+@@version print ‘本地服务器的名称:‘+@@servername --不常用的全局变量 print ‘当前使用的语言的名称:‘+@@language print ‘可以创建的同时连接的最大数目:‘+@@max_connections print ‘受上一个SQL语句影响的行数:‘+@@rowcount print ‘当前连接打开的事物数:‘+@@trancount insert 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 stuMarks select * from stuInfo declare @avg int select @avg=avg(writtwenExam) from stuMarks print ‘本班成绩为:‘+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; end end select * 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 end end --采用美国的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‘ end from 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 ‘优‘ end from stuMarks
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。