文章类型： DATABASE
关键词： SQL,逻辑,编程
内容摘要：

SQL逻辑编程

2015/7/29 11:33:06    来源：apple    阅读：

主要包含以下内容：

1、 掌握如何定义变量并赋值

2、 掌握如何输出显示数据

3、  掌握IF、WHILE、CASE逻辑控制语句

本节内容用到的数据库的SQL脚本如下：

--创建MySchool数据库.sql
USE master  --设置当前数据库为master，以便访问sysdatabases表
GO
IF  EXISTS(SELECT * FROM  sysdatabases  WHERE  name ='MySchool')
DROP DATABASE MySchool
GO
 
/*--案例：使用SQL语句创建数据库MySchool具有一个数据文件和一个日志文件*/
CREATE DATABASE MySchool
ON  PRIMARY  --默认就属于PRIMARY主文件组，可省略
(
/*--数据文件的具体描述--*/
 NAME='MySchool_data',  --主数据文件的逻辑名称
 FILENAME='D:\project\MySchool_data.mdf',  --主数据文件的物理名称
 SIZE=10mb,  --主数据文件的初始大小
 MAXSIZE=100mb,  --主数据文件增长的最大值
 FILEGROWTH=15%   --主数据文件的增长率
)
LOG ON
(
  /*--日志文件的具体描述，各参数含义同上--*/
  NAME='MySchool_log',
  FILENAME='D:\project\MySchool_log.ldf',
  SIZE=3mb,
  MAXSIZE=20mb,  --日志文件增长的最大值
  FILEGROWTH=1mb
)
GO
 
use MySchool
Go
IF EXISTS(SELECT * FROM  sysobjects  WHERE  name='Student')
  DROP  TABLE  Student
GO
CREATE TABLE [dbo].[Student](
       [StudentNo] [int] NOT NULL,
       [LoginPwd] [nvarchar](50) NOT NULL,
       [StudentName] [nvarchar](50) NOT NULL,
       [Sex] bit NOT NULL,
       [GradeId] [int] NOT NULL,
       [Phone] [nvarchar](50) NULL,
       [Address] [nvarchar](255) NULL,
       [BornDate] [datetime] NOT NULL,
       [Email] [nvarchar](50) NULL,
       [IdentityCard] [varchar](18) NOT NULL
 )
 
IF EXISTS(SELECT * FROM  sysobjects  WHERE  name='Grade')
  DROP  TABLE  Grade
GO
CREATE TABLE [dbo].[Grade](
       [GradeId] [int] IDENTITY(1,1) NOT NULL,
       [GradeName] [nvarchar](50) NOT NULL
)
GO
 
ALTER TABLE Student --主键约束
ADD CONSTRAINT PK_StuNo PRIMARY KEY (StudentNo)
 
ALTER TABLE Student --唯一约束（身份证号唯一）
ADD CONSTRAINT UQ_stuID UNIQUE (IdentityCard)
 
ALTER TABLE Student --默认约束（地址不详）
ADD CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR Address
 
ALTER TABLE Student --检查约束（出生日期是自1980年1月1日以后）
ADD CONSTRAINT CK_stuBornDate CHECK(BornDate>='1980-1-1')
 
ALTER TABLE Grade
    ADD CONSTRAINT PK_GradeID PRIMARY KEY(GradeID)
 
ALTER TABLE Student --添加外键约束
ADD CONSTRAINT FK_Grade         
    FOREIGN KEY(GradeID) REFERENCES Grade(GradeID)
GO
 
IF EXISTS (SELECT * FROM  sysobjects  WHERE  name='Subject' )
    DROP  TABLE  Subject
GO
CREATE  TABLE Subject    --创建课程表
(
    SubjectNo int  IDENTITY(1,1)  NOT NULL,
    SubjectName nchar(50) NOT NULL,
    ClassHour int NOT NULL,
       GradeId int NOT NULL
)
GO
 
IF EXISTS (SELECT * FROM  sysobjects  WHERE  name=' Result' )
    DROP  TABLE Result
GO
CREATE  TABLE Result --创建成绩表
(
    StudentNo int NOT NULL,
       SubjectNo int NOT NULL,
       StudentResult int NOT NULL,
       ExamDate datetime NOT NULL
)
GO
 
ALTER TABLE Subject   --主键约束（课程编号）
   ADD CONSTRAINT PK_Subject PRIMARY KEY (SubjectNo)
 
ALTER TABLE Subject  --非空约束（课程名称）
   ADD CONSTRAINT CK_SubjectName CHECK (SubjectName is not null)
 
ALTER TABLE Subject WITH NOCHECK  --检查约束（学时必须大于等于0）
   ADD CONSTRAINT CK_ClassHour CHECK (ClassHour>=0)
 
ALTER TABLE Subject   --外键约束（主表Grade和从表Subject建立引用关系）
   ADD CONSTRAINT FK_GradeId         
      FOREIGN KEY (GradeId) REFERENCES Grade (GradeId)
GO
 
ALTER TABLE Result   --主键约束（学号、科目号、日期）
   ADD CONSTRAINT PK_Result PRIMARY KEY
      (StudentNo, SubjectNo, ExamDate)
 
ALTER TABLE Result   --默认约束（日期为系统当前日期）
   ADD CONSTRAINT CK_ExamDate DEFAULT (getdate()) FOR ExamDate
 
ALTER TABLE Result   --检查约束（分数不能大于100，小于0）
   ADD CONSTRAINT CK_StudentResult CHECK
      (StudentResult BETWEEN 0 AND 100)
 
ALTER TABLE Result   --外键约束（主表Student和从表Result建立关系）
   ADD CONSTRAINT FK_StudentNo         
      FOREIGN KEY (StudentNo) REFERENCES Student (StudentNo)
 
ALTER TABLE Result   --外键约束（主表Subject和从表Result建立关系）
   ADD CONSTRAINT FK_SubjectNo         
      FOREIGN KEY (SubjectNo) REFERENCES Subject (SubjectNo)
GO
 
--向Grade表插入数据
INSERT INTO Grade VALUES('S1')
INSERT INTO Grade VALUES('S2')
INSERT INTO Grade VALUES('Y2')
--向Subject表插入数据
INSERT INTO Subject VALUES('Winforms',20,1)
INSERT INTO Subject VALUES('Java Logic',16,1)
INSERT INTO Subject VALUES('SQL Base',18,1)
INSERT INTO Subject VALUES('HTML',32,1)
INSERT INTO Subject VALUES('Proc',38,1)
INSERT INTO Subject VALUES('Project',36,1)
INSERT INTO Subject VALUES('C# OOP',24,2)
INSERT INTO Subject VALUES('Three Tier',28,2)
INSERT INTO Subject VALUES('Java OOP',12,2)
INSERT INTO Subject VALUES('JSP',26,2)
INSERT INTO Subject VALUES('JavaScript',24,2)
INSERT INTO Subject VALUES('T-SQL',22,2)
INSERT INTO Subject VALUES('Project',42,2)
INSERT INTO Subject VALUES('ASP.NET',38,3)
INSERT INTO Subject VALUES('Control+Ajax',12,3)
--向Student表插入数据
INSERT INTO Student VALUES('10000','GuoJing','郭靖',1,1,02088762106,'天津市河西区','1987-09-08 00:00:00','GuoJing@sohu.com',111111)
INSERT INTO Student VALUES('10001','LiWenCai','李文才',1,1,01062768866,'地址不详','1993-03-04 00:00:00','LiWenCai@sohu.com',111112)
INSERT INTO Student VALUES('10002','LiSiWen','李斯文',1,1,02183410615,'河南洛阳','1992-08-08 00:00:00','LiSiWen@sohu.com',111113)
INSERT INTO Student VALUES('10011','WuSong','武松',0,1,01062768888,'地址不详','1984-12-31 00:00:00','WuSong@sohu.com',111114)
INSERT INTO Student VALUES('10012','HeQiang','何强',0,1,02183567890,'地址不详','1991-08-30 00:00:00','HeQiang@sohu.com',111120)
INSERT INTO Student VALUES('20011','ZhangSan','张三',1,1,15290234178,'北京市海淀区','1994-01-01 00:00:00','ZhangSan@sohu.com',111115)
INSERT INTO Student VALUES('20012','ZhangQiuYu','张秋丽',0,2,01062751414,'北京市东城区','1990-06-05 00:00:00','ZhangQiuYu@sohu.com',111116)
INSERT INTO Student VALUES('20015','XiaoMei','肖梅',0,2,031466557171,'河北省石家庄市','1996-10-01 00:00:00','XiaoMei@sohu.com',111117)
INSERT INTO Student VALUES('30021','OuYangJunXiong','欧阳俊雄',1,2,02277585000,'上海市卢湾区','1995-09-29 00:00:00','OuYangJunXiong@sohu.com',111118)
INSERT INTO Student VALUES('30023','MeiChaoFeng','梅超风',0,2,031466557172,'广州市天河区','1997-08-30 00:00:00','MeiChaoFeng@sohu.com',111119)
--向Result表插入数据
INSERT INTO Result VALUES('10001',2,70.6,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('10000',2,60,'2013-02-17 00:00:00')
INSERT INTO Result VALUES('10001',2,46,'2013-02-17 00:00:00')
INSERT INTO Result VALUES('10002',2,83,'2013-02-17 00:00:00')
INSERT INTO Result VALUES('10011',2,71,'2013-02-16 00:00:00')
INSERT INTO Result VALUES('10011',2,95.5,'2013-02-17 00:00:00')
INSERT INTO Result VALUES('10012',2,76,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('20011',2,60,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('20012',2,91,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('20012',7,61,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('20015',2,60,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('20015',7,65,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('30021',2,23,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('30021',8,74,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('30023',2,23,'2013-02-15 00:00:00')
INSERT INTO Result VALUES('30023',9,39,'2013-02-15 00:00:00')
--MySchool数据库的sql脚本结束

 

---------------正式内容开始-------------

²        变量分为：

*       局部变量：

*       局部变量必须以标记@作为前缀，如@age

*       局部变量的使用也是先声明，再赋值

*       全局变量：

*       全局变量必须以标记@@作为前缀，如@@version

*       全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值

Ø         局部变量的使用如下

ü         声明局部变量    

DECLARE   @变量名  数据类型

例如：

DECLARE @name varchar(8)

DECLARE @seat int

ü         赋值

SET @变量名 = 值

或

    SELECT  @变量名 = 值

例如：

SET @name = '张三'

SELECT @name = studentName FROM Student

        WHERE studentNo = '10011'

 

局部变量示例

举例练习：1、编写T-SQL查找李文才及他的相邻学号学生

/*--查找李文才的信息--*/
DECLARE @name varchar(8)                                      --学生姓名
SET @name = '李文才'                                                  --使用SET赋值
SELECT StudentNo, StudentName, BornDate, Address
 FROM Student
 WHERE StudentName = @name
/*--查找与李文才学好相邻的同学信息--*/
DECLARE @StudentNo int                                          --学号
SELECT @StudentNo = StudentNo FROM Student  --使用SELECT赋值
  WHERE StudentName = @name
SELECT StudentNo, StudentName, BornDate, Address
 FROM Student
 WHERE (StudentNo = @StudentNo+1) OR (StudentNo = @StudentNo-1)
GO

 

关于set和select在赋值时的区别列表比较如下：

比较项

SET

SELECT

同时对多个变量赋值

不支持

支持

表达式返回多个值时

出错

将返回的最后一个值赋给变量

表达式未返回值时

变量被赋NULL值

变量保持原值

 

例如：

DECLARE @addr nvarchar(100) , @name nvarchar(100)
SET @addr='', @name='张三'
SELECT @addr=‘北京’, @name=‘张三’
SET @addr = (SELECT Address FROM Student)
SELECT @addr = Address FROM Student             --最后一个Address列值
SET @addr = (SELECT Address FROM Student WHERE 1<0) --NULL值
SELECT @addr =  Address FROM Student WHERE 1<0          --保持原值

注意：对于select赋值，当select查询出多条结果时，只会将最后一个结果赋值给变量。此外，select除了可以变量赋值，还可以为表赋值，即一张表的数据由一条select数据的结果进行填充

 

Ø         全局变量：

全局变量都使用两个@标志作为前缀

变    量

含    义

@@ERROR

最后一个T-SQL错误的错误号

@@IDENTITY

最后一次插入的标识值

@@LANGUAGE

当前使用的语言的名称

@@MAX_CONNECTIONS

可以创建的同时连接的最大数目

@@ROWCOUNT      

受上一个SQL语句影响的行数

@@SERVERNAME

本地服务器的名称

@@TRANSCOUNT   

当前连接打开的事务数

@@VERSION     

SQL Server的版本信息

 

例如：获得数据库服务器的名称和SQLSERVER的版本信息？

PRINT  '服务器的名称: ' + @@SERVERNAME

PRINT  'SQL Server的版本' + @@VERSION

 

使用SELECT语句输出

SELECT @@SERVERNAME AS  '服务器的名称: '

SELECT @@VERSION AS 'SQL Server的版本'

 

每一条SQL语句执行后都会影响全局变量@@ERROR的值，即最近一条的SQL语句的执行会影响@@ERROR的值，当没有发生错误时，@@ERROR的值为0,；当发生错误时，返回最近一条语句执行失败时的错误号，即@@ERROR大于0的一个错误号

 

举例练习：2、查询学号是10000的学生参加2009年2月15日的“Java Logic”课程考试的成绩，要求输出学生姓名和成绩

答案略

 

Ø         T-SQL提供了两个转换函数

CAST(表达式 AS 数据类型)

例如：SELECT StudentName + '的出生日期是' +

          CAST(BornDate as varchar(50)) AS '学生信息'

FROM Student

CONVERT(数据类型，表达式，样式 )

例如：SELECT StudentName,

               CONVERT(varchar(50),BornDate,102) AS 出生日期

FROM Student

注：CONVERT()与CAST() 的不同点是：可以指定转换的样式

 

²        逻辑控制语句

*       分支结构

*       IF-ELSE语句

*       CASE-END语句

 

*       循环结构

*       WHILE语句

 

Ø         IF-ELSE语句

IF （条件）

  BEGIN

    语句1

    语句2

    … …

  END

ELSE

 BEGIN

    语句1

    语句2

    … …

  END

 

注：ELSE是可选部分；如果有多条语句，才需BEGIN-END语句块

 

举例练习：3、统计并显示2009-2-17的Java Logic考试平均分，

1）如果平均分在70以上，显示“考试成绩优秀”，并显示前三名学生的考试信息

2）如果在70以下，显示“考试成绩较差”，并显示后三名学生的考试信息

--参考答案

DECLARE @myavg decimal(5,2)    --平均分
… …                                                   --查询获得本次考试的平均分
IF (@myavg>70)
  BEGIN
    PRINT '考试成绩优秀，前三名的成绩为'
    SELECT TOP 3 StudentNo, StudentResult FROM Result
     ……
  END
ELSE
  BEGIN
    PRINT '考试成绩较差，后三名的成绩为'
    SELECT TOP 3 StudentNo, StudentResult FROM Result
       ……  
  END

 

Ø         WHILE循环语句

SQL中的WHILE语句

WHILE （条件）

  BEGIN

    语句1

    语句2

    ……

    BREAK

  END

注：BREAK表示跳出循环，也可以出现CONTINUE；如果有多条语句，才需要BEGIN-END语句块

举例练习：4、检查学生“Winforms”课最近一次考试是否有不及格（60分及格）的学生。如有，每人加2分，高于95分的学生不再加分，直至所有学生这次考试成绩均及格

参考答案：

--获得Winforms课程最近一次的考试时间和课程编号
DECLARE @date datetime  --考试时间
DECLARE @subNO int        --课程编号
SELECT  @subNo=SubjectNo FROM Subject
WHERE SubjectName='Winforms'
 
SELECT  @date=max(ExamDate) FROM Result
WHERE SubjectNo=@subNO
--利用WHILE语句为不及格的学生加分，直至全部学生都及格为止
DECLARE @n int                           --不及格人数
WHILE (1 = 1)                                  --条件永远成立
  BEGIN
    SELECT @n=COUNT(*) FROM Result
      WHERE SubjectNo=@subNO AND ExamDate=@date
           AND StudentResult < 60   --统计不及格人数
    IF (@n > 0)                                 --每人加2分
       UPDATE Result SET StudentResult=StudentResult+2 FROM Result
       WHERE SubjectNo=@subNO AND ExamDate=@date AND
                     StudentResult < 95
    ELSE
       BREAK                                    --退出循环
  END
--输出最近一次Winforms课程考试提分后的学生考试成绩
PRINT '加分后的成绩如下：'
SELECT StudentName,StudentResult FROM Result
  INNER JOIN Student ON Result.StudentNo=Student.StudentNo
  WHERE SubjectNo=@subNO AND ExamDate=@date

 

Ø         CASE-END多分支语句

CASE

  WHEN 条件1 THEN  结果1

  WHEN 条件2 THEN  结果2

  ……

  ELSE 其他结果

END

注：ELSE：表示CASE中所有WHEN条件均不为 TRUE 时返回的结果

如果省略ELSE且WHEN条件都为FALSE时，CASE语句返回NULL

 

举例练习：5、采用美国ABCDE五级打分制显示学生Java Logic课最近一次考试成绩

*       A级:   90分以上

*       B级： 80－89分

*       C级:   70－79分

*       D级： 60－69分

*       E级： 60分以下

参考答案：

DECLARE @date datetime                        --考试时间
……
SELECT 学号=StudentNo, 成绩=
CASE
  WHEN StudentResult < 60 THEN 'E'
  WHEN StudentResult BETWEEN 60 AND 69 THEN 'D'
  WHEN StudentResult BETWEEN 70 AND 79 THEN 'C'
  WHEN StudentResult BETWEEN 80 AND 89 THEN 'B'
  ELSE 'A'
END 
  FROM Result

 

本节总结：

l         变量赋值时可以采用SET语句和SELECT语句

l         输出语句可以使用PRINT语句和SELECT语句

l         使用CAST()函数和CONVERT()函数实现数据类型的转换

l         掌握IF、WHILE、CASE逻辑控制语句

↑ 上一篇文章：虚拟目录是什么作用?如何配置IIS设置虚拟目录 关键词：虚拟目录,什么作用,如何,配置,IIS,设置,虚拟目录 发布日期：2015/7/29 11:31:52
↓ 下一篇文章：一个基于分页的复杂SQL查询语句(一) 关键词：复杂,SQL,查询,子查询,in,join,union 发布日期：2015/7/29 11:34:41
相关文章：
探讨SQL查询优化 关键词：SQL,Server,查询优化,SARG,SQL编程优化,索引结构分析 发布日期：2015-07-14 16:06
ASP编程有用的例子 关键词：ASP,编程技巧,mappath,access,ip,随机图像,sql 发布日期：2015-07-29 15:14
Java C# 编程逻辑训练题 关键词：Java,C#,C,Sharp,Java,java,编程逻辑,训练题,if,switch,whi.. 发布日期：2015-07-22 16:32
相关目录：.NET，DATABASE，软件开发，ANDROID
我要评论
正在加载评论信息......