㈠ 如何在db2命令行运行sql必知必会例子
db2 => connect to dbName user xxx using password
db2 => sql语句
如果要执行一个sql脚本文件：
db2 => quit
c:\> db2 -tvf sql文件名
db2 -td@ -f filename
@是语句结束符。
E:\>db2 ? options
db2 [option ...] [db2-command | sql-statement |
[? [phrase | message | sqlstate | class-code]]]
option：-a、-c、-e{c|s}、-finfile、-lhistfile、-n、-o、-p、-rreport、-s、-t、
-td;、-v、-w、-x 和 -zoutputfile。
选项 描述 缺省设置
------ ---------------------------------------- ---------------
-a 显示 SQLCA OFF
-c 自动落实 ON
-e 显示 SQLCODE/SQLSTATE OFF
-f 读取输入文件 OFF
-l 将命令记录到历史文件中 OFF
-n 除去换行字符 OFF
-o 显示输出 ON
-p 显示 db2 交互式提示符 ON
-r 将输出报告保存到文件 OFF
-s 在命令出错时停止执行 OFF
-t 设置语句终止字符 OFF
-v 回送当前命令 OFF
-w 显示 FETCH/SELECT 警告消息 ON
-x 不打印列标题 OFF
-z 将所有输出保存到输出文件 OFF
注意：
使用 DB2OPTIONS 环境变量定制选项缺省值。
紧跟选项字母后的减号（-）使该选项关闭。
使用 UPDATE COMMAND OPTIONS 更改选项设置（以交互式或
文件输入方式）。
只能提供nt环境下编写脚本的例子给你以供参考:
脚本样例:
db2 connect to yourdb user yourname using yourpassword
db2 insert into newuser(username,password,email) values('Amy','1234','[email protected]')
db2 insert into newuser(username,password,email) values('Judy','1234','[email protected]')
db2 commit
db2 disconnect yourdb
运行脚本: 运行db2cmd X:\XXX.bat
以下摘自本论坛的FAQ可参考:
"
在命令窗口中运行DB2脚本，可用 db2 -svtf 脚本文件名 来实现。
例如，脚本文件名为sample.sql，运行：db2 -svtf sample.sql
参数中：
s 代表遇到错误时中止运行脚本
v 代表输出结果到屏幕
t 指以；号作为每行的分隔符
f 指后面需跟脚本文件名 "---此摘录版权归斑竹非本人所有
具体在AS400如何编写脚本非常遗憾.
db2 -x select SERIALNO from tabname where clause
C:>db2 attach to db2164 user ccp
输入 ccp 的当前密码：
实例连接信息
实例服务器 = DB2/NT 8.2.0
授权标识 = CCP
本地实例别名 = DB2164
C:>db2 connect to dw164 user ccp
输入 ccp 的当前密码：
数据库连接信息
数据库服务器 = DB2/NT 8.2.0
SQL 授权标识 = CCP
本地数据库别名 = DW164
C:>db2 select * from CCP_STS1 fetch first 2 rows only with ur
CUST_ID NOW_PRED_S LOAD_TIME
-------------------- -------------------- --------------------------
3094736. ZFS 2008-05-07-10.02.00.453000
3145886. ZFS 2008-05-07-10.02.00.453000
2 条记录已选择。
C:>db2 list command options
命令行处理器选项设置
后端进程等待时间（秒） (DB2BQTIME) = 1
连接至后端的重试次数 (DB2BQTRY) = 60
请求队列等待时间（秒） (DB2RQTIME) = 5
输入队列等待时间（秒） (DB2IQTIME) = 5
命令选项 (DB2OPTIONS) = +m
选项 描述 当前设置
------ ---------------------------------------- ---------------
-a 显示 SQLCA OFF
-c 自动落实 ON
-d 检索并显示 XML 声明 OFF
-e 显示 SQLCODE/SQLSTATE OFF
-f 读取输入文件 OFF
-i 显示 XML 数据并带有缩进 OFF
-l 将命令记录到历史记录文件中 OFF
-m 显示受影响的行数 OFF
-n 除去换行字符 OFF
-o 显示输出 ON
-p 显示交互式输入提示符 ON
-q 保留空格和换行符 OFF
-r 将输出保存到报告文件 OFF
-s 在命令出错时停止执行 OFF
-t 设置语句终止字符 OFF
-v 回传当前命令 OFF
-w 显示 FETCH/SELECT 警告消息 ON
-x 不打印列标题 OFF
-z 将所有输出保存到输出文件 OFF
C:>db2set DB2OPTIONS=-x
C:>db2 select * from CCP_STS1 fetch first 2 rows only with ur
4654908. ZFS 2008-05-07-10.02.00.453000
3716687. ZFS 2008-05-07-10.02.00.453000
㈡ SQL必知必会(索引的使用原则)
你能看到查询效率还是比较低的。当我们对 user_id 字段创建索引之后
两个单索引 comment_time , user_id
联合索引（ user_id , comment_time ）
联合索引（ comment_time , user_id ）
这里我们对 comment_text 创建索引，再执行上面的 SQL 语句
㈢ SQL书籍推荐
1、《SQL必知必会》
福达着, 钟鸣、刘晓霞译
这本书由浅入深地讲解了SQL的基本概念和语法，涉及数据的排序、过滤和分组，以及表、视图、联结、子查询、游标、存储过程和触发器等内容，实例丰富，便于查阅。新版增加了针对ApacheOpenOfficeBase、MariaDB、SQlite等DBMS的描述，并根据新版本的Oracle、SQLServer、MySQL和PostgreSQL更新了相关示例，非常适合初学者。
2、《SQL基础教程》
MICK 着, 孙淼、罗勇 译
这本书豆瓣评分9.0，重印13次，第2版基于新版本RDBMS全面升级，并新增一章介绍从应用程序执行SQL语句的方法。本书从数据库、表的结构到查询、更新表的语法，常用的函数，表的联结等，内容逐步深入。对于初学者常见的疑难点，均通过专栏进行讲解。各章节后精心设计了练习题，帮助读者巩固理解
3、《SQL Cookbook》
Anthony Molinaro 着
本书是一本指南，其中包含了一系列SQL 的常用问题以及它们的解决方案，希望能对读者的日常工作有所帮助。有150 多个小节，这还仅仅是SQL 所能做的事情的一鳞半爪，而这本书更主要的是让读者看到，SQL 能够做多少一般认为是SQL 问题范围之外的事情。
4、《深入浅出SQL（中文版）》
贝里 着，O‘Reilly Taiwan 译
这本对所有没SQL基础的人来说都是本好书，幽默的语言，紧凑的内容，而且还有生动的图画，这无非给了初学者一个很好的开始，不仅会信心大增，也会提高学习兴趣，同时对后续的提高也打下了基础。着重基本语法的理解和基本概念的阐述，穿插在各个章节的练习题恰好提供了巩固作用，如果你正苦于寻找一本SQL入门书籍，那么它一定是不二之选
5、《SQL 反模式》
Bill Karwin 着，谭振林 / Push Chen 译
《SQL反模式》是一本广受好评的SQL图书。它介绍了如何避免在SQL的使用和开发中陷入一些常见却经常被忽略的误区。它通过讲述各种具体的案例，以及开发人员和使用人员在面对这些案例时经常采用的错误解决方案，来介绍如何识别、利用这些陷阱，以及面对问题时正确的解决手段。另外，《SQL反模式》还涉及了SQL的各级范式和针对它们的正确理解。
㈣ SQL必知必会(视图)
这样的话，下次再对视图进行查询的时候，视图结果就进行了更新。
我在讲解 SQL99 标准连接操作的时候，举了一个 NBA 球员和身高等级连接的例子，有两张表，分别为 player 和 height_grades。其中 height_grades 记录了不同身高对应的身高等级。这里我们可以通过创建视图，来完成球员以及对应身高等级的查询。
这样的话，我们直接查询视图，就可以得到格式化后的结果：
㈤ SQL必知必会(SQL 是如何执行的)
SQL 在 Oracle 中的执行过程
首先我们需要看下 profiling 是否开启，开启它可以让 MySQL 收集在 SQL 执行时所使用的资源情况，命令如下：
然后我们执行一个 SQL 查询
㈥ SQL必知必会(第五版)
去重 ：使用DISTINCT关键字，它必须直接放在列名的前面。
检索前5行数据：
LIMIT 5 OFFSET 5指示MySQL等DBMS返回从第5行起的5行数据。第一个数字是检索的行数，第二个数字是指从哪儿开始。
2.1 排序数据
注意： ORDER BY 子句的位置在指定一条ORDER BY子句时，应该保证它是 SELECT语句中最后一条子句 。如果它不是最后的子句，将会出错。
ORDER BY 2, 3表示先按prod_price，再按prod_name进行排序。
好处在于不用重新输入列名。
缺点。首先，不明确给出列名有可能造成错用列名排序。其次，在对SELECT清单进行更改时容易错误地对数据进行排序（忘记对ORDER BY子句做相应的改动）。最后，如果进行排序的列不在SELECT清单中，显然不能使用这项技术。
DESC 是DESCENDING的缩写，这两个关键字都可以使用。与DESC相对的是 ASC （或ASCENDING），在升序(A→Z)排序时可以指定它。但实际上，ASC没有多大用处，因为升序是默认的（如果既不指定ASC也不指定DESC，则假定为ASC）。
DESC关键字只应用到直接位于其前面的列名。如果想在多个列上进行降序排序，必须对每一列指定DESC关键字。
在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出。
在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误。
提示： 单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号。
操作符（operator） 用来联结或改变WHERE子句中的子句的关键字，也称为逻辑操作符（logicaloperator）。
AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。
一个AND子句，只有两个过滤条件。可以增加多个过滤条件，每个条件间都要使用AND关键字。
ORDER BY子句，放在WHERE子句之后。
OR： WHERE子句中使用的关键字，用来表示检索匹配任一给定条件的行。
SQL（像多数语言一样）在处理OR操作符前，优先处理AND操作符。
圆括号具有比AND或OR操作符更高的优先级。
任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。
IN：WHERE子句中用来指定要匹配值的清单的关键字，功能与OR相当。
IN操作符一般比一组OR操作符执行得更快。
IN的最大优点是可以包含其他SELECT语句，能够更动态地建立WHERE子句。
NOT： WHERE子句中用来否定其后条件的关键字。
通配符（wildcard） 用来匹配值的一部分的特殊字符。
搜索模式（search pattern） 由字面值、通配符或两者组合构成的搜索条件。
说明：区分大小写根据DBMS的不同及其配置，搜索可以是区分大小写的。如果区分大小写，则’fish%’与Fish bean bag toy就不匹配。
除了能匹配一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。
'%’不会匹配名称为NULL的行。
下划线的用途与%一样，但它只匹配单个字符，而不是多个字符。
与%能匹配多个字符不同，_总是刚好匹配一个字符，不能多也不能少。
此语句的WHERE子句中的模式为’[JM]%'。这一搜索模式使用了两个不同的通配符。[JM]匹配方括号中任意一个字符，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。
也可以使用NOT操作符得出类似的结果。^的唯一优点是在使用多个WHERE子句时可以简化语法：
例如：
拼接（concatenate）
将值联结到一起（将一个值附加到另一个值）构成单个值。
操作符可用加号（+）或两个竖杠（||）表示。
说明：是+还是||？SQL Server使用+号。DB2、Oracle、PostgreSQL和SQLite使用||。在MySQL和MariaDB中，必须使用特殊的函数。
说明： TRIM函数 大多数DBMS都支持 RTRIM() （去掉字符串右边的空格）、 LTRIM() （去掉字符串左边的空格）以及 TRIM() （去掉字符串左右两边的空格）。
SQL支持列别名。 别名（alias） 是一个字段或值的替换名。别名用 AS 关键字赋予。
AS关键字可选，最好用它。
提示：如何测试计算
SELECT语句为测试、检验函数和计算提供了很好的方法。虽然SELECT通常用于从表中检索数据，但是省略了FROM子句后就是简单地访问和处理表达式，例如SELECT 3＊2;将返回6，SELECT Trim(' abc ');将返回abc,SELECT Curdate()；使用Curdate()函数返回当前日期和时间。
UPPER()函数： 将文本转换为大写。
SQL函数不区分大小写，因此upper(), UPPER(), Upper()都可以，substr(), SUBSTR(), SubStr()也都行。
㈦ SQL必知必会(SQL99连接（JOIN）)
SQL92
SQL99
SQL92
SQL99
SQL99
SQL92
SQL99
至此我们讲解完了 SQL92 和 SQL99 标准下的连接查询，连接操作基本上可以分成三种情况：
SQL 连接具有通用性，但是不同的 DBMS 在使用规范上会存在差异，在标准支持上也存在不同。在实际工作中，你需要参考你正在使用的 DBMS 文档，这里我整理了一些需要注意的常见的问题。
1. 不是所有的 DBMS 都支持全外连接
2.Oracle 没有表别名 AS
3.SQLite 的外连接只有左连接
1. 控制连接表的数量
2. 在连接时不要忘记 WHERE 语句
3. 使用自连接而不是子查询
㈧ SQL必知必会（二）函数、子查询
#文本处理函数
UPPER() --大写
LOWER() --小写
SOUNDEX() --读音类似
LENGTH() --字符串长度
#日期和时间处理函数
SELECT order_code
FROM order_table
WHERE DATEPART(yy,order_date)=2012
#to_char()函数提取日期成分，MySQL可用year()函数提取年份
SELECT order_num
FROM order_table
WHERE to_number(to_char(order_date,'YYYY'))=2012; --to_char提取日期成分to_number转化为数值
SELECT order_num
FROM order_table
WHERE order_date BETWEEN to_date('01-01-2012') AND to_date('12-31--2012'); --字符串转日期
#数值处理函数
ABS() --绝对值
COS() --余弦
EXP() --指数值
PI() --圆周率
SIN() --正弦
SQRT() --平方根
TAN() --正切
#平均数AVG()
SELECT AVG(a) AS avg_a
FROM tableA
WHERE a='DLL01';
#计数
SELECT COUNT(*) AS num_cust --对所有行计数，不管是否NULL值
FROM tableA;
SELECT COUNT(a) AS num_a --指定了列名，会忽略NULL值
FROM tableA;
#最值
MAX()和MIN()，忽略NULL值
#总值SUM()，忽略NULL值
SELECT SUM(price*quantity) AS total_price
FROM tableA
WHERE order_code=20008
#以上聚集函数只包含不同值
SELECT AVG(DISTINCT order_price) AS avg_price
FROM tableA
WHERE id='DLL01';
两个子句：GROUP BY 和 HAVING
#GROUP BY创建分组HAVING过滤分组
SELECT vend_id， COUNT(*) AS num_prods
FROM tableA
WHERE price>=4
GROUP BY vend_id
HAVING COUNT(*)>=2;
ORDER BY price --GROUP BY在WHERE之后在ORDER BY之前
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
#子查询顺序为从内而外
SELECT order_num
FROM tableA
WHERE id='DLL01'；
(输出20007和20008)
SELECT id
FROM tableB
WHERE order_num IN（20007，20008）；
#合并为子查询。只能查询单个列
SELECT id
FROM tableB
WHERE order_num IN (SELECT order_num FROM tableA WHERE id='DLL01')
SELECT cust_name,
cust_state
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id=Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
#Orders.cust_id=Customers.cust_id完全限定列名，在两张表中名字相同列拿出来比较，防止歧义
㈨ SQL必知必会(游标)
第一步，定义游标。
第二步，打开游标。
第三步，从游标中取得数据。
第四步，关闭游标。
最后一步，释放游标。
你会发现执行 call calc_hp_max() 这一句的时候系统会提示 1329 错误，也就是在 LOOP 中当游标没有取到数据时会报的错误。
使用游标来解决一些常见的问题
㈩ SQL必知必会(查询优化器)
了解查询优化器的作用之前，我们先来看看一条 SQL 语句的执行都需要经历哪些环节，如下图所示：
你能看到一条 SQL 查询语句首先会经过分析器，进行语法分析和语义检查。我们之前讲过语法分析是检查 SQL 拼写和语法是否正确，语义检查是检查 SQL 中的访问对象是否存在。比如我们在写 SELECT 语句的时候，列名写错了，系统就会提示错误。语法检查和语义检查可以保证 SQL 语句没有错误，最终得到一棵语法分析树，然后经过查询优化器得到查询计划，最后交给执行器进行执行。
查询优化器的目标是找到执行 SQL 查询的最佳执行计划，执行计划就是查询树，它由一系列物理操作符组成，这些操作符按照一定的运算关液嫌系组成查询的执行计划。在查询优化器中，可以分为逻辑查询优化阶段和物理查询优化阶段。
逻辑查询优化就是通过改变 SQL 语句的内容来使得 SQL 查询更高效，同时为物理查询优化提供更多的候选执行计划。通常采用的方式是对 SQL 语句进行等价变换，对查询进行重写，而查询重写的数学基础就是关系代数。对条件表达式进行等价谓词重写、条件简化，对视图进行重写，对子查询进行优化档埋闹，对连接语义进行了外连接消除、嵌套连接消除等。
逻辑查询优化是基于关系代数进行的查询重写，而关系代数的每一步都对应着物理计算，这些物理计算往往存在多种算法，因此需要计算各种物理路径的代价，从中选择代价最小的作为执行计划。在这个阶段里，对于单表和多表连接的操作，需要高效地使用索引，提升查询效率。
在这两个阶段中，查询重写属于行罩代数级、语法级的优化，也就是属于逻辑范围内的优化，而基于代价的估算模型是从连接路径中选择代价最小的路径，属于物理层面的优化。
查询优化器的目的就是生成最佳的执行计划，而生成最佳执行计划的策略通常有以下两种方式。
但我们需要记住，SQL 是面向集合的语言，并没有指定执行的方式，因此在优化器中会存在各种组合的可能。我们需要通过优化器来制定数据表的扫描方式、连接方式以及连接顺序，从而得到最佳的 SQL 执行计划。
你能看出来，RBO 的方式更像是一个出租车老司机，凭借自己的经验来选择从 A 到 B 的路径。而 CBO 更像是手机导航，通过数据驱动，来选择最佳的执行路径。
大部分 RDBMS 都支持基于代价的优化器（CBO），CBO 随着版本的迭代也越来越成熟，但是 CBO 依然存在缺陷。通过对 CBO 工作原理的了解，我们可以知道 CBO 可能存在的不足有哪些，有助于让我们知道优化器是如何确定执行计划的。
首先，我们先来了解下 MySQL 中的 COST Model ， COST Model 就是优化器用来统计各种步骤的代价模型，在 5.7.10 版本之后，MySQL 会引入两张数据表，里面规定了各种步骤预估的代价（Cost Value） ，我们可以从 mysql.server_cost 和 mysql.engine_cost 这两张表中获得这些步骤的代价：
server_cost 数据表是在 server 层统计的代价，具体的参数含义如下：
由这张表中可以看到，如果想要创建临时表，尤其是在磁盘中创建相应的文件，代价还是很高的。
然后我们看下在存储引擎层都包括了哪些代价：
engine_cost 主要统计了页加载的代价，我们之前了解到，一个页的加载根据页所在位置的不同，读取的位置也不同，可以从磁盘 I/O 中获取，也可以从内存中读取。因此在 engine_cost 数据表中对这两个读取的代价进行了定义：
既然 MySQL 将这些代价参数以数据表的形式呈现给了我们，我们就可以根据实际情况去修改这些参数。因为随着硬件的提升，各种硬件的性能对比也可能发生变化，比如针对普通硬盘的情况，可以考虑适当增加 io_block_read_cost 的数值，这样就代表从磁盘上读取一页数据的成本变高了。当我们执行全表扫描的时候，相比于范围查询，成本也会增加很多。
比如我想将 io_block_read_cost 参数设置为 2.0，那么使用下面这条命令就可以：
我们对 mysql.engine_cost 中的 io_block_read_cost 参数进行了修改，然后使用 FLUSH OPTIMIZER_COSTS 更新内存，然后再查看 engine_cost 数据表，发现 io_block_read_cost 参数中的 cost_value 已经调整为 2.0。
如果我们想要专门针对某个存储引擎，比如 InnoDB 存储引擎设置 io_block_read_cost ，比如设置为 2，可以这样使用：
然后我们再查看一下 mysql.engine_cost 数据表：
从图中你能看到针对 InnoDB 存储引擎可以设置专门的 io_block_read_cost 参数值。
总代价的计算是一个比较复杂的过程，上面只是列出了一些常用的重要参数，我们可以根据情况对它们进行调整，也可以使用默认的系统参数值。
那么总的代价是如何进行计算的呢？
在论文 《Access Path Selection-in a Relational Database Management System》 中给出了计算模型，如下图所示：
你可以简单地认为，总的执行代价等于 I/O 代价 +CPU 代价。在这里 PAGE FETCH 就是 I/O 代价，也就是页面加载的代价，包括数据页和索引页加载的代价。W*(RSI CALLS) 就是 CPU 代价。W 在这里是个权重因子，表示了 CPU 到 I/O 之间转化的相关系数，RSI CALLS 代表了 CPU 的代价估算，包括了键比较（compare key）以及行估算（row evaluating）的代价。
为了让你更好地理解，我说下关于 W 和 RSI CALLS 的英文解释：W is an adjustable weight between I/O and CPU utilization. The number of RSI calls is used to approximate CPU utilization。
这样你应该能明白为了让 CPU 代价和 I/O 代价放到一起来统计，我们使用了转化的系数 W，
另外需要说明的是，在 MySQL5.7 版本之后，代价模型又进行了完善，不仅考虑到了 I/O 和 CPU 开销，还对内存计算和远程操作的代价进行了统计，也就是说总代价的计算公式演变成下面这样：
总代价 = I/O 代价 + CPU 代价 + 内存代价 + 远程代价
这里对内存代价和远程代价不进行讲解，我们只需要关注 I/O 代价和 CPU 代价即可。