SQL Server基础知识

最新推荐文章于 2023-12-24 02:00:29 发布

ReiZhoo

最新推荐文章于 2023-12-24 02:00:29 发布

阅读量127

点赞数

分类专栏：数据库文章标签：数据库 sqlserver

本文链接：https://blog.csdn.net/ReiZhoo/article/details/132041803

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本笔记参考郝斌老师的SQL Server教学视频，使用的数据库例子为Oracle的scott数据库。

一、概述

1、什么是数据库？

狭义：存储数据的仓库

广义：可以对数据进行存储和管理的软件以及数据本身统称为数据库

数据库是由表、关系、操作组成。

2、为什么需要数据库？

几乎所有的应用软件的后台都需要数据库；
数据库存储数据占用空间小，且容易持久保存；
存储比较安全；
容易维护和升级；
移植比较容易；
简化对数据的操作；
为学习Oracle做准备；
B/S架构里面包含数据库；

3、数据库的安装和卸载

略

4、预备知识

数据库原理
编程语言（非必须）
数据结构和数据库的区别：
- 数据库是在应用软件级别研究数据的存储和操作；
- 数据结构是在系统软件级别研究数据的存储和操作
连接
- 区分数据库管理软件和数据库本身
- 我们在软件中的操作是通过软件建立连接来操作数据库的
数据库对于编程语言而言存在的意义
- 对内存数据操作是编程语言的强项
- 对硬盘数据操作是数据库的强项
学习数据库的三个角度
- 存储数据：字段、记录、表、约束（主键、外键、唯一键、非空、check、default、触发器）
- 操作数据：insert、update、delete、T-SQL、存储过程、函数、触发器
- 显示数据：select

二、基础操作

1、创建数据库

右键新建数据库后，数据库资源存放位置会出现mdf和ldf后缀的文件，其中mdf为数据文件，ldf为日志文件。

2、删除数据库

删除数据库的同时也要关闭数据库管理软件与数据库的连接。

3、附加和分离数据库

分离前需要首先关闭数据库管理软件与数据库的连接

4、数据基础知识

1、表的相关数据

概念	描述
字段 / 列 / 属性	事物的某一个特征
记录 / 元组	字段的组合，表示的是一个具体的事物
表	记录的组合，表示的是一个类型的事物集合

2、create table命令

示例：

create table dept(
    dept_id int primary key,
    dept_name nvarchar(100) not null,
    dept_address nvarchar(100)
)

create table emp(
    emp_id int constraint pk_emp_id_haha primary key,
    emp_name nvarchar(20) unique not null ,
    emp_sex nchar(1) default('男'),   --()可以省略，SQL字符串使用''包裹
    emp_sal int check(emp_sal>=1000 and emp_sal <=8000),
    dept_id int constraint fk_dept_id_heihei foreign key references dept(dept_id) 
)

注意：SQL Server命令最后建议不加逗号

3、约束

定义：对一个表中的属性操作的限制叫做约束
分类
- 主键约束：不允许重复元素，避免数据的冗余
- 外键约束：通过外键约束从语法上保证本事物所关联的其他事物一定存在
  
  （事物之间的关系是通过外键来体现的）
- check约束：保证事物属性的取值在合法的范围之内
- default约束：设置事物属性的默认值，保证该属性一定会有一个值
- unique约束：保证了事物属性的取值不允许重复，但允许为空
  
  （unique可以与not null一起使用）
- not null：要求用户必须为事物属性赋一个值

4、表和约束的区别

数据库是通过表来解决事物的存储问题的；
数据库是通过约束来解决事物取值的有效性和合法性问题的；
建表的过程就是指定事物属性及其约束的过程。

5、关系

定义：表和表之间的联系；
实现方式：通过设置不同形式的外键来体现表和表的不同关系；
分类：一对一、一对多、多对多

6、主键

定义：能够唯一标识一个事物的一个字段或者多个字段的组合
附注：
- 含有主键的表叫做主键表；
- 主键通常都是整数，仅在集群式服务中采用字符串格式；
- 主键的值通常都不允许修改，除非所在记录被删除；
- 主键不建议定义为id，而要定义为表名Id或者表名_id；
- 拒绝使用有业务含义的字段当主键，而要用单独添加的整形字段做代理主键；
- 主键可使用关键字identity用于字段自动更新；

7、外键

定义：如果一个表中的若干个字段是来自另外若干个表的主键或唯一键，则这若干个字段就是外键
附注：
- 含有外键的表叫做外键表，外键来自的那张表叫做主键表；
- 外键通常来自另外表的主键而非唯一键，因为唯一键可能为null；
- 外键不全来自另外的表，也有可能为本表的主键；
- 删除主键表和外键表时，要先删除外键表，主键表会由于外键表的引用而无法直接删除

三、数据查询

1、计算列

select ename, job, sal as "月薪", sal * 12 as "年薪" from emp;

注：as可以省略；此外，为保证可移植性，字符串使用双引号包裹。

2、distinct

select distinct deptno from emp;

distinct会过滤掉重复的值，包括重复的null值；
当distinct后面跟多个参数时，会进行组合过滤；

3、between

select * from emp where sal between 1500 and 3000;

注：between的生效范围包括参数本身。

4、in

select * from emp where sal in (1500, 3000, 5000);

注：数据库中的不等于有两种表示：！=和<>，推荐使用第二种

5、top

select top 2 * from emp;   -- 输出前两条记录
select top 15 percent * from emp;   -- 输出前15%记录

top后跟非整数时向上取整并输出；
该关键字多用于数据库分页查询；

6、null

select * from emp where comm <> null;   -- 输出为空 error
select * from emp where comm != null;   -- 输出为空 error
select * from emp where comm = null;   -- 输出为空 error

select * from emp where comm is null;   -- 输出comm为null的记录
select * from emp where comm not is null;   -- 输出comm不为null的记录

select ename, sal*12+isnull(comm, 0) as "年薪" from emp;   
-- isnull(comm, 0) 如果comm是null则返回0，否则返回comm本身的值

null不能参与<>、!=、=运算；
null可以参与is、not is运算;
0和null值是不一样的；
任何数字与null进行运算，其结果永远为null

7、order by

select * from emp order by sal;   -- 默认按照升序输出
select * from emp order by sal desc;   -- 降序输出

order by默认升序（asc）排列，加上desc后降序排列；
order by后有多个字段时，先按照第一个字段排序，相同的再按照第二个字段排序，以此类推；
desc参数只对其前面的一个字段生效；

8、模糊查询

-- 格式：select 字段的集合 from 表名 where 某个字段的名字 like 匹配的条件（带通配符）
select * from emp where ename like '%A%';   -- 输出ename中含有A的记录
select * from emp where ename like '_A%';   -- 输出ename中第二个字母是A的记录
select * from emp where ename like '_[A-F]%';   -- 输出ename中第二个字母在A到F中的记录
select * from emp where ename like '_[A, F]%';   -- 输出ename中第二个字母是A或F的记录
select * from emp where ename like '_[^A-F]%';   -- 输出ename中第二个字母不在A到F中的记录

通配符	含义
%	表示任意零个或多个字符
_（下划线）	表示任意单个字符
[a-f]	a到f中的任意单个字符
[a, f]	a或者f
[^a-f]	a到f之外的单个字符

匹配的条件必须用单引号包裹起来；
匹配的条件中通配符须通过转义字符使用；
```
select * from emp where ename like '%\%%' escape '\';   -- 输出ename中含有%的记录
```
关键词escape的作用是将后面的字符当做特殊字符对待（即转义字符）

9、聚合函数

select low(ename) from emp;   -- 最终返回多行，low()是单行函数
select max(sal) from emp;   -- 最终返回单行，max()是多行函数
select count(*) from emp;   -- 返回emp表中所有记录的个数
select count(distinct deptno) from emp;   -- 返回emp表中deptno字段去重后的个数
select count(comm) from emp;   -- 返回emp表中comm字段不为空的记录个数
select max(sal) "最高工资", min(sal) "最低工资", count(*) "员工人数" from emp;

函数分类

单行函数：每行返回一个值；

多行函数：多行返回一个值，聚合函数是多行函数；
聚合函数分类：
- max()最大值;
- min()最小值;
- avg()平均值;
- count()计数：该函数本身并不会去重，去重需在括号中添加distinct参数，且该函数不统计值为null的记录数;

附注：单行函数和多行函数不能混用。

10、group by

-- 返回部门编号分组的平均工资信息
select deptno, avg(sal) "部门平均工资" 
	from emp 
	group by deptno;
	
-- 返回部门编号及职位分组的平均工资、职位人数、总工资和最低工资信息
select deptno, job, avg(sal) "职位平均工资" count(*) "职位人数" sum(sal) "总工资" min(sal) "最低工资" 
	from emp 
	group by deptno, job;
	
-- 返回领导编号分组的下属人数信息
select mgr "领导", count(*) "下属人数" 
	from emp 
	group by mgr 
	order by mgr;

使用group by关键字后，select语句中只能够出现分组后的整体信息（多行函数），不能出现分组后组内详细信息；
如果group by关键字后跟多个参数，则依次按参数分组，最终统计最小单位的分组信息；

11、having

-- 输出部门平均工资大于2000的部门编号及其平均工资
select deptno, avg(sql) "平均工资" 
	from emp 
	group by deptno 
	having avg(sal) > 2000;
	
-- 输出部门人数大于3的部门编号及其平均工资
select deptno, avg(sql) "平均工资" 
	from emp 
	group by deptno 
	having count(*) > 3;
	
-- 将姓名不包含A的员工按部门编号分组后输出部门平均工资大于2000的部门编号及其平均工资
select deptno, avg(sal) "平均工资"
	from emp
	where ename not like '%A%'
	group by deptno
	having avg(sal) > 2000;

having子句用于对分组之后的数据进行过滤，因此使用having通常都会先使用group by；
如果使用having时未使用group by，意味着将所有记录当做一组进行过滤（不推荐使用）；
having子句出现的字段必须是分组之后的整体信息，而不能是组内的详细信息；
where关键字必须写在having前面，顺序不可颠倒；
where关键字用于对原始数据进行过滤，having关键字用于对分组之后的记录进行过滤；

12、select语句的基本结构

SELECT select_list
	[INTO new_table_name]
	FROM table_list
	[WHERE search_conditions]
	[GROUP BY group_by_list]
	[HAVING search_conditions]
	[ORDER BY order_by_list [ASC|DESC]];
	
SELECT TOP ...
	FROM A
	[JOIN B
	ON ...]
	[JOIN C
	ON ... ]
	[WHERE ...]
	[GROUP BY ...]
	[HAVING ...]
	[ORDER BY ...]

13、连接查询

定义：将两个及以上的表以一定的条件连接起来，从中检索出满足条件的数据；

分类

内连接（inner join … on …）

SQL语句	结果
select … from A, B	产生的结果表中行数为AB之积，列数为AB之和，即表A和表B的笛卡尔积
select … from A, B where …	产生的结果表为表A和表B的笛卡尔积用where中的条件过滤后的记录表
select … from A join B on …	产生的结果表为表A和表B的笛卡尔积用on中的条件进行过滤后的记录表

join是连接，on是连接条件，两者必须同时出现，on不可省略；
select ... from A, B where ...是SQL92标准；select ... from A join B on ...是SQL99标准

同一个逻辑两种标准输出的结果是一样的，但更推荐使用SQL99标准；
在SQL99标准中，on关键字通常用于指定连接条件，where关键字通常用于对连接之后形成的临时表数据进行过滤；

多表内连接举例：

-- 输出工资大于2000且姓名不包括A的前三名员工信息
select top 3 *
	from emp
	join dept
	on emp.deptno = dept.deptno
	join salgrade
	on emp.sal between salgrade.losal and salgrade.hisal
	where emp.sal > 2000 and emp.ename not like '%A%'
	order by emp.sal desc;

外连接(left/right join … on …)
- 定义：不但返回满足连接条件的所有记录，而且会返回部分不满足条件的记录；
- 分类：
  
  左外连接：不但返回满足连接条件的所有记录，而且会返回左表不满足连接条件的记录；
  
  右外连接：不但返回满足连接条件的所有记录，而且会返回右表不满足连接条件的记录；
完全连接(full join … on …)
- 定义：返回满足连接条件与不满足连接条件的所有记录；
交叉连接(cross join … on …)
- 定义：返回两表的笛卡尔积；
自连接
- 定义：自己连接自己
联合(union)
- 定义：表和表之间的数据以纵向的方式连接在一起
附注：除联合外的连接都是以横向的方式连接在一起的。

14、分页查询

假设每页显示n条记录，当前显示第m页（表名为A，主键为A_id）

select top n *
	from A
	where A_id not in (select top (m-1)*n A_id from A)

四、数据库高级语法

1、视图

概念：视图是一个虚拟表，其内容由查询（select语句）定义；视图并不存储数据，其数据来自定义视图的查询所引用的表。

语法：

-- 创建视图
create view 视图名称 as 查询语句;
-- 调用视图
select * from 视图名称;
-- 删除视图
drop view 视图名称;
-- 修改视图
先删除、再创建

优点：
- 简单性：可屏蔽表连接等复杂操作；
- 安全性：简化用户权限的管理，将用户限制在数据的不同子集上；
- 逻辑数据独立性：基本表的改变只需改变视图，而无需修改应用程序；
缺点：
- 增加了数据库维护的成本；
- 视图只是简化了查询，并不能加快查询速度；
注意：
- 创建视图的select语句必须为所有的计算列指定别名；
- 不建议通过视图更新视图所依附的原始表的数据或结果；

2、事务

概念：事务是逻辑上的一组数据库操作，要么都执行，要么都不执行。
特性：
- 原子性：事务是最小的执行单位，不允许分割；
- 一致性：确保从一个正确的状态转换到另外一个正确的状态；
- 隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
- 持久性：一个事务被提交之后，对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

事物之间的影响

影响	含义
脏读	当事务T1正在访问字段A并进行了修改，但未提交到数据库中；这时另外一个事务T2也访问和使用字段A，由于事务T1修改字段A后还没有提交`COMMIT`，因此事务T2读到的字段A是“脏数据”。
不可重复读	不可重复读取是指同一个事务在整个事务过程中对同一笔数据进行读取，每次读取结果都不同。如果事务1在事务2的更新操作之前读取一次数据，在事务2的更新操作之后再读取同一笔数据一次，两次结果是不同的。不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这会导致锁竞争加剧，影响性能。
幻读	在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录, 就好像发生了幻觉一样。幻读是由于并发事务增加记录导致的，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。
丢失更新	第一类丢失更新：撤销一个事务的时候，把其它事务已提交的更新数据覆盖了，这是完全没有事务隔离级别造成的。如果事务1被提交，另一个事务被撤销，那么会连同事务1所做的更新也被撤销。第二类丢失更新：当两个或多个事务查询相同的记录，然后各自基于查询的结果更新记录时会造成第二类丢失更新问题。每个事务不知道其它事务的存在，最后一个事务对记录所做的更改将覆盖其它事务之前对该记录所做的更改。

事务隔离级别

隔离级别脏读不可重复读幻读
读未提交 Y Y Y
读已提交 - Y Y
可重复读 - - Y
可序列化 - - -

隔离级别	脏读	不可重复读	幻读
读未提交	Y	Y	Y
读已提交	-	Y	Y
可重复读	-	-	Y
可序列化	-	-	-

3、索引

概念：索引是一个单独的，存储在磁盘上的数据结构，它们包含对数据表里所有记录的引用指针，使用索引可快速找出在某个或多个列中有某一特定值的行，对相关列使用索引是降低查询操作时间的最佳途径；索引可以是由表或视图中的一列或多列生成的键。
分类：
- 聚簇索引：
  - 聚簇索引的顺序就是数据的物理存储顺序，每张表只能有一个聚集索引，因为数据行本身只能按一个顺序存储
  - SQL Server是按B树（BTREE）方式组织聚簇索引的，聚簇索引的叶节点就是数据节点，由于数据记录按聚簇索引键的次序存储，因此查找效率高。
  - 除非在创建主键时特别指定，否则创建主键时会自动创建聚簇索引；
  - 关键值的唯一性使用UNIQUE关键字或者由内部的唯一标识符明确维护。
- 非聚簇索引：
  - 非聚簇索引的索引顺序与数据物理顺序无关，具有完全独立于数据行的结构；
  - 非聚簇索引也是按B树方式组织的，但非聚簇索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行；
  - 一个表中可有多个非聚集索引，创建索引时，可指定是按升序还是降序；
  - 查询优化器在搜索数据值时，先搜索非聚集索引以找到数据值在表中的位置，然后直接从该位置检索数据。这使得非聚集索引成为完全匹配查询的最佳选择，因为索引中包含搜索的数据值在表中的精确位置的项

语法：

-- 创建索引
create 【unique】 index 索引名称 on 表名(要创建索引的字段1,要创建索引的字段2,...);

-- 重建索引
alter index 索引名称 on 表名 rebuild;

-- 删除索引
drop index 表名.索引名称;

4、存储过程

概念：存储过程（Stored Procedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中，一次编译后永久有效，可通过指定存储过程的名字并给出参数来执行它。
优点：
1. 在数据库服务器中只有首次对存储过程中的命令进行编译，以后直接调用无需编译，加快执行速度。
2. 存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量SQL语句的代码流量。
3. 维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。
4. 自动完成需要预先执行的任务（存储过程可以在SQL Server启动时自动执行）
分类：
- 系统存储过程。系统预定义的存储过程，可以在任何一个数据库中作为命令执行，系统存储过程定义在系统数据库master中，其前缀是“sp_”。例如，sp_help ----显示系统对象信息。
- 扩展存储过程。是指在SQL Server环境之外，使用编程语言（例如C++语言）创建的外部例程形成的动态链接库（DLL）。例如，EXEC xp_cmdshell ‘dir c:’ ----显示目录信息。
- 用户存储过程。可以使用T-SQL语言编写，也可以使用CLR方式编写，用户存储过程一般是指用T-SQL语言编写的存储过程，而使用CLR方式编写的存储过程称为CLR存储过程。CLR存储过程就是使用Microsoft Visual Studio环境下的语言作为脚本编写的、可以对Microsoft .NET Framework公共语言运行时（CLR）方法进行引用的存储过程。

语法：

-- 创建存储过程
create procedure 存储过程名称 [ { @参数名 数据类型 [varying][=default][ouput] },... ]
as
begin
	存储过程需要执行的逻辑
end;
-- 参数解读：
-- =default：表示为该参数设定的默认值，定义默认值后，不必指定该参数的值即可执行过程。
-- ouput：表示该参数是输出值。
-- varying ouput：表示该参数是游标参数。

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

-- 删除存储过程
drop procedure 存储过程名称;

-- 修改存储过程
先删除、在创建

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

5、T-SQL

概念：T-SQL是SQL语言的一种版本，只能用于微软SQL Server以及Sybase Adaptive Server系列数据库。T-SQL除了提供标准的SQL命令之外，还提供了变量说明、流程控制、功能函数等。在SQL Server数据库中，T-SQL语言由DQL、DCL、DDL、 DML 及流控制语句组成。
常量：
- 字符串常量
- 整型常量
- 实型常量
- 日期时间常量
- 货币常量
- 唯一标识常量
- …
变量：
- 全局变量：全局变量由系统提供且预先声明，以“@@”开头。T-SQL全局变量作为函数引用。例如，@@ERROR返回执行的上一个T-SQL语句的错误号；@@CONNECTIONS返回自上次启动SQL Server以来连接或试图连接的次数。
- 局部变量：局部变量以@开头。例如，保存运算的中间结果，作为循环变量等。

语法：

-- 局部变量的定义
declare { @变量名 数据类型 }[,...n]

-- 局部变量的赋值
set语法格式：一个set语句只能给一个变量赋值
set @变量名＝表达式
    
select语法格式：一个select语句可以给多个变量赋值
select { @变量名＝表达式 }[,...n]

数据类型
- 系统数据类型：又称为基本数据类型
- 用户自定义数据类型：基于基本数据类型，可由其他表和字段引用

运算符与表达式

运算符

算术运算符：+（加）、-（减）、*（乘）、/（除）、%（求模）
位运算符：执行位操作，两个表达式的类型可为整型或与整型兼容的数据类型（例如字符型等，但不能为image类型）

位运算符运算规则
& 有0出0，全1出1
| 有1出1，全0出0
^ 有1出0，有0出1
比较运算符：比较运算符又称关系运算符，其运算结果为逻辑值，可以为三种之一：TRUE、FALSE 及 UNKNOWN

运算符含义运算符含义
= 相等 <= 小于等于
> 大于 <>、!= 不等于
< 小于 !< 不小于
>= 大于等于 !> 不大于

位运算符	运算规则
&	有0出0，全1出1
\|	有1出1，全0出0
^	有1出0，有0出1

运算符	含义	运算符	含义
=	相等	<=	小于等于
>	大于	<>、!=	不等于
<	小于	!<	不小于
>=	大于等于	!>	不大于

逻辑运算符

运算符	运算规则
AND	如果两个操作数值都为TRUE，运算结果为TRUE
OR	如果两个操数中有一个为TRUE，运算结果为TRUE
NOT	若一个操作数值为TRUE，运算结果为FALSE，否则为TRUE
ALL	如果每个操作数值都为TRUE，运算结果为TRUE
ANY	在一系列操作数中只要有一个为TRUE，运算结果为TRUE
BETWEEN	如果操作数在指定的范围内，运算结果为TRUE
EXISTS	如果子查询包含一些行，运算结果为TRUE
IN	如果操作数值等于表达式列表中的一个，运算结果为TRUE
LIKE	如果操作数与一种模式相匹配，运算结果为TRUE
SOME	如果在一系列操作数中，有些值为TRUE，运算结果为TRUE

字符串连接运算符：通过运算符“+”实现两个字符串的联接运算
一元运算符：+（正）、-（负）和～（按位取反）
赋值运算符：指SET和SELECT语句中使用的“=”

表达式：表达式就是常量、变量、列名、运算符和函数的组合，一个表达式通常可以得到一个值

流程控制语句：流程控制语句可以改变计算机的执行顺序

控制语句	说明	控制语句	说明
BEGIN…END	语句块	CONTINUE	用于重新开始下一次循环
IF…ELSE	条件语句	BREAK	用于退出最内层的循环
CASE	分支语句	RETURN	无条件返回
GOTO	无条件转移语句	WAITFOR	为语句的执行设置延迟
WHILE	循环语句

系统内置函数

数学函数	说明
abs()	求绝对值
pi()	获取pi的值
floor()	向下取整
ceiling()	向上取整
round()	按小数位数规定的精度四舍五入
rand()	产生一个范围是0-1之间随机小数
sqrt()	开平方根

字符串转换函数	说明
lower()	将字符串全部转为小写
upper()	将字符串全部转为大写
str()	把数值型数据转换为字符型数据
char()	将ASCII码转换为字符。输入0~255之间的ASCII码值，否则返回NULL
ascii()	返回字符表达式最左端字符的ASCII码值

字符串处理函数	说明
ltrim()	去掉字符串左部空格
rtrim()	去掉字符串右部空格
left()	返回字符串左起的第n个字符
right()	返回字符串右起的第n个字符
substring()	返回从字符串左边第i个字符起的n个字符的部分

-- 数据类型转换函数
CAST (表达式 AS 新类型)
CONVERT (新类型 ,表达式)

-- 日期型 → 字符型：
select CAST('1997-12-05' as varchar(30));
select CONVERT(varchar(30),'1997-12-05');

-- 字符型 → 日期型：
select CAST('1997-12-05' as datetime);
select CONVERT(datetime,'1997-12-05');

-- 数值型 → 字符型：
select CAST(1997 as varchar(30));
select CONVERT(varchar(30),1997);

-- 字符型 → 数值型：
select CAST('1997' as int);
select CONVERT(int,'1997');

日期时间函数	说明
getdate()	返回当前系统日期和时间，返回值类型为datetime
year()	返回指定日期的年部分，返回值为整数
month()	返回指定日期的月部分，返回值为整数
day()	返回指定日期的天部分，返回值为整数
dateiff()	返回两个指定日期在datepart（年、月、日）方面date2超过date1的差距值，其结果值是一个带有正负号的整数值

元数据函数	说明
db_id()	根据数据库名，返回数据库标识（ID）号
db_name()	根据数据库ID，返回数据库名
object_id()	返回数据库中对象的ID号，返回值类型为smallint

用户自定义函数

-- 创建函数
create function 函数名称 ( [@参数名 参数类型 [=默认值]],... )
returns 返回值类型
as
begin
	函数体
	return 表达式;
end;

-- 调用函数
select dbo.函数名(实参1, 实参2, ..., 实参n);

-- 删除函数
drop function 函数名称;

-- 修改函数
先删除、在创建

6、游标

概念：游标是一种处理数据的方法，具有对结果集进行逐行处理的能力；可以将游标看作一种特殊的指针，它与某个查询结果集相关联，可以指向结果集的任意位置，可以将数据放在数组、应用程序中或其他的地方，允许用户对指定位置的数据进行处理
实现功能：
- 允许对 SELECT 返回的表中的每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；
- 从表中的当前位置检索一行或多行数据；
- 游标允许应用程序对当前位置的数据进行修改、删除的能力；
- 对于不同用户对结果集包含的数据所做的修改，支持不同的可见性级别；
- 提供脚本、存储过程、触发器中用于访问结果集中的数据的语句。
使用步骤：
1. 声明游标 DECLARE：将游标与 T-SQL 语句的结果集相关联，并定义游标的名称、类型和属性，如游标中的记录是否可以更新、删除；
```
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]      --可修改的列
```
2. 打开游标 OPEN ：执行 T-SQL 语句以填充数据。游标声明后，如果要从游标中读取数据必须要打开游标。打开游标是指打开已经声明但尚未打开的游标，并执行游标中定义的查询；
  - 如果声明游标使用 STATIC 类型，则打开游标时产生一个临时表来存放结果集；
  - 如果声明游标使用 KEYSET 类型，则 OPEN 产生一个临时表来存放键值;
  - 所有的临时表都存放在 tempdb 数据库中。
```
-- 打开游标语法格式
OPEN 游标名称 
```
  在游标被成功打开后，全局变量 @@CORSOR_ROWS 用来记录游标内的数据行数。返回值有四种：
  - -m：表示仍在从基础表向游标读入数据，m表示当前在游标中的数据行数；
  - -1：该游标是一个动态游标，其返回值无法确定；
  - 0：无符合调剂的记录或游标已经关闭；
  - n：从基础表向游标读入数据已结束，n 为游标中已有的数据记录行数
3. 读取游标 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：所读取的数据已经不存在
4. 关闭游标 CLOSE：停止游标使用的查询，但并不删除游标的定义，可以使用 OPEN 再次打开；
```
-- 关闭游标语法格式
CLOSE {{[ GLOBAL] 游标名称} | @游标变量名称 } 
```
5. 释放游标 DEALLOCATE：删除资源并释放其占用的所有资源。
```
-- 删除游标语法格式
DEALLOCATE {{[ GLOBAL] 游标名称} | @游标变量名称 } 
```

7、触发器

概念：触发器是数据库用于保证数据完整性的一种方式，可以说它是与表事件相关的一种特殊的存储过程，它的执行不能由程序调用，也不能手动启用，而只能是通过事件来触发，比如当对表进行 Insert、Delete、Update 操作的时候，就会激活来执行触发器，所以触发器通常用于保证数据完整性和一些业务约束规则等。
优点
- 自动触发，无须调用；
- 强制限制，实现check约束所不能实现的更为复杂的限制，即使用其它表的数据来约束当前表；
- 用于一些关联表数据的更新；
- 跟踪状态，撤销违法操作，保证数据的准确性。
根据语言分类
- DML（数据库操作语言）触发器：执行INSERT、 DELETE 、UPDATE语句时触发，可以用于实现数据的完整性、多个表间数据的一致性等
- DDL（数据库定义语言）触发器：只由T-SQL语句触发，执行CREATE、ALTER、DROP等语句时触发
根据触发器执行顺序分类
- for/after：仅在触发SQL语句中指定的所有操作都已成功执行完后才被触发。
- instead of：触发SQL语句并不实际执行，走完instead of触发器的内容就结束。

语法：

-- 创建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 数据库作用域/服务器作用域;

-- 修改触发器
先删除、在创建