在sysbase中我用程序建表如何建索引？

pan666 2003-01-27 01:53:37
在sysbase中我用程序建表如何建索引？谢谢！
...全文
82 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
wanghai 2003-01-27
  • 打赏
  • 举报
 回复
动态sql
string sql
sql="create index........"
excute immediate sql
大致是这样，查看一下相关资料
xyzhh 2003-01-27
  • 打赏
  • 举报
 回复
ddl sql语句啊，要看你的数据库设置中有没权限了（不过不推荐用程序改结构）
SQL21日自学通
目录1 译者的话 14 第一周概貌 16 从这里开始 16 第一天SQL 简介 17 SQL 简史 17 数据库简史 17 设计数据库的结构21 SQL 总览23 流行的SQL 开发工具 24 SQL 在编程中的应用 27 第二天查询— — SELECT 语句的使用 30 目标 30 背景 30 一般的语法规则 30 你的第一个查询 33 总结 37 问与答 38 校练场 38 练习 39 第三天表达式条件语句与运算 40 第四天函数对数据的进一步处理 60 目标 60 汇总函数 60 COUNT61 SUM 61 AVG 63 MAX 63 MIN 64 VARIANCE65 STDDEV66 日期/时间函数66 ADD_MONTHS 67 LAST_DAY68 MONTHS_BETWEEN 69 NEW_TIME70 NEXT_DAY71 SYSDATE72 数学函数 72 ABS 73 CEIL 和FLOOR 73 COS COSH SIN SINH TAN TANH 73 EXP 75 LN and LOG75 MOD 76 POWER77 SIGN 77 SQRT 78 字符函数 79 CHR 79 CONCAT 79 INITCAP80 LOWER 和UPPER 81 LPAD 与RPAD 82 LTRIM 与RTRIM 83 REPLACE 84 SUBSTR85 TRANSLATE88 INSTR 88 LENGTH89 转换函数 89 TO_CHAR90 TO_NUMBER91 其它函数 91 GREATEST 与LEAST 91 USER 92 总结 92 问与答 93 校练场 93 练习 94 第五天SQL 中的子句95 目标 95 WHERE 子句96 STARTING WITH子句 98 ORDER BY 子句99 GROUP BY 子句104 HAVING 子句109 子句的综合应用112 总结117 问与答117 校练场117 练习118 第六天表的联合119 介绍119 在一个SELECT 语句中使用多个表119 正确地找到列123 等值联合124 不等值联合129 外部联合与内部联合130 表的自我联合132 总结134 问与答134 校练场134 练习135 第七天子查询内嵌的SQL 子句136 目标136 建立一个子查询136 在子查询中使用汇总函数140 子查询的嵌套141 相关子查询144 EXISTS ANY ALL 的使用147 总结151 问与答151 校练场152 练习153 第一周回顾154 预览154 第二周概貌155 这一周都讲些什么155 第八天操作数据156 目标156 数据操作语句156 插入语句157 INSERT VALUES 语句157 INSERT SELECT 语句161 UPDATE语句163 DELETE 语句166 从外部数据源中导入和导出数据169 Microsoft Access 170 Microsoft and Sybase SQL Server 171 Personal Oracle7171 总结172 问与答172 校练场173 练习173 第九天创建和操作表174 目标174 CREATE DATABASE 语句174 建立数据库时的选项175 设计数据库176 建立数据字典176 建立关键字段177 CREATE TABLE 语句178 表名179 FIRST NAME179 空值属性180 唯一属性181 表的存储与尺寸的调整183 用一个已经存在的表来建表184 ALTER TABLE 语句185 DROP TABLE 语句186 DROP DATABASE语句187 总结188 问与答188 校练场189 练习190 第10 天 创建视图和索引191 目标191 使用视图192 列的重命名196 SQL 对视图的处理过程197 在SELECT 语句使用约束201 在视图中修改数据201 在视图中修改数据的几个问题203 通用应用程序的视图204 删除视图语句207 使用索引207 什么是索引207 使用索引的技巧212 对更多的字段进行索引212 在创建索引时使用UNIQUE 关键字214 索引与归并216 群集簇的使用217 总结218 问与答219 校练场219 练习220 第11 天事务处理控制221 目标221 事务控制221 银行应用程序222 开始事务处理223 结束事务处理225 取消事务处理228 在事务中使用保存点231 总结234 问与答234 校练场235 练习235 第12 天数据库安全236 前提数据库管理员236 流行的数据库产品与安全237 如何让一个数据库变得安全237 Personal Oracle7 与安全238 创建用户238 创建角色240 用户权限242 为安全的目的而使用视图247 总结251 问与答252 校练场252 练习253 第13 天 高级SQL254 目标254 临时表254 Title 257 游标259 创建游标260 打开游标260 使用游标来进行翻阅261 测试游标的状态262 关闭游标263 游标的适用范围264 创建和使用存贮过程265 在存贮过程中使用参数267 删除一个存贮过程269 存贮过程的嵌套270 设计和使用触发机制272 触发机制与事务处理273 使用触发机制时的限制275 触发机制的嵌套275 在选择语句中使用更新和删除275 在执行前测试选择语句276 嵌入型SQL277 静态SQL 与动态SQL277 使用SQL 来编程279 总结280 问与答280 校练场280 练习281 第14 天动态使用SQL 282 目标282 快速入门282 ODBC 282 Personal Oracle 7283 InterBase SQL ISQL 283 Visual C++ 284 Delphi284 设置284 创建数据库285 使用MS QUERY 来完成链接290 将VISUAL C++与SQL 结合使用292 将DELPHI 与SQL 结合使用296 总结302 问与答303 校练场303 练习303 第二周回顾304 第三周概貌305 应用你对SQL 的知识305 第15 天对SQL 语句优化以提高其性能306 目标306 让你的SQL 语句更易读307 全表扫描308 加入一个新的索引309 在查询中各个元素的布局309 过程311 避免使用OR311 OLAP 与OLTP 的比较313 OLTP 的调试313 OLAP 的调试314 批量载入与事务处理进程314 删除索引以优化数据的载入316 经常使用COMMIT 来让DBA 走开316 在动态环境中重新生成表和索引317 数据库的调整319 性能的障碍322 内置的调整工具323 总结323 问与答324 校练场324 练习324 第16 天用视图从数据字典中获得信息326 目标326 数据字典简介326 用户的数据字典327 数据字典中的内容327 Oracle 的数据字典328 Sybase 的数据字典328 ORACLE 数据字典的内部结构328 用户视图328 系统数据库管理员视图336 数据库对象339 数据库的生长343 动态执行视图347 总结349 问与答349 校练场350 练习350 第17 天使用SQL 来生成SQL 语句351 目标351 使用SQL 来生成SQL 语句的目的351 几个SQL*PLUS 命令352 SET ECHO ON/OFF353 SET FEEDBACK ON/OFF353 SET HEADING ON/OFF 353 SPOOL FILENAME/OFF353 START FILENAME354 ED FILENAME354 计算所有的表中的行数354 为多个用户赋予系统权限359 将你的表的权限赋予其它的用户361 在载入数据时解除对数的约束363 一次创建多个同义字364 为你的表创建视图368 在一个计划中清除其所有的表的内容369 使用SQL 来生成SHELL 脚本371 再建表和索引372 总结373 问与答373 校练场373 练习374 第18 天PL/SQL 简介376 目标376 入门376 在PL/SQL 中的数据类型377 字符串类型377 数值数据类型378 二进制数据类型378 日期数据类型378 逻辑数据类型378 ROWID379 PL/SQL 块的结构379 注释380 DECLARE 部分380 变量声明380 常量定义381 指针定义381 %TYPE 属性382 %ROWTYPE 属性382 %ROWCOUNT 属性383 Procdure 部分383 BEGIN … … END383 指针控制命令384 条件语句386 LOOPS 循环387 EXCEPTION 部分390 激活EXCEPTION 异常390 异常的处理391 将输入返回给用户392 在PL/SQL 中的事务控制393 让所有的事在一起工作394 示例表及数据394 一个简单的PL/SQL 语句块395 又一个程序398 存储过程包和触发机制403 总结406 问与答407 校练场407 练习407 第19 天TRANSACT-SQL 简介408 目标408 TRANSACT-SQL 概貌408 对ANSI SQL 的扩展408 谁需要使用TRANSACT-SQL409 TRANSACT-SQL 的基本组件409 数据类型409 使用TRANSACT-SQL 来访问数据库411 BASEBALL 数据库411 定义局部变量414 定义全局变量414 使用变量415 PRINT 命令417 流控制417 BEGIN … … END 语句418 IF … … ELSE 语句418 EXIST 条件421 WHILE 循环422 使用WHILE 循环在表中翻阅424 TRANSACT-SQL 中的通配符426 使用COMPUTE 来生成摘要报告426 日期转换427 SQL SERVER 的诊断工具— — SET 命令427 总结428 问与答428 校练场429 练习429 第20 天SQL*PLUS 430 目标430 简介430 SQL*PLUS 缓存430 DESCRIBE 命令435 SHOW 命令436 文件命令438 SAVE GET EDIT 命令438 运行一个文件439 查询的假脱机输出440 SET 命令442 LOGINSQL 文件445 CLEAR 命令446 将你的输出格式化446 TTITLE 与BTITLE446 格式化列COLUMN HEADING FORMAT 447 报表与分类汇总449 BREAK ON449 COMPUTE450 在SQL*PLUS 中使用变量453 DEFINE 454 ACCEPT 455 NEW_VALUE457 DUAL 表458 DECODE 函数459 日期转换462 运行一系列的SQL 文件465 在你的SQL 脚本中加入注释466 高级报表467 总结469 问与答469 校练场469 练习470 第21 天常见的SQL 错误及解决方法471 目标471 介绍471 常见的错误471 Table or View Does Not Exist471 Invalid Username or Password 472 FROM Keyword Not Specified473 Group Function Is Not Allowed Here 474 Invalid Column Name475 Missing Keyword 475 Missing Left Parenthesis 476 Missing Right Parenthesis 477 Missing Comma478 Column Ambiguously Defined 478 Not Enough Arguments for Function480 Not Enough Values481 Integrity Constraint Violated--Parent Key Not Found 482 Oracle Not Available 483 Inserted Value Too Large for Column 483 TNS:listener Could Not Resolve SID Given in Connect Descriptor 484 Insufficient Privileges During Grants484 Escape Character in Your Statement--Invalid Character 485 Cannot Create Operating System File 485 Common Logical Mistakes485 Using Reserved Words in Your SQL statement 486 The Use of DISTINCT When Selecting Multiple Columns487 Dropping an Unqualified Table 487 The Use of Public Synonyms in a Multischema Database488 The Dreaded Cartesian Product 488 Failure to Enforce File System Structure Conventions 489 Allowing Large Tables to Take Default Storage Parameters489 Placing Objects in the System Tablespace490 Failure to Compress Large Backup Files 491 Failure to Budget System Resources 491 Preventing Problems with Your Data491 Searching for Duplicate Records in Your Database491 总结491 校练场492 练习492 第三周回顾494 附件A 在SQL 中的常见术语495 ALTER DATABASE495 ALTER USER495 BEGIN TRANSACTION 495 CLOSE CURSOR495 COMMIT TRANSACTION496 CREATE DATABASE496 CREATE INDEX496 CREATE PROCEDURE496 CREATE TABLE497 CREATE TRIGGER497 CREATE USER497 CREATE VIEW497 DEALLOCATE CURSOR498 DROP DATABASE498 DROP INDEX498 DROP PROCEDURE498 DROP TABLE498 DROP TRIGGER 499 DROP VIEW499 EXECUTE499 FETCH499 FROM499 GRANT500 GROUP BY500 HAVING500 INTERSECT500 ORDER BY500 ROLLBACK TRANSACTION 500 REVOKE500 SELECT501 SET TRANSACTION501 UNION501 WHERE501 *501 附件B 在第14 天中的C++源代码清单502 附件 C 第14 天中的Delphi 源代码清单521 附件D 参考内容524 书524 Developing Sybase Applications 524 Sybase Developer's Guide 524 Microsoft SQL Server 65 Unleashed, 2E 524 Teach Yourself Delphi in 21 Days 524 Delphi Developer's Guide 524 Delphi Programming Unleashed 525 Essential Oracle 72 525 Developing Personal Oracle7 for Windows 95 Applications 525 Teach Yourself C++ Programming in 21 Days 525 Teach Yourself Tansact-SQL in 21 Days 525 Teach Yourself PL/SQL in 21 Days 525 杂志526 DBMS526 Oracle Magazine526 SQL 的互联网资源526 附件E ACSLL 码表527 附件F 问题与练习答案533 第一天SQL 简介533 问题答案533 练习答案533 第二天查询— — SELECT 语句的使用533 问题答案533 练习答案534 第三天表达式条件语句与运算535 问题答案535 练习答案535 第四天函数对获得数据的进一步处理536 问题答案536 练习答案537 第五天SQL 中的子句538 问题答案538 练习答案538 第六天表的联接540 问题答案540 练习答案541 第7 天子查询内嵌的SELECT 语句542 问题答案542 练习答案544 第八天操作数据544 问题答案544 练习答案546 第九天创建和操作表546 问题答案546 练习答案548 第10 天 创建视图和索引549 问题答案549 练习答案550 第11 天事务处理控制550 问题答案550 练习答案551 第12 天数据库安全552 问题答案552 练习答案552 第13 天 高级SQL553 问题答案553 练习答案553 第14 天动态使用SQL 554 问题答案554 练习答案554 第15 天对SQL 语句优化以提高其性能555 问题答案555 练习答案555 第16 天用视图从数据字典中获得信息557 问题答案557 练习答案557 第17 天使用SQL 来生成SQL 语句558 问题答案558 练习答案560 第18 天PL/SQL 简介561 问题答案561 练习答案561 第19 天TRANSACT-SQL 简介562 问题答案562 练习答案562 第20 天SQL*PLUS 563 问题答案563 练习答案563 第21 天常见的SQL 错误及解决方法564 问题答案564 练习答案565
韩顺平oracle学习笔记
韩顺平oracle学习笔记 第0讲:如何学习oracle 一、如何学习oracle Oracle目前最流行的数据库之一，功能强大，性能卓越。学习oracle需要具备一定基础: 1.学习过一门编程语言(如:java ，c) 2.最好学习过一门别的数据库(sql server，mysql ， access) 教程推荐:oracle使用教程， 深入浅出oracle 记住:欲速则不达，做任何事情要遵循他的规律，循序渐进，信心很重要 成为一个oracle高手过程:理解小知识点->做小练习->把小的只是点连成线->做oracle项目->形成只是面->深刻理解 Oracle基础部分:oracle基础使用; oracle用户管理; oracle表管理 Oracle高级部分:oracle表的查询; oracle的函数; oracle数据库管理;oracle 的权角色; pl/sql 编程; 索引，约束和事物。 期望目标: 1 学会安装、启动、卸载oracle 2 使用sql *plus工具 3 掌握oracle用户管理 4 学会在oracle中编写简单的select语句 第1讲:基础语法 内容介绍: 1.为什么学习oracle 2.介绍oracle及其公司的背景 3.学会安装、启动、卸载oracle 4.oracle开发工具 5.Sql*plus的常用命令 6.oracle用户管理 一、主流数据库包括: ?微软:sql server 和 access ?瑞典:mysql AB公司 ?IBM公司:DB2 ?美国sybase公司:sybase ?IBM公司:infromix ?美国oracle公司:oracle(目前最流行的之一) 数据库分类: 小型数据库 中型数据库 大型数据库 mysql Sybase access sql server Oracle informix DB2 负载量在100人内，比如比如在负载日访问量负载可以处理海量数据板，信息系信息留言统。 5000-15000 库 sybasesys和system两个用户 2 说明: 1Sys用户 是 超级用户，具有最高权限，具有sysdba角色，create database? 的权限，默认密码是manager 2System 用户 是 管理操作员，权限也很大，具有sysoper角色，没有create ? database权限，默认密码是 change_on_install 3一般讲，对数据库维护，使用system用户登录就可以了 ? 3.启动oracle 右键单击我的电脑->服务和应用程序:服务->启动OracleServiceMYORA1(MYORA1 是安装oracle时起的名字各有不同)和OracleOracleHome90TNSLlistener 4.卸载oracle 1、先关掉oralce，net stop OracleServiceORCL(ORCL是我的实例名字，换成你的)，或者去我的电脑服务中关闭 2、开始,,程序,,Oracle - oracle的版本号，我的是10ghome,,Oracle Installation Products,, Universal Installer 卸载oracle 3、进注册表，regedit，删除选择 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE下所有的key。HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services、HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\App lication这个里面所有有oracle这个字眼的，删除 4、删除c盘\Program Files\Oracle目录的东西以及oracle安装目录下所有的文件以及文件夹 5、环境变量中删除有关oracle的classpath和path ,, 三、oracle开发工具, Sqlplus是Oracle自带的工具软件，主要用于执行sql语句，pl/sql块。,如何使用:,, 1)在开始，程序，oracle,orachome90，, ,,application,development，sql*plus(这个是在dos下操作), 2)在运行栏中输入:sqlplusw或者sqlplus即可, 3)在开始，程序，oracle,orahome90，, Application,development，sql*plus,worksheet,,4)pl/sql,developer,属于第三方软件，主要用于开发，测试，优化oracle,pl/sql,的 存储过程.如:触发器，此软件oracle不带，需要单独安装。,5)oracle的企业管理器(oem,oracle,enterprise,manager),位置在,,开始，程序，oracle，oracle,oraclehome90，enterprise,manager,console, ,,即可启动oracle的企业管理器，是一个图形界面环境, , , , 四、Sql*plus的常用命令, 3 1.连接命令 (1) conn[ect] 用法:conn 用户名/密码 @ 网络服务名[as sysdba/sysoper](角色登陆)当 用特权用户身份连接时，必须带上as sysdba 或是 as sysoper 例:conn scott/tiger; show user; 显示当前用户 2.断开连接命令 (2) disc[onnect] 说明:该命令用来断开与当前数据库的连接 (3)passw[ord] 说明:该命令用于修改用户名的密码。如果要想修改其他用户的密码，需要用sys/system登陆。 (4) show user 说明:显示当前用户名 (5) exit 说明:该命令会断开与数据库的连接，同时会退出sql*plus 3.文件操作命令 (1) start和@ 说明:运行sql脚本 案例:sql>@ d:\a.sql 或者 sql>start d:a.sql (2) edit 说明:该命令可以编辑指定的sql脚本。 案例:sql>edit d:\a.sql (3) spool 说明:该命令可以将sql*plus屏幕上的内容输出到指定文件中去。 案例:sql>spool d:\b.sql 并输入 sql>spool off 4(交互式命令 (1), 说明:可以替代变量，而该变量在执行时，需要用户输入。如:sql>select * from emp where job=‘&job;’ 输入job是注意大小写 (2) edit 说明:该命令可以编辑指定的sql脚本 案例: sql>edit d:\a.sql 5.显示和设置环境变量 概述:可以用来控制输出的各种格式，set show 如果希望永久的保存相关的设置，可以去修改 glogin.sql脚本 (1)linesize 说明:设置显示行的宽度，默认是80个字符 sql>show linesize sql>set linesize 90 (2)pagesize 说明:设置每页显示的行数目，默认是14，用法和linesize一样，至于其他环境参数的使用也是大同小异 sql>set pagesize 8 实际得到的不是14/8=2页 而是:14/(8-3)=5页 五Oracle用户的管理 4 1.创建用户 概述:在oracle中要创建一个新的用户使用create user语句，一般是具有dba(数据库管理员)的权限才能使用。 create user 用户名 identified by 密码; 2.给用户修改密码 概述:如果给自己修改密码可以直接使用 sql>password 用户名 如果给别人修改密码则需要具有dba的权限，或是拥有alter user的系统权限 sql>alter user 用户名 identified by 新密码 3.删除用户 概述:一般以dba的身份去删除某个用户，如果用其他用户去删除用户则需要具有 drop user的权限。 比如 drop user 用户名【cascade】 注意:在删除用户时，如果要删除的用户已经创建了表，就需要在删除的时候带一个参数cascade 4.用户管理的综合案例 概述:创建的新用户是没有任何权限的，甚至连登陆的数据库的权限都没有，需要为其指定相应的权限。给一个用户赋权限(connect登陆，dba管理员,resource建表)使用命令grant,回收权限使用命令revoke. 为了给讲清楚用户管理，给大家举一个案例。 案例: 新建一个用户lady 并给该用户赋可登陆可创建表 Sql>create user lady identified by lady123(密码) 受登陆权限:，grant connect to lady 受可创建表:grant resource to lady 可以多个同时授权:grant connect，resource to lady; 注意授权用户级别应该为(dba以上sys/system) 还可以使用PL/SQL工具创建:文件->新建->用户 现在我要把scott用户里面的emp表的权限赋给lady用户但lady只能有对emp查询权限: grant select on emp to lady;(授权用户级别应该:授权本身，或者sys/system) lady 查询emp的方式:select * from scott.emp; 案例:同上我想把update的权限也赋给leng ， grant update on emp to leng; 2)我想把emp所有的(增，删，改，查/insert,delete,update,select这四个一起可以用all代替) 操作权限赋给leng grant all on emp to leng;(此时登陆用户为scott) 案例2:这时我想把权限收回来怎么办呢, 这时用关键字revoke(撤回)， revoke select on emp from lady; 收回lady的查权限 案例: 5 现在scott希望吧查询emp表权限通过lady用户继续给别的用户, Scott > grant select on emp to lady with grant option; Leng > grant select on scott.emp to 新建用户 案例: scott想把emp表的权限从leng用户赋权给新用户yoyo; Scott> grant select on emp to leng with grant option; Leng> grant select on scott.emp to yoyo; Yoyo> select * from scott.emp; 如果是对象权限，就加入with grant option 例:grant select on emp to xiaoming with grant option 如果是系统管理权限，system 给xiaoming权限时: 例:grant connect to xiaoming with admin option 如果scott把xiaoming 对emp表的查询权限回收，那么xiaohong会怎么样 Scott->xiaoming->xiaohong ，结果就是xiaoming,xiaohong,都不能查询emp表
PostgreSQL数据库管理（四）
PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES,现在已经更名为PostgreSQL. PostgreSQL支持大部分SQL标准并且提供了许多其它现代特性：复杂查询、外键、触发器、视图、事务完整性等。PostgreSQL 是一个免费的对象-关系数据库服务器(数据库管理系统)，它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如 MySQL 和 Firebird)，和专有系统(比如 Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server)之外的另一种选择。事实上， PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99，首先，它包括了可以说是目前世界上最丰富的数据类型的支持，其中有些数据类型可以说连商业数据库都不具备， 比如 IP 类型和几何类型等；其次，PostgreSQL 是全功能的自由软件数据库，很长时间以来，PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统（MVCC）、数据完整性检查等特性的唯一的一种自由软件的数据库管理系统。 Inprise 的 InterBase 以及SAP等厂商将其原先专有软件开放为自由软件之后才打破了这个唯一。最后，PostgreSQL拥有一支非常活跃的开发队伍，而且在许多黑客的努力下，PostgreSQL 的质量日益提高。从技术角度来讲，PostgreSQL 采用的是比较经典的C/S（client/server）结构，也就是一个客户端对应一个服务器端守护进程的模式，这个守护进程分析客户端来的查询请求，生成规划树，进行数据检索并最终把结果格式化输出后返回给客户端。为了便于客户端的程序的编写，由数据库服务器提供了统一的客户端 C 接口。而不同的客户端接口都是源自这个 C 接口，比如ODBC，JDBC，Python，Perl，Tcl，C/C++，ESQL等， 同时也要指出的是，PostgreSQL 对接口的支持也是非常丰富的，几乎支持所有类型的数据库客户端接口。这一点也可以说是 PostgreSQL 一大优点。本课程作为PostgreSQL数据库管理一，主要讲解以下内容： 1.     PostgreSQL 存储过程基本知识2.     PostgreSQL 用户自定义函数3.     PostgreSQL 控制结构4.     PostgreSQL 游标和存储过程5.     PostgreSQL 索引6.     PostgreSQL 视图7.     PostgreSQL 触发器8.     PostgreSQL 角色、备份和还原9.     PostgreSQL 表空间管理
21天学习SQL V1.0
21天学习SQL V1.0.pdf 66 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 2 日期/时间函数......................................................................................................... 66 ADD_MONTHS................................................................................................ 67 LAST_DAY...................................................................................................... 68 MONTHS_BETWEEN....................................................................................... 69 NEW_TIME...................................................................................................... 70 NEXT_DAY...................................................................................................... 71 SYSDATE......................................................................................................... 72 数学函数................................................................................................................. 72 ABS ................................................................................................................. 73 CEIL 和FLOOR ............................................................................................... 73 COS COSH SIN SINH TAN TANH........................................................ 73 EXP................................................................................................................. 75 LN and LOG..................................................................................................... 75 MOD................................................................................................................ 76 POWER............................................................................................................ 77 SIGN ............................................................................................................... 77 SQRT ............................................................................................................... 78 字符函数................................................................................................................. 79 CHR................................................................................................................. 79 CONCAT .......................................................................................................... 79 INITCAP.......................................................................................................... 80 LOWER 和UPPER ........................................................................................... 81 LPAD 与RPAD................................................................................................. 82 LTRIM 与RTRIM............................................................................................. 83 REPLACE ........................................................................................................ 84 SUBSTR........................................................................................................... 85 TRANSLATE.................................................................................................... 88 INSTR ............................................................................................................. 88 LENGTH.......................................................................................................... 89 转换函数................................................................................................................. 89 TO_CHAR........................................................................................................ 90 TO_NUMBER................................................................................................... 91 其它函数................................................................................................................. 91 GREATEST 与LEAST...................................................................................... 91 USER............................................................................................................... 92 总结........................................................................................................................ 92 问与答.................................................................................................................... 93 校练场.................................................................................................................... 93 练习........................................................................................................................ 94 第五天SQL 中的子句........................................................................................................... 95 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 3 目标.................................................................................................................... 95 WHERE 子句.......................................................................................................... 96 STARTING WITH子句............................................................................................ 98 ORDER BY 子句..................................................................................................... 99 GROUP BY 子句....................................................................................................104 HAVING 子句.........................................................................................................109 子句的综合应用.....................................................................................................112 总结.......................................................................................................................117 问与答...................................................................................................................117 校练场...................................................................................................................117 练习.......................................................................................................................118 第六天表的联合..................................................................................................................119 介绍.......................................................................................................................119 在一个SELECT 语句中使用多个表.........................................................................119 正确地找到列..................................................................................................123 等值联合................................................................................................................124 不等值联合............................................................................................................129 外部联合与内部联合..............................................................................................130 表的自我联合.........................................................................................................132 总结.......................................................................................................................134 问与答...................................................................................................................134 校练场...................................................................................................................134 练习.......................................................................................................................135 第七天子查询内嵌的SQL 子句........................................................................................136 目标...................................................................................................................136 建立一个子查询.....................................................................................................136 在子查询中使用汇总函数.......................................................................................140 子查询的嵌套.........................................................................................................141 相关子查询............................................................................................................144 EXISTS ANY ALL 的使用.................................................................................147 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 4 总结.......................................................................................................................151 问与答...................................................................................................................151 校练场...................................................................................................................152 练习...................................................................................................................153 第一周回顾............................................................................................................................154 预览.......................................................................................................................154 第二周概貌............................................................................................................................155 这一周都讲些什么..................................................................................................155 第八天操作数据..................................................................................................................156 目标.......................................................................................................................156 数据操作语句.........................................................................................................156 插入语句................................................................................................................157 INSERT VALUES 语句.....................................................................................157 INSERT SELECT 语句.....................................................................................161 UPDATE语句........................................................................................................163 DELETE 语句.........................................................................................................166 从外部数据源中导入和导出数据.............................................................................169 Microsoft Access ..............................................................................................170 Microsoft and Sybase SQL Server ......................................................................171 Personal Oracle7...............................................................................................171 总结.......................................................................................................................172 问与答...................................................................................................................172 校练场...................................................................................................................173 练习.......................................................................................................................173 第九天创建和操作表...........................................................................................................174 目标...................................................................................................................174 CREATE DATABASE 语句......................................................................................174 建立数据库时的选项.......................................................................................175 设计数据库.....................................................................................................176 建立数据字典..................................................................................................176 建立关键字段..................................................................................................177 CREATE TABLE 语句.............................................................................................178 表名................................................................................................................179 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 5 FIRST NAME..................................................................................................179 空值属性.........................................................................................................180 唯一属性.........................................................................................................181 表的存储与尺寸的调整....................................................................................183 用一个已经存在的表来建表.............................................................................184 ALTER TABLE 语句...............................................................................................185 DROP TABLE 语句.................................................................................................186 DROP DATABASE语句..........................................................................................187 总结.......................................................................................................................188 问与答...................................................................................................................188 校练场...................................................................................................................189 练习.......................................................................................................................190 第10 天创建视图和索引.......................................................................................................191 目标.......................................................................................................................191 使用视图................................................................................................................192 列的重命名.....................................................................................................196 SQL 对视图的处理过程...................................................................................197 在SELECT 语句使用约束................................................................................201 在视图中修改数据...........................................................................................201 在视图中修改数据的几个问题.........................................................................203 通用应用程序的视图.......................................................................................204 删除视图语句..................................................................................................207 使用索引................................................................................................................207 什么是索引..................................................................................................207 使用索引的技巧..............................................................................................212 对更多的字段进行索引....................................................................................212 在创建索引时使用UNIQUE 关键字.................................................................214 索引与归并.....................................................................................................216 群集簇的使用...........................................................................................217 总结.......................................................................................................................218 问与答................................................................................................................219 校练场...................................................................................................................219 练习...................................................................................................................220 第11 天事务处理控制.........................................................................................................221 目标...................................................................................................................221 事务控制................................................................................................................221 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 6 银行应用程序..................................................................................................222 开始事务处理..................................................................................................223 结束事务处理..................................................................................................225 取消事务处理..................................................................................................228 在事务中使用保存点.......................................................................................231 总结.......................................................................................................................234 问与答...................................................................................................................234 校练场...................................................................................................................235 练习.......................................................................................................................235 第12 天数据库安全............................................................................................................236 前提数据库管理员..............................................................................................236 流行的数据库产品与安全.......................................................................................237 如何让一个数据库变得安全....................................................................................237 Personal Oracle7 与安全..........................................................................................238 创建用户.........................................................................................................238 创建角色.........................................................................................................240 用户权限.........................................................................................................242 为安全的目的而使用视图................................................................................247 总结.......................................................................................................................251 问与答...................................................................................................................252 校练场...................................................................................................................252 练习.......................................................................................................................253 第13 天高级 SQL.................................................................................................................254 目标...................................................................................................................254 临时表...................................................................................................................254 Title ................................................................................................................257 游标.......................................................................................................................259 创建游标.........................................................................................................260 打开游标.........................................................................................................260 使用游标来进行翻阅.......................................................................................261 测试游标的状态..............................................................................................262 关闭游标.........................................................................................................263 游标的适用范围..............................................................................................264 创建和使用存贮过程..............................................................................................265 在存贮过程中使用参数....................................................................................267 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 7 删除一个存贮过程...........................................................................................269 存贮过程的嵌套..............................................................................................270 设计和使用触发机制..............................................................................................272 触发机制与事务处理.......................................................................................273 使用触发机制时的限制....................................................................................275 触发机制的嵌套..............................................................................................275 在选择语句中使用更新和删除................................................................................275 在执行前测试选择语句....................................................................................276 嵌入型SQL............................................................................................................277 静态SQL 与动态SQL.....................................................................................277 使用SQL 来编程....................................................................................................279 总结.......................................................................................................................280 问与答...................................................................................................................280 校练场...................................................................................................................280 练习.......................................................................................................................281 第14 天动态使用SQL ........................................................................................................282 目标.......................................................................................................................282 快速入门................................................................................................................282 ODBC .............................................................................................................282 Personal Oracle 7..............................................................................................283 InterBase SQL ISQL ...................................................................................283 Visual C++ ......................................................................................................284 Delphi.............................................................................................................284 设置.......................................................................................................................284 创建数据库............................................................................................................285 使用MS QUERY 来完成链接..................................................................................290 将VISUAL C++与SQL 结合使用...........................................................................292 将DELPHI 与SQL 结合使用..................................................................................296 总结.......................................................................................................................302 问与答...................................................................................................................303 校练场...................................................................................................................303 练习.......................................................................................................................303 第二周回顾............................................................................................................................304 第三周概貌............................................................................................................................305 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 8 应用你对SQL 的知识.............................................................................................305 第15 天对SQL 语句优化以提高其性能...............................................................................306 目标.......................................................................................................................306 让你的SQL 语句更易读.........................................................................................307 全表扫描................................................................................................................308 加入一个新的索引..................................................................................................309 在查询中各个元素的布局.......................................................................................309 过程................................................................................................................311 避免使用OR...................................................................................................311 OLAP 与OLTP 的比较...........................................................................................313 OLTP 的调试...................................................................................................313 OLAP 的调试..................................................................................................314 批量载入与事务处理进程.......................................................................................314 删除索引以优化数据的载入....................................................................................316 经常使用COMMIT 来让DBA 走开........................................................................316 在动态环境中重新生成表和索引.............................................................................317 数据库的调整.........................................................................................................319 性能的障碍............................................................................................................322 内置的调整工具.....................................................................................................323 总结.......................................................................................................................323 问与答...................................................................................................................324 校练场...................................................................................................................324 练习.......................................................................................................................324 第16 天用视图从数据字典中获得信息................................................................................326 目标.......................................................................................................................326 数据字典简介.........................................................................................................326 用户的数据字典.....................................................................................................327 数据字典中的内容..................................................................................................327 Oracle 的数据字典...........................................................................................328 Sybase 的数据字典..........................................................................................328 ORACLE 数据字典的内部结构...............................................................................328 用户视图.........................................................................................................328 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 9 系统数据库管理员视图....................................................................................336 数据库对象.....................................................................................................339 数据库的生长..................................................................................................343 动态执行视图..................................................................................................347 总结.......................................................................................................................349 问与答...................................................................................................................349 校练场...................................................................................................................350 练习.......................................................................................................................350 第17 天使用SQL 来生成SQL 语句....................................................................................351 目标.......................................................................................................................351 使用SQL 来生成SQL 语句的目的..........................................................................351 几个SQL*PLUS 命令.............................................................................................352 SET ECHO ON/OFF.........................................................................................353 SET FEEDBACK ON/OFF................................................................................353 SET HEADING ON/OFF ..................................................................................353 SPOOL FILENAME/OFF..................................................................................353 START FILENAME..........................................................................................354 ED FILENAME................................................................................................354 计算所有的表中的行数...........................................................................................354 为多个用户赋予系统权限.......................................................................................359 将你的表的权限赋予其它的用户.............................................................................361 在载入数据时解除对数的约束................................................................................363 一次创建多个同义字..............................................................................................364 为你的表创建视图..................................................................................................368 在一个计划中清除其所有的表的内容......................................................................369 使用SQL 来生成SHELL 脚本................................................................................371 再建表和索引.........................................................................................................372 总结.......................................................................................................................373 问与答...................................................................................................................373 校练场...................................................................................................................373 练习.......................................................................................................................374 第18 天PL/SQL 简介..........................................................................................................376 目标.......................................................................................................................376 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 10 入门.......................................................................................................................376 在PL/SQL 中的数据类型........................................................................................377 字符串类型.....................................................................................................377 数值数据类型..................................................................................................378 二进制数据类型.....................................................................................................378 日期数据类型.........................................................................................................378 逻辑数据类型.........................................................................................................378 ROWID...........................................................................................................379 PL/SQL 块的结构...................................................................................................379 注释.......................................................................................................................380 DECLARE 部分......................................................................................................380 变量声明.........................................................................................................380 常量定义.........................................................................................................381 指针定义.........................................................................................................381 %TYPE 属性...................................................................................................382 %ROWTYPE 属性...........................................................................................382 %ROWCOUNT 属性........................................................................................383 Procdure 部分.........................................................................................................383 BEGIN … … END..............................................................................................383 指针控制命令..................................................................................................384 条件语句.........................................................................................................386 LOOPS 循环....................................................................................................387 EXCEPTION 部分..................................................................................................390 激活EXCEPTION 异常.............................................................................390 异常的处理.....................................................................................................391 将输入返回给用户..................................................................................................392 在PL/SQL 中的事务控制........................................................................................393 让所有的事在一起工作....................................................................................394 示例表及数据..................................................................................................394 一个简单的PL/SQL 语句块.............................................................................395 又一个程序.....................................................................................................398 存储过程包和触发机制.......................................................................................403 总结.......................................................................................................................406 问与答...................................................................................................................407 校练场...................................................................................................................407 练习.......................................................................................................................407 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 11 第19 天TRANSACT-SQL 简介............................................................................................408 目标.......................................................................................................................408 TRANSACT-SQL 概貌............................................................................................408 对ANSI SQL 的扩展..............................................................................................408 谁需要使用TRANSACT-SQL..........................................................................409 TRANSACT-SQL 的基本组件..........................................................................409 数据类型................................................................................................................409 使用TRANSACT-SQL 来访问数据库......................................................................411 BASEBALL 数据库.........................................................................................411 定义局部变量..................................................................................................414 定义全局变量..................................................................................................414 使用变量.........................................................................................................415 PRINT 命令....................................................................................................417 流控制...................................................................................................................417 BEGIN … … END 语句......................................................................................418 IF … … ELSE 语句............................................................................................418 EXIST 条件.....................................................................................................421 WHILE 循环....................................................................................................422 使用WHILE 循环在表中翻阅..........................................................................424 TRANSACT-SQL 中的通配符.................................................................................426 使用COMPUTE 来生成摘要报告............................................................................426 日期转换................................................................................................................427 SQL SERVER 的诊断工具— — SET 命令..................................................................427 总结.......................................................................................................................428 问与答...................................................................................................................428 校练场...................................................................................................................429 练习.......................................................................................................................429 第20 天SQL*PLUS .............................................................................................................430 目标.......................................................................................................................430 简介.......................................................................................................................430 SQL*PLUS 缓存.....................................................................................................430 DESCRIBE 命令.....................................................................................................435 SHOW 命令............................................................................................................436 文件命令................................................................................................................438 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 12 SAVE GET EDIT 命令................................................................................438 运行一个文件..................................................................................................439 查询的假脱机输出...........................................................................................440 SET 命令...............................................................................................................442 LOGIN.SQL 文件...................................................................................................445 CLEAR 命令..........................................................................................................446 将你的输出格式化..................................................................................................446 TTITLE 与BTITLE..........................................................................................446 格式化列COLUMN HEADING FORMAT ..............................................447 报表与分类汇总.....................................................................................................449 BREAK ON.....................................................................................................449 COMPUTE......................................................................................................450 在SQL*PLUS 中使用变量......................................................................................453 DEFINE ..........................................................................................................454 ACCEPT .........................................................................................................455 NEW_VALUE..................................................................................................457 DUAL 表........................................................................................................458 DECODE 函数.................................................................................................459 日期转换................................................................................................................462 运行一系列的SQL 文件.........................................................................................465 在你的SQL 脚本中加入注释..................................................................................466 高级报表................................................................................................................467 总结.......................................................................................................................469 问与答...................................................................................................................469 校练场...................................................................................................................469 练习.......................................................................................................................470 第21 天常见的SQL 错误及解决方法..................................................................................471 目标...................................................................................................................471 介绍.......................................................................................................................471 常见的错误............................................................................................................471 Table or View Does Not Exist.............................................................................471 Invalid Username or Password ...........................................................................472 FROM Keyword Not Specified...........................................................................473 Group Function Is Not Allowed Here ..................................................................474 Invalid Column Name........................................................................................475 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 13 Missing Keyword .............................................................................................475 Missing Left Parenthesis ....................................................................................476 Missing Right Parenthesis ..................................................................................477 Missing Comma................................................................................................478 Column Ambiguously Defined ...........................................................................478 Not Enough Arguments for Function...................................................................480 Not Enough Values............................................................................................481 Integrity Constraint Violated--Parent Key Not Found ............................................482 Oracle Not Available .........................................................................................483 Inserted Value Too Large for Column ..................................................................483 TNS:listener Could Not Resolve SID Given in Connect Descriptor .........................484 Insufficient Privileges During Grants...................................................................484 Escape Character in Your Statement--Invalid Character .........................................485 Cannot Create Operating System File ..................................................................485 Common Logical Mistakes.................................................................................485 Using Reserved Words in Your SQL statement .....................................................486 The Use of DISTINCT When Selecting Multiple Columns.....................................487 Dropping an Unqualified Table ...........................................................................487 The Use of Public Synonyms in a Multischema Database.......................................488 The Dreaded Cartesian Product ..........................................................................488 Failure to Enforce File System Structure Conventions ...........................................489 Allowing Large Tables to Take Default Storage Parameters....................................489 Placing Objects in the System Tablespace............................................................490 Failure to Compress Large Backup Files ..............................................................491 Failure to Budget System Resources ...................................................................491 Preventing Problems with Your Data...................................................................491 Searching for Duplicate Records in Your Database................................................491 总结...................................................................................................................491 校练场...................................................................................................................492 练习.......................................................................................................................492 第三周回顾............................................................................................................................494 附件A 在SQL 中的常见术语...............................................................................................495 ALTER DATABASE.........................................................................................495 ALTER USER..................................................................................................495 BEGIN TRANSACTION ..................................................................................495 CLOSE CURSOR.............................................................................................495 COMMIT TRANSACTION...............................................................................496 CREATE DATABASE.......................................................................................496 CREATE INDEX..............................................................................................496 CREATE PROCEDURE....................................................................................496 CREATE TABLE..............................................................................................497 CREATE TRIGGER..........................................................................................497 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 14 CREATE USER................................................................................................497 CREATE VIEW................................................................................................497 DEALLOCATE CURSOR.................................................................................498 DROP DATABASE...........................................................................................498 DROP INDEX..................................................................................................498 DROP PROCEDURE........................................................................................498 DROP TABLE..................................................................................................498 DROP TRIGGER .............................................................................................499 DROP VIEW....................................................................................................499 EXECUTE.......................................................................................................499 FETCH............................................................................................................499 FROM.............................................................................................................499 GRANT...........................................................................................................500 GROUP BY.....................................................................................................500 HAVING..........................................................................................................500 INTERSECT....................................................................................................500 ORDER BY.....................................................................................................500 ROLLBACK TRANSACTION ..........................................................................500 REVOKE.........................................................................................................500 SELECT..........................................................................................................501 SET TRANSACTION.......................................................................................501 UNION............................................................................................................501 WHERE..........................................................................................................501 *.....................................................................................................................501 附件B 在第14 天中的C++源代码清单................................................................................502 附件 C 第14 天中的Delphi 源代码清单...............................................................................521 附件D 参考内容..................................................................................................................524 书..........................................................................................................................524 Developing Sybase Applications ..................................................................524 Sybase Developer's Guide ...........................................................................524 Microsoft SQL Server 6.5 Unleashed, 2E ......................................................524 Teach Yourself Delphi in 21 Days ................................................................524 Delphi Developer's Guide ...........................................................................524 Delphi Programming Unleashed ..................................................................525 Essential Oracle 7.2 ...................................................................................525 Developing Personal Oracle7 for Windows 95 Applications ............................525 Teach Yourself C++ Programming in 21 Days ...............................................525 Teach Yourself Tansact-SQL in 21 Days .......................................................525 Teach Yourself PL/SQL in 21 Days ............................................................525 杂志...................................................................................................................526 DBMS.............................................................................................................526 Oracle Magazine...............................................................................................526 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 15 SQL 的互联网资源.................................................................................................526 附件E ACSLL 码表.............................................................................................................527 附件F 问题与练习答案........................................................................................................533 第一天SQL 简介.................................................................................................533 问题答案.........................................................................................................533 练习答案.........................................................................................................533 第二天查询— — SELECT 语句的使用..................................................................533 问题答案.........................................................................................................533 练习答案.........................................................................................................534 第三天表达式条件语句与运算.........................................................................535 问题答案.........................................................................................................535 练习答案.........................................................................................................535 第四天函数对获得数据的进一步处理...............................................................536 问题答案.........................................................................................................536 练习答案.........................................................................................................537 第五天SQL 中的子句..........................................................................................538 问题答案.........................................................................................................538 练习答案.........................................................................................................538 第六天表的联接..................................................................................................540 问题答案.........................................................................................................540 练习答案.........................................................................................................541 第7 天子查询内嵌的SELECT 语句............................................................542 问题答案.........................................................................................................542 练习答案.........................................................................................................544 第八天操作数据..................................................................................................544 问题答案.........................................................................................................544 练习答案.........................................................................................................546 第九天创建和操作表...........................................................................................546 问题答案.........................................................................................................546 练习答案.........................................................................................................548 第10 天创建视图和索引.......................................................................................549 问题答案.........................................................................................................549 练习答案.....................................................................................................550 第11 天事务处理控制.........................................................................................550 问题答案.........................................................................................................550 练习答案.........................................................................................................551 第12 天数据库安全............................................................................................552 SQL 21 日自学通(V1.0) 翻译人笨猪 EMAIL wyhsillypig@163.com 16 问题答案.........................................................................................................552 练习答案.........................................................................................................552 第13 天高级 SQL.................................................................................................553 问题答案.........................................................................................................553 练习答案.........................................................................................................553 第14 天动态使用SQL ........................................................................................554 问题答案.........................................................................................................554 练习答案.........................................................................................................554 第15 天对SQL 语句优化以提高其性能...............................................................555 问题答案.........................................................................................................555 练习答案.........................................................................................................555 第16 天用视图从数据字典中获得信息................................................................557 问题答案.........................................................................................................557 练习答案.........................................................................................................557 第17 天使用SQL 来生成SQL 语句....................................................................558 问题答案.........................................................................................................558 练习答案.........................................................................................................560 第18 天PL/SQL 简介..........................................................................................561 问题答案.........................................................................................................561 练习答案.........................................................................................................561 第19 天TRANSACT-SQL 简介............................................................................562 问题答案.........................................................................................................562 练习答案.........................................................................................................562 第20 天SQL*PLUS .............................................................................................563 问题答案.........................................................................................................563 练习答案.........................................................................................................563 第21 天常见的SQL 错误及解决方法..................................................................564 问题答案.........................................................................................................564 练习答案.........................................................................................................
Sybase 索引的使用和优化
Sybase 索引的使用和优化 word版本下载 在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度也成为广大数据库用户所接受的优化方法。在良好的数据库设计基础上，能有效地使用索引是SQLServer取得高性能的基础，SQLServer采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查
Sybase

2,596

社区成员

25,614

社区内容

发帖
与我相关
我的任务
社区描述
Sybase相关技术讨论区
社区管理员
  • Sybase社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章