第4章 MySQL查询和视图

第4章MySQL查询和视图关系运算基础4.14.24.3MySQL数据库查询MySQL视图4.1关系运算基础4.1.1选择（Selection）运算选择是单目运算，其运算对象是一个表。该运算按给定的条件，从表中选出满足条件的行形成一个新表作为运算结果。选择运算的记号为F（R）。其中是选择运算符，下标F是一个条件表达式，R是被操作的表。【例4.1】学生情况表如表4.1所示。学号姓名专业名性别出生日期总学分备注081101王林计算机男50081102程明计算机男50081103王燕计算机女504.1.1选择（Selection）运算若要在学生情况表中找出性别为女的行形成一个新表，则运算式为：F（学生情况）上式中F：性别=“女”，该选择运算的结果如表4.2所示。学号姓名专业名性别出生日期总学分备注081103王燕计算机女504.1.2投影（Projection）运算投影也是单目运算，该运算从表中选出指定的属性值组成一个新表，记为：A(R)。其中A是属性名（即列名）表，R是表名。【例4.2】若在表4.1中对学号、姓名和总学分投影，运算式为：学号，姓名，总学分（学生情况）该运算得到如表4.3所示的新表。学号姓名总学分081101王林50081102程明50081103王燕504.1.3连接（Join）运算1．等值连接两个表连接最常用的条件就是两个表的某些列值相等，这样的连接称为等值连接，记为：RFS。其中，R、S是被操作的表，F是条件。【例4.3】若表A和B分别如表4.4和表4.5所示，则RFS如表4.6所示，其中，F为：T1=T3。T1T2T3T4T51A13M6F20N2BT1T2T3T4T51A13M2B20N4.1.3连接（Join）运算2．自然连接自然连接运算记为：RS，其中，R和S是参与运算的两个表。【例4.4】若A表和B表分别如表4.7和表4.8所示，则AB如表4.9所示。T1T2T310A1B15A1C220D2C2T1T4T5T6T1T4T5T61100A1D1200A2D11002B2C1510A2C2T1T2T3T4T5T65A1C210A2C220D2C20A2D14.2MySQL数据库查询4.2.1SELECT语句下面介绍SELECT语句，它是SQL的核心。其语法格式如下：SELECT[ALL|DISTINCT|DISTINCTROW][HIGH_PRIORITY][STRAIGHT_JOIN][SQL_SMALL_RESULT][SQL_BIG_RESULT][SQL_BUFFER_RESULT][SQL_CACHE|SQL_NO_CACHE][SQL_CALC_FOUND_ROWS]select_expr[,select_expr...][FROMtable_reference[,table_reference]…]/*FROM子句*/[WHEREwhere_condition]/*WHERE子句*/[GROUPBY{col_name|expr|position}[ASC|DESC],...[WITHROLLUP]]/*GROUPBY子句*/[HAVINGwhere_condition]/*HAVING子句*/[ORDERBY{col_name|expr|position}[ASC|DESC],...]/*ORDERBY子句*/[LIMIT{[offset,]row_count|row_countOFFSEToffset}]/*LIMIT子句*/[PROCEDUREprocedure_name(argument_list)][INTOOUTFILE'file_name'[CHARACTERSETcharset_name]export_options|INTODUMPFILE'file_name'|INTOvar_name[,var_name]][FORUPDATE|LOCKINSHAREMODE]]4.2.1SELECT语句SELECT关键词的后面可以使用很多的选项：ALL|DISTINCT|DISTINCTROW：这几个选项指定是否重复行应被返回。HIGH_PRIORITY：给予SELECT更高的优先权，使查询立刻执行，加快查询速度。STRAIGHT_JOIN：用于促使MySQL优化器把表联合在一起，加快查询速度。SQL_SMALL_RESULT：可以与GROUPBY或DISTINCT同时使用，来告知MySQL优化器结果集合是较小的。SQL_BIG_RESULT：可以与GROUPBY或DISTINCT同时使用，来告知MySQL优化器结果集合有很多行。SQL_BUFFER_RESULT：促使结果被放入一个临时表中。SQL_CACHE|SQL_NO_CACHE：告知MySQL是否要把查询结果存储在查询缓存中。SQL_CALC_FOUND_ROWS：告知MySQL计算有多少行应位于结果集合中，不考虑任何LIMIT子句。INTOOUTFILE'file_name'：这个语句可以将表中的行导出到一个文件中，这个文件被创建在服务器主机中，file_name为文件名。4.2.2选择SELECT列1．选择指定的列使用SELECT语句选择一个表中的某些列，各列名之间要以逗号分隔。【例4.5】查询xscj数据库的xs表中各个同学的姓名、专业名和总学分。usexscjselect姓名,专业名,总学分fromxs;4.2.2选择SELECT列2．定义列别名当希望查询结果中的某些列（或所有列）显示时使用自己选择的列标题，可以在列名之后使用AS子句来更改查询结果的列别名。其语法格式为如下：SELECTcolumn_name[AS]column_alias【例4.6】查询xs表中计算机专业同学的学号、姓名和总学分，结果中各列的标题分别指定为number、name和mark。select学号asnumber,姓名asname,总学分asmarkfromxswhere专业名='计算机';4.2.2选择SELECT列执行结果如下图所示。4.2.2选择SELECT列3．替换查询结果中的数据在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询xs表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2……ELSE表达式nEND4.2.2选择SELECT列【例4.7】查询xs表中计算机专业各同学的学号、姓名和总学分，对总学分按如下规则进行替换：若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50～52之间，替换为“合格”；若总学分大于52，替换为“优秀”。总学分列的标题更改为“等级”。select学号,姓名,casewhen总学分isnullthen'尚未选课'when总学分50then'不及格'when总学分=50and总学分=52then'合格'else'优秀'endas等级fromxswhere专业名='计算机';4.2.2选择SELECT列执行结果如图所示。4.2.2选择SELECT列4．计算列值使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：SELECTexpression[,expression]【例4.8】按120分制重新计算成绩，显示xs_kc表中学号为081101的学生成绩信息。select学号,课程号,成绩*1.20as成绩120fromxs_kcwhere学号='081101';执行结果如图所示。4.2.2选择SELECT列5．消除结果集中的重复行对表只选择其某些列时，可能会出现重复行。例如，若对xscj数据库的xs表只选择专业名和总学分，会出现多行重复的情况。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行，格式是：SELECTDISTINCT|DISTINCTROWcolumn_name[,column_name…]其含义是对结果集中的重复行只保留一个，以保证行的唯一性。【例4.9】对xscj数据库的xs表只选择专业名和总学分，消除结果集中的重复行。selectdistinct专业名,总学分fromxs;执行结果如图所示。4.2.2选择SELECT列6．聚合函数（aggregationfunction）表4.10列出了一些常用的聚合函数。函数名说明COUNT求组中项数，返回int类型整数MAX求最大值MIN求最小值SUM返回表达式中所有值的和AVG求组中值的平均值STD或STDDEV返回给定表达式中所有值的标准差VARIANCE返回给定表达式中所有值的方差GROUP_CONCAT返回由属于一组的列值连接组合而成的结果BIT_AND逻辑或BIT_OR逻辑与BIT_XOR逻辑异或4.2.2选择SELECT列（1）COUNT函数聚合函数中最经常使用的是COUNT函数，用于统计组中满足条件的行数或总行数，返回SELECT语句检索到的行中非NULL值的数目，若找不到匹配的行，则返回0。语法格式：COUNT({[ALL|DISTINCT]expression}|*)【例4.10】求学生的总人数。selectcount(*)as'学生总数'fromxs;执行结果如图所示。4.2.2选择SELECT列【例4.11】统计备注不为空的学生数目。selectcount(备注)as'备注不为空的学生数目'fromxs;执行结果如图所示。【例4.12】统计总学分在50分以上的人数。selectcount(总学分)as'总学分50分以上的人数'fromxswhere总学分50;执行结果如图所示。4.2.2选择SELECT列（2）MAX和MINMAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：MAX/MIN([ALL|DISTINCT]expression)【例4.13】求选修101课程的学生的最高分和最低分。selectmax(成绩),min(成绩)fromxs_kcwhere课程号='101';执行结果如图所示。4.2.2选择SELECT列（3）SUM函数和AVG函数SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：SUM/AVG([ALL|DISTINCT]expression)其中，expression是常量、列、函数或表达式，其数据类型只能是数值型数据。【例4.14】求学号081101的学生所学课程的总成绩。selectsum(成绩)as'课程总成绩'fromxs_kcwhere学号='081101';执行结果如图所示。4.2.2选择SELECT列【例4.15】求选修101课程的学生的平均成绩。selectavg(成绩)as'课程101平均成绩'fromxs_kcwhere课程号='101';执行结果如图所示。4.2.2选择SELECT列（4）VARIANCE和STDDEV（STD）函数VARIANCE和STDDEV函数分别用于计算特定的表达式中的所有值的方差和标准差。语法格式：VARIANCE/STDDEV([ALL|DISTINCT]expression)【例4.16】求选修101课程的成绩的方差。selectvariance(成绩)fromxs_kcwhere课程号='101';执行结果如图所示。4.2.2选择SELECT列【例4.17】求选修101课程的成绩的标准差。selectstddev(成绩)fromxs_kcwhere课程号='101';执行结果如图所示。4.2.2选择SELECT列（5）GROUP_CONCAT函数MySQL支持一个特殊的聚合函数GROUP_CONCAT。该函数返回来自一个组指定列的所有非NULL值，这些值一个接着一个放置，中间用逗号隔开，并表示为一个长长的字符串。这个字符串的长度是有限制的，标准值是10