【SQL Server】入门教程-基础篇(五)

SQL Server中系统内置函数

一、数学函数

abs(）

语法：

abs()：求绝对值函数

实例：

select ABS(-1);

pi()

语法：

pi()：获取pi的值。

实例：

select PI();

floor()

语法：

floor()：向下取整。

实例：

select FLOOR(3.14);

ceiling()

语法：

ceiling()：向上取整。

实例：

select CEILING(3.14);

round()

语法：

round()：按小数位数规定的精度四舍五入。

实例：

select ROUND(3.1415,2);

rand()

语法：

rand()：产生一个随机数，产生范围是0-1之间的一个小数。

实例：

select RAND();

sqrt()

sqrt()：开平方根。

实例：

select sqrt(4);

二、字符串转换函数 

字符串转换函数

lower()

语法：

lower()：将字符串全部转为小写。

实例：

select LOWER('Hello World');

upper()

语法：

upper()：将字符串全部转为大写。

实例：

select UPPER('Hello World');

str()

语法：

str()：把数值型数据转换为字符型数据。

实例：

select STR(100);

char()

语法：

char()：将ASCII码转换为字符。输入0 ~ 255之间的ASCII 码值，否则返回NULL 。

实例：

select CHAR(97);

ascii()

语法：

ascii()：返回字符表达式最左端字符的ASCII码值。

实例：

select ASCII('a');

三、字符串处理函数 

ltrim()

语法：

ltrim()：去掉字符串左部空格。

实例：

select LTRIM('    Hello    ');

rtrim()

语法：

rtrim()：去掉字符串右部空格。

实例：

select RTRIM('    Hello    ');

left()

语法：

left()：返回字符串左起的第n个字符。

实例：

select LEFT('helloworld',3);

right()

语法：

right()：返回字符串右起的第n个字符。

实例：

select RIGHT('helloworld',3);

substring()

语法：

substring()：返回从字符串左边第i个字符起的n个字符的部分。

实例：

select SUBSTRING('helloworld',2,3);

len()

 语法：

len()：返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。

length()

 语法：

length()：指定字符串或变量名称的长度。

UCase()

语法：

UCase()：返回字符串大写形式。

LCase()

语法：

LCase()：返回字符串小写形式。

reverse()

语法：

reverse()：反转字符串。

四、日期时间函数 

getdate()

语法：

getdate()：返回当前系统日期和时间，返回值类型为datetime。

select GETDATE();

year()

语法：

year()：返回指定日期的年部分，返回值为整数。

select YEAR(GETDATE());

month()

语法：

month()：返回指定日期的月部分，返回值为整数。

select MONTH(GETDATE());

day()

语法：

day()：返回指定日期的天部分，返回值为整数。

实例：

select DAY(GETDATE());

dateiff()

语法：

dateiff()：返回两个指定日期在datepart（年、月、日）方面date2超过date1的差距值，其结果值是一个带有正负号的整数值。

实例：

select DATEDIFF(DAY,'2020-01-01','2020-01-10');
select DATEDIFF(MONTH,'2019-01-01','2020-01-01');
select DATEDIFF(YEAR,'2019-01-01','2020-01-01');

以上就是Sql Server中的函数使用。

                        

Sql Server中的触发器使用

触发器概念

触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发。

简单来说：好比游戏里的隐藏成就，当你达到或者完成了一定的条件，自动触发的东西。

作用：

• 可在写入数据表前，强制检验或转换数据。
• 触发器发生错误时，异动的结果会被撤销。
• 部分数据库管理系统可以针对数据定义语言（DDL）使用触发器，称为DDL触发器。
• 可依照特定的情况，替换异动的指令 (INSTEAD OF)。

在触发器中可以使用两个特殊的虚拟表inserted和deleted：

• inserted表存放新增的记录
• deleted表存放被删除的记录

Update操作时：新的记录存入inserted表，旧的记录存入deleted表。

触发器语法

创建触发器：

##创建DML触发器（掌握）
create trigger 触发器名 on 表名/视图名
{ for | after | instead of }
{ insert [,] | update [,] | delete }
as
begin
    触发器需要执行的逻辑
end;
 
##创建DDL触发器（了解）
create trigger 触发器名 on 数据库作用域/服务器作用域
{ for | after }
{ 事件类型 }
as
begin
    触发器需要执行的逻辑
end;
事件类型：CREATE_对象类型、DROP_对象类型、ALTER_对象类型 
对象类型：DATABASE、TABLE、VIEW等

删除触发器：

##删除DML触发器
drop trigger 触发器名称;
 
##删除DDL触发器
drop trigger 触发器名称 on 数据库作用域/服务器作用域;

修改触发器： 

先删除、再创建

触发器练习

创建DML触发器：当删除Student表中的一条学生记录时将StuInfo表中该学生的相应记录也删除。

create trigger T_Stu_DELETE on Student 
after 
delete
as 
begin
    delete from stuInfo where stuName in (select StuName from deleted)
end;

触发DML触发器：

delete from student where StuName = '王五';
 
select * from student where StuName = '王五';
select * from StuInfo where StuName ='王五';

删除DML触发器：

drop trigger T_Stu_DELETE;

以上就是触发器的概念和使用，大佬们看一下哪有不对的地方，请指正。 

Sql Server中存储过程的使用

存储过程概念

存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数来执行它，通常情况下，将常用或很复杂的语句编写成存储过程。

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

存储过程语法

创建存储过程：

create procedure 存储过程名称 [ { @参数名 数据类型 [varying][=default][ouput] },... ]
as
begin
    存储过程需要执行的逻辑
end;

参数解读：
=default：表示为该参数设定的默认值，定义默认值后，不必指定该参数的值即可执行过程。
ouput：表示该参数是输出值。
varying ouput：表示该参数是游标参数。

温馨提示：存储过程中不能包含一些特殊语句，如：创建及修改视图、创建及修改函数、创建及修改触发器、创建及修改存储过程、创建DEFAULT、创建SCHEMA、USE 数据库等。

调用存储过程：

execute 存储过程名称 { [@参数=]{常量|@变量 [output]|[default]},... };

删除存储过程：

drop procedure 存储过程名称;

修改存储过程：

老方法，先删除，再删除

存储过程练习 

创建存储过程：计算指定班级的学生所选数学课程的平均成绩，要求输入参数为班级，输出参数为数学的平均成绩。

create procedure P_Math_AVG @num char(6), @avgScore float OUTPUT
AS
begin
    select avg(math) from student group by Classes having Classes=@num
end;

调用存储过程：

declare @avg float;
execute P_Math_AVG @num='2001班',@avgScore=@avg OUTPUT;

或者
 

declare @avg float;
execute P_Math_AVG '2001班',@avg;

删除存储过程：

drop procedure P_Math_AVG;