DB2-SQL精萃：学习笔记2

最新推荐文章于 2021-06-15 16:01:15 发布

ciyongyuan4432

最新推荐文章于 2021-06-15 16:01:15 发布

阅读量252

点赞数

文章标签：数据库

26、定时临时集合（values语句的使用）

--结果不正确
SELECT *
FROM USER
WHERE department IN (1,2,3) AND
      birthday IN ('1978-01-01','1997-1-1','1999-1-1')


--直接报错
SELECT *
FROM USER
WHERE (department,birthday) IN (
                                (1,'1978-01-01'),
                                (2,'1997-01-01'),
                                (3,'1999-01-01') 
                               )
                              
--可以这样,很简洁
SELECT *
FROM USER
WHERE (department,birthday) IN (
                                values
	                                 (1,'1978-01-01'),
	                                 (2,'1997-01-01'),
	                                 (3,'1999-01-01') 
                               )
                              
--这样也可以
SELECT * FROM USER WHERE department = 1 AND birthday = '1978-01-01'
UNION
SELECT * FROM USER WHERE department = 2 AND birthday = '1997-01-01'
UNION
SELECT * FROM USER WHERE department = 3 AND birthday = '1999-01-01'


--在DB2中的with公共表表达式中使用values
WITH test(name_test,bday_test)
AS
(
  VALUES('john','1998-1-1'),('jack','1998-2-2')
)

SELECT name_test,bday_test FROM TEST WHERE name_test LIKE 'j%'

27、嵌套表表达式

SELECT name,birthday FROM user

SELECT *
(
	SELECT name,birthday FROM user
)a

SELECT *
(
	SELECT name,birthday FROM user
)a(b,c)


SELECT *
(
	SELECT name,birthday FROM user
)a(b,c) 
INNER JOIN 

(
	SELECT name,birthday FROM user
)x(b,c)
        ON x.b = a.b AND
           x.c = a.c

28、DB2临时表

DECLARE global temporary TABLE session.emp
(
	name VARCHAR(10),     --姓名
	dept SMALLINT,        --部门
	salary DECIMAL(7,2)   --工资
)
ON COMMIT DELETE rows  --事务临时表，在提交或者回滚时，自动截断临时表数据
                       --在退出会话时，自动截断临时表数据
                       

DECLARE global temporary TABLE session.emp
LIKE staff including COLUMN defaults   --创建的表格与源表有相同的列（列名、类型、NULL字符），
                                       --不导入数据
                                       --任意定义在源表中的默认约束都会生效
WITH replace
ON COMMIT preserve rows --会话临时表，在退出会话时，自动截断临时表数据
                        --在提交或者回滚时，数据是保持着的


DECLARE global temporary TABLE session.emp AS
(
	SELECT * FROM staff WHERE name = 'xxx'
)
definition only         --相当于with no data 也就是没数据
WITH replace

同样的创建表也是一样的。

CREATE TABLE emp as 
(
	SELECT name,dept,salary FROM staff
)
definition only   --创建表时不会添加数据


CREATE TABLE emp 
LIKE staff
including COLUMN defaults
excluding IDENTITY COLUMN attributes   --不包含identity列属性


--创建表，导入数据
CREATE TABLE emp
LIKE staff

INSERT INTO emp 
SELECT * FROM staff

注意：with replace选项的意思是如果不存在此临时表，则替换。

29、DB2在线分析处理（OLAP函数的使用）

--rows,ubounded(n) preceding,unbounded(n) following,nulls first的运用
SELECT 
		name,
		salary,
		
		--小于当前工资
		--rows关键字用来限制出现哪些行
		--unbounded preceding相当于小于，表示之前的
		--current row当前行
		sum(salary) OVER(ORDER BY salary               --默认NULL值会排在最后	
		                 rows BETWEEN unbounded preceding AND CURRENT row),
		
		--大于当前工资
		--unbounded following相当于大于，表示之后的
		sum(salary) OVER(ORDER BY salary nulls first   --NULLS first会把null值排在最前面
		                 rows BETWEEN CURRENT row AND unbounded following),
		
		--之前、之后都是无界限的                 
		sum(salary) OVER(ORDER BY salary nulls first
		                 rows BETWEEN unbounded preceding AND unbounded following),
		
		--从当前行开始到它前面的10行为止
		--从当前行开始到它后面的20行为止		
		sum(salary) OVER(ORDER BY salary nulls first
		                 rows BETWEEN 10 preceding AND 20 following) 
FROM 
(
	VALUES
	('three','market',4000),
	('red','tech'2000),
	('four','market',5000),
	('blank','tech',5000),
	('five','market',NULL),
	('blue','tech',4000)
) AS emoloy(name,dept,salary)

--FIRST_VALUE,LAST_VALUE,RANGE
SELECT 
		name,   --姓名
		dept,   --部门
		salary, --工资
		
		--按部门分组之后第一个值，忽略NULL值
		FIRST_VALUE(salary,'IGNORE NULLS') OVER(PARTITION BY DEPT),

        --按部门分组之后最后一个值，考虑NULL值
		LAST_VALUE(salary,'RESPECT NULLS') OVER(PARTITION BY DEPT),
		
		--按工资排序，行是：当前行的前一行、当前行的后两行
		SUM(SALARY) OVER(ORDER BY SALARY 
		                 ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING),
		
		--按工资排序，范围：在 当前值减去500、当前值加500 之间
		SUM(SALARY) OVER(ORDER BY SALARY 
		                 RANGE BETWEEN 500 PRECEDING AND 500 FOLLWING)		         	
FROM 
(
	VALUES
	('three','market',4000),
	('red','tech'2000),
	('four','market',5000),
	('blank','tech',5000),
	('five','market',NULL),
	('blue','tech',4000)
) AS emoloy(name,dept,salary)

/*==============================================================

LAG(表达式或者字段,偏移量,默认值,IGNORE NULLS或者RESPECT NULLS)

LEAD(表达式或者字段,偏移量,默认值,IGNORE NULLS或者RESPECT NULLS)

说明：    
     1、LAG是向前偏移，偏移量是向前偏移的行数，偏移量的默认值是1
        如果没有对应偏移量的行值，那么用默认值代替
        IGNORE NULLS、RESPECT NULLS分别是忽略NULL值、考虑NULL值。
     2、LEAD是向后偏移，偏移量是向后偏移的行数
     
总结：
	函数() OVER(PARTITION BY 子句 
	                ORDER BY 子句
	                 ROWS 子句
	                RANGE 子句  )
===============================================================*/

SELECT NAME,
       DEPT,
       SALARY,
       
       --向前偏移量为0
       LAG(SALARY,0) OVER(ORDER BY SALARY),
       
       --向前偏移量的默认值为1
       LAG(SALARY) OVER(ORDER BY SALARY),
       
       --向前偏移量为2
       LAG(SALARY,2) OVER(ORDER BY SALARY),
       
       --向前偏移量为3，如果没有相应偏移行，那么置为0
       LAG(SALARY,3,0,'IGNORE NULLS') OVER(ORDER BY SALARY),
       
       --向前偏移量为4，如果没有相应偏移行，那么置为-1
       LAG(SALARY,4,-1,'RESPECT NULLS') OVER(ORDER BY SALARY),
       
       --向后偏移量为1
       LEAD(SALARY) OVER(ORDER BY SALARY)
FROM
(
    VALUES
    ('张三','市场部',2000),
    ('赵红','技术部',2400),
    ('李四','市场部',3000),
    ('李白','技术部',3200),
    ('王五','市场部',4000),
    ('王蓝','技术部',5000)
) AS EMPLOY(NAME,DEPT,SALARY);

30、用递归方法合并字符串

CREATE TABLE t(idd INT ,a INT ,v VARCHAR(100))

INSERT INTO t
VALUES (1,1,'a'),
       (2,2,'b'),
       (3,1,'c'),
       (4,1,'d'),
       (5,3,'e'),
       (6,3,'f')

       
;WITH c
AS
(
SELECT idd,
       a, 
       v,
       row_number() 
           OVER(PARTITION BY a   --这里通过分组再排序，会把需要连接的字符串排在一起
                    ORDER BY idd) AS rownum
FROM t
),

cc
as
(
SELECT idd,
       a,
       CAST(v AS varchar(1000)) AS v,
       rownum,
       1 AS level
FROM c

UNION ALL

SELECT cc.idd,
       cc.a,
       CAST(cc.v +','+ c.v AS VARCHAR(1000)),  --保存已经连接的字符串
       cc.rownum+1,
       LEVEL + 1
FROM cc,c
WHERE cc.a = c.a AND
      cc.rownum + 1 = c.rownum   --通过rownum加1，和下一个字符串连接
)

SELECT * FROM cc
WHERE level = (SELECT max(level) FROM cc ccc WHERE ccc.a = cc.a )

31、实用的 DB2 SQL 语句

查看当前时间
VALUES CURRENT TIME;
查看当前日期
VALUES CURRENT DATE;
查看当前时间戳
VALUES CURRENT TIMESTAMP;
查看当前时区
VALUES CURRENT TIMEZONE;
查看用户
VALUES USER;
查看系统用户
VALUES SYSTEM_USER;
查看连接用户
VALUES SESSION_USER;
查看当前用户
VALUES CURRENT USER;
查看当前客户端的账户名称
VALUES CURRENT CLIENT_ACCTNG
查看当前客户端的应用程序名称
VALUES CURRENT CLIENT_APPLNAME
查看当前客户端的用户名称
VALUES CURRENT CLIENT_USERID
查看当前客户端的工作站名称
VALUES CURRENT CLIENT_WRKSTNNAME
查看当前 SCHEMA
VALUES CURRENT SCHEMA
查看当前数据库名
VALUES CURRENT SERVER
查看当前路径
VALUES CURRENT PATH
查看当前包路径
VALUES CURRENT PACKAGE PATH
查看锁超时时间
VALUES CURRENT LOCK TIMEOUT
查看查询优化级别(0-9)
VALUES CURRENT QUERY OPTIMIZATION
查看 DB2 版本
SELECT * FROM SYSIBM.SYSVERSIONS
查看系统中有哪些表以及这些表的概要信息
SELECT * FROM SYSCAT.TABLES
查看某个表有哪些列以及这些列的概要信息
SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME=<YOUR_TABLE_NAME>
查看某个表的权限
SELECT * FROM SYSCAT.TABAUTH WHERE TABNAME=''

32、DB2特殊寄存器

所谓特殊寄存器就是一些变量，这些变量显示了DB2的一些状态信息，我们可以查看所有这些变量，也可以更新其中一部分变量。

--查看
SELECT CURRENT TIME FROM sysibm.sysdummy1;

VALUES CURRENT TIME;

--更新
SET CURRENT SCHEMA  = 'DB2ADMIN';


以下是所有的特殊寄存器列表，关于其中每个变量的具体含义可以参考 db2 信息中心。
    1. SPECIAL REGISTER                          UPDATE     DATA-TYPE
    2. ===================================      ========    ================
    3. CURRENT CLIENT_ACCTNG                        no           VARCHAR(255)
    4. CURRENT CLIENT_APPLNAME                      no           VARCHAR(255)
    5. CURRENT CLIENT_USERID                        no           VARCHAR(255)
    6. CURRENT CLIENT_WRKSTNNAME                    no           VARCHAR(255)
    7. CURRENT DATE                                 no           DATE
    8. CURRENT DBPARTITIONNUM                       no           INTEGER
    9. CURRENT DECFLOAT ROUNDING MODE               no           VARCHAR(128)
    10. CURRENT DEFAULT TRANSFORM GROUP             yes          VARCHAR(18)
    11. CURRENT DEGREE                             yes           CHAR(5)
    12. CURRENT EXPLAIN MODE                        yes          VARCHAR(254)
    13. CURRENT EXPLAIN SNAPSHOT                   yes           CHAR(8)
    14. CURRENT FEDERATED ASYNCHRONY               yes           INTEGER
    15. CURRENT IMPLICIT XMLPARSE OPTION            yes          VARCHAR(19)
    16. CURRENT ISOLATION                           yes          CHAR(2)
    17. CURRENT LOCK TIMEOUT                        yes          INTEGER
    18. CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION  yes     VARCHAR(254)

	19. CURRENT MDC ROLLOUT MODE                    yes          VARCHAR(9)
	20. CURRENT OPTIMIZATION PROFILE                yes          VARCHAR(261)
	21. CURRENT PACKAGE PATH                        yes          VARCHAR(4096)
	22. CURRENT PATH                                yes          VARCHAR(2048)
	23. CURRENT QUERY OPTIMIZATION                  yes          INTEGER
	24. CURRENT REFRESH AGE                         yes          DECIMAL(20,6)
	25. CURRENT SCHEMA                              yes          VARCHAR(128)
	26. CURRENT SERVER                              no           VARCHAR(128)
	27. CURRENT TIME                                no           TIME
	28. CURRENT TIMESTAMP                           no           TIMESTAMP
	29. CURRENT TIMEZONE                            no           DECIMAL(6,0)
	30. CURRENT USER                                no           VARCHAR(128)
	31. SESSION_USER                                yes          VARCHAR(128)
	32. SYSTEM_USER                                 no           VARCHAR(128)
	33. USER                                        yes          VARCHAR(128)

33、DB2物化查询表

DB2物化查询表（Materialized Query Tables），是一个表，存储了一个查询的结果。当我们查询相关表时，DB2会自动决定是使用原表还是使用物化查询表。

优点是：当数据库中有海量数据时，使用物化查询表可以极大的提高查询速度。

缺点是：维护物化查询表相当耗时。

所以，物化查询表广泛用在数据仓库、海量数据的报表查询中，这类查询的特点是数据量大、经常需要分组统计、数据不会频繁变更，在这些场合中物化查询表可以充分发挥优势。

语法：

CREATE TABLE <table-name> AS
<select stmtement>

DATA INITIALLY DEFERRED

--如果原始表数据改变，是延迟刷新，还是立即刷新物化查询表
--如果是延迟的，那么在使用物化查询表直线，必须用REFRESH TABLE语句刷新它
REFRESH [DEFERRED | IMMEDIATE]

--允许优化器使用物化查询表，还是禁止使用
[ENABLE QUREY OPTIMIZATION | DISABLE QUREY OPTIMIZATION]

/*=====================================================================
指定物化查询表是由系统维护，还是由用户维护
如果是用户负责维护物化查询表，
当用户对物化查询表进行insert、update、delet，
那么物化查询表将不能REFRESH了
=======================================================================*/
[MAINTAINED BY [SYSTEM | USER | FEDERATED_TOOOL]]

示例：
CREATE TABLE emp_summary AS
(
        SELECT
                workdept
                ,COUNT(*) AS crows
                ,SUM(empno) AS sumno
         FROM  employee
        GROUP BY workdept
)
DATA INITIALLY DEFERRED
REFRESH IMMEDIATE;

--DB2优化器会自动使用刚才定义的物化查询表
SELECT  workdept,avg(empno) FROM employee GROUP BY workdept

--DB2优化器会将上面的SQL转化成下面这样
SELECT  workdept,summo / crows FROM emp_summary


/*===============================================================
由于维护物化查询表相当耗时，为提高维护效率，
可以给延迟刷新的物化查询表定义一个staging表，
这个表用来对物化查询表执行增量刷新，当刷新完成时，staging表就会被删除。
=================================================================*/
CREATE TABLE emp_summary_staging
AS
(
	workdept,
	crows,
	sumno,
	GLOBALTRANSID,    --表示每个被传播的行对应的全局事务ID
	GLOBALTRANSTIME   --表示事务的时间戳
)FOR emp_summary PROPAGATE IMMEDIATE;   --表示原始表做出任何修改，都将积累在staging表中


/*===============================================================
staging表在创建之后，处于检查暂挂状态，
可以通过SET INTEGRITY语句将表置为正常状态，
这样就可以用staging表刷新物化查询表
=================================================================*/
SET INTEGRITY FOR emp_summary_staging STAGING IMMEDIATE UNCHECKED;

REFRESH TABLE emp_summary;

34、变量

--一次定义多个变量，定义多个变量同时初始化
DECLARE a,b INT;

DECLARE x,y INT DEFAULT 0;

DECLARE z DECIMAL(9,2) DEFAULT 0.0;


--赋值
SET x = 10;

SET x = (SELECT sum(c) FROM t);

SELECT sum(c) INTO y FROM t;

VALUES 10 INTO y;


/*==============================================
会话全局变量:
           与特定的会话关联，
           对于会话中的每个存储过程都是全局的，
           会话变量是在存储过程之外声明的.
================================================*/
CREATE VARIABLE myVar INTEGER DEFAULT 0;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/653220/viewspace-1982105/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/653220/viewspace-1982105/