SQL Server 高级

chenglin016

于 2024-06-13 21:20:12 发布

阅读量866

点赞数 20

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

本文链接：https://blog.csdn.net/chenglin016/article/details/139654868

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、特殊语句

-WITH语句
指定临时命名的结果集，这些结果集称为公用表表达式，该表达式源自简单的查询，并在单条SELECT、INSERT、UPDATE、DELETE语句的执行范围内定义

with expression_name column_name as cte_query_definition
-expression_name 公用表表达式有效标识符
column_name 公用表表达式中指定的列名
-cte_query_definition 指定一个其结果集填充公用表表达式的SELECT语句
with agereps(age,agecount) as
{
select age count(*)
from employee
where as is not null
group by age
}
select age,agecount from agereps
BETWEEN……AND不包含两端的值
IS (NOT )NULL :where自居不能使用比较运算符（=）对空值进行判断，整你用IS (NOT )NULL
HAVING子句，不等你使用text,image,ntext数据类型
ORDER BY :不能使用ntext,text,image,xml数据类型
COMPUTE:生成合计作为附加的汇总列出现在结果集的最后，当与BY一起使用时，OMPUTE子句在结构集中生产控制中断和小计，可在同一个查询内指定COMPUTE BY 和COMPUTE
COMPUTE 聚合函数（不能使用DISTINCT聚合函数） BY
交叉连接
使用CROSS JOIN 连接，没有WHERE子句的交叉连接将产生连接所涉及的表的笛卡尔集，第一个表的行数乘上第二个表的行数等于笛卡尔结果集的大小
交叉连接中的列=原表中类的数量的总和（相加）
交叉练级中的行=原表中的行数的积（相乘）
select fieldlist from table1 cross join table2
二、视图
将查询的结果以虚拟表的形式存储在数据中，视图并不在数据库中以存储数据集的形式存在，在引用视图过程中动态生成
只有当视图包含TOP子句时才能排序视图
视图可以加密
视图分类：
标准视图：保存在数据库中的SELECT查询语句，即通常意义上理解的视图
索引视图：创建有索引的视图，他经过计算并存储自己的数据，可以提高某些类型查询的性能，尤其适合聚合许多行的查询，但不适合经常更新的基本数据集
分区视图：在一台或多台服务器间水平连接一组表中的分区数据，以使数据看上去来自一个表
创建视图：create view viewname as select * from table [with check option] view_attr :{[encryption] [schemabinding][view_metadata]}
修改视图:alter view viewname [with encryption] as select * from table [with check option]
通过视图修改数据表做修改：update viewname set kkkk=llll where kkkk=ddd
删除视图:DROP VIEW viewname
通过视图删除数据表的数据 delete viewname where test=‘*****’
重命名视图：SP_NAME exec sp_name old_view ,new _view

存储过程

预编译SQL语句的集合，作为一个单元来处理。
分类：
系统存储过程：用来管理SQL Server 和显示有关数据库与用户的信息的存储过程
自定义存储过程：用户在SQL Server中通过采用SQL语句创建的存储过程
扩展存储过程：通过编程语言（如C语言）创建的外部例程，并将这个例程在SQL server中作为存储过程使用。
创建存储过程：create proc procname [,number]
[{@parameter datatype} output]
as
sql_statement
执行存储过程：
EXEC [UTE]
(
{ @string_variable | [ N ]‘tsql_string’ }
[ + …n ]
)
[ AS { LOGIN | USER } = ’ name ’ ]
[;]
@return_status：整型变量，用于保存存储过程的返回状态。
procedure_name 或 module_name：拟调用的存储过程或函数的名称。
@parameter：传递给存储过程或函数的参数，可以是值、变量或默认值。
WITH RECOMPILE：指示SQL Server在每次执行时都重新编译存储过程或函数。

EXEC sp_GetEmployeeDetails @EmployeeID = 12345;

DECLARE @ReturnStatus INT;
EXEC @ReturnStatus = sp_GetEmployeeDetails @EmployeeID = 12345;
PRINT @ReturnStatus;

查看存储过程
使用sys.sql_modules查看存储过程的定义 select * from sys.sql_modules
使用OBJECT_DEFINITION查看存储过程的定义 object_defition(object_id)
使用SP_HELPTEXT查看存储过程的定义 sp_helptext ‘pro_name’
修改存储过程：ALTER PROC
重命名存储过程：sp_rename olename,newname
删除存储过程：drop proc procname

触发器

一种特殊的存储过程，当指定表中的数据发生变化时触发器自动生效，与表紧密相连，看做表定义的一部分。触发器不能通过名称被直接调用，更不允许设置参数
一个表可以同时存在3种类型触发器：INSERT UPDATE DELETE
触发器的分类：DML触发器，DDL触发器，登录触发器
DML触发器分类：
AFTER触发器：执行INSERT 、UPDATE、DELETE语句之后执行
INSTEAD OF 触发器：执行INSTEAD OF触发器代替通常的触发动作
CLR触发器：可以是ALTER触发器或Instead of 触发器，或DDL触发器
创建DML触发器：
CREATE TRIGGER TIGEER_NAME
ON TABLE|VIEW
WITH
FOR|AFTER|INSTEAD OF
AS
创建DDL触发器
create trigger trigger_name
on database
with
for drop|alter|create_table
AS
禁用触发器：disable trigger triggername|all on view|table|database|all server
启用触发器：enable trigger triggername|all on view|table|database|all server
删除触发器：drop trigger triggername

游标

取用一组数据并能够一次与一个单独的数据进行交互的方法
定位结果集中的特定行，从结果集的当前行检索一行或多行，对结果集的当前行做修改
游标的5个部分：声明游标，打开游标，从一个游标中查找信息，关闭游标，释放游标
游标的类型：静态、动态、只进，键集驱动
定义游标：declare curname cursor for
读取游标：fetch next from curname
打开游标：open cusrname
关闭游标：close curname
释放游标：deallocate curname

事务

事务包含4个属性：ACID(原子性，一致性，隔离性，持久性)
事务模式：
自动提交事务：每个单独的语句都是一个事务
显示事务：每个事务均以begin transaction语句显示开始，以COMMIT或ROLLBACK语句显示结束
隐式事务：在前一个事务完成时新事物隐式启动，但每个事务仍以COMMIT或ROLLBACK语句显示完成
批处理级事务：只能应用与多个活动结果集（MARS）,在MARS 会话中启动的SQL显示或隐式事务变为批处理事务，当批处理完成时没有提交或回滚的批处理级事务自动由SQL server进行回滚
SQL Server 中的事务隔离级别决定了事务在读取和写入数据时如何与其他事务进行交互。总共有六个事务隔离级别，每个级别提供了不同程度的数据一致性和并发性能。隔离级别分类：
READ UNCOMMITTED（未提交读）

特点：这是最低的隔离级别。允许读取其他事务尚未提交的数据，因此可能会读取到“脏数据”。
并发性：最高。
一致性：最低。可能产生脏读、不可重复读和幻读。
设置方式：SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;