第7章数据查询电子课件 MySQL数据库管理与应用

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,#,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,第7章数据查询电子课件 MySQL数据库管理与应用,第,7,章 数据查询,主要内容,7.1 SELECT,语句,7.2,单表查询,7.3,连接查询,7.4,子查询,7.5,查询的集合操作,7.6,本章小结,7,.1 SELECT,语句,数据查询是指从数据库中获取所需的数据，是数据库中最常用也是最重要的操作。关系数据库管理系统使用,SELECT,语句从一个或多个表中查询数据,。,语法格式：,SELECT ALL | DISTINCT select_expr , select_expr .,FROM tbl_name, tbl_name,WHERE where_condition,GROUP BY col_name | expr | position, . WITH ROLLUP,HAVING having_condition,ORDER BY col_name | expr | position ASC | DESC, .,LIMIT offset, row_count | row_count OFFSET offset,7,.1 SELECT,语句,说明：,SELECT,子句：指定查询语句返回的列或表达式；,FROM,子句：指定查询数据的来源，可以是表、视图、查询结果；,WHERE,子句：限定选择行必须满足的一个或多个条件。,where_condition,是一个表达式，对于要选择的每一行，该表达式的计算结果为,true,。如果没有,WHERE,子句，将选择所有行；,GROUP,BY,子句：指定用于分组的列或表达式；,HAVING,子句：指定返回的分组结果必须满足的条件；,ORDER,BY,子句：指定查询结果的排序方式；,LIMIT,子句：限定查询结果包含的行数。,7.2,单表查询,7.2.1,挑选,列,SELECT,子句指示从表中检索哪些列，关键字,SELECT,后可以是*、字段列表、计算表达式,。,1,查询表中指,定列,在,SELECT,子句中指定要查看的一个或多个列的名称，如果查看多个列，列名之间用逗号分隔,。,查询,结果中列的显示顺序由,SELECT,子句指定，与表中的存储顺序无关。,7.2,单表查询,7.2.1,挑选,列,1,查询表中指,定列,【,例,7.1】,查询所有学生的学号、姓名和性别。,在,MySQL,命令行客户端输入命令：,USE jwgl,SELECT sno, sname, ssex FROM student;,7.2,单表查询,7.2.1,挑选,列,2,查询表,中,所有的,列,SELECT,子句中使用*可以获取表中所有列的值，而不需要指明各列的名称，通常在用户不清楚表中各,列名称,时使用,。,查询,结果中各列按照创建表时列的顺序显示。,【,例,7.2】,查询所有学生的基本信息。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student;,7.2,单表查询,7.2.1,挑选,列,3,使用计算表达式,实际应用中用户所需要的结果需要对表中的列进行计算才能获得，这时可以在,SELECT,子句中使用各种运算符、函数对表中的列进行计算获取所需结果。,【,例,7.4】,查询所有学生的学号、姓名和年龄。,在,MySQL,命令行客户端输入命令：,SELECT sno, sname, YEAR(SYSDATE()-YEAR(sbirthday),FROM student;,7.2,单表查询,7.2.1,挑选,列,4,改变列标题,查询结果中显示的各列的标题就是创建时定义的列名或者,SELECT,子句中使用的计算表达式,。,在,SELECT,子句中，可以给列或计算表达式指定别名作为查询结果中的显示标题。,【,例,7.4】,查询所有学生的学号、姓名和年龄，改变,列标题,。,在,MySQL,命令行客户端输入命令：,SELECT sno AS,学号, sname AS,姓名, YEAR(SYSDATE()-YEAR(sbirthday) AS,年龄,FROM student;,7.2,单表查询,7.2.1,挑选,列,4,改变列标题,注意：,AS,可以省略，但是建议在指定列别名时养成显式使用,AS,的习惯；,不,允许在,WHERE,子句中使用列别名，因为在执行,WHERE,子句时可能尚未确定列的值；,别名,可以在,GROUP BY,、,ORDER BY,或,HAVING,子句中使用。,7.2,单表查询,7.2.1,挑选,列,5,消除重复行,查询数据有时会获得重复的数据，尤其是在挑选表中的部分列的时候,。,可以,在,SELECT,子句中使用,DISTINCT,从查询结果中删除重复的行，使查询结果更加简洁。,【,例,7.5】,查询所有学生所属的专业。,在,MySQL,命令行客户端输入命令：,SELECT DISTINCT mno FROM student;,7.2,单表查询,7.2.2,选择行,WHERE,子句用于过滤表中的数据，对,FROM,子句中指定的表中的行进行判断，只有满足,WHERE,子句中的筛选条件的行才会返回，不满足条件的行不会出现在查询结果中,。,语法,格式为,：,WHERE,where_condition,其中，,where_condition,指定从表中选择行的筛选条件，是由比较运算符、范围比较运算符、,IN,运算符、字符串模式匹配运算符、空值判断运算符以及逻辑运算符构成的表达式，表达式的运算结果为逻辑值真或假。,7.2,单表查询,7.2.2,选择行,1.,比较运算符,比较运算符用于比较两个表达式的值，,WHERE,子句中常用的比较运算符有,=,（等于）、,（不等于）、,!=,（不等于）、,（小于）、,!,（不小于）、,（大于）、,!,（不大于）和,=,（大于等于）。,【,例,7.6】,查询所有男生的基本信息。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student WHERE ssex=,男,;,【,例,7.7】,查询,2001-1-1,之后出生的学生。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student WHERE sbirthday2003-1-1;,7.2,单表查询,7.2.2,选择行,2.,范围比较运算符,范围比较运算符,BETWEENAND,用于判断字段或表达式的值是否在,BETWEEN,和,AND,设定的范围内，该范围是一个连续的闭区间。如果表达式的值在指定的范围内则返回该行，否则不返回。语法格式为：,col_name,| expr NOT BETWEEN value1 AND value2,【,例,7.8】,查询年龄在,18,到,20,之间的学生的学号、姓名、性别和年龄。,在,MySQL,命令行客户端输入命令：,SELECT sno, sname, ssex, YEAR(SYSDATE()-YEAR(sbirthday) FROM student,WHERE YEAR(SYSDATE()-YEAR(sbirthday) BETWEEN 18 AND 20;,7.2,单表查询,7.2.2,选择行,3. IN,运算符,IN,运算符用于判断字段或表达式的值是否在指定的集合中，该集合是由逗号分隔的一些离散值构成，不是连续的范围。如果表达式的值在指定的集合中则返回该行，否则不返回。语法格式为：,col_name,| expr NOT IN (value1, value2,),【,例,7.9】,查询年龄为,18,或,20,的学生的学号、姓名、,性别、年龄,在,MySQL,命令行客户端输入命令：,SELECT sno, sname, ssex, YEAR(SYSDATE()-YEAR(sbirthday) FROM student,WHERE YEAR(SYSDATE()-YEAR(sbirthday) IN (18, 20);,7.2,单表查询,7.2.2,选择行,4.,空值判断运算符,NULL,表示“缺少的未知值”，它的处理方式与其他值有所不同，不能使用诸如,=,、,、,、,!=,之类的比较运算符来测试空值，因为使用,NULL,进行,任何比较,的结果也是,NULL,。判断字段或表达式的值是否为空值使用,IS NULL,或,IS NOT NULL,，语法,格式：,col_name,| expr IS NOT NULL,【,例,7.10】,查询未确定专业的学生。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student,WHERE mno IS NULL;,7.2,单表查询,7.2.2,选择行,5.,模式匹配运算符,LIKE,语法,格式为：,col_name,| expr NOT LIKE pat_string ESCAPE escape_char,其中，,pat_string,是一个字符串，可以包含普通字符和通配符。,escape_char,表示自己定义的转义字符。,MySQL,中常用的通配符有：,%,：匹配零个或多个任意字符构成的字符串；,_(,下划线,),：匹配任意的单个字符,。,7.2,单表查询,7.2.2,选择行,5.,模式匹配运算符,【,例,7.11】,查询学号以,2021,开头的学生。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student,WHERE sno LIKE 2021,%;,【,例,7.12】,查询姓名的第二个字为,明,的学生。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student,WHERE sname LIKE _,明,%;,7.2,单表查询,7.2.2,选择行,5.,模式匹配运算符,【,例,7.13】,查询姓名以,_,结尾的学生。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student,WHERE sname LIKE %_;,或者：,SELECT * FROM student,WHERE sname LIKE %$_ ESCAPE,$;,说明：这里的,$,也可以是其它字符,7.2,单表查询,7.2.2,选择行,5.,模式匹配运算符,REGEXP,或,RLIKE,MySQL,中可以使用,REGEXP,或,RLIKE,关键字指定正则表达式的字符匹配模式，实现复杂搜索的,模式匹配。,语法,格式为：,col_name,| expr NOT REGEXP|RLIKE pat_string,pat_string,是一个正则表达式，可以包含普通字符和特殊字符,。,7.2,单表查询,7.2.2,选择行,5.,模式匹配运算符,字符,说明,示例,匹配值示例,匹配文本的开始字符,a,匹配以字母,a,开头 的字符串,apple,、,ant,、,answer,$,匹配文本的结束字符,gh$,匹配以,gh,结尾的字 符串,laugh,、,cough,、,heigh,.,匹配任何单个字符,s.t,匹配任何,s,和,t,之间有一个字符,sit,、,sat,、,set,*,匹配零个或多个在它前面的字符,a*t,匹配字符,t,前面有任意个字符,a,at,、,aat,、,t,+,匹配前面的字符,1,次或多次,fe+,匹配以,f,开头，后面至少跟一个,e,fe,、,fee,、,feee,匹配包含指定字符的文本,ou,soul,、,thought,、,about,字符集合,匹配字符集合中的任何一个字符,ab,匹配,a,或者,b,fat,、,bit,、,bet,、,abs,匹配不在括号中的任何字符,abc, 匹配任何不包含,a,、,b,或,c,的字符串,duck,、,egg,、,fly,字符串,n,匹配前面的字符串至少,n,次,a2,匹配,2,个或更多的,a,aa,、,aaaa,、,aaaaaaa,字符串,n,m,匹配前面的字符串至少,n,次，至多,m,次,a2,4,匹配最少,2,个， 最多,4,个,a,aa,、,aaa,、,aaaa,正则表达式的常用特殊字符,7.2,单表查询,7.2.2,选择行,5.,模式匹配运算符,【,例,7.14】,查询姓,张,或姓,李,的学生。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student,WHERE sname REGEXP ,张李,;,【,例,7.15】,查询姓名的第二个字为,明,的学生。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student,WHERE sname REGEXP .,明,;,7.2,单表查询,7.2.2,选择行,6.,逻辑运算符,WHERE,子句中可以使用逻辑运算符将多个查询条件组合起来实现复杂的筛选条件,。,常用,的逻辑运算符有,AND(,与,),、,OR(,或,),和,NOT(,非,),，其中,NOT,的优先级最高，,AND,次之，,OR,的优先级最低,。,7.2,单表查询,7.2.2,选择行,6.,逻辑运算符,【,例,7.16】,查询,0101,专业的男生的信息。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student,WHERE ssex= ,男, AND mno=0101;,【,例,7.17】,查询,0101,专业或,0102,专业的学生信息。,在,MySQL,命令行客户端输入命令：,SELECT * FROM student,WHERE mno=0101 OR mno=0102;,7.2,单表查询,7.2.3,分组与统计,1.,聚合函数,聚合,函数用于对一组数据进行计数或统计，获得一个计算结果。,函数名,语法格式,功能描述,COUNT,COUNT(*),返回,SELECT,语句检索到的行的个数,COUNT(DISTINCT col_name|expression),返回,SELECT,语句检索到的行中指定列或表达式的非空值的个数，如果使用,DISTINCT,则只对不重复的值计数,SUM,SUM(DISTINCT col_name|expression),返回,SELECT,语句检索到的行中指定列或表达式的值的和，忽略空值，如果使用,DISTINCT,则只对不重复的值求和,AVG,AVG(DISTINCT col_name|expression),返回,SELECT,语句检索到的行中指定列或表达式的值的平均值，忽略空值，如果使用,DISTINCT,则去除重复值后再求平均值,MAX,MAX(col_name|expression),返回,SELECT,语句检索到的行中指定列或表达式的最大值，忽略空值,MIN,MIN(col_name|expression),返回,SELECT,语句检索到的行中指定列或表达式的最小值，忽略空值,常用聚合函数,7.2,单表查询,7.2.3,分组与统计,1.,聚合函数,【,例,7.18】,查询,0101,专业的学生个数。,在,MySQL,命令行客户端输入命令：,SELECT COUNT(*) FROM student WHERE mno=0101;,7.2,单表查询,7.2.3,分组与统计,1.,聚合函数,【,例,7.19】,查询,student,表中的学生的民族个数。,在,MySQL,命令行客户端输入命令：,SELECT COUNT(DISTINCT snation) FROM student;,【,例,7.20】,查询,score,表中学号为,20190101001,的学生所选课程的总分、平均分、最高分和最低分。,在,MySQL,命令行客户端输入命令：,SELECT SUM(grade) AS,总分, AVG(grade) AS,平均分, MAX(grade) AS,最高分, MIN(grade) AS,最低分,FROM score,WHERE sno=20190101001;,7.2,单表查询,7.2.3,分组与统计,2. GROUP BY,子句,实际应用中有时需要将表中的数据或满足条件的数据按照某些字段的值进行分组，然后对每组中的数据分别进行统计，得到多个组的汇总结果,。,可以,利用,GROUP BY,子句创建分组数据，语法格式为：,GROUP,BY col_name | expr | position, . WITH ROLLUP,GROUP BY,后的列或表达式称为分组列或分组表达式，可以是一个也可以是多个。使用,GROUP BY,子句时，将分组列或分组,表达式取值相同的,行作为一组，对每组数据执行聚合函数，每组产生一行统计结果。,7.2,单表查询,7.2.3,分组与统计,2. GROUP BY,子句,【,例,7.21】,查询各专业的学生人数。,在,MySQL,命令行客户端输入命令：,SELECT mno, COUNT(*) AS,人数,FROM student,GROUP BY mno;,7.2,单表查询,7.2.3,分组与统计,2. GROUP BY,子句,【,例,7.22】,查询各专业的男生和女生人数。,在,MySQL,命令行客户端输入命令：,SELECT mno, ssex, COUNT(*) AS,人数,FROM student,GROUP BY mno, ssex;,7.2,单表查询,7.2.3,分组与统计,2. GROUP BY,子句,GROUP,BY,子句中可以出现多个分组列或表达式，用逗号分隔，在所有分组列或表达式上取值相同的数据被认为是一组；,如果,查询语句中使用了,WHERE,子句，先在表中筛选出满足,WHERE,子句中条件的记录，然后再将这些记录按照,GROUP BY,子句进行分组；,SQL-92,及更早版本不允许,SELECT,列表、,HAVING,条件或,ORDER BY,列表出现不属于,GROUP BY,分组列或分组表达式的内容。,7.2,单表查询,7.2.3,分组与统计,2. GROUP BY,子句,SQL:1999,及更高版本允许根据字段之间的函数依赖关系决定此类查询是否合法，如果,SELECT,子句中的非分组列或表达式在功能上依赖于,GROUP BY,子句中的分组列，则查询是合法的,。,例如下面的查询语句是合法的，因为,mno,列为主键，,mname,列依赖于,mno,列：,SELECT major.mno, mname, COUNT(*) AS,人数,FROM student, major,WHERE student.mno=major.mno,GROUP BY mno;,7.2,单表查询,7.2.3,分组与统计,2. GROUP BY,子句,MySQL,中如果关闭了,ONLY_FULL_GROUP_BY,模式（,MySQL 8.0,的默认设置），则,MySQL,不执行,SQL,标准；,MySQL,中如果开启,ONLY_FULL_GROUP_BY,模式，则,MySQL,执行,SQL:1999,标准。,7.2,单表查询,7.2.3,分组与统计,2. GROUP BY,子句,GROUPBY,子句允许,WITH ROLLUP,关键字，该对分组统计结果进行更高级别的汇总从而产生额外的汇总结果。,【,例,7.23】,查询各专业的男生和女生人数，使用,WITH ROLLUP,关键字。,在,MySQL,命令行客户端输入命令：,SELECT mno, ssex, COUNT(*) AS,人数,FROM student,GROUP BY mno, ssex WITH ROLLUP;,7.2,单表查询,7.2.3,分组与统计,3. HAVING,子句,利用,GROUP BY,子句和聚合函数对记录进行分组和汇总之后，还可以利用,HAVING,子句对分组汇总之后的结果进行,筛选,语法格式：,HAVING,having_condition,HAVING,子句与,WHERE,子句一样，指定筛选条件。,WHERE,子句指定对,FROM,子句中的表或视图中数据的筛选条件，,HAVING,子句指定对分组汇总数据筛选的条件，查询结果仅包括满足条件的,组。,7.2,单表查询,7.2.3,分组与统计,3. HAVING,子句,SELECT,语句中如果同时存在,WHERE,子句、,GROUP BY,子句和,HAVING,子句，执行顺序为先执行,WHERE,子句，再执行,GROUP BY,子句，然后执行,HAVING,子句。先用,WHERE,子句从数据源中筛选出满足条件的记录，再用,GROUP BY,子句对筛选出的记录进行分组汇总，然后再根据,HAVING,子句的条件筛选出符合条件的组；,HAVING,子句可以使用聚合函数，,WHERE,子句中不能使用聚合函数，因为执行,WHERE,子句时还没有分组没有执行聚合函数,；,7.2,单表查询,7.2.3,分组与统计,3. HAVING,子句,SQL,标准要求,HAVING,必须只引用,GROUP BY,子句中的列或聚合函数。但是，,MySQL,支持对该行为的扩展，并且允许引用,SELECT,子句中的列以及外部子查询中的列。,能,出现在,WHERE,子句的条件应该在,WHERE,子句中使用，不应该使用,HAVING,；,HAVING,子句中可以使用,SELECT,子句中的列别名。,7.2,单表查询,7.2.3,分组与统计,3. HAVING,子句,【,例,7.24】,查询平均成绩高于,85,的学生学号和平均成绩。,在,MySQL,命令行客户端输入命令：,SELECT sno, AVG(grade) AS,平均成绩,FROM score,GROUP BY sno,HAVING AVG(grade)85;,或者：,SELECT sno, AVG(grade) AS,平均成绩,FROM score,GROUP BY sno,HAVING,平均,成绩,85;,7.2,单表查询,7.2.4,对查询结果排序,ORDER BY,子句可以对查询结果进行排序，使查询结果按照用户指定的顺序显示，其语法格式为：,ORDER,BY col_name | expr | position ASC | DESC,.,说明：,ORDER,BY,子句指定对查询结果进行排序的依据，可以是列名、表达式、列的位置序号,；,排序,依据可以是一个也可以是多个，如果是多个，只有当待排序数据在第一列上的值相同时才会依据第二列排序，依次,类推,7.2,单表查询,7.2.4,对查询结果排序,说明：,ASC,| DESC,指定排序的方式，,ASC,表示升序排序，为默认排序方式，,DESC,表示降序,ORDER,BY,子句中可以使用,SELECT,子句中定义的别名，也可以使用,SELECT,子句中未出现的列或表达式作为排序依据,。,【,例,7.25】,查询编号为,101,的课程的成绩信息，按照成绩从高到低排序。,SELECT,sno, grade FROM score WHERE cno=101 ORDER BY grade DESC;,也,可以是：,SELECT sno, grade FROM score WHERE cno=101 ORDER BY 2 DESC,;,7.2,单表查询,7.2.4,对查询结果排序,【,例,7.26】,查询各专业的学生人数，按照人数从低到高排序。,在,MySQL,命令行客户端输入命令：,SELECT mno, COUNT(*) AS,人数,FROM student,GROUP BY mno,ORDER BY COUNT(*);,或者：,SELECT mno, COUNT(*) AS,人数,FROM student,GROUP BY mno,ORDER BY,人数,;,7.2,单表查询,7.2.5,限制查询结果的数量,LIMIT,子句可用于限制,SELECT,语句返回的行数，其语法格式为：,LIMIT offset, row_count,或：,LIMIT row_count OFFSET offset,说明：,offset,指定要返回的第一行的偏移量，初始行的偏移量为,0,（不是,1,），如果不指定偏移量，从第一行开始显示；,row_count,指定要返回的最大行数。,7.2,单表查询,7.2.5,限制查询结果的数量,【,例,7.27】,查询各专业的学生人数，返回人数最多的,3,个专业。,在,MySQL,命令行客户端输入命令：,SELECT mno, COUNT(*) AS,人数,FROM student,GROUP BY mno,ORDER BY COUNT(*) DESC,LIMIT 3;,或者：,SELECT mno, COUNT(*) AS,人数,FROM student,GROUP BY mno,ORDER BY COUNT(*) DESC,LIMIT 3 OFFSET 0;,7.3,连接查询,在查询数据时一个表往往不能提供想要的所有信息，需要从多个表中查询数据，这就需要进行多表连接查询。,所谓的连接是指将多个“小表”中的数据按照给定的条件合并成一张“大表”。例如将,major,表和,student,表按照表中的记录在,mno,列的值相等（,major.mno=student.mno,）这个条件进行连接生成一个新的结果,集。,7.3,连接查询,student,表,major,表,连接后产生的结果集,7.3,连接查询,7.3.1,交叉,连接,两个表的交叉连接的结果为两个表的笛卡儿积，即由第一个表的每一行与第二个表的每一行连接得到的结果集。结果集中的行数是两个表的行数的成绩，列数是两个表的列数的和。交叉连接的语法格式为：,SELECT * FROM tbl_name1, tbl_name2, tbl_name3,或者：,SELECT * FROM tbl_name1 CROSS JOIN tbl_name2 CROSS JOIN tbl_name3,交叉连接查询的结果中有大量无意义的数据，在实际应用中很少用到交叉连接查询，应该尽量避免。,7.3,连接查询,7.3.2,内连接,内连接是最常用的连接查询，通过设置连接条件限制两个表,中相,匹配（满足连接条件）的行，只有满足连接条件的记录才会出现在查询结果中。内连接查询的语法格式为：,SELECT ALL | DISTINCT select_expr , select_expr .,FROM tbl_name1, tbl_name2, tbl_name3,WHERE join_condition AND where_conditon,或者：,SELECT ALL | DISTINCT select_expr , select_expr .,FROM tbl_name1 INNER JOIN tbl_name2 ON join_condition INNER JOIN tbl_name3 ON join_condition,WHERE where_conditon,7.3,连接查询,7.3.2,内连接,其中,，,join_condition,表示连接条件，用于在两表之间匹配记录，通常由两个表的共同字段（或相关字段）和关系,运算符组成,。,where_condtion,为筛选条件，用于限制要在结果集中包括哪些行。,7.3,连接查询,7.3.2,内连接,1.,等值连接,等值连接的连接条件中使用的运算符为“,=”,，条件格式通常为“表,1.,列名,=,表,2.,列名”，查询结果只包含两个表中在指定字段上的值相等的行。,【,例,7.28】,查询每个学生和其所属的专业的详细信息。,在,MySQL,命令行客户端输入命令：,SELECT *,FROM student, major,WHERE student.mno=major.mno;,或者：,SELECT *,FROM student INNER JOIN major ON student.mno=major.mno,;,7.3,连接查询,7.3.2,内连接,1.,等值连接,需要注意的是，连接查询语句中如果用到两个表中的同名字段，必须使用“表名,.,列名”的方式进行限制。例如，查询每个学生的学号、姓名、性别、所属专业的编号、名称，因为,major,表和,student,表都有,mno,字段，,SELECT,子句中的,mno,字段如果不加表名限制，系统无法确定从哪个表中提取,mno,的值，就会出现“,is ambiguous”,的错误。,7.3,连接查询,7.3.2,内连接,2.,非等值连接,非等值连接的连接条件中使用的运算符不是“,=”,，而是其他的关系运算符。实际应用中非等值连接使用的比较少,。,示例：以查询学生的成绩等级为例说明非等值连接的使用,创建一个,grade_level,表：,CREATE TABLE grade_level,(,from_grade tinyint,to_grade tinyint,level ENUM(,优秀, ,良好, ,中等, ,及格, ,不及格,),);,7.3,连接查询,7.3.2,内连接,2.,非等值连接,示例,：以查询学生的成绩等级为例说明非等值连接的使用,添加,数据：,INSERT INTO grade_level,VALUES(90, 100, ,优秀,), (80, 89, ,良好,), (70, 79, ,中等,), (60, 69, ,及格,), (0, 59, ,不及格,);,7.3,连接查询,7.3.2,内连接,2.,非等值连接,示例,：以查询学生的成绩等级为例说明非等值连接的使用,【,例,7.29】,查询每个学生的学号、所选课程的编号、成绩和成绩等级。,SELECT,score.*, level,FROM score, grade_level,WHERE score.grade BETWEEN grade_level.from_grade AND grade_level.to_grade ;,或者：,SELECT score.*, level,FROM score, grade_level,ON score.grade BETWEEN grade_level.from_grade AND grade_level.to_grade ;,7.3,连接查询,7.3.2,内连接,3.,自然连接,自然连接是一种特殊的等值连接，不需要指定连接条件，由两个表中的同名字段自动进行等值比较。如果使用的是,SELECT *,，因为是同名字段的等值比较连接，所以结果集中的同名字段的值是完全一样的，自然连接在结果集中只包含一列同名字段和它的值。自然连接用,NATURAL JOIN,关键字实现。,【,例,7.30】,利用自然连接查询每个学生和其所属的专业的详细信息。,在,MySQL,命令行客户端输入命令：,SELECT *,FROM student NATURAL JOIN major;,7.3,连接查询,7.3.2,内连接,4.,自连接,自连接是指一个表同其自身进行连接，即,FROM,子句中涉及的表为同一个表。,【,例,7.31】,查询和,刘丽,同一个专业的学生信息。,在,MySQL,命令行客户端输入命令：,SELECT t1.*,FROM student t1, student t2,WHERE t1.mno=t2.mno AND t1.sname,刘丽, AND t2.sname=,刘丽,;,7.3,连接查询,7.3.2,内连接,4.,自连接,本例中参与连接的表物理上为同一个表,student,，逻辑上我们为其设置不同的别名,t1,和,t2,，作为两个表看待。,为表设置别名的方法是在,FROM,子句中表名的后面加上“,AS,别名”，,AS,可以省略,。,除了,在自连接中给表设置别名之外，其他的,SELECT,语句中如果需要都可以给表设置别名，比如表名比较复杂，为了简化语句提高可读性，就可以给表起别名,。,需要,注意的是，如果为表指定了别名，则,SELECT,语句中所有使用表名的地方都必须使用别名，不能再用原表名，否则就会提示,出错。,7.3,连接查询,7.3.3,外连接,内连接查询的结果中仅包含两个表中满足连接条件的行，一个表中的记录如果在另一个表中找不到相匹配的行就不会出现在查询结果中,。,有时候,我们需要在查询结果中包含一个表中的所有记录，即使它在另一个表中没有相匹配的行，这时可以使用外连接查询。,MySQL,中外连接查询包括左外连接和右外连接。,7.3,连接查询,7.3.3,外连接,左外连接的查询结果中包含两个表连接后满足连接条件行（内连接的查询结果）和,FROM,子句中指定的左表中不满足条件的行，即左表中的所有的行都会包含在查询结果中。如果左表中的某行记录在右表中没有相匹配的记录，查询结果中这些行在右表对应列的值为,NULL,。,左外连接查询的语法格式为：,SELECT ALL | DISTINCT select_expr , select_expr .,FROM tbl_name1 LEFT OUTER JOIN tbl_name2 ON join_condition,7.3,连接查询,7.3.3,外连接,右外连接的查询结果中包含两个表连接后满足连接条件的行（内连接的查询结果）和,FROM,子句中指定的右表中不满足条件的行，即右表中的所有的行都会包含在查询结果中。如果右表中的某行记录在左表中没有相匹配的记录，查询结果中这些行在左表对应列的值为,NULL,。,右外连接查询的语法格式为：,SELECT ALL | DISTINCT select_expr , select_expr .,FROM tbl_name1 RIGHT OUTER JOIN tbl_name2 ON join_condition,7.3,连接查询,7.3.3,外连接,【,例,7.32】,利用左外连接查询每个专业的信息和该专业的学生信息。,在,MySQL,命令行客户端输入命令：,SELECT *,FROM major LEFT OUTER JOIN student ON major.mno=student.mno;,7.4,子查询,如果一个,SELECT,语句嵌套在另一个,SQL,语句（例如,SELECT,语句、,INSERT,语句、,UPDATE,语句、,DELETE,语句）中，则该,SELECT,语句称为子查询（也叫内层查询），包含子查询的,SQL,语句成为主查询（也叫外层查询）。,子查询可以嵌套多层，即一个子查询中可以嵌套其他子查询，每层嵌套都需要用圆括号,( ),括起来。嵌套查询的处理过程由内向外，每个子查询在其上级查询处理之前执行。通过嵌套查询可以用一系列简单查询构成复杂查询，从而增强,SQL,语句的查询能力。,最常见的子查询是在,WHERE,子句和,HAVING,子句中，与,IN,运算符、比较运算符或,EXISTS,一起,构成筛选条件。,7.4,子查询,7.4.1 IN,子,查询,IN,子查询是指外层查询和子查询之间使用,IN,进行连接，判断某个列或表达式的值是否在子查询的结果中。,IN,子查询的一般语法格式为：,WHERE col_name | expr NOT IN (SELECT),HAVING col_name | expr NOT IN (SELECT),【,例,7.33】,查询选修了,101,课程的学生的学号和姓名。,在,MySQL,命令行客户端输入命令：,SELECT sno, sname,FROM student,WHERE sno IN (SELECT sno FROM score where cno=101);,7.4,子查询,7.4.2,比较子查询,可以使用比较运算符将外层查询和子查询进行连接，将某个列或表达式的值和子查询的结果进行比较。比较运算符包括,=,、,、,!=,、,、,=,、,!,、,、,=,、,!,ANY,表示大于子查询结果中的某一个，大于最小值即可,ALL,表示大于子查询结果中的所有值，相当于大于最大值,t.avggrade;,7.5,查询的集合操作,集合操作是指将两个或多个,SELECT,语句的查询结果合并到一起，以完成复杂的查询任务。集合操作主要包括并集、交集和差集，,MySQL,目前仅支持并操作，由运算符,UNION,实现，语法格式为：,SELECT .,UNION ALL | DISTINCT SELECT .,UNION ALL | DISTINCT SELECT .,7.5,查询的集合操作,默认情况下，,MySQL,将从联合结果中删除重复的行，与使用,DISTINCT,关键字具有相同的效果。使用,ALL,关键字，不会删除重复的行，结果包括所有,SELECT,语句中的所有匹配行。,使用,UNION,操作的,SELECT,语句必须返回相同的列数，列的顺序必须一致，数据类型必须兼容，如果各,SELECT,语句中的列名不同，查询结果集中的列标题来自第一个,SELECT,语句,。,7.5,查询的集合操作,【,例,7.37】,使用,UNION,操作实现全外连接。,在,MySQL,命令行客户端输入命令：,SELECT *,FROM student LEFT OUTER JOIN major ON student.mno=major.mno,UNION,SELECT *,FROM student RIGHT OUTER JOIN major ON student.mno=major.mno;,7.6,本章小结,SELECT,语句是,SQL,语言中功能最强大、使用最频繁的语句之一，用于从数据库中查询符合条件的数据。本章主要介绍了,MySQL,中的数据查询操作，包括,SELECT,语句的语法、简单查询、分组统计、连接查询、子查询以及查询的集合操作,。,通过,本章的学习，读者应该掌握,SELECT,语句的语法以及灵活应用,SELECT,语句实现具体的查询任务。,