DB2数据库使用经验漫谈（1）
dn001
DB2数据库使用 DML(Data Manipulation Language commands)数据操纵语言 DDL(Data Definition Language commands) 数据定义语言 TCC(Transaction Control commands) 事务控制语言 SCC(System Control commands ) 系统控制语言 一 DML数据操纵语言 （一）数据查询命令 select <查询内容> From <表名> where <条件> /*in,between,like%或_*/ group by<分组内容> having<组内条件> order by<排序内容>[asc/desc]; （二）数据更新命令 1、数据插入命令(1).具体的值插入表中
Insert into <表名> [(列名表)]
values<值表1>，<值表2>，<值表2>...
（注： 日期，字符型值加引号）
(2).将其它表满足条件的数据插入到一个表中
Insert into <表名> [<列表名>] <select 子句> 2、数据修改命令 Update <表名> set <列名1>=<表达式1>,<列名2>=<表达式2>...[where<条件>]; Update <表名> set <列名1>=(< select 子句 >) [where<条件>]; 3、数据删除命令 Delete from <表名> [where<条件>]; 二 DDL数据定义语言 （一）、基本数据类型 1.字符串 字符串为一个字节序列,字符串的长度为序列中的字节数。假如长度为零，则该字符串的值称为空字符串。 CHAR(x) 是固定长度字符串。(1= VARCHAR(x) 可变长度字符 x<=4000,x>254不能用group by ,order by,distinct 和除
12345678下一页
union all以外的任何设置操作。 GRAPHIC(x) 是固定图形字符串。(1= BLOB 二进制字符串,是一个字节序列,用于保存非传统数据，如图象、图形、声音等数据。 2 数字 ：所有数字都有符号和精度。精度是除开符号的位数或数字数。 SMALLINT 小整数，是精度为5位的两字节整数。 INTEGER 大整数，是精度为10位的四字节整数。 REAL 单精度浮点数，是实数的32位近似值。 DOUBLE 双精度浮点数，是实数的64位近似值，DOUBLE也称FLOAT。 DECIMAL(p,s) DECIMAL是一个十进制数。小数点的位置由数字的 精度(p)和小数位(s) 确定。精度是数字的总位数，必须小于32。小数位是小数部分数字的位数且总是小于或等于精度值。假如未指定精度和小数位，则十进制值的缺省精度为5，缺省小数位为0。 3 日期时间值 ：日期时间值是日期、时间以及时间戳记的表示，日期时间值可以用于某些算术运算和字符串运算并且与某些字符串是相容的。 DATE 由三个部分构成（年、月以及日）。 TIME 使用24小时制,分为三个部分（小时、分钟以及秒）。 IMESTAMP 分为七个部分（年、月、日、小时、分钟、秒以及微秒）。 4空值 空值是一个区别于所有非空值的非凡值。它意味着行中的那一列无任何其 它值。所有数据类型都存在空值。 （二）、数据定义 1、Create (创建) 创建表: Create table [<模式名>.]<表名> (<列名1> <类型> [Null|Not null] [,<列 名2> <类型>...] 创建视图: Create view [<模式名>.]<视图名> [<列名表>] as select 语句
上一页12345678下一页
创建别名: Create alias [<模式名>.]别名 for [<模式名>.]表名/视图名/别名 创建索引: Create [unique] index <索引名> /*I_表名_字段名*/ on <表名>(<列名 >[asc|desc] 创建模式: Create schema 模式名 authorization 权限名 2、Drop (摧毁) 摧毁表: drop table [<模式名>.]表名 摧毁视图: drop view [<模式名>.]视图名 摧毁别名: drop alias [<模式名>.]别名 摧毁触发器: drop trigger [<模式名>.]触发器名 摧毁索引: drop index [<模式名>.]<索引名> 摧毁包: drop package [<模式名>.]包名 3、Alter (变更) 增加表列: Alter table [<模式名>.]<表名> add column [<列名1> <类型> [Null|Not null]]... 增加约束: Alter table [<模式名>.]<表名> add constraint 列名 CHECK (约束) 删除约束: Alter table [<模式名>.]<表名> drop constraint 约束名 修改列类型: Alter table [<模式名>.]<表名> alter column 列名 set data type <类型> 4、Grant (赋权) 对[public/用户/组] 赋于在表上的[all/select/insert/update/delete] 权限: Grant [all/select/insert/update/delete] on [<模式名>.]表名to [public/用户/ 组]; 对[public/用户/组] 赋于在包上的[bind/execute/]权限: Grant [bind/execute/] on package [<模式名>.][包名] to [public/用户/组];
上一页12345678下一页
对[public/用户/组] 赋于在索引上的[control]权限: Grant control on index [<模式名>.]索引名 to [public/用户/组] 5、Revoke (回收) 从public/用户/组] 回收在表上的[all/select/insert/update/delete] 权限: Revoke [all/select/insert/update/delete] on [表名] from [public/用户/组]; 从public/用户/组] 回收在包上的[bind/execute/] 权限: Revoke [bind/execute/] on package [<模式名>.][包名]from [public/用户/组]; 三 事务控制语言 1．事务提交命令: Commit; 2．事务回退命令: Rollback; 四 系统控制语言 1．取消自动提交： Update command options using c off； 2．连接数据库： Connect to 数据库名 user 用户 using 密码 3．断开数据库连接： Connect reset Disconnect 数据库名 4．列出数据库中的所有表： List tables for all 5．列出数据库中的模式名为schema_name的所有表： List tables for schema schema_name 6．查看表结构 Describe table 模式名.表名 Describe select * from 模式名.表名 7．查看表的索引 Describe indexes for table 模式名.表名 五 函数 （一） 列函数 列函数对列中的一组值进行运算以得到单个结果值。 1．AVG 返回某一组中的值除以该组中值的个数的和 2．COUNT (*) 返回非空列值的行数。 3．MAX 返回一组值中的最大值 4．MIN 返回一组值中的最小值 5． MOD 求余 （二） 标量函数
上一页12345678下一页
标量函数对值进行某个运算以返回另一个值。下列就是一些由DB2通用数据库提供的标量函数的示例。 1．ABS 返回数的绝对值 2．HEX 返回值的十六进制表示 3．LENGTH 返回自变量中的字节数（对于图形字符串则返回双字节字符数。） 4．YEAR 抽取日期时间值的年份部分 5．NULLIF(a,b) 假如a=b则值为空，否则值为a 6．COALESCE(a,b,c) :返回第一个具有非空值的参数的值 7．UCASE(str) 小写字符转换成大写字符 8．ICASE(str) 大写字符转换成小写字符 9．LOCAT(str1,str2,n) 返回从第n个字符起，在str1中str2第一次出现的位置 10．SUBSTR(str,m,n) 返回从第m个字符起,，在str中的n个字符串 六 嵌入式SQL(SQLJ) 将SQL语句嵌入应用程序时，必须按以下步骤预编译应用程序并将其与数据库联编： 1.创建源文件，以包含带嵌入式 SQL 语句的程序 格式: # SQL{ SQL语句 } 。 2.连接数据库，然后预编译每个源文件。 语法: SQLJ 源文件名 例：import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
#sql iterator App_Cursor1 (String empno, String firstnme) ;
#sql iterator App_Cursor2 (String) ;
class App
{
static
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
}
catch (Exception e)
{
e.printStackTrace();
}
}
public static void main(String argv[])
{
try
{
App_Cursor1 cursor1;
App_Cursor1 cursor2;
String str1 = null;
String str2 = null;
int count1;
Connection con = null;
String url = "jdbc:odbc:tese2";
DefaultContext ctx = DefaultContext.getDefaultContext();
if (ctx == null) {
try {
if (argv.length == 0) {
String userid ="tdl";
String passwd ="user";
con = DriverManager.getConnection(url, userid, passwd);
}
else if (argv.length == 2) {
// connect with default id/password
con = DriverManager.getConnection(url);
}
else {
System.out.println("
Usage: java App [username password]
");
System.exit(0);
}
con.setAutoCommit(false);
ctx = new DefaultContext(con);
}
catch (SQLException e) {
System.out.println("Error: could not get a default context");
System.err.println(e) ;
System.exit(1);
}
DefaultContext.setDefaultContext(ctx);
}
#sql cursor1 = { SELECT empno, firstnme from db2admin.employee };
System.out.println("Received results:");
while (cursor1.next()) {
str1 = cursor1.empno();
str2 = cursor1.firstnme();
System.out.print (" empno= " + str1);
System.out.print (" firstname= " + str2);
System.out.print ("
");
}
cursor1.close();
#sql cursor2 = { SELECT firstnme from db2admin.employee where empno = :str1 };
System.out.println("Received results:");
while (true) {
#sql { FETCH :cursor2 INTO :str2 };
if (cursor2.endFetch()) break;
System.out.print (" empno= " + str1);
System.out.print (" firstname= " + str2);
System.out.print ("
");
}
cursor2.close();
// rollback the update
System.out.println("
Rollback the update...");
#sql { ROLLBACK work };
System.out.println("Rollback done.");
}
catch( Exception e )
{
e.printStackTrace();
}
}
} 注：本程序采用JDBCODBC桥的方式访问数据库，必须配置ODBC数据源。
上一页12345678下一页
七 触发器 建一个触发器，应包含以下部分： 触发器名字 触发器触发事件: insert,delete,update 激活时间: before,after 粒度: for each statement,for each row 过渡变量: old row:表示触发事件之前被修改的值： new row表示触发事件之后被修改的值 old table表示触发事件之前全部被修改行的一个只读假想表 new table表示触发事件之后全部被修改行的一个假想表 触发条件: 由WHEN开始，可包含一个或多个谓词，可包含过渡变量和子查询 触发体: 由一个或多个SQL语句组成 例:CREATE TRIGGER REORDER
AFTER UPDATE OF ON_HAND, MAX_STOCKED ON PARTS
REFERENCING NEW AS N_ROW
FOR EACH ROW MODE DB2SQL
WHEN (N_ROW.ON_HAND < 0.10 * N_ROW.MAX_STOCKED
AND N_ROW.ORDER_PENDING = 'N')
BEGIN ATOMIC
VALUES(ISSUE_SHIP_REQUEST(N_ROW.MAX_STOCKED -
N_ROW.ON_HAND,
N_ROW.PARTNO));
UPDATE PARTS SET PARTS.ORDER_PENDING = 'Y'
WHERE PARTS.PARTNO = N_ROW.PARTNO;
END 八 存储过程 存储过程主要通过Stored Procedure Builder来建立, (一)对存储过程的调用分三部分: 1.连接(与数据库建立连接) Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance(); Connection con=DriverManager.getConnection(url,user,password);
上一页12345678下一页
2。注册输出参数 cs.registerOutParameter (3, Types.INTEGER); 3。调用存储过程: CallableStatement cs=con.prepareCall("{call store_name(参数,参数,参数)}"); (二)调用举例:import java.net.URL;
import java.sql.*;
class test2
{
public static void main(String args[])
{
String url = "jdbc:db2://wellhope/sample";
String user="db2admin";
String password="db2admin";
try
{
Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance();
//与数据库建立连接
Connection con=DriverManager.getConnection(url,user,password);
checkForWarning(con.getWarnings());
DatabaseMetaData dma=con.getMetaData();
String str="This is a string";
//int hashcode=str.hashCode();
//System.out.println("Hashcode "+hashcode);
//创建Statement对象,用于执行SQL语句
Statement stmt=con.createStatement();
//创建CallableStatement对象,用于执行存储过程
CallableStatement cs=con.prepareCall("{call PRO_YHDL1(?,?,?)}");
//注册输出参数
cs.registerOutParameter (3, Types.INTEGER);
int result = 0;
cs.setString(1,"123");
cs.setString(2,"123");
cs.execute();
result = cs.getInt (3);
dispResultSet(result);
cs.close();
con.close();
}
catch(SQLException ex)
{
System.out.println("
* * * SQLException caught * * *
");
while(ex!=null)
{
System.out.println("SQLState: "+ex.getSQLState());
System.out.println("Message: "+ex.getMessage());
System.out.println("Vendor: "+ex.getErrorCode());
ex=ex.getNextException();
System.out.println("");
}
}
catch(java.lang.Exception ex)
{
ex.printStackTrace();
}
} (三)存储过程举例:
上一页12345678下一页
Pro_yhdl1是一个存储过程，它的功能是从数据库表YHDL中取出PWD：import java.sql.*;
public class Pro_yhdl1
{
public static void pro_yhdl1 ( String m_id,
String m_pwd,
int[] result ) throws SQLException, Exception
{
// Get connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement stmt = null;
ResultSet rs = null;
String sql;
String m_password="";
sql = "SELECT"
+ " DB2ADMIN.YHDL.PWD"
+ " FROM"
+ " DB2ADMIN.YHDL"
+ " WHERE"
+ " ("
+ " ( "
+ " DB2ADMIN.YHDL.ID = '"+m_id.trim()+"'"
+ " )"
+ " )";
stmt = con.prepareStatement( sql );
rs = stmt.executeQuery();
// Access query results
while (rs.next())
{
m_password=rs.getString(1);
m_password=m_password.trim();
if (rs.wasNull())
System.out.print("NULL");
else
System.out.print(m_password);
}
if(m_password.equals(m_pwd.trim()))
{
result[0] =1;
}
else
{
result[0] =0;
}
// close open resources
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (con != null) con.close();
// set return parameter
//result[0] = result[0];
}
} 九 JAVA数据库链接(JDBC) DB2 的 Java 支持包括 JDBC，一个以供给商为中心的动态 SQL 接口,它通过标准的 Java方法提供对应用程序的数据存取。JDBC 与 DB2 CLI 相似之处在于您不必预编译或联编 JDBC 程序。使用 JDBC 编写的应用程序只使用动态 SQL。 JDBC编程步骤： 1建立与数据库的连接： Class.forName("Com.ibm.db2.jdbc.net.DB2Driver"); connection con=DriverManager.getConnection(url); 2.创建Statement对象： Statement stmt=con.createStatement(); 3执行查询语句： ResultSet rs=stmt.execQuery("SQL语句"); 4.获取和设置选项: ResultSetMetaData rsmd=rs.getMetaData(); int numCols=rsmd.getColumnCount()获取结果集总列数; rsmd.getColumnLabel(i))获取记录值; setMaxRows :设置结果集能容纳的最多行数. setQueryTimeout:设置一个语句执行等待的时间. setEscapeProcessing:通知驱动程序如何处理转义字符. 5.关闭Statement stmt.clost(); 十 调用层接口(CLI) CLI不是一种新的查询语言,它只不过是应用程序可利用SQL语句去提交事务处理的一种简单接口,对数据库的查询和修改,仍要使用SQL语言编写,包括CLI函数的调用。 调用层接口(CLI)为DB2所提供的处理动态SQL语句的两种机制之一,即在应用程序首次运行时,动态SQL语句提交给数据库系统,CLI依靠一组函数调用,可嵌入主语言中。
上一页12345678
