OCP实验手册(Linux版本)

1练习题（sql&pl/sql）更新开始日：2011年5月19日讲师：孟祥龙版本：version1.12【课前基础知识补充】注意：操作系统管理员root，密码oracle操作系统一般用户oracle，密码oracle--------------数据库管理员sys，密码oracle数据库一般管理员system，密码oracle数据库测试用户scott，密码tiger数据库测试用户hr，密码hr注意：以下实验环境为英文的数据库服务器，如果是中文、日文或者其他语言的服务器，命令会略有区别；1.显示表的结构（其中describe可以缩写成desc）补充：【数据类型】Number(p,s)：数字类型，p表示数字的有效长度(从数字的左边第1位不为0的开始算起，直到最右边的长度；取值范围0~38位)，s表示数字的精度(即小数点右边的位数，取值范围-84~127位)；Varchar2(s)：可变长的字符类型，s表示字符串的长度，取值范围1~4000位；Char(s)：定长的字符类型，s表示字符串的长度，取值范围1~2000位；Date：时间类型，表示时间的年月日，没有长度和精度，取值范围公元前4713年12月31日~公元后9999年12月31日；2.在SQL命令行中修改SQL语句2.1a[ppend]text，表示在添加文本到当前行的末尾；3注意：在a后面需添加2个半角空格；2.2c[hange]/old/new，表示在当前行中将旧文本改成新文本；c[hange]/old/，表示从当前行中删除文本；2.3l[ist]，表示列举出缓冲区中所有的行；l[ist]n,表示列举出缓冲区中的第n行；l[ist]mn,表示列举出缓冲区中的第m行到第n行；4注意：*标注的行，表示当前活动行，如果修改时不指定哪一行的话，则修改默认的指定行；2.4n，表示将指定行标记为活动行；ntext，表示用文本代替第n行；0text，表示在第1行之前添加1行文本2.5del，表示删除当前活动行；5deln，表示删除第n行；delmn，表示删除第m行至第n行；注意：如果想删除所有的行，那该怎么办？2.6input，表示在指定的活动行后面添加若干行；(例：在第2行后添加2行，结尾处回车即可)inputtext,表示在指定的活动行后面添加1行文本；(例：在第2行后添加1行文本)2.7cl[ear]buff[er]，表示从SQL缓冲区中删除所有行；62.8r[un]，表示执行缓冲区中的SQL语句；注意：也可以用/代替run，也表示执行缓冲区中的SQL语句；2.9！，表示临时退出SQL命令行，回到操作系统命令行；在操作系统命令行中输入exit后，将会退回到SQL命令行；exit，表示永久退出SQL命令行，回到操作系统命令行；在操作系统中必须重新连接数据库中的用户，才能回到SQL命令行；72.10sav[e]filename[rep[lace]app[end]],表示将缓冲区中的SQL语句保存到某个文件(可指定路径)，其中replace表示新的内容将覆盖原来的内容；append表示在原有内容的基础上向后追加；默认的文件扩展名.sql注意：/u01，是操作系统中的主文件夹；cd/filename，表示打开某个路径下的文件夹；ls，表示列举当前文件夹底下的子文件夹以及文件；cd..表示退回到上一级目录；cd\回车，表示退回到根目录；morefilename，表示获取该文件中内容(须为文本文件)例：保存文件内容SQLsavfilename例：覆盖内容SQLsavfilenamerep例：追加内容SQLsavfilenameapp82.11getfilename，表示将操作系统下的文件内容读取到SQL命令行中；2.12startfilename，表示在SQL命令行中运行操作系统下的文件中的SQL命令；注意：也可以@filename去执行操作系统下的文件中的SQL命令；92.13spoolon，表示将缓存打开，不然只能保存最近执行的1条命令；spoolfilename，表示将缓存中出现的命令以及结果输出到某个文件中；spooloff，表示关闭缓存，同时文件会自动保存；查看文件的内容：2.14设定会话中结果的显示格式：setlinesize100，表示显示的行宽度，将结果显示时占的总宽度为100个字符宽宽的，数字可任意修改(正数)，不然可能显示结果过长而产生折行；例：设定前10设定后setpagesize30，表示每页记录数，即每页可以显示多少条数据，包括标题行，数字可任意修改(正数)，不然会产生翻页；例：设定前设定后11注意：以上linesize，pagesize的设定，是当前会话有效，如果会话重新登录，或者打开新的会话，则设定会还原成默认。2.15vifilename，表示新建一个文件，并对其中的内容进行编辑；例：step1：在操作系统的/u01目录下新建一个文件temp01.sql；step2：点击i按钮，转换文本为-insert-模式，此时可以在文本中书写内容；step3：书写完毕后，点击Esc按钮，退出编辑模式；step4：输入:wq，然后回车，将会保存文件并退出，回到操作系统命令行；注意：:q表示退出不保存:q！表示强制退出不保存【第一章编写基本的SQLselect语句】1.用户拥有的表相关信息的查询；1.1查询当前用户拥有对象的信息；12注意：tab是数据字典，可以查询用户拥有对象的信息，具体表定义，可通过desc命令了解；当然也可以通过user_tables进行查询；2.查看表的具体信息，以及其中包含的数据；2.1查看表的结构；2.2查看表中的数据；注意：*表示显示一张表中所有列的定义信息；13当然也可以显示一张表指定的某些列的信息；3.算术运算注意：数字可以进行加减乘除运算，日期可以进行加减运算，字符串不能算术运算；3.1查询emp表中KING员工的工资信息；3.2将KING的工资进行加、减、乘、除运算；3.3查询数据库系统时间，常以服务器默认的格式进行显示(根据数据库的字符集而定)；注意：dual为数据库中的虚表，隶属于管理员sys用户，但所有的用户都可以访问；无实际意义，仅充14当select语句的结构(用select取系统信息、临时结果等时，以dual充当语句结构)；3.4修改系统时间的显示格式；(session表明此修改限当前会话有效)3.5将系统时间进行加减运算；3.5.1加减一个数字，表示将所给的时间加减多少天；(例：加减2天)3.5.2如果想追加多少小时，多少分，多少秒，那该怎么办？(例：加减2小时，2分钟，2秒钟)注意：时间换算时的进制问题；注意：以上时间格式的修改，限当前会话有效；得到的结果只是临时数据进行显示，并不会改变表中原有的数据值；4.关于null的运算注意：null既不是0，也不是空字符串，而是一个不确定的值step1：描述表中数据的信息(注意comm字段的值)15step2：检索comm为null或者非null的记录注意：where条件中不能使用=或者(不等号的表示!=或者或者^=，工作中多使用)step3：null值的计算注意：任何值与null四则运算之后的值仍未null；165.列的别名注意：列的别名中如果包含空格、特殊字符、关键词、或者大小写敏感的信息，需加双引号；6.连字运算符和单引号的使用注意：字符串信息需加单引号；6.1显示【名字】isanameof【编号】6.2显示【编号】‘snameis【名字】176.3显示【编号】isanumber,andit’snameis【名字】注意：两个单引号‘‘表示转义，打印结果时显示一个单引号；例：连接时间、字符串、数字7.显示不重复的信息7.1如果想知道emp表中总共有多少个部门，那怎么看？例：若直接查看，则会存在重复信息；使用distinct消除重复行；注意：distinct写在字段最前面，它会影响之后的所有字段信息；例：如果后面跟多个字段信息，将会把所有字段作为组合信息，与其他记录进行比较；注意：tempinfo对应的1234信息，只是作为临时数据进行显示，而emp表中并不包含此信息；18将job与tempinfo作为组合信息，记录之间相互比较，留下不重复记录；【第二章约束和排序数据】1.在emp表中选择工资介于1500到2500的员工的信息；注意：使用between下边界and上边界时，条件包括边界值；2.在emp表中选择位于10,20部门的员工的信息；193.在emp表中选择位于员工名字中包含大写字符‘O’的员工的信息；注意：如果查询的名字中包含%或者_，而且查询的时候又要查询这样的信息，需要用到换位码；4.通配符%和_的使用，以及换位码的使用；注意：通配符%，表示0或者多个字符一样；通配符_，表示1个字符一样；4.1参照emp表创建一张t01表；注意：通过上述方式创建的表t01，和emp表的结构一样，但是其中没有数据；4.2添加包含通配符的测试用数据；4.3换位码的使用方法；（此处以\作为换位码，其实换位码也可指定其他字符）例：检索包含%的记录信息；例：检索以%开头的记录信息；20例：检索包含_的记录信息；例：检索以_开头的记录信息；5.复合条件的使用(参照第二章1,2练习)5.1对于and条件复合(可以将between...and...进行转换)例：在emp表中选择工资介于1500到2500的员工的信息；5.2对于or条件复合(可以将in()进行转换)例：在emp表中选择位于10,20部门的员工的信息；6.对于表中数据的排序6.1asc，表示按照所给字段进行升序排列(不指明时默认按所给字段升序排列)；desc，表示按照所给字段进行降序排列；例：emp表中10部门员工的信息按照sal进行升序排序；例：emp表中20部门员工的信息按照sal进行降序排序；216.2如果orderby后面跟多个字段，则将结果集先按照第1个字段进行排序，【条件1】，再按照第2个字段进行排序；注意：【条件1】如果按照第1个字段分不开先后顺序的时候，才会按照第2个字段进行排序；注意：asc或者desc影响的字段，仅仅是它紧挨着的那个字段升降顺序；例：emp表中10部门员工的信息按照empno升序、sal降序进行升序排序；例：emp表中20部门员工的信息按照empno降序、sal升序进行升序排序；6.3当然在排序的时候也可以使用字段在表中定义的先后位置进行排序；注意：首先确认字段在表中定义的先后顺序；例：emp表中10部门员工的信息按照empno升序、sal降序进行升序排序；例：emp表中20部门员工的信息按照empno降序、sal升序进行升序排序；226.4当然除了可以使用number类型的字段进行排序外，还可以使用字符串或者时间类型的字段进行排序；注意：字符串排序：按照字符对应的ASCII码的先后进行排序；日期排序：按照日期的先后进行排序，时间越往后越大；例：emp表中员工的信息按照job升序、ename降序进行排列；注意：结果集中如果job相同的情况下，会按照ename进行降序排列；例：emp表中员工的信息按照hiredate升序，sal降序进行排列；注意：将日期类型的hiredate的显示格式进行设定；注意：结果集中如果hiredate相同的情况下，会按照sal进行降序排列；237.使用结果集中列的别名进行排序；例：按照emp表中员工对应的年薪(sal*12)进行排序；注意：当然也可以按照字符串、日期对应的别名进行排序；8.如果排序的字段中包含null值，那结果会怎样？注意：在字段进行比较大小的时候，null值比任何值都大；例：emp表中员工的信息按照comm升序进行排列；注意：结果集会按照comm升序排列(有值的进行排列，null放在后面，但是null值之间怎么排列暂不考虑[因为null和null之间无法比较大小])；如果，我想把黑色部分和白色部分位置上下条换，该怎么办？24例：同上，emp表中员工的信息按照comm降序进行排列；如果，我想把黑色部分和白色部分位置上