SQL语言基础.ppt

SQL语言基础DDL和DML是什么以及使用它们的实例。怎样注册到SQL*Plus。最常见的参数设置。怎样从数据库中检索数据。怎样使用SQL*Plus格式化数据。怎样更新和删除数据库中的数据。怎样创建表和将数据插入表中。怎样变更表SQL语言oSQL语句有两大类：DDL数据定义语言和DML数据操纵语言。oDDL(DataDefinitionLanguage)数据定义语言是SQL中定义数据库中数据结构的语言。使用DDL定义数据结构时，将在Oracle的数据字典中生成相应的数据项。常见的DDL关键字是create、revoke、grant和alter。oDML(DataManipulationLanguage)是数据操纵语言，在数据库中用来操纵数据(而非定义数据，定义数据由DDL完成)。常见的DML关键字为select、insert、update和delete。DDLo数据定义语言使用户能完成下列任务：n创建数据库对象。n删除数据库对象。n更改数据库对象。n为数据库对象授权。n回收已授给数据库对象的权限。当用户执行DDL语句时，在每一条DDL语句执行前后，Oracle都将提交当前的事务，理解这一点很重要。因此，如果用户使用insert命令插入记录到数据库中后，执行了一条DDL语句，如create table，此时来自insert命令的数据将被提交到数据库。DDL数据定义语言是一组SQL命令，用于创建和定义数据库对象，并且将对这些对象的定义保存在数据字典中。部分DDL语句列表SQL命令功能Alterprocedure重编译存储过程Altertable增加表列、重定义表列、更改存储分配analyze收集数据库对象的性能统计值并送入基于代价的优化器Altertableaddconstraint在已有的表上增加约束Createtable创建表Createindex创建索引Dropindex删除索引Droptable删除表grant将权限或角色授予用户或其他角色truncate删除表中所有行revoke从用户或数据库角色回收权限DMLo数据操纵语言允许用户对数据库中的数据进行insert、update、delete和select等操作。正如名字所示，用户可以通过DML语句来操纵数据库中的数据内容。部分DML语句列表SQL命令功能insert增加数据行到表delete从表中删除数据行update更改表中数据select从表或视图中检索数据行Commitwork把当前事务所做的更改永久化(写入磁盘)rollback作废上次提交以来的所有更改SQL*Plus入门o1)从开始菜单，单击programs|Oracle-Ora81|ApplicationDevelopment|SQLPlus。在connect对话框中按提示输入用户名和口令。o注意因为用Ora81作为ORACLE_HOME，所以开始菜单的选项为Oracle_Ora81;如果用户设置了不同的ORACLE_HOME，开始菜单的选项就有可能不同。o2)从DOS窗进入：o输入命令sqlplus polly/gone，Oracle8i将打开一个单独的DOS窗口。登录o如果在登录到SQL*Plus时只提供用户名，即使用命令sqlplus polly，那么将被提示输入口令。o如果在登录到SQL*Plus时什么也不提供，即使用命令sqlplus，那么将被提示输入用户名和口令。o在进入SQL*Plus后，就会看到SQL*Plus的提示符SQL。创建语句o由于创建数据库对象的工作是由DDL语句来完成的，所以建立数据库应用总是从DDL语句开始的。首先，创建四个表：Customer、state、X和Y。示例o注意在上面的清单中，SQL提示符以下的数字无需用户输入。当用户按回车键进入下一行时，SQL*Plus会自动加入这些数字。需用一对圆括号括起表中各个列的定义。数据类型o上述create table语句中，有几项是必需的。用户不仅要给出表的名称(如customer)，还必须列出表中所有的列或域(如last_name、stat_cd以及sales)。此外，还要定义数据库表中各个列或域的数据类型。例如，表列Sales中存放数字型信息。Oracle数据库支持多种不同类型的数据。数据类型说明char(size)存放定长字符数据，最长2000个字符nchar(size)与char型类似，不过其最大长度由数据库所用的字符集(如EasternEuropean、Korean或AmericanEnglish)决定varchar2(size)存放可变长字符数据，最大长度为4000字符nvarchar(size)类似于varchar2Varchar目前等同于charnumber(l,d)存放数值型数据，l代表总位数，d代表小数点后位数Blob二进制大对象，其最大长度为4GBraw(size)纯(Raw)二进制数据，最大长度为2000字节Date存放日期，范围从公元前4712年的1月1日到公元后9999年的12月31日Long存放可变长字符数据，最大为2GB(gigabytes)提示o用长度和小数点后的位数定义数值型表列时，长度定义十进制数字个数，其中包括整数部分和小数部分数字的位数。例如：number(4，2)，定义的最大数值为99.99。o切记，Oracle数据库是由表的集合所组成的，而各表中的表列或字段定义了该表。这些表列或字段都有一个属性，属性表明可保存何种数据。数据库系统根据数据类型决定对其内容的操作，这些操作都有相应的函数，如“处理数字型数据的函数”、“处理字符型数据的函数”以及“处理日期型数据的函数”等等。例如：Oracle可对数值型数据进行加、减乘、除操作。后面将顺序介绍。空值与非空值(Null和Not Null)o进一步考察创建customer表的语句，用户会看到last_name表列后跟一个限定符“not null”，这表示数据库不允许没有此表列数据的数据行被添加到customer表中。换句话说，使用了限定符not null的表列是强制性字段，在表customer和state中，如果想要在该表中插入一行，那么last_name和state_cd表列(字段)必须含有值。o提示：not null(非空值)可用词mandatory(强制性)理解，即非空表列中的数据绝不能为空值。空值(Null Value)o空值(Null value)是指不含数据的表列。可以将null value理解为长度为0的字符串。很多时候若不知道某表列的数据类型或数据值则可以给它赋一空值。但人们最容易犯的一个错误就是将空值加载到一个数值型表列中，由于“1+null=null”！因此，如果用户偶然将空值加载至数值型域中，那么产生的统计报表肯定不正确。o提示：不要用null表示数值型域中的0，如果要对数值表列进行算术运算，那么该空值一定要用0来代替。描述语句oOracle对SQL最重要的改进之一就是增加了描述(describe)命令。使用describe命令，用户可以快速掌握表及其中所有表列的概要。在下一章中，将介绍存储的PL/SQL包和存储过程。odescribe命令可以列出存储的PL/SQL包中所有的过程和这些过程所使用的输入/输出参数。如命令describe customer得出下列有关客户表输出：oNameNull?Typeo-oLast_namenotnullVARCHAR2(50)oState_cdnotnullCHAR(2)oSalesNUMBER插入语句o现在我们已建立了一些表，下面将从customer表开始，介绍DML语句的使用方法。语句Insert into customer values(Teplow,MA,23445.67)；以及语句insert into customer values(Abbev,GA,6969.96)；在Customer表中建立了两行。每条insert语句执行后，Oracle以下述信息响应insert命令建立的每行信息：o1rowcreated.o每次成功地完成一条insert语句后，均返回行创建信息，该信息通知用户已建立的行数。多条INSERT的执行插入指定列o这些命令中，将指定存放数据的表列。这在处理非常大的表时很有用，因为用户可能没有指定该表中每一表列的数据。查询语句o查询(select)命令用于从Oracle数据库中检索数据。用户利用select命令告诉数据库要检索什么样的信息。select是用户最常用的SQL语句，select命令由四个基本部分构成：o1)select后跟用户需要检索的信息(如下一部分将要提到的表中表列的名字)，这是select命令必不可少的部分。o2)from后跟检索对象(如存放数据的一个或多个表的名称)，from部分也是必不可少的。o3)where后跟检索条件(如限制检索内容的条件)，where部分是可选的。o4)order by后跟分类准则(如控制数据如何给出的第一部分的表列名称表)，order by部分是可选的。视图o视图可看作一个或多个表的子集，是特殊的数据库对象，用于限制对表中指定的表列或数据行的访问。处理视图与处理表类似，在大多数情况下，也可以对视图进行update或insert操作。可用视图来限制部门经理，使他只能了解其部门内员工薪水情况。这是为什么要用视图的一个极好的例子。o提示：视图是一个特殊的数据库对象，可用来创建限制访问表中特定的表列或数据行。o选择指定的列与前一节中使用星号不同，可以在select关键字后跟一个或多个表列名。星号表示Oracle显示表中的所有表列where子句o如果用户只想看特定的数据行，怎么办呢？这就需要用where子句来解决了。例如：我们想要查看state_cd值为MA的所有用户，可以用命令oSelect last_name,state_cd,sales from customer where state_cd=MA;Orderby子句o执行命令select*from customer order by last_name；查询结果将按升序排列；若执行命令select*from customer order by state_cd desc，last_name；则进行多级排序。该命令首先按state_cd的降序进行排序，在state_cd相同的情况下，再按照last_name的升序排列客户。o提示:在order by子句中未指定升序或降序时，Oracle按升序排列。数值型数据o用户可以执行所有标准的算术运算：加、减、乘、除。在Oracle中，除了这些标准运算符外，还有许多函数函数o函数在SQL语句中用于处理表列内容。在SQL语句中使用函数时，被函数作用的表列的值将发生改变，并将在结果中返回改变后的值。显示数值表列的绝对值是使用函数的一个很好的例子，值为-321的表列的绝对值为321。在SQL*Plus中，求绝对值的表达式由关键字“abs”后跟一对括号以及括号中的表列名称三部分组成，如abs(ytd_sales)。因此SQL语句oselect abs(ytd_sales)from customer；o将显示值321，而不论ytd_sales表列中的数据为-321或是+321。数值函数函数返回值样例显示ceil(n)大于等于数值n的最小整数selectceil(10.6)fromdual；11floor(n)小于等于数值n的最大整数selectfloor(10.6)fromdual；10mod(m,n)m除以n的余数，若n=0，则返回mselectmod(7,5)fromdual；2power(m,n)m的n次方selectpower(3,2)fromdual；9round(n,m)将n四舍五入，保留小数点后m位selectround(1234.5678,2)fromdual；1234.57sign(n)若n=0，返回0；否则n0，返回1;n0,返回-1selectsign(12)fromdual；1sqrt(n)n的平方根selectsqrt(25)fromdual；5字符型数据o字符型数据是create table语句中定义为char、varchar或varchar2的域中存放的数据。字符型数据可以表示所有的字符、数字或可以从键盘输入的特殊字符。Oracle8i中有一系列的用于处理字符型数据的函数。字符函数函数返回值样例显示initcap(char)把每个字符串的第一个字符换成大写Selectinitcap(mr.teplow)fromdual；Mr.Teplowlower(char)整个字符串换成小写Selectlower(Mr.TterryBeaker)fromdual；mr.terrybeakerreplace(char,str1,str2)字符串中所有str1换成str2selectreplace(Scott,S,Boy)fromdual；Boycottsoundex(char)字符串的语音表示，常用于名字的模糊查询，可查找发音相似拼写不同的字符串selectlast_namefromemployeewheresoundex(last_name)=soundex(SMYTHE)；Abbeysubstr(char,m,n)取出从m字符开始的n个字符的子串selectsubstr(ABCDEF,2,1)fromdual；CDlength(char)求字符串的长度selectlength(Anderson)fromdual；8并置运算符o在联结两个字符域时，它非常有用。虽然我们将其列为函数，但它实际上是个运算符。用两竖线“|”表示并置运算符。执行语句oselect ABC|DEF from dual;o返回文本ABCDEF。若某行的last_name值为John，执行语句oselect Dear|last_name|:from customer；o将返回文本“DearJohn:”日期型数据o日期型数据是Oracle数据库中第三种常见的数据类型。在建立customer表时，可以很容易地增加一个名为sale_date的日期域，如下所示：create table customer(sale_date date);o在Oracle中，日期型数据实际含有两个值：日期和时间。由于Oracle总是将日期和时间存放在一起，因此在比较两个日期时要加以注意。Oracle中日期的缺省格式为：DD-MON-YY，oDD代表日，MON代表月，YY代表以两位数字表示的年。o注意：Oracle提供了一种特殊的世纪日期格式标记DD-MON-RR，它是用来在输入两字符的年份时维护世纪数字的。详细内容请参阅Oracle8i Server SQL Reference。o提示：为保证进入21世纪不出问题，请尽可能地使用四位数字的年份DD-MON-YYYY日期型函数函数返回值样例显示Sysdate当前日期和时间Selectsysdatefromdual；28-FEB-02February 28,2002Last_day本月最后一天Selectlast_day(sysdate)fromdual；31-MAR-02March 12,2002Add_months(d,n)当前日期d后推n个月Selectadd_months(sysdate,2)fromdual；18-MAY-02March 18,2002Months_between(f,s)日期f和s间相差月数Selectmonths_between(sysdate,12-MAR-02)fromdual；13April 2003Next_day(d,day)d后第一周指定的day日期Selecnext_day(sysdate,Monday)fromdual；03-JAN-02December30,2001特殊格式的日期型数据续o提示:注意不要将MM格式用于分钟(分钟应该使用MI)。MM是用于月份的格式，将它用于分钟也能工作，但结果是错误的。日期运算o当用户开始使用日期型数据时，也许会提出这样的问题：“在多少天前刚加过油？”，日期型运算使用户能得到这个答案。当给日期表列数据加2时，Oracle知道用户的意思是增加两天。假定表列sale_date的值为03-MAR-98，在此情况下，SQL语句select sale_date+10 from customer；将返回13-MAR-02。下面还要介绍两个更具典型意义的例子。o提示：日期运算在SQL*Plus中是最容易出错的，因此Oracle制定了严格的日期格式规则，以确定允许执行何种运算。Oracle特别注意月和年的界限问题。如语句select to_char(sysdate+14)from dual；将在原值23-DEC-2002上加14天，得出06-JAN-03。表列数据类型转换o用户常常会用遇到需要将数据表列从一种类型转换为另一种类型的情况(如数值型转换成o日期型，字符型转换成数值型)。Oracle提供三种主要的转换函数。nTo_char将任意类型的数据转换成字符类型。语句select to_char(8897)from dual；返回一个包含字符串8897的字符类型的数据。nTo_number将一组合法的数字字符串(如字符数据8897)转换成数值。语句select to_number(8897)from dual；返回一个包括数值8897的数值类型的数据。续nTo_date将适合格式的字符串数据转换成日期型数据，这是最容易产生错误的转换。因为12-DEC-02是合法的日期格式，语句select to_date(12-DEC-02)from dual；能成功地进行数据转换。而语句select to_date(bad date)from dual；在执行时会出问题。语句select to_date(20021227,YYYYMMDD)from dual；将返回日期27-DEC-02，因为在to_date函数中的日期格式和被转化的数据是合法的。语句select to_date(20021236,YYYYMMDD)from dual；将失败，因为在12月中没有36天。o提示：用to_date转换机制时，如果被转换的数据格式不对，会出现各种各样的Oracle错误。Update、Delete、Insert和Alter等语句o除select外，用户用得最多的SQL命令就数这三个了。用户所写的许多SQL程序都是由三个命令(例如update、delete和insert)与select命令混合组成的。在本章的前面部分，已经讨论了insert语句是怎样工作的。在这部分，将着重讨论update和delete命令的用法，同时也将讨论DDL中alter命令的用法。update语句o在需要修改表中数据时，可使用update命令，命令由三部分组成：nupdate后跟一个或多个要修改的表，这部分是必不可少的。nset后跟一个或多个要修改的表列，这也是必不可少的。nwhere后跟更新限定条件，这是可选的。o例如，若用户准备把customer表中的所有sales值改为0，只要执行命令update customer set sales=0；即可。Oracle将修改所有的数据行，并返回被修改的行数。如果只想把State_cd为MA的客户的sales改为0，那么可用SQL语句update customer set sales=0 where state_cd=MA；。可见update命令的功能十分重要。delete语句oDelete命令用来从表中删除一行或多行数据。该命令由两部分组成：n关键字delete from后跟准备从中删除数据的表名，这是必不可少的。n关键字where后跟删除条件，为可选的。o如果用户想删除customer表的所有记录，可用SQL语句delete from customer；。如果用户准备删除state_cd为CA的客户记录，可用SQL语句delete from customer where state_cd=CA；。alter语句o在创建表后，用户可能想要增加表列，这时就要用到alter table命令了。例如，如果表customer中没有sale_date表列的话，语句alter table customer add(sale_date date)；将成功地把表列sale_date加到表customer中。在多数情况下，用户都是用alter增加表列到表中的。语句alter table x modify(col1 date)；用来改变一个已存在表列的数据类型。o提示：在执行alter命令时，如果要改变含有数据的列的数据类型，那么Oracle8i对alter命令所执行的操作将有很严格的限制。续o允许用户在alter table语句中同时更改多个表列。语句alter table x modify(col1 date,col5 number(3,1)；的作用和两条独立的alter语句一样。oOracle8i的新内容是，用户能够用alter table语句删除不再想要的列。例如，如果想把customer表中的一个新增加的sale_date列删除，可使用如下语句：oAlter table customer drop column sale_date；o这将立即从表中删除这列，回收sale_date列所用的空间。o提示：在任何条件下允许使用何种类型的alter table命令都有严格规定。如果用户输入一条非法的alter table语句，Oracle将给出错误以及错误的原因。连接两个表o现实中，用户需要的大量数据往往存放在多个表中。很多情况下需要处理多个表。例如，customer表中只存放州代码(state_code)，然而用户还想知道州名，这时需要将表customer与表state连接起来，这就要用到表的连接。通过定义，Oracle一类的关系数据库允许用户基于公共域连接两个或更多个表。这些公共域通常称为键域(Key Field)。o存在两种类型的键：主键(Primary)和外部键(Foreign)。主键使表中的数据行保持唯一。在表state中，state_cd就是主键。表customer中也包含有state_cd，此时的state_cd就是外部键。一个表的外部键用于从其他表中获取信息。续oOracle允许用户为表另起一个名o字，称作别名。这里给X和Y两个表分别起别名为right和left。使用带表列的别名使语句表达o的意义更清楚。语句nselect right.col,left.col nfrom x right,y left nwhere right.col=left.col；o注意，比较了两个表中col表列值，SQL语句要求它们相等。格式化输出o在SQL*Plus中，有许多参数可控制SQL*Plus的输出显示格式。利用SQL*Plus命令show all。页和行的大小o命令set linesize告诉Oracle页宽是多少，最常用的设置为80和132。为设置行宽为80，可输入命令set linesize 80。set pagesize命令告诉Oracle页的长度是多少，常用设置为55和60。为了更容易地看到分页，可以用命令set pagesize 30设置页长为30行。页头标o可以用ttitle命令设置每页的标题。ttitle命令包括许多选项。通常使用的缺省设置为：标题文本在行中央，每页上都有日期和页号。如果需要两行头标，则需要使用竖字符(|)。命令ottitle Database techologies|Customer Reporto使Oracle居中放置文本“Database Technologies”为第一个头标行，然后将文本“Customer Report”居中放置在第二行。页脚标o可用btitle命令在每页的底部放置一些信息。建议将程序名放在此处。如果用户需要改一个报告时，只要指定页底部的程序名，就可知道要改那个报告，这有助于避免混淆。命令obtitle-sample.sql-o使SQL*Plus把文本“-sample.sql-”放于每一页底部中央。用户还可以使用left或right关键字将btitle命令中的文字放到相应的位置。如果btitle命令中没有用于定位的关键字，Oracle将文本放在底行的中央。将SQL*Plus结果写到文件中o格式化输出样例Spool命令ospool命令使Oracle将SQL*Plus的输出保存到一个数据文件中。spool命令中要指定输出文件名。如在Windows NT上，要将SQL*Plus的输出保存到数据文件out.lis中，可用命令spool c:reportout.lis。在大多数操作系统中，如果用户未指定后缀，系统会自动在文件名后增加后缀“.lst”。例如，命令spool report会自动产生一个名为report.lst的文件。o提示：SQL*Plus自动把文件扩展名加到spool命令中指定的文件名后。在不同的操作系统中，扩展名可能不同。续o如果已执行了命令set linesize 70,set pagesize 23，并用命令col sales format 99999999格式化了sales表列，要保存SQL语句select*from customer的输出到一个文件。这个输出将放在用spool命令指定的文件中。请注意，因为sales表列是用99999999格式化的，因此显示结果中没有小数。为停止假脱机(spooling)，可用命令spool off或spool out。后者关闭输出文件并打印输出。格式化输出表列o大多数情况下，用户需要格式化实际的表列数据，Column命令可完成这项工作。下面先执行两条格式化命令，然后再查询customer表。命令column last_name format a8 wrap heading Last|Name告诉SQL*Plus在last_name表列只显示8个字符。8代表last_name的显示宽度，a表示每个位置只能是字符。其中wrap部分说明如果last_name多于8个字符，多余的字符显示在下一行的对应位置。Heading部分告诉SQL*Plus把“Last Name”分两行打印在报告的头上。Column命令o命令column state_cd format a8 heading State|Code让SQL*Plus以8个字符位置显示ostate_cd，并且在state_cd表列的顶部放一个两行的列标题“State Code”。现在同样的SQL语句oselect*from customer；Format命令o现在我们来格式化数值型域。format子句可规定用于显示每个数值的位数，并指明在何处插入逗号。语句column sales format 999,999,999,999.00 heading Sales使SQL*Plus显示12位数字，两位小数，用逗号作分隔符。断开逻辑(Break Logic)o现在增加断开逻辑。在SQL*Plus中，增加断开逻辑的方法很简单。只要执行break命令，SQL*Plus就会管理所有的断开逻辑。未断开逻辑ocol sales format 999999.00 和SQL查询语句select state_cd,last_name,sales from customer order by state_cd,last_name；断开逻辑o执行断开命令break on state_cd，上述查询语句的输出如图所示。注意，在图中，state_cd为CA的在第1行显示后，第2行至第5行都不显示(因为从第一行后state_cd没有改变)，然后第6行和第7行的值不同，又再显示。排序o提示：为在SQL*Plus中实现断开逻辑，必须在断开命令指定的表列上进行查询排序。o为说明此点，执行查询select state_cd,last_name,sales from customer order by last_name；再执行命令break on state_cd，输出如图所示。断开和跳过(Break and Skip)o通常，实现断开逻辑后，我们希望在出现新的断开列值前空几行，这时就要用到skip命令。用命令col sales format$999,999,999.99 heading YTD|Sales重新格式化sales表列，再执行断开命令break on state_cd skip 1。现在，语句select state_cd,last_name,sales from customer oderby state_cd,last_name；在断开点计算表列值o用compute sum命令，用户可以让SQL*Plus计算所需的和。现在讨论完成断开格式化和计算YTD合计值所需的命令。o命令compute sum of sales on report是在报表结尾处，计算报表的合计值。report在此处用于触发数值域求和，并显示在报表的结尾处。而命令“compute sum of sales on state_cd”使在打印新的state_cd时打印合计。续o为同时打印报表合计和断开点合计，需要用命令break on report skip 1 on state_cd skip 1重置断开条件。修改SQL语句以便用state_cd排序数据。对于断开报表，必须用break语句中同样的表列排序数据。o注意：为使本节的输出能打印在一页中，必须增加页尺寸。为此输入命令set pagesize 28。结尾处带总计的state_cd断开报表oSQL语句select state_cd,last_name,sales from customer order by state_cd,last_name；用语句col sales format$999,999,999.00 heading YTD|Sales重新格式化sales表列后，执行上述select语句，其断开输出如图所示基于双表的断开逻辑与求和o在多个表中，通过匹配表列值选择数据。下面显示州名(state_name)而非州名代码(state_cd)。最后的修饰o需要state表中的state_name表列的值，可以通过state_cd连接state和customer两个表。o选择语句select state_name,last_name,sales from state a,customer b where a.state_cd=b.state_cd order by state_name,last_name；。因为计算时使用state_name表列，所以必须用break on report skip 1 on state_name skip 1重新向SQL*Plus描述断开条件。我们也需要用compute sum of sales on state_name和compute sum of sales on report语句重新定义断开点合计和报表合计。最终结果