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 练习答案.........................................................................................................