利用dbaccess和shell实现数据查询

关系数据库模型简介 使用informix数据库服务器创建的数据库就是关系数据库关系数据库中的数据以具有行和列的表的格式显示数据库中的术语与实体对象的简单对应关系描述 关系描述表 实体一个表表示数据库对某一主题或事务已知的全部内容列 属性一列表示一个属性 特征或表主题适用的事实行 实例一行表示表主题的一个特例例如 保单基本信息表是描述的实际保单的基本信息 保单标的表描述的是标的的内容 而保单基本信息表中的起保日期列则表示的是保单的起保日期的属性 保单基本信息表中的某一行数据则对应实际的某张保单 SQL结构化查询语言简介 为什么需要结构化查询语言 由于目前大部分的计算机软件无法实现 老张的车是否在我公司上了保险 这样的询问 必须使用软件能够分析的格式化的语法来表述问题SQL是结构化查询语言 StructuredQueryLanguage 的缩写可以分成数据定义语言 数据操纵语言 数据控制访问语言等几类数据定义语言 DDL 用来创建 更改 删除数据库 表或视图等数据操纵语言 DML 对数据库中的数据进行查询或增删改操作数据控制访问语言 DCL 对存取权限的控制 授权和回收 游标语句 动态管理语句 数据完整性语句 查询优化语句 事务处理语句 标准SQL和informixSQL 70年代IBM发明和开发了SQL和关系模型标准SQL 86年ANSI发布了标准SQL SQL 86自1986年以来 已经存在多个版本 SQL 86 SQL 92 SQL 99 SQL 2003 其中SQL 2003是该标准的当前版本InformixSQL与标准SQL兼容InformixSQL包含对标准的扩展 大多数差异出现在不经常使用的语句中 例如 在SELECT语句方面很少出现差异 该语句占SQL使用的90 Informix结构 客户端程序 数据库引擎 数据库 Informix客户端常用工具 Informix sql IsqlDb access dbaccess实现的功能相近 都可以通过SQL语句对数据库进行操作isql可以生成form并通过form实现对数据的操作 利用dbaccess学习SQL语言 Dbaccess提供了用于输入 执行和调试SQL语句和存储过程 SPL 的用户界面使用DB Access的几种基本方法 在命令行提示符 下输入dbaccess例如 dbaccessdbaccess数据库名称例如 dbaccessstores demoDbaccessSQL脚本文件例如 dbaccessstores demoorders sql批处理命令Dbaccessstores demo Select fromorders Select fromcustomers Dbaccess菜单项 输入dbaccess后即进入主菜单 Dbaccess菜单项 Dbaccess菜单项 Dbaccess菜单项 Dbaccess菜单项 Dbaccess菜单项 Dbaccess菜单项查询演示一 输入dbaccess进入主菜单后选择查询语言菜单 在选择查询语言菜单时如未选择数据库则出现数据库选择界面 Dbaccess菜单项查询演示二 查询语言菜单 选择新建菜单后进入dbaccess自有的SQL编辑界面 如果SQL语句较长则选择 使用编辑器 菜单进行SQL输入更为方便 使用编辑器 菜单调用系统编辑器 可通过DBEDIT环境变量进行设置 如果未设置DBEDIT环境变量则会出现编辑器选择界面 一般未认为vi 此界面直接回车就可以了 Dbaccess菜单项查询演示三 选择 新建 菜单后的界面 完成编辑后 按ESC 退回到上级菜单 直接按 键或选择运行菜单后回车 则dbaccess开始检查语法和执行SQL语句 运行 选项可以支持各种数据定义 操作 控制语句以及SPL程序 SELECT语句一 基本功能 简单描述 将数据从数据库的表中选出两个关键字 从 FROM 选出 SELECT 基本语法格式 SELECT 列名 列名 FROM 表名 最基本的SELECT语句只需包含select和from部分举例 从保单基本信息表中选出被保险人名称Select被保险人名称from保单基本信息表Selectinseruednamefromprpcmain要选择表的所有字段就用select from表名的格式 SELECT语句二 SELECT语句通过调整可以完成下列操作 查询所有或指定的列查询所有或指定的行对列或行数据进行计算或其他功能对数据进行排序 使用DISTINCT过滤掉重复数据 功能 在查询结果中去除重复数据语法格式 SELECTDISTINCT 列名 FROM 表名 举例 从保单基本信息表中选出唯一的被保险人名称Selectdistinct被保险人名称from保单基本信息表Selectdistinctinsurednamefromprpcmain说明 UNIQUE的功能和用法与DISTINCT相同 ORDERBY对数据进行排序 功能 以特定顺序对查询出的数据进行排序语法格式 SELECT 列名1 列名2 FROM 表名 ORDERBY 列名 列名2 缺省情况下按升序进行排序 对于字符按从大写A到小写z 对于数值按从小到大 对于日期按从最早到最晚 汉字按汉语拼音首字符的A到z 如果要按降序使用DESC关键字 ORDERBY后可以用SELECT部分字段出现的顺序来替代字段名举例 从保单基本信息表查询被保险人和保费 并按保费排序Selectinsuredname sumprmeiumfromprpcmainorderbysumprmeium也可以写成selectinsuredname sumprmeiumfromprpcmainorderby2按降序Selectinsuredname sumprmeiumfromprpcmainorderbysumprmeiumDESC可以对多列进行排序 如orderbysumprmeium insuredname 使用FIRST选择数据 功能 选择指定数字的前多少行数据语法格式 SELECTFIRST指定数字 FROM 表名 ORDERBY1通常结合ORDERBY一起使用举例 从保单基本信息表中选出保费最大的前100个被保险人名称和保费按降序Selectfirst100insuredname sumprmeiumfromprpcmainwhereorderbysumprmeiumDESC 使用WHERE进行条件查询 功能 根据给定条件过滤出需要的数据语法格式 SELECT 列名 FROM 表名 WHERE 条件 条件的组成 可以使用关键字BETWEEN IN LIKE MATCHES ISNULL来设定条件或在这些关键前加NOT来设定相关的条件 还可以使用条件运算符来设定条件 关系运算符包括 等于 或 不等于 大于 大于等于 10000000从保单基本信息表中选出险种不是DAA的保单Select fromprpcmainwhereriskcode DAA AND和OR 功能 连接多个查询条件语法格式 SELECT 列名 FROM 表名 WHERE 简单条件 AND OR 简单条件 举例 从保单基本信息表中选出投保了车辆险并且保费大于一千万的被保险人名称Select被保险人名称from保单基本信息表where投保了车辆险AND保费大于一千万Selectinsurednamefromprpcmainwhereriskcode DAA ANDsumprmeium 10000000查询险种为DAA或者DAB的保单基本信息表数据Select fromprpcmainwhereriskcode DAA orriskcode DAB IN 功能 替代多个OR条件语法格式 SELECT 列名 FROM 表名 WHERE 列名 In 值1 值2 值3 举例 查询险种为DAA或者DAB或者DAE的保单基本信息表数据Select fromprpcmainwhereriskcodein DAA DAB DAE 查询险种不为DAA或者DAB或者DAE的保单基本信息表数据Select fromprpcmainwhereriskcodenotin DAA DAB DAE BETWEEN 功能 选择取值范围在某一区间内的数据 某种程序上可以替代多个AND条件组成的语句语法格式 SELECT 列名 FROM 表名 WHERE 列名 BETWEEN 值1 AND 值2举例 查询险种为DAA起保日期为2006年10月1日至10月15日之间的保单Select fromprpcmainwherestartdatebetween 20061001 and 20061015 相当于Select fromprpcmainwherestartdate 20061001 andstartdate 20061015 空值 可以用ISNULL或者NOTISNULL来检查空值空值表示没有数据或是未知值 与零或空白不同对于字符型列 空串 和空值是不同的 空串可以用 表示如select fromprpcmainwhereproposalnoisnull和select fromprpcmainwhereproposalno 查询出的结果是不同的注意 在update语句中设某字段为空值要用 如Updateprpcmainsetprintno nullwherepolicyno 12345678 字符串比较 精确匹配可以使用 IN 不加通配符的LIKE或MATCHS等设定查询条件如 select fromprpcmainwhereinsuredname 张三 Select fromprpcmainwhereinsurednamelike 张三 Select fromprpcmainwhereinsurednamematches 张三 Select fromprpcmainwhereriskcodein DAA DAB 模糊查询 如要查询被险人名称字段中含有 出租汽车公司 的保单则可以使用如下查询select fromprpcmainwhereinsurednamelike 出租汽车公司 我们把语句中的 称作通配符 LIKE和MATCHS 两者实现功能相同 都是可以通过通配符来实现模糊查询 LIKE是ANSI标准 MATCHES是INFORMIX扩展 二者对通配符含义的定义不同关键字通配符含义LIKE 零个或多个字符LIKE 单个字符LIKE 转义字符MATCHES 零个或多个字符MATCHES 单个字符MATCHES 单个字符或一定范围内的值MATCHES 转义字符 通配符应用举例 Select fromprpcmainwhereinsurednamelike 张三 Select fromprpcmainwhereinsurednamematches 张三 Select fromprpcmainwhereriskcodelike AA Select fromprpcmainwhereriskcodematches AA Select fromprpcmainwhereriskcodemagches A H 子串操作 如果明确知道在字符串类型字段的第几个字符到第几个字符之间取值时可以用子串形式设定查询条件 又叫下标操作如 查询保单号第二到四位为DAA保单则可以用Select fromprpcmainwherepolicyno 2 4 DAA 在查询列表部分同样可以用子串 如Selectpolicyno 5 8 fromprpcmain 计算列和派生列 可以通过用表达式计算或直接赋值的形式在查询结果中得出查询的表中没有的列如 查询保单基本信息表取出险种为DAA的数据 显示保单号 险种 险种名称 和保费打八折后的值Selectpolicyno riskcode 一般机动车辆保险 sumprmeium 0 8fromprpcmainwhereriskcode DAA 也可以结计算列和派生列起一个列名Selectpolicyno riskcode 一般机动车辆保险 asriskname sumprmeium 0 8asdissumprmeiumfromprpcmainwhereriskcode DAA 可以使用 等运算Selectpolicyno startdate operatedatefromprpcmain显示保单的起保日期和签单日期之间的天数 CASE语句 CASE语句相当于编程语言中的CASE语句的概念 当想要更改数据的表达方式时通常使用如 查询保单基本信息 如果险类代码为D显示 机动车类 如果险类代码为Z显示 责任险类 如果险类代码为Q显示 企财险类 如果险类代码为除此之外的其他代码 显示 其他险类 Selectcasewhenclasscode D then 机动车类 whenclasscode Z then 责任险类 whenclasscode Q then 企财险类 else 其他险类 FromprpcmainSelectcasewhensumprmeium 10000000then 有钱客户 whensumprmeium 100then 没钱客户 else 一般客户 Fromprpcmain 聚集函数和GROUPBY 聚集函数包括SUM COUNT MAX MIN等 通常和分组语句GROUPBY配合使用更有意义 如果不配合GROUPBY语句使用 则结果只显示对整表数据进行计算后的一行数据例如 selectsum sumprmeium fromprpcmain保单基本信息表中所有行的保费合计配合GROUPBY按归属机构计算保费合计 则可以写成Selectcomcode sum sumprmeium fromprpcmaingroupbycomcode或selectcomcode sum sumprmeium groupby1按机构计算保单数量Selectcomcode count fromprpcmaingroupby1 INTOTEMP 可以INTOTEMP子句将查询结果写入临时表中如 Selectcomcode count aspolicycountfromprpcmaingroupby1Intotemptmpt1 计算列或派生列在使用INTOTEMP时需要指定一个列名当SQL会话或者程序结束时临时表会被自动删除 GROUPBY和HAVING 对于使用了GROUPBY进行分组的查询 可以通过结合HAVING子句实现更多过滤条件例如 Selectcomcode count fromprpcmaingroupby1Havingcount 1000按归属机构分组查询保单数量并只显示数量大于1000的机构的数据如果使用INTOTEMP将分组查询结果写入临时表则上面语句等同于Selectcomcode count aspolicycountfromprpcmaingroupby1Intotemptmpt1 Select fromtmpt1wherepolicycount 1000 日期函数 日期函数包括DAY MONTHS YEAR WEEKDAY TODAY CURRENT等DAY 返回日期值中的天MONTHS 返回日期值中的月YEAR 返回日期值中的年WEEKDAY 返回日期值是星期几 0代表是星期天TODAY 返回当前日期CURRENT 返回带时间的当前日期Selectday today months today year today weekday today today currentFromprpcmainwherepolicyno abcdefg 字符串处理函数 字符串处理函数包括LOWER UPPER REPLACE SUBSTR SUBSTRING等LOWER 将大写字母替换成小写字母UPPER 将小写字母替换成大写字母REPLACE 将字符串中的某一组字符替换为其他字符SUBSTR 返回字符串的一部分SUBSTRING 返回字符串的一部分SUBSTR和SUBSTRING语法有些区别 NVL函数 NVL可以将求值为空值的表达式转换成指定值语法 NVL 表达式 表达式 如果表达式 的值为空 则返回表达式 的值 否则返回表达式 的值Selectpolicyno nvl sumprmeium 0 fromprpcmain如果保费为空则返回0 多表查询 要在两个或多个表中进行数据查询 可以在from字句中指定两个或两个以上的表名 并在where子句中在每个表的至少一个字段间创立连接条件 笛卡尔积 笛卡尔积 当进行多表查询没有声明表之间的连接条件时就生成了笛卡尔积 笛卡尔积由表的行的每种可能组合而成可以在示例库stores demo中运行下列语句来查看一下笛卡尔积的查询结果Select fromcustomer stateState表有52行 customer表有28行 查询出的结果是52 28 1456行 等值连接 使用等号或join关键字来连接多表间的具有对应关系的字段来构成查询条件Select fromprpcmain prpcitemkindWhereprpcmain policyno prpcitemkind policyno多表间等值连接Select fromprpcmain prpcitem car prpcitemkindWhereprpcmain policyno prpcitemkind policynoAndprpcmain policyno prpcitem car policyno或Select fromprpcmainjoinprpcitem caronprpcmain policyno prpcitem car policynojoinprpcitemkindonprpcmain policyno prpcitemkind policyno 使用别名 可以通过在from子句中给表指定别名来提高查询速度 并使SQL语句可读性更高 书写更简单Selectt1 policyno t1 riskcode t2 kindcodefromprpcmaint1 prpcitemkindt2Wheret1 policyno t2 policyno指定别名时也可以加as关键字如fromprpcmainast1 prpcitemkindast2 使用自连接 当需要对同一个表的同一个列之间的值进行比较时往往需要用到自连接例如 要查询某支公司投保DAA险种的保单 保费差异大于10000元的保单的情况selectt1 policyno t2 policyno t1 sumpremium t2 sumpremiumfromprpcmaint1 prpcmainT2wheret1 policynot2 policynoandt1 sumpremium t2 sumpremium 10000 使用外连接 外连接和等值连接不同 等值连接只返回相关表中相等的数据 外连接则除返回相等数据之外还会显示出控制表的全部数据 从属表没有关联上的数据则返回NULL 外连接分为左外连接和右外连接外连接关键字 leftjoin rightjoin控制表 以左连接为例控制表为使用leftjoin关键字前的表表 表 等值连接则结果为111 1222 23不等值连接结果则为1 12 23 NULL WHERE条件中使用子查询 可以在WHERE条件中使用查询语句 通常使用的条件关键字包括 0 查询保单PJAZ200454010093000002的赔款是否大于保额Select fromprpcmainwheresumamount selectsum sumpaid fromprplcompensatewherepolicyno PJAZ200454010093000002 andpolicyno PJAZ200454010093000002 使用UNION联合多个查询结果 可以使用UNION关键字将多个返回数据列数和类型相等的查询结果联合起来共同输出Selectstock num manu codefromstockWhereunit price 600UnionallSelectstock num manu codefromcatalogWherecatalog num 10025UnionallSelectstock num manu codefromitemsWherequantity 10Orderby2 UPDATE语句 基本功能 更改表中的一个或多个列的数据基本语法格式 UPDATE 表名 SET 列名 设定值WHERE条件updateprpcmainsetunderwriteflag 0wherepolicyno abcdefg 注意 一般在生产环境下需要谨慎使用UPDATE语句 最好先用SELECT语句设定好WHERE条件并确认条件正确后再将SELECT部分修改成UPDATE语句的格式 INSERT语句 基本功能 向表中插入新行基本语法格式 不用指定列名的形式INSERTINTO表名VALUES 值1 值2 值3 值4 指定列名的形式INSERTINTO表名 列名 列名 列名 VALUES 值1 值2 值3 值来自查询结果INSERTINTO表名 列名 列名 列名 SELECT语句 DELETE语句 基本功能 删除表中数据基本语法格式 DELETEFROM表名WHERE条件 查询优化 查询优化首要任务是减少数据库服务器的I O操作 加上其他技术运用实现最快查询合理使用索引避免或简化排序可以使用等连接的多表查询尽量少用in合理使用临时表有时可以加快查询通过设置脏读也可以提高查询效率设置PDQ充分利用服务器资源数据分片技术 索引一 索引 索引优点 通过使用索引定位取代顺序扫描提高查询速度提高数据排序速度保证被索引字段的唯一性当仅仅查询索引字段时 避免读取记录全部字段内容 索引二 索引策略 对连接 jion 字段建立索引对选择性过虑 selectivefilter 字段建立索引对排序 order 字段建立索引避免对高重复率 highlyduplicate 的字段建立索引利用组合索引 compositeindexs 降低索引重复率建立组合索引时 应该将重复率低的字段放在前面 重复率高的字段放在后面 索引三 索引策略 运用聚集索引 clusteredindex 提高查询速度数字字段的索引查找速度较其他类型字段 如字符串字段等 的索引快 一个数据表的索引不应该过多 索引过多 数据插入 数据删除 数据修改速度一定程度上会影响 利用 部分键查找 partialkeysearch 提高索引利用率 例如 建立在表tab上的一个索引idx f1 f2 f3 f4 当对tab按照 f1 f2 f3 f4 或者按照 f1 f2 f3 或者按