SELECT CUSTNO,SUM(CURRMONTHAMASS)/365 FROM
(SELECT CUSTNO,CURRMONTHAMASS FROM MONTH_HQ_ACCT_INFO WHERE MINORGID='856270' AND CURRMONTHAMASS>0 AND MONTHID BETWEEN '201010' AND '201110'
UNION ALL
SELECT CUSTNO,CURRMONTHAMASS FROM MONTH_FIX_ACCT_INFO WHERE MINORGID='856270' AND CURRMONTHAMASS>0 AND MONTHID BETWEEN '201010' AND '201110')A
GROUP BY CUSTNO HAVING SUM(CURRMONTHAMASS)/365>=20000
DISTINCT
SELECT DISTINCT 列名称 FROM 表名称
查属性列种类
排序,
ORDER BY
db2 select * from table where id='***' order by name
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC
UPDATE
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
DELETE
DELETE FROM 表名称 WHERE 列名称 = 值
DELETE FROM Person WHERE LastName = 'Wilson'
top (非DB2命令)
top 2 前2条记录
SELECT TOP 2 * FROM Persons
like
SELECT * FROM Persons WHERE City LIKE 'N%'
SELECT * FROM Persons WHERE City LIKE '%g'
SELECT * FROM Persons WHERE City LIKE '%lon%'
SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
SELECT * FROM Persons WHERE City LIKE '[ALN]%' 任何单一字eg:A L N
IN (非DB2命令)
IN 操作符允许我们在 WHERE 子句中规定多个值
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')
BETWEEN
如需以字母顺序显示介于 "Adams"(包括)和 "Carter"(不包括)之间的人
SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
AS
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'
不使用
SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName
FROM Persons, Product_Orders
WHERE Persons.LastName='Adams' AND Persons.FirstName='John'
JOIN (INNER JOIN 。。。ON)注释:INNER JOIN 与 JOIN 是相同的
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
LEFT JOIN
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
RIGHT JOIN
RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FULL JOIN
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name
UNIQUE
UNIQUE 约束唯一标识数据库表中的每条记录。(在表创建时进行
db2常用命令大全_IT/计算机_专业资料 暂无评价0人阅读0次下载举报文档 db2常用命令大全_IT/计算机_专业资料。db2常用命令大全今日推荐 ...
DB2命令大全_计算机软件及应用_IT/计算机_专业资料。DB2命令大全1. 建立数据库 DB2_GCB CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB USING CODESET GBK ...
DB2命令大全 1DB2命令大全 1.1查看表空间 db2 list tablespaces show detail 1.2查看数据库的表死锁 方法一: 打开监控 db2 update monitor switches using lock...
DB2 命令大全 连接数据库: connect to [数据库名] user [操作用户名] using [密码] 创建缓冲池(8K): create bufferpool ibmdefault8k IMMEDIATE 创建缓冲池(16K...
db2命令大全_经济学_高等教育_教育专区。db2命令语句使用大全Db2 命令练习 1 复制表 insert into dr_ryqx_em select * from dr_ryqx_em; 2 列出所有正在...
DB2常用命令大全列表_IT/计算机_专业资料。登陆电子对帐数据库22.5.233.142 db2 connect to ebill user ebank using ebank 登陆公司网银数据库22.5.233.142 ...
db2命令大全_IT/计算机_专业资料。DB2命令大全今日推荐 160份文档 四季养生 中医养生与保健 中医养生知识大全 女人养生之道89份文档 ...
DB2 命令大全 连接数据库: connect to [数据库名] user [操作用户名] using [密码] 创建缓冲池(8K): create bufferpool ibmdefault8k IMMEDIATE SIZE 5000 PAGE...
DB2常用操作命令大全_IT/计算机_专业资料。个人整理的DB2命令操作小结。Talking about db2常用命令与导入导出 Quote db2常用命令与导入导出 db2命令介绍: 相比图形...
DB2命令大全DB2命令大全隐藏>> db2 常用命令大全 EXPORT TO D:\PRINTXML.IXF OF IXF 近一年来在项目开发中使用到了 IBM 的 DB2 9.1 的数据库产品, ...
热门文档