SQL Server语法规则及示例

丘丘人

于 2024-06-17 17:14:30 发布

阅读量713

点赞数 27

文章标签： sqlserver

本文链接：https://blog.csdn.net/weixin_45579165/article/details/139749391

版权

增删改查

查询

将查询结果保存为新表

在SELECT子句的后面，FROM子句的前面加了一个“INTO”关键字，关键字的后面紧跟用于保存查询结果的新表的名字。

SELECT  *（或字段列表）
INTO   新表名
FROM  table_source
……

连接字段

【例9】从stu_info表中，查询所有学生的姓名和系别，并将这两个字段连接为一个字段。

SELECT  sname+depart  AS  姓名及来源地
FROM   stu_info

（2）姓名和来源地之间的距离太大，应当缩小距离。

RTRIM函数去除字段值右侧的空格

SELECT  RTRIM(sname)+RTRIM(depart)  AS  姓名及来源地
FROM   stu_info

(3）应当将来源地放进括号内，与姓名隔开。

SELECT  RTRIM(sname)+'('+RTRIM(depart)+')'  AS  姓名及来源地
FROM   stu_info

根据条件查询数值数据（WHERE）

SELECT   *
FROM     stu_info
WHERE   birth > ’ 01/20/1977’

between and

SELECT  sname 姓名,birth 出生日期,depart 所属院系
FROM    stu_info
WHERE   birth  BETWEEN ’ 01/01/1977’ AND ’ 12/31/1979’

IS NULL / IS NOT NULL

SELECT  *
FROM    stu_info
WHERE   telephone  IS NULL

group by

设置排序方向

在ORDER BY子句中使用ASC关键字指定升序，使用DESC关键字指定降序。如果没有使用关键字，则默认排序方式是升序。

按字段位置排序

在实际应用中，有时也需要按字段位置排序。因为SELECT关键字后并非都是字段名，也可能是表达式。如果希望按表达式的值排序，而又没有给表达式取别名，则可以按字段位置排序。

SELECT     sno 学号,sname 姓名,DATEDIFF(year, birth, GETDATE( )) 年龄
FROM      stu_info
ORDER BY  3  DESC

SELECT     sno 学号,sname 姓名,DATEDIFF(year, birth, GETDATE( )) 年龄
FROM      stu_info
ORDER BY  DATEDIFF(year, birth, GETDATE( ))  DESC

TOP n

查询前5行数据

在SQL Server中，使用关键字TOP可以轻松完成这一任务。TOP关键字可以限制返回到结果集中的记录个数

SELECT     TOP 5 sname 姓名,birth 生日,telephone 手机号码
FROM      stu_info
ORDER BY  birth

TOP关键字除了上述用法以外，还有一种用法：

TOP n PERCENT

其含义为从顶部开始获取结果集的百分之N。

例如，下面的语句查询stu_info表中以出生日期排序后，前30%的学生信息。

SELECT  TOP 30 PERCENT sname 姓名,birth 生日,telephone 手机号码
FROM   stu_info
ORDER BY  birth

WHERE与ORDER BY的结合使用
ORDER BY子句必须放在WHERE子句的后面，作用是排序满足查询条件的查询结果集。。

SELECT  sno 学号,sname 姓名, telephone 手机号码,depart 所属院系
FROM    stu_info
WHERE   telephone IS NOT NULL
ORDER BY 学号

AND与OR的优先顺序问题
WHERE子句中可以包含任意数量的AND和OR运算符，并且允许两者结合使用。

SELECT  *
FROM    stu_info
WHERE   depart = '中文系'
OR      depart = '外语系'
AND     sex = '女'
ORDER BY sno

查看运行结果后会发现一个男生进入了查询结果集中。导致这一错误的根源是运算符的优先级问题。在表达式中，如果同时出现了AND和OR两种运算符，则并非从左到右按顺序运算，而是优先执行AND，然后执行OR运算符。
了解了运算符的优先级后，上面错误的原因就很容易地被找到了。因为上面的条件表达式与下面的表达式等价。
所属院系=‘中文系’ OR (所属院系=‘外语系’ AND性别=‘女’)

NOT运算符

NOT运算符的作用是对其后的表达式求反。

SELECT  *
FROM   stu_info
WHERE  birth NOT BETWEEN  ‘01/01/1978’ AND ‘12/31/1980’

IN运算符

IN运算符的运算规则是：当X在集合{Value1, Value2,……ValueN}中时，表达式X IN (Value1, Value2,……ValueN)为True，而X不在集合{Value1, Value2,……ValueN}中时，上面的表达式为False。

SELECT  *
FROM    course
WHERE   credit IN (2,3,4)
ORDER BY credit DESC,cno

说明：在IN运算符表达式中，集合必须用圆括号括住，并且各元素之间用逗号（,）分隔。

IN运算符还有一个反向运算符——NOT IN。

LIKE运算符与“%”通配符

模糊查询，下面主要介绍LIKE运算符和“%”通配符结合使用。
在SQL Server中，“%”通配符代表0个或多个字符。

SELECT  *
FROM   stu_info
WHERE  RTRIM(sname) LIKE  '%三'

说明：RTRIM函数用于将字符串右侧的空格去掉，在本例中是将sname字段值右侧的空格去掉。由于，在表结构的设计中sname字段的类型是nchar，宽度是20，所以其值的宽度不满20个字符时，SQL Server自动用空格填满了剩余位置，因此，本例中需要使用RTRIM函数将右侧的空格去掉。

“_”通配符的使用

下画线（）通配符，它只代表任意一个字符。例如，“刘”代表以“刘”字开头的，最多由两个汉字组成的字符串。

SELECT  *
FROM   stu_info
WHERE  RTRIM(sname) LIKE  '刘_'

“_”通配符也可以不与字符组合，而单独使用。

SELECT  *
FROM   stu_info
WHERE  RTRIM(sname) LIKE  '__'

“[ ]”通配符的使用

从stu_info表中，查询姓张、李或刘的所有学生，并按姓名升序排序。

SELECT  *
FROM   stu_info
WHERE  sname LIKE  '[张李刘]%'
ORDER BY sname

“^”的使用

如果在方括号内的第一个位置输入符号*“^”**，则表示取反向值。*

从stu_info表中，查询除姓张、李或刘以外的所有学生，并按姓名升序排序。

SELECT  *
FROM   stu_info
WHERE  sname LIKE  '[^张李刘]%'
ORDER BY sname

定义转义字符（ESCAPE关键字）

如果想要查询最后两个字符为百分之五的所有字符串，即将“%5%”中，第二个“%”视为是普通字符，而不是通配符，此时，便应该定义和使用转义字符。在SQL Server中，使用ESCAPE关键字定义转义字符。

例如，要查询最后两个字符为百分之五（5%）的所有字符串，其LIKE语句为：

LIKE  '%5#%'  ESCAPE  '#'

其中，ESCAPE '#'定义了转义字符“#”，它表示紧跟着“#”后的“%”为普通字符，而并非通配符。
注意：只有紧跟在转义字符后面的通配符才被视为转义字符，例如，如果上面的LIKE语句为

LIKE  '%5#%%'  ESCAPE  '#'
则表示要查询的是包含百分之五（5%）的所有字符串。这里最后一个“%”仍当做通配符来使用，只有紧跟着“#”的“%”（第二个）才被当做普通字符。

联表查询

1、普通联表查询

（1）使用无连接规则连接两表（不设置WHERE子句）

所谓无连接规则连接，就是指连接两表的SELECT语句中不设置任何连接条件，这样得到的连接结果是第一个表中的每一行都会和第二个表中的所有行进行连接，即得到一个笛卡尔积。

SELECT  *(或字段列表)
FROM  表名1,表名2

(2)使用有连接规则连接两表（设置WHERE子句）

SELECT  *
FROM   T1,T2
WHERE  T1.职工号=T2.职工号

这种使用等于号组成的连接，实际上叫等值连接。需要说明的一点是，只有两表有共同的字段时才可以使用等值连接。
技巧：在多表连接时，即使不要求在表独有的字段前加表名，但笔者还是建议加上表名，因为这样可以很清楚地表示哪个字段属于哪个表，这将对以后的维护起到很好的作用。

(3)使用多表连接查询数据

分析：在上一题中，已经知道了stu_info和score表可以用共同拥有的学号字段进行连接。接下来的问题是将course表连接到上述两个表上。由于stu_info表和course表没有共同字段，所以不能连接，但是score表和course表有共同字段——课号（cno），因此score表和course表可以连接，如此经过score表的搭桥，上述三个表就可以连接了。

SELECT  stu_info.sno,stu_info.sname,course.cname,score.usually,score.exam
FROM   stu_info,score,course
WHERE  stu_info.sname='张三'
AND    stu_info.sno=score.sno
AND    score.cno=course.cno
ORDER BY score.exam DESC,score.usually DESC

（4）使用表别名简化语句

用表别名不仅可以简化SQL语句，还可以在单条查询语句中多次使用同一个表，这对于自连接查询是非常重要的前提条件。
说明：与设置字段别名相同，设置表别名时，可以省略“AS”关键字。

（5）使用INNER JOIN连接查询

SELECT  *(或字段列表)
FROM  表名1
INNER JOIN 表名2
ON  连接规则1
INNER JOIN 表名3
ON  连接规则2
……
INNER JOIN表名n
ON  连接规则n


SELECT  st.sno, st.sname, st.depart, s.usually, s.exam
FROM   score AS s
        INNER JOIN course AS c
        ON s.cno=c.cno
        INNER JOIN stu_info AS st
        ON st.sno= s.sno
WHERE  c.cname='心理学'
ORDER BY s.exam DESC

说明：使用INNER JOIN的连接，通常被人们称为内部连接或内连接。

（6）连接查询实例

2、高级连接查询

**（1）自连接查询：**表自身与自身进行连接。

从stu_info表中，查询“张三”所在院系的所有学生的信息。
分析：按照以前所学的知识，完成本例的查询任务需要两次查询，首先查询“张三”所在的院系，其次才能查询属于该院系的所有学生的信息。

（1）查询“张三”所在的院系名称。
SELECT  depart
FROM  stu_info
WHERE  sname='张三'
 
（2）根据上面的查询，知道了“张三”在中文系学习，下面查询“中文系”所有学生的信息。
SELECT  *
FROM  stu_info
WHERE depart='中文系'

遇到类似本例的查询任务，应当首选自连接查询。因为自连接查询可以用一条SELECT语句完成本例的查询任务。

SELECT  st1.*
FROM  stu_info AS st1, stu_info AS st2
WHERE  st1.depart = st2.depart
AND   st2.sname = '张三'

上面SELECT子句中“st1.*”的意思是，要显示st1表的所有字段，如果将其改为“*”，则会显示st1和st2表的所有字段

（2）内连接查询

内连接包括等值连接、自然连接和不等值连接三种。内连接最大的特点是只返回两个表中互相匹配的记录，而那些不能匹配的记录就被自动去除了。
1．等值连接，连接规则由等于号（=）组合而成
2．自然连接，不将相同的字段显示两次，即在SELECT子句中列出需要显示的字段列表。
3．不等值连接，连接规则由等于号以外的运算符组成，例如，由>、>=、<、<=、<>或BETWEEN等。
下面通过一个示例介绍不等值连接的使用方法。

SELECT  st.姓名,st.出生日期,n.年代
FROM    stu_info AS st,nddzb AS n
WHERE  st.birth BETWEEN n.起始年份 AND n.终止年份

（3）左外连接查询：

**外连接：**所有记录都被包含进去，即使没能匹配的记录也被查询结果集包含在内。

左外连接的规则是将左外连接运算符（LEFT OUTER JOIN）左侧表的所有记录都包含到结果集中，而只将右边表中有匹配的记录包含进结果集

**(4）右外连接（RIGHT OUTER JOIN）**右边表的所有记录都包含到结果集中，而左边表中有匹配的记录才包含进结果集，

（5）全外连接

SELECT  *
FROM  t1
FULL OUTER JOIN t2
ON  t1.职工号=t2.职工号

（6）交叉连接查询

SELECT  *
FROM   t1,t2

SELECT  *
FROM  t1  CROSS JOIN  t2

上面的两种SELECT语句完全等同，交叉连接的返回结果是一个笛卡尔积，即两个表中的每一行都互相连接。

（7）连接查询中使用聚合函数

 
SELECT  COUNT(*) AS 没有考任何考试的人数
FROM   stu_info AS st
        LEFT OUTER JOIN score AS s
        ON st.sno=s.sno
WHERE  s.sno IS NULL

（8）高级连接查询实例

3、组合查询

使用UNION关键字将多个SELECT语句组合起来，将多个SELECT语句的查询结果显示到一个结果集中。组合查询与连接查询不同的是，前者将多个表的查询结果，竖着组合，而后者是将查询结果横着连接

**（1）使用组合查询:**使用UNION关键字将多个SELECT查询语句组合起来查询，以一个查询结果集的形式显示出来。

SELECT语句1
UNION
SELECT语句2
UNION
SELECT语句3
……
UNION
SELECT语句n


SELECT  ……
……
UNION ALL
SELECT  ……

说明：使用UNION时，如果希望不删除重复值，则可以在UNION后加上ALL关键字。例如，下面的语句不删除重复值记录。

（2）使用UNION的规则

使用UNION组合查询语句时，应当注意两条最重要的规则。
**1．每个单独的SELECT查询语句应当有相同数量的字段,**如果不同则会出现错误。

注意：使用UNION、INTERSECT或EXCEPT运算符合并的所有查询必须在其目标列表中有相同数目的表达式。
技巧：当独立查询语句的字段个数不同时，可以在字段个数不够的地方使用常量补位。例如，在上面的第一个SELECT子句中补上一个NULL值，就可以避免错误。

2．每个查询语句中相应的字段的类型必须相互兼容

技巧：当相应位置的字段类型不同时，可以使用类型转换函数强制转换字段类型。

（3）使用UNION得到复杂的统计汇总样式

联合UNION、GROUP BY和聚合函数三者会得到具有很棒的统计汇总样式的查询结果，这就是OR所不能替代的一个例子。例如，下面的语句会得到一个具有复杂统计汇总样式的查询结果集。

SELECT sno,cno,exam
FROM  score
UNION
SELECT sno,'总分：',SUM(exam)
FROM  score
GROUP BY sno
UNION
SELECT sno,'平均分：',AVG(exam)
FROM  score
GROUP BY sno

（4）排序组合查询的结果

虽然组合查询中可以有多个单独的SELECT语句，而且每个独立的SELECT语句又都可以拥有自己的WHERE子句、GROUP BY子句和HAVING子句，但是，整个语句中却只能出现一个ORDER BY子句，而且它的位置必须在整个语句的末尾，就是说只能对组合查询最后的结果进行排序，而并不能只对某个单独的SELECT语句的结果进行排序。

SELECT  *
FROM   stu_info
WHERE  depart = '计算机系'
UNION
SELECT  *
FROM   stu_info
WHERE  DATEDIFF(year,birth,GETDATE())>30
ORDER BY birth

因为组合查询结果集的字段名列表是根据第一个SELECT子句的字段名列表而定的，所以在使用ORDER BY时，应当注意这一点。组合查询其实存在一个很有意思的排序问题。当没有ORDER BY子句时，查询结果会根据第一个SELECT子句中字段名列表升序排序。

4、子查询

嵌入另一个SELECT语句中的SELECT语句被称为子查询。目前，子查询能完成的工作，通过表连接几乎也都可以完成，而在过去，因为内连接的运行效率比较差，外连接又不能使用，所以子查询被运用得非常广。但是，近些年来由于对SQL Server的优化，使得内连接的运行效率明显高于子查询，而外连接也被开发了出来，所以用户开始丢掉那些比较难理解的子查询语句，而改用相对容易理解的表连接查询语句。
(1) 使用返回单值的子查询
如果子查询返回单值，则可以使用关系运算符，例如，等于（=）、不等于（<>）等，将其与主查询结合起来。

(2) 子查询与聚合函数的配合使用
【例13】查询出生日期最小的学生的所有信息。

SELECT  *
FROM  stu_info
WHERE  birth=(SELECT MIN(birth)
              FROM stu_info)

聚合函数

SUM（求和函数）、AVG（求平均值函数）、MIN（求最小值函数）MAX（求最大值函数）、COUNT（求数量的函数）

根据提交时间submit_time不为空筛选活跃的的人。知识点：select...from...where...
筛选每个月的平均活跃天数和总活跃人数：
根据月份来选择时间。知识点：date_format() 通过这个函数匹配'%Y%m'年份和月份；
计算用户平均活跃天数：
根据不同的日期且不同的用户ID统计每个月用户的总活跃天数。知识点：distinct、count()、date_format()
统计每个月用的总人数。知识点：distinct、count()
总天数/总人数得到每个月的用户平均活跃天数；
计算每月总活跃人数，直接统计每月不同的用户id数。知识点：count()、distinct
按照月份分组group by date_format(submit_time, '%Y%m') 知识点：group by ...
保留两位小数。 知识点：round(x,2)

select date_format(submit_time, '%Y%m') as month,
       round((count(distinct uid, date_format(submit_time, '%y%m%d'))) / count(distinct uid), 2) as avg_active_days,
       count(distinct uid) as mau
from exam_record
where submit_time is not null
and year(submit_time) = 2021
group by date_format(submit_time, '%Y%m')

类型转换函数

在SQL Server 2008中，提供了**CONVERT()和CAST()**两个数据类型转换函数。

CONVERT()函数

CONVERT( datatype[(length)],expression,[style])

datatype：表示要转换的数据类型，如果要转换成CHAR、VARCHAR、BINARY或VARBINARY数据类型，还要设置数据类型的长度。
● expression：表达式，要进行数据类型转换的值或列名。
● style：用于日期格式的设置。如果要将日期型数据转换为字符型数据，则还可以使用style参数设置日期显示格式。style参数的取值与日期显示格式如下表所示。

说明：style参数可以取两类值，如果从第一类取值，则返回日期的年份为2位；如果从第二类取值，则返回日期年份为4位

【例6】把当前数据库的时间转换成字符类型。
获取当前数据库的时间使用的函数是GETDATE()。

SELECT CONVERT(CHAR, GETDATE())

查询学生信息表中学生信息，并把学生的出生日期转换成字符类型。

SELECT STUNNAME,CONVERT(CHAR,STUSTUBIRTH,103)
FROM STUINFO

CAST()函数

与CONVERT相比，在使用方面更加容易，但是如果要对日期类型转换时却没有CONVERT()函数方便。所以在一般数据类型转换时推荐使用CAST()函数，对于日期类型的转换要使用CONVERT()函数转换。

CAST (expression AS datatype[(length)])

其中，
● expression为表达式。
● datatype为数据类型，如果是CHAR、VARCHAR、NUMERIC等数据类型，则可以选择length参数设置长度。
【例8】从STUINFO表中，查询所有学生的姓名、出生日期，并将日期转换为字符串显示。

SELECT  STUNAME,CAST(STUSTUBIRTH AS char(10)) AS生日
FROM   STUINFO

日期函数

SQL Server支持的日期函数有GETDATE、DATEADD、DATEDIFF、DATENAME和DATEPART等函数。

**GETDATE函数：**可获得当前系统时间。

SELECT  GETDATE()

**DATEADD函数：**用于在指定日期上增加年、月、日或时间等，其返回值为日期型数据。其格式为：

DATEADD(datepart,number,date)

datepart参数规定在日期的哪个部分（如年份、月份等）增加（减）数值。 datepart参数的可用值

在当前时间的“年”上增加了5年，并返回5年后的日期.

DATEADD(year,5,GETDATE())
在当前时间的“月”上增加了5个月，并返回5个月后的日期
DATEADD(month,5,GETDATE())
说明：datepart参数值也可以使用缩写，例如，DATEADD(mm,5,GETDATE())也是在当前时间上增加5个月。

**DATEDIFF函数：**用于获取两个日期间的差，并返回数值数据

DATEDIFF(datepart,date1,date2)

其中，datepart参数的说明同上，date1和date2是日期或日期格式的字符串。

从STUINFO表中查询所有学生的姓名、出生日期和年龄。

SELECT  STUNAME,
    STUSTUBIRTH,
    DATEDIFF(year, STUSTUBIRTH,GETDATE()) AS年龄
FROM  STUINFO

DATEDIFF(year, STUSTUBIRTH,GETDATE())//返回年份的差距，即年龄。

DATEDIFF(month, STUSTUBIRTH,GETDATE())//返回的是当前时间和出生日期之间的月份的差距，即返回相差多少个月。

DATENAME函数：用于获取日期的一部份，并以字符串形式返回

DATENAME (datepart,date)

其中，datepart参数的说明同上，date是日期或者日期格式的字符串。例如，假设当前日期为2010年2月25日，则DATENAME(month,GETDATE( ))的结果为字符串’02’，DATENAME(dd,GETDATE( ))的结果为字符串’25’。
注意：假设当前日期为2010年3月5日，则DATENAME (dd,GETDATE( ))返回的结果为字符串’5’，而并非是’05’。
从STUINFO表中查询每位1号出生的所有学生。

SELECT  *
FROM   STUINFO
WHERE  DATENAME(day,STUSTUBIRTH)='1'

注意：DATENAME函数返回的是字符串，因此必须与字符串（‘1’）比较。

DATEPART函数：用于获取日期的一部份，并以整数值返回：

DATEPART (datepart,date)

其中，datepart参数的说明同上，date是日期或者日期格式的字符串。例如，假设当前日期为2010 年2 月25 日，则DATEPART (month,GETDATE())的结果为数值2，DATEPART (dd,GETDATE())的结果为数值25。

从STUINFO表中查询每位1号出生的所有学生

SELECT  *
FROM   STUINFO
WHERE  DATEPART (day, STUSTUBIRTH)=1

注意：DATEPART函数返回的是数值，因此必须与数值（1）比较。
SQL Server中除上述日期时间函数以外，还有YEAR、MONTH、DAY三个函数，分别用于获取日期数据的年份、月份和日期部分，这三个函数的返回值都是数值型。

ISNULL

SQL Server中的ISNULL函数可以将NULL值更改为其他值，其语法如下所示。

如果参数一为空，则返回参数二

ISNULL (check_expression , replacement_value )


SELECT c1, ISNULL(c2,0)
FROM testnull;

分组查询

数据分组是指将数据表中的数据按照某种值分为很多组。例如，将STUINFO表中的数据用性别进行分组，会得到两组：所有男生为一组，所有女生为一组。数据分组使用GROUP BY子句，当然，如果想要将满足条件的分组查询出来，还需要HAVING子句的配合。

技巧：前面讲过去除相同值，需要使用DISTINCT关键字。但是，使用DISTINCT会严重降低查询效率，为此，使用GROUP BY子句代替DISTINCT是一种非常好的解决方案。

通过错误提示可以得到如下启示，如果查询语句带有GROUP BY子句，则：
● SELECT子句中通常不单独使用星号通配符。如果非要单独使用星号通配符，则应当在GROUP BY子句中列出表的所有字段名，字段名之间用逗号分隔。不过这样会使GROUP BY子句失去它的作用。因为，此时并不是按单个字段分组，而是使用GROUP BY后列出的所有字段的组合分组。
● 如果SELECT子句后是字段名列表，而这些字段名又不在聚合函数中，则应当在GROUP BY子句中列出所有这些字段名。此时，需要注意的还有，分组是按GROUP BY后的所有字段的组合分组，而并非是按单个字段分组。例如，“GROUP BY depart,sname”表示只有某几个记录中的所属院系和姓名都相同时才把这些记录分为一组。

聚合函数与分组配合使用

上面的查询结果虽然将统计数据查询了出来，但还不够完美。这里由于表中的数据表较少，还看不出太明显的效果，如果要查询出每一个专业中男生是多少人、女生是多少人，在学习SQL语句时要学会灵活运用，CASE表达式和GROUP BY子句联合使用会得到很多有用的数据表示，其中就包括反转查询结果的数据表示，具体语句如下。

SELECT STUMAJOR,
      COUNT(CASE
              WHEN STUSEX='男' THEN 1
              ELSE NULL
          END) AS 男生人数,
      COUNT(CASE
              WHEN STUSEX ='女' THEN 1
              ELSE NULL
          END) AS 女生人数
FROM  STUINFO
GROUP BY STUMAJOR

使用HAVING子句设置分组查询条件

查看想要的分组的统计信息，而并不是所有分组的统计信息。
HAVING子句用于设置分组查询条件，即过滤不需要的分组。该子句通常和GROUP BY子句一起使用。单独使用HAVING子句没有太大的意义。

在STUINFO表中统计计算机专业和会计专业的学生人数，并按学生人数降序排序。

SELECT   STUMAJOR,COUNT(*) AS 人数
FROM     STUINFO
GROUP BY STUMAJOR
HAVING   STUMAJOR IN('计算机','会计')
ORDER BY  COUNT(*)

当然，本例也可以用WHERE子句代替HAVING子句：

SELECT   STUMAJOR,COUNT(*) AS 人数
FROM     STUINFO
WHERE   STUMAJOR IN('计算机','会计')
GROUP BY STUMAJOR
ORDER BY  COUNT(*)

注意：HAVING子句与WHERE子句之后都写条件表达式，而且都会根据条件表达式的结果筛选数据。但它们是有区别的，主要区别如下。
（1）HAVING子句用于筛选组，而WHERE子句用于筛选记录。
（2）HAVING子句中可以使用聚合函数，而WHERE子句中不能使用聚合函数。
（3）**HAVING子句中不能出现既不被GROUP BY子句包含，又不被聚合函数包含的字段。**而WHERE子句中可以出现任意字段。通常，HAVING子句总是和GROUP BY子句配合使用的，而WHERE子句可以不用任何子句的配合。

插入

插入记录的方式汇总：

普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, ...)
普通插入（限定字段）：INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)
多条一次性插入：INSERT INTO table_name (column1, column2, ...) VALUES (value1_1, value1_2, ...), (value2_1, value2_2, ...), ...
从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]

replace into（已有则替换）

REPLACE INTO examination_info
VALUES(NULL,9003,'SQL','hard',90,'2021-01-01 00:00:00');

replace into 跟 insert into功能类似，不同点在于：replace into 首先尝试插入数据到表中，

如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；
否则，直接插入新数据。

要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

DELETE FROM examination_info
WHERE exam_id=9003;
INSERT INTO examination_info
VALUES(NULL,9003, 'SQL','hard', 90, '2021-01-01 00:00:00')

更新表

（3） 使用表连接更新数据

通过FROM子句和WHERE子句配合，可以进行多表连接

UPDATE  score
SET     usually= usually +5
FROM   score AS s,course AS c
WHERE  c.cname='大学英语'
AND    s.cno=c.cno

（4）使用UPDATE语句删除指定字段的数据

UPDATE stu_info
SET    telephone = NULL
WHERE  depart='外语系'

修改记录的方式汇总：

设置为新值：UPDATE table_name SET column_name=new_value [, column_name2=new_value2] [WHERE column_name3=value3]
根据已有值替换：UPDATE table_name SET key1=replace(key1, ‘查找内容’, ‘替换成内容’) [WHERE column_name3=value3]

本题采用两种修改方式均可，语义为『当tag为PYTHON时，修改tag为Python』，先用第一种:
UPDATE examination_info
SET tag = "Python"
WHERE tag = "PYTHON";

如果采用第二种，写作：
UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag = "PYTHON";

思维扩展：第二种方式不仅可用于整体替换，还能做子串替换，例如要实现将tag中所有的PYTHON替换为Python（如CPYTHON=>CPython），可写作：
UPDATE examination_info
SET tag = REPLACE(tag, "PYTHON", "Python")
WHERE tag LIKE "%PYTHON%";

delete

（2）在DELETE语句中使用多表连接

DELETE  score_copy
FROM   score_copy AS s,stu_info AS st
WHERE  st.sname ='张三'
AND    st.sno=s.sno

说明：删除语句中**，DELETE关键字后的表名指定要从哪个数据表删除数据**，在本语句中DELETE关键字后是score_copy，因此，只删除score_copy表中的相关数据，而与FROM子句中列出的其他表无关，例如与stu_info表无关。

5、使用TRUNCATE语句删除所有记录

实际上使用DELETE语句删除表中所有记录的效率有时非常低，因为SQL Server会向事务处理日志写入一些内容，这些内容在删除执行失败时，可以帮助用户将数据回滚（回退）到删除执行前的状态。
TRUNCATE是删除表中所有记录的另一种语句，与DELETE语句相比，TRUNCATE运行效率非常高，因为使用TRUNCATE语句时，SQL Server不会写入任何内容，换个角度说，TRUNCATE语句所做的修改是不能回滚的

TRUNCATE TABLE stu_info_copy

DELETE
FROM
	exam_record
WHERE
	timestampdiff(
		MINUTE,
		start_time,
		submit_time
	)<5
AND score < 60 
timestampdiff 查询两个时间以第一参数为单位的差

表的创建、修改与删除：

1.1 直接创建表：

CREATE TABLE
[IF NOT EXISTS] tb_name -- 不存在才创建，存在就跳过
(column_name1 data_type1 -- 列名和类型必选
  [ PRIMARY KEY -- 可选的约束，主键
   | FOREIGN KEY -- 外键，引用其他表的键值
   | AUTO_INCREMENT -- 自增ID
   | COMMENT comment -- 列注释（评论）
   | DEFAULT default_value -- 默认值
   | UNIQUE -- 唯一性约束，不允许两条记录该列值相同
   | NOT NULL -- 该列非空
  ], ...
) [CHARACTER SET charset] -- 字符集编码
[COLLATE collate_value] -- 列排序和比较时的规则（是否区分大小写等）

1.2 从另一张表复制表结构创建表： CREATE TABLE tb_name LIKE tb_name_old
1.3 从另一张表的查询结果创建表： CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options
2.1 修改表：ALTER TABLE 表名修改选项 。选项集合：

    { ADD COLUMN <列名> <类型>  -- 增加列
     | CHANGE COLUMN <旧列名> <新列名> <新列类型> -- 修改列名或类型
     | ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT } -- 修改/删除 列的默认值
     | MODIFY COLUMN <列名> <类型> -- 修改列类型
     | DROP COLUMN <列名> -- 删除列
     | RENAME TO <新表名> -- 修改表名
     | CHARACTER SET <字符集名> -- 修改字符集
     | COLLATE <校对规则名> } -- 修改校对规则（比较和排序时用到）

3.1 删除表：DROP TABLE [IF EXISTS] 表名1 [ ,表名2]。

修改表

alter table user_info add school varchar(15) after level;
增加列在某列之后
alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)

alter table user_info change job profession varchar(10);
更换列的名称及数据类型
alter table user_info change 原列名 修改列名 修改数据类型

alter table user_info modify achievement int(11) default 0;
更改数据类型
alter table 表名 modify 修改列名称 数据类型 默认值等

索引创建、删除与使用：

1.1 create方式创建索引：

CREATE 
  [UNIQUE -- 唯一索引
  | FULLTEXT -- 全文索引
  ] INDEX index_name ON table_name -- 不指定唯一或全文时默认普通索引
  (column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引

1.2 alter方式创建索引：ALTER TABLE tb_name ADD [UNIQUE | FULLTEXT] [INDEX] index_content(content)
2.1 drop方式删除索引：DROP INDEX <索引名> ON <表名>
2.2 alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>
3.1 索引的使用：
- 索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
- 索引不包含有NULL值的列
- 一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
- like做字段比较时只有前缀确定时才会使用索引
- 在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引

日期函数

CONVERT()

CONVERT()函数的优点是可以格式化日期和数值，它需要两个参数：第1个是目标数据类型，第2个是源数据。

计算平均

这里开始我的想法就是计算这个月与下个月之间的日期差datediff然后发现没办法用，要求两个都是date类型，怎么把下个月表示出来是个问题，除非两次转换，所以就放弃了

last_day()
然后看到一个神奇的函数就是last_day()返回参数日期的最后一天那再用day不就能获取这个月的天数了嘛

也就是day(last_day(subnit_time))

select 
DATE_FORMAT(submit_time,'%Y%m') as submit_month,
count(*) as month_q_cnt,
round(count(*) / day(last_day(submit_time)) ,3) as avg_day_q_cnt
from practice_record 
and year(submit_time) = '2021'
group by DATE_FORMAT(submit_time,'%Y%m')

union all 

select 
'2021汇总' as submit_month,
count(*) as month_q_cnt,
round(count(*) /31 ,3) as avg_day_q_cnt -- /30 会不通过用例
from practice_record where score is not null 
and year(submit_time) = '2021' 

order by submit_month ;

SELECT
    coalesce(DATE_FORMAT(submit_time,"%Y%m"),'2021汇总') submit_month,
    COUNT(submit_time) month_q_cnt,
    round(COUNT(submit_time) / MAX(DAY(last_day(submit_time))),3)avg_day_q_cnt
FROM
    practice_record
WHERE
    year(submit_time) = '2021'
GROUP BY
    DATE_FORMAT(submit_time,"%Y%m") WITH ROLLUP;

新知识时间

coalesce

COALESCE是一个函数，coalesce (expression_1, expression_2, …,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。

SQL实例

select coalesce(success_cnt, ``1``) from tableA

当success_cnt 为null值的时候，将返回1，否则将返回success_cnt的真实值。

select coalesce(success_cnt,period,``1``) from tableA

当success_cnt不为null，那么无论period是否为null，都将返回success_cnt的真实值（因为success_cnt是第一个参数），当success_cnt为null，而period不为null的时候，返回period的真实值。只有当success_cnt和period均为null的时候，将返回1。

在这里题解用的是第一种，也就是当submit_time为null的时候返回一个‘2021汇总’加到最后那最后的均值呢？

with rollup

with在sql语句中定义在group by之后。当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。而在groupby后面还可以加入withcube和withrollup等关键字对数据进行汇总。不过这个cube在mysql中并不适用。

使用 WITH ROLLUP，此函数是对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组计算。

题目应该也是想让用这个函数因为题上给的最后平均就是总数/31而不是一年的365 or366

所以使用这个函数刚好可以

连接函数

统计未完成试卷作答数和已完成试卷作答数：count(incomplete) as incomplete_cnt
统计作答过的tag集合：
- 对于每条作答tag，用:连接日期和tag：concat_ws(':', date(start_time), tag)
- 对于一个人（组内）的多条作答，用;连接去重后的作答记录：group_concat(distinct concat_ws(':', date(start_time), tag) SEPARATOR ';')
筛选未完成试卷作答数大于1的有效用户：
```
having complete_cnt >= 1 and incomplete_cnt BETWEEN 2 and 4
```
- 完成试卷作答数至少为1：complete_cnt >= 1
- 未完成数小于5：incomplete_cnt < 5
- 未完成试卷作答数大于1：incomplete_cnt > 1

月份格式化函数DATE_FORMAT

统计该用户有完成试卷的月份数count(distinct DATE_FORMAT(start_time, "%Y%m"))；
分组后过滤having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3；

begin end

标识代码块是为一部分，一起执行

create procedure pro_calc 
@num int
as
if @num<3
begin
 print '小于3'
 print 'sss'
end
else
begin
 print '大于等于3'
 print 'fdf'
end

这个存储过程，一定要有begin 和end,因为if后面有两条语句要处理，相当于c#里的花括号一样。

row_number, rank(), dense_rank()

rank()排序相同时会重复，总数不变，即会出现1、1、3这样的排序结果；
dense_rank()排序相同时会重复，总数会减少，即会出现1、1、2这样的排序结果；
row_number()排序相同时不会重复，会根据顺序排序。

sql内置函数

1）窗口函数：有三种排序方式

rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

2）聚合函数：通常查找最大值最小值的时候，首先会想到使用聚合函数。

a.group by的常见搭配：常和以下聚合函数搭配

avg（）-- 求平均值
count（）-- 计数
sum（）-- 求和
max（） – 最大值
min（）-- 最小值

b.group by 的进阶用法，和with rollup一起使用。

3）左右连接

左连接：表1 left join 表2 on 表1.字段=表2.字段（以表1为准，表2进行匹配）

右连接：表1 right join 表2 on 表1.字段=表2.字段（以表2为准，表1进行匹配）

全连接：表1 union all 表2 （表1 和表2的列数必须一样多，union 去除重复项，union all 不剔除重复项）

内连接：表1 inner join 表2（取表1和表2相交部分）

外连接：表1 full outer join 表2 （取表1和表2不相交的部分）

ps：MYSQL 不支持外连接，可以用左右连接后再全连接代替

lead的窗口函数用法

lead(字段名，n) over () :取值向后偏移n行（空间的理解就是直接将一列数据往前推n个位置，后面的位置就空出来了，具体配合图片理解）;
lag(字段名，n) over () :取值向前偏移n行（空间的理解就是直接将一列数据往前后n个位置，前面的位置就空出来了，具体配合图片理解）;
lag(字段名，n，x) over () :取值向前偏移n行，并将空值填充为数字x（空间的理解就是直接将一列数据往前后n个位置，前面的空出来的位置用X填充上，具体配合图片理解）。

4）rank()与dense_rank()的区别

由以上的例子得出，rank()和dense_rank()都可以将并列第一名的都查找出来；但rank()是跳跃排序，有两个第一名时接下来是第三名；而dense_rank()是非跳跃排序，有两个第一名时接下来是第二名。

5）5个常用的排序函数

rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)\
row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)
percent_rank() over() 按照数字所在的位置进行百分位分段
ntile(n)over() 将数字按照大小平均分成n段
lead(字段名，n)over()把字段数据向前移n个单元格
lag(字段名，n)over()把字段数据向后移n个单元格

select * ,
    row_number()over(order by uid) row_number1, /*按照uid的大小不重不漏1 2 3 4 5 6 7 */
    rank()over(order by uid) rank1, /*按照uid的大小并列第一无第二，1 1 1 4 5 5 7*/
    dense_rank()over(order by uid) dense_rank1,/*按照uid的大小并列第一有第二，1 1 1  2 3 3 4*/
    percent_rank()over(order by uid) percent_rank1,/*按照uid的大小进行百分法排序*/
    ntile(2)over(order by uid) ntile1,/*按照uid的大小，把uid评价分成2组*/
    lead(uid)over(order by uid) lead1,/*把uid向上推1个位置*/
    lag(uid)over(order by uid) lag1 /*把uid向下推1个位置*/    
from user_id;

聚类窗口函数

主要考察聚类窗口函数，和聚类窗口函数的用法和GROUP BY 函数类似。

MIN()OVER() :不改变表结构的前提下，计算出最小值
MAX()OVER():不改变表结构的前提下，计算出最大值
COUNT()OVER():不改变表结构的前提下，计数
SUM()OVER():不改变表结构的前提下，求和
AVG()OVER():不改变表结构的前提下，求平均值

1）聚合窗口函数

MIN()OVER() :不改变表结构的前提下，计算出最小值
MAX()OVER():不改变表结构的前提下，计算出最大值
COUNT()OVER():不改变表结构的前提下，计数
SUM()OVER():不改变表结构的前提下，求和
AVG()OVER():不改变表结构的前提下，求平均值

2）排序窗口函数

percent_rank() over() 按照数字所在的位置进行百分位分段
ntile(n)over() 将数字按照大小平均分成n段
lead(字段名，n)over()把字段数据向前移n个单元格
lag(字段名，n)over()把字段数据向后移n个单元格

例：

SELECT start_month ,#每个月     COUNT(DISTINCT uid) mau, #月活用户数   
    SUM(new_day) month_add_uv, #新增用户
    MAX(SUM(new_day))OVER(ORDER BY start_month) max_month_add_uv, #截止当月的单月最大新增用户数
    SUM(SUM(new_day))OVER(ORDER BY start_month) cum_sum_uv
FROM (
    SELECT *,DATE_FORMAT(start_time,'%Y%m') start_month,     IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) new_day
    FROM exam_record)t1
GROUP BY start_month;

例题：

筛选出每个0级用户高难度题的得分及耗时：
- 得分信息、用户信息、题目信息分布三个表格中，我们用exam_id将exam_record和examination_info连在一起，再通过uid连上user_info。知识点：join…on…
- 从连接后的表格中筛选出用户等级为0试题难度为hard的信息。知识点：where
- 修改得分为空的分数为0。if(score is not null, score, 0) as new_score 知识点：if
- 计算用户做这份试卷的用时，没有提交时间就设置为试卷限制时间。if(submit_time is not null, timestampdiff(minute, start_time, submit_time), duration) as cost_time 知识点：if、timestampdiff
- 筛选出来的信息记为new_table
从new_table中筛选出每个用户的平均得分及平均用时，要以uid分组统计。知识点：group by、round()、avg()

筛选出每个0级用户高难度题的得分及耗时：
得分信息、用户信息、题目信息分布三个表格中，我们用exam_id将exam_record和examination_info连在一起，再通过uid连上user_info。知识点：join...on...
从连接后的表格中筛选出用户等级为0试题难度为hard的信息。知识点：where
修改得分为空的分数为0。if(score is not null, score, 0) as new_score 知识点：if
计算用户做这份试卷的用时，没有提交时间就设置为试卷限制时间。if(submit_time is not null, timestampdiff(minute, start_time, submit_time), duration) as cost_time 知识点：if、timestampdiff
筛选出来的信息记为new_table
从new_table中筛选出每个用户的平均得分及平均用时，要以uid分组统计。知识点：group by、round()、avg()

正则

RLIKE后面可以跟正则表达式。

正则表达式``"^[0-9]+$"``的意思：

1、字符^

意义：表示匹配的字符必须在最前边。

例如：^A不匹配“an A”中的‘A’，但匹配“An A”中最前面的‘A’。

2、字符$

意义：与^类似，匹配最末的字符。

例如：t$不匹配“eater”中的‘t’，但匹配“eat”中的‘t’。

3、字符[0-9]

意义：字符列表，匹配列出中的任一个字符。你可以通过连字符-指出字符范围。

例如：[abc]跟[a-c]一样。它们匹配“brisket”中的‘b’和“ache”中的‘c’。

4、字符+

意义：匹配+号前面的字符1次及以上。等价于{1,}。

例如：a+匹配“candy”中的‘a’和“caaaaaaandy”中的所有‘a’。

来源：可参考https://www.yulucn.com/question/4866556442

SELECT uid, exam_id, ROUND(AVG(score),0) avg_score FROM exam_record
WHERE uid IN (SELECT uid FROM user_info 
              WHERE nick_name RLIKE "^牛客[0-9]+号$" OR nick_name RLIKE "^[0-9]+$") AND
      exam_id IN (SELECT exam_id FROM examination_info
                 WHERE tag RLIKE "^[cC]") AND
      score IS NOT NULL
GROUP BY uid, exam_id
ORDER BY uid,avg_score;

case when then else

select level, score_grade, 
       round(count(uid) / total, 3) as ratio
from (
    select u_i.uid as uid,
           exam_id, score, level,
           case when score >= 90 then '优'
                when score >= 75 then '良'
                when score >= 60 then '中'
                else '差' end as score_grade,
           count(*) over(partition by level) as total
    from user_info u_i join exam_record e_r
    on u_i.uid = e_r.uid
    where score is not null 
) user_grade_table
group by level, score_grade
order by level desc, ratio desc

String操作

筛选出录错了的记录：WHERE tag LIKE '%,%'
提取tag，第一个逗号前的值：substring_index(tag, ',', 1) AS tag
提取难度，第二个逗号前倒数第一个逗号后：substring_index(substring_index(tag, ',', 2), ',', -1) AS difficulty
提取时长，最后一个逗号后，并类型转换：CAST( substring_index(tag, ',', -1) AS DECIMAL ) AS duration

筛选昵称字符数大于10的用户：WHERE CHAR_LENGTH(nick_name) > 10
对字符数大于13的用户昵称做处理：IF(CHAR_LENGTH(nick_name) > 13,
前10个字符加上三个点号：CONCAT(SUBSTR(nick_name, 1, 10), '...')

sql server

@ #符号认识

@ 表示局部变量

@@ 表示全局变量

# 表示本地临时表的名称，以单个数字符号打头；它们仅对当前的用户连接是可见的

## 表示全局临时表

7c1ed21b0ef41bd5a51b624b5fda81cb38db3dc0

本地临时表

以一个井号 (#) 开头的表名。只有在创建本地临时表连接时才能看得到，连接断开时临时表立马被删除，也就是到货本地临时表为创建它的该链接的会话所独有，或者说局部临时表是有当前用户创建的，并且只有当前用户的会话才可以访问。

全局临时表

以两个井号 (##) 开头的表名。在所有连接上都能看到全局临时表，也就是说只要全局临时表存在，那么对所有创建用户的会话后都是可见的。如果在创建全局临时表的连接断开前没有显式地除去全局临时表，那么只能等到其它所有任务都停止引用，这些表才会被删除。

当创建全局临时表的连接断开后，新的任务不能再引用它们，也就是说旧的任务才可以引用。当前的语句一执行完，任务与表之间的关联即被除去；因此通常情况下，只要创建全局临时表的连接断开，全局临时表也会同时被删除。

流程控制语句

1.BEGIN…END语句

Transact_SQL使用BEGIN和END来标记一个程序语句块的开始和结束。它经常与IF…ELSE和WHILE循环一起使用。

BEGIN
语句1
语句2
语句3
……
END

2. IF…ELSE语句

IF条件
BEGIN
语句块1
END
[ELSE
BEGIN
语句块2
END]

在IF或ELSE中还可以嵌套其他IF语句。

【例4】下面的程序用于求两数之商，如果除数不为0，则求出正确结果，如果为0，则给出提示。

DECLARE @x real,@y real,@z real
SELECT @x=9,@y=5
IF @y<>0
BEGIN
    SELECT @z=@x/@y
    PRINT '结果为：'+CAST(@z AS char)
END
ELSE
    PRINT '除数不能为零！'

说明：如果IF或ELSE中只有一条语句，则可以省略BEGIN和END标记。

3.WHILE语句

WHILE循环条件
BEGIN
语句块（循环体）
END

【例5】编程计算1+2+3+…+100的结果。

DECLARE @x int,@s int
SELECT @x=1,@s=0
WHILE @x<=100
BEGIN
    SELECT @s=@s+@x
    SELECT @x=@x+1
END
PRINT '结果为：'+CAST(@s AS char)

4.BREAK语句

该命令通常和IF…ELSE语句配合使用。
【例6】下面的程序用于打印1，2，3，4。

DECLARE @x int
SELECT @x=1
WHILE @x<=10
BEGIN
    IF @x=5  /*判断是否为5，如果是则结束循环 */
        BREAK
    ELSE
        PRINT CAST(@x AS char)
    SELECT @x=@x+1
END

5.CONTINUE语句

COUNTINUE命令也用于WHILE循环。它会令循环立即从BEGIN处开始重新执行，也就是说不再执行其语句块中剩下的部分。通常COUNTINUE也和IF…ELSE语句配合使用。
【例7】下面的程序用于打印1~5之间的所有奇数。

DECLARE @x int
SELECT @x=0
WHILE @x<=5
BEGIN
    SELECT @x=@x+1
    IF @x%2=0   /*判断是否为偶数，如果是则重新开始循环 */
        CONTINUE
    PRINT CAST(@x AS char)
END

6.WAITFOR语句

WAITFOR语句指定在一段时间后执行下一个Transact-SQL语句、语句块。

WAITFOR{ DELAY 'time_to_pass'｜TIME 'time_to_execute'  }

其中：
● DELAY指定在多长时间后执行语句，最长为24小时。
● TIME指定运行批处理、存储过程或事务的时间。
【例8】要在1小时后，执行一条查询语句：

WAITFOR  DELAY  '01:00:00'
SELECT * FROM student

7.CASE语句

CASE语句就是一个条件判断语句，在执行CASE语句时，当匹配了一个子句时就从CASE语句中跳出。

CASE <表达式>
    WHEN <表达式> THEN <表达式>
    [[WHEN <表达式> THEN <表达式>][…]]
    [ELSE  <表达式>]
END

说明：CASE语句不仅可以单独使用，也可以嵌套到SQL命令中。

【例9】根据学生成绩划分等级。

SELECT
    CASE
      WHEN  学生成绩>=85  THEN  '优秀'
      WHEN  学生成绩>=60 AND学生成绩<=84  THEN '中等'
      WHEN  学生成绩<60  THEN '不及格'
    END
FROM学生信息表

游标

1. 什么是游标

游标是一种处理数据的方法，具有对结果集进行逐行处理的能力。

可以将游标看作一种特殊的指针，它与某个查询结果集相关联，可以指向结果集的任意位置，可以将数据放在数组、应用程序中或其他的地方，允许用户对指定位置的数据进行处理。

游标函数

2.为什么使用游标

出来 SELECT 的 WHERE 可以限制一条记录被选中外，T-SQL 没有提供查询表中单条记录的方法，但我们常常会遇到需要逐行读取记录的情况。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序往往采取非数据库语言（如 C#、VB、ASP或其他开发工具）内嵌T-SQL的形式来开发，而这些非数据库语言无法将表作为一个单元来处理，因此，这些应用程序需要一种机制以便每次处理一行或一部分行。

3.游标的实现功能及使用步骤

游标的实现功能

允许对 SELECT 返回的表中的每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；
从表中的当前位置检索一行或多行数据；
游标允许应用程序对当前位置的数据进行修改、删除的能力；
对于不同用户对结果集包含的数据所做的修改，支持不同的可见性级别；
提供脚本、存储过程、触发器中用于访问结果集中的数据的语句。

游标的使用步骤：前4个步骤是必须的。

声明游标 DECLARE：将游标与 T-SQL 语句的结果集相关联，并定义游标的名称、类型和属性，如游标中的记录是否可以更新、删除
打开游标 OPEN：执行 T-SQL 语句以填充数据。
读取游标 FETCH：从游标的结果集中检索想要查看的行，进行逐步操作。
关闭游标 CLOSE：停止游标使用的查询，但并不删除游标的定义，可以使用 OPEN 再次打开。
释放游标 DEALLOCATE：删除资源并释放其占用的所有资源。

4.游标语法

1-1 声明游标
声明游标是指用 DECLARE 语句创建一个游标。声明游标主要包括以下内容：游标名称、数据来源、选取条件和属性。

声明游标语法格式

DECLARE 游标名称 CURSOR       
[ LOCAL | GLOBAL ]                                   --游标的作用域
[ FORWORD_ONLY | SCROLL ]                            --游标的移动方向
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]         --游标的类型
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]            --游标的访问类型
[ TYPE_WARNING]                                      --类型转换警告语句
FOR SELECT 语句                                      --SELECT查询语句
[ FOR { READ ONLY | UPDATE [OF 列名称]}][,...n]      --可修改的列

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[FOR { READ ONLY｜UPDATE [ OF column_name [ ,...n ]]}]

● DECLARE：关键词，声明游标。
● cursor_name：创建的游标的名称。
● INSENSITIVE：表示创建的游标中的数据是基础表中的数据副本，该游标不允许修改，如果省略该关键词，那么从游标中提取的数据被修改后将作用在基础表中。
● SCROLL：表示游标的所有相关操作方式被允许，包括FIRST、LAST、NEXT、RELATIVE等，如果省略该项，那么游标的操作将只有NEXT被允许，如果指定了FAST_FORWARD项，则SCROLL将不能被使用。
● select_statement：查询语句，用来提供游标结果集，该查询语句有一定的限制，不允许使用INTO、COMPUTE、COMPUTE BY等关键词。
● FOR：关键词。
● READ ONLY：表示游标只读，不允许通过游标来修改数据。
● UPDATE：表示指定游标可以修改的列，[ OF column_name [ ,...n ]]将指定具体允许被修改的列名，如果只有UPDATE关键词，则表示所有列都允许被更新。


 
  DECLARE ATriTest_Cusor SCROLL CURSOR            --创建游标
  FOR
  SELECT id,name
  FROM dbo.ATriTest
  说明：游标中必须包含SELECT语句，SELECT语句为游标搜集数据，而DECLARE CURSOR则是声明创建游标的关键词。

在游标被成功打开后，全局变量 @@CORSOR_ROWS 用来记录游标内的数据行数。返回值有四种：

返回值	描述
-m	表示仍在从基础表向游标读入数据，m表示当前在游标中的数据行数
-1	该游标是一个动态游标，其返回值无法确定
0	无符合调剂的记录或游标已经关闭
n	从基础表向游标读入数据已结束，n 为游标中已有的数据记录行数

读取游标

当游标打开后就可以使用 FETCH 命令从游标中逐行地读取数据，以进行相关处理。

读取游标语法格式

FETCH
[[ NEXT | PRIOR | FIRST | LAST | ABSOLUTE{n|@nvar }| RELATIVE { n|@nvar }] FROM ]  -- 读取数据的位置
{{[ GLOBAL] 游标名称} | @游标变量名称 } 
[ INTO @游标变量名称 ][,...n]                                                  -- 将读取的游标数据存放到指定变量中

FETCH语句执行时，可以使用全局变量 @@FETCH_STATUS 返回上次执行 FETCH 命令的状态。在每次用 fetch 从游标中读取数据时，都应检查该变量，以确定上次 FETCH操作是否成功，来决定如何进行下一步处理。返回值有三种：

返回值	描述
0	FETCH命令被成功执行
1	FETCH命令失败或者行数据超过游标数据结果集的范围
2	所读取的数据已经不存在

遍历游标中的数据集

  USE AdventureWorks
  GO
 
  DECLARE ATriTest_Cusor CURSOR      --声明创建游标
  FOR
  SELECT id,name FROM ATriTest       --游标绑定查询语句
  ORDER BY id
 
 OPEN ATriTest_Cusor                    --打开游标
  DECLARE @Id INT, @Name VARCHAR(10)     --声明变量
 
  PRINT'游标结果集中的记录总数为：'+CAST(@@CURSOR_ROWS AS varchar(2))
 
  FETCH NEXT FROM ATriTest_Cusor INTO @ID,@Name
 
  -- 检查Check @@FETCH_STATUS变量，查看FETCH命令是否成功执行
  WHILE @@FETCH_STATUS = 0
  BEGIN
     --输出当前行的记录
     PRINT 'ID：'+CAST(@ID AS char(4))+' 姓名：'+@Name
     --定位到下一行的记录
     FETCH NEXT FROM ATriTest_Cusor INTO @ID,@Name
  END --结束
 
  CLOSE ATriTest_Cusor          --关闭游标
  DEALLOCATE ATriTest_Cusor     --释放
  GO

利用游标修改数据

使用游标的最终目的是修改相关的数据，前面介绍了如何利用游标来逐条提取数据，而要修改游标中的数据则需要指明游标非只读，游标除了使用FOR READ ONLY关键字指明其只读外，在查询语句中使用ORDER BY、DISTINCT等关键词也会使游标只读。

  UPDATE table_name
  {SET col_name = expression }[,...n]
  WHERE CURRENT OF cursor_name

● UPDATE：关键词，表示修改。
● table_name：表名，指需要修改列的所属表名称。
● SET：关键词。
● col_name：准备修改的列名。
● expression：数值或表达式，修改后的值。
● WHERE CURRENT OF：关键词，表示指针所在的当前记录行。
● cursor_name：游标名称。

修改游标中的数据

要求修改表ATriStudent中的数据，把name列中的数据后面都加上“_”，相关脚本如下：

  USE AdventureWorks
  GO
 
  DECLARE ATriStudent_Cusor SCROLL CURSOR    --声明创建游标
  FOR
  SELECT * FROM ATriStudent                  --游标绑定查询语句
  FOR UPDATE OF name                         --允许更新的列
 
  --ATriStudent中的原始记录
  SELECT * FROM ATriStudent
  ORDER BY stunum
 
  OPEN ATriStudent_Cusor                     --打开游标
  FETCH NEXT FROM ATriStudent_Cusor
 
  --检查Check @@FETCH_STATUS变量，查看FETCH命令是否成功执行
  WHILE @@FETCH_STATUS = 0
  BEGIN
  UPDATE ATriStudent
  SET name = name+'_'
  WHERE CURRENT OF ATriStudent_Cusor
 
  --定位到下一行的记录
  FETCH NEXT FROM ATriStudent_Cusor
  END --结束
 
  CLOSE ATriStudent_Cusor                    --关闭游标
  DEALLOCATE ATriStudent_Cusor               --释放
 
  --ATriTest表修改后的记录
  SELECT * FROM ATriStudent
  ORDER BY stunum
  GO

关闭游标 T-SQL 语句

-- 关闭游标
CLOSE curl

删除游标语法格式

DEALLOCATE {{[ GLOBAL] 游标名称} | @游标变量名称 }

游标除了允许修改数据，也可以删除指定的数据：

  DELETE FROM table_name
  WHERE CURRENT OF cursor_name

带游标参数的存储过程

if (object_id('proc_cursor', 'P') is not null)
    drop proc proc_cursor
go
create proc proc_cursor
    @cur cursor varying output
as
    set @cur = cursor forward_only static for
    select id, name, age from student;
    open @cur;
go
--调用
declare @exec_cur cursor;
declare @id int,
        @name varchar(20),
        @age int;
exec proc_cursor @cur = @exec_cur output;--调用存储过程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
    fetch next from @exec_cur into @id, @name, @age;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--删除游标

存储过程

1 概述

存储过程其实就是一系列SQL语句的集合体，我们可以理解为一个封装单元，这个单元可以有出入参数，也可以没有。

首先，存储过程（Stored Procedure）是使用Transact-SQL语言编写的一段能实现指定功能的程序。其次，这种程序被SQL Server编译好后，存放在SQL Server数据库中。用户可以通过存储过程的名称和参数传递调用这些具有指定功能的存储过程。存储过程也是数据库对象。人们通常使用存储过程提高数据库的安全性和减少网络通信数据量。

优点：

存储过程的优点表现在以下几个方面：
（1）存储过程可以嵌套使用，支持代码重用；
（2）存储过程可以接受与使用参数动态执行其中的SQL语句；
（3）存储过程比一般的SQL语句执行速度快。存储过程在创建时就已经被编译，每次执行时，不需要重新编译。而SQL语句每次执行都需要编译；

（4）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加发到他们的证书。用户可以被授权来执行存储过程而不必直接对存储过程中引用的对象具有权限（有点像JAVA面向对象里面的封装）。
（5）存储过程允许模块化程序设计。存储过程一旦创建，以后即可在程序中调用任意多次。这可以改进应用程序的可维护性（有点像JAVA中的分层架构的思想），并允许应用程序同意访问数据库。
（6）存储过程可以减少网络通讯流量。一个需要数百行SQL语句代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。
（7）存储过程可以强制应用程序的安全性。参数化存储过程有助于保护应用程序不受SQL Injection攻击。

分类：

在SQL Server 2008中，存储过程可以分为三大类。
● 系统存储过程（System Stored Procedures）：系统存储过程一般是以“sp_”为前缀的，是由SQL Server 2008自己创建、管理和使用的一种特殊的存储过程，不要对其进行修改或删除。从物理意义上来说，系统存储过程存储在Resource数据库中，但从逻辑意义上来说，系统存储过程出现在系统数据库和用户定义数据库的sys架构中。
● 扩展存储过程（Extended Stored Procedures）：扩展存储过程通常以“xp_”为前缀。扩展存储过程允许使用其他编辑语言（如C#等）创建自己的外部存储过程，其内容并不存在SQL Server 2008中，而是以DLL形式单独存在。不过该功能在以后的SQL Server版本中可能会被废除，所以尽量不要使用。
● 用户自定义存储过程（User-defined Stored Procedures）：由用户自行创建的存储过程，可以输入参数、向客户端返回表格或结果、消息等，也可以返回输出参数。在SQL Server 2008中，用户自定义存储过程又分为Transact-SQL存储过程和CLR存储过程两种：Transact-SQL存储过程，保存Transact-SQL语句的集合，可以接受和返回用户提供的参数；CLR存储过程，该存储过程是针对微软的.NET Framework公共语言运行时（CLR）方法的引用，可以接受和返回用户提供的参数。CLR存储过程在.NET Framework程序中是作为公共静态方法实现的。

2 创建和使用存储过程

在创建存储过程时，要确定存储过程的三个组成部分。
● 输入参数和输出参数。
● 在存储过程中执行的Transact-SQL语句。
● 返回的状态值，指明执行存储过程是成功还是失败。

使用CREATE PROCEDURE语句创建存储过程

使用CREATE PROCEDURE语句创建存储过程
 
 CREATE { PROC｜PROCEDURE }
     [schema_name.] procedure_name [ ; number ]          --架构名。存储过程名[;分组]
     [ { @parameter [ type_schema_name. ] data_type }     --参数
       [ VARYING ] [ = default ] [ [ OUT [ PUT ]         --作为游标输出参数
     ] [ ,...n ]
 [ WITH <procedure_option> [ ,...n ]
 [ FOR REPLICATION ]                    --不能在订阅服务器上执行为复制创建的存储过程
 AS { <sql_statement> [;][ ...n ]       --存储过程语句
｜<method_specifier> }
 [;]
 <procedure_option> ::=
     [ ENCRYPTION ]                      --加密
     [ RECOMPILE ]                       --不预编译
     [ EXECUTE_AS_Clause ]      --执行存储过程的安全上下文
 <sql_statement> ::=
 { [ BEGIN ] statements [ END ] }                        --存储过程语句
 <method_specifier> ::=
 EXTERNAL NAME assembly_name.class_name.method_name      --指定程序集方法

其参数解释如下。
● schema_name：架构名。
● procedure_name：存储过程名。
● number：对同名过程进行分组的选项，使用drop procedure语句可以将这些分组过程一起删除。
● @parameter：存储过程的参数。
● [ type_schema_name. ] data_type：参数的架构及类型。
● VARYING：指定作为输出参数支持的结果集，仅适用于cursor参数。
● default：参数的默认值，如果定义了default值，则无须指定此参数的值也可执行存储过程。
● OUTPUT：输出参数，此选项的值可以返回给调用存储过程的语句。
● ENCRYPTION：加密存储过程。
● RECOMPILE：指明该存储过程在运行时才编译，不预编译。
● EXECUTE_AS_Clause：指定执行存储过程的安全上下文。
● FOR REPLICATION：不能在订阅服务器上执行为复制创建的存储过程。
● <sql_statement>语法块：存储过程执行的T-SQL语句。
● <method_specifier>语法块：指定.NET Framework程序集的方法，以便CLR存储过程引用。

无参存储过程

--存储过程相当于一个函数 可以有参数，输出参数 as后为方法体 使用return进行返回--
create proc usp_getAllMoneyInfo
--as前面为定义参数 相当于函数中的参数， 不用进行声明--
as
select * from Money

执行存储过程

--执行存储过程

exec usp_getAllMoneyInfo;

删除存储过程

--删除存储过程
drop proc usp_getAllMoneyInfo;
go

修改存储过程

alter proc usp_getAllMoneyInfo
as
select Money.money from Money

创建一个带参数的存储过程

drop proc usp_getMoneyInfo
--创建一个带参数的存储过程 并执行
create proc usp_getMoneyInfo
--as前面为定义参数 相当于函数中的参数 不用进行声明
@id int
as
select * from Money where id=@id
exec usp_getMoneyInfo 2 --在调用后面添加参数

创建一个带默认值参数的存储过程

CREATE PROC procGetAvgMaxMin
    @course_name  char(20)=’信息基础’
AS
……
有了默认值后，当用户使用EXEC调用过程时，如果没有提供该参数值，则会自动将“信息基础”作为输入参数的值。
输入参数的默认值也可以是NULL值。
CREATE PROC proc1
    @course_name  char(20)=NULL
AS
IF @course_name IS NULL
PRINT '请您提供课程名称'
ELSE
SELECT  AVG(exam) AS平均分,
        MAX(exam) AS最高分,
        MIN(exam) AS最低分
FROM   score AS s
        INNER JOIN course AS c
        ON s.cno=c.cno
WHERE  c.cname=@course_name

创建一个存储过程proc2，用于求指定数值的阶乘。

CREATE PROC proc2
        @x int,
        @y int OUTPUT /*声明变量y为输出参数*/
AS
/*声明两个局部变量i和t，并为其分别赋值为1*/
DECLARE @i int,@t int
SELECT @i=1,@t=1
/*使用循环语句，计算x的阶乘*/
WHILE @i<=@x
    BEGIN
      SELECT @t=@t*@i
      SELECT @i=@i+1
    END
/*将t的值，赋值给了输出参数y*/
SELECT @y=@t

创建一个带output参数的存储过程

--创建一个带output参数的存储过程 并执行
if exists(select * from sysobjects where name='usp_getAllMoneyCount')
drop proc usp_getAllMoneyCount
go
create proc usp_getAllMoneyCount
@count int output --外部参数在后面加output类似于c#的out
as
set @count = (select count(*) from Money)
go

declare @cnt int;
exec usp_getAllMoneyCount @count=@cnt output --在调用的时候 要加output的关键字
print @cnt

创建一个带有返回值的存储过程(主要区别是有return)

--创建一个带有返回值的存储过程 并执行
--带有返回值的存储过程--
--return只能返回整型的数据，如果需要返回其他类型的数据，需要使用output输出参数--
if exists(select * from sysobjects where name='usp_getCount')
drop proc usp_getCount
go
create proc usp_getCount
as
declare @count int 
set @count = (select count(*) from Money)
return @count
go
declare @totalCount int
exec @totalCount = usp_getCount
print @totalCount

视图

1、视图基础

视图由一个预定义的查询（SELECT语句）组成，可以像基本表一样用于SELECT语句中。如果视图满足一定条件，还可以用在INSERT、UPDATE和DELETE语句中。

视图里存放了SELECT语句而并非是查询结果。每次在SQL语句中使用视图，其实就是在执行视图内存放的SELECT语句，因此通过视图总能够得到最新的数据。

2、视图的创建

（1）在SSMS中创建视图（略）
（2）使用CREATE VIEW语句创建视图

CREATE VIEW  视图名称 [(字段1,字段2…)]
AS
SELECT查询语句
[WITH CHECK OPTION]

其中，必须提供视图名称，视图名称后的[(字段1,字段2…)]为可选项，如果不提供字段名，则隐含视图由SELECT子句中列出的各字段组成。但在下列三种情况下必须明确指定组成视图的所有字段名。
● SELECT子句中的某个列不是单纯的字段，而是集合函数或表达式。
● 多表连接时选出了几个同名字段，作为视图的字段。
● 需要在视图中为某个字段设置更合适的新名字。
注意：如果提供视图的字段名，则必须全部提供，不能只提供一部分。

（3）用别名命名视图字段

创建视图vw_ boy1，用于将表stu_info中全部男生的信息显示出来，并给相应字段设置中文别名。

CREATE VIEW  vw_boy1(学号,姓名,性别,出生日期,电子信箱,手机号码,所属院系)
AS
SELECT  *
FROM  stu_info
WHERE  sex='男'

(4) 创建视图时的注意事项

在用CREATE VIEW创建视图时，SELECT子句里不能包括以下内容：
● COMPUTE、COMPUTE BY子句
● ORDER BY子句，除非在SELECT子句里有TOP关键字
● OPTION子句
● INTO关键字
● 临时表或表变量

3 、操作视图

（1）用ALTER VIEW修改视图

ALTER VIEW  视图名称 [(字段1,字段2…)]
AS
SELECT查询语句
[WITH CHECK OPTION]

所谓操作视图数据，其实是通过视图在操作其基表的数据而已

（2）使用INSERT语句插入数据

 
INSERT INTO vw_boy
VALUES ('0018','蒋十九','男','1988-05-29',NULL,NULL,'计算机系')
GO
SELECT *
FROM vw_boy
GO

（3 ）使用UPDATE语句更新数据

在视图上使用UPDATE语句也可以更新基表的数据。并不是所有视图都能够更新数据，以下几种视图不能用于更新。
● 表值函数返回的结果只有在某些情况下才能更新。
● 如果查询或视图所包括的列来自多个表或视图，则不能更新这些查询或视图。
● 不能更新使用GROUP BY或DISTINCT子句的查询或视图。
● 不能更新存储过程返回的结果。

UPDATE  vw_boy
SET     depart='外语系'
WHERE  sname='周伦杰'
GO
SELECT  *
FROM   vw_boy
GO

（4）使用DELETE语句删除数据

DELETE FROM  vw_boy
WHERE      sname = '张三'

（5）使用DROP VIEW语句删除视图

DROP VIEW  视图名称

触发器

1 认识触发器

触发器是由一系列的T-SQL编写完成的，和存储过程类似。也可以说它是一种特殊的存储过程。在数据库中，触发器有着和“约束条件”类似的功能，利用它可以解决常规“约束条件”解决不了的问题。

1.1 什么是触发器
触发器由Transact-SQL编写并存储在SQL Server服务器中，但触发器本身的调用和存储过程调用却是不一样的。存储过程由用户、应用程序、触发器或其他过程调用。触发器只能由数据库的特定事件来触发，所谓的特定事件主要包括如下几种类型的事件。
（1）用户在指定的表或视图做DML操作，主要包括如下几种：
● INSERT操作，在特定的表或视图中增加数据。
● UPDATE操作，对特定的表或视图修改数据。
● DELETE操作，删除特定表或视图的数据。
（2）用户做DDL操作，主要包括如下几种：
● CREATE操作，创建对象。
● ALTER操作，修改对象。
● DROP操作，删除对象。
（3）用户进行LOGON操作。
● 与SQL Server实例建立连接。
当创建触发器的类型同指定的事件相匹配时，触发器将会被激发，其他情况下触发器不会被激发，这一点和存储过程不一样。
1.2 触发器的作用
触发器的运用虽然很耗费数据库系统的性能，但它所起的作用却比较特殊。在SQL Server中触发器可以实现数据的完整性，并保证复杂业务规则的强制执行。这主要表现在以下几个方面：
● 利用触发器可以执行相对复杂的业务操作。增加、删除、修改数据是对表最基本的操作方式。这种操作方式只能完成固定的数据变动，而使用触发器则在完成数据变动的基础上做额外的操作，以达到完成特殊业务的目的。
● 可以防止无意义的数据操作。利用触发器可以对符合某些条件的数据加以保护，使其不能被随意改动。
● 用于级联操作数据。当一个表中的数据有变动时，可以利用触发器修改这些变动数据在其他表中的关联数据（正常情况下可以利用外键进行限制）。
● 保证数据的同步复制。
● 利用触发器可以跟踪对数据库的操作。在指定的表或视图中设置触发器，当记录被改变时，利用触发器把数据变动日志记录下来。
● 允许或限制修改某些表，利用触发器可以限制表的变动。
1.3 触发器分类
在SQL Server 2008中可以使用的触发器可以分为4大类，分别是DML触发器、DDL触发器、CLR触发器及登录触发器。其中最常用的是DML触发器。
（1）DML触发器。这种类型的触发器可以称为数据操纵语言（DML）触发器。它作用在表或视图上，在对表或视图进行DML操作时会激发该类型的触发器，该类型触发器包括：
● AFTER触发器，此类型触发器会在执行INSERT、UPDATE及DELETE操作后被激发并执行。它被激发的时机是在表或视图中的数据修改之后。
● INSTEAD OF触发器，此类型的触发器会用触发器本身的操作替换原来的操作（INSERT，UPDATE，DELETE）。也就是说，当对该触发器作用对象进行DML操作时，DML操作并不会被执行，而是被触发器中的操作所替换。它通常作用在视图上，利用该触发器可以使得视图变成可更新视图。
（2）DDL触发器。被称为数据定义语言（DDL）触发器。当CREATE，ALTER，DROP对象时及进行其他DDL操作时会激发相关的触发器，利用它可以影响数据库业务规则。
（3）CLR触发器。该类触发器在.NET Framework中创建，不像其他触发器需要执行T-SQL过程。
（4）登录触发器。当与SQL Server实例建立连接时会激发该类触发器。
2 创建触发器
2.1 触发器工作原理
触发器的原理涉及两张虚拟的表，这两张虚拟的表分别是INSERTED表和DELETED表。
● INSERT操作的触发器。当增加数据时，会在数据表和INSERTED表中同时放入数据。利用INSERTED表，可以得到已经插入的数据，可以利用该数据库进行业务对比操作。
● DELETE操作的触发器。当从数据表中删除数据时，数据首先被放到DELETED表中。该表是一张存放了已经删除的数据的虚拟表。在触发器中可以调用该表中的数据。
● UPDATE操作的触发器。该类型触发器和其他两种不一样，当对触发器所在的表执行UPDATE语句时，原数据会被转移到DELETED表中，而修改后的数据则被插入到INSERTED表中。最后触发器检查这两个表的数据，并更新数据表。
2.2 触发器语法结构
（1）数据操纵语言（DML）触发器语法。

  CREATE TRIGGER [ schema_name . ]trigger
  ON { table｜view }
  [ WITH <trigger_option> [ ,...n ] ]
  { FOR｜AFTER｜INSTEAD OF }
  { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
  [ WITH APPEND ]
  [ NOT FOR REPLICATION ]
  AS { sql_statement  [ ; ] [ ,...n ]
          ｜EXTERNAL NAME assembly_name.class_name.method_name[ ; ] }
 
  <trigger_option> ::=
  [ ENCRYPTION ]
  [ EXECUTE AS Clause ]
  
  
  【语法说明】
● CREATE TRIGGER项：表示创建触发器的关键词。
● schema_name项：表示触发器所属的架构名称。在SQL Server中触发器作用域是以架构为单位的。
● trigger项：触发器的名称。
● ON { table｜view }项：触发器所作用的表或视图。DML类型触发器不能作用在局部或全局临时表上。
● FOR｜AFTER项：AFTER表示触发器被激发的时机。它在SQL所有的操作都完成，并且约束检查完成后被激发。默认是AFTER。
● INSTEAD OF项：表示替换类型的触发器。对每个INSERT、UPDATE或DELETE语句只能定义一个INSTEAD OF触发器。
● [ INSERT ] [ UPDATE ] [ DELETE ]项：激发触发器的操作，这里可以选取任意的组合。
● WITH APPEND项：指定添加一个已有类型的触发器。但如果显式声明了AFTER类型触发器，或触发器类型是INSTEAD OF时，则不能使用该项。
● NOT FOR REPLICATION项：当复制代理修改涉及触发器的表时，不应执行触发器。
● sql_statement项：可以是确定触发器具体操作的判断条件和操作。
● EXTERNAL NAME assembly_name.class_name.method_name[ ; ]项：针对CLR触发器，指定程序集与触发器绑定的方法。
● ENCRYPTION项：表示对创建触发器语句进行模糊处理。
● EXECUTE AS项：指定用于执行该触发器的安全上下文。

2 管理触发器

利用T-SQL修改触发器

修改DML类型触发器的语法结构。

 ALTER TRIGGER schema_name.trigger
 ON ( table｜view )
 [ WITH <trigger_option> [ ,...n ] ]
 ( FOR｜AFTER｜INSTEAD OF )
 { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
 [ NOT FOR REPLICATION ]
 AS { sql_statement [ ; ] [ ...n ]
｜EXTERNAL NAME assembly_name.class_name.method_name[ ; ] }
 <dml_trigger_option> ::=
     [ ENCRYPTION ]
     [ <EXECUTE AS Clause> ]

修改DDL类型触发器的语法结构。

ALTER TRIGGER trigger
ON { DATABASE｜ALL SERVER }
[ WITH <trigger_option> [ ,...n ] ]
{ FOR｜AFTER } { event_type [ ,...n ]｜event_group }
AS { sql_statement [ ; ]
   ｜EXTERNAL NAME assembly_name.class_name.method_name [ ; ] }
<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ <EXECUTE AS Clause> ]

要求创建触发器trg_chk_i，并对其进行修改：

  --创建触发器
  CREATE TRIGGER trg_chk_i
       ON dbo.ATriTest
       AFTER insert
       AS
       BEGIN
           print'创建trg_chk_i完成！';
       END
 
  --修改触发器
  ALTER TRIGGER trg_chk_i
      ON dbo.ATriTest
       AFTER insert
       AS
       BEGIN
           print'修改trg_chk_i完成！';
       END

3 删除触发器

删除触发器的语法结构如下。
（1）删除DML触发器语法结构：

DROP TRIGGER [schema_name.]trigger_name [ ,...n ] [ ; ]

（2）删除DDL触发器语法结构：

DROP TRIGGER trigger_name [ ,...n ]
ON { DATABASE｜ALL SERVER } [ ; ]

（3）删除登录触发器语法结构：

DROP TRIGGER trigger_name [ ,...n ]
ON ALL SERVER

例子：

  USE AdventureWorks
  GO
 
  DROP TRIGGER dbo.trg_ATriStudent_i1;
  GO

4 禁用触发器

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ]｜ALL }
ON { object_name｜DATABASE｜ALL SERVER } [ ; ]

【语法说明】
● DISABLE TRIGGER项：表示禁用触发器的关键词。
● ALL项：表示禁用ON子句中定义的所有触发器。
● object_name项：表示触发器所在的表或视图。
● DATABASE项：表示整个数据库都是禁用的作用范围，针对DDL触发器。
● ALL SERVER项：表示服务器都是禁用的作用范围，针对DDL触发器和登录触发器。

利用T-SQL禁用触发器。

要求禁用ATriTest表下的trg_ATriTest_i触发器：

  USE AdventureWorks
  GO
 
  DISABLE TRIGGER dbo.trg_ATriTest_i
  ON  ATriTest

说明：禁用触发器对开发人员来说是个不错的选择，因为在实际开发过程中，某项业务很可能是暂时不使用，而不是永久放弃，所以，建议读者当触发器不需要时，尽量禁用

5 启用触发器

ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ]｜ALL }
ON { object_name｜DATABASE｜ALL SERVER } [ ; ]

要求启用ATriTest表下的trg_ATriTest_i触发器：

  USE AdventureWorks
  GO
 
  ENABLE TRIGGER dbo.trg_ATriTest_i
  ON  ATriTest

在SQL Server中触发器的顺序只能指定第一个被激发的触发器和最后一个被激发的触发器，这一点和Oracle不同
对于暂时不使用的触发器，建议开发人员对其进行禁用操作，而不是删除操作（除非确认以后不再使用）。

SQL的自定义函数

创建使用标量函数

将用户定义函数分为两大类，分别是标量函数和表值函数，其中表值函数又被分为内嵌表值函数和多语句表值函数

SQL中的存储过程与函数。为什么放在一起讲，一是因为单独讲存储过程或者函数，其实就那么多东西，再者是因为这两类对象有一定的相似性。

 CREATE function age_cs 
 (@ksrq  date,
 @jsrq  date)
 returns VARCHAR (16) 
as
begin
declare @days int ,@year int,
        @day int,@age  VARCHAR (16) 
select @days=datediff(day,@ksrq,@jsrq)
select @year=@days/365
select @day=@days%365

select @age= convert(varchar(3),@year)+'岁'
             +convert(varchar(3),@day)+'天'
return(@age)
 end

调用

SELECT *
FROM dbo.MyTableFunction()

我们先调用一下，看看结果

 select dbo.age_cs('1991-01-01','2021-01-26')

解析

（1）函数的入参必须用括号包住，定义完入参以后紧接着就需要定义返回的参数类型，正文部分必须用begin…end包裹住；

（2）我们先定义了四个变量：@days总天数, @year年数,@day天数,@age输出的年龄。

先用datediff函数算出总天数，然后年数等于总天数除以365得到的整数，因为两个字段都是int型的，所以直接除下来不会有小数。

再用总天数%365得到余天数，注意%是取余的意思。

最后我们再把算出来的年龄，拼接起来赋值给@age。

（3）用return(@age) 输出结果。

这样，我们就得到了算年龄的函数，把它创建在数据库里，随时想用就随时调，不用再每次都去写一大段语句。函数调用需要加上dbo，也就是数据库所有者

创建使用表值函数

CREATE FUNCTION funcGetStuDepa
    ( @depa_name char(20))
RETURNS TABLE
AS
RETURN(SELECT *
    FROM  student
    WHERE  所属院系= @depa_name)

多语句函数

如果RETURNS子句指定的TABLE类型带有字段及其数据类型，则该函数是多语句表值函数。多语句函数的主体中允许使用多种语句。
● 赋值语句。
● 控制流语句。
● DECLARE语句，该语句定义函数局部的数据变量和游标。
● SELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量。
● 游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以INTO子句向局部变量赋值的FETCH语句；不允许使用将数据返回到客户端的FETCH语句。
● INSERT、UPDATE、DELETE语句，这些语句修改函数的局部table类型的变量。
● 调用存储过程的EXECUTE语句。
下面是多语句函数的语法格式。

CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] )
RETURNS @return_variable TABLE < table_type_definition >
[ WITH < function_option > [ [,] ...n] ]
[ AS ]
BEGIN
  function_body
  RETURN
END

CREATE FUNCTION funcGetStuScore
  (@stu_name char(20))
RETURNS @temp TABLE
    (姓名char(20),
    课名char(20),
    平时成绩int,
    考试成绩int)
AS
BEGIN
  /*下面的语句将SELECT语句的查询结果，插入到临时表变量@temp中*/
  INSERT INTO @temp
  SELECT st.姓名,c.课名,s.平时成绩,s.考试成绩
  FROM  student AS st
    INNER JOIN score AS s
    ON  st.学号=s.学号
    INNER JOIN course AS c
    ON  c.课号=s.课号
  WHERE  st.姓名=@stu_name
  ORDER BY s.考试成绩DESC
RETURN  /*将临时表变量@temp的结果返回给调用语句*/
END

下面的语句调用函数funcGetStuScore，查询“张三”的所有课程的成绩。

SELECT *
FROM  funcGetStuScore('张三')

查看与修改用户自定义函数

用户自定义函数的定义存放在sys.sql_modules视图中，在sys.sql_modules中就可以查看到所有用户自定义的函数了。
修改用户自定义函数使用的是ALTER FUNCTION语句，其他的语法与创建用户自定义函数一样，这里就不过多说明了。其实使用ALTER FUNCTION命令就相当于是创建了一个重名的函数。但是使用ALTER FUNCTION命令不能更改用户自定义函数的类型，例如不能将标量函数更改为内联表值函数或者多语句表值函数。

删除用户自定义函数

DROP FUNCTION { [ owner_name .] function_name } [ ,...n ]

例如，删除用户定义函数func1的语句为：
DROP FUNCTION  func1

索引的创建

CREATE [ UNIQUE ] [ CLUSTERED｜NONCLUSTERED ] INDEX index_name
ON table_or_view_name
( column [ ASC｜DESC ] [ ,...n ] )
[with
[PAD_INDEX = { ON｜OFF }]
[[,]FILLFACTOR=fillfactor][[,]IGNORE_DUP_KEY = { ON｜OFF }]
[[,]DROP_EXISTING = { ON｜OFF }]
    [[,]STATISTICS_NORECOMPUTE = { ON｜OFF }]
[[,]SORT_IN_TEMPDB = { ON｜OFF }]
]
[ ON filegroup ]

【语法说明】
● CREATE，INDEX：创建索引关键词。
● UNIQUE：表明该索引是唯一索引。唯一索引不允许两行具有相同的索引键值，视图的聚集索引必须唯一。
● CLUSTERED：表示创建聚集索引。创建聚集索引时会重新生成表中现有的非聚集索引。如果没有指定CLUSTERED，则创建非聚集索引。
● NONCLUSTERED：表示创建非聚集索引。每个表都最多可包含249个非聚集索引。
● index_name：表示索引的名称，索引名称要求在表或视图中必须唯一（数据库中不做要求）。
● column：为索引所作用的列，可以有多个。如果指定两个或多个列名，那么可以为指定列的组合值创建组合索引，同时可以在后面的括号中，按排序优先级列出组合索引中要包括的列。
● ASC｜DESC：指定特定索引列的升序或降序排序方向，默认值为升序。
● PAD_INDEX：索引填充，默认为OFF。
● FILLFACTOR=fillfactor：定一个百分比，指示在创建或重新生成索引期间，数据库引擎对各索引页的叶级填充的程度。fillfactor必须为介于1至100之间的整数值。默认值为0。
● IGNORE_DUP_KEY：指定在插入操作尝试向唯一索引插入重复键值时的错误响应。
● DROP_EXISTING：指定应删除并重新生成已命名的先前存在的聚集或非聚集索引。默认值为OFF。
● STATISTICS_NORECOMPUTE：指定是否重新计算分发统计信息。默认值为OFF。
● SORT_IN_TEMPDB：指定是否在tempdb中存储临时排序结果。默认值为OFF。
● ON filegroup：用于指定存放索引的文件组。

丘丘人

关注

27
点赞
踩
26

收藏

觉得还不错? 一键收藏
0
评论
SQL Server语法规则及示例

游标是一种处理数据的方法，具有对结果集进行逐行处理的能力。可以将游标看作一种特殊的指针，它与某个查询结果集相关联，可以指向结果集的任意位置，可以将数据放在数组、应用程序中或其他的地方，允许用户对指定位置的数据进行处理。游标函数将用户定义函数分为两大类，分别是标量函数和表值函数，其中表值函数又被分为内嵌表值函数和多语句表值函数SQL中的存储过程与函数。为什么放在一起讲，一是因为单独讲存储过程或者函数，其实就那么多东西，再者是因为这两类对象有一定的相似性。asbegin。
复制链接

扫一扫