一、特殊语句
-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;
- READ COMMITTED(已提交读)
特点:大多数数据库系统的默认隔离级别。事务只能读取已提交的数据。
并发性:较高。
一致性:可以避免脏读,但可能出现不可重复读和幻读。
设置方式:SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
- REPEATABLE READ(可重复读)
特点:确保在同一事务中多次读取相同数据的结果是一致的。
并发性:中等。
一致性:可以避免脏读和不可重复读,但可能出现幻读。
设置方式:SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
- SERIALIZABLE(可串行化)
特点:最高的隔离级别。通过强制事务串行执行来避免所有并发问题。
并发性:最低。
一致性:可以完全避免脏读、不可重复读和幻读。
设置方式:SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
- SNAPSHOT(快照)
特点:使用多版本并发控制(MVCC)来提供事务的一致读取。允许事务读取数据的早期版本,而不是等待其他事务释放锁。
并发性:高。
一致性:避免脏读,但可能不是完全可重复的(取决于其他事务的并发修改)。
注意:需要数据库启用快照隔离或允许快照。
- READ COMMITTED SNAPSHOT(基于行版本的已提交读隔离级别)
特点:类似于SNAPSHOT,但仅针对READ COMMITTED隔离级别。
并发性:高。
一致性:避免脏读,提供已提交数据的读一致性。
注意:需要数据库启用READ COMMITTED SNAPSHOT选项。
锁
- 锁模式
- 共享(S):用于不更新或不更改数据的操作(只读操作),如select语句
- 更新(U):用于更新的资源,防止当多个会话在读取,锁定以及随后可能进行的资源更新时发生的常见形式的死锁
- 排他(X):用于修改数据操作,如insert\uopdate \delete,避免同一资源记性多重更新
- 意向:用于建立锁的层次结构,意向共享,意向排他,意向排他共享
- 架构:在执行依赖于表架构的操作时使用,架构锁的类型为架构修改和架构稳定性
- 大容量更新:向表中大容量复制数据并指定TABLOCK提示时使用
- 锁粒度:
- 行锁:行标识符,单独锁定表中的一行,最小的锁
- 键锁:锁定索引中的节点,保护可串行事务中的键范围
- 页锁:锁定8KB的数据页或索引页
- 扩展盘区锁:锁定相邻的8个数据页或索引页
- 表锁:锁定整张表
- 数据库锁:锁定整个数据库