MySQL教程（第4版） 课件 第4章 查询和视图

第4章

查询和视图——关系运算基础关系运算基础关系运算的特点：运算的对象和结果都是表。例如，学生表、课程表和成绩表所示。学号姓名专业名性别出生日期总学分备注221101王林计算机12004-02-1015

221102程明计算机12005-02-0115

221103王燕计算机02003-10-0615参加校女子足球队221201刘华通信工程12004-06-1013辅修计算机专业221202王林通信工程12004-01-2913

221204马琳琳通信工程02003-02-1015

学生表关系运算基础课程表课程号课程名开课学期学时学分101计算机导论1805102程序设计与语言2684206离散数学4684208数据结构5684209操作系统6684210计算机原理5855212数据库原理7684301计算机网络7513302软件工程7513成绩表学号课程号成绩2211011018022110110278221101206762211021027822110220652221103101622211031025022110320681221201101802212021015501选择（Selection）运算选择（Selection）运算选择运算的记号为

F(R)。其中，

是选择运算符，下标F是一个条件表达式，R是被操作的表。【例】若要在学生表中找出性别为女的行形成一个新表，则运算式为

F(学生)。其中，F：性别=“女”。该选择运算的结果如表。学号姓名专业名性别出生日期总学分备注221103王燕计算机02003-10-0615参加校女子足球队221204马琳琳通信工程02003-02-1015

02投影（Projection）运算投影（Projection）运算投影运算也是单目运算，该运算从表中选出指定的属性值组成一个新表，记为

A(R)。其中，A是属性名（列名）表，R是表名。【例】若在学生表中对学号、姓名和总学分投影，运算式如下：

学号，姓名，总学分(学生)该运算得到如表所示的新表。学号姓名总学分221101王林15221102程明15221103王燕15221201刘华13221202王林13221204马琳琳1503连接（Join）运算等值连接自然连接连接（Join）运算1．等值连接两个表连接的常用条件是两个表的某些列值相等，这样的连接称为等值连接，记为R⋈FS。其中，R、S是被操作的表，F是条件。【例】若表R和表S分别为成绩表和学生表（

学号，姓名，总学分(学生情况)结果），则R⋈FS如表。其中，F为“成绩表.学号=学生表.学号”。成绩表.学号课程号成绩学生表.学号姓名总学分22110110180221101王林1522110110278221101王林1522110120676221101王林1522110210278221102程明1522110220652221102程明1522110310162221103王燕1522110310250221103王燕1522110320681221103王燕1522120110180221201刘华1322120210155221202王林13连接（Join）运算2．自然连接自然连接运算记为R⋈S。其中，R和S是参与运算的两个表。【例】若R表和S表分别为成绩表和课程表所示，则R⋈S如表。学号课程号成绩课程名开课学期学时学分22110110180计算机导论180522110110278程序设计与语言268422110120676离散数学468422110210278程序设计与语言268422110220652离散数学468422110310162计算机导论180522110310250程序设计与语言268422110320681离散数学468422120110180计算机导论180522120210155计算机导论1805第4章

查询和视图——数据库表查询数据库表查询SELECT语句格式如下：SELECT [ALL|DISTINCT|DISTINCTROW]表达式,...] [FROM源表,...] /*FROM子句*/ [WHERE条件] /*WHERE子句*/ [GROUPBY{列名|表达式|输出项号}[ASC|DESC],...[WITHROLLUP]] /*GROUPBY子句*/ [HAVING条件] /*HAVING子句*/ [ORDERBY{列名|表达式|输出项号}[ASC|DESC],...] /*ORDERBY子句*/ [LIMIT[偏移,]行数 /*LIMIT子句*/ [UNION[ALL|DISTINCT]SELECT语句] /*UNION子句*/ [INTO变量名,...] [INTOOUTFILE'文件名'[CHARACTERSET字符集名]数据库表查询说明：（1）最简单的SELECT语句是“SELECT表达式”，显示表达式的值。表达式可以是常量、列、函数以及由运算符连接起来的式子。例如，如果变量@x值为2，下列语句显示3：SELECT@x+1;（2）ALL|DISTINCT|DISTINCTROW：ALL（默认）返回所有的匹配行；DISTINCT和DISTINCTROW是同义词，用于消除结果集中的重复行。（3）INTOOUTFILE'文件名'：将查询结果导出到指定文件中，这个文件被创建在服务器主机中。（4）所有被使用的子句必须严格按顺序编写。例如，一个HAVING子句必须位于GROUPBY子句之后，并位于ORDERBY子句之前。（5）格式没有包含MySQL相对于标准SQL的扩展选项（以SQL_为开头），一般情况下不用。01选择输出项选择指定的列替换查询结果中的数据计算列值消除结果集中的重复行聚合函数选择输出项1．选择指定的列SELECT*|列名[AS]别名|表达式[AS]别名，...说明：（1）选择表中的部分列，各列名之间要以逗号分隔，“*”表示所有列。（2）用AS子句可更改查询结果的列标题，当自定义的列标题中含有空格时，必须使用引号将标题括起来。不允许在WHERE子句中使用列别名。【例】按专业查询学生表中各个学生的学号、姓名和总学分。USExscj;SELECT*FROMxsWHERE专业='计算机'; SELECT学号ASID,姓名ASNAME,总学分AS'Totalcredit' FROMxs WHERE专业='通信工程'; 选择输出项2．替换查询结果中的数据语句如下：CASE WHEN条件1THEN表达式1 WHEN条件2THEN表达式2 …… ELSE表达式nEND其中：将符合条件i的值用表达式i值替换。【例】把成绩表百分制成绩分成等级。USExscj;SELECT学号,成绩, CASE WHEN成绩>=90THEN'优秀' WHEN成绩>=80AND成绩<90THEN'良好' WHEN成绩>=70AND成绩<80THEN'中等' WHEN成绩>=60AND成绩<70THEN'及格' ELSE'不及格' ENDAS等级 FROMcj WHERE课程号='206';选择输出项查询结果如图。选择输出项3．计算列值在结果中可以输出对包含列在内的表达式计算后的值，即可使用表达式作为输出项。【例】查询2022年入学学生的年龄。SELECT学号,姓名,出生日期,year(now())-year(出生日期)+1AS年龄 FROMxs WHEREleft(学号,2)='22';查询结果如图。选择输出项4．消除结果集中的重复行对表查询输出部分列时，可能会出现重复行。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行。【例】查询成绩表已有课程号。SELECTDISTINCT课程号FROMcj;查询结果如图。选择输出项5．聚合函数聚合函数常用于对一组值进行计算，然后返回单个值。除COUNT()函数外，聚合函数都会忽略空值。聚合函数通常与GROUPBY子句一起使用。表列出了一些常用的聚合函数。函数名说明COUNT求组中项数，返回int类型整数MAX求最大值MIN求最小值SUM返回表达式中所有值的和AVG求组中值的平均值STD或STDDEV返回给定表达式中所有值的标准差VARIANCE返回给定表达式中所有值的方差GROUP_CONCAT返回由属于一组的列值连接组合而成的结果BIT_AND逻辑或BIT_OR逻辑与BIT_XOR逻辑异或选择输出项说明：（1）COUNT()函数：用于统计组中满足条件的行数或总行数，统计的是行中非NULL值的数目，格式如下。COUNT({[ALL|DISTINCT]表达式}|*)其中，表达式的数据类型是除BLOB或TEXT之外的任何类型；ALL（默认）表示对所有值进行统计，DISTINCT表示去除重复值。（2）MAX()函数和MIN()函数：分别用于求所有项表达式的最大值与最小值，忽略NULL值，格式如下。MAX/MIN([ALL|DISTINCT]表达式)其中，表达式的数据类型可以是数字、字符和时间日期类型。（3）SUM()函数和AVG()函数：分别用于求所有项表达式值的总和与平均值，格式如下。SUM/AVG([ALL|DISTINCT]表达式)其中，表达式的数据类型只能是数值型数据。选择输出项【例】求选修101号课程的学生的人数、最高分、最低分和平均成绩。SELECTCOUNT(成绩),MAX(成绩),MIN(成绩),AVG(成绩) FROMcj WHERE课程号='101';查询结果如图。选择输出项（4）VARIANCE()、STDDEV()和STD()函数：分别用于求所有项表达式值的方差和标准差，格式如下。VARIANCE/STDDEV([ALL|DISTINCT]表达式)方差的计算步骤：①计算相关列的平均值；②求列中的每一个值和平均值的差；③计算差值的平方的总和；④用总和除以（列中的）值的个数得到结果。STDDEV()或STD()函数用于计算标准差。标准差等于方差的平方根。所以它和SQRT()或VARIANCE()这两个表达式是等效的。【例】求选修101号课程等效学生成绩的标准差和方差。SELECTSTDDEV(成绩),VARIANCE(成绩) FROMcj WHERE课程号='101';查询结果如图。选择输出项（5）GROUP_CONCAT()函数：它返回来自一个组指定表达式的所有非NULL值，这些值一个接着一个放置，中间用逗号隔开，并表示为一个长长的字符串，这个字符串的长度是有限制的，标准值是1024，格式如下。GROUP_CONCAT({[ALL|DISTINCT]表达式}|*)【例】求选修了206号课程的学生的学号。SELECTGROUP_CONCAT(学号) FROMcj WHERE课程号='206';查询结果如图。选择输出项（6）BIT_AND()函数、BIT_OR()函数和BIT_XOR()函数：它们与二进制运算符&（与）、|（或）和^（异或）相对应格式如下。BIT_AND|BIT_OR|BIT_XOR({[ALL|DISTINCT]表达式}|*)【例】查询（xsk）学生表共同的爱好和所有的爱好。USExscj；SELECT*FROMxsk; #（a）SELECTBIT_OR(爱好),BIT_AND(爱好)FROMxsk; #（b）查询结果如图。

02限制输出行限制输出行LIMIT子句用于限制输出起始位置和输出行数，格式为。LIMIT[偏移,]行数说明：限制返回查询结果集不超过指定行数。包含偏移，从偏移位置开始，否则从第1行开始。【例】查找学生表中学号最靠前的5位学生的信息。SELECT学号,姓名,专业,性别,出生日期,总学分 FROMxs ORDERBY学号 LIMIT5; #（a）SELECT学号,姓名,专业,性别,出生日期,总学分 FROMxs ORDERBY学号 LIMIT5,5; #（b）限制输出行查询结果如图。

03指定查询数据源全连接JOIN连接指定查询数据源SELECT指定查询对象（数据源）：FROM源表,...其中，源表：表名[AS别名]|视图名[AS别名]|表连接

说明：（1）SELECT指定查询数据源为数据库的表、视图或多个表组成的连接。（2）别名为表指定的另一个（更直观）名字。如果指定了表别名，则这条SELECT语句中的其他子句都必须使用表别名来代替原表名。（3）直接指定表名属于当前数据库，否则表名前需要加数据库名作为前缀。例如：USExscj;SELECT*FROMtest.memoryb;当然，在SELECT关键字后指定列名的时候也可以在列名前带上所属数据库和表的名字。指定查询数据源1.全连接将各个表用逗号分隔，就指定了一个全连接。FROM子句产生的中间结果是一个新表，表的每行都与其他表中的每行交叉以产生所有可能的组合，列包含了所有表中出现的列，也就是笛卡儿积。【例】查找所有学生选过的课程信息。SELECTDISTINCTkc.课程名,cj.课程号,学时,学分 FROMkc,cj WHEREkc.课程号=cj.课程号;查询结果如图。指定查询数据源2.JOIN连接JOIN连接子句如下：表[INNER|CROSS]JOIN表[连接条件]|表STRAIGHT_JOIN表|表STRAIGHT_JOIN表ON连接条件|表{LEFT|RIGHT}[OUTER]JOIN表连接条件|表NATURAL[{LEFT|RIGHT}[OUTER]]JOIN表其中，连接条件：ON连接表达式|USING(列表)指定查询数据源使用JOIN关键字的连接主要分为以下3种。（1）内连接。指定INNER关键字的连接为内连接，它相当于全连接用ON实现WHERE等值功能。【例】查找所有学生选过的课程名和课程号。SELECTDISTINCT课程名,cj.课程号 FROMKCINNERJOINcj ON(kc.课程号=cj.课程号);

内连接是系统默认的，可以省略INNER关键字。使用内连接后，FROM子句中的ON条件主要用来连接表，其他并不属于连接表的条件可以使用WHERE子句来指定。【例】查找选修了206号课程且成绩80分以上的学生的姓名及成绩。SELECT姓名,成绩 FROMxsJOINcjONxs.学号=cj.学号 WHERE课程号='206'AND成绩>=80;查询结果如图。指定查询数据源内连接还可用于多个表的连接。【例】查找选修了计算机基础课程且成绩不少于80分的学生的学号、姓名、课程名及成绩。SELECTxs.学号,姓名,课程名,成绩 FROMcjJOINxsONcj.学号=xs.学号 JOINkcONcj.课程号=kc.课程号 WHERE课程名='计算机导论'AND成绩>=80;查询结果如图。指定查询数据源作为特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。【例】查找相同成绩的学生的学号、课程号和成绩。SELECTa.学号,a.课程号,b.课程号,a.成绩 FROMcjasaJOINcjasb ONa.成绩=b.成绩ANDa.课程号!=b.课程号 WHEREa.成绩>=85;查询结果如图。指定查询数据源【例】查询课程表中所有学生选过的课程号和课程名。SELECTDISTINCT课程号,课程名 FROMkcINNERJOINcj USING(课程号);查询结果如图。指定查询数据源【例】查询前5个成绩记录学生的学号、课程名和成绩。SELECT学号,课程名,成绩 FROMkcINNERJOINcj USING(课程号) LIMIT5; USING(课程号);查询结果如图。指定查询数据源（2）外连接。指定了OUTER关键字的连接为外连接。外连接有以下几种形式。左外连接（LEFTOUTERJOIN）：结果表中除了匹配行外，还包括左表有但右表中不匹配的行，对于这样的行，从右表被选择的列设置为NULL。右外连接（RIGHTOUTERJOIN）：结果表中除了匹配行外，还包括右表有但左表中不匹配的行，对于这样的行，从左表被选择的列设置为NULL。自然连接（NATURALJOIN）：自然连接又分自然左外连接（NATURALLEFTOUTERJOIN）和自然右外连接（NATURALRIGHTOUTERJOIN）。NATURALJOIN的语义与使用了ON条件的INNERJOIN相同。指定查询数据源【例】查找所有学生情况及他们选修的课程号，若学生未选修任何课程，也要包括其情况。SELECTxs.*,课程号,成绩FROMxsLEFTOUTERJOINcjONxs.学号=cj.学号;【例】查找被选修了的课程的选修情况和所有开设的课程名。SELECT课程名,cj.* FROMcjRIGHTJOINkcONcj.课程号=kc.课程号 LIMIT40,10;查询结果如图。指定查询数据源【例】使用自然连接查询成绩表中的课程号和课程名。SELECT课程名,课程号 FROMkc WHERE课程号IN (SELECTDISTINCT课程号FROMkcNATURALRIGHTOUTERJOINcj);查询结果如图。指定查询数据源（3）交叉连接。指定了CROSSJOIN关键字的连接为交叉连接，功能与INNERJOIN等同。【例】查询学生表所有可能的选课情况。SELECTxs.学号,姓名,课程号 FROMxsCROSSJOINcj LIMIT40,10;查询结果如图。另外，STRAIGHT_JOIN连接的用法和INNERJOIN连接基本相同。不同的是，STRAIGHT_JOIN后不可使用USING子句替代ON条件。【例】查询成绩表包含的课程。SELECTDISTINCT课程名,cj.课程号 FROMkcSTRAIGHT_JOINcj ON(kc.课程号=cj.课程号);04查

询

条

件比较运算模式匹配范围比较空值比较子查询查询条件WHERE子句设置查询条件。WHERE条件其中，条件：<判定条件[逻辑运算符<判定条件>]判定条件:表达式{=|<|<=|>|>=|<=>|<>|!=}表达式 /*比较运算*/|匹配列[NOT]LIKE匹配表达式[ESCAPE'转义字符'] /*LIKE运算符*/|匹配列[NOT][REGEXP|RLIKE]匹配表达式 /*REGEXP运算符*/|表达式[NOT]BETWEEN表达式AND表达式 /*指定范围*/|表达式IS[NOT]NULL /*是否是空值判断*/|表达式[NOT]IN(SELECT语句|表达式[,…n]) /*IN子句*/|表达式{=|<|<=|>|>=|<=>|<>|!=}{ALL|SOME|ANY}(SELECT语句) /*比较子查询*/|EXIST(SELECT语句) /*EXIST子查询*/查询条件1．比较运算比较运算符用于比较两个表达式的值，MySQL支持的比较运算符有=（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<=>（相等或都等于空）、<>（不等于）、!=（不等于）。表达式{=|<|<=|>|>=|<=>|<>|!=}表达式其中，表达式是除TEXT和BLOB外的类型的表达式。当两个表达式值均不为空值（NULL）时，除了“<=>”运算符，其他比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，将返回UNKNOWN。【例】查询成绩表中学号为221101的学生的成绩。SELECT学号,课程号,成绩 FROMcj WHERE学号='221101';查询结果如图。查询条件【例】查询学生表中总学分大于50分的学生的情况。SELECT姓名,学号,出生日期,总学分 FROMxs WHERE总学分>50;查询结果如图。查询条件【例】查询学生表中包含备注的学生的情况。SELECT姓名,学号,专业,备注 FROMxs WHERE备注!='';查询结果如图。说明：（1）因为“备注”列为text类型，所以包含内容肯定不为''（注意中间没有空格）。（2）因为text属于字符串，可以用操作字符串函数，所以条件：“LENGTH(备注)>0”与上述效果相同。其中，LENGTH()为获得字符串长度。查询条件【例】查询学生表中专业为计算机、性别为女的学生的情况。SELECT姓名,学号,总学分 FROMxs WHERE专业='计算机'AND性别=0;查询结果如图。查询条件2．模式匹配（1）LIKE运算符。用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar、text、datetime等类型的数据，返回逻辑值TRUE或FALSE。匹配列[NOT]LIKE匹配表达式[ESCAPE'转义字符']说明：用匹配列与匹配表达式匹配。用LIKE进行模式匹配时，常使用特殊符号“_”和“%”，可进行模糊查询。转义字符：没有默认值，且必须为单个字符。当要匹配的字符串中含有与特殊符号（“_”和“%”）相同的字符时，应通过该字符前的转义字符指明其为模式串中的一个匹配字符。查询条件【例】查询学生表中姓“王”的学生的学号、姓名及性别。SELECT学号,姓名,性别 FROMxs WHERE姓名LIKE'王%';查询结果如图。【例】查询学生表中大三（学号第二个数字为0）的学生的学号、姓名及专业。SELECT学号,姓名,专业 FROMxs WHERE学号LIKE'_0%';查询结果如图。查询条件（2）REGEXP运算符。REGEXP是正则表达式的缩写，和LIKE运算符一样，REGEXP运算符有多种功能，但它不是SQL标准的一部分。REGEXP运算符的一个同义词是RLIKE。列名[NOT][REGEXP|RLIKE]匹配表达式REGEXP运算符则有更多具有特殊含义的符号，参见表。特殊字符含义特殊字符含义^匹配字符串的开始部分[abc]匹配方括号里出现的字符串abc$匹配字符串的结束部分[a-z]匹配方括号里出现的在a～z范围内的1个字符.匹配任意一个字符（包括回车和新行）[^a-z]匹配方括号里出现的不在a～z范围内的1个字符*匹配星号之前的0个或多个字符的任意序列|匹配符号左边或右边出现的字符串+匹配加号之前的1个或多个字符的任意序列[[..]]匹配方括号里出现的符号（如空格、换行、括号、句号、冒号、加号、连字符等）?匹配问号之前的0个或多个字符[[:<:]]和[[:>:]]匹配一个单词的开始和结束{n}匹配括号前的内容出现n次的序列[[::]]匹配方括号里出现的字符中的任意一个()匹配括号里的内容

查询条件【例】查询学生表姓“李”的学生的学号、姓名和专业。SELECT学号,姓名,专业 FROMxs WHERE姓名REGEXP'^李';查询结果如图。【例】查询学生表学号里包含数字4、5、6的学生的学号、姓名和专业。SELECT学号,姓名,专业 FROMxs WHERE学号REGEXP'[4,5,6]';查询结果如图。查询条件【例】查询学生表学号以22开头、以01结尾的学生的学号、姓名和专业。SELECT学号,姓名,专业 FROMxs WHERE学号REGEXP'^22.*01$';查询结果如图。查询条件3．范围比较用于范围比较的关键字有两个：BETWEEN和IN。（1）BETWEEN指定查询范围。表达式[NOT]BETWEEN表达式1AND表达式2当不使用NOT时，若表达式的值在表达式1与表达式2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。查询条件（2）IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时都返回TRUE，否则返回FALSE。表达式IN(表达式[,…n])【例】查询学生表中2004年出生的学生的情况。SELECT学号,姓名,专业,出生日期 FROMxs WHERE出生日期BETWEEN'2004-1-1'AND'2004-12-31';查询结果如图。查询条件【例】查询学生表中计算机、通信工程或电气工程专业的学生的情况。SELECT* FROMxs WHERE专业IN('计算机','通信工程','电气工程');该语句与下句等价：SELECT* FROMxs WHERE专业='计算机'OR专业='通信工程'OR专业='电气工程';查询条件4．空值比较当需要判定一个表达式的值是否为空值时，使用ISNULL关键字。表达式IS[NOT]NULL当不使用NOT时，若表达式值为空值，则返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例】查询学生表备注为空的学生的情况。SELECT* FROMxs WHERE备注ISNULL;查询条件5．子查询子查询除了可以用在SELECT语句中外，还可以用在INSERT、UPDATE及DELETE语句中，通常与IN、比较运算符及EXIST谓词结合使用。（1）IN子查询。IN子查询用于进行一个给定值是否在子查询结果集中的判断。表达式[NOT]IN(SELECT语句)当表达式与子查询SELECT语句的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。查询条件在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再执行外查询。【例】查找xscj数据库中选修了206号课程的学生的姓名、学号。SELECT姓名,学号 FROMxs WHERE学号IN (SELECT学号 FROMcj WHERE课程号='206' );说明：先执行下列子查询：SELECT学号 FROMcj WHERE课程号='206';得到一个只含有学号列的表，cj中的每个课程号为206的行在结果表中都有一行。查询条件【例】查找未选修离散数学课程的学生的姓名、学号、专业。SELECT姓名,学号,专业 FROMxs WHERE学号NOTIN (SELECT学号 FROMcj WHERE课程号IN (SELECT课程号 FROMkc WHERE课程名='离散数学' ) );查询条件（2）比较子查询将表达式的值与子查询的结果进行比较运算。表达式{<|<=|=|>|>=|<>|!=}{ALL|SOME|ANY}(select语句)如果子查询的结果集只返回一行数据，可以通过比较运算符直接比较。ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时才返回TRUE，否则返回FALSE。【例】查找选修了离散数学课程的学生的学号。SELECTcount(学号) FROMcj WHERE课程号= (SELECT课程号 FROMkc WHERE课程名='离散数学' );查询结果如图。查询条件【例】查找学生表中比所有计算机专业的学生出生日期都大的学生的学号、姓名、专业、出生日期。SELECT学号,姓名,专业,出生日期 FROMxs WHERE出生日期>ALL (SELECT出生日期 FROMxs WHERE专业='计算机' );查询结果如图。查询条件【例】查找课程号为206的成绩不低于课程号为102的最低成绩的学生的学号。SELECT学号 FROMcj WHERE课程号='206'AND成绩>=ANY (SELECT成绩 FROMcj WHERE课程号='102' );查询条件（3）EXISTS子查询用于测试子查询的结果是否为空表，若子查询的结果集不为空，则返回TRUE，否则返回FALSE。NOTEXISTS返回值与EXIST刚好相反。[NOT]EXISTS(SELECT语句)【例】查找选修206号课程的学生的姓名。SELECT姓名 FROMxs WHEREEXISTS (SELECT* FROMcj WHERE学号=xs.学号AND课程号='206' );查询条件【例】查找选修了全部课程的学生的姓名。SELECT姓名 FROMxs WHERENOTEXISTS (SELECT* FROMkc WHERENOTEXISTS (SELECT* FROMcj WHERE学号=xs.学号AND课程号=kc.课程号 ) );说明：由于没有人选修全部课程，所以结果为空。查询条件（4）子查询的4种类型如下：返回一个表的子查询是表子查询；返回带有一个或多个值的一行的子查询是行子查询；返回一行或多行，但每行上只有一个值的是列子查询；返回一个值的是标量子查询。每个标量子查询都是一个列子查询和行子查询。上面介绍的子查询都属于列子查询。另外，子查询还可以用在SELECT语句的其他子句中。表子查询可以用在FROM子句中，但必须为子查询产生的中间表定义一个别名。【例】从xs表中查找总学分大于50分的男学生的姓名、学号和总学分。SELECT姓名,学号,总学分 FROM(SELECT姓名,学号,性别,总学分 FROMxs WHERE总学分>50 )ASstudent WHERE性别;查询结果如图。查询条件【例】从学生表中查找所有女学生的姓名、学号，以及与221101号学生的年龄差距。SELECT学号,姓名,YEAR(出生日期)-YEAR((SELECT出生日期 FROMxs WHERE学号='221101'))AS年龄差距FROMxsWHERE性别=FALSE;查询结果如图。查询条件【例】查找与学号为221101的学生性别相同、总学分相同的学生的学号和姓名。SELECT学号,姓名 FROMxs WHERE(性别,总学分)=(SELECT性别,总学分 FROMxs WHERE学号='221101' );查询结果如图。05查

询

分

组查询分组GROUPBY子句用于对查询结果集分组。GROUPBY{列名|表达式|输出项号}[ASC|DESC],...[WITHROLLUP]说明：可以按照列名、表达式、输出项号进行分组，分组后输出顺序指定ASC为升序或DESC为降序。表达式由列、运算符和函数（聚合函数）组成。【例】求xscj数据库中各专业的学生数。SELECT专业,COUNT(*)AS'学生数' FROMxs GROUPBY专业;查询结果如图。查询分组【例】求被选修的各门课程的平均成绩和选修该课程的人数。SELECT课程号,AVG(成绩)AS'平均成绩',COUNT(学号)AS'选修人数' FROMcj GROUPBY课程号;查询结果如图。查询分组【例】查询每个专业的男生人数、女生人数、总人数，以及学生总人数。SELECT专业,性别,count(*)AS'人数' FROMxs GROUPBY专业,性别 WITHROLLUP;查询结果如图。查询分组产生的规则如下：按列的逆序依次进行汇总。如本例根据专业（2个专业）和性别（1和0）组合将xs表分为4组，使用ROLLUP操作符后，先对性别列产生了汇总行（针对专业相同的行），然后对专业与性别均不同的值产生了汇总行。所产生的汇总行中对应具有不同列值的列值将置为NULL。可以将上述语句与不带ROLLUP操作符的GROUPBY子句的执行情况进行比较：SELECT专业,性别,COUNT(*)AS'人数' FROMxs GROUPBY专业,性别;查询结果如图。查询分组【例】查询每门课程各专业的平均成绩、每门课程的总平均成绩和所有课程的总平均成绩。SELECT课程名,专业,AVG(成绩)AS'平均成绩' FROMcj,kc,xs WHEREcj.课程号=kc.课程号ANDcj.学号=xs.学号 GROUPBY课程名,专业 WITHROLLUP;查询结果如图。06分组后过滤分组后过滤使用HAVING子句的目的与WHERE子句不同，WHERE子句从FROM指定的表选择行，而HAVING子句则用来在GROUPBY子句选择分组后选择输出的行。HAVING子句中的条件可以包含聚合函数，而WHERE子句则不可以。HAVING条件SQL标准要求HAVING必须引用GROUPBY子句中的列或用于聚合函数中的列。【例】查询平均成绩在85分以上的学生的学号和平均成绩。SELECT学号,AVG(成绩)AS平均成绩 FROMcj GROUPBY学号 HAVING平均成绩>=85;查询结果如图。分组后过滤说明：（1）如果不包含分组GROUP子句和HAVING子句：SELECT学号,AVG(成绩)AS平均成绩 FROMcj;则查询结果显示一个记录为所有人的平均成绩，如图。（2）如果不包含分组GROUP子句，则查询结果所有人的平均成绩不大于等于85，没有显示记录。（3）如果不包含分组GROUP子句，而输出项不包括聚合函数，则HAVING与WHERE子句功能相当。SELECT学号,成绩 FROMcj HAVING成绩>=85;查询结果为所有大于等于85的学生课程成绩，如图。分组后过滤【例】查找选修课程超过2门且成绩都在70分以上的学生的学号。SELECT学号 FROMcj WHERE成绩>=70 GROUPBY学号 HAVINGCOUNT(*)>2;查询结果如图。分组后过滤【例】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。SELECT学号,AVG(成绩)AS'平均成绩' FROMcj WHERE学号IN (SELECT学号 FROMxs WHERE专业='通信工程' ) GROUPBY学号 HAVINGAVG(成绩)>=85;查询结果如图。07输

出

排

序输出排序ORDERBY子句指定结果行按指定列、表达式或者输出项号顺序排列，否则无法预料结果中行的顺序格式如下。ORDERBY{列名|表达式|输出项号}[ASC|DESC],...说明：输出项号表示按SELECT结果集中位置上的项排序。【例】将计算机专业学生的计算机导论课程成绩按降序排列。SELECTxs.学号,姓名,成绩 FROMxs,kc,cj WHERExs.学号=cj.学号 ANDcj.课程号=kc.课程号 AND课程名='计算机导论' AND专业='计算机' ORDERBY成绩DESC;查询结果如图。输出排序【例】将出生日期2003年后的学生按专业名拼音排序，相同专业按照出生日期从小到大排序。SELECT学号,姓名,专业,出生日期 FROMxsb WHERE出生日期>='2004-1-1' ORDERBY专业DESC,出生日期查询结果如图。输出排序【例】将通信工程专业学生的情况按平均成绩升序排列。SELECT学号,姓名,总学分 FROMxs WHERE专业='通信工程' ORDERBY(SELECTAVG(成绩) FROMcj GROUPbycj.学号 HAVINGxs.学号=cj.学号);查询结果如图。08联

合

查

询联合查询使用UNION子句，可以把来自许多SELECT语句的结果组合到一个结果集中。SELECT语句UNIONSELECT语句...说明：使用UNION的时候，在第一个SELECT语句中被使用的列名称被用于结果中的列名称。【例】联合计算机专业和通信工程专业2022学生信息。（1）为了测试联合，将学生表（xs）分成计算机专业和通信工程专业表，模拟实际独立结构相同的表。USExscj;DROPTABLEIFEXISTSxs_jsj,xs_txgc;CREATETABLExs_jsjSELECT*FROMxsWHERE专业='计算机';CREATETABLExs_txgcSELECT*FROMxsWHERE专业='通信工程';联合查询（2）联合计算机专业和通信工程专业2022学生信息。SELECT* FROMxs_jsj WHERE学号LIKE'22%' UNION SELECT* FROMxs_txgc WHERE学号LIKE'22%';查询结果如图。联合查询下列命令与上述功能相同：SELECT* FROM (SELECT* FROMxs_jsj UNION SELECT* FROMxs_txgc )ASmyxs WHERE学号LIKE'22%';09输出到变量或文件输出到变量或文件使用INTO子句，可以把来自许多SELECT语句的结果输出到变量或者文件中。INTO变量名,...INTOOUTFILE'文件名'[CHARACTERSET字符集名]说明：默认情况下，查询结果在屏幕上显示。指定INTO变量名项，可将查询结果存放到内存变量中。指定OUTFILE项，将查询结果存放到指定文件中。10非基本数据类型查询枚举类型列查询条件集合类型列查询条件JSON类型列查询条件空间类型列查询条件非基本数据类型查询先显示rsk表结构：USExscj;DESCRIBExsk; #（a）SELECT*FROMxsk; #（b）显示如图。非基本数据类型查询1．枚举类型列查询条件例如，“毕业去向”枚举定义enum('直接就业','考研','考公务员','出国留学','创业')，该列可以取的值和对应的索引编号如表。成员索引编号NULLNULL“0'直接就业'1'考研'2'考公务员'3'出国留学'4'创业'5非基本数据类型查询（1）精确查询枚举类型列查询条件可以用成员序号，也可以用字符串。【例】在xsk表中查询毕业去向为直接就业或者创业。USExscj;SELECT学号,毕业去向FROMxsk WHERE毕业去向='直接就业'OR毕业去向=5;显示如图。（2）模糊查询可以将枚举类型的列存放的内容认为是枚举字符串。SELECT学号,毕业去向FROMxsk WHERE毕业去向LIKE'%业';显示如图。非基本数据类型查询2．集合类型列查询条件例如：“爱好”集合定义set('书法','绘画','音乐','运动')，对应“爱好”列存储内容如表。成员二进制值十进制值'书法'11'绘画'102'音乐'1004'运动'10008非基本数据类型查询1）精确查询集合类型列查询条件可以采用将成员的二进制位序号表示成十进制，也可以采用字符串，多个成员顺序必须完全相同，用逗号（,）分隔。【例】在xsk表中查询爱好为书法或者绘画，查询同时爱好书法和绘画。（1）查询爱好为书法或者绘画：USExscj;SELECT学号,爱好FROMxsk WHERE爱好='书法'OR爱好=2;显示如图（a）。非基本数据类型查询（2）查询同时爱好书法和绘画。USExscj;SELECT学号,爱好FROMxsk WHERE爱好='书法,绘画';或者SELECT学号,爱好FROMxsk WHERE爱好=3;显示如图（b）。非基本数据类型查询2）模糊查询可以将集合类型的列存放的内容认为是集合字符串，之间用逗号（,）分隔。SELECT学号,爱好FROMxskWHERE爱好LIKE'%书法%';显示如图（c）。3）按内容查询USExscj;SELECT学号,爱好FROMxsk WHEREFIND_IN_SET('绘画',爱好);或者SELECT学号,爱好FROMxsk WHEREFIND_IN_SET('绘画',爱好)>0;查询结果如图（d）。非基本数据类型查询3．JSON类型列查询条件JSON类型列使用“列名->>路径”来指定JSON的某一路径“键”。也可以使用JSON函数进行查询。JSON函数很多，请参考有关文档。【例】在xsk表中按家庭地址查询。USExscj;SELECT学号,家庭地址->>'$."市"'AS城市,家庭地址->>'$."街道"'AS具体位置FROMxsk WHERE家庭地址->>'$."街道"'LIKE'%学府%'; #（a）SELECT学号,家庭地址->>'$."区县"'AS区县,家庭地址->>'$."街道"'AS具体位置FROMxsk WHEREJSON_CONTAINS(家庭地址,'"南京"','$."市"'); #（b）查询结果如图。

非基本数据类型查询4．空间类型列查询条件空间类型列数据一般不能直接使用表达式作为查询条件，而是需要通过空间数据处理系统函数进行处理。【例】查询南京仙尧投递站与学生家庭地理位置的距离。SET@g1=ST_GeomFromText('POINT(118.8832.11)'); #南京仙尧投递站位置USExscj;SELECT学号,家庭地址->>'$."市"'AS城市,家庭地址->>'$."街道"'AS地点,TRUNCATE(ST_Distance(地理位置,@g1)*111195/1000,2)ASkm FROMxsk;运行查询结果如图。11分

区

查

询查询数据库表分区情况直接按分区查询分区查询1．查询数据库表分区情况在MySQL系统数据库information_schema中的PARTITIONS表存放MySQL实例中所有数据库表的分区信息，对该表查询就可了解你关注的数据库表的分区情况。【例】查询xscj数据库xsb表分区情况。SELECT PARTITION_NAME分区名称, PARTITION_ORDINAL_POSITION排序, PARTITION_METHOD分区类型, PARTITION_EXPRESSION表达式, PARTITION_DESCRIPTION描述, CREATE_TIME创建时间, TABLE_ROWSAS记录数 FROMinformation_schema.PARTITIONS WHERETABLE_SCHEMA='xscj'ANDTABLE_NAME='xsb';查询结果如图。分区查询若查询条件换成如下形式运行，查询结果如图。WHERETABLE_SCHEMA='xscj'ANDTABLE_NAME='kcb';若查询条件换成如下形式运行，查询结果如图。WHERETABLE_SCHEMA='xscj'ANDTABLE_NAME='cjb';分区查询2．直接按分区查询如果查询的条件为分区列，则可以直接通过分区查询。【例】查询xscj数据库xsb表的2021年入学的女生。USExscj;SELECT* FROMxsb PARTITION(p1) WHERE!性别;SELECT* FROMxsb WHERE学号LIKE'21%'AND!性别;查询结果如图。分区查询【例】查询第四学年开设课程名以“计算机”打头的课程。USExscj;SELECT* FROMkcb PARTITION(四学年课)WHERE课程名LIKE'计算机%';查询结果如图。分区查询【例】查询指定学号和指定课程号的学生成绩。USExscj;SET@xh='211101';SET@kch='101';SELECT*FROMcjb WHERE学号=@xhAND课程号=@kch;查询结果如图。第4章

查询和视图——视

图视

图使用视图具有下列优点。（1）为用户集中数据。定义视图可以将分散在多个表中数据集中在一起，用户对多表数据查询和处理就可以在一个视图中完成。（2）创建视图可以向最终用户隐藏复杂的表连接，使用视图的用户并不知道基本数据的来源。（3）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。（4）便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库中的数据，这样同样的数据只需存储一次。（5）可以重新组织数据以便输出到其他应用程序中。01创

建

视

图创建视图视图在数据库中是作为一个对象来存储的。用户创建视图前，要保证自己已被数据库所有者授权使用CREATEVIEW语句，并且有权操作视图所涉及的表或其他视图。CREATE[ORREPLACE]VIEW视图名[(视图列名,...)] [选项] ASSELECT语句 [WITH[CASCADED|LOCAL]CHECKOPTION]视图名：视图的命名必须遵循标识符命名规则，不能与表和其他视图同名。ORREPLACE：能够替换已有的同名视图。它