MSSQL基础-2

第六章 T-SQL

T-SQL

​ T-SQL（Transact Structured Query Language）是标准的Microsoft SQL Server的扩展。是标准的SQL程序设计语言的增强版，是用来让程序与SQL Server沟通的主要语言。T-SQL语言主要由以下几部分组成：

​ 1.数据定义语言：用于数据库系统中对数据库、表、视图、索引等数据库对象进行创建和管理。

​ 2.数据控制语言：用于实现对数据库中数据的完整性，安全性等的控制。

​ 3.数据操纵语言：用于插入、修改、删除和查询数据库中的数据。

T-SQL 语言结构如下：

SELECT 子句

[INTO 子句]

FROM 子句

[WHERE 子句]

[GROUP BY 子句]

[HAVING 子句]

[ORDER BY 子句]

常量

​ 数据在内存中存储始终不变化的量叫常量。常量，也称为文字值或标量值，是表示一个特定数据值的符号。常量的格式取决于它所表示的值的数据类型。

常见的常量有：

1. 数字常量
2. 字符串常量
3. 日期和时间常量
4. 符号常量

变量

​ 数据在内存中存储可以变化的量叫变量。为了在内存存储信息，用户必须指定存储信息的单元，并为该存储单元命名，以方便获取信息，这就是变量的功能。Transact-SQL可以使用两种变量，一种是局部变量，另外一种是全局变量。局部变量和全局变量的主要区别在于存储的数据作用范围不一样。
​ 局部变量是用户可自定义的变量，它的作用范围仅在程序内部。局部变量的名称是用户自定义的，命名的局部变量名要符合SQL Server 200S标识符命名规则，局部变量名必须以
@开头。
​ 全局变量是SQL Server系统内部事先定义好的变量，不用用户参与定义，对用户而言，其作用范围并不局限于某一程序，而是任何程序均可随时调用。

DECLARE @SONG CHAR(20)
SET @SONG='China'
PRINT @SONG

DECLARE @STR CHAR(20),@A INT,@B INT
SELECT @STR='USA',@A=200,@B=123
PRINT @STR
PRINT @A
PRINT @B

注释符 运算符与通配符

​ 注释语句不是可执行语句，不参与程序的编译，通常是一些说明性的文字，对代码的功能或者代码的实现方式给出简要的解释和提示。
​ 运算符是一种符号，用来进行常量、变量或者列之间的数学运算和比较操作，它是Transact-SQL语言很重要的部分。运算符有几种类型，分别为：算术运算符、赋值运算符、比较运算符、逻辑运算符、位运算符、连接运算符。
​ 在涉及模式匹配的字符串比较中使用%、_（下划线）、[]、[^]等通配符。

注释符

--我是单行注释1
--我是单行注释2

/*
我是多行注释1
我是多行注释2
*/

运算符

---运算符 取余
DECLARE @x int,@y int,@z int
SELECT @x=2,@y=5
SET @z=@x%@y
PRINT @z

逻辑运算符

优先级：NOT > AND > OR

---逻辑运算符
SELECT * FROM Student
WHERE 性别='女' AND 年龄>24

连接运算符

---连接运算符
DECLARE @name CHAR(20)
SET @name='基督山伯爵'
PRINT '该小说名称是：'+@name

通配符

​ 匹配指定范围内或者属于方括号所指定的集合中的任意单个字符。可以在涉及模式匹配的字符串比较（例如 LIKE 和 PATINDEX ）中使用这些通配符

在SQL中通常用LINK关键字与通配符结合起来实现模糊查询。其中SQL支持的通配符如表所示。

通配符	描述	示例
%	包含零个或更多字符的任意字符	“张%” 可以表示：“张三”、“张四”、“张有人”
_下划线	任何单个字符	“张_” 可以表示：“张三”、“张四”
[]	指定范围[a~z]或集合[abcdef]中的任何单个字符	[09]123表示09之间任意一个字符开头,以‘123’结尾的字符
[^]	不属于指定范围（[a-f]）或集合（[abcdef]）的任何单个字符	[^05]123表示05之间任意一个字符开头,却以‘123’结尾的字符

流程控制

​ 流程控制语句是用来控制程序执行流程的语句。使用流程控制语句可以提高编程语言的处理能力。与程序设计语言（如C语言）一样，Transact-SQL语言提供的流程控制语句如下所示

BEGIN…END WAITFOR GOTO WHILE IF..ELSE BREAK RETURN CONTINUE

DECLARE @x int,@y int,@t int
SELECT @x=1,@y=2
BEGIN
SET @t=@x
SET @x=@y
SET @y=@t
END
PRINT @x
PRINT @y

DECLARE @x int
SET @x=3
if @x>0
PRINT '@x是正数'
PRINT 'end'

DECLARE @y int
SET @y=8
IF @y%2=0
PRINT '@y是偶数'
PRINT 'end'

DECLARE @q int,@w int
SELECT @q=3,@w=5
IF @q>@w
PRINT '@q大于@w'
ELSE
PRINT '@q小于@w'
---判断象限
DECLARE @x2 int,@y2 int
SELECT @x2=8,@y=-3
IF @x2>0
	IF @y>0
		PRINT '@x2@y2位于第一象限'
	ELSE
		PRINT '@x2@y2位于第四象限'
ELSE
	IF @y2>0
		PRINT '@x2@y2位于第二象限'
	ELSE
		PRINT '@x2@y2位于第三象限'

DECLARE @n int,@sum int
SELECT @n=1,@SUM=0
WHILE @n<=10
BEGIN
SET @sum=@sum+@n
SET @n=@n+1
END
PRINT @sum
--计算10以内的偶数和
DECLARE @n2 int,@sum2 int
SELECT @n2=1,@sum2=0
WHILE @n2<=10
BEGIN
SET @n2=@n2+1
IF @n2%2=0
SET @sum2=@sum2+@n2
ELSE
CONTINUE
END
PRINT @sum2
---GOTO 跳转
DECLARE @x3 int
SELECT @x3=1
PP:
	PRINT @X3
	SELECT @x3=@x3+1
WHILE @x3<=3 GOTO PP
---等待3秒后执行
WAITFOR DELAY'00:00:03'
PRINT '新年快乐'

CASE函数

CASE函数用于计算条件列表并返回多个可能结果表达式之一

CASE函数具有两种格式：

简单CASE函数将某个表达式与一组简单表达式进行比较以确定结果

CASE搜索函数计算一组布尔表达式以确定结果

两个格式都支持可选的ELSE参数。

---考试分数分等级
SELECT title,stuid,SCO,
等级=CASE
WHEN sco >= 90 THEN '优秀'
WHEN sco >= 80 AND sco <90 THEN '良好'
WHEN sco >= 70 AND sco <80 THEN '中等'
WHEN sco >= 60 AND sco <70 THEN '及格'
ELSE '不及格'
END
FROM stuco

第七章 函数

​ 在SQL Server中提供了许多内置函数，按函数种类可以分为聚合函数、数学函数、字符串函数、日期时间函数、转换函数和元数据函数6种。

聚合函数

---查询某学科总分
SELECT SUM(Grade) AS 总分 FROM sc WHERE Cno=001
---查询某学科平均分
SELECT AVG(Grade) AS 平均分 FROM sc WHERE Cno=001
---查询某学科最低分
SELECT MIN(Grade) AS 最低分 FROM sc WHERE Cno=001
---查询某学科最高分
SELECT MAX(Grade) AS 最高分 FROM sc WHERE Cno=001
---查询某学科分数计数
SELECT COUNT(Grada) AS 分数计数 FROM sc WHERE Cno=001

数学函数

​ 数学函数可以对数据类型为整形（intger）、实型（real）、浮点型（float）、货币型（money）和smallmoney的列进行操作。它的返回值是6位小数，如果使用出错，则返回NULL值并显示提示信息，通常该函数可以用在SQL语句表达式中。常用的数学函数及说明如表所示

函数名称	说明	示例	结果
ABS	返回指定的数字表达式的绝对值	SELECT ABS(-1)	1
COS	返回指定的表达式中指定的弧度的三角余玄值
COT	返回指定的表达式中指定的弧度的三角余切值
PI	返回值为圆周率	SELECT PI()	3.14159265358979
POWER	将指定的表达式乘指定次方	SELECT POWER(2,3)	8
RAND	返回0~1之间的随机float值	SELECT RAND()	随机的0.286702661673299
ROUND	将数字表达式四舍五入为指定的长度或精度	SELECT ROUND(123.9994,3)	123.9990
SIGN	返回指定表达式订单零（0）、正号（+）或负号（-）
SIN	返回指定表达式中指定弧度的三角正玄值
SQUARE	返回指定表达式平方	SELECT SQUARE(2)	4
SQRT	返回指定表达式平方根	SELECT SQRT(4)	2
TAN	返回指定表达式中指定弧度的三角正切值

字符串函数

​ 字符串函数作用于char、varchar、binary和varbinary数据类型以及可以隐式转换为char或varchar的数据类型。常用的数学函数及说明如表所示

函数名称	说明	示例	结果
ASCII	返回字符表达式最左端字符的ASCII代码值	SELECT ASCII(‘a’)	97
CHARINDEX	返回字符串中指定表达式的起始位置	SELECT CHARINDEX(‘中’,’美中不足’)	2
LEFT	从左边开始，取得字符串左边指定个数的字符	SELECT LEFT(‘张三’,1)	张
LEN	返回指定字符串的字符（而不是字节）个数	SELECT LEN(‘张三’)	2
REPLACE	将指定的字符串替换为另一指定的字符串	SELECT REPLACE(‘法外狂徒张三’,’张三’,’李四’)	法外狂徒李四
REVERSE	返回字符表达式的反转	SELECT REVERSE(‘法外狂徒张三’)	三张徒狂外法
RIGHT	从右边开始，取得字符串左边指定个数的字符	SELECT RIGHT(‘诸葛孔明’,2)	孔明
STR	返回由数字数据转换来的字符数据	SELECT STR(3.1415,10)	‘ 3’
SUBSTRING	返回指定个数的字符	SELECT SUBSTRING(‘法外狂徒张三’,3,2)	狂徒

日期时间函数

​ 日期和时间函数主要用来操作datetime、smalldatetime类型的数据，日期和时间函数执行算术运行与其他函数一样，也可以在SQL语句的SELECT、WHERE子句以及表达式中使用。常用的数学函数及说明如表所示

函数名称	说明	示例	结果
DATEADD	在向指定日期加上一段时间的基础上，返回新的datetime值	SELECT DATEADD(“YEAR”,100,’1949-10-1’)	2049-10-01 00:00:00.000
DATEDIFF	返回跨两个指定日期的日期和时间边界数	SELECT DATEDIFF(YEAR,’1949-10-01’,’2022-10-01’)	73
GETDATE	返回当前系统日期和时间	SELECT GETDATE()	‘2022-02-12 13:19:41.890’
DAY	返回指定日期中的天的整数	SELECT DAY(‘2022-01-15’)	15
MONTH	返回指定日期中的月份的整数	SELECT DAY(‘2022-01-15’)	1
YEAR	返回指定日期中的年份的整数	SELECT DAY(‘2022-01-15’)	2022

转换函数

​ 当遇到类型转换的问题时，可以使用SQL Server所提供的CAST和CONVERT函数。这两种函数不但可以将指定的户籍类型转为另一种数据类型，还可用来获得各种特殊的数据格式。CAST和CONVERT函数都可用于选择列表、WHERE子句和允许使用表达式的任何地方。

​ 在SQL Server中数据类型转换分为两种，分别如下：

• 隐性转换：SQL Server自动处理某些数据类型的的转换。例如，如果比较char和datetime表达式、smallint和int表达式、或不同长度的char表达式，SQL Server可将它们自动转换，这种转换称为隐性转换，对这些转换不必使用CAST函数。

• 显示转换：显示转换是指CAST和CONVERT函数，CAST和CONVERT函数将数值从一种数据类型（局部变量、列或其他表达式）转换到另一种数据类型。

SELECT CAST('法外狂徒张三' AS nvarchar(4))
---结果
法外狂徒

元数据函数

​ 元数据函数描述了数据的结构和意义，它主要用于返回数据库中的相应信息，其中包括

返回数据库中数据表或视图的个数和名称。

返回数据库中数据字段的名称、数据类型、长度等描述信息。

返回数据库中定义的约束、索引、主键或外键等信息。

函数名称	说明	示例	结果
COL_LENGTH	返回列的定义长度（以字节为单位）	SELECT COL_LENGTH(‘GraspcwZt’,’fullname’)	50
COL_NAME	返回数据库列的名称，该列具有相应的表标识号和列标识号	SELECT COL_NAME(OBJECT_ID(‘GraspcwZt’),1)	order
DB_NAME	返回数据库名	SELECT DB_NAME()	master
OBJECT_ID	返回数据库对象标识号	SELECT OBJECT_ID(‘GraspcwZt’)	1435152158

第八章 视图

​ 视图是一种常用的数据库对象，它将查询的结果以虚表的形式存储在数据中。视图并不在数据库中以存储数据集的形式存在。视图的结构和内容是建立在对表的查询基础之上的，和表一样包括行和列，这些行和列都来源于其所引用的表，并且是在引用视图过程中动态生成的。
​ 视图中的内容是由查询定义来的，并且试图和查询都是通过SQL语句定义的，它们有着许多相同和不同之处。具体如下：
（1）存储：视图存储为数据库设计的一部分，而查询则不是。视图可以禁止所有用户访问数据库中的表，而要求用户只能通过视图操作数据。这种方法可以保护用户和应用程序不受某些数据库修改的影响，同样也可以保护数据表的安全性。
（2）排序：可以排序任何查询结果，但是只有当视图包括TOP子句时才能排序视图。
（3）加密：可以加密视图，但是不能加密查询。

第九章 子查询

​ 子查询是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。
​ 嵌套查询是指将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询。嵌套查询中上层的查询块称为外侧查询或父查询，下层查询块称为内层查询或子查询。

---将大于90分的学生查询出来
SELECT * FROM Student
WHERE Sno=（SELECT Sno FROM SC WHERE Grade>90）

第十章 存储过程

​ 存储过程（Stored Procedure）是预编译SQL语句的集合，这些语句存储在一个名称下并作为一个单元来处理。存储过程代替了传统的逐条执行SQL语句的方式。一个存储过程中可包含查询、插入、删除、更新等操作的一系列SQL语句，当这个存储过程被调用执行时，这些操作也会同时执行。
​ 存储过程与其他编程语言中的过程类似。它可以接受输入参数并以输出参数的格式向调用过程或批处理返回多个值；包含用于在数据库中执行操作（包括调用其他过程）的编程语句；向调用过程或批处理返回状态值，以指明成功或失败（以及失败的原因）。
SQL Server中供了3种类型的存储过程。各类型存储过程如下：

• 用来管理SQL Server和显示有关数据库和用户的信息的存储过程，这些存储过程被称为系统存储过程。

• 用户在SQL Server中通过采用SQL语句创建存储过程，这类存储过程被称为用户自定义存储过程。

• 通过编程语言（例如：C）创建外部例程，并将这个例程在SQL Server中作为存储过程使用，这类存储过程称为扩展存储过程。

存储过程优点

存储过程的优点表现在以下几个方面：
（1）存储过程可以嵌套使用，支持代码重用。
（2）存储过程可以接受与使用参数动态执行其中的SQL语句。
（3）存储过程比一般的SQL语句执行速度快。存储过程在创建时已经被编译，每次执行时不需要从新编译。而SQL语句每次执行都需要编译。
（4）存储过程具有安全特性（例如权限）和所有权链接，以及可以附加到它们的证书。用户可以被授予权限来执行存储过程而不必直接对存储过程中引用的对象具有权限。
（5）存储过程允许模块化程序设计。存储过程一旦创建，以后即可在程序中调用任意多次。
这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。
（6）存储过程可以减少网络通信流量。一个需要数百行SQL语句代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码。
（7）存储过程可以强制应用程序的安全性。参数化存储过程有助于保护应用程序不受SQL injection攻击。

带参数存储过程

CREATE PROCEDURE Proc_mj
---输入长和宽输出面积
@chang int,
@kuan int,
@mianji int OUTPUT
AS
SELECT @MIANJI=@chang*@kuan
GO

---使用
DECLARE @jisuanmianji int
exec Proc_mj @chang=3,@kuan=4,@mianji=@jisuanmianji output
SELECT @jisuanmianji
---结果
12

管理存储过程

1.执行存储过程
存储过程创建完成后，可以通过EXECUTE执行，可简写为EXEC。
2.查看存储过程
1.使用sys.sql_modules 查看存储过程的定义、使用OBJECT_DEFINITION查看存储过程的定义、使用sp_helptext 查看存储过程的定义。
3.修改存储过程
使用ALTER PROCEDURE语句修存储过程。该语句修改存储过程时不会更改权限，也不影响相关的存储过程或触发器。
4.重命名存储过程
使用p_rename系统存储过程重新命名存储过程。
5.删除存储过程
执行DROP PROCEDURE语句删除存储过程。

第十一章 触发器

触发器的介绍

​ 触发器是一种特殊类型的存储过程，当指定表中的数据发生变化时触发器自动生效。
它与表紧密相连，可以看做是表定义的一部分。触发器不能通过名称被直接调用，更不允许设置参数。
在SQL Server中一张表可以有多个触发器。用户可以跟据INSERT、UPDATE或DELETE语句对触发器进行设置，也可以对一张表上的特定操作设置多个触发器。触发器可以包含复杂的Transact-SQL语句。不论触发器所进行的操作有多复杂，触发器都只作为一个独立的单元被执行，被看作是一个事务。如果在执行触发器的过程中发生了错误，则整个事务将会自动回滚。

触发器的种类

​ SQL Server 包括三种常规类型的触发器：DML触发器、DDL触发器和登录触发器。
当数据库中发生数据操作语言（DML）事件时将调用DML触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。DML触发器可以查询其他表，还可以包含复杂的Transact-SQL语句。
您可以设计以下类型的DL触发器：
​ AFTER触发器、INSTEAD OF触发器、CLR触发器。
​ DDL触发器是一种特殊的触发器，它在响应数据定义语言（DDL）语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规蓉数据库操作。
​ 登录触发器将为响应LOGON事件而激发存储过程。与SQL Server实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。
可以使用登录触发器来审核和控制服务器会话，例如通过跟踪登录活动、限制SQL Server的登录名或限制特定登录名的会话数。