> 데이터 베이스 > MySQL 튜토리얼 > 数据库中临时表,表变量和CTE使用优势极其区别

数据库中临时表,表变量和CTE使用优势极其区别

WBOY
풀어 주다: 2016-06-07 16:04:50
원래의
1279명이 탐색했습니다.

1 在写SQL时经常会用到临时表,表变量和CTE,这三者在使用时各有优势: 1. 临时表:分为局部临时表和全局临时表. 1.1局部临时表,创建时以#开头,在系统数据库tempdb中存储. 在当前的链接可见,链接断开则临时表就自动被释放,也可以手动drop table #tmptable 在使用

1 在写SQL时经常会用到临时表,表变量和CTE,这三者在使用时各有优势:

1. 临时表:分为局部临时表和全局临时表.

1.1局部临时表,创建时以#开头,在系统数据库tempdb中存储. 在当前的链接可见,链接断开则临时表就自动被释放,也可以手动drop table #tmptable

在使用不同的链接同时创建相同的临时表时,互不影响,系统在tempdb中会自动附加以特定的session为标识的名字来区分. 常常在SP中使用,把需要操作的数据或者共同的数据取出放在临时表中,后续可以进行其他的操作(SELECT,UPDATE,DELETE,DROP等).

可以像创建永久表一样创建临时表:

CREATE TABLE #tmpTable
(
ID INT,
NAME VARCHAR(<strong>10</strong>),
COMPANY VARCHAR(<strong>50</strong>)
)

SELECT * FROM #tmpTable JOIN ...

DROP TABLE #tmpTable
로그인 후 복사

也可以使用INTO创建临时表,如查询EmployeeID=1的所有订单,放在临时表中,以备后续的处理.

SELECT E.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate 
INTO #tmpTable
FROM Orders O JOIN Employees E ON O.EmployeeID=E.EmployeeID
WHERE E.EmployeeID=<strong>1</strong>
로그인 후 복사

1.2全局临时表,创建时以##开头. 在tempdb中存储,对所有的session都可见.

CREATE TABLE ##tmpTable2
(
ID INT,
NAME VARCHAR(<strong>20</strong>),
COMPANY VARCHAR(<strong>50</strong>)
)
SELECT * FROM ##tmpTable2 JOIN ...

DROP TABLE ##tmpTable2
로그인 후 복사

2.表变量:在内存中存储,比临时表执行速度快. 在SP或者function越过有效scope之后会自动释放,不用显式的写drop.表变量只可用在DML的操作中,会有比较多的限制.

--直接声明表变量
DECLARE @varTable TABLE
(
ID INT,
NAME VARCHAR(<strong>20</strong>),
COMPANY VARCHAR(<strong>50</strong>)
)

--先创建表类型
CREATE TYPE [dbo].[T_TEMP] AS TABLE(
ID INT,
NAME VARCHAR(<strong>20</strong>),
COMPANY VARCHAR(<strong>50</strong>)
)

--在声明表变量
DECLARE @varTable T_TEMP
로그인 후 복사

3.CTE(Common Table Expressions)通用表表达:是一个可以由定义语句引用的临时命名的结果集,在它们的简单形式中,可将 CTE 视为类似于非持续性类型视图的派生表.只须定义 CTE 一次,即可在查询中多次引用.

WITH CTE_NAME
AS
(
SELECT E.EmployeeID,E.FirstName,E.LastName,O.OrderID,O.CustomerID,O.OrderDate 
FROM Orders O JOIN Employees E ON O.EmployeeID=E.EmployeeID
WHERE E.EmployeeID=<strong>1</strong>
)
SELECT * FROM CTE_NAME
로그인 후 복사

CTE最强大之处在于递归查询,如要仔细研究可以参考微软的文章.

원천:php.cn
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿