当前位置：文档之家 › SQL-06高级查询

SQL-06高级查询

  • 格式：doc
  • 大小：55.50 KB
  • 文档页数：6

练习6 复杂的结构化查询语句6．1 上机目的1、掌握SELECT语句的统计函数的作用和使用方法2、通过练习SELECT语句的GROUP BY和ORDER BY字句的用法，理解其作用，掌握语句的写法。

3、通过练习涉及多张表的连接查询，掌握它的作用和写法6．2 上机练习预备知识点说明：①函数中DISTINCT的作用是统计计算的过程中去掉重复值。

②函数中ALL的作用是统计计算全部的值包括重复值。

可省略。

6．2．2 GROUP BY 子句作用：将记录根据GROUP BY后所跟字段的值分成多个组，进行分组计算。

一般情况GROUP BY 子句与汇总函数连用。

格式：GROUP BY（字段，…n）例14 按照班级把学生信息表的数据分组,并且统计每个班级的人数分析：Students表中班级人数这一列，因此需要计算才能得到。

因此需要先按class分组，class列中有几个不同的值就要分成几组，再按各组进行统计计算。

SELECT class,人数= count(*)FROM StudentsGROUP BY class注意：①分组也可以根据多个字段；②不能对数据类型为ntext,text,image或bit的字段使用GROUP BY1、HAVING 子句作用：HAVING子句将对GROUP BY 子句选择出来的结果进行再次筛选，最后输出符合HAVING 子句条件的结果。

HAVING子句必须与GROUP BY子句连用。

例15 查询平均入学总分在350分以上的班级分析：Students表中没有平均分这一列，因此需要计算才能得到。

因此需要先按class分组，class列中有几个不同的值就要分成几组，再进行统计计算，最后用HAVING子句筛选出AVG(mgrade) >=350的记录。

SELECT class,AVG(mgrade)FROM StudentsGROUP BY classHAVING AVG(mgrade)>=3502、ALL关键字作用：暂时忽略WHERE子句中的查询条件。

如果使用了ALL关键字那么查询结果将包括由GROUP BY子句所产生的所有组，无论这些组中的记录是否符合WHERE子句中的查循条件。

但是对于不符合WHERE子句中的查循条件的记录值不进行汇总计算。

例16 Select class,平均成绩=AVG(mgrade)from studentswhere class <>’03物流3’group by ALL class分析：使用了ALL关键字后，会出现表中所有班级的分组情况，但”03物流3班”的平均成绩为空，不再计算。

3、CUBE关键字WITH CUBE关键字的主要作用是自动对GROUP BY子句中列出的每个字段都进行分组汇总运算。

（注意有CUBE关键字和没有CUBE关键字的区别）例17 SELECT class,ssex, 平均分=avg(mgrade)From studentsGroup by class,ssex with cube分析：在该例子的检索结果集不但包含按班级和性别的分组计算结果，而且包含按班级分组后的计算结果和按性别分组计算的结果，最后还包含整张表不分组的计算结果。

说明：正确理解WHERE子句、GROUP BY子句和HAVING子句的作用及其作用顺序。

①WHERE子句用来筛选 FROM子句中指定的数据源的记录②GROUP BY子句将WHERE子句的结果集进行分组③HAVING子句将从经过分组后的中间结果集中筛选记录6．2．3 ORDER BY子句作用：在SELECT子句用ORDERBY 子句是对查询结果按照一列或多列进行排序[ASC｜DESC]。

ASC表示升序排列，DESC表示降序排列。

默认情况下为升序排列。

例18 检索学生信息表，并且按照入学总分由高到低输出查询结果分析：入学总分由高到低排列，就表示检索结果要求按入学总分作降序排列，那么关键字为desc。

降序排列不是默认的排列方式，需要特别表示。

SELECT sno,sname,mgradeFROM StudentsORDER BY mgrade desc注意：①Order by 子句中可以指定多个字段，系统将根据子句中排序字段的顺序对查询结果进行嵌套排序。

②Order by 子句中不能包含text,image的字段③Order by列表中不允许使用子查询或常量表达式。

但可以在选择字段列表中为聚合表达指定一个名称，然后在Order by 子句引用这个名称。

6．2．4 联合查询（使用UNION）作用：联合运算符UNION可以将两个或着两个以上的查询的结果合并成一个结果集合显示。

语法格式：查询1……UNION [ALL] ALL：显示所有记录（即使出现重复的记录）查询2……说明：①查询的结果的列标题为第一个查询的列标题②还可以将不同数据库中表的查询结果联合起来例19 将JXGL数据库中的students表和教学管理数据库中的学生表中的记录一起显示出来。

select *from studentsunion allselect *from 教学管理.dbo.学生注意：①参加UNION运算的结果集必须具有相同的结构，字段数目相同，数据类型兼容。

②使用UNION运算符时，单独SELECT语句不能包含Order by 子句。

只能在最后一个Select 语句中使用Order by 子句，对最终的组合结果集产生作用。

6．2．5 用联接进行多表查询根据各个数据表之间的逻辑关系从两个或多个数据表中检索数据。

联接一种关系运算，它是从两个关系的的笛卡儿积中选取属性间满足一定条件的元组。

说明：常用的联接表达式为：([表1.字段1]＝[表2.字段2])1、内联接 INNER JOIN①利用JOIN关键字在FROM子句中指定连接条件。

格式：数据表1 INNER JOIN 数据表2 ON 联接表达式例20查询学号为‘0311101’的学生的学号，姓名，班级，所选修的课程号和成绩。

分析：检索的学号，姓名，班级属于students表，课程号和成绩属于sc表，检索的内容分别属于两张表的情况下，需要先把两张表连接起来，再作检索。

首先看这两张表是否可以直接联接，有联接字段？现在students表和sc表有公共的联接字段sno，那么就可以实现通过两张表的联接进行多表查询。

如果两张没有公共的联接字段，无法联接，那么需要找其他的表，看是否可以通过第三张表将两张表联接起来。

Select students.sno,sname, 学号，姓名，班级class,cno,gradeFrom students inner join sc on students.sno= sc.snoWhere students.sno=‘0311101’②可以使用FROM分句和WHERE分句指定数据表之间的连接Select students.sno,sname,class,cno,gradeFrom students,scWhere students.sno= sc.sno and students.sno=‘0311101’③为了增机SELECT语句的可读性，是SELECT语句中的数据表名更加容易识别，可以使用数据表别名Select A.sno, A.sname, A.class, o, B.gradeFrom students as A,sc as BWhere A.sno= B.sno and A.sno=‘0311101’*2、外表联接①左向外联接 LEFT JOIN格式：数据表1 LEFT JOIN 数据表2 ON联接表达式说明：使用左向外联接进行查询的结果集包括数据表1中的所有记录，而不仅仅是联接字段所匹配的记录。

例21 显示所有学生的学号，姓名，如果有选修课程的显示课程编号和成绩分析：要检索学生的学号，姓名和选修课程课程编号和成绩，这些字段涉及到两张表students和sc，因此必须先把两张表联接起来。

但是inner join只能显示两个表中相互匹配的记录即只显示选修了课程的学生，不显示还没有选修课程的学生。

要达到题目的要求就要选用其他的联接方式：LEFT JOIN，显示出students表中的所有记录也显示出sc表中相关的记录。

SELECT Students.sno,sname,o,SC.gradeFROM Students left join SC ON Students.sno = SC.sno说明：也可以使用FROM分句和WHERE分句指定数据表之间的连接(但是为了防止出现混淆，不建议用该方法)SELECT Students.sno,sname,o,SC.gradeFROM Students ,SCWHERE Students.sno *= SC.sno②右向外联接 RIGHT JOIN格式：数据表1 RIGHT JOIN 数据表2 ON联接表达式同左向外连接相反，使用右向外联接进行查询的结果，将包含数据表2中的所有记录，而不仅仅是连接字段所匹配的记录。

注意：这种联接方式在显示表中记录上正好与LEFT JOIN相反。

说明：也可以使用FROM分句和WHERE分句指定数据表之间的连接(但是为了防止出现混淆，不建议用该方法)SELECT Students.sno,sname,o,SC.gradeFROM Students ,SCWHERE Students.sno =* SC.sno③完整外联接 FULL JOIN格式：数据表1 FULL JOIN 数据表2 ON联接表达式使用完整外联接进行查询的结果将包含两个数据表中的所有记录，当某一条记录在另一条数据表中没有匹配记录时，则另一个数据表的选择列表字段将指定为空值注意：FULL JOIN是将LEFT JOIN和RIGHT JOIN的结果合并起来。

④交叉联接 CROSS JOIN格式：数据表1 CROSS JOIN 数据表2 ON联接表达式如果SELECT语句中没有使用WHERE子句，那么交叉连接将返回数据表1和数据表2中记录的笛卡儿积。

6．3 实验内容001 统计学生信息表中的学生人数（count()函数）002 统计学生信息表中的班级个数（count()函数）003统计学生信息表中所有学生“入学成绩”的平均分、最高分、最低分（avg( ),max( ),min( )）004 分别统计每个班级的男女人数（要求显示出班级、性别、对应的人数）005 统计每个系的教师人数（group by和count（）函数）006 统计教师人数超过4人的部门名称和对应的人数（group by……having……和count函数）007计算选修了“0000027”的学生的平均成绩（要求显示出课程号和对应的平均成绩）008查询全体学生情况，查询结果按所在班级名升序排列，同一班级中的学生按出生日期降序排列（order by）009统计每个部门老师的平均工资，按照平均工资作降序排列010查询选修课程学生的学号、姓名、所选课程号、取得的成绩（inner join）011查询“03物流2”班同学的所有信息，如果有选修课的话还要对应的求列出选修的课程号以及成绩。

合集下载

数据库高级查询sql语句实例

数据库高级查询sql语句实例

数据库高级查询sql语句实例## Advanced SQL Queries for Database Manipulation.### Subqueries.Subqueries are nested queries that are used within the WHERE, HAVING, or FROM clauses of a main query. They allow for complex filtering and data retrieval.Example:sql.SELECT.FROM users.WHERE id IN (SELECT id FROM orders WHERE product_id = 10);This query retrieves all users who have placed an order for product ID 10.### Joins.Joins combine data from multiple tables by matching rows based on common columns. Different types of joins include inner joins, outer joins, cross joins, and self joins.Example:sql.SELECT.FROM users u.INNER JOIN orders o ON u.id = er_id;This query retrieves all users and their corresponding orders.### Window Functions.Window functions perform calculations across a set of rows within a window frame. They are used for aggregation, ranking, and moving calculations.Example:sql.SELECT ,。

SQL高级查询

SQL高级查询

1. SQL(高级查询)1.1. 子查询1.1.1. 子查询在WHERE子句中在SELECT查询中，在WHERE查询条件中的限制条件不是一个确定的值，而是来自于另外一个查询的结果。

为了给查询提供数据而首先执行的查询语句叫做子查询。

子查询：嵌入在其它SQL语句中的SELECT语句，大部分时候出现在WHERE子句中。

子查询嵌入的语句称作主查询或父查询。

主查询可以是SELECT语句，也可以是其它类型的语句比如DML或DDL语句。

根据返回结果的不同，子查询可分为单行子查询、多行子查询及多列子查询。

多行多列或单行多列(没有什么实际意义)多行多列子查询通常用于建立在二次查询,常出现在FROM子句中图-1 子查询例如查找和SCOTT同职位的员工：1.SELECT e.ename, e.job2.FROM emp e3.WHERE e.job =4.(SELECT job FROM emp WHERE ename ='SCOTT')；查找薪水比整个机构平均薪水高的员工：1.SELECT deptno, ename, sal2.FROM emp e3.WHERE sal >(SELECT AVG(sal) FROM emp;如果子查询返回多行，主查询中要使用多行比较操作符，包括IN、ALL、ANY。

其中ALL和ANY 不能单独使用，需要配合单行比较操作符>、>=、<、<=一起使用。

例如查询出部门中有SALESMAN但职位不是SALESMAN的员工的信息：EXISTS:后面跟着子查询判断子查询返回是否存在,如果存在就输出,不存在就忽略这一条;1.SELECT empno, ename, job, sal, deptno2.FROM emp3.WHERE deptno IN4.(SELECT deptno FROM emp WHERE job ='SALESMAN')5.AND job <>'SALESMAN';在子查询中需要引用到主查询的字段数据，使用EXIST关键字。

sql的高级用法

sql的高级用法

sql的高级用法
SQL的高级用法包括以下几个方面：
1.子查询（Subquery）：子查询是一个查询嵌套在另一个查询内
部的语句，可以作为查询条件、选择列表或表达式使用。

子查询可以
嵌套多层，用于解决复杂的数据查询和条件筛选。

2.联接（Join）：联接是将两个或多个表中的数据连接起来进行
查询和分析的方法。

常用的联接方式包括内连接、左连接、右连接和
全连接，通过指定连接条件，可以获取两个或多个表中相关联的数据。

3.分组和聚合函数（Group By和Aggregate Functions）：使用
分组和聚合函数可以对查询结果按照指定的列进行分组，并对每个分
组进行计算或统计。

常用的聚合函数包括SUM、AVG、COUNT、MAX和MIN。

4.窗口函数（Window Functions）：窗口函数是用于在查询结果
中创建动态的、基于特定窗口（Window）的计算的函数。

窗口函数可
以对查询结果集中的每一行进行计算，并返回计算结果。

5.透视表（Pivot Tables）：透视表是一种将原始表格数据按照
指定的列进行重排和汇总的技术。

通过使用透视表，可以根据需要对
数据进行重塑和转换，方便数据分析和报表生成。

6.常用的高级查询：包括条件查询、模糊查询、多表查询、排序、分页、嵌套查询、子查询优化、索引的使用等。

此外，SQL还有其他一些高级用法，如使用临时表、自定义函数、存储过程和触发器等来处理复杂的数据操作和业务逻辑。

同时，SQL也支持用户定义的类型、视图、索引以及事务控制等功能，用于满足不
同应用场景下的需求。

第6章 SQL高级查询

第6章 SQL高级查询


6.3.3 自连接
• 自连接是指同一张表之间的连接查询，它主 要用于在自参照表上显示上下级关系或层次 关系。 例如:emp表中包含empno列和mgr列，二者之 间就有参照关系。（mgr参照empno）。 由于自连接是在同一张表之间的连接， 所以必须定义表别名。
例1： SQL> select manager.ename from emp manager,emp worker 2* where manager.empno=worker.mgr and worker.ename='BLAKE‘ ENAME ---------KING
Oracle 10g 数据库
第6章 SQL高级查询
目标
• • • • 了解关系操作和关系完整性； 理解和掌握连接查询； 掌握子查询； 会使用集合操作符。
6.1 关系操作
• 关系运算包括传统的集合运算和专门的关系 运算。 • 传统的集合运算将关系看成元组的集合，其 运算是从关系的“水平”方向即行的角度进 行；而专门的关系运算不仅涉及行而且涉及 列。 • 传统的集合元素包括并、交、差、笛卡尔积 四种运算，专门的关系运算包括选择、投影、 连接和除法运算。
A a1 a2 NULL
B b1 b3 b2
C 5 8 NULL
E 3 10 7
右外连接
6.2 关系的完整性约束
关系模型中有三种完整性约束：实体完整性、 参照完整性、用户自定义完整性。其中前两个是 关系模型必须满足的完整性约束条件，有关系系 统自动支持。而用户自定义完整性是应用领域需 要遵循的约束条件，体现了具体应用领域中的语 义约束。 （1）实体完整性 若属性（指一个或一组属性）A是关系R的主 属性（即主键中的属性），则A不能去空值。 所谓空值就是“不知道”或“不存在”的值。