教学材料《Oracle数据库》-项目五 数据查询的应用

知识目标掌握SQL语言分类01.02.掌握各种函数的用法03.掌握SELECT语句的基本结构能力目标能进行SELECT基本查询01.02.能进行SELECT多表连接查询03.能创建与使用子查询任务1单表数据查询010203创建与使用基本查询创建与使用条件查询创建与使用分组查询任务1单表数据查询SQL（structuredquerylanguage）语言是用于数据库查询的结构化语言。SQL语言包含四个部分：（1）DQL—数据查询语言（如，SELECT语句）；（2）DML—数据操纵语言（如，INSERT、UPDATE、DELETE语句）；（3）DDL—数据定义语言（如，CREATE、DROP等语句）；（4）DCL—数据控制语言（如，COMMIT、ROLLBACK等语句）。Select语句的一般格式如下：Select<字段名或表达式列表>From[方案名].<数据表名或视图名>Where<条件表达式>GroupBy<分组的字段名或表达式>Having<筛选条件>OrderBy<排序的字段名或表达式>[ASC|DESC]Select语句的使用方法（1）Select是查询语句必需的关键字（2）From子句是Select语句所必需的子句，用于标识从中检索数据的一个或多个数据表或视图。（3）Where子句用于设定检索条件以返回需要的记录。（4）GroupBy子句用于将查询结果按指定的一个字段或多个字段的值进行分组统计。（5）Having子句与GroupBy子句配合使用，用于对由GroupBy子句分组的结果进一步限定搜索条件。（6）OrderBy子句用于将查询结果按指定的字段进行排序。01查询表的全部信息任务1单表数据查询-01创建与使用基本查询任务：查询“Student”表所有学生的全部信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。Select*Fromsys.Student;02查询表的指定列信息任务1单表数据查询-01创建与使用基本查询任务：查询“Student”表中所有学生的“SNO”、“SNAME”、“TELPHONE”的信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。SelectSNO,SNAME,TELPHONEFromsys.Student;03查询表并更改显示的列标题任务1单表数据查询-02创建与使用条件查询任务：查询“Student”表中所有学生的“SNO”、“SNAME”、“TELPHONE”的信息，并分别以“学号”、“姓名”、“联系电话”作为输出标题。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。SelectSNOAS学号,SNAMEAS姓名,TELPHONEAS联系电话Fromsys.Student;任务1单表数据查询-02创建与使用条件查询命令行方创建表空间SQL语言中包括的运算符与表达式主要有：算术运算符、比较运算符、逻辑运算符、集合运算符和连接运算符等。1）算术运算符算术运算符主要用来进行加、减、乘、除等算术运算。SQL中常用的算术运算符有：+、-、*、（）等。2）比较运算符SQL中常用的比较操作符有：=(等于)、!=（不等于）、<（小于）、>（大于）、<=（小于等于）、>=（大于等于）、in（在列表中）、between（介于之间）、like（匹配）等。3）逻辑运算符SQL语言中常用的逻辑操作符有：and（与）、or（或）、not（非）。逻辑操作符的优先顺序为not>and>or。4）集合运算符集合运算符又称为谓词运算符。常用的集合运算符有union、intersect、minus5)连接运算符用来连接连个字段，或者将多个字符串连接起来。操作符的优先级别：算术>连接>比较>逻辑。01任务1单表数据查询-02创建与使用条件查询查询表中指定行任务：查询“Student”表中学号为“100001”的学生的全部信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。Select*Fromsys.StudentWHEREsno=’100001’;02任务1单表数据查询-02创建与使用条件查询查询不重复的行任务：查询“Student”表中“CLASSNO”的所有信息，并去除重复的记录。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。SelectDistinctclassnoFromsys.Student;03任务1单表数据查询-02创建与使用条件查询查询表中前若干行任务：查询“Student”表中前5个学生信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。Select*Fromsys.StudentWHERErownum<6;04任务1单表数据查询-02创建与使用条件查询指定条件查询任务：查询“student”表中出生日期在“1992-10-30”以前的男生信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。SELECT*FROMSYS.STUDENTWHEREsbirthday<'1992-10-30'ANDssex='男';04任务1单表数据查询-02创建与使用条件查询指定条件查询任务：查询“student”表中姓“吴”的女生信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。SELECT*FROMSYS.STUDENTWHEREsnamelike'吴%'ANDssex='女';04任务1单表数据查询-02创建与使用条件查询指定条件查询任务：查询“RJ01”和“RJ02”班入学成绩大于420的学生信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。SELECT*FROMSYS.STUDENTWHEREclassnoIN('RJ01','RJ02')andsscore>420;01查询数据并分组任务1单表数据查询-03创建与使用分组查询任务：查询“Student”表并分班显示人数。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。SELECTclassno,COUNT(sno)FROMSYS.STUDENTGROUPbyclassno;02查询时进行分组统计任务1单表数据查询-03创建与使用分组查询任务：查询“Student”表并分班显示学生人数大于7人班级。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。SELECTclassno,COUNT(sno)FROMSYS.STUDENTGROUPbyclassnoHAVINGCOUNT(sno)>7;03查询时进行分组并排序任务1单表数据查询-03创建与使用分组查询任务：查询“Student”表中各个班级的平均成绩并升序显示。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。SELECTclassno,round(avg(sscore),0)FROMSYS.STUDENTGROUPbyclassnoORDERbyavg(sscore);任务2多表连接查询010203创建基本连接多表查询创建多表内连接查询创建多表外连接查询04创建多表交叉连接查询任务2多表连接查询连接查询是在SELECT语句使用WHERE子句、JOIN关键字构建连接条件对多表进行连接，并显示结果。连接查询分为内连接查询、外连接查询和交叉连接查询。其中外连接查询包括左外连接查询、右外连接查询和全外连接查询3种。连接的格式有如下2种：格式一：Select<输出字段或表达式列表>From[<方案名>.]<表1或视图1>[别名1],[<方案名>.]<表2或视图2>[别名2][Where<表1.列名><连接操作符><表2.列名>]连接操作符可以是：=、<>、!=、>、<、<=、>=，当操作符是“=”时表示等值连接。格式二：Select<输出字段或表达式列表>From[<方案名>.]<表1或视图1>[别名1]<连接类型>[<方案名>.]<表2或视图2>[别名2]On<连接条件>其中“连接类型”用于指定所执行的连接查询的类型，内连接为InnerJoin，外连接为OutJoin，交叉连接为CrossJoin，左外联接为LeftJoin，右外联接为RightJoin，完整外联系为FullJoin。01两表连接查询任务2多表连接查询-01创建基本连接多表查询任务：查询“sys.Student”表和“sys.score”表中所有学生的信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。select*fromsys.student,sys.scoreWHEREsys.student.sno=sys.score.sno;02多表连接查询任务2多表连接查询-01创建基本连接多表查询任务：查询“sys.Student”表、“sys.score”表、“sys.class”表中所有学生的数据库成绩信息，显示结果包括“sno”、“cname”、“score”、“counselor”字段。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。selectsys.student.sname,ame,sys.score.score,sys.class.counselorfromsys.studentsys.score,sys.classwheresys.student.sno=sys.score.snoandsys.class.classno=sys.student.classnoandame='数据库';01任务2多表连接查询--02创建多表内连接查询创建多表内连接查询任务：从“sys.Student”表中查询在“sys.score”表里有的学生课程成绩，要求显示结果包括“sname”、“cname”、“score”字段。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。Selectsys.student.sname,ame,sys.score.scorefromsys.studentinnerjoinsys.scoreonsys.student.sno=sys.score.sno;01创建左外连接查询任务2多表连接查询-03创建多表外连接查询任务：从“sys.Student”表中查询在“sys.class”表中有对应的班级的学生信息，显示结果包括“sno”、“sname”、“classno”、“counselor”。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。select*fromsys.student,sys.scoreWHEREsys.student.sno=sys.score.sno;左外连接查询leftjoin显示符合条件的数据行，同时显示左边数据表不符合条件的数据行，右边没有对应的条目显示NULL。02创建右外连接查询任务2多表连接查询-03创建多表外连接查询任务：查询“sys.Student”表和“sys.class”表中有哪些的班级没有对应的学生信息，显示结果包括“sno”、“sname”、“classno”、“counselor”。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。selectsys.student.sno,sys.student.sname,sys.class.classno,sys.class.counselorfromsys.studentrightjoinsys.classonsys.student.classno=sys.class.classno；右外连接查询rightjoin显示符合条件的数据行，同时显示右边数据表不符合条件的数据行，左边没有对应的条目显示NULL03创建完全连接查询任务2多表连接查询-03创建多表外连接查询任务：查询“sys.Student”表、“sys.class”表和“sys.score”表中所有学生的班级情况和成绩，显示结果包括“sno”、“sname”、“classno”、“cname”、“score”、“counselor”。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。selectsys.student.sno,sys.student.sname,sys.class.classno,ame,sys.score.score,sys.class.counselorfromsys.studentfulljoinsys.classonsys.student.classno=sys.class.classnofulljoinsys.scoreonsys.student.sno=sys.score.sno；完全连接查询fulljoin显示符合条件的数据行，同时显示左右不符合条件的数据行，相应的左右两边显示NULL，即显示左连接、右连接和内连接的并集。01任务2多表连接查询--04创建多表交叉连接查询不加条件，交叉查询任务：查询“sys.Student”表和“sys.score”表中所有学生的成绩，显示结果包括“sno”、“sname”、“cname”、“score”。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。selectsys.student.sno,sys.student.sname,ame,sys.score.scorefromsys.studentcrossjoinsys.score；交叉连接查询crossjoin会返回被连接的两个表所有数据行的笛卡尔积，即返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。02任务2多表连接查询--04创建多表交叉连接查询加条件，交叉查询任务：查询“sys.Student”表和“sys.score”表中所有学生的成绩，显示结果包括“sno”、“sname”、“cname”、“score”。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。selectsys.student.sno,sys.student.sname,ame,sys.score.scorefromsys.studentcrossjoinsys.scoreWHEREsys.student.sno=sys.score.sno；交叉连接查询crossjoin会返回被连接的两个表所有数据行的笛卡尔积，即返回到的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。任务3子查询010203创建与使用单值子查询创建与使用多值子查询创建与使用相关子查询任务3子查询子查询其实就是嵌套查询，一个查询语句嵌套在另一个查询语句中那么称该查询语句为子查询。子查询在主查询之前执行，主查询使用子查询的结果。子查询可以返回单个结果，可以返回多个结果，也可以不返回结果。如果子查询返回单个值结果，则为单值子查询，可以在主查询中对其使用相应的单行记录比较运算符。如果子查询返回多个结果，则为多值子查询，此时不允许对其使用单行记录比较运算符。在DQL和DML语句中都可以嵌套使用SELECT子句。即SELECT、INSERT、UPDATE和DELETE语句都可以嵌套SELECT子句。在一般情况下SELECT子句将作为以WHERE或HAVING子句引导的条件。常见语法格式如下：select字段列表fromtable1where表达式operator(select字段列表fromtable2);其条件表达式的形式有以下几种：（1）字段名=（SELECT子句），如果SELECT子句只返回单个值作为条件，可以使用此条件表达式。（2）字段名IN（SELECT子句），如果SELECT子句返回多个值作为条件，必须使用此条件表达式。（3）字段名EXISTS（SELECT子句），如果SELECT子句返回值是布尔类型的，必须使用此条件表达式01子查询返回空值任务3子查询-01创建与使用单值子查询任务：查询“score”表中“王二”的所有信息。由于“score”表中只有学号字段“sno”，没有姓名字段“sname”，所以要通过学生信息表“student”查找到“王二”的学号“sno”的值。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。select*fromsys.scorewheresno=(selectsnofromsys.studentwheresname='王二')；02子查询返回单值任务3子查询-01创建与使用单值子查询任务：查询“score”表中“李丽”的所有信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。select*fromsys.scorewheresno=(selectsnofromsys.studentwheresname='李丽')；01任务3子查询-02创建与使用多值子查询创建单表多值子查询息任务：查询“student”表中入学成绩大于班级入学成绩平均分的学生信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。select*fromsys.studentwheresscore>any(selectavg(sscore)fromsys.studentgroupbyclassno);02任务3子查询-02创建与使用多值子查询创建多表多值子查询息任务：查询“student”表中“数据库”成绩>85分的所有学生信息。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。select*fromsys.studentwheresnoin(selectsnofromsys.scorewherecname='数据库'andscore>85);01在select字段列表中使用相关子查询任务3子查询-03创建与使用相关子查询任务：查询“student”表中“sscore”>400分的学生信息，显示结果包括“sno”、“sname”、“sscore”、“classno”，并分别显示为“学号”、“姓名”、“入学成绩”、“班级名称”。显示结果按照入学成绩升序排列。操作步骤：在脚本输入区域，输入如下所示的SQL查询语句。selectsno学号,sname姓名,ssco