DB2-SQL精萃：学习笔记1

最新推荐文章于 2020-03-02 16:17:50 发布

ciyongyuan4432

最新推荐文章于 2020-03-02 16:17:50 发布

阅读量266

点赞数 2

最近从网上下载了一个名为：DB2 SQL精粹的电子书，以下是作者的联系方式：

联系作者：
E-mail ： wave0409@163.com
博客： http://blog.csdn.net/shangboerds 或 http://iamwave.javaeye.com/

这个学习笔记只是记录我认为需要特别关注的知识点，不会面面俱到。

1、在DB2中连接字符串类似于Oracle中，可以用“||”符号，或者concat函数。

2、在DB2中的字符串中有单引号时，必须用2个单引号代替一个（Oracle、SQL Server完全一样）。

3、在DB2中的字符串中需要特殊字符时，比如下面加上回车、换行符：

select 'a' || 'b' || CHAR(13) || CHAR(10)

这里的char函数的参数是特殊字符的ACSII值。

4、在DB2中通过values关键字一次添加多行数据。

insert into t(col1,col2)
values (1,2),
       (2,3)

5、Update语句的特别写法：

update
(
	select col1,
		   col2 
	from t 
	where col2 = 2
)
set col1 = 5


update
(
	select col1,
		   col2,
		   row_number() over() as row_num  --联机分析函数，起到编号的作用
    from t
    where col2 = 2
)
set col1 = row_num

6、delete语句高性能的用法：

--第一种办法
delete from
(
	select col1,
	       col2 
	from t
	where col2 = 2 
)


--第二种办法先删除表，再重新建立表
drop table t

create table t(col1 int,col2 int)


--第三种通过不产生日志来加快速度
alter table t 
activate not logged 
initially with empty table

7、多字段查询

select col1,
       col2
from t
where (col1,col2) = (1,2)


select col1,
       col2
from t
where (col1,col2) = (select col1,col2 from t where col2 = 2)


update t
set (col1,col2) = (select col1,col2 from t where col2 = 2)
where col2 = 3

8、Grouping Sets的注意点：

group by grouping sets(a,b,c) 
定义就是:
group by a 
union all
group by b
union all
group by c


group by grouping sets((a,b,c)) 
加括号后,里面是一个整体,特别要注意加括号和不加括号的区别,等价于:
group by a,b,c


group by grouping sets(a,(b,c)) 
等价于
group by a 
union all
group by b,c


group by grouping sets(a),
         grouping sets(b),
         grouping sets(c)
等价于
group by a,b,c   


group by grouping sets(a),
         grouping sets((b,c))
等价于
group by a,b,c  


group by grouping sets(a),
         grouping sets(b,c)
等价于
group by a,b
union all
group by a,c   


group by a,
         b,
         grouping sets((b,c))
等价于
group by a,b,c


group by a,
         b,
         grouping sets(b,c)
等价于
group by a,b
union all
group by a,b,c
 
 
group by a,
         b,
         c,
         grouping sets(b,c)
等价于
group by a,b,c
union all
group by a,b,c   

--注意：由于union all不去重,所以结果集中会出现重复的记录
select *
from 
(
values(1,2,3),
      (2,3,4),
      (3,4,5)
)x(a,b,c)
group by a,
         b,
         grouping sets(b,c)

9、rollup关键字运用时要注意字段的顺序，对字段列表从左到右，进行分层级的汇总；而cube关键字不关注字段顺序，按照多字段的各种值进行多维度的汇总。

10、some与any关键字是完全相同的，意思是一部分；all关键字表示所有；exists关键字表示只要有就可以；in关键字表示在列表中。这里特别要注意的是当用all关键字，而子查询中返回了NULL时：如

1. ---语句 1,返回一条记录
2. SELECT NAME FROM STUDENT WHERE CLASS='五年级 A 班' AND CHINESE >
3. (
        SELECT MAX(CHINESE) FROM STUDENT WHERE CLASS='五年级 B 班'
4.
5. );
6.
7. ---语句 2，没有返回记录
8. SELECT NAME FROM STUDENT WHERE CLASS='五年级 A 班' AND CHINESE > ALL
9. (
        SELECT CHINESE FROM STUDENT WHERE CLASS='五年级 B 班'
10.
11. );

第2个语句之所以没有返回任何记录，原因在于子查询返回了3条记录，而其中有一条的值是NULL，那么在判断CHINESE > ALL(子查询返回的值)时，由于任何值与NULL比较时返回UNKNOWN（在SQL Server中是这样的，并且可以通过设置会话属性来改变具体的比较行为，这里DB2应该也有类似的情况），就是两者是不能比较的，那么最后这个判断不成立，所以没有返回任何记录。这是对作者的一点补充。

11、union（合集）、except（差集）、intersect（交集）都是去重的，不去重的版本是union all（合集）、except all（差集）、intersect all（交集）。由于这几个关键字都不同的优先级，所以在混合使用时，最好使用括号。

12、查看update语句、delete语句、insert语句，在更新之前、之后的数据。

--返回那些已经修改过的数据
SELECT * FROM final TABLE
(
	UPDATE USER SET salary = salary * (1+0.2)
	WHERE salary <= 2000
)

--返回那些已经修改过的数据，同时增加了过滤条件
SELECT * FROM final TABLE
(
	UPDATE USER SET salary = salary * (1+0.2)
	WHERE salary <= 2000
)
WHERE name LIKE '李%'

--返回那些被修改之前的数据
SELECT * FROM old TABLE
(
	UPDATE USER SET salary = salary * (1+0.2)
	WHERE salary <= 2000
)

--返回那些被修改的数据，同时显示被修改之前的值
SELECT * FROM final TABLE
(
	UPDATE USER
	include(old_salary float)    --include(old_字段名 字段类型)
	SET salary = salary * (1+0.2)
	WHERE salary <= 2000
)

13、merge语句的用法。

merge INTO employe AS e
using manager AS m
ON e.employeid = m.managerid           --其他的关联条件，不要写在这里，否则会导致逻辑错误

WHEN matched AND e.salary < m.salary   --匹配、同时满足条件，那么更新目标表匹配记录 
     THEN UPDATE SET e.salary = m.salary
     
WHEN matched AND e.salary > m.salary   --匹配、同时满足条件，那么删除目标表匹配记录 
     THEN DELETE

WHEN NOT matched --不匹配，那么把源表中的记录添加到目标表
     THEN INSERT VALUES(m.managerid,m.name,m.salary)
     
ELSE ignore;     --其他的情况被忽略：从这语句能看出是匹配、e.salary = m.salary的情况

14、采集样本数据

--返回前10行
SELECT * FROM employe FETCH first 10 rows only

--随机返回前10行
SELECT * FROM BASE_ORG 
ORDER BY rand() 
FETCH first 10 rows only

/*=========================================================================
语法：

select 
from 表名
tablesample [bernoulli | system] (percent) repeatable(num)

说明：
1、bernoulli伯努利采样方法：会检查每一行，准确率高，但是性能较差
2、system 系统页级采样方法：会检查每个数据页，性能高，但是准确率差
3、repeatable ：多次执行相同的语句返回相同的行集合
==========================================================================*/
SELECT *
FROM employe
tablesample bernoulli (8) repeatable(586)

15、尽量避免在SQL中使用distinct。

SELECT *
FROM employe
WHERE name IN (SELECT DISTINCT name FROM manager)

这里在子查询中加了distinct后，由于DB2的优化器改写了上面的查询，所以不会产生性能问题，但还是尽量不要用distinct。

16、尽量避免在SQL中使用or，因为这样会影响SQL语句的性能。

--可能产生性能问题
SELECT *
FROM employe
WHERE name = 'abc' OR name ='def'

--改进版本1
SELECT *
FROM employe
WHERE name IN ('abc','def')

--改进版本2
SELECT *
FROM employe
WHERE name = 'abc'

UNION ALL

SELECT *
FROM employe
WHERE name = 'def'

17、尽量避免在SQL语句的where子句中使用函数

--如果使用函数的字段上建了索引，会导致索引无效
SELECT *
FROM USER
WHERE DATE(registerdate) = '2012-05-05'

--改进版,注意这里的registerdate字段类型是timestamp类型
SELECT *
FROM USER
WHERE registerdate = '2012-05-05 00:00:00.0'

18、尽量避免在SQL语句中使用Like，这是作者在文中的建议，但实际上这样写时 like 'abc%' ，还是可以用索引的，只是在like '%abc%' 时，才会导致索引无效。

19、DB2中的4种隔离级别。

隔离级别由低到高，隔离级别越低，性能越好，但是会导致并发性问题：
未落实的读 UR
游标稳定性 CS
读稳定性   RS
可重复读   RR

20、把查询条件、关联条件，写在Join的on中，和写在where中，有细微的差别，特别是对于Left Join，把条件写在on中，写在where中是很不一样的。

21、半角全角转换，这里作者说的比较模糊。

varchar(vargraphic('123456789'))

22、将NULL转化成其他值

value(id,0)             --当id为NULL时返回0，如果id不为NULL返回id对应的值

coalesce(id1,id2,id3,0) --返回列表中第一个非NULL的值

23、操作日期和时间

类型         格式
TIME        hh:mm:ss
DATE        yyyy-mm-dd
TIMESTAMP   yyyy-mm-dd hh:mm:ss.zzzzzz


支持的关键字:
单数		        复数	
YEAR           YEARS
MONTH          MONTHS
DAY            DAYS
HOUR           HOURS
MINUTE         MINUTES
SECOND         SECONDS
MICROSECOND    MICROSECONDS


--使用方法
values date('2009-10-1') + 1 year + 2 month - 8day
values date('10:23:15') + 3 hour -26 minute
values timestamp('2009-10-1 10:23:15.000000') - 3 second + 450 microsecond


/*==========================================
两个日期相减时会出现问题：

这里首先把日期、时间转化成decimal，按照如下转化

类型         DECIMAL         转化后的格式
TIME        DECIAML(6,0)    hhmmss
DATE        DECIAML(8,0)    yyyymmdd
TIMESTAMP   DECIMAL(20,6)   yyyymmddhhmmss.zzzzzz

然后再相减，计算出来的值是有问题的
===========================================*/
values date('2009-10-5') - date('2009-10-2')
values date('10:25:15') -date('10:23:15')
values timestamp('2009-10-5 10:23:15.000000') - timestamp('2009-10-1 10:23:15.000000')


--两个日期相减的改进版本
values days(date('2009-10-5')) - days(date('2009-10-2'))
values days(date('10:25:15') - days(date('10:23:15'))
values days(timestamp('2009-10-5 10:23:15.000000')) - days(timestamp('2009-10-1 10:23:15.000000'))


/*=======================================
两个日期相减的更灵活版本
timestamp(参数1,参数2)

说明
参数1：可以指定为1、2、4、8、16、32、64、128、256，
      分别表示两个日期之间的 毫秒数、秒数、分钟数、
      小时数、天数、周数、月数、季度数、年数
参数2：为两个日期相减的结果
=========================================*/
values timestampdiff(256,char(date('2009-10-5') - date('2009-10-2')))
values timestampdiff(128,char(date('2009-10-5') - date('2009-5-2')))

24、数据类型转化

--原来字段id是字符型，现在转成整型
select *
from 
(
values ('11','wave','1997-7-1'),
       ('2','wave','1992-1-5')
)x(id,name,birthdate)
order by integer(id)


select *
from 
(
values ('11','wave','1997-7-1'),
       ('2','wave','1992-1-5')
)x(id,name,birthdate)
order by cast(id as integer)

25、SQL语句中的IF-ELSE（case语句的使用）

select name,
       
       case when sex = 1 then '男'
            else '女'
       end as sex,
       
       birthday
from user


--根据不同的条件来更新不同值，在name没有索引的情况下，只要一次全表扫描就完成更新
update user
set birthday = (
				  case when name = '张三' then '1997-05-01'
				       when name = '李四' then '1998-06-01'
				       else birthday
				  end
               )
where name in ('张三','李四')

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

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