SQLServer 学习笔记之超详细基础SQL语句 Part 11

Sqlserver 学习笔记

by:授客 QQ：1033553122

-----------------------接Part 10-------------------

DECLARE @myavg float--定义平均分

SELECT @myavg=avg(writtenExam) FROM studentMars

print '本班平均分'+convert(VARCHAR(5),@myavg)

IF(@myavg>70)

    BEGIN

        PRINT '本班笔试成绩优秀，前三名的成绩是'

        SELECT TOP 3 * FROM studentMars order by writtenExam desc

    END

ELSE

    BEGIN

        PRINT '本班笔试成绩较差,后三门的成绩为'

        SELECT TOP 3 * FROM studentMars ORDER BY WrittenExam

END

 

结果：

 

 

2.WHILE循环语句

WHILE （条件）

BEGIN

语句1

语句2

……

BREAK

END

 

注意：

BREAK表示退出循环

如果有多条语句，才需要BEGIN-END语句块

实例

 

 

问题：

本次考试成绩较差，假定要提分，确保每人笔试都通过。提分规则很简单，先每人都加2分，看是否都通过，如果没有全部通过，每人再加2分，再看是否都通过，如此反复提分，直到所有人都通过为止 。

分析：

第一步，统计没通过的人数；

第二步，如果有人没通过，加分；

第三步，循环判断。

--WHILE 循环语句的使用

DECLARE @n INT

WHILE(1=1)--条件永远成立

BEGIN

    SELECT @n=COUNT(*) FROM studentMars

            WHERE writtenExam<</span>60--统计不及格人数

    IF(@n>0)

        UPDATE studentMars--每个人加两分

            SET writtenExam=writtenExam+2

    ELSE

        BREAK--退出循环

END

PRINT '加分后的成绩如下：'

SELECT * FROM studentMars

结果：

 

 

 

3.CASE－END多分支语句

CASE

  WHEN 条件1 THEN  结果1

  WHEN 条件2 THEN  结果2

  ……

  ELSE 其他结果

END

 

 

问题：

采用美国的ABCDE五级打分制来显示笔试成绩。

 A级:   90分以上

 B级： 80－89分

 C级:   70－79分

 D级： 60－69分

 E级： 60分以下

--CASE---END多分支语句

PRINT 'ABCDE五级显示成绩如下：'

 

SELECT stunNo,

    成绩=CASE

        WHEN writtenExam<</span>60 THEN 'E'

        WHEN writtenExam BETWEEN 60 AND 69 THEN 'D'

        WHEN writtenExam BETWEEN 70 AND 79 THEN 'C'

        WHEN writtenExam BETWEEN 80 AND 89 THEN 'B'

        END

FROM studentMars

 

 

 

11 EXISTS子查询的语法：

IF EXISTS （子查询）

语句

 

对应的有NOT EXISTS

IF NOT EXISTS （子查询）

    语句

 

1.  如果子查询的结果非空，即存在至少1条匹配记录数，则EXISTS （子查询）将返回真（true），否则返回假(false)

2.  EXISTS也可以作为WHERE 语句的子查询，但一般都能用IN子查询替换

 

 

问题：

检查本次考试，本班如果有人笔试成绩达到80分以上，则每人提2分；否则，每人允许提5分

 

分析：

是否有人笔试成绩达到80分以上，可以采用EXISTS检测

 

 

IF EXISTS(SELECT * FROM studentMars WHERE writtenExam>80)

    BEGIN

        PRINT '本班有人笔试成绩高于分，每人加分，加分后的成绩为：'

        UPDATE studentMars SET writtenExam=writtenExam+2

 

        SELECT * FROM studentMars

    END

ELSE

    BEGIN

        PRINT '本班无人笔试成绩高于分，没人可以加分，加分后的成绩是：'

        UPDATE studentMars SET writtenExam=writtenExam+5

        SELECT * FROM stuMarks

    END

GO

 

 

 

问题：

检查本次考试，本班如果没有一人通过考试（笔试和机试成绩都>60分），则试题偏难，每人加3分，否则，每人只加1分

分析：

没有一人通过考试，即不存在“笔试和机试成绩都>60分”，可以采用NOT EXISTS检测

IF NOT EXISTS (SELECT * FROM stuMarks WHERE                                 writtenExam>60 AND labExam>60)

  BEGIN

    print '本班无人通过考试，试题偏难，每人加3分，加分后的成绩为：'

    UPDATE stuMarks  SET writtenExam=writtenExam+3,labExam=labExam+3

    SELECT * FROM stuMarks

  END

ELSE

  BEGIN

    print '本班考试成绩一般，每人只加1分，加分后的成绩为：'

    UPDATE stuMarks SET writtenExam=writtenExam+1,labExam=labExam+1

    SELECT * FROM stuMarks

  END

GO

 

12 FIRST()函数

FIRST() 函数返回指定的字段中第一个记录的值。

 

语法

SELECT FIRST(column_name) FROM table_name

 

实例

 

我们拥有下面这个 "Orders" 表：

 

 

13 LAST() 函数

函数返回指定的字段中最后一个记录的值。

 

语法

SELECT LAST(column_name) FROM table_name

 

实例

我们拥有下面这个 "Orders" 表：

 

 

 

现在，我们希望查找 "OrderPrice" 列的最后一个值。我们使用如下 SQL 语句：

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

 

结果集类似这样：

 

 

 

14 SQL HAVING 子句

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

 

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。

 

语法

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value

 

实例

我们拥有下面这个 "Orders" 表：

 

 

现在，我们希望查找订单总金额少于 2000 的客户。我们使用如下 SQL 语句：

SELECT Customer,SUM(OrderPrice) FROM Orders

GROUP BY Customer

HAVING SUM(OrderPrice)<2000

 

结果集类似：

 

现在我们希望查找客户 "Bush" 或 "Adams" 拥有超过 1500 的订单总金额。

我们在 SQL 语句中增加了一个普通的 WHERE 子句：

SELECT Customer,SUM(OrderPrice) FROM Orders

WHERE Customer='Bush' OR Customer='Adams'

GROUP BY Customer

HAVING SUM(OrderPrice)>1500

 

结果集：

 

15 MID() 函数

MID 函数用于从文本字段中提取字符。

 

语法

SELECT MID(column_name,start[,length]) FROM table_name