本1班11号罗志宁大型数据库技术(Oracle)实验2

第1页共22页实验2SQL基本查询与SQLPlus的使用【实验目的与要求】1．熟悉OracleSQLPlus的登录；2．熟悉使用OracleSQLPlus进行数据库基本配置和数据操作；3．掌握SQL基本查询。【实验内容与步骤】2.1启动SQL*Plus要调用SQLPlus，需依次完成如下步骤：开始––所有程序––Oracle––应用程序开发––SQLPlus图2-1启动SQL之后，将会出现图2-2所示的屏幕。填写相应的详细资料。按Tab键转到下一个选项。第2页共22页图2-2SQLPlus登录界面(Oracle10g)图2-3SQLPlus登录界面(Oracle11g)也可以直接在dos下输入：sqlplus帐号/密码@网络服务名第3页共22页安装Oracle时创建的默认用户为Scott，密码为tiger。主机字符串是oracle。在本实验中我们要使用Oracle默认用户scott和它的一些库表做实验。scott默认是被锁定，解锁方法：alteruserscottaccountunlock;默认密码是tiger，修改密码的方法：alteruserscottidentifiedby[newpassword];修改口令请给出解锁的命令和结果图示：第4页共22页退出SYS帐号，使用scott登录：图2-4使用scott帐号登录将会出现如下图所示的屏幕，显示产品信息。图2-5SQLPluse界面SQL是SQL提示符。在此提示符下提供要执行的SQL命令和语句。该用户所拥有的数据库表信息，存放在user_tables表下，我们可以使用“descuser_tables;”命令查看该表描述：第5页共22页从表中，我们可以看到我们有一个TABLE_NAME字段，存放该用户所拥有的所有表的表名，请使用select语句查看scot拥有哪些表：DEPT,EMP,BONUS,SALGRADE。第6页共22页还是使用“desc[TABLE_NAME];”的方法，可以查询scott拥有的各个表的描述：请在下表中给出可通过scott/tiger登录访问的默认表及其列名，并且指出哪些字段非空。在本实验中，我们将使用这些表。EMPDEPTSALGRADEEMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNODEPTNODNAMELOCGRADELOSALHISAL第7页共22页根据截图可以知道，所有字段都是非空的。2.2从表中查看数据输入表中的数据可通过使用SELECT语句进行查看。2.2.1查看所有列语法如下：SELECT*FROMtablename;其中“*”表示将显示所有列。实验2-1部门表中查看所有记录和所有字段，请执行如下步骤：在SQL提示符下键入“Select*fromdept”（从部门选择*）。按Enter。请给出你得到的结果。第8页共22页2.2.2查看选择列要查看选择列，请输入列名，并用逗点而不是“*”隔开。语法如下：SELECTcolumnname1,columnname2,……,columnnameNFROMtablename;实验2-2只显示部门表中的部门名和位置，执行如下所示的语句：图2-5请给出运行结果：某些列标题由于其列大小限制而使用缩写词。要将字符列的显示宽度设置为12，请给出如下语句：COLUMNcolumnnameFORMATA12COLUMN语句不能以分号结尾，因为它是格式化命令。通过COLUMN命令可以使用的选项包括：选项功能FORMAT指定数据格式HEADINGtexttext为列标题JUSTIFYLRC将标题对齐为LEFTRIGHTCENTERWRAPTRUNC在列中回行或截断数据通过FORMAT选项可用的掩码包括：掩码功能第9页共22页Ann个字母数字字符99[9…]不含前导零的数字00[…]含前导零的数字.显示的小数点V隐藏的小数点$先导$MI数字后面是减号PR负值包括在“”之内B显示空白而不是零要显示包含前导零的薪金，请在SQL提示符下给出如下命令。SQLcolumnsalformat009999实验2-3设置格式之后，执行如下所示的命令。图2-请给出运行结果：这样，所有雇员的薪金就以包含前导零的格式显示。要删除此格式，请在提示符下输入如下给定命令。第10页共22页SQLcolumnsalformat9999992.2.3查看符合条件的行可以使用WHERE子句完成对某些相关数据行的条件检索。WHERE子句中指定的条件称为谓词。谓词可以是任意数据类型、字符、数字或日期。像AND、OR和NOT这样的逻辑运算符也可以用于合并谓词。语法如下：SELECTcolumnname1,columnname2,?,columnnameNFROMtablenameWHEREcondition;实验2-4要从雇员表中只显示那些在部门20工作的雇员，执行如下所示的语句：图2-请给出运行结果：在谓词中可以有效使用的其他运算符还有：1.IN运算符：此运算符用于显式规定可以接受的值组。实验2-5要只显示属于“CLERK”（办事员）或“MANAGER”（经理）的雇员，执行如下命令。图2-请给出运行结果：第11页共22页另一方面，NOTIN运算符将返回与条件不匹配的行。2.BETWEEN运算符：此运算符与IN运算符相似，不过可以指定值的范围而不是匹配的值组。实验2-6要只显示在“02-JAN-82”（1982年1月1日）和“32-DEC-87”（1987年12月31日）之间加入的雇员，请给出如下命令。图2-请给出运行结果：NOTBETWEEN运算符将获取与条件不匹配的行。3.LIKE运算符：该运算符用于匹配字符串或字符串的一部分（称为子字符串）。该运算符使用通配符扩展其字符串匹配功能。有两种通配符与LIKE运算符一起使用：下划线(_)：这表示任意单字符。百分号(%)：这表示多字符的序列。实验2-7要显示姓名以“J”开头的雇员，请给出如下命令。第12页共22页图2-请给出运行结果：2.2.4查看汇总数据（使用统计函数）GROUPBY子句根据字段值对行进行分组。该子句在应用时将与聚合函数联合。有时，也要求对已分组的查询进行条件检索。可以使用“HAVING”子句对已分组查询进行条件检索。“HAVING”子句与“WHERE”子句相似，只不过“WHERE”子句用于行，而“HAVING”子句用于已分组的结果。语法如下：SELECTcolumnname1,aggregate_function(columnname2)FROMtablenameGROUPBYcolumnname1;实验2-8要从雇员表中显示付给经理的最高薪金，执行如下所示的语句：图2-请给出运行结果：第13页共22页聚合函数通常与Groupby和Having子句一起使用。要讨论的聚合函数是：1.Count：此函数对包括Null值的查询所返回的行数进行记数。实验2-9要查找每个工作类别的人数，执行如下所示的语句：图2-请给出运行结果：2.Max：从查询返回的值中返回最大值。3.Min：从查询返回的值中返回最小值。4.Avg：用来确定某个列中出现的值的平均数。5.Sum：用来获取查询输出的行的算术和。实验2-10要从雇员表中显示付给经理的最高、最低、平均和总薪金，执行如下所示的语句：图2-请给出运行结果：第14页共22页2.2.5以指定顺序查看数据ORDERBY子句可帮助按照希望出现的顺序检索查询。可以通过指定DESC参数或ASC参数来按升序顺序或降序顺序进行排序。如果没有指定任何参数，则默认为升序排序。还可对多列进行排序。语法如下：SELECTcolumnname1,columnname2,?,columnnameNFROMtablenameWHEREcondition;ORDERBYcolumnname1,columnname2,…,columnnameN;实验2-11要以雇员的工作的顺序显示收入大于或等于3000的雇员，执行如下所示的语句：图2-13请给出运行结果：3.2.6使用计算列查看数据第15页共22页除了从数据库表中直接提取的列外，SQL还可以用于计算多个列中的数据的值。可在查询中使用SQL表达式执行所有类型的算术操作。唯一必须注意的是，这些表达式涉及的列应该只属于数字数据类型。语法如下：SELECTcolumnname1,(columnname2arithmeticoperatorcolumnname3)FROMtablename实验2-12要从雇员表中显示雇员总薪金，执行如下语句：图2-请给出运行结果：注意：在上面的命令中，NVL函数用来将空值转换为数字值，我们在此将其转换为“0”。我们必须按任意表达式执行此操作，表达式中包含空值，因为操作数返回NULL。因此，为避免在任何操作中出现这种错误，我们使用NVL函数。2.3退出SQL在SQL提示符下键入Exit（“退出”）或执行如下步骤：从MainMenu（“主菜单”）中选择File（“文件”）选项。第16页共22页单击Exit（“退出”）。您也可以在命令提示符下输入“Exit”。2.4实验练习：在scott用户模式下，完成下查询需求，给出相关查询语句和运行结果：(1)列出各种类别SALES工作的雇员的姓名，假定不知道销售部的部门编号.(2)列出薪金高于公司平均水平的所有雇员.(3)列出与SCOTT从事相同工作的所有雇员.(4)列出分配有雇员数量的所有部门的详细信息,即使是分配有0个雇员.(5)列出各种类别工作的最低工资.第17页共22页(6)列出各个部门MANAGER的最低薪金.(7)列出按计算的字段排序的所有雇员的年薪.(8)选择部门30中的雇员。第18页共22页(9)列出所有办事员的姓名、编号和部门。(10)找出佣金高于薪金的雇员。(11)找出佣金高于薪金60%的雇员。(12)找出部门10中所有经理和部门20中所有办事员的详细资料。(13)找出部门10中所有经理、部门20中所有办事员以及既不是经理又不是办事员但其薪金大于或等于2000的所有雇员的详细资料。第19页共22页(14)找出不收取佣金或收取的佣金低于100的雇员。(15)找出早于12年之前受雇的雇员。第20页共22页(16)显示只有首字母大写的所有雇员的姓名。(17)显示雇员的详细资料，按姓名排序。第21页共22页(18)显示雇员姓名，根据其服务年限，将最老的雇员排在最前面。(19)显示所有雇员的姓名、工作和薪金，按工作内的工作的降序顺序排序，同工作按薪金排序。第22页共22页(20)显示姓名字段的任何位置包含“A”的所有雇员的姓名。