《MySQL高级查询》由会员分享，可在线阅读，更多相关《MySQL高级查询（51页珍藏版）》请在金锄头文库上搜索。
1、第第6 6章章 MySQLMySQL高级查询高级查询MySQLMySQL基础基础教师自我介绍v教师姓名：教师姓名：q( (请进行自我介绍请进行自我介绍 ) )课程介绍 本课程主要介绍了MySQL数据库的安装，管理，数据类型，SQL语句语法和使用，高级查询等方面的知识, 以应用为目标，具有较强的实践性。学习对象v希望了解MySQL数据库基本概念，常见使用方法的学生、专业人士等学习目标v在完成本章的学习后，您将能够：v熟悉MySQL的聚合函数的使用v掌握分组查询v掌握联合查询和内、外连接查询v熟悉子查询用法 目录大纲1.聚合函数2.分组查询3.联合查询4.连接查询5.子查询v数据库的一个最大的特点
2、就是将各种分散的数据按照一定规律、条件进行分类组合，最后得出统计结果。常用的几个聚合函数如下所示： 1 聚合函数v数据库的一个最大的特点就是将各种分散的数据按照一定规律、条件进行分类组合，最后得出统计结果。常用的几个聚合函数如下所示：v聚合函数对一组值执行计算并返回单一的值。除 COUNT 函数之外，聚合函数忽略空值（NULL）。v聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。所有聚合函数都具有确定性。任何时候用一组给定的输入值调用它们时，都返回相同的值。 1 聚合函数v聚合函数仅在下列项中允许作为表达式使用：qSELECT 语句的选择列表（子查询或外部查询）qHAVI
3、NG 子句首先在 student 表中加个score字段，并插入一些数据，如下所示： 1 聚合函数vSUMSUM 返回表达式中所有数值的总和，SUM 只能用于数字类型的列，不能够汇总字符、日期等其他类型。下面的示例计算学生的总分： 1 聚合函数注意这种查询只能返回一个数值，因此，不能够直接与可能返回多行的列一起使用参与查询。如下列操作将报告错误，但是，在一个查询中可以使用多个聚合函数。 1 聚合函数vAVGAVG 函数返回表达式中所有数值的平均值，AVG 函数也只能用于数字类型的列，例如求学生的平均成绩。 1 聚合函数vMAX 和 MINMAX 和 MIN 函数返回表达式中的最大值和最小值，它
4、们可以用于数字类型、字符型、日期/时间类型的列。 1 聚合函数vCOUNTCOUNT 返回提供的表达式中非空值的计数，COUNT 可以用于数字和字符类型的列。另外，也可以使用星号（*）作为 COUNT 的表达式，使用星号可以不必指定特定的列而计算所有的行数。 1 聚合函数COUNT 函数还可以嵌套另一个函数 DISTINCT，表示求出字段值非空并且惟一的记录个数。AVG 函数也可以嵌套函数 DISTINCT，表示求出非空且非重复字段的平均值。 1 聚合函数v分组查询分组函数对每一组数据进行计算，得到对应的计算数据。在前面的例子中，返回结果都只有一行，因为在使用了分组函数的查询语句中并没有对数据
5、进行分组。这意味着整个表中的所有数据被作为一组进行了统计计算，所以最终得到了一行结果。除了对整个表的数据进行统计计算外，更多的需求是根据用户的实际需要对表中的数据进行分组，然后对每个分组进行组函数计算。使用 Group by 子句可以对表中的数据进行分组。SELECTCOLUMN,GROUP_FUNCTIONFROMTABLEWHERECONDITIONGROUPBYGROUP_BY_EXPRESSIONORDERBYCOLUMN; 2 分组查询v规则 1：出现在 SELECT 后面的字段，如果出现的位置不在分组函数中，那么必须要出现在 GROUP BY 子句中。 2 分组查询v规则 2：出现
6、在 GROUP BY 子句中的字段并不一定要出现在 SELECT 后面。 2 分组查询v规则 3：WHERE 子句中不能使用聚合函数。使用 Having 子句对分组结果进行限制SELECTCOLUMN,GROUP_FUNCTIONFROMTABLEWHERECONDITIONGROUPBYGROUP_BY_EXPRESSIONHAVINGGROUP_CONDITONORDERBYCOLUMN;这是一个完整 SELECT 查询语句，在整个语句的执行过程中，最先执行的是 WHERE 子句，在对表数据进行过滤后，符合条件的数据通过 GROUP BY 进行分组，分组数据通过 HAVING 子句进行组函
7、数过滤，最终的结果通过 ORDER BY 子句进行排序，排序的结果被返回给用户。 2 分组查询对分数高于 65 分的学生进行分组，统计分数高于 70 分的人数和分数。 2 分组查询vUNION 运算符可以将两个或两个以上上 SELECT 语句的查询结果集合合并成一个结果集合显示，即执行联合查询。vUNION 的语法格式为：select_statementUNIONALLselectstatementUNIONALLselectstatementn其中 selectstatement 为待联合的 SELECT 查询语句。ALL 选项表示将所有行合并到结果集合中。不指定该项时，被联合查询结果集合中
8、的重复行将只保留一行。3 联合查询联合查询时，查询结果的列标题为第一个查询语句的列标题。因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时，也必须使用第一查询语句中的列名、列标题或者列序号。在使用 UNION 运算符时，应保证每个联合查询语句的选择列表中有相同数量的表达式，并且每个查询选择表达式应具有相同的数据类型，或是可以自动将它们转换为相同的数据类型。在自动转换时，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。3 联合查询表 I student 表表 II stu 表3 联合查询执行联合查询的效果：3 联合查询在关系数据库管理系统中，表建立时各数据之间的关系
9、不必确定，常把一个实体的所有信息存放在一个表中。当检索数据时，通过连接操作查询出存放在多个表中的不同实体的信息。连接操作给用户带来很大的灵活性，他们可以在任何时候增加新的数据类型。为不同实体创建新的表，尔后通过连接进行查询。SQL-92 标准所定义的 FROM 子句的连接语法格式为：SelectcolumnsFROMjoin_tablejoin_typejoin_tableON(join_condition)4 连接查询join_table 指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。join_type 指出连接类型，可分为三种：内连接、
10、外连接和交叉连接。内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。外连接分为左外连接(LEFT OUTERJOIN 或 LEFT JOIN)、右外连接(RIGHT OUTER JOIN 或 RIGHT JOIN)和全外连接(FULL OUTER JOIN或 FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。ON(join_cond
11、ition)子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。4 连接查询v交叉连接(CROSS JOIN)没有 WHERE 子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。如对 student 和 stu 表执行交叉连接，效果如下图所示。4 连接查询v内连接内连接查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值。内连接分三种：1、等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。2、不等连接： 在
12、连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括、=、=、!和。3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。4 连接查询v外连接采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)中的所有数据行。简言之即左外连接包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录，右外连接包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录。右连接和左连接类似，两者是可以相互转化的。下面通过一个示例来演示内连接和外连接的
13、相关用法。4 连接查询创建两个表：createtableemployee(eidint(5),namevarchar(30),didint(5);createtabledept(idint(5),dnamevarchar(30)charactersetutf8collateutf8_general_ci)ENGINE=InnoDBDEFAULTCHARSET=utf8;insertintodeptvalues(1,技术部技术部),(2,市场部市场部),(3,工程部工程部);insertintoEmployeevalues(1,李小飞李小飞,1),(2,郑波郑波,1),(3,关思宇关思宇,2),
14、(4,戊卫成戊卫成,2),(5,朱妙妙朱妙妙,null);如果插入中文时提示长度不够时，请先执行 set names gbk;注意：朱妙妙不属于任何部门（新来的员工，还没有分配到任何的部门），而工程部不存在任何的员工（比如是一个新成立的部门，还没有员工）4 连接查询v内连接查询查询出各员工的信息以及所在的部门名称我们可以有两种方式，这两种是等效的一种是：selecte.eid员工员工ID,e.name员工姓名员工姓名,d.dname部门名称部门名称fromemployeee,deptdwheree.did=d.id另外一个是：selecte.eid员工员工ID,e.name员工姓名员工姓名,d
15、.dname部门名称部门名称fromemployeeeinnerjoindeptdone.did=d.id4 连接查询检索的结果都是：当我们连接两张检索数据的时候，检索的方式是首先逐行扫描“员工信息表”中的记录，然后根据连接条件来决定此记录是否被检索。比如对于李小飞，这条记录的 deptid 是 1（部门编号），它在部门表中能找到和它匹配的编号 1，而编号 1 的部门名称（deptname）是“技术部”所以张三这条记录会被检索，最终的结果肯定是：0001 李小飞 技术部。但是朱妙妙的部门编号是 NULL，它在部门信息表中找不到匹配的项（因为部门信息表中不存在部门编号为 NULL 的部门），所以
16、朱妙妙不会被检索。4 连接查询v左外联结但是有些情况下，我们需要知道所有员工的信息，即使他不属于任何部门。这样我们就可以采用外连接，在这里为左外连接，也就是连接中的左表的表中的记录，无论能不能在右表中找到匹配的项，都要检索，如果没有匹配的项目，那么右表中的字段值为 NULL（空），在这里就代表，此员工不属于任何部门。显示所有的员工和部门名称，包括新员工。检索语句为：selecte.eid员工员工ID,e.name员工姓名员工姓名,d.dname部门名称部门名称fromemployeeeleftjoindeptdone.did=d.id4 连接查询检索的结果是：但是在这里，工程部同样不会被检索，
17、因为，deptname 是在连接的右边的表中，“工程部”在左表中不存在任何的记录，所以不会被检索。这里关注的是“连接中的左边的表”。4 连接查询v右外连接有时，我们需要知道，全部部门的信息，即使它没有任何的员工。在我们的查询中部门表在连接的右边，如果我们想知道右边表中的所有记录信息，那么就可以采用右外连接，如果此记录在左边的表中找不到匹配项，则相应字段（employeeid,employeename)为 NULL检索语句为：selecte.eid员工员工ID,e.name员工姓名员工姓名,d.dname部门名称部门名称fromemployeeerightjoindeptdone.did=d.i
18、d4 连接查询检索的结果是：但在这里，朱妙妙是不会被检索了，因为它在右表中找不到匹配项，这里关注的是“连接中的右边的表”4 连接查询这里考虑一下，如果要统计出各部门里员工的人数，如何操作呢？selectd.dnameas部门名称部门名称,count(e.eid)as员工人数员工人数fromemployeeaserightjoindeptasdone.did=d.idgroupbydid;检索的结果是：4 连接查询子查询：某些情况下，当进行查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。用于子查询的关键字主要包括 in、not in、=、!=、exists、
19、not exists 等。例如要查询所有部门的员工信息select*fromemployeewheredidin(selectidfromdept);检索的结果是：5 子查询如果子查询的结果是唯一的，还可以用=代替 in。select*fromemployeewheredid=(selectidfromdept);结果不唯一时会抛错：select*fromemployeewheredid=(selectidfromdeptlimit1);检索的结果是：5 子查询某些情况下，子查询是可以转化为表连接的，例如前面的查询所有部门的员工信息就可以转化为表连接。selectemployee.*frome
20、mployee,deptwhereemployee.did=dept.id;注意：MYSQL4.1 以前的版本不支持子查询，需要用表连接来实现子查询的功能。表连接在很多情况下用于优化子查询。5 子查询v使用ANY, IN和SOME进行子查询ANY关键词必须接在一个比较操作符后面。ANY关键词的意思是“对于在子查询返回的列中的任一数值，如果比较结果为TRUE的话，则返回TRUE”。例如:SELECTs1FROMt1WHEREs1ANY(SELECTs1FROMt2);假设表t1中有一行包含（10）。如果表t2包含（21，14，7），则表达式为TRUE，因为t2中有一个值为7，该值小于10。如果表
21、t2包含（20，10），或者如果表t2为空表，则表达式为FALSE。如果表t2包含（NULL, NULL, NULL），则表达式为UNKNOWN。词语IN是ANY的别名。因此，这两个语句是一样的：SELECTs1FROMt1WHEREs1=ANY(SELECTs1FROMt2);SELECTs1FROMt1WHEREs1IN(SELECTs1FROMt2);词语SOME是ANY的别名。因此，这两个语句是一样的：SELECTs1FROMt1WHEREs1ANY(SELECTs1FROMt2);SELECTs1FROMt1WHEREs1SOME(SELECTs1FROMt2);5 子查询词语IN是
22、ANY的别名。因此，这两个语句是一样的：SELECTs1FROMt1WHEREs1=ANY(SELECTs1FROMt2);SELECTs1FROMt1WHEREs1IN(SELECTs1FROMt2);词语SOME是ANY的别名。因此，这两个语句是一样的：SELECTs1FROMt1WHEREs1ANY(SELECTs1FROMt2);SELECTs1FROMt1WHEREs1SOME(SELECTs1FROMt2);5 子查询v 使用ALL进行子查询词语ALL必须接在一个比较操作符的后面。ALL的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE。”例如：SELE
23、CTs1FROMt1WHEREs1ALL(SELECTs1FROMt2);假设表1中有一行包含（10）。如果表t2包含（-5，0，5），则表达式为TRUE，因为10比t2中的所有三个值都大。如果表t2包含（12，6，NULL，100），则表达式为FALSE，因为表t2中有一个值12大于10。如果表t2包含（0，NULL，1），则表达式为unknown。5 子查询如果表t2为空表，则结果为TRUE。因此，当表t2为空表时，以下语句为TRUE：SELECT*FROMt1WHERE1ALL(SELECTs1FROMt2);但是，当表t2为空表时，本语句为NULL：SELECT*FROMt1WHERE
24、1(SELECTs1FROMt2);另外，当表t2为空表时，以下语句为NULL：SELECT*FROMt1WHERE1ALL(SELECTMAX(s1)FROMt2);NOT IN是 ALL的别名。因此，以下两个语句是相同的：SELECTs1FROMt1WHEREs1ALL(SELECTs1FROMt2);SELECTs1FROMt1WHEREs1NOTIN(SELECTs1FROMt2);5 子查询v行子查询行子查询是一个能返回一个单一行的子查询变量，因此可以返回一个以上的列值。以下是两个例子：SELECT*FROMt1WHERE(1,2)=(SELECTcolumn1,column2FRO
25、Mt2);SELECT*FROMt1WHEREROW(1,2)=(SELECTcolumn1,column2FROMt2);如果在表t2的一个行中，column1=1并且column2=2，则查询结果均为TRUE。5 子查询vEXISTS和NOT EXISTS如果一个子查询返回任何的行，则EXISTS 子查询为TRUE。例如：SELECTcolumn1FROMt1WHEREEXISTS(SELECT*FROMt2);更常见的用法是结合关联子查询一起使用5 子查询v关联子查询相关联的子查询是一个包含对表的引用的子查询。该表也显示在外部查询中。例如：SELECT*FROMt1WHEREEXISTS
26、(SELECTcolumn1FROMt2WHEREt2.column2=t1.column2);该子查询的匹配过程是，先对t1进行逐行扫描，然后看每行的记录是否满足where条件，如果满足则输出该行。5 子查询vFROM子句中的子查询在SELECT语句的FROM子句中，子查询是合法的。实际的语法是：SELECT.FROM(subquery)ASname. AS name子句是强制性的，因为FROM子句中的每个表必须有一个名称。在子查询选择列表中的任何列都必须有唯一的名称。5 子查询例：假设您想了解一个分类后的表的一组和的平均值。可以采用如下操作：SELECTAVG(SUM(column1)FR
27、OMt1GROUPBYcolumn1;也可以使用子查询：SELECTAVG(sum_column1)FROM(SELECTSUM(column1)ASsum_column1FROMt1GROUPBYcolumn1)ASt1;5 子查询结束语谢谢！MajpjMVcyzj21HLfrvy96dv02lPPfYgxUS7IYmZkyEmZ0kGeYZS3bpLCkYH1lt4EK7CxmUX3ijoYSOer7ZuaVWYgz4EpZrUirVpMzzvNtf1XZw5oswSXOtFaejnOcmfE1lZgnN1RSXg8wLCG8CVQ3XPJMvodPFWcpiYJgZazNSEPNIakl
28、YSu7qSd1UpaxmZDlpN9zW7kljfsLCLi26Yv109ffbnDH8LbUN1G6ACURQ39eG12KHL9tXsZ1jzgoCK8g1kuNOh5eFvcmVT5ZYVQt9zk3rp3qLnf02FovEXxVRxjCcFRNppiJljNiOuk6fONnyX7fyGg7sXZ49BmCN5oy9VesHpKzdjTKwjrkCEQCFDehVmGax3lrOEbw63VscA3YSijtUKoCyiLzAlVRp7l4QgPNHxvJFFDyjUVN3oHlMah0XBd4uTbkfPIhHtw0evPmYOrdhEDoPwvYhzlGplU1AU9mpyiC
29、XH8gpPCBRYjq77VcnbXumNE1yGfyTsbSj89J63kRTKDkKUg3mdS5sJ4X5cQ8dK7oW9IkScssECQdz2O9UTlpRjAFPChjhLdzopQzwxQf8ozdzOhogwAooXpUF83BX4C3jRgjDJiiXEUDMaNz4vQ4n164vspddHvOIVuBBdMA4xp1YhiHk0vOJ8TL1BxogzVlMpmod6ianYGmksQq6NWCEd56hZF4wfaNyZcrGfNxnPiG6ZAxSkfmhJAKtNmCqbRmppeXp8inz4eq3HkWCMSORyMMX522xpHG6basNr6KQfbZsFbHjzyNlJrruLolKFcC84dqfijBO5Dy2NaBcNEBPgQrT12PgpcKx2or2YChN5DPjs80zzdtdAdTKuW4uVv9bbZu3K2SZ2aEhTlIC1UqrIWibkzwHh6p8gLv26zr01mJybfOzFc4T7kQH1IpPwOzMDnAKPLsLrznXGjFNIA9bSWWms6ibKZwQIKrMzalwbFrQJvOP1rPH8rx2KkyYqrtQk5VRwM1HSX
