SQL高级开发

变量的使用

局部变量

1. 必须以标记@作为前缀，如@age

2. 局部变量的使用先声明(使用关键字declare)，再赋值

   DECLARE @age INT   --声明局部变量@age
   --SET @age=20      --赋值局部变量
   SELECT @age=20
   SELECT @age        --查看局部变量的值

   使用SELECT赋值确保筛选出的记录只有一条

全局变量

1. 必须以标记@@作为前缀，如@@version

2. 由系统定义和维护，只能读取，不能修改全局变量值

变量的使用

问题：编写T-SQL查询小明及其学号相邻的学员

DECLARE @StuId INT --声明学号变量，这个变量记录小明的学号
SELECT @StuId=ID FROM Student WHERE Name='小明'--根据学生姓名查询出改学生的学号
SELECT * FROM Student WHERE ID=(@StuId+1) OR ID=(@StuId-1)--根据这个学号查询相邻的学员信息

SET与SELECT区别

使用场景	SET	SELECT
同时对多个变量赋值	不支持	支持
表达式返回多个值时	出错	将返回的最后一个值赋给变量
表达式未返回值时	变量被赋予NULL值	变量保持原值

DECLARE @age INT,@name VARCHAR(20)
--SET @age=20,@name='张三'--不允许这样做
SELECT @age=20,@name='张三'
SELECT @name
--SET @name=(SELECT Name FROM Student)
SELECT @name=Name FROM Student
--SET @name=(SELECT NAME FROM Student WHERE ID=0)
SELECT @name=NAME FROM Student WHERE ID=0
SELECT @name

全局变量的使用

变量	含义
@@ERROR	最后一个T-SQL错误的错误号
@@IDENTITY	最后一次插入的标识值
@@LANGUAGE	当前使用的语言的名称
@@MAX_CONNECTIONS	可以创建的同时连接的最大数目
@@ROWCOUNT	受上一个SQL语句影响的行数
@@SERVERNAME	本地服务器的名称
@@TRANSCOUNT	当前连接打开的事务数
@@VERSION	SQL Server的版本信息

数据类型转换

数据类型转换原因

DECLARE @avgAge INT
SET @avgAge=(SELECT AVG(Age)FROM Student)
--输出
PRINT '平均年龄是：'+@avgAge

数据类型转换方法

1. 使用Convert函数实现强制转换

   CONVERT(数据类型,表达式,样式)--第三个参数可以省略。它一般用于日期类型数据转换为字符类型，
   PRINT '平均年龄是：'+CONVERT(VARCHAR(20),@avgAge)

    

2. 使用CAST函数进行转换

   CASR(表达式 AS 数据类型)
   PRINT '平均年龄是：'+CAST(@avgAge AS VARCHAR(20))

    

CONVERT和CAST的不同：CONVERT可以指定转换的样式

样 式	说 明	输入/输出格式
0或100(*)	默认值	mon dd yyyy hh:mi AM(或者PM)
1/101	美国	mm/dd/yyyy
2/102	ANSI	yy.mm.dd
3/103	英国/法国	dd/mm/yy
4/104	德国	dd.mm.yy
5/105	意大利	dd-mm-yy
6/106	-	dd mon yy
7/107	-	mon dd,yy
8/108	-	hh:mm:ss
9或109(*)	默认值+毫秒	mon dd yyyy hh:mi:ss:mmmAM(或者PM)
10或110	美国	mm-dd-yy
11或111	日本	yy/mm/dd
12或112	ISO	yymmdd
13或113(*)	欧洲默认值+毫秒	dd mon yyyy hh:mm:ss:mmm(24h)
14或114	-	hh:mi:ss:mmm(24h)
20或120(*)	ODBC规范	yyyy-mm-dd hh:mm:ss(24h)
21或121(*)	ODBC规范(带毫秒)	yyyy-mm-dd hh:mm:ss.mmm(24h)
126	ISO8601	yyyy-mm-dd Thh:mm:ss:mmm(不含空格)
130	科威特	dd mon yyyy hh:mi:ss:mmmAM(或者PM)
131	科威特	dd/mm/yy hh:mi:ss.mmmAM(或者PM)

T-SQL中的函数

数学函数

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

函数名称	说 明
COS	返回指定的表达式中指定弧度的三角余弦值
COT	返回指定的表达式中指定弧度的三角余切值
PI	返回值为圆周率
POWER	将指定的表达式乘指定次方
RAND	返回0～1之间的随机float数
ROUND	将数字表达式四舍五入为指定的长度或精度
SIGN	返回指定表达式的零(0)、正号(+1)或负号(-1)
SIN	返回指定的表达式中指定弧度的三角正弦值
SQUARE	返回指定表达式的平方
SQRT	返回指定表达式的平方根
TAN	返回指定的表达式中指定弧度的三角正切值
ABS	返回指定数字表达式的绝对值

算术函数（例如ABS、CEILING、DEGREES、FLOOR、POWER、RADIANS和SIGN）返回与输入值具有相同数据类型的值。三角函数和其他函数（包括EXP、LOG、LOG10、SQUARE和SQRT）将输入值转换为float并返回float值

SELECT PI()
SELECT RAND()--随机0-1浮点
SELECT ROUND(1.205,0)--四舍五入
SELECT FLOOR(1.9002)--向下取整
SELECT CEILING(1.0005)--向上取整
SELECT DEGREES(-1.0)--将弧度转为度
SELECT RADIANS(60.0)--将度转为弧度

字符串函数

字符串函数作用于char、varchar、binary和varbinary数据类型以及可以隐式转换为char或varchar的数据类型。通 常字符串函数可以用在SQL语句的表达式中。

常用的字符串函数及说明：

函 数 名 称	说 明
ASCII	返回字符表达式最左端字符的ASCII代码值
CHARINDEX	返回字符串中指定表达式的起始位置
LEFT	从左边开始，取得字符串左边指定个数的字符
LEN	返回指定字符串的字符（而不是字节）个数
REPLACE	将指定的字符串替换为另一指定的字符串
REVERSE	返回字符表达式的反转
RIGHT	从右边开始，取得字符串右边指定个数的字符
STR	返回由数字数据转换来的字符数据
SUBSTRING	返回指定个数的字符

日期时间函数

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

常用的日期时间函数及说明:

函 数 名 称	说 明
DATEADD	在向指定日期加上一段时间的基础上，返回新的datetime值
DATEDIFF	返回跨两个指定日期的日期和时间边界数
GETDATE	返回当前系统日期和时间
DAY	返回指定日期中的天的整数
MONTH	返回指定日期中的月份的整数
YEAR	返回指定日期中的年份的整数

DATEADD函数将表示日期或时间间隔的数值与日期中指定的日期部分相加后，返回一个新的 DT_DBTIMESTAMP 值。

number参数的值必须为整数，而date参数的取值必须为有效日期。

语法如下：

DATEADD(datepart, number, date)

GROUP BY 分组查询

通过指定的字段进行对数据进行分组

SELECT COUNT(*),Sex,AVG(Age) FROM Student GROUP BY Sex

HAVING函数

HAVING函数，查询数据表中的重复记录，可以借助HAVING子句实现，该子句用来指定组或聚合的搜索条件。HAVING子句只能与SELECT语句一起使用，而且，它通常在GROUP BY子句中使用。相当于在GROUP BY里面添加筛选条件

SELECT COUNT(*),Age FROM Student GROUP BY Age HAVING COUNT(*)<2