数据库学习笔记（三）

7.行与列互转
7.1.行转列
7.1.1.传统写法
使用select…case的方式实现行转列
例子：
CREATE TABLE [StudentScores]
(
[UserName] NVARCHAR(20), –学生姓名
[Subject] NVARCHAR(30), –科目
[Score] FLOAT, –成绩
)
INSERT INTO [StudentScores] SELECT ‘Nick’, ‘语文’, 80
INSERT INTO [StudentScores] SELECT ‘Nick’, ‘数学’, 90
INSERT INTO [StudentScores] SELECT ‘Nick’, ‘英语’, 70
INSERT INTO [StudentScores] SELECT ‘Nick’, ‘生物’, 85
INSERT INTO [StudentScores] SELECT ‘Kent’, ‘语文’, 80
INSERT INTO [StudentScores] SELECT ‘Kent’, ‘数学’, 90
INSERT INTO [StudentScores] SELECT ‘Kent’, ‘英语’, 70
INSERT INTO [StudentScores] SELECT ‘Kent’, ‘生物’, 85
如果我想知道每位学生的每科最高成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据
SELECT
UserName,
MAX(CASE Subject WHEN ‘语文’ THEN Score ELSE 0 END) AS ‘语文’,
MAX(CASE Subject WHEN ‘数学’ THEN Score ELSE 0 END) AS ‘数学’,
MAX(CASE Subject WHEN ‘英语’ THEN Score ELSE 0 END) AS ‘英语’,
MAX(CASE Subject WHEN ‘生物’ THEN Score ELSE 0 END) AS ‘生物’
FROM dbo.[StudentScores]
GROUP BY UserName

7.1.2.Sqlserver写法
使用pivot关键字来实现
例子：
SELECT UserName,[语文],[数学],[英语],[生物] FROM
(
Select UserName,Subject ,Score from StudentScores
) t pivot
(
max(Score) for Subject in([语文],[数学],[英语],[生物])
)TBL
GROUP BY UserName

EXEC sp_dbcmptlevel Test, 90

7.2.列转行
7.2.1.传统写法
使用union all的方式实现列转行
例子：
CREATE TABLE [ProgrectDetail]
(
ProgrectName NVARCHAR(20), –工程名称
OverseaSupply INT,
NativeSupply INT,
SouthSupply INT,
NorthSupply INT
)
INSERT INTO ProgrectDetail
SELECT ‘A’, 100, 200, 50, 50
UNION ALL
SELECT ‘B’, 200, 300, 150, 150
UNION ALL
SELECT ‘C’, 159, 400, 20, 320
UNION ALL
SELECT ‘D’, 250, 30, 15, 15
查询脚本
SELECT ProgrectName, ‘OverseaSupply’ AS Supplier,
MAX(OverseaSupply) AS ‘SupplyNum’
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, ‘NativeSupply’ AS Supplier,
MAX(NativeSupply) AS ‘SupplyNum’
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, ‘SouthSupply’ AS Supplier,
MAX(SouthSupply) AS ‘SupplyNum’
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT ProgrectName, ‘NorthSupply’ AS Supplier,
MAX(NorthSupply) AS ‘SupplyNum’
FROM ProgrectDetail
GROUP BY ProgrectName

7.2.2.Sqlserver写法
使用UNPIVOT关键字来实现
例子：
SELECT ProgrectName, Supplier,SupplyNum
FROM
(
SELECT ProgrectName, OverseaSupply, NativeSupply,
SouthSupply, NorthSupply
FROM ProgrectDetail
)T
UNPIVOT
(
SupplyNum FOR Supplier IN
(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P

8.存储过程与函数
8.1.不同点：
1、标识符不同。函数的标识符为FUNCTION，过程为：PROCEDURE。
2、函数中有返回值，且必须返回，而过程没有返回值。
3、过程无返回值类型，不能将结果直接赋值给变量；函数有返回值类型，调用时，除在select中，必须将返回值赋给变量。
4、函数可以在select语句中直接使用，而过程不能，例如：假设已有函数fun_getAVG() 返回number类型绝对值。那么select fun_getAVG(col_a) from table 这样是可以的。

9.包的定义与声明
1.create or replace package ppppppppppppp is
1.create or replace package body bbbbbbbbbbbbb is

10.oracle查询效率优化
http://download.csdn.net/detail/langzi_zhang/9666105

11.物化视图
http://download.csdn.net/detail/langzi_zhang/4522582