工作需要,数据库sql语句及使用示例
分类:数据库

转自: http://www.maomao365.com/?p=5277
摘要:
下文主要讲述,如何对"已上线的系统"中的表,增加新的字段。

db2 系统表信息 

利用 Transact-SQL 语言创建满足以下要求的数据库:

1) 数据库存在于连接 MySQL 中;
2) 数据库名称为 mydb;
3) 字符集选择 utf8 -- UTF-8 Unicode;
4) 排序规则选择 utf8_general_ci;

具体步骤如下:
点击“新建查询”,在查询编辑器输入以下代码,点击“运行”,完成后, 左侧列表
中选中连接“数 MySQL”,点击“刷新”按钮,列表即可见到“testdb”。

CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE
utf8_general_ci;

collate在sql中是用来定义排序规则的。排序规则其实就是当比较两个字符串时,根据某种规则来确定哪个比较大,是否相等。各个数据库支持不同的排序规则。

utf8_bin将字符串中的每一个字符用二进制数据存储,区分大小写。

utf8_genera_ci不区分大小写,ci为case insensitive的缩写,即大小写不敏感。

utf8_general_cs区分大小写,cs为case sensitive的缩写,即大小写敏感。

前段时间做一个项目,其中涉及到报表部分编写了大量复杂的SQL,比如其中的一个存储过程就有700多行。项目上线过后,进入维护阶段,发现大量的SQL很难维护。于是总结点经验教训:

access与sql语法上有哪些区别

 假设表game有一字段为gameYuiJian为bit字段(SQL SERVER 20005)和"是/否"字段(ACCSS数据库),在编写脚本文件时,如下才能正确执行

  SQL strCmd = "Select Top 14 * From game Where gameTuiJian = 1 Order By Id Desc";

  OleDb strCmd = "Select Top 14 * From game Where gameTuiJian = true Order By Id Desc";

  在时间的处理:

  sql server 语句"Select * From tt1 Where time1>'2009-12-01' "

  Access 语句" Select * From tt1 Where time1>#2009-12-01#"也可以用上面语句

  Sql Server 语句"Select * From tt1 Where DateDiff(day,time1,getdate())

  Access语句"Select *澳门新萄京, From tt1 Where DateDiff('d',time1,now)

  Access与SQL SERVER数据库转换

  由于SQL2000里面没有"自动编号",所以你的以"自动编号"设置的字段都会变成非空的字段,这就必须手工修改这些字段,并把他的"标示"选择"是",种子为"1",增量为"1",

  2,另外,ACCESS2000转换成SQL2000后,原来属性为"是/否"的字段将被转换成非空的"bit",这时候你必须修改成自己想要的属性了;

  ACCESS转SQL SERVER中的一些经验

  1.ACCESS的数据库中的自动编号类型在转化时,sql server并没有将它设为自动编号型,我们需在SQL创建语句中加上identity,表示自动编号! 2.转化时,跟日期有关的字段,SQL SERVER默认为smalldatetime型,我们最好将它变为datetime型,因为datetime型的范围比smalldatetime型大。我遇见这种情况,用smalldatetime型时,转化失败,而用datetime型时,转化成功。

  3.对此两种数据库进行操作的sql语句不全相同,例如:在对ACCESS数据库进行删除纪录时用:"delete * from user where id=10",而对SQL SERVER数据库进行删除是用:"delete user where id=10".

  4.日期函数不相同,在对ACCESS数据库处理中,可用date()、time()等函数,但对

  SQL SERVER数据库处理中,只能用datediff,dateadd等函数,而不能用date()、time()等函数。

  5.在对ACCESS数据库处理中,sql语句中直接可以用一些VB的函数,像cstr()函数,而对SQL SERVER数据库处理中,却不能用。

  1、必须先安装Microsoft Office Access 2003,和SQL Server2000。2、把旧的动网数据库备份,备份完成后,用Access 2003打开动网旧数据库,在打开时会出现一个警告,不要理会它(安全警告),按打开键,打开后按工具栏--数据库实用工具--转换数据库--转换为2002-2003格式,把数据库转换成2003格式。

  2、转换完成后再用Access 2003打开,打开后按工具栏--数据库实用工具--升迁向导--新建数据库--填写SQL数据库登陆名称、密码和要新建的动网数据库(准备转成新的动网数据库),按下一步,按" 》"键,再按下一步,选取所有选项,再按下一步,选择"不对应用程序作任何改动",再按完成。

  3、打开SQL企业管理器--数据库 吹礁詹判陆ǖ亩 菘饬税桑 慊髡飧鍪 菘庖幌拢 缓笤诠ぞ呃浮 ?/FONT>SQL脚本--常规--全部显示--编写全部对象脚本--确定(记住存放的位置)。

  4、用记事本打开刚才生成的SQL脚本,在编辑栏--替换--查找内容为"smalldatetime"替换为"datetime"--替换全部;完成后再在编辑栏--替换--查找内容为"nvarchar"替换为"varcha"--替换全部,完成后保存退出。

  5、打开SQL企业管理器--数据库--点击这个数据库一下新建的动网数据库,然后在工具栏--SQL查询分析器--文件--打开--"刚才生成的SQL脚本"--查询--执行,然后关闭窗口。

  6、再回到SQL企业管理器--数据库--点击这个数据库一下新建的动网数据库,然后打开工具栏--数据库转换服务--导入数据--下一步--数据源"Microsoft Access"文件名"为旧的动网数据库"--下一步--再下一步--从源数据复制表和视图--下一步--全选--下一步--立即运行--下一步--完成。

  7、修改动网文件夹两个文件conn.asp和incconst.asp。

  SQL是Structured Quevy Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集,是一种功能齐全的数据库语言。在使用它时,只需要发出"做什么"的命令,"怎么做"是不用使用者考虑的。SQL功能强大、简单易学、使用方便,已经成为了数据库操作的基础,并且现在几乎所有的数据库均支持SQL。 <br>

  ##1 二、SQL数据库数据体系结构 <br>

  SQL数据库的数据体系结构基本上是三级结构,但使用术语与传统关系模型术语不同。在SQL中,关系模式(模式)称为"基本表"(base table);存储模式(内模式)称为"存储文件"(stored file);子模式(外模式)称为"视图"(view);元组称为"行"(row);属性称为"列"(column)。名称对称如^00100009a^: <br>

  ##1 三、SQL语言的组成 <br>

  在正式学习SQL语言之前,首先让我们对SQL语言有一个基本认识,介绍一下SQL语言的组成: <br>

  1.一个SQL数据库是表(Table)的集合,它由一个或多个SQL模式定义。 <br>

  2.一个SQL表由行集构成,一行是列的序列(集合),每列与行对应一个数据项。 <br>

  3.一个表或者是一个基本表或者是一个视图。基本表是实际存储在数据库的表,而视图是由若干基本表或其他视图构成的表的定义。 <br>

  4.一个基本表可以跨一个或多个存储文件,一个存储文件也可存放一个或多个基本表。每个存储文件与外部存储上一个物理文件对应。 <br>

  5.用户可以用SQL语句对视图和基本表进行查询等操作。在用户角度来看,视图和基本表是一样的,没有区别,都是关系(表格)。 <br>

  6.SQL用户可以是应用程序,也可以是终端用户。SQL语句可嵌入在宿主语言的程序中使用,宿主语言有FORTRAN,COBOL,PASCAL,PL/I,C和Ada语言等。SQL用户也能作为独立的用户接口,供交互环境下的终端用户使用。 <br>

  ##1 四、对数据库进行操作 <br>

  SQL包括了所有对数据库的操作,主要是由4个部分组成: <br>

  1.数据定义:这一部分又称为"SQL DDL",定义数据库的逻辑结构,包括定义数据库、基本表、视图和索引4部分。 <br>

  2.数据操纵:这一部分又称为"SQL DML",其中包括数据查询和数据更新两大类操作,其中数据更新又包括插入、删除和更新三种操作。 <br>

  3.数据控制:对用户访问数据的控制有基本表和视图的授权、完整性规则的描述,事务控制语句等。 <br>

  4.嵌入式SQL语言的使用规定:规定SQL语句在宿主语言的程序中使用的规则。 <br>

  下面我们将分别介绍: <br>

  ##2 (一)数据定义 <br>

  SQL数据定义功能包括定义数据库、基本表、索引和视图。 <br>

  首先,让我们了解一下SQL所提供的基本数据类型:(如^00100009b^) <br>

  1.数据库的建立与删除 <br>

  (1)建立数据库:数据库是一个包括了多个基本表的数据集,其语句格式为: <br>

  CREATE DATABASE <数据库名> 〔其它参数〕 <br>

  其中,<数据库名>在系统中必须是唯一的,不能重复,不然将导致数据存取失误。〔其它参数〕因具体数据库实现系统不同而异。 <br>

  例:要建立项目管理数据库(xmmanage),其语句应为: <br>

  CREATE DATABASE xmmanage <br>

  (2) 数据库的删除:将数据库及其全部内容从系统中删除。 <br>

  其语句格式为:DROP DATABASE <数据库名> <br>

  例:删除项目管理数据库(xmmanage),其语句应为: <br>

  DROP DATABASE xmmanage <br>

  2.基本表的定义及变更 <br>

  本身独立存在的表称为基本表,在SQL语言中一个关系唯一对应一个基本表。基本表的定义指建立基本关系模式,而变更则是指对数据库中已存在的基本表进行删除与修改。 <br>

INSERT   INTO   mobile   SELECT   mobileID, ' ' ' mobilephone     FROM   OPENROWSET( 'Microsoft.Jet.OLEDB.4.0 ', 'Excel   8.0;Database=D:Mobile.xls ',   'SELECT   *   FROM   [Sheet1$] ')

假设表game有一字段为gameYuiJian为bit字段(SQL SERVER 20005)和是/否字段(ACCSS数据库),在编写脚本文件时,如下才能...


2011-05-25 10:00:18|  分类: DB2 |字号 订阅

利用 Transact-SQL 语言查看数据库及表的信息

1.查看连接中的所有数据库
步骤:点击“新建查询”,输入

SHOW DATABASES 

2.查看数据库 mysql 中所有的表
步骤:点击“新建查询”,输入

USE mysql;
SHOW TABLES;

3.查看数据库“mysql”中表“help_keyword”的结构。
步骤:点击“新建查询”,输入

USE mysql;
DESC help_keyword;
或者DESCRIBE help_keyword;
或者SHOW COLUMNS FROM help_keyword;

4.查看数据库表中的内容

USE mysql;
SELECT * FROM help_keyword;

设计

一、数据库命名遵循一些通用规范。

数据库命名规范是个基本的命名标准,每个团队都有自己的命名规范,我们做项目中以全大写下划线分割作为标准。表名或字段名要准确表达其业务含义。以DATE结尾的数据类型都是date类型,以TIME结尾的数据类型是datetime类型。以IS开头的都是bool类型。

二、大数据对象列应该独立成表。

比如员工照片是一个blob对象,按照范式来说,这个字段完全可以放在Employee表中,但是出于性能的考虑,最好单独出一张EmployeePhoto表,与Employee是一对一的关系,这样使用ORM的时候,平时取Employee对象就不会取到照片,只有需要显示照片时才取EmployeePhoto对象。

三、数据库字段尽量不要为null。

一个字段允许为空,那么在SQL查询时就需要进行一些特殊处理,比如在WHERE条件中用上 t1.COLUMN1 IS NULL或者在SELECT时用上ISNULL()函数。而在ORM时,对应的对象的数据类型如果是不允许为空的,还必须加上?表示允许为空。在编程时也要进行判断该值是否为空。一不小心就容易漏掉空的判断,造成计算结果不正确。所以在数据库设计时,尽量将每个字段设计为not null。

四、带小数的字段使用Decimal数据类型而不要使用Float数据类型。

因为Float类型是用于表示浮点数据的近似数据类型,所以存储后可能会造成一点误差,如果在程序中传入2.4进行保存,可能读取到的值却是2.4000001或者2.399999999。

五、使用配置表来存储可能经常变化的配置项,而不是写死在代码中。

在编写查询语句,写存储过程或者出报表时,经常会对某些字段进行过滤。比如ProjectAssignment表中有个RoleCode字段,表示在往项目上分配人时,该人的角色。在查询时经常会把角色A、B、C放在一起作为管理层角色,那么在关于管理层分配的各种报表中,就充斥着where pa.ROLE_CODE in ('A','B','C')这样的条件。但是有一天,用户说现在角色D也算是管理层角色了,那么之前做的所有报表,都要将这段代码进行修改。

所以对于这种可能修改的查询条件,那么最好是建立一个配置表,然后所有查询都是从这个配置表中读取数据进行查询。那么前面是SQL可以改为:

where pa.ROLE_CODE in (select CODE from CONFIG where CODE_TYPE='Management')

虽然这样要牺牲一点点的性能,但是由于本身配置表数据量不会很大,而且可以以CODE_TYPE建立聚集索引,那么性能不是很大的问题。

六、不要使用ORM工具通过对象模型生成数据库。

数据库的创建和修改都应该以脚本来完成,而每个字段的数据类型、长度、表的各种约束(主键约束、外键约束、唯一约束、非空约束、CHECK约束等)、索引都应该是需要根据实际需求进行设计的,而使用ORM工具通过对象模型只能生成一个大概的表和列,无法生成准确的Schema。推荐使用专业的数据库建模工具PowerDesigner或者ERWin进行数据库建模,然后生成数据库脚本。

 

系统部署脚本,增加列的方法:
在系统脚本发布中,如何是存储过程 自定义函数 视图的修改和新增,我们通常采用以下步骤来编写此类脚本
1 判断对象(存储过程 自定义函数 视图)是否存在,如何存在我们就删除对象(存储过程 自定义函数 视图)
2 新建对象
<hr />
但是增加系统字段(列)时,我们不能采用删除表,然后重新的方式进行脚本处理,
那么我们通常采用 先判断列是否存在,如果不存在就创建列,如果存在就修改列属性<span style="color:red;font-weight:bold;">(修改列属性--请注意是否会影响历史数据)</span>
例:

利用 Transact-SQL 语言修改数据库 mydb 的字符集

步骤:点击“新建查询”,输入以下代码,点击运行

ALTER DATABASE mydb DEFAULT CHARACTER SET = latin1;

开发

一、使用有意义的表别名。

在进行查询时经常会JOIN很多表,那么就经常用到表别名,表别名使得SQL开发更简单,查看起来也更简洁。表别名一般就1个字母,或者2个字母,采用表的单词首字母作为别名即可。

select 'ProjectAssignAuth', p.PROJECT_ID , 0 , 0 , gs.EMPLOYEE_ID,'STAFF' 
from GROUP_STAFF gs 
join GROUP_PROJECT gp on gs.GROUP_ID = gp.GROUP_ID 
join PROJECT p on gp.PROJECT_ID = p.PROJECT_ID

二、SQL语句中应该写上详细注释。

这个算是老生常谈了,SQL也是一种语言,对于复杂的逻辑,一不小心存储过程就写出了几百行,如果没有注释,那么看一个几百行的SQL那真是无比痛苦的事情,即使这个SQL是自己写的,那么一个月以后,没有注释连自己都看不懂自己在写什么。

三、使用print打印出过程信息。

在编写复杂的存储过程时,不可避免的就是要调试存储过程的正确性,虽然SQL Server支持调试SQL语句的功能,但是在对于几百行的SQL来说,还是很麻烦的。所以在编写SQL时加入print过程信息的功能,这个相当于写程序时的Debug.WriteLine(),打印的信息对外部程序并没有影响,只是在SSMS调用存储过程时能够打印一些有用的信息。

四、增加调试参数帮助输出更多的调试信息。

在编写C#代码的时候,我们在VS中可以设置Debug或者Release模式,同样我们可以在存储过程中增加一个带有默认值的参数,比如我们有一个计算项目金额的存储过程,计算逻辑复杂,我们可以增加一个@debug参数,默认情况下是关闭的,输入一些调试信息。

create proc PROC_CALC_PROJECT_AMOUNT 
@pid int,--项目ID 
@debug bit=0 
as

…SQL

if(@debug=1) 
begin 
--输出一下调试信息

end

这样我们平时调用时只传入一个参数,在SSMS中想打开调试信息时,只需要增加第二个参数1即可:

exec PROC_CALC_PROJECT_AMOUNT 100,1

五、尽量避免在WHERE条件中对字段使用函数。

这个是编程人员容易犯的错误。因为对字段使用函数后将无法使用到字段中的索引,降低了执行效率。比如查询所有2012年新建的项目,那么应该写成:

select *
from Project p
where p.CreateDate between ‘2012-1-1’ and ‘2012-12-31’;

而不要写成

select *
from Project p
where Year(p.CreateDate)=2013;

 

六、使用视图来抽象公共的查询部分。

在设计中提到使用配置表来把一些可能变化的查询条件放在数据库中,这样在需求更改时只修改数据库中的配置,而不用一个一个的改存储过程和SQL语句。另外还有一种方法就是使用视图来抽取公共查询的部分,将一些逻辑和条件放在视图中,然后其他存储过程和SQL直接使用视图,在需求发生变化时,我们只需要修改视图,其他的存储过程和SQL都不用修改。

 

 

 

七、小心查询时数据类型不匹配隐式转换导致的性能问题。

对于数据库中每个字段的类型不一定完全和其存储的值匹配。比如我们在设计员工表的员工号字段时,考虑到员工号不一定是个整数,所以设计成了varchar(10),但是在实际应用中所有员工号都是5位数的整数,那么我们可能在写查询时可能就直接把int类型的员工号传入进行查询。

 

八、公用表表达式CTE、临时表和表变量的使用。

CTE 可用于:

  • 创建递归查询。这个在树结构查询中常用。

  • 在不需要常规使用视图时替换视图,也就是说,不必将定义存储在元数据中。

  • 启用按从标量嵌套 select 语句派生的列进行分组,或者按不确定性函数或有外部访问的函数进行分组。

  • 在同一语句中多次引用生成的表。

临时表分为局部临时表#开头和全局临时表##开头。临时表可以建立索引,对于大数据量的临时存储时就使用临时表。

表变量适用于存储数据量不大的临时数据。表变量不可用创建索引。

IF NOT EXISTS(SELECT * FROM SYS.COLUMNS 
WHERE OBJECT_ID = OBJECT_ID(N'表名') 
AND NAME = '列名') ---判断列是否存在
begin
---增加列
alter table [表名] 
add column [列名] [列类型]
end
else
begin
---修改列属性
alter table [表名] 
add column [列名] [列类型]
end

DB2 CLP 简介

利用 Transact-SQL 语言删除数据库 testdb

DROP DATABASE testdb

运维

一、数据库操作必须脚本化并进行版本控制。

所有数据库的操作,包括前期的建表、初始化数据、建索引后期的增量修改和数据维护,都必须以SQL脚本来执行。这些脚本都保存到源代码管理中。这样方便于测试和部署。

二、数据库脚本应该能够重复执行。

在创建或者修改数据库对象时,先判断现有数据库中是否已经有这个对象,有的话就不再创建或者改为更新对象或者将原对象删除,重新创建。这样脚本可以重复执行,避免了环境不一致导致脚本在这个环境可以正常运行,在另外一个数据库却报错的情况。

三、在修改或删除数据时,先把原有的数据值SELECT出来并将结果保存在Log中。

系统上线后有可能因为用户操作的原因,也可能是系统的Bug,导致了错误数据的产生,那么就需要出维护脚本将这些错误的数据删除或者更新回来。对于delete和update类的维护脚本,需要在删除和修改之前先select出要修改的数据,维护人员将查询的结果保存到维护日志中,这样如果编写的维护脚本有问题,那么还可以根据维护日志看到原来的数据,将数据修复回来。

采用以上方式编写sql部署脚本的优点为,sql脚本可以执行多次,不会出现报错信息,可以避免已经手动增加列的数据库产生部署错误提示信息

DB2 Command Line Processor(DB2 CLP)是所有 DB2 产品中都有的,可以使用这个应用程序运行 DB2 命令、操作系统命令或 SQL 语句。DB2 CLP 可以成为强大的工具,因为它能够将经常使用的命令或语句序列存储在批处理文件中,可以在必要的时候运行这些批处理文件。在 Windows 系统中,必须先(从普通命令窗口)运行 db2cmd 命令来启动 DB2 命令行环境。

在数据库 xsgl 中,利用 Transact-SQL 语言创建表格:

1)表格名为 kc(课程情况表);
2)表格中各个属性用sql描述

USE xsgl;
create table kc
(
kch char(4) not null, (不能取空值)
kcm char(20) null,
xss int null,
xf int null,
primary key(kch) (设置为主键)
)engine=innodb default charset=utf8 auto_increment=1;(使用innodb引擎,数据库默认编码为utf-8,自增键的起始序号为1)

* *

在什么时候使用本文介绍的脚本?

利用 Transact-SQL 语言修改 kc 表

1.增加“成绩”一列 cj, int 型,允许为空值,默认为 0。

ALTER TABLE kc ADD COLUMN cj INT DEFAULT 0;

2.修改cj列的类型为char。

ALTER TABLE kc CHANGE COLUMN cj cj CHAR(4);

3.修改cj列的列名为mark。

ALTER TABLE kc CHANGE cj mark CHAR(4) DEFAULT 0;

4.删除mark列。

ALTER TABLE kc DROP COLUMN mark;

5.删除表 kc。

DROP TABLE kc

6.利用 Transact-SQL 将表 xs 重命名为 Students。

RENAME TABLE xsgl.xs TO xsgl.Students;

7.使用alert table增加和删除列和改变数据类型

ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE table_name ;
DROP COLUMN column_name;
ALTER TABLE table_name
ALTER COLUMN column_name datatype

脚本的优势在于可以重复执行。如果一个任务会被执行多次,或者被多人执行。那么将该任务编写成脚本会提高任务的执行效率和准确性。本文介绍的脚本都是一些在我们进行存储过程开发和调试中经常用到的。读者在后面会渐渐体会到脚本给我们带来的便利。

简单查询语句

1.查询 world 数据库的 country 表中的国名(Name)、洲名(Continent)和地区(Region)。

USE world
SELECT NAME,Continent,Region FROM country;

2.从 world 数据库的 city 表中搜索返回前 20 条的数据。

USE world;
SELECT * FROM city LIMIT 0,20;

其中,“LIMIT M,N”中的 M 表示从第 M 条(不包括 M)开始

3.使用 WHERE 子句从 world 数据库的 country 表中检索出所有领土面积超过一百万
平方公里的国家名称(Name)、洲名(Continent) 以及领土面积(SurfaceArea)

USE world;
SELECT NAME,Continent,SurfaceArea
FROM country
WHERE SurfaceArea > 1000000;

4.查询在 world 数据库的 country 表中 Name 以字母 C 开头的国家的洲名 Continent,地
区 Region

USE world;
SELECT Name,Continent,Region
FROM country
WHERE NAME like 'C%';

5.查询 world 数据库 Country 表中所有国家的 Name 和 Condinent,并按生日 SurfaceArea
从小到大进行排列。

USE world;
SELECT Name,Continent,SurfaceArea
FROM country
ORDER BY SurfaceArea;

如果是降序排列的话

ORDER BY Population DESC;

6.country表中查询独立年份为空的国家的名称、国家年份。

USE world;
SELECT NAME,IndepYear
FROM country
WHERE IndepYear is null;

DB2 系统表的功能

使用 Transact-SQL 语言输入表数据、修改表数据和删除表数据

1.在表 newlanguage 中插入中国的客家话,其名称为 CountryCode 为 CHN, language
为 Kejia, isOfficial 为 F, percentage 为 0.3。

USE world;
INSERT INTO newlanguage
VALUES ('CHN','Kejia','F',0.3);

2.试将表 countrylanguage 中的所有记录插入到表 newlanguage 中去,

USE world;
INSERT INTO newlanguage
SELECT *
FROM countrylanguage;

3.将表 newlanguage 中 Language 为“Kejia” 的语言的 Percentage 改为 0.4

USE world;
UPDATE newlanguage
SET Percentage = 0.4
WHERE Language = 'Kejia';

4.将表 newlanguage 中语言的 Percentage 均减去 0.1

USE world;
UPDATE newlanguage
SET Percentage = Percentage - 0.1;

5.删除表 newlanguage 中澳大利亚(CountrCode 为“AUS”) 的英语记录。

USE world;
DELETE FROM newlanguage
WHERE CountryCode='AUS'
AND Language='English';

6.清空表 newlanguage 中的所有数据。

USE world;
TRUNCATE TABLE newlanguage;

编写针对 DB2 的脚本,我们需要首先了解 DB2 为我们提供了哪些命令和信息。DB2 为我们提供了大量的命令例如连接数据库,执行一个 SQL 文件,获得表结构的信息等等。我们会在下面解释具体的脚本的同时对一些简单的 DB2 命令进行解释说明。同时,DB2 把数据库对象的很多信息都存储到了系统表中。熟悉这些系统表就能够通过 SQL 语句获得我们需要的信息。下面我们先来学习一下 DB2 系统表。

视图管理

视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。对其中所引用的基础表来说,视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表,或者其它视图。通过视图进行查询没有任何限制,通过它们进行数据修改时的限制也很少。视图是存储在数据库中的查询的SQL 语句,它主要出于两种原因:安全原因, 视图可以隐藏一些数据,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,另一原因是可使复杂的查询易于理解和使用。这个视图就像一个“窗口”,从中只能看到你想看的数据列。这意味着你可以在这个视图上使用SELECT *,而你看到的将是你在视图定义里给出的那些数据列

1.建立亚洲国家的视图 asia_country,并要求进行修改和插入操作时仍需保证该视图
只有亚洲国家,视图的属性名为 Code, Name, Continent, Population, GNP,
GovernmentForm.

USE world;
CREATE VIEW asia_country
AS
SELECT Code,Name,Continent,Population,GNP,GovernmentForm
FROM country
WHERE Continent='Asia';

2.建立国家简称(country.Code)、城市名(city.Name)、语言名(countrylanguage.Language)
的视图 cc_language。本视图由三个基本表的连接操作导出

USE world;
CREATE VIEW cc_language
AS
SELECT country.Code,city.Name,countrylanguage.Language
FROM country,city,countrylanguage
WHERE country.Code = city.CountryCode
AND country.Code = countrylanguage.CountryCode;

在 DB2 数据库被创建的时候,DB2 会创建一些系统表。这些系统表中记录了所有数据库对象的信息,表或视图的列的数据类型,约束的定义,对象的权限和对象之间的依赖关系等。这些系统表的模式为 SYSIBM,其表名以 SYS 作为前缀。例如: SYSTABLES、SYSVIEWS 等等。DB2 为这些系统表建立了相对应的只读视图。这些视图的模式是 SYSCAT,它们的内容是其相对应的系统表的全部或者部分内容。这些视图的名字没有 SYS 的前缀。例如:SYSCAT.TABLES 是 SYSIBM.SYSTABLES 的视图。

数据类型

1.日期和时间数据类型

数据类型 含义
date 3字节,日期,格式:2014-09-18
time 3字节,时间,格式,09:20:20
datetime 8字节,日期格式,格式:2014-09-18 08:42:40
timestamp 4字节,自动存储记录修改时间
year 1字节,年份

2.整型

mysql数据类型 含义(有符号)
tinyint 1字节,范围(-128~127)
smallint 2字节,范围(-32768~32767)
int 4字节(-2147483648~-2147483648)

3.浮点型

mysql数据类型 含义
float(m,d) 4字节,单精度浮点,m总个数,d小数位
double(m,d) 8字节,双精度浮点,m总个数,d小数位
decimal(m,d) decimal是存储为字符串的浮点数

4.字符串类型

mysql数据类型 含义
char(n) 固定长度,最多255个字符
varchar(n) 可变长度,最多65535个字符
tinytext 可变长度,最多255个字符
text 可变长度,最多65535个字符

char(n)和 varchar(n)中括号中 n 代表字符的个数,并不代表字节个数,所以当使用了中文的时候(UTF8)意味着可以插入 m 个中文,但是实际会占用 m*3 个字节

同时 char 和 varchar 最大的区别就在于 char 不管实际 value 都会占用 n 个字符的空间,而
varchar 只会占用实际字符应该占用的空间 1,并且实际空间 1<=n

超过 char 和 varchar 的 n 设置后,字符串会被截断

char 在存储的时候会截断尾部的空格, varchar 和 text 不会。

varchar 会使用 1-3 个字节来存储长度, text 不会。

5.其他类型

  • enum(“member1″ , “member2″ , … “member65535″ ): enum 数据类型就是定义了
    一种枚举,最多包含 65535 个不同的成员。当定义了一个 enum 的列时,该列的值限制为列
    定义中声明的值。如果列声明包含 NULL 属性,则 NULL 将被认为是一个有效值,并且是
    默认值。如果声明了 NOT NULL,则列表的第一个成员是默认值
  • set(“member” , “member2″ , … “member64″ ): set 数据类型为指定一组预定义值中
    的零个或多个值提供了一种方法,这组值最多包括 64 个成员。值的选择限制为列定义中声
    明的值

我们可以通过 LIST TABLES FOR SYSTEM 或 LIST TABLES FOR SCHEMA schemaname 命令查看所有的系统表和相关的视图信息。下面我们会介绍一些本文用到的系统表和视图。

数据类型属性

1.auto_increment

auto_increment 能为新插入的行赋一个唯一的整数标识符。为列赋此属性将为每个新插
入的行赋值为上一次插入的 ID 1。
MySQL 要求将 auto_increment 属性用于作为主键的列。此外,每个表只允许有一个
auto_increment 列。例如:

id smallint not null auto_increment primary key 

2.binary

binary 属性只用于 char 和 varchar 值。当为列指定了该属性时,将以区分大小写的方式
排序。与之相反,忽略 binary 属性时,将使用不区分大小写的方式排序。例如:

hostname char(25) binary not null

3.default

default 属性确保在没有任何值可用的情况下,赋予某个常量值,这个值必须是常量,因
为 MySQL 不允许插入函数或表达式值。此外,此属性无法用于 BLOB 或 TEXT 列。如果已
经为此列指定了 NULL 属性,没有指定默认值时默认值将为 NULL,否则默认值将依赖于
字段的数据类型。例如:

subscribed enum('0', '1') not null default '0'

4.index(索引)

如果所有其他因素都相同,要加速数据库查询,使用索引通常是最重要的一个步骤。索
引一个列会为该列创建一个有序的键数组,每个键指向其相应的表行。以后针对输入条件可
以搜索这个有序的键数组,与搜索整个未索引的表相比,这将在性能方面得到极大的提升

create table employees
(
id varchar(9) not null,
firstname varchar(15) not null,
lastname varchar(25) not null,
email varchar(45) not null,
phone varchar(10) not null,
index lastname(lastname),
primary key(id)
);

我们也可以利用 MySQL 的 create index 命令在创建表之后增加索引:

create index lastname on employees (lastname(7)) 

5.not null

如果将一个列定义为 not null,将不允许向该列插入 null 值。建议在重要情况下始终使
用 not null 属性,因为它提供了一个基本验证,确保已经向查询传递了所有必要的值

6.null

为列指定 null 属性时,该列可以保持为空,而不论行中其它列是否已经被填充。记住,
null 精确的说法是“无”,而不是空字符串或 0。

7.primary key

primary key 属性用于确保指定行的唯一性。指定为主键的列中,值不能重复,也不能
为空。为指定为主键的列赋予 auto_increment 属性是很常见的,因为此列不必与行数据有任
何关系,而只是作为一个唯一标识符。主键又分为以下两种:
①单字段主键: 如果输入到数据库中的每行都已经有不可修改的唯一标识符,一般会使
用单字段主键。注意,此主键一旦设置就不能再修改。
②多字段主键: 如果记录中任何一个字段都不可能保证唯一性,就可以使用多字段主
键。这时,多个字段联合起来确保唯一性。如果出现这种情况,指定一个 auto_increment 整
数作为主键是更好的办法

8.unique

被赋予 unique 属性的列将确保所有值都有不同的值,只是 null 值可以重复。一般会指
定一个列为 unique,以确保该列的所有值都不同。例如:

email varchar(45) unique 

9.zerofill

zerofill 属性可用于任何数值类型,用 0 填充所有剩余字段空间。例如,无符号 int 的默
认宽度是 10;因此,当“零填充”的 int 值为 4 时,将表示它为 0000000004。例如:

orderid int unsigned zerofill not null

SYSCAT.TABLES:数据库中对象的信息,包括 table,view,nickname 和 alias 的一些定义。详细说明见表 1。

高级查询语句

1.查询所有国家名称及相应的城市、语言。 (涉及三个表)

USE world;
SELECT country.Name,city.Name,countrylanguage.Language
FROM country,city,countrylanguage
WHERE country.Code= city.CountryCode
AND city.CountryCode = countrylanguage.CountryCode;

2.查询国家政体为共和国且国家人口在一千万以上的城市的名称和城市人口、所属国

USE world;
SELECT city.Name,city.Population,country.Name
FROM country,city
WHERE city.CountryCode = country.Code
AND country.Population > 10000000;

3.统计 country 表中共和国政体的国家数。

USE world;
SELECT COUNT(Code)//计算数量,计算code数量
FROM country
WHERE GovernmentForm = 'Republic';

4.统计 country 表中共和国政体国家的平均人口。

USE world;
SELECT AVG(Population)//计算平均
FROM country
WHERE GovernmentForm = 'Republic';

5.分组统计 country 表中各政体的国家个数。

USE world;
SELECT GovernmentForm,count(*)//计算
FROM country
GROUP BY GovernmentForm;//分组

6.查询有超过两条城市记录的国家的名称。

USE world;
SELECT country.Name
FROM city,country
WHERE city.CountryCode = country.Code
GROUP BY city.CountryCode
HAVING COUNT(city.Name) > 2; //表示拥有的数量

7、查询非共和政体的国家的名称和政体

USE world;
SELECT Name,GovernmentForm
FROM country
WHERE GovernmentForm <> 'Republic'; //怎样取非

8.查询平均每国人口数高于非洲的大洲名称及该平均数,以平均数的降序排列

USE world;
SELECT Continent,AVG(Population)
FROM country
GROUP BY Continent
HAVING AVG(Population) >
(SELECT AVG(Population) FROM country WHERE Continent = 'Africa')
ORDER BY AVG(Population) DESC;//嵌套循环

9.查询 city 表中多于 3 个城市记录且缩写以“A”开头的国家的名称和城市平均人口,
以平均人口的升序排列

USE world;
SELECT city.CountryCode,country.Name,AVG(city.Population)
FROM city,country
WHERE city.CountryCode LIKE 'A%'
AND city.CountryCode = country.Code
GROUP BY city.CountryCode
HAVING COUNT(city.Name) > 3
ORDER BY AVG(city.Population);

10.查找零件表中最小

USE test;
select pno
from p
having min(weight);

11.查找同时为J1和J2提供零件的供应商代码

USE test;
select sno
from spj
where jno='J1' and sno in (select sno from spj where jno='j2')

表 1. SYSCAT.TABLES 视图的说明
列名 数据类型 描述
TABSCHEMA VARCHAR(128) 记录 schema 的名字
TABNAME VARCHAR(128) 记录数据库对象的名称。包括表、视图、别名等
TYPE CHAR(1) 表示该数据库对象是表,视图还是别名 ('T'表示table; 'V'表示 view; 'N' 表示nickname; 'A' 表示 alias。)
COLCOUNT SMALLINT 表或视图中列的个数
……

使用sql语言创建复杂数据库

1.学生基本表和课程基本表

要求:

1、 在 xs 表中定义 xh 为主键。
2、在 kc 表中定义 xh 和 kch 联合构成主键。
3、定义 kc 表中的 kcm 列满足唯一性约束。
4、定义 kc 表中的 fs 列默认值为 0。
5、定义 xs 表中的 xb 列的 CHECK 约束“男”或“女”。
6、在 xs 表与 kc 表之间定义外键 xh。

xs表:

use xsgl;
create table xs
(
xh int not null,
xm char(8) null,
xb char(2) null check(xb in('男','女')),//check约束,范围规定,枚举值规定,特定匹配
nl tinyint null,
zy char(16) null,
jtzz char(50) null,
primary key(xh)
)engine=innodb default charset=utf8 auto_increment=1;

kc表

create table kc
(
xh int not null,
kch int not null,
kcm char(20) null unique,
xss int null,
xf int null,
fs int null default 0,
primary key(xh,kch),
foreign key(xh) references xs(xh) on delete cascade on update
cascade//外键,并且xs的xh发生改变时,跟随改变和删除
)engine=innodb default charset=utf8 auto_increment=1;

2.创建一个学生—课程数据库,包括学生关系Student、课程关系Course和选修
关系SC

1)建立学生-课程数据库xskc,创建Student表,并将Student表中的Sno属性定义为主键

2)创建Course表和SC表,并将SC表中的Sno,Cno联合构成主键

CREATE DATABASE xskc;
USE xskc;
CREATE TABLE Student
(Sno CHAR(9) PRIMARY KEY,
Sname CHAR(20) NOT NULL,
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
)

USE xskc;
CREATE TABLE Course
(Cno CHAR(9) PRIMARY KEY,
Cname CHAR(20),
Cpno CHAR(4),
Ccredit SMALLINT
) ;
CREATE TABLE SC
(Sno CHAR(9) NOT NULL,
Cno CHAR(9) NOT NULL,
Grade SMALLINT,
PRIMARY KEY(Sno,Cno)
)

3)定义sc表中参照完整性

USE xskc;
ALTER TABLE SC ADD FOREIGN KEY (Sno) REFERENCES Student(Sno);
ALTER TABLE SC ADD FOREIGN KEY (Cno) REFERENCES Course(Cno);

我们常常希望保证在一个关系中给定属性集上的取值也在另一个关系的特定属性集的取值中出现。这种情况称为参照完整性(referential integrity)

外键级联的三种方式

1):on delete no action 数据库默认的方式,禁止删除,就是不级联删除

2):on delete cascade 级联删除

3):on delete set null 不级联删除,将外键级联的字段赋个空值。

SYSCAT.VIEWS:视图的定义信息。详细说明见表 2。

数据库自定义函数

需要mysql实现sql逻辑处理,参数是IN参数,含有returns语句用来指定函数返回类型

创建:CREATE FUNCTION函数名称(参数列表)

RETURNS 返回值类型

函数体

修改: ALTER FUNCTION 函数名称 [characteristic ...]

删除:DROP FUNCTION [IF EXISTS] 函数名称

调用:SELECT 函数名称(参数列表)

表 2. SYSCAT.VIEWS 视图的说明
列名 数据类型 描述
VIEWSCHEMA VARCHAR(128) 视图的 Schema
VIEWNAME VARCHAR(128) 视图名称
READONLY CHAR(1) 视图是否只读:

数据库存储过程

一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,比一个个执行sql语句效率高,用户通过指定存储过程的名字并给出参数来执行它。参数可以为IN, OUT, 或INOUT

创建:CREATE PROCEDURE 过程名 (参数列表) [characteristic ...]

函数体

修改: ALTER PROCEDURE 过程名 [characteristic ...]

删除:DROP PROCEDURE [IF EXISTS] 过程名

调用:CALL 过程名(参数列表)

存储过程与函数区别

1.存储过程,功能强大,可以执行包括修改表等一系列数据库操作,也可以创建为数据库启动时自动运行的存储过程。

自定义函数,用户定义函数不能用于执行一组修改全局数据库状态的操作。
2.对于sql server: 存储过程,可以使用非确定函数。自定义函数,不允许在用户定义函数主体中内置非确定函数。

3.存储过程,可返回记录集。

自定义函数,可以返回表变量。
4.存储过程,其返回值不能被直接引用。

自定义函数,其返回值可以被直接引用。
5.存储过程,用 CALL 语句执行。

自定义函数,在查询语句中调用。

    Y = 视图是只读
    N = 视图不是只读

触发器

与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

创建:

CREATE TRIGGER <触发器名称> --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
{ BEFORE | AFTER } --触发器有执行的时间设置:可以设置为事件发生前或后。
{ INSERT | UPDATE | DELETE } --同样也能设定触发的事件:它们可以在执行insert、update或delete的过程中触发。
ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
FOR EACH ROW --触发器的执行间隔:FOR EACH ROW子句通知触发器 每隔一行执行一次动作,而不是对整个表执行一次。
<触发器SQL语句> --触发器包含所要触发的SQL语句:这里的语句可以是任何合法的语句, 包括复合语句,但是这里的语句受的限制和函数的一样。
--创建触发器(CREATE TRIGGER),需要SUPER权限。

eg:

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4   1 WHERE a4 = NEW.a1;
  END

可以用来进行另一种级联式修改

删除:DROP TRIGGER 方案名称.触发器名称

VALID CHAR(1) 视图状态是否合法:

事件

可以将数据库按自定义的时间周期触发某种操作,可以理解为时间触发器,类似于linux系统下面的任务调度器crontab,或者类似与window下面的计划任务。值得一提的是MySQL的事件调度器可以精确到每秒钟执行一个任务,而操作系统的计划任务(如:Linux下的CRON或Windows下的任务计划)只能精确到每分钟执行一次。

创建

CREATE

    [DEFINER = { user | CURRENT_USER }]   --定义事件执行的时候检查权限的用户。

    EVENT

    [IF NOT EXISTS]

    event_name

    ON SCHEDULE schedule              --定义执行的时间和时间间隔。

    [ON COMPLETION [NOT] PRESERVE]     --定义事件是一次执行还是永久执行,默认为一次执行,即NOT PRESERVE。

    [ENABLE | DISABLE | DISABLE ON SLAVE]   --定义事件创建以后是开启还是关闭,以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话,会自动加上DISABLE ON SLAVE

    [COMMENT 'comment']                 -- 注释

    DO event_body;



schedule:

    AT timestamp [  INTERVAL interval] ...

     | EVERY interval

    [STARTS timestamp [  INTERVAL interval]...]

    [ENDS timestamp [  INTERVAL interval] ...]

interval:

  quantity {YEAR | QUARTER | MONTH | DAY | HOUR| MINUTE |

              WEEK | SECOND | YEAR_MONTH |DAY_HOUR |

DAY_MINUTE |DAY_SECOND| HOUR_MINUTE |

HOUR_SECOND| MINUTE_SECOND}

删除:

DROP EVENT [IF EXISTS] event_name

修改:

ALTER

    [DEFINER = { user | CURRENT_USER }]

    EVENT event_name

    [ON SCHEDULE schedule]

    [ON COMPLETION [NOT] PRESERVE]

    [RENAME TO new_event_name]

    [ENABLE | DISABLE | DISABLE ON SLAVE]

    [COMMENT 'comment']

    [DO event_body]

查看是否开启了事件

SHOW VARIABLES LIKE 'event_scheduler';

SELECT @@event_scheduler;

SHOW PROCESSLIST;

如果看到event_scheduler为on或者PROCESSLIST中显示有event_scheduler的信息说明就已经开启了事件。如果显示为off或者在PROCESSLIST中查看不到event_scheduler的信息,那么就说明事件没有开启,我们需要开启它。

    Y = 视图状态合法(valid)
    X = 视图状态不合法(invalid)

TEXT CLOB (64K) 视图的源程序(DDL)
……

SYSCAT.ROUTINES:DB2 UDF,系统方法(system-generated method),用户定义方法(user-defined method)和存储过程(SP)的定义。我们可以认为该视图包含了数据库中程序的定义。见表 3。

表 3. SYSCAT.ROUTINES 视图的说明
列名 数据类型 描述
ROUTINESCHEMA VARCHAR(128) 记录程序的 schema
ROUTINENAME VARCHAR(128) 记录程序名称
ROUTINETYPE CHAR(1) 记录程序类型:

    F = Function
    M = Method
    P = Procedure

SPECIFICNAME VARCHAR(128) 程序实例的名称(可以指定,也可以由系统自动生成)
VALID CHAR(1) 如果存储过程依赖的一些对象被删除或修改了,该存储过程必须要被重建

    Y = SQL 存储过程是合法的
    N = SQL 存储过程是非法的
    X = SQL 存储过程是不可操作的

TEXT CLOB(1M) 如果是用 SQL 编写的程序,该字段记录了其创建的 DDL

表 4 所示的 SYSCAT.ROUTINEDEP 说明了 DB2 UDF与其他对象的依赖关系。

表 4. SYSCAT.ROUTINEDEP 视图的说明
列名 数据类型 描述
ROUTINESCHEMA VARCHAR(128) 依赖于其他对象的 DB2 程序的 schema
ROUTINENAME VARCHAR(128) 依赖于其他对象的 DB2 程序的名称
BTYPE CHAR(1) 依赖对象的类型:

    A = Alias
    S = Materialized query table
    T = Table
    V = View

BSCHEMA VARCHAR(128) 被依赖的对象的 schema
BNAME VARCHAR(128) 被依赖的对象的名称

表 5 所示的 SYSCAT.COLUMNS 说明了表或视图的每一个列的信息。

表 5. SYSCAT.COLUMNS 视图的说明
列名 数据类型 描述
TABSCHEMA VARCHAR(128) 表或视图的 Schema
TABNAME VARCHAR(128) 表或视图名称
COLNAME VARCHAR(128) 列名称
KEYSEQ SMALLINT 记录列在其表的主键的位置

表 6 所示的 SYSCAT.PACKAGEDEP 说明了 Pachage 与其他数据库对象的依赖关系。

表 6. SYSCAT.PACKAGEDEP 视图的说明
列名 数据类型 描述
PKGSCHEMA VARCHAR(128) Package 的 schema
PKGNAME VARCHAR(18) Package 的名称
BTYPE CHAR(1) 依赖对象的类型:

    A = Alias
    B = Trigger
    I = Index
    S = Materialized query table
    T = Table
    V = View

BSCHEMA VARCHAR(128) 被依赖的对象的 schema
BNAME VARCHAR(128) 被依赖的对象的名称

表 7 所示的 SYSCAT.TABDEP 说明了视图或者固化视图和 DB2 对象之间的依赖关系。

表 7. SYSCAT.TABDEP 视图的说明
列名 数据类型 描述
TABSCHEMA VARCHAR(128) 视图或者固化视图的 schema
TABNAME VARCHAR(128) 视图或者固化视图的名称
DTYPE CHAR(1) 视图的类型:

    S = Materialized query table
    V = View (untyped)
    W = Typed view

BTYPE CHAR(1) 依赖对象的类型:

    A = Alias
    S = Materialized query table
    T = Table
    V = View

BSCHEMA VARCHAR(128) 被依赖的对象的 schema
BNAME VARCHAR(128) 被依赖的对象的名称

回页首

使用脚本进行存储过程开发

在 DB2 开发过程中我们经常使用的操作是:

    连接数据库;
    在不同的数据库中执行编写好的 SQL 文件,对创建的数据库对象进行简单测试;
    当我们编写存储过程或者 UDF 的时候,我们需要查看其依赖的数据库表或视图的定义;
    修改表结构的时候,我们需要获得所有依赖这个表的存储过程和 UDF,防止因为表结构变化,使得相关的存储过程和 UDF 不可用。

以上操作我们可以在 CLP 下通过调用 DB2 提供的命令或者通过 SELECT 语句从 DB2 系统表中查出相关信息来完成。我们把这些操作编写为如清单 1 所示的脚本。

清单 1. conn.bat ――使用脚本连接数据库

                1. @echo off 2. if {%1}=={} goto noparms  3. if {%1}=={dev} goto dev 4. if {%1}=={test} goto test 5. if {%1}=={prod} goto prod 6. if {%1}=={off} goto terminate 7. goto dev  8. :noparms  9. echo Usage: conn db2type  10. echo example: conn dev  11. echo db2type -- dev, test, prod, off 12. goto end   13. :dev 14. db2 connect to db2dev user username using password 15. PROMPT $P(%1)$G 16. goto end 

  1. :test 18. db2 connect to db2test user username using password 19. PROMPT $P(%1)$G 20. goto end  21. :prod 22. db2 connect to db2prod user username using password 23. PROMPT $P(%1)$G 24. goto end  25. :terminate
  2. db2 terminate 27. PROMPT $P$G 28. goto end   29. :end

每次连接数据库我们只需要键入 conn dev/test 就可以完成数据库的连接,简化了命令输入,避免输入错误的用户和密码。同时这个脚本也可被其他的脚本调用。

第 2 个批处理脚本是如清单 2 所示的执行 SQL 文件的脚本。使用方法是 esql test.sql [logsout.log]。

清单 2. esql.bat――执行SQL文件

                1. @echo off 2. if {%2} =={} goto setlog 3. set LogFile=%2 4. goto exe 5. rem 如果没有指定输出文件,设置默认输出文件 6. :setlog 7. set LogFile=logsrun.log 8.  9. :exe 10. echo %LogFile% 11. echo --------------------- execute file:%1-------------------- >> %LogFile% 12. db2 values(current time) >> %LogFile% 13. db2 -td@ -f %1 -z %LogFile% 14. db2 values(current time) >> %LogFile% 15. pause

其中 –td@ 选项表示 SQL 文件中各 SQL 语句间的分割符为 @。–z 选项可以使 sql 的执行结果输出到指定的 Log 文件中。这里我们使用 -z 而不使用脚本中的管道符号(>)是因为 -z 不仅可以把执行结果记录到指定的文件中,同时还会在控制台上显示,这样即方便我们在执行 sql 的时候实时掌握 sql 运行情况,又可以在 log 中查找分析 sql 执行情况。

该脚本需要两个参数,第一个参数指明要执行的 sql 文件,第二个参数指明 log 文件名称,该参数可选。在脚本的第 12 行,第 14 行,我们使用 values(current time) 来记录脚本执行的开始时间和结束时间。这样可以帮助我们计算出脚本执行完毕需要多长时间。

第 3 个批处理脚本的作用是获得表定义,包括字段属性、主键、前 20 条记录,如果是视图,显示其创建语句,并显示视图中包含的表。如清单 3 所示。它的使用方法是 viewtable schema tablename。

清单 3. viewtable.bat ――获得表定义

                1. @echo off 2. mkdir logs 3. set logfile=logsview_%2.txt 4. db2 describe table %1.%2 show detail >%logfile% 5. echo  -------below is (are) pk-----------   >>%logfile% 6. db2 "select substr(colname,1,30) as pk from syscat.columns  where keyseq is not null and tabschema=upper('%1') and tabname = upper('%2')" >>%logfile% 7. echo  -----below are the fisrt 20 rows in the %1.%2 table----   >>%logfile% 8. db2 select * from %1.%2 fetch first 20 rows  only >>%logfile% 9. echo  ---------below is the sql of the %1.%2 view ----   >>%logfile% 10. db2 "select varchar(text,32670) from syscat.views  where viewschema=upper('%1')and  viewname = upper('%2')" >>%logfile% 11. echo  -----below are tables  of the %1.%2 view ----   >>%logfile%

  1. db2 "select distinct bname from syscat.viewdep  where viewname= upper('%1')  and btype='T'" >>%logfile% 13. start %logfile%

脚本中第 4 行的 describe 命令用于显示数据库表或视图的结构。包括字段名称,数据类型等。第 6 行用于查出表中的主键。视图 syscat.columns 中的 keyseq 字段表明了该字段在表的主键的顺序,为空表示该字段不是表的主键。第 8 行是获得表或视图中的前 20 条记录,便于我们对其分析。第 10 行是获得视图的 SQL 定义。第 12 行获得视图中依赖的表。

第 4 个批处理脚本的作用是查看存储过程或 UDF 的定义。如清单 4 所示。它的使用方法是 view spname。

清单 4. view.bat ――查看存储过程或 UDF 的定义

                1. @echo off 2. set logfile=logsview_%1.sql 3. db2 select varchar(text,32670)  from syscat.routines  where routinename=upper('%1') > %logfile% 4. echo  -----------------------below are tables used by %1 ---------  >>%logfile% 5. db2 select bname from syscat.packagedep  where (btype='T' or btype='V') and pkgname in (select bname from sysibm.sysdependencies where dname =upper('%1')) >> %logfile% 6. 

  1. start %logfile%

在脚本中我们之所以使用 VARCHAR 函数来格式 TEXT 字段中的内容,是因为 TEXT 字段是 CLOB 类型,其存储的存储过程或 UDF 的创建语句的字符比较多。如果我们不使用 VARCHAR 函数进行格式化,DB2 就会使用默认的 CHAR 函数(CHAR 的最大长度是 254)对其格式化,这样返回的 SQL 创建语句就可能被截去后面的部分。

第 5 行中脚本首先获得依赖某存储过程的所有的包(package)名,然后查找依赖这些包的所有的表和视图。这样就可以获得存储过程中使用的表和视图。

第 4 个批处理脚本的作用是使用脚本获得依赖某个表的所有存储过程,UDF 和视图。如清单 5 所示。它的使用方法是 dep schema spname。

清单 5. dep.bat ――使用脚本获得依赖某个表的所有存储过程,UDF 和视图

                1. @echo off 2. mkdir logs 3. set logfile=logs%2.dep.txt 4. echo --- dependent SPs --- >> %logfile% 5. db2 "select r.routineschema, r.routinename  from syscat.routines r,  syscat.packagedep pdep where pdep.bname=upper('%2') and pdep.bschema=upper('%1')  and pdep.pkgname = 'p'||substr(char(r.lib_id 10000000),2) " >> %logfile% 6. echo --- dependent UDF --- >> %logfile% 7. db2 select routineschema, routinename from syscat.routinedep  where bschema = upper('%1')  and bname = upper('%2') and btype ='T'  order by bname >> %logfile% 8. echo --- dependent view --- >> %logfile% 9. db2 select viewschema, viewname from syscat.viewdep  where bschema = upper('%1')  and bname = upper('%2') and btype ='T'  order by bname >> %logfile% 10. start %logfile%

脚本第 5 行是获得依赖某表的存储过程。因为存储过程会被编译成包(package)。所以,存储过程和表的依赖关系实际上是存储过程对应的包(package)和表之间的依赖关系。这种关系被记录在 syscat.packagedep 视图中。同样的,我们可以通过查询 syscat.routinedep 和 syscat.viewdep 视图,获得 UDF 和表,视图和表之间的依赖关系。

回页首

开发实例

工作需要,数据库sql语句及使用示例。使用上面的脚本,进行数据库开发就会很方便。下面我们看一个开发的例子。

需求描述

由于业务变更,我们的《订单管理系统》中的表 MIS.ORDER 需要加一个字段 GOV_FLAG SMALLINT 来标识是否是政府订单,如果是则需要执行特殊的折扣政策。同时需要去除冗余字段 ORDER_PRICE_TOT DECIMAL(19,4)。

需求描述

开发过程

我们需要修改相应的表,存储过程和 UDF 的 SQL 文件,并把它们重新装载到开发数据库和集成测试数据库中。并需要保留表中原有数据。

假设我们有两个数据库,一个是名为 dev 的开发数据库,一个是名为 test 的集成测试数据库。只有项目组长拥有在 test 数据库上执行的权限,开发人员只可以在 dev 数据库上执行操作。

首先项目组长会分析需求,制定任务分配;然后开发人员根据任务分配编写相应的 SQL 和脚本文件,并且在 dev 数据库上进行装载和测试;最后项目组长把开发人员提交的脚本文件汇总起来,在 test 数据库上进行装载和测试。

  1. 项目组长

项目组长需要把这个任务分配给组员去完成。首先组长使用脚本 viewtable MIS ORDER 查看一下当前在数据库中表的结构,检查添加的字段是否会和其他字段有冲突。然后项目组长使用脚本 dep MIS ORDER 获得依赖表 MIS.ORDER 的所有 DB2 对象。经过分析这些依赖对象,项目组长发现,表结构的修改会导致存储过程 MIS.SP_ADD_ORDER、UDF MIS.F_CHECK_ORDER 和视图 MIS.V_ORDER 的修改,并会导致两个存储过程需要 rebind。分析结果汇总如下表 8 所示:

表 8. 项目组长的分析结果
名称 执行操作 备注
MIS.SP_ADD_ORDER 修改
MIS.SP_UPDATE_ORDER rebind 没有使用要删除的字段,不必修改
MIS.SP_ADD_ORDER rebind 没有使用要删除的字段,不必修改
MIS.F_CHECK_ORDER 修改
MIS.V_ORDER 修改

于是项目组长进行如表 9 的任务分配。

表 9. 项目组长制定的任务分配
编号 任务 执行人 前提条件 完成标志
10 修改 ORDER.SQL,在 MIS.ORDER 中:

    添加一个字段 GOV_FLAG SMALLINT;
    去除冗余字段 ORDER_PRICE_TOT DECIMAL(19,4);
    备注:在删除原表前,需要首先删除依赖该表的 UDF 和视图。创建新表后,需要把除了 MIS.F_CHECK_ORDER 和 MIS.V_ORDER 以外的其他的 UDF 等重新装载到数据库中,并且 rebind 存储过程 MIS.SP_UPDATE_ORDER 和 MIS.SP_SELECT_ORDER。

A 无

    项目组长检查完毕 SQL 脚本;
    在开发数据库实例 db2dev 上创建和测试成功。

20 根据新的 MIS.ORDER 表结构修改储存过程: SP_ADD_ORDER.SQL B 任务 10 完成

    项目组长检查完毕 SQL 脚本;
    在开发数据库实例 db2dev 上创建和测试成功。

30

    根据 MIS.ORDER 表结构修改 UDF F_CHECK_ORDER.SQL 和视图 V_ORDER.SQL
    备注:请先删除依赖它们的 DB2 对象,并重新装载它们,使它们的状态合法。

C 任务 10 完成

    项目组长检查完毕 SQL 脚本;
    在开发数据库实例 db2dev 上创建和测试成功。

  1. 开发人员 A

A 接到任务后,从 CVS 中获得表创建 SQL 文件 ORDER.SQL,根据任务要求修改了字段 GOV_FLAG 和 ORDER_PRICE_TOT。下面 A 需要把它装载到数据库中。在重新创建表 MIS.ORDER 前,我们需要先做一些准备工作:

    备份表 MIS.ORDER 中的数据;
    获得依赖表 MIS.ORDER 所有 UDF,备份这些UDF;
    获得依赖表 MIS.ORDER 的所有存储过程,以便在重新创建表后,对它们进行 rebind。

A 首先编写了备份表的 SQL 文件 BACKUP_MIS_ORDER.SQL;然后编写恢复数据的 SQL 文件 RESTORE_MIS_ORDER.SQL;执行 dep MIS ORDER 获得依赖该表的 UDF 和存储过程。编写 DROP_UDF_4_ORDER.SQL,RELOAD_UDF_4_ORDER.SQL 和 REBIND_SP_4_ORDER.SQL。如果读者不熟悉如何获得和 rebind 非法的存储过程,可以参考 DB2 存储过程开发最佳实践。

当所有的 SQL 文件编写完后,A 开始编写执行脚本,如清单 6 所示:

清单 6. 开发人员 A 的执行脚本

                rem 连接开发数据库 dev conn dev rem 备份表 esql BACKUP_MIS_ORDER.SQL rem 删除依赖表的 UDF esql DROP_UDF_4_ORDER.SQL rem 重建表 esql ORDER.SQL rem 恢复表数据 esql RESTORE_MIS_ORDER.SQL rem 恢复 UDF esql RELOAD_UDF_4_ORDER.SQL rem rebind 相关存储过程 esql REBIND_SP_4_ORDER.SQL rem 断开数据库连接 conn off

通过上面的脚本,A 成功的完成了他的任务。他把 SQL 和 Windows 脚本提交给项目组长。

  1. 开发人员 B

B 接到任务后,从 CVS 中得到 SP_ADD_ORDER.SQL。 为了避免其他人也在修改这个存储过程,B 使用脚本 view SP_ADD_ORDER 命令获得了当前数据库中的存储过程脚本,然后和 CVS 上的进行比较。当确认获得了最新的 SP_ADD_ORDER.SQL 后,B 开始修改 SP_ADD_ORDER.SQL。修改完毕以后,B 又修改了针对该存储过程的测试脚本 TEST_SP_ADD_ORDER.SQL。所有的 SQL 修改完,并且被检查通过后,B 开始编写脚本把存储过程装载到数据库中。脚本内容大致如清单 7 所示。

清单 7. 开发人员 B 的执行脚本

                rem 连接开发数据库 conn dev rem 重建修改过的存储过程 esql SP_ADD_ORDER.SQL rem 对重建的存储过程进行测试 esql TEST_SP_ADD_ORDER.SQL conn off

通过上面的脚本,B 成功的完成了他的任务。他把 SQL 和 Windows 脚本提交给项目组长。

  1. 开发人员 C

C 同样使用 dep MIS F_CHECK_ORDER 和 dep MIS V_ORDER 获得依赖它们的对象,发现只是 F_CHECK_ORDER 有一些依赖对象需要重建。所以 C 编写了下面的 SQL 文件:DROP_UDF_4_MIS_F_CHECK_ORDER.SQL 和 RESTORE_UDF_4_MIS_F_CHECK_ORDER.SQL。下面是 Windows 脚本,如清单 8 所示:

清单 8. 开发人员 C 的执行脚本

                rem 连接开发数据库 conn dev rem 删除 F_CHECK_ORDER 的依赖对象 esql DROP_UDF_4_MIS_F_CHECK_ORDER.SQL rem 重建修改的 UDF 和视图 esql V_ORDER.SQL esql F_CHECK_ORDER.SQL rem 恢复依赖对象 esql RESTORE_UDF_4_MIS_F_CHECK_ORDER.SQL rem 测试重建的 UDF 和视图 esql TEST_V_ORDER.SQL esql TEST_F_CHECK_ORDER.SQL conn off

通过上面的脚本,C 成功的完成了他的任务。他把 SQL 和 Windows 脚本提交给项目组长。

  1. 项目组长

项目组长把 A、B、C 提交上的 Windows 脚本合并成一个脚本,并改为连接集成测试数据库,把这些存储过程,UDF 等的改变装载到了集成开发测试数据库中。脚本如清单 9 所示:

清单 9. 合并执行脚本

                conn test esql BACKUP_MIS_ORDER.SQL … esql TEST_V_ORDER.SQL … conn off

这样,一个数据库对象的修改就完成了。在实际开发中的情况可能要更复杂一些。但是通过这个例子我们可以看到,通过使用 Windows 脚本,我们可以节省一些重复性的工作并且提高了工作的准确性。当整个团队熟悉了这些脚本,工作效率的提高是相当可观的。

回页首

总结

对脚本的灵活快捷特性以及 DB2 系统表含义的理解,是编写出文中脚本的根本。利用脚本和 DB2 系统表的方式和方法远比本文描述的要丰富得多。有兴趣的读者可以加深对它们的研究。相信会对大家的日常工作有所帮助。

本文由澳门新萄京发布于数据库,转载请注明出处:工作需要,数据库sql语句及使用示例

上一篇:澳门新萄京mysql数据库小常识 下一篇:没有了
猜你喜欢
热门排行
精彩图文